Nagu ma olen paljudes oma ajaveebides maininud, et SUMPRODUCT on väga mitmekülgne funktsioon ja seda saab kasutada mitmel otstarbel. Selles artiklis näeme, kuidas saame seda funktsiooni kasutada mitme VÕI kriteeriumiga väärtuste loendamiseks.
Üldine SUMPRODUCT valem mitme või kriteeriumiga arvestamiseks
| = SUMPRODUCT (-(((kriteeriumid1)+(kriteeriumid2)+… )>0) |
Kriteeriumid 1: See on mis tahes kriteerium, mis tagastab massiivi TRUE ja FALSE.
Kriteeriumid 2: See on järgmine kriteerium, mida soovite kontrollida. Samamoodi võib teil olla nii palju kriteeriume kui soovite.
Ülaltoodud üldvalemit muudetakse sageli, et see vastaks mitme VÕI kriteeriumiga arvestamise nõuetele. Kuid põhivalem on selline. Kõigepealt näeme näite kaudu, kuidas see toimib, ja pärast seda arutame muid stsenaariume, kus peate seda valemit veidi muutma.
Näide: loendage kasutajad, kui edasimüüja kood või aastaTikud SUMPRODUCTi kasutamine
Siin on meil müügimeeste andmekogum. Andmed sisaldavad palju veerge. Peame loendama kasutajate arvu, kellel on kood "INKA" või aasta on "2016". Veenduge, et kui kellelgi on mõlemad (kood "inka" ja aasta 2016), tuleks see lugeda 1 -ks.
Seega on meil kaks kriteeriumi. Kasutame ülalmainitud SUMPRODUCT valemit:
| = SUMPRODUCT (-((((kood = I3)+(aasta = K3))> 0)) |
Siin nimetatakse koodi ja aastat vahemikeks.
See tagastab 7.
Andmetes on meil 5 kirjet INKA koodist ja 4 kirjet aastast 2016. Kuid kahel kirjel on nii "INKA" kui ka 2016. aasta kood ja aasta vastavalt. Neid kirjeid loetakse 1. Ja nii saame 7.
Kuidas see töötab?
Nii et vaatame samm -sammult, kuidas valemit lahendatakse, siis arutlen selle toimimise üle.
| =SUMPRODUCT(-((((kood = I3)+(aasta = 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 |
Esimeses etapis võrreldakse I3 väärtust ("INKA") iga koodivahemiku lahtriga. See tagastab massiivi TRUE ja FALSE. ÕIGE iga mängu kohta. Ruumi kokkuhoiu huvides pole ma näidanud kõiki TRUE-FALSE. Samamoodi sobitatakse K3 (2016) väärtus aastavahemiku iga lahtriga.
Järgmisel etapil lisame need kaks massiivi, mille tulemuseks on uus arvväärtuste massiiv. Nagu te ehk teate, käsitletakse Excelis tõde 1 ja 1 väärtust VÄÄR ja väärtust 0. Nii et kui lisada tõde ja tõde, saame 2 ja ülejäänud saate aru.
Järgmises etapis kontrollime, milline väärtus on massiivis suurem kui 0. See muudab massiivi taas tõeliseks vale massiiviks. Iga saadud 0 väärtuse puhul teisendatakse väär ja ülejäänud väärtused tõeks. Nüüd on meie vastuseks massiivi tõeliste väärtuste arv. Aga kuidas me neid kokku loeme? Siin on, kuidas seda teha.
Kahekordseid negatiivseid (-) märke kasutatakse tõeväärtuste teisendamiseks 1-ks ja 0-ks. Seega teisendatakse iga massiivi tõene väärtus 1 -ks ja FALSE 0 -ks.
Viimases etapis võtab SUMPRODUCT selle massiivi kokku ja saame vastuseks 7.
Rohkemate või kriteeriumide lisamine loendamiseks SUMPRODUCT abil
Seega, kui peate loendamiseks lisama rohkem kriteeriume, saate funktsioonile lihtsalt lisada märke, kasutades + märki.
Näiteks kui soovite ülaltoodud valemile lisada veel ühe kriteeriumi, nii et see lisab töötajate arvu, kes on müünud rohkem kui 5 toodet. SUMPRODUCT valem näeb lihtsalt välja selline:
| = SUMPRODUCT (-(((kood = I3)+(aasta = K3)+(müük> 5))> 0)) |
Lihtne! kas pole?
Kuid oletame, et soovite saada kahte kriteeriumi Kood vahemik. Oletame, et soovite lugeda "INKB". Niisiis, kuidas seda teha? Üks meetod kasutab ülaltoodud tehnikat, kuid see oleks korduv. Oletame, et tahan lisada veel 10 kriteeriumi samast vahemikust. Sellistel juhtudel pole see tehnika SUMPRODUCTiga loendamiseks nii tark.
Oletame, et meil on sellised andmed korraldatud.
Kriteeriumikoodid on ühes reas I2: J2. Andmete paigutus on siin oluline. SUMPRODUCT valem 3 VÕI kriteeriumide loendusseade on järgmine:
| = SUMPRODUCT (-(((kood = I2: J2)+(aasta = I3: J3))> 0)) |
See on SUMPRODUCT valem, mida loetakse mitme kriteeriumiga, kui ühest vahemikust on mitu kriteeriumi järjest kirjutatud.
See tagastab õige vastuse, mis on 10.
Kui sisestate J3 -sse mis tahes aasta, lisab valem ka selle arvu.
Seda kasutatakse siis, kui kriteeriumid on ühes reas. Kas see töötab, kui kriteeriumid on samas vahemikus ühes veerus? Ei.
Selles näites on meil loendamiseks mitu koodi, kuid need tüübikoodid on kirjutatud ühte veergu. Kui kasutame ülaltoodud valemit SUMPRODUCT, saame vea ans #N/A. Me ei hakka uurima, kuidas see viga tekkis, kuna see muudab selle artikli liiga pikaks. Vaatame, kuidas seda tööd teha.
Selle valemi toimimiseks peate koodikriteeriumid pakkima funktsiooni TRANSPOSE. Nii saab valem tööle.
| = SUMPRODUCT (-(((Kood = TRANSPOSE (H3: H4))+(Aasta = TRANSPOSE (I3: I4)))> 0)) |
See on valem loendamiseks mitme või tingimustega samas vahemikus, kui veerus on kriteeriumid loetletud.
Nii et jah, semu, ma loodan, et olin piisavalt selge ja see oli loogiline. Loodan, et see täidab teie eesmärki siin olla. Kui see valem ei lahendanud teie probleemi, andke mulle oma nõuetest teada allpool olevas kommentaaride jaotises. Mul on hea meel teid igal viisil aidata. Võite mainida kahtlusega seotud Exceli/VBA -d. Kuni selle ajani jätkake õppimist, olge tublid.
Funktsiooni SUMPRODUCT kasutamine Excelis: Tagastab SUM -i pärast väärtuste korrutamist mitmes Exceli massiivis. Seda funktsiooni saab kasutada mitme ülesande täitmiseks. See on üks mitmekülgsemaid funktsioone.
COUNTIFS dünaamiliste kriteeriumide vahemikuga : Dünaamiliste kriteeriumide vahemikuga arvestamiseks kasutame lihtsalt funktsiooni INDIRECT. See funktsioon võib
COUNTIFS koos mitme kriteeriumiga või : Loendage lahtrid, millel on mitu kriteeriumi, mis vastavad funktsiooni OR abil. Funktsiooni COUNTIFS VÕI loogika lisamiseks ei pea te funktsiooni VÕI kasutama.
IF kasutamine funktsioonidega AND / OR Microsoft Excelis : Neid loogilisi funktsioone kasutatakse mitme kriteeriumi arvutamiseks. IF korral kasutatakse vasteid kaasamiseks või välistamiseks funktsioone VÕI ja JA.
Funktsiooni VÕI kasutamine Microsoft Excelis : Funktsiooni kasutatakse kõigi tõeliste väärtuste kaasamiseks mitmesse kriteeriumisse.
Kuidas arvutada Excelis seda või teist sisaldavaid rakke Excelis : Rakkudele, mis sisaldavad seda või teist, saame kasutada funktsiooni SUMPRODUCT. Siin on, kuidas neid arvutusi teha.
Populaarsed artiklid:
50 Exceli otseteed tootlikkuse suurendamiseks | Saa oma ülesandega kiiremini hakkama. Need 50 otseteed aitavad teil Excelis veelgi kiiremini töötada.
Kuidas kasutada funktsiooni Excel VLOOKUP| See on üks enim kasutatud ja populaarsemaid Exceli funktsioone, mida kasutatakse erinevate vahemike ja lehtede väärtuse otsimiseks.
Kuidas Excelit kasutada Funktsioon COUNTIF| Loendage väärtused tingimustega, kasutades seda hämmastavat funktsiooni. Te ei pea oma andmeid teatud väärtuste loendamiseks filtreerima. Countif funktsioon on armatuurlaua ettevalmistamisel hädavajalik.
Funktsiooni SUMIF kasutamine Excelis | See on veel üks armatuurlaua oluline funktsioon. See aitab teil teatud tingimustel väärtusi kokku võtta.
