Excel

Funkce INDEX v aplikaci Microsoft Excel

Jednou z nejužitečnějších funkcí aplikace Excel je operátor INDEX. Hledá data v rozmezí na průsečíku zadaného řádku a sloupce a vrátí výsledek do předem určeného buňky. Ale plné možnosti této funkce jsou odhaleny při použití v komplexních vzorcích v kombinaci s jinými operátory. Podívejme se na různé možnosti její aplikace.

Použití funkce INDEX

Operátor INDEX odkazuje na skupinu funkcí z kategorie "Odkazy a matice" . Má dvě odrůdy: pro pole a odkazy.

Varianta pro pole má následující syntaxi:

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

V tomto případě mohou být poslední dva argumenty ve vzorci použity buď společně, nebo jakýkoli z nich, pokud je pole jednorozměrné. Pro vícerozměrný rozsah by se měly používat obě hodnoty. Mělo by se také vzít v úvahu, že pod číslem řádku a sloupce se rozumí číslo na souřadnicích listu, ale pořadí uvnitř zadaného pole.

Syntaxe referenčního variantu vypadá takto:

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

Zde můžete použít pouze jeden argument ze dvou: "Číslo řádku" nebo "Číslo sloupce" . Argument "Číslo oblasti" je obecně volitelný a platí pouze v případě, že je v operaci zahrnuto několik rozsahů.

Operátor tedy vyhledává data ve stanoveném rozsahu při zadávání řádku nebo sloupce. Tato funkce je velmi podobná operátor , ale na rozdíl od toho může vyhledávat téměř všude, a to nejen v levém sloupci tabulky.

Metoda 1: použití operátoru INDEX pro pole

Nejprve se podívejme na nejjednodušší příklad algoritmu pro použití pole INDEX pro pole.

Máme tabulku platů. V prvním sloupci jsou zobrazena jména zaměstnanců, v druhém - den platby a ve třetí - částka výdělku. Musíme vypsat jméno zaměstnance ve třetím řádku.

  1. Vyberte buňku, ve které se zobrazí výsledek zpracování. Klepněte na ikonu "Vložit funkci" , která je umístěna bezprostředně nalevo od řádek vzorce.

    2. Přejděte na Průvodce funkcí v aplikaci Microsoft Excel

  2. Existuje postup pro aktivaci průvodce funkcí . V kategorii "Odkazy a matice" tohoto nástroje nebo "Úplný abecední seznam" hledáme název "INDEX" . Poté, co najdete operátora, vyberte jej a klikněte na tlačítko "OK" , které se nachází v dolní části okna.

    3. Průvodce funkcí v aplikaci Microsoft Excel

  3. Otevře se malé okno, ve kterém vyberete jeden z typů funkcí: "Array" nebo "Link" . Potřebujeme volbu "Array" . Nejprve se nachází a ve výchozím nastavení je zvýrazněno. Stačí kliknout na tlačítko "OK" .

    4. Výběr typu funkce INDEX v aplikaci Microsoft Excel

  4. Otevře okno argumentů funkce INDEX . Jak bylo uvedeno výše, má tři argumenty, a tudíž tři pole pro vyplnění.

    V poli "Array" je třeba zadat adresu zpracovaného datového rozsahu. Může být ručně tažen. Abychom však tento úkol zjednodušili, postupujeme jinak. Umístěte kurzor do příslušného pole a nakreslete celý rozsah tabulkových dat na listu. Poté se v poli okamžitě objeví adresa rozsahu.

    V poli "Číslo řádku" uvedeme číslo "3" , protože podmínkou je, abychom definovali třetí jméno v seznamu. V poli "Číslo sloupce" nastavte číslo "1" , protože sloupec s názvy je první ve zvoleném rozsahu.

    Po dokončení všech nastavení klikněte na tlačítko "OK" .

    5. Okno argumentu funkce INDEX v aplikaci Microsoft Excel

  5. Výsledek zpracování se zobrazí v buňce, která byla specifikována v prvním odstavci této příručky. Jedná se o odvozený název, který je třetím ze seznamu ve zvoleném rozsahu dat.

Výsledkem zpracování funkce INDEX v aplikaci Microsoft Excel

Rozložili jsme použití funkce INDEX v multidimenzionálním poli (několik sloupců a řádků). Pokud by rozsah byl jednorozměrný, vyplnění dat v okně s argumenty by bylo ještě jednodušší. V poli "Array" použijeme stejnou metodu jako je uvedeno výše. V tomto případě rozsah dat obsahuje pouze hodnoty v jednom sloupci "Název" . V poli "Číslo řádku" zadáme hodnotu "3" , protože potřebujeme znát údaje z třetího řádku. Pole "Číslo sloupce" může být vůbec prázdné, protože máme jednorozměrný rozsah, ve kterém je použit pouze jeden sloupec. Klikněte na tlačítko "OK" .

Okno argumentu funkce INDEX pro jednorozměrné pole v aplikaci Microsoft Excel

Výsledek bude přesně stejný jako výše.

Výsledkem zpracování funkce INDEX pro jednorozměrné pole v aplikaci Microsoft Excel

Byl to nejjednodušší příklad toho, abyste viděli, jak tato funkce funguje, ale v praxi se tato verze jejího užívání používá ještě zřídka.

Lekce: Průvodce funkcemi v aplikaci Excel

Metoda 2: použití ve spojení s operátorem MATCH

V praxi se funkce INDEX často používá ve spojení s argumentem MATCH . Bundle INDEX - MATCHING je výkonný nástroj při práci v aplikaci Excel, která je flexibilnější svou funkční než její nejbližší analogovou - operátor VPR .

Hlavní funkcí funkce MATCH je zadání čísla v pořadí určité hodnoty ve zvoleném rozsahu.

Syntaxe příkazu POSITION je:

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

  • Požadovaná hodnota je hodnota, jejíž poloha v oblasti, kterou hledáme;
  • Naskenované pole je rozsah, ve kterém je tato hodnota umístěna;
  • Typ mapování je volitelný parametr, který určuje, zda lze hodnoty přesně nebo přibližně najít. Budeme hledat přesné hodnoty, takže se tento argument nepoužívá.

Pomocí tohoto nástroje můžete automatizovat zavedení argumentů "Číslo řádku" a "Číslo sloupce" do funkce INDEX .

Podívejme se, jak to lze udělat konkrétním příkladem. Pracujeme se stejnou tabulkou, jak je uvedeno výše. Samostatně máme dvě další pole - "Name" a "Sum . " Je nutné, aby při uvedení jména pracovníka byla automaticky vykázána částka, kterou získal. Podívejme se, jak to může být provedeno v praxi s využitím funkcí INDEX a MATCH .

  1. Nejprve zjistíme, jaký druh mzdy obdrží zaměstnanec společnosti Parfenov DF. Zadejte jeho jméno do příslušného pole.

    2. Název je zapsán do pole v aplikaci Microsoft Excel

  2. V poli "Částka" vyberte buňku, ve které se zobrazí konečný výsledek. Spustíme okno argumentu funkce INDEX pro pole.

    V poli "Array" zadáváme souřadnice sloupce, ve kterém se nacházejí součty mzdových nákladů zaměstnanců.

    Pole "Číslo sloupce" je prázdné, protože jako příklad používáme jednorozměrný rozsah.

    Ale v poli "Číslo řádku" stačí napsat funkci MATCH . Pro jeho záznam dodržujeme výše popsanou syntaxi. Ihned v poli zadejte jméno operátora "SEARCH" bez uvozovek. Pak okamžitě otevřete závorku a určete souřadnice požadované hodnoty. Jedná se o souřadnice buňky, do které jsme odděleně zaznamenali jméno pracovníka Parfenov. Položte středník a zadejte souřadnice rozsahu, který právě prohlížíte. V našem případě se jedná o adresu sloupce s názvy zaměstnanců. Potom zavřete držák.

    Po zadání všech hodnot klikněte na tlačítko "OK" .

    3. Okno argumentu funkce INDEX v kombinaci s operátorem MATCH v aplikaci Microsoft Excel

  3. Výsledek výše výdělku Parfenov DF po zpracování je zobrazen v poli "Částka".

    4. Výsledkem zpracování funkce INDEX v kombinaci s operátorem MATCH v aplikaci Microsoft Excel

  4. Nyní, pokud v poli "Jméno" změníme obsah z "Parfenov DF" , například "Popova MD" , pak se hodnota mzdy v poli "Sum" automaticky změní.

Změna hodnot při použití funkce INDEX v kombinaci s operátorem MATCH v aplikaci Microsoft Excel

Metoda 3: Zpracování více tabulek

Nyní uvidíme, jak s pomocí operátoru INDEX můžete zpracovat několik tabulek. Pro tyto účely se použije dodatečný argument "Číslo oblasti" .

Máme tři tabulky. Každá tabulka zobrazuje mzdy zaměstnanců po dobu jednoho měsíce. Naším úkolem je zjistit plat (třetí sloupec) druhého zaměstnance (druhý řádek) pro třetí měsíc (třetí oblast).

  1. Vyberte buňku, ve které bude výsledek vypsán, a otevřete Průvodce funkcemi obvyklým způsobem, ale při výběru typu operátora vybereme referenční pohled. Potřebujeme to, protože tento typ podporuje práci s argumentem "Číslo oblasti" .

    2. Výběr referenčního zobrazení funkce INDEX v aplikaci Microsoft Excel

  2. Otevře okno s argumenty. V poli "Link" je třeba zadat adresy všech tří rozsahů. Chcete-li to provést, vložte kurzor do pole a stisknutím levého tlačítka myši vyberte první rozsah. Pak umístíme středník. To je velmi důležité, protože pokud se okamžitě dostanete k výběru dalšího pole, jeho adresa jednoduše nahradí souřadnice předchozího pole. Takže po zavedení středníkového bodu zvolíme následující rozsah. Pak znovu vložte středník a vyberte poslední pole. Veškerý výraz, který je v poli "Reference", je zarovnán.

    V poli "Číslo řádku" označujeme číslo "2" , protože hledáme druhé příjmení v seznamu.

    V poli "Číslo sloupce" označujeme číslo "3" , protože sloupec platu je třetí sloupec v každé tabulce.

    V poli "Číslo oblasti" uveďte číslo "3" , jelikož potřebujeme najít údaje ve třetí tabulce, která obsahuje údaje o mzdách za třetí měsíc.

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

    3. Okno argumentu funkce INDEX při práci se třemi oblastmi v aplikaci Microsoft Excel

  3. Poté se výsledky výpočtu zobrazují v předem vybrané buňce. Ukazuje výši mzdy druhého zaměstnance (Safronov VM) za třetí měsíc.

Výsledek zpracování funkce INDEX při práci se třemi oblastmi v aplikaci Microsoft Excel

Metoda 4: Výpočet součtu

Referenční formulář se často nepoužívá jako maticový formulář, ale může být použit nejen při práci s několika rozsahy, ale také pro jiné potřeby. Může se například použít k výpočtu částky v kombinaci s operátorem SUM .

Při přidání součtu má SUM následující syntaxi:

=СУММ(адрес_массива)

V našem konkrétním případě lze výše výdělku všech zaměstnanců měsíčně vypočítat podle následujícího vzorce:

=СУММ(C4:C9)

Výsledek funkce SUM v aplikaci Microsoft Excel

Ale můžete ho trochu upravit pomocí funkce INDEX . Pak bude vypadat takto:

=СУММ(C4:ИНДЕКС(C4:C9;6))

Výsledkem kombinace funkce SUM a INDEX v aplikaci Microsoft Excel

V tomto případě začne v buňkách, na kterých začne pole, souřadnice buňky, se kterou začíná. Ale v souřadnicích označujících konec pole se používá INDEX operátor. V tomto případě první argument operátora INDEX ukazuje na rozsah a druhý na poslední - šestý.

Lekce: Užitečné funkce aplikace Excel

Jak můžete vidět, funkce INDEX může být použita v aplikaci Excel pro řešení poměrně různorodých úkolů. Přesto jsme se rozhodli zdaleka ne všechny možné možnosti její aplikace, ale pouze ty nejoblíbenější. Existují dva typy této funkce: referenční a pro pole. Nejúčinnější lze jej použít v kombinaci s jinými operátory. Takto vytvořené vzorce mohou vyřešit nejobtížnější problémy.