Preluați prima valoare care se potrivește din celulă cu o listă
Să presupunem că aveți o listă de cuvinte cheie, pentru a prelua primul cuvânt cheie care a apărut într-o anumită celulă, în timp ce celula conține alte câteva valori, va trebui să utilizați un INDEX și MECI formula cu ajutorul AGREGAT și Căutare funcții.
Cum se recuperează prima valoare care se potrivește din celulă cu o listă?
Pentru a obține primul cuvânt cheie potrivit dintr-o celulă cu lista de cuvinte cheie ca în tabelul prezentat mai sus, înseamnă că trebuie să efectuăm o potrivire de conținut, mai degrabă decât o potrivire exactă. Pentru a face acest lucru, puteți folosi funcția CĂUTARE pentru a introduce pozițiile cuvintelor cheie apărute în celulă în numere numerice în funcția AGGREGATE. Apoi AGGREGATE va obține cel mai mic număr setându-l function_num la 15 și argumentul ref2 la 1. Apoi utilizați MATCH pentru a localiza prima cea mai mică valoare și transmiteți numărul poziției la INDEX pentru a prelua valoarea din acea poziție.
Sintaxa generică
=INDEX(keyword_rng,MATCH(AGGREGATE(15,6,SEARCH(keyword_rng,lookup_cell),1),SEARCH(keyword_rng,lookup_cell),0))
√ Notă: Aceasta este o formulă matrice care necesită să introduceți cu Ctrl + Schimba + Intrați.
- keyword_rng: Gama de celule cu cuvinte cheie.
- celulă_căutare: Celula de căutat dacă conține cuvintele cheie.
Pentru a recupera primul cuvânt cheie potrivit a apărut în celula B5 în coloana Cuvinte cheie, copiați sau introduceți formula de mai jos în celula C5 și apăsați Ctrl + Schimba + Intrați pentru a obține rezultatul:
= INDEX ($ E $ 5: $ E $ 7,MATCH(AGGREGATE(15,6,SEARCH($ E $ 5: $ E $ 7,B5),1),CAUTARE($ E $ 5: $ E $ 7,B5), 0))
√ Notă: semnele dolarului ($) de mai sus indică referințe absolute, ceea ce înseamnă keyword_rng în formulă nu se va modifica atunci când mutați sau copiați formula în alte celule. Cu toate acestea, nu există semne de dolar adăugate la celulă_căutare deoarece vrei să fie dinamică. După ce ați introdus formula, trageți mânerul de umplere în jos pentru a aplica formula celulelor de mai jos.
Explicația formulei
=INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0))
- CĂUTARE($E$5:$E$7,B5): Funcția SEARCH returnează poziția fiecărui cuvânt cheie din interval $ E $ 5: $ E $ 7 când se găsește în numere numerice și #VALOARE! eroare dacă nu este găsită. Rezultatul ar fi într-o matrice ca aceasta: {15;11;#VALOARE!}.
- AGREGAT(15,6,CĂUTARE($E$5:$E$7,B5), 1) = AGREGAT(15,6,{15;11;#VALOARE!}, 1): Funcția AGREGATE cu a function_num of 15 și varianta de 6 va returna cea mai mică valoare din matrice în conformitate cu ref2 argumentul 1, ignorând în același timp valorile de eroare. Deci, fragmentul ar reveni 11.
- MECI(AGREGAT(15,6,CĂUTARE($E$5:$E$7,B5), 1),CĂUTARE($E$5:$E$7,B5), 0) = MECI(11,{15;11;#VALOARE!}, 0): tip_potrivire 0 forțează funcția MATCH să efectueze o potrivire exactă și să returneze poziția de 11 în matrice {15;11;#VALOARE!}. Deci, funcția va reveni 2.
- INDEX($E$5:$E$7,MECI(AGREGAT(15,6,CĂUTARE($E$5:$E$7,B5), 1),CĂUTARE($E$5:$E$7,B5), 0)) = INDEX($E$5:$E$7,2): Funcția INDEX returnează apoi 2a doua valoare din interval $ E $ 5: $ E $ 7, Care este bbb.
Observație
- Dacă nu există cuvinte cheie într-o celulă, a #PE UNU! eroarea va fi returnată.
- Formula nu face distincție între majuscule și minuscule. Pentru a efectua o potrivire sensibilă la majuscule și minuscule, puteți înlocui pur și simplu funcția CĂUTARE cu FIND.
Funcții conexe
Funcția Excel INDEX returnează valoarea afișată pe baza unei poziții date dintr-un interval sau dintr-o matrice.
Funcția Excel MATCH caută o anumită valoare într-un interval de celule și returnează poziția relativă a valorii.
În Excel, funcția CĂUTARE vă poate ajuta să găsiți poziția unui anumit caracter sau a unui șir din șirul de text dat, după cum se arată în următoarea captură de ecran. Acest tutorial, vă voi prezenta cum să utilizați această funcție de CĂUTARE în Excel.
Funcția Excel AGGREGATE returnează un agregat de calcule, cum ar fi SUM, COUNT, SMALLL și așa mai departe, cu opțiunea de a ignora erorile și rândurile ascunse.
Formule conexe
Preluați prima valoare a listei dintr-o celulă
Pentru a prelua primul cuvânt cheie care este listat într-un interval dintr-o anumită celulă, în timp ce celula conține una dintre mai multe valori, va trebui să utilizați o formulă matrice destul de complexă cu funcțiile INDEX, MATCH, ISNUMBER și SEARCH.
Potrivire exactă cu INDEX și MATCH
Dacă trebuie să aflați informațiile listate în Excel despre un anumit produs, film sau o persoană etc., ar trebui să folosiți bine combinația de funcții INDEX și MATCH.
Verificați dacă o celulă conține un anumit text
Aici tutorialul oferă câteva formule pentru a verifica dacă o celulă conține un anumit text și pentru a returna ADEVĂRAT și FALS așa cum este prezentat mai jos, și explică argumentele și modul în care funcționează formulele.
Verificați dacă celula conține multe lucruri
Presupunând că în Excel există o listă de valori în coloana E, doriți să verificați dacă celulele din coloana B conțin toate valorile din coloana E și să returnați ADEVĂRAT sau FALS așa cum este prezentat mai jos. Aici, în acest tutorial, oferă o formulă pentru rezolvarea acestei sarcini.
Verificați dacă celula conține unul dintre multele lucruri
Acest tutorial oferă o formulă pentru a verifica dacă o celulă conține una din mai multe valori în Excel și explică argumentele din formulă și modul în care funcționează formula.
Cele mai bune instrumente de productivitate Office
Kutools pentru Excel - Vă ajută să vă distingeți de mulțime
Kutools pentru Excel se mândrește cu peste 300 de caracteristici, Asigurați-vă că ceea ce aveți nevoie este la doar un clic distanță...
Fila Office - Activați lectura și editarea cu file în Microsoft Office (includeți Excel)
- O secundă pentru a comuta între zeci de documente deschise!
- Reduceți sute de clicuri de mouse pentru dvs. în fiecare zi, spuneți adio de la mâna mouse-ului.
- Vă crește productivitatea cu 50% atunci când vizualizați și editați mai multe documente.
- Aduce file eficiente la Office (inclusiv Excel), la fel ca Chrome, Edge și Firefox.
