Wie summiere ich in Excel nach Farben? (2 nützliche Methoden)
Die beiden wichtigsten Methoden zum Summieren nach Farben in Excel lauten wie folgt:
- Verwendung der SUBTOTAL-Formel in Excel und Filterung nach Farbfunktion.
- Anwenden der GET.CELL-Formel durch Definieren des Namens auf der Registerkarte Formel und Anwenden der SUMIF-Formel in Excel, um die Werte nach Farbcodes zusammenzufassen.
Lassen Sie uns jeden von ihnen im Detail besprechen -
Sie können diese Excel-Vorlage für Summe nach Farbe hier herunterladen - Excel-Vorlage für Summe nach Farbe# 1 - Summe nach Farbe mit Zwischensummenfunktion
Um den Ansatz zur Berechnung der Wertesumme nach Hintergrundfarben zu verstehen, betrachten wir die folgende Datentabelle, die Einzelheiten zu Beträgen in US-Dollar nach Region und Monat enthält.
- Schritt 1: Angenommen, wir möchten diejenigen Zellen hervorheben, deren Werte zu Anzeigezwecken negativ sind. Dies kann erreicht werden, indem entweder eine bedingte Formatierung angewendet wird oder indem die Zellen manuell hervorgehoben werden, wie unten gezeigt.
- Schritt 2: Um nun die Summe der Zellen zu erhalten, die in Excel gefärbt sind, geben Sie die Formel für SUBTOTAL unter der Datentabelle ein. Die Syntax für die SUBTOTAL- Formel ist unten dargestellt.
Die Formel, die zur Berechnung der Summe eingegeben wird, lautet
= Zwischensumme (9, D2: D13)
Hier bezieht sich die Zahl '9' im Argument function_num auf die Summenfunktionalität , und das Referenzargument wird als Bereich der zu berechnenden Zellen angegeben. Unten ist der Screenshot für das gleiche.
- Schritt 3: Wie im obigen Screenshot zu sehen ist, wurde eine Summe des USD-Betrags berechnet. Um die Beträge zu berechnen, die in einer hellroten Hintergrundfarbe hervorgehoben sind. Wenden Sie einen Filter auf die Datentabelle an, indem Sie zu Daten gehen und dann einen Filter auswählen.
- Schritt 4: Wählen Sie dann Filter nach Farbe und wählen Sie die hellrote Zellenfarbe unter 'Nach Zellenfarbe filtern'. Unten sehen Sie den Screenshot, um den Filter besser zu beschreiben.
- Schritt 5: Sobald der Excel-Filter angewendet wurde, wird die Datentabelle nur nach hellroten Hintergrundzellen gefiltert, und die am Ende der Datentabelle angewendete Zwischensummenformel zeigt die Summe der farbigen Zellen an, die wie unten gezeigt gefiltert werden.
Wie im obigen Screenshot gezeigt, wird die Berechnung der farbigen Zelle in Zelle E17, Zwischensummenformel, erreicht.
# 2 - Summe nach Farbe mit der Funktion Get.Cell
Der zweite Ansatz wird erläutert, um die Summe der Farbzellen in Excel zu erhalten. Dies wird im folgenden Beispiel erläutert. Betrachten Sie die Datentabelle, um die Methodik besser zu verstehen.
- Schritt 1: Lassen Sie uns nun die Liste der Zellen in der Spalte USD-Betrag markieren, die wir bereit sind, die gewünschte Summe farbiger Zellen zu erhalten, wie unten gezeigt.
- Schritt 2: Wie wir im obigen Screenshot sehen können, haben wir im Gegensatz zum ersten Beispiel hier mehrere Farben. Dabei verwenden wir die Formel = GET.CELL, indem wir sie im Namensfeld definieren und nicht direkt in Excel verwenden.
- Schritt 3: Sobald das Dialogfeld für "Name definieren" angezeigt wird, geben Sie den Namen und die Formel für GET.CELL unter "Verweisen auf" ein (siehe Abbildung unten).
Wie im obigen Screenshot zu sehen ist, lautet der für die Funktion eingegebene Name 'CellColor' und die Formel = GET.CELL (38, 'Beispiel 2! $ D2) ist in' Refers to 'einzugeben. Innerhalb der Formel bezieht sich die Zahl 38 auf die Zellcodeinformationen und das zweite Argument ist die Zellennummer D2, die sich auf die Referenzzelle bezieht. Klicken Sie nun auf OK.
- Schritt 4: Geben Sie nun den Funktionsnamen 'CellColor' in die Zelle neben der farbcodierten Zelle ein, die im Dialogfeld definiert wurde, wie in Schritt 3 erläutert.
Wie im obigen Screenshot zu sehen ist, wird die Funktion 'CellColor' eingegeben, die den Farbcode für die Hintergrundzellenfarbe zurückgibt.
Ebenso wird die Formel für die gesamte Spalte gezogen.
- Schritt 5: Um nun die Summe der Werte nach Farben in Excel zu erhalten, geben wir die SUMIF-Formel ein. Die Syntax für die SUMIF-Formel lautet wie folgt: -
Wie aus dem obigen Screenshot ersichtlich, werden die folgenden Argumente in die SUMIF-Formel eingegeben: -
- Das Bereichsargument wird für den Zellenbereich E2: E13 eingegeben
- Die Kriterien werden als G2 eingegeben, dessen zusammengefasste Werte abgerufen werden müssen.
- Der Zellenbereich wird als D2: D13 eingegeben
Die SUMIF-Formel wird für alle Farbcode-Nummern, für die Werte addiert werden sollen, nach unten gezogen.
Dinge, an die man sich erinnern sollte
- Bei Verwendung des Ansatzes der SUBTOTAL-Formel können Benutzer mit dieser Funktion jeweils nur nach einer Filterfarbe filtern. Darüber hinaus kann mit dieser Funktionalität nur eine Wertespalte nach Filterfarben hinzugefügt werden. Wenn mehr als eine Spalte mit unterschiedlichen Farben nach Zeilen in der unterschiedlichen Spalte vorhanden ist, zeigt SUBTOTAL möglicherweise nur für eine bestimmte Spalte das richtige Ergebnis für einen Filter nach Farbe an.
- Die GET.CELL-Formel in Kombination mit dem SUMIF-Ansatz beseitigt diese Einschränkung, da diese Funktionalität verwendet werden kann, um Farben für mehrere Farbcodes im Zellenhintergrund zusammenzufassen.
