Okuqukethwe
Indlela 1. Amafomula
Ake siqale, ukufudumeza, ngenketho elula - amafomula. Uma sinethebula elincane elihlelwa ngedethi njengokufakwayo, ukuze sibale isamba esisebenzayo kukholamu ehlukile, sidinga ifomula yokuqala:
Isici esiyinhloko lapha ukulungisa okukhohlisayo kobubanzi ngaphakathi komsebenzi we-SUM - ireferensi ekuqaleni kobubanzi yenziwa ngokuphelele (ngezimpawu zamadola), futhi kuze kube sekupheleni - isihlobo (ngaphandle kwamadola). Ngokufanelekile, lapho sikopisha ifomula phansi kuyo yonke ikholomu, sithola ububanzi obukhulayo, isamba saso esibalayo.
Ububi bale ndlela busobala:
- Ithebula kufanele lihlungwe ngedethi.
- Uma wengeza imigqa emisha ngedatha, ifomula kuzodingeka inwetshwe mathupha.
Indlela 2. Ithebula le-Pivot
Le ndlela iyinkimbinkimbi kancane, kodwa ijabulisa kakhulu. Futhi ukuze sibhebhethekise, ake sicabangele inkinga engathi sína kakhulu - ithebula lemigqa yedatha engu-2000, lapho kungekho khona ukuhlelwa ngekholomu yedethi, kodwa kunezimpinda (okungukuthi singathengisa izikhathi ezimbalwa ngosuku olufanayo):
Siguqulela ithebula lethu langempela libe isinqamuleli sekhibhodi "ehlakaniphile" (ashukumisayo). Ctrl+T noma iqembu Ikhaya – Fometha njengetafula (Ekhaya — Fometha Njengethebula), bese sakha ithebula le-pivot phezu kwalo ngomyalo Faka – PivotTable (Faka — Ithebula Le-Pivot). Sibeka idethi endaweni yemigqa esifinyezweni, kanye nenani lezimpahla ezithengiswa endaweni yamanani:
Sicela uqaphele ukuthi uma ungenayo inguqulo endala ye-Excel, khona-ke amadethi ahlelwa ngokuzenzakalelayo ngeminyaka, amakota nezinyanga. Uma udinga iqembu elihlukile (noma ungalidingi nhlobo), ungalilungisa ngokuchofoza kwesokudla kunoma yiluphi usuku bese ukhetha imiyalo. Iqembu / Hlukanisa iqembu (Iqembu / Hlukanisa iqembu).
Uma ufuna ukubona kokubili amanani angumphumela ngezikhathi kanye nesamba esisebenzayo kukholamu ehlukile, khona-ke kunengqondo ukuphonsa inkambu endaweni yenani. Kuthengiswe futhi ukuze uthole impinda yenkambu - kuyo sizovula isibonisi samanani asebenzayo. Ukuze wenze lokhu, chofoza kwesokudla endaweni bese ukhetha umyalo Izibalo ezengeziwe - Isamba Esiqongelelwe (Bonisa Amanani njengokuthi - Amanani Asebenzayo):
Lapho ungaphinda ukhethe inketho yokukhula kwamanani njengephesenti, futhi efasiteleni elilandelayo udinga ukukhetha inkambu lapho ukuqoqwa kuzoya khona - kithi, lena inkambu yedethi:
Izinzuzo zale ndlela:
- Inani elikhulu ledatha lifundwa ngokushesha.
- Awekho amafomula adinga ukufakwa mathupha.
- Lapho ushintsha idatha yomthombo, kwanele ukubuyekeza isifinyezo ngenkinobho yegundane engakwesokudla noma ngomyalo we-Data - Vuselela Konke.
Ububi bulandela eqinisweni lokuthi lesi isifinyezo, okusho ukuthi awukwazi ukwenza noma yini oyifunayo kuyo (faka imigqa, bhala amafomula, yakha noma yimiphi imidwebo, njll.) ngeke isasebenza.
Indlela yesi-3: Umbuzo wamandla
Masilayishe ithebula lethu “elihlakaniphile” elinedatha yomthombo kusihleli sombuzo Wombuzo Wamandla sisebenzisa umyalo Idatha - Kusuka kuThebula/Ububanzi (Idatha - Kusuka Kuthebula/Ububanzi). Ezinguqulweni zakamuva ze-Excel, ngendlela, yaqanjwa kabusha - manje isibizwa Ngamaqabunga (Kusuka Eshidini):
Ngemuva kwalokho sizokwenza lezi zinyathelo ezilandelayo:
1. Hlunga ithebula ngohlelo olukhuphukayo ngekholomu yedethi ngomyalo Hlunga ngokwenyuka ohlwini lokudonsela phansi lesihlungi kunhlokweni yethebula.
2. Kamuva, ukubala inani elisebenzayo, sidinga ikholomu eyisizayo enenombolo yomugqa we-ordinal. Asiyengeze ngomyalo Engeza Ikholomu - Ikholomu Yenkomba - Kusuka ku-1 (Engeza ikholomu - Ikholomu yenkomba - Kusukela ku-1).
3. Futhi, ukuze ubale isamba esisebenzayo, sidinga ireferensi yekholomu Kuthengiswe, lapho idatha yethu efinyeziwe ilele. Embuzweni Wamandla, amakholomu abizwa nangokuthi izinhlu (uhlu) futhi ukuze uthole isixhumanisi kuwo, chofoza kwesokudla kusihloko sekholomu bese ukhetha umyalo. Imininingwane (Bonisa imininingwane). Inkulumo esiyidingayo izovela kubha yefomula, ehlanganisa igama lesinyathelo sangaphambilini #"Inkomba yengeziwe", lapho sithatha khona itafula kanye negama lekholomu [Ukuthengisa] kusuka kuleli thebula kubakaki abayisikwele:
Kopisha lesi sisho ebhodini lokunamathisela ukuze usetshenziswe futhi.
4. Susa isinyathelo sokugcina esingadingekile Kuthengiswe bese wengeza ikholomu ebaliwe yokubala inani elisebenzayo ngomyalo Ukwengeza Ikholomu – Ikholomu Ngokwezifiso (Engeza ikholomu — Ikholomu yangokwezifiso). Ifomula esiyidingayo izobukeka kanje:
Lapha umsebenzi Uhlu.Ububanzi ithatha uhlu lwangempela (ikholomu [Ukuthengisa]) futhi ikhiphe izakhi kuyo, iqala kweyokuqala (kwifomula, lokhu kungu-0, njengoba ukubala izinombolo kokuthi Umbuzo Wamandla kuqala ziro). Inani lama-elementi okufanele abuyiswe inombolo yomugqa esiyithatha kukholomu [Inkomba]. Ngakho-ke lo msebenzi womugqa wokuqala ubuyisela kuphela iseli yokuqala yokuqala yekholomu Kuthengiswe. Ngomugqa wesibili - kakade amaseli amabili okuqala, okwesithathu - amathathu okuqala, njll.
Awu, ke umsebenzi Uhlu.Isamba sihlanganisa amanani akhishiwe futhi sithola emgqeni ngamunye isamba sazo zonke izici zangaphambilini, okungukuthi inani eliqoqiwe:
Kusele ukususa ikholomu ye-Index esingasayidingi futhi silayishe imiphumela siyibuyisele ku-Excel ngokuthi Ikhaya - Vala & Layisha ukuze uyale.
Inkinga isixazululiwe.
Ukushesha Nokuthukuthela
Empeleni, lokhu bekungase kumiswe, kodwa kukhona impukane encane emafutheni - isicelo esisidalile sisebenza ngesivinini sofudu. Isibonelo, ku-PC yami engeyona ebuthakathaka, itafula lemigqa engu-2000 kuphela licutshungulwa ngemizuzwana engu-17. Kuthiwani uma kunedatha eyengeziwe?
Ukusheshisa, ungasebenzisa ukugcina kumthamo usebenzisa umsebenzi okhethekile we-List.Buffer, olayisha uhlu (uhlu) olunikezwe lona njengengxabano ku-RAM, okusheshisa kakhulu ukufinyelela kuyo ngokuzayo. Kithina, kunengqondo ukufaka isivikelo ohlwini #”Added index”[Okuthengiswayo], umbuzo Wamandla okufanele ufinyelele kuwo lapho kubalwa isamba esisebenzayo kumugqa ngamunye wethebula lethu lemigqa engu-2000.
Ukuze wenze lokhu, kusihleli Sombuzo Wamandla kuthebhu Eyinhloko, chofoza inkinobho Yokuhlela Okuthuthukile (Ikhaya - Isihleli Esithuthukisiwe) ukuze uvule ikhodi yomthombo yombuzo wethu ngolimi luka-M olwakhelwe kokuthi Umbuzo Wamandla:
Bese wengeza umugqa onokuguquguquka lapho Uhlu Lwami, inani elibuyiswa umsebenzi wokubhafa, futhi esinyathelweni esilandelayo sibuyisela ucingo ohlwini ngalokhu kuhluka:
Ngemva kokwenza lezi zinguquko, umbuzo wethu uzoshesha kakhulu futhi uzobhekana netafula lemigqa engu-2000 ngemizuzwana engu-0.3 nje!
Enye into, akunjalo? 🙂
- Ishadi le-Pareto (80/20) nendlela yokulakha ku-Excel
- Ukusesha kwegama elingukhiye embhalweni nasekugcineni kumthamo wemibuzo kokuthi Umbuzo Wamandla