Če že nekaj časa berete nasvete programa Excel, ste vedno našli nekoga, ki govori o uporabi funkcij Excel INDEX () & MATCH () namesto Excel VLOOKUP. Če govorim zase, je bilo vedno pretežko poskusiti obvladati DVE novi funkciji hkrati. Ampak, to je kul trik. Dajte mi pet minut in poskusil bom to razložiti v preprosti angleščini.
30-sekundni pregled VLOOKUP-a
Recimo, da imate tabelo evidenc zaposlenih. Prvi stolpec je številka zaposlenega, preostali stolpci pa so različni podatki o zaposlenem. Kadar koli imate na delovnem listu številko zaposlenega, lahko z VLOOKUP vrnete določeno referenčno točko o zaposlenem. Sintaksa je VLOOKUP (vrednost, obseg podatkov, št. Stolpca, FALSE). V Excelu piše: "Pojdite na obseg podatkov. V prvem stolpcu obsega podatkov poiščite vrstico, ki ima (vrednost). Vrnite vrstico (št. Stolpca) iz te vrstice. Ko jo obesite, je zelo preprost in zmogljiv.
Težava
Nekega dne imate situacijo, ko imate ime zaposlenega, vendar potrebujete številko zaposlenega. Na naslednji sliki imate ime v A10 in morate najti številko zaposlenega v B10.
Ko je ključno polje desno od podatkov, ki jih želite pridobiti, VLOOKUP ne bo deloval. Če bi samo VLOOKUP sprejel -1 kot številko stolpca, ne bi bilo težav. Ampak, ne. Ena najpogostejših rešitev je, da začasno vstavite nov stolpec A, kopirate stolpec imen v nov stolpec A, zapolnite VLOOKUP, prilepite posebne vrednosti in nato izbrišete začasni stolpec A. Profi za Excel lahko to premikajo verjetno v spanju.
Predlagam vam, da sprejmete izziv in poskusite uporabiti to enostopenjsko metodo. Da, formulo boste morali nekaj tednov pritrditi na steno, toda to ste že dolgo storili tudi z VLOOKUP, kajne?
Razlog, da je to tako težko, je ta, da uporabljate dve funkciji, ki ju verjetno še nikoli niste uporabljali. Torej, naj razčlenim na dva dela.
Najprej je tu funkcija INDEX (). To je grozljivo imenovana funkcija. Ko nekdo reče "indeks", mi v mislih ne pričara ničesar podobnega, kar počne ta funkcija. Indeks zahteva tri argumente.
=INDEX(data range, row number, column number)
V angleščini Excel preide na obseg podatkov in vam vrne vrednost v presečišču vrstice (številka vrstice) in stolpca (številka stolpca). Hej, premisli - to je precej preprosto, kajne? =INDEX($A$2:$C$6,4,2)vam bo dal vrednost v B5.
Uporaba INDEX () za naš problem, lahko ugotovimo, da vrne število zaposlenih iz območja, ki bi jih to uporabite: =INDEX($A$2:$A$6,?,1). Pravzaprav se zdi, da je ta delček tako nepomemben, da se zdi neuporaben. Ko pa vprašalnik zamenjate s funkcijo MATCH (), imate rešitev.
Tu je sintaksa:
=MATCH(Value, Single-column data range, FALSE)
Excelu pove: "Poiščite obseg podatkov in mi povejte relativno številko vrstice, kjer najdete ujemanje za (podatke). Torej, če želite poiskati, katero vrstico ima zaposleni v A10, bi uporabili =MATCH(A10,$B$2:$B$6,FALSE). Da, to je bolj zapleteno kot kazalo , vendar bi moral biti tik do ulice profesionalcev VLOOKUP. Če A10 vsebuje "Miller, Bob", bo ta MATCH vrnil, da je v 3. vrstici obsega B2: B6.
Tu je - funkcija MATCH () pove funkciji Index, v katero vrstico naj pogleda - končali ste. Vzemite funkcijo Index, naš vprašalnik zamenjajte s funkcijo MATCH in zdaj lahko naredite ekvivalent VLOOKUP-ov, kadar polje s ključem ni v levem stolpcu. Tu je funkcija, ki jo je treba uporabiti:
=INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,FALSE),1)
Nalepka na moji steni jo dejansko prikazuje v dveh vrsticah. Najprej sem napisal razlago za MATCH (). Spodaj sem napisal razlago za INDEX (). Nato sem med njima narisal obliko lijaka, ki označuje, da se funkcija MATCH () spusti v 2. argument funkcije INDEX ().
Prvih nekajkrat, ko sem moral narediti eno od teh, me je zamikalo, da bi samo zabil nov začasni stolpec A, vendar sem namesto tega šel skozi bolečino. Je hitrejši in zahteva manj manipulacije. Torej, ko boste naslednjič želeli, da v funkcijo VLOOKUP dodate negativno število, poskusite s to čudno kombinacijo INDEX in MATCH rešiti svoje težave.
