V tomto článku sa naučíme Ako VYHĽADAŤ hodnotu medzi dvoma číslami v programe Excel.
Scenár:
V tabuľke je ľahké vyhľadať hodnotu s jedným kritériom. Môžeme jednoducho použiť VLOOKUP. Čo by sme však mohli urobiť, ak by sa vo vašich dátach zhodovalo viacero kritérií stĺpcov a bolo by potrebné vyhľadať vo viacerých stĺpcoch hodnotu. Pozrime sa, ako je možné tento problém vyriešiť pomocou rôznych vyhľadávacích verzií programu Excel
LOOKUP hodnota medzi dvoma číslami
Použitie vzorca VLOOKUP
V prípade približnej zhody VLOOKUP, ak sa hodnota nenájde, porovná sa posledná hodnota, ktorá je menšia ako hodnota vyhľadávania, a vráti sa hodnota z daného stĺpcového indexu.
Obecný vzorec na vyhľadanie hodnoty medzi dvoma číslami:
| = VLOOKUP (hodnota, tabuľka, lookup_col, 1) |
A ešte jedna vec o Vlookup je, že hľadá hodnotu v stĺpci, a ak nenájde hodnotu v poli stĺpcov, potom sa zhoduje a vráti hodnotu, ktorá je menšia ako táto hodnota v poli tabuľky.
Poznámka: VLOOKUP v predvolenom nastavení zodpovedá približnej zhode, ak vynecháme premennú na vyhľadávanie rozsahu. Približná zhoda je užitočná vtedy, ak chcete vykonať približnú zhodu a keď máte pole tabuľky zoradené vzostupne.
Použitie vzorca INDEX a MATCH
Vzorec INDEX a MATCH funguje rovnako ako vyššie, ale s odlišnou syntaxou. Ale ak by ste si mali vybrať iba ten, s ktorým funkciami ste spokojní
Obecný vzorec na hodnotu LOOKUP medzi dvoma číslami
| = INDEX (rozsah_hľadania, MATCH (1, INDEX ((kritérium1 = rozsah1)*(kritérium2 = rozsah2),0,1),0)) |
lookup_range: rozsah, z ktorého chcete získať hodnotu.
Kritériá 1, Kritériá 2, Kritériá N: Toto sú kritériá, ktoré chcete priradiť v rozsahu 1, rozsahu 2 a rozsahu N. Môžete mať až 270 kritérií - dvojice rozsahov.
Rozsah1, rozsah2, rozsahN: Toto sú rozsahy, v ktorých budete zodpovedať svojim príslušným kritériám.
Príklad:
Toto všetko môže byť mätúce na pochopenie. Poďme pochopiť, ako používať funkciu na príklade. Tu máme ID študenta a ich príslušné známky v teste. Teraz sme museli získať známky pre každého študenta vyhľadaním v tabuľke systému známok.
Na tento účel použijeme atribút funkcie VLOOKUP, ktorý v prípade, že funkcia VLOOKUP nenájde presnú zhodu, hľadá iba zhodu v tabuľke iba vtedy, ak je posledným argumentom funkcie buď PRAVDA alebo 1.
Tabuľka známok / skóre musí byť vzostupne
Použite vzorec:
| = VLOOKUP (B2, tabuľka, 2, 1) |
Ako to funguje?
Funkcia Vlookup vyhľadá hodnotu 40 v stĺpci značiek a vráti zodpovedajúcu hodnotu, ak sa zhoduje. Ak sa nezhoduje, funkcia vyhľadá menšiu hodnotu ako je hodnota vyhľadávania (t.j. 40) a vráti svoj výsledok.
Tu je table_array pomenovaný range as stôl vo vzorci a B2 je uvedený ako odkaz na bunku.
Ako vidíte, dostali sme známku pre prvého študenta. Teraz, aby sme získali všetky ostatné známky, použijeme skratku Ctrl + D alebo v Exceli použite možnosť potiahnutia bunky nadol.
Tu sú všetky ročníky triedy pomocou funkcie VLOOKUP.
Hodnota LOOKUP medzi dvoma číslami v tabuľke Zamestnanec
Máme tabuľku, ktorá obsahuje podrobnosti o všetkých zamestnancoch v organizácii na samostatnom hárku. Prvý stĺpec obsahuje ID týchto zamestnancov. Túto tabuľku som pomenoval ako emp_data.
Teraz musí môj vyhľadávací hárok načítať informácie o zamestnancovi, ktorého ID je zapísané v bunke B3. Ako ID som pomenoval B3.
Pre jednoduchosť pochopenia sú všetky záhlavia stĺpcov v presne rovnakom poradí ako tabuľka emp_data.
Teraz do bunky C3 napíšte nasledujúci vzorec, aby ste získali zónu ID zamestnanca zapísanú v B3.
| = VLOOKUP (ID, Emp_Data, 2,0) |
Tým sa vráti zóna ID zamestnanca 1-1830456593, pretože stĺpec číslo 2 v databáze obsahuje zónu zamestnancov.
Skopírujte tento vzorec do zvyšných buniek a zmeňte číslo stĺpca vo vzorci, aby ste získali všetky informácie o zamestnancoch.
Všetky informácie súvisiace so spomínaným ID môžete vidieť v bunke B3. Bez ohľadu na to, aké ID napíšete do bunky B3, všetky informácie sa načítajú bez toho, aby ste vo vzorci urobili akúkoľvek zmenu.
Ako to funguje?
Nie je nič zložité. Jednoducho používame funkciu VLOOKUP na vyhľadanie ID a potom načítanie spomínaného stĺpca. Cvičte VLOOKUP s použitím týchto údajov, aby ste VLOOKUP lepšie porozumeli.
Načítajte údaje o zamestnancoch pomocou nadpisov
Vo vyššie uvedenom príklade sme mali všetky stĺpce usporiadané v rovnakom poradí, ale môžu nastať prípady, keď budete mať databázu, ktorá bude mať stovky stĺpcov. V takýchto prípadoch tento spôsob získavania informácií o zamestnancoch nebude dobrý. Bude lepšie, ak sa vzorec môže pozrieť na záhlavie stĺpca a načítať údaje z tohto stĺpca z tabuľky zamestnancov.
Na získanie hodnoty z tabuľky pomocou záhlavia stĺpcov teda použijeme metódu obojsmerného vyhľadávania alebo povedzme dynamický VLOOKUP stĺpca.
Tento vzorec použite v bunke C3 a skopírujte do ostatných buniek. Vo vzorci nemusíte nič meniť, všetko bude načítané z thd emp_data.
| = VLOOKUP (ID, Emp_Data, MATCH (C2, Emp_Data_Headers, 0), 0) |
Tento vzorec jednoducho získa všetky informácie zo zodpovedajúcich stĺpcov. V správe môžete zamiešať hlavičky, tým sa nič nezmení. Bez ohľadu na to, ktorý nadpis je napísaný v bunke vyššie, príslušné údaje obsahujú.
Ako to funguje?
Toto je jednoducho dynamické VLOOKUP. Môžete si o tom prečítať tu. Ak to tu vysvetlím, stane sa z toho príliš veľký článok.
Získať ID zamestnanca čiastočnou zhodou
Môže sa stať, že si nepamätáte celé ID zamestnanca, ale napriek tomu chcete získať informácie o nejakom ID. V takýchto prípadoch je najlepším riešením čiastočná zhoda VLOOKUP.
Ak napríklad viem, že niektoré ID obsahuje 2345, ale nepoznám celé ID. Ak zadám toto číslo do bunky C3, výstup bude podobný.
Nič nedostaneme. Pretože sa nič nezhoduje s 2345 v tabuľke. Vyššie uvedený vzorec upravte takto.
| =VLOOKUP("*"&ID&"*", Emp_Data,ZÁPAS(C2, Emp_Data_Headers, 0), 0) |
Skopírujte to do celého riadka. A teraz máte informácie o prvom zamestnancovi, ktoré obsahujú toto číslo.
Upozorňujeme, že dostaneme prvý identifikátor, ktorý obsahuje zodpovedajúce číslo v stĺpci Emp Id. Ak akékoľvek iné ID obsahuje rovnaké číslo, tento vzorec nenačíta informácie o tomto zamestnancovi.
Ak chcete získať všetky ID zamestnancov, ktoré obsahujú rovnaké číslo, použite vzorec, ktorý vyhľadá všetky zodpovedajúce hodnoty.
Použitie vzorca INDEX a MATCH
Tu máme tabuľku údajov. Chcem vytiahnuť Meno zákazníka pomocou dátumu rezervácie, staviteľa a oblasti. Takže tu mám tri kritériá a jeden rozsah vyhľadávania.
Napíšte tento vzorec do bunky I4 a stlačte kláves Enter.
| = INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)) |
Ako to funguje:
Už vieme, ako fungujú funkcie INDEX a MATCH v programe EXCEL, takže to tu nebudem vysvetľovať. Povieme si o triku, ktorý sme tu použili.
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16): Hlavná časť je táto. Každá časť tohto príkazu vráti pole true false.
Keď sú booleovské hodnoty vynásobené, vrátia pole 0 a 1. Násobenie funguje ako operátor AND. Hense, keď sú všetky hodnoty pravdivé, vráti iba 1 ďalšiu 0
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16) To celkom vráti
| {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} |
Čo sa preloží 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): INDEX Funkcia vráti rovnaké pole ({0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}), aby funkcia MATCH fungovala ako vyhľadávacie pole.
MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): Funkcia MATCH vyhľadá 1 v poli {0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}. A vráti indexové číslo prvého 1 nájdeného v poli. Čo je tu 8.
INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)): Nakoniec sa vráti INDEX hodnota z daného rozsahu (E2: E16) pri nájdenom indexe (8).
Tu sú všetky poznámky k pozorovaniu pomocou vzorca v programe Excel
Poznámky:
- Stĺpec tabuľky známok / skóre musí byť vzostupne, inak môže funkcia poskytnúť nesprávny výsledok.
- Funkcia VLOOKUP vyhľadá hodnotu v prvom riadku Table_array a extrahuje zodpovedajúce hodnoty iba vpravo od oblasti lookup_range.
- Na získanie približnej zhody funkcie musí byť posledný argument funkcie VLOOKUP nastavený na hodnotu TRUE alebo 1.
- Ak je adresa zošita neplatná alebo nesprávna, funkcia VLOOKUP vráti chybu.
- Funkcia VLOOKUP vráti chybu, ak sa hodnota nezhoduje.
Dúfam, že tento článok o tom, ako VYHĽADAŤ hodnotu medzi dvoma číslami v programe Excel, je vysvetľujúci. Tu nájdete ďalšie články o výpočte hodnôt a súvisiacich vzorcoch programu Excel. Ak sa vám páčili naše blogy, zdieľajte ich so svojimi priateľmi na Facebooku. A tiež nás môžete sledovať na Twitteri a Facebooku. Radi by sme vás počuli, dajte nám vedieť, ako môžeme zlepšiť, doplniť alebo inovovať našu prácu a zlepšiť ju pre vás. Napíšte nám na emailovú stránku.
Spôsob, ako používať funkciu Vlookup pri overovaní údajov : Obmedzte používateľov na povolenie hodnôt z vyhľadávacej tabuľky pomocou poľa vzorca na overenie údajov v programe Excel. Rámček vzorca pri overovaní údajov vám umožňuje vybrať požadovaný typ obmedzenia.
Ako získať najnovšiu cenu v programe Excel : Aktualizácia cien je bežná v každom podniku a používanie najnovších cien pri každom nákupe alebo predaji je nevyhnutné. Na získanie najnovšej ceny zo zoznamu v programe Excel používame funkciu LOOKUP. Funkcia LOOKUP načítava najnovšiu cenu.
Funkcia VLOOKUP na výpočet známky v programe Excel : Na výpočet stupňov IF a IFS nie sú jediné funkcie, ktoré môžete použiť. VLOOKUP je pre takéto podmienené výpočty efektívnejší a dynamickejší. Na výpočet známok pomocou VLOOKUP môžeme použiť tento vzorec.
17 vecí o VLOOKUPE v Exceli : VLOOKUP sa najčastejšie používa na získavanie zhodných hodnôt, ale VLOOKUP dokáže oveľa viac. Tu je 17 vecí o VLOOKUP, ktoré by ste mali vedieť efektívne používať.
VYHĽADAJTE prvý text zo zoznamu v programe Excel : Funkcia VLOOKUP funguje dobre so zástupnými znakmi. Môžeme to použiť na extrahovanie prvej textovej hodnoty z daného zoznamu v programe Excel. Tu je generický vzorec.
HĽADAJ dátum s poslednou hodnotou v zozname : Na získanie dátumu, ktorý obsahuje poslednú hodnotu, použijeme funkciu LOOKUP. Táto funkcia vyhľadá bunku, ktorá obsahuje poslednú hodnotu vo vektore, a potom použije tento odkaz na vrátenie dátumu.
Populárne články:
50 skratiek programu Excel na zvýšenie produktivity : Zrýchlite sa so svojimi úlohami v Exceli. Tieto skratky vám pomôžu zvýšiť efektivitu práce v programe Excel.
Ako používať funkciu VLOOKUP v programe Excel : Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
Ako používať funkciu IF v programe Excel : Príkaz IF v programe Excel skontroluje podmienku a vráti konkrétnu hodnotu, ak je podmienka PRAVDA, alebo vráti inú konkrétnu hodnotu, ak je NEPRAVDA.
Ako používať funkciu SUMIF v programe Excel : Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.
Ako používať funkciu COUNTIF v programe Excel : Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnych hodnôt nie je potrebné filtrovať údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.