V tomto článku sa naučíme Ako získať cenu zo zoznamu, ktorý zodpovedá kritériám kategórie a položky v programe Excel.
Scenár:
V tabuľke je ľahké nájsť hodnotu s jedným kritériom, na to by sme mohli jednoducho použiť VLOOKUP. Ak však vo svojich údajoch nemáte ani jeden stĺpec, ale potrebujete nájsť kritériá pre viacero stĺpcov, aby zodpovedali hodnote, funkcia VLOOKUP nepomôže.
Generický vzorec pre kategóriu aj položku
| = INDEX (rozsah_hľadania, MATCH (1, INDEX ((položka1 = rozsah_položky) * (kategória2 = rozsah_skupiny), 0,1), 0)) |
lookup_range: Je to 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 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)) |
Už vieme, ako fungujú funkcie INDEX a MATCH v programe EXCEL, takže to tu nebudeme 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
Č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).
Ak následne stlačíte CTRL + SHIFT + ENTER, môžete odstrániť vnútornú funkciu INDEX. Stačí napísať tento vzorec a stlačiť CTRL + SHIFT + ENTER.
Vzorec
| = INDEX (E2: E16, MATCH (1, (I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0)) |
Generický vzorec poľa na vyhľadávanie viacerých kritérií
| = INDEX (rozsah_hľadania, MATCH (1, (kritérium1 = rozsah1)*(kritérium2 = rozsah2)*(kritériumN = rozsahN), 0)) |
Tu sú všetky poznámky k pozorovaniu pomocou vzorca v programe Excel
Poznámky:
- Ak máte jedno kritérium, ktoré sa má zhodovať, použite Vlookup, je to bežná prax.
- Do vyhľadávacieho poľa môžete pridať ďalšie riadky a stĺpce.
- Textové argumenty musia byť uvedené v úvodzovkách ("").
- Tabuľka VLOOKUP musí mať v ľavom alebo prvom stĺpci pole lookup_array.
- Col index nemôže byť 1, pretože je vyhľadávacím poľom
- Na hodnotu presnej zhody sa používa argument 0. Na približnú hodnotu zhody použite 1.
- Funkcia vráti chybu #N/A, ak sa vo vyhľadávacom poli nenašla hodnota vyhľadávania. Ak je to potrebné, chyťte chybu.
Dúfam, že tento článok o tom, ako získať cenu zo zoznamu, ktorý zodpovedá kritériám kategórie a položky 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.
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.