Wir haben also bereits gelernt, was 3D-Referenz in Excel ist. 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.
Die generische Formel für SUMIF mit 3D-Referenz in Excel
Es sieht kompliziert aus, ist es aber nicht (so viel).
| =SUMMENPRODUKT(SUMIF(INDIRECT("'"&name_range_of_sheet_names&"'!" & "criteria_range"),criteria,INDIRECT("'"&name_range_of_sheet_names&"'!" &"sum_range"))) |
"'"name_range_of_sheet_names"'":Es ist ein benannter Bereich, der die Blattnamen enthält. Dies ist sehr wichtig.
"Kriterienbereich":Es ist die Textreferenz von Kriterien, die einen Bereich enthalten. (Es sollte in allen Blättern zu 3-D-Nachschlagewerken gleich sein.)
Kriterien:Es ist einfach die Bedingung, die Sie für die Summierung stellen möchten. Dies kann ein Text- oder Zellbezug sein.
"Summenbereich":Es ist die Textreferenz des Summenbereichs. (Es sollte in allen Blättern zu 3-D-Nachschlagewerken gleich sein.)
Genug der Theorie, lassen Sie uns 3D-Referenzen mit funktionierender SUMIF-Funktion.
Beispiel: Summieren nach Region aus mehreren Blättern unter Verwendung der 3D-Referenz von Excel:
Wir verwenden die gleichen Daten wie im einfachen 3D-Referenzbeispiel. 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 die Sammlung nach Region aus allen Blättern. Machen wir es zuerst für Einheiten. 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,Meister!B4,Jan:Apr!D2:D14)
Es wird #VALUE zurückgegeben! Error. Wir können es also nicht verwenden. Wir verwenden die oben erwähnte generische Formel.
Verwenden Sie die obige generische 3D-Referenz-SUMIF-Formel von Excel und schreiben Sie diese Formel in Zelle C3:
| =SUMMENPRODUKT(SUMIF(INDIREKT("'"&Monate&"'!" & "A2:A14"),Master!B3,INDIRECT("'"&Monate&"'!" &"D2:D14"))) |
Hier Monate ist ein benannter Bereich, der die Namen von Blättern enthält. Dies ist entscheidend.
Wenn Sie die Eingabetaste drücken, erhalten Sie Ihre genaue Ausgabe.
Wie funktioniert es?
Der Kern der Formel ist die INDIREKTE Funktion und die benannter Bereich. Hier die Saite"'"&Monate&"'!" & "A2:A14"übersetzt in ein Array von Bereichsreferenzen jedes Blattes im benannten Bereich.
| {"'Jan'!D2:D14";"'Feb'!D2:D14";"'Mär'!D2:D14";"'Apr'!D2:D14"} |
Dieses Array enthält die Textreferenzder Bereiche, 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 innerhalb der INDIREKTEN Funktionen (festhalten) sieht die Formel so aus:
| =SUMMENPRODUKT(SUMIF(INDIREKT(({"'Jan'!A2:A14";"'Feb'!A2:A14";"'Mär'!A2:A14";"'Apr'!A2:A14"}) , Meister!B3,INDIREKT({"'Jan'!D2:D14";"'Feb'!D2:D14";"'Mär'!D2:D14";"'Apr'!D2:D14"}))) |
Jetzt kommt die SUMIF-Funktion zum Einsatz (nicht die INDIREKT, wie Sie vielleicht vermutet haben). Die Bedingung wird in den ersten Bereich abgeglichen"'Jan'!A2:A14". Hier arbeitet die INDIREKT-Funktion dynamisch und wandelt diesen Text in den tatsächlichen Bereich um (Wenn Sie also versuchen, INDIRECT zuerst mit der Taste F9 zu lösen, erhalten Sie das Ergebnis nicht). Als nächstes werden die übereinstimmenden Werte im Bereich zusammengefasst"'Jan'!D2:D14".Dies geschieht für jeden Bereich im Array. Schließlich haben wir ein Array, das von der SUMIF-Funktion zurückgegeben wird.
| =SUMMENPRODUKT({97;82;63;73}) |
Jetzt macht das SUMPRODUCT das, was es am besten kann. Es fasst diese Werte zusammen und wir bringen unsere 3D-SUMIF-Funktion zum Laufen.
Also ja, Leute, so können Sie eine 3D-SUMIF-Funktion erreichen. Das ist etwas komplex, da stimme ich zu. Es gibt viel Spielraum für Fehler in dieser 3D-Formel. Ich würde vorschlagen, dass Sie die SUMIF-Funktion auf jedem Blatt in einer bestimmten Zelle verwenden und dann die normale 3D-Referenz zum Summieren dieser Werte verwenden.
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.
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. Mit diesen 50 Tastenkombinationen arbeiten Sie noch schneller mit Excel.
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.