Wie man Summe von Zeilen und Spalten in Excel Passende
Bisher haben wir gelernt, wie man eine mathematische Funktion einer Tabelle in Excel verknüpft: / mathematische Funktionen, wie man eine Spalte in einem Excel durch passende Überschrift summiert [summiert die gesamte übereinstimmende Spalte]. Aber wie summieren wir Werte, wenn wir Spalte und Zeile abgleichen müssen? In diesem Artikel erfahren Sie, wie Sie die Summe der übereinstimmenden Zeilen und Spalten berechnen.
Es gibt zwei Formeln, um dies zu tun, aber lassen Sie uns zuerst das Szenario betrachten.
Hier habe ich eine Tabelle, in der die Verkäufe von Mitarbeitern in verschiedenen Monaten aufgezeichnet sind. Der Name der Mitarbeiter kann wiederholt werden. Siehe folgende Abbildung:
Wir müssen die Summe des Monats Mai erhalten, in dem der Verkäufer Donald ist.
Methode 1: Zusammenfassen des übereinstimmenden Spalten- und Zeilenkopfs mit der Funktion SUMPRODUCT. Die link: / summing-excel-sumproduct-Funktion [SUMPRODUCT Function] ist die vielseitigste Funktion, wenn es darum geht, Werte mit kniffligen Kriterien zu summieren und zu zählen. Die generische Funktion, die durch Abgleichen von Spalte und Zeile summiert werden kann, lautet:
|
= |
Spalten: * Dies ist der zweidimensionale Bereich der Spalten, die Sie summieren möchten. Es sollte keine Header enthalten. In der obigen Tabelle ist es C3: N7.
column_headers: Dies ist der Header-Bereich von Spalten *, den Sie summieren möchten. In den obigen Daten ist es C2: N2.
column_heading: * Dies ist die Überschrift, mit der Sie übereinstimmen möchten. Im obigen Beispiel ist es in B13.
Verwenden wir ohne weitere Verzögerung die Formel.
row_headers: Dies ist der Header-Bereich von Zeilen *, den Sie summieren möchten.
In den obigen Daten ist es B3: B10.
row_heading: * Dies ist die Überschrift, die Sie in Zeilen abgleichen möchten. Im obigen Beispiel ist es in F13.
Verwenden wir ohne weitere Verzögerung die Formel.
Schreiben Sie diese Formel in Zelle D13 und lassen Sie den Excel seine Magie wirken (es gibt keine Magie) …
|
= |
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 am 5. Standort enthält.
(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 als Bereich B3: B10 hat zwei Instanzen von „Donald“.
(C2: N2 = B13) (B3: B10 = E13): * Jetzt multiplizieren wir die von Anweisungen zurückgegebenen Arrays. Dies wird eine Logik implementieren und wir werden ein Array von Einsen und Nullen erhalten. Jetzt haben wir ein 2D-Array, das 2 1s 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 die Zahlen zurückgegeben, die den Kriterien entsprechen.
Schließlich fasst die SUMPRODUCT-Funktion das Array zusammen, was zur gewünschten Ausgabe führt.
Methode 2: Aufsummieren des übereinstimmenden Spalten- und Zeilenkopfs mithilfe der SUM- und IF-Funktion Die allgemeine Formel zum Summieren übereinstimmender Zeilen und Spalten mithilfe der SUM- und IF-Excel-Funktion lautet:
|
= |
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:
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 unterschiedlich. Wenn ich es kurz erkläre, gibt der innere IF-Funktion ein 2D-Array mit derselben Dimension wie die Tabelle zurück. Dieses Array enthält die Anzahl von zwei übereinstimmenden Zeilen. Dann der innere IF-Funktion
Entspricht den zweispaltigen Überschriften in diesem Array und gibt das 2D-Array zurück, das nur die Zahlen enthält, die sowohl mit der Spalte als auch mit der Überschrift übereinstimmen. Alle anderen Elemente des Arrays sind FALSE.
Schließlich fasst der link: / summing-summing-values-am-Schnittpunkt-von-zwei-oder-mehr-Bereichen [SUM-Funktion] dieses Array zusammen und wir erhalten unsere Summe.
Also ja Leute, so können Sie die passenden Zeilen und Spalten aus einer Tabelle in Excel zusammenfassen. Ich hoffe, es war erklärend und nützlich für Sie. Wenn Sie Zweifel an diesem Thema haben oder andere Zweifel im Zusammenhang mit Excel / VBA haben, fragen Sie im Kommentarbereich unten nach.
Verwandte Artikel:
link: / Summieren-wie-die-Summe-Spalte-in-einem-Excel-durch-Matching-Überschrift [Wie man Spalte in einem Excel durch Matching-Überschrift summiert] | Wenn Sie die Summe einer Spalte erhalten möchten, indem Sie nur den Spaltennamen verwenden, können Sie dies in Excel auf drei einfache Arten tun. Die Syntax der SUMPRODUCT-Methode zum Summieren der übereinstimmenden Spalte lautet:
link: / excel-generals-sumif-mit-3d-referenz-in-excel [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.
link: / excel-generals-relative-und-absolute-referenz-in-excel [Relative und absolute Referenz in Excel] | Das Referenzieren in Excel ist ein wichtiges Thema für jeden Anfänger. Selbst erfahrene Excel-Benutzer machen Fehler beim Referenzieren.
link: / lookup-formulas-dynamic-worksheet-reference [Dynamische Arbeitsblattreferenz] | Geben Sie Referenzblätter dynamisch mit der INDIRECT-Funktion von Excel. Das ist einfach …
link: / excel-bereichsname-erweitern-referenzen-in-excel [Referenzen in Excel erweitern] | Die expandierende Referenz wird beim Kopieren nach unten oder rechts erweitert. Dazu verwenden wir das $ -Zeichen vor der Spalten- und Zeilennummer. Hier ist ein Beispiel …
link: / excel-bereichsname-absolute-referenz-in-excel [Alles über absolute Referenz] | Der Standardreferenztyp in Excel ist relativ. Wenn Sie jedoch möchten, dass die Referenz von Zellen und Bereichen absolut ist, verwenden Sie das $ -Zeichen. Hier finden Sie alle Aspekte der absoluten Referenzierung in Excel.
Beliebte Artikel:
link: / Tastatur-Formel-Verknüpfungen-50-Excel-Verknüpfungen-zur-Steigerung-Ihrer-Produktivität [50 Excel-Verknüpfungen zur Steigerung Ihrer Produktivität] | Werden Sie schneller bei Ihrer Aufgabe. Diese 50 Verknüpfungen beschleunigen Ihre Arbeit in Excel noch schneller.
link: / formeln-und-funktionen-einführung-der-vlookup-funktion [Die VLOOKUP-Funktion in Excel] | Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, mit der Werte aus verschiedenen Bereichen und Tabellen gesucht werden.
link: / tips-countif-in-microsoft-excel [COUNTIF in Excel 2016] | Zählen Sie Werte mit Bedingungen, die diese erstaunliche Funktion verwenden. Sie müssen Ihre Daten nicht filtern, um einen bestimmten Wert zu zählen.
Die Countif-Funktion ist wichtig, um Ihr Dashboard vorzubereiten.
link: / excel-formel-und-funktion-excel-sumif-funktion [Verwendung der SUMIF-Funktion in Excel] | Dies ist eine weitere wichtige Funktion des Dashboards. Auf diese Weise können Sie Werte unter bestimmten Bedingungen zusammenfassen.