Ispitajte ovu brojku:
Pretpostavimo da iz toga želite izraditi izvještaj kao da ste filtrirali regiju. Odnosno, ako filtrirate na sjeveru, vidjet ćete:
Ali što ako želite istu verziju zasnovanu na formuli?
Evo rezultata koji tražite u stupcima I: K:
Jasno je da je to isto izvješće, ali ovdje nema filtriranih stavki. Ako želite novo izvješće o Istoku, bilo bi lijepo da vrijednost u G1 jednostavno promijenite u Istok:
Evo kako se to radi. Prije svega, to se ne radi pomoću VLOOKUP-a. Tako sam lagao o naslovu ove tehnike!
Stupac F prije nije bio prikazan i može se sakriti (ili premjestiti negdje drugdje, tako da ne ometa izvješće).
Ono što je prikazano u stupcu F jesu brojevi redaka gdje se G1 nalazi u stupcu A; odnosno koji redovi sadrže vrijednost "Sjever"? Ova tehnika uključuje korištenje ćeliju gore, tako da se mora početi u najmanje redom 2. To odgovara vrijednosti „Sjever” protiv stupcu A, ali umjesto cijelog stupca, koristite offset funkciju: OFFSET($A$1,F1,0,1000,1).
Budući da je F1 0, ovo je ono OFFSET(A1,0,0,1000,1)što je A1: A1000. (1000 je proizvoljno, ali dovoljno veliko za obavljanje posla - možete ga napraviti bilo koji drugi broj).
Vrijednost 2 u F2 je mjesto gdje je prvi "sjever". Također želite dodati vrijednost F1 na kraju, ali ovo je zasad nula.
"Čarolija" oživljava u ćeliji F3. Već znate da se prvi Sjever nalazi u 2. redu. Dakle, želite započeti pretraživanje dva reda ispod A1. To možete učiniti tako da odredite 2 kao drugi argument funkcije OFFSET.
Formula u F3 će se automatski upućuju na 2 koji je izračunat u staničnoj F2: Kad kopirate formulu dolje, vidjet ćete =OFFSET($A$1,F2,0,1000,1)što je OFFSET($A$1,2,0,1000,1)što je A3: A1000. Dakle, uspoređujete Sjever s novim rasponom i on pronalazi Sjever u trećoj ćeliji novog raspona, tako da MATCH daje 3.
Dodavanjem vrijednosti iz gornje ćelije F2 vidjet ćete 3 plus 2 ili 5, što je redak koji sadrži drugi sjever.
Ova se formula popunjava dovoljno daleko da se dobiju sve vrijednosti.
Tako ćete dobiti brojeve redaka u kojima se nalaze svi zapisi Sjevera.
Kako prevesti te brojeve redaka u rezultate u stupcima od I do K? Sve se to radi s jednom formulom. Unesite ovu formulu u I2: =IFERROR(INDEX(A:A,$F2),””). Kopirajte desno, a zatim kopirajte dolje.
Zašto koristiti IFERROR? Gdje je pogreška? Primijetite ćeliju F6 - sadrži # N / A (zbog čega biste željeli sakriti stupac F) jer nakon retka 15 više nema Sjevera, pa ako je stupac F pogreška, vratite prazno. Inače preuzmite vrijednost iz stupca A (i kada se popuni udesno, B & C).
$ F2 apsolutna je referenca na stupac F, tako da se pravo popunjavanja i dalje odnosi na stupac F.
Ovaj gost članak je iz Excela MVP-a Bob Umlas. To mu je jedna od najdražih tehnika iz knjige "Excel izvan okvira".
Excel izvan okvira »
