Wie kann man Daten in Excel basierend auf Werten kategorisieren?
Bei vielen täglichen Excel-Datenverarbeitungsaufgaben müssen Sie möglicherweise Datenwerte gruppieren oder klassifizieren, um die Analyse und Berichterstellung zu erleichtern. Zum Beispiel könnten Sie bei der Verwaltung von Prüfungsergebnissen, Umsatzzahlen oder Umfrageergebnissen schnell Kategorien wie Hoch, Mittel und Niedrig basierend auf festgelegten Schwellenwerten zuweisen wollen. Angenommen, Sie haben einen Datensatz, in dem Sie jeden Wert größer als 90 als "Hoch", zwischen 60 und 90 als "Mittel" und weniger als 60 als "Niedrig" kennzeichnen möchten, wie im folgenden Screenshot dargestellt. Diese Art der Kategorisierung macht es viel einfacher, große Datensätze zu interpretieren und Trends oder Leistungen auf einen Blick zu kommunizieren. Wie können Sie diese Kategorisierung effizient in Excel erreichen?
Daten basierend auf Werten mit der WENN-Funktion kategorisieren
Für eine einfache Klassifizierung basierend auf einer kleinen Anzahl von Regeln können Sie die WENN-Funktion verwenden, um Kategorien entsprechend den von Ihnen festgelegten Wertebereichen zuzuweisen.
Diese Methode ist ideal, wenn Ihre Kategorisierungsregeln einfach und die Schwellenwerte festgelegt sind. Ihr größter Vorteil ist die Einfachheit, aber sie kann unhandlich werden, wenn es zu viele Kategorien gibt oder die Logik komplex wird.
Um Ihre Daten zu kategorisieren, gehen Sie wie folgt vor:
Schritt 1: Geben Sie die folgende Formel in eine leere Zelle ein (z. B. B2, vorausgesetzt, Ihre Werte beginnen bei A2):
=IF(A2>90,"High",IF(A2>60,"Medium","Low"))Schritt 2: Drücken Sie die Eingabetaste, um zu bestätigen. Ziehen Sie dann das Ausfüllkästchen nach unten, um die Formel auf die restlichen Daten anzuwenden. Werte werden nun wie unten gezeigt kategorisiert:
Parameter-Erklärung & Tipps:
- Die Formel überprüft den Wert in A2. Ist er größer als 90, ist das Ergebnis "Hoch". Wenn nicht, wird überprüft, ob er größer als 60 ist; wenn ja, wird "Mittel" zurückgegeben. Andernfalls wird "Niedrig" zugewiesen.
- Sie können die Schwellenwerte und Kategoriebezeichnungen (z. B. 90, 60) an Ihr Szenario anpassen.
- Wenn Ihre Daten in einer anderen Zeile beginnen, passen Sie "A2" entsprechend an.
- Überprüfen Sie doppelt die Größer-als / Kleiner-als-Zeichen, um eine korrekte Kategorisierung sicherzustellen.
Häufige Probleme & Fehlerbehebung:
- Wenn die Formel einen Fehler zurückgibt, überprüfen Sie auf zusätzliche Leerzeichen oder falsche Zellbezüge.
- Wenn die Ausgabe nicht den Erwartungen entspricht, stellen Sie sicher, dass die Reihenfolge der geschachtelten WENN-Logik korrekt ist.
Daten basierend auf Werten mit der SVERWEIS-Funktion kategorisieren
Wenn Sie komplexere Klassifizierungsregeln mit mehreren Kategorien verarbeiten müssen oder eine einfachere Möglichkeit zur Anpassung dieser Regeln wünschen, bietet die SVERWEIS-Funktion eine flexible Alternative. Dies ist besonders nützlich, wenn sich Ihre Kategorien oder Intervalle häufig ändern oder in einer separaten Referenztabelle gepflegt werden.
In dieser Methode definiert eine Nachschlagetabelle die Wertgrenzen und deren entsprechenden Kategorienamen. Dadurch können Sie Kategorielogiken leicht hinzufügen, entfernen oder aktualisieren, ohne einzelne Formeln zu ändern.
Schritt 1: Erstellen Sie eine Referenztabelle (z. B. in den Zellen F1:G6), wobei die linke Spalte die Mindestwerte für jede Kategorie auflistet und die rechte Spalte die entsprechenden Kategorienamen zeigt.
Schritt 2: Geben Sie die folgende Formel in eine leere Zelle ein, z. B. B2:
=VLOOKUP(A2,$F$1:$G$6,2,1)Schritt 3: Drücken Sie die Eingabetaste, ziehen Sie dann das Ausfüllkästchen, um die Formel auf die restlichen Daten anzuwenden. Ihre Werte werden entsprechend kategorisiert:
Hinweis: In der Formel:
- A2 ist Ihre Wertezelle.
- $F$1:$G$6 ist der Bereich der Nachschlagetabelle.
- 2 bezieht sich auf die Spalte mit den Kategoriebezeichnungen.
- 1 bedeutet ungefähre Übereinstimmung. Stellen Sie sicher, dass Spalte F in aufsteigender Reihenfolge sortiert ist.
Parameter-Erklärung & Tipps:
- Sie können die Nachschlagetabelle aktualisieren, um Änderungen in der Klassifizierungslogik widerzuspiegeln, ohne die Hauptformel zu bearbeiten.
- Stellen Sie sicher, dass Ihre Nachschlagetabelle nach dem minimalen Schwellenwert in aufsteigender Reihenfolge sortiert ist.
- Geeignet für die Handhabung vieler Kategorien oder komplexer Segmentierungsszenarien.
Häufige Probleme & Fehlerbehebung:
- Wenn die Formel
#NVzurückgibt, überprüfen Sie, ob der Wert innerhalb des Bereichs Ihrer Nachschlagetabelle liegt und dass die Tabelle korrekt sortiert ist. - Wenn Kategorien nicht übereinstimmen, überprüfen Sie, ob Ihre Grenzwerte in der linken Spalte logisch geordnet und für die Daten geeignet sind.
Daten visuell mit bedingter Formatierung kategorisieren
Die bedingte Formatierung in Excel ermöglicht es Ihnen, Datenkategorien visuell zu unterscheiden, ohne explizite Textbeschriftungen hinzuzufügen. Durch die Anwendung von Farbskalen, Datenbalken oder Symbolsätzen können Sie High-, Medium- und Low-Werte leicht hervorheben, um sie schnell zu interpretieren. Diese Methode ist ideal für Dashboards, Berichte und Analysen auf einen Blick, wo visuelle Hinweise effektiver sind als Text.
Typische Anwendungsfälle umfassen:
- Präsentation zusammengefasster Erkenntnisse während Besprechungen oder in Berichten.
- Hervorheben von Ausreißern oder Aufspüren von Trends über einen Datenbereich hinweg.
- Minimierung visueller Unordnung durch Vermeiden zusätzlicher Spalten oder Textbeschriftungen.
So wenden Sie die bedingte Formatierung an, um Ihre Daten zu kategorisieren:
- Wählen Sie den Datenbereich aus (z. B. A2:A20).
- Klicken Sie auf Start > Bedingte Formatierung.
- Für Farbskalen:
1）Wählen Sie Farbskalen, und wählen Sie eine dreifarbige Skala, die Niedrig, Mittel und Hoch darstellt.
2）Um Schwellenwerte anzupassen, gehen Sie zu Bedingte Formatierung > Regeln verwalten > Regel bearbeiten. - Für Symbolsätze:
1）Wählen Sie Symbolsätze (z. B. Ampeln, Pfeile).
2）Verwenden Sie dann Regeln verwalten > Regel bearbeiten um Schwellenwerte zu definieren, wie z. B.:
“Grün” für Werte > 90, “Gelb” für Werte > 60 und “Rot” für ≤ 60.
Tipps und Vorsichtsmaßnahmen:
- Bedingte Formatierung ändert nicht die zugrunde liegenden Daten oder Strukturen – Ihre Tabelle bleibt sauber.
- Um die Formatierung zu löschen oder zu ändern, verwenden Sie Bedingte Formatierung > Regeln löschen.
- Sie können dieselbe Formatierung mit dem Formatpinsel wiederverwenden.
- Passen Sie Farbthemen oder Symbolsätze nach Bedarf für Ihre Berichterstattung frei an.
Mögliche Probleme & Fehlerbehebung:
- Wenn falsche Symbole oder Farben erscheinen, überprüfen Sie Ihre Regel-Schwellenwerte erneut.
- Wenn die Formatierung auf den falschen Bereich angewendet wird, löschen Sie die Regeln und wenden Sie sie erneut auf die richtige Auswahl an.
Vorteile: Schnelle visuelle Kategorisierung ohne zusätzliche Spalten.
Nachteile: Keine tatsächliche Kategorietextausgabe – möglicherweise nicht ideal, wenn weitere Filterung, Export oder Berechnungen benötigt werden.
Automatisierung der Kategorisierung mit VBA-Code
Für große Datensätze oder stark angepasste Klassifizierungsanforderungen kann die Verwendung von VBA (Visual Basic for Applications)-Code den Prozess der Zuweisung von Kategorien oder der Anwendung von Formatierungen basierend auf Wertebereichen automatisieren. Dieser Ansatz ist praktisch, wenn Sie repetitive Aufgaben haben, die Datenverarbeitung standardisieren möchten oder Kategorisierung schnell mit unterschiedlichen Regeln aktualisieren oder neu ausführen müssen.
Typische Anwendungsfälle:
- Automatisches Kategorisieren langer Listen ohne manuelle Formeleingabe.
- Anwenden benutzerdefinierter Logik oder Kombination von Kategoriezuweisung mit anderen Aufgaben (z. B. Hervorheben oder Exportieren).
- Schnelles erneutes Anwenden der Klassifizierung nach Datenaktualisierungen.
Hinweis: Bitte speichern Sie Ihr Arbeitsblatt, bevor Sie VBA-Code ausführen, da Makros nicht rückgängig gemacht werden können. Aktivieren Sie Makros, falls Sie dazu aufgefordert werden.
So verwenden Sie VBA für die automatische Kategorisierung:
1. Klicken Sie auf Entwickler > Visual Basic, um das Microsoft Visual Basic for Applications-Fenster zu öffnen. Klicken Sie dann auf Einfügen > Modul und fügen Sie den folgenden Code in das Modulfenster ein:
Sub CategorizeValues()
Dim rng As Range
Dim cell As Range
Dim categoryCol As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.InputBox("Select data range (single column):", xTitleId, "", Type:=8)
If rng Is Nothing Then Exit Sub
Set categoryCol = rng.Offset(0, 1)
For Each cell In rng
If IsNumeric(cell.Value) Then
Select Case cell.Value
Case Is > 90
categoryCol.Cells(cell.Row - rng.Row + 1, 1).Value = "High"
Case Is > 60
categoryCol.Cells(cell.Row - rng.Row + 1, 1).Value = "Medium"
Case Else
categoryCol.Cells(cell.Row - rng.Row + 1, 1).Value = "Low"
End Select
Else
categoryCol.Cells(cell.Row - rng.Row + 1, 1).Value = ""
End If
Next cell
End Sub2. Klicken Sie auf die 
Ausführen Schaltfläche, um auszuführen. Wenn Sie dazu aufgefordert werden, wählen Sie die Spalte mit den Werten aus (z. B. Noten). Das Makro schreibt das Kategorieergebnis (Hoch / Mittel / Niedrig) in die Spalte direkt daneben.
Erklärung & wichtige Punkte:
- Schwellenwerte sind im Code festgelegt: Werte > 90 → Hoch, > 60 → Mittel, sonst Niedrig. Sie können diese Zahlen anpassen.
- Nicht-numerische Werte werden ignoriert und bleiben leer.
- Um in eine andere Spalte auszugeben, passen Sie
rng.Offset(0, 1)entsprechend an.
Fehlerhinweise & Fehlerbehebung:
- Wenn nichts passiert, überprüfen Sie Ihre Makrosicherheitseinstellungen und bestätigen Sie, dass Makros aktiviert sind.
- Wenn Sie den falschen Bereich ausgewählt haben, führen Sie das Makro einfach erneut aus.
- Arbeiten Sie immer an einer Kopie Ihrer Datei, wenn Sie zum ersten Mal testen.
Vorteile: Effizient für große Datensätze, anpassbare Regeln, reduziert manuelle Arbeit.
Nachteile: Erfordert die Aktivierung von Makros und grundlegende VBA-Kenntnisse.
Entfesseln Sie die Magie von Excel mit Kutools AI
- Intelligente Ausführung: Führen Sie Zellenoperationen durch, analysieren Sie Daten und erstellen Sie Diagramme – alles angetrieben durch einfache Befehle.
- Benutzerdefinierte Formeln: Erstellen Sie maßgeschneiderte Formeln, um Ihre Arbeitsabläufe zu optimieren.
- VBA-Codierung: Schreiben und implementieren Sie VBA-Code mühelos.
- Formelinterpretation: Verstehen Sie komplexe Formeln mit Leichtigkeit.
- Textübersetzung: Überwinden Sie Sprachbarrieren in Ihren Tabellen.
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