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 (-(((kriteerium1)+(kriteerium2)+…)> 0) |
Kriteeriumid1: See on mis tahes kriteerium, mis tagastab massiivi TÕENE ja VÄÄR.
Kriteeriumid2: 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.
Probleem:
Lahtri hind tuleks jagada täpselt 5 sarnaseks makseks. Jagades hinna 88,04 dollarit lihtsalt 5 -ga ja ümardades tulemuse kahe kümnendkohani, saame individuaalseid makseid 17,61 dollarit. Sellega kaasneks aga kokku 88,05 dollari suurune makse, mis ei ole täpne summa, millega alustame. Seetõttu tahame arvutada 5 sarnast numbrit, mis moodustavad kogusumma.
Lahendus:
Esiteks Arvutage esimene makse, jagades hinna 5 -ga ja ümardades tulemuse.
Kasutage funktsiooni ROUND järgmises valemis:
= RING (B1/5,2)
2. samm:
Arvutage iga järelejäänud makse.
Kasutage ROUND, SUM ja ROW funktsioone järgmises valemis:
= ROUND (($ B $ 1-SUM ($ B $ 2: B2))/(5-ROW ()+ROW ($ B $ 2)), 2)
Näide:
Loendage kasutajaid, kui edasimüüja kood sobib või aasta, kasutades SUMPRODUCT -i
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 lugeda 1 -ks.
Nii et kuule, meil on 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. Ja neid rekordeid loetakse 1. Ja nii saame 7.
Vaatame, kuidas see toimib?
Nii et vaatame samm -sammult, kuidas valemit lahendatakse, siis arutlen selle toimimise üle.
-
- = SUMPRODUCT(-((((kood = I3)+(aasta = K3))> 0))
- SUMPRODUCT(-(({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE;…}+{FALSE; FALSE; FALSE; TRUE; TRUE;…})> 0))
- SUMPRODUCT(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0))
- SUMPRODUCT(-({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE;…})
- SUMPRODUCT({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})
7
Kahekordset negatiivset (-) märki kasutatakse loogiliste väärtuste teisendamiseks 1-ks ja 0-ks. Seega teisendatakse iga massiivi tõene väärtus 1 -ks ja FALSE 0 -ks.
Loendamiseks rohkemate või kriteeriumide lisamine SUMPRODUCT abil. Seega, kui peate loendamiseks lisama rohkem kriteeriume, saate funktsioonile lihtsalt lisada kriteeriumid, 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, et koodivahemikust oleks kaks kriteeriumi. Oletame, et soovite lugeda "INKB". Kuidas siis 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 loendamiseks on järgmine:
| = SUMPRODUCT (-(((kood = I2: J2)+(aasta = I3: J3))> 0)) |
See on SUMPRODUCT valem, mida tuleb arvestada mitme kriteeriumiga, kui reale kirjutatakse mitu kriteeriumi ühest vahemikust.
Ülaltoodud valem 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 ühe veeru sama vahemiku kriteeriumid? 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 ilmneb, 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.
Siin on kõik tähelepanekud valemi kasutamise kohta.
Märkused:
- Valem töötab nii teksti kui ka numbrite puhul.
- Otsinguväärtused võib anda lahtriviidena või kasutada valemis argumentidena otse tsitaatsümbolit (").
Loodetavasti mõistsite, kuidas jagada summa võrdseteks makseteks, vältides samal ajal Exceli jagamis- / ümardamisvigu. Siit leiate rohkem artikleid Exceli otsingu väärtuse ja Excel 2019 funktsioonide kohta. Kui teile meeldisid meie ajaveebid, jagage seda oma sõpradega Facebookis. Samuti saate meid jälgida Twitteris ja Facebookis. Tahaksime sinust kuulda, andke meile teada, kuidas saaksime oma tööd täiustada, täiendada või uuendada ning muuta see teie jaoks paremaks. Kirjuta meile meilisaidile.
Väärtuse otsimiseks kasutage INDEX ja MATCH : Funktsioon INDEX & MATCH väärtuse otsimiseks vastavalt vajadusele.
SUM vahemik koos indeksiga Excelis : Kasutage funktsiooni INDEX, et leida soovitud väärtuste SUMM.
Funktsiooni INDEX kasutamine Excelis : Leidke massiivi INDEX näite abil selgitatud funktsiooni INDEX abil.
Funktsiooni MATCH kasutamine Excelis : Leidke massiivist MATCH, kasutades näite abil selgitatud funktsiooni MATCH sees olevat INDEX -väärtust.
Funktsiooni LOOKUP kasutamine Excelis : Otsige massiivist otsinguväärtus, kasutades näidisega selgitatud funktsiooni LOOKUP.
Populaarsed artiklid:
Funktsiooni IF kasutamine Excelis : Exceli IF -lause kontrollib tingimust ja tagastab konkreetse väärtuse, kui tingimus on TRUE, või tagastab mõne muu väärtuse, kui FALSE.
Funktsiooni VLOOKUP kasutamine Excelis : See on üks enim kasutatud ja populaarsemaid Exceli funktsioone, mida kasutatakse erinevate vahemike ja lehtede väärtuse otsimiseks.
Funktsiooni SUMIF kasutamine Excelis : See on veel üks armatuurlaua oluline funktsioon. See aitab teil teatud tingimustel väärtusi kokku võtta.
Funktsiooni COUNTIF kasutamine Excelis : 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.