Wenn Sie die Summe einer Spalte nur mithilfe des Spaltennamens erhalten möchten, können Sie dies in Excel auf 3 einfache Arten tun. Lassen Sie uns diese Wege erkunden.
Im Gegensatz zu anderen Artikeln sehen wir uns zuerst das Szenario an.
Hier habe ich eine Verkaufstabelle, die von verschiedenen Verkäufern in verschiedenen Monaten erstellt wurde.
Jetzt besteht die Aufgabe darin, die Summe der Verkäufe eines bestimmten Monats in Zelle C10 zu erhalten. Wenn wir den Monat in B10 ändern, sollte sich die Summe ändern und die Summe dieses Monats zurückgeben, ohne etwas an der Formel zu ändern.
Methode 1: Gesamte Spalte in Tabelle mit der Funktion SUMPRODUCT summieren.
Die Syntax der SUMPRODUCT-Methode zum Summieren der übereinstimmenden Spalte lautet:
| =SUMMENPRODUKT((Spalten)*(Kopfzeilen=Überschrift)) |
Säulen:Es ist der 2-dimensionale Bereich der Spalten, die Sie summieren möchten. Es sollte keine Kopfzeilen enthalten. In der obigen Tabelle ist es C3:N7.
Überschriften:Es ist der Header-Bereich von Säulen die du zusammenfassen möchtest. In den obigen Daten ist es C2:N2.
Üerschrift: Es ist die Überschrift, die Sie abgleichen möchten. Im obigen Beispiel ist es in B10.
Lassen Sie uns ohne weitere Verzögerung die Formel verwenden.
| =SUMMENPRODUKT((C3:N7)*(C2:N2=B10)) |
und das wird zurückkommen:
Wie funktioniert es?
Es ist einfach. In der Formel ist die AussageC2:N2=B10 gibt ein Array zurück, das alle FALSE-Werte enthält, außer einem, der B10 entspricht. Nun lautet die Formel
| =SUMMENPRODUKT((C3:N7)*{FALSCH,FALSCH,FALSCH,FALSCH,WAHR,FALSCH,FALSCH,FALSCH,FALSCH,FALSCH,FALSCH,FALSCH}) |
Jetzt wird C3:N7 mit jedem Wert dieses Arrays multipliziert. Jede Spalte wird null, außer der Spalte, die mit TRUE multipliziert wird. Jetzt wird die Formel:
| =SUMMENPRODUKT({0,0,0,0,6,0,0,0,0,0,0,0;0,0,0,0,12,0,0,0,0,0,0, 0;0,0,0,0,15,0,0,0,0,0,0,0,0;0,0,0,0,15,0,0,0,0,0,0,0,0; 0,0,0,0,8,0,0,0,0,0,0,0}) |
Jetzt wird dieses Array aufsummiert und wir erhalten die Summe der Spalte, die der Spalte in der Zelle B10 entspricht.
Methode 2: Gesamte Spalte in Tabelle mit INDEX-MATCH-Funktion summieren.
Die Syntax der Methode zum Summieren der übereinstimmenden Spaltenüberschrift in Excel lautet:
| =SUMME(INDEX(Spalten,,MATCH(Überschrift,Kopfzeilen,0))) |
Alle Variablen in dieser Methode sind mit der SUMPRODUCT-Methode identisch. Lassen Sie es uns einfach implementieren, um das Problem zu lösen. Schreiben Sie diese Formel in C10.
| =SUMME(INDEX(C3:N7,,MATCH(B10,C2:N2,0))) |
Das gibt zurück:
Wie funktioniert es?
Die Formel wird von innen nach außen gelöst. Zunächst gibt die MATCH-Funktion den Index des übereinstimmenden Monats aus dem Bereich C2:N2 zurück. Da wir in B1o Mai haben, erhalten wir 5. Jetzt wird die Formel
| =SUMME(INDEX(C3:N7,,5)) |
Als nächstes gibt die INDEX-Funktion Werte aus der 5. Spalte von C3:N7 zurück. Jetzt wird die Formel:
| =SUMME({6;12;15;15;8}) |
Und schließlich erhalten wir die Summe dieser Werte.
Methode 3: Gesamte Spalte in Tabelle mit Named Range und INDIRECT-Funktion summieren
Alles wird einfach, wenn Sie Ihre Bereiche als Spaltenüberschriften benennen. Bei dieser Methode müssen wir zuerst die Spalten als ihre Überschriftennamen benennen.
Wählen Sie die Tabelle mit den Überschriften aus und drücken Sie STRG+UMSCHALT+F3. Es öffnet sich ein Dialog, um einen Namen aus den Bereichen zu erstellen. Überprüfen Sie die obere Zeile und klicken Sie auf die Schaltfläche OK.
Es wird alle Datenspalten als ihre Überschriften benennen.
Nun lautet die generische Formel zum Summieren der übereinstimmenden Spalte:
| =SUMME(INDIREKT(Überschrift)) |
Üerschrift: Es ist der Name der Spalte, die Sie summieren möchten. In diesem Beispiel enthält B10 ab sofort Mai.
Um diese generische Formel zu implementieren, schreiben Sie diese Formel in Zelle C10.
| =SUMME(INDIREKT(B10)) |
Dies gibt die Summe des Monats Mai zurück:
Eine andere Methode ist dieser ähnlich. Bei dieser Methode verwenden wir Excel-Tabellen und ihre strukturierte Benennung. Nehmen wir an, Sie haben die obige Tabelle als table1 bezeichnet. Dann funktioniert diese Formel genauso wie die obige Formel.
| =SUMME(INDIREKT("Tabelle1["&B10&"]")) |
Wie funktioniert es?
In dieser Formel nimmt die INDIRECT-Funktion die Referenz des Namens und wandelt sie in eine tatsächliche Namensreferenz um. Das Verfahren abwärts ist einfach. Die SUM-Funktion summiert den benannten Bereich.
Also ja, Leute, so können Sie die übereinstimmende Spalte in Excel summieren. Ich hoffe, es ist hilfreich und erklärend für Sie. Wenn Sie Zweifel an diesem Artikel oder einem anderen Thema im Zusammenhang mit Excel/VBA haben, fragen Sie im Kommentarbereich unten nach.
So summieren Sie durch Abgleichen von Zeilen und Spalten in Excel|Das SUMMENPRODUKT ist die vielseitigste Funktion, wenn es darum geht, Werte mit kniffligen Kriterien zu summieren und zu zählen. Die generische Funktion zum Summieren durch Abgleichen von Spalte und Zeile ist…
SUMIF mit 3D-Referenz in Excel |Die lustige Tatsache ist, dass die normale Excel-3D-Referenzierung nicht mit bedingten Funktionen wie der SUMIF-Funktion funktioniert. In diesem Artikel erfahren Sie, wie Sie die 3D-Referenzierung mit der SUMIF-Funktion zum Laufen bringen.
Relativer und absoluter Bezug in Excel | Referenzieren in Excel ist ein wichtiges Thema für jeden Anfänger. Selbst erfahrene Excel-Benutzer machen Fehler bei der Referenzierung.
Dynamische Arbeitsblattreferenz | Geben Sie Referenzblätter dynamisch mit der INDIREKT-Funktion von Excel an. Das ist einfach…
Referenzen in Excel erweitern | Die Erweiterungsreferenz wird erweitert, wenn sie nach unten oder rechts kopiert wird. Dazu verwenden wir das $-Zeichen vor der Spalten- und Zeilennummer. Hier ist ein Beispiel…
Alles über absolute Referenz | Der Standardreferenztyp in Excel ist relativ, aber wenn die Referenz von Zellen und Bereichen absolut sein soll, verwenden Sie das $-Zeichen. Hier sind alle Aspekte der absoluten Referenzierung in Excel.
Populäre Artikel:
50 Excel-Kurzbefehle zur Steigerung Ihrer Produktivität | Werden Sie schneller bei Ihrer Aufgabe. Diese 50 Tastenkombinationen machen Ihre Arbeit in Excel noch schneller.
Die SVERWEIS-Funktion in Excel | Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die verwendet wird, um Werte aus verschiedenen Bereichen und Blättern zu suchen.
ZÄHLENWENN in Excel 2016 | Zählen Sie Werte mit Bedingungen mit dieser erstaunlichen Funktion. Sie müssen Ihre Daten nicht filtern, um einen bestimmten Wert zu zählen. Die Countif-Funktion ist unerlässlich, um Ihr Dashboard vorzubereiten.
So verwenden Sie die SUMIF-Funktion in Excel | Dies ist eine weitere wesentliche Funktion des Dashboards. Dies hilft Ihnen, Werte unter bestimmten Bedingungen zusammenzufassen.