V tomto článku se naučíme Jak VYHLEDAT hodnotu mezi dvěma čísly v Excelu.
Scénář:
Je snadné vyhledat hodnotu s jedním kritériem v tabulce. Můžeme jednoduše použít VLOOKUP. Co bychom ale mohli dělat, kdybychom ve vašich datech měli shodná kritéria pro více sloupců a potřebovali bychom hledat ve více sloupcích, aby odpovídali hodnotě. Pojďme se naučit, jak lze tento problém vyřešit pomocí různých vyhledávacích verzí aplikace Excel
LOOKUP hodnota mezi dvěma čísly
Pomocí vzorce VLOOKUP
Pokud ve VLOOKUP přibližná shoda nenajde hodnotu, pak se shoduje poslední hodnota, která je menší než hodnota vyhledávání, a vrátí se hodnota z daného sloupcového indexu.
Obecný vzorec pro hodnotu LOOKUP mezi dvěma čísly:
| = VLOOKUP (hodnota, tabulka, lookup_col, 1) |
A ještě jedna věc o Vlookup je, že hledá hodnotu ve sloupci a pokud nenajde hodnotu v poli sloupců, pak odpovídá a vrátí hodnotu, která je menší než tato hodnota v poli tabulky.
Poznámka: VLOOKUP ve výchozím nastavení odpovídá přibližně, pokud pomineme proměnnou vyhledávání rozsahu. Přibližná shoda je užitečná, když chcete provést přibližnou shodu a když máte pole tabulky seřazené vzestupně.
Pomocí vzorce INDEX a MATCH
Vzorec INDEX a MATCH funguje stejně jako výše, ale s jinou syntaxí. Pokud byste si však měli vybrat pouze ten, s nímž vám funkce jsou příjemné
Obecný vzorec pro hodnotu LOOKUP mezi dvěma čísly
| = INDEX (rozsah vyhledávání, MATCH (1, INDEX ((kritéria1 = rozsah1)*(kritérium2 = rozsah2),0,1),0)) |
lookup_range: rozsah, ze kterého chcete načíst 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 studentské ID a jejich příslušné známky v testu. Nyní jsme museli získat známky pro každého studenta vyhledáním v tabulce systému známek.
K tomu použijeme atribut funkce VLOOKUP, který v případě, že funkce VLOOKUP nenajde přesnou shodu, hledá přibližně shodu pouze v tabulce a pouze v případě, že poslední argument funkce je buď SKUTEČNÝ nebo 1.
Tabulka známek / skóre musí být vzestupně
Použijte vzorec:
| = VLOOKUP (B2, tabulka, 2, 1) |
Jak to funguje?
Funkce Vlookup vyhledá hodnotu 40 ve sloupci značek a vrátí odpovídající hodnotu, pokud se shoduje. Pokud se neshoduje, funkce vyhledá nižší hodnotu, než je vyhledávací hodnota (tj. 40), a vrátí její výsledek.
Zde je table_array pojmenován range as stůl ve vzorci a B2 je uveden jako odkaz na buňku.
Jak vidíte, dostali jsme známku pro prvního studenta. Nyní, abychom získali všechny ostatní stupně, použijeme zkratku Ctrl + D nebo v Excelu použijte možnost přetažení buňky dolů.
Zde jsou všechny stupně třídy pomocí funkce VLOOKUP.
LOOKUP hodnota mezi dvěma čísly v tabulce Zaměstnanec
Máme tabulku, která obsahuje podrobnosti o všech zaměstnancích v organizaci na samostatném listu. První sloupec obsahuje ID těchto zaměstnanců. Tuto tabulku jsem pojmenoval emp_data.
Nyní můj vyhledávací list potřebuje načíst informace zaměstnance, jehož ID je zapsáno v buňce B3. Jako ID jsem pojmenoval B3.
Pro lepší pochopení jsou všechny záhlaví sloupců přesně ve stejném pořadí jako tabulka emp_data.
Nyní napište do buňky C3 níže uvedený vzorec a načtěte zónu ID zaměstnance zapsaného v B3.
| = VLOOKUP (ID, Emp_Data, 2,0) |
Tím se vrátí zóna ID zaměstnance 1-1830456593, protože sloupec číslo 2 v databázi obsahuje zónu zaměstnanců.
Zkopírujte tento vzorec do ostatních buněk a změňte číslo sloupce ve vzorci, abyste získali všechny informace o zaměstnancích.
V buňce B3 můžete vidět všechny informace související se zmíněným ID. Bez ohledu na to, jaké ID do buňky B3 napíšete, všechny informace budou načteny, aniž by došlo ke změně vzorce.
Jak to funguje?
Není nic složitého. Jednoduše používáme funkci VLOOKUP k vyhledání ID a poté načtení zmíněného sloupce. Procvičte si VLOOKUP s použitím takových dat, abyste VLOOKUP lépe porozuměli.
Načíst data zaměstnanců pomocí nadpisů
Ve výše uvedeném příkladu jsme měli všechny sloupce uspořádané ve stejném pořadí, ale někdy nastane situace, kdy budete mít databázi, která bude mít stovky sloupců. V takových případech tento způsob získávání informací o zaměstnancích nebude dobrý. Bude lepší, když se vzorec může podívat na záhlaví sloupce a načíst data z tohoto sloupce z tabulky zaměstnanců.
Abychom získali hodnotu z tabulky pomocí záhlaví sloupců, použijeme metodu obousměrného vyhledávání nebo řekněme VLOOKUP dynamického sloupce.
Tento vzorec použijte v buňce C3 a zkopírujte do ostatních buněk. Ve vzorci nemusíte nic měnit, vše bude načteno z thd emp_data.
| = VLOOKUP (ID, Emp_Data, MATCH (C2, Emp_Data_Headers, 0), 0) |
Tento vzorec jednoduše načte všechny informace z odpovídajících sloupců. Můžete zamíchat záhlaví ve zprávě, to nic nezmění. Ať už je nadpis v buňce výše, příslušné údaje obsahují.
Jak to funguje?
Toto je prostě dynamické VLOOKUP. Můžete si o tom přečíst zde. Pokud to zde vysvětlím, bude z toho příliš velký článek.
Načíst ID zaměstnance s částečnou shodou
Může se stát, že si nepamatujete celé ID zaměstnance, ale přesto chcete získat informace o nějakém ID. V takových případech je nejlepším řešením částečná shoda VLOOKUP.
Pokud například vím, že některé ID obsahuje 2345, ale neznám celé ID. Pokud zadám toto číslo do buňky C3, výstup bude podobný.
Nic nedostaneme. Protože v tabulce nic neodpovídá 2345. Upravte výše uvedený vzorec takto.
| =VLOOKUP("*"&ID&"*", Emp_Data,ZÁPAS(C2, Emp_Data_Headers, 0), 0) |
Zkopírujte to do celého řádku. A nyní máte informace o prvním zaměstnanci, který obsahuje toto číslo.
Vezměte prosím na vědomí, že ve sloupci Emp Id získáme první ID, které obsahuje odpovídající číslo. Pokud jakékoli jiné ID obsahuje stejné číslo, tento vzorec nenačte informace o tomto zaměstnanci.
Pokud chcete získat všechna ID zaměstnanců, která obsahují stejné číslo, použijte vzorec, který vyhledá všechny odpovídající hodnoty.
Pomocí vzorce INDEX a MATCH
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)) |
Jak to funguje:
Už víme, jak fungují funkce INDEX a MATCH v EXCEL, takže to zde nebudu 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í
| {FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}*
{FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE}* {FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE} |
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).
Zde jsou všechny poznámky k pozorování pomocí vzorce v aplikaci Excel
Poznámky:
- Sloupec tabulky známek / skóre musí být ve vzestupném pořadí, jinak může funkce poskytnout špatný výsledek.
- Funkce VLOOKUP vyhledá hodnotu v prvním řádku Table_array a extrahuje odpovídající hodnoty pouze vpravo od rozsahu vyhledávání.
- Chcete -li získat přibližnou shodu, musí být poslední argument funkce VLOOKUP funkce nastaven na hodnotu TRUE nebo 1.
- Funkce VLOOKUP vrací chybu, pokud je adresa sešitu neplatná nebo nesprávná.
- Funkce VLOOKUP vrací chybu, pokud hodnota není shodná.
Doufám, že tento článek o tom, jak VYHLEDAT hodnotu mezi dvěma čísly 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.
Způsob použití funkce Vlookup při ověřování dat : Omezte uživatele na povolení hodnot z vyhledávací tabulky pomocí pole Vzorec pro ověření dat v aplikaci Excel. Rámeček vzorce v validaci dat vám umožňuje zvolit typ požadovaného omezení.
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.