Lejupielādējiet darbgrāmatas piemēru
Šī apmācība parāda, kā lietot Excel SUMPRODUCT funkcija programmā Excel.
SUMPRODUCT Funkciju pārskats
Funkcija SUMPRODUCT Reizina skaitļu masīvus un summē iegūto masīvu.
Lai izmantotu SUMPRODUCT Excel darblapas funkciju, atlasiet šūnu un ierakstiet:
(Ievērojiet, kā parādās formulas ievades)
Funkcija SUMPRODUCT Sintakse un ievades:
| 1 | = SUMPRODUCT (masīvs1, masīvs2, masīvs3) |
masīvs1 - Ciparu masīvi.
Kas ir SUMPRODUCT funkcija?
Funkcija SUMPRODUCT ir viena no jaudīgākajām funkcijām programmā Excel. Tā nosaukums var likt domāt, ka tas ir paredzēts tikai matemātikas pamata aprēķiniem, bet to var izmantot daudz ko citu.
Masīvi
SUMPRODUCT nepieciešama masīvu ievade.
Tātad, pirmkārt, ko mēs domājam ar “masīvu”? Masīvs ir vienkārša vienību grupa (piemēram, skaitļi), kas sakārtota noteiktā secībā, tāpat kā šūnu diapazons. Tātad, ja šūnās A1: A3 būtu skaitļi 1, 2, 3, programma Excel to lasītu kā masīvu {1,2,3}. Faktiski jūs varat ievadīt {1,2,3} tieši Excel formulās, un tas atpazīs masīvu.
Tālāk mēs runāsim vairāk par masīviem, bet vispirms apskatīsim vienkāršu piemēru.
Matemātikas pamati
Apskatīsim SUMPRODUCT pamata piemēru, izmantojot to, lai aprēķinātu kopējos pārdošanas apjomus.
Mums ir mūsu produktu tabula, un mēs vēlamies aprēķināt kopējos pārdošanas apjomus. Jums rodas kārdinājums vienkārši pievienot jaunu kolonnu, ņemt pārdoto daudzumu * cenu un pēc tam apkopot jauno kolonnu. Tomēr tā vietā varat vienkārši izmantot funkciju SUMPRODUCT. Apskatīsim formulu:
| 1 | = SUMPRODUCT (A2: A4, B2: B4) |
Funkcija ielādēs skaitļu diapazonus masīvos, reizinās tos viens pret otru un pēc tam summēs rezultātus:
| 1234 | = SUMPRODUCT ({100, 50, 10}, {6, 7, 5})= SUMPRODUCT ({100 * 6, 50 * 7, 10 * 5})= SUMPRODUCT ({600, 350, 50}= 1000 |
SUMPRODUCT Funciton spēja reizināt visus skaitļus mūsu vietā un veikt summēšanu.
Svērtais vidējais
Vēl viens gadījums, kad ir noderīgi izmantot SUMPRODUCT, ir tad, kad jāaprēķina vidējais svērtais. Tas visbiežāk notiek, veicot skolas darbus, tāpēc ņemsim vērā šo tabulu.
Mēs varam redzēt, cik viktorīnas, testi un mājasdarbi ir vērti pret kopējo atzīmi, kā arī to, kāds ir pašreizējais vidējais rādītājs katrai konkrētai precei. Kopējo atzīmi mēs varam aprēķināt, rakstot
| 1 | = SUMPRODUCT (B2: B4, C2: C4) |
Mūsu funkcija atkal reizina katru masīvā esošo vienumu pirms kopsummas summēšanas. Tas izdodas šādi
| 123 | = SUMPRODUCT ({30%, 50%, 20%}, {73%, 90%, 95%})= SUMPRODUCT ({22%, 45%, 19%})= 86% |
Vairākas kolonnas
Vēl viena vieta, kur mēs varētu izmantot SUMPRODUCT, ir vēl vairāk kolonnu, kuras visas jāreizina viena pret otru. Apskatīsim piemēru, kur mums jāaprēķina apjoms zāģmateriālu gabalos.
Tā vietā, lai izveidotu palīgu kolonnu, lai aprēķinātu kopējo pārdošanas apjomu katrai rindai, mēs to varam izdarīt ar vienu formulu. Mūsu formula būs
| 1 | = SUMPRODUCT (B2: B5, C2: C5, D2: D5) |
Katra masīva pirmie vienumi reizinās viens pret otru (piemēram, 4 * 2 * 1 = 8). Tad otrais (4 * 2 * 2 = 16) un 3rd, utt. Kopumā tas radīs tādu produktu klāstu, kas izskatās kā {8, 16, 16, 32). Tad kopējais apjoms būtu šī masīva summa, 72.
Viens kritērijs
Labi, pievienosim vēl vienu sarežģītības slāni. Mēs esam redzējuši, ka SUMPRODUCT var apstrādāt skaitļu masīvus, bet kā būtu, ja mēs vēlētos pārbaudīt kritērijus? Jūs varat arī izveidot masīvus Būla vērtībām (Būla vērtības ir vērtības, kas ir TRUE vai FALSE).
Piemēram, ņemiet pamata masīvu {1, 2, 3}. Izveidosim atbilstošu masīvu, kas norāda, vai katrs skaitlis ir lielāks par 1. Šis masīvs izskatīsies šādi: FALSE, TRUE, TRUE}.
Tas ir ļoti noderīgi formulās, jo mēs varam viegli pārvērst TRUE / FALSE par 1 / 0. Apskatīsim piemēru.
Izmantojot zemāk esošo tabulu, mēs vēlamies aprēķināt “Cik pārdoto vienību bija sarkanā krāsā?”
Mēs to varam izdarīt, izmantojot šādu formulu:
| 1 | = SUMPRODUCT (A2: A4, -(B2: B4 = "sarkans")) |
"Uzgaidi! Kas tur ir ar dubultā mīnusa simbolu? ” tu saki. Atcerieties, kā es teicu, ka mēs varētu pārvērst no patiesas/nepatiesas par 1/0? Mēs to darām, liekot datoram veikt matemātisku darbību. Šajā gadījumā mēs sakām: “ņem negatīvo vērtību un pēc tam vēlreiz ņem negatīvo”. Izrakstot to, mūsu masīvs mainīsies šādi:
| 123 | {True, True, False}{-1, -1, 0}{1, 1, 0} |
Tātad, atgriežoties pie pilnas SUMPRODUCT formulas, tā tiks ielādēta mūsu masīvos un pēc tam reizināta, kā tas ir
| 123 | = SUMPRODUCT ({100, 50, 10}, {1, 1, 0})= SUMPRODUCT ({100, 50, 0})= 150 |
Ievērojiet, kā 3rd vienums kļuva par 0, jo viss, kas reizināts ar 0, kļūst par nulli.
Vairāki kritēriji
Mēs savā funkcijā varam ielādēt līdz 255 masīviem, tāpēc noteikti varam ielādēt vairāk kritēriju. Apskatīsim šo lielāku tabulu, kurā esam pievienojuši pārdoto mēnesi.
Ja mēs vēlamies uzzināt, cik pārdoto preču bija sarkanas un bija februāra mēnesī, mēs varētu uzrakstīt savu formulu kā
| 1 | = SUMPRODUCT (A2: A4, -(B2: B4 = "Red"), -(C2: C4 = "Feb")) |
Pēc tam dators novērtēs mūsu masīvus un reizinās. Mēs jau esam apskatījuši, kā patiesie/nepatiesie masīvi tiek mainīti uz 1/0, tāpēc es pagaidām izlaidīšu šo soli.
| 123 | = SUMPRODUCT ({100, 50, 10}, {1, 1, 0}, {0, 1, 1})= SUMPRODUCT ({0, 50, 0})= 50 |
Mūsu piemērā bija tikai viena rinda, kas atbilda visiem kritērijiem, taču, izmantojot reālus datus, iespējams, jums bija jāsaskaita vairākas nepieciešamās rindas.
Sarežģīti kritēriji
Labi, līdz šim brīdim jūs, iespējams, neiepriecināsit, jo visus mūsu piemērus varēja izdarīt, izmantojot citas funkcijas, piemēram, SUMIF vai COUNTIF. Tagad mēs darīsim kaut ko no šīm citām funkcijām nevar darīt. Iepriekš mūsu slejā Mēnesis bija faktiskie mēnešu nosaukumi. Ko darīt, ja tā vietā būtu datumi?
Mēs nevaram veikt SUMIF, jo SUMIF nevar tikt galā ar mums nepieciešamajiem kritērijiem. Tomēr SUMPRODUCT var tikt galā ar to, ka mēs manipulējam ar masīvu un veicam dziļāku pārbaudi. Mēs jau esam manipulējuši ar masīviem, kad esam tulkojuši patieso/nepatieso 1/0. Mēs manipulēsim ar šo masīvu, izmantojot funkciju MONTH. Šeit ir pilna formula, kuru mēs izmantosim
| 1 | = SUMPRODUCT (A2: A4, -(B2: B4 = "sarkans"), -(MĒNESIS (C2: C4) = 2)) |
Apskatīsim 3rd masīvs ciešāk. Pirmkārt, mūsu formula iegūs mēneša numuru no katra datuma C2: C4. Tādējādi mēs iegūsim {1, 2, 2}. Tālāk mēs pārbaudām, vai šī vērtība ir vienāda ar 2. Tagad mūsu masīvs izskatās kā {False, True, True}. Mēs atkal veicam dubultmīnusu, un mums ir {0, 1, 1}. Tagad mēs esam atgriezušies līdzīgā vietā, kas bija 3. piemērā, un pēc mūsu formulas mēs varēsim pateikt, ka februārī tika pārdotas 50 sarkanās vienības.
Dubults mīnuss pret reizināšanu
Ja esat iepriekš redzējis SUMPRODUCT funkciju, iespējams, redzējāt nedaudz atšķirīgu apzīmējumu. Tā vietā, lai izmantotu dubultmīnusu, varat rakstīt
| 1 | = SUMPRODUCT (A2: A4*(B2: B4 = "Red")*(Mēnesis (C2: C4) = 2)) |
Formula joprojām darbosies tāpat, mēs tikai manuāli sakām datoram, ka vēlamies pavairot masīvus. SUMPRODUCT to darīs jebkurā gadījumā, tāpēc matemātikas darbībā nav izmaiņu. Veicot matemātisko darbību, mūsu patiesā/nepatiesā vērtība tiek pārveidota par 1/0. Tātad, kāpēc atšķirība?
Lielākoties tam nav pārāk lielas nozīmes, un tas ir atkarīgs no lietotāju vēlmēm. Tomēr ir vismaz viens gadījums, kad nepieciešama reizināšana.
Izmantojot SUMPRODUCT, dators sagaida, ka visi argumenti (masīvs1, masīvs2 utt.) Būs vienāda lieluma. Tas nozīmē, ka tiem ir vienāds rindu vai kolonnu skaits. Tomēr, izmantojot SUMPRODUCT, jūs varat darīt to, kas pazīstams kā divdimensiju masīva aprēķins, ko mēs redzēsim nākamajā piemērā. Kad jūs to darāt, masīvi ir dažāda lieluma, tāpēc mums ir jāapiet šī “visu vienāda izmēra” pārbaude.
Divas dimensijas
Visos iepriekšējos piemēros mūsu masīvi devās vienā virzienā. SUMPRODUCT var rīkoties divos virzienos, kā mēs redzēsim nākamajā tabulā.
Šeit ir mūsu pārdoto vienību tabula, taču dati tiek pārkārtoti, kur kategorijas atrodas augšpusē. Ja mēs vēlamies noskaidrot, cik priekšmetu bija sarkanā krāsā un A kategorijā, mēs varam rakstīt
| 1 | = SUMPRODUCT ((A2: A4 = "sarkans")*(B1: C1 = "A")*B2: C4) |
Kas šeit notiek?? Izrādās, ka mēs reizināsim divos dažādos virzienos. To vizualizēt ir grūtāk ar tikai uzrakstītu teikumu, tāpēc mums ir daži attēli, kas mums palīdz. Pirmkārt, mūsu rindas kritēriji (vai tas ir sarkans?) Reizinās katrā masīva rindā.
| 1 | = SUMPRODUCT ((A2: A4 = "RED")*B2: C4) |
Tālāk kolonnu kritēriji (vai tā ir A kategorija?) Palielinās katru kolonnu
| 1 | = SUMPRODUCT ((A2: A4 = "sarkans")*(B1: C1 = "A")*B2: C4) |
Pēc tam, kad abi šie kritēriji būs paveikuši savu darbu, vienīgie, kas nav nulles, ir 5 un 10. SUMPRODUCT mums atbildēs uz kopsummu 15.
Atcerieties, kā mēs runājām par to, ka masīviem jābūt vienāda izmēra, ja vien neveicat divas dimensijas? Tas bija daļēji pareizi. Atkal aplūko masīvus, kurus izmantojām mūsu formulā. The augstums no diviem mūsu masīviem ir vienādi, un platums divi no mūsu masīviem ir vienādi. Tātad, jums joprojām ir jāpārliecinās, ka lietas sakārtosies pareizi, taču jūs varat to izdarīt dažādās dimensijās.
Divas dimensijas un sarežģītas
Daudzas reizes mums tiek piedāvāti dati, kas nav vislabākajā izkārtojumā, kas piemērots mūsu formulām. Mēs varētu mēģināt to manuāli pārkārtot, vai arī mēs varam būt gudrāki ar savām formulām. Apskatīsim šādu tabulu.
Šeit mums ir dati par mūsu precēm un pārdošanu kopā par katru mēnesi. Kā mēs uzzinātu, cik priekšmetu Bobs ir pārdevis visu gadu?
Lai to izdarītu, mēs izmantosim divas papildu funkcijas: SEARCH un ISNUMBER. Funkcija MEKLĒT ļaus mums meklēt atslēgvārdu “vienumi” galvenes šūnās. Šīs funkcijas iznākums tiks iegūts, izmantojot skaitli vai kļūdu (ja atslēgvārds nav atrasts). Pēc tam reklāmguvumam izmantosim ISNUMBER ka izlaide mūsu Būla vērtībās. Mūsu formula izskatīsies zemāk.
Tagad jums vajadzētu būt labi pazīstamam ar pirmo masīvu. Tas radīs izvadi, piemēram, {0, 1, 0, 1}. Nākamais kritēriju masīvs, par kuru mēs tikko runājām. Tiks izveidots numurs visām šūnām ar vienumu “Vienumi” un kļūda pārējām {5, #N/A !, 5, #N/A!}. Pēc tam ISNUMBER pārvērš to par Būla vērtību {True, False, True, False}. Tad, reizinot, tas saglabās tikai pirmās un trešās kolonnas vērtības. Kad visi masīvi reizinās viens ar otru, vienīgie skaitļi, kas nav nulle, mums būs tie, kas šeit ir iezīmēti:
| 1 | = SUMPRODUCT ((A2: A5 = "Bob")*(ISNUMBER (SEARCH ("preces", B1: E1))*B2: E5)) |
Pēc tam SUMPRODUCT tos visus saskaitīs, un mēs iegūsim galīgo rezultātu 29.
SUMPRODUCT Or
Pastāv daudzas situācijas, kad mēs vēlētos apkopot vērtības, ja mūsu kritēriju slejā ir viena vērtība VAI cita vērtība. To var paveikt programmā SUMPRODUCT, pievienojot divus kritēriju masīvus.
Šajā piemērā mēs vēlamies saskaitīt vienības, kas pārdotas gan sarkanajam, gan zilajam.
Mūsu formula izskatīsies šādi
| 1 | = SUMPRODUCT (A2: A7, (B2: B7 = "Red")+(B2: B7 = "Blue")) |
Apskatīsim sarkano kritēriju masīvu. Tas radīs masīvu, kas izskatās šādi: {1, 1, 0, 0, 0, 0}. Zilo kritēriju masīvs izskatīsies kā {0, 0, 1, 0, 1, 0}. Pievienojot tos kopā, jaunais masīvs izskatīsies šādi: {1, 1, 1, 0, 1, 0}. Mēs varam redzēt, kā abi masīvi ir apvienoti vienā kritēriju masīvā. Funkcija to reizinās ar mūsu pirmo masīvu, un mēs iegūsim {100, 50, 10, 0, 75, 0}. Ņemiet vērā, ka zaļās vērtības ir atceltas. SUMPRODUCT pēdējais solis ir visu skaitļu saskaitīšana kopā, lai sasniegtu mūsu 235 risinājumu.
Šeit viens brīdinājuma vārds. Esiet piesardzīgs, ja kritēriju masīvi neizslēdz viens otru. Mūsu piemērā vērtības B slejā var būt sarkanas vai zilas, bet mēs zinājām, ka tās nekad nevar būt abas. Apsveriet, vai mēs būtu uzrakstījuši šo formulu:
| 1 | = SUMPRODUCT (A2: A7, (A2: A7> = 50)+(B2: B7 = "zils")) |
Mūsu nolūks ir atrast zilas preces, kuras tika pārdotas vai kuru daudzums pārsniedza 50. Tomēr šie nosacījumi nav ekskluzīvi, jo A rindā viena rinda var būt gan virs 50. un esi zils. Rezultātā pirmais kritēriju masīvs izskatīsies šādi: {1, 1, 0, 1, 1, 0}, bet otrais kritēriju masīvs būs {0, 0, 1, 0, 1, 0}. Apvienojot tos, tika iegūts {1, 1, 1, 1, 2, 0}. Vai redzat, kā mums tagad ir 2? Ja to atstātu vienu pašu, SUMPRODUCT beigās dubultotu šīs rindas vērtību, mainot 75 uz 150, un mēs iegūtu nepareizu rezultātu. Lai to labotu, mēs veicam ārēju kritēriju pārbaudi mūsu masīvā, piemēram:
| 1 | = SUMPRODUCT (A2: A7, -((A2: A7> = 50)+(B2: B7 = "zils")> 0)) |
Tagad, kad abi iekšējo kritēriju masīvi ir saskaitīti, mēs pārbaudīsim, vai rezultāts ir lielāks par 0. Tādējādi tiks atbrīvoti no diviem iepriekšējiem, un tā vietā mums būs masīvs, piemēram, {1, 1, 1 , 1, 1, 0}, kas radīs pareizu rezultātu.
SUMPRODUCT Precīzi
Lielākā daļa Excel funkciju nav reģistrjutīgas, taču dažreiz mums ir jāspēj veikt uzmeklēšanu, ņemot vērā reģistrjutību. Ja vēlamais rezultāts ir skaitlisks, mēs to varam paveikt, izmantojot funkciju SACPRODUCT (precīzs). Apsveriet šādu tabulu:
Mēs vēlamies atrast punktu vienumam “ABC123”. Parasti funkcija EXACT salīdzina divus vienumus un atgriež Būla rezultātu, norādot, vai abi vienumi ir tieši tā tas pats. Tomēr, tā kā mēs esam SUMPRODUCT, mūsu dators zinās, ka mēs strādājam ar masīviem, un varēs salīdzināt vienu vienumu ar katru masīva vienumu. Mūsu formula izskatīsies šādi
| 1 | = SUMPRODUCT (-PRECĪZS ("ABC123", A2: A5), B2: B5) |
Funkcija EXACT pēc tam pārbaudīs katru A2: A5 vienumu, lai redzētu, vai tas atbilst vērtībai un lielajiem burtiem. Tādējādi tiks izveidots masīvs, kas izskatās kā {0, 1, 0, 0}. Reizinot pret B2: B5, masīvs kļūst par {0, 2, 0, 0}. Pēc galīgās summēšanas mēs iegūstam 2 risinājumu.
SUMPRODUCT Google izklājlapās
Funkcija SUMPRODUCT Google izklājlapās darbojas tieši tāpat kā programmā Excel:
SUMPRODUCT Piemēri VBA
VBA varat izmantot arī SUMPRODUCT funkciju. Tips: application.worksheetfunction.sumproduct (masīvs1, masīvs2, masīvs3)
Izpildot šādus VBA paziņojumus
| 1 | Diapazons ("B10") = Application.WorksheetFunction.SumProduct (Diapazons ("A2: A7"), diapazons ("B2: B7")) |
radīs šādus rezultātus
Funkcijas argumentiem (masīvs1 utt.) Varat tos ievadīt tieši funkcijā vai definēt mainīgos, ko izmantot.
