Excel

Excel SUMPRODUCT funkcija

Excel Sumproduct Function

Excel SUMPRODUCT funkcijaKopsavilkums

Funkcija SUMPRODUCT reizina diapazonus vai masīvus kopā un atgriež produktu summu. Tas izklausās garlaicīgi, bet SUMPRODUCT ir neticami daudzpusīga funkcija, ko var izmantot, lai skaitītu un summētu, piemēram, COUNTIFS vai SUMIFS, taču ar lielāku elastību. SUMPRODUCT iekšpusē var viegli izmantot citas funkcijas, lai vēl vairāk paplašinātu funkcionalitāti.





Mērķis Reizināt, pēc tam summas masīvi Atgriezt vērtību Daudzkārtotu un summētu masīvu rezultāts Sintakse = SUMPRODUCT (masīvs1, [masīvs2], ...) Argumenti
  • masīvs1 - Pirmais masīvs vai diapazons, kas jāreizina, pēc tam pievienojiet.
  • masīvs2 - [pēc izvēles] Otrais masīvs vai diapazons, kas jāreizina, pēc tam jāpievieno.
Versija Excel 2003 Lietošanas piezīmes

Funkcija SUMPRODUCT darbojas ar masīviem, taču tai nav nepieciešama parasta masīva sintakse (Ctrl + Shift + Enter). Funkcijas SUMPRODUCT mērķis ir masīvus reizināt, pēc tam summēt. Ja tiek piegādāts tikai viens masīvs, SUMPRODUCT vienkārši summē masīva vienumus. Var piegādāt līdz 30 masīviem.

Pirmoreiz sastopoties ar SUMPRODUCT, tas var šķist garlaicīgi, sarežģīti un pat bezjēdzīgi. Bet SUMPRODUCT ir pārsteidzoši daudzpusīga funkcija ar daudziem lietojumiem. Tā kā tas masīvus apstrādās graciozi un bez sūdzībām, varat to izmantot, lai gudri un eleganti apstrādātu šūnu diapazonus (skatiet saites uz formulas piemēriem šajā lapā).





Lai ilustrētu SUMPRODUCT darbību, šeit ir daži izplatīti piemēri.

SUMPRODUCT nosacītām summām un skaitļiem

Pieņemsim, ka jums ir daži pasūtījuma dati A2: B6, valsts A slejā, pārdošana B slejā:



TO B
1 Valsts Pārdošana
2 ĀRĀ 75
3 KAS 100
4 TX 125
5 KAS 125
6 TX 150

Izmantojot SUMPRODUCT, jūs varat saskaitīt kopējais pārdošanas apjoms Teksasā (“TX”) ar šādu formulu:

 
= SUMPRODUCT (--(A2:A6='TX'))

Un jūs varat summa kopējais pārdošanas apjoms Teksasā (“TX”) ar šādu formulu:

 
= SUMPRODUCT (--(A2:A6='TX'),B2:B6)

Piezīme. Neļaujiet sevi sajaukt ar dubultnegatīvu. Šis ir izplatīts triks, ko izmanto progresīvākās Excel formulās, lai piespiestu TRUE un FALSE vērtības 1 un 0. Vairāk skatīt zemāk ...

Excel atkarīgais nolaižamais saraksts ar vairākiem vārdiem

Iepriekš redzamajam summas piemēram, šeit ir divu masīvu virtuāls attēlojums, ko vispirms apstrādāja SUMPRODUCT:

masīvs1 masīvs2
FALSE 75
FALSE 100
PATIESA 125
FALSE 125
PATIESA 150

Katrā masīvā ir 5 vienības. Pirmajā masīvā ir TRUE / FALSE vērtības, kas izriet no izteiksmes A2: A6 = 'TX', bet otrais masīvs ir B2: B6 saturs. Katrs pirmā masīva vienums tiks reizināts ar atbilstošo vienību otrajā masīvā. Tomēr pašreizējā stāvoklī SUMPRODUCT rezultāts būs nulle, jo TRUE un FALSE vērtības tiks uzskatītas par nulli. Mums ir nepieciešams, lai masīva1 vienumi būtu skaitliski - tie ir “jāsaspiež” vienībās un nullēs. Šeit parādās dubultnegatīvs.

Izmantojot dubulto negatīvo - (dubultā vienota, jums, tehniskajiem tipiem), mēs varam piespiest TRUE/FALSE skaitliskajās vērtībās viens un nulle, kā parādīts zemāk esošajā virtuālajā attēlā. Pēdējā sleja “Produkts” atspoguļo abu masīvu reizināšanas rezultātu. Apkopotais rezultāts 275 ir vērtība, ko atgriež SUMPRODUCT.

masīvs1 masīvs2 Produkts
0 * 75 = 0
0 * 100 = 0
1 * 125 = 125
0 * 125 = 0
1 * 150 = 150
Summa 275

Izmantojot masīvu cirtainās breketes sintaksi, piemērs pēc piespiešanas izskatās šādi:

kā izmantot uzmeklēšanas formulu
 
= SUMPRODUCT ({0,0,1,0,1},{75,100,125,125,150}) 

un šādi pēc reizināšanas:

 
= SUMPRODUCT ({0,0,125,0,150})

Šis piemērs paplašina iepriekš minētās idejas ar sīkāku informāciju.

SUMPRODUCT ar citām funkcijām

SUMPRODUCT var tieši izmantot citas funkcijas. Jūs varētu redzēt, ka SUMPRODUCT tiek lietots kopā ar funkciju LEN, lai saskaitītu kopējo rakstzīmju skaitu diapazonā, vai ar tādām funkcijām kā ISBLANK, ISTEXT utt. Parasti tās nav masīva funkcijas, bet, ja tām tiek piešķirts diapazons, tās izveido “rezultātu masīvu”. Tā kā SUMPRODUCT ir veidots darbam ar masīviem, tas spēj veikt masīvu aprēķinus tieši. Tas var būt labs veids, kā ietaupīt vietu darblapā, novēršot vajadzību pēc kolonnas “palīgs”.

Piemēram, pieņemsim, ka A1: A10 ir 10 dažādas teksta vērtības un vēlaties saskaitīt visu 10 vērtību rakstzīmes. Jūs varat pievienot palīgu kolonnu B slejā, kurā tiek izmantota šāda formula: LEN (A1), lai aprēķinātu rakstzīmes katrā šūnā. Tad jūs varētu izmantot SUM, lai saskaitītu visus 10 ciparus. Tomēr, izmantojot SUMPRODUCT, varat uzrakstīt šādu formulu:

 
= SUMPRODUCT ( LEN (A1:A10))

Lietojot ar diapazonu, piemēram, A1: A10, LEN atgriež 10 vērtību masīvu. Tad SUMPRODUCT vienkārši summē visas vērtības un atgriež rezultātu, bez palīgkolonnas.

Tālāk skatiet piemērus daudzos citos SUMPRODUCT izmantošanas veidos.

Piezīmes:

  1. SUMPRODUCT masīvos esošos vienumus, kas nav cipari, uzskata par nullēm.
  2. Masīva argumentiem jābūt vienāda lieluma. Pretējā gadījumā SUMPRODUCT ģenerēs #VALUE! kļūdas vērtība.
  3. Loģiskie testi masīvos radīs patiesas un nepatiesas vērtības. Vairumā gadījumu jūs vēlaties tos piespiest pie 1 un 0.
  4. SUMPRODUCT bieži var tieši izmantot citu funkciju rezultātus (skatiet tālāk sniegtos formulu piemērus)


^