Cum să cauți un număr în Excel. Găsirea de soluții în Excel - un exemplu de utilizare a serviciului Găsirea de soluții în Excel

Optimizarea valorilor foilor de calcul Excel care îndeplinesc anumite criterii poate fi un proces dificil. Din fericire, Microsoft oferă un add-on Rezolvarea problemelor pentru optimizare numerică. Deşi acest serviciu nu poate rezolva toate problemele, poate fi util ca instrument ce-ar fi dacă. Această postare este despre add-in Rezolvarea problemelorîn Excel.

Suprastructură Rezolvarea problemelor disponibil în toate versiunile de Excel. Rețineți că este posibil ca capturile de ecran să nu se potrivească cu versiunea dvs. În ciuda faptului că unele funcții își pot schimba locația în funcție de versiunea suplimentului, funcționalitatea rămâne practic aceeași.

Ce este Căutarea de soluții

Căutarea deciziilor- Completare Excel care vă ajută să găsiți o soluție prin schimbarea valorilor celulelor țintă. Scopul poate fi de a minimiza, maximiza sau atinge o anumită valoare țintă. Problema este rezolvată prin ajustarea criteriilor de intrare sau a limitelor definite de utilizator.

Unde găsiți soluții în Excel

Suprastructură Căutarea deciziilor vine cu Excel, dar este dezactivat implicit. Pentru a-l activa, accesați fila Fişier la grup Opțiuni.În caseta de dialog care apare Opțiuni, alege Suplimente -> Gestionați: SuplimenteExcel -> Go. La fereastră Suplimente bifați caseta de lângă câmp Găsirea unei soluții, apăsăm BINE.

Acum în filă Date a apărut un grup nou Analiză cu buton Căutați o soluție.

Un exemplu de utilizare a Căutării pentru o soluție

Această postare se bazează pe un caz de utilizare . Fișierul este compatibil cu toate versiunile de Excel.

Definirea problemei

Să presupunem că avem un set de date de 8 articole, fiecare dintre ele având propria sa valoare.

... și trebuie să combinăm valorile în două grupuri, astfel încât sumele valorilor acestor grupuri să coincidă aproximativ.

În primul rând, trebuie să identificați fiecare element într-un grup.

Pentru a indica atașarea unui element la un grup, le vom marca cu unul (1), altfel zero (0).

În coloana următoare, vom rezuma valorile fiecărui element din grup, iar apoi vom rezuma la sfârșitul coloanei.

De asemenea, trebuie să procesăm valoarea fiecărui articol din fiecare grup, pentru aceasta înmulțim valoarea articolului cu valoarea grupului corespunzătoare acestui articol.

În cele din urmă, trebuie să însumăm grupurile și să lucrăm cu diferența dintre ele.

Sarcina noastră este să minimizăm diferența dintre sumele grupurilor.

Acum putem atribui puncte fiecărui grup, pentru aceasta punem manual unități în coloanele C și D. Excel va afișa diferența în sumele grupurilor din celula G11.

Problema este că numărul de combinații posibile este 2 8, adică. 256 de răspunsuri posibile la întrebare. Dacă petrecem 5 secunde pe fiecare dintre ele, ne va dura 21,3 minute, presupunând că putem menține ritmul și ne amintim cea mai bună combinație.

Acolo Găsirea unei soluții găsește aplicație.

Găsirea celei mai bune soluții în Excel

Pentru a aplica serviciul Caută o soluție, trebuie să definim un set de cerințe, reguli și constrângeri care să permită suplimentului să găsească răspunsul corect.

Regulile noastre

Principala noastră cerință este să minimizăm diferența dintre cele două grupuri. În exemplul nostru, este în celula G11 - Grupa B minus Grupa A. Dorim ca valoarea din celula G11 să fie cât mai mică posibil, dar mai mare sau egală cu 0.

De asemenea, știm că un element poate fi fie în Grupa A, fie în Grupa B, în plus, nu poate fi fracțional. Astfel, avem două constrângeri pentru fiecare element:

În primul rând: valoarea articolului din coloană Rezultat ar trebui să fie egal cu unu.

În al doilea rând: valorile elementelor din grupuri trebuie să fie întregi.

De asemenea, știm că numărul total de articole este de 8, ceea ce este o altă limitare. Vom discuta cum să folosim aceste restricții în secțiunea următoare.

Caseta de dialog Găsiți soluție

Această secțiune descrie fereastra de completare Găsirea unei soluțiiși folosind-o pentru a identifica problema.

Fereastra goală Căutare soluție

S-a finalizat Căutarea unei ferestre de soluție

Optimizați funcția obiectiv

Aceasta este celula țintă în care încercăm să rezolvăm problema. Celula noastră țintă G11 este diferența de grup.

Inainte de

Aici indicăm ce rezultate dorim să obținem din funcția obiectiv.

Dorim ca sumele ambelor grupuri să coincidă, adică astfel încât diferența dintre sume să fie egală cu 0. Poate părea ciudat, dar nu trebuie să minimizăm diferența, deoarece acest lucru va pune toate elementele în Grupa A, ceea ce va avea ca rezultat ca valoarea celulei G11 să fie mai mică decât zero.

O altă modalitate de a impune o constrângere este schimbarea G11 în = ABS (G10-F10). Cu aceasta vom putea seta markerul la Minim, ca urmare a realizării funcţiei ţintă.

Dar deocamdată ne vom concentra pe formula = G10-F10și setați markerul la 0.

Schimbarea celulelor variabile

Celulele modificabile sunt celule pe care programul de completare va încerca să le modifice pentru a rezolva o problemă. În cazul nostru, aceasta este legarea unui element la un anumit grup: $ C $ 2: $D $ 9.

Conform restricțiilor

Constrângerile sunt reguli care limitează solutii posibile Probleme.

Trebuie să adăugăm câteva constrângeri la lista noastră:

Într-o coloană Total fiecare element trebuie să fie egal cu 1
Elementele grupului trebuie să fie un număr întreg
Suma valorilor coloanei Total ar trebui să fie 8

Pentru a impune restricții, apăsați butonul Adăuga

Poti Schimbare sau Șterge constrângere, dacă ați făcut o greșeală selectând o constrângere specifică și făcând clic pe butoanele corespunzătoare din caseta de dialog.

Încărcați/salvați parametrii soluției de căutare

Serviciul de căutare a soluțiilor vă permite să salvați și să încărcați setări de completare. Pentru a face acest lucru, există un buton în fereastră Încărcați/salvați. Parametrii modelului sunt salvați în intervalul pe care l-ați specificat mai devreme. Această abordare vă permite să configurați și să modificați rapid parametrii Căutați o soluție.

Rularea căutării celei mai bune soluții în Excel

Un avertisment!!! Suplimentul Solver este un program de completare complex, așa că salvați registrul de lucru înainte de a rula.

Înainte de a rula modelul, mai sunt câțiva parametri care trebuie setați pentru a vă asigura că serviciul va funcționa corect. În caseta de dialog principală, asigurați-vă că există un marker lângă câmp Faceți variabilele nelimitate ne-negative.În aceeași fereastră, faceți clic pe butonul Opțiuni.

Doi parametri care vor trebui modificați din când în când:

Precizie limită: o valoare de la 0 la 1, unde cu cât cifra este mai mare, cu atât este mai mare restricția

Optimitatea întregului: arată cât de departe de un număr întreg este permisă restricția.

Rularea modelului

Pentru a rula programul de completare, faceți clic pe buton Gaseste o solutieîn fereastra principală.

În bara de stare, veți vedea o serie de date statice care vor reflecta funcționarea interioară a programului de completare. Ele tind să se schimbe rapid și sunt greu de citit. Dacă modelul este complex, atunci lucrarea se poate opri pentru o vreme, suprastructura de obicei se recuperează singură din aceste probleme.

După Găsirea unei soluțiiîși termină munca, Excel va afișa o casetă de dialog Rezultatele căutării soluției cu unele informatii. Primul lucru la care ar trebui să acordați atenție este inscripția Soluția a fost găsită în deviația acceptabilă. Dacă se găsește o soluție, celulele din registrul de lucru se vor schimba odată cu soluția sugerată.

Acum aveți 4 opțiuni din care să alegeți:

- Restabiliți valorile originale

Rulați raportul

Puteți crea un raport selectând rapoartele disponibile din listă. Va fi creată o nouă foaie Raport asupra rezultatelor 1.

Vă rugăm să rețineți că diferite rapoarte vor fi disponibile în funcție de limitele pe care le setați.

Salvați scriptul

Unde trebuie să introduceți numele scriptului modelului dvs. și să faceți clic pe butonul BINE.

Toate scripturile sunt disponibile în Manager de scripturi care se află în filă Dateîntr-un grup Lucrul cu date -> Analiză ce se întâmplă -> Manager script.

Să presupunem că raportul dvs. conține un tabel cu multe date și multe coloane. Este extrem de dificil să se efectueze o analiză vizuală a unor astfel de tabele. Și una dintre sarcinile de lucru cu raportul este - analiza datelor privind titlurile rândurilor și coloanelor referitoare la o anumită lună. La prima vedere, aceasta este o sarcină foarte simplă, dar nu poate fi rezolvată folosind o singură funcție standard. Da, desigur, puteți utiliza instrumentul: "HOME" - "Edit" - "Găsiți" CTRL + F pentru a deschide fereastra de căutare a valorilor pe Foaia de lucru Excel... Sau creați o regulă de formatare condiționată pentru tabel. Dar apoi nu vor fi posibile alte calcule cu rezultatele obținute. Prin urmare, este necesar să se creeze și să se aplice corect formula adecvată.

Găsirea unei valori într-o matrice Excel

Schema de rezolvare a problemei arată cam așa:

în celula B1 vom introduce datele care ne interesează;
celula B2 va afișa antetul coloanei care conține valoarea celulei B1
celula B3 va afișa numele rândului care conține valoarea celulei B1.

De fapt, trebuie să căutați coordonatele în Excel. Pentru ce este? Destul de des, trebuie să obținem coordonatele unui tabel după valoare. Un pic ca analiza inversă a matricei. Un exemplu concret pe scurt arată cam așa. Scopul în cifre este valoarea inițială, trebuie să determinați cine și când este cel mai aproape de acest obiectiv. Ca exemplu, vom folosi o matrice de date simplă care raportează numărul de articole vândute timp de trei sferturi, așa cum se arată în figura de mai jos. Este important ca toate numerele să fie consistente. Dacă nu există dorința de a crea și completa manual Foaie de calcul Excel de la zero, apoi la sfârșitul articolului îl puteți descărca cu un exemplu gata făcut.

Vom lua în considerare secvențial soluții de complexitate diferită, iar la sfârșitul articolului - rezultatul final.

Găsirea unei valori într-o coloană Excel

Mai întâi, să învățăm cum să obținem titlurile coloanelor din tabel după valoare. Pentru a face acest lucru, urmați acești pași:

În celula B1, introduceți valoarea luată din tabelul 5277 și evidențiați fundalul acesteia în albastru pentru lizibilitatea câmpului de introducere (în continuare vom introduce alte numere în celula B1 pentru a experimenta cu noi valori).
În celula C2, introduceți formula pentru a obține antetul coloanei tabelului care conține această valoare:

După introducerea formulei pentru confirmare, apăsați combinația de taste rapide CTRL + SHIFT + Enter, deoarece formula trebuie executată într-o matrice. Dacă totul este făcut corect, acolade () vor apărea la marginile barei de formule.

Găsirea unei valori într-un șir Excel

Acum obținem numărul liniei pentru aceeași valoare (5277). Pentru a face acest lucru, introduceți următoarea formulă în celula C3:

După ce ați introdus formula pentru confirmare, apăsați din nou combinația de taste CTRL + SHIFT + Enter și obțineți rezultatul:

Formula a returnat numărul 9 - a găsit titlul rândului foii după valoarea corespunzătoare din tabel. Ca urmare, avem adresa completa valorile D9.

Cum să obțineți antetul coloanei și numele rândului unui tabel

Acum să învățăm cum să obținem coordonatele nu ale întregii foi, ci ale tabelului curent după valoare. Într-un cuvânt, trebuie să găsim antetele după valoarea 5277 în loc de D9:

pentru o coloană de tabel - martie;
pentru rând - Item4.

Pentru a rezolva această problemă, vom folosi o formulă cu valorile deja obținute în celulele C2 și C3. Pentru a face acest lucru, facem acest lucru:

Ca urmare, coordonatele interne ale tabelului sunt obținute după valoare - martie; Punctul 4:

Găsirea valorilor duplicate într-un interval Excel

Pentru a controla prezența duplicatelor printre valorile tabelului, vom crea o formulă care ne poate informa despre prezența duplicatelor și să numărăm numărul acestora. Pentru a face acest lucru, introduceți formula în celula E2:

Mai mult, pentru intervalul secțiunii tabelare, vom crea o regulă de formatare condiționată:

După cum puteți vedea, în prezența duplicatelor, formula pentru titluri preia titlul de la primul duplicat orizontal (de la stânga la dreapta). Și formula pentru obținerea numelui (numărul) liniei ia numărul de la primul duplicat vertical (de sus în jos). Pentru corectare această decizie sunt 2 moduri:

În acest caz, schimbăm formulele fie una, fie alta, dar nu două deodată. Merită să ne amintim că vechea formulă ar trebui să rămână în celula C3:

Aici sunt afișate coordonatele primului duplicat vertical (de sus în jos) - I7 pentru foaie și august; Produs2 pentru masă. Să lăsăm această opțiune pentru următorul exemplu final.

Găsiți cea mai apropiată valoare dintr-un interval Excel

Această masă încă nu este perfectă. La urma urmei, atunci când analizați, trebuie să știți exact toate valorile sale. Dacă formula nu găsește numărul introdus în celula B1 din tabel, atunci este returnată o eroare - #VALOARE! Ar fi ideal dacă formula, în absența numărului inițial din tabel, ar selecta ea însăși cea mai apropiată valoare pe care o conține tabelul. Pentru a crea un astfel de program pentru analiza tabelelor din celula F1, introduceți o nouă formulă:

Apoi urmează în toate celelalte formule schimbați legătura în loc de B1 ar trebui să fie F1! De asemenea, trebuie să modificați linkul în formatarea condițională... Selectați: HOME - Stiluri - Formatare condiționată - Gestionați reguli - Editați regula. Și aici în parametri specificați F1 în loc de B1. Pentru a verifica funcționarea programului, introduceți în celula B1 un număr care nu se află în tabel, de exemplu: 8000. Aceasta va duce la rezultatul final:

Acum puteți introduce orice valoare inițială, iar programul în sine va selecta cel mai apropiat număr pe care îl conține tabelul. Apoi afișează antetul coloanei și numele rândului pentru valoarea curentă. De exemplu, dacă introduceți numărul 5000, obținem un nou rezultat:

Programul nostru în Excel a găsit cea mai apropiată valoare de 4965 pentru originalul - 5000. Un astfel de program poate fi util pentru solutie automata diverse sarcini analitice în planificarea afacerii, stabilirea obiectivelor, găsirea unei soluții raționale etc. Iar rândurile și coloanele rezultate vă permit să extindeți și mai mult capacitățile de calcul ale acestui tip de rapoarte folosind noi formule Excel.

Puteți lucra cu succes în Excel Online pentru Windows folosind o tastatură externă. Pentru utilizatorii cu mobilitate redusă sau cu vedere redusă, comenzile rapide de la tastatură pot fi mai convenabile decât lucrul cu acestea Touch screen sau cu mouse-ul. Acest articol descrie comenzile rapide de la tastatură disponibile atunci când lucrați cu aplicație Excel Online pentru Windows.

Note:

Comenzile rapide de la tastatură din acest articol se bazează pe aspectul tastaturii din SUA. Comenzile rapide de la tastatură pentru alte forme de tastatură pot diferi de cele prezentate aici.

În comenzile rapide în care sunt apăsate mai multe taste în același timp, numele lor sunt separate prin semnul plus (+). Dacă tastele sunt apăsate secvențial, acestea sunt separate printr-o virgulă (,).

Comenzi rapide de la tastatură utilizate frecvent

Cele mai frecvente comenzi rapide de la tastatură pentru Excel Online sunt listate aici.

Acțiune
Accesați o anumită celulă
Deplasați-vă în jos	PAGINA JOS sau SAGEATA JOS
În sus	PAGINA SUS sau SAGEATA SUS

Copierea

Decuparea
Anularea unei operațiuni
Deschiderea unei cărți
Închiderea cărții


Font aldine
Deschidere meniul contextual	Tastatura Windows: scurtătură meniul Windows+ F10. Tasta meniului contextual Windows este situată în stânga între tastele ALT și CTRL. Alte tastaturi: SHIFT + F10
Asistent
	CTRL + F sau SHIFT + F3
Repetarea căutării (în jos)
Repetarea căutării (în sus)
Introduceți o diagramă

Taste de acces: comenzi rapide de la tastatură pentru lucrul cu Panglică

Tastele de acces sunt acum disponibile în Excel Online - comenzi rapide de la tastatură pentru navigarea în panglică. Acestea sunt utilizate în același mod ca și în versiunea clasică de Excel și economisesc timp atunci când lucrați.

În Excel 2016 pentru Windows, aceste comenzi rapide de la tastatură au început cu tastele ALT... În Excel Online, toate aceste comenzi rapide încep cu tastele ALT + WIN, urmate de tasta care corespunde filei. De exemplu, fila Revizuire este deschisă apăsând ALT + WIN + P.

Dacă utilizați Excel Online pe computer Mac, apăsați CTRL + OPT pentru a începe.

Pentru a merge la panglică, apăsați Alt + WIN sau apăsați CTRL + F6 până când vă aflați în fila Acasă.

Apăsați tasta Tab pentru a naviga între filele din panglică.

Pentru a ascunde panglica și pentru a obține mai mult spațiu de lucru, apăsați CTRL + F1. Pentru a afișa din nou panglica, repetați acest pas.

Utilizarea cheilor de acces pentru panglică

Pentru a naviga la o filă de pe panglică, apăsați una dintre următoarele taste de acces.

Acțiune
Accesați câmpul „Ce doriți să faceți?” pe panglică pentru a introduce un cuvânt de căutare.
Accesați fila Fișier pentru a lucra cu vizualizarea Backstage.
Accesați fila Acasă și formatați text și numere sau utilizați alte instrumente (cum ar fi căutarea).
Accesați fila Inserare și introduceți o funcție, tabel, diagramă, hyperlink sau casetă de text.
Accesați fila Date și actualizați linkurile sau utilizați instrumentele de date.
Accesați fila Revizuire și verificați accesibilitatea sau lucrați cu adnotări.
Faceți clic pe fila Vizualizare pentru a selecta o vizualizare, pentru a îngheța rândurile sau coloanele unui registru de lucru sau pentru a afișa o grilă sau anteturi.

Lucrul cu file și meniuri cu panglică

Comenzile rapide de la tastatură din acest tabel vă pot ajuta să economisiți timp atunci când lucrați cu file și meniuri panglică.

Comenzi rapide de la tastatură pentru Excel Online

Notă: Dacă o foaie de calcul este deschisă în Excel Online în vizualizarea de citire, comenzile de editare nu funcționează. Pentru a trece la modul de editare, mutați focalizarea în afara foii (apăsați CTRL + F6) și apoi utilizați tasta TAB pentru a naviga la listă Editează cartea... Apăsați SPAȚIU, apoi tasta săgeată în jos și apoi faceți clic Editați în Excel Online.

Comenzi Excel Online: Editarea celulelor

Acțiune	Chei
Introduceți rânduri de tabel de sus	ALT + WIN + Z, BB, A
Introduceți coloane de tabel în stânga	ALT + WIN + I, BB, L
Decuparea
Copie

Anularea unei operațiuni
Repetați operația
Mergi la linie nouăîn celulă

Introduceți un tabel
Inserarea unei funcții
Măriți dimensiunea fontului
Reduceți dimensiunea fontului
Aplicație de filtrare
Reaplicarea filtrului

Comenzi Excel Online: Introducerea datelor

Comenzi Excel Online: Editați datele într-o celulă

Acțiune	Chei
Editarea celulei selectate
Suport tehnic pentru utilizatorii cu dizabilități Microsoft se angajează să se asigure că toți clienții noștri primesc cele mai bune produse si serviciul. Dacă aveți dizabilități sau sunteți interesat de întrebări legate de oportunități speciale, contactați Microsoft Disability Answer Desk pentru suport tehnic... Microsoft Disability Answer Desk este familiarizat cu multe tehnologii de asistență populare și poate oferi asistență în engleză, spaniolă, franceză și limbajul semnelor american. Accesați site-ul Microsoft Disability Answer Desk pentru informații de contact pentru regiunea dvs. Dacă sunteți de la o agenție guvernamentală sau o organizație comercială, vă rugăm să contactați

Acțiune

Chei

Editarea celulei selectate

Suport tehnic pentru utilizatorii cu dizabilități

Microsoft se angajează să se asigure că toți clienții noștri primesc cele mai bune produse si serviciul. Dacă aveți dizabilități sau sunteți interesat de întrebări legate de oportunități speciale, contactați Microsoft Disability Answer Desk pentru suport tehnic... Microsoft Disability Answer Desk este familiarizat cu multe tehnologii de asistență populare și poate oferi asistență în engleză, spaniolă, franceză și limbajul semnelor american. Accesați site-ul Microsoft Disability Answer Desk pentru informații de contact pentru regiunea dvs.

Dacă sunteți de la o agenție guvernamentală sau o organizație comercială, vă rugăm să contactați

Acest tutorial vorbește despre principalele beneficii ale caracteristicilor INDEXși CĂUTAREîn Excel, care le fac mai atractive în comparație cu CĂUTARE V... Veți vedea mai multe exemple de formule care vă vor ajuta să faceți față cu ușurință multor probleme complexe pe care funcția CĂUTARE V neputincios.

În mai multe articole recente, am depus toate eforturile pentru a educa utilizatorii începători cu privire la elementele de bază ale funcției. CĂUTARE Vși arată exemple de formule mai complexe pentru utilizatorii avansați. Acum vom încerca, dacă nu, să vă descurajăm să utilizați CĂUTARE V, atunci măcar arată moduri alternative implementați căutarea verticală în Excel.

De ce avem nevoie de asta? - tu intrebi. Da deoarece CĂUTARE V Nu este singura funcție de căutare din Excel, iar 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- sunt 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 SEARCH

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.

Vom da aici minimul necesar pentru a înțelege esența, iar apoi vom analiza în detaliu exemple de formule care arată avantajele utilizării INDEXși CĂUTARE in loc de CĂUTARE V.

INDEX - sintaxa și aplicarea funcției

Funcţie INDEX(INDEX) în Excel returnează o valoare dintr-o matrice la numerele de rând și coloană date. Funcția are următoarea sintaxă:

Fiecare argument are o explicație foarte simplă:

matrice(matrice) este intervalul de celule din care se extrage valoarea.
row_num(line_number) este numărul liniei din matrice din care se extrage valoarea. Dacă nu este specificat, atunci este necesar un argument. num_coloană(număr_coloană).
num_coloană(număr_coloană) este numărul coloanei din matrice din care se extrage 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.

Aici cel mai simplu exemplu funcţie INDEX(INDEX):

INDEX (A1: C10,2,3)
= INDEX (A1: C10; 2; 3)

Formula caută intervalul A1: C10și returnează valoarea celulei din 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 - aplicație de sintaxă și funcție

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 o cifră 3 deoarece „Londra” este al treilea element din listă.

MECI ("Londra", B1: B3,0)
= CĂUTARE („Londra”; B1: B3; 0)

Funcţie MECI(MATCH) are următoarea sintaxă:

MATCH (lookup_value, lookup_array,)
MATCH (valoare_căutare; matrice_căutare; [tip_potrivire])

valoare_căutare(lookup_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ă.
lookup_array(lookup_array) - intervalul de celule în care se caută.
match_type(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 scanată trebuie să fie î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 o potrivire exactă este întotdeauna necesară, deci al treilea argument al funcției CĂUTARE ar trebui să fie egal 0 .
- -1 - Găsește cea mai mică valoare mai mare sau egală cu valoarea dorită. Matricea scanată trebuie să fie în ordine descrescătoare, adică de la cel mai mare la cel mai mic.

La prima vedere, beneficiile funcției CĂUTARE este în îndoială. Cine trebuie să cunoască poziția unui element într-un interval? Vrem să cunoaștem semnificația acestui element!

Permiteți-mi să vă reamintesc că poziția relativă a valorii dorite (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 num_coloană(număr_coloană) funcție INDEX(INDEX). După cum vă amintiți, funcția INDEX poate returna valoarea la intersectie linii dateși coloană, dar nu poate determina ce rând și coloană ne interesează.

Cum se utilizează INDEX și SEARCH î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 dorite într-un interval dat de celule și INDEX folosește acel număr (sau numere) și returnează rezultatul din celula corespunzătoare.

Nu este încă destul de clar? Introduceți funcții INDEXși CĂUTAREîn această formă:

INDEX (, (POTIRE ( valoarea dorită,coloana în care privim,0))
= INDEX ( coloana din care se extrage; (CĂUTARE ( valoarea dorită;coloana în care privim;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; CĂUTARE ("Japonia"; $ B $ 2: $ B $ 10; 0))

Acum să aruncăm o privire la ceea ce face fiecare element al acestei formule:

Funcţie MECI(MATCH) caută „Japonia” într-o coloană B, în special - în celule B2: B10, și returnează numărul 3 pentru că „Japonia” se află pe locul trei pe listă.
Funcţie INDEX(INDEX) utilizări 3 pentru argumentare row_num(line_number), care specifică din ce linie să returneze valoarea. Acestea. se obtine o formula simpla:
INDEX ($ D $ 2: $ D $ 10,3)
= INDEX ($ D $ 2: $ D $ 10,3)
Formula spune cam așa: căutare în celule din D2 inainte de D10și extrageți valoarea din al treilea rând, adică din celulă D4întrucât numărarea începe de la a doua linie.

Iată rezultatul în Excel:

Important! Numărul de rânduri și coloane din matricea utilizată de funcție INDEX(INDEX), trebuie să se potrivească cu valorile argumentelor row_num(număr_linie) și num_coloană(număr_coloană) funcție MECI(CĂUTARE). În caz contrar, rezultatul formulei va fi eronat.

Stop, stop... de ce nu putem folosi funcția CĂUTARE V(CĂUTARE V)? Are vreun rost să pierzi timpul încercând să descoperi labirinturile? CĂUTAREși INDEX?

CĂUTARE V ("Japonia", $ B $ 2: $ D $ 2,3)
= CĂUTARE V ("Japonia"; $ B $ 2: $ D $ 2; 3)

În acest caz, nu are sens! Scopul acestui exemplu este doar în scop demonstrativ, astfel încât să puteți înțelege cum funcționează CĂUTAREși INDEX lucrează în perechi. Următoarele exemple vă vor arăta adevărata putere a pachetului. INDEXși CĂUTARE care se descurcă cu ușurință multe situații dificile în care CĂUTARE V este năucită.

De ce este INDEX / SEARCH mai bun decât VLOOKUP?

Atunci când decid ce formulă să folosești pentru căutarea 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 încă folosesc CĂUTARE V de cand această funcție este mult mai simplă. Acest lucru se întâmplă deoarece foarte puțini oameni înțeleg pe deplin beneficiile trecerii de la CĂUTARE V pe un pachet INDEXși CĂUTARE, și nimeni nu vrea să piardă timpul studiind o formulă mai complexă.

4 avantaje principale ale utilizării SEARCH / INDEX în Excel:

1. Căutați de la dreapta la stânga. După cum știe orice utilizator Excel alfabetizat, CĂUTARE V nu poate privi spre stânga, ceea ce înseamnă că valoarea dorită trebuie neapărat să fie în coloana din stânga a intervalului investigat. 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ăugarea sau eliminarea coloanelor în siguranță. Formule cu funcție CĂUTARE V nu mai funcționează sau returnați valori eronate dacă ștergeți sau adăugați o coloană la tabelul de căutare. Pentru funcție CĂUTARE V orice coloană inserată sau ștearsă va schimba rezultatul formulei, din sintaxă CĂUTARE V vă solicită să specificați întreaga gamă și numărul de coloană specific din care doriți să extrageți date.

De exemplu, dacă aveți o masă A1: C10, și doriți să extrageți date din coloană B, atunci trebuie să setați valoarea 2 pentru argumentare col_index_num(număr_coloană) funcție CĂUTARE V, asa:

VLOOKUP („valoare de căutare”, A1: C10,2)
= CĂUTARE V ("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 pe care tocmai ai introdus-o.

Folosind CĂUTARE/INDEX, Puteți elimina sau adăuga coloane în intervalul studiat 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 cu privire la remedierea fiecărei funcții pe care o utilizați. CĂUTARE V.

3. Nu există limită pentru mărimea valorii dorite. Folosind CĂUTARE V, amintiți-vă despre limita de lungime a valorii dorite de 255 de caractere, altfel riscați să obțineți o eroare #VALOARE!(#VALOARE!). Deci, dacă tabelul conține șiruri lungi, singura soluție validă este utilizarea INDEX/CĂUTARE.

Să presupunem că utilizați această formulă cu CĂUTARE V cautand in celule din B5 inainte de D10 valoarea specificată în celulă A2:

VLOOKUP (A2, B5: D10,3, FALSE)
= VLOOKUP (A2, B5: D10, 3, FALSE)

Formula nu va funcționa dacă valoarea este în celulă A2 mai mult de 255 de caractere. În schimb, trebuie să utilizați o formulă similară INDEX/CĂUTARE:

INDEX (D5: D10, MATCH (TRUE, INDEX (B5: B10 = A2,0), 0))
= INDEX (D5: D10; CĂUTARE (ADEVĂRAT; INDEX (B5: B10 = A2; 0); 0))

4. Viteză mai mare de lucru. Dacă lucrați cu tabele mici, atunci diferența de performanță Excel cel mai probabil nu va fi vizibilă, mai ales în ultimele 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 atunci când utilizați CĂUTAREși INDEX in loc de CĂUTARE V... În general, o astfel de înlocuire crește viteza Excel cu 13% .

Influență CĂUTARE V performanța Excel este remarcabilă în special 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 matricea conține mai multe valori și cu cât mai multe formule matrice conține tabelul dvs., cu atât Excel funcționează mai lent.

Pe de altă parte, o formulă cu funcții CĂUTAREși INDEX doar caută și returnează rezultatul, făcând aceeași muncă vizibil mai rapid.

INDEX și MATCH - exemple de formule

Acum că înțelegi motivele pentru care merită să înveți funcțiile CĂUTAREși INDEX, să trecem la cele mai interesante și să vedem cum poți aplica cunoștințele teoretice în practică.

Cum să căutați din partea stângă folosind CĂUTARE și INDEX

Orice tutorial pe CĂUTARE V insistă că această funcție nu poate privi spre stânga. Acestea. dacă coloana care este vizualizată nu este cea mai din stânga în 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 le pasă unde se află coloana cu valoarea de preluat. De exemplu, să revenim la tabelul cu capitalele de stat și populația. De data aceasta, să scriem formula CĂUTARE/INDEX, care va arăta ce loc din punct de vedere al populației este capitala Rusiei (Moscova).

După cum puteți vedea în imaginea de mai jos, formula face acest lucru foarte bine:

INDEX ($ A $ 2: $ A $ 10, MECI ("Rusia", $ B $ 2: $ B $ 10,0))

Acum nu ar trebui să aveți nicio problemă să înțelegeți cum funcționează această formulă:

În primul rând, folosim funcția MECI(MATCH) care găsește poziția „Rusiei” în listă:
MECI ("Rusia", $ B $ 2: $ B $ 10,0))
= CĂUTARE („Rusia”; $ B $ 2: $ B $ 10; 0))
Apoi, setăm intervalul pentru funcție INDEX(INDEX) din care se extrage valoarea. În cazul nostru este A2: A10.
Apoi conectăm ambele părți și obținem formula:
INDEX ($ A $ 2: $ A $ 10; MECI ("Rusia"; $ B $ 2: $ B $ 10; 0))
= INDEX ($ A $ 2: $ A $ 10; CAUTARE („Rusia”; $ B $ 2: $ B $ 10; 0))

Prompt: Soluția corectă este să folosiți întotdeauna link-uri absolute pentru INDEXși CĂUTARE astfel încât intervalele de căutare să nu se piardă la copierea formulei în alte celule.

Calcule folosind INDEX și SEARCH în Excel (MEDIA, MAX, MIN)

Puteți cuibărește alții Funcții Excel v INDEXși CĂUTARE, de exemplu, pentru a găsi minimul, maximul sau cel mai apropiat de medie. Iată câteva opțiuni pentru formulele aplicate tabelului din:

1. MAX(MAX). Formula găsește maximul într-o coloană D C aceeasi linie:

INDEX ($ C $ 2: $ C $ 10, MECI (MAX ($ D $ 2: I $ 10), $ D $ 2: D $ 10,0))
= INDEX ($ C $ 2: $ C $ 10; CĂUTARE (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, MECI (MIN ($ D $ 2: I $ 10), $ D $ 2: D $ 10,0))
= INDEX ($ C $ 2: $ C $ 10; CĂUTARE (MIN ($ D $ 2: I $ 10); $ D $ 2: D $ 10; 0))

Rezultat: Lima

3. IN MEDIE(IN MEDIE). Formula calculează media într-un interval D2: D10, apoi găsește cel mai apropiat de acesta și returnează valoarea din coloană C aceeasi linie:

INDEX ($ C $ 2: $ C $ 10, MECI (MEDIA ($ D $ 2: D $ 10), $ D $ 2: D $ 10,1))
= INDEX ($ C $ 2: $ C $ 10; CAUTARE (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 CĂUTARE

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 dacă intervalul pe care îl vizualizați conține o valoare egală cu media. Dacă ești sigur că există o astfel de valoare, pune 0 pentru a găsi o potrivire exactă.

Dacă specificați 1 , valorile din coloana de căutare trebuie sortate în ordine crescătoare, iar formula va returna valoarea maximă mai mică sau egală cu media.
Dacă specificați -1 , valorile din coloana de căutare trebuie sortate în ordine descrescătoare, iar valoarea minimă care este mai mare sau egală cu media este returnată.

În exemplul nostru, valorile din coloană D ordonate în ordine crescătoare, deci folosim tipul de potrivire 1 ... Formulă INDEX/SEARCHPOZ returnează „Moscova” deoarece populația orașului Moscova este cea mai apropiată de medie (12.269.006).

Cum să utilizați INDEX și SEARCH pentru a căuta un rând și o coloană cunoscute

Această formulă este echivalentă cu căutarea bidimensională CĂUTARE Vși vă permite să găsiți o valoare 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 acest tutorial, 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_linie; [număr_coloană])

Și îi felicit pe cei care ați ghicit!

Să începem prin a scrie un șablon de formulă. Pentru aceasta, luăm formula deja cunoscută INDEX/CĂUTAREși adăugați încă o funcție CĂUTARE care va returna numărul coloanei.

INDEX (Tabelul dvs., (POTIRE (, coloana în care se caută, 0)), (POTIRE (, șir în care să căutați,0))
= INDEX (Tabelul dvs., (POTIT ( valoare pentru căutare verticală,coloana în care se caută, 0)), (POTRIV ( valoare pentru căutare orizontală,șir în care să căutați,0))

Rețineți că pentru o căutare bidimensională, trebuie să specificați întregul tabel în argument matrice(matrice) funcții INDEX(INDEX).

Acum să punem acest model la încercare. Mai jos puteți vedea o listă cu cele mai populate țări din lume. Să presupunem că sarcina noastră este să aflăm populația SUA în 2015.

Bine, hai să scriem formula. Când trebuie să creez o formulă complexă în Excel cu funcții imbricate, mai întâi scriu fiecare una imbricată separat.

Deci, să începem cu două funcții CĂUTARE care va returna numerele rândurilor și coloanelor pentru funcție INDEX:

CAUTĂ o coloană- căutăm în coloană B, sau mai degrabă în gamă B2: B11, valoarea specificată în celulă H2(STATELE UNITE ALE AMERICII). Funcția va arăta astfel:
MECI ($ H $ 2, $ B $ 1: $ B $ 11,0)
= CĂUTARE ($ H $ 2; $ B $ 1: $ B $ 11; 0)
4 ca „SUA” este al patrulea 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:
MECI ($ H $ 3, $ A $ 1: $ E $ 1,0)
= CĂUTARE ($ H $ 3; $ A $ 1: $ E $ 1; 0)

Rezultatul acestei formule va fi 5 deoarece „2015” este în coloana a 5-a.

Acum inserăm aceste formule în funcție INDEX si voila:

INDEX ($ A $ 1: $ E $ 11, MECI ($ H $ 2, $ B $ 1: $ B $ 11,0), MECI ($ H $ 3, $ A $ 1: $ E $ 1,0))
= INDEX ($ A $ 1: $ E $ 11; CĂUTARE ($ H $ 2; $ B $ 1: $ B $ 11; 0); CĂUTARE ($ H $ 3; $ A $ 1: $ E $ 1; 0))

Dacă înlocuim funcţiile CĂUTARE pe valorile pe care le returnează, formula va deveni ușoară și simplă:

INDEX ($ A $ 1: $ E $ 11,4,5))
= INDEX ($ A $ 1: $ E $ 11; 4; 5))

Această formulă returnează valoarea la intersecție Al 4-leașiruri și al 5-lea coloană în interval A1: E11, adică valoarea celulei E4... Doar? Da!

Căutare multiplă cu INDEX și SEARCH

Î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 mai multe criterii. Cu toate acestea, o limitare semnificativă a acestei soluții a fost necesitatea adăugării unei coloane auxiliare. Vești bune: formula INDEX/CĂUTARE poate căuta după valori în două coloane, fără a fi nevoie să creați o coloană auxiliară!

Să presupunem că avem o listă de comenzi și dorim să găsim suma după 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, iar numele cumpărătorilor sunt în tabelul de pe foaie Tabel de căutare sunt aranjate într-o ordine anume.

Aceasta este 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; MATCH (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 celelalte pe care le-am discutat mai devreme, dar înarmată cu cunoașterea funcțiilor. INDEXși CĂUTARE O vei învinge. Cea mai grea parte este funcția CĂUTARE Cred că ar trebui 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 dorită este 1 iar tabloul de căutare este rezultatul înmulțirii. Bine, ce ar trebui să înmulțim și de ce? Să o despărțim în ordine:

Luăm prima valoare din coloană A(Client) pe foaie Masa principalăși comparați-l cu numele tuturor 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 (MINCIT).
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 obține 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 o poziție numai atunci când ambele criterii sunt î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, setăm întregul tabel și trebuie să spunem funcției din ce coloană să extragă valoarea. În cazul nostru, aceasta este coloana C(Suma) si asa am intrat 3 .

Și în sfârșit, de când trebuie să verificăm fiecare celulă din matrice, această formulă ar trebui să fie o formulă matrice. Puteți vedea acest lucru prin acoladele care îl înconjoară. Deci, când ați terminat de introdus formula, nu uitați să apăsați Ctrl + Shift + Enter.

Dacă totul este făcut corect, veți obține rezultatul ca în imaginea de mai jos:

INDEX și SEARCH în combinație cu IFERROR în Excel

După cum probabil ați observat deja (și de mai multe ori), dacă introduceți o valoare incorectă, de exemplu, care nu se află în matricea 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 formula cu INDEXși CĂUTARE in functie DACA EROARE.

Sintaxa funcției DACA EROARE foarte simplu:

IFERROR (valoare, value_if_error)
IFERROR (valoare; value_if_error)

Unde este argumentul valoare(valoare) este valoarea verificată pentru o eroare (în cazul nostru, rezultatul formulei INDEX/CĂUTARE); si argumentul valoare_dacă_eroare(value_if_error) este valoarea de returnat dacă formula aruncă o eroare.

De exemplu, puteți introduce în funcție DACA EROARE asa:

DACĂ EROARE (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. Vă rugăm să încercați din nou!”) = IFEROARE (INDEX ($ A $ 1: $ E $ 11; CĂUTARE ($ G $ 2; $ B $ 1: $ B $ 11; 0); CĂUTARE ($ G) $ 3; $ A $ 1 : $ E $ 1; 0));
„Nu s-au găsit potriviri. Vă rugăm să încercați din nou!”)

Și acum, dacă cineva introduce o valoare greșită, formula va returna 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... Asa:

IFEROARE (INDEX (matrice, MATCH (valoare_căutare, matrice_căutare, 0), "")
IFEROARE (INDEX (matrice; CĂUTARE (valoare_căutare; matrice_căutare; 0), "")

Sper că ați găsit util cel puțin una dintre formulele din acest tutorial. Dacă ați întâlnit 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ă.

Acest articol discută sintaxa funcției CĂUTARE Excelși, de asemenea, cum să utilizați funcția CAUTA in Excel cu exemple formule.

De asemenea, acest articol discută o combinație de formule CĂUTARE INDEX în Excel.

V Microsoft Excel există multe funcții de căutare diferite care vă pot ajuta să găsiți sens definitîntr-un număr de celule și CĂUTARE- unul din ei. CĂUTARE în engleză MATCH identifică practic poziția relativă a unui element într-un interval de celule. Cu toate acestea, funcția CĂUTARE Excel poate face mult mai mult atunci când este combinat cu alte funcții.

Funcția MATCH în Excel - sintaxă și utilizare

Funcția MATCH în Excel caută o anumită valoare într-un interval de celule și returnează poziția relativă a acelei valori.

Sintaxă formule MATCH după cum urmează:

MATCH (valoare_căutare; matrice_căutare; [tip_potrivire])

Valoare_căutare(argument obligatoriu) - valoarea pe care doriți să o găsiți. Poate fi o valoare numerică, textuală sau booleană sau o referință de celulă.

View_array(argument obligatoriu) - intervalul de celule de căutat.

Tip_potrivire(argument opțional) - specifică tipul de potrivire. Poate fi una dintre următoarele valori: 1, 0, -1.

1 sau absent (implicit) - găsește cea mai mare valoare într-o matrice care este mai mică sau egală cu valoarea de căutare. Matricea de căutare trebuie sortată în ordine crescătoare, de la cel mai mic la cel mai mare sau de la A la Z.
0 - găsește prima valoare din matrice care exact egal valoarea de căutare. Nu este necesară sortarea.
-1 - găsește cea mai mică valoareîntr-o matrice care este mai mare sau egală cu valoarea de căutare. Matricea de căutare trebuie sortată în ordine descrescătoare, de la cel mai mare la cel mai mic sau de la Z la A.

Pentru a înțelege mai bine această funcție, să scriem un simplu formula CĂUTARE pe baza următoarelor date: numele elevilor din coloana A și scorurile la examene din coloana B, sortate de la cel mai mare la cel mai mic. Pentru a afla unde se află, printre altele, un anumit student (de exemplu, un student la Vinogradova), utilizați această formulă simplă:

CĂUTARE (E1; A2: A8; 0)

Opțional, puteți pune valoarea de căutare în celulă (E1 în acest exemplu) și faceți referire la acea celulă din formula Excel MATCH:

Funcția MATCH în Excel - Un exemplu de utilizare a funcției MATCH în Excel

După cum puteți vedea în imaginea de mai sus, numele de familie ale studenților sunt introduse într-o ordine anume, așa că stabilim argumentul match_type egal cu 0 (potrivire exactă), deoarece acest tip de potrivire singur nu necesită sortarea valorilor din matricea de căutare. Din punct de vedere tehnic, formula CĂUTARE returnează poziția relativă a elevului Vinogradova în intervalul studiat. Dar, din moment ce notele sunt sortate de la cel mai mare la cel mai mic, acest lucru ne spune, de asemenea, că Vinogradova este a cincea la rând.

Caracteristici ale funcției Excel MATCH

După cum tocmai ați văzut, utilizarea MATCH în Excel este ușoară. Cu toate acestea, ca orice funcție Excel, are câteva ciudații de care trebuie să fiți conștienți:

Funcția MATCH revine poziție relativă valorile de căutare din matrice, nu valoarea în sine.
CĂUTARE insensibil la majuscule, ceea ce înseamnă că nu face distincție între caracterele majuscule și minuscule atunci când lucrați cu valori de text.
Dacă matricea de căutare conține mai multe apariții ale valorii de căutare, este returnată poziția primei valori.
Dacă valoarea de căutare nu este găsită în matricea care este scanată, este returnată eroarea # N / A.

Utilizarea MATCH în Excel - exemple de formule

Acum că știți posibilitățile de bază Funcțiile MATCH în Excel Să aruncăm o privire la câteva exemple de formule care depășesc elementele de bază.

Formula de CĂUTARE Excel cu metacaractere

Ca multe alte funcții Excel, CĂUTARE recunoaște următoarele wildcards:

Semnul întrebării(?) - înlocuiește orice caracter
Asterisc (*) - înlocuiește orice secvență de caractere

Notă. Wildcardurile pot fi folosite numai în formule MATCH cu parametru match_type egal cu 0.

Formula CĂUTARE cu wildcards este util în situațiile în care doriți să căutați nu întregul șir de text, ci doar unele caractere sau o parte a șirului. Pentru a ilustra acest lucru, luați în considerare următorul exemplu.

Să presupunem că aveți o listă de companii și datele lor de vânzări pentru ultima săptămână. Doriți să găsiți poziția relativă a unei anumite companii în listă (sortată după vânzări în ordine descrescătoare), dar nu vă puteți aminti exact numele acesteia, deși vă amintiți primele litere.

Presupunând că numele companiilor sunt în intervalul A2: A8 și căutați un nume care începe cu est, formula arată astfel:

CĂUTARE ("est *"; A2: A8; 0)

Pentru a ne face formula CĂUTARE mai versatil, puteți introduce valoarea de căutare în orice celulă (E1 în acest exemplu) și acea celulă cu un wildcard, de exemplu:

CĂUTARE (E1 & „*”; A2: A8; 0)

După cum se arată în imaginea de mai jos, formula returnează a doua poziție, care corespunde companiei Vostok-Avto:

Funcția MATCH în Excel - Exemplu de funcție Excel MATCH cu metacaractere

Pentru a înlocui un singur caracter în valoarea de căutare, utilizați operatorul „?” Wildcard, de exemplu:

CĂUTARE („b? Yker”; A2: A8; 0)

Formula de mai sus se va potrivi cu numele „Baker” și va reveni la poziția sa relativă, care este 5.

Formulă MATCH sensibilă la majuscule

După cum am menționat la începutul acestui articol, Funcția Excel MATCH nu distinge între superior și literă mică... Pentru a crea o formulă de potrivire sensibilă la majuscule, utilizați CĂUTAREîmpreună cu funcția EXECUTE, care compară exact celulele, inclusiv cazul caracterului.

Sensibilitatea formulei pentru maparea datelor în Excel este următoarea:

CĂUTARE (ADEVĂRAT; POTRIVIRE (valoare_căutare; matrice_căutare); 0)

Logica din spatele acestei formule este următoarea:

Funcția EXACT compară valoarea de căutare cu fiecare element din tabloul care este scanat. Dacă celulele comparate sunt exact egale, funcția returnează TRUE sau FALSE în caz contrar.
Apoi, funcția MATCH compară TRUE (care este valoarea sa de căutare) cu fiecare valoare din matrice returnată de MATCH și returnează poziția primei potriviri

Notă că este o formulă matrice care necesită apăsarea Ctrl + Shift + Enter pentru a procesa corect.

Lăsați valoarea de căutare să fie în celula E1 și matricea de căutare este A2: A8, apoi formula arată astfel:

CĂUTARE (ADEVĂRAT; POTRIVITĂ (A2: A8; E1); 0)

Următoarea imagine demonstrează formula MATCH sensibil la majuscule și minuscule în Excel:

Funcția MATCH în Excel - Exemplu de funcție MATCH în Excel sensibil la majuscule și minuscule

Comparați 2 coloane pentru potriviri și diferențe (UNM și MATCH)

Verificarea a două liste pentru potriviri și diferențe este una dintre cele mai comune sarcini din Excel și se poate face căi diferite... Utilizarea formulei UNM / SEARCH este una dintre ele:

IF (UND (CĂUTARE (prima valoare din Lista 1; Lista 2; 0)); „Nu în Lista 1”; „”)

Pentru orice valoare din Lista 2 care nu este în Lista 1, formula returnează „Nu în Lista 1”. Și așa funcționează:

Funcția MATCH caută o valoare din Lista 1 în Lista 2. Dacă o valoare este găsită, returnează o poziție relativă, în caz contrar o eroare # N / A.
Funcția UND din Excel face un singur lucru: verifică erorile # N/A (ceea ce înseamnă „nu este disponibil”). Dacă valoarea specificată este o eroare # N / A, funcția returnează TRUE; în caz contrar, FALSE. În cazul nostru, TRUE înseamnă că o valoare din Lista 1 nu a fost găsită în Lista 2 (MATCH returnează # N / A eroare).
Deoarece va fi dificil pentru alți utilizatori să interpreteze rezultatele TRUE/FALSE, vom folosi funcția IF pentru a afișa textul „Nu este în Lista 1” pentru valorile care nu sunt în Lista 1.

Să presupunem că trebuie să comparăm valorile din coloana B cu valorile din coloana A, formula va lua următoarea formă (unde B2 este celula cea mai de sus):

IF (UND (CĂUTARE (B2; A: A; 0)); "Nu este în lista 1"; "")

După cum vă amintiți, funcția MATCH din Excel în sine nu face distincție între majuscule și minuscule. Pentru a face sensibile la majuscule și minuscule, introduceți funcția EXECUTE în argumentul lookup_array și nu uitați să apăsați Ctrl + Shift + Enter pentru a completa această formulă matrice:

DACĂ (UND (CĂUTARE (ADEVĂRAT; POTRIVIRE (A: A; B2); 0)); „Nu este în Lista 1”; „”)

Următoarea imagine demonstrează ambele formule în acțiune:

Funcția MATCH în Excel - Comparați 2 coloane pentru potriviri folosind RED și MATCH

INDEX și CĂUTARE în Excel

Cu funcție CĂUTARE Excel ne cunoaștem deja. Să vedem acum cum poți folosi combinația CĂUTARE INDEXîn Excel. Să începem cu sintaxa funcției INDEX.

Sintaxa și utilizarea funcției INDEX

Funcţie INDEX Excel returnează o valoare într-o matrice bazată pe rândurile și coloanele pe care le-ați specificat. Sintaxa pentru funcția INDEX este simplă:

INDEX (matrice; line_num; [column_num])

Iată o explicație foarte simplă pentru fiecare parametru:

matrice este intervalul de celule din care doriți să returnați o valoare.
line_number este numărul liniei din tabloul din care doriți să returnați o valoare. Dacă acest parametru este omis, este necesar column_number.
column_number este numărul coloanei din matrice din care doriți să returnați o valoare. Dacă acest parametru este omis, este necesar line_number.

Dacă sunt folosite atât row_num, cât și column_num, INDEX returnează valoarea din celula de la intersecția rândului și coloanei specificate.

Iată cel mai simplu exemplu de formulă INDEX:

INDEX (A1: C10; 2; 3)

Formula caută celulele de la A1 la C10 și returnează valoarea celulei din al doilea rând și a treia coloană, adică celula C2.

Foarte ușor, nu-i așa? Cu toate acestea, atunci când lucrați cu date reale, cu greu știți de ce rânduri și coloane aveți nevoie, așa că aveți nevoie de ajutorul funcției CĂUTARE.

CĂUTARE INDEX în exemplu Excel

Acum că cunoașteți sintaxa și elementele de bază ale acestor două funcții, probabil că aveți deja o înțelegere a modului în care funcționează. CĂUTARE INDEX Excel.

Funcția CĂUTARE determină poziția relativă a valorii de căutare în intervalul specificat de celule. A funcția INDEX ia acest număr și returnează valoarea în celula corespunzătoare.

Combinaţie CĂUTARE INDEX în Excel poate fi reprezentat astfel:

INDEX (coloana pentru a returna valoarea, SEARCH (valoarea de căutare, coloana pentru a căuta, 0))

Pentru o mai bună înțelegere, luați în considerare un exemplu ilustrativ. Să presupunem că avem o listă de țări cu astfel de populații:

Funcția CĂUTARE în Excel - Date sursă pentru formula CĂUTARE INDEX în Excel

Să găsim dimensiunea populației, de exemplu, a Rusiei, folosind următoarele formula CĂUTARE INDEX:

INDEX ($ C $ 2: $ C $ 11; CAUTARE („Rusia”; $ B $ 2: $ B $ 11; 0))

Acum să aruncăm o privire la ceea ce realizează de fapt fiecare componentă a acestei formule:

Funcția MATCH caută valoarea Rusia în coloana B, mai precis în celulele B2: B11, și returnează numărul 7 deoarece Rusia se află pe locul șapte în listă.
Funcția INDEX ia „7” în al doilea parametru (line_number), care indică din ce linie doriți să returnați valoarea și devine o formulă simplă = INDEX ($ C $ 2: $ C $ 11, 7). Adică, formula caută celulele C2-C11 și returnează valoarea celulei din al șaptelea rând, adică celula C8, deoarece începem din al doilea rând.

Și iată rezultatul pe care îl obținem în Excel:

Funcția CĂUTARE în Excel - Exemplu de formulă CĂUTARE INDEX

Notă! Numărul de rânduri și coloane din tabloul INDEX trebuie să corespundă cu valorile din parametrii row_num și/sau column_num ai funcției MATCH. În caz contrar, formula va returna un rezultat incorect.