Lassen Sie uns über die SUMPRODUCT-Funktion von Excel sprechen
Dieser Artikel vermittelt Ihnen relevantes Wissen über Excel, das hauptsächlich Probleme im Zusammenhang mit der SUMPRODUCT-Funktion vorstellt. Diese Funktion kombiniert nicht nur die beiden Hauptfunktionen der bedingten Summierung und des Zählens, sondern kann auch in komplexen Szenarien verwendet werden Ich hoffe, dass es für alle hilfreich ist.
Verwandte Lernempfehlungen: Excel-Tutorial
Heute werde ich eine sehr häufig verwendete und praktische Funktion mit Ihnen teilen: SUMPRODUCT. Wie wir alle wissen, sind bedingte Summierung und Zählung die beiden häufigsten Probleme, auf die Tabellenbenutzer stoßen. Diese Funktion kombiniert nicht nur die beiden Hauptfunktionen bedingte Summierung und Zählung, sondern kann auch für die Rangfolgeverarbeitung in komplexen Szenarien verwendet werden Ich habe gehört, dass manche Leute mit nur einer Funktion die halbe Excel-Welt erobert haben ... also müssen sie es lernen.
Grundlegende Syntax
Schauen wir uns zunächst die grundlegende Syntax an. Die offizielle Syntaxbeschreibung von SUMPRODUCT besteht darin, die entsprechenden Elemente zwischen den Arrays in bestimmten Array-Sätzen zu multiplizieren und die Summe der Produkte zurückzugeben. Das Syntaxformat ist wie folgt:
rrree– SUM bedeutet Summation, PRODUCT bedeutet Multiplikation. Die Parameter werden multipliziert und dann summiert. SUMPRODUCT macht seinem Namen alle Ehre.
Schauen Sie sich meine Hand an, Wai, Tu, Sri... Zusammenfassend weist die SUMPRODUCT-Funktion die folgenden drei Eigenschaften auf:
1> Sie führt standardmäßig Array-Operationen aus.
2> Nicht-numerische Array-Elemente in den Parametern werden als 0 behandelt.
3> Die Parameter müssen die gleiche Größe haben, sonst wird ein Fehlerwert zurückgegeben.
Funktionsanalyse
Nachdem sie den Lebenslauf von SUMPRODUCT gelesen haben, müssen viele Freunde ihn im Nebel betrachten und nur eine vage Vorstellung davon haben. Was bedeuten diese Eigenschaften davon? Welche Art von Arbeit kann es leisten? Tatsächlich ist es nicht klar.
Schnippen Sie mit den Fingern und ich gebe Ihnen ein paar Beispiele.
Wie in der Datentabelle oben gezeigt, ist Spalte C der Stückpreis des Produkts und Spalte D die Verkaufsmenge. Jetzt müssen wir den Gesamtumsatz in Zelle C9 berechnen.
C9 Geben Sie die folgende Formel ein, um das Ergebnis 11620,60 zu erhalten
=SUMPRODUCT（array1,array2,array3, …）
Dies ist eine einfache SUMPRODUCT-Funktion. Sein Operationsprozess ist: Multiplizieren Sie die Elemente in den beiden Bereichsarrays C3:C7 bzw. D3:D7, also C3*D3, C4*D4, C5*D5 ..., bis C7*D7
gleich ist Berechnen Sie zunächst den Verkaufsbetrag jedes Artikels und fassen Sie ihn schließlich zusammen.
Aufgrund der ersten Funktion der SUMPRODUCT-Funktion unterstützt sie Operationen zwischen Arrays. Obwohl die Formel mehrere Operationen ausführt, ist es nicht erforderlich, die drei Array-Tasten
Einige Freunde sagten, dass die Formel auch so geschrieben werden kann:
=SUMPRODUCT(C3:C7, D3:D7)
Oder Sie können die folgende Array-Formel verwenden.
=SUMPRODUCT(C3:C7*D3:D7)
Was ist also der Unterschied zwischen diesen drei Formeln?
Zuallererst erfordert die SUMPRODUCT-Funktion in den meisten Fällen nicht die Array-Dreifachtaste, um die Formeleingabe zum Ausführen von Array-Operationen zu beenden, die SUM-Funktion hingegen schon.
Zweitens sprechen wir über ein weiteres sehr wichtiges Merkmal der SUMPRODUCT-Funktion.
…
Wir haben die obige Tabelle leicht modifiziert und die Verkaufsmenge von „Stiften“ geändert auf: Noch nicht gezählt. Außerdem ist es notwendig, den Gesamtumsatz in Zelle C9 zu berechnen.
Wenn Sie zu diesem Zeitpunkt die Formel:
=SUM(C3:C7*D3:D7)
oder die Array-Formel:
=SUMPRODUCT(C3:C7*D3:D7)
verwenden, wird der Fehlerwert #VALUE zurückgegeben!
Der Grund, warum der Fehlerwert zurückgegeben wird, ist, dass Zelle D4 „Noch nicht gezählt“ ist ein Textwert. Textwerte können nicht direkt an mathematischen Operationen teilnehmen, daher gibt C4*D4 den Fehlerwert #VALUE! zurück, was dazu führt, dass das Ergebnis der gesamten Formel einen Fehlerwert zurückgibt.
Bei Verwendung der folgenden Formel gibt es kein solches Problem und das korrekte Ergebnis wird direkt zurückgegeben:
=SUMPRODUCT(C3:C7,D3:D7)
Dies ist die zweite Funktion der SUMPRODUCT-Funktion: Konvertieren von Nicht- Numerische Werte in Array-Elementen werden als 0 behandelt.
In diesem Beispiel ist der Wert der Zelle D4 „noch nicht gezählt“ Text und kein numerischer Wert. SUMPRODUCT behandelt ihn aktiv als Null, also ist C4*D4 das Ergebnis ebenfalls Null und die verbleibenden Array-Elemente werden als berechnet üblich, und wir erhalten ein Ergebnis von 11385,60.
Es ist zu beachten, dass SUMPRODUCT nicht numerische Array-Elemente als 0 behandelt. Die sogenannten nicht numerischen Array-Elemente umfassen logische Werte und Text, enthalten jedoch keine Fehlerwerte. Wenn das Array-Element einen Fehlerwert enthält, Die Formeln geben auch falsche Werte zurück, wie beispielsweise die erste Formel in diesem Beispiel.
…
Nachdem wir über die beiden Funktionen der SUMPRODUCT-Funktion gesprochen haben, sprechen wir über ihre dritte Funktion: Die Array-Parameter müssen die gleiche Größe haben, andernfalls wird ein Fehlerwert zurückgegeben.
Wir verwenden weiterhin das Beispiel im obigen Bild als Beispiel, um weiterhin den Gesamtumsatz des Produkts zu berechnen. Wenn wir die Formel in C9 eingeben:
=SUM(C3:C7*D3:D7)
Was wird das Ergebnis sein?
Fehlerwert: #VALUE!
Warum?
细心的你肯定已经注意到了，两个区域数组，C3:C7明显显比D3:D6多了一个元素，C3和D3结对子，C4和D4结对子……那么C7和谁结对子呢？女人们都嫁了，结果剩下一个光棍，这日子没法过了！一个萝卜一个坑，只有萝卜没有坑，这不是要萝卜死吗？
——于是SUMPRODUCT就不高兴了，它给你一个错误值#VALUE!，明确告诉你，和谐时代幸福岁月，日子不能这么过。
这就是SUMPRODUCT函数的第三个特点：数组参数必须有相同的尺寸，否则返回错误值。
下面是一道练习题，你看看，能用SUMPRODUCT函数做出来吗？
案例拓展
假设下面这张图，是某个公司工资发放的部分记录表（数据纯属虚拟，如有雷同，那是穿越）。A列是工资发放的时间，B列是员工所属的部门，C列是员工姓名，D列是相关员工领取的工资金额。
——那么，问题和广告都来了：
1
员工西门庆领取了几次工资？
这是一个单条件计数的问题,通常我们使用COUNTIF函数,但如果使用SUMPRODUCT函数，一般写成这样：
=SUMPRODUCT((C2:C13="西门庆")*1)
先判断C2:C13的值是否等于”西门庆”，相等则返回TRUE，不等则返回FALSE，由此建立一个有逻辑值构成的内存数组。
上文已经说过，SUMPRODUCT有一个特性，它会将非数值型的数组元素作为0处理，逻辑值自然是属于非数值型的数组元素，为了避免SUMPRODUCT函数把逻辑值视为0，造成统计错误，我们使用*1的方式，把逻辑值转化为数值，TRUE转化为1，FALSE转化为0，最后统计求和。
2
员工西门庆领取了多少工资？
这是一个单条件求和的问题,通常我们使用SUMIF函数,如果使用SUMPRODUCT函数，我们可以写成这样：
=SUMPRODUCT((C2:C13="西门庆")*D2:D13)
依然首先判断C2:C13的值是否等于”西门庆”，得到逻辑值FALSE或TRUE，再和D2:D13的值对应相乘。TRUE乘以数值，得到数值本身。FALSE乘以数值返回0。最后统计求和得出结果。
看完了上面两个问题，有些朋友可能会在心里想，貌似SUMPRODUCT能干的事，SUMIF和COUNTIF也能做到，而且做的更好，那么还要SUMPRODUCT干啥嘞？
乡亲们呐，话不能这么说，SUMPRODUCT可是上得厅堂下得厨房，对工作环境不挑不拣，它对参数类型没有啥特别要求，COUNTIF和SUMIF就不同了，他俩要求个别参数，必须是区域（Range型），不支持数组，比如下面这两个问题，COUNTIF和SUMIF就要绕了。
3
二月份外交部发放了几次工资？总额是多少？
第1个问题，二月份外交部发放了几次工资？
这是一个多条件计数的问题。
第一个条件，发放工资的时间必须是二月份；第二个条件，发放工资的部门必须是外交部。
如果使用多条件计数函数COUNTIFS，判断发放工资的时间是否属于六月份，会简单问题复杂化。而使用SUMPRODUCT函数，咱们可以把公式写成这样：
=SUMPRODUCT((MONTH(A2:A13)=2)*(B2:B13="外交部"))
……
第2个问题，统计二月份外交部发放了多少工资？
这是一个常见的多条件求和问题。
如果使用SUMIFS函数，判断发放工资的时间是否属于六月份，也会简单问题复杂化。
SUMPRODUCT跃然而至：
=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="外交部"),D2:D13)
或者：
=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="外交部")*D2:D13)
打个响指，关于这两个形式的SUMPRODUCT函数的区别，咱们上文已有详细说明——你还记得吗？
上面这个公式可以说是SUMPRODUCT多条件求和的典型用法啦，可以归纳为:
=SUMPRODUCT((条件一)*(条件二)……,求和区域)
4
二月份外交部和步兵部合计发放了多少工资？
解决了上面的问题，相信大家已经晓得如何计算二月份外交部发放多少工资了，那么二月份外交部和步兵部合计发了多少工资，又当怎么计算呢
我们经常见有些性格朴素的表亲们把公式写成这样：
=SUMPRODUCT((MONTH(A2:A13)=2)*(B2:B13="外交部")*D2:D13)+SUMPRODUCT((MONTH(A2:A13)=2)*(B2:B13="步兵部")*D2:D13)
这些表亲们估计心想，不就是计算两个部门吗？甭说两个，二十个咱也能算，一个加一个，一直加到二十个，世上无难事，只怕有心人嘛，一砖加一砖，长城就建成了，一泡加一泡，长江就奔流了……
呃……公式写的那么长，先不谈计算速度，首先它累手啊，万一写错了，又要修改，那也是麻烦他妈哭麻烦——麻烦死了。
其实我们可以写成这样：
=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13={"外交部","步兵部"})*D2:D13)5
排名应用
认识了SUMPRODUCT函数在条件计数和求和方面的用法，最后，咱们再来看一个它在排名上的使用方法。
如上图所示，某个月某个公司某些人领了某些工资，然后呢，他们想看看自己的工资，在部门内的排名情况，比如说步兵部的鲁智深都是老员工了，非常想知道自个工资在各自部门排几号。
当然啦，不排不知道，一排就傻掉。
SUMPRODUCT是这么解决这个问题的，D2输入公式向下复制：
=SUMPRODUCT(($A$2:$A$9=A2)*(C2<$C$2:$C$9))+1
（思考，为什么公式的最后+1，而不是直接写成如下：）
=SUMPRODUCT(($A$2:$A$9=A2)*(C2<=$C$2:$C$9))
结束语
唠唠叨叨说了这么多，眼睛都说酸麻了，是到了该结束的时候啦。
最后，请思考两个小问题：
第1个问题：下面SUMPRODUCT函数有几个参数？
=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="财务部")*D2:D13)
下面这个SUMPRODUCT函数又有几个参数？
=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="财务部")，D2:D13)
第二个问题：
SUMPRODUCT为什么有时候比SUMIF/COUNTIF计算速度慢？
相关学习推荐：excel教程
Das obige ist der detaillierte Inhalt vonLassen Sie uns über die SUMPRODUCT-Funktion von Excel sprechen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!
Heiße KI -Werkzeuge
Undresser.AI Undress
KI-gestützte App zum Erstellen realistischer Aktfotos
AI Clothes Remover
Online-KI-Tool zum Entfernen von Kleidung aus Fotos.
Undress AI Tool
Ausziehbilder kostenlos
Clothoff.io
KI-Kleiderentferner
Video Face Swap
Tauschen Sie Gesichter in jedem Video mühelos mit unserem völlig kostenlosen KI-Gesichtstausch-Tool aus!
Heißer Artikel
Heiße Werkzeuge
Notepad++7.3.1
Einfach zu bedienender und kostenloser Code-Editor
SublimeText3 chinesische Version
Chinesische Version, sehr einfach zu bedienen
Senden Sie Studio 13.0.1
Leistungsstarke integrierte PHP-Entwicklungsumgebung
Dreamweaver CS6
Visuelle Webentwicklungstools
SublimeText3 Mac-Version
Codebearbeitungssoftware auf Gottesniveau (SublimeText3)
Heiße Themen
Was soll ich tun, wenn die Rahmenlinie beim Drucken in Excel verschwindet?
Mar 21, 2024 am 09:50 AM
Wenn wir beim Öffnen einer Datei, die gedruckt werden muss, feststellen, dass die Tabellenrahmenlinie aus irgendeinem Grund in der Druckvorschau verschwunden ist, müssen wir uns rechtzeitig darum kümmern, wenn dies auch in Ihrem Ausdruck erscheint file Wenn Sie Fragen wie diese haben, dann schließen Sie sich dem Editor an, um den folgenden Kurs zu erlernen: Was soll ich tun, wenn die Rahmenlinie beim Drucken einer Tabelle in Excel verschwindet? 1. Öffnen Sie eine Datei, die gedruckt werden muss, wie in der Abbildung unten gezeigt. 2. Wählen Sie alle erforderlichen Inhaltsbereiche aus, wie in der Abbildung unten dargestellt. 3. Klicken Sie mit der rechten Maustaste und wählen Sie die Option „Zellen formatieren“, wie in der Abbildung unten gezeigt. 4. Klicken Sie oben im Fenster auf die Option „Rahmen“, wie in der Abbildung unten gezeigt. 5. Wählen Sie links das dünne, durchgezogene Linienmuster im Linienstil aus, wie in der Abbildung unten gezeigt. 6. Wählen Sie „Äußerer Rand“
So filtern Sie in Excel mehr als 3 Schlüsselwörter gleichzeitig
Mar 21, 2024 pm 03:16 PM
Excel wird im Büroalltag häufig zur Verarbeitung von Daten verwendet und es ist oft notwendig, die Funktion „Filter“ zu nutzen. Wenn wir uns für die „Filterung“ in Excel entscheiden, können wir nur bis zu zwei Bedingungen für dieselbe Spalte filtern. Wissen Sie also, wie man in Excel mehr als drei Schlüsselwörter gleichzeitig filtert? Lassen Sie mich es Ihnen als Nächstes demonstrieren. Die erste Methode besteht darin, die Bedingungen schrittweise zum Filter hinzuzufügen. Wenn Sie drei qualifizierende Details gleichzeitig herausfiltern möchten, müssen Sie zunächst eines davon Schritt für Schritt herausfiltern. Zu Beginn können Sie anhand der Konditionen zunächst Mitarbeiter mit dem Namen „Wang“ herausfiltern. Klicken Sie dann auf [OK] und aktivieren Sie dann in den Filterergebnissen die Option [Aktuelle Auswahl zum Filter hinzufügen]. Die Schritte sind wie folgt. Führen Sie die Filterung ebenfalls separat erneut durch
So ändern Sie den Kompatibilitätsmodus für Excel-Tabellen in den Normalmodus
Mar 20, 2024 pm 08:01 PM
Bei unserer täglichen Arbeit und beim Lernen kopieren wir Excel-Dateien von anderen, öffnen sie, um Inhalte hinzuzufügen oder sie erneut zu bearbeiten, und speichern sie dann. Manchmal wird ein Dialogfeld zur Kompatibilitätsprüfung angezeigt, was sehr problematisch ist Software, kann es in den Normalmodus geändert werden? Im Folgenden wird Ihnen der Herausgeber detaillierte Schritte zur Lösung dieses Problems vorstellen. Lassen Sie uns gemeinsam lernen. Denken Sie abschließend daran, es zu speichern. 1. Öffnen Sie ein Arbeitsblatt und zeigen Sie im Namen des Arbeitsblatts einen zusätzlichen Kompatibilitätsmodus an, wie in der Abbildung dargestellt. 2. In diesem Arbeitsblatt wird nach dem Ändern des Inhalts und dem Speichern immer das Dialogfeld des Kompatibilitätsprüfers angezeigt. Es ist sehr schwierig, diese Seite zu sehen, wie in der Abbildung gezeigt. 3. Klicken Sie auf die Office-Schaltfläche, klicken Sie auf Speichern unter und dann
So geben Sie den Index in Excel ein
Mar 20, 2024 am 11:31 AM
eWir verwenden häufig Excel, um einige Datentabellen usw. zu erstellen. Manchmal müssen wir bei der Eingabe von Parameterwerten eine bestimmte Zahl hochstellen oder tiefstellen Schauen Sie sich die detaillierten Schritte an: 1. Hochgestellte Methode: 1. Geben Sie zunächst a3 (3 ist hochgestellt) in Excel ein. 2. Wählen Sie die Zahl „3“, klicken Sie mit der rechten Maustaste und wählen Sie „Zellen formatieren“. 3. Klicken Sie auf „Hochgestellt“ und dann auf „OK“. 4. Schauen Sie, der Effekt ist so. 2. Tiefgestellte Methode: 1. Geben Sie ähnlich wie bei der hochgestellten Einstellungsmethode „ln310“ (3 ist der tiefgestellte Index) in die Zelle ein, wählen Sie die Zahl „3“ aus, klicken Sie mit der rechten Maustaste und wählen Sie „Zellen formatieren“. 2. Aktivieren Sie „Tiefstellen“ und klicken Sie auf „OK“.
So setzen Sie in Excel hochgestellt
Mar 20, 2024 pm 04:30 PM
Bei der Verarbeitung von Daten stoßen wir manchmal auf Daten, die verschiedene Symbole wie Vielfache, Temperaturen usw. enthalten. Wissen Sie, wie man in Excel hochgestellte Zeichen setzt? Wenn wir Excel zum Verarbeiten von Daten verwenden und keine hochgestellten Zeichen setzen, wird die Eingabe vieler unserer Daten schwieriger. Heute stellt Ihnen der Editor die spezifische Einstellungsmethode für Excel-Hochstellung vor. 1. Öffnen wir zunächst das Microsoft Office Excel-Dokument auf dem Desktop und wählen Sie den Text aus, der hochgestellt werden soll, wie in der Abbildung gezeigt. 2. Klicken Sie dann mit der rechten Maustaste und wählen Sie die Option „Zellen formatieren“ im Menü, das nach dem Klicken erscheint, wie in der Abbildung gezeigt. 3. Als nächstes im Dialogfeld „Zellen formatieren“, das automatisch angezeigt wird
So verwenden Sie die iif-Funktion in Excel
Mar 20, 2024 pm 06:10 PM
Die meisten Benutzer verwenden Excel, um Tabellendaten zu verarbeiten. Abgesehen von Experten haben nicht viele Benutzer diese Funktion beim Schreiben in VBA verwendet Die Funktionen der Funktionen sind ähnlich. Lassen Sie mich Ihnen die Verwendung der iif-Funktion vorstellen. Es gibt iif-Funktionen in SQL-Anweisungen und VBA-Code in Excel. Die iif-Funktion ähnelt der IF-Funktion im Excel-Arbeitsblatt. Sie führt eine Beurteilung von wahren und falschen Werten durch und gibt unterschiedliche Ergebnisse basierend auf den logisch berechneten wahren und falschen Werten zurück. IF-Funktionsverwendung ist (Bedingung, ja, nein). IF-Anweisung und IIF-Funktion in VBA Die erstere IF-Anweisung ist eine Steueranweisung, die je nach Bedingungen unterschiedliche Anweisungen ausführen kann, während die letztere
Wo wird der Excel-Lesemodus eingestellt?
Mar 21, 2024 am 08:40 AM
Beim Studium von Software sind wir es gewohnt, Excel zu verwenden, nicht nur, weil es praktisch ist, sondern auch, weil es eine Vielzahl von Formaten erfüllen kann, die in der tatsächlichen Arbeit benötigt werden, und Excel sehr flexibel zu verwenden ist, und es gibt einen Modus dafür Praktisch zum Lesen. Heute habe ich für alle mitgebracht: Wo man den Excel-Lesemodus einstellt. 1. Schalten Sie den Computer ein, öffnen Sie dann die Excel-Anwendung und suchen Sie die Zieldaten. 2. Es gibt zwei Möglichkeiten, den Lesemodus in Excel festzulegen. Der erste: In Excel gibt es eine Vielzahl praktischer Verarbeitungsmethoden, die im Excel-Layout verteilt sind. In der unteren rechten Ecke von Excel gibt es eine Verknüpfung zum Festlegen des Lesemodus. Klicken Sie darauf, um in den Lesemodus zu gelangen. Auf der rechten Seite des Kreuzstrichs befindet sich eine kleine dreidimensionale Markierung .
So fügen Sie Excel-Symbole in PPT-Folien ein
Mar 26, 2024 pm 05:40 PM
1. Öffnen Sie die PPT und blättern Sie zu der Seite, auf der Sie das Excel-Symbol einfügen müssen. Klicken Sie auf die Registerkarte Einfügen. 2. Klicken Sie auf [Objekt]. 3. Das folgende Dialogfeld wird angezeigt. 4. Klicken Sie auf [Aus Datei erstellen] und dann auf [Durchsuchen]. 5. Wählen Sie die einzufügende Excel-Tabelle aus. 6. Klicken Sie auf OK und die folgende Seite wird angezeigt. 7. Aktivieren Sie [Als Symbol anzeigen]. 8. Klicken Sie auf OK.
