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č ali merili.
Splošna formula SUMPRODUCT za štetje z več ali merili
| = PODROČJE (-(((merila1)+(merila2)+…)> 0) |
Merila 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 jih ž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.
Težava:
Cena v celici je treba natančno razdeliti na 5 podobnih plačil. Ko preprosto razdelimo ceno 88,04 USD na 5 in rezultat zaokrožimo na dve decimalni mesti, dobimo posamezna plačila v višini 17,61 USD. Vendar bi to skupaj pomenilo skupno plačilo 88,05 USD, kar ni natančen znesek, s katerim začnemo. Zato želimo izračunati 5 podobnih števil, ki seštejejo skupni znesek.
Rešitev:
Najprej izračunajte prvo plačilo tako, da ceno delite s 5 in rezultat zaokrožite.
Uporabite funkcijo ROUND v naslednji formuli:
= OKROGLA (B1/5,2)
2. korak:
Izračunajte vsako preostalo plačilo.
Uporabite funkcije ROUND, SUM in ROW v naslednji formuli:
= ROUND (($ B $ 1-SUM ($ B $ 2: B2))/(5-ROW ()+ROW ($ B $ 2)), 2)
Primer:
Štejte uporabnike, če se koda prodajalca ujema ali leto z uporabo 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, slišimo, da imamo 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. Tako dobimo 7.
Poglejmo, 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))
- PODROČJE(-(({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE;…}+{FALSE; FALSE; FALSE; TRUE; TRUE;…})> 0))
- PODROČJE(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0))
- PODROČJE(-({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE;…})
- PODROČJE({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})
7
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.
Dodajanje več ali meril za štetje z uporabo SUMPRODUCT -a, tako da če želite dodati več ali merila za štetje, lahko funkciji dodate samo merila 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 iz območja kode. Recimo, da želite šteti "INKB". Kako potem 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 nastavitev štetja meril 3 ALI bo:
| = SUMPRODUCT (-(((Koda = I2: J2)+(Leto = I3: J3))> 0)) |
To je formula SUMPRODUCT za štetje z več merili, ko je v vrsto zapisanih več meril iz enega obsega.
Zgornja formula 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 vrsti. Ali bo delovalo, če bodo merila v enem stolpcu za isto območje? 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, ker je ta članek 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.
Tu so vse opombe glede uporabe formule.
Opombe:
- Formula deluje tako za besedilo kot za številke.
- Iskalne vrednosti so lahko podane kot referenca celice ali pa neposredno uporabljeni navednik (") v formuli kot argumenti.
Upam, da ste razumeli, kako razdeliti znesek na enaka plačila, pri tem pa se izogniti napakam pri delitvi / zaokroževanju v Excelu. Raziščite več člankov o iskalni vrednosti Excel in funkcijah Excel 2019 tukaj. Če so vam bili naši blogi všeč, jih delite s prijatelji na Facebooku. Prav tako nas lahko spremljate na Twitterju in Facebooku. Radi bi slišali od vas, nam sporočite, kako lahko izboljšamo, dopolnimo ali inoviramo svoje delo in ga izboljšamo. Pišite nam na spletnem mestu e -pošte.
Za iskanje vrednosti uporabite INDEX in MATCH : Funkcija INDEX & MATCH za iskanje vrednosti po potrebi.
Obseg SUM z INDEX v Excelu : S funkcijo INDEX poiščite SUM vrednosti, kot je potrebno.
Kako uporabljati funkcijo INDEX v Excelu : Poiščite INDEX matrike s funkcijo INDEX, razloženo s primerom.
Kako uporabljati funkcijo MATCH v Excelu : Poiščite MATCH v matriki z uporabo vrednosti INDEX znotraj funkcije MATCH, razložene s primerom.
Kako uporabljati funkcijo LOOKUP v Excelu : Poiščite iskalno vrednost v matriki s funkcijo LOOKUP, razloženo s primerom.
Priljubljeni članki:
Kako uporabljati funkcijo IF v Excelu : Stavek IF v Excelu preveri pogoj in vrne določeno vrednost, če je pogoj TRUE, ali vrne drugo posebno vrednost, če je FALSE.
Kako uporabljati funkcijo VLOOKUP v Excelu : To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.
Kako uporabljati funkcijo SUMIF v Excelu : To je še ena bistvena funkcija nadzorne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.
Kako uporabljati funkcijo COUNTIF v Excelu : Š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.