Kā izmantot funkciju Excel SUMPRODUCT -

Satura rādītājs

Kopsavilkums

Funkcija SUMPRODUCT reizina diapazonus vai masīvus kopā un atgriež produktu summu. Tas izklausās garlaicīgi, bet SUMPRODUCT ir neticami daudzpusīga funkcija, kuru var izmantot skaitīšanai un summēšanai, 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

Reiziniet, pēc tam summējiet masīvus

Atgriešanās vērtība

Reizinātu 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 jāpievieno.
  • 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, lai to ievadītu, nav nepieciešama parastā masīva sintakse (Ctrl + Shift + Enter). Funkcijas SUMPRODUCT mērķis ir reizināt, pēc tam summēt masīvus. Ja tiek piegādāts tikai viens masīvs, SUMPRODUCT vienkārši summēs masīva vienumus. Var piegādāt līdz 30 masīviem.

Pirmoreiz sastopoties ar SUMPRODUCT, tas var šķist garlaicīgs, sarežģīts un pat bezjēdzīgs. Bet SUMPRODUCT ir pārsteidzoši daudzpusīga funkcija ar daudziem lietojumiem. Tā kā tas ar masīviem rīkosies graciozi un bez sūdzībām, varat to izmantot, lai gudri, eleganti apstrādātu šūnu diapazonus (skatiet saites uz formulu 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ījumiem

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

A B
1 Valsts Pārdošana
2 UT 75
3 CO 100
4 TX 125
5 CO 125
6 TX 150

Izmantojot SUMPRODUCT, jūs varat uzskaitīt kopējo Teksasas ("TX") pārdošanas apjomu, izmantojot šo formulu:

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

Kopējo Teksasas ("TX") pārdošanas apjomu varat summēt, izmantojot šo formulu:

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

Piezīme: Nejauciet dubultnegatīvo. Tas ir izplatīts triks, kas tiek izmantots modernākās Excel formulās, lai TRUE un FALSE vērtības piespiestu 1 un 0 vērtībās. Skatīt vairāk zemāk …

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

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

Katrā masīvā ir 5 vienumi. Pirmajā masīvā ir TRUE / FALSE vērtības, kas izriet no izteiksmes A2: A6 = "TX", un 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 masīvā1 esošie vienumi ir skaitliski - tie ir "jāpiespiež" vienās un nullēs. Šeit rodas dubultnegatīvs.

Izmantojot dubultnegatīvu - (divkāršs, jums tehniskiem tipiem), mēs varam piespiest PATIESĪBU / PATIESĪBU skaitliskās vērtībās viens un nulle, kā parādīts zemāk redzamajā virtuālajā attēlojumā. Pēdējā kolonna "Produkts" parāda divu masīvu reizināšanas rezultātu. Apkopotais rezultāts 275 ir vērtība, kuru SUMPRODUCT atgriež.

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

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

=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 izvērstāk izklāsta iepriekš minētās idejas.

SUMPRODUCT ar citām funkcijām

SUMPRODUCT var tieši izmantot citas funkcijas. Iespējams, redzēsit SUMPRODUCT, kas tiek izmantots kopā ar funkciju LEN, lai skaitītu diapazona kopējās rakstzīmes, vai ar tādām funkcijām kā ISBLANK, ISTEXT utt. Parasti tās nav masīva funkcijas, bet, piešķirot tām diapazonu, tās izveido "rezultātu masīvu". Tā kā SUMPRODUCT ir veidota darbam ar masīviem, tā var tieši veikt masīvu aprēķinus. Tas var būt labs veids, kā ietaupīt vietu darblapā, novēršot nepieciešamību pēc kolonnas “palīgs”.

Piemēram, pieņemsim, ka jums ir 10 dažādas teksta vērtības A1: A10 un vēlaties saskaitīt visu 10 vērtību rakstzīmes. B slejā var pievienot palīgkolonnu, kurā tiek izmantota šī 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 atgriezīs 10 vērtību masīvu. Tad SUMPRODUCT vienkārši summēs visas vērtības un atgriezīs rezultātu bez palīgkolonnas.

Skatiet tālāk sniegtos piemērus ar daudziem citiem SUMPRODUCT izmantošanas veidiem.

Piezīmes:

  1. SUMPRODUCT masīvos esošos skaitliskos vienumus 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. Masīvu iekšējie loģiskie testi radīs TRUE un FALSE vērtības. Vairumā gadījumu jūs vēlaties tos piespiest uz 1 un 0.
  4. SUMPRODUCT bieži var tieši izmantot citu funkciju rezultātus (skatiet formulu piemērus zemāk)

Interesanti raksti...