Bisher haben wir gelernt, wie man die gesamte übereinstimmende Spalte einer Tabelle in Excel summiert. Aber wie summieren wir Werte, wenn wir Spalte und Zeile abgleichen müssen? In diesem Artikel erfahren Sie, wie Sie die Summe übereinstimmender Zeilen und Spalten berechnen.
Dafür gibt es zwei Formeln, aber lassen Sie uns zunächst das Szenario betrachten.
Hier habe ich eine Tabelle, die die Verkäufe der Mitarbeiter in verschiedenen Monaten aufzeichnet. Der Name der Mitarbeiter kann wiederholt werden. Siehe die folgende Abbildung:
Wir brauchen die Summe des Monats Mai, in dem Donald der Verkäufer ist.
Methode 1: Zusammenfassen der übereinstimmenden Spaltenüberschrift und Zeilenüberschrift Verwenden der SUMPRODUCT-Funktion.
Die SUMPRODUCT-Funktion 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 lautet:
| =SUMMENPRODUKT((Spalten)*(Spaltenüberschriften=Spaltenüberschrift)*(Zeilenüberschriften=Zeilenü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.
Spaltenüberschriften:Es ist der Header-Bereich vonSäulen die du zusammenfassen möchtest. In den obigen Daten ist es C2:N2.
Spaltenüberschrift: Es ist die Überschrift, die Sie abgleichen möchten. Im obigen Beispiel ist es in B13.
Lassen Sie uns ohne weitere Verzögerung die Formel verwenden.
row_headers:Es ist der Header-Bereich vonReihen die du zusammenfassen möchtest. In den obigen Daten ist es B3:B10.
Zeilenüberschrift: Es ist die Überschrift, die Sie in Zeilen abgleichen möchten. Im obigen Beispiel ist es in F13 .
Lassen Sie uns ohne weitere Verzögerung die Formel verwenden.
Schreiben Sie diese Formel in Zelle D13 und lassen Sie Excel zaubern (so etwas wie Magie gibt es nicht) …
| =SUMMENPRODUKT((C3:N10)*(C2:N2=B13)*(B3:B10=E13)) |
Dies gibt den Wert zurück:
Wenn Sie nun den Monat oder den Verkäufer ändern, ändert sich die Summe gemäß der Zeilen- und Spaltenüberschrift.
Wie funktioniert es?
Diese einfache boolesche Logik.
(C2:N2=B13): Diese Anweisung gibt ein Array von TRUE und FALSE zurück. Alle übereinstimmenden Werte in der Spalte haben true und andere haben false. In diesem Fall haben wir nur ein True, da der Bereich C2:N2 nur eine Instanz May bei 5 . enthältNS Standort.
(B3:B10=E13): Dies funktioniert genauso wie oben und gibt ein Array TRUE und FALSE zurück. Alle übereinstimmenden Werte haben TRUE und andere haben FALSE. In diesem Fall haben wir 2 TRUEs, da der Bereich B3:B10 zwei Instanzen von "Donald" enthält.
(C2:N2=B13)*(B3:B10=E13): Jetzt multiplizieren wir die von Anweisungen zurückgegebenen Arrays. Dies wird eine Logik implementieren und wir erhalten ein Array von 1s und 0s. Jetzt haben wir ein 2D-Array, das 2 1er und Rest 0s enthält.
(C3:N10)*(C2:N2=B13)*(B3:B10=E13)= Schließlich multiplizieren wir das 2D-Array mit der 2D-Tabelle. Es wird wieder ein Array von Nullen und den Zahlen zurückgegeben, die den Kriterien entsprechen.
Endlich, das SUMMENPRODUKT Die Funktion summiert das Array, was die gewünschte Ausgabe ergibt.
Methode 2: Zusammenfassen der übereinstimmenden Spaltenüberschrift und Zeilenüberschrift Verwenden der SUM- und IF-Funktion
Die generische Formel zum Summieren übereinstimmender Zeilen und Spalten mithilfe der SUM- und IF-Excel-Funktion lautet:
| =SUMME(IF(Spaltenüberschrift=Spaltenüberschrift,IF(Zeilenüberschrift=Zeilenüberschrift,Spalten))) |
Alle Variablen sind die gleichen wie bei der oben erläuterten Methode. Hier müssen sie nur in einer anderen Reihenfolge verwendet werden.
Schreiben Sie diese Formel in Zelle D13:
| =SUMME(WENN(C2:N2=B13,WENN(B3:B10=E13,C3:N10))) |
Dies gibt die richtige Antwort zurück. Siehe den Screenshot unten:
Wie funktioniert es?
Die Logik ist dieselbe wie bei der ersten SUMPRODCUT-Methode, nur der Mechanismus ist anders. Wenn ich es kurz erkläre, gibt die innere IF-Funktion ein 2D-Array mit der gleichen Dimension wie die Tabelle zurück. Dieses Array enthält die Anzahl von zwei übereinstimmenden Zeilen. Dann vergleicht die innere IF-Funktion die zweispaltigen Überschriften in diesem Array und gibt das 2D-Array zurück, das nur die Zahlen enthält, die sowohl Spalte als auch Überschrift entsprechen. Alle anderen Elemente des Arrays sind FALSE.
Schließlich summiert die SUM-Funktion dieses Array und wir erhalten unsere Summe.
Also ja, Leute, so können Sie die übereinstimmenden Zeilen und Spalten aus einer Tabelle in Excel zusammenfassen. Ich hoffe, es war erklärend und nützlich für Sie. Wenn Sie Zweifel in Bezug auf dieses Thema oder andere Zweifel im Zusammenhang mit Excel / VBA haben, fragen Sie im Kommentarbereich unten nach.
So summieren Sie eine Spalte in einer Excel durch übereinstimmende Überschrift | Wenn Sie die Summe einer Spalte nur mithilfe des Spaltennamens erhalten möchten, können Sie dies in Excel auf 3 einfache Arten tun. Die Syntax der SUMPRODUCT-Methode zum Summieren der übereinstimmenden Spalte lautet:
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.