Kuinka luoda vesiputouskaavio Excelissä

Tämä opetusohjelma osoittaa, kuinka luoda vesiputouskaavio kaikissa Excel -versioissa: 2007, 2010, 2013, 2016 ja 2022.

Vesiputouskaavio (jota kutsutaan myös siltakarttaksi, lentäväksi tiilikarttaksi, kaskadikarttaksi tai Mario -kaavioksi) on kaavio, joka hajottaa visuaalisesti kumulatiivisen vaikutuksen, jonka peräkkäiset positiiviset tai negatiiviset arvot ovat vaikuttaneet lopputulokseen.

Perinteisesti ensimmäinen ja viimeinen sarake edustavat alkuperäisiä ja lopullisia arvoja, jotka on erotettu välimuokkauksilla, jotka kuvaavat pisteestä A pisteeseen B.

Tämäntyyppistä kaaviota ovat laajalti käyttäneet ammattilaiset kaikilla suurilla toimialoilla kvantitatiivisen analyysin suorittamiseen aina tilinpäätöksen purkamisesta kaupan varaston seurantaan.

Siltakaaviot esiteltiin sisäänrakennetun kaaviotyypinä vain Excel 2016: ssa, mikä tarkoittaa, että aiempien Excel-versioiden käyttäjillä ei ole muuta vaihtoehtoa kuin luoda kaavio alusta alkaen.

Tästä syystä tässä opetusohjelmassa opit luomaan tämän Mario -kaavion sekä manuaalisesti alusta alkaen että siirtymällä alkuperäiseen kaaviotyyppiin, joka tekee kaiken likaisen työn puolestasi.

Päästä alkuun

Heti portin ulkopuolelta meidän on heitettävä raakadataa kuvaan. Oletetaan esimerkiksi havainnollistamiseksi, että ryhdyt analysoimaan jonkin fiktiivisen yrityksen neljännesvuosittaista nettorahavirtaa.

Ota tämä huomioon ottaen seuraavat tiedot:

Jotta voisit mukauttaa kaavion omiin tarpeisiisi, puhumme taulukosta yksityiskohtaisemmin:

• Kausi: Tämä sarake edustaa kaavioon piirrettävien positiivisten ja negatiivisten arvojen sarjaa.
• Nettorahavirta: Tämä sarake koostuu vastaavista arvoista. Ensimmäinen (B2) ja viimeiseksi (B13) solut sisältävät alku- ja lopulliset arvot, kun taas jäljellä olevat numerot tarkoittavat määrällisiä muutoksia ajan mittaan.

Nyt kun tiedät kaavion olennaiset asiat, voimme siirtyä karkeaseen.

Vesiputouskaavion luominen Excel 2016+: ssa

Ensin haluan näyttää teille ratkaisun niille, jotka etsivät vähimmän vastarinnan polkua.

Käyttämällä sisäänrakennettua, valmiita malleja, jos käytät Excel 2016: ta tai 2022: tä, vesiputouskaavion määrittäminen ei vie enempää kuin muutama yksinkertainen vaihe.

Kaiken tähän liittyy kuitenkin rajallisten mukautusvaihtoehtojen hinta ja mahdolliset yhteensopivuusongelmat; vanhemmat Excel -versiot eivät yksinkertaisesti pysty käsittelemään uudempia alkuperäisiä kaaviotyyppejä.

Bottom line: Kun lähetät kaavion, on aina turvallisempaa tehdä se manuaalisesti.

Vaihe 1: Piirrä vesiputouskaavio.

Luo ensin vesiputouskaavio todellisten tietojen perusteella. Tämän menetelmän kauneus on, että sinun ei tarvitse hypätä minkään kehän läpi:

1. Korosta kaikki tiedot (A1: B13).
2. Siirry kohtaan Lisää välilehti.
3. Valitse "Lisää vesiputous, suppilo, kanta, pinta tai tutkakaavio”-Painiketta.
4. Valita "Vesiputous."

Heti lentävien tiilikarttasi tulee esiin:

Vaihe 2: Aseta välisumma- ja kokonaissarakkeet.

Seuraava askel on erottaa alkuperäiset ja lopulliset arvot "lentävistä palikoista".

Kaksoisnapsauta ja napsauta sitten hiiren kakkospainikkeella datapisteitä edustavia sarakkeita "Siirtosaldo"Ja"Nykyinen tasapaino"Ja valitse"Aseta kokonaiseksi.”

Teknisesti kannattaa ehkä pysähtyä ja jättää kaavio sellaisenaan. Mutta tämä kaskadikaavio ei kerro meille mitään siitä piirretyistä tiedoista.

Miksi tyytyä vähempään? Sen sijaan voit parantaa huomattavasti uutta Mario-kaaviotasi muuttamalla muutamia asioita siellä täällä.

Vaihe 3: Muuta värimaailmaa.

Yksi tehokas tapa parantaa kaaviota on antaa värimaailman toimia puolestasi, ei päinvastoin. Väritä datasarjat uudelleen tavalla, joka auttaa välittämään, mistä kaaviossa on kyse.

1. Kaksoisnapsauta ja napsauta sitten hiiren kakkospainikkeella kuvaketta Sarja "Vähennys".
2. Valitse "Täyttää”-Painiketta.
3. Valita punainen värivalikoimasta.

Väritä samalla tavalla Sarja "Lisää" vihreiksi ja Sarja "Yhteensä" siniseksi. Siellä kaskadikaavion pitäisi näyttää tältä:

Vaihe 4: Räätälöi pystyakselialueet todellisten tietojen perusteella.

Jos haluat zoomata kelluviin sarakkeisiin tarkemmin, muuta pystysuoran akselin asteikkoa. Napsauta hiiren kakkospainikkeella ensisijaista pystysuoraa akselia ja napsauta "Muotoile akseli.”

Kohteessa Muotoile akseli tehtäväruudussa, toimi seuraavasti:

1. Vaihda kohtaan Akselivaihtoehdot välilehti.
2. Aseta Vähimmäisrajat kohteeseen "50,000.”
3. Aseta Enimmäisrajat kohteeseen "110,000.”

Vaihe 5: Hienosäädä yksityiskohtia.

On järkevää, että mitä enemmän kaavion elementtejä, sitä sotkuisempi se näyttää. Päästämme siis eroon tarpeettomista elementeistä poistamalla selite ja ruudukko.

Napsauta kaavion selitettä hiiren kakkospainikkeella ja valitse "Poistaa”Avautuvasta valikosta. Toista sama prosessi ruudukkoille.

Muuta lopuksi kaavion otsikkoa, ja voit kutsua sitä päiväksi!

Vesiputouskaavion luominen Excelissä 2007, 2010 ja 2013

Tämä opetusohjelma päättyy tähän, jos yllä esitetty menetelmä on yhteensopiva kaikkien Excel -versioiden kanssa. Valitettavasti näin ei ole.

Tästä syystä sinun kannattaa ehkä piirtää kaavio manuaalisesti tyhjästä, vaikka käytät Excel 2016: ta tai 2022: a.

On selvää, että kaavion kokoaminen kestää kauemmin, mutta voitto on vaivan arvoinen.

Tämän osion loppuun mennessä opit luomaan tämän mukautettavan, dynaamisen siltakaavion, joka on yhteensopiva kaikkien Excel -versioiden kanssa:

Pysäytetään pallo ilman lisäselvityksiä.

Vaihe 1: Valmistele kaaviotiedot.

Rehellisesti sanottuna tämä vaihe on vaikein.

Yksinkertaisesti sanottuna mukautettu vesiputouskaavio luodaan pinotun pylväskaavion perusteella. Mutta vetääksemme sen pois meidän on lisättävä yhtälöön lisää aputietoja.

Kätevyytesi vuoksi vilkaise nopeasti, miten kaaviotietosi on järjestettävä valmisteluvaiheen loppuun mennessä.

Niille, jotka ottavat ensimmäiset vauvan askeleensa kohti Excelin hallintaa, tämä taulukko voi tuntua suorastaan ​​pelottavalta, mutta todellisuudessa kuka tahansa voi hoitaa tehtävän noudattamalla alla esitettyjä yksityiskohtaisia ​​ohjeita.

Aloitetaan lisäämällä kolme auttajasaraketta nimeltä "Näkymätön” (sarake B), “Lisääntyä” (sarake C), ja "Vähennä” (sarake D), jotka sisältävät tyhjät arvot todellisten tietojen sijoittamiseksi tulevaan kaavioon.

Ja jos sarakkeet Lisää ja Vähennä ovat melko itsestään selviä, mistä on kysymys sarake Näkymätön? Pohjimmiltaan nämä arvot muodostavat näkymättömän pohjan tulevalle pinotulle pylväskaavioon todellisten arvojen tukemiseksi sopivalle korkeudelle.

Käsittelemme sarake Lisää ensimmäinen: Kirjoita seuraava kaava soluun C2 ja vedä se alas käyttämällä täyttökahvaa suorittaaksesi sen jäljellä oleville soluille (C3: C13):

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

Kirjoita nyt peilikaava verkkotunnuksen ensimmäiseen soluun sarake Vähennä (sarake D) ja kopioi se myös:

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

Kaksi alas, yksi matkaan. Kun olet löytänyt tarvittavat kaaviotiedot, voit nyt määrittää sarake Näkymätön Voit kuvata kassavirran vaihtelut tarkasti kirjoittamalla seuraavan kaavan kolonnin toiseen soluun (B3) ja kopioida se alas B12:

1	= B2+C2-D3

HUOMAUTUS: Sinun on ehdottomasti poistuttava ensimmäisestä ja viimeisestä solusta (B2 ja B13) sarakkeesta tyhjä!

Muista muotoilla kaikki kaavion tiedot valuutaksi (Etusivu> Numeroryhmä> Valuutta).

Tässä vaiheessa laajennetun taulukon pitäisi näyttää tältä:

Viimeisenä säädönä määritä vielä yksi sarake nimeltä "Tunnisteet”, Joka sisältää hienoja mukautettuja datatarroja.

Kun olet määrittänyt tämän sarakkeen, kirjoita tämä kaava sivun toiseen soluun sarake Tunnisteet (F3) ja kopioi se, jättäen ensimmäisen solun (F2) ja viimeinen solu (F13) tyhjä:

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

Muiden numeromuotojen kanssa työskentelevien tulisi käyttää tätä kaavaa:

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

Vaihe 2: Luo pinottu sarakekaavio.

Pääsimme kuopan yli, joten vesiputouskaavion rakentamisen aika on vihdoin koittanut.

1. Valitse kaikki tiedot sarakkeista Aika, näkymätön, lisäys, ja Vähennä (A1: D13).
2. Siirry kohtaan Lisää välilehti.
3. Klikkaus "Lisää sarake tai pylväskaavio.”
4. Valitse "Pinottu sarake.”

Excel kokoaa tämän yksinkertaisen kaavion, joka lopulta muutetaan upeaksi vesiputouskaavioksi:

Vaihe 3: Piilota näkymättömät sarjat.

Ennen kuin siirrymme kaavion muuhun osaan, piilota taustalla olevat tietosarjat työntämällä kelluvat sarakkeet ylös. Napsauta hiiren kakkospainikkeella mitä tahansa sinistä saraketta Sarja "Näkymätön" ja avaa Muoto datasarja tehtäväruutu.

Tee auttajapylväät näkymättömiksi:

1. Siirry kohtaan Fill & Line välilehti-the Täyttää välilehti vanhemmissa Excel -versioissa.
2. Alla "Täyttää,"Valitse"Ei täyttöä.”
3. Alla "Raja," klikkaus "Ei linjaa.”

Vaihe 4: Säädä värimaailmaa.

Samassa Muoto datasarja väritä jokainen tietosarja noudattamalla yllä esitettyjä vaiheita (Sarja -asetukset > Täyttää > Kiinteä täyttö > Väri), jotta se voidaan ymmärtää intuitiivisesti yhdellä silmäyksellä:

1. Varten Sarja "Vähennys" valita punainen.
2. Varten Sarja "Lisäys" valita vihreä.
3. Lisäksi kaksoisnapsauta sarakkeita “Siirtosaldo” ja "Nykyinen tasapaino" ja värjätä ne uudelleen sininen.

Tässä sinun pitäisi saada tässä vaiheessa:

Vaihe 5: Muuta raon leveydeksi "20%".

Lisää nyt sarakkeiden leveyttä paremman visuaalisen havainnon vuoksi.

1. Avaa Muoto datasarja tehtäväruutu (napsauta hiiren kakkospainikkeella jotakin tietosarjaa ja valitse Muoto datasarja) ja vaihda kohtaan Sarja -asetukset välilehti.
2. Muuta "Aukon leveys"Kohtaan"20%.”

Vaihe 6: Säädä pystyakselialueita.

Jälleen akseliasteikkoa on muutettava lähentämään tietoja tarkemmin. Voit kerrata prosessin nopeasti niille, jotka ohittivat tämän opetusohjelman ensimmäisen osan, seuraavasti:

1. Napsauta pystyakselia hiiren kakkospainikkeella ja valitse "Muotoile akseli.”
2. Avaa tehtäväruudussa Akselivaihtoehdot välilehti.
3. Alla "Rajat, "Muutos"Vähintään"Kohtaan"50,000"Ja"Enimmäismäärä"Kohtaan"110,000.”

Vaihe 7: Lisää ja aseta mukautetut datatarrat.

Nämä mukautetut datatarrat ovat odottaneet liian kauan, joten otamme ne vihdoin käyttöön. Napsauta mitä tahansa saraketta hiiren kakkospainikkeella ja valitse "Lisää datatarrat.”

Heti auttaja -arvoihin sidotut oletusdatatarrat lisätään kaavioon:

Mutta se ei ole juuri sitä, mitä etsimme. Voit kiertää ongelman korvaamalla oletustarrat manuaalisesti etukäteen valmistetuilla mukautetuilla arvoilla.

1. Kaksoisnapsauta datatarraa, jota haluat muuttaa.
2. Tulla sisään "=”Osaksi Kaavapalkki.
3. Korosta vastaava solu sarake Tunnisteet.

Korvaa jäljellä olevat datatarrat toistamalla sama prosessi jokaiselle kelluvalle sarakkeelle.

Kun olet siellä, sinun on vedettävä tarrat vastaaviin paikkoihinsa: kaikki positiiviset arvot on sijoitettava vastaavien vihreiden sarakkeiden yläpuolelle, kun taas negatiiviset on siirrettävä punaisten sarakkeiden alle.

Kun olet ratkaissut tarrat onnistuneesti, Mario -kaaviosi pitäisi muuttua seuraavanlaiseksi:

Vaihe 8: Puhdista kartta -alue.

Poista lopuksi kaavion selite ja ruudukko, jotka eivät tuo mitään pöydälle (Oikealla painikkeella > Poistaa). Vaihda kaavion otsikko, ja siellä on vesiputouskaaviosi!