Vrtilne tabele so dobre za vsakogar – izračunajo hitro in so prilagodljive, po potrebi pa je mogoče vanje elegantno zaviti dizajn. Nekaj pa je tudi vmes, predvsem nezmožnost ustvarjanja povzetka, kjer območje vrednosti ne sme vsebovati številk, ampak besedilo.
Poskusimo se izogniti tej omejitvi in si omislimo "nekaj bergel" v podobni situaciji.
Recimo, da naše podjetje prevaža svoje izdelke v zabojnikih v več mest v naši državi in Kazahstanu. Kontejnerji se pošiljajo največ enkrat na mesec. Vsaka posoda ima alfanumerično številko. Kot začetni podatki obstaja standardna tabela s seznamom dobav, iz katere morate narediti nekakšen povzetek, da boste jasno videli število zabojnikov, poslanih v vsako mesto in vsak mesec:
Za udobje naredimo tabelo z začetnimi podatki "pametno" vnaprej z ukazom Domov – Oblikuj kot tabelo (Domov — Oblikuj kot tabelo) in ji daj ime Dobave tab Konstruktor (Oblikovanje). V prihodnosti bo to življenje poenostavilo, saj. ime tabele in njenih stolpcev bo mogoče uporabiti neposredno v formulah.
Metoda 1. Najlažji - uporabite Power Query
Power Query je izjemno zmogljivo orodje za nalaganje in preoblikovanje podatkov v Excelu. Ta dodatek je privzeto vgrajen v Excel od leta 2016. Če imate Excel 2010 ali 2013, ga lahko prenesete in namestite ločeno (popolnoma brezplačno).
Celoten postopek sem zaradi jasnosti korak za korakom analiziral v naslednjem videu:
Če ni mogoče uporabiti Power Queryja, lahko greste drugače – skozi vrtilno tabelo ali formule.
Metoda 2. Pomožni povzetek
V prvotno tabelo dodamo še en stolpec, kjer s preprosto formulo izračunamo število vsake vrstice v tabeli:
Očitno je -1 potreben, ker imamo v tabeli enovrstično glavo. Če vaša tabela ni na začetku lista, lahko uporabite nekoliko bolj zapleteno, a univerzalno formulo, ki izračuna razliko v številkah trenutne vrstice in glave tabele:
Zdaj bomo na standarden način zgradili vrtilno tabelo želenega tipa na podlagi naših podatkov, vendar bomo v polju vrednosti izpustili polje Številka vrstice namesto tega, kar želimo posoda:
Ker v istem mesecu nimamo več zabojnikov v istem mestu, naš povzetek dejansko ne bo dal količine, temveč številke vrstic zabojnikov, ki jih potrebujemo.
Poleg tega lahko na zavihku izklopite glavne in delne vsote Konstruktor – Splošni seštevki и Vmesne seštevke (Zasnova — skupne vsote, vmesne vsote) in na istem mestu z gumbom preklopite povzetek na bolj priročno postavitev tabele Prijavi maketo (Postavitev poročila).
Tako smo že na pol poti do rezultata: imamo tabelo, kjer je na presečišču mesta in meseca številka vrstice v izvorni tabeli, kjer leži koda vsebnika, ki jo potrebujemo.
Zdaj pa kopirajmo povzetek (na isti ali drug list) in ga prilepimo kot vrednosti, nato pa vnesite našo formulo v območje vrednosti, ki bo izvleklo kodo vsebnika s številko vrstice, ki jo najdete v povzetku:
funkcija IF (ČE), v tem primeru preveri, ali naslednja celica v povzetku ni prazna. Če je prazna, izpiši prazen besedilni niz “”, tj. pusti celico prazno. Če ni prazno, izvleči iz stolpca Posoda izvorna tabela Dobave vsebino celice po številki vrstice z uporabo funkcije INDEX (KAZALO).
Morda je edina ne preveč očitna točka dvojna beseda Posoda v formuli. Tako čudna oblika pisanja:
potrebščine[[Posoda]:[Posoda]]
… je potreben samo za sklicevanje na stolpec Posoda je bil absoluten (kot sklic z znaki $ za navadne »nepametne« tabele) in ni zdrsnil v sosednje stolpce pri kopiranju naše formule na desno.
V prihodnje ob spreminjanju podatkov v izvorni tabeli Dobave, ne smemo pozabiti posodobiti našega pomožnega povzetka s številkami vrstic, tako da z desno miškino tipko kliknemo nanj in izberemo ukaz Posodobi in shrani (Osveži).
Metoda 3. Formule
Ta metoda ne zahteva ustvarjanja vmesne vrtilne tabele in ročnega posodabljanja, temveč uporablja Excelovo »težko orožje« – funkcijo SUMMESLIMN (SUMIFS). Namesto da iščete številke vrstic v povzetku, jih lahko izračunate s to formulo:
Z nekaj zunanje obsežnosti je to pravzaprav standardni primer uporabe funkcije selektivnega seštevanja SUMMESLIMNA, ki sešteje številke vrstic za dano mesto in mesec. Še enkrat, ker nimamo več zabojnikov v istem mestu v istem mesecu, naša funkcija dejansko ne bo izdala zneska, ampak samo številko vrstice. In potem funkcija, ki je že znana iz prejšnje metode INDEX Izvlečete lahko tudi kode vsebnikov:
Seveda vam v tem primeru ni treba več razmišljati o posodabljanju povzetka, ampak na velikih tabelah funkcija SUMMESLI je lahko opazno počasen. Potem boste morali izključiti samodejno posodabljanje formul ali uporabiti prvo metodo - vrtilno tabelo.
Če videz povzetka ni zelo primeren za vaše poročilo, lahko številke vrstic izvlečete iz njega v končno tabelo ne neposredno, kot smo storili mi, ampak s funkcijo GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). Kako to storite, lahko najdete tukaj.
- Kako ustvariti poročilo z uporabo vrtilne tabele
- Kako nastaviti izračune v vrtilnih tabelah
- Selektivno štetje s SUMIFS, COUNTIFS itd.