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 saskaitī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ēriji1)+(kritēriji2)+… )>0) |
1. kritērijs: Šis ir jebkurš kritērijs, kas atgriež masīvu TRUE un FALSE.
2. kritērijs: Šis ir nākamais kritērijs, kuru vēlaties pārbaudīt. Tāpat 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.
Piemērs: saskaitiet lietotājus, ja izplatītāja kods vai gadsSērkociņi 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 abi (kods “inka” un 2016. gads), tas ir jāskaita kā 1.
Tātad, šeit 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. Šie ieraksti tiek skaitīti kā 1. Un tā mēs iegūstam 7.
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)) |
| 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 |
Pirmajā solī I3 ("INKA") vērtība tiek salīdzināta ar katru koda diapazona šūnu. Tas atgriež masīvu TRUE un FALSE. PATIESA katram mačam. Lai ietaupītu vietu, es neesmu parādījis visu TRUE-FALSE. Līdzīgi K3 (2016) vērtība tiek saskaņota ar katru šūnu gada diapazonā.
Nākamajā solī mēs pievienojam šos divus masīvus, kā rezultātā tiek izveidots jauns skaitlisko vērtību masīvs. Kā jūs zināt, programmā Excel TRUE tiek uzskatīta par 1 un FALSE kā 0. Tātad, pievienojot TRUE un TRUE, mēs iegūstam 2, bet pārējo jūs varat saprast.
Nākamajā solī mēs pārbaudām, kura vērtība masīvā ir lielāka par 0. Tas atkal pārvērš masīvu par patiesu nepatiesu masīvu. Katrai iegūtajai 0 vērtībai False un rest tiek konvertēti kā patiesi. Tagad TRUE vērtību skaits masīvā ir mūsu atbilde. Bet kā mēs tos saskaitām? Lūk, kā.
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.
Pēdējā posmā SUMPRODUCT apkopo šo masīvu, un mēs saņemam atbildi kā 7.
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 iegūt divus kritērijus Kods diapazons. Pieņemsim, ka vēlaties saskaitīt “INKB”. Tātad, kā jūs to darā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 3 VAI kritēriju skaita iestatījumiem būs šāda:
| = SUMPRODUCT (-(((kods = I2: J2)+(gads = I3: J3))> 0)) |
Šī ir SUMPRODUCT formula, kas jāuzskaita ar vairākiem kritērijiem, ja pēc kārtas tiek rakstīti vairāki kritēriji no viena diapazona.
Tas 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 ir vienā kolonnā vienam 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ā radās šī kļūda, jo tas padarīs š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.
Jā, draugs, es ceru, ka es biju pietiekami skaidrs un tam bija jēga. Es ceru, ka tas kalpo jūsu mērķim būt šeit. Ja šī formula neatrisināja jūsu problēmu, paziņojiet man savas prasības zemāk esošajā komentāru sadaļā. Es būšu priecīgs jums jebkādā veidā palīdzēt. Jūs varat pieminēt visas ar šaubām saistītās Excel/VBA. Līdz tam turpiniet mācīties, turpiniet izcelties.
Funkcijas SUMPRODUCT izmantošana programmā Excel: Atgriež SUM pēc vērtību reizināšanas vairākos Excel masīvos. Šo funkciju var izmantot vairāku uzdevumu veikšanai. Šī ir viena no daudzpusīgākajām funkcijām.
COUNTIFS ar dinamisko kritēriju diapazonu : Lai skaitītu ar dinamisko kritēriju diapazonu, mēs vienkārši izmantojam funkciju INDIRECT. Šī funkcija var
COUNTIFS ar vai vairākiem kritērijiem : Saskaitiet šūnas, kurām ir vairāki kritēriji, kas atbilst, izmantojot funkciju VAI. Lai funkcijā COUNTIFS ievietotu VAI loģiku, jums nebūs jāizmanto funkcija VAI.
IF izmantošana ar funkcijām UN / VAI programmā Microsoft Excel : Šīs loģiskās funkcijas tiek izmantotas, lai veiktu vairāku kritēriju aprēķinus. Izmantojot IF, funkcijas OR un AND tiek izmantotas, lai iekļautu vai izslēgtu atbilstības.
Kā lietot funkciju VAI programmā Microsoft Excel : Funkcija tiek izmantota, lai iekļautu visas TRUE vērtības vairākos kritērijos.
Kā saskaitīt šūnas, kurās ir tas vai tas, programmā Excel programmā Excel : Šūnām, kurās ir tas vai tas, mēs varam izmantot funkciju SUMPRODUCT. Lūk, kā jūs veicat šos aprēķinus.
Populāri raksti:
50 Excel saīsnes, lai palielinātu produktivitāti | Ātrāk izpildiet savu uzdevumu. Šie 50 īsceļi ļaus jums strādāt vēl ātrāk programmā Excel.
Kā izmantot Excel VLOOKUP funkciju| Šī 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 Excel Funkcija COUNTIF| 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.
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.
