Aðferð 1. Formúlur
Við skulum byrja, fyrir upphitun, með einfaldasta valkostinum - formúlum. Ef við höfum litla töflu flokkaða eftir dagsetningu sem inntak, þá þurfum við grunnformúlu til að reikna út heildartöluna í sérstökum dálki:
Aðaleiginleikinn hér er erfiður lagfæring á bilinu innan SUM fallsins - tilvísunin í upphaf bilsins er gerð algjör (með dollaramerkjum) og til enda - afstæð (án dollara). Í samræmi við það, þegar formúlan er afrituð niður í allan dálkinn, fáum við stækkandi svið, summan sem við reiknum út.
Ókostirnir við þessa aðferð eru augljósir:
- Taflan verður að vera flokkuð eftir dagsetningu.
- Þegar nýjum línum er bætt við með gögnum verður að framlengja formúluna handvirkt.
Aðferð 2. Snúningstafla
Þessi aðferð er aðeins flóknari, en miklu skemmtilegri. Og til að auka enn á, skulum við íhuga alvarlegra vandamál – töflu með 2000 raðir af gögnum, þar sem engin flokkun er eftir dagsetningardálknum, en það eru endurtekningar (þ.e. við getum selt nokkrum sinnum á sama degi):
Við umbreytum upprunalegu töflunni okkar í „snjalla“ (dýnamíska) flýtilykla Ctrl+T eða lið Heim - Snið sem töflu (Heima - Snið sem töflu), og svo byggjum við pivot töflu á það með skipuninni Setja inn - PivotTable (Setja inn - snúningstafla). Við setjum dagsetninguna í línusvæðið í samantektinni og fjölda seldra vara á gildissvæðinu:
Vinsamlegast athugaðu að ef þú ert með ekki alveg gamla útgáfu af Excel, þá eru dagsetningarnar sjálfkrafa flokkaðar eftir árum, ársfjórðungum og mánuðum. Ef þú þarft aðra flokkun (eða þarft hana alls ekki), þá geturðu lagað það með því að hægrismella á hvaða dagsetningu sem er og velja skipanir Hópur / Afriðla (Hópur / Taka upp).
Ef þú vilt sjá bæði heildartölurnar eftir tímabilum og hlaupandi heildartöluna í sérstökum dálki, þá er skynsamlegt að henda reitnum inn í gildissvæðið Selt aftur til að fá afrit af reitnum - í honum munum við kveikja á birtingu hlaupandi heildar. Til að gera þetta skaltu hægrismella á reitinn og velja skipunina Viðbótarútreikningar - Uppsöfnuð samtals (Sýna gildi sem — Heildartölur):
Þar geturðu líka valið þann möguleika að hækka heildartölur sem prósentu og í næsta glugga þarftu að velja reitinn sem uppsöfnunin mun fara fyrir – í okkar tilviki er þetta dagsetningarreiturinn:
Kostir þessarar aðferðar:
- Mikið magn gagna er fljótt lesið.
- Engar formúlur þarf að slá inn handvirkt.
- Þegar frumgögnum er breytt er nóg að uppfæra samantektina með hægri músarhnappi eða með skipuninni Data – Refresh All.
Ókostirnir fylgja því að þetta er samantekt, sem þýðir að þú getur ekki gert hvað sem þú vilt í henni (setja inn línur, skrifa formúlur, smíða einhverjar skýringarmyndir o.s.frv.) virkar ekki lengur.
Aðferð 3: Power Query
Við skulum hlaða „snjöllu“ töflunni okkar með upprunagögnum í Power Query fyrirspurnaritlinum með því að nota skipunina Gögn – Frá töflu/sviði (Gögn — úr töflu/sviði). Í nýjustu útgáfum af Excel, við the vegur, var það endurnefnt - nú heitir það Með laufblöðum (Úr blaði):
Þá munum við framkvæma eftirfarandi skref:
1. Raðaðu töflunni í hækkandi röð eftir dagsetningardálknum með skipuninni Raða hækkandi í fellilistanum síu í töfluhausnum.
2. Nokkru síðar, til að reikna út heildartöluna, þurfum við aukadálk með raðlínunúmerinu. Við skulum bæta því við með skipuninni Bæta við dálki – Vísitöludálki – Frá 1 (Bæta við dálki - Vísindadálki - Frá 1).
3. Einnig, til að reikna út heildartöluna, þurfum við tilvísun í dálkinn Selt, þar sem samantekt gögn okkar liggja. Í Power Query eru dálkar einnig kallaðir listar (listi) og til að fá tengil á hann skaltu hægrismella á dálkhausinn og velja skipunina Útlista (Sýna smáatriði). Tjáningin sem við þurfum mun birtast á formúlustikunni, sem samanstendur af nafni fyrra skrefs #“Vísi bætt við“, þaðan sem við tökum töfluna og dálknafnið [Sala] úr þessari töflu í hornklofa:
Afritaðu þessa tjáningu á klemmuspjaldið til frekari notkunar.
4. Eyða óþarfa meira síðasta skrefi Selt og bættu í staðinn við útreiknuðum dálki til að reikna út heildartöluna með skipuninni Að bæta við dálki - Sérsniðinn dálkur (Bæta við dálki - sérsniðinn dálkur). Formúlan sem við þurfum mun líta svona út:
Hér er aðgerðin Listi.Range tekur upprunalega listann (dálkur [Sala]) og dregur út þætti úr því, byrjað á þeim fyrsta (í formúlunni er þetta 0, þar sem tölusetning í Power Query byrjar á núlli). Fjöldi þátta til að sækja er röð númerið sem við tökum úr dálknum [Vísi]. Þannig að þessi aðgerð fyrir fyrstu línu skilar aðeins einni fyrstu reit dálksins Selt. Fyrir aðra línu - þegar fyrstu tvær frumurnar, fyrir þá þriðju - fyrstu þrjár osfrv.
Jæja, þá aðgerðin Listi.Suma leggur saman útdregin gildi og við fáum í hverri röð summan af öllum fyrri þáttum, þ.e. uppsafnaða heildarfjölda:
Það er eftir að eyða Index dálknum sem við þurfum ekki lengur og hlaða niður niðurstöðunum aftur í Excel með Home - Close & Load til að stjórna.
Vandamálið er leyst.
Fljótur og trylltur
Í grundvallaratriðum hefði verið hægt að stöðva þetta, en það er lítil fluga í smyrslinu – beiðnin sem við bjuggum til virkar á hraða skjaldböku. Til dæmis, á minni ekki veikustu tölvunni, er borð með aðeins 2000 raðir afgreidd á 17 sekúndum. Hvað ef það eru fleiri gögn?
Til að flýta fyrir geturðu notað biðminni með því að nota sérstaka List.Buffer aðgerðina, sem hleður listanum (listanum) sem honum er gefinn sem rök í vinnsluminni, sem flýtir mjög fyrir aðgangi að honum í framtíðinni. Í okkar tilviki er skynsamlegt að biðja um #“Added index”[Sold] listann, sem Power Query þarf að fá aðgang að þegar reiknað er út heildartöluna í hverri röð í 2000 raða töflunni okkar.
Til að gera þetta, í Power Query ritlinum á aðalflipanum, smelltu á Advanced Editor hnappinn (Heima – Advanced Editor) til að opna frumkóðann fyrir fyrirspurn okkar á M tungumálinu sem er innbyggt í Power Query:
Og bæta svo við línu með breytu þar Listi minn, gildi þess er skilað af biðminni, og í næsta skrefi skiptum við út kallinu á listann fyrir þessa breytu:
Eftir að hafa gert þessar breytingar mun fyrirspurn okkar verða verulega hraðari og mun takast á við 2000 raða töflu á aðeins 0.3 sekúndum!
Annað, ekki satt? 🙂
- Pareto graf (80/20) og hvernig á að byggja það í Excel
- Leitarorðaleit í texta og biðminni fyrir fyrirspurnir í Power Query