„Excel“ turi daug funkcijų - apie 450+ jų.
Ir daugelis iš jų yra tiesiog nuostabūs. Darbo kiekis, kurį galite atlikti naudodami kelias formules, mane vis dar stebina (net po to, kai „Excel“ naudojau daugiau nei 10 metų).
Ir tarp visų šių nuostabių funkcijų išsiskiria „INDEX MATCH“ funkcijų derinys.
Aš esu didžiulis „INDEX MATCH Combo“ gerbėjas ir daug kartų tai aiškiai pasakiau.
Aš net parašiau straipsnį apie „Index Match Vs VLOOKUP“, kuris sukėlė šiek tiek diskusijų (galite patikrinti fejerverkų komentarų skiltį).
Ir šiandien aš rašau šį straipsnį tik sutelkdamas dėmesį į „Index Match“, norėdamas parodyti keletą paprastų ir pažangių scenarijų, kuriuose galite naudoti šį galingą formulės derinį ir atlikti darbą.
Pastaba: „Excel“ yra ir kitų paieškos formulių, tokių kaip VLOOKUP ir HLOOKUP, ir jos yra puikios. Daugelis žmonių mano, kad „VLOOKUP“ yra lengviau naudoti (ir tai tiesa). Manau, kad INDEX MATCH daugeliu atvejų yra geresnis pasirinkimas. Bet kadangi žmonėms tai sunku, jis mažiau naudojamas. Taigi aš bandau tai supaprastinti naudodamas šią pamoką.Prieš parodydamas, kaip „INDEX MATCH“ derinys keičia analitikų ir duomenų mokslininkų pasaulį, pirmiausia leiskite jums pristatyti atskiras dalis - INDEX ir MATCH funkcijas.
Funkcija INDEX: suranda vertę pagal koordinates
Lengviausias būdas suprasti, kaip veikia indekso funkcija, yra galvoti apie tai kaip apie GPS palydovą.
Kai tik palydovui pasakysite platumos ir ilgumos koordinates, jis tiksliai žinos, kur eiti, ir suras tą vietą.
Taigi, nepaisant nepaprastai daugybės ilgų derinių, palydovas tiksliai žinotų, kur ieškoti.
Greitai ieškojau savo darbo vietos ir štai ką radau.
Šiaip ar taip, užtenka geografijos.
Kaip ir palydovui reikalingos platumos ir ilgumos koordinatės, „Excel“ funkcijai INDEX reikia eilutės ir stulpelio numerio, kad žinotumėte, į kurią ląstelę kalbate.
Tai yra „Excel INDEX“ funkcija riešuto kevale.
Taigi leiskite man tai apibūdinti jums paprastais žodžiais.
Funkcija INDEX naudos eilutės numerį ir stulpelio numerį, kad surastų langelį nurodytame diapazone ir grąžintų jame esančią vertę.
Viskas, INDEX yra labai paprasta funkcija, be jokios naudos. Galų gale, jūs tikriausiai nežinote eilučių ir stulpelių numerių.
Bet…
Tai, kad galite jį naudoti su kitomis funkcijomis (užuomina: MATCH), kurios gali rasti eilutės numerį ir stulpelio numerį, daro INDEX nepaprastai galinga „Excel“ funkcija.
Žemiau yra funkcijos INDEX sintaksė:
= INDEX (masyvas, eilutės numeris, [stulpelio_numeris]) = INDEX (masyvas, eilutės numeris, [stulpelio_numeris], [ploto_numeris])
- masyvas - a ląstelių diapazonas arba masyvo konstanta.
- eilutės numeris - eilutės numeris, iš kurio reikia gauti reikšmę.
- [col_num] - stulpelio numeris, iš kurio reikia gauti reikšmę. Nors tai neprivalomas argumentas, bet jei eilutės numeris nepateiktas, jį reikia pateikti.
- [area_num] - (Neprivaloma) Jei masyvo argumentą sudaro keli diapazonai, šis skaičius bus naudojamas norint pasirinkti nuorodą iš visų diapazonų.
Funkcija INDEX turi 2 sintakses (tik FYI).
Pirmasis naudojamas daugeliu atvejų. Antrasis naudojamas tik pažengusiais atvejais (pvz., Atliekant trijų krypčių paiešką), kurį aptarsime viename iš pavyzdžių vėliau šioje pamokoje.
Bet jei jūs dar nesinaudojote šia funkcija, tiesiog prisiminkite pirmąją sintaksę.
Žemiau yra vaizdo įrašas, kuriame paaiškinama, kaip naudoti funkciją INDEX
Funkcija MATCH: suranda poziciją, nurodytą paieškos vertėje
Grįžtant prie ankstesnio ilgumos ir platumos pavyzdžio, MATCH yra funkcija, kuri gali rasti šias pozicijas („Excel“ skaičiuoklių pasaulyje).
Paprasta kalba „Excel MATCH“ funkcija gali rasti langelio padėtį diapazone.
Ir kokiu pagrindu ji ras ląstelės padėtį?
Remiantis paieškos verte.
Pavyzdžiui, jei turite sąrašą, kaip parodyta žemiau, ir norite jame rasti pavadinimo „Žymėti“ poziciją, galite naudoti funkciją MATCH.
Funkcija grąžina 3, nes tai yra langelio, kurio pavadinimas Markas, padėtis.
Funkcija MATCH pradeda ieškoti iš viršaus į apačią ieškomos vertės (kuri yra „Žymėti“) nurodytame diapazone (šiame pavyzdyje A1: A9). Kai tik suranda pavadinimą, jis grąžina poziciją tame konkrečiame diapazone.
Žemiau yra „Excel“ funkcijos MATCH sintaksė.
= MATCH (lookup_value, lookup_array, [match_type])
- lookup_value - Vertė, kuriai ieškote atitikties „lookup_array“.
- lookup_array - Ląstelių diapazonas, kuriame ieškote lookup_value.
- [match_type] - (Neprivaloma) Čia nurodoma, kaip „Excel“ turėtų ieškoti atitinkamos vertės. Tai gali turėti tris reikšmes -1, 0 arba 1.
Atitikties tipo argumento supratimas funkcijoje MATCH
Yra dar vienas dalykas, kurį turite žinoti apie funkciją MATCH, ir tai, kaip ji eina per duomenis ir nustato langelio padėtį.
Trečiasis funkcijos MATCH argumentas gali būti 0, 1 arba -1.
Žemiau pateikiamas šių argumentų veikimo paaiškinimas:
- 0 - bus ieškoma tiksli vertės atitiktis. Jei randama tiksli atitiktis, funkcija MATCH grąžins langelio padėtį. Priešingu atveju tai grąžins klaidą.
- 1 - randama didžiausia vertė, kuri yra mažesnė arba lygi paieškos vertei. Kad tai veiktų, jūsų duomenų diapazonas turi būti surūšiuotas didėjančia tvarka.
- -1 - randama mažiausia vertė, didesnė arba lygi paieškos vertei. Kad tai veiktų, jūsų duomenų diapazonas turi būti surūšiuotas mažėjančia tvarka.
Žemiau yra vaizdo įrašas, kuriame paaiškinama, kaip naudoti funkciją MATCH (kartu su atitikties tipo argumentu)
Apibendrinant ir išdėstant paprastais žodžiais:
- INDEX reikia langelio padėties (eilutės ir stulpelio numeris) ir pateikia langelio vertę.
- „MATCH“ suranda poziciją naudodami paieškos vertę.
Sujunkime juos, kad sukurtume „Powerhouse“ (INDEX + MATCH)
Dabar, kai turite pagrindinį supratimą apie tai, kaip INDEX ir MATCH funkcijos veikia atskirai, sujungkime šiuos du dalykus ir sužinokime apie visus nuostabius dalykus, kuriuos jis gali padaryti.
Norėdami tai geriau suprasti, turiu keletą pavyzdžių, kuriuose naudojamas INDEX MATCH derinys.
Pradėsiu nuo paprasto pavyzdžio ir parodysiu jums keletą pažangių naudojimo atvejų.
Spustelėkite čia, jei norite atsisiųsti pavyzdinį failą
1 pavyzdys: paprasta paieška naudojant „INDEX MATCH Combo“
Atlikime paprastą paiešką naudodami INDEX/MATCH.
Žemiau yra lentelė, kurioje turiu dešimties mokinių pažymius.
Iš šios lentelės noriu rasti Džimo žymes.
Žemiau yra formulė, kuri gali lengvai tai padaryti:
= INDEKSAS ($ A $ 2: $ B $ 11, MATCH ("Jim", $ A $ 2: $ A $ 11,0), 2)
Dabar, jei manote, kad tai galima lengvai padaryti naudojant VLOOKUP funkciją, jūs teisi! Tai nėra geriausias „INDEX MATCH“ nuostabumo panaudojimas. Nepaisant to, kad esu „INDEX MATCH“ gerbėjas, tai yra šiek tiek sunkiau nei „VLOOKUP“. Jei norite gauti duomenis iš dešinėje esančio stulpelio, tai viskas, ką norite padaryti, rekomenduoju naudoti VLOOKUP.
Priežastis, kodėl aš parodžiau šį pavyzdį, kurį taip pat galima lengvai padaryti naudojant „VLOOKUP“, yra parodyti jums, kaip INDEX MATCH veikia paprasta aplinka.
Dabar leiskite parodyti INDEX MATCH pranašumą.
Tarkime, kad turite tuos pačius duomenis, bet ne stulpeliuose, o eilutėse (kaip parodyta žemiau).
Žinote ką, vis tiek galite naudoti „INDEX MATCH“ kombinaciją, kad gautumėte Jimo ženklus.
Žemiau yra formulė, kuri suteiks jums rezultatą:
= INDEKSAS ($ B $ 1: $ K $ 2,2, MATCH („Jim“, $ B $ 1: $ K $ 1,0))
Atminkite, kad turite pakeisti diapazoną ir perjungti eilutės/stulpelio dalis, kad ši formulė veiktų ir horizontaliems duomenims.
To negalima padaryti naudojant „VLOOKUP“, tačiau vis tiek galite tai padaryti lengvai naudodami „HLOOKUP“.
„INDEX MATCH“ derinys gali lengvai valdyti horizontalius ir vertikalius duomenis.Spustelėkite čia, jei norite atsisiųsti pavyzdinį failą
2 pavyzdys: Ieškokite į kairę
Tai dažniau nei manote.
Daug kartų gali tekti gauti duomenis iš stulpelio, kuris yra kairėje stulpelio, kuriame yra paieškos vertė.
Kažkas, kaip parodyta žemiau:
Norėdami sužinoti Michaelio pardavimus, turėsite ieškoti kairėje.
Jei galvojate „VLOOKUP“, leiskite man sustabdyti jus.
„VLOOKUP“ nėra skirtas ieškoti ir gauti vertybių kairėje.
Ar vis tiek galite tai padaryti naudodami VLOOKUP?
Taip tu gali!
Bet tai gali virsti ilga ir negražia formule.
Taigi, jei norite ieškoti ir gauti duomenis iš kairėje esančių stulpelių, geriau naudokite „INDEX MATCH“ derinį.
Žemiau yra formulė, pagal kurią gausite Michaelo pardavimo numerį:
= INDEKSAS ($ A $ 2: $ C $ 11, MATCH ("Michael", C2: C11,0), 2)
3 pavyzdys: dviejų krypčių paieška
Iki šiol matėme pavyzdžių, kai norėjome gauti duomenis iš stulpelio, esančio šalia stulpelio, kuriame yra paieškos vertė.
Tačiau realiame gyvenime duomenys dažnai apima kelis stulpelius.
„INDEX MATCH“ gali lengvai valdyti dvipusį paiešką.
Žemiau pateikiamas trijų skirtingų mokinių pažymių duomenų rinkinys.
Jei norite greitai gauti visų trijų dalykų mokinio pažymius, tai galite padaryti naudodami INDEX MATCH.
Žemiau pateikta formulė suteiks jums visų trijų dalykų „Jim“ ženklus (nukopijuokite ir įklijuokite į vieną langelį ir vilkite, kad užpildytumėte kitas ląsteles arba nukopijuokite ir įklijuokite į kitas ląsteles).
= INDEKSAS ($ B $ 2: $ D $ 11, MATCH ($ F $ 3, $ A $ 2: $ A $ 11,0), MATCH (G $ 2, $ B $ 1: $ D $ 1,0))
Leiskite greitai paaiškinti ir šią formulę.
INDEX formulėje kaip diapazonas naudojamas B2: D11.
Pirmasis MATCH naudoja pavadinimą (Jim langelyje F3) ir nuskaito jo vietą pavadinimų stulpelyje (A2: A11). Tai tampa eilutės numeriu, iš kurio reikia gauti duomenis.
Antroji MATCH formulė naudoja objekto pavadinimą (langelyje G2), kad gautų to konkretaus dalyko pavadinimo poziciją B1: D1. Pavyzdžiui, matematika yra 1, fizika - 2, chemija - 3.
Kadangi šios MATCH pozicijos įtraukiamos į funkciją INDEX, ji grąžina balą pagal studento vardą ir dalyko pavadinimą.
Ši formulė yra dinamiška, o tai reiškia, kad jei pakeisite mokinio vardą ar dalyko pavadinimą, jis vis tiek veiktų ir gautų teisingus duomenis.
Vienas puikus dalykas naudojant „INDEX/MATCH“ yra tas, kad net jei pakeisite subjektų pavadinimus, jis ir toliau duos teisingą rezultatą.4 pavyzdys: paieškos vertė iš kelių stulpelių/kriterijų
Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite gauti „Mark Long“ žymes.
Kadangi duomenys yra dviejuose stulpeliuose, negaliu ieškoti Marko ir gauti duomenų.
Jei aš tai padarysiu, aš gausiu „Mark Frost“, o ne „Mark Long“ ženklų duomenis (nes funkcija „MATCH“ suteiks man atitikties MARK rezultatą).
Vienas iš būdų tai padaryti yra sukurti pagalbinę stulpelį ir sujungti pavadinimus. Kai turėsite pagalbinį stulpelį, galite naudoti VLOOKUP ir gauti ženklų duomenis.
Jei norite sužinoti, kaip tai padaryti, perskaitykite šią mokymo programą apie VLOOKUP naudojimą pagal kelis kriterijus.
Tačiau naudojant INDEX/MATCH kombinaciją, jums nereikia pagalbinės stulpelio. Pačioje formulėje galite sukurti formulę, kuri tvarko kelis kriterijus.
Žemiau pateikta formulė duos rezultatą.
= INDEKSAS ($ C $ 2: $ C $ 11, MATCH ($ E $ 3 & "|" & $ F $ 3, $ A $ 2: A11 & "|" & $ B $ 2: $ B $ 11,0))
Leiskite greitai paaiškinti, ką daro ši formulė.
Formulės MATCH dalis sujungia paieškos vertę (Mark ir Long), taip pat visą paieškos masyvą. Kai $ A $ 2: A11 & "|" & $ B $ 2: $ B $ 11 naudojamas kaip paieškos masyvas, jis tikrina paieškos vertę pagal bendrą vardo ir pavardės eilutę (atskirtą vamzdžio simboliu).
Tai užtikrina, kad gausite tinkamą rezultatą nenaudodami jokių pagalbinių stulpelių.
Tokią paiešką (kai yra keli stulpeliai/kriterijai) galite atlikti ir naudodami VLOOKUP, tačiau turite naudoti pagalbinę stulpelį. „INDEX MATCH“ kombinacija leidžia tai padaryti šiek tiek lengvai be jokių pagalbinių stulpelių.5 pavyzdys: gaukite vertes iš visos eilutės/stulpelio
Anksčiau pateiktuose pavyzdžiuose mes panaudojome funkciją INDEX, kad gautume vertę iš konkrečios ląstelės. Pateikiate eilutės ir stulpelio numerį ir jis grąžina konkretaus langelio vertę.
Bet jūs galite padaryti daugiau.
Taip pat galite naudoti funkciją INDEX, kad gautumėte reikšmes iš visos eilutės ar stulpelio.
Ir kaip tai gali būti naudinga, jūs klausiate!
Tarkime, kad norite sužinoti bendrą visų trijų dalykų Jimo balą.
Galite naudoti funkciją „INDEX“, kad iš pradžių gautumėte visus „Jim“ ženklus, o tada naudokite funkciją „SUM“, kad gautumėte bendrą sumą.
Pažiūrėkime, kaip tai padaryti.
Žemiau turiu visų mokinių balus trijuose dalykuose.
Žemiau pateikta formulė suteiks man bendrą visų trijų dalykų Jimo balą.
= SUMA (INDEKSAS ($ B $ 2: $ D $ 11, MATCH ($ F $ 4, $ A $ 2: $ A $ 11,0), 0))
Leiskite man paaiškinti, kaip ši formulė veikia.
Apgaulė yra naudoti 0 kaip stulpelio numerį.
Kai funkcijoje INDEX naudojate 0 kaip stulpelio numerį, jis grąžins visas eilutės reikšmes. Panašiai, jei kaip eilutės numerį naudojate 0, jis grąžins visas stulpelio reikšmes.
Taigi žemiau pateikta formulės dalis pateikia reikšmių masyvą - {97, 70, 73}
INDEKSAS ($ B $ 2: $ D $ 11, MATCH ($ F $ 4, $ A $ 2: $ A $ 11,0), 0)
Jei tiesiog įvesite šią formulę „Excel“ langelyje ir paspausite „Enter“, pamatysite #VALUE! klaida. Taip yra todėl, kad ji negrąžina vienos vertės, bet vertės masyvą.
Tačiau nesijaudinkite, vertybių masyvas vis dar yra. Tai galite patikrinti pasirinkę formulę ir paspausdami F9 klavišą. Tai parodys formulės rezultatą, kuris šiuo atveju yra trijų reikšmių masyvas - {97, 70, 73}
Dabar, jei įjungsite šią INDEX formulę į SUM funkciją, gausite visų Jimo surinktų pažymių sumą.
Taip pat galite naudoti tą patį, kad gautumėte aukščiausius, žemiausius ir vidutinius Jimo įvertinimus.
Kaip tai padarėme studentui, taip pat galite tai padaryti dalykui. Pvz., Jei norite vidutinio dalyko balo, INDEX formulėje galite palikti eilutės numerį kaip 0 ir jis suteiks visas tos temos stulpelių reikšmes.
Spustelėkite čia, jei norite atsisiųsti pavyzdinį failą
6 pavyzdys: raskite studento pažymį (apytikslė atitikties technika)
Iki šiol mes naudojome MATCH formulę, kad gautume tikslią paieškos vertės atitiktį.
Bet jūs taip pat galite jį naudoti apytiksliai suderinti.
Dabar, koks velnias yra apytikslė atitiktis?
Leisk man paaiškinti.
Kai ieškote tokių dalykų kaip vardai ar ID, ieškote tikslios atitikties. Tačiau kartais jūs turite žinoti diapazoną, kuriame yra jūsų paieškos vertės. Dažniausiai taip būna su skaičiais.
Pvz., Būdami klasės mokytoju, galbūt norėsite sužinoti, koks yra kiekvieno dalyko mokinio pažymys, o įvertinimas sprendžiamas pagal balą.
Žemiau yra pavyzdys, kai noriu pažymio visiems mokiniams, o įvertinimas sprendžiamas remiantis dešinėje esančia lentele.
Taigi, jei mokinys gauna mažiau nei 33, pažymys yra F, o jei jis gauna mažiau nei 50, bet daugiau nei 33, tai yra E ir pan.
Žemiau yra formulė, kuri tai padarys.
= INDEKSAS ($ F $ 3: $ F $ 8, MATCH (B2, $ E $ 3: $ E $ 8,1), 1)
Leiskite man paaiškinti, kaip ši formulė veikia.
Funkcijoje MATCH mes naudojome 1 kaip [match_type] argumentą. Šis argumentas grąžins didžiausią vertę, mažesnę arba lygią paieškos vertei.
Tai reiškia, kad MATCH formulė eina per ženklų diapazoną ir, kai tik suranda ženklų diapazoną, kuris yra lygus arba mažesnis už paieškos ženklų vertę, jis sustos ir grąžins savo poziciją.
Taigi, jei paieškos ženklo vertė yra 20, funkcija MATCH grąžins 1, o jei 85, grąžins 5.
Funkcija INDEX naudoja šią pozicijos vertę, kad gautų įvertinimą.
SVARBU: kad tai veiktų, jūsų duomenys turi būti surūšiuoti didėjančia tvarka. Jei taip nėra, galite gauti klaidingų rezultatų.Atminkite, kad aukščiau pateiktą veiksmą taip pat galima atlikti naudojant toliau pateiktą VLOOKUP formulę:
= VLOOKUP (B2, $ E $ 3: $ F $ 8,2, TRUE)
Tačiau MATCH funkcija gali žengti dar vieną žingsnį, kai kalbama apie apytikslę atitiktį.
Taip pat galite turėti mažėjančius duomenis ir rezultatui rasti naudokite „INDEX MATCH“ derinį. Pavyzdžiui, jei pakeisiu pažymių lentelės tvarką (kaip parodyta žemiau), vis tiek galiu rasti mokinių pažymius.
Norėdami tai padaryti, man tereikia pakeisti [match_type] argumentą į -1.
Žemiau yra mano naudojama formulė:
= INDEKSAS ($ F $ 3: $ F $ 8, MATCH (B2, $ E $ 3: $ E $ 8, -1), 1)„VLOOKUP“ taip pat gali atlikti apytikslę atitiktį, bet tik tada, kai duomenys rūšiuojami didėjančia tvarka (tačiau tai neveikia, jei duomenys rūšiuojami mažėjančia tvarka).
7 pavyzdys: didžiosios ir mažosios raidės
Iki šiol visos mūsų atliktos paieškos buvo skirtingos.
Tai reiškia, kad nesvarbu, ar paieškos vertė buvo Jim, JIM, ar Jim. Jūs gausite tą patį rezultatą.
Bet ką daryti, jei norite, kad peržiūrint būtų skiriamos didžiosios ir mažosios raidės.
Paprastai taip būna, kai turite didelius duomenų rinkinius ir galimybę juos kartoti arba atskirus pavadinimus (ID) (vienintelis skirtumas yra toks)
Pavyzdžiui, tarkime, kad turiu tokį studentų duomenų rinkinį, kuriame yra du studentai, vardu Jim (vienintelis skirtumas yra tas, kad vienas įvedamas kaip Jim, o kitas - kaip Jim).
Atkreipkite dėmesį, kad yra du mokiniai tuo pačiu vardu - Jimas (A2 ir A5 langelis).
Kadangi įprasta paieška neveiks, turite atlikti didžiųjų ir mažųjų raidžių peržiūrą.
Žemiau yra formulė, kuri suteiks jums teisingą rezultatą. Kadangi tai yra masyvo formulė, turite naudoti „Control“ + „Shift“ + „Enter“.
= INDEKSAS ($ B $ 2: $ B $ 11, MATCH (TRUE, EXACT (D3, A2: A11), 0), 1)
Leiskite man paaiškinti, kaip ši formulė veikia.
Funkcija EXACT patikrina, ar tiksliai atitinka paieškos vertė (šiuo atveju tai yra „jim“). Jis peržiūri visus pavadinimus ir grąžina FALSE, jei tai nėra atitiktis, ir TRUE, jei tai yra atitiktis.
Taigi šiame pavyzdyje funkcijos EXACT išvestis yra - {FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}
Atminkite, kad yra tik viena TRUE, kuri yra tada, kai funkcija EXACT rado tobulą atitikimą.
Tada funkcija MATCH suranda TRUE padėtį masyve, kurią grąžina EXACT funkcija, o šiame pavyzdyje yra 4.
Kai turime poziciją, funkcija INDEX naudoja ją, kad surastų ženklus.
8 pavyzdys: raskite artimiausią atitikmenį
Dabar šiek tiek pažengsime.
Tarkime, kad turite duomenų rinkinį, kuriame norite rasti asmenį, kurio darbo patirtis yra arčiausiai reikalaujamos patirties (minima D2 langelyje).
Nors paieškos formulės tam nėra skirtos, galite tai sujungti su kitomis funkcijomis (pvz., MIN ir ABS).
Žemiau pateikiama formulė, pagal kurią bus surastas asmuo, turintis arčiausiai reikalaujamos patirties, ir grąžintas asmens vardas. Atminkite, kad patirtis turi būti kuo arčiau (tai gali būti mažiau arba daugiau).
= INDEKSAS ($ A $ 2: $ A $ 15, MATCH (MIN (ABS (D2-B2: B15))), ABS (D2- $ B $ 2: $ B $ 15), 0))
Kadangi tai yra masyvo formulė, turite naudoti „Control“ + „Shift“ + „Enter“.
Šios formulės gudrybė yra pakeisti paieškos vertę ir paieškos masyvą, kad būtų nustatytas minimalus reikalaujamų ir faktinių verčių skirtumas.
Prieš paaiškindami formulę, suprasime, kaip tai darytumėte rankiniu būdu.
Peržiūrėsite kiekvieną langelį B stulpelyje ir sužinosite, koks yra patirties skirtumas tarp to, ko reikia, ir to, kurį turi žmogus. Kai turėsite visus skirtumus, rasite tą, kuris yra minimalus, ir atsiųskite to asmens vardą.
Būtent tai mes darome pagal šią formulę.
Leisk man paaiškinti.
Paieškos vertė MATCH formulėje yra MIN (ABS (D2-B2: B15)).
Šioje dalyje pateikiamas minimalus skirtumas tarp nurodytos patirties (kuri yra 2,5 metų) ir visos kitos patirties. Šiame pavyzdyje jis grąžina 0,3
Atminkite, kad naudojau ABS, kad įsitikinčiau, jog ieškau artimiausio (tai gali būti daugiau ar mažiau nei nurodyta patirtis).
Dabar ši minimali vertė tampa mūsų paieškos verte.
Funkcijos MATCH paieškos masyvas yra ABS (D2- $ B $ 2: $ B $ 15).
Tai suteikia mums skaičių masyvą, iš kurio buvo atimta 2,5 (reikalinga patirtis).
Taigi dabar turime paieškos vertę (0,3) ir paieškos masyvą ({6,8; 0,8; 19,5; 21,8; 14,5; 11,2; 0,3; 9,2; 2; 9,8; 14,8; 0,4; 23,8; 2,9})
Funkcija MATCH šiame masyve nustato 0,3 poziciją, kuri taip pat yra asmens, turinčio artimiausią patirtį, vardo pozicija.
Tada šį pozicijos numerį funkcija INDEX naudoja norėdama grąžinti asmens vardą.
Susijęs skaitymas: Raskite „Excel“ artimiausią atitiktį (pavyzdžiai naudojant paieškos formules)
Spustelėkite čia, jei norite atsisiųsti pavyzdinį failą
9 pavyzdys: naudokite INDEX MATCH su pakaitos simboliais
Jei norite ieškoti vertės, kai yra dalinė atitiktis, turite naudoti pakaitos simbolius.
Pavyzdžiui, žemiau pateikiamas įmonės pavadinimo ir jų rinkos kapitalizacijos duomenų rinkinys, ir jūs norite gauti rinkos viršutinę ribą. trijų dešinėje esančių bendrovių duomenys.
Kadangi tai nėra tikslios atitiktys, šiuo atveju negalite atlikti įprastos paieškos.
Tačiau vis tiek galite gauti reikiamus duomenis naudodami žvaigždutę (*), kuri yra pakaitos simbolis.
Žemiau yra formulė, kuri suteiks jums duomenis, suderindama įmonių pavadinimus iš pagrindinio stulpelio ir gaudama jų rinkos viršutinės ribos skaičių.
= INDEKSAS ($ B $ 2: $ B $ 10, MATCH (D2 & ”*”, $ A $ 2: $ A $ 10,0), 1)
Leiskite man paaiškinti, kaip ši formulė veikia.
Kadangi tikslios paieškos verčių atitikties nėra, aš naudojau D2 ir „*“ kaip paieškos reikšmė funkcijoje MATCH.
Žvaigždutė yra pakaitos simbolis, nurodantis bet kokį simbolių skaičių. Tai reiškia, kad „Apple“* formulėje reikštų bet kokią teksto eilutę, prasidedančią žodžiu „Apple“ ir po kurios gali būti bet koks simbolių skaičius.
Todėl, kai „Apple“* yra naudojama kaip paieškos vertė, o MATCH formulė jos ieško A stulpelyje, ji grąžina „Apple Inc.“ poziciją, kuri prasideda žodžiu „Apple“.
Taip pat galite naudoti pakaitos simbolius, kad surastumėte teksto eilutes, kuriose paieškos vertė yra tarp jų. Pvz., Jei kaip paieškos vertę naudojate * „Apple“ *, ji suras bet kokią eilutę, kurioje yra žodis obuolys.
Pastaba: ši technika veikia gerai, kai turite tik vieną atitikimo egzempliorių. Bet jei turite kelis atitikimo atvejus (pvz., „Apple Inc“ ir „Apple Corporation“, funkcija MATCH grąžins tik pirmo atitikimo egzemplioriaus padėtį.
10 pavyzdys: trijų krypčių paieška
Tai yra išplėstinis „INDEX MATCH“ naudojimas, tačiau aš vis tiek jį padengsiu, kad parodyčiau šio derinio galią.
Prisiminkite, kad sakiau, kad funkcija INDEX turi dvi sintakses:
= INDEX (masyvas, eilutės numeris, [stulpelio_numeris]) = INDEX (masyvas, eilutės numeris, [stulpelio_numeris], [ploto_numeris])
Iki šiol visuose mūsų pavyzdžiuose naudojome tik pirmąjį.
Tačiau norint ieškoti trijų krypčių, turite naudoti antrąją sintaksę.
Pirmiausia leiskite paaiškinti, ką reiškia trijų krypčių išvaizda.
Dviejų krypčių paieškoje mes naudojame formulę INDEX MATCH, kad gautume pažymius, kai turime mokinio vardą ir dalyko pavadinimą. Pavyzdžiui, Jimo ženklų gavimas matematikoje yra dviejų krypčių paieška.
Trijų krypčių išvaizda pridėtų dar vieną aspektą. Pvz., Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite sužinoti Jimo rezultatą matematikos vidurio laikotarpio egzamine, tada tai būtų trijų krypčių paieška.
Žemiau yra formulė, kuri duos rezultatą.
= INDEKSAS (($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23), MATCH ($ F $ 5, $ A $ 3: $ A $ 7,0), MATCH (G $ 4 , $ B $ 2: $ D $ 2,0), (IF (G $ 3 = "Vieneto testas", 1, IF (G $ 3 = "vidurio laikotarpis", 2,3))))
Aukščiau pateikta formulė patikrino tris dalykus - studento vardą, dalyką ir egzaminą. Suradęs tinkamą vertę, jis grąžina jį į langelį.
Leiskite man paaiškinti, kaip ši formulė veikia, suskaidydama formulę į dalis.
- masyvas - ($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23): Vietoj vieno masyvo, šiuo atveju skliausteliuose panaudojau tris masyvus.
- row_num - MATCH ($ 5 $, $ A $ 3: $ A $ 7,0): Funkcija MATCH naudojama mokinio vardo pozicijai rasti studento vardo sąrašo langelyje $ F $ 5.
- col_num - MATCH (4 USD, 2 USD: 2 USD: 2,0 USD): Funkcija MATCH naudojama norint rasti objekto pavadinimo vietą ląstelės $ B $ 2 dalyko pavadinimo sąraše.
- [area_num] - IF (G $ 3 = „Vieneto testas“, 1, IF (G $ 3 = „vidurio laikotarpis“, 2,3)): Ploto skaičiaus reikšmė nurodo funkcijai INDEX, kurį iš trijų masyvų naudoti norint gauti reikšmę. Jei egzaminas yra vieneto terminas, funkcija IF grąžins 1, o funkcija INDEX reikšmei gauti naudos pirmąjį masyvą. Jei egzaminas yra vidurio laikotarpis, IF formulė grąžins 2, kitu atveju-3.
Tai yra išplėstinis INDEX MATCH naudojimo pavyzdys ir vargu ar rasite situaciją, kai turėsite tai naudoti. Tačiau vis tiek gerai žinoti, ką gali padaryti „Excel“ formulės.
Spustelėkite čia, jei norite atsisiųsti pavyzdinį failą
Kodėl INDEX/MATCH yra geresnis už VLOOKUP?
Ar tai yra?
Taip, tai yra - daugeliu atvejų.
Po kurio laiko pristatysiu savo atvejį.
Bet prieš tai darydamas, leiskite man pasakyti tai - VLOOKUP yra labai naudinga funkcija ir man tai patinka. Jis gali padaryti daug dalykų „Excel“, ir aš pats kartais jį naudoju. Tai pasakius, tai nereiškia, kad negali būti nieko geresnio, o INDEX/MATCH (su daugiau lankstumo ir funkcijų) yra geresnis.
Taigi, jei norite atlikti keletą pagrindinių paieškų, geriau naudokite VLOOKUP.
INDEX/MATCH yra steroidų VLOOKUP. Ir kai jūs išmoksite INDEX/MATCH, jūs visada norėsite jį naudoti (ypač dėl jo lankstumo).
Netempdamas jo per toli, leiskite man greitai pasakyti priežastis, kodėl INDEX/MATCH yra geresnis už VLOOKUP.
INDEX/MATCH gali ieškoti kairėje (taip pat ir dešinėje) paieškos vertės
Aš tai aprašiau viename iš aukščiau pateiktų pavyzdžių.
Jei turite vertę, kuri yra kairėje nuo paieškos vertės, to negalite padaryti naudodami VLOOKUP
Bent jau ne tik naudojant VLOOKUP.
Taip, galite sujungti VLOOKUP su kitomis formulėmis ir tai padaryti, tačiau tai tampa sudėtinga ir netvarkinga.
Kita vertus, „INDEX/MATCH“ yra skirtas ieškoti visur (ar tai būtų kairė, dešinė, aukštyn ar žemyn)
„INDEX/MATCH“ gali veikti vertikaliai ir horizontaliai
Vėlgi, visiškai gerbiant VLOOKUP, tai nėra skirta tai padaryti.
Galų gale, V in VLOOKUP reiškia vertikalę.
VLOOKUP gali peržiūrėti tik vertikalius duomenis, o INDEX/MATCH - vertikaliai ir horizontaliai.
Žinoma, yra HLOOKUP funkcija, skirta pasirūpinti horizontaliu paieška, bet tai nėra VLOOKUP … tiesa?
Man patinka tai, kad „INDEX MATCH“ derinys yra pakankamai lankstus, kad galėtų dirbti tiek su vertikaliais, tiek su horizontaliais duomenimis.
VLOOKUP negali dirbti su mažėjančiais duomenimis
Kalbant apie apytikslę atitiktį, VLOOKUP ir INDEX/MATCH yra to paties lygio.
Tačiau INDEX MATCH taiko tašką, nes taip pat gali tvarkyti mažėjančia tvarka esančius duomenis.
Parodau tai viename iš šios pamokos pavyzdžių, kur turime rasti mokinių pažymius pagal vertinimo lentelę. Jei lentelė surūšiuota mažėjančia tvarka, VLOOKUP neveiks (bet INDEX MATCH).
INDEX/MATCH gali būti šiek tiek greitesnis
Aš būsiu teisus. Aš pats neatlikau šio testo.
Aš pasikliauju „Excel“ meistro - Charley Kydo - išmintimi.
VLOOKUP ir INDEX/MATCH greičio skirtumas sunkiai pastebimas, kai turite mažų duomenų rinkinių. Bet jei turite tūkstančius eilučių ir daug stulpelių, tai gali būti lemiamas veiksnys.
Savo straipsnyje Charley Kyd teigia:
„Blogiausiu atveju„ INDEX-MATCH “metodas yra maždaug toks pat greitas kaip„ VLOOKUP “; geriausiu atveju tai daug greičiau “.
INDEX/MATCH nepriklauso nuo faktinės stulpelio padėties
Jei turite duomenų rinkinį, kaip parodyta žemiau, kai renkate Jimo rezultatą fizikoje, tai galite padaryti naudodami VLOOKUP.
Norėdami tai padaryti, VLOOKUP stulpelio numerį galite nurodyti kaip 3.
Viskas gerai.
O kas, jei ištrinsiu matematikos stulpelį.
Tokiu atveju VLOOKUP formulė sulaužys.
Kodėl? - Kadangi buvo sunku koduoti naudoti trečiąjį stulpelį, o kai ištrinu stulpelį tarp jų, trečiasis stulpelis tampa antruoju.
Naudoti INDEX/MATCH šiuo atveju yra geriau, nes stulpelio numerį galite padaryti dinamišką naudodami MATCH. Taigi vietoj stulpelio numerio jis tikrina temos pavadinimą ir naudoja jį stulpelio numeriui grąžinti.
Žinoma, tai galite padaryti derindami VLOOKUP su MATCH, bet jei vis tiek derinate, kodėl gi to nepadarius su daug lankstesniu INDEX.
Naudodami INDEX/MATCH, galite saugiai įterpti/ištrinti stulpelius savo duomenų rinkinyje.
Nepaisant visų šių veiksnių, yra priežastis, kodėl VLOOKUP yra toks populiarus.
Ir tai yra didelė priežastis.
„VLOOKUP“ lengviau naudoti
VLOOKUP apima tik keturis argumentus. Jei galite apvynioti galvą aplink šiuos keturis, galite eiti.
Ir kadangi daugumą pagrindinių paieškos atvejų tvarko ir „VLOOKUP“, ji greitai tapo populiariausia „Excel“ funkcija.
Aš tai vadinu „Excel“ funkcijų karaliumi.
Kita vertus, INDEX/MATCH naudoti yra šiek tiek sunkiau. Galite pajusti, kai pradėsite jį naudoti, tačiau pradedantiesiems VLOOKUP yra daug lengviau paaiškinti ir išmokti.
Ir tai nėra nulinės sumos žaidimas.
Taigi, jei esate naujas paieškos pasaulyje ir nežinote, kaip naudotis „VLOOKUP“, geriau to išmokite.
Turiu išsamų VLOOKUP naudojimo „Excel“ vadovą (su daugybe pavyzdžių)
Mano tikslas šiame straipsnyje nėra prieštarauti dviem nuostabioms funkcijoms. Norėjau parodyti „INDEX MATCH“ kombinacijos galią ir visus puikius dalykus, kuriuos ji gali padaryti.
Tikimės, kad šis straipsnis jums buvo naudingas.
Praneškite man savo mintis komentarų skiltyje ir, jei šioje pamokoje rasite kokių nors klaidų, praneškite man.
