SUMIF mit 3D-Referenz in Excel

Es sieht kompliziert aus, ist es aber nicht (so viel).

„‚“ name_range_of_sheet_names „‚“: * Es handelt sich um einen benannter Bereich , der die Blattnamen enthält. Dies ist sehr wichtig.

„Kriterien_Bereich“: * Dies ist die Textreferenz von Kriterien, die den Bereich enthalten. (Es sollte in allen Blättern für 3D-Nachschlagewerke gleich sein.)

Kriterien: * Es ist einfach die Bedingung, die Sie für die Summierung festlegen möchten. Dies kann eine Text- oder Zellreferenz sein.

„sum_range“: * Dies ist die Textreferenz des Summenbereichs. (Es sollte in allen Blättern für 3D-Nachschlagewerke gleich sein.)

Genug der Theorie, lassen Sie uns die 3D-Referenzierung mit der SUMIF-Funktion funktionieren. === Beispiel: Summe nach Region aus mehreren Blättern mithilfe der 3D-Referenzierung von Excel: *

Wir verwenden dieselben Daten wie im einfaches 3D-Referenzierungsbeispiel. In diesem Beispiel habe ich fünf verschiedene Blätter, die ähnliche Daten enthalten. Jedes Blatt enthält die Daten eines Monats. Im Master-Blatt möchte ich die Summe der Einheiten und der Sammlung nach Region aus allen Blättern. Lassen Sie es uns zuerst für Einheiten tun. Die Einheiten liegen in allen Blättern im Bereich D2: D14.

Wenn Sie nun die normale 3D-Referenzierung mit SUMIF-Funktion verwenden, = SUMIF (Jan: Apr! A2: A14, Master! B4, Jan: Apr! D2: D14)

Es wird #VALUE zurückgegeben! Error. Wir können es also nicht benutzen. Wir werden die oben erwähnte generische Formel verwenden.

Schreiben Sie diese Formel unter Verwendung der obigen generischen 3D-Referenzierungs-SUMIF-Formel von Excel in Zelle C3:

Hier ist Monate ein „benannter Bereich„, der die Namen von Blättern enthält. Das ist entscheidend.

Wenn Sie die Eingabetaste drücken, erhalten Sie Ihre genaue Ausgabe.

How does it work? The core of the formula is the INDIRECT function and the named range. Here the string“‚“&Months&“‚!“ & „A2:A14″translates to an array of range references of each sheet in the named range.*

Dieses Array enthält die Textreferenz * von Bereichen, nicht die tatsächlichen Bereiche. Da es sich nun um eine Textreferenz handelt, kann sie von der INDIRECT-Funktion verwendet werden, um sie in tatsächliche Bereiche umzuwandeln. Dies geschieht für beide INDIRECT-Funktionen. Nach dem Auflösen der Texte in den INDIRECT-Funktionen (festhalten) sieht die Formel folgendermaßen aus:

Jetzt wird die SUMIF-Funktion aktiviert (nicht die INDIREKTE, wie Sie vielleicht vermutet haben). Die Bedingung wird in den ersten Bereich „‚Jan‘! A2: A14“ eingepasst. Hier arbeitet die INDIRECT-Funktion dynamisch und konvertiert diesen Text in den tatsächlichen Bereich (daher erhalten Sie das Ergebnis nicht, wenn Sie versuchen, INDIRECT zuerst mit der Taste F9 zu lösen). Fasst als nächstes die übereinstimmenden Werte im Bereich „‚Jan‘! D2: D14“ zusammen. * Dies geschieht für jeden Bereich im Array. Schließlich wird ein Array von der SUMIF-Funktion zurückgegeben.

Jetzt macht das SUMPRODUCT das, was es am besten kann. Es fasst diesen Wert zusammen und wir bringen unsere 3D-SUMIF-Funktion zum Laufen.

Also ja Leute, so können Sie eine 3D-SUMIF-Funktion erreichen. Das ist ein bisschen komplex, da stimme ich zu. In dieser 3D-Formel gibt es viel Spielraum für Fehler. Ich würde vorschlagen, dass Sie die SUMIF-Funktion für jedes Blatt in einer bestimmten Zelle verwenden und dann den normale 3D-Referenzierung verwenden, um diese Werte zusammenzufassen.

Ich hoffe ich war erklärend genug. Wenn Sie Zweifel haben, ob Excel auf eine andere Excel / VBA-bezogene Abfrage verweist, fragen Sie im Kommentarbereich unten nach.

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-range-name-expanding-references-in-excel [Erweitern von Referenzen in Excel] | 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.

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. Mit diesen 50 Verknüpfungen können Sie noch schneller in Excel arbeiten.

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.