U ovom ćemo članku naučiti Kako tražiti točna podudaranja pomoću funkcije SUMPRODUCT u Excelu.
Scenarij:
Jednostavnim riječima, tijekom rada s podatkovnim tablicama ponekad moramo tražiti vrijednosti s malim slovima u Excelu. Funkcije pretraživanja poput funkcija VLOOKUP ili MATCH ne pronalaze točno podudaranje jer to nisu funkcije osjetljive na velika i mala slova. Pretpostavimo da radite na podacima u kojima se 2 imena razlikuju na temelju osjetljivosti na velika i mala slova, tj. Jerry i JERRY dva su različita imena. Možete izvoditi zadatke poput operacija u više raspona koristeći formulu dolje objašnjenu.
Kako riješiti problem?
Za ovaj problem morat ćemo koristiti funkciju SUMPRODUCT & EXACT. Sada ćemo od funkcije napraviti formulu. Ovdje nam je dana tablica i moramo pronaći vrijednosti koje odgovaraju točnom podudaranju u tablici u Excelu. Funkcija SUMPRODUCT vraća SUM odgovarajućih TRUE vrijednosti (kao 1) i zanemaruje vrijednosti koje odgovaraju FALSE vrijednostima (kao 0) u vraćenom nizu
Opća formula:
| = SUMPRODUCT ( - (EXACT (lookup_value, lookup_array)), (return_array)) |
lookup_value: vrijednost za traženje.
lookup_array: traži niz za traženje vrijednosti.
return_array: niz, vraćena vrijednost koja odgovara nizu za pretraživanje.
-: Negacija (-) char mijenja vrijednosti, TRUE ili 1s u FALSEs ili 0s i FALSEs ili 0s u TRUEs ili 1s.
Primjer:
Sve ovo može biti zbunjujuće za razumijevanje. Dakle, isprobajmo ovu formulu tako da je pokrenemo na dolje prikazanom primjeru. Ovdje imamo podatke o količini i cijeni proizvoda primljenih na datume. Ako se neki proizvod kupuje dvaput, on ima 2 naziva. Ovdje moramo pronaći broj stavki za sve bitne stavke. Tako smo za to dodijelili 2 popisa kao primljeni popis i bitne stavke potrebne u stupcu za formulu. Sada ćemo koristiti donju formulu za dobivanje količine "mlijeka" iz tablice.
Koristite formulu:
| = SUMPRODUCT ( - (TOČNO (F5, B3: B11)), (C3: C11)) |
F6: potražite vrijednost u F6 ćeliji
B3: B11: niz za traženje su stavke
C3: C11: povratni niz je Količina
-: Negacija (-) char mijenja vrijednosti, TRUE ili 1s u FALSEs ili 0s i FALSEs ili 0s u TRUEs ili 1s.
Ovdje je raspon dat kao referenca ćelije. Pritisnite Enter za dobivanje količine.
Kao što vidite, 58 je količina koja odgovara vrijednosti "mlijeko" u ćeliji B5, a ne "mlijeko" u ćeliji B9. Morat ćete tražiti vrijednost "Mlijeko" ako vam je potrebna količina "54" u ćeliji C9.
Možete potražiti cijenu koja odgovara vrijednosti "mlijeko" samo pomoću donje formule.
Koristite formulu:
| = SUMPRODUCT ( - (TOČNO (F5, B3: B11)), (D3: D11)) |
F6: potražite vrijednost u F6 ćeliji
B3: B11: niz za traženje su stavke
D3: D11: povratni niz je Cijena
Ovdje imamo 58 je cijena koja odgovara vrijednosti "mlijeko" u ćeliji B5, a ne "mlijeko" u ćeliji B9. Morat ćete tražiti vrijednost "Mlijeko" ako trebate cijenu "15,58" u ćeliji D9.
Jedinstvene vrijednosti u nizu traženja
Slično, pomoću formule možete pronaći jedinstvene vrijednosti. Ovdje se kao primjer koristi vrijednost pretraživanja "EGGS".
Na gornjoj slici imamo vrijednosti koje prilagođavaju istu formulu. No problem se javlja ako ima jedinstvenu vrijednost i ne tražite odgovarajuću vrijednost pretraživanja. Kao ovdje koristeći vrijednost "Kruh" u formuli za pretraživanje u tablici.
Formula vraća 0 kao količinu i cijenu, ali to ne znači da je količina i cijena kruha 0. Samo što formula vraća 0 kao vrijednost kada vrijednost koju tražite nije pronađena. Stoga koristite ovu formulu samo za traženje točnog podudaranja.
Također možete izvršiti traženje točnih podudaranja pomoću funkcija INDEX i MATCH u Excelu. Saznajte više o tome kako napraviti pretraživanje osjetljivo na velika i mala slova pomoću funkcije INDEKS & MATCH u Excelu. Djelomična podudaranja možete potražiti i pomoću zamjenskih znakova u Excelu.
Evo nekoliko opservacijskih napomena prikazanih u nastavku.
Bilješke:
- Formula funkcionira samo za traženje točnog podudaranja.
- Funkcija SUMPRODUCT smatra numeričke vrijednosti 0.
- Funkcija SUMPRODUCT logičku vrijednost TRUE smatra 1, a False 0.
- Polje argumenata mora biti iste veličine, inače funkcija vraća pogrešku.
- SUMPRODUCT funkcija vraća vrijednost koja odgovara TRUE vrijednostima u vraćenom nizu nakon uzimanja pojedinačnih proizvoda u odgovarajućem nizu.
- Operatori kao što je jednako ( = ), manje od jednakog ( <= ), veće od ( > ) ili nije jednako () može se izvesti unutar primijenjene formule, samo s brojevima.
Nadam se da je ovaj članak o tome kako pronaći točno podudaranje pomoću funkcije SUMPRODUCT u Excelu objašnjen. Više članaka o formulama za brojanje potražite ovdje. Ako su vam se svidjeli naši blogovi, podijelite ih sa svojim početnicima na Facebooku. Također nas možete pratiti na Twitteru i Facebooku. Voljeli bismo čuti vaše mišljenje, javite nam kako možemo poboljšati, nadopuniti ili inovirati svoj rad i učiniti ga boljim za vas. Pišite nam na web stranici e -pošte
Kako koristiti funkciju SUMPRODUCT u Excelu: Vraća SUM nakon množenja vrijednosti u više niza u Excelu.
Upotrijebite INDEX i MATCH za traženje vrijednosti : Formula INDEX-MATCH koristi se za dinamičko i precizno traženje vrijednosti u danoj tablici. Ovo je alternativa funkciji VLOOKUP i prevladava nedostatke funkcije VLOOKUP.
Zbroj prema OFFSET grupama u redovima i stupcima : Funkcija OFFSET može se koristiti za dinamičko zbrajanje skupine ćelija. Te grupe mogu biti bilo gdje u listu.
Kako dohvatiti svaku N -tu vrijednost u rasponu u Excelu: Pomoću funkcije OFFSET u Excelu možemo dohvatiti vrijednosti iz izmjenjivih redaka ili stupaca. Ova formula koristi funkciju ROW za izmjenu redova i COLUMN funkciju za izmjenu u stupcima.
Kako koristiti OFFSET funkciju u Excelu : Funkcija OFFSET moćna je Excel funkcija koju mnogi korisnici potcjenjuju. No, stručnjaci znaju moć funkcije OFFSET i kako tu funkciju možemo koristiti za obavljanje čarobnih zadataka u formuli Excel. Ovdje su osnove funkcije OFFSET.
Kako koristiti zamjenske znakove u Excelu : Brojanje ćelija koje se podudaraju s izrazima pomoću zamjenskih znakova u Excelu
Popularni članci
50 Excel prečac za povećanje vaše produktivnosti : Brže izvršite svoj zadatak. Ovih 50 prečaca učinit će da radite još brže na Excelu.
Kako koristiti tFunkcija VLOOKUP u Excelu : Ovo je jedna od najčešće korištenih i popularnih funkcija programa Excel koja se koristi za traženje vrijednosti iz različitih raspona i listova u Excelu.
Kako koristiti funkciju COUNTIF u Excelu : Brojte vrijednosti s uvjetima pomoću ove nevjerojatne funkcije. Ne morate filtrirati svoje podatke da biste brojali određene vrijednosti u Excelu. Funkcija COUNTIF bitna je za pripremu vaše nadzorne ploče.
Kako koristiti funkciju SUMIF u Excelu : Ovo je još jedna bitna funkcija nadzorne ploče. To vam pomaže pri zbrajanju vrijednosti s posebnim uvjetima.