Yadda ake gina ginshiƙi na ruwa

Sau da yawa ina haɗuwa a cikin rahoton kamfanoni daban-daban kuma ina jin buƙatun daga waɗanda aka horar da su don yin bayanin yadda aka gina zane-zane na ɓarna - shi ma "waterfall", shi ma "waterfall", shi ma "gada ce". ”, shi ma “gada”, da sauransu. Yana kama da wani abu kamar haka:

Daga nesa, da gaske yana kama da raƙuman ruwa a kan kogin dutse ko gada mai rataye - wanda ya ga abin da 🙂

Bambancin irin wannan zane shine:

• A fili muna ganin ƙimar farko da ta ƙarshe na siga (ginshiƙan farko da na ƙarshe).
• Ana nuna canje-canje masu kyau (girma) a launi ɗaya (yawanci kore), da kuma marasa kyau (ƙi) ga wasu (yawanci ja).
  
• Wani lokaci ginshiƙi na iya ƙunsar ginshiƙan ginshiƙai (mya sauka a kan ginshiƙan x-axis).
  

A cikin rayuwar yau da kullum, ana amfani da irin waɗannan zane-zane a lokuta masu zuwa:

• Kayayyakin nunin kuzari kowane tsari a cikin lokaci: tsabar kudi (tsabar kudi), zuba jari (muna zuba jari a cikin aikin kuma muna samun riba daga gare ta).
• na gani aiwatar da shirin (shafi na hagu a cikin zane gaskiya ne, ginshiƙi na dama shine tsari, duka zane yana nuna tsarin mu na motsawa zuwa sakamakon da ake so)
• Lokacin da kuke buƙatar gani nuna dalilaiwanda ke shafar ma'aunin mu (binciken factorial na riba - abin da ya ƙunshi).
  

Akwai hanyoyi da yawa don gina irin wannan ginshiƙi - duk ya dogara da sigar ku ta Microsoft Excel.

Hanyar 1: Mafi Sauƙi: Nau'in da aka gina a cikin Excel 2016 da sabo

Idan kuna da Excel 2016, 2019 ko kuma daga baya (ko Office 365), to gina irin wannan ginshiƙi ba shi da wahala - waɗannan nau'ikan Excel sun riga sun gina irin wannan ta tsohuwa. Zai zama dole kawai don zaɓar tebur tare da bayanai kuma zaɓi akan shafin Saka (Saka) umurnin cascading (Waterfall):

A sakamakon haka, za mu sami kusan shirye-shiryen zane:

Kuna iya nan da nan saita launuka masu cika da ake so don ginshiƙai masu kyau da mara kyau. Hanya mafi sauƙi don yin wannan ita ce zaɓar layuka masu dacewa ƙara и ragewa kai tsaye a cikin labari kuma ta danna-dama akan su, zaɓi umarnin Cika (Cika):

Idan kana buƙatar ƙara ginshiƙai tare da ƙananan bayanai ko jimlar shafi na ƙarshe zuwa ginshiƙi, to ya fi dacewa don yin wannan ta amfani da ayyuka. SUBTOTALS (SUBTALS) or UNIT (GIRGA). Za su lissafta adadin da aka tara daga farkon tebur, yayin da ban da shi daga cikin jimlar makamancin haka da ke sama:

A wannan yanayin, hujja ta farko (9) ita ce lambar aikin taƙaitaccen lissafin lissafi, kuma na biyu (0) yana sa aikin yin watsi da jimlar da aka riga aka ƙididdige don ɓata na baya a cikin sakamakon.

Bayan ƙara layuka tare da jimla, ya rage don zaɓar jimillar ginshiƙan da suka bayyana akan zane (yin dannawa guda biyu a jere a kan shafi) kuma, ta danna dama akan linzamin kwamfuta, zaɓi umarnin. Saita azaman jimla (A saita a matsayin jimla):

Shagon da aka zaɓa zai sauka akan axis x kuma ta atomatik canza launi zuwa launin toka.

Wannan, a gaskiya, shi ne duka - zane-zane na ruwa yana shirye:

Hanyar 2. Universal: ginshiƙan ganuwa

Idan kuna da nau'ikan Excel 2013 ko tsofaffi (2010, 2007, da sauransu), to hanyar da aka bayyana a sama ba zata yi muku aiki ba. Dole ne ku zagaya ku yanke ginshiƙi na ruwa da ya ɓace daga lissafin lissafi na yau da kullun (a tattara sanduna a saman juna).

Dabarar a nan ita ce a yi amfani da ginshiƙai masu fa'ida don ɗaga layukanmu ja da kore zuwa daidai tsayi:

Don gina irin wannan ginshiƙi, muna buƙatar ƙara wasu ƙarin ginshiƙai masu taimako tare da dabaru zuwa bayanan tushen:

• Da farko, muna buƙatar raba ginshiƙin mu na asali ta hanyar raba kyawawan dabi'u da mara kyau zuwa ginshiƙai daban-daban ta amfani da aikin. IF (IF).  
• Abu na biyu, kuna buƙatar ƙara ginshiƙi a gaban ginshiƙan pacifiers, inda ƙimar farko zata kasance 0, kuma farawa daga tantanin halitta na biyu, dabarar za ta ƙididdige tsayin waɗannan ginshiƙan masu goyan baya.
  

Bayan haka, ya rage don zaɓar teburin duka ban da ginshiƙi na asali Flow da ƙirƙirar histogram na yau da kullun a duk faɗin Inset - Histogram (Saka - Chart Shafi):

Idan yanzu kun zaɓi ginshiƙan shuɗi kuma ku sa su zama marasa ganuwa (danna dama akan su - Tsarin layi - Cika - Babu Cika), sannan mu sami abin da muke bukata kawai. 

Amfanin wannan hanya shine sauƙi. A cikin minuses - buƙatar ƙidaya ginshiƙan taimako.

Hanyar 3. Idan muka shiga cikin ja, duk abin ya fi wuya

Abin takaici, hanyar da ta gabata tana aiki daidai da ƙima mai kyau kawai. Idan aƙalla a wasu wuraren ruwan mu ya shiga wani yanki mara kyau, to, wahalar aikin yana ƙaruwa sosai. A wannan yanayin, zai zama dole don lissafta kowane jere (dummy, kore da ja) daban don sassa mara kyau da tabbatacce tare da dabaru:

Don kada ku sha wahala da yawa kuma kada ku sake haɓaka dabaran, ana iya sauke samfurin da aka shirya don irin wannan yanayin a cikin taken wannan labarin.

Hanyar 4. m: up-saukar makada

Wannan hanya ta dogara ne akan amfani da wani abu na musamman wanda ba a san shi ba na ginshiƙai (histograms da jadawalai) - Makada na sama (Sauran Bars). Waɗannan makada suna haɗa maki na jadawalai biyu bibiyu don nuna a fili wanne ne daga cikin maki biyun ya fi girma ko ƙasa, wanda ake amfani da shi sosai yayin hango ainihin shirin:

Yana da sauƙi a gane cewa idan muka cire layin ginshiƙai kuma muka bar kawai ƙungiyoyin sama a kan ginshiƙi, to, za mu sami "waterfall" iri ɗaya.

Don irin wannan ginin, muna buƙatar ƙara ƙarin ginshiƙai biyu zuwa teburinmu tare da dabaru masu sauƙi waɗanda za su lissafta matsayin jadawali biyu da ake buƙata waɗanda ba a iya gani:

Don ƙirƙirar "waterfall", kuna buƙatar zaɓar ginshiƙi tare da watanni (don sa hannu tare da axis X) da ƙarin ginshiƙai biyu. Tsarin 1 и Tsarin 2 kuma gina jadawali na yau da kullun don masu farawa amfani Saka - Graph (Saka - Layin Shart):

Yanzu bari mu ƙara sama-saukar makada zuwa ginshiƙi:

• A cikin Excel 2013 da sababbi, dole ne a zaɓi wannan akan shafin Constructor umurnin Ƙara Ƙimar Chart - Makada na karuwa-rage (Mai ƙira - Ƙara Ma'anar Chart - Bars na sama)
• A cikin Excel 2007-2010 - je zuwa shafin Layout - Ci gaba-Rage Bars (Layout - Bars na sama)

Jadawalin zai yi kama da wani abu kamar haka:

Ya rage don zaɓar jadawalin kuma sanya su a bayyane ta danna su bi da bi tare da maɓallin linzamin kwamfuta na dama kuma zaɓi umarnin. Tsarin bayanai (Tsarin Tsarin). Hakazalika, zaku iya canza ma'auni, a maimakon haka, launuka masu launin baki da fari masu ban sha'awa zuwa kore da ja don samun kyakkyawan hoto a ƙarshe:

A cikin sabbin nau'ikan Microsoft Excel, ana iya canza faɗin sanduna ta danna ɗaya daga cikin jadawali (ba sanduna ba!) Tare da maɓallin linzamin kwamfuta na dama kuma zaɓi umarnin. Tsarin Bayanai - Tsare Gefe (Tsarin Tsarin - Faɗin Tazari).

A cikin tsofaffin nau'ikan Excel, dole ne ku yi amfani da umarnin Visual Basic don gyara wannan:

1. Hana zanen da aka gina
2. Danna gajeriyar hanyar madannai alt+F11don shiga Editan Kayayyakin Kayayyakin Kaya
3. Danna maɓallin gajeren hanya Ctrl+Gdon buɗe shigar da umarni kai tsaye da panel debug nan da nan (yawanci yana a kasa).

4. Kwafi da liƙa wannan umarni a can: ActiveChart.ChartGroups(1).GapWidth = 30 kuma latsa Shigar:

Kuna iya, ba shakka, yin wasa tare da ƙimar siga idan kuna so. GapWidthdon cimma burin da ake so:

• Yadda ake gina taswirar harsashi a cikin Excel don ganin KPI  
• Menene sabo a cikin Charts a cikin Excel 2013
• Yadda ake ƙirƙirar ginshiƙi "rayuwa" mai hulɗa a cikin Excel