Funkcia MATCH v programe Microsoft Excel

Jeden z najpopulárnejších operátorov medzi používateľmi aplikácie Excel je funkcia MATCHING . Jeho úlohou je určiť číslo položky prvku v danom súbore údajov. Najväčší prínos, ktorý prináša, keď sa používa v spojení s inými operátormi. Pozrime sa, akú funkciu má MATCH a ako ju možno používať v praxi.

Používanie operátora MAP

Operátor MAP patrí do kategórie "Linky a matice" . Vyhľadáva špecifikovaný prvok v zadanom poli a vracia číslo svojej pozície v tomto rozsahu do samostatnej bunky. Dokonca aj jeho názov to naznačuje. Pri kombinácii s inými operátormi táto funkcia informuje o čísle pozície určitého prvku pre neskoršie spracovanie týchto údajov.

Syntax príkazu MATCH vyzerá takto:

=ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])

Teraz zoberte každý z týchto troch argumentov samostatne.

Hľadanie významu je prvok, ktorý by mal byť nájdený. Môže mať textovú, numerickú formu a logickú hodnotu. Tento argument môže byť tiež odkaz na bunku, ktorá obsahuje niektorú z vyššie uvedených hodnôt.

"Naskenované pole" je adresa rozsahu, v ktorom sa nachádza hodnota. Je to poloha tohto prvku v tomto poli, ktorú musí určiť operátor MAP .

Typ zhody označuje presnú zhodu, ktorú chcete vyhľadať alebo je nepresná. Tento argument môže mať tri hodnoty: "1" , "0" a "-1" . Ak je hodnota "0", operátor hľadá presnú zhodu. Ak je zadaná hodnota "1" , pri absencii presnej zhody MATCH produkuje najbližší prvok v zostupnom poradí. Ak je hodnota "-1" , potom v prípade, ak sa nenašla presná zhoda, funkcia produkuje najbližší prvok vo vzostupnom poradí. Dôležité je, ak vyhľadávanie nie je presná hodnota, ale približná, takže pole, ktoré sa má skenovať, je usporiadané vo vzostupnom poradí (typ mapovania "1" ) alebo zostupne (typ mapovania "-1" ).

Argument typu "Matching Type" je nepovinný. Môže sa to vynechať, ak to nie je potrebné. V tomto prípade je predvolená hodnota "1" . Použitie argumentu typu "Zhoda typu" má zmysel len vtedy, keď sú spracovávané číselné hodnoty, nie text.

Ak MATCH pre zadané nastavenia nemôže nájsť požadovanú položku, operátor zobrazí chybu "# N / D" v bunke.

Pri vykonávaní vyhľadávania prevádzkovateľ nerozlišuje medzi registrami znakov. Ak je v poli niekoľko presných zhody, potom MATCHING zobrazí polohu prvej v bunke.

Metóda 1: Zobrazenie umiestnenia prvku v rozsahu textových údajov

Poďme sa pozrieť na príklad najjednoduchšieho prípadu, keď používame MATCH, môžete určiť umiestnenie zadaného prvku v mape textových dát. Zistíme, akú pozíciu v rozsahu, v ktorom sa nachádzajú názvy tovaru, je slovo "cukor" .

1. Vyberte bunku, v ktorej bude výsledok spracovaný. Kliknite na ikonu "Vložiť funkciu" vedľa lišty vzorcov.
2. Spustí sa Sprievodca funkciami. Otvorte kategóriu "Úplný abecedný zoznam" alebo "Odkazy a matice" . V zozname operátorov hľadáme meno "MATCH" . Po vyhľadaní a výbere kliknite na tlačidlo "OK" v spodnej časti okna.
3. Zobrazí sa okno pre argumenty operátora MAP . Ako vidíte, v tomto okne sú tri polia podľa počtu argumentov. Musíme ich naplniť.

   Keďže potrebujeme nájsť pozíciu slova "Cukor" v rozsahu, prinášame tento názov do poľa "Hľadajte hodnotu" .

   V poli "Scanned array" je potrebné určiť súradnice rozsahu. Môžete ho zbaliť ručne, ale je ľahšie umiestniť kurzor do poľa a vybrať toto pole na hárku a súčasne držať ľavé tlačidlo myši. Potom sa jeho adresa zobrazí v okne s argumentmi.

   V treťom poli "Typ zhody" zadáme číslo "0" , pretože budeme pracovať s textovými údajmi, a preto potrebujeme presný výsledok.

   Po nastavení všetkých údajov kliknite na tlačidlo "OK" .

4. Program vypočíta a zobrazí poradové číslo pozície "Cukor" vo vybranom poli v bunke, ktorú sme určili v prvom kroku tejto inštrukcie. Číslo pozície bude "4" .

Lekcia: Sprievodca funkciami v programe Excel

Metóda 2: Automatizujte použitie operátora MATCH

Vyššie uvedené sme považovali za najprioratívnejší prípad používania operátora POSITION , ale dokonca ho možno automatizovať.

1. Pre pohodlie na hárku pridajte ďalšie dve polia: "Špecifikovaná hodnota" a "Číslo" . V poli " Priradená hodnota" smerujeme meno, ktoré musíme nájsť. Teraz nech je to "mäso" . V poli "Číslo" nastavte kurzor a prejdite na okno s argumentmi operátora rovnakým spôsobom ako je uvedené vyššie.
2. V okne funkčných argumentov v poli "Hodnota vyhľadávania" určujeme adresu bunky, na ktorej je napísané slovo "Mäso" . V poliach "Scanned array" a "Mapping type" zadáme rovnaké údaje ako v predchádzajúcej metóde - adresa rozsahu a číslo "0" . Potom kliknite na tlačidlo "OK" .
3. Po vykonaní vyššie uvedených krokov v poli "Číslo" sa zobrazí pozícia slova "Mäso" vo vybratom rozsahu. V tomto prípade sa rovná "3" .
4. Táto metóda je dobrá, pretože ak chceme poznať pozíciu akéhokoľvek iného názvu, nebudeme musieť znova formulovať alebo meniť vzorec zakaždým. V poli "Špecifikovaná hodnota" je jednoduché zadať nové vyhľadávacie slovo namiesto predchádzajúceho. Spracovanie a výstup výsledku sa potom stane automaticky.

Metóda 3: Použite operátor MAP na číselné výrazy

Teraz sa pozrime na to, ako môžete použiť MATCHING na prácu s číselnými výrazmi.

Úlohou je nájsť tovary za sumu realizácie 400 rubľov alebo najbližšie k tejto sume vo vzostupnom poradí.

1. Najskôr je potrebné zoradiť položky v stĺpci "Suma" v zostupnom poradí. Vyberte tento stĺpec a prejdite na kartu Domov . Klikneme na ikonu "Zoradiť a triediť" , ktorá je umiestnená na pásiku v bloku "Upraviť" . V zozname, ktorý sa zobrazí, zvoľte "Zoradiť z maxima na minimum" .
2. Po vykonaní triedenia vyberte bunku, na ktorej bude výsledok zobrazený, a spustite okno s argumentmi rovnakým spôsobom, ako je popísané v prvej metóde.

   V poli "Vyhľadávacia hodnota" smerujeme číslo "400" . V poli "Naskenované pole" zadajte súradnice stĺpca "Suma" . Do poľa "Typ zhody" nastavte hodnotu na hodnotu "-1" , pretože hľadáme rovnakú alebo vyššiu hodnotu ako tá, ktorú hľadáme. Po dokončení všetkých nastavení kliknite na tlačidlo "OK" .

3. Výsledok spracovania sa zobrazí v predtým špecifikovanej bunke. Toto je poloha "3" . Korešponduje s "zemiakmi" . Suma tržieb z predaja tohto produktu je v skutočnosti najbližšia k 400 a je 450 rubľov.

Podobne môžete vyhľadávať najbližšiu pozíciu na "400" v zostupnom poradí. Len na tento účel je potrebné filtrovať údaje vo vzostupnom poradí av poli "Typ zhody" funkčných argumentov nastavte hodnotu na hodnotu "1" .

Lekcia: Triedenie a filtrovanie údajov v programe Excel

Metóda 4: Používanie v spojení s inými operátormi

Je najúčinnejšie využívať túto funkciu s inými operátormi ako súčasť zložitého vzorca. Najčastejšie sa používa v spojení s funkciou INDEX . Tento argument vygeneruje obsah rozsahu určeného číslom riadku alebo stĺpca do určenej bunky. A číslovanie, ako v prípade operátora POSITION , sa vykonáva nie s ohľadom na celý list, ale iba v rámci rozsahu. Syntax tejto funkcie je:

=ИНДЕКС(массив;номер_строки;номер_столбца)

V tomto prípade, ak je pole jednorozmerné, môžete použiť iba jeden z dvoch argumentov: "Číslo riadku" alebo "Číslo stĺpca" .

Zvláštnosťou spojenia medzi funkciami INDEX a MATCH je to, že toto môže byť použité ako prvý argument, to znamená ukazovať na pozíciu riadku alebo stĺpca.

Poďme sa pozrieť na to, ako to možno urobiť v praxi, s použitím tej istej tabuľky. Máme úlohu vytlačiť názov tovaru do dodatočného poľa "tovar" , ktorého celkový výnos sa rovná 350 rublov alebo najbližšie k tejto hodnote v zostupnom poradí. Tento argument je uvedený v poli "Približná výška výnosu na hárku" .

1. Zoradiť položky vo stĺpci Výška príjmov vo vzostupnom poradí. Ak to chcete urobiť, vyberte požadovaný stĺpec a na karte "Domov" kliknite na ikonu "Zoradiť a triediť" a potom kliknite na položku "Zoradiť od minimálnej na maximálnu" v zobrazenej ponuke.
2. V poli "Produkt" vyberte bunku a pomocou funkcie "Vložiť funkciu" zavolajte sprievodcu funkciami obvyklým spôsobom.
3. V otvorenom okne sprievodcu funkcií v kategórii "Odkazy a matice" hľadáme názov "INDEX" , vyberte ho a kliknite na tlačidlo "OK" .
4. Potom sa otvorí okno, ktoré ponúka voľbu možnosti INDEX : pre pole alebo pre referenciu. Potrebujeme prvú možnosť. Preto ponecháme v tomto okne všetky predvolené nastavenia a kliknite na tlačidlo "OK" .
5. Otvorí okno s argumentmi funkcie INDEX . Do poľa "Array" zadajte adresu rozsahu, v ktorom operátor INDEX vyhľadá názov produktu. V našom prípade je to stĺpec "Názov produktu" .

   Pole "Číslo riadku" bude obsahovať vnorenú funkciu MATCH . Bude musieť byť zalomené ručne použitím syntaxe uvedenej na samom začiatku článku. Okamžite napíšte názov funkcie - "MATCH" bez úvodzoviek. Potom otvorte držiak. Prvým argumentom tohto operátora je "hodnota záujmu" . Nachádza sa na hárku v poli "Približná suma výnosov . " Zadajte súradnice bunky s číslom 350 . Vložte bodkočiarku. Druhým argumentom je "Scanned array" . MATCHING sa bude zaoberať rozsahom, v ktorom sa nájde výťažok, a vyhľadávať najviac približne 350 rubľov. Preto v tomto prípade uvádzame súradnice stĺpca "Výška príjmu" . Znova položíme bodkočiarku. Tretí argument je "Typ zhody" . Keďže hľadáme číslo zodpovedajúce danému číslu alebo najbližšiemu menšiemu, potom tu nastavíme číslicu "1" . Zatvorte zátvorky.

   Tretí argument funkcie INDEX "Číslo stĺpca" zostáva prázdny. Potom kliknite na tlačidlo "OK" .

6. Ako vidíte, funkcia INDEX s pomocou operátora POSITION v predvolenej bunke zobrazuje názov "Čaj" . V skutočnosti je suma z predaja čaju (300 rubľov) najbližšia v zostupnom poradí k súčtu 350 rubľov zo všetkých hodnôt v spracovávanej tabuľke.
7. Ak zmeníme číslo v poli "Približná suma výnosov" na druhé, obsah položky "Produkt" sa automaticky prepočíta.

Lekcia: Funkcia INDEX v programe Excel

Ako môžete vidieť, operátor MAP je veľmi výhodnou funkciou na určenie poradového čísla zadaného prvku v dátovom poli. Ale prínos z toho je výrazne zvýšený, ak sa používa v zložitých vzorcoch.

• ← Výpočet NPV v programe Microsoft Excel
• Funkcia INDEX v programe Microsoft Excel →