Avem un tabel în care volumele de vânzări ale anumitor mărfuri sunt înregistrate în diferite luni. Este necesar în tabel pentru a găsi datele, iar criteriul de căutare vor fi titluri de rânduri și coloane. Dar căutarea trebuie efectuată separat de intervalul de linie sau coloană. Acesta este, va fi utilizat doar unul dintre criterii. Prin urmare, funcția Index nu poate fi aplicată aici și este necesară o formulă specială.

Căutați valori în tabelul Excel

Pentru a rezolva această problemă, ilustrează un exemplu pe o masă schematică, care corespunde mai sus condițiilor descrise.

Foaie cu o masă pentru a căuta valori vertical și orizontale:

Deasupra tabelului în sine este un șir cu rezultate. În celula B1 există un criteriu pentru o interogare de căutare, adică un antet de coloană sau un nume de șir. Și în celula D1, formula de căutare trebuie să returneze rezultatul calculării valorii corespunzătoare. După aceasta, a doua formulă va funcționa în celula F1, care va folosi deja valorile celulelor B1 și D1 ca criterii de căutare a lunii relevante.

Valorile căutării în șirul Excel

Acum aflăm ce volum maxim și în ce lună a fost vânzarea maximă a mărfurilor 4.

Pentru a căuta coloane urmează:

Găsită în ce lună și care a fost cea mai mare vânzare de bunuri 4 pentru două sferturi.

Principiul acțiunii Valoarea formulei de căutare în linia Excel:

În primul argument, funcția PRD (Vizualizare verticală) indică o legătură cu o celulă în care sunt criteriile de căutare. Al doilea argument indică gama de celule pentru vizualizare în timpul procesului de căutare. În al treilea argument, funcția PRD trebuie indicată de numărul coloanei din care trebuie luată valoarea împotriva rândului cu numele bunurilor 4. Dar, deoarece nu cunoaștem acest număr în avans. Folosim funcția coloanei. Creați o serie de numere de coloane pentru gama B4: G15.

Acest lucru permite funcțiile VDC să colecteze o întreagă gamă de valori. Ca rezultat, toate valorile corespunzătoare ale fiecărei coloane de pe elementul 4 sunt stocate în memorie (și anume: 360; 958; 201; 605; 462; 832). După aceasta, funcțiile maxime rămân doar pentru a lua un număr maxim din această matrice și pentru a reveni ca o valoare pentru o celulă D1, ca rezultat al calculului formulei.

După cum puteți vedea designul formulei simplă și laconic. La baza sa, este posibil într-un mod similar de a găsi pentru un anumit produs și alți indicatori. De exemplu, valoarea minimă sau medie a vânzărilor utilizează pentru această funcție mine sau CPNAV. Nu vă împiedicați ca acest schelet cu formula să fie aplicat utilizând funcții mai complexe pentru a implementa cea mai confortabilă analiză a raportului de vânzări.

Cum să obțineți titlurile coloanelor pe inspirirea unei singure celule?

De exemplu, la fel de spectaculos am afirmat o lună în care a existat o vânzare maximă folosind a doua formulă. Nu este dificil să observați că în cea de-a doua formulă am folosit scheletul primei formule fără funcția MAX. Structura principală a formulei: PRD (B1; A5: G14; Coloană (B5: G14); 0). Am înlocuit funcția MAX în căutare, care în primul argument utilizează valoarea obținută prin formula anterioară. Acum acționează ca un criteriu de căutare pentru o lună. Și, ca rezultat, funcția de căutare pentru noi returnează numărul coloanei 2 unde este localizat valoare maximă Volumul vânzărilor pentru produs 4. După aceea, funcția include o funcție de index care returnează valoarea de către cronologie și coloana din intervalul definit în argumentele sale. Deoarece avem un număr de coloană 2 și numărul rândului în intervalul în care numele lunilor sunt stocate în orice caz vor fi 1. Apoi rămâne o funcție a indicelui pentru a obține o valoare corespunzătoare din gama B4: G4 - Februarie (a doua lună).



Valorile de căutare în coloana Excel

Cea de-a doua sarcină este de a căuta tabelul folosind numele lunii ca criteriu. În astfel de cazuri, trebuie să schimbăm scheletul formulei: funcția UPR este înlocuită cu GRP, iar funcția coloanei este înlocuită cu un șir.

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

Pentru a găsi ce produs a avut vânzările maxime într-o anumită lună, rezultă:

Principiul acțiunii de căutare Valoarea formulei în coloana Excel:

În primul argument, funcția GPR (Vizualizare orizontală) indică o legătură cu o celulă cu un criteriu de căutare. În al doilea argument, este indicată o legătură cu gama privită a tabelului. Al treilea argument generează o funcție de șir care creează o serie de linii de la 10 elemente în memorie. Deoarece în partea de masă avem 10 linii.

Apoi, funcția GRP utilizând alternativ fiecare număr de linie creează o serie de valori de vânzări adecvate de la masă la o anumită lună (iunie). Apoi, funcțiile maxime rămân doar pentru a alege valoarea maximă din această matrice.

ATENŢIE! Când utilizați un schelet de formule pentru alte sarcini, acordați întotdeauna atenție celui de-al doilea și al treilea argument al funcției de căutare a GRP. Numărul de rânduri acoperite în intervalul argumentului specificat în argument ar trebui să coincidă cu numărul de rânduri din tabel. De asemenea, numerotarea ar trebui să înceapă cu al doilea rând!

În documente Microsoft Excel.care constau din un numar mare Câmpurile trebuie adesea să găsească anumite date, numele șirului etc. Foarte incomod când trebuie să vezi un număr mare de rânduri pentru a găsi cuvântul sau expresia dorită. Economisiți timpul și nervii vor ajuta căutarea Microsoft Excel încorporată. Să-i dau seama cum funcționează și cum să o folosim.

Funcția de căutare B. programul 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 capacitatea de a îmbunătăți căutarea de date.

Metoda 1: Căutare simplă

O selecție simplă de date în programul Excel vă permite să găsiți toate celulele care conțin caracterul setat în fereastra de căutare (litere, numere, cuvinte etc.) fără a înregistra registrul.

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

Dacă aveți o masă destul de mare, atunci în acest caz nu este întotdeauna convenabil să căutați peste tot pe foaie, deoarece în rezultatele cautarii Este posibil să existe 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 numai cu o anumită gamă de celule.

Metoda 3: Căutare avansată

Așa cum am menționat mai sus, cu căutarea obișnuită a emiterii de rezultate, absolut toate celulele care conțin un set de simboluri de căutare în orice formă nu sunt dependente de registru.

În plus, nu numai conținutul unei anumite celule, ci și adresa elementului la care se referă poate fi eliberată. De exemplu, în celula E2 conține o formulă care este suma celulelor A4 și C3. Această cantitate este de 10, iar acest număr este afișat în celula E2. Dar, dacă cerem în căutarea numărului "4", atunci printre rezultatele emiterii, va fi aceeași celulă E2. Cum se poate întâmpla? Pur și simplu în celula E2, formula conține o adresă la celula A4, care include doar figura 4 dorită.

Dar, cum să taie astfel, și alte rezultate de căutare inacceptabile cu bună știință? Este pentru aceste scopuri că există o căutare avansată Excel.

1. După deschiderea ferestrei "Găsiți și înlocuiți" În orice mod descris în mod, faceți clic pe buton "Parametrii".



2. O serie de instrumente suplimentare pentru gestionarea căutării apar în fereastră. În mod implicit, toate aceste instrumente sunt într-o stare, ca în cazul căutării normale, dar dacă este necesar, vă puteți ajusta.

   

   Funcția implicită "Luați în considerare registrul" și "Celulele în întregime" Dezactivat, dar dacă punem căpușele în apropierea elementelor relevante, atunci în acest caz, când rezultatul este generat, registrul introdus va fi luat în considerare și coincidența exactă. Dacă introduceți un cuvânt cu o literă mică, atunci în eliberarea căutării, celulele care conțin scrisul acestui cuvânt cu o scrisoare de capital, deoarece ar fi în mod implicit, nu va mai cădea. În plus, dacă funcția este activată "Celulele în întregime", Numai elementele care conțin nume exacte vor fi adăugate la emitere. De exemplu, dacă specificați search Query. "Nikolaev", apoi celulele care conțin textul "Nikolaev A. D." nu vor fi adăugate la emitere.

   

   În mod implicit, căutarea este făcută numai pe activă fișa Excel.. Dar dacă parametrul "Căutare" Veți transfera în poziție "In carte"Căutarea va fi făcută pe toate fișierele deschise.

   

   În parametru. "Naviga" Puteți schimba direcția de căutare. În mod implicit, după cum sa menționat mai sus, căutarea este efectuată în linia de comandă. Reîntorgeți comutatorul în poziție "Prin coloane", Puteți seta procedura de formare a rezultatelor emiterii, pornind de la prima coloană.

   

   În Graf. "Zona de căutare" Determinate, printre care elementele specifice sunt căutate. În mod implicit, acestea sunt formule, adică acele date care dau clic pe celulă sunt afișate în șirul de formule. Poate fi un cuvânt, un număr sau o legătură cu celula. În același timp, programul, efectuând căutarea, vede doar referința și nu rezultatul. Acest efect a fost discutat mai sus. Pentru a căuta cu precizie rezultatele, datele afișate în celulă sunt afișate și nu în șirul de formulare, trebuie să rearanjați comutatorul din poziție "Formule" A pozitiona "Valori". În plus, există posibilitatea de a căuta note. În acest caz, comutatorul este rearanjat în poziție "Note".

   

   Chiar mai precis, căutarea poate fi setată făcând clic pe buton. "Format".

   

   Aceasta deschide fereastra de format celular. Aici puteți seta formatul celulelor pentru a participa la căutare. Puteți seta restricții formatul numeric, alinierea, fontul, granița, umplerea și protecția, unul dintre acești parametri sau combinându-le împreună.

   

   Dacă doriți să utilizați un anumit format celular, apoi în partea de jos a ferestrei, faceți clic pe buton. "Utilizați formatul acestei celule ...".

   

   După aceea, apare un instrument sub forma unei pipete. Cu aceasta, puteți selecta acea celulă pe care o veți utiliza formatul.

   

   După ce formatul de căutare este configurat, faceți clic pe buton O.K.

   

   Există cazuri atunci când trebuie să căutați nu pentru fraze specifice, dar găsiți celule în care cuvintele de căutare sunt în orice ordine, chiar dacă alte cuvinte și simboluri sunt separate. Apoi aceste cuvinte ar trebui să fie alocate pe ambele părți semnul "*". Acum, în rezultatele căutării vor fi afișate toate celulele în care aceste cuvinte sunt localizate în orice ordine.



3. De îndată ce setările de căutare sunt instalate, faceți clic pe buton "Găsiți toate" sau "Găsiți mai departe"Pentru a merge la rezultatele căutării.

După cum puteți vedea programul Excel. Este destul de simplu, dar în același timp o căutare foarte funcțională a instrumentelor de căutare. Pentru a produce cea mai simplă scârțire, este suficient să apelați o casetă de căutare, introduceți o interogare în acesta și faceți clic pe buton. Dar, în același timp, este posibilă configurarea căutării individuale cu un număr mare de parametri diferiți și setări suplimentare.

Formatare condiționată (5)
Liste și intervale (5)
Macrocomenzi (Proceduri VBA) (63)
Diverse (39)
Excel bug-uri și glitches (3)

Cum să găsiți o valoare într-o altă masă sau o putere PRP

De fapt, vreau să spun despre oportunitatea nu numai în acest articol. funcții de armedar, de asemenea, doresc să afecteze și Boarda de căutareCa o funcție foarte conexă cu arbitraj. Fiecare dintre aceste funcții are atât avantajele, cât și Contra. Dacă într-o nucă de port, UPR caută o anumită valoare specificată de noi printre multe valori situate într-o singură coloană. Poate că cel mai adesea necesitatea unui arbitraj apare atunci când este necesar să se compare datele, să găsiți datele într-un alt tabel, din același tabel Adăugați date către altul, pe baza oricărui criteriu etc.
Pentru a înțelege puțin mai bine principiul de funcționare al UPR, este mai bine să începeți cu un anumit exemplu practic. Există un tabel de acest tip:
fig.1.

Și de la primul tabel trebuie înlocuit în a doua dată pentru fiecare nume. Pentru trei înregistrări, aceasta nu este o problemă și face mâinile - totul este evident. Dar în viață, acestea sunt tabele pe mii de intrări, iar căutarea cu substituirea datelor pot dura manual o oră. Plus o altă pereche de zbor în țintă: nu numai faptul că numele este amplasat complet diferit în ambele tabele, iar numărul de înregistrări din tabele sunt diferite, astfel încât mai multe tabele sunt situate pe diferite foi / cărți. Presupun că te-am convins că substituirea acestor mâini nu este o opțiune deloc. Dar Vlookup) Va fi indispensabil aici. În același timp, practic nimic nu va trebui să facă - să scrie doar în prima celulă a coloanei de la a doua masă (unde este necesar să înlocuiți datele din prima masă) O astfel de formulă:
\u003d Upr ($ a2; list1! $ A $ 2: $ 4; 3; 0)
Puteți scrie formula fie direct în celulă, fie utilizând dispecerul funcției selectând în categorie Linkuri și arrays. PRD și specificând separat criteriile necesare. Acum copiați ( Ctrl.+C.) Celulă cu o formulă care alocă toate celulele coloanei până la capătul datelor și introduceți ( Ctrl.+V.).

În primul rând, principiul principal al funcționării: UPR caută o valoare de masă specificată de argumentul din prima coloană a argumentului Skeeping_new . Când găsiți valoarea dorită, funcția returnează valoarea opusă valorii găsite, dar din coloana specificată de argument Numărul_stolbets. . Cu vedere la interval, ne vom da seama puțin mai târziu. VDP poate returna doar o singură valoare - prima potrivită pentru criteriu. Dacă valoarea dorită nu este găsită (lipsă în tabel)Apoi rezultatul va fi funcția # N / d . Nu vă fie frică de acest lucru - este chiar util. Veți ști exact ce înregistrări nu sunt și astfel puteți compara două mese unul cu celălalt. Uneori se dovedește că vedeți: Datele se află în ambele tabele, dar problemele VD # n / d. Astfel încât datele din tabelele dvs. nu sunt identice. În unele dintre ele există lacune excesive inconspicuoase (de obicei înainte de valoare sau după) sau semnele de chirilic sunt amestecate cu semne latine. La fel # N / d va fi dacă criteriile numărului și în tabelul dorit sunt înregistrate ca text (De regulă, triunghiurile verzi apar în colțul din stânga sus al unei astfel de celule), și în final - ca numere. Sau vice versa.

Descrierea argumentelor argumentului
$ A2 - argument Skeeping_new (Să o numim Criteriu să fie scurt). Aceasta este ceea ce căutăm. Acestea. Pentru prima înregistrare a celui de-al doilea tabel, acesta va fi Petrov S.A. Aici puteți specifica fie textul criteriului (în acest caz, acesta ar trebui să fie în citate - \u003d VD ("Petrov SA"; listul1! $ 2: $ 4; 3; 0) sau un link către Celula, cu acest text (ca în exemplul de funcție). Există o mică nuanță: puteți aplica, de asemenea, simboluri de substituție: "*" și "?". Este foarte convenabil dacă aveți nevoie să găsiți valori numai prin partea liniei. De exemplu, nu puteți intra complet "Petrov S.A.", dar pentru a introduce numai numele de familie și semnul Star - "Petrov *". Apoi, orice intrare va fi retrasă, care începe pe Petrov. Dacă trebuie să găsiți o intrare în care numele "Petrov" se găsește oriunde în linie, atunci puteți specifica acest lucru: "PETROV *". Dacă doriți să găsiți numele de familie petrov și indiferent de ce inițiale vor fi la numele personal (dacă numele este înregistrat sub formă de Ivanov I.I.), atunci aici este cea mai bună: "Ivanov?". " . Este adesea necesar să specificați valoarea sa pentru fiecare linie (în coloană și numele de familie și trebuie să le găsiți pe toate). În acest caz, referințele la celulele coloanei A. De exemplu, în celula A1 înregistrată: Ivanov. De asemenea, este cunoscut faptul că Ivanov este într-o altă masă, dar după numele de familie, numele și patronimul pot fi înregistrate (sau altceva). Dar trebuie să găsim doar un șir care începe pe numele de familie. Apoi trebuie să înregistrați după cum urmează: A1 & "*". Această intrare va fi echivalentă cu "Ivanov *". În A1, Ivanov este înregistrat, Ampersand (&) este folosit pentru a fuziona într-o singură linie de două valori de text. Un asterisc în citate (deoarece ar trebui să fie text în interiorul formulei). Așa că ajungem:
A1 & "*" \u003d\u003e
"Ivanov" & "*" \u003d\u003e
"Ivanov *"
Foarte convenabil dacă există multe personaje pentru căutare.
Dacă trebuie să determinați dacă există un cuvânt undeva în șir, atunci stelele au pus pe ambele părți: "*" & A1 & "*"

Sheet1! $ A $ 2: $ C $ 4 - Argument Masa. Specifică gama de celule. Numai intervalul trebuie să conțină date din prima celulă cu datele către cea mai mare parte. Acest lucru nu trebuie specificat în intervalul de exemplu. Dacă șirurile 100, apoi listei1! $ A $ 2: $ C $ 100. Este important să vă amintiți trei lucruri: În primul rând, ea Masa ar trebui să înceapă întotdeauna cu această coloană în care căutăm Criteriu . Si nimic altceva. În caz contrar, nu se găsește nimic nu va fi sau rezultatul nu va fi exact cel care se așteaptă. Al doilea: argument Masa trebuie să fie "fixă" . Ce înseamnă. Vedeți semnele de dolar - $? Acest lucru se fixează (Mai exact, aceasta se numește o legătură absolută cu gama). Cum sa terminat. Alocați textul linkului (Doar o gamă este un criteriu) Și Zhmete. F4. Până vom vedea că dolarul nu a apărut înainte de denumirea denumirii numelui coloanei și înainte de numărul liniei. Dacă acest lucru nu este făcut, atunci când copiați formula, tabelul de argument va "mișca" și rezultatul va fi din nou incorect. Și ultima - tabelul trebuie să conțină coloane de la primul (în care căutăm) la acesta din urmă (din care este necesar să returneze valorile). În exemplul Listă1! $ A $ 2: $ C $ 4 - deci nu va fi posibilă returnarea valorii din coloana D (4), deoarece Tabelul este doar trei coloane.

3 - Numărul_stolbets. . Aici specificați pur și simplu numărul coloanei din argument Masa, Valori de la care trebuie să înlocuim ca rezultat. În acest exemplu, aceasta este data adoptării - adică Coloana numărul 3. Dacă am avut nevoie de un departament, aș indica 2 și dacă am fi nevoie să comparăm pur și simplu dacă există un nume de tabel în altul, atunci ar fi posibil să se indice și 1. IMPORTANT: argument Numărul_stolbets. nu ar trebui să depășească numărul de coloane din argument Masa . În caz contrar, rezultatul formulei va fi o eroare #LEGĂTURĂ!. De exemplu, dacă este specificată o gamă de $ 2: $ 4 $ 4 și este necesar să returnați datele din coloana C, atunci indicați corect 2. deoarece argument Masa($ 2: $ 2: $ 4) conține doar două coloane - în și C. Dacă încercați să specificați numărul coloanei 3 (ce contul este pe foaie), vom obține o greșeală #LEGĂTURĂ!deoarece Cea de-a treia coloană din intervalul specificat este pur și simplu.

Sfat practic: Dacă tabelul de argument are un număr prea mare de coloane și trebuie să returnați rezultatul din ultima coloană, atunci nu este necesar să le calculați cu numărul. Acesta poate fi specificat ca acesta: \u003d VD ($ A2; listul1! $ 2: $ C $ 4; Numeric (foaie 1! $ 2: $ C $ 4); 0). Apropo în această foaie de caz1! De asemenea, puteți să eliminați ca inutile: \u003d VD ($ a2; listul1! $ 2: $ 4 C $ 4; Nuterstolba ($ 2: $ C $ 4); 0).

0 - Interval_prinderea - Argument foarte interesant. Poate fi egal cu adevărat sau fals. Imediat apare întrebarea: de ce în formula mea acolo 0? Totul este foarte simplu - Excel în formulele poate percepe 0 ca o minciună și 1 ca adevăr. Dacă în UPR pentru a specifica acest parametru este 0 sau o minciună, atunci va apărea căutarea conformității precise cu criteriul specificat. Nu are nimic de-a face cu semnele de substituție ("*" și "?"). Dacă utilizați 1 sau adevăr (sau nu specificați deloc ultimul argument, pentru că, în mod implicit, este adevărul), atunci ... o istorie foarte lungă. Pe scurt - EPR va căuta cea mai asemănătoare valoare potrivită pentru Criteriu . Uneori foarte utile. Adevărat, dacă utilizați acest parametru, este necesar ca lista din tabelul de argument să fie sortată ascendentă. Atrac atenția asupra faptului că sortarea este necesară numai dacă argumentul interval_Specting sunteți egal cu adevărul sau 1. Dacă 0 sau o minciună - sortarea nu este necesară.

Mulți au observat probabil că în imaginea mea am avut apartamente pentru numele complet. Aceasta nu este o eroare de intrare. În exemplul atașat la articol, este evidențiat, cât se poate înlocui și datele, fără a schimba argumentul manual Numărul_stolbets.. Mi se părea că un astfel de exemplu ar putea veni la îndemână.

Cum să evitați erorile # N / D (# N / A) în UPR?
O altă problemă frecventă - mulți nu doresc să vadă rezultate # n / d, dacă coincidența nu este găsită. Este ușor să ajungeți:
\u003d Dacă (Egr ($ a2, listul1! $ 2: $ 4; 3; 0)); "" ARM ($ A2; list1! $ 2: $ 4; 3; 0))
Acum, dacă o arbitrarină nu găsește coincidența, celula va fi goală.
Și utilizatorii versiunilor Excel 2007 pot fi utilizate dacă puteți utiliza dacă:
\u003d Dacă utilitatea (ARM A2, listul1! $ A $ 2: $ 4; 3; 0); "")

Consiliul de căutare promis

Această caracteristică caută valoarea specificată de parametru Skeeping_new în argument Vizibil_massiv. . Și rezultatul funcției este numărul de poziție al valorii găsite în Vizualizated_massiv.. Este numărul de poziție, nu valoarea însăși. În principiu, nu o voi picta în același mod, deoarece punctele principale sunt exact aceleași. Dacă am vrut să o aplicăm pentru tabelul de mai sus, ar fi așa:
\u003d Companie de căutare ($ a2; list1! $ 2: $ 4; 0)
$ A2 este cea dorită. Aici totul este exact la fel ca și cu UPR. Sunt permise, de asemenea, simbolurile înlocuirii și exact în aceeași performanță.

Leaf1! $ A $ 2: $ 4 - Viewed_Massive. Diferența principală față de UMR este permisă să specifice o matrice cu o singură coloană. Ar trebui să fie coloana în care vom arăta Skeeping_new . Dacă încercați să specificați mai mult de o coloană, funcția va returna eroarea.

Type_Specions (0) - la fel ca în UPR Interval_prinderea . Cu aceleași caracteristici. Se distinge de ocazia de a căuta cel mai mic de la cele dorite sau cele mai mari. Dar, pe acest lucru, nu mă voi opri în acest articol.

Practic tratate. Dar trebuie să întoarcem numărul de poziție, dar valoarea însăși. Prin urmare, încercarea de căutare nu este potrivită pentru noi. Cel puțin unul, de la sine. Dar dacă este utilizat împreună cu indicele funcției - atunci acesta este ceea ce avem nevoie și chiar mai mult.
\u003d Index (foaie1! $ A $ 2: $ C $ 4; Board de căutare ($ a2, list1! $ 2: $ 4; 0); 2)
O astfel de formulă va reveni la fel ca un ARD.

Indexul funcției argumentelor
Leaf1! $ A $ 2: $ 4 $ - o matrice. Ca acest argument, indicăm intervalul de la care vrem să obținem valori. Poate fi atât o coloană, cât și mai multe. Dacă coloana este una, ultimul argument nu este necesar să specificați funcția. Apropo, acest argument nu poate absolut să coincidă cu cel pe care îl prezentăm în argumentul considerat de_massiv funcția consiliului de căutare.

Apoi vine numere_stroke și numere_stall. Este ca un număr pe care îl înlocuim placa de căutare, care se întoarce la numărul de poziție din SUA în matrice. Acest lucru este construit pe acesta. Indicele returnează valoarea din matrice care se află în linia specificată (numărul serverului) a matricei și a coloanei specificate (număr_number) dacă coloanele sunt mai mari decât una. Este important să știți că, în acest pachet, numărul de rânduri din argument o serie de indice de funcții și numărul de șiruri de caractere din argument considerat by_massive funcțiile comisiei de căutare ar trebui să coincidă. Și începeți cu aceeași linie. Acest lucru este în cazuri normale, dacă nu urmăriți scopuri diferite.
De asemenea, ca în cazul unui HDP, indicele în cazul faptului că nu găsiți valoarea dorită returnează # H / D. Și puteți ocoli astfel de erori în același mod:
Pentru toate versiunile Excel (inclusiv anul 2003):
\u003d Dacă ($ a2; list1! $ 2: $ 4: $ 4; 0)); ""; Index (foaie1! $ 2: $ C $ 4; companie de căutare ($ a2; list1! $ Un $ 2: $ 4; 0); 2))
Pentru versiuni din 2007 și mai sus:
\u003d Dacă utilitate (Index (foaie1! $ A $ 2: $ C $ 4; Board de căutare ($ a2, list1! $ 2: $ 4; 0); 2); "")

Lucrul cu criteriile este mai mare de 255 de caractere
Există un index de căutare și un alt avantaj înainte de PRD. Faptul este că EPR nu poate căuta sens lungimea șirului care conține mai mult de 255 de caractere. Se întâmplă rar, dar se întâmplă. Puteți, desigur, să înșele arbitrajul și să reduceți criteriul:
\u003d PRP (PSTR ($ a2; 1; 255); PSTR (Foaia1! $ 2: $ 4; 1; 255); 3; 0)
Dar aceasta este o formulă de matrice. Și în plus, nu întotdeauna o astfel de formulă va reveni rezultatul dorit. Dacă primele 255 de caractere sunt identice cu primele 255 de caractere din tabel, și apoi semnele diferă - formula nu va vedea acest lucru. Da și returnează formula în mod excepțional de valori textuale, care în cazurile în care numerele ar trebui returnate, nu foarte convenabile.

Prin urmare, este mai bine să se utilizeze o astfel de formulă cunning:
\u003d Index (foaie1 $ A $ 2: $ C $ 4; sumare (adevăr, list1! $ 2: $ 4 \u003d $ A2; 0)); 2)
Aici am folosit aceleași game de lizibilitate, dar în exemplul de descărcare, ele diferă de cele indicate aici.
Formula însăși este construită pe capabilitățile funcției de transformare a Sumpera în calcule masive ale unor funcții în interiorul acesteia. În acest caz, posesia de căutare caută o poziție de rând în care criteriul este egal cu valoarea rândului. Caracterele Wildcard aici nu pot fi aplicate.

În exemplul de realizare, veți găsi exemple de utilizare a tuturor cazurilor descrise și un exemplu de motiv pentru care indicele și placa de căutare sunt uneori preferabile la UPR.

Exemplu de descărcare

(26,0 kib, 14.082 descărcări)

Articolul a ajutat? Distribuiți un link cu prietenii! Lecții video.

("Bara de jos": "TextStyle": "static", "text pozitiv": "partea de jos", "Textautohide": TRUE, "Textpozitionarginstatic": 0, "Textpozitiondynamic": "bottleft", "TextpozitionMarginleft": 24 " TextPesitionMarguRright ": 24," TextPozitionMargintop ": 24," TextPositionMarginbottom ": 24", Texteffect ":" Slide "," TEXTEFECUBIC "," TEXTEFFECTURAȚIE ": 600," TEXTEFFECTSIDERECTION ":" Stânga "," Texteffectsleslidesance " . "TexteffectDelay1": 1000, "TEXTEFFECT2": "Slide", "TexteffectsLideirection2": "dreapta", "TexteffectsLidesance2": 120, "Texteffecteasing2": "SoioutCubic", "Texteffectducturation2": 600, "TexteffectDelay2": 1500 " TextCss ":" Afișaj: bloc: 12px; Text-align: stânga; "," TextbgCss ":" Afișaj: bloc; poziția: absolut: 0px; stânga: 0px; lățime: 100%; înălțime: 100% ; Culoare de fundal: # 333333; Opacitate: 0,6; Filtru: a Lpha (opacitate \u003d 60); "," Titlass ":" Afișaj: bloc; Poziție: rudă; Font: Bold 14px \\ "Lucida Sans Unicode \\", \\ "Lucida Grande \\", Sans-Serif, Arial; Culoare: #ffff; "," DescriereCss ":" Afișaj: bloc; Poziție: rudă; Font: 12px \\ "Lucida Sans Unicode \\", \\ "Lucida Grande \\", Sans-Serif, Arial; Culoare: #ffff; Margin-top: 8px; "," butoncss ":" Afișaj: bloc; Poziție: rudă; Margin-top: 8px: "", "Addgoooglefonts": FALSE "," GoogleFonts ":" "," TextleftRightPercentForsticatic ": 40))

Salut prieteni. Cât de des trebuie să căutați conformitatea în tabelul Excel pentru orice înțeles? De exemplu, trebuie să găsiți adresa unei persoane sau în lista de prețuri - prețul mărfurilor. Dacă se găsesc astfel de sarcini - acest post este pentru dvs.!

Fac proceduri similare în fiecare zi și fără funcțiile descrise mai jos, aș fi avut cu adevărat strâns. Luați o notă și aplicați-i în munca dvs.!

Căutați în tabelul exil, funcțiile PRD și GRP

Rolul acestor funcții în viața utilizatorului obișnuit este dificil de supraestimat. Acum puteți găsi cu ușurință o intrare adecvată în tabelul de date și returnați valoarea corespunzătoare acestuia.

Sintaxa funcției PRD este: \u003d VD ( Desirable_dation; Table_del_pook; numere_store_stolbets; [Tipul de tip]). Luați în considerare argumentele:

• Valoarea secundară - Valoarea pe care o vom căuta. Acesta este un argument obligatoriu;
• Tabel pentru căutare - Această serie de celule în care va fi o căutare. Coloana cu valorile dorite ar trebui să fie prima în această matrice. Acesta este, de asemenea, un argument obligatoriu;
• Numărul coloanei de ieșire - Numărul de secvență al coloanei (pornind de la primul din matrice), din care funcția va emite datele când valorile dorite coincid. Argument obligatoriu;
• Tipul de comparație - Selectați "1" (sau "Adevăr") pentru o coincidență non-strictă, "0" ("minciuni") - pentru coincidența completă. Argumentul este opțional dacă este ratat - căutarea va fi efectuată. coincidență non-strictă.

Căutați o coincidență exactă utilizând VDP

Să ne uităm la exemplul, cum funcționează funcția PRD când este selectată tipul de comparație "FALS", căutați o coincidență exactă. În clasa B5: E10, activele fixe ale unei anumite companii sunt indicate, valoarea cărții, numărul inventarului și locația. Celula B2 indică numele pentru care este necesar să găsiți numărul de inventar în tabel și să îl plasați în celula C2.

Funcția PRD în Excel

Noi scriem formula: \u003d VD (B2; B5: E10; 3; minciuni).

Aici, primul argument indică faptul că tabelul trebuie să caute o valoare din celula B2, adică Cuvântul "fax". Al doilea argument spune că tabelul de căutare este în intervalul B5: E10 și să caute cuvântul "fax" în prima coloană, adică. În Array B5: B10. Al treilea argument informează programul că rezultatul calculului este cuprins în cea de-a treia coloană a matricei, adică. D5: D10. Al patrulea argument este "fals", adică. Este necesară o coincidență completă.

Și astfel, funcția va primi o linie "fax" din celula B2 și va căuta în clasa B5: B10 de sus în jos. Odată ce coincidența este găsită (linia 8), funcția va returna valoarea corespunzătoare din coloana D, adică Conținut D8. Asta am nevoie de noi, sarcina a fost rezolvată.

Dacă valoarea dorită nu este găsită, funcția se va întoarce.

Căutați coincidența inexactă folosind

Datorită acestei opțiuni în uPR de lucruPutem evita formulele complexe pentru a găsi rezultatul dorit.

În matricea C12 Q5: C12, ratele dobânzilor sunt indicate în funcție de suma împrumutului. În celula B2, specificăm suma împrumutului și dorim să obținem un pariu în C2 pentru o astfel de tranzacție. Sarcina este complicată de faptul că suma poate fi una și este puțin probabil să coincidă cu cea specificată în matrice, căutarea unei coincidență exactă nu se potrivește:

Apoi scriem o formulă de căutare non-accident vascular cerebrală: \u003d VD (B2; B5: C12; 2; adevăr). Acum, de la toți cei reprezentați în coloana din date, programul va căuta cel mai apropiat mai puțin. Aceasta este, pentru suma de 8000, valoarea 5000 va fi selectată și procentul corespunzător a fost afișat.

Căutarea neîntreruptă a unui EPR în Excel

Pentru funcționarea corectă, funcția trebuie să fie sortată prin prima coloană a mesei ascendente. În caz contrar, poate da un rezultat eronat.

Funcția GRP are aceeași sintaxă ca un ARP, dar caută rezultatul nu în coloane, ci în linii. Adică, se uită la mesele de sus în jos, dar de la stânga la dreapta și afișează un număr de linie specificat, nu o coloană.

Căutați date utilizând caracteristica Vizualizare

Funcția de vizualizare funcționează similar cu UPR, dar are o altă sintaxă. Îl folosesc când tabelul de date conține mai multe coloane de duzină și pentru a utiliza o artă, trebuie să calculați suplimentar numărul difuzorului de ieșire. În astfel de cazuri, caracteristica de vizualizare facilitează sarcina. Și așa, sintaxă: \u003d Vizualizare ( Desirable_dation; Masiv_dl_poog; Masiv_dela_motion) :

• Valoarea secundară - date sau trimitere la datele pe care doriți să le căutați;
• Matrice pentru căutare - o linie sau o coloană în care căutăm o valoare similară. Această matrice Cu siguranță, sortăm ascendența;
• O matrice pentru afișare- Datele care conțin date pentru a obține rezultate. În mod natural, ar trebui să fie o dimensiune cu o matrice de căutare.

Cu o astfel de înregistrare, nu dați o gamă de referință relativă de rezultate. Și să indice direct, adică Nu este nevoie să pre-calculați numărul coloanei de ieșire. Folosim funcția de vizualizare în primul exemplu pentru funcția PRD (active fixe, numere de inventar): \u003d Vizualizare (B2; B5: B10; D5: D10). Sarcina este rezolvată cu succes!

Funcție "View" în Microsoft Excel

Căutați după coordonatele relative. Căutarea funcțiilor și indexul

O altă metodă de căutare a datelor este de a combina funcțiile căutării și indexului.

Primul dintre ele servește la căutarea valorilor în matrice și obținerea numărului de ordine: Board de căutare ( Desirable_dation; Privind prin_massive; [Tipul de comparație] ). Argumentele funcții:

• Valoarea secundară - Argument obligatoriu
• Privind prin matrice - O linie sau o coloană în care căutăm o coincidență. Argument obligatoriu
• Tipul de comparație - Specificați "0" pentru a căuta o coincidență precisă, "1" - cea mai apropiată, "-1" este cea mai apropiată mai mult. Deoarece funcția caută de la începutul listei până la sfârșit, atunci când căutați cel mai apropiat coloana de căutare mai mică - Sortare. Și când căutați mai mult - sortați-vă ascendența.

Se găsește poziția valorii necesare, acum puteți afișa pe ecran utilizând funcția. INDEX( Matrice; Numărul serverului; [Număr_stolbit]) :

• Array. - argumentul indică ce matrice de celule trebuie să aleagă o valoare
• Numărul rândului- Specificați numărul de secvență al liniei (pornind de la prima celulă a matricei) care urmează să fie ieșire. Aici puteți scrie valoarea manuală sau puteți utiliza rezultatul calculării unei alte funcții. De exemplu, placa de căutare.
• Numărul coloanei- Argumentul opțional este indicat dacă matricea este formată din mai multe coloane. Dacă argumentul este ratat, formula utilizează prima coloană a tabelului.

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

Funcțiile căutării și indexului în Excel

Acestea sunt modalitățile de căutare și de respingere a ecranului de date există în Excel. Apoi, le puteți folosi în calcule, utilizați în prezentare, efectuați funcționarea cu acestea, indicați ca argumente ale altor funcții etc.

Simțiți cum creșteți și fixați cunoștințele și abilitățile? Apoi nu vă opriți, continuați să citiți! În următorul post vom lua în considerare: va fi dificil și interesant!

Buna seara prieteni!

Tema acestui articol va fi o altă ocazie de a lucra cu textul, este cunoașterea modului în care funcționează. Cred că nu ar trebui să explicați care poate fi exprimată utilitatea acestei funcții, este deja clar pur și simplu din nume și înseamnă că această funcție va căuta potrivirea exactă a textului specificat în șirul de text specificat și va returna pornirea linia liniei găsite.

Dar pot spune imediat că în forma sa pură, funcția găsită arată foarte inestetic și, în principiu, practic nu a fost folosit. Cea mai obișnuită oportunitate este utilizată numai în formă combinată, pentru a se conecta cu astfel de funcții ca Levsimv, Pratseim. și . Astfel de combinații vă vor permite să utilizați în mod eficient toate caracteristicile funcției de găsit și să efectuați sarcini mai complexe în comparație cu utilizarea simplă.

Ei bine, acum să ne uităm la sintaxa care are:

\u003d Găsiți (căutați; text vizibil; [Poziția inițială])Unde

Vă atrag atenția că o caracteristică care are, este hipersensibilitate la cazul literelor, adică trebuie să specificați grinzi corecte și mici.

De exemplu, ia în considerare 2 exemple de comparație:

\u003d Găsiți ("apă"; "apă apă")Vom returna valoarea de 1, dar formula

\u003d Găsiți ("apă"; "apă apă")va returna poziția 6.

După cum se poate observa din exemplu, aceasta este o diferență foarte semnificativă pe care o va fi ignorată.

În cazul în care textul pe care doriți să îl găsiți nu va fi detectat, formula se va întoarce. Deci, de fapt, să luăm în considerare variabilitatea în exemple, așa cum este utilizat în exemple simple și complexe:

Exemple simple:

Luați în considerare aplicarea unei singure funcții de a găsi, în mai multe variante și versiuni. Luați, de exemplu, valoarea textului "Nagaev Artem" și aplicați funcția:

\u003d Găsiți ("a"; A2), în această formulă, căutăm poziția primei litere "A" în celula A2 și obținem rezultatul "8";

\u003d Găsiți ("a"; A2), în această formulă, căutăm poziția primei litere "A" în celula A2 și obținem rezultatul "2";

\u003d Găsiți ("A"; A2; 3)În această formulă, căutăm poziția primei litere "A" în celula A2, dar căutarea începe de la a treia marcă și obține rezultatul "4".
Exemple combinate:

Primul exemplu Va exista o combinație utilizând funcția PSTR. De exemplu, avem numele de produse cu numărul nomenclaturii "LED №456217-485" și trebuie să separăm boabele de la splava, adică numele camerei din cameră și acest lucru necesită o formulă:

\u003d PST (A4; 1; găsi («№»; A4; 1) -1)În formula găsim poziția "№" și tot ceea ce este atras de el prin formula corespunzătoare și obținem rezultatul "LED". Argumentul "-1" va lua în considerare faptul că poziția pe care o cautau textul pe care o căutăm este, de asemenea, parte a intervalului de numărare și nu este supus revenirii.
Al doilea exemplu Eu folosesc combinații cu funcții Levsimv, Pratseim.. De exemplu, în celula A5, am introdus numele și prenumele "Nagaev Artem", în acest caz, folosind formule:

=Levsimv (A5; găsi (simbol (32); A5) -1), vom putea extrage numele de familie,

\u003d Pracemir (A5, DLSTR (A5)-noapte (simbol (32); A5)), Elimin numele.
În cazul în care vor exista lacune inutile între cele două nume și nume de familie, două și mai multe, apoi să funcționeze formulele de mai sus, este necesar să se utilizeze funcția SLCROBELY..

Pe aceasta cred că o altă funcție textuală, ați reușit să apreciați. Am învățat sau cel puțin am dat seama cum și unde este folosit. Am studiat utilitatea și aplicațiile. Mai multe funcții sunt prezentate pentru familiarizare.

Dacă aveți întrebări, scrieți comentarii, puneți ca, dacă vă place.

Ne vedem în articole noi!

"Avem întotdeauna bani pe capriciu, vom pierde doar costurile utile și necesare.
"
O. Balzac.