Nzira 1. Zvirongwa
Ngatitange, yekudziya, neiyo yakapusa sarudzo - mafomula. Kana isu tiine tafura diki yakarongedzwa nezuva sekuisa, saka kuverenga iyo inomhanya yakazara mune yakaparadzana koramu, isu tinoda yekutanga formula:
Chinhu chikuru pano ndechekugadzirisa kwemaitiro mukati meSUM basa - chirevo chekutanga kwehuwandu hunoitwa zvachose (nemadhora emadhora), uye kusvika kumagumo - hama (pasina madhora). Saizvozvo, kana tichikopa fomula pasi kusvika kukoromo yose, tinowana kuwedzera kwehuwandu, iyo yakawanda yatinoverenga.
Kuipa kweiyi nzira kuri pachena:
- Tafura inofanira kurongwa nemazuva.
- Paunenge uchiwedzera mitsara mitsva nedata, iyo fomula ichafanirwa kuwedzerwa nemaoko.
Nzira 2. Pivot tafura
Iyi nzira inonyanya kuoma, asi yakawanda inofadza. Uye kuwedzera, ngatifungei nezvedambudziko rakakura - tafura ye2000 mitsara yedata, uko pasina kurongeka neiyo date column, asi pane kudzokorora (kureva kuti tinogona kutengesa kakawanda pazuva rimwe chete):
Isu tinoshandura tafura yedu yekutanga kuita "smart" (simba) nzira yekudimbudzira yekhibhodi Ctrl+T kana timu Kumba – Fomati setafura (Kumba - Fomati seTafura), uye tobva tavaka tafura yepivot pairi nemurairo Pinza – PivotTable (Isa - Pivot Tafura). Isu tinoisa zuva mumitsara munzvimbo muchidimbu, uye huwandu hwezvinhu zvinotengeswa munzvimbo yezvinokosha:
Ndokumbira utarise kuti kana iwe usina yakare vhezheni yeExcel, saka mazuva acho anoiswa otomatiki nemakore, makota nemwedzi. Kana iwe uchida boka rakasiyana (kana usingaride zvachose), saka unogona kuzvigadzirisa nekudzvanya-kurudyi pane chero zuva uye kusarudza mirairo. Group / Ungroup (Boka / Ungroup).
Kana iwe uchida kuona ese ari maviri mhedzisiro nenhambo uye iyo inomhanya yakazara muchikamu chakasiyana, saka zvine musoro kukanda munda munzvimbo yakakosha. Sold zvakare kuti uwane duplicate yemunda - mairi tichabatidza chiratidziro chekumhanya kwehuwandu. Kuti uite izvi, tinya-kurudyi pamunda uye sarudza murairo Kuwedzera Masvomhu – Cumulative Total (Ratidza Values se - Running Totals):
Ikoko iwe unogona zvakare kusarudza sarudzo yekukura huwandu hwese muzana, uye pahwindo rinotevera iwe unofanirwa kusarudza iyo iyo iyo kuunganidza ichaenda - kwatiri isu, iyi ndiyo zuva ndima:
Zvakanakira nzira iyi:
- Nhamba huru ye data inokurumidza kuverengwa.
- Hapana mafomula anoda kuiswa nemaoko.
- Paunenge uchichinja mune sosi data, zvinokwana kugadzirisa pfupiso nebhatani rekurudyi rembeva kana neData - Refresh All command.
Izvo zvisingabatsiri zvinotevera kubva pakuti iyi ipfupiso, izvo zvinoreva kuti haugone kuita chero chaunoda mairi (isa mitsetse, nyora mafomula, kuvaka chero mifananidzo, nezvimwewo) haichashandi.
Nzira 3: Power Query
Ngatiisei yedu "smart" tafura ine source data muPower Query query edhita tichishandisa rairo Data – Kubva Tafura/Range (Data - Kubva Tafura/Range). Mune shanduro dzekupedzisira dzeExcel, nenzira, yakatumidzwa zita - ikozvino inonzi Nemashizha (Kubva Pepa):
Zvadaro tichaita zvinotevera matanho:
1. Ronga tafura mukukwira kurongeka nekoramu yemazuva nemurairo Ronga uchikwira mune sefa yekudonhedza pasi runyorwa mumusoro wetafura.
2. Mushure mechinguva chidiki, kuti tiverenge iyo inomhanya yakazara, isu tinoda yekubatsira mbiru ine ordinal mutsara nhamba. Ngatizviwedzere nemurairo Wedzera Column - Index Column - Kubva 1 (Wedzera koramu - Index column - Kubva 1).
3. Zvakare, kuti tiverenge iyo inomhanya yakazara, isu tinoda rengedzo kune iyo column Sold, panowanikwa data redu rakapfupikiswa. MuMubvunzo Wesimba, makoramu anonziwo rondedzero (rondedzero) uye kuti uwane chinongedzo kwairi, tinya-kurudyi pamusoro wekoramu uye sarudza murairo. Kudhara (Ratidza zvakadzama). Chirevo chatinoda chinozoonekwa muformula bar, ine zita renhanho yapfuura #"Index yakawedzerwa", kubva kwatinotora tafura uye zita rekoramu [Sales] kubva patafura iyi mumabhuraketi akaenzana:
Kopa kutaura uku kubhodhibhodhi kuti uwedzere kushandiswa.
4. Delete zvisingaite nhanho yekupedzisira Sold uye wedzera panzvimbo yakaverengerwa koramu yekuverenga iyo inomhanya yakazara nemurairo Kuwedzera Column - Custom Column (Wedzera koramu - Tsika mbiru). Iyo formula yatinoda ichaita seizvi:
Heino basa List.Range inotora runyorwa rwekutanga (column [Kutengesa]) uye inobvisa zvinhu kubva mairi, kutanga kubva kune yekutanga (mune fomula, iyi i0, sezvo kuverenga muSimba Query kunotanga kubva zero). Huwandu hwezvinhu zvekutoresa ndiyo nhamba yemutsara yatinotora kubva mukoromo [Index]. Saka basa iri remutsara wekutanga rinongodzosera sero rimwe rekutanga remutsetse Sold. Pamutsara wechipiri - atova masero maviri ekutanga, chechitatu - matatu ekutanga, nezvimwe.
Zvakanaka, ipapo basa List.Sm inoverengera iyo yakabviswa kukosha uye isu tinowana mumutsara wega wega huwandu hwezvinhu zvese zvakapfuura, kureva kuwanda kwese:
Izvo zvinosara kudzima Index column yatisingachada uye kurodha zvabuda kudzokera kuExcel neImba - Vhara & Rodha kuraira.
Dambudziko rinogadziriswa.
Kurumidza uye Hasha
Muchidimbu, izvi zvingadai zvakamiswa, asi kune nhunzi duku mumafuta - chikumbiro chatakagadzira chinoshanda nekumhanya kwekamba. Semuenzaniso, pane yangu isiri iyo isina kusimba PC, tafura ye2000 mitsara chete inogadziriswa mumasekondi gumi nematanhatu. Ko kana paine data rakawanda?
Kuti ukurumidze, unogona kushandisa buffering uchishandisa iyo yakakosha List.Buffer basa, iyo inoremedza runyoro (rondedzero) yakapihwa iyo senharo mu RAM, iyo inokurumidza kukurumidza kusvika kwairi mune ramangwana. Kwatiri, zvine musoro kuvharira iyo #”Yakawedzerwa index”[Kutengeswa] runyorwa, iyo Power Query inofanirwa kuwana kana uchiverenga iyo inomhanya yakazara mumutsara wega wega wetafura yedu yemitsara mazana maviri.
Kuti uite izvi, mune iyo Power Query edhita paMain tab, tinya bhatani rePamusoro Edhita (Kumba - Yepamberi Mharidzo) kuti uvhure kodhi kodhi yemubvunzo wedu mumutauro weM wakavakirwa muSimba Query:
Uye wobva wawedzera mutsara une shanduko ipapo MyList, kukosha kwaro kunodzoserwa nebasa rebuffering, uye padanho rinotevera tinotsiva kufona kune iyo rondedzero neiyi shanduko:
Mushure mekuita shanduko idzi, mubvunzo wedu unokurumidza kukurumidza uye uchasangana netafura yemitsara mazana maviri mumasekonzi 2000 chete!
Chimwe chinhu, handiti? 🙂
- Pareto chati (80/20) uye maitiro ekuvaka muExcel
- Kutsvaga kwemazwi akakosha mune zvinyorwa uye kubvunza kubhafa muMasimba Query