Generische Formel
=SUMPRODUCT(data*(headers=A1))
Zusammenfassung
Um Werte in Spalten durch Abgleichen übereinstimmender Spaltenüberschriften zu summieren, können Sie eine Formel verwenden, die auf der Funktion SUMPRODUCT basiert. Im gezeigten Beispiel lautet die Formel in J5:
=SUMPRODUCT(data*(LEFT(headers)=J4))
Dabei ist "Daten" der benannte Bereich B5: G14 und "Header" der benannte Bereich B4: G4.
Die Formel summiert Spalten, in denen Überschriften mit "a" beginnen und 201 zurückgeben.
Erläuterung
Im Kern basiert diese Formel auf der Funktion SUMPRODUCT, um Werte in übereinstimmenden Spalten im benannten Bereich "Daten" C5: G14 zu summieren. Wenn alle Daten in einem einzigen Bereich für SUMPRODUCT bereitgestellt würden, wäre das Ergebnis die Summe aller Werte im Bereich:
=SUMPRODUCT(data) // all data, returns 387
Um einen Filter durch Abgleichen von Spaltenüberschriften anzuwenden - Spalten mit Überschriften, die mit "A" beginnen - verwenden wir die LEFT-Funktion wie folgt:
LEFT(headers)=J4) // must begin with "a"
Dieser Ausdruck gibt TRUE zurück, wenn ein Spaltenkopf mit "a" beginnt, und FALSE, wenn nicht. Das Ergebnis ist ein Array:
(TRUE,TRUE,FALSE,FALSE,TRUE,FALSE)
Sie können sehen, dass die Werte 1,2 und 5 Spalten entsprechen, die mit "a" beginnen.
Innerhalb von SUMPRODUCT wird dieses Array mit "Daten" multipliziert. Aufgrund der Übertragung ist das Ergebnis ein zweidimensionales Array wie folgt:
(8,10,0,0,7,0;9,10,0,0,10,0;8,6,0,0,6,0;7,6,0,0,6,0;8,6,0,0,6,0;10,11,0,0,7,0;7,8,0,0,8,0;2,3,0,0,3,0;3,4,0,0,4,0;7,7,0,0,4,0)
Wenn wir dieses Array in einer Tabelle visualisieren, ist leicht zu erkennen, dass nur Werte in Spalten, die mit "a" beginnen, die Operation überlebt haben. Alle anderen Spalten sind Null. Mit anderen Worten, der Filter behält interessierende Werte bei und "löscht" den Rest:
| A001 | A002 | B001 | B002 | A003 | B003 |
|---|---|---|---|---|---|
| 8 | 10 | 0 | 0 | 7 | 0 |
| 9 | 10 | 0 | 0 | 10 | 0 |
| 8 | 6 | 0 | 0 | 6 | 0 |
| 7 | 6 | 0 | 0 | 6 | 0 |
| 8 | 6 | 0 | 0 | 6 | 0 |
| 10 | 11 | 0 | 0 | 7 | 0 |
| 7 | 8 | 0 | 0 | 8 | 0 |
| 2 | 3 | 0 | 0 | 3 | 0 |
| 3 | 4 | 0 | 0 | 4 | 0 |
| 7 | 7 | 0 | 0 | 4 | 0 |
Mit nur einem zu verarbeitenden Array gibt SUMPRODUCT die Summe aller Werte 201 zurück.
Summe nach exakter Übereinstimmung
Das obige Beispiel zeigt, wie Spalten summiert werden, die mit einem oder mehreren bestimmten Zeichen beginnen. Um die Spalte basierend auf einer genauen Übereinstimmung zu summieren, können Sie eine einfachere Formel wie die folgende verwenden:
=SUMPRODUCT(data*(headers=J4))
