Jei kurį laiką skaitėte „Excel“ patarimus, visada radote žmogų, kuris kalba apie „Excel INDEX“ () ir MATCH () funkcijų naudojimą vietoj „Excel VLOOKUP“. Kalbėdamas už save, visada buvo per sunku vienu metu išbandyti DU naujas funkcijas. Bet tai yra šaunus triukas. Duokite man penkias minutes ir aš pabandysiu tai paaiškinti paprasta anglų kalba.
30 sekundžių „VLOOKUP“ apžvalga
Tarkime, kad turite darbuotojų įrašų lentelę. Pirmasis stulpelis yra darbuotojo numeris, o likę stulpeliai yra įvairūs duomenys apie darbuotoją. Bet kada, kai darbalapyje turite darbuotojo numerį, galite naudoti VLOOKUP, kad grąžintumėte konkretų atskaitos tašką apie darbuotoją. Sintaksė yra VLOOKUP (reikšmė, duomenų diapazonas, stulpelio Nr., FALSE). Programai „Excel“ sakoma: „Eikite į duomenų diapazoną. Raskite eilutę, kurioje yra (vertė) pirmajame duomenų diapazono stulpelyje. Grąžinkite tos eilutės (stulpelio Nr.) Vertę. Kai užfiksuosite, tai labai paprasta ir galinga.
Problema
Vieną dieną turite situaciją, kai turite darbuotojo vardą, bet jums reikia darbuotojo numerio. Šiame paveikslėlyje turite vardą A10 ir turite rasti darbuotojo numerį B10.
Kai raktų laukas yra dešinėje duomenų, kuriuos norite gauti, VLOOKUP neveiks. Jei tik „VLOOKUP“ priimtų -1 kaip stulpelio numerį, nebūtų jokių problemų. Bet taip nėra. Vienas įprastas sprendimas yra laikinai įterpti naują A stulpelį, nukopijuoti vardų stulpelį į naują A stulpelį, užpildyti VLOOKUP, Paste Special Values, tada ištrinti laikiną stulpelį A. „Excel“ profesionalai tikriausiai gali atlikti šį žingsnį miegodami.
Aš siūlau jums priimti iššūkį ir pabandyti naudoti šį vieno žingsnio metodą. Taip, keletą savaičių turėsite užklijuoti formulę ant savo sienos, bet taip pat jau seniai padarėte su VLOOKUP?
Manau, kad taip sunku tai, kad jūs naudojate dvi funkcijas, kurių tikriausiai niekada anksčiau nenaudojote. Taigi, leiskite man jį suskaidyti į dvi dalis.
Pirma, yra funkcija INDEX (). Tai siaubingai pavadinta funkcija. Kai kas nors sako „indeksas“, tai mano galvoje neužburia nieko panašaus į tai, ką daro ši funkcija. Indeksui reikalingi trys argumentai.
=INDEX(data range, row number, column number)
Anglų kalba „Excel“ eina į duomenų diapazoną ir grąžina vertę (eilutės numeris) trečiosios eilutės ir (stulpelio numeris) stulpelio sankirtoje. Ei, pagalvok - tai gana paprasta, tiesa? =INDEX($A$2:$C$6,4,2)suteiks jums vertę B5.
Taikant indeksą () į mūsų problemą, galite suprasti, kad grąžinti darbuotojų skaičių iš asortimento, galite naudoti šį: =INDEX($A$2:$A$6,?,1). Tiesą sakant, ši jo dalis atrodo tokia nereikšminga, kad atrodo nenaudinga. Bet kai klaustuką pakeisite funkcija MATCH (), turėsite sprendimą.
Čia yra sintaksė:
=MATCH(Value, Single-column data range, FALSE)
Programoje „Excel“ sakoma: „Ieškokite duomenų diapazono ir pasakykite man santykinį eilutės numerį, kur rasite atitikimą (duomenims). Taigi, norėdami sužinoti, kurią eilutę turi A10 darbuotojas, naudokite =MATCH(A10,$B$2:$B$6,FALSE). Taip, tai yra sudėtingiau nei„ Index “ , bet tai turėtų būti tiesiai VLOOKUP profesionalų alėjoje. Jei A10 yra „Miller, Bob“, tai ši ATITIKLIS grąžins, kad jis yra 3-oje B2: B6 diapazono eilėje.
Štai čia - funkcija MATCH () nurodo indekso funkcijai, kurioje eilutėje ieškoti - jūs baigėte. Paimkite indekso funkciją, pakeiskite mūsų klaustuką funkcija MATCH ir dabar galite atlikti VLOOKUP ekvivalentą, kai rakto lauko nėra kairiajame stulpelyje. Čia yra naudojama funkcija:
=INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,FALSE),1)
Lipni lapelis ant mano sienos iš tikrųjų rodo dvi eilutes. Pirmiausia išrašiau paaiškinimą „MATCH“ (). Žemiau parašiau INDEX () paaiškinimą. Tada aš nupiešiau piltuvėlio figūrą tarp dviejų, nurodydamas, kad funkcija MATCH () patenka į 2-ąjį funkcijos INDEX () argumentą.
Pirmus kelis kartus, kai turėjau atlikti vieną iš šių būdų, susigundžiau tiesiog užtrenkti naują laikiną A stulpelį, bet vietoj to patyriau skausmą. Jis yra greitesnis ir reikalauja mažiau manipuliavimo. Taigi, kitą kartą norėdami, kad į VLOOKUP funkciją įrašytumėte neigiamą skaičių, išbandykite šį keistą INDEX ir MATCH derinį, kad išspręstumėte savo problemas.
