V tomto článku se naučíme Jak načíst cenu ze seznamu, který odpovídá kritériím kategorie i položky v Excelu.
Scénář:
Je snadné v tabulce najít hodnotu s jedním kritériem, k čemuž bychom mohli jednoduše použít VLOOKUP. Pokud ale ve svých datech nemáte ani jeden sloupcový kriterium a potřebujete najít kritéria pro více sloupců, aby odpovídala hodnotě, VLOOKUP nepomůže.
Obecný vzorec pro kategorii i položku
| = INDEX (rozsah vyhledávání, MATCH (1, INDEX ((položka1 = rozsah_položky) * (kategorie2 = rozsah_skupiny), 0,1), 0)) |
lookup_range: Je to rozsah, ze kterého chcete získat hodnotu.
Kritéria 1, Kritéria 2, Kritéria N: Toto jsou kritéria, která chcete shodovat v rozsahu 1, rozsahu 2 a rozsahu N. Můžete mít až 270 kritérií - dvojice rozsahů.
Rozsah1, rozsah2, rozsahN: Toto jsou rozsahy, ve kterých budete odpovídat svým příslušným kritériím
Příklad:
Pochopit to všechno může být matoucí. Pojďme pochopit, jak používat funkci pomocí příkladu. Zde máme datovou tabulku. Chci vytáhnout Jméno zákazníka pomocí Datum rezervace, Tvůrce a Oblast. Takže tady mám tři kritéria a jeden rozsah vyhledávání.
Tento vzorec napište do buňky I4 a stiskněte Enter.
| = INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)) |
Už víme, jak fungují funkce INDEX a MATCH v EXCEL, takže to zde nebudeme vysvětlovat. Povíme si o triku, který jsme zde použili.
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16): Hlavní část je tato. Každá část tohoto příkazu vrací pole true false.
Když jsou booleovské hodnoty vynásobeny, vrátí pole 0 a 1. Násobení funguje jako operátor AND. Hense, když jsou všechny hodnoty pravdivé, pak vrátí 1 else 0
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16) To celkem vrátí
Což se převede do
| {0;0;0;0;0;0;0;1;0;0;0;0;0;0;0} |
INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): Funkce INDEX vrátí stejné pole ({0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}), aby MATCH fungovala jako vyhledávací pole.
MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): Funkce MATCH vyhledá 1 v poli {0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}. A vrátí indexové číslo první 1 nalezené v poli. Což je zde 8.
INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)): Nakonec se vrátí INDEX hodnota z daného rozsahu (E2: E16) při nalezeném indexu (8).
Pokud následně můžete stisknout CTRL + SHIFT + ENTER, můžete odstranit vnitřní funkci INDEX. Stačí napsat tento vzorec a stisknout CTRL + SHIFT + ENTER.
Vzorec
| = INDEX (E2: E16, MATCH (1, (I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0)) |
Obecný vzorec pole pro vyhledávání více kritérií
| = INDEX (rozsah vyhledávání, MATCH (1, (kritéria1 = rozsah1)*(kritéria2 = rozsah2)*(kritériaN = rozsahN), 0)) |
Zde jsou všechny poznámky k pozorování pomocí vzorce v aplikaci Excel
Poznámky:
- Pokud máte jedno kritérium pro shodu, použijte Vlookup, je to běžná praxe.
- Do vyhledávacího pole můžete přidat další řádky a sloupce.
- Textové argumenty musí být uvedeny v uvozovkách ("").
- Tabulka VLOOKUP musí mít v levém nebo prvním sloupci pole lookup_array.
- Col index nemůže být 1, protože je vyhledávacím polem
- Pro přesnou hodnotu shody se používá argument 0. Pro přibližnou hodnotu shody použijte 1.
- Funkce vrací chybu #N/A, pokud ve vyhledávacím poli nebyla nalezena hodnota pro vyhledávání. Pokud je to nutné, chyťte chybu.
Doufám, že tento článek o tom, jak získat cenu ze seznamu, který odpovídá kritériím kategorie i položky v aplikaci Excel, je vysvětlující. Další články o výpočtu hodnot a souvisejících vzorcích Excelu najdete zde. Pokud se vám naše blogy líbily, sdílejte je se svými přáteli na Facebooku. A také nás můžete sledovat na Twitteru a Facebooku. Rádi bychom od vás slyšeli, dejte nám vědět, jak můžeme zlepšit, doplnit nebo inovovat naši práci a zlepšit ji pro vás. Napište nám na e -mail.
Jak načíst nejnovější cenu v aplikaci Excel : Je běžné aktualizovat ceny v jakémkoli podnikání a použití nejnovějších cen pro jakýkoli nákup nebo prodej je nutností. K získání nejnovější ceny ze seznamu v aplikaci Excel používáme funkci LOOKUP. Funkce LOOKUP načte nejnovější cenu.
Funkce VLOOKUP pro výpočet známky v aplikaci Excel : Pro výpočet známek nejsou IF a IFS jediné funkce, které můžete použít. VLOOKUP je pro takové podmíněné výpočty efektivnější a dynamičtější. Pro výpočet známek pomocí VLOOKUP můžeme použít tento vzorec.
17 Věcí o VYHLEDÁVÁNÍ Excelu : VLOOKUP se nejčastěji používá k načítání odpovídajících hodnot, ale VLOOKUP dokáže mnohem více. Zde je 17 věcí o VLOOKUP, které byste měli vědět, jak je efektivně využívat.
VYHLEDEJTE první text ze seznamu v aplikaci Excel : Funkce VLOOKUP funguje dobře se zástupnými znaky. Můžeme to použít k extrahování první textové hodnoty z daného seznamu v Excelu. Zde je obecný vzorec.
LOOKUP datum s poslední hodnotou v seznamu : K načtení data, které obsahuje poslední hodnotu, použijeme funkci LOOKUP. Tato funkce zkontroluje buňku, která obsahuje poslední hodnotu ve vektoru, a poté použije tento odkaz k vrácení data.
Oblíbené články:
50 zkratek aplikace Excel pro zvýšení produktivity : Získejte rychleji své úkoly v aplikaci Excel. Tyto zkratky vám pomohou zvýšit efektivitu práce v Excelu.
Jak používat funkci VLOOKUP v Excelu : Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která se používá k vyhledávání hodnot z různých rozsahů a listů.
Jak používat funkci IF v aplikaci Excel : Příkaz IF v aplikaci Excel zkontroluje podmínku a vrátí konkrétní hodnotu, pokud je podmínka PRAVDA, nebo vrátí jinou konkrétní hodnotu, pokud NEPRAVDA.
Jak používat funkci SUMIF v aplikaci Excel : Toto je další základní funkce řídicího panelu. To vám pomůže shrnout hodnoty za konkrétních podmínek.
Jak používat funkci COUNTIF v aplikaci Excel : Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétních hodnot nemusíte data filtrovat. Funkce Countif je nezbytná pro přípravu řídicího panelu.