Funcția CĂUTARE ( ) , versiunea în engleză SEARCH (), găsește prima apariție a unui șir de text într-un alt șir și returnează poziția de pornire a șirului găsit.

Sintaxa funcției

CĂUTARE(text_căutare;șir_căutare; [poziție_pornire])

Text_căutat- textul de găsit.

Viewed_String- textul în care se caută Text_căutat.

Start_position- poziția caracterului în șirul de căutare la care ar trebui să înceapă căutarea. Dacă argumentul poziție_începută este omis, valoarea se presupune a fi 1.

În argumentare text_căutare poate fi folosit - semnul întrebării (?) și asterisc (*). Semnul întrebării se potrivește cu orice caracter; un asterisc - orice succesiune de caractere. Dacă trebuie să găsiți un semn de întrebare sau un asterisc în text, ar trebui să puneți un tilde (~) în fața lor.

Dacă searchup_text nu este găsit, este returnată valoarea de eroare #VALUE!

Funcția SEARCH () nu respectă literele CASE. Pentru o căutare sensibilă la majuscule și minuscule, utilizați funcția FIND ().

Exemple de

Formulă = CĂUTARE ("către"; "Primul canal") va reveni 8, deoarece scrisoare La se afla pe pozitia a 8-a din stanga.

Lăsați să intre în celulă A2 linia introdusă Channel One este cel mai bun... Formulă = CĂUTARE (CHAR (32), A2) va reveni 7, deoarece caracterul spatiu (cod 32) se afla in pozitia a 7-a.

Formulă = CĂUTARE ("# ??? #"; "SKU # 123 # ID") va căuta în șirul " Articolul # 123 # ID„o secvență de 5 caractere care începe și se termină cu semnul #.

Pentru a găsi poziția celei de-a doua apariții a literei „a” în șirul „cadru de săpun mama” utilizați formula = CAUTARE („a”; „mama a spălat rama”; CĂUTARE („a”; „mama a spălat cadrul”) + 1). Pentru a determina dacă există o a treia apariție a literei „m” în linia „cadru de săpun pentru mama”, utilizați formula = IF (DLSTR (SUBSTITUTE ("cadru de săpun de mamă"; "m"; ""; 3)) = DLSTR ("cadru de săpun de mama"); „Fără a treia apariție a lui m”; „Există o a treia apariție a lui m” )

Formulă = CĂUTARE („comoara?”; „Memo”) va returna 3, i.e. cuvântul „memo” conține un cuvânt din 5 litere, dintre care primele 4 comoară(începând cu a treia literă a cuvântului raport).

Funcția FIND () vs SEARCH ().

Funcția FIND () este sensibilă la majuscule și nu acceptă metacaractere. Pentru căutări care nu țin seama de majuscule și minuscule, precum și pentru căutări folosind wildcards utilizați funcția CĂUTARE ().

Comunicare cu funcțiile STÂNGA (), DREAPTA () și MID ().

Funcția SEARCH () poate fi utilizată împreună cu funcțiile STÂNGA (), DREAPTA () și MID ().

De exemplu, în celulă A2 conține numele și prenumele „Ivanov Ivan”, apoi formula = STÂNGA (A2, CĂUTARE (CHAR (32), A2) -1) va extrage numele de familie și = DREAPTA (A2, DLSTR (A2) -CĂUTARE (CHAR (32), A2))- Nume. Dacă există mai mult de un spațiu între nume și prenume, atunci utilizați funcția TRIM () pentru a face ca formulele de mai sus să funcționeze.

Scopul principal al programului de birou Excel este de a efectua calcule. Documentul acestui program (Cartea) poate conține multe foi cu tabele lungi pline cu numere, text sau formule. O căutare rapidă automată vă permite să găsiți celulele necesare în ele.

Căutare simplă

Pentru a căuta o valoare într-un tabel Excel, deschideți lista derulantă a instrumentului Căutare și înlocuire din fila Acasă și faceți clic pe Găsiți. Același efect poate fi obținut folosind comanda rapidă de la tastatură Ctrl + F.

În cel mai simplu caz, în fereastra „Find and Replace” care apare, introduceți valoarea dorită și faceți clic pe „Find All”.

După cum puteți vedea, rezultatele căutării au apărut în partea de jos a casetei de dialog. Valorile găsite sunt subliniate cu roșu în tabel. Dacă în loc de „Find All” faceți clic pe „Find Next”, atunci prima celulă cu această valoare va fi căutată, iar când faceți din nou clic, a doua va fi căutată.

Căutarea textului se efectuează în același mod. În acest caz, textul de căutare este introdus în bara de căutare.

Dacă nu se caută date sau text în întregul tabel Excel, atunci trebuie mai întâi selectată zona de căutare.

Cautare Avansata

Să presupunem că doriți să găsiți toate valorile în intervalul 3000 până la 3999. În acest caz, tastați 3 ??? în bara de căutare. Wildcard "?" înlocuiește oricare altul.

Analizând rezultatele căutării, se poate observa că, alături de cele 9 rezultate corecte, programul a returnat și cele neașteptate, evidențiate cu roșu. Ele sunt asociate cu prezența numărului 3 într-o celulă sau formulă.

Se poate mulțumi cu majoritatea rezultatelor obținute ignorându-le pe cele greșite. Dar funcția de căutare din Excel 2010 poate funcționa mult mai precis. Pentru a face acest lucru, utilizați instrumentul „Opțiuni” din caseta de dialog.

Făcând clic pe „Opțiuni”, utilizatorul poate efectua o căutare avansată. În primul rând, să fim atenți la elementul „Zona de căutare”, în care valoarea „Formule” este setată implicit.

Aceasta înseamnă că căutarea a fost efectuată, inclusiv în acele celule în care nu se află valoarea, ci formula. Prezența numărului 3 în ele a dat trei rezultate incorecte. Selectarea Valorilor ca zonă de căutare va căuta numai date, iar rezultatele incorecte asociate cu celulele formulei vor dispărea.

Pentru a scăpa de singurul rezultat incorect rămas pe prima linie, în fereastra de căutare avansată, selectați elementul „Toată celulă”. După aceea, rezultatul căutării devine 100% precis.

Un astfel de rezultat ar putea fi obținut prin selectarea imediată a elementului „Întrega celulă” (chiar lăsând valoarea „Formule” în „Zona de căutare”).

Acum să trecem la elementul „Căutare”.

Dacă în loc de „Pe foaie” implicit selectați „În carte”, atunci nu este nevoie să fiți pe foaia celulelor pe care le căutați. Captura de ecran arată că utilizatorul a inițiat căutarea în timp ce se afla pe foaia goală 2.

Următorul element din fereastra de căutare avansată este „Răsfoiește”, care are două semnificații. În mod implicit, este setat la „linie cu linie”, ceea ce înseamnă succesiunea de scanare a celulelor linie cu linie. Alegerea unei alte valori - „pe coloane”, va schimba doar direcția de căutare și succesiunea rezultatelor.

Când căutați în documente Microsoft Excel, puteți utiliza un alt caracter joker - „*”. Dacă este considerat „?” înseamnă orice caracter, atunci „*” înlocuiește nu unul, ci orice număr de caractere. Mai jos este o captură de ecran a unei căutări pentru Louisiana.

Uneori, căutarea dvs. trebuie să țină cont de majuscule și minuscule. Dacă cuvântul louisiana este scris cu litere mici, rezultatele căutării nu se vor schimba. Dar dacă selectați „Potrivire majuscule” în fereastra de căutare avansată, căutarea nu va fi reușită. Programul va considera cuvintele Louisiana și Louisiana diferite și, desigur, nu va găsi primul dintre ele.

Căutați soiuri

Căutați potriviri

Uneori este necesar să găsiți valori duplicate într-un tabel. Pentru a căuta potriviri, mai întâi trebuie să selectați un interval de căutare. Apoi, în aceeași filă Acasă, în grupul Stiluri, deschideți instrumentul Formatare condiționată. Apoi selectați secvențial elementele „Reguli de selecție a celulelor” și „Valori duplicate”.

Rezultatul este afișat în captura de ecran de mai jos.

Dacă este necesar, utilizatorul poate schimba culoarea afișajului vizual al celulelor potrivite.

Filtrare

Un alt tip de căutare este filtrarea. Să presupunem că utilizatorul dorește să găsească valori numerice în coloana B în intervalul de la 3000 la 4000.

După cum puteți vedea, sunt afișate doar liniile care îndeplinesc condiția introdusă. Toate celelalte au fost ascunse temporar. Pentru a reveni la starea inițială, repetați pasul 2.

Au fost examinate diferite opțiuni de căutare folosind exemplul Excel 2010. Cum se face o căutare în Excel a altor versiuni? Diferența de tranziție la filtrare este în versiunea 2003. În meniul „Date”, trebuie să selectați secvențial comenzile „Filter”, „AutoFilter”, „Condition” și „Custom AutoFilter”.

Video: Căutare în tabel Excel

De asemenea, articole despre lucrul cu tabele în Excel:

• Formatarea tabelelor în Excel
• Crearea tabelelor in excel
• Creați un tabel pivot în excel
• Cum se selectează un întreg tabel în Excel?

Printre miile de rânduri și zeci de coloane de date manual din tabelul Excel, este aproape imposibil să găsești ceva. Singura opțiune este să folosiți un fel de funcție de căutare și apoi ne vom uita la modul în care se efectuează o căutare într-un tabel Excel.

Pentru a căuta date într-un tabel Excel, trebuie să utilizați elementul de meniu „Găsiți și selectați” din fila „Acasă”, în care trebuie să selectați opțiunea „Găsiți” sau să utilizați combinația de taste „Ctrl + F” pentru a apela .

De exemplu, să încercăm să găsim numărul necesar printre datele din tabelul nostru, deoarece atunci când căutați numere este necesar să luăm în considerare unele dintre subtilitățile căutării. Să căutăm numărul „10” în tabelul Excel.

După ce ați selectat elementul de meniu necesar în caseta de căutare care apare, introduceți valoarea dorită. Avem două opțiuni pentru a găsi valori în tabelul Excel, aceasta este să găsim toate potrivirile simultan făcând clic pe butonul „Găsiți toate” sau vizualizați imediat fiecare celulă găsită apăsând butonul „Găsiți următorul” de fiecare dată. Când utilizați butonul „Găsiți următorul”, ar trebui să luați în considerare și locația curentă a celulei active, deoarece căutarea va începe exact din această poziție.

Să încercăm să găsim toate valorile simultan, în timp ce tot ce a fost găsit va fi listat în fereastra sub setările de căutare. Dacă părăsiți toate setările implicite, rezultatul căutării nu va fi exact ceea ce ne așteptam.

Pentru căutarea corectă a datelor în tabelul Excel, faceți clic pe butonul „Parametri” și configurați zona de căutare. Acum valoarea necesară este căutată chiar și în formulele utilizate în celule pentru calcule. Trebuie să specificăm căutarea numai în valori și, dacă se dorește, putem specifica și formatul datelor căutate.

Atunci când căutați cuvinte într-un tabel Excel, ar trebui să țineți cont și de toate aceste subtilități și, de exemplu, puteți lua în considerare chiar și cazul literelor.

Și, în sfârșit, să vedem cum să căutați date în Excel numai în zona necesară a foii. După cum puteți vedea din exemplul nostru, valoarea dorită „10” apare în toate coloanele de date simultan. Dacă trebuie să găsiți această valoare, să spunem, doar în prima coloană, trebuie să selectați această coloană sau orice interval de valori în care doriți să căutați, apoi să începeți căutarea.

Prima noastră coloană are doar două valori egale cu „10”, așa că dacă aplicăm opțiunea „Find All”, ar trebui să apară doar două rezultate de căutare în listă.

În documentele Microsoft Excel, care constau dintr-un număr mare de câmpuri, este adesea necesar să se găsească date specifice, numele rândului etc. Este foarte incomod când trebuie să te uiți printr-un număr mare de rânduri pentru a găsi cuvântul sau expresia dorită. Căutarea încorporată Microsoft Excel vă va ajuta să economisiți timp și nervi. Să vedem cum funcționează și cum se folosește.

Funcția de căutare în Excel

Funcția de căutare din Microsoft Excel oferă posibilitatea de a găsi textul sau valorile numerice dorite prin fereastra Găsire și înlocuire. În plus, aplicația are opțiunea de căutare avansată a datelor.

Metoda 1: căutare simplă

O simplă căutare de date în Excel vă permite să găsiți toate celulele care conțin un set de caractere (litere, cifre, cuvinte etc.) introduse în fereastra de căutare, fără a ține seama de majuscule și minuscule.

1. Fiind în fila „Acasă”, faceți clic pe butonul „Găsiți și selectați”, care se află pe panglica din bara de instrumente „Editare”. În meniul care apare, selectați elementul „Găsiți...”. În loc de aceste acțiuni, puteți pur și simplu să tastați comanda rapidă de la tastatură Ctrl + F.
2. După ce ați parcurs elementele relevante de pe panglică sau ați apăsat combinația de „taste rapide”, se va deschide fereastra „Găsiți și înlocuiți” în fila „Găsiți”. Avem nevoie de ea. În câmpul „Găsiți”, introduceți cuvântul, simbolurile sau expresiile după care vom căuta. Faceți clic pe butonul „Find Next” sau pe butonul „Find All”.
3. Când faceți clic pe butonul „Find Next”, trecem la prima celulă, care conține grupurile de caractere introduse. Celula însăși devine activă.

   Căutarea și livrarea rezultatelor se efectuează rând cu rând. Toate celulele din primul rând sunt procesate mai întâi. Dacă nu au fost găsite date care să corespundă condiției, programul începe să caute în a doua linie și așa mai departe, până când găsește un rezultat satisfăcător.

   Caracterele de căutare nu trebuie să fie elemente independente. Deci, dacă expresia „drepturi” este specificată ca cerere, atunci toate celulele care conțin acest set secvenţial de caractere, chiar și în cadrul unui cuvânt, vor fi prezentate în rezultat. De exemplu, cuvântul „Dreapta” va fi considerat o interogare relevantă în acest caz. Dacă introduceți numărul „1” în motorul de căutare, atunci răspunsul va fi celule care conțin, de exemplu, numărul „516”.

   Pentru a trece la următorul rezultat, apăsați din nou butonul „Găsiți următorul”.

   Acest lucru poate fi continuat până când afișarea rezultatelor începe pe un nou cerc.

4. Dacă, la începerea procedurii de căutare, dați clic pe butonul „Găsiți toate”, toate rezultatele căutării vor fi prezentate sub forma unei liste în partea de jos a ferestrei de căutare. Această listă conține informații despre conținutul celulelor cu date care satisfac interogarea de căutare, adresa locației acestora, precum și foaia și cartea cărora le aparțin. Pentru a accesa oricare dintre rezultatele problemei, faceți clic pe el cu butonul stâng al mouse-ului. După aceea, cursorul se va muta în celula Excel pe care a făcut clic utilizatorul.

Metoda 2: căutare după un interval specificat de celule

Dacă aveți un tabel destul de mare, atunci în acest caz nu este întotdeauna convenabil să căutați întreaga foaie, deoarece rezultatele căutării pot conține un număr mare de rezultate care nu sunt necesare într-un anumit caz. Există o modalitate de a limita spațiul de căutare doar la un anumit interval de celule.

1. Selectați zona de celule în care dorim să căutăm.
2. Introducem combinația de taste Ctrl + F pe tastatură, după care va începe fereastra deja familiară „Găsiți și înlocuiți”. Acțiunile ulterioare sunt exact aceleași ca în metoda anterioară. Singura diferență este că căutarea este efectuată numai în intervalul specificat de celule.

Metoda 3: Căutare avansată

După cum sa menționat mai sus, în timpul unei căutări normale, absolut toate celulele care conțin un set secvenţial de caractere de căutare sub orice formă, indiferent de caz, sunt incluse în rezultatele căutării.

În plus, nu numai conținutul unei anumite celule, ci și adresa elementului la care se referă, pot intra în rezultate. De exemplu, celula E2 conține o formulă care este suma celulelor A4 și C3. Această sumă este 10 și acesta este numărul care apare în celula E2. Dar, dacă setăm numărul „4” în căutare, atunci aceeași celulă E2 se va afla printre rezultatele căutării. Cum s-ar fi putut întâmpla asta? Doar că celula E2 conține adresa celulei A4 ca formulă, care include doar cifra dorită 4.

Dar cum să tăiați astfel și alte rezultate de căutare evident inacceptabile? În aceste scopuri există o căutare avansată în Excel.

1. După deschiderea ferestrei „Găsiți și înlocuiți” în oricare dintre modurile de mai sus, faceți clic pe butonul „Opțiuni”.
2. În fereastră apar un număr de instrumente suplimentare pentru gestionarea căutării. În mod implicit, toate aceste instrumente sunt în aceeași stare ca o căutare normală, dar pot fi făcute ajustări dacă este necesar.

   În mod implicit, funcțiile „Match case” și „Whole cells” sunt dezactivate, dar dacă bifam casetele de lângă articolele corespunzătoare, atunci când se formează rezultatul se va ține cont de majusculele introduse, iar potrivirea exactă va fi luată. în considerare. Dacă introduceți un cuvânt cu literă mică, atunci în rezultatele căutării, celulele care conțin ortografia acestui cuvânt cu literă majusculă, așa cum ar fi implicit, nu vor mai fi incluse. În plus, dacă funcția „Celele întregi” este activată, atunci numai elementele care conțin numele exact vor fi adăugate la ieșire. De exemplu, dacă introduceți interogarea de căutare „Nikolaev”, atunci celulele care conțin textul „Nikolaev AD” nu vor fi adăugate la rezultatele căutării.

   În mod implicit, este căutată numai foaia de lucru Excel activă. Dar, dacă mutați parametrul „Căutare” în poziția „În carte”, atunci căutarea se va efectua în toate foile fișierului deschis.

   În parametrul „Browse”, puteți schimba direcția căutării. În mod implicit, după cum sa menționat mai sus, căutarea se efectuează rând cu rând în ordine. Deplasând comutatorul în poziția „După coloane”, puteți seta ordinea în care sunt generate rezultatele emiterii, începând de la prima coloană.

   În coloana „Zona de căutare” se stabilește ce elemente specifice sunt căutate. În mod implicit, acestea sunt formule, adică datele care, atunci când dați clic pe o celulă, sunt afișate în bara de formule. Poate fi un cuvânt, un număr sau o referință de celulă. În același timp, programul, atunci când efectuează o căutare, vede doar linkul, nu rezultatul. Acest efect a fost discutat mai sus. Pentru a căuta exact după rezultate, după datele care sunt afișate în celulă, și nu în bara de formule, trebuie să mutați comutatorul din poziția „Formule” în poziția „Valori”. În plus, este posibilă căutarea după note. În acest caz, mutați comutatorul în poziția „Note”.

   Puteți seta căutarea și mai precis făcând clic pe butonul „Format”.

   Aceasta deschide fereastra cu formatul celulei. Aici puteți seta formatul celulelor care vor participa la căutare. Puteți seta restricții privind formatul numerelor, aliniere, font, chenar, umplere și protecție, unul dintre acești parametri sau o combinație a acestora.

   Dacă doriți să utilizați formatul unei anumite celule, atunci în partea de jos a ferestrei, faceți clic pe butonul „Utilizați formatul acestei celule...”.

   După aceea, apare un instrument de picătură. Cu acesta, puteți selecta celula al cărei format îl veți utiliza.

   După ce formatul de căutare este configurat, faceți clic pe butonul „OK”.

   Există momente când trebuie să căutați nu o anumită expresie, ci să găsiți celule care conțin cuvinte de căutare în orice ordine, chiar dacă sunt separate prin alte cuvinte și simboluri. Apoi, aceste cuvinte trebuie evidențiate pe ambele părți cu un „*”. Acum rezultatele căutării vor afișa toate celulele în care se află cuvintele date în orice ordine.

3. Odată ce setările de căutare sunt setate, ar trebui să faceți clic pe butonul „Find All” sau „Find Next” pentru a merge la rezultatele căutării.

După cum puteți vedea, Excel este un set de instrumente de căutare destul de simplu, dar în același timp foarte funcțional. Pentru a face cel mai simplu scârțâit, este suficient să apelați fereastra de căutare, să introduceți o interogare în ea și să faceți clic pe butonul. Dar, în același timp, este posibil să personalizați o căutare individuală cu un număr mare de parametri diferiți și setări suplimentare.

Ne bucurăm că am putut să vă ajutăm să rezolvați problema.

Pune-ți întrebarea în comentarii, detaliind esența problemei. Experții noștri vor încerca să răspundă cât mai repede posibil.

Te-a ajutat acest articol?

O parte semnificativă a sarcinilor care sunt rezolvate cu ajutorul foilor de calcul presupun că utilizatorul are deja cel puțin câteva date inițiale pentru a găsi rezultatul dorit. Cu toate acestea, Excel 2010 dispune de instrumentele necesare cu care puteți rezolva această problemă dimpotrivă - să selectați datele necesare pentru a obține rezultatul dorit.

„Căutarea unei soluții” este unul dintre astfel de instrumente, cel mai convenabil pentru „problemele de optimizare”. Și dacă nu l-ați folosit până acum, acum este momentul să îl reparați.

Deci - începem prin a instala acest add-on (din moment ce nu va apărea singur). Din fericire, acum acest lucru se poate face destul de simplu și rapid - deschideți meniul „Service” și deja în el „Suplimente”

Rămâne doar să indicați „Excel Add-ins” în coloana „Management”, apoi faceți clic pe butonul „Go”.

După această simplă acțiune, butonul de activare a „Căutare soluție” va fi afișat în „Date”. Așa cum se arată în imagine

Să aruncăm o privire la cum să utilizați corect Găsiți soluții în Excel 2010 cu câteva exemple simple.

Exemplul unu.

Să presupunem că sunteți șeful unui departament mare de producție și trebuie să distribuiți corect bonusurile angajaților. Să presupunem că valoarea totală a bonusurilor este de 100.000 de ruble și este necesar ca bonusurile să fie proporționale cu salariile.

Adică acum trebuie să găsim coeficientul de proporționalitate corect pentru a determina cuantumul bonusului raportat la salariu.

În primul rând, este necesar să compilați rapid (dacă nu există deja) un tabel în care vor fi stocate formulele și datele originale, conform cărora se va putea obține rezultatul dorit. Pentru noi, acest rezultat este suma totală a primei. Și acum, atenție - celula țintă C8 trebuie să fie legată de celula variabilă dorită sub adresa E2 folosind formule. Acest lucru este critic. În exemplu, le legăm folosind formule intermediare, care sunt responsabile de calcularea bonusului pentru fiecare angajat (C2: C7).

Acum puteți activa Căutare soluții. Se va deschide o nouă fereastră, în care trebuie să specificăm parametrii necesari.

Sub „” se află celula țintă. Nu poate fi decât unul.

"" Sunt posibile opțiuni de optimizare. În total, puteți selecta valori posibile „Maximum”, „Minim” sau „Specific”. Și dacă aveți nevoie doar de o anumită valoare, atunci trebuie să o indicați în coloana corespunzătoare.

"" - pot exista mai multe celule modificabile (un întreg interval sau adrese specificate separat). La urma urmei, cu ele va funcționa Excel, parcurgând opțiunile astfel încât să se obțină valoarea specificată în celula țintă.

"" - Dacă trebuie să setați restricții, atunci ar trebui să utilizați butonul "Adăugați", dar vom lua în considerare acest lucru puțin mai târziu.

"" - un buton pentru trecerea la calcule interactive bazate pe programul pe care l-am specificat.

Dar acum să revenim la capacitatea de a ne schimba sarcina folosind butonul „Adăugați”. Această etapă este destul de responsabilă (nu mai puțin decât construcția formulelor), deoarece este constrângerea care vă permite să obțineți rezultatul corect la ieșire. Totul aici se face cât mai convenabil posibil, astfel încât să le puteți seta nu numai pentru întreaga gamă simultan, ci și pentru anumite celule.

Pentru a face acest lucru, puteți utiliza o serie de semne specifice (și familiare tuturor utilizatorilor Excel 2010) „=", "> =", "

Avem un tabel care înregistrează volumele vânzărilor anumitor bunuri în luni diferite. Este necesar să găsiți date în tabel, iar criteriul de căutare va fi anteturile de rânduri și coloane. Dar căutarea trebuie făcută separat pe un interval de rând sau coloană. Adică va fi folosit doar unul dintre criterii. Prin urmare, funcția INDEX nu poate fi folosită aici, dar este nevoie de o formulă specială.

Găsirea valorilor într-un tabel Excel

Pentru a rezolva această problemă, vom ilustra un exemplu pe un tabel schematic care corespunde condițiilor descrise mai sus.

Foaie cu un tabel pentru găsirea valorilor pe verticală și pe orizontală:

Deasupra tabelului în sine, există un rând cu rezultatele. În celula B1 introducem criteriul pentru interogarea de căutare, adică antetul coloanei sau numele rândului. Și în celula D1, formula de căutare ar trebui să returneze rezultatul calculării valorii corespunzătoare. După aceea, a doua formulă va funcționa în celula F1, care va folosi deja valorile celulelor B1 și D1 ca criterii pentru găsirea lunii corespunzătoare.

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

Acum vom afla în ce volum maxim și în ce lună a fost vânzarea maximă a Produsului 4.

Pentru a căuta pe coloane:

S-a găsit în ce lună și care a fost cea mai mare vânzare a produsului 4 timp de două trimestre.

Principiul formulei pentru găsirea unei valori într-un rând Excel:

Primul argument al funcției VLOOKUP (Vertical Look) specifică o referință la celula în care se află termenul de căutare. Al doilea argument specifică intervalul de celule de vizualizat în timpul căutării. Al treilea argument al funcției CĂUTARE V ar trebui să indice numărul coloanei din care ar trebui luată valoarea față de linia cu numele Produs 4. Dar, deoarece nu cunoaștem acest număr dinainte, folosim funcția COLUMN pentru a crea o matrice de numere de coloane pentru intervalul B4: G15.

Acest lucru permite funcției VLOOKUP să colecteze o întreagă matrice de valori. Ca urmare, toate valorile corespunzătoare pentru fiecare coloană pentru rândul Produs 4 sunt stocate în memorie (și anume: 360; 958; 201; 605; 462; 832). După aceea, funcția MAX trebuie doar să ia numărul maxim din această matrice și să-l returneze ca valoare pentru celula D1, ca rezultat al calculării formulei.

După cum puteți vedea, construcția formulei este simplă și concisă. Pe baza acestuia, puteți găsi alți indicatori pentru un anumit produs într-un mod similar. De exemplu, valoarea minimă sau medie a volumului vânzărilor folosind funcțiile MIN sau MEDIE. Nimic nu te împiedică să aplici acest schelet al formulei folosind funcții mai complexe pentru a implementa cea mai confortabilă analiză a raportului de vânzări.

Cum să obțineți titlurile coloanelor de la începutul unei celule?

De exemplu, cât de impresionant am afișat luna în care a existat vânzarea maximă, folosind a doua formulă. Nu este greu de observat că în a doua formulă am folosit scheletul primei formule fără funcția MAX. Structura principală a formulei: VLOOKUP (B1; A5: G14; COLUMN (B5: G14); 0). Am înlocuit funcția MAX cu MATCH, care folosește valoarea obținută prin formula anterioară în primul argument. Acum acționează ca un criteriu pentru găsirea lunii. Și ca urmare, funcția CĂUTARE ne returnează numărul coloanei 2, unde se află valoarea maximă a volumului de vânzări pentru produsul 4. După aceea, funcția INDEX este inclusă în lucrare, care returnează valoarea prin număr. a datei și a coloanei din intervalul specificat în argumentele sale. Deoarece avem un număr de coloană de 2, iar numărul rândului din intervalul în care sunt stocate numele lunilor va fi în orice caz 1. Atunci avem funcția INDEX pentru a obține valoarea corespunzătoare din intervalul B4: G4 - februarie (a doua lună).



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

A doua variantă a problemei va fi o căutare în tabel folosind numele lunii drept criteriu. În astfel de cazuri, trebuie să schimbăm scheletul formulei noastre: înlocuiți funcția VLOOKUP cu HLOOKUP, iar funcția COLUMN este înlocuită cu ROW.

Acest lucru ne va permite să aflăm ce volum și ce produs a fost vânzarea maximă într-o anumită lună.

Pentru a afla care produs a avut cel mai mare volum de vânzări într-o anumită lună, ar trebui să:

Principiul formulei pentru găsirea unei valori într-o coloană Excel:

În primul argument al funcției HLP (Horizontal View), indicăm o legătură către o celulă cu un criteriu de căutare. Al doilea argument este o referire la intervalul tabelului căutat. Al treilea argument este generat de funcția LINE, care creează o matrice de 10 elemente de numere de linie în memorie. Deoarece avem 10 rânduri în secțiunea tabelară.

În plus, funcția HLP, folosind pe rând fiecare număr de linie, creează o matrice a valorilor vânzărilor corespunzătoare din tabel pentru o anumită lună (iunie). Apoi funcția MAX trebuie doar să selecteze valoarea maximă din această matrice.

ATENŢIE! Când utilizați formula schelet pentru alte sarcini, acordați întotdeauna atenție celui de-al doilea și al treilea argument al funcției de căutare HLO. Numărul de rânduri acoperite din intervalul specificat în argument trebuie să se potrivească cu numărul de rânduri din tabel. Și, de asemenea, numerotarea trebuie să înceapă din a doua linie!

Bună prieteni. Cât de des trebuie să căutați o potrivire într-un tabel Excel pentru o anumită valoare? De exemplu, trebuie să găsiți adresa unei persoane într-un director sau în lista de prețuri - prețul unui produs. Dacă astfel de sarcini sunt întâlnite - această postare este pentru tine!

Fac aceste proceduri în fiecare zi și fără funcțiile descrise mai jos, chiar mi-ar fi greu. Ia notă și aplică-le în munca ta!

Funcții de căutare în tabel Excel, CĂUTARE V și CĂUTARE HL

Rolul acestor funcții în viața unui utilizator obișnuit poate fi cu greu supraestimat. Acum puteți găsi cu ușurință o înregistrare adecvată în tabelul de date și puteți returna valoarea corespunzătoare.

Sintaxa pentru funcția CĂUTARE V este: = CĂUTARE V ( Valoare_căutare; tabel_pentru_căutare; column_number de afișat; [match_type]). Luați în considerare argumentele:

• Valoarea dorită- valoarea pe care o vom căuta. Acesta este un argument necesar;
• Căutați în tabel- matricea de celule în care va fi căutarea. Coloana cu valorile dorite trebuie să fie prima din această matrice. Acesta este, de asemenea, un argument necesar;
• Numărul coloanei afișat- numărul ordinal al coloanei (începând de la prima din matrice) din care funcția va scoate date dacă se potrivesc valorile necesare. Argument necesar;
• Tip de potrivire- selectați „1” (sau „ADEVRAT”) pentru o potrivire nestrictă, „0” (“FALSE”) pentru o potrivire completă. Argumentul este opțional, dacă îl omiteți, căutarea va fi efectuată chibrit liber.

Găsirea unei potriviri exacte folosind VLOOKUP

Să ne uităm la un exemplu despre cum funcționează funcția CĂUTARE VL când tipul de potrivire este „FALSE”, căutând o potrivire exactă. În matricea B5: E10 sunt indicate mijloacele fixe ale unei anumite companii, valoarea lor contabilă, numărul de inventar și locația. Celula B2 conține numele pentru care trebuie să găsiți numărul de inventar în tabel și să-l plasați în celula C2.

Funcția VLOOKUP în Excel

Să scriem formula: = CĂUTARE V (B2; B5: E10; 3; FALS).

Aici, primul argument indică faptul că tabelul ar trebui căutat pentru valoarea din celula B2, adică. cuvântul „Fax”. Al doilea argument spune că tabelul pentru căutare este în intervalul B5: E10 și trebuie să căutați cuvântul „Fax” în prima coloană, adică. în matricea B5: B10. Al treilea argument îi spune programului că rezultatul calculului este conținut în a treia coloană a tabloului, adică. D5: D10. Al patrulea argument este FALS, adică. este necesară potrivirea completă.

Și astfel, funcția va primi linia „Fax” din celula B2 și o va căuta în matricea B5: B10 de sus în jos. Odată ce este găsită o potrivire (linia 8), funcția va returna valoarea corespunzătoare din coloana D, adică continutul D8. Este exact ceea ce aveam nevoie, problema a fost rezolvată.

Dacă valoarea necesară nu este găsită, funcția va reveni.

Găsiți potriviri neclare cu VLOOKUP

Datorită acestei opțiuni în munca VLOOKUP, putem evita formulele complexe pentru a găsi rezultatul dorit.

Matricea B5: C12 arată ratele dobânzii la împrumuturi în funcție de valoarea împrumutului. În celula B2, indicăm suma împrumutului și dorim să obținem rata în C2 pentru o astfel de tranzacție. Sarcina este dificilă deoarece suma poate fi orice și este puțin probabil să coincidă cu cele specificate în matrice; căutarea după potrivirea exactă nu este potrivită:

Apoi notăm formula pentru o căutare non-strict: = CĂUTARE V (B2, B5: C12, 2, ADEVĂRAT)... Acum programul va căuta cea mai apropiată mai mică dintre toate datele prezentate în coloana B. Adică pentru suma de 8.000 se va selecta valoarea 5000 și se va afișa procentul corespunzător.

Căutare lină VLOOKUP în Excel

Pentru ca funcția să funcționeze corect, trebuie să sortați prima coloană a tabelului în ordine crescătoare. În caz contrar, poate da un rezultat eronat.

Funcția HLOOKUP are aceeași sintaxă ca și funcția VLOOKUP, dar caută rezultatul nu în coloane, ci în rânduri. Adică scanează tabelele nu de sus în jos, ci de la stânga la dreapta și afișează numărul de rând specificat, nu coloana.

Găsirea datelor utilizând funcția BROWSE

Funcția LOOKUP funcționează similar cu VLOOKUP, dar are o sintaxă diferită. Îl folosesc atunci când tabelul de date conține câteva zeci de coloane și pentru a folosi VLOOKUP este necesar să calculez suplimentar numărul coloanei afișate. În astfel de cazuri, funcția LOOKUP facilitează sarcina. Și astfel, sintaxa este: = CĂUTARE ( Valoare_căutare; Array_for_search; Array_to_ display) :

• Valoarea dorită- date sau link către datele de căutat;
• Matrice de căutare- un rând sau coloană în care căutăm o valoare similară. Trebuie să sortăm această matrice în ordine crescătoare;
• Matrice de afișat- un interval care conține date pentru afișarea rezultatelor. Desigur, trebuie să aibă aceeași dimensiune ca și matricea de căutare.

Cu această notație, dați o referință nerelativă la tabloul rezultat. Și arăți direct către el, adică. nu este nevoie să precalculați numărul coloanei de ieșire. Folosim funcția LOOKUP în primul exemplu pentru funcția VLOOKUP (active fixe, numere de inventar): = CĂUTARE (B2, B5: B10, D5: D10)... Problema a fost rezolvată cu succes!

Funcția LOOKUP în Microsoft Excel

Căutare după coordonatele relative. Funcțiile CĂUTARE și INDEX

O altă modalitate de a căuta date este să combinați funcțiile CĂUTARE și INDEX.

Prima dintre ele este folosită pentru a căuta o valoare într-o matrice și pentru a obține numărul său ordinal: SEARCH ( Valoare_căutare; Watched_array; [Tip de potrivire] ). Argumente ale funcției:

• Valoarea dorită- argument necesar
• Matrice de privit- un rând sau coloană în care căutăm o potrivire. Argument necesar
• Tip de potrivire- specificați „0” pentru a căuta o potrivire exactă, „1” – cea mai apropiată mai mică, „-1” – cea mai apropiată mai mare. Deoarece funcția caută de la începutul listei până la sfârșit, atunci când îl căutați pe cel mai apropiat cel mai mic, sortați coloana de căutare în ordine descrescătoare. Și când căutați mai multe, sortați-o în ordine crescătoare.

Poziția valorii necesare a fost găsită, acum o puteți afișa pe ecran folosind funcția INDEX( Matrice; Numărul de linie; [Column_number]) :

• Matrice- argumentul indică din ce matrice de celule să selecteze o valoare
• Numărul de linie- specificați numărul ordinal al liniei (începând de la prima celulă a matricei) pe care doriți să o afișați. Aici puteți scrie valoarea manual sau puteți utiliza rezultatul calculării unei alte funcții. De exemplu, CĂUTARE.
• Numărul coloanei- argument opțional, specificat dacă tabloul este format din mai multe coloane. Dacă este omisă, formula utilizează prima coloană din tabel.

Acum să combinăm aceste funcții pentru a obține rezultatul:

Funcțiile CĂUTARE și INDEX în Excel

Acestea sunt modalitățile de a găsi și afișa date în Excel. Mai mult, le puteți folosi în calcule, le puteți folosi într-o prezentare, puteți efectua operații cu ele, specifica alte funcții ca argumente etc.

Simți cum cunoștințele și abilitățile tale cresc și devin mai puternice? Atunci nu te opri, continuă să citești! În următoarea postare vom lua în considerare: va fi dificil și interesant!

Funcţie CĂUTAREîn Excel este folosit pentru a determina poziția textului în orice text și pentru a indica poziția exactă a acestuia.

Ce returnează funcția

Funcția returnează o valoare numerică care indică poziția de pornire a textului de căutare în alt text. Poziția desemnează numărul ordinal al caracterului de la care începe textul de căutare.

Sintaxă

= CĂUTARE (găsește_text, în_text,)- Versiune în limba engleză

= CĂUTARE (text_căutare; text_căutare; [poziție_pornire])- versiunea rusă

Argumente ale funcției

• find_text (search_text)- textul sau șirul de text pe care doriți să îl găsiți;
• în_text (text_vizualizabil)- textul în care cauți;
• ([poziție_pornire])- o valoare numerică care indică poziția din care doriți să începeți căutarea. Dacă nu specificați acest argument, atunci funcția va începe căutarea de la începutul textului.

Informații suplimentare

• Dacă nu este specificată poziția de pornire a căutării, atunci căutarea textului este efectuată prima dintre text;
• Funcția nu face distincție între majuscule și minuscule. Dacă aveți nevoie de sensibilitatea majusculelor, atunci utilizați funcția;
• Funcția poate gestiona metacaracterele. Există trei caractere metalice în Excel -?, *, ~.
  • semn "?" - se potrivește cu orice caracter individual;
  • „*” - se potrivește cu orice caractere suplimentare;
  • semnul „~” este folosit atunci când trebuie să găsiți semnul de întrebare în sine sau un asterisc.
• Funcția returnează o eroare dacă textul de căutare nu a fost găsit.

Exemple de utilizare a funcției CĂUTARE în Excel

Exemplul 1. Căutarea unui cuvânt într-un șir de text (de la început)

În exemplul de mai sus, puteți vedea că atunci când căutăm cuvântul „bun” în textul „Bună dimineața”, funcția returnează valoarea „1”, care corespunde poziției cuvântului „bun” în textul „ Buna dimineata".

Deoarece funcția nu face distincție între majuscule și minuscule, nu are nicio diferență cum specificăm cuvântul dorit „bun”, fie că este „KIND”, „Kind”, „kind”, etc. funcția va returna aceeași valoare.

Dacă trebuie să faceți o căutare sensibilă la majuscule și minuscule - utilizați funcția din Excel.

Exemplul 2. Căutăm un cuvânt în interiorul unui șir de text (indicând poziția de început a căutării)

Al treilea argument al funcției indică poziția ordinală în text, din care se va efectua căutarea. În exemplul de mai sus, funcția returnează „1” atunci când caută „bun” în textul „Bună dimineața”, începând cu prima poziție.

În același timp, dacă indicăm funcției că căutarea ar trebui să înceapă de la al doilea caracter al textului „Bună dimineața”, adică, funcția în acest caz vede textul ca „bună dimineața” și caută cuvântul „ bine”, atunci rezultatul va fi o eroare.

Dacă nu specificați poziția de început pentru căutare ca argument, funcția va începe automat căutarea de la începutul textului.

Exemplul 3. Căutați un cuvânt în prezența mai multor potriviri în text

Funcția începe căutarea textului din poziția de pornire, pe care o putem specifica ca argument, sau va începe căutarea de la începutul textului automat. În exemplul de mai sus, căutăm cuvântul „bun” în textul „Bună dimineața” cu poziția de pornire a căutării „1”. În acest caz, funcția returnează „1”, deoarece primul cuvânt găsit „Kind” începe cu primul caracter al textului.

Dacă indicăm funcției începutul căutării, de exemplu, de la al doilea caracter, atunci rezultatul calculului funcției va fi „8”.

Exemplul 4. Utilizarea metacaracterelor în funcția CĂUTARE în Excel

Funcția ia în considerare metacaracterele la căutare. În exemplul de mai sus, căutăm textul „c * l”. Prezența wildcardului „*” în această interogare înseamnă că căutăm orice cuvânt care începe cu litera „c” și se termină cu litera „l”, iar ceea ce este între aceste două litere nu este important. Ca urmare, funcția returnează valoarea „3”, deoarece în cuvântul „Excel” situat în celula A2, litera „c” se află în a treia poziție.