În acest articol, vom învăța cum să căutați potriviri exacte utilizând funcția SUMPRODUCT în Excel.
Scenariu:
În cuvinte simple, în timp ce lucrăm cu tabele de date, uneori trebuie să căutăm valori cu litere sensibile la majuscule și minuscule în Excel. Funcțiile de căutare precum funcțiile VLOOKUP sau MATCH nu găsesc potrivirea exactă, deoarece acestea nu sunt funcții sensibile la majuscule. Să presupunem că lucrăm la date în care 2 nume sunt diferențiate pe baza sensibilității la majuscule, adică Jerry și JERRY sunt două prenume diferite. Puteți efectua sarcini precum operații pe mai multe intervale folosind formula explicată mai jos.
Cum se rezolvă problema?
Pentru această problemă, ni se va cere să folosim funcția SUMPRODUCT și funcția EXACT. Acum vom face o formulă din funcție. Aici ni se oferă un tabel și trebuie să găsim valori corespunzătoare potrivirii exacte din tabel în Excel. Funcția SUMPRODUCT returnează SUMA valorilor TRUE corespunzătoare (ca 1) și ignoră valorile corespunzătoare valorilor FALSE (ca 0) în matricea returnată
Formula generică:
| = SUMPRODUCT (- (EXACT (lookup_value, lookup_array)), (return_array)) |
lookup_value: valoare de căutat.
lookup_array: matrice de căutare pentru valoarea de căutare.
return_array: matrice, valoarea returnată corespunzătoare matricei de căutare.
-: Negation (-) char schimbă valorile, TRUEs sau 1s în FALSE sau 0s și FALSEs sau 0s în TRUEs sau 1s.
Exemplu:
Toate acestea ar putea fi confuze pentru a înțelege. Deci, să testăm această formulă executând-o pe exemplul prezentat mai jos. Aici avem date cu cantitatea și prețul produselor primite la date. Dacă un produs este cumpărat de două ori, acesta are 2 nume. Aici trebuie să găsim numărul de articole pentru toate articolele esențiale. Deci, pentru aceasta, am atribuit 2 liste ca listă primită și elementele esențiale necesare într-o coloană pentru formulă. Acum vom folosi formula de mai jos pentru a obține Cantitatea „laptelui” din tabel.
Utilizați formula:
| = SUMPRODUCT (- (EXACT (F5, B3: B11)), (C3: C11)) |
F6: căutați valoarea în celula F6
B3: B11: căutați matricea este Items
C3: C11: matricea returnată este Cantitate
-: Negation (-) char schimbă valorile, TRUEs sau 1s în FALSE sau 0s și FALSEs sau 0s în TRUEs sau 1s.
Aici intervalul este dat ca referință de celulă. Apăsați Enter pentru a obține Cantitatea.
După cum puteți vedea, 58 este cantitatea corespunzătoare valorii „lapte” din celula B5, nu „Lapte” din celula B9. Va trebui să căutați valoarea „Lapte” dacă aveți nevoie de cantitatea „54” în celula C9.
Puteți căuta prețul corespunzător valorii „lapte” doar utilizând formula prezentată mai jos.
Utilizați formula:
| = SUMPRODUCT (- (EXACT (F5, B3: B11)), (D3: D11)) |
F6: căutați valoarea în celula F6
B3: B11: căutați matricea este Items
D3: D11: matricea returnată este Preț
Aici avem 58 este Prețul corespunzător valorii „lapte” în celula B5 nu „Lapte” în celula B9. Va trebui să căutați valoarea „Lapte” dacă aveți nevoie de Prețul „15.58” în celula D9.
Valori unice în matricea de căutare
În mod similar, puteți găsi valorile unice folosind formula. Aici valoarea de căutare „EGGS” utilizată ca exemplu.
În imaginea de mai sus, am obținut valorile ajustând aceeași formulă. Dar problema apare dacă are o valoare unică și nu căutați valoarea de căutare adecvată. Ca și aici, folosind valoarea „Pâine” în formulă pentru a căuta în tabel.
Formula returnează 0 ca cantitate și preț, dar acest lucru nu înseamnă că cantitatea și prețul pâinii sunt 0. Este doar că formula returnează 0 ca valoare atunci când valoarea pe care o căutați nu este găsită. Deci, utilizați această formulă numai pentru a căuta potrivirea exactă.
De asemenea, puteți efectua căutări potrivite exacte utilizând funcțiile INDEX și MATCH în Excel. Aflați mai multe despre cum se face căutarea sensibilă la majuscule și minuscule folosind funcția INDEX & MATCH în Excel. De asemenea, puteți căuta potrivirile parțiale folosind metacaracterele din Excel.
Iată câteva note observaționale prezentate mai jos.
Note:
- Formula funcționează numai pentru a căuta potrivirea exactă.
- Funcția SUMPRODUCT consideră valorile nenumerice ca 0s.
- Funcția SUMPRODUCT consideră valoarea logică TRUE ca 1 și False ca 0.
- Argumentul trebuie să aibă aceeași dimensiune, altfel funcția returnează o eroare.
- Funcția SUMPRODUCT returnează valoarea corespunzătoare valorilor TRUE din matricea returnată după ce a luat produse individuale în matricea corespunzătoare.
- Operatorii sunt egali cu ( = ), mai mic decât egal cu ( <= ), mai mare ca ( > ) sau nu egal cu () poate fi efectuat în cadrul unei formule aplicate, numai cu numere.
Sper că acest articol despre Cum să căutați potrivirea exactă utilizând funcția SUMPRODUCT în Excel este explicativ. Găsiți mai multe articole despre formulele de numărare aici. Dacă ți-au plăcut blogurile noastre, împărtășește-le cu prietenii tăi de pe Facebook. Și, de asemenea, ne puteți urmări pe Twitter și Facebook. Ne-ar plăcea să aflăm de la dvs., să ne anunțați cum putem îmbunătăți, completa sau inova munca noastră și să o îmbunătățim. Scrieți-ne pe site-ul de e-mail
Cum se utilizează funcția SUMPRODUCT în Excel: Returnează SUMA după multiplicarea valorilor în mai multe tablouri în excel.
Utilizați INDEX și MATCH pentru a căuta valoarea : Formula INDEX-MATCH este utilizată pentru a căuta dinamic și precis o valoare într-un tabel dat. Aceasta este o alternativă la funcția VLOOKUP și depășește neajunsurile funcției VLOOKUP.
Suma prin grupuri OFFSET în rânduri și coloane : Funcția OFFSET poate fi utilizată pentru a însuma dinamic grupul de celule. Aceste grupuri pot fi oriunde în foaie.
Cum se recuperează fiecare valoare n-o dintr-un interval în Excel: Folosind funcția OFFSET din Excel putem prelua valori din rânduri sau coloane alternative. Această formulă oferă ajutor funcției ROW pentru a alterna prin rânduri și funcția COLUMN pentru a alterna în coloane.
Cum se utilizează funcția OFFSET în Excel : Funcția OFFSET este o funcție Excel puternică, subestimată de mulți utilizatori. Dar experții știu puterea funcției OFFSET și cum putem folosi această funcție pentru a realiza unele sarcini magice în formula Excel. Iată elementele de bază ale funcției OFFSET.
Cum se utilizează metacaracterele în Excel : Numărați celule care se potrivesc cu fraze folosind metacaracterele în excel
Articole populare
50 Comandă rapidă Excel pentru a vă crește productivitatea : Deveniți mai rapid la sarcina dvs. Aceste 50 de comenzi rapide vă vor face să lucrați și mai rapid pe Excel.
Cum se folosește tFuncția VLOOKUP în Excel : Aceasta este una dintre cele mai utilizate și populare funcții ale Excel, care este utilizată pentru a căuta valoarea din diferite intervale și foi în Excel.
Cum se utilizează funcția COUNTIF în Excel : Numărați valorile cu condiții folosind această funcție uimitoare. Nu trebuie să vă filtrați datele pentru a număra anumite valori în Excel. Funcția COUNTIF este esențială pentru a vă pregăti tabloul de bord.
Cum se utilizează funcția SUMIF în Excel : Aceasta este o altă funcție esențială a tabloului de bord. Acest lucru vă ajută să rezumați valori cu condiții specifice.