Contents
ʻOi aku ka nui o koʻu hālāwai ʻana ma ka hōʻike ʻana o nā hui like ʻole a lohe i nā noi mai nā mea aʻo e wehewehe i ke ʻano o ke kūkulu ʻia ʻana o kahi diagram cascade of deviations - ʻo ia hoʻi he "wailele", he "wailele", ʻo ia hoʻi he "alahaka. ", he "alahaka" kekahi, etc. Ua like ia me keia:
Mai kahi mamao aku, ua like ia me ka wailele ma ke kahawai mauna a i ʻole ke alahaka e kau ana - ʻo wai ka mea ʻike i ka mea 🙂
ʻO ke ʻano o ia kiʻi ʻo ia:
- ʻIke maopopo mākou i ka waiwai mua a me ka hope o ka palena (nā kolamu mua a hope).
- Hōʻike ʻia nā loli maikaʻi (ulu) i hoʻokahi kala (maʻamau ʻōmaʻomaʻo), a me nā mea ʻino (hōʻole) i nā poʻe ʻē aʻe (maʻamau ʻulaʻula).
- I kekahi manawa, loaʻa i ka pakuhi nā kolamu subtotal (hinahinapae ma nā kolamu x-axis).
I ke ola o kēlā me kēia lā, hoʻohana pinepine ʻia nā kiʻi i kēia mau hihia:
- Ke kākoʻo hōʻike dinamika kekahi kaʻina hana i ka manawa: ke kahe kālā (cash-flow), nā hoʻopukapuka (ke kālele mākou i kahi papahana a loaʻa ka waiwai mai ia mea).
- Hōʻike manaʻo hoʻokō hoʻolālā (He ʻoiaʻiʻo ka kolamu hema loa, ʻo ke kolamu ʻākau he hoʻolālā, hōʻike ke kiʻikuhi holoʻokoʻa i kā mākou neʻe ʻana i ka hopena i makemake ʻia)
- Ke makemake ʻoe i ka ʻike hōʻike i nā kumue pili ana i kā mākou pākuʻi (ka helu kumu o ka waiwai - he aha ia).
Nui nā ala e kūkulu ai i kahi pakuhi - pili ia i kāu mana o Microsoft Excel.
Kaʻina 1: Maʻalahi loa: ʻAno i kūkulu ʻia ma Excel 2016 a ʻoi aku ka hou
Inā loaʻa iā ʻoe ka Excel 2016, 2019 a i ʻole ma hope (a i ʻole Office 365), a laila ʻaʻole paʻakikī ke kūkulu ʻana i ia pakuhi - ua loaʻa i kēia mau mana o Excel kēia ʻano i kūkulu ʻia ma ka paʻamau. E koho wale i kahi papa me ka ʻikepili a koho i ka pā hookomo (Hookomo) E kauoha aku cascading (Walele):
ʻO ka hopena, e loaʻa iā mākou kahi kiʻi i hoʻomākaukau ʻia:
Hiki iā ʻoe ke hoʻonohonoho koke i nā kala hoʻopiha i makemake ʻia no nā kolamu maikaʻi a maikaʻi ʻole. ʻO ke ala maʻalahi e hana i kēia, ʻo ia ke koho i nā lālani kūpono mahuahua и emi iho pololei i ka moʻolelo a ma ke kaomi ʻākau iā lākou, koho i ke kauoha E hoʻopiha (Hoʻopiha):
Inā pono ʻoe e hoʻohui i nā kolamu me nā subtotal a i ʻole kahi kolamu hope loa i ka pakuhi, a laila ʻoi aku ka maʻalahi o ka hana ʻana i kēia me nā hana. NUI (NĀ KĀNĀWAI) or UNIT (HUI). E helu lākou i ka nui i hōʻiliʻili ʻia mai ka hoʻomaka ʻana o ka papaʻaina, ʻoiai e hoʻokaʻawale ʻia nā huina like ma luna nei:
I kēia hihia, ʻo ka hoʻopaʻapaʻa mua (9) ʻo ia ke code o ka hana hōʻuluʻulu makemakika, a ʻo ka lua (0) ke kumu e nānā ʻole ai ka hana i nā huina i helu mua ʻia no nā hapaha mua o nā hopena.
Ma hope o ka hoʻohui ʻana i nā lālani me nā huina, e koho i ka nui o nā kolamu i ʻike ʻia ma ke kiʻikuhi (e hana i ʻelua kaomi hoʻokahi ma ke kolamu) a, ma ke kaomi ʻana i ka ʻiole, koho i ke kauoha. Hoʻonoho i ka huina (Hoʻonoho i ka huina):
E pae ka kolamu i koho ʻia ma ka axis-x a hoʻololi ʻokoʻa i ke kala i hina.
ʻO ia wale nō - ua mākaukau ke kiʻi wailele:
Hana 2. Universal: nā kolamu ʻike ʻole ʻia
Inā loaʻa iā ʻoe ka Excel 2013 a i ʻole nā mana kahiko (2010, 2007, a me nā mea ʻē aʻe), a laila ʻaʻole pono ke ʻano i wehewehe ʻia ma luna nei iā ʻoe. Pono ʻoe e hele a ʻoki i ka pakuhi wailele mai kahi histogram i hoʻopaʻa mau ʻia (e hōʻuluʻulu i nā kaola ma luna o kekahi).
ʻO ka hana maʻaneʻi, ʻo ia ka hoʻohana ʻana i nā kolamu prop akaka e hoʻokiʻekiʻe i kā mākou mau lālani ʻikepili ʻulaʻula a ʻōmaʻomaʻo i ke kiʻekiʻe kūpono:
No ke kūkulu ʻana i ia pakuhi, pono mākou e hoʻohui i kekahi mau kolamu kōkua hou me nā formula i ka ʻikepili kumu:
- ʻO ka mea mua, pono mākou e hoʻokaʻawale i kā mākou kolamu kumu ma ka hoʻokaʻawale ʻana i nā waiwai maikaʻi a maikaʻi ʻole i nā kolamu ʻokoʻa me ka hoʻohana ʻana i ka hana. IF (I).
- ʻO ka lua, pono ʻoe e hoʻohui i kahi kolamu ma mua o nā kolamu pacifiers, kahi e 0 ai ka waiwai mua, a e hoʻomaka ana mai ka lua o ka cell, e helu ke kumu i ke kiʻekiʻe o kēlā mau kolamu kākoʻo akaka loa.
Ma hope o kēlā, e koho i ka papaʻaina holoʻokoʻa koe wale nō ke kolamu kumu pele a hana i kahi histogram hoʻopaʻa maʻamau a puni Hoʻokomo - Histogram (Hoʻokomo - Papa Kiʻi):
Inā koho ʻoe i nā kolamu polū a ʻike ʻole ʻia (e kaomi ʻākau iā lākou - Hōʻano Lālani – Hoʻopiha – ʻAʻohe Hoʻopiha), a laila loaʻa iā mākou ka mea e pono ai.
ʻO ka maikaʻi o kēia ala he maʻalahi. I nā minuses - pono e helu i nā kolamu kōkua.
Keʻano 3. Inā mākou e hele i kaʻulaʻula,ʻoi aku ka paʻakikī o nā mea a pau
ʻO ka mea pōʻino, hana maikaʻi wale ke ala mua no nā waiwai maikaʻi. Inā ma kahi liʻiliʻi ma kekahi wahi e hele ko mākou wailele i kahi ʻāpana maikaʻi ʻole, a laila piʻi nui ka paʻakikī o ka hana. I kēia hihia, pono e helu i kēlā me kēia lālani (dummy, ʻōmaʻomaʻo a me ka ʻulaʻula) kaʻawale no nā ʻāpana maikaʻi ʻole a maikaʻi me nā ʻano:
I ʻole e ʻeha nui a ʻaʻole hoʻi hou i ka huila, hiki ke hoʻoiho ʻia kahi mamana i hoʻomākaukau ʻia no ia hihia ma ke poʻo o kēia ʻatikala.
Kaʻina 4. Exotic: up-down bands
Hoʻokumu ʻia kēia ʻano hana ma ka hoʻohana ʻana i kahi mea ʻike liʻiliʻi i ʻike ʻia o nā pakuhi palahalaha (histograms a me nā kiʻi) - Nā pūʻali i luna (Paʻa i luna-lalo). Hoʻopili kēia mau pūʻulu i nā kiko o nā pakuhi ʻelua i ʻelua e hōʻike pono i kahi o nā kiko ʻelua i ʻoi aku ka kiʻekiʻe a i ʻole ka haʻahaʻa, i hoʻohana ikaika ʻia i ka nānā ʻana i ka plan-fact:
He mea maʻalahi ke noʻonoʻo inā e wehe mākou i nā laina o nā kiʻi a waiho wale i nā kaula i luna ma ka pakuhi, a laila e loaʻa iā mākou ka "wailele" like.
No ia hana ʻana, pono mākou e hoʻohui i ʻelua kolamu hou aʻe i kā mākou papa ʻaina me nā ʻōkuhi maʻalahi e helu i ke kūlana o nā kiʻi ʻike ʻole ʻelua.
No ka hana ʻana i kahi "wailele", pono ʻoe e koho i kahi kolamu me nā mahina (no nā pūlima ma ke axis X) a me ʻelua kolamu hou. Hōʻuluʻulu 1 и Hōʻuluʻulu 2 a kūkulu i ka pakuhi maʻamau no ka hoʻomaka ʻana e hoʻohana Hoʻokomo - Graph (Hoʻokomo - Line Сhart):
I kēia manawa e hoʻohui i nā pūʻulu i luna i kā mākou pakuhi:
- Ma Excel 2013 a hou aku, pono e koho ʻia kēia ma ka pā Kukahula E kauoha aku Hoʻohui i ka ʻĀpana Papa - Nā pūʻali hoʻonui-emi (Hoʻolālā — Hoʻohui i ka ʻElemu Papa — Nā Paʻa i lalo)
- Ma Excel 2007-2010 - hele i ka pā Layout – Nā Bar Hoʻoemi-Hoʻoemi (Layout — Nā Paʻa i luna-Iho)
A laila e nānā ka pakuhi e like me kēia:
Ke waiho nei i ke koho ʻana i nā kiʻi a hoʻomālamalama iā lākou ma ke kaomi ʻana iā lākou me ka pihi ʻiole ʻākau a koho i ke kauoha. Hōʻano moʻo ʻikepili (Hōʻano moʻo). Pēlā nō, hiki iā ʻoe ke hoʻololi i nā kala ʻeleʻele a me ke keʻokeʻo i ka ʻōmaʻomaʻo a me ka ʻulaʻula no ka loaʻa ʻana o kahi kiʻi maikaʻi loa i ka hopena:
I nā mana hou loa o Microsoft Excel, hiki ke hoʻololi ʻia ka laulā o nā kaola ma ke kaomi ʻana i kekahi o nā kiʻi maopopo (ʻaʻole nā pahu!) me ke pihi ʻiole ʻākau a koho i ke kauoha. Hōʻike Hōʻikeʻike - Hoʻomaʻemaʻe ʻaoʻao (Hōʻano moʻo — Laulā ākea).
I nā mana kahiko o Excel, pono ʻoe e hoʻohana i ke kauoha Visual Basic e hoʻoponopono i kēia:
- E hōʻike i ke kiʻikuhi i kūkulu ʻia
- E kaomi i ka pōkole kī Alt+F11e komo i ka Lunahooponopono Visual Basic
- Kaomi pākuhi pākuhi Ctrl+Ge wehe i ka papa kuhikuhi kauoha a me ka debug panel koke (ʻo ka mea maʻamau aia ma lalo).
- E kope a paʻi i kēia kauoha ma laila: ActiveChart.ChartGroups(1).GapWidth = 30 a ua pilikia komo:
Hiki iā ʻoe, ʻoiaʻiʻo, ke pāʻani a puni me ka waiwai parameter inā makemake ʻoe. ʻAhaWidthe hoʻokō i ka ʻae i makemake ʻia:
- Pehea e kūkulu ai i kahi pakuhi pōkā ma Excel e ʻike i ka KPI
- He aha ka mea hou ma nā pakuhi ma Excel 2013
- Pehea e hana ai i kahi pakuhi "ola" ma Excel