Hur man skapar ett vattenfallsschema i Excel

Denna handledning visar hur du skapar ett vattenfallsschema i alla versioner av Excel: 2007, 2010, 2013, 2016 och 2022.

Ett vattenfallsschema (även kallat ett bryggdiagram, flygande tegelstenar, kaskaddiagram eller Mario -diagram) är en graf som visuellt bryter ner den kumulativa effekten som en serie sekventiella positiva eller negativa värden har bidragit till det slutliga resultatet.

Konventionellt representerar de första och sista kolumnerna de initiala och slutliga värdena åtskilda av mellanliggande justeringar som visar hur många steg det tog för att komma från punkt A till punkt B.

Denna typ av diagram har använts i stor utsträckning av yrkesverksamma inom alla större industrier för att genomföra kvantitativa analyser, allt från avkodning av finansiella rapporter till övervakning av butiksinventarier.

Brografer introducerades som en inbyggd diagramtyp endast i Excel 2016, vilket innebär att användare av tidigare Excel-versioner inte har något annat val än att skapa diagrammet från grunden.

Av den anledningen lär du dig i denna handledning hur du skapar detta Mario -diagram både manuellt från grunden och genom att vända dig till den ursprungliga diagramtypen som gör allt smutsigt arbete för dig.

Komma igång

Rätt ut ur porten måste vi kasta in några rådata i bilden. Så, för illustration, låt oss anta att du bestämde dig för att analysera kvartalsvis nettokassaflöde för något fiktivt företag.

Med allt detta i åtanke, överväg följande data:

För att hjälpa dig att kunna anpassa diagrammet till dina egna behov, låt oss prata mer om bordet:

• Period: Denna kolumn representerar en sekvens av positiva och negativa värden som ska ritas i diagrammet.
• Nettokassaflöde: Denna kolumn består av motsvarande värden. Den första (B2) och sist (B13) celler innehåller initiala och slutliga värden medan de återstående talen däremellan står för de kvantitativa förändringarna över tid.

Nu när du vet det väsentliga i diagrammet, kan vi gå vidare till det nitty-gritty.

Hur man skapar ett vattenfallsschema i Excel 2016+

Låt mig först visa dig en lösning för dem som söker minsta motståndets väg.

Genom att använda den inbyggda, färdiga mallen, förutsatt att du använder Excel 2016 eller 2022, borde det inte ta dig mer än några enkla steg för att skapa ett vattenfallsschema.

Men allt kommer med priset på begränsade anpassningsalternativ och potentiella kompatibilitetsproblem; äldre versioner av Excel kan helt enkelt inte bearbeta nyare inbyggda diagramtyper.

Slutsats: när du ska skicka ett diagram är det alltid en säkrare satsning att bygga det manuellt.

Steg 1: Planera ett vattenfallsschema.

Till att börja med, skapa ett standard vattenfall diagram baserat på dina faktiska data. Det fina med denna metod är att du inte behöver hoppa igenom några hoops:

1. Markera all data (A1: B13).
2. Gå till Föra in flik.
3. Välj "Sätt in vattenfall, tratt, lager, yta eller radardiagram" knapp.
4. Välja "Vattenfall."

Omedelbart kommer ditt flygande tegeltabell att dyka upp:

Steg #2: Ange delsummor och totala kolumner.

Vårt nästa steg är att skilja de initiala och slutliga värdena från "flygande tegelstenar".

Dubbelklicka och högerklicka sedan på kolumnerna som representerar datapunkterna "Överföringsbalans”Och”Aktuellt saldo”Och välj”Ange som totalt.”

Tekniskt sett kanske du vill stanna där och lämna diagrammet som det är. Men den här kaskadgrafen berättar ingenting om data som plottas på den.

Varför nöja sig med mindre? Istället kan du förbättra ditt nyskapade Mario-diagram väsentligt genom att ändra några saker här och där.

Steg #3: Ändra färgschemat.

Ett effektivt sätt att förbättra ditt diagram är att låta färgschemat fungera för dig, inte tvärtom. Färga dataserien på ett sätt som hjälper till att förmedla vad diagrammet handlar om.

1. Dubbelklicka och högerklicka sedan på förklaringsobjektet som representerar Serien "Minska".
2. Välj "Fylla" knapp.
3. Plocka röd från färgpaletten.

På samma sätt, färga om Serien "Öka" till grönt och Serien "Totals" till blått. Väl där ska ditt kaskaddiagram se ut så här:

Steg #4: Skräddarsy de vertikala axelområdena till dina faktiska data.

För att zooma in på de flytande kolumnerna för mer information, ändra den vertikala axeln. Högerklicka på den primära vertikala axeln och klicka på "Format Axis.”

I Format Axis uppgiftsfönstret, följ dessa enkla steg:

1. Byt till Axialternativ flik.
2. Ställ in Minsta gränser till ”50,000.”
3. Ställ in Maximala gränser till ”110,000.”

Steg #5: Finjustera detaljerna.

Det är sunt förnuft att ju fler element som ingår i diagrammet, desto rörigare ser det ut. Så låt oss bli av med de onödiga elementen genom att ta bort legend och rutnät.

Högerklicka på diagramförklaringen och välj "Radera”Från menyn som dyker upp. Upprepa samma process för rutnätet.

Slutligen, ändra diagramtiteln, och du kan kalla det en dag!

Hur man skapar ett vattenfallsschema i Excel 2007, 2010 och 2013

Denna handledning skulle sluta här om metoden som visas ovan var kompatibel med alla versioner av Excel. Tyvärr är det inte så.

Av den anledningen kan du överväga att manuellt planera diagrammet från början, även om du använder Excel 2016 eller 2022.

Uppenbarligen tar det mer tid att sätta ihop grafen på det sättet, men utbetalningen är väl värd ansträngningen.

I slutet av detta avsnitt kommer du att lära dig hur du skapar detta anpassningsbara, dynamiska bryggdiagram som är kompatibelt med alla versioner av Excel:

Utan vidare, låt oss få bollen att rulla.

Steg 1: Förbered diagramdata.

Ärligt talat är detta steg det svåraste.

Enkelt uttryckt kommer vårt anpassade vattenfall diagram skapas baserat på ett staplat kolumn diagram. Men för att få bort det måste vi lägga till ytterligare hjälpdata i ekvationen.

För din bekvämlighet, ta en snabb titt på hur dina diagramdata ska organiseras i slutet av förberedelseskedet.

För dem som tar sina första babysteg på vägen till Excel -behärskning kan den här tabellen verka skrämmande, men i verkligheten kan vem som helst klara uppgiften genom att följa de detaljerade instruktionerna som beskrivs nedan.

Låt oss börja med att lägga till tre hjälparkolumner med namnet "Osynlig” (kolumn B), “Öka” (kolumn C) och "Minska” (kolumn D), som kommer att innehålla dummyvärdena för positionering av dina faktiska data på det framtida diagrammet.

Och om kolumner Öka och Minska är ganska självförklarande, vad är grejen med kolumn Osynlig? I grund och botten kommer dessa värden att utgöra den osynliga basen för det framtida staplade kolumndiagrammet för att ge de faktiska värdena upp till lämplig höjd.

Låt oss ta itu med kolumn Öka först: Ange följande formel i cellen C2 och dra ner det med fyllningshandtaget för att köra det för de återstående cellerna (C3: C13):

1	= OM (E2> 0, E2, 0)

Skriv nu in spegelformeln i den första cellen i kolumn Minska (kolumn D) och kopiera ner det också:

1	= OM (E2 <= 0, -E2, 0)

Två ner, en att gå. Efter att ha hittat nödvändiga diagramdata kan du nu konfigurera kolumn Osynlig för att noggrant kartlägga kassaflöde fluktuationer genom att skriva följande formel in i kolumnens andra cell (B3) och kopiera ner den till B12:

1	= B2+C2-D3

OBS: Det är absolut nödvändigt för dig att lämna de första och sista cellerna (B2 och B13) i kolumnen tom!

Glöm inte att formatera all diagramdata som valuta (Hem> Nummergrupp> Valuta).

Här ser du hur ditt utökade bord ska se ut:

Som en sista justering, konfigurera ännu en kolumn med namnet "Etiketter”Som kommer att innehålla snygga anpassade datatiketter.

När du har konfigurerat den här kolumnen anger du den här formeln i den andra cellen i kolumn Etiketter (F3) och kopiera ner den och lämna den första cellen (F2) och sista cellen (F13) tom:

1	= IF (E3 <= 0, TEXT (-E3, "$#, ##") & CHAR (10) & TEXT (-E3/SUM (B3: D3), "(#% ↓)"), TEXT (E3, "$#, ##") & CHAR (10) & TEXT (E3/SUM (B3: D3), "(#% ↑)"))

De som arbetar med andra nummerformat bör istället tillämpa denna formel:

1	= IF (E3 <= 0, TEXT (-E3, "#, ##") & CHAR (10) & TEXT (-E3/SUM (B3: D3), "(#% ↓)")) TEXT (E3, " #, ## ") & CHAR (10) & TEXT (E3/SUM (B3: D3)," (#% ↑) "))

Steg #2: Skapa ett staplat kolumndiagram.

Vi tog oss över puckeln, så det är äntligen dags att bygga vårt vattenfallsschema.

1. Markera all data i kolumner Period, osynlig, ökning, och Minska (A1: D13).
2. Gå till Föra in flik.
3. Klick "Infoga kolumn eller stapeldiagram.”
4. Välj "Staplad kolumn.”

Excel kommer att sammanställa denna enkla graf som så småningom kommer att omvandlas till ett fantastiskt vattenfallsschema:

Steg 3: Dölj serien ”Osynlig”.

Innan vi går vidare till resten av diagrammet, dölja de underliggande dataserierna som skjuter de flytande kolumnerna uppåt. Högerklicka på någon av de blå kolumnerna som representerar Serien "Osynlig" och öppna Formatera dataserier uppgiftsfönster.

Väl där, gör hjälpkolumnerna osynliga:

1. Gå till Fyll & rad fliken-the Fylla fliken i äldre versioner av Excel.
2. Under "Fylla," Välj "Ingen fyllning.”
3. Under "Gräns," klick "Ingen rad.”

Steg #4: Justera färgschemat.

I samma Formatera dataserier i aktivitetsfönstret, färga om varje dataserie genom att följa samma steg som visas ovan (Serialternativ > Fylla > Fast fyllning > Färg) så att det snabbt kan förstås intuitivt:

1. För Serien "Minska" plocka röd.
2. För Serien "Öka" plocka grön.
3. Dubbelklicka dessutom på kolumnerna “Överföringsbalans” och "Aktuellt saldo" och färga dem igen blå.

Här är vad du bör sluta ha vid denna tidpunkt:

Steg #5: Ändra gapbredden till "20%".

Nu, öka bredden på kolumnerna för bättre visuell uppfattning.

1. Öppna Formatera dataserier aktivitetsfönster (högerklicka på en av dataserierna och välj Formatera dataserier) och växla till Serialternativ flik.
2. Förändra "Gapbredd”Till”20%.”

Steg #6: Justera de vertikala axelområdena.

Återigen bör axelskalan ändras för att zooma in närmare på data. För att snabbt sammanfatta processen för dem som hoppade över det första avsnittet i den här självstudien, så här gör du:

1. Högerklicka på den vertikala axeln och välj "Format Axis.”
2. I aktivitetsfönstret öppnar du Axialternativ flik.
3. Under "Gräns," förändra "Minimum”Till”50,000”Och”Maximal”Till”110,000.”

Steg #7: Lägg till och placera de anpassade datatiketterna.

Dessa anpassade datatiketter har väntat för länge-så låt oss äntligen ta dem i bruk. Högerklicka på vilken kolumn som helst och välj "Lägg till datatiketter.”

Omedelbart kommer standarddatatiketterna som är knutna till hjälpvärdena att läggas till i diagrammet:

Men det är inte precis det vi letar efter. Undvik problemet genom att manuellt ersätta standardetiketterna med de anpassade värden som du förberett i förväg.

1. Dubbelklicka på datatiketten du vill ändra.
2. Stiga på "=" in i Formel bar.
3. Markera en motsvarande cell i kolumn Etiketter.

Ersätt de återstående datatiketterna genom att upprepa samma process för varje flytande kolumn.

Väl där måste du dra etiketterna till sina respektive positioner: alla positiva värden ska placeras ovanför respektive gröna kolumner medan de negativa måste flyttas under de röda kolumnerna.

När du har tagit tag i etiketterna bör ditt Mario -diagram förvandlas till något så här:

Steg #8: Rensa upp diagramområdet.

Slutligen, ta bort diagramförklaring och rutnät som inte ger något till bordet (Högerklicka > Radera). Ändra diagramtiteln, och där har du ditt vattenfallsschema!