Funcţie CĂUTAREîn Excel este folosit pentru a determina locația textului în cadrul unui 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 indică numărul ordinal al caracterului de la care începe textul de căutare.
Sintaxă
=CAUTĂ(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 (view_text)– textul în care cauți;
- ([poziție_pornire]) este o valoare numerică care indică poziția din care doriți să începeți căutarea. Dacă acest argument nu este specificat, funcția va începe căutarea de la începutul textului.
informatii suplimentare
- Dacă nu este specificată poziția de pornire a căutării, atunci textul este căutat primul dintre text;
- Funcția nu face distincție între majuscule și minuscule. Dacă aveți nevoie de diferențierea minusculelor, atunci utilizați ;
- Funcția poate gestiona metacaracterele. Există trei caractere wildcard în Excel - ?, *, ~.
- semn "?" – se potrivește cu orice caracter individual;
- semnul „*” – se potrivește cu orice caractere suplimentare;
- semnul „~” este folosit dacă trebuie să găsiți semnul întrebării sau asteriscul în sine.
- Funcția returnează o eroare dacă textul de căutare nu este 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)
Exemplul de mai sus arată 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 „Bună dimineața”.
Deoarece funcția nu face distincție între majuscule și minuscule, nu are nicio diferență cum specificăm cuvântul de căutare „kind”, dacă este „KIND”, „Kind”, „kind”, etc. funcția va returna aceeași valoare.
Dacă trebuie să efectuați o căutare cu 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ă valoarea „1” la căutarea cuvântului „bun” în textul „Bună dimineața”, începând căutarea acestuia din prima poziție.
Cu toate acestea, dacă îi spunem 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 „bun”, atunci rezultatul va fi o eroare.
Dacă nu specificați o poziție de pornire pentru căutare ca argument, funcția va începe automat căutarea de la începutul textului.
Exemplul 3: Găsirea unui cuvânt atunci când există mai multe potriviri în text
Funcția începe căutarea textului dintr-o poziție 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 „Bine” începe cu primul caracter al textului.
Dacă îi spunem funcției să înceapă căutarea, de exemplu, de la al doilea caracter, atunci rezultatul calculului funcției va fi „8”.
Exemplul 4: Utilizarea caracterelor wildcard în funcția CĂUTARE în Excel
La căutare, funcția ia în considerare metacaracterele. Î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.
Să presupunem că raportul dvs. conține un tabel cu multe date în mai multe coloane. Analiza vizuală a unor astfel de tabele este extrem de dificilă. Și una dintre sarcinile de lucru cu raportul este analiza datelor referitoare la 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, bineînțeles că puteți folosi instrumentul: „HOME” - „Editare” - „Găsiți” CTRL + F pentru a apela fereastra de căutare pentru valori pe foaia Excel. Sau creați o regulă de formatare condiționată pentru tabel. Dar atunci va fi imposibil să efectuați calcule suplimentare cu rezultatele obținute. Prin urmare, este necesar să se creeze și să se aplice corect formula corespunzătoare.
Găsirea unei valori într-o matrice Excel
Schema de rezolvare a problemelor 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 matriceală inversă. Un exemplu specific, pe scurt, arată cam așa. Scopul stabilit în cifre este valoarea inițială, este necesar să se determine cine și când este cel mai aproape de acest obiectiv. Ca exemplu, să folosim o matrice de date simplă care raportează trei sferturi din articolele vândute, așa cum se arată în figura de mai jos. Este important ca toate numerele să se potrivească. Dacă nu doriți să creați și să completați manual o foaie de calcul Excel de la zero, atunci la sfârșitul articolului puteți descărca un exemplu gata făcut.
Vom lua în considerare succesiv soluții de complexitate diferită, iar la sfârșitul articolului - rezultatul final.
Găsirea unei valori într-o coloană Excel
În primul rând, să învățăm cum să obținem antetele 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 intrare (î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ă ce ați introdus formula pentru confirmare, apăsați combinația de taste rapide CTRL + SHIFT + Enter, deoarece formula trebuie executată într-o matrice. Dacă este făcut corect, acoladele ( ) vor apărea în jurul marginilor barei de formule.
Găsirea unei valori într-un rând Excel
Acum obținem numărul liniei pentru aceeași valoare (5277). Pentru a face acest lucru, în celula C3, introduceți următoarea formulă:
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 tabelului corespunzătoare. Ca rezultat, avem adresa completă a valorii D9.
Cum să obțineți antetul coloanei și titlul rândului unui tabel
Acum să învățăm cum să obținem după valoare coordonatele nu ale întregii foi, ci ale tabelului curent. Într-un cuvânt, trebuie să găsim după valoarea 5277 în loc de D9 pentru a obține anteturi:
- pentru coloana tabelului - martie;
- pentru linia - Item4.
Pentru a rezolva această problemă, vom folosi formula 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 acelorași valori î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, în celula E2, introduceți formula:
Mai mult, pentru intervalul secțiunii tabelare, vom crea o regulă de formatare condiționată:
După cum puteți vedea, dacă există duplicate, formula antetului preia titlul de la primul duplicat orizontal (de la stânga la dreapta). Iar formula pentru obținerea numelui (numărul) liniei ia numărul de la primul duplicat vertical (de sus în jos). Există 2 moduri de a remedia această soluție:
Î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, coordonatele verticale ale primului duplicat (de sus în jos) sunt corect afișate - I7 pentru foaie și august; Produs2 pentru masă. Să lăsăm această opțiune pentru următorul exemplu final.
Găsirea celei mai apropiate valori dintr-un interval Excel
Această masă încă nu este perfectă. La urma urmei, atunci când analizezi, trebuie să știi exact toate valorile sale. Dacă numărul introdus în celula B1 nu este găsit în tabel, atunci este returnată o eroare - #VALOR! Ideal ar fi ca formula, în absența unui număr inițial în tabel, să selecteze ea însăși cea mai apropiată valoare pe care o conține tabelul. Pentru a crea un astfel de program de analiză de tabel, în celula F1, introduceți o nouă formulă:
După care urmează în toate celelalte formule, schimbați referința în loc de B1 ar trebui să fie F1! De asemenea, trebuie să modificați linkul în formatare condiționată. Selectați: „ACASĂ” - „Stiluri” - „Formatare condiționată” - „Gestionați regulile” - „Editare regulă”. Și aici în parametri specificați F1 în loc de B1. Pentru a testa programul, 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 însuși 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 cel original - 5000. Un astfel de program poate fi util pentru rezolvarea automată a diverselor probleme analitice în planificarea afacerii, stabilirea de obiective, găsirea unei soluții raționale etc. Și 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.
Adesea, atunci când lucrați cu date în Excel, trebuie să ștergeți sau să ascundeți selectiv unele dintre informații, cel mai adesea acestea sunt rânduri sau coloane care conțin sau nu anumite cuvinte, litere, numere, simboluri sau combinații ale acestora. Instrumentele standard Excel, cum ar fi căutarea, filtrul și filtrul avansat, ajută în acest sens. Dacă aceste instrumente nu sunt suficiente pentru a rezolva problema, VBA vine în ajutor.
Cum să găsiți cuvintele date în rândurile Excel? Găsiți cuvinte, litere, numere și simboluri în Excel
Cel mai simplu mod de a căuta un anumit cuvânt, literă, număr, caracter sau combinație a acestora este căutarea standard. Toți parametrii pentru căutare sunt introduși în caseta de dialog „Găsiți și înlocuiți”, care poate fi apelată din meniul principal sau folosind combinația de taste rapide „Ctrl + f” (unde f este prima literă a cuvântului englez find - găsi). Pe lângă căutarea obișnuită, puteți efectua și o căutare de înlocuire.
Utilizarea filtrelor pentru a căuta un anumit cuvânt, literă, număr sau simbol în Excel
Puteți utiliza filtrarea pentru a selecta rândurile care conțin informații definite de utilizator. În Excel 2007 și versiuni ulterioare, de exemplu, pe lângă filtrul de text obișnuit, există un filtru după culoarea de umplere a celulei și culoarea fontului. Filtrul de text vă permite să utilizați condiții precum „este egal cu...”, „nu este egal cu...”, „începe cu...”, „se termină cu...”, „conține... ", "nu contine ...". După ce toate liniile necesare sunt filtrate, puteți efectua orice acțiuni cu acestea, inclusiv ștergerea liniilor.
Cum să găsiți și să ștergeți în mod programatic anumite rânduri în Excel folosind declarația similară VBA?
Din diverse motive, instrumentele standard Excel nu sunt întotdeauna potrivite pentru rezolvarea anumitor sarcini. Mai jos este codul macro care vă permite să găsiți textul definit de șablon în celulele intervalului utilizat și să ștergeți întreaga linie a foii de lucru active care conține celula cu textul dat. Textul de căutare este atribuit variabilei „Shablon” folosind caractere speciale de potrivire a modelului .
Sub Delete_Strok_Po_Shablonu() Dim r As Long, FirstRow As Long, LastRow As Long Dim Region As Range, iRow As Range, Cell As Range Dim Shablon As String Shablon = "textul de căutare este introdus aici" Set Region = ActiveSheet.UsedRange FirstRow = Regiune .Rând LastRow = Regiune.Rând - 1 + Regiune.Rânduri.Număr pentru r = LastRow la FirstRow Pasul -1 Setați iRow = Regiune.Rânduri (r - FirstRow + 1) Pentru fiecare celulă din iRow.Cells Dacă Celulă Ca Shablon Apoi Rânduri ( r).Delete End If Next Cell Next r End Sub
Această macrocomandă folosește căutarea neclară și operatorul de comparare VBA Like pentru a găsi expresiile necesare pentru a potrivi șirurile cu un model. Atunci când compară șiruri, acest operator distinge între litere mari și mici, iar rezultatul comparării depinde de instrucțiune Opțiunea Comparare .
Funcțiile VBA Instr și Find pot fi, de asemenea, folosite pentru a rezolva probleme similare.
Efectuează acțiuni similare supliment pentru Excel, a cărui utilizare vă permite să introduceți textul de căutare fără caractere speciale în caseta de dialog și să setați diferite zone de căutare.
Cu o cantitate mare de date pe o foaie Excel, mai devreme sau mai târziu va trebui să cauți o valoare redistribuită. O căutare simplă se efectuează folosind comanda: „Găsiți și selectați”, care se află în grupul „Editare” din fila „Acasă”.
Vezi și versiunea video a articolului.
De asemenea, puteți apela caseta de dialog de căutare folosind combinația de taste rapide " ctrl+f«.
De fapt, caseta de dialog se numește „ Găsiți și înlocuiți„pentru că rezolvă aceste două sarcini legate și dacă este apelat printr-o combinație fierbinte” ctrl+h„, atunci totul va fi la fel, doar fereastra se va deschide cu fila „Înlocuire” activată.
Aici, la prima vedere, totul este simplu: am introdus cuvântul și am găsit celula necesară pe foaia Excel. Desigur, nu se poate exclude o astfel de opțiune de dezvoltare atunci când căutarea va fi efectuată rapid, cu toate acestea, situația este mai probabilă atunci când introduceți valoarea dorită, iar foaia de calcul nu o caută sau trebuie să efectuați o non-strict. căutați atunci când textul căutat este amintit aproximativ, ceea ce, apropo, se întâmplă adesea când căutați persoane după nume.
În acest material, vom lua în considerare nuanțele și secretele casetei de căutare, cu care majoritatea utilizatorilor nu sunt familiarizați.
Unele setări care pot ajuta la rezolvarea sarcinii sunt ascunse în spatele butonului „Parametri”.
Luați în considerare situația în care trebuie să găsiți un angajat al lui Komarov Alexander Ivanovich, dar există o serie de factori care provoacă dificultăți:
- nu știm cum se scrie, cu nume complet și patronim, sau doar inițiale;
- există o anumită incertitudine cu privire la a doua literă „a” sau „o”;
- nu este clar cum exact angajatul este înregistrat mai întâi cu numele de familie, apoi prenumele și patronimul sau invers.
Komarov Alexander Ivanovich poate fi scris ca: A. Komarov, Komarov Alexander, K dar Marov Alexandru I. etc. opțiunea este suficientă.
Pentru a efectua o căutare nestrictă, ar trebui să utilizați metacaractere, cunoscute și sub denumirea de metacaractere. Fereastra de căutare în Excel acceptă două astfel de caractere: „*” și „?”:
- „*” se potrivește cu orice număr de caractere;
- "?" se potrivește cu orice caracter.
Aici apare imediat întrebarea, ce se întâmplă dacă trebuie să căutați un semn de întrebare sau un asterisc (cunoscut ca semn de înmulțire)? Totul este simplu, doar puneți o tilde „~” în fața personajelor pe care le căutați, respectiv, dacă trebuie să căutați o tilde, atunci trebuie să puneți două tilde.
Deci, pentru a efectua o căutare inexactă pentru numele de familie menționat mai sus, ar trebui să scrieți „K? Marov” în caseta de căutare și să faceți clic pe butonul „Găsiți tot”, în acest caz, vor fi afișate toate intrările care îndeplinesc condiția. în caseta de căutare, dar numai dacă opțiunea Whole cell.
Parametrul „Întrega celulă” înseamnă căutarea unui șir strict text introdus în câmpul de căutare și nimic mai mult, cu alte cuvinte, dacă există și inițiale pe lângă nume, atunci înregistrarea nu va fi găsită.
În același timp, dacă reconstruim ușor interogarea „*К?marov*”, atunci înregistrările vor fi găsite indiferent de parametrul „Întreaga celulă” care este activ.
Există câteva reguli importante de care trebuie să țineți cont atunci când căutați în Excel:
- Căutarea se efectuează în zona selectată, iar dacă nu există selecție, atunci pe întreaga foaie. Prin urmare, nu fi surprins dacă selectați din greșeală câteva celule goale, lansați caseta de căutare și nu găsiți nimic.
- Căutarea nu ține cont de formatare, prin urmare, nu trebuie adăugate simboluri valutare.
- Când lucrați cu date, este mai bine să le căutați în formatul implicit pentru un anumit sistem, în acest caz, Excel va găsi toate datele care îndeplinesc condiția. De exemplu, dacă sistemul dvs. folosește formatul d/m/y, atunci interogarea de căutare */12/2015 va afișa toate datele pentru decembrie 2015, indiferent de modul în care sunt formatate (28.12.2015, 28.12.2015). , sau 28 decembrie 2015 etc.).
În documentele Microsoft Excel care constau dintr-un număr mare de câmpuri, este adesea necesar să găsiți date specifice, un nume de rând și așa mai departe. 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 potrivită. 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 din Microsoft Excel oferă posibilitatea de a găsi textul sau valorile numerice dorite prin fereastra „Găsiți și înlocuiți”. În plus, aplicația are posibilitatea de căutare avansată a datelor.
Metoda 1: căutare simplă
O simplă căutare a datelor în Excel vă permite să găsiți toate celulele care conțin un set de caractere introduse în caseta de căutare (litere, cifre, cuvinte etc.) fără a ține seama de majuscule și minuscule.
Metoda 2: Căutați într-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.
Metoda 3: Căutare avansată
După cum am menționat mai sus, în timpul unei căutări normale, absolut toate celulele care conțin un set consecutiv de caractere de căutare sub orice formă, indiferent de caz, intră în rezultatele problemei.
În plus, nu numai conținutul unei anumite celule poate intra în ieșire, ci și adresa elementului la care se referă. De exemplu, celula E2 conține o formulă care este suma celulelor A4 și C3. Această sumă este 10 și acesta este numărul afișat în celula E2. Dar, dacă setăm numărul „4” în căutare, atunci aceeași celulă E2 se va afla printre rezultatele de ieșire. Cum se poate întâmpla? Doar că celula E2 conține adresa către celula A4 ca formulă, care include doar numărul dorit 4.
Dar cum să tăiați astfel și alte rezultate de căutare evident inacceptabile? Exact în aceste scopuri există o căutare avansată Excel.
- După deschiderea ferestrei „Găsiți și înlocuiți”în oricare dintre modurile de mai sus, faceți clic pe butonul "Parametrii".
- Î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 într-o căutare normală, dar puteți face ajustări dacă este necesar.
Implicit, funcții "caz sensibil"Și „Celule întregi” sunt dezactivate, dar dacă bifam casetele de lângă articolele corespunzătoare, atunci în acest caz, la generarea rezultatului, se va lua în considerare cazul introdus și potrivirea exactă. Dacă introduceți un cuvânt cu o literă mică, atunci în rezultatele căutării, celulele care conțin ortografia acestui cuvânt cu o literă majusculă, așa cum ar fi implicit, nu vor mai fi incluse. În plus, dacă funcția este activată „Celule întregi”, atunci numai elementele care conțin numele exact vor fi adăugate la ieșire. De exemplu, dacă setați interogarea de căutare „Nikolaev”, atunci celulele care conțin textul „Nikolaev A.D.” nu vor fi adăugate la rezultatele căutării.
În mod implicit, căutarea se efectuează numai pe foaia Excel activă. Dar dacă parametrul "Căutare" te vei muta pe poziție "In carte", atunci căutarea se va efectua pe toate foile fișierului deschis.
În parametru "Naviga" puteți schimba direcția de căutare. În mod implicit, după cum am menționat mai sus, căutarea se efectuează în ordine, rând cu rând. Prin deplasarea comutatorului în poziție "pe coloane", puteți seta ordinea în care sunt generate rezultatele de ieșire, începând din prima coloană.
În grafic „Zona de căutare” determină ce elemente particulare 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, și 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ă rearanjați comutatorul din poziție "Formulele"în poziție „Valori”. În plus, este posibilă căutarea după note. În acest caz, comutatorul este mutat în poziția „Note”.
Puteți specifica căutarea și mai precis făcând clic pe butonul "Format".
Aceasta deschide fereastra Format Cells. Aici puteți seta formatul celulelor care vor participa la căutare. Puteți seta restricții privind formatul numerelor, aliniere, font, chenar, umbrire și protecție, una dintre aceste opțiuni 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 o unealtă sub formă de pipetă. 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 Bine.
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 semnul „*”. Acum rezultatele căutării vor afișa toate celulele în care se află aceste cuvinte în orice ordine.
- După ce setările de căutare sunt setate, faceți clic pe butonul "Găsiți toate" sau "Găsește următorul" pentru a merge la rezultatele căutării.
După cum puteți vedea, programul 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, există posibilitatea de a configura o căutare individuală cu un număr mare de parametri diferiți și setări suplimentare.
