Njira 1. Zolemba
Tiyeni tiyambe, pakuwotha, ndi njira yosavuta - mafomu. Ngati tili ndi tebulo laling'ono losanjidwa ndi deti ngati zolowetsa, ndiye kuti tiwerenge kuchuluka kwazomwe zikuchitika mugawo lapadera, timafunikira fomula yoyambira:
Chinthu chachikulu apa ndikukonza kwachinyengo kwamtundu mkati mwa ntchito ya SUM - kutchulidwa koyambira kwamtunduwu kumapangidwa mtheradi (ndi zizindikiro za dollar), mpaka kumapeto - wachibale (popanda madola). Chifukwa chake, pokopera fomula mpaka gawo lonselo, timapeza kuchuluka komwe timawerengera.
Zoyipa za njirayi ndizodziwikiratu:
- Tebulo liyenera kusanjidwa ndi deti.
- Mukawonjezera mizere yatsopano ndi data, fomulayo iyenera kukulitsidwa pamanja.
Njira 2. Gome la pivot
Njirayi ndi yovuta kwambiri, koma yosangalatsa kwambiri. Ndipo kuti tichulukitse, tiyeni tilingalire vuto lalikulu kwambiri - tebulo la mizere 2000 ya data, pomwe palibe kusanja ndi gawo la deti, koma pali kubwereza (mwachitsanzo, titha kugulitsa kangapo tsiku lomwelo):
Timatembenuza tebulo lathu loyambirira kukhala njira yachidule ya kiyibodi ya "smart" (dynamic). Ctrl+T kapena timu Kunyumba - Pangani ngati tebulo (Kunyumba - Pangani Monga Table), ndiyeno timapanga tebulo la pivot pa izo ndi lamulo Ikani - PivotTable (Lowetsani - Pivot Table). Timayika tsikulo mumizere yachidule, ndi kuchuluka kwa katundu wogulitsidwa m'dera lamtengo wapatali:
Chonde dziwani kuti ngati mulibe mtundu wakale wa Excel, ndiye kuti masikuwo amasanjidwa ndi zaka, kotala ndi miyezi. Ngati mukufuna gulu lina (kapena simukulifuna nkomwe), ndiye kuti mutha kukonza ndikudina kumanja pa tsiku lililonse ndikusankha malamulo. Gulu / Osagwirizana (Gulu / Olekanitsa).
Ngati mukufuna kuwona ziwopsezo zonse ndi nthawi ndi kuchuluka komwe kumayendera mugawo lapadera, ndiye kuti ndizomveka kuponyera gawolo mugawo lamtengo wapatali. Zagulitsidwa kachiwiri kuti titenge gawo la gawoli - momwemo tidzayatsa mawonedwe a ziwerengero zothamanga. Kuti muchite izi, dinani kumanja pagawo ndikusankha lamulo Kuwerengera Zowonjezera - Cumulative Total (Onetsani Makhalidwe Monga - Mawerengedwe Othamanga):
Kumeneko mungathe kusankha njira yowonjezeretsa kuchuluka kwa chiwerengero, ndipo pawindo lotsatira muyenera kusankha malo omwe kudziunjikirako kudzapitira - kwa ife, ili ndilo gawo la masiku:
Ubwino wa njirayi:
- Kuchuluka kwa deta kumawerengedwa mwamsanga.
- Palibe mafomu omwe ayenera kulembedwa pamanja.
- Mukasintha zomwe zili mugwero, ndikokwanira kusinthira chidulecho ndi batani lamanja la mbewa kapena ndi lamulo la Data - Refresh All.
Zoyipa zimatsatira mfundo yakuti iyi ndi chidule, kutanthauza kuti simungathe kuchita chilichonse chomwe mukufuna mmenemo (lowetsani mizere, lembani ndondomeko, pangani zojambula zilizonse, ndi zina zotero) sizidzagwiranso ntchito.
Njira 3: Kufufuza Mphamvu
Tiyeni tikweze tebulo lathu la "smart" lomwe lili ndi deta yochokera ku Power Query query editor pogwiritsa ntchito lamulo Deta - Kuchokera pa Table/Range (Deta - Kuchokera pa Table/Range). M'matembenuzidwe aposachedwa a Excel, mwa njira, idasinthidwanso - tsopano ikutchedwa Ndi masamba (Kuchokera pa Mapepala):
Kenako tidzachita zotsatirazi:
1. Sanjani tebulo mu dongosolo lokwera ndi deti ndi lamulo Sungani kukwera mu mndandanda wotsikira pansi wa fyuluta pamutu wa tebulo.
2. Pakapita nthawi, kuti tiwerenge kuchuluka kwazomwe zikuchitika, timafunikira gawo lothandizira ndi nambala ya mzere wa ordinal. Tiyeni tiwonjezere ndi lamulo Onjezani Column - Index Column - Kuchokera ku 1 (Onjezani ndime - Index column - Kuchokera 1).
3. Komanso, kuti tiwerenge kuchuluka kwazomwe zikuyenda, tifunika kutchulidwa pamzerewu Zagulitsidwa, pomwe deta yathu yachidule ili. Mu Power Query, mizati imatchedwanso mindandanda (mndandanda) ndipo kuti mupeze ulalo, dinani kumanja pamutu wagawo ndikusankha lamulo. Zambiri (Onetsani zambiri). Mawu omwe tikufuna adzawonekera mu bar ya formula, yomwe ili ndi dzina la sitepe yapitayi #"Mlozera wawonjezeredwa", kuchokera pomwe timatenga tebulo ndi dzina lazanja [Zogulitsa] kuchokera patebulo ili m'mabulaketi masikweya:
Koperani mawuwa pa clipboard kuti muwagwiritsenso ntchito.
4. Chotsani sitepe yomaliza yosafunikira Zagulitsidwa ndipo yonjezerani m'malo mwake ndime yowerengeka kuti muwerenge kuchuluka kwazomwe zikuchitika ndi lamulo Kuwonjezera Mzere - Mzere Wamakonda (Onjezani gawo - Mzere Wamakonda). Fomula yomwe tikufuna idzawoneka motere:
Apa ntchito List.Range imatenga mndandanda woyambirira (gawo [Zogulitsa]) ndikuchotsa zinthu kuchokera pamenepo, kuyambira woyamba (mu chilinganizo, iyi ndi 0, popeza kuwerengera mu Power Query kumayambira paziro). Chiwerengero cha zinthu zomwe tingapeze ndi nambala ya mzere womwe timatenga kuchokera pamndandanda [Index]. Chifukwa chake ntchitoyi pamzere woyamba imangobweretsa selo imodzi yoyamba pamzerewu Zagulitsidwa. Kwa mzere wachiwiri - kale maselo awiri oyambirira, achitatu - atatu oyambirira, ndi zina zotero.
Chabwino, ndiye ntchito List.Sum imawerengera zomwe zachotsedwa ndipo timapeza mumzere uliwonse kuchuluka kwa zinthu zonse zam'mbuyomu, mwachitsanzo, kuchuluka:
Zimatsalira kuchotsa gawo la Index lomwe sitikufunanso ndikukweza zotsatira kubwerera ku Excel ndi Home - Close & Load to command.
Vuto lathetsedwa.
Achangu ndi aukali
Kwenikweni, izi zikanayimitsidwa, koma pali ntchentche yaying'ono mumafuta - pempho lomwe tidapanga limagwira ntchito pa liwiro la kamba. Mwachitsanzo, pa PC yanga yomwe si yofooka kwambiri, tebulo la mizere 2000 yokha limakonzedwa mumasekondi 17. Nanga bwanji ngati pali zambiri?
Kuti mufulumizitse, mungagwiritse ntchito buffering pogwiritsa ntchito ntchito yapadera ya List.Buffer, yomwe imanyamula mndandanda (mndandanda) woperekedwa kwa iwo ngati mkangano mu RAM, yomwe imafulumizitsa kwambiri kuipeza m'tsogolomu. Kwa ife, ndizomveka kusungitsa mndandanda wa #"Added index"[Ogulitsidwa], womwe Power Query iyenera kupeza powerengera kuchuluka kwa mzere uliwonse wa tebulo lathu la mizere 2000.
Kuti muchite izi, mu Power Query editor pa Main tabu, dinani batani la Advanced Editor (Home - Advanced Editor) kuti mutsegule gwero la funso lathu m'chinenero cha M chopangidwa mu Power Query:
Kenako onjezani mzere wokhala ndi zosinthika pamenepo MyList, mtengo wake womwe umabwezeredwa ndi buffering ntchito, ndipo pa sitepe yotsatira timalowetsa kuyitana pamndandanda ndikusintha uku:
Tikasintha izi, funso lathu lidzakhala lofulumira kwambiri ndipo lidzatha kuthana ndi tebulo la mizere 2000 mumasekondi 0.3 okha!
Chinthu china, chabwino? 🙂
- Pareto chart (80/20) ndi momwe mungapangire mu Excel
- Kusaka kwa mawu osakira m'mawu ndi kusungitsa mafunso mu Power Query