Zaokretne tablice su dobre za sve – brzo izračunavaju i fleksibilno se konfiguriraju, a dizajn se može elegantno uviti u njih, ako je potrebno. No, postoji i nekoliko nedoumica, posebice nemogućnost stvaranja sažetka, gdje područje vrijednosti ne bi trebalo sadržavati brojeve, već tekst.
Pokušajmo zaobići ovo ograničenje i osmisliti "nekoliko štaka" u sličnoj situaciji.
Pretpostavimo da naša tvrtka prevozi svoje proizvode u kontejnerima u nekoliko gradova u našoj zemlji i Kazahstanu. Kontejneri se šalju ne više od jednom mjesečno. Svaki spremnik ima alfanumerički broj. Kao početni podaci postoji standardna tablica isporuka iz koje je potrebno napraviti neku vrstu sažetka kako bi se jasno vidjeli brojevi poslanih kontejnera u svaki grad i svaki mjesec:
Radi praktičnosti, učinimo tablicu s početnim podacima "pametnom" unaprijed pomoću naredbe Početna – Format kao tablica (Početna — Formatiraj kao tablicu) i daj joj ime isporuke kartica Konstruktor (Oblikovati). U budućnosti će to pojednostaviti život, jer. bit će moguće koristiti naziv tablice i njezinih stupaca izravno u formulama.
Metoda 1. Najlakši - koristite Power Query
Power Query je super moćan alat za učitavanje i transformaciju podataka u Excelu. Ovaj je dodatak ugrađen u Excel prema zadanim postavkama od 2016. Ako imate Excel 2010 ili 2013, možete ga preuzeti i instalirati zasebno (potpuno besplatno).
Cijeli sam proces, radi jasnoće, analizirao korak po korak u sljedećem videu:
Ako nije moguće koristiti Power Query, možete ići drugim putem – kroz zaokretnu tablicu ili formule.
Metoda 2. Pomoćni sažetak
Dodajmo još jedan stupac našoj izvornoj tablici, gdje pomoću jednostavne formule izračunavamo broj svakog retka u tablici:
Očito je potreban -1, jer u našoj tablici imamo zaglavlje od jednog retka. Ako vaša tablica nije na početku lista, tada možete koristiti malo složeniju, ali univerzalnu formulu koja izračunava razliku u brojevima trenutnog retka i zaglavlja tablice:
Sada ćemo na standardan način izgraditi zaokretnu tablicu željenog tipa na temelju naših podataka, ali u polju vrijednosti ispustit ćemo polje Broj retka umjesto onoga što želimo kontejner:
Budući da nemamo više kontejnera u istom gradu u istom mjesecu, naš sažetak će zapravo dati ne količinu, već redne brojeve kontejnera koji su nam potrebni.
Osim toga, na kartici možete isključiti ukupne i međuzbrojeve Konstruktor – Opći zbrojevi и Međuzbroji (Dizajn — ukupni zbrojevi, međuzbrojevi) a na istom mjestu tipkom prebaciti sažetak u zgodniji izgled tablice Prijavi mockup (Izgled izvješća).
Dakle, već smo na pola puta do rezultata: imamo tablicu u kojoj se na sjecištu grada i mjeseca nalazi broj retka u izvornoj tablici, gdje se nalazi kod spremnika koji nam je potreban.
Sada kopirajmo sažetak (na isti ili drugi list) i zalijepimo ga kao vrijednosti, a zatim unesite našu formulu u područje vrijednosti, što će izdvojiti kod spremnika prema broju retka koji se nalazi u sažetku:
funkcija IF (AKO), u ovom slučaju, provjerava da sljedeća ćelija u sažetku nije prazna. Ako je prazna, ispišite prazan tekstualni niz “”, tj. ostavite ćeliju praznom. Ako nije prazno, izdvojite iz stupca Kontejner izvorna tablica isporuke sadržaj ćelije prema broju retka pomoću funkcije INDEKS (INDEKS).
Možda jedina ne baš očita točka ovdje je dvostruka riječ Kontejner u formuli. Tako čudan oblik pisanja:
Pribor[[Spremnik]:[Spremnik]]
… potrebno je samo za referencu stupca Kontejner bila je apsolutna (kao referenca sa znakovima $ za obične "nepametne" tablice) i nije skliznula u susjedne stupce prilikom kopiranja naše formule udesno.
U budućnosti, kada mijenjate podatke u izvornoj tablici isporuke, moramo zapamtiti da ažuriramo naš pomoćni sažetak brojevima retka desnim klikom na njega i odabirom naredbe Ažurirajte i spremite (Osvježiti).
Metoda 3. Formule
Ova metoda ne zahtijeva izradu srednje zaokretne tablice i ručno ažuriranje, već koristi Excelovo “teško oružje” – funkciju SUMMESLIMN (SUMIFS). Umjesto traženja brojeva redaka u sažetku, možete ih izračunati pomoću ove formule:
Uz nešto vanjske glomaznosti, ovo je zapravo standardni slučaj upotrebe funkcije selektivnog zbrajanja SUMMESLIMNA koji zbraja brojeve redaka za dati grad i mjesec. Opet, budući da nemamo nekoliko kontejnera u istom gradu u istom mjesecu, naša funkcija će, zapravo, dati ne iznos, već sam broj linije. A zatim funkcija već poznata iz prethodne metode INDEKS Također možete izdvojiti kodove spremnika:
Naravno, u ovom slučaju više ne morate razmišljati o ažuriranju sažetka, ali na velikim tablicama funkcija SUMMESLI može biti primjetno spor. Tada ćete morati isključiti automatsko ažuriranje formula ili koristiti prvu metodu - stožernu tablicu.
Ako izgled sažetka nije baš prikladan za vaše izvješće, tada iz njega možete izdvojiti brojeve redaka u konačnu tablicu ne izravno, kao što smo mi učinili, već pomoću funkcije GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). Kako to učiniti možete pronaći ovdje.
- Kako izraditi izvješće pomoću zaokretne tablice
- Kako postaviti izračune u zaokretnim tablicama
- Selektivno brojanje sa SUMIFS, COUNTIFS, itd.