V tomto článku se naučíme Jak vyhledat více instancí hodnoty v Excelu.
Hledat hodnoty pomocí rozevírací možnosti?
Zde chápeme, jak můžeme pomocí vzorce pole funkcí INDEX vyhledat různé výsledky. Stačí vybrat hodnotu ze seznamu a odpovídající výsledek tam bude.
Obecný vzorec
| {= INDEX (pole, SMALL (IF (lookup_value = lookup_value_range, ROW (lookup_value_range) -ROW (první buňka lookup_value_range) +1), ROW (1: 1)))}} |
Pole: Rozsah, odkud chcete načíst data.
lookup_value: Vaše vyhledávací_hodnota, kterou chcete filtrovat.
lookup_value_range: Rozsah, ve kterém chcete filtrovat lookup_value.
První buňka v rozsahu lookup_value: pokud je váš rozsah lookup_value $ A $ 5: $ A $ 100, pak jeho $ A $ 5.
Důležité: Všechno by mělo být absolutně odkazovaný. lookup_value může být relativní podle požadavku.
Zadejte jej jako vzorec pole. Po napsání vzorce z něj udělejte vzorec pole pomocí kláves CTRL+SHIFT+ENTER.
Příklad vyhledávání více výsledků
Mám tato studentská data v dosahu A2: E14. V buňce G1 mám rozevírací seznam hodnot oblastí, např. Střed, východ, sever, jih a západ. Nyní chci, ať už v G1 mám jakýkoli region, ve sloupci H by měl být zobrazen seznam všech studentů z této oblasti.
Abychom v Excelu vyhledali více hodnot, pojďme identifikovat naše proměnné.
Pole: $ C $ 2: $ C $ 14
lookup_value: $ G $ 1
lookup_value_range: $ A $ 2: $ A $ 14
První buňka rozsahu lookup_value: $ A $ 2
Podle výše uvedených údajů bude náš vzorec pro načtení více hodnot v aplikaci Excel:
| {= IFERROR (INDEX ($ C $ 2: $ C $ 14, SMALL (IF ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14)) -ROW ($ A $ 2) +1) , ŘÁDEK (1: 1))), „Žádná další hodnota“)} |
Toto je vzorec pole. Po zadání vzorce nepoužívejte pouze Enter. Společně použijte CTRL + SHIFT + ENTER.
Nyní pojďme pochopit, JAK TO FUNGUJE.
I když vzorec může vypadat složitě, ale myšlenka je jednoduchá. Potřebujeme získat indexové číslo každého výskytu hodnoty a poté načíst hodnoty pomocí funkce INDEX aplikace Excel.
Hlavní výzvou je proto získání řady indexových čísel lookup_value. K získání čísel indexů jsme použili funkce IF a ROW. Vzorec je vskutku komplexní, pojďme si jej rozebrat.
Chceme získat hodnoty ze studentského sloupce, takže naše pole pro funkci INDEX je $ C $ 2: $ C $ 14.
Nyní musíme zadat čísla řádků z $ A $ 2: $ A $ 14 (vyhledávací hodnota), ve které hodnota G1 existuje (prozatím řekněme G1 má v sobě střed).
IF ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14): Nyní tato část vrací číslo řádku, pokud je hodnota buňky buněčné G1 (centrální) v dosahu $ A $ 2: $ A $ 14 jinak se vrací NEPRAVDIVÉ. V tomto případě se vrátí
{2; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE; FALSE; 11; FALSE; FALSE; FALSE}.
Nyní, protože výše uvedené pole obsahuje čísla řádků z 1. řádku (1: 1) a potřebujeme řádky začínající z našeho pole (A2: A14). K tomu použijeme -ROW ($ A $ 2) +1 ve vzorci IF. Tím se vrátí několik řádků před spuštěním našeho pole.
V tomto případě je -1. Pokud by to začínalo od A3, vrátilo by se to -2 a tak dále. Toto číslo bude odečteno od každého čísla v poli vráceném IF. Takže konečně IF ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1) to se přeloží do {1; FALSE; FALSE; FALSE; FALSE;6;7; FALSE; FALSE;10; FALSE; FALSE; FALSE}.
Dále je toto pole obklopeno funkcí SMALL. Tato funkce vrací N -tu nejmenší hodnotu v daném poli. Nyní máme SMALL ({2; FALSE; FALSE; FALSE; FALSE;7;8; FALSE; FALSE;11; FALSE; FALSE; FALSE}, ŘADA (1: 1)). Řádek (1: 1) vrátí 1. Proto výše uvedená funkce vrátí 1. nejmenší hodnotu v poli, což je 2.
Když zkopírujete tento vzorec do níže uvedených buněk, ROW (1: 1) se změní na ROW (2: 2) a vrátí 2. nejmenší hodnotu v poli, což je 7 a tak dále. To umožňuje funkci vrátit nejprve nalezenou hodnotu jako první. Pokud ale chcete nejprve získat poslední nalezenou hodnotu, použijte místo funkce SMALL funkci LARGE.
Nyní pomocí hodnot vrácených nad funkcemi funkce INDEX snadno vrací každou odpovídající hodnotu z rozsahu.
Přesné shody vyhledávání můžete také provádět pomocí funkcí INDEX a MATCH v aplikaci Excel. Další informace o tom, jak provádět vyhledávání rozlišující malá a velká písmena pomocí funkce INDEX & MATCH v aplikaci Excel. Dílčí shody můžete také vyhledat pomocí zástupných znaků v aplikaci Excel.
Doufám, že tento článek o tom, jak vyhledat více instancí hodnoty v aplikaci Excel, je vysvětlující. Další články o výpočtu hodnot a souvisejících vzorcích Excelu najdete zde. Pokud se vám naše blogy líbily, sdílejte je se svými přáteli na Facebooku. A také nás můžete sledovat na Twitteru a Facebooku. Rádi bychom od vás slyšeli, dejte nám vědět, jak můžeme zlepšit, doplnit nebo inovovat naši práci a zlepšit ji pro vás. Napište nám na e -mail.
Použijte VLOOKUP ze dvou nebo více vyhledávacích tabulek | Pro vyhledávání z více tabulek můžeme použít přístup IFERROR. Pro vyhledávání z více tabulek je chyba považována za přepínač pro další tabulku. Další metodou může být přístup If.
Jak provádět vyhledávání rozlišující malá a velká písmena v aplikaci Excel | funkce VLOOKUP aplikace Excel nerozlišuje velká a malá písmena a vrátí první odpovídající hodnotu ze seznamu. INDEX-MATCH není výjimkou, ale lze jej upravit tak, aby rozlišoval velká a malá písmena. Podívejme se, jak…
Vyhledávání často se vyskytujícího textu s kritérii v aplikaci Excel | Vyhledávání se nejčastěji objevuje v textu v rozsahu, který používáme INDEX-MATCH s funkcí MODE. Zde je metoda.
Oblíbené články:
Jak používat funkci IF v aplikaci Excel : Příkaz IF v aplikaci Excel zkontroluje podmínku a vrátí konkrétní hodnotu, pokud je podmínka PRAVDA, nebo vrátí jinou konkrétní hodnotu, pokud NEPRAVDA.
Jak používat funkci VLOOKUP v Excelu : Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která se používá k vyhledávání hodnot z různých rozsahů a listů.
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.
Jak používat funkci COUNTIF v aplikaci Excel : 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.