Galite naudoti Excel MATCH funkciją, kad surastumėte santykinę konkrečios reikšmės padėtį langelių diapazone arba masyve.
Funkcija MATCH yra panaši į funkciją VLOOKUP, nes jos abi suskirstytos į „Excel“ paieškos / nuorodos funkcijas. VLOOKUP ieško konkrečios reikšmės stulpelyje ir grąžina reikšmę toje pačioje eilutėje, o funkcija MATCH ieško tam tikros vertės diapazone ir grąžina tos reikšmės padėtį.
Funkcija „Excel MATCH“ ieško nurodytos reikšmės langelių diapazone arba masyve ir grąžina santykinę tos reikšmės atsiradimo diapazone vietą. Funkcija MATCH taip pat gali būti naudojama norint surasti tam tikrą reikšmę ir grąžinti atitinkamą reikšmę naudojant funkciją INDEX (kaip ir Vlookup). Pažiūrėkime, kaip naudoti „Excel MATCH“ funkciją norint rasti paieškos reikšmės vietą langelių diapazone.
„Excel“ MATCH funkcija
Funkcija MATCH yra įtaisyta „Excel“ funkcija ir pirmiausia naudojama santykinei peržvalgos reikšmės vietai stulpelyje arba eilutėje nustatyti.
Funkcijos MATCH sintaksė:
=MATCH(paieškos_vertė,paieškos_masyvas,[atitikties_tipas})Kur:
lookup_value – Reikšmė, kurią norite ieškoti nurodytame langelių diapazone arba masyve. Tai gali būti skaitinė reikšmė, teksto reikšmė, loginė reikšmė arba langelio nuoroda, turinti reikšmę.
lookup_masyvas – langelių masyvai, kuriuose ieškote reikšmės. Tai turi būti vienas stulpelis arba viena eilutė.
atitikimo_tipas – Tai pasirenkamas parametras, kurį galima nustatyti į 0, 1 arba -1, o numatytoji vertė yra 1.
- 0 ieško tikslios atitikties, kai jos nerandama, pateikia klaidą.
- -1 ieško mažiausios reikšmės, kuri yra didesnė arba lygi lookup_value, kai peržvalgos masyvas didėja didėjančia tvarka.
- 1 ieško didžiausios reikšmės, kuri yra mažesnė arba lygi look_up reikšmei, kai peržvalgos masyvas mažėjimo tvarka.
Raskite tikslios atitikties poziciją
Tarkime, kad turime šį duomenų rinkinį, kuriame norime rasti tam tikros reikšmės padėtį.
Šioje lentelėje norime rasti miesto pavadinimo vietą (Memfis) stulpelyje (A2:A23), todėl naudojame šią formulę:
=MATCH("memfis",A2:A23,0)Trečiasis argumentas nustatytas į „0“, nes norime rasti tikslią miesto pavadinimo atitiktį. Kaip matote, miesto pavadinimas „memfis“ formulėje rašomas mažosiomis raidėmis, o lentelėje pirmoji miesto pavadinimo raidė – didžiąja (Memfis). Vis dėlto formulė gali rasti nurodytos reikšmės vietą duotame diapazone. Taip yra todėl, kad funkcija MATCH neskiria didžiųjų ir mažųjų raidžių.
Pastaba: Jei lookup_value nerasta peržvalgos diapazone arba jei nurodote neteisingą paieškos diapazoną, funkcija grąžins klaidą #N/A.
Pirmajame funkcijos argumente vietoj tiesioginės reikšmės galite naudoti langelio nuorodą. Toliau pateikta formulė nustato reikšmės vietą F2 langelyje ir pateikia rezultatą langelyje F3.
Raskite apytikslę atitikmens poziciją
Yra du būdai, kaip ieškoti apytikslės arba tikslios paieškos vertės atitikties ir grąžinti jos poziciją.
- Vienas iš būdų yra rasti mažiausią reikšmę, kuri yra didesnė arba lygi (kita didžiausia atitiktis) nurodytai vertei. Tai galima pasiekti nustačius paskutinį funkcijos argumentą (match_type) kaip „-1“
- Kitas būdas yra didžiausia reikšmė, kuri yra mažesnė arba lygi (kita mažiausia atitiktis) nurodytai vertei. Tai galima pasiekti nustačius funkcijos match_type kaip „1“
Kitas mažiausias atitikmuo
Jei funkcija negali rasti tikslios nurodytos reikšmės atitikties, kai atitikties tipas nustatytas į '1', ji suranda didžiausią reikšmę, kuri yra šiek tiek mažesnė už nurodytą reikšmę (tai reiškia kitą mažiausią reikšmę) ir grąžina savo poziciją. . Kad tai veiktų, turite rūšiuoti masyvą didėjančia tvarka, jei ne, tai sukels klaidą.
Pavyzdyje naudojame toliau pateiktą formulę, kad surastume kitą mažiausią atitiktį:
=MATCH(F2,D2:D23,1)Kai ši formulė negali rasti tikslios F2 langelio reikšmės atitikties, ji nurodo kitos mažiausios reikšmės, ty 98, padėtį (16).
Kitas didžiausias mačas
Kai atitikties tipas nustatytas į „-1“, o funkcija MATCH negali rasti tikslios atitikties, ji suranda mažiausią reikšmę, kuri yra didesnė už nurodytą reikšmę (tai reiškia kitą didžiausią reikšmę), ir grąžina savo poziciją. Šio metodo paieškos masyvas turi būti surūšiuotas mažėjimo tvarka, kitaip bus pateikta klaida.
Pavyzdžiui, įveskite šią formulę, kad rastumėte kitą didžiausią paieškos reikšmės atitiktį:
=MATCH(F2,D2:D23,-1)Ši funkcija MATCH ieško reikšmės F2 (55) peržvalgos diapazone D2:D23 ir, kai negali rasti tikslios atitikties, grąžina kitos didžiausios reikšmės poziciją (16), ty 58.
Pakaitos simbolių rungtynės
Pakaitos simboliai gali būti naudojami funkcijoje MATCH tik tada, kai match_type nustatytas į 0, o paieškos reikšmė yra teksto eilutė. Funkcijoje MATCH galite naudoti pakaitos simbolius: žvaigždutę (*) ir klaustuką (?).
- Klaustukas (?) naudojamas norint suderinti bet kurį vieną simbolį ar raidę su teksto eilute.
- Žvaigždutė (*) naudojamas norint suderinti bet kokį simbolių skaičių su eilute.
Pavyzdžiui, MATCH funkcijos lookup_value (Lo??n) naudojome du pakaitos simbolius „?“, kad rastume reikšmę, atitinkančią teksto eilutę su bet kuriais dviem simboliais (pakaitos simbolių vietose). O funkcija grąžina atitinkamos reikšmės santykinę padėtį langelyje E5.
=MATCH("Ne",A2:A22,0)
Galite naudoti (*) pakaitos simbolį taip pat, kaip (?), bet žvaigždutė naudojama bet kokiam simbolių skaičiui atitikti, o klaustukas – bet kuriam atskiram simboliui.
Pavyzdžiui, jei naudojate „sp*“, funkcija gali atitikti garsiakalbį, greitį ar spielbergą ir kt. Tačiau jei funkcija randa kelias / pasikartojančias reikšmes, atitinkančias paieškos reikšmę, ji grąžins tik pirmosios reikšmės poziciją.
Pavyzdyje argumente lookup_value įvedėme „Kil*o“. Taigi funkcija MATCH() ieško teksto, kurio pradžioje yra „Kil“, pabaigoje „o“ ir bet koks simbolių skaičius tarp jų. „Kil*o“ atitinka Kilimandžarą masyve, todėl funkcija grąžina santykinę Kilimandžaro padėtį, kuri yra 16.
INDEX ir MATCH
MATCH funkcijos retai naudojamos atskirai. Jie dažnai susiejami su kitomis funkcijomis, kad sukurtų galingas formules. Kai funkcija MATCH derinama su funkcija INDEX, ji gali atlikti išplėstines paieškas. Daugelis žmonių vis dar nori naudoti VLOOKUP, norėdami ieškoti reikšmės, nes tai paprastesnė, tačiau INDEX MATCH yra lankstesnė ir greitesnė nei VLOOKUP.
VLOOKUP gali ieškoti vertės tik vertikaliai, t. y. stulpelių, o INDEX MATCH derinys gali atlikti vertikalią ir horizontalią paiešką.
Funkcija INDEX, naudojama norint gauti reikšmę konkrečioje lentelės arba diapazono vietoje. Funkcija MATCH grąžina santykinę reikšmės padėtį stulpelyje arba eilutėje. Sujungus, MATCH suranda konkrečios reikšmės eilutės arba stulpelio numerį (vietovę), o funkcija INDEX nuskaito reikšmę pagal tos eilutės ir stulpelio numerius.
Funkcijos INDEX sintaksė:
=INDEX(masyvas,eilutės_skaičius,[stulpelio_skaičius],)Bet kokiu atveju pažiūrėkime, kaip veikia INDEX MATCH su pavyzdžiu.
Toliau pateiktame pavyzdyje norime gauti mokinio „Anos“ „Quiz2“ balą. Norėdami tai padaryti, naudosime žemiau pateiktą formulę:
=INDEKSAS(B2:F20,MATCH(H2,A2:A20,0),3)INDEX reikalingas eilutės ir stulpelio numeris, kad būtų galima gauti reikšmę. Aukščiau pateiktoje formulėje įdėta MATCH funkcija suranda reikšmės „Ane“ (H2) eilutės numerį (poziciją). Tada pateikiame tą eilutės numerį funkcijai INDEX su diapazonu B2:F20 ir stulpelio numeriu (3), kurį nurodome. O funkcija INDEX grąžina rezultatą „91“.
Dviejų krypčių paieška su INDEX ir MATCH
Taip pat galite naudoti funkcijas INDEX ir MATCH norėdami ieškoti reikšmės dvimačiame diapazone (dvipusė paieška). Aukščiau pateiktame pavyzdyje naudojome funkciją MATCH, kad surastume reikšmės eilutės numerį, tačiau stulpelio numerį įvedėme rankiniu būdu. Tačiau galime rasti ir eilutę, ir stulpelį, sudėję dvi MATCH funkcijas, vieną į INDEX funkcijos argumentą row_num, o kitą - į INDEX funkcijos argumentą column_num.
Naudokite šią formulę dvipusei paieškai su INDEX ir MATCH:
=INDEKSAS(A1:F20,ATTIKTIS(H2,A2:A20,0),MATCH(H3,A1:F1,0))Kaip žinome, funkcija MATCH gali ieškoti reikšmės tiek horizontaliai, tiek vertikaliai. Šioje formulėje antroji MATCH funkcija, esanti argumente coum_num, suranda Quiz2 (4) padėtį ir pateikia ją funkcijai INDEX. O RODYKLĖ nuskaito rezultatą.
Dabar jūs žinote, kaip naudoti „Excel“ funkciją „Match“.