Laden Sie die Beispielarbeitsmappe herunter
In diesem Tutorial wird gezeigt, wie Sie die Summe der Textwerte ermitteln, wobei jedem solchen Textwert in Excel und Google Sheets ein eindeutiger Code zugewiesen ist.
Als Text gespeicherte SUM-Zahlen
Zuerst werden wir uns ansehen, wie man als Text gespeicherte oder formatierte Zahlen summiert.
Im folgenden Beispiel wird die Spalte Gehalt als Text gespeichert. Wenn Sie versuchen, die Werte zu summieren, zeigt Excel eine Null an.
| 1 | =SUMME(C3:C7) |
Um die SUM-Operation für als Text gespeicherte Zahlen durchzuführen, können Sie stattdessen eine Matrixformel mit den SUM- und VALUE-Funktionen wie folgt verwenden:
| 1 | =SUMME(WERT(C3:C7)) |
Die VALUE-Funktion konvertiert einen Text, der eine Zahl darstellt, in eine Zahl. Die SUM-Funktion summiert diese Zahlen.
In Excel 365 und einer neueren Excel-Version als 2022 können Sie die Formel einfach wie gewohnt eingeben. Wenn Sie jedoch Excel 2022 und früher verwenden, müssen Sie die Matrixformel eingeben, indem Sie drücken STRG + UMSCHALT + EINGABETASTE (Anstatt von EINTRETEN), die Excel mitteilt, dass die Formel eine Arrayformel ist. An den geschweiften Klammern, die um die Formel erscheinen, erkennen Sie, dass es sich um eine Array-Formel handelt (siehe oberes Bild). In späteren Versionen von Excel und Excel 365 können Sie einfach drücken EINTRETEN stattdessen.
Schauen wir uns die folgende Erklärung an, um die Formel besser zu verstehen.
WERT-Funktion
Als Matrixformel verwendet, wandelt die VALUE-Funktion den gesamten als Text gespeicherten Zahlenbereich in ein Zahlenarray um und gibt ihn als Eingabe für die SUM-Funktion zurück.
| 1 | =SUMME({10000;6500;7500;15000;8000}) |
Um zu sehen, was die VALUE-Funktion zurückgibt, wählen Sie die gewünschte Funktion und drücken Sie F9.
SUMME von Textwerten
Um einen Bereich von Textwerten zu summieren, wobei jedem solchen Textwert ein eindeutiger Code zugewiesen ist, kann eine Matrixformel verwendet werden.
Die folgende Tabelle zeichnet auf, was Personen für drei vorgegebene Fragen auswählen. Die Tabelle rechts listet die vier möglichen Antworten auf, denen jeweils ein Codewert zugewiesen wurde. Wir müssen die Codes für jede Person summieren.
| 1 | =SUMME(INDEX(I$3:I$6,N(IF(1,MATCH(C4:E4,H$3:H$6,0))))) |
Wir werden die Formel unten durchgehen.
MATCH-Funktion
Die MATCH-Funktion sucht nach einem angegebenen Element in einem Bereich und gibt seine relative Position in diesem Bereich zurück. Seine Syntax lautet:
Da es sich in diesem Beispiel um eine Array-Formel handelt, sehen wir uns an, was die MATCH-Funktion zurückgibt.
| 1 | =SUMME(INDEX(I$3:I$6,N(IF(1,{1,3,2})))) |
Für eine bestimmte Person findet die MATCH-Funktion die relative Position jeder Antwort im Bereich H3:H6. Das Ergebnis ist ein Array von Positionen.
Notiz: Um in einer Array-Formel anzuzeigen, was eine Funktion zurückgibt, wählen Sie die gewünschte Funktion aus und drücken Sie F9.
IF- und N-Funktion
Die zusammen verwendete IF- und N-Funktion geben das folgende Array als Eingabe für die INDEX-Funktion zurück.
| 1 | =SUMME(INDEX(I$3:I$6,{1,3,2})) |
Hier geben die beiden Funktionen ein Array der relativen Position der Antworten im Bereich zurück H3:H6. Der Zweck der Verwendung der IF- und der N-Funktionen besteht darin, einen Prozess auszuführen, der als Dereferenzierung bezeichnet wird. Einfach ausgedrückt zwingen die beiden Funktionen die INDEX-Funktion, das gesamte Array von Codewerten an die SUM-Funktion weiterzugeben.
Dies erklären wir im nächsten Abschnitt.
INDEX-Funktion
Die INDEX-Funktion gibt den Wert zurück, der am Schnittpunkt einer angegebenen Zeile und Spalte in einem Bereich positioniert ist. Seine Syntax lautet:
Sehen wir uns an, wie es als Array-Formel funktioniert:
| 1 | =SUMME({10,5,8}) |
Die INDEX-Funktion findet die Codewerte im Bereich I3: I6 nach den angegebenen Positionsnummern. Es gibt dann ein Array von Werten, d. h. den entsprechenden Code für jede Antwort, an die SUM-Funktion zurück, um Operationen auszuführen.
Stellen Sie sicher, dass die Anzahl der Zeilen und Spalten in beiden Antworten und der Code Spalte ist gleich.
SUM-Funktion
Die SUM-Funktion summiert die von der INDEX-Funktion zurückgegebenen Codewerte.
| 1 | =SUMME({10,5,8}) |
All dies zusammen ergibt unsere Ausgangsformel:
| 1 | {=SUMME(INDEX(I$3:I$6,N(IF(1,MATCH(C4:E4,H$3:H$6,0))))} |
SUMME von Textwerten - Ohne IF- und N-Funktionen
In diesem Abschnitt wird erläutert, wie Excel reagiert, wenn wir die IF- und die N-Funktion in der oben genannten Formel nicht verwenden.
Das gleiche Beispiel wird mit den gleichen Codes und Antworten verwendet.
| 1 | {=SUMME(INDEX(I$3:I$6,VERGLEICH(C4:E4,H$3:H$6,0)))} |
Wie Sie sehen, übergibt die INDEX-Funktion nur den Code für die erste Antwort an die SUM-Funktion. Wenn Sie die INDEX-Funktion überprüfen, indem Sie F9 Sie erhalten Folgendes:
Der Wert! Es wird ein Fehler zurückgegeben, da die INDEX-Funktion das Array der Zeilennummern nicht als Array lesen kann. Daher ist die Verwendung der IF- und der N-Funktion der Trick.
Notiz: In Excel 365 können Sie die Verwendung der IF- und der N-Funktionen insgesamt überspringen.
Summentext - Google Tabellen
Diese Formeln funktionieren in Google Tabellen genauso wie in Excel.
