Preskúmajte tento obrázok:
Predpokladajme, že z toho chcete vytvoriť správu, akoby ste filtrovali podľa regiónu. To znamená, že keby ste filtrovali na sever, videli by ste:
Čo keby ste však chceli verziu toho istého založeného na vzorcoch?
Tu je výsledok, ktorý hľadáte v stĺpcoch I: K:
Je zrejmé, že ide o rovnaký prehľad, ale tu nie sú žiadne filtrované položky. Ak by ste chceli novú správu o východe, bolo by pekné jednoducho zmeniť hodnotu v G1 na východ:
Takto sa to deje. Najskôr sa to nerobí pomocou VLOOKUP. Klamal som teda o názve tejto techniky!
Stĺpec F sa predtým nezobrazoval a môže byť skrytý (alebo presunutý niekam inam, aby to neprekážalo prehľadu).
V stĺpci F sú zobrazené čísla riadkov, kde sa G1 nachádza v stĺpci A; to znamená, ktoré riadky obsahujú hodnotu „Sever“? Táto technika zahŕňa použitie bunku vyššie, takže musí začať aspoň v rade 2. To zodpovedá hodnote "North" proti stĺpci A, ale namiesto celého stĺpca, použiť OFFSET funkcie: OFFSET($A$1,F1,0,1000,1).
Pretože F1 je 0, toto OFFSET(A1,0,0,1000,1)je A1: A1000. (1000 je ľubovoľný, ale dostatočne veľký na to, aby vykonal danú prácu - môžete ho vytvoriť ľubovoľným iným číslom).
Hodnota 2 na F2 je tam, kde je prvý „Sever“. Tiež chcete na konci pridať hodnotu F1, ale zatiaľ je to nula.
„Kúzlo“ ožíva v bunke F3. Už viete, že prvý sever sa nachádza v rade 2. Takže chcete začať prehľadávať dva riadky pod A1. Môžete to urobiť tak, že zadáte 2 ako druhý argument funkcie OFFSET.
Vzorec v F3 automaticky ukáže na 2, ktoré sa vypočítali v bunke F2: Keď skopírujete vzorec dole, uvidíte, =OFFSET($A$1,F2,0,1000,1)ktorá z OFFSET($A$1,2,0,1000,1)nich je A3: A1000. Takže porovnávate sever s týmto novým rozsahom a nachádza sever v tretej bunke tohto nového rozsahu, takže MATCH dáva 3.
Ak pridáte späť hodnotu z bunky vyššie, F2, uvidíte 3 plus 2 alebo 5, čo je riadok, ktorý obsahuje druhý sever.
Tento vzorec je vyplnený dostatočne ďaleko na to, aby získal všetky hodnoty.
Dostanete sa tak k číslam riadkov, kde sa nachádzajú všetky severné záznamy.
Ako preložíte tieto čísla riadkov do výsledkov v stĺpcoch I až K? Všetko sa to deje pomocou jediného vzorca. Zadajte tento vzorec v I2: =IFERROR(INDEX(A:A,$F2),””). Skopírujte doprava a potom skopírujte nadol.
Prečo používať IFERROR? Kde je chyba? Všimnite si bunku F6 - obsahuje # N / A (čo je dôvod, prečo by ste chceli skryť stĺpec F), pretože po riadku 15 už nie je sever. Takže ak je v stĺpci F chyba, vráťte medzeru. V opačnom prípade vyzdvihnite hodnotu zo stĺpca A (a po správnom vyplnení B & C).
$ F2 je absolútny odkaz na stĺpec F, takže výplňové právo stále odkazuje na stĺpec F.
Tento článok je hosťom programu Excel MVP Boba Umlasa. Je to jedna z jeho obľúbených techník z knihy Excel mimo krabicu.
Excel mimo krabicu »
