Scopul principal al biroului programe Excel– efectuarea de calcule. Documentul acestui program (Cartea) poate conține multe foi cu tabele lungi pline cu numere, text sau formule. Automatizat căutare rapidă vă permite să găsiți celulele necesare în ele.

Căutare simplă

Pentru a căuta o valoare în Foaie de calcul Excel, trebuie să deschideți lista derulantă a instrumentului „Găsiți și înlocuiți” din fila „Acasă” și faceți clic pe elementul „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 „Găsiți și înlocuiți” care apare, trebuie să introduceți valoarea dorită și să faceți clic pe „Găsiți tot”.

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 „Găsiți tot” faceți clic pe „Găsiți următorul”, atunci prima celulă cu această valoare va fi căutată mai întâi, iar când dați clic din nou, va fi căutată a doua.

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

Dacă datele sau textul nu sunt căutate în întregul tabel Excel, atunci zona de căutare trebuie mai întâi selectată.

Căutare avansată

Să presupunem că doriți să găsiți toate valorile în intervalul de la 3000 la 3999. În acest caz, ați tasta 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 produs și unele neașteptate, evidențiate cu roșu. Ele sunt asociate cu prezența numărului 3 într-o celulă sau formulă.

Poți fi mulțumit de majoritatea rezultatelor obținute, ignorându-le pe cele incorecte. Dar funcția de căutare din Excel 2010 poate funcționa mult mai precis. Acest lucru se face folosind instrumentul Opțiuni din caseta de dialog.

Făcând clic pe „Opțiuni”, utilizatorul are posibilitatea de a efectua căutări avansate. În primul rând, să acordăm atenție elementului „Zona de căutare”, care în mod implicit este setat la „Formule”.

Aceasta înseamnă că căutarea a fost efectuată, inclusiv în acele celule în care nu există o valoare, ci o formulă. Prezența numărului 3 în ele a dat trei rezultate incorecte. Dacă selectați „Valori” ca domeniu de căutare, veți 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ă trebuie să selectați elementul „Toată celulă”. După aceasta, rezultatul căutării devine 100% precis.

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

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

Dacă în loc de „On Sheet” implicit, selectați „În registru de lucru”, atunci nu este necesar să fiți pe foaia de celule pe care o 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 „Vizualizare”, care are două semnificații. Valoarea implicită este „pe rânduri”, ceea ce înseamnă că celulele sunt scanate rând cu rând. Selectarea unei valori diferite – „pe coloane” – va schimba doar direcția de căutare și succesiunea rezultatelor.

La căutarea în documente Microsoft Excel, puteți folosi și un alt wildcard – „*”. Dacă 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 este necesar să se țină cont de cazul caracterelor la căutare. Dacă cuvântul louisiana este scris cu majuscule, rezultatele căutării nu se vor modifica. Dar dacă selectați „Potriviți majuscule” în fereastra de căutare avansată, căutarea nu va fi reușită. Programul va considera cuvintele Louisiana și Louisiana diferite și, firește, nu va găsi primul dintre ele.

Tipuri de căutare

Căutați potriviri

Uneori este necesar să detectaț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ă” din grupul „Stiluri”, deschideți „ Formatare condiționată" Apoi, selectați secvențial elementele „Reguli pentru evidențierea celulelor” și „Valori repetate”.

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 intervalul de la 3000 la 4000 în coloana B.

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

Au fost discutate diferite opțiuni de căutare folosind Excel 2010 ca exemplu Cum să căutați în Excel din alte versiuni? Există o diferență în trecerea la filtrare în versiunea 2003. În meniul „Date”, ar trebui să selectați secvențial comenzile „Filter”, „Auto Filter”, „Condition” și „Custom Auto Filter”.

Video: Căutați într-un tabel Excel

Este cu siguranță mai ușor să căutați într-un singur tabel, deși mare, întreg sau în intervale adiacente de celule, decât prin mai multe tabele împărțite în părți împrăștiate în diferite intervale neadiacente sau chiar în foi separate. Chiar dacă o faci căutare automată simultan pe mai multe tabele, pot apărea obstacole semnificative. Dar aranjarea tuturor datelor într-un singur tabel este dificilă, uneori practic imposibilă. Pe exemplu concret Vom demonstra soluția corectă pentru căutarea simultană în mai multe tabele în Excel.

Căutare simultană în mai multe intervale

Pentru un exemplu vizual, să creăm trei tabele separate simple situate în intervale neadiacente ale unei singure foi:

Ar trebui să căutați cantitatea necesară pentru a produce 20 de bucăți de produse. Din păcate, aceste date sunt în diferite coloane și rânduri. Prin urmare, în primul rând, trebuie să verificați cât timp va dura producerea acestor produse (primul tabel).

Pe baza datelor obținute, trebuie să treceți imediat la o căutare într-un alt tabel și să aflați câți muncitori ar trebui să fie implicați într-un anumit volum de producție. Rezultatul obținut trebuie comparat cu datele din al treilea tabel. Astfel, într-o singură operațiune de căutare în trei tabele vom determina imediat costurile necesare (suma).

Utilizatorul mediu de Excel ar căuta o soluție folosind funcții bazate pe formule, cum ar fi CĂUTARE V. Și ar efectua o căutare în 3 etape (separat pentru fiecare tabel). Se pare că puteți obține imediat un rezultat gata făcut prin efectuarea unei căutări într-o singură etapă folosind o formulă specială. Pentru a face acest lucru:

1. În celula E6, introduceți valoarea 20, care este condiția pentru interogarea de căutare.
2. În celula E7, introduceți următoarea formulă:

Cost de producție pentru 20 buc. un anumit produs.

Cum funcționează formula cu VLOOKUP în mai multe tabele:

Principiul de funcționare al acestei formule se bazează pe căutarea secvențială a tuturor argumentelor pentru funcția principală CĂUTARE V (prima). Mai întâi, a treia funcție CĂUTARE VL caută în primul tabel cantitatea de timp necesară pentru a produce 20 de bucăți din produs specificată ca valoare pentru celula E6 (care poate fi apoi modificată dacă este necesar). Apoi, a doua funcție CĂUTARE VĂ caută valoarea pentru primul argument al funcției principale.

Ca rezultat al căutării celei de-a treia funcție, obținem valoarea 125, care este primul argument pentru a doua funcție. După ce a primit toți parametrii, a doua funcție caută în cel de-al doilea tabel numărul de lucrători necesar pentru producție. Ca rezultat, este returnată valoarea 5, care va fi apoi utilizată de funcția principală. Pe baza tuturor datelor primite, formula returnează rezultatul final al calculului. Și anume, suma de 1.750 USD necesară pentru a produce 20 de bucăți dintr-un anumit produs.

Folosind acest principiu, puteți folosi formule pentru funcția CĂUTARE V din mai multe foi.

Bună ziua, dragi locuitori Habro!

Din când în când, unii (poate mai mulți) dintre noi ne confruntăm cu sarcina de a procesa cantități mici de date, de la compilare și analiză. bugetul caseiși terminând cu orice calcule pentru muncă, studiu etc. Poate cel mai potrivit instrument pentru aceasta este Microsoft Excel (sau poate alți analogi, dar sunt mai puțin obișnuiți).

Căutarea mi-a oferit un singur articol despre Habré pe un subiect similar - „Talmud folosind formule în Google SpreadSheet”. Oferă o descriere bună a lucrurilor de bază pentru lucrul în excel (deși nu este 100% despre excel în sine).

Astfel, după ce a acumulat un anumit pool de solicitări/sarcini, a apărut ideea de a le tipa și de a propune solutii posibile(deși nu tot posibil, dar dând rapid rezultate).

Vom vorbi despre rezolvarea celor mai frecvente probleme pe care le întâmpină utilizatorii.

Descrierea soluțiilor este structurată astfel: se oferă un caz care conține sarcina inițială, care se complică treptat, și se oferă o soluție detaliată cu explicații pentru fiecare pas. Numele funcțiilor vor fi date în limba rusă, dar numele original în limba rusă va fi dat între paranteze la prima mențiune. engleză(din moment ce, conform experienței, marea majoritate a utilizatorilor au instalată versiunea rusă).

Caz_1: Funcții logiceși funcții de potrivire
„Am un set de valori într-un tabel și este necesar ca atunci când este îndeplinită o anumită condiție/set de condiții, să fie afișată o anumită valoare” (c) Utilizator

Datele sunt de obicei prezentate sub formă de tabel:

Stare:

• dacă valoarea din coloana „Cantitate” este mai mare de 5,
• apoi trebuie să afișați valoarea „Nu este necesară nicio comandă” în coloana „Rezultat”,

Sintaxa formulei este următoarea:
IF(expresie_logică, [valoare_dacă_adevărat], [valoare_dacă_fals])

• Expresia logică este o expresie care se evaluează la TRUE sau FALSE.
• Value_if_true - valoarea care este tipărită dacă expresia logică este adevărată
• Value_if_false - valoarea care este tipărită dacă expresia logică este falsă

=IF(C5>5, „Nu este necesară nicio comandă”, „Se cere comandă”)

Se întâmplă ca condiția să fie mai complexă, de exemplu, îndeplinirea a 2 sau mai multe condiții:

• dacă valoarea din coloana „Cantitate” este mai mare de 5, iar valoarea din coloana „Tip” este „A”

• Valoare_booleană1-2 etc. - o condiție de testat, a cărei evaluare are ca rezultat valoarea TRUE sau FALSE

=DACĂ(ȘI(C2>5;B2=„A”),1,0)

Să încercăm să complicăm sarcina - o nouă condiție:

• dacă valoarea din coloana „Cantitate” este 10, iar valoarea din coloana „Tip” este „A”
• sau valoarea din coloana Cantitate este mai mare sau egală cu 5, iar valoarea Tip este B
• atunci trebuie să afișați valoarea „1” în coloana „Rezultat”, în caz contrar „0”.

=DACĂ(SAU(ȘI(C2=10;B2=„A”); ȘI(C2>=5,B2=„B”)),1,0)

Acum să trecem la următoarea situație:
Să ne imaginăm că, în funcție de valoarea din coloana „Condiție”, o anumită condiție ar trebui să fie afișată în coloana „Rezultat” de mai jos este corespondența dintre valori și rezultat;
Stare:

• 1 = A
• 2 = B
• 3 = B
• 4 = G

=DAC(A2=1, „A”, DACA(A2=2, „B”, DACA(A2=3, „C”, DACA(A2=4, „D”,0))))

După cum puteți vedea, scrierea unei astfel de formule nu numai că nu este foarte convenabilă și greoaie, dar poate dura ceva timp pentru ca un utilizator fără experiență să o editeze în cazul unei erori.
Dezavantajul acestei abordări este că este aplicabilă pentru un număr mic de condiții, deoarece toate acestea vor trebui introduse manual și formula noastră „umflată” la dimensiuni mari, dar abordarea se distinge prin „omnivoritatea” totală a valorilor. și versatilitate de utilizare.

Soluție alternativă_1:
Folosind formula ALEGE
Sintaxa funcției:
SELECT (număr_index, valoare1, [valoare2], ...)

• Număr_index - numărul argumentului valorii selectate. Numărul de index trebuie să fie un număr între 1 și 254, o formulă sau o referință la o celulă care conține un număr între 1 și 254.
• Valoare1, valoare2,... - o valoare de la 1 la 254 de argumente valori, din care funcția „SELECT”, folosind numărul de index, selectează valoarea sau acțiunea de efectuat. Argumentele pot fi numere, referințe de celule, nume specifice, formule, funcții sau text.

• 1 = A
• 2 = B
• 3 = B
• 4 = G

= ALEGEREA(A2, „A”, „B”, „C”, „D”)

• 1 = A
• 2 = B
• 3 = B
• 5 = G

=ALEGERE(A31, „A”, „B”, „C”, „D”)

Soluție alternativă_2:
Aici ajungem la una dintre cele mai populare Funcții Excel, a cărui stăpânire transformă automat orice angajat de birou într-un „utilizator Excel cu experiență” /sarcasm/.
Sintaxa formulei:
CĂUTARE V(valoare_căutare, tabel, număr_coloană, [căutare_interval])

• Search_value – valoarea care este căutată de funcție.
• Un tabel este o serie de celule care conțin date. În aceste celule va avea loc căutarea. Valorile pot fi text, numerice sau booleene.
• Număr_coloană - numărul coloanei din argumentul „Tabel” din care va fi derivată valoarea dacă există o potrivire. Este important să înțelegeți că coloanele sunt numărate nu de-a lungul grilei generale a foii (A.B,C,D etc.), ci în interiorul matricei specificate în argumentul „Tabel”.
• Interval_lookup - determină dacă funcția ar trebui să găsească o potrivire exactă sau o potrivire aproximativă.

Apoi, în partea de lucru a tabelului, se notează o formulă cu un link către cartea de referință completată mai devreme. Aceste. în director, în coloana „D” se caută valoarea din coloana „A” iar când se găsește o potrivire, în coloana „B” este afișată valoarea din coloana „E”.
Sintaxa formulei:
Ieșirea rezultatului în celula B2:

Acum imaginați-vă o situație în care trebuie să extrageți date într-un tabel din altul, iar tabelele nu sunt identice. Vezi exemplul de mai jos

Se poate observa că rândurile din coloanele „Produs” ale ambelor tabele nu se potrivesc, totuși, acest lucru nu este un obstacol în calea utilizării funcției „CĂUTARE V”.
Ieșirea rezultatului în celula B2:

• O referință este o celulă sau un interval de celule pentru care doriți să returnați numărul coloanei.

=CĂUTAREV($A3,$H$3:$M$6, COLUMN(),0)

Continuăm să dezvoltăm subiectul și să complicăm condiția: imaginați-vă că avem două directoare cu date diferite despre produse și trebuie să afișăm valorile în tabel cu rezultatul în funcție de ce tip de director este indicat în „Director” coloană
Stare:

• Dacă numărul 1 este indicat în coloana „Directory”, datele trebuie extrase din tabelul „Directory_1”, dacă numărul este 2, apoi din tabelul „Directory_2” în conformitate cu luna specificată

Soluția care îmi vine imediat în minte este următoarea:

=DACĂ($B3=1; CĂUTARE V.($A3,$G$3:$I$6; COLONĂ()-1,0); CĂUTARE V($A3, $K$3:$M$6; COLONAN()-1;0 ))

=CĂUTAREV($A3, SELECTARE($B3,$G$3:$I$6,$K$3:$M$6), COLUMN()-1,0)

Bonus: CĂUTARE V. bazată pe două sau mai multe caracteristici din argumentul „valoare_căutare”.
Stare:

• Să ne imaginăm că, ca întotdeauna, avem o matrice de date în formă tabelară (dacă nu, atunci aducem datele la ea), trebuie să obținem valori din matrice pe baza anumitor caracteristici și să le plasăm într-o altă formă tabelară .

După cum se vede din forme tabulare, fiecare articol nu are doar un nume (care nu este unic), ci aparține și unei clase specifice și are propria opțiune de ambalare.
Folosind o combinație de nume și clasă și ambalaj, putem crea o nouă caracteristică pentru aceasta, în tabelul cu datele creăm o coloană suplimentară „Caracteristică suplimentară”, pe care o completăm folosind următoarea formulă:

=CONCATENATE(H3;"_";I3;"_";J3)

=DACĂ EROARE(CĂUTARE V(A2&"_"&B2&"_"$G$2:$K$6,5,0),0)

Această tehnică poate fi utilizată pentru un număr mai mare de caracteristici, singura condiție este unicitatea combinațiilor rezultate, dacă aceasta nu este îndeplinită, rezultatul va fi incorect;

Caz_3 Căutarea unei valori într-o matrice sau când CĂUTARE V nu ne poate ajuta

Să luăm în considerare o situație în care trebuie să înțelegem dacă matricea de celule conține valorile de care avem nevoie.
Sarcină:

• coloana „Condiție de căutare” conține o valoare și trebuie să determinați dacă aceasta este prezentă în coloana „Matrice de căutare”

După cum putem vedea, funcția „CĂUTARE V” este neputincioasă aici, deoarece Nu căutăm o potrivire exactă, ci mai degrabă prezența valorii de care avem nevoie în celulă.
Pentru a rezolva problema este necesar să folosiți o combinație de mai multe funcții, și anume:
"DACĂ"
„DACA EROARE”
„JOS”
"GĂSI"

În ordine despre toată lumea, am discutat deja despre „DAC” mai devreme, așa că să trecem la funcția „IFEROARE”.

IFERROR(valoare, eroare_valoare)

• Valoarea este argumentul care este verificat pentru erori.
• Value_on_error - valoarea returnată dacă există o eroare la calcularea formulei. Posibil următoarele tipuri erori: #N/A, #VALOARE!, #REF!, #DIV/0!, #NUMĂR!, #NUME? și #GOL!.

„JOS”

• Text - text convertit în minuscule.

Acum să aruncăm o privire mai atentă la sintaxa funcției FIND.

FIND(text_căutare, text_vizual, [poziție_început])

• Search_text - textul care trebuie găsit.
• Search_text - textul în care doriți să găsiți textul căutat.
• Start_position - semnul de la care se începe căutarea. Primul caracter din textul „view_text” este numerotat cu 1. Dacă numărul nu este specificat, acesta este implicit 1.

=IF(IFEROARE(FIND(LINE(A2), LINE(E2),1),0)=0,„erec”, „bingo!”)

1. LOWER(A2) – convertește argumentul Search_Text din celula A2 în text cu minuscule
2. Funcția FIND începe să caute argumentul convertit Search_Text în matricea Search_Text, care este convertit de funcția LOWER(E2), tot în text cu minuscule.
3. Dacă funcția găsește o potrivire, de ex. returnează numărul de serie al primului caracter al cuvântului/valorii care se potrivește, este declanșată condiția TRUE din formula „DAC”, deoarece valoarea rezultată nu este zero. Ca rezultat, coloana „Rezultat” va afișa valoarea „Bingo!”
4. Dacă, totuși, funcția nu găsește o potrivire, i.e. nu este indicat numărul de serie al primului caracter al cuvântului/valorii potrivite și se returnează o eroare în locul valorii, se declanșează condiția inclusă în formula „IFEROARE” și se returnează o valoare egală cu „0”, care corespunde la condiția FALS din formula „DAC”, deoarece valoarea rezultată este „0”. Ca urmare, valoarea „eșuat” va fi afișată în coloana „Rezultat”.

După cum se poate observa din figura de mai sus, datorită funcțiilor „LOW” și „FIND”, găsim valorile dorite indiferent de cazul caracterelor și locația din celulă, dar trebuie să fim atenți la linia 5 .
Termenul de căutare este setat la „111”, dar matricea de căutare conține valoarea „1111111 cookie-uri”, dar formula returnează rezultatul „Bingo!” Acest lucru se întâmplă deoarece valoarea „111” este inclusă în seria de valori „1111111”, ca rezultat, se găsește o potrivire. Altfel această condiție nu va funcționa.

Caz_4 Căutarea unei valori într-o matrice bazată pe mai multe condiții sau când CUȚIUNEA V este și mai incapabil să ne ajute

Să ne imaginăm o situație în care trebuie să găsiți o valoare din „Tabelul cu rezultatul”. matrice bidimensională„Director” pentru mai multe condiții, și anume valoarea „Nume” și „Lună”.
Forma tabelară a sarcinii va arăta astfel:

Stare:

• Este necesar să trageți datele în tabel cu rezultatul în conformitate cu coincidența condițiilor „Nume” și „Luna”.

Sintaxa funcției INDEX

INDEX(matrice, număr_rând, [număr_coloană])

• Matrice - un interval de celule din care vor fi afișate valorile dacă condițiile lor de căutare se potrivesc.
• Dacă tabloul conține doar un rând sau o coloană, argumentul row_number sau, respectiv, column_number, este opțional.
• Dacă tabloul ocupă mai mult de un rând și o coloană și este dat doar unul dintre argumentele row_number și column_number, atunci funcția INDEX returnează un tablou format din întregul rând sau întreaga coloană a argumentului matricei.
• Line_number - numărul liniei din matrice din care doriți să returnați o valoare.
• column_number - numărul coloanei din matrice din care doriți să returnați o valoare.

Sintaxa funcției MATCH

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

• Lookup_value este valoarea care se potrivește cu valorile din argumentul lookup_array. Argumentul lookup_value poate fi o valoare (număr, text sau boolean) sau o referință la o celulă care conține o astfel de valoare.
• Looked_array - intervalul de celule în care se efectuează căutarea.
• match_type este un argument opțional. Numărul este -1, 0 sau 1.

Parte a formulei:

MECI ($A4,$I$4:$I$7,0)

MECI (B$3,$J$3:$L$3,0)

Astfel, am obținut coordonatele punctului (1; #N/A) pe care funcția „INDEX” le folosește pentru a căuta în argumentul „Matrice”.
Funcția scrisă complet pentru celula B4 ar arăta astfel:

=INDEX($J$4:$L$7, MATCH($A4,$I$4:$I$7,0), MATCH(B$3,$J$3:$L$3,0))

=INDEX($J$4:$L$7,1,#N/A))

Pentru a neutraliza acest efect negativ, folosim funcția „IFEROARE”, despre care am citit mai devreme, și înlocuim valoarea returnată în cazul unei erori cu „0”, apoi formula va arăta astfel:

=DACĂ EROARE(INDEX($J$4:$L$7, MATCH($A4,$I$4:$I$7,0), MATCH(B$3,$J$3:$L$3,0)),0)

După cum puteți vedea în imagine, valorile „#N/A” nu mai interferează cu calculele noastre ulterioare folosind valorile din tabelul cu rezultate.

Case_5 Găsirea unei valori într-un interval de numere

Să ne imaginăm că trebuie să dăm un anumit semn numerelor incluse într-un anumit interval.
Stare:
În funcție de costul produsului, ar trebui să i se atribuie o anumită categorie
Dacă valoarea este în interval

• De la 0 la 1000 = A
• De la 1001 la 1500 = B
• Din 1501 până în 2000 = B
• Din 2001 până în 2500 = G
• Mai mult de 2501 = D

Funcția LOOKUP returnează o valoare dintr-un rând, coloană sau matrice. Funcția are două forme sintactice: formă vectorială și formă matrice.

LOOKUP(lookup_value, lookup_vector, [result_vector])

• lookup_value este valoarea pe care funcția LOOKUP o caută în primul vector. Lookup_value poate fi o referință de număr, text, boolean, nume sau valoare.
• Watch_vector este un interval format dintr-un rând sau o coloană. Valorile din argumentul lookup_vector pot fi text, numere sau boolean.
• Valorile din argumentul lookup_vector trebuie să fie în ordine crescătoare: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; în caz contrar, funcția LOOKUP poate returna un rezultat incorect. Textul cu litere mici și majuscule sunt considerate echivalente.
• result_vector este un interval format dintr-un rând sau coloană. Rezultatul_vector trebuie să aibă aceeași dimensiune ca și lookup_vector.

=VIZUALIZARE(E3,$A$3:$A$7,$B$3:$B$7)

Argumentele „View_vector” și „Result_vector” pot fi scrise sub formă de matrice - în acest caz, nu trebuie să le afișați într-un tabel separat pe o foaie Excel.
În acest caz, funcția va arăta astfel:
Ieșirea rezultatului în celula B3:
=VIZUALIZARE(E3;(0;1001;1501;2001;2501);("A","B","C","D","D"))

Cazul_6 Însumarea numerelor după caracteristici

Pentru a suma numerele pe baza anumitor caracteristici, puteți utiliza trei funcții diferite:
SUMIF – sume doar cu un singur atribut
SUMIFS – sume pe mai multe caracteristici
SUMPRODUS – însumează mai multe caracteristici
Există, de asemenea, o opțiune care utilizează „SUM” și funcția de formulă matrice, atunci când formula „SUMA” este ridicată la o matrice:
((=SUMA(()*()))
dar această abordare este destul de incomodă și este complet acoperită de funcționalitate de formula „SUMPRODUCT”.
Acum pentru mai multe detalii despre sintaxa „SUMPRODUCT”:

SUMPRODUCT(matrice1, [matrice2], [matrice3],...)

• Array1 este primul tablou ale cărui componente trebuie înmulțite și apoi adăugate rezultatele.
• Array2, array3... - de la 2 la 255 de matrice, ale căror componente trebuie înmulțite și apoi adăugate rezultatele.

• Găsiți costul total al livrărilor pentru fiecare produs pentru o anumită perioadă:

După cum se poate observa din tabelul cu date, pentru a calcula costul, este necesar să se înmulțească prețul cu cantitatea și să se transfere valoarea rezultată, aplicând condițiile de selecție, în tabelul cu rezultat.
Cu toate acestea, formula SUMPROIZ permite efectuarea unor astfel de calcule în cadrul formulei.
Ieșirea rezultatului în celula B4:

=SUMAPRODUS(($A4=$H$3:$H$11)*($K$3:$K$11>=B$3)*($K$3:$K$11
Să ne uităm la formula în părți:
– setați condiția de selecție în coloana „Nume” a tabelului cu date în coloana „Nume” din tabel cu rezultatul

($K$3:$K$11>=B$3)*($K$3:$K$11 – stabilim o condiție în funcție de intervalul de timp, data este mai mare sau egală cu prima zi a lunii curente, dar mai mică decât prima zi a lunii următoare. În mod similar, condiția este în tabelul cu rezultatul, matricea este în tabelul cu datele.
– înmulțiți coloanele „Cantitate” și „Preț” din tabel cu datele.
Avantajul incontestabil al acestei funcții este ordinea liberă a condițiilor de înregistrare, acestea pot fi scrise în orice ordine, acest lucru nu va afecta rezultatul;
Rezultat:

Acum să complicăm condiția și să adăugăm cerința ca selecția pentru numele „cookies” să aibă loc numai în clasele „mic” și „mari”, iar pentru numele „rulouri” totul, cu excepția clasei „cu gem”:

Ieșirea rezultatului în celula B4:

=SUMPRODUS(($A4=$H$3:$H$11)*($J$3:$J$11>=B$3)*($J$3:$J$11
La formula de selectare a cookie-urilor a fost adăugată o nouă condiție:

(($I$3:$I$11=„mic”)+($I$3:$I$11=“mari”)

– după cum puteți vedea, două sau mai multe condiții dintr-o coloană sunt separate într-un grup separat folosind simbolul „+” și încadrând condițiile între paranteze suplimentare.
O nouă condiție a fost adăugată la formula de selecție prin role:

=SUMAPRODUS(($A5=$H$3:$H$11)*($J$3:$J$11>=B$3)*($J$3:$J$11 „cu gem”);($L$3:$L$11)*($K$3:$K$11))

Acest:

($I$3:$I$11<>"cu dulceata")

– de fapt, în această formulă a fost posibil să se scrie o condiție de selecție la fel ca la selectarea prin cookie-uri, dar apoi, ar trebui să enumerați trei condiții în formulă, în acest caz, este mai ușor să scrieți o excepție - nu este egal cu „cu gem” pentru aceasta folosim valoarea „<>».
În general, dacă grupurile de caracteristici/clase sunt cunoscute în avans, atunci este mai bine să le combinați în aceste grupuri, creând cărți de referință, decât să scrieți toate condițiile într-o funcție, umflarea acesteia.
Rezultat:

Ei bine, aici ajungem la sfârșitul scurtului nostru manual, care de fapt ar putea fi mult mai lung, dar scopul era totuși să ofere o soluție la cele mai frecvente situații și nu să descriem soluția la cazuri particulare (dar mult mai interesante). ).
Sper că manualul va ajuta pe cineva în rezolvarea problemelor folosind Excel, pentru că asta va însemna că munca mea nu a fost în zadar!

Mulțumesc pentru timpul acordat!

ÎN documente Microsoft Excel, care constă dintr-un număr mare de câmpuri, trebuie adesea să găsească anumite date, 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 potrivită. Căutarea încorporată Microsoft Excel vă va ajuta să economisiți timp și nervi. Să ne dăm seama cum funcționează și cum să-l folosim.

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 oferă capabilități avansate de căutare 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 setul de caractere introduse în fereastra de căutare (litere, cifre, cuvinte etc.) fără a ține cont de majuscule.

Metoda 2: Căutați după intervalul de celule specificat

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 sa menționat mai sus, în timpul unei căutări normale, rezultatele căutării includ absolut toate celulele care conțin un set secvenţial de caractere de căutare sub orice formă, indiferent de caz.

În plus, în rezultate pot fi incluse nu numai conținutul unei anumite celule, 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 este acest număr care apare în celula E2. Dar, dacă introducem numărul „4” în căutare, atunci aceeași celulă E2 va fi printre rezultate. Cum s-ar putea întâmpla asta? Doar că celula E2 conține ca formulă adresa pentru celula A4, care include doar numărul dorit 4.

Dar cum putem întrerupe aceste și alte rezultate de căutare evident inacceptabile? În aceste scopuri există căutarea avansată Excel.

1. După deschiderea ferestrei „Găsiți și înlocuiți” folosind orice metodă descrisă 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.

   

   Implicit, funcții "Majuscule de potrivire"Ş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 celulele care conțin ortografia acestui cuvânt cu majusculă nu vor mai apărea în rezultatele căutării, așa cum ar fi cazul implicit. În plus, dacă funcția este activată „Celule întregi”, atunci numai elementele care conțin numele exact vor fi adăugate la rezultate. 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 transfera pe poziție „În carte”, apoi căutarea va fi efectuată pe toate foile fișierului deschis.

   

   În parametru "Răsfoiește" puteți schimba direcția de căutare. În mod implicit, după cum s-a menționat mai sus, căutarea este efectuată în ordine linie cu linie. Mutarea comutatorului în poziție „După coloane”, puteți seta ordinea în care sunt generate rezultatele căutării, începând din prima coloană.

   

   În coloană „Zona de căutare” se determină 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. Acesta 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 în funcție de rezultate, în funcție de datele care sunt afișate în celulă, și nu în bara de formule, trebuie să mutați comutatorul din poziție "Formulele"în poziție "Valori". În plus, este posibilă căutarea după note. În acest caz, mutați comutatorul în poziție „Note”.

   

   Puteți specifica o căutare ș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 după formatul numărului, aliniere, font, chenar, umplere și protecție, oricare dintre acestea 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ă aceasta, apare un instrument sub forma unei pipete. Folosind-o, 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 după o anumită expresie, ci să găsiți celule în care cuvintele de căutare sunt situate î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 care conțin aceste cuvinte în orice ordine.



3. După ce setările de căutare sunt setate, faceți clic pe butonul „Găsiți totul” sau „Găsiți următorul” 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 un scârțâit simplu, trebuie doar să apelați fereastra de căutare, să introduceți o interogare în ea și să apăsați butonul. Dar, în același timp, este posibil să configurați o căutare individuală cu un număr mare de parametri diferiți și setări suplimentare.

Să presupunem că trebuie să găsiți extensia de telefon a unui angajat folosind numărul său și, de asemenea, să estimați corect raportul comisionului pentru suma vânzărilor. Căutați date pentru a găsi rapid și eficient date specifice într-o listă și verificați automat dacă datele sunt utilizate corect. După ce vizualizați datele, puteți efectua calcule și afișa rezultatele, indicând valorile returnate. Există mai multe moduri de a căuta valori într-o listă de date și de a afișa rezultatele.

În acest articol

Găsiți valori într-o listă pe verticală după potrivirea exactă

Puteți utiliza funcția CĂUTARE V sau o combinație a funcțiilor INDEX și MATCH pentru a efectua această sarcină.

Exemple de utilizare a funcției CĂUTARE V

Funcția VLOOKUP.

Exemple de INDEX-uri și MATCHES

Ce înseamnă:

=INDEX(trebuie să returnați valoarea din C2:C10, care se va potrivi cu MATCH(prima valoare „Varză” din tabloul B2:B10))

Formula caută prima valoare în celula C2:C10 corespunzătoare varză(în B7) și returnează valoarea în C7 ( 100 ) - prima valoare corespunzătoare varză.

Pentru mai multe informații, consultați funcția INDEX și funcția MATCH.

Găsiți valori într-o listă pe verticală, după potrivire aproximativă

Pentru a face acest lucru, utilizați funcția VLOOKUP.

Important: Asigurați-vă că valorile din primul rând sunt sortate în ordine crescătoare.

În exemplul de mai sus, funcția VLOOKUP caută numele unui elev care are 6 întârzieri în intervalul A2:B7. Nu există nicio intrare în tabel pentru 6 întârzieri, astfel încât funcția CĂUTARE VL caută următoarea potrivire cea mai mare sub 6 și găsește valoarea 5 asociată prenumelui Dave, și, prin urmare, se întoarce Dave.

Pentru mai multe informații, consultați funcția VLOOKUP.

Găsirea valorilor verticale într-o listă de dimensiuni necunoscute cu potrivire exactă

Pentru a efectua această sarcină, utilizați funcțiile OFFSET și MATCH.

Nota: Această abordare este utilizată dacă datele se află într-un interval de date extern pe care îl actualizați în fiecare zi. Știți că coloana B are Preț, dar nu știți câte rânduri de date returnează serverul, iar prima coloană nu este sortată alfabetic.

C1 este celula din stânga sus a intervalului (numită și celula de pornire).

Potrivire(„portocale”; C2: C7; 0) caută culoarea portocalie în intervalul C2:C7. Nu ar trebui să includeți celula de pornire în interval.

1 - numărul de coloane din dreapta celulei de pornire pentru care trebuie returnată valoarea returnată. În exemplul nostru, valoarea returnată este în coloana D, Vânzări.

Găsiți valori într-o listă orizontal după potrivire exactă

Pentru a efectua această sarcină, este utilizată funcția GLOOKUP. Mai jos este un exemplu.

Funcția LOOKUP caută o coloană Vânzăriși returnează valoarea de la rândul 5 în intervalul specificat.

Pentru mai multe informații, consultați funcțiile LOOKUP.

Creați o formulă de căutare folosind Expertul de căutare (numai pentru Excel 2007)

Nota: Add-in-ul Lookup Wizard a fost întrerupt în Excel 2010. Această funcționalitate a fost înlocuită de Function Wizard și funcțiile de căutare și referință (referință) disponibile.

În Excel 2007, Expertul de căutare creează o formulă de căutare bazată pe datele din foaia de lucru care au titluri de rând și coloană. Asistentul de căutare vă ajută să găsiți alte valori într-un rând atunci când cunoașteți valoarea dintr-o coloană și invers. Expertul de căutare folosește index și MATCH în formulele pe care le creează.