V tomto článku sa naučíme Ako vyhľadať viac inštancií hodnoty v Exceli.
Chcete vyhľadávať hodnoty pomocou rozbaľovacej možnosti?
Tu chápeme, ako môžeme vyhľadať rôzne výsledky pomocou vzorca poľa funkcie INDEX. Stačí vybrať hodnotu zo zoznamu a príslušný výsledok tam bude.
Generický vzorec
| {= INDEX (pole, SMALL (IF (lookup_value = lookup_value_range, ROW (lookup_value_range) -ROW (first cell of lookup_value_range) +1), ROW (1: 1)))}} |
Pole: Rozsah, z ktorého chcete načítať údaje.
lookup_value: Hodnota lookup_value, ktorú chcete filtrovať.
rozsah_hľadanej_hodnoty: Rozsah, v ktorom chcete filtrovať hodnotu lookup_value.
Prvá bunka v rozsahu lookup_value: ak je váš rozsah parametra lookup_value $ A $ 5: $ A $ 100, potom je to $ A $ 5.
Dôležité: Všetko by malo byť absolútne odkazované. lookup_value môže byť relatívna podľa požiadavky.
Zadajte ho ako vzorec poľa. Po napísaní vzorca z neho urobte vzorec poľa pomocou klávesov CTRL+SHIFT+ENTER.
Príklad vyhľadávania viacerých výsledkov
Tieto údaje o študentoch mám v dosahu A2: E14. V bunke G1 mám rozbaľovací zoznam hodnôt oblastí, napr. Stred, východ, sever, juh a západ. Teraz chcem, bez ohľadu na to, ktorý región mám v G1, v stĺpci H by mal byť zobrazený zoznam všetkých študentov z tohto regiónu.
Na vyhľadanie viacerých hodnôt v programe Excel identifikujeme naše premenné.
Pole: $ C $ 2: $ C $ 14
lookup_value: $ G $ 1
rozsah_hľadanej_hodnoty: $ A $ 2: $ A $ 14
Prvá bunka rozsahu lookup_value: $ A $ 2
Podľa vyššie uvedených údajov bude náš vzorec na získanie viacerých hodnôt v programe Excel:
| {= IFERROR (INDEX ($ C $ 2: $ C $ 14, SMALL (IF ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14)) -ROW ($ A $ 2) +1) , ROW (1: 1))), „No value“)} |
Toto je vzorec poľa. Po zadaní vzorca nepoužívajte iba Enter. Spoločne použite kombináciu klávesov CTRL + SHIFT + ENTER.
Teraz pochopíme, AKO TO FUNGUJE.
Aj keď vzorec môže vyzerať zložito, ale myšlienka je jednoduchá. Musíme získať číslo indexu každého výskytu hodnoty a potom načítať hodnoty pomocou funkcie INDEX programu Excel.
Hlavnou výzvou je preto získať množstvo indexových čísel lookup_value. Na získanie čísel indexov sme použili funkcie IF a ROW. Vzorec je skutočne komplexný, poďme si ho rozobrať.
Chceme získať hodnoty zo študentského stĺpca, takže naše pole pre funkciu INDEX je $ C $ 2: $ C $ 14.
Teraz musíme zadať čísla riadkov z $ A $ 2: $ A $ 14 (vyhľadávacia hodnota), v ktorom existuje hodnota G1 (povedzme zatiaľ G1 má v sebe centrálny bod).
IF ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14): Teraz táto časť vráti číslo riadku, ak je hodnota počtu buniek G1 (centrálny) v dosahu $ A $ 2: $ A $ 14 inak sa vracia NEPRAVDA. V tomto prípade sa to vráti
{2; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE; FALSE; 11; FALSE; FALSE; FALSE}.
Teraz, pretože vyššie uvedené pole obsahuje čísla riadkov z 1. riadka (1: 1) a potrebujeme riadky začínajúce z nášho poľa (A2: A14). Na to použijeme -ROW ($ A $ 2) +1 vo vzorci IF. Tým sa vráti niekoľko riadkov pred spustením nášho poľa.
V tomto prípade je to -1. Ak by to začínalo od A3, vrátilo by sa -2 a tak ďalej. Toto číslo sa odpočíta od každého čísla v poli vrátenom pomocou IF. Takže konečne IF ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1) toto sa preloží do {1; FALSE; FALSE; FALSE; FALSE;6;7; FALSE; FALSE;10; FALSE; FALSE; FALSE}.
Ďalej je toto pole obklopené funkciou SMALL. Táto funkcia vráti N -tú najmenšiu hodnotu v danom poli. Teraz tu máme MALÉ ({2; FALSE; FALSE; FALSE; FALSE;7;8; FALSE; FALSE;11; FALSE; FALSE; FALSE}, ROW (1: 1)). ROW (1: 1) vráti 1. Vyššie uvedená funkcia teda vráti 1. najmenšiu hodnotu v poli, ktorá je 2.
Keď skopírujete tento vzorec do nižšie uvedených buniek, ROW (1: 1) sa zmení na ROW (2: 2) a vráti druhú najmenšiu hodnotu v poli, ktorá je 7 a tak ďalej. To umožňuje funkcii vrátiť najskôr prvú nájdenú hodnotu. Ak však chcete najskôr získať poslednú nájdenú hodnotu, použite funkciu LARGE namiesto funkcie SMALL.
Teraz pomocou hodnôt vrátených nad funkciami funkcia INDEX ľahko vracia každú zodpovedajúcu hodnotu z rozsahu.
Presné zhody vyhľadávania môžete vykonávať aj pomocou funkcií INDEX a MATCH v programe Excel. Získajte viac informácií o tom, ako postupovať pri rozlišovaní malých a veľkých písmen pomocou funkcie INDEX & MATCH v programe Excel. Čiastkové zhody môžete vyhľadať aj pomocou zástupných znakov v programe Excel.
Dúfam, že tento článok o tom, ako vyhľadať viac inštancií hodnoty v programe Excel, je vysvetľujúci. Tu nájdete ďalšie články o výpočte hodnôt a súvisiacich vzorcoch programu Excel. Ak sa vám páčili naše blogy, zdieľajte ich so svojimi priateľmi na Facebooku. A tiež nás môžete sledovať na Twitteri a Facebooku. Radi by sme vás počuli, dajte nám vedieť, ako môžeme zlepšiť, doplniť alebo inovovať našu prácu a zlepšiť ju pre vás. Napíšte nám na emailovú stránku.
Použite VLOOKUP z dvoch alebo viacerých vyhľadávacích tabuliek | Na vyhľadávanie z viacerých tabuliek môžeme použiť prístup IFERROR. Pri vyhľadávaní z viacerých tabuliek sa chyba považuje za prepínač pre nasledujúcu tabuľku. Ďalšou metódou môže byť prístup If.
Ako vyhľadávať veľké a malé písmena v programe Excel | funkcia VLOOKUP v Exceli nerozlišuje malé a veľké písmená a vráti prvú zodpovedajúcu hodnotu zo zoznamu. INDEX-MATCH nie je výnimkou, ale môže byť upravený tak, aby rozlišoval veľké a malé písmena. Pozrime sa, ako…
Vyhľadávanie často sa vyskytujúceho textu s kritériami v programe Excel | Vyhľadávanie sa najčastejšie vyskytuje v texte v rozsahu, v ktorom používame funkciu INDEX-MATCH s funkciou REŽIM. Tu je metóda.
Populárne články:
Ako používať funkciu IF v programe Excel : Príkaz IF v programe Excel skontroluje podmienku a vráti konkrétnu hodnotu, ak je podmienka PRAVDA, alebo vráti inú konkrétnu hodnotu, ak je NEPRAVDA.
Ako používať funkciu VLOOKUP v programe Excel : Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
Ako používať funkciu SUMIF v programe Excel : Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.
Ako používať funkciu COUNTIF v programe Excel : Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnych hodnôt nie je potrebné filtrovať údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.