Excel: arvutage õigesti tingimuslikud summad
Excelis saate arvutada summasid, mis ei võta arvesse kõiki valitud ala elemente - nn tingimuslikud summad. Näitame teile, kuidas teha kõige mõistlikum arvutus.
Kolm erinevat funktsiooni tingimuslike summade jaoks Excelis
Excel pakub tingimuslike kogusummade jaoks erinevaid võimalusi ja funktsioone. Õige valik sõltub tingimuste tüübist:
- Kui igale kõnealusele väärtusele kehtib fikseeritud tingimus, kasutage funktsiooni SUMIF.
- Kui tuleb arvestada mitmete tingimustega, on ka SUMIFS-i funktsioon versioonis Excel 2007.
- Muutuvate tingimuste korral on teil vaja maatriksvalemit või funktsiooni SUM PRODUCT.
- Järgmistes lõikudes selgitame kolme funktsiooni näidete abil.
SUMIF: fikseeritud tingimustega tingimuslikud summad
- Oletame, et andmed on lahtrites A1 kuni A10 ja tingimused rakkudes B1 kuni C10 erinevad.
- Esimene fikseeritud tingimusega ülesanne tähendab näiteks kõigi veeru A väärtuste liitmist, mille vastav veeru B väärtus on ligikaudu suurem kui 10.
- Selleks kasutage valemit »= SUMIF (B1: B10;"> 10 "; A1: A10)«.
- Esimene argument »B1: B10« määratleb hinnatava ala, millele järgneb tingimus »"> 10 "«. Pange tähele, et sümbolite või operaatoritega tingimused peavad olema jutumärkides.
- Lõpuks määratleb »A1: A10« summeeritava ala.
- Tingimuste ja kuupäevade vahemikud võivad samuti olla identsed, nii et valem »= SUMIF (A1: A10;"> 10 ")« on piisav.
SUM IF: mitme tingimusega tingimuslikud summad
Alates versioonist Excel 2007 laiendab SUMIFS-i funktsioon seda põhimõtet mitmele kuni 127 tingimusele:
- Pange tähele argumentide teist järjekorda. Summeeritav ala asub siin kõigepealt. Sellele järgneb kriteeriumide piirkond ja sellega seotud tingimus, kusjuures alad tuleb alati eraldi määratleda.
- Näiteks valem »= SUMIFS (A1: A10; B1: B10;"> 10 "; C1: C10;" <100 ")" võtab kõik veeru A väärtused kokku, mille vastavad veeru B väärtused on suuremad kui 10 ja vastavad veeru C väärtused on alla 100.
SUM PRODUCT: Tingimuslik summa muutuvate tingimustega
Muutuvad kriteeriumid, näiteks "kui veeru B väärtus on suurem kui veeru C väärtus", nõuab hindamiseks maatriksvalemit:
- Arvesse võetavad väärtused valitakse korrutades loogilise väärtusega.
- Selleks sisestage umbes "= SUM (A1: A10 * (B1: B10> C1: C10))" ja sulgege kirje alati, isegi pärast muutmist klahvikombinatsiooniga [Ctrl] + [Shift] + [Enter], nii et valem on traksidega.
- Sel juhul saate sama tulemuse saavutada funktsiooniga "= SUM PRODUCT (SUM PRODUCT (A1: A10; 1 * (B1: B10> C1: C10))").
- Siiski peate arvestama, et esmalt tuleb teine argument korrutada 1-ga, et teisendada võrdluse loogiline väärtus numbriks.
Märkus. Praktiliselt kõigil juhtudel saate summa selgelt luua lisakolonni abil. See on soovitatav ka arvutuse väljatöötamisel keerukate valemite tulemuste kontrollimiseks. Näpunäide. Siin selgitame, kuidas Excelis paljusid lahtrid lisada.