Okretne tablice su dobre za sve – brzo se računaju i fleksibilno su konfigurirane, a dizajn se može elegantno umotati u njih, ako je potrebno. Ali postoji i nekoliko problema, posebno nemogućnost kreiranja sažetka, gdje područje vrijednosti ne bi trebalo sadržavati brojeve, već tekst.
Pokušajmo zaobići ovo ograničenje i smisliti “par štaka” u sličnoj situaciji.
Pretpostavimo da naša kompanija transportuje svoje proizvode u kontejnerima u nekoliko gradova u našoj zemlji i Kazahstanu. Kontejneri se šalju najviše jednom mjesečno. Svaki kontejner ima alfanumerički broj. Kao početni podaci, postoji standardna tabela sa spiskom isporuka, iz koje je potrebno napraviti neku vrstu rezimea kako biste jasno vidjeli koji su brojevi kontejnera poslati u svaki grad i svaki mjesec:
Radi praktičnosti, napravimo tabelu sa početnim podacima unapred „pametnom“ koristeći naredbu Početna – Formatirajte kao tabelu (Početna — Format kao tabela) i daj joj ime Isporuke tabulator konstruktor (Dizajn). U budućnosti će to pojednostaviti život, jer. biće moguće koristiti ime tabele i njenih kolona direktno u formulama.
Metod 1. Najlakši – koristite Power Query
Power Query je super moćan alat za učitavanje i transformaciju podataka u Excel. Ovaj dodatak je podrazumevano ugrađen u Excel od 2016. Ako imate Excel 2010 ili 2013, možete ga zasebno preuzeti i instalirati (potpuno besplatno).
Cijeli proces, radi jasnoće, analizirao sam korak po korak u sljedećem videu:
Ako nije moguće koristiti Power Query, onda možete ići na druge načine – kroz zaokretnu tablicu ili formule.
Metoda 2. Pomoćni sažetak
Dodajmo još jednu kolonu našoj originalnoj tablici, gdje pomoću jednostavne formule izračunavamo broj svakog reda u tabeli:
Očigledno je potrebno -1, jer imamo zaglavlje u jednom redu u našoj tabeli. Ako vaša tabela nije na početku lista, onda možete koristiti malo složeniju, ali univerzalniju formulu koja izračunava razliku u brojevima trenutnog reda i zaglavlja tabele:
Sada ćemo, na standardan način, napraviti pivot tabelu željenog tipa na osnovu naših podataka, ali u polju vrednosti ćemo ispustiti polje Broj linije umesto 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ć brojeve redova kontejnera koji su nam potrebni.
Osim toga, možete isključiti glavne i međuzbrojeve na kartici Konstruktor – Opšti zbrojevi и Međuzbrojevi (Dizajn — ukupni zbrojevi, međuzbirovi) i na istom mjestu prebacite sažetak na pogodniji izgled tabele pomoću dugmeta Model izvještaja (Izgled izvještaja).
Dakle, već smo na pola puta do rezultata: imamo tabelu u kojoj se, na raskrsnici grada i mjeseca, nalazi broj reda u izvornoj tabeli, gdje leži kod kontejnera 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, koje će izvući kod kontejnera prema broju reda 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 prazno, onda ispišite prazan tekstualni niz “”, tj. ostavite ćeliju praznom. Ako nije prazan, izvucite iz kolone kontejner izvorna tabela Isporuke sadržaj ćelije prema broju reda koristeći funkciju INDEX (INDEX).
Možda je jedina ne baš očigledna poenta ovdje dvostruka riječ kontejner u formuli. Tako čudan oblik pisanja:
zalihe[[kontejner]:[kontejner]]
… je potrebno samo za referencu na kolonu kontejner je bila apsolutna (kao referenca sa znakovima $ za obične „nepametne“ tabele) i nije skliznula na susedne kolone prilikom kopiranja naše formule udesno.
U budućnosti, prilikom promjene podataka u izvornoj tabeli Isporuke, moramo zapamtiti da ažuriramo naš pomoćni sažetak s brojevima redova desnim klikom na njega i odabirom naredbe Ažurirajte i sačuvajte (Osvježiti).
Metoda 3. Formule
Ova metoda ne zahtijeva kreiranje srednje zaokretne tablice i ručno ažuriranje, već koristi Excelovo “teško oružje” – funkciju SUMMESLIMN (SUMIFS). Umjesto traženja brojeva redova u sažetku, možete ih izračunati pomoću ove formule:
Uz određenu vanjsku glomaznost, u stvari, ovo je standardni slučaj upotrebe funkcije selektivnog zbrajanja SUMMESLIMNA koji zbraja brojeve redova za dati grad i mjesec. Opet, pošto nemamo više kontejnera u istom gradu u istom mjesecu, naša funkcija će, u stvari, izdati ne iznos, već sam broj linije. A zatim funkcija već poznata iz prethodne metode INDEX Također možete izdvojiti kodove kontejnera:
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 – pivot tablicu.
Ako izgled sažetka nije baš prikladan za vaš izvještaj, onda možete izdvojiti brojeve redova iz njega u konačnu tabelu ne direktno, kao što smo to učinili, već pomoću funkcije GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). Kako to učiniti možete pronaći ovdje.
- Kako kreirati izvještaj koristeći pivot tablicu
- Kako postaviti kalkulacije u zaokretnim tabelama
- Selektivno brojanje sa SUMIFS, COUNTIFS, itd.