Căutarea caracterelor dintr-un șir este cea mai importantă sarcină care vă permite să automatizați lucrul cu text. De exemplu, ușurează atunci când poziția liniei țintă este necunoscută.
Am atins instrumentul de căutare Excel în , dar aici ne vom uita la funcțiile care caută text într-un șir și returnează poziția sa de pornire.
Există două funcții pentru căutarea textului în Microsoft Excel:
- GĂSI()
. Această funcție are 2 argumente obligatorii și unul opțional:
- Căutați text– succesiunea de caractere care trebuie găsită. Acesta este un argument necesar
- Text original– șirul în care se efectuează căutarea. Argument necesar
- Poziția de pornire– numărul de serie al personajului din textul sursă de la care începe căutarea. Argument opțional. Dacă nu este specificat, căutarea începe de la primul caracter
Funcția FIND ia în considerareȘi nu acceptă utilizarea wildcard-urilor(citiți mai departe despre ele).
Funcția FIND în Excel - CĂUTARE(Căutare text; Text original; [Poziția de pornire]) are sintaxă și argumente similare. Diferă prin aceea insensibil la majusculeȘi acceptă utilizarea metacaracterilor.
Funcția CĂUTARE în Excel După cum puteți vedea, această funcție a dat un rezultat diferit, deoarece căutarea nu a fost sensibilă la majuscule și minuscule.
Ce sunt metacaracterele, te întrebi? Acestea sunt caractere speciale care vă permit să săriți peste o parte necunoscută a textului pentru căutare:
- ? – orice simbol
- * – orice succesiune de caractere
Dacă semnul de întrebare sau asteriscul face parte din șirul de căutare și nu un caracter joker, precedați-l cu un tilde (~).
Să notăm următoarea formulă: =SEARCH(“??chn”,A1,1). Funcția va căuta un șir format din oricare două caractere urmate de literele „chn”. Căutarea se potrivește cu o parte a cuvântului „Tabular” din textul exemplu, de exemplu. combinație de caractere „personal”. Rezultatul funcției va fi valoarea „4”.
Wildcards în Excel Acum să scriem formula =SEARCH(“p*s”,A2,1) . Funcția caută combinația „p” + orice număr de orice caractere + „s”. În exemplu, aceasta face parte din cuvântul „procesor”. Rezultatul funcției este 11.
Wildcard asterisc în Excel Având funcțiile de căutare, vă este mai ușor să urmăriți poziția textului dorit, astfel încât este mai ușor să îl înlocuiți. Asigurați-vă că exersați folosirea lor. Post-tutorialul meu despre funcțiile text vă va ajuta în acest sens.
Și următorul articol va fi dedicat. Un subiect important care trebuie studiat cu siguranță!
Pune întrebări în comentarii și abonează-te la actualizări pentru a nu pierde nimic!
Acest articol descrie sintaxa formulei și utilizarea funcției CĂUTAREȘi CĂUTAREîn Microsoft Excel.
Descriere
Funcții CĂUTAREȘI CĂUTARE găsiți un șir de text în altul și returnați poziția de pornire a primului șir de text (numărând de la primul caracter al celui de-al doilea șir de text). De exemplu, pentru a găsi poziția literei „n” în cuvântul „printer”, puteți folosi următoarea funcție:
CĂUTARE("n","imprimantă")
Această funcție revine 4 , deoarece „n” este al patrulea caracter din cuvântul „printer”.
Puteți găsi cuvinte și cu alte cuvinte. De exemplu, funcția
CĂUTARE("bază","bază de date")
se intoarce 5 , deoarece cuvântul „bază” începe cu al cincilea caracter al cuvântului „bază de date”. Pot fi folosite funcții CĂUTAREȘi CĂUTARE pentru a determina poziția unui caracter sau a unui șir de text într-un alt șir de text și apoi a returna textul folosind funcții PSTRȘi PSTRB sau înlocuiți-l cu funcții A INLOCUIȘi A INLOCUI. Aceste funcții sunt prezentate în acest articol.
Important:
Este posibil ca aceste funcții să nu fie disponibile în toate limbile.
Funcția SEARCH numără doi octeți pe caracter numai dacă limba implicită este o limbă activată pentru BDC. În caz contrar, funcția SEARCH funcționează la fel ca și funcția SEARCH și numără un octet pe caracter.
Limbile care acceptă BDCS includ japoneză, chineză simplificată, chineză tradițională și coreeană.
Sintaxă
SEARCH(search_text, viewed_text, [start_position])
CĂUTARE(text_căutare, text_vizual, [poziție_pornire])
Argumentele pentru funcțiile SEARCH și SEARCHHB sunt descrise mai jos.
Căutare_text Necesar. Textul pe care doriți să-l găsiți.
Text_vizualizat Necesar. Text în care să găsiți valoarea argumentului text_căutare .
Start_position Opțional. Numărul caracterului din argument text_vizual , unde ar trebui să începeți căutarea.
cometariu
Funcții CĂUTAREȘi CĂUTARE nu sunt sensibile la majuscule. Dacă trebuie să distingeți majuscule și minuscule, utilizați funcțiile GĂSIȘi NIGHTIB.
În argumentare text_căutare Puteți folosi metacaracterele: semnul întrebării ( ? ) și un asterisc ( * ). Un semn de întrebare se potrivește cu orice caracter, un asterisc se potrivește cu orice secvență de caractere. Dacă doriți să găsiți un semn de întrebare sau un asterisc, introduceți un tilde ( ~ ).
Dacă valoarea argumentului text_căutare nu a fost găsit, #VALOARE! valoarea de eroare returnată.
Dacă argumentul poziție_de_pornire este omis, apoi este setat la 1.
Dacă Start_position nu mai mult de 0 sau mai mare decât lungimea argumentului text_vizual , #VALOARE! valoarea de eroare returnată.
Argument poziție_de_pornire poate fi folosit pentru a sări peste un anumit număr de caractere. Să presupunem că funcția CĂUTARE ar trebui să fie folosit pentru a lucra cu șirul de text „MDS0093.Haine pentru bărbați”. Pentru a găsi prima apariție a lui „M” în porțiunea descriptivă a unui șir de text, setați argumentul la poziție_de_pornire valoarea este 8, astfel încât căutarea să nu fie efectuată în partea de text care este numărul de serie (în acest caz, „MDS0093”). Funcţie CĂUTAREîncepe căutarea de la al optulea caracter, găsește caracterul specificat în argument text_căutare , în următoarea poziție și returnează numărul 9. Funcția CĂUTARE returnează întotdeauna numărul caracterului, numărând de la început a textului care este vizualizat , inclusiv caracterele care sunt omise dacă valoarea argumentului este poziție_de_pornire mai mult de 1.
Exemple
Copiați eșantionul de date din următorul tabel și inserați-l în celula A1 a unei noi foi de lucru Excel. Pentru a afișa rezultatele formulelor, selectați-le și apăsați F2, apoi apăsați Enter. Dacă este necesar, modificați lățimea coloanelor pentru a vedea toate datele.
Date |
||
|---|---|---|
|
Venituri: marja |
||
|
„Șeful” aici. |
||
|
Formulă |
Descriere |
Rezultat |
|
CĂUTARE(„și”;A2;6) |
Poziția primului „și” din rândul celulei A2, începând cu al șaselea caracter. |
|
|
CĂUTARE(A4;A3) |
Poziția de pornire a rândului „marjă” (rândul căutat este în celula A4) în rândul „Venit: marjă” (celula căutată este A3). |
|
|
ÎNLOCUIȚI(A3,CĂUTARE(A4,A3),6,„volum”) |
Înlocuiește cuvântul „marjă” cu cuvântul „volum” prin localizarea cuvântului „marjă” în celula A3 și înlocuind acel caracter și următoarele cinci caractere cu șirul de text „volum”. |
Venituri: volum |
|
PSTR(A3;CĂUTARE(" ";A3)+1,4) |
Returnează primele patru caractere care urmează primul spațiu din rândul Venituri: Marjă (celula A3). |
|
|
CĂUTARE("""";A5) |
Poziția primei ghilimele duble ("") în celula A5. |
|
|
PSTR(A5;CĂUTARE("""";A5)+1;CĂUTARE("""";A5;CĂUTARE("""";A5)+1)-CĂUTARE("""";A5)-1) |
Returnează numai textul cuprins între ghilimele duble din celula A5. |
Acest tutorial explică principalele beneficii ale funcțiilor INDEXȘi CĂUTAREîn Excel, ceea ce le face mai atractive în comparație cu CĂUTARE V. Veți vedea câteva exemple de formule care vă vor ajuta să faceți față cu ușurință la multe sarcini complexe cu care se confruntă funcția. CĂUTARE V neputincios.
În mai multe articole recente, am depus toate eforturile pentru a explica elementele de bază ale funcției utilizatorilor începători. CĂUTARE Vși arată exemple de formule mai complexe pentru utilizatorii avansați. Vom încerca acum, dacă nu vă descurajăm să utilizați CĂUTARE V, apoi cel puțin afișați modalități alternative de implementare a căutării verticale în Excel.
De ce avem nevoie de asta? - tu intrebi. Da deoarece CĂUTARE V Căutarea nu este singura funcție de căutare din Excel și numeroasele sale limitări vă pot împiedica să obțineți rezultatele dorite în multe situații. Pe de altă parte, funcțiile INDEXȘi CĂUTARE– mai flexibile și au o serie de caracteristici care le fac mai atractive în comparație cu CĂUTARE V.
Informații de bază despre INDEX și MATCH
Deoarece scopul acestui tutorial este de a arăta capacitățile funcțiilor INDEXȘi CĂUTARE pentru a implementa căutarea verticală în Excel, nu ne vom opri asupra sintaxei și aplicației acestora.
Aici prezentăm minimul necesar pentru a înțelege esența, iar apoi vom examina în detaliu exemple de formule care arată avantajele utilizării INDEXȘi CĂUTAREîn loc de CĂUTARE V.
INDEX – sintaxa și utilizarea funcției
Funcţie INDEX(INDEX) în Excel returnează o valoare dintr-o matrice la numerele de rând și de coloană date. Funcția are această sintaxă:
Fiecare argument are o explicație foarte simplă:
- matrice(matrice) este intervalul de celule din care doriți să extrageți valoarea.
- row_num(număr_linie) este numărul liniei din matrice din care doriți să extrageți valoarea. Dacă nu este specificat, atunci este necesar un argument coloană_num(număr_coloană).
- coloană_num(număr_coloană) este numărul coloanei din tabloul din care doriți să extrageți valoarea. Dacă nu este specificat, atunci este necesar un argument row_num(Numărul de linie)
Dacă sunt specificate ambele argumente, atunci funcția INDEX returnează valoarea din celula de la intersecția rândului și coloanei specificate.
Iată un exemplu simplu de funcție INDEX(INDEX):
INDEX(A1:C10,2,3)
=INDEX(A1:C10,2,3)
Formula caută un interval A1:C10și returnează valoarea celulei în al 2-lea linie și 3m coloană, adică dintr-o celulă C2.
Foarte simplu, nu? Cu toate acestea, în practică, nu știți întotdeauna de ce rând și coloană aveți nevoie și, prin urmare, aveți nevoie de ajutorul funcției CĂUTARE.
MATCH - sintaxa și utilizarea funcției
Funcţie MECI(MATCH) în Excel caută o valoare specificată într-un interval de celule și returnează poziția relativă a acelei valori în interval.
De exemplu, dacă se află în interval B1:B3 conține valorile New-York, Paris, Londra, apoi următoarea formulă va returna numărul 3 , deoarece „Londra” este al treilea element din listă.
MATCH(„Londra”, B1:B3,0)
=POTIRE(„Londra”;B1:B3;0)
Funcţie MECI(MATCH) are următoarea sintaxă:
MATCH(valoare_căutare,matrice_căutare,)
MATCH(valoare_căutare, matrice_căutare, [tip_potrivire])
- valoare_căutare(search_value) este numărul sau textul pe care îl căutați. Argumentul poate fi o valoare, inclusiv un boolean, sau o referință de celulă.
- matrice_căutare(viewed_array) – intervalul de celule în care are loc căutarea.
- tip_potrivire(match_type) – Acest argument spune funcția CĂUTARE, indiferent dacă doriți să găsiți o potrivire exactă sau aproximativă:
- 1 sau nu este specificat– găsește valoarea maximă mai mică sau egală cu cea dorită. Matricea care este vizualizată trebuie ordonată în ordine crescătoare, adică de la cel mai mic la cel mai mare.
- 0 – găsește prima valoare egală cu cea dorită. Pentru combinație INDEX/CĂUTARE aveți întotdeauna nevoie de o potrivire exactă, deci al treilea argument al funcției CĂUTARE trebuie să fie egal 0 .
- -1 – găsește cea mai mică valoare mai mare sau egală cu valoarea de căutare. Matricea care este vizualizată trebuie sortată în ordine descrescătoare, adică de la cel mai mare la cel mai mic.
La prima vedere, beneficiul funcției CĂUTARE este îndoielnic. Cine trebuie să cunoască poziția unui element într-un interval? Vrem să știm semnificația acestui element!
Să vă reamintim că poziția relativă a valorii pe care o căutăm (adică numărul rândului și/sau al coloanei) este exact ceea ce trebuie să specificăm pentru argumente row_num(număr_linie) și/sau coloană_num(număr_coloană) funcții INDEX(INDEX). După cum vă amintiți, funcția INDEX poate returna valoarea la intersecția rândului și coloanei date, dar nu poate determina de ce rând și coloană ne interesează.
Cum se utilizează INDEX și MATCH în Excel
Acum că știți informațiile de bază despre aceste două funcții, cred că deja devine clar cum funcționează CĂUTAREȘi INDEX pot lucra împreună. CĂUTARE determină poziția relativă a valorii de căutare într-un interval dat de celule și INDEX folosește acel număr (sau numere) și returnează rezultatul din celula corespunzătoare.
Încă nu este complet clar? Prezentați funcțiile INDEXȘi CĂUTAREîn această formă:
INDEX(,(POTIRE ( valoarea de căutare,coloana în care căutăm,0))
=INDEX( coloana din care extragem;(MECI( valoarea de căutare;coloana în care căutăm;0))
Cred că va fi și mai ușor de înțeles cu un exemplu. Să presupunem că aveți următoarea listă de capitale de stat:
Să găsim populația uneia dintre capitale, de exemplu, Japonia, folosind următoarea formulă:
INDEX($D$2:$D$10,MATCH(„Japonia”,$B$2:$B$10,0))
=INDEX($D$2:$D$10,MATCH(„Japonia”,$B$2:$B$10,0))
Acum să ne uităm la ce face fiecare element al acestei formule:
- Funcţie MECI(MATCH) caută valoarea „Japan” în coloană Bși în mod specific – în celule B2:B10, și returnează un număr 3 , deoarece „Japonia” este pe locul trei pe listă.
- Funcţie INDEX(INDEX) utilizări 3
pentru argumentare row_num(row_number), care specifică din ce rând trebuie returnată valoarea. Acestea. obținem o formulă simplă:
INDEX($D$2:$D$10,3)
=INDEX($D$2:$D$10,3)Formula spune cam așa: uitați-vă în celule de la D2 inainte de D10și extrageți valoarea din al treilea rând, adică din celulă D4, deoarece numărarea începe de la a doua linie.
Acesta este rezultatul pe care îl obțineți în Excel:
Important! Numărul de rânduri și coloane din matrice pe care funcția le folosește INDEX(INDEX), trebuie să se potrivească cu valorile argumentului row_num(număr_linie) și coloană_num(număr_coloană) funcții MECI(MECI). În caz contrar, rezultatul formulei va fi eronat.
Stai, stai... de ce nu putem folosi o funcție CĂUTARE V(VPR)? Are rost să pierzi timpul încercând să descoperi labirinturi? CĂUTAREȘi INDEX?
CĂUTARE V ("Japonia", $B$2:$D$2,3)
=CĂUTARE(„Japonia”, $B$2:$D$2,3)
În acest caz, nu are rost! Scopul acestui exemplu este doar pentru scopuri demonstrative, astfel încât să puteți înțelege cum funcționează CĂUTAREȘi INDEX lucra in perechi. Următoarele exemple vă vor arăta adevărata putere a pachetului. INDEXȘi CĂUTARE, care face față cu ușurință multor situații dificile când CĂUTARE V se găsește într-o fundătură.
De ce este INDEX/MATCH mai bun decât VLOOKUP?
Atunci când decid ce formulă să folosească pentru o căutare verticală, cei mai mulți guru din Excel cred asta INDEX/CĂUTARE mult mai bine decât CĂUTARE V. Cu toate acestea, mulți utilizatori Excel recurg în continuare la utilizare CĂUTARE V, deoarece această funcție este mult mai simplă. Acest lucru se întâmplă deoarece foarte puțini oameni înțeleg pe deplin toate beneficiile trecerii de la CĂUTARE V pe ciorchine INDEXȘi CĂUTARE, și nimeni nu vrea să piardă timpul studiind o formulă mai complexă.
4 Beneficii principale ale utilizării MATCH/INDEX în Excel:
1. Căutați de la dreapta la stânga. După cum știe orice utilizator Excel competent, CĂUTARE V nu poate privi spre stânga, ceea ce înseamnă că valoarea căutată trebuie să fie neapărat în coloana din stânga a intervalului examinat. In caz de CĂUTARE/INDEX, coloana de căutare poate fi fie în partea stângă, fie în partea dreaptă a intervalului de căutare. Exemplu: va afișa această caracteristică în acțiune.
2. Adăugați sau eliminați coloane în siguranță. Formule cu funcție CĂUTARE V nu mai funcționează sau returnați valori eronate dacă eliminați sau adăugați o coloană la un tabel de căutare. Pentru funcție CĂUTARE V orice coloană inserată sau eliminată va schimba rezultatul formulei deoarece sintaxa CĂUTARE V vă solicită să specificați întregul interval și numărul de coloană specific din care doriți să extrageți datele.
De exemplu, dacă ai o masă A1:C10, și doriți să preluați date dintr-o coloană B, atunci trebuie să setați valoarea 2 pentru argumentare col_index_num(număr_coloană) funcții CĂUTARE V, ca aceasta:
CĂUTARE V ("valoare de căutare", A1:C10,2)
=CĂUTAREV(„valoare de căutare”;A1:C10;2)
Dacă mai târziu introduceți o nouă coloană între coloane AȘi B, atunci valoarea argumentului va trebui modificată de la 2 pe 3 , altfel formula va returna rezultatul din coloana nou introdusă.
Folosind CĂUTARE/INDEX Puteți elimina sau adăuga coloane la intervalul examinat fără a distorsiona rezultatul, deoarece coloana care conține valoarea dorită este definită direct. Într-adevăr, acesta este un mare avantaj, mai ales atunci când trebuie să lucrați cu cantități mari de date. Puteți adăuga și elimina coloane fără să vă faceți griji că trebuie să remediați fiecare funcție pe care o utilizați CĂUTARE V.
3. Nu există limită pentru dimensiunea valorii căutate. Folosind CĂUTARE V, rețineți că lungimea valorii căutate este limitată la 255 de caractere, altfel riscați să obțineți o eroare #VALOARE!(#VALOARE!). Deci, dacă tabelul conține rânduri lungi, singura soluție viabilă este utilizarea INDEX/CĂUTARE.
Să presupunem că folosești această formulă cu CĂUTARE V, care caută în celule din B5 inainte de D10 valoarea specificată în celulă A2:
CĂUTARE V(A2;B5:D10;3;FALSE)
=CĂUTARE V(A2;B5:D10;3;FALSE)
Formula nu va funcționa dacă valoarea din celulă este A2 mai mult de 255 de caractere. În schimb, trebuie să utilizați o formulă similară INDEX/CĂUTARE:
INDEX(D5:D10,POTRIVIRE(ADEVĂRAT,INDEX(B5:B10=A2,0),0))
=INDEX(D5:D10,PORITARE(ADEVĂRAT,INDEX(B5:B10=A2,0),0))
4. Viteză de operare mai mare. Dacă lucrați cu tabele mici, atunci diferența de performanță Excel cel mai probabil nu va fi vizibilă, mai ales în cele mai recente versiuni. Dacă lucrați cu tabele mari care conțin mii de rânduri și sute de formule de căutare, Excel va funcționa mult mai rapid dacă utilizați CĂUTAREȘi INDEXîn loc de CĂUTARE V. În general, această înlocuire crește viteza Excel cu 13% .
Influență CĂUTARE V Performanța Excel este vizibilă mai ales dacă registrul de lucru conține sute de formule matrice complexe, cum ar fi CĂUTARE V+SUMA. Faptul este că verificarea fiecărei valori din matrice necesită un apel separat de funcție CĂUTARE V. Prin urmare, cu cât o matrice conține mai multe valori și cu cât mai multe formule matrice conține tabelul, cu atât Excel funcționează mai lent.
Pe de altă parte, o formulă cu funcții CĂUTAREȘi INDEX pur și simplu efectuează o căutare și returnează rezultatul, efectuând o muncă similară considerabil mai rapid.
INDEX și MATCH - exemple de formule
Acum că înțelegi motivele pentru care ar trebui să înveți funcțiile CĂUTAREȘi INDEX, să trecem la partea distractivă și să vedem cum poți aplica cunoștințele teoretice în practică.
Cum să căutați din partea stângă folosind MATCH și INDEX
Orice manual pe CĂUTARE V spune că această funcție nu poate privi spre stânga. Acestea. dacă coloana la care te uiți nu este cea din stânga din intervalul de căutare, atunci nu există nicio șansă de a ajunge de la CĂUTARE V rezultatul dorit.
Funcții CĂUTAREȘi INDEX Excel este mult mai flexibil și nu-i pasă unde se află coloana cu valoarea pe care doriți să o preluați. De exemplu, să revenim la tabelul cu capitalele de stat și populația. De data aceasta vom scrie formula CĂUTARE/INDEX, care va arăta ce loc ocupă capitala Rusiei (Moscova) ca populație.
După cum puteți vedea în figura de mai jos, formula face această treabă perfect:
INDEX($A$2:$A$10,MATCH(„Rusia”,$B$2:$B$10,0))
Acum nu ar trebui să aveți nicio problemă să înțelegeți cum funcționează această formulă:
- Mai întâi, să folosim funcția MECI(MECI), care găsește poziția „Rusia” în listă:
MATCH(„Rusia”, $B$2:$B$10,0))
=PORITARE(„Rusia”, $B$2:$B$10,0)) - Apoi, setați intervalul pentru funcție INDEX(INDEX) din care se extrage valoarea. În cazul nostru este A2:A10.
- Apoi combinăm ambele părți și obținem formula:
INDEX($A$2:$A$10;POITIRE(„Rusia”;$B$2:$B$10,0))
=INDEX($A$2:$A$10,MATCH(„Rusia”,$B$2:$B$10,0))
Cheie: Soluția corectă este să folosiți întotdeauna referințe absolute pentru INDEXȘi CĂUTARE, astfel încât intervalele de căutare să nu se piardă atunci când copiați formula în alte celule.
Calcule folosind INDEX și MATCH în Excel (AVERAGE, MAX, MIN)
Puteți imbrica alte funcții Excel în interior INDEXȘi CĂUTARE, de exemplu, pentru a găsi valoarea minimă, maximă sau cea mai apropiată de valoarea medie. Iată câteva opțiuni pentru formule în raport cu tabelul de la:
1. MAX(MAX). Formula găsește maximul într-o coloană D C aceeasi linie:
INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))
=INDEX($C$2:$C$10,POITIRE(MAX($D$2:I$10),$D$2:D$10,0))
Rezultat: Beijing
2. MIN(MIN). Formula găsește minimul într-o coloană Dși returnează valoarea din coloană C aceeasi linie:
INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))
=INDEX($C$2:$C$10,POITIRE(MIN($D$2:I$10),$D$2:D$10,0))
Rezultat: Lima
3. IN MEDIE(IN MEDIE). Formula calculează media unui interval D2:D10, apoi îl găsește pe cel mai apropiat de acesta și returnează valoarea din coloană C aceeasi linie:
INDEX($C$2:$C$10,PORITARE(MEDIE($D$2:D$10),$D$2:D$10,1))
=INDEX($C$2:$C$10,PORITARE(MEDIE($D$2:D$10),$D$2:D$10,1))
Rezultat: Moscova
Lucruri de reținut când utilizați funcția MEDIE cu INDEX și MATCH
Folosind funcția IN MEDIE in combinatie cu INDEXȘi CĂUTARE, ca al treilea argument al funcției CĂUTARE cel mai adesea va trebui să indicați 1 sau -1 în cazul în care nu sunteți sigur că intervalul pe care îl vizualizați conține o valoare egală cu media. Dacă sunteți sigur că există o astfel de valoare, puneți 0 pentru a găsi o potrivire exactă.
- Daca indicati 1 , valorile din coloana de căutare ar trebui să fie ordonate în ordine crescătoare, iar formula va returna valoarea maximă mai mică sau egală cu media.
- Daca indicati -1 , valorile din coloana de căutare ar trebui ordonate în ordine descrescătoare și va fi returnată valoarea minimă mai mare sau egală cu media.
În exemplul nostru, valorile din coloană D sunt ordonate în ordine crescătoare, deci folosim tipul de colare 1 . Formulă INDEX/SEARCHPOZ returnează „Moscova”, deoarece populația orașului Moscova este cea mai apropiată de valoarea medie (12.269.006).
Cum să utilizați INDEX și MATCH pentru a căuta un rând și o coloană cunoscute
Această formulă este echivalentă cu o căutare bidimensională CĂUTARE Vși vă permite să găsiți valoarea la intersecția unui anumit rând și coloană.
În acest exemplu formula INDEX/CĂUTARE va fi foarte asemănătoare cu formulele pe care le-am discutat deja în această lecție, cu o singură diferență. Ghici care?
După cum vă amintiți, sintaxa funcției INDEX(INDEX) permite trei argumente:
INDEX(matrice,row_num,)
INDEX(matrice, număr_rând, [număr_coloană])
Și îi felicit pe cei care ați ghicit!
Să începem prin a scrie șablonul de formulă. Pentru a face acest lucru, să luăm formula care ne este deja familiară INDEX/CĂUTAREși adăugați o altă funcție CĂUTARE, care va returna numărul coloanei.
INDEX(Tabelul dvs. ,(POTIRE(, coloană pentru a căuta,0)),(POTIRE(, linie pentru a căuta,0))
=INDEX(Tabelul dvs. ,(POTIT( valoare pentru căutare verticală,coloană pentru a căuta,0)),(POTIRE( valoare pentru căutarea orizontală,linie pentru a căuta,0))
Rețineți că pentru căutarea bidimensională trebuie să specificați întregul tabel în argument matrice(matrice) funcții INDEX(INDEX).
Acum să încercăm acest model în practică. Mai jos vedeți o listă cu cele mai populate țări din lume. Să presupunem că sarcina noastră este să aflăm populația Statelor Unite în 2015.
Bine, hai să scriem formula. Când trebuie să creez o formulă complexă în Excel cu funcții imbricate, mai întâi notez fiecare funcție imbricată separat.
Deci, să începem cu două funcții CĂUTARE, care va returna numerele rândurilor și coloanelor pentru funcție INDEX:
- MATCH pentru coloană– căutăm în coloană B, sau mai degrabă în gamă B2:B11, valoarea care este specificată în celulă H2(STATELE UNITE ALE AMERICII). Funcția va arăta astfel:
MECI ($H$2,$B$1:$B$11,0)
=PORITARE($H$2,$B$1:$B$11,0)4 , deoarece „SUA” este al 4-lea element din listă din coloană B(inclusiv titlul).
- MATCH pentru șir– căutăm valoarea celulei H3(2015) în linie 1
, adică în celule A1:E1:
MATCH($H$3,$A$1:$E$1,0)
=PORITARE($H$3,$A$1:$E$1,0)Rezultatul acestei formule va fi 5 , deoarece „2015” se află în a 5-a coloană.
Acum inserăm aceste formule în funcție INDEX si voila:
INDEX($A$1:$E$11,POTRIBUIRE($H$2,$B$1:$B$11,0),POTIRE($H$3,$A$1:$E$1,0))
=INDEX($A$1:$E$11,POTRIBUIRE($H$2,$B$1:$B$11,0),POTIRE($H$3,$A$1:$E$1,0))
Dacă înlocuiți funcțiile CĂUTARE pe baza valorilor pe care le returnează, formula va deveni ușoară și de înțeles:
INDEX($A$1:$E$11,4,5))
=INDEX($A$1:$E$11,4,5))
Această formulă returnează valoarea la intersecție al 4-lea linii şi al 5-lea coloană în interval A1:E11, adică valoarea celulei E4. Doar? Da!
Căutare cu mai multe criterii cu INDEX și MATCH
În tutorialul de pe CĂUTARE V am arătat un exemplu de formulă cu o funcție CĂUTARE V pentru a căuta folosind mai multe criterii. Cu toate acestea, o limitare semnificativă a acestei soluții a fost necesitatea de a adăuga o coloană auxiliară. Vești bune: formula INDEX/CĂUTARE poate căuta în valori în două coloane, fără a fi nevoie să creați o coloană de ajutor!
Să presupunem că avem o listă de comenzi și dorim să găsim suma pe baza a două criterii − numele cumpărătorului(Client) și produs(Produs). Problema este complicată de faptul că un cumpărător poate cumpăra mai multe produse diferite simultan și numele cumpărătorilor din tabelul de pe foaie Tabel de căutare aranjate în ordine aleatorie.
Iată formula INDEX/CĂUTARE rezolva problema:
(=INDEX ("Tabel de căutare"!$A$2:$C$13,MATCH(1,(A2="Tabel de căutare"!$A$2:$A$13))*
(B2="Tabel de căutare"!$B$2:$B$13),0),3))
(=INDEX(„Tabel de căutare”!$A$2:$C$13;POTIRE(1,(A2="Tabel de căutare"!$A$2:$A$13))*
(B2="Tabel de căutare"!$B$2:$B$13);0);3))
Această formulă este mai complexă decât altele despre care am discutat mai devreme, dar înarmată cu cunoștințe despre funcții INDEXȘi CĂUTARE O vei învinge. Cea mai grea parte este funcția CĂUTARE, cred că trebuie explicat mai întâi.
MATCH(1,(A2="Tabel de căutare"!$A$2:$A$13),0)*(B2="Tabel de căutare"!$B$2:$B$13)
MATCH(1;(A2="Tabel de căutare"!$A$2:$A$13);0)*(B2="Tabel de căutare"!$B$2:$B$13)
În formula prezentată mai sus, valoarea pe care o căutăm este 1 , iar tabloul de căutare este rezultatul înmulțirii. Bine, ce ar trebui să înmulțim și de ce? Să privim totul în ordine:
- Luați prima valoare din coloană A(Client) pe foaie Masa principalăși comparați-l cu toate numele clienților din tabelul de pe foaie Tabel de căutare(A2:A13).
- Dacă se găsește o potrivire, ecuația revine 1 (ADEVĂRAT), iar dacă nu - 0 (MINCIUNĂ).
- Apoi, facem același lucru pentru valorile coloanei B(Produs).
- Apoi înmulțim rezultatele obținute (1 și 0). Numai dacă se găsesc potriviri în ambele coloane (adică ambele criterii sunt adevărate), veți primi 1 . Dacă ambele criterii sunt false, sau numai unul dintre ele este îndeplinit, vei primi 0 .
Acum înțelegeți de ce am întrebat 1 , care este valoarea dorită? Este corect ca functia CĂUTARE a returnat postul numai atunci când ambele criterii au fost îndeplinite.
Notă:În acest caz, trebuie să utilizați al treilea argument opțional al funcției INDEX. Este necesar pentru că în primul argument specificăm întregul tabel și trebuie să spunem funcției din ce coloană să preia valoarea. În cazul nostru, aceasta este coloana C(Suma), și așa am intrat 3 .
Și în sfârșit, pentru că trebuie să verificăm fiecare celulă din matrice, această formulă trebuie să fie o formulă matrice. Puteți vedea acest lucru după bretele în care este închis. Deci, când ați terminat de introdus formula, nu uitați să faceți clic Ctrl+Shift+Enter.
Dacă totul este făcut corect, veți obține rezultatul ca în figura de mai jos:
INDEX și MATCH combinate cu IFERROR în Excel
După cum probabil ați observat deja (de mai multe ori), dacă introduceți o valoare incorectă, de exemplu, una care nu se află în matricea care este vizualizată, formula INDEX/CĂUTARE raportează o eroare #N / A(#N/A) sau #VALOARE!(#VALOARE!). Dacă doriți să înlocuiți un astfel de mesaj cu ceva mai ușor de înțeles, puteți introduce o formulă cu INDEXȘi CĂUTAREîntr-o funcție DACA EROARE.
Sintaxa funcției DACA EROARE foarte simplu:
IFEROARE(valoare,valoare_dacă_eroare)
IFEROARE(valoare,valoare_dacă_eroare)
Unde este argumentul valoare(valoare) este valoarea care este verificată pentru o eroare (în cazul nostru, rezultatul formulei INDEX/CĂUTARE); si argumentul valoare_dacă_eroare(value_if_error) este valoarea care trebuie returnată dacă formula aruncă o eroare.
De exemplu, puteți introduce într-o funcție DACA EROARE ca aceasta:
DACĂ EROARE(INDEX($A$1:$E$11,POTRIVIRE($G$2,$B$1:$B$11,0),POTIRE($G$3,$A$1:$E$1,0))),
„Nu s-au găsit potriviri. Încercați din nou!”) =IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1 ): $E$1;0));
„Nu s-au găsit potriviri. Încercați din nou!”)
Și acum, dacă cineva introduce o valoare incorectă, formula va produce acest rezultat:
Dacă preferați să lăsați celula goală în cazul unei erori, puteți utiliza ghilimele (“”) ca valoare a celui de-al doilea argument al funcției DACA EROARE. Ca aceasta:
IFERROR(INDEX(matrice, MATCH(valoare_căutare,matrice_căutare,0),"")
IFERROR(INDEX(matrice, MATCH(valoare_căutare,matrice_caută,0),"")
Sper că ați găsit utilă cel puțin o formulă descrisă în acest manual. Dacă ați întâmpinat alte probleme de căutare pentru care nu ați putut găsi o soluție potrivită printre informațiile din această lecție, nu ezitați să vă descrieți problema în comentarii și vom încerca cu toții să o rezolvăm împreună.
Buna seara prieteni!
Subiectul acestui articol va fi o altă oportunitate de a lucra cu text, aceasta este cunoașterea modului în care funcționează. Cred că nu este nevoie să explicăm care este utilitatea acestei funcții, este deja clar pur și simplu din nume și înseamnă că această funcție va căuta o potrivire exactă a textului specificat în șirul de text specificat și va returna poziţia de pornire a şirului găsit.
Dar pot spune imediat că în forma sa pură funcția FIND arată foarte inestetic și, în principiu, practic nu este folosită. Cea mai comună caracteristică este utilizată numai în combinație, împreună cu funcții precum STANGA DREAPTAȘi . Astfel de combinații vă vor permite să utilizați eficient toate capacitățile funcției FIND și să efectuați sarcini mai complexe în comparație cu utilizarea simplă.
Ei bine, acum să ne uităm la sintaxa care are:
=FIND(textul pe care îl căutăm; textul pe care îl vedem; [poziția de pornire]), Unde
Vă rugăm să rețineți că o caracteristică care are este hipersensibilitatea la majusculele literelor, adică trebuie să indicați corect literele mari și mici.
De exemplu, luați în considerare 2 exemple pentru comparație:
=FIND("APA","APA apa"), va returna valoarea 1, dar aici este formula
=FIND("apa","APA apa"), va reveni la poziția 6.
După cum se poate vedea din exemplu, aceasta este o diferență foarte semnificativă de ignorat.
Dacă textul pe care doriți să îl găsiți nu este găsit, formula va returna . 
Deci, de fapt, să ne uităm la variabilitate folosind exemple, deoarece este folosită în exemple simple și complexe:
Exemple simple:
Să luăm în considerare utilizarea unei singure funcții FIND, în mai multe variante și modele. Să luăm, de exemplu, valoarea textului „Artyom Nagaev” și să aplicăm funcția:
=FIND("A",A2), în această formulă căutăm poziția primei litere „A” în celula A2 și obținem rezultatul „8”;
=FIND(„a”,A2), în această formulă căutăm poziția primei litere „a” în celula A2 și obținem rezultatul „2”;
=FIND("a",A2,3), în această formulă căutăm poziția primei litere „a” în celula A2, dar începem căutarea de la al treilea caracter și obținem rezultatul „4”. 
Exemple combinate:
Primul exemplu va folosi combinația folosind funcția PSTR. De exemplu, avem un nume de produs cu numărul articolului „LED Nr. 456217-485” și trebuie să separăm grâul de pleavă, adică numele părții de număr și pentru aceasta avem nevoie de formula:
=PSTR(A4,1,FIND(“ №”,A4,1) -1), în formulă găsim poziția „Nu.” și totul înainte de a fi scos prin formula corespunzătoare și obținem rezultatul „LED”. Argumentul „-1” va ține cont de faptul că și poziția ocupată de textul pe care îl căutăm este inclusă în intervalul de numărare și nu poate fi returnată. 
Al doilea exemplu Folosesc combinații cu funcții STANGA DREAPTA. De exemplu, în celula A5 am introdus numele și numele „Artyom Nagaev”, în acest caz folosind formulele:
=LEFTCHAR(A5;FIND(CHAR(32);A5) -1), putem extrage numele de familie,
=DREAPTA(A5;LENGTH(A5) -FIND(CHAR(32);A5)), hai să extragem numele. 
În cazul în care există spații suplimentare, două sau mai multe, între cele două valori ale prenumelui și prenumelui, atunci pentru ca formulele de mai sus să funcționeze este necesar să se folosească Funcția SPACE.
În acest moment, cred că ați putut aprecia încă o funcție text. Am învățat, sau cel puțin ne-am dat seama cum și unde este folosit. Am studiat utilitatea acestuia și opțiunile de aplicare. Mai multe funcții sunt prezentate pentru revizuire în.
Dacă aveți întrebări, scrieți comentarii, like dacă v-a plăcut.
Ne vedem în articole noi!
„Întotdeauna avem bani la un capriciu; ne zgârim doar cu cheltuielile utile și necesare.
"
O. Balzac
Să presupunem că trebuie să găsiți extensia de telefon a unui angajat folosind numărul său și, de asemenea, să estimați corect raportul comisionului pentru suma vânzărilor. Căutați date pentru a găsi rapid și eficient date specifice într-o listă și verificați automat dacă datele sunt utilizate corect. După ce vizualizați datele, puteți efectua calcule și afișa rezultatele, indicând valorile returnate. Există mai multe moduri de a căuta valori într-o listă de date și de a afișa rezultatele.
În acest articol
Găsiți valori într-o listă pe verticală după potrivirea exactă
Puteți utiliza funcția CĂUTARE V sau o combinație a funcțiilor INDEX și MATCH pentru a efectua această sarcină.
Exemple de utilizare a funcției CĂUTARE V
Funcția VLOOKUP.
Exemple de INDEX-uri și MATCHES
Ce înseamnă:
=INDEX(trebuie să returnați valoarea din C2:C10, care se va potrivi cu MATCH(prima valoare „Varză” din tabloul B2:B10))
Formula caută prima valoare în celula C2:C10 corespunzătoare varză(în B7) și returnează valoarea în C7 ( 100 ) - prima valoare corespunzătoare varză.
Pentru mai multe informații, consultați funcția INDEX și funcția MATCH.
Găsiți valori într-o listă pe verticală, după potrivire aproximativă
Pentru a face acest lucru, utilizați funcția VLOOKUP.
Important:
În exemplul de mai sus, funcția VLOOKUP caută numele unui elev care are 6 întârzieri în intervalul A2:B7. Nu există nicio intrare în tabel pentru 6 întârzieri, astfel încât funcția CĂUTARE VL caută următoarea potrivire cea mai mare sub 6 și găsește valoarea 5 asociată prenumelui Dave, și, prin urmare, se întoarce Dave.
Pentru mai multe informații, consultați funcția VLOOKUP.
Găsirea valorilor verticale într-o listă de dimensiuni necunoscute cu potrivire exactă
Pentru a efectua această sarcină, utilizați funcțiile OFFSET și MATCH.
Notă: Această abordare este utilizată dacă datele se află într-un interval de date extern pe care îl actualizați în fiecare zi. Știți că coloana B are Preț, dar nu știți câte rânduri de date returnează serverul, iar prima coloană nu este sortată alfabetic.
C1 este celula din stânga sus a intervalului (numită și celula de pornire).
Potrivire(„portocale”; C2: C7; 0) caută culoarea portocalie în intervalul C2:C7. Nu ar trebui să includeți celula de pornire în interval.
1 - numărul de coloane din dreapta celulei de pornire pentru care ar trebui returnată valoarea returnată. În exemplul nostru, valoarea returnată este în coloana D, Vânzări.
Găsiți valori într-o listă orizontal după potrivire exactă
Pentru a efectua această sarcină, este utilizată funcția GLOOKUP. Mai jos este un exemplu.
Funcția LOOKUP caută o coloană Vânzăriși returnează valoarea de la rândul 5 în intervalul specificat.
Pentru mai multe informații, consultați funcțiile LOOKUP.
Găsirea valorilor într-o listă pe orizontală folosind potrivirea aproximativă
Pentru a efectua această sarcină, este utilizată funcția GLOOKUP.
Important: Asigurați-vă că valorile din primul rând sunt sortate în ordine crescătoare.
În exemplul de mai sus, funcția GLOOKUP caută valoarea 11000 în rândul 3 în intervalul specificat. Nu găsește 11000 și, prin urmare, caută următoarea valoare cea mai mare care este mai mică de 1100 și returnează numărul 10543.
Pentru mai multe informații, consultați funcțiile LOOKUP.
Creați o formulă de căutare folosind Expertul de căutare (numai Excel 2007)
Notă: Complementul Lookup Wizard a fost întrerupt în Excel 2010. Această funcționalitate a fost înlocuită de Function Wizard și de funcționalitatea disponibilă de căutare și referință (referință).
În Excel 2007, Expertul de căutare creează o formulă de căutare bazată pe datele din foaia de lucru care au titluri de rând și coloană. Asistentul de căutare vă ajută să găsiți alte valori într-un rând atunci când cunoașteți valoarea dintr-o coloană și invers. Expertul de căutare folosește index și MATCH în formulele pe care le creează.