Kā jau daudzos savos emuāros esmu minējis, ka SUMPRODUCT ir ļoti daudzpusīga funkcija un to var izmantot vairākiem mērķiem. Šajā rakstā mēs redzēsim, kā mēs varam izmantot šo funkciju, lai skaitītu vērtības ar vairākiem vai kritērijiem.
Vispārīga SUMPRODUCT formula, kas jāaprēķina ar vairākiem vai kritērijiem
| = SUMPRODUCT (-(((kritērijs1)+(kritērijs2)+…)> 0) |
Kritēriji1: Tas ir jebkurš kritērijs, kas atgriež masīvu ar patiesu un nepatiesu.
Kritēriji2: Šis ir nākamais kritērijs, kuru vēlaties pārbaudīt. Līdzīgi jums var būt tik daudz kritēriju, cik vēlaties.
Iepriekš minētā vispārīgā formula bieži tiek mainīta, lai tā atbilstu prasībām, kas jāsaskaita ar vairākiem VAI kritērijiem. Bet pamata formula ir šāda. Vispirms mēs redzēsim, kā tas darbojas, izmantojot piemēru, un pēc tam mēs apspriedīsim citus scenārijus, kuros jums būs nedaudz jāmaina šī formula.
Problēma:
Cena šūnā ir precīzi jāsadala 5 līdzīgos maksājumos. Vienkārši dalot cenu 88,04 ASV dolārus ar 5 un noapaļojot rezultātu līdz divām zīmēm aiz komata, mēs saņemam individuālus maksājumus 17,61 ASV dolāru apmērā. Tomēr kopējais maksājums būtu 88,05 ASV dolāri, kas nav precīza summa, ar kuru mēs sākam. Tāpēc mēs vēlamies aprēķināt 5 līdzīgus skaitļus, kas veido kopējo summu.
Risinājums:
Vispirms aprēķiniet pirmo maksājumu, dalot cenu ar 5 un noapaļojot rezultātu.
Izmantojiet funkciju ROUND šādā formulā:
= APAĻA (B1/5,2)
2. darbība:
Aprēķiniet katru atlikušo maksājumu.
Izmantojiet funkcijas ROUND, SUM un ROW šādā formulā:
= ROUND (($ B $ 1-SUM ($ B $ 2: B2))/(5-ROW ()+ROW ($ B $ 2)), 2)
Piemērs:
Skaitiet lietotājus, ja izplatītāja kods atbilst vai gads, izmantojot SUMPRODUCT
Tātad šeit mums ir pārdevēju datu kopa. Dati satur daudzas kolonnas. Mums ir jāsaskaita to lietotāju skaits, kuriem ir kods "INKA" vai gads ir "2016". Pārliecinieties, ka, ja kādam ir abas (kods “inka” un 2016. gads), tās ir jāskaita kā 1.
Tātad, dzirdiet, ka mums ir divi kritēriji. Mēs izmantojam iepriekš minēto SUMPRODUCT formulu:
| = SUMPRODUCT (-(((kods = I3)+(gads = K3))> 0)) |
Šeit kods un gads tiek nosaukti diapazonos. Tas atgriež 7.
Datos mums ir 5 INKA koda ieraksti un 4 2016. gada ieraksti. Bet 2 ierakstos kā kods un gads ir attiecīgi "INKA" un 2016. gads. Un šie ieraksti tiek skaitīti kā 1. Un tā mēs iegūstam 7.
Apskatīsim, kā tas darbojas?
Tātad, apskatīsim soli pa solim, kā formula tiek atrisināta, tad es apspriedīšu, kā tā darbojas.
-
- = SUMPRODUCT(-(((kods = I3)+(gads = 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
Dubultās negatīvās (-) zīmes tiek izmantotas, lai Būla vērtības pārvērstu par 1s un 0s. Tātad katra PATIESA vērtība masīvā tiek pārvērsta par 1 un FALSE par 0.
Vairāk vai kritēriju pievienošana skaitīšanai, izmantojot SUMPRODUCT. Tātad, ja jums ir jāpievieno vairāk vai kritēriji, lai tos saskaitītu, varat funkcijai vienkārši pievienot kritērijus, izmantojot + zīmi.
Piemēram, ja iepriekšminētajai formulai vēlaties pievienot citu kritēriju, lai tā pievienotu to darbinieku skaitu, kuri ir pārdevuši vairāk nekā 5 produktus. SUMPRODUCT formula vienkārši izskatīsies šādi:
| = SUMPRODUCT (-(((kods = I3)+(gads = K3)+(pārdošana> 5))> 0)) |
Vienkārši! vai ne?
Bet pieņemsim, ka vēlaties, lai no koda diapazona būtu divi kritēriji. Pieņemsim, ka vēlaties saskaitīt “INKB”. Tad kā to izdarīt? Viena no metodēm ir iepriekš minētās metodes izmantošana, taču tā būtu atkārtota. Pieņemsim, ka vēlos pievienot vēl 10 kritērijus no tā paša diapazona. Šādos gadījumos šī metode nav tik gudra, lai skaitītu, izmantojot SUMPRODUCT.
Pieņemsim, ka mums ir šādi sakārtoti dati.
Kritēriju kodi ir vienā rindā I2: J2. Šeit svarīga ir datu sakārtošana. SUMPRODUCT formula VAI kritēriju skaita noteikšanai būs šāda:
| = SUMPRODUCT (-(((kods = I2: J2)+(gads = I3: J3))> 0)) |
Šī ir SUMPRODUCT formula, kas jāskaita ar vairākiem kritērijiem, ja rindā ir rakstīti vairāki kritēriji no viena diapazona.
Iepriekš minētā formula atgriež pareizo atbildi, kas ir 10.
Ja J3 ievadāt jebkuru gadu, formula arī pievienos šo skaitli.
To izmanto, ja kritēriji ir vienā rindā. Vai tas darbosies, ja kritēriji vienā kolonnā vienam un tam pašam diapazonam? Nē, nebūs.
Šajā piemērā mums ir jāskaita vairāki kodi, bet šie kodu kodi ir ierakstīti vienā kolonnā. Izmantojot iepriekš minēto SUMPRODUCT formulu, mēs saņemam kļūdu ans #N/A. Mēs neiedziļināsimies, kā rodas šī kļūda, jo tas padara šo rakstu pārāk garu. Apskatīsim, kā mēs varam panākt šo darbu.
Lai šī formula darbotos, koda kritēriji jāiesaiņo funkcijā TRANSPOSE. Tādējādi formula darbosies.
| = SUMPRODUCT (-(((kods = TRANSPOSE (H3: H4))+(gads = TRANSPOSE (I3: I4)))> 0)) |
Šī ir formula skaitīšanai ar vairākiem vai nosacījumiem vienā diapazonā, kad slejā ir norādīti kritēriji.
Šeit ir visas novērošanas piezīmes par formulas izmantošanu.
Piezīmes:
- Formula darbojas gan tekstam, gan skaitļiem.
- Uzmeklēšanas vērtības var norādīt kā šūnu atsauci vai tieši, izmantojot formulā pēdiņu simbolu (") kā argumentus.
Ceru, ka sapratāt, kā sadalīt summu vienādos maksājumos, vienlaikus izvairoties no dalīšanas / noapaļošanas kļūdām programmā Excel. Šeit skatiet citus rakstus par Excel uzmeklēšanas vērtību un Excel 2019 funkcijām. Ja jums patika mūsu emuāri, kopīgojiet to ar saviem draugiem Facebook. Un arī jūs varat sekot mums Twitter un Facebook. Mēs labprāt uzklausītu jūsu viedokli, dariet mums zināmu, kā mēs varam uzlabot, papildināt vai ieviest jauninājumus mūsu darbā un uzlabot to jūsu labā. Rakstiet mums e -pasta vietnē.
Izmantojiet INDEX un MATCH, lai atrastu vērtību : Funkcija INDEX & MATCH, lai pēc vajadzības meklētu vērtību.
SUM diapazons ar INDEX programmā Excel : Izmantojiet funkciju INDEX, lai pēc vajadzības atrastu vērtību SUM.
Funkcijas INDEX izmantošana programmā Excel : Atrodiet masīva INDEKSU, izmantojot funkciju INDEX, kas izskaidrota ar piemēru.
Kā lietot funkciju MATCH programmā Excel : Atrodiet MATCH masīvā, izmantojot INDEX vērtību MATCH iekšpusē, kas izskaidrota ar piemēru.
Kā lietot funkciju LOOKUP programmā Excel : Atrodiet uzmeklēšanas vērtību masīvā, izmantojot funkciju LOOKUP, kas izskaidrota ar piemēru.
Populāri raksti:
Kā lietot funkciju IF programmā Excel : IF priekšraksts programmā Excel pārbauda nosacījumu un atgriež noteiktu vērtību, ja nosacījums ir TRUE, vai atgriež citu noteiktu vērtību, ja FALSE.
Kā lietot funkciju VLOOKUP programmā Excel : Šī ir viena no visbiežāk izmantotajām un populārākajām Excel funkcijām, kas tiek izmantota, lai meklētu vērtību no dažādiem diapazoniem un lapām.
Kā lietot SUMIF funkciju programmā Excel : Šī ir vēl viena būtiska informācijas paneļa funkcija. Tas palīdz apkopot vērtības noteiktos apstākļos.
Kā lietot funkciju COUNTIF programmā Excel : Saskaitiet vērtības ar nosacījumiem, izmantojot šo apbrīnojamo funkciju. Jums nav jāfiltrē dati, lai saskaitītu noteiktas vērtības. Skaitītāja funkcija ir būtiska, lai sagatavotu informācijas paneli.