Funkce MATCH v aplikaci Microsoft Excel

Jedním z nejoblíbenějších operátorů mezi uživateli aplikace Excel je funkce MATCHING . Jeho úkolem je určit číslo položky prvku v daném datovém souboru. Největší přínos, který přináší, když je používán ve spojení s jinými operátory. Podívejme se, jaká je funkce MATCH a jak může být použita v praxi.

Použití operátora MAP

Operátor MAP patří do kategorie "Odkazy a matice" . Hledá zadaný prvek ve specifikovaném poli a vrací číslo své pozice v tomto rozsahu do samostatné buňky. Dokonce i jeho název to naznačuje. Také v kombinaci s jinými operátory informuje tuto funkci o čísle pozice určitého prvku pro pozdější zpracování těchto dat.

Syntaxe příkazu MATCH vypadá takto:

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

Nyní zvažte každý z těchto tří argumentů zvlášť.

"Hledání významu" je prvek, který by měl být nalezen. Může mít textovou, číselnou podobu a také logickou hodnotu. Tento argument může být také odkaz na buňku, která obsahuje některou z výše uvedených hodnot.

"Skenované pole" je adresa rozsahu, ve kterém je hodnota umístěna. Poloha tohoto prvku v tomto poli musí stanovit provozovatel MAP .

"Typ shody " označuje přesnou shodu, kterou chcete vyhledat, nebo je nepřesná. Tento argument může mít tři hodnoty: "1" , "0" a "-1" . Pokud je hodnota "0", operátor hledá pouze přesnou shodu. Je-li zadána hodnota "1" , při neexistenci přesné shody vytvoří MATCH nejbližší prvek v sestupném pořadí. Pokud je hodnota "-1" , pak v případě, že není nalezena přesná shoda, funkce vytvoří nejbližší prvek ve vzestupném pořadí. Je důležité, pokud vyhledávání není přesná, ale přibližná, takže pole, které má být skenováno, je uspořádáno ve vzestupném pořadí (typ mapování "1" ) nebo sestupně (typ mapování "-1" ).

Argument "Typ shody" je nepovinný. To může být vynecháno, pokud to není potřeba. V tomto případě je výchozí hodnota "1" . Použití argumentu "Typ shody" má smysl pouze v případě zpracování číselných hodnot, nikoli textu.

Pokud MATCH pro daná nastavení nemůže najít požadovanou položku, operátor zobrazí v buňce chybu "# N / D" .

Při provádění vyhledávání operátor nerozlišuje mezi registry znaků. Pokud je v poli několik přesných shod, pak MATCHING zobrazí polohu první v buňce.

Metoda 1: Zobrazte místo prvku v rozsahu textových dat

Podívejme se na příklad nejjednoduššího případu, když používáme MATCH, můžete určit umístění daného prvku v poli textových dat. Zjistíme, jaké pozice v rozsahu, ve kterém jsou umístěny názvy zboží, je slovo "cukr" .

1. Vyberte buňku, ve které bude výstup zpracován. Klikněte na ikonu "Vložit funkci" vedle panelu vzorců.
2. Spustí se Průvodce funkcí . Otevřete kategorii "Úplný abecední seznam" nebo "Odkazy a matice" . V seznamu operátorů hledáme jméno "MATCH" . Po nalezení a výběru klikněte na tlačítko "OK" ve spodní části okna.
3. Okno pro argumenty operátora MAP je aktivováno . Jak vidíte, v tomto okně jsou tři pole podle počtu argumentů. Musíme je vyplnit.

   Protože potřebujeme nalézt pozici slova "cukr" v rozsahu, řídíme toto jméno do pole "Hledaná hodnota" .

   V poli "Skenované pole" je třeba zadat souřadnice samotného rozsahu. Lze jej ručně tlačit, ale je jednodušší umístit kurzor na pole a vybrat toto pole na listu, zatímco držíte levé tlačítko myši. Poté se jeho adresa zobrazí v okně s argumenty.

   Ve třetím poli "Typ shody" uveďte číslo "0" , protože budeme pracovat s textovými daty, a proto potřebujeme přesný výsledek.

   Po nastavení všech údajů klikněte na tlačítko "OK" .

4. Program vypočítá a zobrazí pořadové číslo pozice "Cukr" ve vybraném poli v buňce, kterou jsme zadali v prvním kroku této instrukce. Číslo pozice bude "4" .

Lekce: Průvodce funkcemi v aplikaci Excel

Metoda 2: Automatizujte použití operátora MATCH

Většinou jsme považovali nejprimitivnější případ použití operátora POSITION , ale i automatizaci.

1. Pro větší pohodlí na listu přidejte dvě další pole: "Specified Value" a "Number" . V poli " Přiřazená hodnota" řídíme jméno, které musíme najít. Nyní je to "Maso" . Do pole "Číslo" nastavte kurzor a přejděte do okna argumentů operátora stejným způsobem, jak je popsáno výše.
2. V okně s parametry funkcí v poli "Hodnota vyhledávání" určíme adresu buňky, kde je napsáno slovo "Meat" . V polích "Scanned array" a "Mapping type" specifikujeme stejné údaje jako předchozí metoda - adresa rozsahu a číslo "0" . Poté klikněte na tlačítko "OK" .
3. Poté, co jsme provedli výše uvedená opatření, pole "Číslo" zobrazuje pozici slova "Maso" ve zvoleném rozsahu. V tomto případě se rovná hodnotě "3" .
4. Tato metoda je dobrá, protože pokud chceme poznat pozici jakéhokoli jiného jména, nebudeme muset opakovaně psát nebo měnit vzorec pokaždé. V poli "Specifikovaná hodnota" je zcela jednoduché zadat nové vyhledávací slovo namísto předchozí. Zpracování a výstup výsledku po tomto se stane automaticky.

Metoda 3: Použití operátoru MAP pro číselné výrazy

Nyní se podíváme, jak můžete pomocí funkce MATCHING pracovat s číselnými výrazy.

Úkolem je najít zboží pro množství realizace 400 rublů nebo nejbližší k této částce ve vzestupném pořadí.

1. Nejprve je třeba uspořádat položky ve sloupci "Částka" v sestupném pořadí. Vyberte tento sloupec a přejděte na kartu "Domov" . Klikneme na ikonu "Třídit a filtr" , která se nachází na pásu karet v bloku "Upravit" . V seznamu, který se zobrazí, vyberte možnost "Seřadit z maxima na minimum" .
2. Po dokončení třídění vyberte buňku, na které bude výsledek zobrazen, a spusťte okno s argumenty stejným způsobem, jak je popsáno v první metodě.

   V poli "Hodnota vyhledávání" řídíme číslo "400" . Do pole "Scanned array" zadejte souřadnice sloupce "Amount" (Částka) . Do pole "Typ shody" nastavte hodnotu na hodnotu "-1" , protože hledáme stejnou nebo větší hodnotu než ta, kterou hledáme. Po dokončení všech nastavení klikněte na tlačítko "OK" .

3. Výsledek zpracování je zobrazen v předem určené buňce. Toto je pozice "3" . Odpovídá "bramborám" . Ve skutečnosti je výše tržeb z prodeje tohoto produktu nejblíže 400 vzestupně a činí 450 rublů.

Podobně můžete hledat nejbližší polohu na "400" v sestupném pořadí. Pouze pro tento účel je nutné data filtrovat ve vzestupném pořadí a v poli "Typ shody" argumentů funkcí nastavte hodnotu na hodnotu "1" .

Lekce: Třídění a filtrování dat v aplikaci Excel

Metoda 4: Použití ve spojení s jinými operátory

Nejúčinnější je použít tuto funkci s ostatními operátory jako součást komplexního vzorce. Nejčastěji se používá ve spojení s funkcí INDEX . Tento argument vygeneruje obsah rozsahu určeného číslem řádku nebo sloupce do zadané buňky. A číslování, jako v případě operátora POSITION , se provádí nikoliv vzhledem k celému listu, ale pouze v rámci rozsahu. Syntaxe této funkce je:

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

V tomto případě, pokud je pole jednorozměrné, můžete použít pouze jeden ze dvou argumentů: "Číslo řádku" nebo "Číslo sloupce" .

Zvláštnost spojení mezi funkcemi INDEX a MATCH je, že tato funkce může být použita jako první argument, tj. Ukazovat na pozici řádku nebo sloupce.

Podívejme se na to, jak to lze v praxi udělat, pomocí stejné tabulky. Máme za úkol vypsat název zboží do dalšího pole "zboží" , jehož celková částka výnosu se rovná 350 rublům nebo nejbližší k této hodnotě v sestupném pořadí. Tento argument je uveden v poli "Přibližná částka výnosu na listu" .

1. Seřadit položky ve sloupci "Výnosové částky" ve vzestupném pořadí. Chcete-li to provést, vyberte požadovaný sloupec a na kartě Domů klikněte na ikonu "Seřadit a filtrovat" a v zobrazeném menu klikněte na "Seřadit od minima na maximum" .
2. V poli "Produkt" vyberte buňku a běžným způsobem zavolejte Průvodce funkcemi pomocí tlačítka "Vložit funkci" .
3. V otevřeném okně Průvodce funkcí v kategorii "Odkazy a matice" vyhledáme název "INDEX" , vybereme jej a klikneme na tlačítko "OK" .
4. Dále se otevře okno, které nabízí volbu INDEX : pro pole nebo pro odkaz. Potřebujeme první možnost. Proto ponecháme v tomto okně všechna výchozí nastavení a klikněte na tlačítko "OK" .
5. Otevře okno argumentů funkce INDEX . Do pole " pole " zadejte adresu rozsahu, ve kterém bude vyhledávač INDEX hledat název produktu. V našem případě je to sloupec "Název produktu" .

   Pole "Číslo řádku" obsahuje vnořenou funkci MATCH . Bude muset být kohouta ručně pomocí syntaxe zmíněné na samém začátku článku. Okamžitě napište název funkce - "MATCH" bez uvozovek. Potom otevřete držák. Prvním argumentem tohoto operátora je "hodnota zájmu" . Na poli je v poli "Přibližná částka výnosu". Určete souřadnice buňky obsahující číslo 350 . Položte středník. Druhým argumentem je "Scanned array" . MATCHING se bude zabývat rozsahem, ve kterém se zjišťuje výše výnosu, a hledat nejpřesněji 350 rublů. Proto v tomto případě uveďte souřadnice sloupce "Výše příjmu" . Opět umístíme středník. Třetí argument je "Typ shody" . Protože hledáme číslo, které se rovná danému číslu nebo nejbližšímu menšímu, pak zde nastavíme číslici "1" . Zavřete konzoly.

   Třetí argument funkce INDEX "Číslo sloupce" zůstane prázdný. Poté klikněte na tlačítko "OK" .

6. Jak vidíte, funkce INDEX pomocí operátoru POSITION v předvolené buňce zobrazuje název "Čaj" . Množství z prodeje čaje (300 rublů) je nejblíže v sestupném pořadí od součtu 350 rublů ze všech hodnot ve zpracované tabulce.
7. Pokud změníme číslo v poli "Přibližné množství výnosů" na druhé, bude obsah pole "Produkt" automaticky automaticky přepočítán.

Lekce: Funkce INDEX v aplikaci Excel

Jak je vidět, operátor MAP je velmi výhodná funkce pro určení pořadového čísla zadaného prvku v datovém poli. Ale přínos z toho je značně zvýšený, pokud se používá v komplexních vzorcích.

• ← Výpočet NPV v aplikaci Microsoft Excel
• Funkce INDEX v aplikaci Microsoft Excel →