Ši pamoka parodo, kaip naudotis Išskirtinisel SUMIF ir SUMIFS Funcjoms „Excel“ ir „Google“ skaičiuoklėse, kad būtų galima apibendrinti duomenis, atitinkančius tam tikrus kriterijus.
SUMIF funkcijų apžvalga
Galite naudoti „SUMIF“ funkciją programoje „Excel“, kad susumuotumėte langelius, kuriuose yra tam tikra reikšmė, sumuotumėte langelius, kurie yra didesni arba lygūs reikšmei ir pan.
(Atkreipkite dėmesį, kaip atrodo formulės įvestys)
SUMIF funkcijos sintaksė ir argumentai:
| 1 | = SUMIF (diapazonas, kriterijai, [sum_range]) |
diapazonas - Ląstelių diapazonas, kuriam norite taikyti kriterijus.
kriterijai - kriterijai, naudojami norint nustatyti, kurias langelius pridėti.
sumos_diapazonas - [neprivaloma] Ląstelės, kurias reikia sudėti. Jei sumos diapazonas praleistas, diapazono langeliai sudedami kartu.
Kas yra SUMIF funkcija?
Funkcija SUMIF yra viena iš senesnių funkcijų, naudojamų skaičiuoklėse. Jis naudojamas skenuoti per langelių diapazoną, tikrinant konkretų kriterijų, ir tada pridedant reikšmes į tas reikšmes atitinkantį diapazoną. Pradinė SUMIF funkcija apsiribojo tik vienu kriterijumi. Po 2007 m. Buvo sukurta funkcija SUMIFS, kuri leidžia taikyti daugybę kriterijų. Didžioji dalis bendro naudojimo tarp jų išlieka ta pati, tačiau yra keletas esminių sintaksės skirtumų, kuriuos aptarsime šiame straipsnyje.
Jei to dar nepadarėte, galite peržiūrėti daug panašios struktūros ir pavyzdžių straipsnyje COUNTIFS.
Pagrindinis pavyzdys
Pažvelkime į šį įrašytų pardavimų sąrašą ir norime žinoti visas pajamas.
Kadangi turėjome išlaidų, neigiamos vertės, negalime padaryti tik pagrindinės sumos. Vietoj to, mes norime apibendrinti tik tas reikšmes, kurios yra didesnės nei 0. „Suma“ yra didesnis nei 0. Mūsų formulė tai pasakyti
| 1 | = SUMIF (A2: A7, "> 0") |
Dviejų stulpelių pavyzdys
Nors pradinė SUMIF funkcija buvo sukurta taip, kad galėtumėte taikyti kriterijų skaičių diapazonui, kurį norite sudėti, daugumą laiko turėsite taikyti vieną ar kelis kriterijus kitiems stulpeliams. Apsvarstykite šią lentelę:
Dabar, jei naudosime pradinę SUMIF funkciją norėdami sužinoti, kiek bananų turime (išvardytų D1 langelyje), turėsime nurodyti norimą diapazoną suma kaip paskutinis argumentas, ir tokia būtų mūsų formulė
| 1 | = SUMIF (A2: A7, D1, B2: B7) |
Tačiau kai programuotojai galiausiai suprato, kad vartotojai nori pateikti daugiau nei vieną kriterijų, buvo sukurta funkcija SUMIFS. Norint sukurti vieną struktūrą, kuri atitiktų bet kokį kriterijų skaičių, SUMIFS reikalauja, kad pirmiausia būtų nurodytas sumų diapazonas. Mūsų pavyzdyje tai reiškia, kad formulė turi būti tokia
| 1 | = SUMIFS (B2: B7, A2: A7, D1) |
PASTABA: Šios dvi formulės gauna tą patį rezultatą ir gali atrodyti panašiai, todėl atidžiai stebėkite, kuri funkcija naudojama, kad būtinai išvardytumėte visus argumentus teisinga tvarka.
Darbas su datomis, keli kriterijai
Dirbant su datomis skaičiuoklėje, nors datą galima įvesti tiesiai į formulę, geriausia, kad data būtų langelyje, kad galėtumėte tiesiog nurodyti formulės langelį. Pavyzdžiui, tai padeda kompiuteriui žinoti, kad norite naudoti datą 2020-05-27, o ne skaičių 5, padalytą iš 27, padalytą iš 2022 m.
Pažvelkime į kitą lentelę, kurioje kas dvi savaites įrašomas svetainės lankytojų skaičius.
D2 ir E2 galime nurodyti diapazono, į kurį norime žiūrėti, pradžios ir pabaigos taškus. Mūsų formulė, pagal kurią galima susumuoti šio diapazono lankytojų skaičių, gali būti tokia:
| 1 | = SUMIFS (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2) |
Atkreipkite dėmesį, kaip mums pavyko sujungti „=“ palyginimus su ląstelių nuorodomis, kad sukurtume kriterijus. Be to, nors abu kriterijai buvo taikomi tam pačiam langelių diapazonui (A2: A7), diapazoną turite parašyti du kartus, vieną kartą pagal kiekvieną kriterijų.
Keli stulpeliai
Kai naudojate kelis kriterijus, galite juos taikyti tame pačiame diapazone, kaip ir ankstesniame pavyzdyje, arba galite taikyti skirtingiems diapazonams. Sujunkime pavyzdinius duomenis į šią lentelę:
Nustatėme kai kuriuos langelius, kad vartotojas įvestų tai, ko nori ieškoti ląstelėse E2 – G2. Taigi mums reikia formulės, kuri sudarytų bendrą vasario mėnesį nuskintų obuolių skaičių. Mūsų formulė atrodo taip:
| 1 | = SUMIFS (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2) |
SUMIFS su OR tipo logika
Iki šiol visi mūsų naudojami pavyzdžiai buvo palyginimas AND ir ieškome eilučių, atitinkančių visus mūsų kriterijus. Dabar mes apsvarstysime atvejį, kai norite ieškoti galimybės, kad eilė atitiktų vieną ar kitą kriterijų.
Pažvelkime į šį pardavimo sąrašą:
Norėtume sudėti visus Adomo ir Bobo pardavimus. Norėdami tai padaryti, turite keletą variantų. Paprasčiausia yra pridėti du SUMIFS kartu, kaip nurodyta toliau:
| 1 | = SUMIFS (B2: B7, A2: A7, „Adomas“)+SUMIFS (B2: B7, A2: A7, „Bobas“) |
Čia kompiuteris apskaičiavo mūsų individualius balus ir tada juos sudėjo.
Kitas mūsų pasirinkimas tinka, kai turite daugiau kriterijų diapazonų, todėl nenorite, kad visa formulė turėtų būti pakartotinai perrašyta. Ankstesnėje formulėje rankiniu būdu liepėme kompiuteriui pridėti du skirtingus SUMIFS. Tačiau tai galite padaryti ir įvesdami kriterijus į masyvą, pavyzdžiui:
| 1 | = SUMA (SUMIFS (B2: B7, A2: A7, {"Adomas", "Bobas"})) |
Pažiūrėkite, kaip masyvas sukonstruotas garbanotų skliaustų viduje. Kai kompiuteris įvertins šią formulę, jis žinos, kad norime apskaičiuoti SUMIFS funkciją kiekvienam mūsų masyvo elementui, taip sukurdami skaičių masyvą. Išorinė SUM funkcija paims tą skaičių masyvą ir pavers jį vienu skaičiumi. Žvelgiant į formulės vertinimą, tai atrodytų taip:
| 123 | = SUMA (SUMIFS (B2: B7, A2: A7, {"Adomas", "Bobas"}))= SUM (27401, 43470)= 70871 |
Gauname tą patį rezultatą, tačiau sugebėjome formulę išrašyti kiek lakoniškiau.
Tvarkymasis su ruošiniais
Kartais jūsų duomenų rinkinyje yra tuščių langelių, kuriuos turite surasti arba jų išvengti. Šių kriterijų nustatymas gali būti šiek tiek sudėtingas, todėl pažvelkime į kitą pavyzdį.
Atminkite, kad langelis A3 yra tuščias, o A5 langelis turi formulę, grąžinančią nulinio ilgio eilutę „“. Jei norime rasti bendrą sumą nuoširdžiai tuščių langelių, mes naudojame kriterijų „=“, o mūsų formulė atrodytų taip:
| 1 | = SUMIFS (B2: B7, A2: A7, "=") |
Kita vertus, jei norime gauti sumą visoms ląstelėms, kurios vizualiai atrodo tuščios, pakeisime kriterijus į „“, o formulė atrodo taip
| 1 | = SUMIFS (B2: B7, A2: A7, "") |
Apverskime: ką daryti, jei norite rasti tuščių langelių sumą? Deja, dabartinis dizainas neleis išvengti nulio ilgio eilutės. Galite naudoti kriterijų „“, tačiau, kaip matote pavyzdyje, jis vis tiek apima 5 eilutės reikšmę.
| 1 | = SUMIFS (B2: B7, A2: A7, "") |
Jei reikia neskaičiuoti langelių, kuriuose yra nulinio ilgio eilučių, apsvarstykite galimybę naudoti funkciją LEN SUMPRODUCT
SUMIF „Google“ skaičiuoklėse
Funkcija SUMIF „Google“ skaičiuoklėse veikia taip pat, kaip ir „Excel“:
