Nzira yekugadzira waterfall chati

Nguva zhinji ini ndinosangana mukubika kwemakambani akasiyana uye ndinonzwa zvikumbiro kubva kune vanodzidzira kuti vatsanangure kuti dhizaini yekutsauka inovakwa sei - zvakare i "waterfall", iri zvakare "waterfall", iri zvakare "bhiriji." ", ihwowo "bhiriji", nezvimwewo. Zvinotaridzika seizvi:

Kubva kure, inonyatso kutaridzika kunge mapopoma emvura parwizi rwegomo kana zambuko rakarembera - ndiani anoona chii 🙂

Iyo peculiarity yemufananidzo wakadaro ndeyekuti:

• Isu tinoona zvakajeka kukosha kwekutanga uye kwekupedzisira kweparameter (yekutanga uye yekupedzisira makoramu).
• Positive shanduko (kukura) inoratidzwa mune rumwe ruvara (kazhinji girinhi), uye zvakaipa (kuderera) kune vamwe (kazhinji tsvuku).
  
• Dzimwe nguva chati yacho inogonawo kunge iine subtotal columns (gireyiyakamhara pa x-axis columns).
  

Muhupenyu hwezuva nezuva, mifananidzo yakadaro inowanzoshandiswa muzviitiko zvinotevera:

• zvekuona nemeso dynamics kuratidza chero maitiro munguva: kuyerera kwemari (kuyerera-kwemari), mari (isu tinodyara mupurojekiti uye tinowana purofiti kubva mairi).
• Kuonekwa kuronga kuita (koramu yekuruboshwe mudhayagiramu ichokwadi, koramu yekurudyi chirongwa, dhayagiramu rese rinoratidza maitiro edu ekufamba kuenda kune mhedzisiro yatinoda)
• Paunenge uchida zvinoonekwa ratidza zvinhuizvo zvinokanganisa yedu parameter (factorial ongororo yepurofiti - izvo zvinosanganisirwa nazvo).
  

Pane nzira dzinoverengeka dzekugadzira chati yakadaro - zvese zvinoenderana neshanduro yako yeMicrosoft Excel.

Method 1: Yakareruka: Yakavakwa-mukati mhando muExcel 2016 uye nyowani

Kana iwe uine Excel 2016, 2019 kana gare gare (kana Hofisi 365), saka kuvaka chati yakadaro hakuna kuoma - idzi vhezheni dzeExcel dzatove nerudzi urwu rwakavakwa mukati nekukasira. Zvichangove zvakakosha chete kusarudza tafura ine data uye sarudza pane iyo tab Insert (Isa) murayiro cascading (Mapopoma):

Nekuda kweizvozvo, isu tichawana inenge yakagadzirira-yakagadzirwa dhizaini:

Iwe unogona pakarepo kuseta yaunoda kuzadza mavara kune yakanaka uye yakaipa makoramu. Nzira iri nyore yekuita izvi ndeyekusarudza mitsara yakakodzera wedzera и kuderera zvakananga mungano uye nekudzvanya-kurudyi pavari, sarudza murairo Zadza (Zadza):

Kana iwe uchida kuwedzera makoramu ane subtotals kana yekupedzisira column-yakazara kune chati, saka zvakanyanya nyore kuita izvi uchishandisa mabasa. SUBTOTALS (ZVIRI MUKATI) or UNIT (ZVAKAWANDA). Vachaverenga huwandu hwakaunganidzwa kubva pakutanga kwetafura, asi vasingabatanidzi kubva mairi mazana akafanana ari pamusoro:

Panyaya iyi, gakava rekutanga (9) ndiyo kodhi yemasvomhu kuita oparesheni, uye yechipiri (0) inokonzeresa kuti basa risaregererwe hwerengedzo dzakaverengerwa dzezvikamu zvapfuura mune zvabuda.

Mushure mekuwedzera mitsara ine ma totals, inoramba ichisarudza makoramu ese akaonekwa padhiyagiramu (ita kudzvanya kaviri kwakateedzana pane imwe koramu) uye, nekudzvanya-kurudyi pane mbeva, sarudza rairo. Seta sezvakazara (Set se yakazara):

Iyo column yakasarudzwa inomhara pa x-axis uye otomatiki inoshandura ruvara kuita grey.

Izvo, muchokwadi, ndizvo zvese - iyo mapopoma dhizaini yakagadzirira:

Nzira 2. Universal: mbiru dzisingaoneki

Kana iwe uine Excel 2013 kana shanduro dzekare (2010, 2007, nezvimwewo), saka nzira yatsanangurwa pamusoro apa haishande kwauri. Iwe uchafanirwa kutenderera uye kucheka iyo isipo yemapopoma chati kubva kune yakajairwa akaturikidzana histogram (kupfupisa mabhawa pamusoro peumwe neumwe).

Manomano apa ndeekushandisa akajeka prop makoramu kusimudza yedu tsvuku uye yegirinhi data mitsara kusvika pahurefu chaihwo:

Kuvaka chati yakadaro, tinoda kuwedzera mamwe makoramu mashoma ane mafomula kune iyo dhata rekutanga:

• Chekutanga, isu tinofanirwa kupatsanura mbiru yedu yepakutanga nekuparadzanisa hunhu hwakanaka uye husina kunaka kuita makoramu akasiyana tichishandisa basa. IF (KANA).  
• Chechipiri, iwe unozofanirwa kuwedzera koramu pamberi pemakoramu pacifiers, uko kukosha kwekutanga kuchave 0, uye kutanga kubva pasero rechipiri, fomula ichaverengera kureba kweaya akajeka anotsigira makoramu.
  

Mushure mezvo, inoramba ichisarudza tafura yose kunze kwekoramu yepakutanga kuyerera uye gadzira yenguva dzose yakaturikidzana histogram mhiri Inset - Histogram (Isa — Column Chart):

Kana iwe zvino ukasarudza makoramu ebhuruu woita kuti asaonekwe (tinya-kurudyi paari - Row Format - Zadza - Hapana Zadza), ipapo tinongowana zvatinoda. 

Zvakanakira nzira iyi zviri nyore. Mune minuses - chido chekuverenga mbiru dzekubatsira.

Nzira 3. Kana tikapinda mutsvuku, zvinhu zvose zvakanyanya kuoma

Zvinosuruvarisa, nzira yapfuura inongoshanda zvakakwana kune zvakakosha zvakakosha. Kana zvishoma mune imwe nzvimbo mvura yedu inopinda munzvimbo isina kunaka, ipapo kuoma kwebasa kunowedzera zvakanyanya. Muchiitiko ichi, zvichave zvakakosha kuverenga mutsara wega wega (dummy, girini uye tsvuku) zvakasiyana kune zvakaipa uye zvakanaka zvikamu zvine mafomula:

Kuti urege kutambura zvakanyanya uye kuti urege kudzoreredza vhiri, yakagadzirira-yakagadzirwa template yenyaya yakadai inogona kutorwa mumusoro wechinyorwa ichi.

Nzira 4. Exotic: kumusoro-pasi mabhandi

Iyi nzira yakavakirwa pakushandiswa kwechinhu chidiki-chinozivikanwa che flat charts (histograms uye magirafu) - Kukwira-pasi mabhandi (Mabhawa Akakwira). Aya mabhendi anobatanidza mapoinzi emagirafu maviri ari maviri maviri kuratidza zvakajeka kuti ndeipi pamapoinzi maviri ari pamusoro kana pasi, ayo anoshandiswa zvakanyanya pakuona hurongwa-chokwadi:

Zviri nyore kufunga kuti kana tikabvisa mitsetse yemachati uye tichisiya chete kumusoro-pasi mabhandi pane chati, ipapo tichawana "waterfall" yakafanana.

Pakuvaka kwakadai, isu tinofanirwa kuwedzera mamwe makoramu maviri ekuwedzera patafura yedu nemafomu akareruka ayo anozoverenga nzvimbo yeaya maviri anodiwa asingaonekwe magirafu:

Kuti ugadzire "mapopoma", unofanirwa kusarudza koramu ine mwedzi (yekusayina pamwe neiyo X axis) uye maviri ekuwedzera makoramu. Purogiramu 1 и Purogiramu 2 uye kuvaka girafu yenguva dzose yekutanga kushandisa Pinza – Girafu (Isa — Mutsara Сhart):

Zvino ngatiwedzerei kumusoro-pasi mabhendi kune yedu chati:

• MuExcel 2013 uye nyowani, izvi zvinofanirwa kusarudzwa pane tebhu Constructor murayiro Wedzera Chart Element - Mabhendi ekuwedzera-kuderera (Dzaini - Wedzera Chart Element - Up-Down Bars)
• MuExcel 2007-2010 - enda kune tab Kurongeka – Advance-Decrement Bars (Kurongeka - Up-Down Bars)

Iyo chati inozotaridzika seizvi:

Izvo zvinosara kusarudza magirafu uye kuita kuti ave pachena nekudzvanya paari nebhatani rekurudyi uye nekusarudza rairo. Data series format (Format series). Saizvozvo, iwe unogona kushandura chiyero, pane shabby kutarisa dema uye chena mitsetse mavara kune yegirini uye tsvuku kuti uwane yakanaka mufananidzo pakupedzisira:

Mushanduro dzichangoburwa dzeMicrosoft Excel, hupamhi hwemabhawa hunogona kuchinjwa nekudzvanya pane imwe yemagirafu akajeka (kwete mabhawa!) nebhatani rekurudyi rembeva uye kusarudza rairo. Data Series Format - Side Clearance (Format series - Gap width).

Mune shanduro dzekare dzeExcel, waifanira kushandisa Visual Basic command kugadzirisa izvi:

1. Simbisa mufananidzo wakagadzirwa
2. Dzvanya nzira yekudimbudzira kiibhodhi alt+F11kuti upinde muVisual Basic Editor
3. Dzvanyiridza tsananguro ye keyboard Ctrl+Gkuvhura iyo yakananga yekuraira yekupinda uye debug pani Pakarepo (kazhinji inowanikwa pazasi).

4. Kopa uye unamate murairo unotevera ipapo: ActiveChart.ChartGroups(1).GapWidth = 30 uye tigoshingairira pinda:

Iwe unogona, hongu, kutamba uchitenderedza neiyo parameter kukosha kana uchida. GapWidthkuti uwane mvumo yaunoda:

• Maitiro ekugadzira bullet chati muExcel kuona KPI  
• Chii Chitsva mumaChati muExcel 2013
• Maitiro ekugadzira inodyidzana "kurarama" chati muExcel