Snúningstöflur eru góðar fyrir alla - þær reikna fljótt og eru stilltar á sveigjanlegan hátt og hægt er að vefja hönnunina inn í þær á glæsilegan hátt, ef þörf krefur. En það eru líka nokkrar flugur í smyrslinu, einkum vanhæfni til að búa til samantekt, þar sem gildissvæðið ætti ekki að innihalda tölur, heldur texta.
Við skulum reyna að komast í kringum þessa takmörkun og koma með „hækjur“ í svipaðri stöðu.
Segjum sem svo að fyrirtækið okkar flytji vörur sínar í gámum til nokkurra borga í landinu okkar og Kasakstan. Gámar eru sendir ekki oftar en einu sinni í mánuði. Hver ílát hefur alfanumerískt númer. Sem upphafsgögn eru staðlaðar töflur sem sýna sendingar, þaðan sem þú þarft að gera einhvers konar yfirlit til að sjá greinilega fjölda gáma sem eru sendir til hverrar borgar og mánaðarlega:
Til hægðarauka skulum við gera töfluna með upphafsgögnunum „snjöll“ fyrirfram með því að nota skipunina Heim - Snið sem töflu (Heima - Snið sem töflu) og gef henni nafn Afhendingar flipi Framkvæmdaaðili (Hönnun). Í framtíðinni mun þetta einfalda lífið, því. hægt verður að nota heiti töflunnar og dálka hennar beint í formúlurnar.
Aðferð 1. Auðveldasta - notaðu Power Query
Power Query er frábær öflugt tól til að hlaða og umbreyta gögnum í Excel. Þessi viðbót hefur sjálfgefið verið innbyggð í Excel síðan 2016. Ef þú ert með Excel 2010 eða 2013 geturðu hlaðið því niður og sett upp sérstaklega (alveg ókeypis).
Allt ferlið, til glöggvunar, greindi ég skref fyrir skref í eftirfarandi myndbandi:
Ef það er ekki hægt að nota Power Query, þá geturðu farið aðrar leiðir - í gegnum snúningstöflu eða formúlur.
Aðferð 2. Auka samantekt
Bætum einum dálki í viðbót við upprunalegu töfluna okkar, þar sem með einföldum formúlu reiknum við fjölda hverrar línu í töflunni:
Augljóslega þarf -1, því við erum með einnar línu haus í töflunni okkar. Ef taflan þín er ekki í upphafi blaðsins, þá geturðu notað aðeins flóknari, en alhliða formúlu sem reiknar út muninn á tölum núverandi línu og töfluhaussins:
Nú, á hefðbundinn hátt, munum við búa til snúningstöflu af viðkomandi gerð út frá gögnum okkar, en í gildisreitnum munum við sleppa reitnum Línunúmer í stað þess sem við viljum gámur:
Þar sem við erum ekki með nokkra gáma í sömu borg í sama mánuði mun samantektin okkar í raun gefa upp ekki upphæðina heldur línunúmer þeirra gáma sem við þurfum.
Að auki geturðu slökkt á stórum og undirsamtölum á flipanum Smiður – Almennar heildartölur и Undirtölur (Hönnun - Stórar heildartölur, undirsamtölur) og á sama stað skiptu yfirlitinu yfir í hentugra töfluskipulag með hnappinum Tilkynna mockup (Skýrsluútlit).
Þannig erum við nú þegar hálfnuð að niðurstöðunni: við erum með töflu þar sem, á mótum borgar og mánaðar, er línunúmer í upprunatöflunni, þar sem gámakóði sem við þurfum liggur.
Nú skulum við afrita samantektina (á sama blað eða annað) og líma það sem gildi og slá inn formúluna okkar í gildissvæðið, sem mun draga út gámakóðann með línunúmerinu sem er að finna í samantektinni:
virka IF (EF), í þessu tilviki, athugar að næsta hólf í samantektinni sé ekki tómt. Ef það er tómt, sendu þá út tóman textastreng "", þ.e. skildu reitinn eftir auðan. Ef það er ekki tómt skaltu draga úr dálknum Container upprunatöflu Afhendingar innihald klefi eftir röð númer með því að nota aðgerð INDEX (VÍSITALA).
Kannski er eini ekki mjög augljósi punkturinn hér tvöfalt orðið Container í formúlunni. Svo undarleg skrif:
Birgðir[[Ílát]:[Ílát]]
… þarf aðeins til að vísa í dálkinn Container var alger (eins og tilvísun með $ táknum fyrir venjulegar „ekki snjallar“ töflur) og rann ekki í nærliggjandi dálka þegar formúlan okkar var afrituð til hægri.
Í framtíðinni, þegar gögnum í upprunatöflunni er breytt Afhendingar, við verðum að muna að uppfæra aukayfirlitið okkar með línunúmerum með því að hægrismella á það og velja skipunina Uppfærðu og vistaðu (Endurnýja).
Aðferð 3. Formúlur
Þessi aðferð krefst þess ekki að búa til millisnúningstöflu og handvirka uppfærslu, heldur notar „þunga vopnið“ Excel – aðgerðina SUMMESLIMN (SUMIFS). Í stað þess að fletta upp línunúmerum í samantekt geturðu reiknað þau út með þessari formúlu:
Með nokkrum ytri umfangsmiklum er þetta í raun staðlað notkunartilvik fyrir sértæka samantektaraðgerðina SUMMESLIMNA sem leggur saman línunúmerin fyrir tiltekna borg og mánuði. Aftur, þar sem við erum ekki með nokkra gáma í sömu borg í sama mánuði, mun aðgerðin okkar í raun gefa upp ekki upphæðina, heldur línunúmerið sjálft. Og þá aðgerðin sem þegar er kunnugleg frá fyrri aðferð INDEX Þú getur líka dregið út gámakóða:
Auðvitað, í þessu tilfelli þarftu ekki lengur að hugsa um að uppfæra samantektina, en á stórum töflum er aðgerðin SUMMESLI getur verið áberandi hægur. Þá verður þú að slökkva á sjálfvirkri uppfærslu á formúlum, eða nota fyrstu aðferðina - snúningstöflu.
Ef útlit yfirlitsins hentar ekki mjög vel fyrir skýrsluna þína, þá geturðu dregið línunúmer úr henni inn í lokatöfluna ekki beint eins og við gerðum, heldur með því að nota aðgerðina FÁ.PIVOT.TAFLU.GÖGN (GET.PIVOT.DATA). Hvernig á að gera þetta má finna hér.
- Hvernig á að búa til skýrslu með pivot töflu
- Hvernig á að setja upp útreikninga í snúningstöflum
- Sértæk talning með SUMIFS, COUNTIFS o.fl.