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 OR kriterijais.
Bendra „SUMPRODUCT“ formulė, kurią reikia skaičiuoti naudojant kelis ar kriterijus
| = SUMPRODUCT (-(((kriterijai1)+(kriterijai2)+… )>0) |
1 kriterijus: Tai yra bet koks kriterijus, kuris grąžina TIESA ir NETIESA masyvą.
2 kriterijus: Tai yra kitas kriterijus, kurį 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ę.
Pavyzdys: suskaičiuokite vartotojus, jei prekiautojo kodas ar metaiDegtukai Naudojant SUMPRODUCT
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.), Jis turėtų būti skaičiuojamas kaip 1.
Taigi, mes 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. Šie įrašai skaičiuojami kaip 1. Ir taip gauname 7.
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)) |
| 1=>SUMPRODUCT(-(({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE;…}+{FALSE; FALSE; FALSE; TRUE; TRUE;…})> 0)) |
| 2=>SUMPRODUCT(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0)) |
| 3=>SUMPRODUCT(-({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE;…}) |
| 4=>SUMPRODUCT({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0}) |
| 5=>7 |
Pirmajame etape I3 („INKA“) vertė lyginama su kiekvienu kodo diapazono langeliu. Tai grąžina TIESA ir NETIESA masyvą. TIKRA kiekvienos rungtynės. Norėdami sutaupyti vietos, nerodžiau visų TIESIOGIŲ. Panašiai K3 (2016) vertė yra suderinama su kiekvienu metų diapazono langeliu.
Kitame žingsnyje pridedame šiuos du masyvus, kurie sukuria naują skaitinių verčių masyvą. Kaip žinote, „Excel“ programoje TRUE yra laikoma 1, o FALSE - 0. Taigi, pridėjus TRUE ir TRUE, gauname 2, o likusią dalį galite suprasti.
Kitame žingsnyje mes patikriname, kuri vertė yra didesnė už 0 masyve. Tai vėl paverčia masyvą į tikrą klaidingą masyvą. Kiekvienai gautai 0 reikšmei False ir poilsis konvertuojami kaip tiesa. Dabar mūsų atsakymas yra TIKRŲ reikšmių skaičius masyve. Bet kaip mes juos skaičiuojame? Štai kaip.
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.
Paskutiniame etape „SUMPRODUCT“ apibendrina šį masyvą ir mes gauname atsakymą kaip 7.
Daugiau ar kriterijų pridėjimas skaičiuojant naudojant SUMPRODUCT
Taigi, jei reikia pridėti daugiau ar kriterijų, kad galėtumėte suskaičiuoti, galite tiesiog pridėti kriterijus naudodami funkciją + ženklas.
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 Kodas diapazonas. Tarkime, kad norite suskaičiuoti „INKB“. Taigi 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 3 ARBA kriterijų skaičiavimo nustatymų formulė bus tokia:
| = SUMPRODUCT (-((((kodas = I2: J2)+(metai = I3: J3))> 0)) |
Tai SUMPRODUCT formulė, kurią reikia skaičiuoti naudojant kelis kriterijus, kai iš eilės rašomi keli kriterijai iš vieno diapazono.
Tai 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 kriterijai yra viename to paties diapazono 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 atsirado ši klaida, nes dėl to šis straipsnis bus 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.
Taigi taip, drauge, tikiuosi, kad buvau pakankamai aiškus ir tai buvo prasminga. Tikiuosi, kad tai tarnauja jūsų tikslui būti čia. Jei ši formulė neišsprendė jūsų problemos, praneškite man apie savo reikalavimus žemiau esančiame komentarų skyriuje. Man bus malonu bet kokiu būdu jums padėti. Galite paminėti bet kokias su abejonėmis susijusias „Excel“/VBA. Iki tol mokykis, tobulėk.
Kaip naudoti „SUMPRODUCT“ funkciją programoje „Excel“: Grąžina SUM, padauginus reikšmes keliuose „Excel“ masyvuose. Ši funkcija gali būti naudojama kelioms užduotims atlikti. Tai viena iš universaliausių funkcijų.
COUNTIFS su dinaminių kriterijų diapazonu : Norėdami skaičiuoti pagal dinaminį kriterijų diapazoną, mes tiesiog naudojame INDIRECT funkciją. Ši funkcija gali
COUNTIFS su keliais kriterijais arba : Suskaičiuokite langelius, kurie turi kelis kriterijus, kurie atitinka, naudojant funkciją OR. Norėdami įtraukti ARBA logiką į COUNTIFS funkciją, jums nereikės naudoti funkcijos OR.
IF naudojimas su AND ir OR funkcijomis „Microsoft Excel“ : Šios loginės funkcijos naudojamos kelių kriterijų skaičiavimams atlikti. Jei IF, OR ir AND funkcijos naudojamos atitiktims įtraukti arba neįtraukti.
Kaip naudoti „OR“ funkciją „Microsoft Excel“ : Ši funkcija naudojama įtraukiant visas TRUE reikšmes į kelis kriterijus.
Kaip skaičiuoti ląsteles, kuriose yra šio ar to, „Excel“ programoje „Excel“ : Ląstelėms, kuriose yra tas ar kitas, galime naudoti funkciją SUMPRODUCT. Štai kaip jūs atliekate šiuos skaičiavimus.
Populiarūs straipsniai:
50 „Excel“ nuorodų, skirtų produktyvumui padidinti | Greičiau atlikite savo užduotį. Šie 50 sparčiųjų klavišų leis dar greičiau dirbti naudojant „Excel“.
Kaip naudotis „Excel VLOOKUP“ funkcija| Tai yra viena iš dažniausiai naudojamų ir populiariausių „Excel“ funkcijų, naudojama norint ieškoti vertės iš įvairių diapazonų ir lapų.
Kaip naudotis „Excel“ Funkcija COUNTIF| Naudodami šią nuostabią funkciją, suskaičiuokite reikšmes su sąlygomis. Norint suskaičiuoti konkrečias vertes, nereikia filtruoti duomenų. Skaitiklio funkcija yra būtina norint paruošti prietaisų skydelį.
Kaip naudoti „SUMIF“ funkciją „Excel“ | Tai dar viena esminė prietaisų skydelio funkcija. Tai padeda apibendrinti vertes konkrečiomis sąlygomis.