Wie zählt oder summiert man Zellen basierend auf der Zellenfarbe in einem Google-Tabellenblatt?
Bei täglicher Arbeit mit Tabellenkalkulationen kann es vorkommen, dass Sie Zellenwerte entsprechend einer bestimmten Hintergrundfarbe zählen oder summieren müssen, wie im folgenden Screenshot dargestellt. Zum Beispiel könnten Sie nur diejenigen Zellen zusammenzählen oder summieren wollen, die in einer bestimmten Farbe hervorgehoben sind, um Daten schnell nach Kategorie oder Status zu analysieren. Dieser Leitfaden behandelt, wie dies nicht nur in Google Sheets, das keine eingebaute Unterstützung für solche farbbasierten Berechnungen bietet, sondern auch in Microsoft Excel erreicht werden kann, das mehrere verschiedene Ansätze bietet – von integrierten Funktionen bis hin zu fortschrittlichen Hilfsprogrammen.
Das Verständnis, wie man farbbasierte Datenanalysen durchführt, kann Ihre Arbeit effizienter machen, insbesondere wenn Farben verwendet werden, um Status, Prioritäten oder Kategorien zu markieren. Wir werden auch verschiedene Lösungen besprechen, ihre Anwendungsszenarien vergleichen und praktische Tipps zur Bedienung sowie Fehlerhinweise bereitstellen, um sicherzustellen, dass Ihre Aufgaben reibungslos verlaufen.
- Zählen von Zellwerten basierend auf der Zellenfarbe mit einem Skript in Google Sheets
- Summieren von Zellwerten basierend auf der Zellenfarbe mit einem Skript in Google Sheets
- Zählen oder Summieren von Zellwerten basierend auf der Zellenfarbe mit Kutools für Excel in Microsoft Excel
Zählen von Zellwerten basierend auf der Zellenfarbe mit einem Skript in Google Sheets
Google Sheets bietet keine direkte Möglichkeit, Zellen basierend auf der Hintergrundfarbe zu zählen. Allerdings können Sie dies mit einem benutzerdefinierten Apps-Skript erreichen. Dieses Skript fungiert als eine vom Benutzer definierte Funktion, sodass Sie es wie eine Formel referenzieren können. So richten Sie das Skript ein und verwenden es:
1. Klicken Sie auf Tools > Scripteditor, um auf die Skriptumgebung zuzugreifen. Siehe Screenshot:
2. Wählen Sie im Projekt-Fenster Datei > Neu > Skriptdatei, um ein neues Code-Modul zu öffnen, wie gezeigt:
3. Wenn Sie dazu aufgefordert werden, geben Sie einen Namen für Ihr neues Skript ein und bestätigen Sie. Benennen Sie das Skript sinnvoll, um später dessen Zweck zu identifizieren.
4. Klicken Sie auf OK, dann kopieren und fügen Sie den folgenden Code ein, um jeglichen Beispielcode im Modul zu ersetzen. Stellen Sie sicher, dass Sie ihn genau so einfügen, wie er bereitgestellt wird.
function countColoredCells(countRange,colorRef) {
var activeRg = SpreadsheetApp.getActiveRange();
var activeSht = SpreadsheetApp.getActiveSheet();
var activeformula = activeRg.getFormula();
var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
var BackGround = activeSht.getRange(colorRefAddress).getBackground();
var countCells = 0;
for (var i = 0; i < backGrounds.length; i++)
for (var k = 0; k < backGrounds[i].length; k++)
if ( backGrounds[i][k] == BackGround )
countCells = countCells + 1;
return countCells;
};
5. Speichern Sie diese Skriptdatei, kehren Sie zu Ihrem Blatt zurück und verwenden Sie die neue Funktion wie jede andere Formel in Google Sheets. Geben Sie: =countcoloredcells(A1:E11,A1) in eine leere Zelle ein, um die Zellen im Bereich A1:E11 zu zählen, die der Farbe von A1 entsprechen. Drücken Sie die Eingabetaste, um das Ergebnis zu erhalten. Falls Sie nach Berechtigungen gefragt werden, autorisieren Sie das Skript, in Ihrem Blatt zu laufen.
Hinweis: A1:E11 ist Ihr Datenbereich; A1 ist die Referenzzelle der zu zählenden Farbe. Stellen Sie sicher, dass die Referenzzellen exakt die Farbe haben und vermeiden Sie verbundene Zellen für die beste Zuverlässigkeit.
6. Um andere Farben zu zählen, wiederholen Sie die Formel mit einer anderen Farbreferenzzelle nach Bedarf. Wenn sich Ihr Bereich ändert, passen Sie den Bereich in der Formel entsprechend an.
Wenn Sie einen Fehler oder ein unerwartetes Ergebnis erhalten, überprüfen Sie nochmals, ob das Skript gespeichert wurde und dass Sie die korrekte Farbreferenz verwendet haben. Apps-Skript-basierte Funktionen werden nur neu berechnet, wenn sich die Funktion oder ihre Argumente ändern – wenn Sie Zellen später neu einfärben, geben Sie die Formel erneut ein oder drücken Sie die Eingabetaste erneut, um sie zu aktualisieren.
Summieren von Zellwerten basierend auf der Zellenfarbe mit einem Skript in Google Sheets
Das Summieren von Zellwerten basierend auf einer definierten Zellenfarbe in Google Sheets erfordert einen ähnlichen Ansatz unter Verwendung eines Apps-Skripts. Dies ist besonders nützlich für Finanztabellen, Statusprotokolle oder jedes Szenario, in dem Farben Kategorien mit darunterliegenden numerischen Daten darstellen.
1. Öffnen Sie in Google Sheets den Scripteditor über Tools > Scripteditor. Wählen Sie im Projekt-Fenster Datei > Neu > Skriptdatei, um ein neues Code-Modul hinzuzufügen. Geben Sie im Dialogfeld einen eindeutigen Namen ein, um dessen Zweck zu verfolgen, zum Beispiel „SumColoredCells“. Bestätigen Sie, um das Modul zu erstellen.
2. Klicken Sie auf OK, und ersetzen Sie in dem neuen Code-Modulfenster jeglichen Standardcode, indem Sie das bereitgestellte Skript zum Summieren farbiger Zellen einfügen. Stellen Sie sorgfältig sicher, dass der gesamte Code kopiert wird, da fehlende Zeichen zu Syntaxfehlern führen können.
function sumColoredCells(sumRange,colorRef) {
var activeRg = SpreadsheetApp.getActiveRange();
var activeSht = SpreadsheetApp.getActiveSheet();
var activeformula = activeRg.getFormula();
var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
var sumValues = activeSht.getRange(countRangeAddress).getValues();
var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
var BackGround = activeSht.getRange(colorRefAddress).getBackground();
var totalValue = 0;
for (var i = 0; i < backGrounds.length; i++)
for (var k = 0; k < backGrounds[i].length; k++)
if ( backGrounds[i][k] == BackGround )
if ((typeof sumValues[i][k]) == 'number')
totalValue = totalValue + (sumValues[i][k]);
return totalValue;
};
3. Nachdem Sie das Skript gespeichert haben, kehren Sie zu Ihrem Blatt zurück und geben Sie die Formel =sumcoloredcells(A1:E11,A1) in eine leere Zelle ein, dann drücken Sie die Eingabetaste. Diese Formel summiert Werte in A1:E11, bei denen die Hintergrundfarbe mit A1 übereinstimmt. Stellen Sie beim Verwenden dieser Funktion sicher, dass alle Zielsummenzellen numerisch sind; nicht-numerische Werte werden ignoriert.
Hinweis: A1:E11 repräsentiert Ihren Datenbereich, und A1 liefert die Farbreferenz. Die Formel summiert nur sichtbare numerische Werte – stellen Sie sicher, dass verbundene Zellen oder Fehler innerhalb des Bereichs Ihre Gesamtsummen nicht beeinträchtigen.
4. Sie können den oben genannten Prozess replizieren, um Werte für verschiedene Farbkategorien zu summieren, indem Sie die Farbreferenzzelle in Ihrer Formel ändern. Wenn Ihre Daten aktualisiert werden oder Sie die Hintergrundfarben ändern, denken Sie daran, die Formel für aktualisierte Ausgaben zu aktualisieren.
Wenn die Summe null oder ein Fehlerwert zurückgibt, überprüfen Sie, ob der Bereich Zahlen enthält und die Farbübereinstimmung exakt ist. Außerdem erfolgt die Neuberechnung nicht automatisch, wenn sich nur die Zellenfarbe ändert – bearbeiten Sie die Formelzelle, um eine Aktualisierung zu erzwingen.
Zählen oder Summieren von Zellwerten basierend auf der Zellenfarbe mit Kutools für Excel in Microsoft Excel
Beim Arbeiten in Microsoft Excel ist das Zählen oder Summieren von Zellen nach Farbe eine häufige Anforderung, insbesondere innerhalb von Projektmanagement, Inventar- oder Qualitätskontrollberichten. Kutools für Excel bietet ein dediziertes Hilfsmittel Count by Color, das es Ihnen ermöglicht, Zählungen und Summen direkt basierend auf der Hintergrund- oder Schriftfarbe zu erhalten – dies ist besonders hilfreich für größere Datenbereiche und wenn Sie schnelle, wiederholbare Ergebnisse benötigen.
Sobald Kutools für Excel installiert ist, gehen Sie wie folgt vor:
1. Markieren Sie den Bereich, in dem Sie nach Farbe zählen oder summieren möchten, dann klicken Sie auf Kutools Plus > Nach Farbe zählen. Sehen Sie sich den folgenden Screenshot zur Orientierung an:
2. Das Dialogfeld Nach Farbe zählen erscheint. Setzen Sie Standardformatierung unter Farbmodus, und wählen Sie Hintergrund für Zähltyp. Überprüfen Sie die Vorschau und Optionen sorgfältig:
3. Klicken Sie auf Bericht generieren, um ein neues Arbeitsblatt zu erstellen, das eine Aufschlüsselung der Zählungen und Summen für jede Farbe in Ihrem Bereich enthält. Dieser Bericht enthält sowohl die Anzahl als auch die Summe der farbigen Zellen, was eine einfache Referenz oder weitere Analyse ermöglicht.
Hinweis: Diese Funktion kann auch Werte basierend auf bedingter Formatierung oder Schriftfarbe berechnen. Verwenden Sie Regeln für bedingte Formatierung für dynamische Analysen; andernfalls funktioniert das Tool am besten für statische Farbfüllungen. Änderungen in den Farben der Quellzellen erfordern das erneute Ausführen des Hilfsmittels Nach Farbe zählen für aktualisierte Ergebnisse. Falls Probleme auftreten, bestätigen Sie, dass Kutools aktiv und auf dem neuesten Stand ist.
Klicken Sie hier, um Kutools für Excel herunterzuladen und eine kostenlose Testversion zu starten!
Die besten Produktivitätstools für das Büro
Stärken Sie Ihre Excel-Fähigkeiten mit Kutools für Excel und genießen Sie Effizienz wie nie zuvor. Kutools für Excel bietet mehr als300 erweiterte Funktionen, um die Produktivität zu steigern und Zeit zu sparen. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen...
Office Tab bringt die Tab-Oberfläche in Office und macht Ihre Arbeit wesentlich einfacher
- Aktivieren Sie die Tabulator-Bearbeitung und das Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Tabs innerhalb desselben Fensters, statt in neuen Einzelfenstern.
- Steigert Ihre Produktivität um50 % und reduziert hunderte Mausklicks täglich!
Alle Kutools-Add-Ins. Ein Installationspaket
Das Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro und ist ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- All-in-One-Paket — Add-Ins für Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Ein Installationspaket, eine Lizenz — in wenigen Minuten einsatzbereit (MSI-kompatibel)
- Besser gemeinsam — optimierte Produktivität in allen Office-Anwendungen
- 30 Tage kostenlos testen — keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis — günstiger als Einzelkauf der Add-Ins