Preglejte to sliko:
Recimo, da želite iz tega pripraviti poročilo, kot da ste filtrirali regijo. To pomeni, da če filtrirate na severu, boste videli:
Kaj pa, če bi želeli različico iste stvari, ki temelji na formuli?
Tukaj je rezultat, ki ga iščete v stolpcih I: K:
Jasno je, da gre za isto poročilo, vendar tukaj ni filtriranih elementov. Če bi želeli novo poročilo o vzhodu, bi bilo lepo preprosto spremeniti vrednost v G1 na vzhod:
Evo, kako se to naredi. Prvič, to ni storjeno z uporabo VLOOKUP. Tako sem lagal o naslovu te tehnike!
Stolpec F prej ni bil prikazan in ga je mogoče skriti (ali premakniti kam drugam, da ne moti poročila).
V stolpcu F je prikazano število vrstic, kjer je G1 v stolpcu A; to pomeni, katere vrstice vsebujejo vrednost "sever"? Ta tehnika vključuje uporabo celico zgoraj, zato mora začeti vsaj v vrsti 2. To se ujema z vrednostjo "North" proti stolpcu A, ampak na celotno kolono, uporabite OFFSET funkcijo: OFFSET($A$1,F1,0,1000,1).
Ker je F1 0, je to OFFSET(A1,0,0,1000,1)A1: A1000. (1000 je poljubno, vendar dovolj veliko, da lahko opravite delo - lahko ga poljubno poljubno).
Vrednost 2 v F2 je tam, kjer je prvi "sever". Na koncu želite dodati tudi vrednost F1, vendar je to zaenkrat nič.
"Magija" zaživi v celici F3. Že veste, da je prvi sever v vrstici 2. Torej, želite začeti iskati dve vrstici pod A1. To lahko storite tako, da kot drugi argument funkcije OFFSET navedete 2.
Formula v F3 bo samodejno pokazala na 2, ki je bila izračunana v celici F2: Ko kopirate formulo navzdol, boste videli =OFFSET($A$1,F2,0,1000,1), OFFSET($A$1,2,0,1000,1)kateri je A3: A1000. Torej primerjate Sever s tem novim obsegom in Sever najde v tretji celici tega novega obsega, tako da MATCH daje 3.
Z dodajanjem vrednosti iz zgornje celice F2 boste videli 3 plus 2 ali 5, kar je vrstica, ki vsebuje drugi sever.
Ta formula je izpolnjena dovolj daleč, da dobimo vse vrednosti.
Tako boste dobili številke vrstic, v katerih so vsi zapisi Severja.
Kako prevesti te številke vrstic v rezultate v stolpcih od I do K? Vse se naredi z eno samo formulo. Vpišite to formulo v I2: =IFERROR(INDEX(A:A,$F2),””). Kopirajte desno in nato kopirajte navzdol.
Zakaj uporabljati IFERROR? Kje je napaka? Opazite celico F6 - vsebuje # N / A (zato bi želeli skriti stolpec F), ker po vrstici 15 ni več severov. Če je stolpec F napaka, vrnite prazno. V nasprotnem primeru dvignite vrednost iz stolpca A (in ko je izpolnjeno desno, B & C).
$ F2 je absolutna referenca na stolpec F, zato se desna izpolnitev še vedno nanaša na stolpec F.
Ta gostujoči članek je iz Excelovega MVP-ja Bob Umlas. To je ena njegovih najljubših tehnik iz njegove knjige, Excel Outside the Box.
Excel zunaj škatle »
