VLOOKUP, HLOOKUP a INDEX-MATCH jsou slavné a běžné způsoby vyhledávání hodnot v tabulce Excel. Ale to nejsou jediné způsoby, jak v Excelu vyhledávat hodnoty. V tomto článku se naučíme, jak používat funkci OFFSET spolu s funkcí MATCH v Excelu. Ano, čtete správně, použijeme nechvalně známou funkci OFFSET aplikace Excel k vyhledání určité hodnoty v tabulce aplikace Excel.
Obecný vzorec,
| = OFFSET (StartCell, MATCH (RowLookupValue, RowLookupRange, 0), MATCH (ColLookupValue, ColLookupRange, 0)) |
Přečtěte si pozorně toto:
StartCell:Toto je počáteční buňka vyhledávací tabulky. Řekněme, že pokud chcete vyhledat v rozsahu A2: A10, pak StartCell bude A1.
RowLookupValue: Toto je vyhledávací hodnota, kterou chcete najít v řádcích podStartCell.
Řádek vyhledávání:Toto je rozsah, ve kterém chcete vyhledat RowLookupValue. Je to níže uvedený rozsah StartCell (A2: A10).
ColLookupValue: Toto je vyhledávací hodnota, kterou chcete najít ve sloupcích (záhlaví).
Rozsah ColLookup:Toto je rozsah, ve kterém chcete ColLookupValue vyhledat. Je to rozsah na pravé straně StartCell (jako B1: D1).
Takže dost teorie. Začněme příkladem.
Příklad: Vyhledejte tržby pomocí funkce OFFSET a MATCH z tabulky aplikace Excel
Zde máme ukázkovou tabulku tržeb provedených různými zaměstnanci v různých měsících.
Nyní nás náš šéf žádá, abychom poskytli tržby provedené Id 1004 v měsíci červnu. Existuje mnoho způsobů, jak to udělat, ale protože se učíme o vzorci OFFSET-MATCH, použijeme je k vyhledání požadované hodnoty.
Obecný vzorec již máme výše. Potřebujeme tyto proměnné identifikovat v této tabulce.
StartCell je zde B1. RowLookupValue je M1. RowLookupRange je B2: B1o (rozsah pod počáteční buňkou).
ColLookupValue je v buňce M2. ColLookupRange je C1: I1 (rozsah záhlaví vpravo od StartCell).
Identifikovali jsme všechny proměnné. Pojďme je dát do vzorce Excel.
Napište tento vzorec do buňky M3 a stiskněte tlačítko Enter.
| = OFFSET (B1, MATCH (M1, B2: B10,0), MATCH (M2, C1: I1,0)) |
Jakmile stisknete tlačítko Enter, výsledek získáte okamžitě. Prodej provedený ID 1004 v měsíci červnu je 177.
Jak to funguje?
Funkce OFFSET je přesunout se z dané počáteční buňky do daného řádku a sloupců a poté vrátit hodnotu z této buňky. Pokud například napíšu OFFSET (B1,1,1), funkce OFFSET přejde do buňky C2 (1 buňka dolů, 1 buňka doprava) a vrátí její hodnotu.
Tady máme vzorecOFFSET (B1, MATCH (M1, B2: B10,0), MATCH (M2, C1: I1,0)).
První funkce MATCH vrací index M1 (1004) v rozsahu B2: B10, což je 4. Nyní je vzorec,OFFSET (B1,4, MATCH (M2, C1: I1,0)).
Další funkce MATCH vrací index M2 ('Jun') v rozsahu C1: I1, který i 7. Nyní je vzorecOFFSET (B1,4,7).
Funkce OFFSET nyní jednoduše přesune 4 buňky dolů do buňky B1, která ji přenese do B5. Poté se funkce OFFSET přesune na 7 doleva na B5, čímž přejde na I5. To je vše. Funkce OFFSET vrací hodnotu z buňky I5, která 177.
Výhody této vyhledávací techniky?
To je rychlé. Jedinou výhodou této metody je, že je rychlejší než ostatní metody. Totéž lze provést pomocí funkce INDEX-MATCH nebo funkce VLOOKUP. Je ale dobré znát některé alternativy. Jednou se to může hodit.
Takže ano, lidi, takto můžete použít funkce OFFSET a MATCH k VYHLEDÁVÁNÍ hodnot v tabulkách aplikace Excel. Doufám, že to bylo dostatečně vysvětlující. Máte -li jakékoli pochybnosti týkající se tohoto článku nebo jiného tématu souvisejícího s Excelem/VBA, zeptejte se mě v sekci komentáře níže.
K vyhledání hodnoty použijte INDEX a MATCH:Vzorec INDEX-MATCH slouží k dynamickému a přesnému vyhledávání hodnoty v dané tabulce. Toto je alternativa k funkci VLOOKUP a překonává nedostatky funkce VLOOKUP.
Součet podle skupin OFFSET v řádcích a sloupcích: Funkci OFFSET lze použít k dynamickému součtu skupiny buněk. Tyto skupiny mohou být kdekoli v listu.
Jak načíst každou devátou hodnotu v rozsahu v aplikaci Excel: Pomocí funkce OFFSET aplikace Excel můžeme načíst hodnoty ze střídajících se řádků nebo sloupců. Tento vzorec využívá funkci ROW pro střídání řádků a funkci COLUMN pro střídání ve sloupcích.
Jak používat funkci OFFSET v aplikaci Excel: Funkce OFFSET je výkonná funkce aplikace Excel, kterou mnoho uživatelů podceňuje. Odborníci však vědí, jak moc je funkce OFFSET a jak můžeme tuto funkci použít k provádění magických úkolů ve vzorci aplikace Excel. Zde jsou základy funkce OFFSET.
Oblíbené články:
50 zkratek aplikace Excel pro zvýšení produktivity | Získejte rychleji svůj úkol. Těchto 50 klávesových zkratek vám umožní pracovat v Excelu ještě rychleji.
Jak používat funkci Excel VLOOKUP| Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která slouží k vyhledávání hodnot z různých rozsahů a listů.
Jak používat Excel Funkce COUNTIF| Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétních hodnot nemusíte data filtrovat. Funkce COUNTIF je nezbytná pro přípravu řídicího panelu.
Jak používat funkci SUMIF v aplikaci Excel | Toto je další základní funkce řídicího panelu. To vám pomůže shrnout hodnoty za konkrétních podmínek.