Eine der häufigsten Fragen im Message Board ist, wie die SumIf-Funktion unter zwei verschiedenen Bedingungen verwendet werden kann. Leider ist die Antwort, dass SumIf nicht mit zwei verschiedenen Bedingungen umgehen kann.
Um zwei Bedingungen zu erfüllen, müssen Sie eine ziemlich komplizierte Array-Formel verwenden. Mit dem Assistenten für bedingte Summen können Sie diese komplizierten Formeln problemlos eingeben.
Hier ist ein Excel-Arbeitsblatt mit Spalten für Produkt, Vertriebsmitarbeiter und Vertrieb. Die Daten befinden sich in den Zellen A2: C29.
Wenn Sie den Umsatz summieren möchten, funktioniert eine einfache SUM () - Funktion. =SUM(C2:C29).
Viele Exceller entdecken die SumIf-Funktion. Mit dieser Funktion ist es ziemlich einfach, den Gesamtumsatz für das Produkt ABC zu ermitteln.=SUMIF(A2:A29,E2,C2:C29)
Es ist auch einfach, die Gesamtsumme der Verkäufe von Vertriebsmitarbeiter Joe mit herauszufinden =SUMIF(B2:B29,E2,C2:C29).
Sie würden dann annehmen, dass es möglich ist, den Gesamtumsatz des von Joe hergestellten Produkts ABC zu ermitteln. Mit der SumIf-Funktion ist dies jedoch nicht möglich. Es stellt sich heraus, dass Sie ein ziemlich komplexes Array oder eine CSE-Formel verwenden müssen.
Seien wir ehrlich - die Summenformel lautet Excel 101. Die SumIf-Formel ist in ihrer Komplexität nicht weit entfernt. Die CSE-Formel zur Berechnung der Gesamtzahl der von Joe getätigten ABC-Verkäufe reicht jedoch aus, um selbst meinen Kopf zu drehen.
Die gute Nachricht: Microsoft bietet den Assistenten für bedingte Summen an, mit dem selbst Anfänger komplexe bedingte Formeln basierend auf 1, 2 oder mehr Bedingungen eingeben können. Der Assistent für bedingte Summen ist ein Add-In. Um diese Funktionalität zu Excel hinzuzufügen, gehen Sie zum Menü Extras und wählen Sie Add-Ins. Aktivieren Sie im Dialogfeld Add-Ins das Kontrollkästchen neben dem Assistenten für bedingte Summen und klicken Sie auf OK. Möglicherweise benötigen Sie zu diesem Zeitpunkt Ihre Installations-CD, da Microsoft den Assistenten nicht in die Standardinstallation einbezieht.
Sobald das Add-In erfolgreich aktiviert wurde, wird unten im Menü Extras die Option Bedingte Summe… angezeigt.
Wählen Sie eine einzelne Zelle in Ihrem Dataset aus und wählen Sie Extras - Bedingte Summe. Unter der Annahme, dass Ihre Daten mit einer einzigen Zeile von Überschriften gut formatiert sind, wird Excel den Bereich Ihrer Daten richtig erraten. Wählen Sie Weiter.
Wählen Sie in Schritt 2 die zu summierende Spalte aus. In diesem Fall hat der Assistent bereits vermutet, dass Sie die erste (und einzige) numerische Spalte - Verkäufe - summieren möchten. In der Mitte des Dialogfelds befinden sich drei Dropdown-Steuerelemente. Diese sind für die erste Bedingung korrekt - Produkt entspricht ABC. Wählen Sie daher die Schaltfläche Bedingung hinzufügen.
Dann können Sie Ihre zweite Bedingung hinzufügen. In diesem Fall möchten Sie angeben, dass der Vertriebsmitarbeiter Joe ist. Wählen Sie den Pfeil für das erste Dropdown. Excel bietet eine alphabetische Liste der verfügbaren Spaltennamen. Wählen Sie Vertriebsmitarbeiter.
Die mittlere Dropdown-Liste ist korrekt, aber der Vollständigkeit halber können Sie hier sehen, dass Sie gleich, kleiner als, größer als, kleiner als oder gleich, größer als oder gleich oder nicht gleich gewählt haben könnten.
Wählen Sie in der dritten Dropdown-Liste Joe aus.
Wählen Sie die Schaltfläche Bedingung hinzufügen.
Jetzt können Sie mit Schritt 3 fortfahren. Klicken Sie auf die Schaltfläche Weiter.
In Schritt 3 haben Sie zwei Möglichkeiten. Bei der ersten Auswahl gibt der Assistent eine einzelne Formel mit den in die Formel fest codierten Werten "ABC" und "Joe" ein. Sie erhalten die Antwort, aber es gibt keine Möglichkeit, die Formel einfach zu ändern. Bei der zweiten Auswahl richtet Excel eine neue Zelle mit dem Wert "ABC" und eine neue Zelle mit dem Wert "Joe" ein. Eine dritte Zelle enthält die Formel, die eine bedingte Summe basierend auf diesen beiden Werten erstellt. Mit dieser Option können Sie neue Werte in die Zellen eingeben, um die von Adam insgesamt verkauften XYZs anzuzeigen.
Der Assistent fragt dann, wo Sie den Wert für ABC haben möchten. Wählen Sie eine Zelle aus und wählen Sie Weiter. Wiederholen Sie diesen Vorgang, während der Assistent Sie auffordert, eine Zelle für Joe und die Formel auszuwählen.
Wenn Sie im letzten Schritt Fertig stellen wählen, erstellt Excel eine etwas andere (aber gültige) Version der CSE-Formel.
Diese Formel berechnet, dass Joe ABC 33.338 USD verkauft hat.
Wenn Sie die Produkteingabezelle von ABC in DEF ändern, wird die Formel neu berechnet, um anzuzeigen, dass Joe DEF im Wert von 24.478 USD verkauft hat.
Mit dem Assistenten für bedingte Summen sind komplexe Formeln für alle Excel-Besitzer gut erreichbar.
Zusätzliche Information:Wenn Sie eine Tabelle erstellen möchten, in der die Verkäufe jedes Produkts von jedem Vertriebsmitarbeiter angezeigt werden, müssen Sie einige spezielle "Pflege- und Fütterungsmaßnahmen" für diese Formeln kennen. Geben Sie jeden Vertriebsmitarbeiter im oberen Bereich des Bereichs ein. Geben Sie jedes Produkt in die linke Spalte des Sortiments ein. Bearbeiten Sie die vom Assistenten bereitgestellte Formel. In der Abbildung unten zeigt die Formel auf das Produkt in Zelle E6. Diese Referenz muss wirklich $ E6 sein. Wenn Sie die Referenz als E6 belassen und die Formel in Spalte G kopieren, würde die Formel F6 anstelle von E6 anzeigen, und dies wäre falsch. Durch Hinzufügen eines Dollarzeichens vor dem E in E6 wird sichergestellt, dass die Formel immer das Produkt in Spalte E betrachtet. Die Formel zeigt auch auf einen Vertriebsmitarbeiter in Zelle F5. Diese Referenz muss wirklich F $ 5 sein. Wenn Sie die Referenz als F5 belassen und in Zeile 7 kopieren,Die F5-Referenz ändert sich in F6 und dies ist nicht richtig. Durch Hinzufügen eines Dollarzeichens vor der Zeilennummer wird die Zeilennummer gesperrt, und die Referenz zeigt immer auf Zeile 5.
Geben Sie im Bearbeitungsmodus (wählen Sie die Zelle aus und drücken Sie zum Bearbeiten F2) ein $ vor dem E. Geben Sie ein Dollarzeichen vor der 5 in F5 ein. Drücken Sie noch nicht die Eingabetaste!
Diese Formel ist eine spezielle Art von Formel. Wenn Sie die Eingabetaste drücken, erhalten Sie eine 0, was nicht korrekt ist.
Anstatt die Eingabetaste einzugeben, halten Sie die Strg- und Umschalttaste gedrückt, während Sie die Eingabetaste drücken. Diese magische Kombination von C trl + S hift + E nter ist der Grund, warum ich diese CSE-Formeln nenne.
Bevor Sie die Formel in den Rest der Tabelle kopieren, müssen Sie noch eine letzte Überlegung anstellen. Möglicherweise möchten Sie F6 kopieren und in F6: G8 einfügen. Wenn Sie dies versuchen, erhalten Sie in Excel die rätselhafte Meldung "Sie können einen Teil eines Arrays nicht ändern". Excel beschwert sich, dass Sie eine CSE-Formel nicht in einen Bereich einfügen können, der die ursprüngliche CSE-Formel enthält.
Es ist einfach, dies zu umgehen. Kopieren Sie F6. Einfügen in F7: F8.
Kopieren Sie F6: F8. Einfügen in G6: G8. Sie erhalten eine Tabelle mit CSE-Formeln, in der die Summen basierend auf zwei Bedingungen angezeigt werden.
