Kaip jau minėjau daugelyje savo tinklaraščių, SUMPRODUCT yra labai universali funkcija ir gali būti naudojama įvairiems tikslams. Šiame straipsnyje pamatysime, kaip galime naudoti šią funkciją skaičiuoti vertes su keliais ar kriterijais.
Bendra „SUMPRODUCT“ formulė, kurią reikia skaičiuoti naudojant kelis ar kriterijus
| = SUMPRODUCT (-(((kriterijus1)+(kriterijus2)+…)> 0) |
Kriterijai1: Tai yra bet koks kriterijus, kuris grąžina TIESA ir NETIESA masyvą.
Kriterijai2: Tai yra kiti kriterijai, kuriuos norite patikrinti. Panašiai galite turėti tiek kriterijų, kiek norite.
Aukščiau pateikta bendroji formulė dažnai modifikuojama, kad atitiktų reikalavimus, susijusius su skaičiavimu pagal kelis OR kriterijus. Tačiau pagrindinė formulė yra tokia. Pirmiausia pamatysime, kaip tai veikia per pavyzdį, o po to aptarsime kitus scenarijus, kuriuose turėsite šiek tiek pakeisti šią formulę.
Problema:
Kainą langelyje reikia tiksliai padalyti į 5 panašius mokėjimus. Tiesiog padalijus kainą 88,04 USD iš 5 ir apvalinant rezultatą iki dviejų skaičių po kablelio, gauname 17,61 USD individualius mokėjimus. Tačiau tai sudarytų bendrą 88,05 USD mokėjimą, o tai nėra tiksli suma, nuo kurios pradedame. Todėl norime apskaičiuoti 5 panašius skaičius, kurie sudaro bendrą sumą.
Sprendimas:
Pirmiausia apskaičiuokite pirmąjį mokėjimą, padaliję kainą iš 5 ir suapvalinę rezultatą.
Naudokite funkciją ROUND pagal šią formulę:
= RUNAS (B1/5,2)
2 žingsnis:
Apskaičiuokite kiekvieną likusį mokėjimą.
Naudokite ROUND, SUM ir ROW funkcijas pagal šią formulę:
= ROUND (($ B $ 1-SUM ($ B $ 2: B2))/(5-ROW ()+ROW ($ B $ 2)), 2)
Pavyzdys:
Naudodami „SUMPRODUCT“ suskaičiuokite vartotojus, jei prekiautojo kodas atitinka metus ar metus
Taigi čia mes turime pardavėjų duomenų rinkinį. Duomenyse yra daug stulpelių. Turime suskaičiuoti, kiek vartotojų turi kodą „INKA“ arba metai yra „2016“. Įsitikinkite, kad jei kas nors turi abu (kodas „inka“ ir 2016 m.), Jie bus skaičiuojami kaip 1.
Taigi, girdime, turime du kriterijus. Mes naudojame aukščiau paminėtą SUMPRODUCT formulę:
| = SUMPRODUCT (-(((kodas = I3)+(metai = K3))> 0)) |
Čia kodas ir metai yra pavadinti diapazonai. Tai grąžina 7.
Duomenyse turime 5 INKA kodo įrašus ir 4 2016 metų įrašus. Tačiau 2 įrašuose yra ir „INKA“, ir 2016 metai, atitinkamai kaip kodas ir metai. Ir šie įrašai skaičiuojami kaip 1. Ir taip gauname 7.
Pažiūrėkime, kaip tai veikia?
Taigi pažvelkime, kaip formulė išspręsta žingsnis po žingsnio, tada aptarsiu, kaip ji veikia.
-
- = SUMPRODUCT(-((((kodas = I3)+(metai = K3))> 0))
- SUMPRODUCT(-(({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE;…}+{FALSE; FALSE; FALSE; TRUE; TRUE;…})> 0))
- SUMPRODUCT(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0))
- SUMPRODUCT(-({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE;…})
- SUMPRODUCT({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})
7
Dvigubi neigiami (-) ženklai naudojami loginėms reikšmėms konvertuoti į 1s ir 0s. Taigi kiekviena masyvo TRUE reikšmė paverčiama 1, o FALSE - 0.
Daugiau ar kriterijų pridėjimas skaičiuojant naudojant SUMPRODUCT, taigi, jei reikia pridėti daugiau ar kriterijų, kuriuos reikia suskaičiuoti, galite tiesiog pridėti kriterijus naudodami funkciją +.
Pavyzdžiui, jei norite pridėti dar vieną kriterijų prie aukščiau pateiktos formulės, kad ji pridėtų daugiau nei 5 produktus pardavusių darbuotojų skaičių. SUMPRODUCT formulė paprasčiausiai atrodys taip:
| = SUMPRODUCT (-(((kodas = I3)+(metai = K3)+(pardavimai> 5))> 0)) |
Paprasta! ar ne?
Bet tarkime, kad norite turėti du kriterijus iš kodo diapazono. Tarkime, kad norite suskaičiuoti „INKB“. Tada kaip tai padaryti? Vienas iš būdų yra naudoti aukščiau aprašytą metodą, tačiau tai kartotųsi. Tarkime, noriu pridėti dar 10 kriterijų iš to paties diapazono. Tokiais atvejais ši technika nėra tokia protinga skaičiuojant naudojant SUMPRODUCT.
Tarkime, kad mes turime tokius duomenis.
Kriterijų kodai yra vienoje eilutėje I2: J2. Čia svarbus duomenų išdėstymas. SUMPRODUCT formulė 3 ARBA kriterijų skaičiavimui bus tokia:
| = SUMPRODUCT (-((((kodas = I2: J2)+(metai = I3: J3))> 0)) |
Tai SUMPRODUCT formulė, kurią reikia skaičiuoti su keliais kriterijais, kai eilutėje rašomi keli kriterijai iš vieno diapazono.
Aukščiau pateikta formulė grąžina teisingą atsakymą, kuris yra 10.
Jei J3 įvesite bet kuriuos metus, formulė taip pat pridės tą skaičių.
Tai naudojama, kai kriterijai yra vienoje eilutėje. Ar tai veiks, kai to paties diapazono kriterijai viename stulpelyje? Ne. Nebus.
Šiame pavyzdyje turime suskaičiuoti kelis kodus, tačiau šie tipo kodai parašyti viename stulpelyje. Kai naudojame aukščiau pateiktą SUMPRODUCT formulę, gauname klaidą ans #N/A. Nesigilinsime, kaip atsiranda ši klaida, nes dėl to šis straipsnis yra per ilgas. Pažiūrėkime, kaip mes galime tai padaryti.
Kad ši formulė veiktų, turite įvesti kodo kriterijus į funkciją TRANSPOSE. Dėl to formulė veiks.
| = SUMPRODUCT (-(((kodas = TRANSPOSE (H3: H4))+(metai = TRANSPOSE (I3: I4)))> 0)) |
Tai formulė, skirta skaičiuoti naudojant kelis ar sąlygas tame pačiame diapazone, kai kriterijai yra išvardyti stulpelyje.
Čia yra visos pastabos, susijusios su formulės naudojimu.
Pastabos:
- Formulė tinka ir tekstui, ir skaičiams.
- Ieškomos vertės gali būti pateiktos kaip langelio nuoroda arba tiesiogiai formulėje kaip argumentai naudojant citatos simbolį (").
Tikimės, kad supratote, kaip sumą padalyti į lygias išmokas, išvengiant „Excel“ padalijimo / apvalinimo klaidų. Čia rasite daugiau straipsnių apie „Excel“ paieškos vertę ir „Excel 2019“ funkcijas. Jei jums patiko mūsų tinklaraščiai, pasidalykite jais su draugais „Facebook“. Taip pat galite sekti mus „Twitter“ ir „Facebook“. Mes norėtume išgirsti jūsų nuomonę, praneškite mums, kaip galime patobulinti, papildyti ar naujovinti savo darbą ir padaryti jį geresnį. Rašykite mums el.
Norėdami ieškoti vertės, naudokite INDEX ir MATCH : Funkcija INDEX & MATCH, jei reikia, ieškoti vertės.
SUM diapazonas su „INDEX“ programoje „Excel“ : Naudokite funkciją INDEX, kad surastumėte reikiamų verčių SUM.
Kaip naudoti „INDEX“ funkciją programoje „Excel“ : Raskite masyvo INDEKSĄ naudodami funkciją INDEX, paaiškintą pavyzdžiu.
Kaip naudoti funkciją „MATCH“ programoje „Excel“ : Raskite MATCH masyve naudodami INDEX reikšmę MATCH funkcijos viduje, paaiškinta pavyzdžiu.
Kaip naudoti „LOOKUP“ funkciją „Excel“ : Raskite paieškos reikšmę masyve naudodami funkciją LOOKUP, paaiškinta pavyzdžiu.
Populiarūs straipsniai:
Kaip naudoti „IF“ funkciją „Excel“ : „Excel“ IF sakinys patikrina sąlygą ir grąžina konkrečią reikšmę, jei sąlyga yra TRUE, arba grąžina kitą konkrečią reikšmę, jei FALSE.
Kaip naudotis „VLOOKUP“ funkcija „Excel“ : Tai yra viena iš dažniausiai naudojamų ir populiariausių „Excel“ funkcijų, naudojama vertei iš įvairių diapazonų ir lapų ieškoti.
Kaip naudoti „SUMIF“ funkciją „Excel“ : Tai dar viena esminė prietaisų skydelio funkcija. Tai padeda apibendrinti vertes konkrečiomis sąlygomis.
Kaip naudoti „COUNTIF“ funkciją „Excel“ : Skaičiuokite vertes su sąlygomis naudodami šią nuostabią funkciją. Norint suskaičiuoti konkrečias vertes, nereikia filtruoti duomenų. Skaitiklio funkcija yra būtina norint paruošti prietaisų skydelį.