Pridobite prvo ujemajočo se vrednost v celici na seznamu
Recimo, da imate seznam ključnih besed, za pridobitev prve ključne besede, ki se je pojavila v določeni celici, medtem ko celica vsebuje več drugih vrednosti, boste morali uporabiti INDEX in MATCH formulo s pomočjo AGREGAT in ISKANJE funkcije.
Kako pridobiti prvo ujemajočo se vrednost v celici na seznamu?
Da bi dobili prva ujemajočo se ključno besedo v celici na seznamu ključnih besed kot je prikazana zgornja tabela, to pomeni, da moramo izvesti vsebno ujemanje in ne natančno ujemanje. Če želite to narediti, lahko s funkcijo ISKANJE vnesete položaje ključnih besed, prikazanih v celici v številskih številkah, v funkcijo AGREGATE. Potem bo AGREGATE dobil najmanjše število, tako da nastavi njegovo funkcija_num na 15 in argument ref2 na 1. Nato uporabite MATCH, da poiščete prvo najmanjšo vrednost in podate številko položaja v INDEX, da pridobite vrednost na tem mestu.
Splošna skladnja
=INDEX(keyword_rng,MATCH(AGGREGATE(15,6,SEARCH(keyword_rng,lookup_cell),1),SEARCH(keyword_rng,lookup_cell),0))
√ Opomba: To je matrična formula, ki zahteva, da vnesete Ctrl + Shift + Vnesite.
- keyword_rng: Obseg celic s ključnimi besedami.
- iskalna_celica: Celica za iskanje, če vsebuje ključne besede.
Za pridobitev prva ustrezna ključna beseda se je pojavila v celici B5 v stolpcu Ključne besede, kopirajte ali vnesite spodnjo formulo v celico C5 in pritisnite Ctrl + Shift + Vnesite da dobite rezultat:
= INDEX ($ 5 $: $ 7 $,MACH(AGREGATE(15,6,ISKANJE($ 5 $: $ 7 $,B5),1),ISKANJE($ 5 $: $ 7 $,B5), 0))
√ Opomba: Zgornji znaki za dolar ($) označujejo absolutne reference, kar pomeni ključna_rng v formuli se ne bo spremenilo, ko jo premaknete ali kopirate v druge celice. Vendar pa ni dodanih znakov dolarja iskalna_celica ker želite, da je dinamičen. Ko vnesete formulo, povlecite ročico za polnjenje navzdol, da uporabite formulo v spodnjih celicah.
Pojasnilo formule
=INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0))
- ISKANJE($E$5:$E$7,B5): Funkcija SEARCH vrne položaj vsake ključne besede iz obsega $ 5 $: $ 7 $ ko najdemo v številskih številkah, in #VALUE! napaka, če ni najdena. Rezultat bi bil v matriki, kot je ta: {15;11;#VREDNOST!}.
- AGREGATE(15,6,ISKANJE($E$5:$E$7,B5), 1) = AGREGATE(15,6,{15;11;#VREDNOST!}, 1): Funkcija AGREGATE z a funkcija_število 15 in možnost 6 bo vrnil najmanjšo vrednost v matriki glede na argument ref2 1, medtem ko prezremo vrednosti napak. Torej bi se odrezek vrnil 11.
- UJEMA (AGREGATE(15,6,ISKANJE($E$5:$E$7,B5), 1),ISKANJE($E$5:$E$7,B5), 0) = UJEMA (11,{15;11;#VREDNOST!}, 0): O match_type 0 prisili funkcijo MATCH, da izvede natančno ujemanje in vrne položaj 11 v matriki {15;11;#VREDNOST!}. Torej se bo funkcija vrnila 2.
- INDEKS($E$5:$E$7,UJEMA (AGREGATE(15,6,ISKANJE($E$5:$E$7,B5), 1),ISKANJE($E$5:$E$7,B5), 0)) = INDEKS($E$5:$E$7,2): Funkcija INDEX nato vrne 2nd vrednost v območju $ 5 $: $ 7 $, Ki je bbb.
Opomba
- Če v celici ni ključnih besed, a #ŠT.! bo vrnjena napaka.
- Formula ni občutljiva na velike in male črke. Če želite izvesti ujemanje, ki razlikuje velike in male črke, lahko preprosto zamenjate funkcijo SEARCH z POIŠČITE.
Sorodne funkcije
Funkcija Excel INDEX vrne prikazano vrednost na podlagi danega položaja iz obsega ali matrike.
Funkcija Excel MATCH išče določeno vrednost v obsegu celic in vrne relativni položaj vrednosti.
V Excelu vam funkcija SEARCH lahko pomaga najti položaj določenega znaka ali podniza iz danega besedilnega niza, kot je prikazano na spodnji sliki zaslona. V tej vadnici vam bom predstavil, kako uporabljati to funkcijo SEARCH v Excelu.
Excelova funkcija AGREGATE vrne agregat izračunov, kot so SUM, COUNT, SMALLL in tako naprej z možnostjo prezrenja napak in skritih vrstic.
Sorodne formule
Pridobite prvo vrednost seznama iz celice
Če želite iz določene celice pridobiti prvo ključno besedo, ki je navedena v obsegu, medtem ko celica vsebuje eno od več vrednosti, boste morali uporabiti precej zapleteno formulo matrike s funkcijami INDEX, MATCH, ISNUMBER in SEARCH.
Natančno ujemanje z INDEX in MATCH
Če želite izvedeti podatke, navedene v Excelu, o določenem izdelku, filmu ali osebi itd., Bi morali dobro uporabiti kombinacijo funkcij INDEX in MATCH.
Preverite, ali celica vsebuje določeno besedilo
Tu vadnica ponuja nekaj formul, s katerimi lahko preverite, ali celica vsebuje določeno besedilo, in vrne TRUE in FALSE, kot je prikazano spodaj, in razloži argumente ter kako formule delujejo.
Preverite, ali celica vsebuje veliko stvari
Recimo, da je v Excelu seznam vrednosti v stolpcu E, želite preveriti, ali celice v stolpcu B vsebujejo vse vrednosti v stolpcu E, in vrnite TRUE ali FALSE, kot je prikazano spodaj. Tu v tej vadnici vsebuje formulo za rešitev te naloge.
Preverite, ali celica vsebuje eno od mnogih stvari
Ta vadnica vsebuje formulo za preverjanje, ali celica vsebuje eno od več vrednosti v Excelu, in razlaga argumente v formuli in kako formula deluje.
Najboljša orodja za pisarniško produktivnost
Kutools za Excel - vam pomaga izstopati iz množice
Kutools za Excel se ponaša z več kot 300 funkcijami, Zagotavljanje, da je vse, kar potrebujete, le en klik stran ...
Kartica Office - omogočite branje in urejanje z zavihki v programu Microsoft Office (vključite Excel)
- Eno sekundo za preklop med desetinami odprtih dokumentov!
- Vsak dan zmanjšajte na stotine klikov z miško, poslovite se od roke miške.
- Poveča vašo produktivnost za 50% pri ogledu in urejanju več dokumentov.
- Prinaša učinkovite zavihke v Office (vključno z Excelom), tako kot Chrome, Edge in Firefox.
