Kuten olen maininnut monissa blogeissani, että SUMPRODUCT on erittäin monipuolinen toiminto ja sitä voidaan käyttää moniin tarkoituksiin. Tässä artikkelissa näemme, kuinka voimme käyttää tätä toimintoa useiden TAI -ehtojen sisältävien arvojen laskemiseen.
Yleinen SUMPRODUCT -kaava, joka lasketaan useilla tai kriteereillä
| = SUMPRODUCT (-((((kriteerit 1)+(kriteerit 2)+… )>0) |
Kriteerit 1: Tämä on mikä tahansa ehto, joka palauttaa taulukon TOSI ja EPÄTOSI.
Kriteerit2: Tämä on seuraava kriteeri, jonka haluat tarkistaa. Samoin sinulla voi olla niin monta kriteeriä kuin haluat.
Yllä olevaa yleistä kaavaa muutetaan usein vastaamaan vaatimuksia, jotka lasketaan useilla OR -kriteereillä. Mutta peruskaava on tämä. Ensin näemme kuinka tämä toimii esimerkin kautta ja sen jälkeen keskustelemme muista skenaarioista, joissa sinun on muutettava tätä kaavaa hieman.
Esimerkki: Laske käyttäjät, jos jälleenmyyjän koodi tai vuosiOttelut SUMPRODUCTin käyttäminen
Tässä on siis myyjien tietojoukko. Tiedot sisältävät monia sarakkeita. Meidän on laskettava niiden käyttäjien määrä, joilla on koodi "INKA" tai vuosi on "2016". Varmista, että jos jollakin on molemmat (koodi "inka" ja vuosi 2016), se lasketaan yhdeksi.
Tässä on siis kaksi kriteeriä. Käytämme yllä mainittua SUMPRODUCT -kaavaa:
| = SUMPRODUCT (-((((koodi = I3)+(vuosi = K3))> 0)) |
Tässä koodi ja vuosi on nimetty alueiksi.
Tämä palauttaa 7.
Aineistossa on 5 tietuetta INKA -koodista ja 4 tietuetta vuodelta 2016. Mutta kahdessa tietueessa on sekä "INKA" että vuosi 2016 koodina ja vuosi. Nämä tietueet lasketaan 1. Ja näin saamme 7.
Kuinka se toimii?
Joten katsotaanpa kuinka kaava ratkaistaan askel askeleelta, sitten keskustelen siitä, miten se toimii.
| =SUMPRODUCT(-(((koodi = I3)+(vuosi = K3))> 0)) |
| 1=>SUMPRODUCT(-(({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE;…}+{FALSE; FALSE; FALSE; TRUE; TRUE;…})> 0)) |
| 2=>SUMPRODUCT(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0)) |
| 3=>SUMPRODUCT(-({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE;…}) |
| 4=>SUMPRODUCT({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0}) |
| 5=>7 |
Ensimmäisessä vaiheessa I3 -arvoa ("INKA") verrataan jokaiseen koodialueen soluun. Tämä palauttaa taulukon TOSI ja EPÄTOSI. TOSI jokaisesta ottelusta. Tilan säästämiseksi en ole näyttänyt kaikkia TRUE-FALSE. Samoin K3 (2016) -arvo sovitetaan yhteen kunkin vuoden solun solun kanssa.
Seuraavassa vaiheessa lisäämme nämä kaksi taulukkoa, jotka johtavat uuteen numeeristen arvojen taulukkoon. Kuten ehkä tiedät, TOSI käsitellään Excelissä 1: nä ja EPÄTOSI 0: na Excelissä. Joten kun lisätään TOSI ja TOSI, saamme 2 ja loput ymmärrät.
Seuraavassa vaiheessa tarkistamme, mikä arvo on suurempi kuin 0 taulukossa. Tämä muuttaa taulukon jälleen todelliseksi vääriä matriiseja. Jokaisesta saamastamme 0 -arvosta False ja rest muunnetaan tosi -arvoksi. Vastaus on nyt TOSI -arvojen lukumäärä taulukossa. Mutta miten laskemme ne? Näin voit tehdä.
Kaksoisnegatiivisia (-) merkkejä käytetään muuntamaan totuusarvot 1s ja 0s. Joten jokainen TOSI -arvo taulukossa muutetaan yhdeksi ja EPÄTOSI arvoksi 0.
Viimeisessä vaiheessa SUMPRODUCT tiivistää tämän taulukon ja saamme vastauksemme 7.
Lisää tai ehtoja laskemiseen käyttämällä SUMPRODUCT -ohjelmaa
Joten jos sinun on lisättävä enemmän tai laskettava ehtoja, voit vain lisätä ehtoja funktiolle + -merkillä.
Jos esimerkiksi haluat lisätä toisen kriteerin yllä olevaan kaavaan niin, että se lisää niiden työntekijöiden määrän, jotka ovat myyneet yli 5 tuotetta. SUMPRODUCT -kaava näyttää yksinkertaisesti tältä:
| = SUMPRODUCT (-(((koodi = I3)+(vuosi = K3)+(myynti> 5))> 0)) |
Yksinkertainen! eikö olekin?
Oletetaan kuitenkin, että haluat saada kaksi kriteeriä Koodi valikoima. Oletetaan, että haluat laskea "INKB". Joten miten teet tämän? Yksi tapa on käyttää yllä olevaa tekniikkaa, mutta se olisi toistuvaa. Oletetaan, että haluan lisätä 10 muuta ehtoa samalta alueelta. Tällaisissa tapauksissa tämä tekniikka ei ole kovin fiksu laskettaessa SUMPRODUCT -ohjelmaa.
Oletetaan, että meillä on tällaisia tietoja.
Ehtokoodit ovat yhdellä rivillä I2: J2. Tietojen järjestely on tässä tärkeä. SUMPRODUCT -kaava 3 TAI ehtojen laskenta -asetuksille on:
| = SUMPRODUCT (-((((koodi = I2: J2)+(vuosi = I3: J3))> 0)) |
Tämä on SUMPRODUCT -kaava, joka lasketaan useilla ehdoilla, kun useita ehtoja yhdeltä alueelta kirjoitetaan peräkkäin.
Tämä palauttaa oikean vastauksen, joka on 10.
Jos kirjoitat minkä tahansa vuoden kohtaan J3, kaava lisää myös tämän määrän.
Tätä käytetään, kun kriteerit ovat yhdellä rivillä. Toimiiko se, kun kriteerit ovat samassa sarakkeessa samalle alueelle? Ei.
Tässä esimerkissä meillä on useita koodeja laskettavaksi, mutta nämä tyyppikoodit on kirjoitettu yhteen sarakkeeseen. Kun käytämme yllä olevaa SUMPRODUCT -kaavaa, saamme virheen #N/A. Emme tutki, miten tämä virhe tuli, koska tämä tekee tästä artikkelista liian pitkän. Katsotaanpa, miten voimme saada tämän toimimaan.
Jotta tämä kaava toimisi, sinun on pakattava koodiehdot TRANSPOSE -funktioon. Tämä saa kaavan toimimaan.
| = SUMPRODUCT (-(((Koodi = TRANSPOSE (H3: H4))+(Vuosi = TRANSPOSE (I3: I4)))> 0)) |
Tämä on kaava laskemiseen useilla tai ehdoilla samalla alueella, kun kriteerit on lueteltu sarakkeessa.
Joten kyllä, toveri, toivon, että olin tarpeeksi selkeä ja se oli järkevää. Toivottavasti se palvelee tarkoitustasi olla täällä. Jos tämä kaava ei ratkaissut ongelmaasi, kerro minulle vaatimuksistasi alla olevassa kommenttiosassa. Autan sinua mielelläni kaikin tavoin. Voit mainita epäilyttävään Excel/VBA: han liittyvät asiat. Siihen asti jatka oppimista, jatka loistamista.
SUMPRODUCT -toiminnon käyttäminen Excelissä: Palauttaa summan Excelin useiden matriisien arvojen kertomisen jälkeen. Tätä toimintoa voidaan käyttää useiden tehtävien suorittamiseen. Tämä on yksi monipuolisimmista toiminnoista.
COUNTIFS dynaamisilla kriteereillä : Laskemiseksi dynaamisilla kriteerialueilla käytämme vain INDIRECT -funktiota. Tämä toiminto voi
COUNTIFS TAI useille kriteereille : Laske solut, joilla on useita kriteerejä, jotka vastaavat TAI -funktiota. TAI -logiikan lisäämiseksi COUNTIFS -funktioon sinun ei tarvitse käyttää TAI -funktiota.
IF: n käyttäminen AND / OR -funktioiden kanssa Microsoft Excelissä : Näitä loogisia toimintoja käytetään useiden kriteerien laskemiseen. IF -toiminnolla TAI- ja JA -toimintoja käytetään otteluiden sisällyttämiseen tai poissulkemiseen.
TAI -toiminnon käyttäminen Microsoft Excelissä : Toimintoa käytetään sisällyttämään kaikki TRUE -arvot useisiin kriteereihin.
Kuinka laskea solut, jotka sisältävät tämän tai sen Excelissä Excelissä : Soluille, jotka sisältävät sitä tai sitä, voimme käyttää SUMPRODUCT -funktiota. Näin teet laskelmat.
Suosittuja artikkeleita:
50 Excel -pikanäppäintä tuottavuuden lisäämiseksi | Nopeuta tehtävääsi. Nämä 50 pikanäppäintä tekevät työskentelystäsi entistä nopeampaa Excelissä.
Excel VLOOKUP -toiminnon käyttäminen| Tämä on yksi eniten käytetyistä ja suosituimmista Excel -toiminnoista, jota käytetään arvon etsimiseen eri alueilta ja arkeilta.
Excelin käyttäminen COUNTIF -toiminto| Laske arvot olosuhteilla käyttämällä tätä hämmästyttävää toimintoa. Sinun ei tarvitse suodattaa tietoja laskeaksesi tiettyjä arvoja. Laskutoiminto on välttämätön kojelaudan valmistelemiseksi.
SUMIF -toiminnon käyttäminen Excelissä | Tämä on toinen kojelaudan olennainen toiminto. Tämä auttaa sinua laskemaan yhteen arvot tietyissä olosuhteissa.
