Selles artiklis õpime SUM IF, kasutades Exceli otsinguväärtusi.
Lihtsamalt öeldes peame erinevate andmetabelitega töötades mõnikord arvutama teise tabeli esimese tabeli väärtuste väärtused. Tingimus on saada esimese tabeli väärtuste summa, kasutades teise tabeli väärtusi
Selle artikli jaoks vajame järgmiste funktsioonide kasutamist:
- Funktsioon SUMPRODUCT
- SUMIF funktsioon
Funktsioon SUMPRODUCT on Exceli matemaatiline funktsioon. See töötab mitmes vahemikus. See korrutab vastavad massiivid ja lisab need seejärel.
Kokku = (A1 * B1 * C1 *… + A2 * B2 * C2 *… +…)
Süntaks:
= SUMPRODUCT (massiiv1, [massiiv2],…)
massiiv: see on väärtuste vahemik või loend.
Funktsioon SUMIF Excelis tagastab määratud kriteeriumidele vastavate numbrite summa. SUM vahemik ja kriteeriumide vahemik võib olla sama või erinev.
Üldine Exceli SUMIF -valem:
= SUMIF (kriteeriumivahemik, kriteeriumid, summa_vahemik)
kriteeriumivahemik: vahemik, kus tingimust rakendatakse.
kriteeriumid: tingimus, mida rakendada kriteeriumide_vahemikus.
summa_vahemik: summa vahemik kriteeriumide alusel.
Nüüd teeme neist funktsioonidest valemi. Siin anname andmed ja vajasime summa tulemusi, kus väärtus vastab otsingutabeli väärtusele.
Üldine valem:
= SUMPRODUCT (SUMIF (tulemus, kirjed, summa_numbrid))
tulemus : otsingutabelis vastendatavad väärtused
rekord : väärtused esimeses tabelis
sum_nums : väärtused, mis summeeritakse vastavate väärtuste järgi.
Katsetame seda valemit näite abil
Siin arvutame jalgpalli mängiva meeskonna kogupunktid punktitabeli või otsingutabeli abil.
Siin on tulemus (B8: B10) nimega vahemik ja punktid (C8: C10) nimega vahemik.
Nüüd kasutame allolevat valemit, et saada F3 lahtrisse Kuu1 punktide summa
Kasutage valemit:
= SUMPRODUCT (SUMIF (tulemus, B3: E3, punktid))
Selgitus:
- Funktsioon SUMIF sobitab tulemuse väärtustega B3: E3.
- Seejärel tagastatakse tulemusele vastavad punktid funktsioonile SUMPRODUCT.
- Funktsioon SUMPRODUCT tagastab funktsioonile massiivina antud väärtuste SUM.
Nagu näete siit, on meeskonna esimesel kuul kogutud 7 punkti.
3 (W) + 0 (L) + 1 (D) + 3 (W) = 7 punkti
Kopeerige valem teistesse lahtritesse, valige lahtrid, mis võtavad esimese lahtri, kus valem on juba rakendatud, kasutage kiirklahvi Ctrl + D või kasutage Exceli lohistamisvalikut.
Nagu ülaltoodud hetktõmmist näha, on nelja erineva kuu jooksul kogutud punktid.
Hankige kõigi punktide summa 4 kuu jooksul.
See tähendab, et meeskond viskas nelja kuuga kokku 18 punkti.
Alternatiivne meetod
Kasutage funktsiooni SUMPRODUCT asemel SUM ja massiivi meetodit.
Kasutage valemit:
{= SUM (SUMIF (tulemus, B3: E3, punktid))}
Lokkisulgud on massiivi meetod, mis soovitab funktsioonil arvutada kogu tagastatud väärtus SUMIF -funktsiooni abil.
Märkus: Ärge tehke kasutage lokkis trakside sümboleid käsitsi. Kasuta Ctrl + Tõstuklahv + Enter massiivi meetodi kasutamiseks Excelis.
Nagu ülaltoodud valemist näete, saate tingimusliku tabeli abil kogupunkte.
Märkused:
- Valem töötab ainult numbritega.
- Valem töötab ainult siis, kui otsingutabelis pole duplikaate
- Funktsioon SUMPRODUCT loeb mittearvulised väärtused 0 -deks.
- Funktsioon SUMPRODUCT loeb loogilise väärtuse TRUE väärtuseks 1 ja vale väärtuseks 0.
- Argumendimassiiv peab olema sama pikkusega, muidu funktsioon
Loodan, et see artikkel selgitab SUM -i tagastamist ainult Exceli valemitest. Siit leiate rohkem artikleid funktsioonide SUMPRODUCT kohta. Palun jagage oma päringut allpool kommentaaride kastis. Me aitame teid.
Funktsiooni SUMPRODUCT kasutamine Excelis: Tagastab SUM -i pärast väärtuste korrutamist mitmes Exceli massiivis.
SUM, kui kuupäev jääb vahele : Tagastab Exceli kuupäevade või perioodide vaheliste väärtuste SUMMA.
Summa, kui kuupäev on antud kuupäevast suurem: Tagastab väärtuste SUM pärast Excelis antud kuupäeva või perioodi.
2 võimalust Excelis iga kuu summeerimiseks: Tagastab Exceli konkreetse kuu väärtuste summa.
Kuidas liita mitu veergu olekuga: Tagastab väärtuste SUM väärtuse mitmes veerus, mille tingimus on Excelis
Populaarsed artiklid
50 Exceli otsetee tootlikkuse suurendamiseks
Muutke ripploendit
Absoluutne viide Excelis
Kui tingimusvorminguga
Kui metamärkidega
Vlookup kuupäeva järgi
Teisendage tollid Excelis 2016 jalgadeks ja tollideks
Liituge Excelis ees- ja perekonnanimega
Loendage lahtrid, mis vastavad kas A või B.