Examinați această cifră:
Să presupunem că doriți să produceți un raport din acest lucru ca și cum ați fi filtrat pe regiune. Adică, dacă filtrați pe Nord, veți vedea:
Dar dacă ați dori o versiune bazată pe formulă a aceluiași lucru?
Iată rezultatul pe care îl căutați în coloanele I: K:
În mod clar, este același raport, dar nu există elemente filtrate aici. Dacă doriți un nou raport despre Est, ar fi bine să schimbați pur și simplu valoarea din G1 în Est:
Iată cum se face. În primul rând, nu se face folosind VLOOKUP. Așa că am mințit despre titlul acestei tehnici!
Coloana F nu a fost afișată înainte și poate fi ascunsă (sau mutată în altă parte, astfel încât să nu interfereze cu raportul).
Ceea ce se arată în coloana F este numărul rândurilor unde G1 se găsește în coloana A; adică ce rânduri conțin valoarea „Nord”? Această tehnică implică utilizarea celulei de mai sus, deci trebuie să înceapă în cel puțin rândul 2. Se potrivește cu valoarea „Nord“ împotriva coloana A, dar în loc întregii coloane, folosiți o funcție OFFSET: OFFSET($A$1,F1,0,1000,1).
Deoarece F1 este 0, acesta OFFSET(A1,0,0,1000,1)este A1: A1000. (1000 este arbitrar, dar suficient de mare pentru a face treaba - îl puteți face cu orice alt număr).
Valoarea 2 din F2 este locul unde este primul „Nord”. De asemenea, doriți să adăugați înapoi valoarea F1 la final, dar aceasta este zero, până acum.
„Magia” prinde viață în celula F3. Știți deja că primul Nord se găsește în rândul 2. Deci, doriți să începeți să căutați două rânduri sub A1. Puteți face acest lucru specificând 2 ca al doilea argument al funcției OFFSET.
Formula din F3 va indica în mod automat la 2 , care a fost calculată în F2 celula: Când copiați formula în jos, veți vedea =OFFSET($A$1,F2,0,1000,1)care este OFFSET($A$1,2,0,1000,1)care este A3: A1000. Așadar, vă asortați Nordul cu această nouă gamă și se găsește Nordul în a treia celulă a acestei noi game, așa că MATCH dă 3.
Adăugând înapoi valoarea din celula de mai sus, F2, veți vedea 3 plus 2 sau 5, care este rândul care conține al doilea Nord.
Această formulă este completată suficient de departe pentru a obține toate valorile.
Astfel veți obține numerele de rând în care se găsesc toate înregistrările din nord.
Cum traduceți aceste numere de rând în rezultatele din coloanele I până la K? Totul se face cu o singură formulă. Introduceți această formulă în I2: =IFERROR(INDEX(A:A,$F2),””). Copiați dreapta și apoi copiați.
De ce să utilizați IFERROR? Unde este eroarea? Observați celula F6 - conține # N / A (motiv pentru care ați dori să ascundeți coloana F) deoarece nu mai există nord după rândul 15. Deci, dacă coloana F este o eroare, returnați un spațiu necompletat. În caz contrar, ridicați valoarea din coloana A (și când este completată dreapta, B & C).
$ F2 este o referință absolută la coloana F, astfel încât umplerea din dreapta se referă în continuare la coloana F.
Acest articol invitat este de la Excel MVP Bob Umlas. Este una dintre tehnicile sale preferate din cartea sa, Excel Outside the Box.
Excel în afara cutiei »
