Com construir un gràfic en cascada

Cada cop més sovint em trobo en els informes de diferents empreses i escolto les peticions dels estudiants per explicar com es construeix un diagrama en cascada de desviacions: també és una "cascada", també és una "cascada", també és un "pont". ”, també és un “pont”, etc. Sembla una cosa així:

Des de la distància, realment sembla una cascada de cascades en un riu de muntanya o un pont penjant: qui veu què 🙂

La peculiaritat d'aquest diagrama és que:

• Veiem clarament el valor inicial i final del paràmetre (la primera i l'última columnes).
• Els canvis positius (creixement) es mostren en un color (normalment verd), i els negatius (disminució) als altres (normalment vermell).
  
• De vegades, el gràfic també pot contenir columnes de subtotal (grisva aterrar a les columnes de l'eix x).
  

A la vida quotidiana, aquests diagrames s'utilitzen normalment en els casos següents:

• Visual visualització de la dinàmica qualsevol procés en el temps: flux de caixa (cash-flow), inversions (invertim en un projecte i en obtenim beneficis).
• Visualització implementació del pla (la columna més a l'esquerra del diagrama és un fet, la columna més a la dreta és un pla, tot el diagrama reflecteix el nostre procés d'avançar cap al resultat desitjat)
• Quan necessiteu visual mostrar factorsque afecten el nostre paràmetre (anàlisi factorial del benefici –en què consisteix).
  

Hi ha diverses maneres de crear aquest gràfic: tot depèn de la vostra versió de Microsoft Excel.

Mètode 1: més fàcil: tipus integrat a Excel 2016 i posterior

Si teniu Excel 2016, 2019 o posterior (o Office 365), construir aquest gràfic no és difícil: aquestes versions d'Excel ja tenen aquest tipus integrat de manera predeterminada. Només caldrà seleccionar una taula amb dades i seleccionar a la pestanya Insereix (Insereix) Comando en cascada (Cascada):

Com a resultat, obtindrem un diagrama gairebé preparat:

Podeu definir immediatament els colors de farciment desitjats per a les columnes positives i negatives. La manera més senzilla de fer-ho és seleccionar les files adequades Augmentar и disminuir directament a la llegenda i fent clic amb el botó dret sobre ells, seleccioneu l'ordre Omplir (Omplir):

Si necessiteu afegir columnes amb subtotals o un total de columnes final al gràfic, és més convenient fer-ho amb funcions. SUBTOTALS (SUBTOTALS) or UNITAT (AGREGAT). Calcularan l'import acumulat des de l'inici de la taula, excloent-ne els totals similars situats més amunt:

En aquest cas, el primer argument (9) és el codi de l'operació de sumació matemàtica, i el segon (0) fa que la funció ignori els totals ja calculats dels trimestres anteriors als resultats.

Després d'afegir files amb totals, queda seleccionar el total de columnes que han aparegut al diagrama (feu dos clics individuals consecutius a la columna) i, fent clic amb el botó dret del ratolí, seleccionar l'ordre Estableix com a total (Estableix com a total):

La columna seleccionada aterrarà a l'eix x i canviarà automàticament de color a gris.

Això, de fet, és tot: el diagrama de la cascada està preparat:

Mètode 2. Universal: columnes invisibles

Si teniu Excel 2013 o versions anteriors (2010, 2007, etc.), el mètode descrit anteriorment no us funcionarà. Haureu de donar una volta i retallar el gràfic de cascada que falta d'un histograma apilat normal (sumant les barres unes sobre les altres).

El truc aquí és utilitzar columnes de suport transparents per elevar les nostres files de dades vermelles i verdes a l'alçada correcta:

Per construir aquest gràfic, hem d'afegir unes quantes columnes auxiliars més amb fórmules a les dades d'origen:

• Primer, hem de dividir la nostra columna original separant els valors positius i negatius en columnes separades mitjançant la funció IF (SI).  
• En segon lloc, haureu d'afegir una columna davant de les columnes xumets, on el primer valor serà 0, i a partir de la segona cel·la, la fórmula calcularà l'alçada d'aquestes columnes de suport molt transparents.
  

Després d'això, queda seleccionar tota la taula excepte la columna original Flux i creeu un histograma apilat regular Inserció — Histograma (Insereix — gràfic de columnes):

Si ara seleccioneu les columnes blaves i les feu invisibles (feu clic amb el botó dret sobre elles - Format de fila - Omplir - Sense omplir), llavors només obtenim el que necessitem. 

L'avantatge d'aquest mètode és la simplicitat. Als menys: la necessitat de comptar columnes auxiliars.

Mètode 3. Si anem al vermell, tot és més difícil

Malauradament, el mètode anterior només funciona adequadament per a valors positius. Si almenys en alguna zona la nostra cascada entra en una zona negativa, la complexitat de la tasca augmenta significativament. En aquest cas, caldrà calcular cada fila (maniquí, verd i vermell) per separat per a les parts negatives i positives amb fórmules:

Per no patir gaire i no reinventar la roda, es pot descarregar una plantilla preparada per a aquest cas al títol d'aquest article.

Mètode 4. Exòtic: bandes amunt-baix

Aquest mètode es basa en l'ús d'un element especial poc conegut de gràfics plans (histogrames i gràfics): Bandes de dalt a baix (Barres amunt i avall). Aquestes bandes connecten els punts de dos gràfics per parelles per mostrar clarament quin dels dos punts és més alt o més baix, que s'utilitza activament quan es visualitza el pla-fet:

És fàcil esbrinar que si eliminem les línies dels gràfics i deixem només les bandes amunt i avall al gràfic, obtindrem la mateixa "cascada".

Per a aquesta construcció, hem d'afegir dues columnes addicionals a la nostra taula amb fórmules senzilles que calcularan la posició dels dos gràfics invisibles necessaris:

Per crear una "cascada", heu de seleccionar una columna amb mesos (per a signatures al llarg de l'eix X) i dues columnes addicionals Programa 1 и Programa 2 i construïu un gràfic regular per començar a utilitzar Inserir - Gràfic (Insereix — gràfic de línies):

Ara afegim bandes amunt i avall al nostre gràfic:

• A Excel 2013 i versions posteriors, això s'ha de seleccionar a la pestanya constructor Comando Afegeix un element de gràfic — Faxes d'augment-disminució (Disseny — Afegeix un element de gràfic — Barres amunt i avall)
• A Excel 2007-2010: aneu a la pestanya Disposició: barres de decrement avançat (Disposició: barres amunt i avall)

Aleshores, el gràfic es veurà com aquest:

Queda per seleccionar els gràfics i fer-los transparents fent clic sobre ells al seu torn amb el botó dret del ratolí i seleccionant l'ordre Format de sèrie de dades (Format sèrie). De la mateixa manera, podeu canviar els colors estàndard de les ratlles blanques i negres d'aspecte bastant desagradable a verd i vermell per obtenir una imatge més agradable al final:

A les últimes versions de Microsoft Excel, l'amplada de les barres es pot canviar fent clic a un dels gràfics transparents (no les barres!) amb el botó dret del ratolí i seleccionant l'ordre. Format de la sèrie de dades: espai lliure lateral (Format de sèrie — Amplada de l'espai).

A les versions anteriors d'Excel, havíeu d'utilitzar l'ordre Visual Basic per solucionar-ho:

1. Ressalteu el diagrama construït
2. Premeu la drecera del teclat Alt+F11per entrar al Visual Basic Editor
3. Premeu la drecera de teclat Ctrl+Gper obrir l'entrada d'ordres directes i el tauler de depuració Immediat (normalment es troba a la part inferior).

4. Copieu i enganxeu l'ordre següent allà: ActiveChart.ChartGroups(1).GapWidth = 30 i premi Enter:

Per descomptat, podeu jugar amb el valor del paràmetre si voleu. GapWidthper aconseguir l'autorització desitjada:

• Com crear un gràfic de vinyetes a Excel per visualitzar KPI  
• Novetats als gràfics a Excel 2013
• Com crear un gràfic interactiu "en directe" a Excel