Selles artiklis õpime, kuidas Excelis funktsiooni SUMPRODUCT ja SUMIFS asemel kasutada funktsiooni IF.
Stsenaarium:
Lihtsamalt öeldes peame pika hajutatud andmekogumiga töötades mõnikord leidma numbrite summa koos mõne kriteeriumiga. Näiteks palga summa leidmine konkreetses osakonnas või mitme kriteeriumi määramine kuupäeva, nimede, osakonna või isegi numbrite kohta, näiteks palgad alla väärtuse või kogus üle väärtuse. Selleks kasutate tavaliselt funktsiooni SUMPRODUCT või SUMIFS. Kuid te ei usuks, täidate sama funktsiooni Exceli põhifunktsiooni IF funktsiooniga.
Kuidas probleemi lahendada?
Peate mõtlema, kuidas see on võimalik, et teha IF -funktsiooni kasutades loogilisi toiminguid tabelimassiivide kohal. Kui funktsioon Excelis on väga kasulik, aitab see teil Excelis või mõnes muus kodeerimiskeeles läbi teha keerulisi ülesandeid. Funktsioon IF testib massiivi tingimusi, mis vastavad nõutavatele väärtustele, ja tagastab tulemuse massiivina, mis vastab tõelistele tingimustele 1 ja vale väärtuseks 0.
Selle probleemi lahendamiseks kasutame järgmisi funktsioone:
- Funktsioon SUM
- IF funktsioon
Me vajame neid ülaltoodud funktsioone ja mõningaid andmete töötamise põhitõdesid. massiivide loogilisi tingimusi saab rakendada loogiliste operaatorite abil. Need loogikaoperaatorid töötavad nii teksti kui ka numbrite kallal. Siin on üldine valem. { } lokkis traksid on maagiline tööriist IF -funktsiooniga massiivivalemite täitmiseks.
Üldine valem:
| { = SUM (KUI ((loogiline_1) * (loogiline_2) *… * (loogiline_n), summa_massiiv)) } |
Märkus: lokkis trakside jaoks ( { } ) Kasutamine Ctrl + Tõstuklahv + Enter kui töötate Excelis massiivide või vahemikega. See loob valemis vaikimisi lokkis traksid. ÄRGE proovige lokkis breketite märke kõvasti kodeerida.
Loogika 1: testib massiivi 1 tingimust 1
Loogika 2: testib massiivi 2 tingimust 2 ja nii edasi
summa_massiiv: massiiv, operatsioonisumma tehakse
Näide:
Kõiki neid võib segadusse ajada. Niisiis, katsetame seda valemit, kasutades seda allpool näidatud näitel. Siin on meil erinevatesse linnadesse tarnitud toodete andmed koos vastavate kategooriaväljade ja kogustega. Siin on meil andmed ja peame leidma Bostonisse saadetud küpsiste koguse, mille kogus on suurem kui 40.
Andmetabel ja kriteeriumitabel on näidatud ülaltoodud pildil. Mõistmise huvides kasutasime kasutatud massiivide jaoks nimega vahemikke. Nimetatud vahemikud on loetletud allpool.
Siin:
Massiivi A2 jaoks määratletud linn: A17.
Massiivi B2 jaoks määratletud kategooria: A17.
Massiivi C2 jaoks määratletud kogus: C17.
Nüüd olete valmis soovitud tulemuse saamiseks kasutama järgmist valemit.
Kasutage valemit:
| { = SUM (IF ((linn = "Boston") * (kategooria = "küpsised") * (kogus> 40), kogus)) } |
Selgitus:
- City = "Boston": kontrollib linnavahemiku väärtusi, et need vastaksid "Bostonile".
- Kategooria = "Küpsised": kontrollib kategooriavahemiku väärtusi, et need vastaksid "Küpsistele".
- Kogus> 40: kontrollib väärtusi vahemikus Kogus vahemikus ma
- Kogus on massiiv, kus summa on nõutav.
- Funktsioon IF kontrollib kõiki kriteeriume ja tärniga char (*) korrutatakse kõik massiivi tulemused.
= SUM (KUI ({0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0}, {33; 87; 58; 38; 54; 51; 28; 36; 28; 44; 23; 27; 43; 42; 33; 30}))
- Nüüd tagastab funktsioon IF ainult 1 -le vastavad kogused ja ülejäänud eiratakse.
- Funktsioon SUM tagastab SUM.
Nüüd lisandub 1 -le vastav kogus ainult tulemuse saamiseks.
Nagu näete, tagastatakse kogus 43, kuid Bostonisse toimetatakse kolm küpsisetellimust, mille kogus on 38, 36 ja 43. Vajasime koguse summat, mille kogus ületab 40. Seega valem tagastab ainult 43. Nüüd kasutage linna SUM koguse saamiseks muid kriteeriume: "Los Angeles" ja kategooria: "Baarid" ja kogus alla 50.
Kasutage valemit
| { = SUM (IF ((linn = "Los Angeles") * (kategooria = "baarid") * (kogus <50), kogus)) } |
Nagu näete, tagastab valem selle tulemusel väärtused 86. See on kahe tellimuse summa, mis vastavad tingimustele, mille kogus on 44 ja 42. See artikkel illustreerib, kuidas asendada pesastatud IF -valem massiivivalemis ühe IF -ga. Seda saab kasutada keeruliste valemite keerukuse vähendamiseks. Selle konkreetse probleemi saab aga hõlpsalt lahendada funktsiooniga SUMIFS või SUMPRODUCT.
Funktsiooni SUMPRODUCT kasutamine:
Funktsioon SUMPRODUCT tagastab massiivi vastavate väärtuste summa. Seega saame massiivid tagastada 1s a tõese lause väärtused ja 0 s vale väite väärtused. Nii et viimane summa vastab kõikidele väidetele tõele.
Kasutage valemit:
| = SUMPRODUCT ( - (Linn = "Boston"), - (Kategooria = "Küpsised"), - (Kogus> 40), Kogus) |
-: toiming, mida kasutatakse kõigi tõeväärtuste teisendamiseks 1-ks ja vale väärtuseks 0.
Funktsioon SUMPRODUCT kontrollib uuesti eespool selgitatud funktsiooni SUM ja IF tagastatud koguste summat.
Teise näite puhul on tulemus sama.
Nagu näete, saab funktsioon SUMPRODUCT sama ülesande täita.
Siin on kõik tähelepanekud valemi kasutamise kohta.
Märkused:
- Summaarray valemis töötab ainult numbritega.
- Kui valem tagastab vea #VALUE, kontrollige, kas lokkis traksid peavad valemis olema, nagu on näidatud artiklis.
- Eitus (-) sümbol muudab väärtused TRUE või 1s väärtuseks FALSEs või 0s ja FALSEs või 0s väärtuseks TRUEs või 1s.
- Sellised toimingud on võrdsed ( = ), väiksem kui ( <= ), suurem kui ( > ) või ei ole võrdne () saab teostada rakendatud valemis, ainult numbritega.
Loodan, et see artikkel selle kohta, kuidas Excelis funktsiooni SUMPRODUCT ja SUMIFS asemel kasutada IF -d, on selgitav. Siit leiate rohkem artikleid summeerimisvalemite 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
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 : Saate oma ülesandega kiiremini hakkama. Need 50 otseteed aitavad teil Excelis veelgi kiiremini töötada.
Kuidas kasutada tta funktsioon VLOOKUP Excelis : See on üks enim kasutatud ja populaarsemaid Exceli funktsioone, mida kasutatakse erinevate vahemike ja lehtede väärtuse otsimiseks.
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.
Funktsiooni SUMIF kasutamine Excelis : See on veel üks armatuurlaua oluline funktsioon. See aitab teil teatud tingimustel väärtusi kokku võtta.