Kā datu modeļos izmantot Excel SUMPRODUCT funkciju

Funkcija SUMPRODUCT faktiski ir norādīta Excel funkciju matemātikas un trigonometrijas kategorijā. Tā kā SUMPRODUCT galvenais mērķis ir aprēķināt summas reizinājumu, lielākā daļa cilvēku nezina, ka varat to izmantot, lai meklētu vērtības. Patiesībā jūs varat diezgan efektīvi izmantot šo daudzpusīgo funkciju lielākajā daļā datu modeļu.

SUMPRODUCT pamati

Funkcija SUMPRODUCT ir paredzēta, lai reizinātu vērtības no diviem vai vairākiem datu diapazoniem un pēc tam saskaitītu rezultātus, lai atgrieztu produktu summu. Apskatiet šo attēlu, lai redzētu tipisku scenāriju, kurā SUMPRODUCT ir noderīgs.

SUMPRODUCT, lai iegūtu kopējo pārdošanas apjomu, ir jāreizina cena” width=”458″/>

Bez SUMPRODUCT, lai iegūtu kopējo pārdošanas apjomu, ir jāreizina cena ar vienībām un pēc tam jāsaskaita rezultāti.

Jūs redzat kopīgu analīzi, kurā jums ir nepieciešams kopējais pārdošanas apjoms 2011. un 2012. gadā. Kā redzat, lai iegūtu katra gada kopējo pārdošanas apjomu, vispirms cena ir jāreizina ar vienību skaitu, lai iegūtu katra gada kopējo pārdošanas apjomu. Novads. Pēc tam šie rezultāti ir jāsaskaita, lai iegūtu katra gada kopējo pārdošanas apjomu.

Izmantojot funkciju SUMPRODUCT, varat veikt divpakāpju analīzi tikai ar vienu formulu. Nākamajā attēlā parādīta tā pati analīze ar SUMPRODUCT formulām. Tā vietā, lai izmantotu 11 formulas, jūs varat veikt to pašu analīzi tikai ar 3!

Funkcija SUMPRODUCT ļauj veikt to pašu analīzi ar just” width=”535″/>

Funkcija SUMPRODUCT ļauj veikt to pašu analīzi tikai ar 3 formulām, nevis 11.

Funkcijas SUMPRODUCT sintakse ir diezgan vienkārša:

SUMPRODUCT ( masīvs1 , masīvs2 ,…)

Masīvs : masīvs attēlo datu diapazonu. SUMPRODUCT formulā varat izmantot jebkur no 2 līdz 255 masīviem. Masīvi tiek reizināti kopā un pēc tam pievienoti. Vienīgais stingrais noteikums, kas jums jāatceras, ir tāds, ka visiem masīviem ir jābūt vienādam vērtību skaitam. Tas nozīmē, ka nevar izmantot SUMPRODUCT, ja diapazonam X ir 10 vērtības un diapazonam Y ir 11 vērtības. Pretējā gadījumā jūs saņemsiet #VALUE! kļūda.

SUMPRODUCT funkcijas pagrieziens

Interesanta funkcija SUMPRODUCT ir tā, ka to var izmantot vērtību filtrēšanai. Apskatiet šādu attēlu:

Funkciju SUMPRODUCT var izmantot, lai filtrētu datus, pamatojoties uz kritērijiem. platums=”535″/>

Funkciju SUMPRODUCT var izmantot, lai filtrētu datus, pamatojoties uz kritērijiem.

Formula šūnā E12 velk kopējo vienību summu tikai ziemeļu reģionam. Tikmēr šūna E13 velk vienības, kas reģistrētas Ziemeļu reģionam 2011. gadā.

Lai saprastu, kā tas darbojas, apskatiet formulu šūnā E12. Šī formula skan SUMPRODUCT((C3:C10=“Ziemeļi”)*(E3:E10)).

Programmā Excel vērtība TRUE tiek novērtēta kā 1 un FALSE — 0. Katra vērtība kolonnā C, kas ir vienāda ar ziemeļiem, tiek novērtēta kā TRUE vai 1. Ja vērtība nav ziemeļu, tā tiek novērtēta kā FALSE vai 0. Formulas daļa, kas skan (C3 :C10=“Ziemeļi“) uzskaita katru vērtību diapazonā C3:C10, katrai vērtībai piešķirot 1 vai 0. Tad iekšēji SUMPRODUCT formula nozīmē

(1*E3)+(0*E4)+(0*E5)+(0*E6)+(1*E7)+(0*E8)+(0*E9)+(0*E10).

Tas sniedz jums atbildi par 1628, jo

(1*751)+(0*483)+(0*789)+(0*932)+(1*877)+(0*162)+(0*258)+(0*517)

vienāds ar 1628.

SUMPRODUCT formulu lietošana datu modelī

Kā vienmēr programmā Excel, jums nav stingri jākodē kritēriji formulās. Tā vietā, lai formulā SUMPRODUCT skaidri izmantotu “Ziemeļi”, varat atsaukties uz šūnu, kurā ir filtra vērtība. Varat iedomāties, ka šūnā A3 ir vārds North, un tādā gadījumā varat izmantot (C3:C10=A3), nevis (C3:C10=“Ziemeļi”). Tādā veidā jūs varat dinamiski mainīt filtra kritērijus, un formula paliek nemainīga.

Nākamajā attēlā ir parādīts, kā varat izmantot šo koncepciju, lai apkopotu datus pakāpeniskā tabulā, pamatojoties uz vairākiem kritērijiem. Ņemiet vērā, ka katra no šeit redzamajām SUMPRODUCT formulām atsaucas uz šūnām B3 un C3, lai tās filtrētu kontā un produktu rindā. Atkal varat pievienot datu validācijas nolaižamos sarakstus šūnām B3 un C3, ļaujot viegli mainīt kritērijus.

Funkciju SUMPRODUCT var izmantot, lai izvilktu apkopotus skaitļus no d” width=”535″/>

Funkciju SUMPRODUCT var izmantot, lai apkopotos skaitļus no datu slāņa izvilktu stadiju tabulās.