Hur man bygger ett vattenfallsdiagram

Allt oftare träffas jag i olika företags rapportering och hör önskemål från praktikanter om att förklara hur ett kaskaddiagram av avvikelser är uppbyggt – det är också ett ”vattenfall”, det är också ett ”vattenfall”, det är också en ”bro”. ”, det är också en ”bro” etc. . Det ser ut ungefär så här:

På avstånd ser det verkligen ut som en kaskad av vattenfall på en bergsflod eller en hängbro – vem ser vad 🙂

Det speciella med ett sådant diagram är att:

• Vi ser tydligt det initiala och slutliga värdet för parametern (den första och sista kolumnen).
• Positiva förändringar (tillväxt) visas i en färg (vanligtvis grön), och negativa (nedgång) till andra (vanligtvis röd).
  
• Ibland kan diagrammet också innehålla delsumma kolumner (grålandade på x-axelns kolumner).
  

I vardagen används sådana diagram vanligtvis i följande fall:

• Visuell dynamikdisplay vilken process som helst i tiden: kassaflöde (kassaflöde), investeringar (vi investerar i ett projekt och får vinst på det).
• Visualisering planens genomförande (kolumnen längst till vänster i diagrammet är ett faktum, kolumnen längst till höger är en plan, hela diagrammet återspeglar vår process att gå mot det önskade resultatet)
• När du behöver visuellt visa faktorersom påverkar vår parameter (faktoriell analys av vinst – vad den består av).
  

Det finns flera sätt att bygga ett sådant diagram – allt beror på din version av Microsoft Excel.

Metod 1: Enklast: Inbyggd typ i Excel 2016 och nyare

Om du har Excel 2016, 2019 eller senare (eller Office 365) är det inte svårt att bygga ett sådant diagram – dessa versioner av Excel har redan denna typ inbyggd som standard. Det kommer bara att vara nödvändigt att välja en tabell med data och välja på fliken Insert (Föra in) Kommando cascading (Vattenfall):

Som ett resultat kommer vi att få ett nästan färdigt diagram:

Du kan omedelbart ställa in önskade fyllningsfärger för de positiva och negativa kolumnerna. Det enklaste sättet att göra detta är att välja lämpliga rader Öka и minskning direkt i förklaringen och genom att högerklicka på dem väljer du kommandot Fyll (Fylla):

Om du behöver lägga till kolumner med delsummor eller en slutlig kolumntotal i diagrammet är det mest praktiskt att göra detta med funktioner DELSUMMOR (DELSUMMOR) or ENHET (AGGREGAT). De kommer att beräkna det ackumulerade beloppet från början av tabellen, samtidigt som de exkluderar de liknande summorna ovan:

I det här fallet är det första argumentet (9) koden för den matematiska summeringsoperationen, och det andra (0) gör att funktionen ignorerar redan beräknade summor för tidigare kvartal i resultaten.

Efter att ha lagt till rader med totalsummor återstår att välja den totala kolumner som har dykt upp i diagrammet (gör två på varandra följande enstaka klick på kolumnen) och, genom att högerklicka på musen, välj kommandot Ställ in som totalt (Ange som totalt):

Den valda kolumnen kommer att landa på x-axeln och automatiskt ändra färg till grått.

Det är faktiskt allt – vattenfallsdiagrammet är klart:

Metod 2. Universal: osynliga kolumner

Om du har Excel 2013 eller äldre versioner (2010, 2007, etc.), kommer metoden som beskrivs ovan inte att fungera för dig. Du måste gå runt och klippa ut det saknade vattenfallsdiagrammet från ett vanligt staplat histogram (summa staplarna ovanpå varandra).

Tricket här är att använda genomskinliga propskolumner för att höja våra röda och gröna datarader till rätt höjd:

För att bygga ett sådant diagram måste vi lägga till ytterligare några hjälpkolumner med formler till källdata:

• Först måste vi dela upp vår ursprungliga kolumn genom att separera de positiva och negativa värdena i separata kolumner med funktionen IF (OM).  
• För det andra måste du lägga till en kolumn framför kolumnerna nappar, där det första värdet kommer att vara 0, och från den andra cellen kommer formeln att beräkna höjden på dessa mycket genomskinliga stödjande kolumner.
  

Därefter återstår att välja hela tabellen förutom den ursprungliga kolumnen Flöde och skapa ett vanligt staplat histogram tvärs över Infälld — Histogram (Infoga — kolumndiagram):

Om du nu väljer de blå kolumnerna och gör dem osynliga (högerklicka på dem – Radformat – Fyll – Ingen fyllning), då får vi bara det vi behöver. 

Fördelen med denna metod är enkelheten. I minusen - behovet av att räkna extra kolumner.

Metod 3. Om vi ​​går in i minus är allt svårare

Tyvärr fungerar den tidigare metoden bara tillräckligt för positiva värden. Om åtminstone i något område vårt vattenfall går in i ett negativt område, ökar komplexiteten i uppgiften avsevärt. I det här fallet kommer det att vara nödvändigt att beräkna varje rad (dummy, grön och röd) separat för de negativa och positiva delarna med formler:

För att inte lida mycket och inte uppfinna hjulet på nytt, kan en färdig mall för ett sådant fall laddas ner i rubriken på den här artikeln.

Metod 4. Exotiskt: band upp och ner

Denna metod är baserad på användningen av ett speciellt föga känt element av platta diagram (histogram och grafer) - Upp och ner band (Up-Down Bars). Dessa band kopplar samman punkterna i två grafer i par för att tydligt visa vilken av de två punkterna som är högre eller lägre, vilket används aktivt när man visualiserar planfaktumet:

Det är lätt att räkna ut att om vi tar bort linjerna i diagrammen och bara lämnar upp-och-ner-banden på diagrammet, så kommer vi att få samma "vattenfall".

För en sådan konstruktion måste vi lägga till ytterligare två kolumner till vår tabell med enkla formler som beräknar positionen för de två nödvändiga osynliga graferna:

För att skapa ett "vattenfall" måste du välja en kolumn med månader (för signaturer längs X-axeln) och ytterligare två kolumner Schema 1 и Schema 2 och bygga en vanlig graf till att börja med Infoga – Graf (Infoga — Line Сhart):

Låt oss nu lägga till band upp och ner till vårt diagram:

• I Excel 2013 och nyare måste detta väljas på fliken Konstruktör Kommando Lägg till diagramelement — Band av ökning-minskning (Design – Lägg till diagramelement – ​​staplar upp och ner)
• I Excel 2007-2010 – gå till fliken Layout – försänkningsstaplar (Layout – staplar upp och ner)

Diagrammet kommer då att se ut ungefär så här:

Det återstår att välja graferna och göra dem transparenta genom att klicka på dem i tur och ordning med höger musknapp och välja kommandot Dataserieformat (Format serie). På samma sätt kan du ändra de vanliga, ganska sjaskiga svartvita randfärgerna till grönt och rött för att få en snyggare bild i slutändan:

I de senaste versionerna av Microsoft Excel kan bredden på staplarna ändras genom att klicka på en av de genomskinliga graferna (inte staplarna!) med höger musknapp och välja kommandot Dataserieformat – sidoavstånd (Formatserie — Gap width).

I äldre versioner av Excel var du tvungen att använda kommandot Visual Basic för att fixa detta:

1. Markera det byggda diagrammet
2. Tryck på kortkommandot andra+F11för att komma in i Visual Basic Editor
3. Tryck på snabbtangenten ctrl+Gför att öppna den direkta kommandoinmatningen och felsökningspanelen omedelbar (vanligen placerad längst ner).

4. Kopiera och klistra in följande kommando där: ActiveChart.ChartGroups(1).GapWidth = 30 och tryck ange:

Du kan naturligtvis leka med parametervärdet om du vill. GapWidthför att uppnå önskat spelrum:

• Hur man bygger ett punktdiagram i Excel för att visualisera KPI  
• Vad är nytt i diagram i Excel 2013
• Hur man skapar ett interaktivt "live" diagram i Excel