Kot sem že omenil v številnih svojih blogih, je SUMPRODUCT zelo vsestranska funkcija in se lahko uporablja za več namenov. V tem članku bomo videli, kako lahko s to funkcijo štejemo vrednosti z več merili OR.
Splošna formula SUMPRODUCT za štetje z več ali merili
| = SUMPRODUCT (-(((merila1)+(merila 2)+… )>0) |
Kriterij 1: To je vsako merilo, ki vrne niz TRUE in FALSE.
Merila 2: To je naslednje merilo, ki ga želite preveriti. Podobno imate lahko toliko meril, kot želite.
Zgornja splošna formula se pogosto spreminja tako, da ustreza zahtevam za štetje z več merili OR. Toda osnovna formula je ta. Najprej bomo na primeru videli, kako to deluje, nato pa bomo razpravljali o drugih scenarijih, kjer boste morali to formulo nekoliko spremeniti.
Primer: Preštejte uporabnike, če koda trgovca ali letoTekme Uporaba SUMPRODUCT
Torej imamo tukaj nabor podatkov prodajalcev. Podatki vsebujejo veliko stolpcev. Kar moramo storiti, je, da preštejemo število uporabnikov, ki imajo kodo "INKA" ali je leto "2016". Če ima nekdo oboje (oznako »inka« in leto 2016), se prepričajte, da se šteje kot 1.
Torej imamo tukaj dva merila. Uporabljamo zgoraj omenjeno formulo PODROČJA:
| = SUMPRODUCT (-(((Koda = I3)+(Leto = K3))> 0)) |
Tu sta koda in leto poimenovani obsegi.
To vrne 7.
V podatkih imamo 5 zapisov kode INKA in 4 zapise za leto 2016. Toda 2 zapisa imata za kodo oziroma leto tako INKA kot leto 2016. Ti zapisi se štejejo kot 1. In tako dobimo 7.
Kako deluje?
Torej, poglejmo, kako se formula reši korak za korakom, nato bom razpravljal o tem, kako deluje.
| =PODROČJE(-(((Koda = I3)+(Leto = K3))> 0)) |
| 1=>PODROČJE(-(({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE;…}+{FALSE; FALSE; FALSE; TRUE; TRUE;…})> 0)) |
| 2=>PODROČJE(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0)) |
| 3=>PODROČJE(-({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE;…}) |
| 4=>PODROČJE({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0}) |
| 5=>7 |
V prvem koraku se vrednost I3 ("INKA") primerja z vsako celico v območju kod. To vrne niz TRUE in FALSE. TRUE za vsako tekmo. Zaradi prihranka prostora nisem prikazal vseh TRUE-FALSE. Podobno se vrednost K3 (2016) ujema z vsako celico v letnem obsegu.
V naslednjem koraku dodamo ti dve matriki, ki imata za posledico nov niz numeričnih vrednosti. Kot morda veste, je TRUE v Excelu obravnavano kot 1, FALSE pa kot 0. Ko dodamo TRUE in TRUE, dobimo 2, ostalo pa lahko razumete.
V naslednjem koraku preverimo, katera vrednost je v matriki večja od 0. To spet pretvori matriko v resnično napačno matriko. Za vsako vrednost 0, ki jo dobimo, se vrednost False in rest pretvorijo v resnico. Zdaj je naš odgovor število vrednosti TRUE v matriki. Toda kako jih štejemo? Evo kako.
Dvojni negativni (-) znaki se uporabljajo za pretvorbo logičnih vrednosti v 1s in 0s. Tako se vsaka vrednost TRUE v matriki pretvori v 1 in FALSE v 0.
V zadnjem koraku SUMPRODUCT povzame ta niz in odgovor dobimo kot 7.
Dodajanje več ali meril za štetje z uporabo SUMPRODUCT
Če torej želite dodati več ali merila za štetje, lahko funkciji preprosto dodate kriterije z znakom +.
Na primer, če želite zgornji formuli dodati še eno merilo, tako da se doda število zaposlenih, ki so prodali več kot 5 izdelkov. Formula SUMPRODUCT bo preprosto videti tako:
| = SUMPRODUCT (-(((Koda = I3)+(Leto = K3)+(Prodaja> 5))> 0)) |
Enostavno! kajne?
Recimo, da želite imeti dva merila Koda obseg. Recimo, da želite šteti "INKB". Torej, kako to storite? Ena metoda uporablja zgornjo tehniko, vendar bi se ponavljala. Recimo, da želim dodati še 10 meril iz istega območja. V takih primerih ta tehnika ni tako pametna za štetje s SUMPRODUCT.
Recimo, da imamo podatke urejene tako.
Kode meril so v eni vrstici I2: J2. Tu je pomembna ureditev podatkov. Formula SUMPRODUCT za 3 nastavitve štetja meril OR bo:
| = SUMPRODUCT (-(((Koda = I2: J2)+(Leto = I3: J3))> 0)) |
To je formula SUMPRODUCT za štetje z več merili, ko je v eno vrstico zapisanih več meril iz enega obsega.
To vrne pravilen odgovor, ki je 10.
Če katero koli leto vnesete v J3, bo formula dodala tudi to število.
To se uporablja, če so merila v eni vrstici. Ali bo delovalo, če so merila v enem stolpcu za isti obseg? Ne. Ne bo.
V tem primeru imamo za štetje več kod, vendar so te kode zapisane v enem stolpcu. Ko uporabimo zgornjo formulo SUMPRODUCT, dobimo napako #N/A. Ne bomo raziskovali, kako je prišlo do te napake, saj bo ta članek zaradi tega predolg. Poglejmo, kako lahko to naredimo.
Če želite, da ta formula deluje, morate v funkcijo TRANSPOSE zaviti merila kode. Tako bo formula delovala.
| = SUMPRODUCT (-(((Koda = TRANSPOSE (H3: H4))+(Leto = TRANSPOSE (I3: I4)))> 0)) |
To je formula za štetje z več ali pogoji v istem obsegu, ko so merila navedena v stolpcu.
Tako da kolega, upam, da sem bil dovolj jasen in je imel smisel. Upam, da služi vašemu namenu, da ste tukaj. Če ta formula ni rešila vaše težave, mi sporočite svoje zahteve v spodnjem razdelku za komentarje. Z veseljem vam bom na kakršen koli način pomagal. Omenite lahko kakršen koli dvom, povezan z Excelom/VBA. Do takrat se še naprej učite, bodite odlični.
Kako uporabljati funkcijo SUMPRODUCT v Excelu: Vrne SUM po množenju vrednosti v več matrikah v Excelu. To funkcijo lahko uporabite za opravljanje več nalog. To je ena najbolj vsestranskih funkcij.
COUNTIFS z dinamičnim obsegom meril : Za štetje z obsegom dinamičnih meril preprosto uporabimo funkcijo INDIRECT. Ta funkcija lahko
RAČUNI Z OR Za več meril : Preštejte celice z več kriteriji, ki se ujemajo s funkcijo OR. Če želite logiko OR vnesti v funkcijo COUNTIFS, vam ni treba uporabiti funkcije OR.
Uporaba IF s funkcijami AND / OR v programu Microsoft Excel : Te logične funkcije se uporabljajo za izračun več meril. S funkcijo IF se OR in AND uporabljata za vključitev ali izključitev ujemanj.
Kako uporabljati funkcijo OR v Microsoft Excelu : Funkcija se uporablja za vključitev vseh vrednosti TRUE v več meril.
Kako šteti celice, ki vsebujejo to ali ono v Excelu v Excelu : Za celice, ki vsebujejo to ali ono, lahko uporabimo funkcijo SUMPRODUCT. Evo, kako naredite te izračune.
Priljubljeni članki:
50 bližnjic v Excelu za večjo produktivnost | Poskrbite, da bo vaša naloga hitrejša. S temi 50 bližnjicami boste še hitreje delali v Excelu.
Kako uporabljati funkcijo Excel VLOOKUP| To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.
Kako uporabljati Excel COUNTIF funkcija| Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določenih vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.
Kako uporabljati funkcijo SUMIF v Excelu | To je še ena bistvena funkcija armaturne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.
