Cum să găsești un număr într-un tabel Excel. Manual pentru rezolvarea problemelor tastate în Microsoft Excel

Bună ziua, dragi locuitori Habro!

Din când în când, unii dintre noi (și poate mai mulți) dintre noi trebuie să se ocupe de sarcini de prelucrare a unor cantități mici de date, de la compilarea și analiza unui buget gospodăresc până la orice calcul pentru muncă, studiu etc. Poate cel mai potrivit instrument pentru aceasta este Microsoft Excel(sau poate alți analogi, dar sunt mai puțin frecvente).

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 rapid dând 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ă).

Cazul_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ă expresie logică adevărat
• Value_if_false - valoarea care este tipărită dacă expresia logică este falsă

=IF(C5>5, „Nu este necesară nicio comandă”, „Este necesară 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, totuși, 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, dar 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 „search_value”.
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 poate observa din formularele de tabel, 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. Sunt posibile următoarele tipuri de 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 rezultat, 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 în celulă, dar trebuie să fim atenți la rândul 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. În caz contrar, 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 „Luna”.
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 - o gamă 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 view_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. Text în partea de jos și majuscule considerat echivalent.
• 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ă „SUMA” ș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, o condiție este într-un tabel cu rezultatul, o matrice este într-un tabel cu date.
– î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
O nouă condiție a fost adăugată la formula de selecție a cookie-urilor:

(($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, iată că 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!

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 Excel 2007)

Nota: Complementul Lookup Wizard a fost întrerupt în Excel 2010. Această funcționalitate a fost înlocuită de Function Wizard și de funcționalitatea disponibilă de căutare și referință (referință).

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

Avem un tabel care înregistrează volumele vânzărilor anumitor produse în luni diferite. Trebuie să găsiți date în tabel, iar criteriile de căutare vor fi titlurile de rând și de coloană. Dar căutarea trebuie făcută separat după intervalul 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ă, ilustrăm un exemplu pe un tabel schematic care corespunde condițiilor descrise mai sus.

Foaie cu un tabel pentru căutarea valorilor pe verticală și pe orizontală:

Deasupra tabelului în sine există o linie cu rezultatele. În celula B1 introducem criteriul pentru interogarea de căutare, adică titlul 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 căutarea lunii corespunzătoare.

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

Acum aflăm în ce volum maxim și în ce lună a existat o vânzare maximă a Produsului 4.

Pentru a căuta după coloane:

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

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

Primul argument al funcției VLOOKUP (Vizualizare verticală) specifică o legătură către celula în care se află criteriul 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 VL trebuie să indice numărul coloanei din care ar trebui luată valoarea față de rândul numit 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ă din rândul Produs 4 sunt stocate în memorie (și anume: 360; 958; 201; 605; 462; 832). După aceea, funcția MAX poate lua numai numărul maxim din această matrice și îl poate returna ca valoare pentru celula D1, ca rezultat al calculării formulei.

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

Cum să obțineți antetele de coloană bazate pe începutul unei celule?

De exemplu, cât de eficient am afișat luna în care a existat o vânzare 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 este: VLOOKUP(B1,A5:G14,COLUMN(B5:G14),0). Am înlocuit funcția MAX cu MATCH, care folosește ca prim argument valoarea obținută prin formula anterioară. Acum acționează ca un criteriu pentru căutarea lunii. Iar ca urmare, funcția SEARCH ne returnează numărul coloanei 2, unde se află valoarea maximă a volumului vânzărilor pentru produsul 4 După care se include în lucrare funcția INDEX, care returnează valoarea după dată și numărul 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 trebuie doar să folosim 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 opțiune pentru sarcină ar fi să căutați în tabel folosind numele lunii ca criteriu. În astfel de cazuri, trebuie să schimbăm scheletul formulei noastre: înlocuiți funcția VLOOKUP cu HLOOKUP și înlocuiți funcția COLUMN cu ROW.

Acest lucru ne va permite să aflăm ce volum și ce produs au fost vânzările maxime într-o anumită lună.

Pentru a afla ce produs a avut volumul maxim 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 HLOO (Horizontal View), indicăm un link către celula cu criteriul de căutare. Al doilea argument specifică o referință la intervalul de tabel care este vizualizat. Al treilea argument este generat de funcția ROW, care creează o matrice de 10 elemente de numere de linii în memorie. Deoarece avem 10 rânduri în partea tabulară.

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

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

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 Excel 2007)

Nota: Complementul Lookup Wizard a fost întrerupt în Excel 2010. Această funcționalitate a fost înlocuită de Function Wizard și de funcționalitatea disponibilă de căutare și referință (referință).

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

Este destul de dificil să descoperi informațiile de care ai nevoie într-o foaie de lucru cu multe date. Cu toate acestea, caseta de dialog Găsiți și înlocuiți face găsirea de informații mult mai ușoară. În plus, are câteva caracteristici utile de care mulți utilizatori nu sunt conștienți.

Rulați comanda Acasă Editare Găsiți și evidențiați Găsiți(sau faceți clic Ctrl+F) pentru a deschide caseta de dialog Găsiți și înlocuiți. Dacă trebuie să înlocuiți datele, selectați comanda Acasă Editare Găsiți și selectați Înlocuire(sau faceți clic Ctrl+H). Comanda exactă pe care o executați determină pe care dintre cele două file se deschide caseta de dialog.

Dacă în caseta de dialog care se deschide Găsiși apăsați butonul pentru a înlocui Opțiuni, apoi vor fi afișate opțiuni suplimentare de căutare a informațiilor (Fig. 21.1).

În multe cazuri, este mai bine să specificați în căutare un text aproximativ, mai degrabă decât exact, de exemplu suporturi de chei de perete. De exemplu, pentru a găsi date despre clientul Ivan Smirnov, puteți, desigur, să introduceți textul exact în câmpul de căutare. Cu toate acestea, este posibil să nu găsiți nimic, deoarece este posibil să fi introdus numele clientului altfel, cum ar fi Ivan Smirnov sau I. Smirnov, sau chiar să fi greșit numele de familie. Când căutați un astfel de nume, cel mai bine este să utilizați metacaracterele.

Intră iv*zâmb*în câmp Găsiși apoi faceți clic pe butonul Găsiți toate. Folosirea metacarelor nu numai că reduce numărul de cuvinte pe care le introduceți, dar vă asigură și că găsiți datele clienților dacă acestea există pe foaia de lucru. Desigur, rezultatele căutării pot conține intrări care nu corespund scopului căutării dvs., dar acest lucru este mai bine decât nimic.

Când căutați folosind caseta de dialog Găsiți și înlocuiți pot fi utilizate două caractere metalice:

• ? - se potrivește cu orice personaj;
• * - se potrivește cu orice număr de caractere.

În plus, aceste caractere wildcard pot fi folosite și la căutarea valorilor numerice. De exemplu, dacă specificați în bara de căutare 3* , rezultatul va afișa toate celulele care conțin o valoare care începe cu 3, iar dacă introduceți 1?9, veți obține toate intrările din trei cifre care încep cu 1 și se termină cu 9.

Pentru a căuta un semn de întrebare sau un asterisc, precedați-l cu un tilde (~).
De exemplu, următorul șir de căutare găsește textul *NONE*: -*N0NE~*
Pentru a găsi caracterul tilde, puneți două tilde în bara de căutare.

Dacă credeți că căutarea dvs. nu funcționează corect, verificați următoarele trei setări (care se schimbă uneori de la sine).

• Caseta de selectare Cazul meciului- setați-o astfel încât litera textului căutat să se potrivească cu majusculele textului specificat. De exemplu, dacă introduceți cuvântul Ivan în căutare și bifați caseta specificată, cuvântul Ivan nu va apărea în rezultatele căutării.
• Caseta de selectare Întreaga celulă- setați-l pentru a găsi o celulă care conține exact textul care este specificat în bara de căutare. De exemplu, dacă tastați Excel în bara de căutare și bifați caseta, nu veți găsi o celulă care conține expresia Microsoft Excel.
• Lista drop-down Zona de căutare- lista conține trei articole: valori, formule și note. De exemplu, dacă introduceți numărul 900 în bara de căutare și în lista derulantă Zona de căutare Dacă selectați elementul cu valoare, nu veți vedea celula care conține valoarea 900 în rezultatele căutării dacă aceasta a fost obținută prin utilizarea formulei.

Rețineți că căutarea se efectuează în intervalul de celule selectat. Dacă trebuie să căutați în întreaga foaie, selectați o singură celulă înainte de a începe căutarea.

De asemenea, rețineți că folosind fereastra Găsiți și înlocuiți valorile numerice formatate nu pot fi găsite. De exemplu, dacă intri în bara de căutare $5* , o valoare care are aplicată formatarea monedei și care arată ca 54,00 USD nu va fi găsită.

Lucrul cu date poate fi o provocare, deoarece Excel acceptă atât de multe formate de date. Dacă căutați o dată care are aplicată formatare implicită, Excel găsește datele chiar dacă sunt formatate în moduri diferite. De exemplu, dacă sistemul dvs. utilizează formatul de dată l/d/y, șirul de căutare 10/*/2010 găsește toate datele din octombrie 2010, indiferent de modul în care sunt formatate.

Folosiți un câmp gol Înlocuiește cu pentru a elimina rapid unele informații dintr-o foaie de lucru. De exemplu, introduceți - * în câmp Găsiși părăsesc câmpul Înlocui pe gol. Apoi faceți clic pe butonul Înlocuiește-le pe toate pentru ca Excel să găsească și să elimine toate stelele din foaia de lucru.