Šiame straipsnyje mes išmoksime skaičiuoti „Excel“ eilutes pagal kriterijus SUMPRODUCT.
Scenarijus:
Paprastais žodžiais tariant, dirbdami su duomenimis, kartais turime suskaičiuoti ląsteles, kuriose 2 diapazonai atitinka kriterijus. Sąlyginį skaičiavimą galima atlikti naudojant funkciją COUNTIF, tačiau tai nėra vienintelė funkcija, galinti tai padaryti. Funkcija SUMPRODUCT yra universali funkcija, kurią galima naudoti skaičiuojant pagal kriterijus, kartais net geriau nei skaitiklio funkcija.
Kaip išspręsti problemą?
Šiame straipsnyje mes turėsime naudoti SUMPRODUCT funkciją. Dabar iš šių funkcijų sudarysime formulę. Čia mums pateikiami du diapazonų duomenys ir turime suskaičiuoti vertes, kuriose formulė atitinka kriterijus.
Bendra formulė:
= SUMPRODUCT ( - (diapazonas1 operatorius diapazonas2))
diapazonas1 : 1 diapazonas
diapazonas2 : 2 diapazonas
operatorius: kriterijai, sąlyga, nurodyta kaip operatorius tarp 2 diapazonų
Pavyzdys:
Visa tai gali būti painu suprasti. Taigi, išbandykime šią formulę, paleisdami ją žemiau pateiktame pavyzdyje.
Čia yra paskutinių 7 didelių „Premier“ lygos fanų klubų rungtynių rekordas.
Turime paskutinių 7 rungtynių rekordą: namų komanda, prieš komandą, namai įmušė ir priešininkas įmušė įvarčius.
Pirma, turime rasti bet kurios komandos namuose laimėtų žaidimų skaičių.
Dabar mes naudosime šią formulę, kad apskaičiuotume namuose laimėjusių žaidimų skaičių.
Naudokite formulę:
= SUMPRODUCT ( - (E5: E11> F5: F11))
E5: E11 : 1 -asis diapazonas, namuose įmušti įvarčiai
F5: F11 : 2 -as nuotolis, varžovas įmušė įvarčius
>: kriterijai, sąlyga nurodyta didesnė nei operatorius
Paaiškinimas:
- Sąlyga, kurią reikia tenkinti, yra tai, kad namų įmušti įvarčiai turi būti didesni nei priešininko įmušti.
- E5: E11 (diapazonas1) turi būti didesnis nei F5: F11 (diapazonas2).
- -- operatorius, kuris TRUE reikšmę pavertė 1, o klaidingą - 0.
- Funkcija SUMPRODUCT grąžintame masyve gauna 1 s sumą, kuri bus reikalingų laimėjimų skaičius.
Čia diapazonas pateikiamas kaip langelio nuoroda. Norėdami gauti skaičių, paspauskite „Enter“.
Kaip matote, visos rungtynės, kuriose šeimininkai turėjo pranašumą ir laimėjo, yra 4 rungtynės.
Aukščiau pateiktas pavyzdys buvo lengvas. Taigi, kad būtų įdomu, pagal visus duomenis suskaičiuosime, kiek rungtynių komanda laimėjo.
Pirma, turime rasti bet kurios komandos namuose laimėtų žaidimų skaičių.
Dabar, norėdami gauti žaidimų skaičių, naudosime šią formulę, Žmogau. „United“ laimėjo.
Naudokite formulę:
= SUMPRODUCT ( - (C5: C11 = G5) * (E5: E11> F5: F11) + (D5: D11 = G5) * (E5: E11 <F5: F11))
Paaiškinimas:
- (C5: C11 = G5) * (E5: E11> F5: F11) tikrina namų komandą kaip Žmogus. „United“ ir įmušė daugiau įvarčių nei varžovų komanda.
- (D5: D11 = G5) * (E5: E11 <F5: F11) tikrina prieš komandą kaip žmogus. „United“ ir įmušė daugiau įvarčių nei namų komanda.
- Funkcija SUMPRODUCT grąžintame masyve gauna 1s sumą, kuri bus reikalingų laimėjimų skaičius. „United“.
Čia diapazonas pateikiamas kaip langelio nuoroda. Norėdami gauti skaičių, paspauskite „Enter“.
Kaip matote iš viso rungtynių, kuriose Žmogus. „United“ komanda laimėjo 2 rungtynes.
Štai keletas stebėjimo pastabų, parodytų žemiau.
Pastabos:
- Formulė veikia tik su skaičiais.
- Formulė veikia tik tada, kai peržiūros lentelėje nėra dublikatų
- Funkcija SUMPRODUCT neskaitines reikšmes laiko 0s.
- Funkcija SUMPRODUCT loginę reikšmę TRUE laiko 1, o klaidingą - 0.
- Argumentų masyvas turi būti tokio pat ilgio kaip funkcija
Tikimės, kad šis straipsnis apie tai, kaip grąžinti skaičių, jei naudojant „SUMPRODUCT“ programoje „Excel“, yra aiškinamasis. Daugiau straipsnių apie SUMPRODUCT funkcijas rasite čia. Prašome pasidalinti savo užklausa žemiau komentarų laukelyje. Mes jums padėsime.
Kaip naudoti „SUMPRODUCT“ funkciją programoje „Excel“: Grąžina SUM, padauginus reikšmes keliuose „Excel“ masyvuose.
COUNTIFS su dinaminių kriterijų diapazonu : Skaičiuokite ląsteles, priklausančias nuo kitų „Excel“ langelių reikšmių.
„COUNTIFS“ atitinka du kriterijus: Skaičiuokite langelius, atitinkančius du skirtingus „Excel“ sąrašo kriterijus.
COUNTIFS su keliais kriterijais arba : Suskaičiuokite langelius, kurie turi kelis kriterijus, kurie atitinka, naudojant funkciją OR.
„Excel“ funkcija COUNTIFS : Skaičiuokite ląsteles, priklausančias nuo kitų ląstelių reikšmių.
Kaip naudoti „Countif“ VBA „Microsoft Excel“ : Skaičiuokite langelius naudodami „Visual Basic for Applications“ kodą.
Kaip „Excel“ naudoti pakaitos simbolius : Suskaičiuokite frazes atitinkančias frazes naudodami „Excel“ pakaitos simbolius
Populiarūs straipsniai
50 „Excel“ nuorodų, skirtų produktyvumui padidinti
Redaguoti išskleidžiamąjį sąrašą
Absoliuti nuoroda „Excel“
Jei su sąlyginiu formatavimu
Jei su pakaitos simboliais
„Vlookup“ pagal datą
Konvertuokite colius į pėdas ir colius programoje „Excel 2016“
Prijunkite „Excel“ vardą ir pavardę
Suskaičiuokite A arba B atitinkančias ląsteles