logo

TechCodeview

Funkcia INDEX a MATCH v Exceli

INDEX-MATCH sa stal populárnejším nástrojom pre Excel, pretože rieši obmedzenie funkcie VLOOKUP a jeho používanie je jednoduchšie. Funkcia INDEX-MATCH v Exceli má oproti funkcii VLOOKUP niekoľko výhod:

  1. INDEX a MATCH sú flexibilnejšie a rýchlejšie ako Vlookup
  2. Je možné vykonávať horizontálne vyhľadávanie, vertikálne vyhľadávanie, 2-smerné vyhľadávanie, vyhľadávanie vľavo, vyhľadávanie s rozlišovaním veľkých a malých písmen a dokonca vyhľadávanie na základe viacerých kritérií.
  3. V triedených údajoch je INDEX-MATCH o 30 % rýchlejší ako VLOOKUP. To znamená, že vo väčšom súbore údajov o 30 % rýchlejšie dáva väčší zmysel.

Začnime podrobnými pojmami každého INDEXU a ZÁPASU.



Funkcia INDEX

Funkcia INDEX v Exceli je veľmi výkonný a zároveň flexibilný nástroj, ktorý načíta hodnotu na danom mieste v rozsahu. Iným slovom, vracia obsah bunky určený posunom riadkov a stĺpcov.

Syntax: 

=INDEX(reference, [row], [column])>

Parametre:



    referencia: Pole buniek, do ktorých sa má odsadiť. Môže to byť jeden rozsah alebo celá množina údajov v tabuľke údajov. riadok [voliteľné]: Počet riadkov odsadenia. Znamená to, že ak zvolíme referenčný rozsah tabuľky ako A1:A5, potom je bunka/obsah, ktorý chceme extrahovať, v akej vertikálnej vzdialenosti. Tu bude pre riadok A1 1, pre riadok A2 = 2 atď. Ak dáme riadok = 4, vytiahne A4. Keďže riadok je voliteľný, ak nešpecifikujeme žiadne číslo riadku, extrahuje celé riadky v referenčnom rozsahu. To je v tomto prípade A1 až A5. stĺpec [voliteľné]: Počet odsadených stĺpcov. Znamená to, že ak zvolíme referenčný rozsah tabuľky ako A1:B5, potom bunka/obsah, ktorý chceme extrahovať, je v horizontálnej vzdialenosti. Tu pre A1 riadok bude 1 a stĺpec bude 1, pre B1 riadok bude 1, ale stĺpec bude 2 podobne pre A2 riadok = 2 stĺpec = 1, pre B2 riadok = 2 stĺpec = 2 atď. Ak dáme riadok = 5 a stĺpec 2, vytiahne B5. Keďže stĺpec je voliteľný, ak neuvedieme žiadny riadok č. potom vytiahne celý stĺpec v referenčnom rozsahu. Napríklad, ak dáme riadok = 2 a stĺpec ako prázdny, potom sa to extrahuje (A2:B2). Ak nešpecifikujeme riadok aj stĺpec, extrahuje sa celá referenčná tabuľka (A1:B5).

Referenčná tabuľka: Nasledujúca tabuľka bude použitá ako referenčná tabuľka pre všetky príklady funkcie INDEX. Prvá bunka je na B3 (FOOD) a posledná diagonálna bunka je na F10 (180).

Referenčná tabuľka

Príklady: Nižšie sú uvedené niektoré príklady funkcií indexu.



Prípad 1: Nie sú uvedené žiadne riadky a stĺpce.

Vstupný príkaz: =INDEX(B3:C10)

Prípad 1

Prípad 2: Spomínajú sa iba riadky.

Vstupný príkaz: =INDEX(B3:C10;2)

Prípad 2

Prípad 3: Spomínajú sa riadky aj stĺpce.

Vstupný príkaz: =INDEX(B3:D10;4;2)

Prípad 3

Prípad 4: Spomínajú sa iba stĺpce.

Vstupný príkaz: =INDEX(B3 : D10 , , 2)

Prípad 4

Problém s funkciou INDEX: Problém funkcie INDEX je v tom, že je potrebné špecifikovať riadky a stĺpce pre údaje, ktoré hľadáme. Predpokladajme, že máme čo do činenia so súborom údajov strojového učenia s 10 000 riadkami a stĺpcami, potom bude veľmi ťažké vyhľadať a extrahovať údaje, ktoré hľadáme. Tu prichádza koncept funkcie Match, ktorá identifikuje riadky a stĺpce na základe určitej podmienky.

Funkcia MATCH

Získa pozíciu položky/hodnoty v rozsahu. Ide o menej prepracovanú verziu VLOOKUP alebo HLOOKUP, ktorá vracia iba informácie o polohe a nie skutočné údaje. MATCH nerozlišuje veľké a malé písmená a nezáleží na tom, či je rozsah horizontálny alebo vertikálny.

Syntax: 

=MATCH(search_key, range, [search_type])>

Parametre:

    search_key: hodnota, ktorú treba vyhľadať. Napríklad 42, Cats alebo I24. rozsah: Jednorozmerné pole, ktoré sa má prehľadávať. Môže to byť buď jeden riadok alebo jeden stĺpec. napr.->A1:A10 , A2:D2 atď. search_type [voliteľné]: Metóda vyhľadávania. = 1 (predvolené) nájde najväčšiu hodnotu menšiu alebo rovnú kľúču vyhľadávania, keď je rozsah zoradený vzostupne.
    • = 0 nájde presnú hodnotu, keď rozsah nie je zoradený.
    • = -1 nájde najmenšiu hodnotu väčšiu alebo rovnú kľúču vyhľadávania, keď je rozsah zoradený v zostupnom poradí.

Číslo riadka alebo číslo stĺpca je možné nájsť pomocou funkcie zhody a môžete ho použiť vo funkcii indexu, takže ak existujú nejaké podrobnosti o položke, všetky informácie možno extrahovať o položke nájdením riadka/stĺpca položky pomocou zhody potom ho vnoríte do funkcie indexu.

Referenčná tabuľka: Nasledujúca tabuľka bude použitá ako referenčná tabuľka pre všetky príklady funkcie MATCH. Prvá bunka je na úrovni B3 (jedlo) a posledná bunka na diagonále je na hodnote F10 (180)

Funkcia MATCH referenčnej tabuľky

Príklady: Nižšie je uvedených niekoľko príkladov funkcie MATCH –

Prípad 1: Typ vyhľadávania 0, to znamená presná zhoda.

Vstupný príkaz: =MATCH(Južná India,C3:C10;0)

Prípad 1 ZÁPAS

Prípad 2: Typ vyhľadávania 1 (predvolené).

Vstupný príkaz: =MATCH(Južná India,C3:C10)

Prípad 2 ZÁPAS

binárny strom java


Prípad 3: Typ vyhľadávania -1.

Vstupný príkaz: =MATCH(Južná India,C3:C10;-1)

Prípad 3 ZÁPAS

INDEX-MATCH Spolu

V predchádzajúcich príkladoch boli statické hodnoty riadkov a stĺpcov poskytnuté vo funkcii INDEX Predpokladajme, že neexistujú žiadne predchádzajúce znalosti o pozíciách riadkov a stĺpcov, potom je možné polohu riadkov a stĺpcov poskytnúť pomocou funkcie MATCH. Toto je dynamický spôsob vyhľadávania a získavania hodnoty.

Syntax: 

 =INDEX(Reference Table , [Match(SearchKey,Range,Type)/StaticRowPosition],  [Match(SearchKey,Range,Type)/StaticColumnPosition])>

Referenčná tabuľka: Použije sa nasledujúca referenčná tabuľka. Prvá bunka je na úrovni B3 (jedlo) a posledná bunka na diagonále je na hodnote F10 (180)

Referenčná tabuľka INDEX-MATCH

Príklad: Povedzme, že úlohou je nájsť cenu Masala Dosa. Je známe, že stĺpec 3 predstavuje náklady na položky, ale pozícia riadku Masala Dosa nie je známa. Problém možno rozdeliť do dvoch krokov -

Krok 1: Nájdite polohu Masala Dosa pomocou vzorca:

 =MATCH('Masala Dosa',B3:B10,0)>

Tu B3:B10 predstavuje stĺpec jedlo a 0 znamená presnú zhodu. Vráti číslo riadku Masala Dosa.

Krok 2: Zistite cenu Masala Dosa. Na zistenie ceny Masala Dosa použite funkciu INDEX. Nahradením vyššie uvedeného dotazu funkcie MATCH vo funkcii INDEX na mieste, kde sa vyžaduje presná poloha Masala Dosa a číslo stĺpca nákladov je 3, čo je už známe.

=INDEX(B3:F10, MATCH('Masala Dosa', B3:B10 , 0) ,3)>

INDEX-MATCH Spolu

Dva spôsoby vyhľadávania s INDEX-MATCH spolu

V predchádzajúcom príklade bola pozícia stĺpca atribútu Cena pevne zakódovaná. Takže to nebolo úplne dynamické.

Prípad 1: Predpokladajme, že neexistujú žiadne znalosti o čísle stĺpca nákladov, potom ho možno získať pomocou vzorca:

 =MATCH('Cost',B3:F3,0)>

Tu B3:F3 predstavuje stĺpec hlavičky.

Prípad 2: Keď sa riadok, ako aj hodnota stĺpca, poskytujú prostredníctvom funkcie MATCH (bez uvedenia statickej hodnoty), nazýva sa to obojsmerné vyhľadávanie. Dá sa to dosiahnuť pomocou vzorca:

 =INDEX(B3:F10, MATCH('Masala Dosa',B3:B10, 0) , MATCH('Cost' ,B3:F3 ,0))>

Obojsmerné vyhľadávanie

Ľavé vyhľadávanie

Jednou z kľúčových výhod INDEX a MATCH oproti funkcii VLOOKUP je schopnosť vykonávať vyhľadávanie vľavo. To znamená, že je možné extrahovať pozíciu riadku položky pomocou ľubovoľného atribútu vpravo a hodnotu iného atribútu vľavo možno extrahovať.

Povedzme napríklad, že si kúpite jedlo, ktorého cena by mala byť 140 Rs. Nepriamo hovoríme kúpiť Biryani. V tomto príklade je známa cena Rs 140/-, je potrebné extrahovať jedlo. Pretože stĺpec Náklady je umiestnený napravo od stĺpca Jedlo. Ak použijete funkciu VLOOKUP, nebude možné vyhľadávať na ľavej strane stĺpca Cena. Preto pomocou funkcie VLOOKUP nie je možné získať názov jedla.

Na prekonanie tejto nevýhody je možné použiť funkciu INDEX-MATCH Ľavé vyhľadávanie.
Krok 1: Prvý extrahujte pozíciu riadku nákladov 140 Rs pomocou vzorca:

 =MATCH(140, D3:D10,0)>

Tu D3: D10 predstavuje stĺpec Náklady, v ktorom sa hľadá číslo riadku Cena 140 Rs.

Krok 2: Po získaní čísla riadku je ďalším krokom použitie funkcie INDEX na extrahovanie názvu jedla pomocou vzorca:

 =INDEX(B3:B10, MATCH(140, D3:D10,0))>

Tu B3:B10 predstavuje stĺpec potravín a 140 sú náklady na potravinovú položku.

Ľavé vyhľadávanie

Rozlišovanie malých a veľkých písmen

Samotná funkcia MATCH nerozlišuje veľké a malé písmená. To znamená, že ak existuje názov jedla DHOKLA a funkcia MATCH sa používa s nasledujúcim hľadaným slovom:

  1. Dhokla
  2. dhokla
  3. DhOkLA

Všetky vrátia pozíciu riadku DHOKLA. Funkciu EXACT však možno použiť s INDEX a MATCH na vykonanie vyhľadávania, ktoré rešpektuje veľké a malé písmená.

Presná funkcia: Funkcia Excel EXACT porovnáva dva textové reťazce, pričom berie do úvahy veľké a malé písmená, a vráti TRUE, ak sú rovnaké, a FALSE, ak nie. EXACT rozlišuje veľké a malé písmená.

Príklady:

    EXACT(DHOKLA,DHOKLA): Toto vráti hodnotu True. PRESNÉ (DHOKLA,Dhokla): Toto vráti False. PRESNE (DHOKLA,dhokla): Toto vráti False. PRESNE (DHOKLA,DhOkLA): Toto vráti False.

Príklad: Povedzme, že úlohou je vyhľadať typ jedla Dhokla, ale s rozlišovaním veľkých a malých písmen. To sa dá urobiť pomocou vzorca -

 =INDEX(C3:C10, MATCH(TRUE , EXACT('Dhokla', B3:B10) ,0))>

Tu funkcia EXACT vráti True, ak sa hodnota v stĺpci B3:B10 zhoduje s Dhokla s rovnakým prípadom, inak vráti False. Teraz sa funkcia MATCH použije v stĺpci B3:B10 a vyhľadá riadok s presnou hodnotou TRUE. Potom funkcia INDEX získa hodnotu stĺpca C3:C10 (stĺpec typu jedla) v riadku vrátenom funkciou MATCH.

Rozlišovanie malých a veľkých písmen

Vyhľadávanie viacerých kritérií

Jedným z najzložitejších problémov v Exceli je vyhľadávanie na základe viacerých kritérií. Inými slovami, vyhľadávanie, ktoré sa zhoduje vo viacerých stĺpcoch súčasne. V nižšie uvedenom príklade sa funkcie INDEX a MATCH a booleovská logika používajú na porovnávanie v 3 stĺpcoch-

  1. Jedlo.
  2. náklady.
  3. Množstvo.

Na extrakciu celkových nákladov.

Príklad: Povedzme, že úlohou je vypočítať celkové náklady na cestoviny

    Jedlo: Cestoviny. Cena: 60. Množstvo: 1.

V tomto príklade teda existujú tri kritériá na vykonanie zhody. Nižšie sú uvedené kroky pre vyhľadávanie na základe viacerých kritérií –

Krok 1: Najprv porovnajte potravinový stĺpec (B3:B10) s cestovinami podľa vzorca:

 'PASTA' = B3:B10>

Týmto sa hodnoty B3:B10 (stĺpec potravín) skonvertujú na boolovské hodnoty. To je pravda, kde jedlo je cestoviny, inak nepravda.

Krok 2: Potom priraďte kritériá nákladov nasledujúcim spôsobom:

 60 = D3:D10>

Toto nahradí hodnoty D3:D10 (stĺpec nákladov) ako boolovské. To je pravda, kde cena = 60, inak je nepravda.

Krok 3: Ďalším krokom je zhoda s tretími kritériami, ktorými sú Množstvo = 1, a to nasledujúcim spôsobom:

 1 = E3:E10>

Toto nahradí stĺpec E3:E10 (stĺpec Množstvo) ako Pravda, kde Množstvo = 1, inak bude Nepravda.

Krok 4: Vynásobte výsledok prvého, druhého a tretieho kritéria. Toto bude priesečník všetkých podmienok a prevedie sa boolovská hodnota True / False ako 1/0.

Krok 5: Teraz bude výsledkom stĺpec s 0 a 1. Tu použite funkciu MATCH na zistenie počtu riadkov stĺpcov, ktoré obsahujú 1. Pretože ak má stĺpec hodnotu 1, znamená to, že spĺňa všetky tri kritériá.

Krok 6: Po získaní čísla riadku použite funkciu INDEX, aby ste získali celkové náklady na daný riadok.

 =INDEX(F3:F10, MATCH(1, ('Pasta'=B3:B10) * (60=D3:D10) * (1=E3:E10) , 0 ))>

Tu F3:F10 predstavuje stĺpec celkových nákladov.