Ak už nejaký čas čítate tipy pre Excel, vždy ste našli niekoho, kto hovorí namiesto Excel VLOOKUP o používaní funkcií Excel INDEX () a MATCH (). Keď som hovoril za seba, bolo vždy príliš ťažké pokúsiť sa zvládnuť DVA nové funkcie súčasne. Ale je to super trik. Dajte mi päť minút a pokúsim sa to vysvetliť jednoduchou angličtinou.
Tridsaťsekundová recenzia VLOOKUP
Povedzme, že máte tabuľku záznamov o zamestnancoch. Prvý stĺpec je číslo zamestnanca a zvyšné stĺpce obsahujú rôzne údaje o zamestnancovi. Kedykoľvek máte v pracovnom liste číslo zamestnanca, môžete pomocou nástroja VLOOKUP vrátiť konkrétny údaj o zamestnancovi. Syntax je VLOOKUP (hodnota, rozsah údajov, číslo stĺpca, FALSE). V programe Excel sa hovorí: „Prejdite do rozsahu údajov. Vyhľadajte riadok, ktorý má v prvom stĺpci rozsahu údajov hodnotu (value). Vráťte (hodnotu č. Stĺpca) th hodnotu z tohto riadku. Akonáhle sa dostanete na kĺb, je to veľmi jednoduché a výkonné.
Problém
Jedného dňa sa dostanete do situácie, keď máte meno zamestnanca, ale potrebujete číslo zamestnanca. Na nasledujúcom obrázku máte meno v A10 a musíte nájsť číslo zamestnanca v B10.
Keď je pole s kľúčom napravo od údajov, ktoré chcete načítať, VLOOKUP nebude fungovať. Ak by iba SVYHLEDANIE akceptovalo -1 ako číslo stĺpca, nebol by problém. Ale nie je. Jedným z bežných riešení je dočasné vloženie nového stĺpca A, skopírovanie stĺpca mien do nového stĺpca A, vyplnenie príkazom VLOOKUP, vloženie zvláštnych hodnôt a potom dočasný stĺpec A. Odstránenie dočasných stĺpcov A bude pravdepodobne prebiehať v režime Excel.
Navrhnem, aby ste sa chopili výzvy a pokúsili sa použiť túto jednokrokovú metódu. Áno, budete musieť niekoľko týždňov držať vzorec na svojej stene, ale to ste už dávno robili s VLOOKUP, však?
Myslím, že dôvod, prečo je to také ťažké, je to, že používate dve funkcie, ktoré ste pravdepodobne nikdy predtým nepoužívali. Dovoľte mi, aby som to rozdelil na dve časti.
Najskôr je tu funkcia INDEX (). Toto je strašne pomenovaná funkcia. Keď niekto povie „index“, nevykúzli mi z toho nič podobné, ako to robí táto funkcia. Register vyžaduje tri argumenty.
=INDEX(data range, row number, column number)
V angličtine Excel prejde do rozsahu údajov a vráti vám hodnotu v priesečníku (riadku) a (stĺpca) a (stĺpec). Hej, premýšľaj - je to celkom jednoduché, však? =INDEX($A$2:$C$6,4,2)vám dá hodnotu v B5.
Uplatnenie index (), aby náš problém, môžete zistiť, že k vráteniu počtu zamestnancov z rozsahu, mali by ste použiť toto: =INDEX($A$2:$A$6,?,1). V skutočnosti sa tento kúsok javí tak triviálny, že sa zdá zbytočný. Keď však nahradíte otáznik funkciou MATCH (), máte riešenie.
Tu je syntax:
=MATCH(Value, Single-column data range, FALSE)
Programu Excel hovorí: „Vyhľadajte rozsah údajov a povedzte mi relatívne číslo riadku, v ktorom nájdete zhodu pre (údaje). Takže na zistenie, ktorý riadok má zamestnanca v A10, by ste použili =MATCH(A10,$B$2:$B$6,FALSE). Áno, je to zložitejšie ako index , ale malo by to byť hneď v uličke profesionálov VLOOKUP. Ak A10 obsahuje „Miller, Bob“, potom táto ZÁPAS vráti, že je v 3. rade rozsahu B2: B6.
Tam to je - funkcia MATCH () informuje funkciu Index o tom, ktorý riadok sa má pozrieť - a máte hotovo. Vezmite funkciu Index, nahraďte náš otáznik funkciou MATCH a teraz môžete robiť ekvivalent VLOOKUP, keď pole kľúča nie je v ľavom stĺpci. Tu je funkcia, ktorú treba použiť:
=INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,FALSE),1)
Lepiaca poznámka na mojej stene ju v skutočnosti zobrazuje ako dva riadky. Najprv som napísal vysvetlenie pre MATCH (). Ďalej som napísal vysvetlenie INDEX (). Potom som medzi nimi nakreslil tvar lievika, aby som naznačil, že funkcia MATCH () spadne na 2. argument funkcie INDEX ().
Prvých párkrát, čo som musel urobiť jeden z nich, som bol v pokušení len zabuchnúť nový dočasný stĺpec A, ale prešiel som tým, že som to namiesto toho urobil týmto spôsobom. Je rýchlejšia a vyžaduje menšiu manipuláciu. Takže nabudúce, keď si želáte, aby ste mohli vložiť záporné číslo do funkcie VLOOKUP, vyskúšajte túto zvláštnu kombináciu INDEXU a MATCHu na vyriešenie vašich problémov.
