Az Excel SUMPRODUCT függvény használata -

Tartalomjegyzék

Összegzés

A SUMPRODUCT függvény szorozza meg a tartományokat vagy tömböket, és adja vissza a termékek összegét. Ez unalmasan hangzik, de a SUMPRODUCT egy hihetetlenül sokoldalú függvény, amelyet fel lehet használni a COUNTIFS vagy SUMIFS számlálásra és összegzésre, de nagyobb rugalmassággal. Más funkciók könnyen használhatók a SUMPRODUCT-en belül, hogy a funkcionalitást még tovább bővítsék.

Célja

Szorozzon, majd összegezzen tömböket

Visszatérési érték

Megszorzott és összegzett tömbök eredménye

Szintaxis

= SUMPRODUCT (tömb1, (tömb2),…)

Érvek

  • tömb1 - Az első tömb vagy tartomány, többszörösen, majd adjunk hozzá.
  • tomb2 - (opcionális) A második tömb vagy tartomány, többszörösen, majd adjunk hozzá.

Változat

Excel 2003

Használati megjegyzések

A SUMPRODUCT függvény tömbökkel működik, de a belépéshez nem szükséges a normál tömbszintaxis (Ctrl + Shift + Enter). A SUMPRODUCT függvény célja a tömbök szorzása, majd összeadása. Ha csak egy tömb van megadva, a SUMPRODUCT egyszerűen összefoglalja a tömb elemeit. Legfeljebb 30 tömböt lehet szállítani.

Amikor először találkozik a SUMPRODUCT szolgáltatással, unalmasnak, összetettnek és még értelmetlennek is tűnhet. De a SUMPRODUCT elképesztően sokoldalú funkció, sok felhasználással. Mivel a tömböket kecsesen és panaszmentesen fogja kezelni, használhatja a cellasorok okos, elegáns módon történő feldolgozásához (lásd a képletre mutató hivatkozásokat ezen az oldalon).

A SUMPRODUCT működésének bemutatásához itt van néhány általános példa.

SUMPRODUCT feltételes összegekhez és számlálásokhoz

Tegyük fel, hogy rendelkezik néhány rendelési adattal az A2: B6 mezőben, az állam az A oszlopban, az értékesítés a B oszlopban:

A B
1 Állapot Értékesítés
2 UT 75
3 CO 100
4 TX 125
5. CO 125
6. TX 150

A SUMPRODUCT használatával a következő teljes képlettel számolhatja meg Texas ("TX") teljes eladását:

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

És összeszámolhatja Texas ("TX") teljes eladását a következő képlettel:

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

Megjegyzés: Ne keverje össze a kettős-negatív. Ez egy általános trükk, amelyet a fejlettebb Excel képletekben használnak az IGAZ és HAMIS értékek 1 és 0 értékekre kényszerítésére. További információk alább …

A fenti összegző példa esetében itt látható a két tömb virtuális ábrázolása, amelyet a SUMPRODUCT először dolgozott fel:

tömb1 tömb2
HAMIS 75
HAMIS 100
IGAZ 125
HAMIS 125
IGAZ 150

Minden tömb 5 elemet tartalmaz. Az első tömb tartalmazza az IGAZ / HAMIS értékeket, amelyek az A2: A6 = "TX" kifejezésből származnak, a második tömb pedig a B2: B6 tartalmát tartalmazza. Az első tömb minden elemét megszorozzuk a második tömb megfelelő elemével. Azonban a jelenlegi állapotban a SUMPRODUCT eredménye nulla lesz, mert az IGAZ és HAMIS értékeket nullának tekintjük. Szükségünk van arra, hogy az 1. tömbben lévő elemek numerikusak legyenek - ezeket egysé és nullává kell "kényszeríteni". Itt jön be a kettős-negatív.

A kettős negatív - (kettős unáriás, az Ön számára technikai típusok) használatával az IGAZ / HAMIS értéket kényszeríthetjük az első és a nulla numerikus értékekre, amint az az alábbi virtuális ábrázolásban látható. Az utolsó "Termék" oszlop a két tömb együttes megszorzásának eredményét mutatja. Az összesített eredmény, a 275, az az érték, amelyet a SUMPRODUCT ad vissza.

tömb1 tömb2 Termék
0 * 75 = 0
0 * 100 = 0
1 * 125 = 125
0 * 125 = 0
1 * 150 = 150
Összeg 275

A göndör zárójeles szintaxist tömböknél használva a példa kényszer után így néz ki:

=SUMPRODUCT((0,0,1,0,1),(75,100,125,125,150))

és így a szorzás után:

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

Ez a példa a fenti ötleteket bővebben részletezi.

SUMPRODUCT más funkciókkal

A SUMPRODUCT más funkciókat is közvetlenül használhat. Láthatja, hogy a SUMPRODUCT a LEN funkcióval együtt használható a teljes karakterek számolásához egy tartományban, vagy olyan funkciókkal, mint az ISBLANK, ISTEXT stb. Ezek általában nem tömbfüggvények, de ha tartományt kapnak, akkor létrehoznak egy "eredménytömböt". Mivel a SUMPRODUCT tömbökkel való együttműködésre van felépítve, közvetlenül képes a tömbökön végzett számításokra. Ez jó módszer lehet a munkalapon a helytakarékosságra, kiküszöbölve a "segítő" oszlop szükségességét.

Tegyük fel például, hogy az A1: A10-ben 10 különböző szövegérték van, és meg akarja számolni mind a 10 érték összes karakterét. Hozzáadhat egy segítő oszlopot a B oszlopba, amely a következő képletet használja: LEN (A1) az egyes cellákban szereplő karakterek kiszámításához. Ezután a SUM segítségével összeadhatja mind a 10 számot. A SUMPRODUCT segítségével azonban írhat egy ilyen képletet:

=SUMPRODUCT(LEN(A1:A10))

Az A1: A10 tartományhoz hasonlóan a LEN 10 értékű tömböt ad vissza. Ekkor a SUMPRODUCT egyszerűen összesíti az összes értéket, és visszaadja az eredményt, nincs szükség helper oszlopra.

Az alábbiakban olvashat példákat a SUMPRODUCT számos más módjára.

Megjegyzések:

  1. A SUMPRODUCT a tömbökben szereplő nem numerikus elemeket nullákként kezeli.
  2. A tömb argumentumoknak azonos méretűeknek kell lenniük. Ellenkező esetben a SUMPRODUCT #VALUE értéket generál! hibaérték.
  3. A tömbökön belüli logikai tesztek IGAZ és HAMIS értékeket hoznak létre. A legtöbb esetben ezeket 1 és 0 értékekre kell kényszeríteni.
  4. A SUMPRODUCT gyakran más funkciók eredményét is közvetlenül felhasználhatja (lásd az alábbi képleteket)

érdekes cikkek...