Prohlédněte si tento obrázek:
Předpokládejme, že z toho chcete vytvořit zprávu, jako kdybyste filtrovali oblast. To znamená, že pokud filtrujete na sever, uvidíte:
Ale co kdybyste chtěli stejnou verzi založenou na vzorcích?
Zde je výsledek, který hledáte ve sloupcích I: K:
Je zřejmé, že jde o stejnou zprávu, ale zde nejsou žádné filtrované položky. Pokud byste chtěli novou zprávu o východu, bylo by hezké jednoduše změnit hodnotu v G1 na východ:
Takto se to dělá. Nejprve se to nedělá pomocí VLOOKUP. Takže jsem lhal o názvu této techniky!
Sloupec F se předtím nezobrazoval a lze jej skrýt (nebo přesunout někam jinam, aby to nezasahovalo do zprávy).
Ve sloupci F jsou zobrazena čísla řádků, kde se G1 nachází ve sloupci A; to znamená, jaké řádky obsahují hodnotu „Sever“? Tato technika zahrnuje použití buňku výše, takže musí začít alespoň v řadě 2. To odpovídá hodnotě „North“ proti sloupci A, ale místo celého sloupce, použít OFFSET funkce: OFFSET($A$1,F1,0,1000,1).
Protože F1 je 0, je OFFSET(A1,0,0,1000,1)to A1: A1000. (1000 je libovolné, ale dostatečně velké na to, aby to zvládlo - můžete si z něj vytvořit jakékoli jiné číslo).
Hodnota 2 v F2 je tam, kde je první „sever“. Na konci také chcete přidat hodnotu F1, ale zatím je to nula.
„Kouzlo“ ožívá v buňce F3. Již víte, že první sever se nachází v řádku 2. Chcete tedy začít hledat dva řádky pod A1. Můžete to udělat tak, že zadáte 2 jako druhý argument funkce OFFSET.
Vzorec v F3 bude automaticky ukazovat na 2, která byla vypočítána v buňce F2: Když zkopírujete vzorec dolů, uvidíte, =OFFSET($A$1,F2,0,1000,1)která z OFFSET($A$1,2,0,1000,1)nich je A3: A1000. Takže porovnáváte sever s tímto novým rozsahem a najde sever ve třetí buňce tohoto nového rozsahu, takže MATCH dává 3.
Když přidáte zpět hodnotu z buňky výše, F2, uvidíte 3 plus 2 nebo 5, což je řádek, který obsahuje druhý sever.
Tento vzorec je vyplněn dostatečně daleko, aby získal všechny hodnoty.
Získáte tak čísla řádků, kde se nacházejí všechny záznamy severu.
Jak přeložíte tato čísla řádků na výsledky ve sloupcích I až K? Vše se provádí pomocí jediného vzorce. Zadejte tento vzorec v I2: =IFERROR(INDEX(A:A,$F2),””). Zkopírujte doprava a poté zkopírujte dolů.
Proč používat IFERROR? Kde je chyba? Všimněte si buňky F6 - obsahuje # N / A (což je důvod, proč byste chtěli skrýt sloupec F), protože po řádku 15 již nejsou žádné severy. Pokud je tedy sloupec F chybou, vraťte mezeru. Jinak vyzvedněte hodnotu ze sloupce A (a po správném vyplnění B & C).
$ F2 je absolutní odkaz na sloupec F, takže výplňové právo stále odkazuje na sloupec F.
Tento článek pro hosta je od Excel MVP Boba Umlasa. Je to jedna z jeho oblíbených technik z jeho knihy Excel Outside the Box.
Excel mimo krabici »
