Wie summiert man Zahlen mit Einheiten in einem Bereich in Excel korrekt?
Beim Arbeiten mit Excel kann es vorkommen, dass Sie Zahlen summieren müssen, die Einheiten enthalten, wie z. B. "5 kg", "12 lbs" oder "10 m". Dies ist üblich, wenn Gewichte, Längen oder Mengen erfasst werden – insbesondere in Einkaufslisten, technischen Messungen, Laborprotokollen und Logistikdaten, bei denen jeder Wert mit einer Maßeinheit versehen ist. Wenn Sie jedoch versuchen, Excels Standard-SUM-Formel auf solche Daten anzuwenden, funktioniert sie nicht wie erwartet. Der Grund dafür ist, dass Excel Werte, die Text (wie Einheiten) enthalten, als Zeichenketten interpretiert und nicht als numerische Werte, was bedeutet, dass SUM und andere grundlegende mathematische Funktionen diese ignorieren oder Fehler zurückgeben. Um diese Werte genau zu summieren, ist eine Methode erforderlich, um den numerischen Teil vor der Berechnung zu extrahieren. In diesem Tutorial führen wir Sie durch mehrere praktische und effektive Methoden, einschließlich Formeln, integrierter Funktionen und praktischer Add-ins wie Kutools für Excel, um Ihnen dabei zu helfen, Zahlen mit Einheiten in verschiedenen realen Szenarien korrekt zu summieren.
➤ Summieren von Zahlen mit Einheiten mithilfe einer Formel
➤ Summieren von Zahlen mit Einheiten mithilfe von Kutools für Excel
➤ Summieren von Zahlen basierend auf derselben Einheit mit Kutools für Excel
➤ Duplikate zusammenführen und entsprechende Zahlen summieren mit Kutools für Excel
➤ VBA-Makro: Zahlen aus Textzeichenfolgen mit Einheiten extrahieren und automatisch summieren
➤ Power Query verwenden, um Zahlen mit Einheiten zu extrahieren und zu summieren
Summieren von Zahlen mit Einheiten mithilfe einer Formel
Wenn alle Ihre Werte dieselbe Einheit teilen und Sie eine schnelle, formelbasierte Lösung benötigen, können Array-Formeln in Excel dabei helfen, den numerischen Teil zu extrahieren und die Summe zu berechnen. Diese Methode eignet sich für Bereiche, in denen jede Zelle eine Zahl enthält, unmittelbar gefolgt von genau derselben Einheit, zum Beispiel "8 kg", "15 kg" usw.
Wählen Sie eine leere Zelle für Ihr Ergebnis aus und geben Sie die folgende Formel ein:
=SUM(SUBSTITUTE(B2:B7, "kg", "") +0)Nachdem Sie die Formel eingegeben haben, bestätigen Sie sie, indem Sie gleichzeitig Strg + Umschalt + Enter drücken (auf einigen Systemen ist dies für Array-Formeln erforderlich), damit Excel sie als Array-Operation erkennt. Anschließend sehen Sie die Summe der numerischen Teile in Ihrer ausgewählten Zelle.
Tipp: In der obigen Formel bezieht sich B2:B7 auf den Datenbereich, der die Werte mit Einheiten enthält, die Sie addieren möchten, und "kg" ist die in Ihren Daten enthaltene Einheit. Stellen Sie sicher, dass alle Einträge im Bereich in Bezug auf die Einheit konsistent sind und keine zusätzlichen Leerzeichen oder Rechtschreibabweichungen vorhanden sind, da die Formel andernfalls die Werte nicht korrekt extrahieren wird. Wenn Ihre Einheit oder Ihr Bereich unterschiedlich ist, passen Sie beide entsprechend an. Diese Formel behandelt keine Szenarien, in denen mehrere Arten von Einheiten im Bereich vorhanden sind.
Summieren von Zahlen mit Einheiten mithilfe von Kutools für Excel
Für Benutzer, die eine einfachere, visuellere Herangehensweise bevorzugen oder das Auswendiglernen von Formeln vermeiden möchten, bietet Kutools für Excel eine direkte Möglichkeit, numerische Werte zu bereinigen. Seine Funktion „Bestimmte Zeichen löschen“ macht es einfach, die nicht-numerischen Einheiten zu entfernen, sodass Sie die Zahlen direkt summieren können. Dies ist ideal für Situationen, in denen Ihre Daten Zahlen und Einheiten in einer Spalte enthalten und Sie die Zahlen schnell für Berechnungen nutzbar machen müssen.
1. Markieren Sie die Zellen, die die Zahlen mit Einheiten enthalten. Gehen Sie zur Registerkarte Kutools, klicken Sie auf Text und wählen Sie Bestimmte Zeichen löschen. Siehe Screenshot:
2. Aktivieren Sie im Dialogfeld Bestimmte Zeichen löschen nur die Option Nicht-numerisch. Diese Einstellung entfernt alle Zeichen außer Zahlen. Siehe Screenshot:
3. Klicken Sie auf OK oder Übernehmen. Nun bleiben nur noch die numerischen Inhalte in Ihren ausgewählten Zellen übrig, die Sie nun mit einer einfachen Formel summieren können. Wählen Sie eine leere Zelle aus und geben Sie die folgende Formel ein, um Ihr Ergebnis zu erhalten:
=SUM(B2:B7)Das folgende Bild zeigt, wie die bereinigte Spalte und die Summe nach diesem Verfahren aussehen.
Kutools für Excel - Verleihen Sie Excel mit über 300 essenziellen Tools einen echten Schub. Nutzen Sie dauerhaft kostenlose KI-Funktionen! Holen Sie es sich jetzt
Hinweis: Kutools führt direkte Ersetzungen in den ausgewählten Zellen durch, daher sollten Sie bei Bedarf eine Kopie Ihrer Daten erstellen, um den ursprünglichen Text zu erhalten. Diese Lösung funktioniert am besten bei Einheiten eines einzigen Typs. Stellen Sie nach der Verwendung dieser Funktion sicher, dass alle bereinigten Werte numerisch sind, bevor Sie sie summieren.
Summieren von Zahlen mit derselben Einheit mithilfe von Kutools für Excel und der SUM-Funktion
Summieren von Zahlen basierend auf derselben Einheit mit Kutools für Excel
Wenn Ihre Tabelle Werte mit unterschiedlichen Einheiten enthält – zum Beispiel Gewichte sowohl in "Kg" als auch in "Pfund" – und Sie separat für jede Einheit summieren möchten (z. B. das Gesamtgewicht in Kg und Pfund berechnen), bietet Kutools eine effiziente Möglichkeit mit seiner Formelhelfer-Funktion. Dies ist nützlich für Zusammenfassungstabellen, Bestandsberichte oder beim Vorbereiten von Daten für weitere Vergleiche.
Kutools für Excel - Mit über 300 essenziellen Tools für Excel. Nutzen Sie dauerhaft kostenlose KI-Funktionen! Laden Sie es jetzt herunter!
1. Wählen Sie die Zelle aus, in der das summierte Ergebnis erscheinen soll. Navigieren Sie zu Kutools > Formelhelfer > Statistisch > Summe basierend auf gleichem Text.
2. Im Fenster Formelhelfer richten Sie Ihre Berechnung ein: Wählen Sie unter Argumenteingabe den vollständigen Datenbereich als Ihren Bereich aus und geben Sie die spezifische Einheit (z. B. "Kg" oder "Pound") in das Textfeld ein. Dann klicken Sie auf OK.
Tipp: Die Standardformel verwendet absolute Bezüge (z. B. $B$2:$B$7). Wenn Sie diese Funktion für mehrere Einheiten oder Zeilen automatisch ausfüllen möchten, ändern Sie die Bezüge bei Bedarf manuell in relative.
Nun zeigt Ihre Ergebniszelle die Summe für die gewählte Einheit an. Wiederholen Sie den Vorgang für jede Einheit, um alle Gruppensummen zu erhalten.
Hinweis: Diese Methode funktioniert gut für Tabellen mit klar gekennzeichneten Einheitentypen und hilft, das Summieren von Multi-Einheiten zu automatisieren und Fehler durch manuelles Gruppieren zu minimieren.
Duplikatzeilen kombinieren und entsprechende Zahlen summieren mit Kutools für Excel
Einige Datensätze enthalten Elemente oder Entitäten, die mehrfach mit zugehörigen Mengen und Einheiten aufgeführt sind, wie Früchte mit Gewichtseinträgen in unterschiedlichen Einheiten. Angenommen, Sie möchten Werte gruppiert nach Artikel und Einheit summieren, wobei Sie doppelte Zeilen zusammenführen und die Gesamtsumme bilden. Kutools für Excel’s Funktion Erweiterte Zeilen zusammenführen ist genau für solche Szenarien konzipiert und macht die Aggregation schnell und unkompliziert.
1. Wählen Sie Ihre Tabelle mit doppelten Elementen und gemischten Einheiten aus, dann gehen Sie zu Kutools > Zusammenführen & Aufteilen > Erweiterte Zeilen zusammenführen.
2. Wählen Sie im Dialogfeld Zeilen basierend auf Spalte zusammenführen zunächst die Spalte mit den Einheiten aus und markieren Sie sie als Primärschlüssel. Dadurch gruppiert Kutools Ihre Daten nach Einheit. Siehe Screenshot:
3. Markieren Sie als nächstes die Spalte mit den numerischen Werten (z. B. "Gewicht"), dann wählen Sie Berechnen und wählen Sie Summe aus, um die Werte für jede Gruppe zu addieren.
4. Klicken Sie auf OK und Ihre Tabelle fasst die Daten nach Einheit zusammen und kombiniert die Zahlen wie erforderlich. Dies ist besonders nützlich für Bestands- und Verkaufsdaten, bei denen mehrere Einträge nach übereinstimmender Einheit aggregiert werden müssen.
Kutools für Excel - Verleihen Sie Excel mit über 300 essenziellen Tools einen echten Schub. Nutzen Sie dauerhaft kostenlose KI-Funktionen! Holen Sie es sich jetzt
Tipp: Sie können nach mehr als einem Schlüssel (z. B. Artikel und Einheit) gruppieren, um eine tiefere Konsolidierung zu erreichen.
Duplikate kombinieren und Zahlen summieren
VBA-Makro: Zahlen aus Textzeichenfolgen mit Einheiten extrahieren und automatisch summieren
Für fortgeschrittene Benutzer, die eine vollständige Automatisierung suchen oder mit komplexen oder variablen Mustern in Einheiten arbeiten, kann ein VBA-Makro die Extraktion und Summierung in einem Schritt handhaben. Diese Methode ist besonders hilfreich, wenn Ihr Bereich Zahlen enthält, gefolgt von unterschiedlichen Einheiten oder inkonsistenten Datenstrukturen, und wenn integrierte Excel-Formeln zu limitierend oder umständlich sind, um sie für jeden Fall anzupassen.
1. Öffnen Sie den VBA-Editor, indem Sie auf Entwickler > Visual Basic klicken. Im Microsoft Visual Basic for Applications-Fenster klicken Sie auf Einfügen > Modul, dann fügen Sie den folgenden Code in das Modul ein:
Sub SumNumbersWithUnits()
Dim cell As Range
Dim WorkRng As Range
Dim xTitleId As String
Dim NumSum As Double
Dim NumStr As String
Dim i As Long
Dim ch As String
On Error Resume Next
xTitleId = "Kutools for Excel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the range with numbers and units", xTitleId, WorkRng.Address, Type:=8)
NumSum = 0
For Each cell In WorkRng
NumStr = ""
For i = 1 To Len(cell.Value)
ch = Mid(cell.Value, i, 1)
If ch Like "[0-9.]" Then
NumStr = NumStr & ch
ElseIf Len(NumStr) > 0 Then
Exit For
End If
Next i
If IsNumeric(NumStr) Then
NumSum = NumSum + Val(NumStr)
End If
Next cell
MsgBox "Sum of numbers is: " & NumSum, vbInformation, "Kutools for Excel"
End Sub2. Um den Code auszuführen, klicken Sie irgendwo innerhalb des Makros und drücken F5, oder klicken Sie auf die 
Ausführen-Schaltfläche über dem Code-Editorfenster. Ein Dialogfeld wird angezeigt, das Sie auffordert, den Bereich mit den Zahlen und Einheiten auszuwählen. Das Skript extrahiert automatisch den führenden numerischen Wert aus jeder Textzelle und summiert alle Ergebnisse, dann zeigt es die Summe in einem Pop-up-Fenster an.
Tipps und Hinweise zur Verwendung:
- Dieses Makro extrahiert die numerischen Zeichen vom Anfang jeder Zeichenfolge bis ein Nicht-Zahl gefunden wird, was bedeutet, dass es in den meisten Formaten wie "5kg", "12 lbs" oder "20.5 m" funktioniert.
- Wenn das Format der Zahlen inkonsistent ist oder innerhalb der Zeichenfolge eingebettet ist, sind möglicherweise weitere Anpassungen erforderlich.
- Sie können den VBA-Code ändern, um Einheiten separat zu sammeln oder zu verarbeiten, falls gewünscht.
- Um den Verlust der Originaldaten zu vermeiden, verwenden Sie Makros immer auf einer Kopie oder Sicherung Ihres Arbeitsblatts.
- Es ist erforderlich, dass die Makrofunktionalität in Ihren Excel-Einstellungen aktiviert ist.
Diese VBA-Lösung ist vielseitig, wenn es um Variationen in Ihren Daten geht, und spart bei der Anwendung auf große Datensätze erheblich Zeit.
Verwenden Sie Power Query, um Zahlen mit Einheiten zu extrahieren und zu summieren
Power Query, Excels integriertes Daten-Transformationswerkzeug, ist ideal für die Verarbeitung größerer oder komplexerer Datensätze, die Zahlen mit Einheiten enthalten.
Es funktioniert besonders gut, wenn Datenstrukturen inkonsistent sind oder wenn Automatisierung für wiederkehrende Aufgaben benötigt wird.
1. Wählen Sie den Bereich aus, der Ihre Daten (einschließlich Zahlen mit Einheiten) enthält, dann gehen Sie zur Registerkarte Daten und klicken Sie auf Aus Tabelle/Bereich. Falls Sie dazu aufgefordert werden, stellen Sie sicher, dass Ihre Daten Überschriften enthalten.
2. Im Power Query-Editor wählen Sie die Spalte mit Ihren Textwerten aus. Verwenden Sie Spalte hinzufügen Registerkarte > Extrahieren > Text vor Trennzeichen oder Text zwischen Trennzeichen.
Wählen Sie je nach Ihrem Datenformat:
- Verwenden Sie ein Leerzeichen-Trennzeichen, wenn die Daten wie "12 kg" aussehen.
- Verwenden Sie ein buchstabenbasiertes Trennzeichen, wenn es "12kg" ist.
3. Ändern Sie den Datentyp der neuen Spalte nach Bedarf in Dezimalzahl oder Ganzzahl, indem Sie mit der rechten Maustaste auf ihre Überschrift klicken und Typ ändern auswählen.
4. (Optional) Wenn Sie nach Einheit summieren möchten, trennen Sie die Einheit weiter mit Extrahieren Text nach Trennzeichen ab, dann gruppieren Sie nach (auf der Startseite-Registerkarte) und wählen Sie Summe für das neue numerische Feld.
5. Klicken Sie auf Schließen & Laden, um die verarbeiteten Daten und Summen direkt in Excel als neues Blatt oder eine neue Tabelle zu importieren.
Vorteile:
- Verarbeitet komplexe, inkonsistente oder umfangreiche Daten sauber und schnell.
- Schritte können gespeichert und auf zukünftige oder aktualisierte Daten erneut angewendet werden – was diesen Ansatz sehr wiederholbar macht.
- Sie können die Transformationslogik anpassen, die über einfache Extraktion hinausgeht, einschließlich der Behandlung von Präfixen, Suffixen, Dezimalstellen und Gruppierungen nach unterschiedlichen Einheiten.
Einschränkungen:
- Es gibt eine Lernkurve für Benutzer, die neu in Power Query sind.
- Am besten geeignet für strukturierte Daten oder bei der Arbeit mit Tabellen statt direkten Ad-hoc-Zelleinträgen.
Problembehandlung und Vorschläge:
- Für optimale Ergebnisse sollten Sie immer die extrahierten oder konvertierten Zahlen auf Anomalien überprüfen, insbesondere wenn das Format der Einheiten oder Zahlen stark variiert.
- Sichern Sie Ihre Quelldaten vor jedem Massenvorgang (einschließlich mit Kutools und VBA).
- Bei der Verwendung von Formeln überprüfen Sie doppelt auf versteckte Leerzeichen oder Tippfehler in Daten und Einheiten.
- Falls Formeln fehlschlagen oder Fehler zurückgegeben werden (z. B.
#WERT!), überprüfen Sie, ob alle referenzierten Zellen die erwarteten Formate enthalten, und passen Sie den Bereich oder die Einheitskriterien entsprechend an. - Wenn Sie VBA verwenden, stellen Sie sicher, dass Makros aktiviert sind, und speichern Sie vor dem Ausführen für eine einfachere Wiederherstellung.
- Power Query-Transformationsschritte können nach der Vorschau über den Bereich Angewendete Schritte angepasst werden, falls sich Ihre Daten geändert haben.
Verwandte Artikel:
- Wie kombiniert man mehrere Zellen mit führenden Nullen in eine Zelle in Excel?
- Wie kombiniert man Bereiche aus verschiedenen Arbeitsblättern/Arbeitsmappen in einen?
- Wie fasst man Zeilen schnell basierend auf einem Spaltenwert zusammen und führt einige Berechnungen in Excel durch?
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