Donnerstag, 3. Juli 2025
20:49 Uhr
20:49 Uhr
Mehrere Zellen auf identische Werte in Excel prüfen - Matrixformel (Arrayformel) in Office 365 und Alternativen
Innerhalb Controlling ist Excel natürlich nicht nur in Kombination mit SAP ein Thema (siehe "SAP und Office Integration Excel im SAP GUI (Excel-Inplace) oder als separates Fenster (Excel-Outplace)"), sondern auch sonst aus dem beruflichen Alltag im Controlling nicht ganz wegzudenken.
Mit Office 365 (siehe "Microsoft Office Produkte") sind nicht nur technische Herausforderungen, sondern auch praktische neue Funktionen hinzugekommen. Auf eine davon mag ich im folgenden Artikel gerne näher eingehen. Neben einer praktischen Fragestellung werden hier auch die "Matrixformel (Arrayformel)" sowie ÜBERLAUF in Microsoft Excel für Office 365 erläutert.
Ausgangslage:
In einer Liste werden mehrere Projekte aufgeführt, die unterschiedlichen Programmlinien zugeordnet werden. Dabei unterscheiden wir die Programme A, B und C. Die Zuordnung der einzelnen Projekte zu den einzelnen Programmen erfolgt jährlich und in der Regel sollte sich an der Zuordnung nichts ändern. Es soll je nach Programmlinie die Summe der Beträge für die einzelnen Jahre ausgegeben werden.
Meine Tabelle hat folgenden Aufbau:
Konkret ist dies auch im Screenshot der Tabelle zu sehen.
Die Formel in Zelle C10 lautet:
=SUMMEWENN( $H$3:$H$8; $H10; C$3:C$8)
Suchbereich ist die aktuelle Zuordnung (Spalte H Zeile 3 bis 8), Suchkriterium die aktuelle Zuordnung in Spalte H für Programm A (hier in Zelle H10 "A") und Summenbereich sind die Werte für 2025 in den Zellen C3 bis C8.
Entsprechend sind die Formeln auch für die anderen Programme und Jahre aufgeführt.
Jetzt gibt es zwei Methoden, um die aktuelle Zuordnung mit der Vorjahreszuordnung zu vergleichen.
Ich nutze in beiden Methoden Funktionen, die als Ergebnis einer Überprüfung WAHR oder FALSCH ausgegeben. Wenn solche Werte in Berechnungen verwendet werden, entspricht WAHR 1 und FALSCH entspricht 0.
Wie oben erwähnt werden WAHR als 1 und FALSCH als 0 in Berechnungen verwendet, sodass ich hier folgende Berechnung durchführen kann, indem ich die einzelnen Werte mit den Vorjahreswerten vergleiche.
=IDENTISCH(H3;I3) * IDENTISCH(H3;J3) * IDENTISCH(H3;K3)
Das Ergebnis zeigt sich in der ersten Kontrollspalte. Da die Werte übereinstimmen, ist der zurückgegebene Wert 1 ( WAHR * WAHR * WAHR).
Alternativ hätte ich auch mit
=IDENTISCH(H4;I4) & IDENTISCH(H4;J4) & IDENTISCH(H4;K4)
arbeiten können, dann wäre die Ausgabe in der Form "WAHRWAHRFALSCH", was zum schnellen Lesen etwas umständlich ist.
In den Zeilen 3 und 4 ist in Spalte L das Ergebnis und in Spalte M die Formel zu sehen.
Der Nachteil der ersten Lösung ist, dass die Formel recht lang werden kann, daher wäre eine Matrix zum Vergleich von mehreren Zellen interessant.
Entsprechend wollte ich per
=IDENTISCH(A4; G4:I4)
einen Abgleich der Zelle A4 mit den Zellen G4 bis I4 machen.
Formeln die nicht nur einen Einzelwert sondern eine Reihe von Werten zurückgeben, geben diese Werte an benachbarte Zellen zurück. Dieses Verhalten für Array wird als Überlaufen bezeichnet. Wird obige Formel per ENTER (Eingabetaste) bestätigt, passt Excel die Größe des Ausgabebereichs dynamisch an und platziert die Ergebnisse in den folgenden Zellen.
Das Ergebnis wird in den Spalten L, M und N ausgegeben.
Können die Ergebnisse der Formel nicht vollständig ausgegeben werden, da bspw. ein Wert in den Zellen steht, gibt es eine neue Fehlermeldung für die Formel #ÜBERLAUF!. Weitere Ursachen können auch verbundene Zellen sein oder aus anderen Gründen der Platz nicht ausreicht.
Im Gegensatz zu Matrixfunktionen (per STRG + SHIFT + ENTER) sehen wir hier auch alle Ergebnisse. Das dynamische Array wird auch über einen blauen Balken hervorgehoben.
=UND(IDENTISCH(H7; I7:K7))
Die Formel UND prüft, ob alle übergebene Argumente WAHR sind und gibt in diesen Fall WAHR zurück. Sofern es zu Abweichungen kommt, lautet das Ergebnis FALSCH.
Auch hier könnte ich mit * 1 das Ergebnis auf 0 oder 1 verändern.
In vorherigen Excel-Versionen hätte ich diese Formel als Matrixformel nutzen müssen, indem ich zur Bestätigung STRG + UMSCHALTTASTE (Shitft) + EINGABETASTE (Enter) gedrückt hätte.
Die Matrixformel ist nach STRG + UMSCH + EINGABE anhand der geschwungene Klammer {}erkennbar.
Dennoch sind weiterhin solche Matrixformeln möglich.
Um eine dynamische Arrayformel nutzen zu können reicht der Abschluss der Formel per Eingabetaste. In früheren Excel-Versionen muss die Formel als herkömmliche Matrixformel behandelt werden. Hier passt sich der Ausgabebereich nicht automatisch an und muss manuell ausgewählt werden und die Formel per STRG+UMSCHALT+EINGABETASTE abgeschlossen werden. Excel stellt diese Formeln dann automatisch mit einer geschweiften Klammer am Anfang und Ende der Formel dar. In der Web-App (Excel für das Web) können Arrayformeln angezeigt werden (sofern schon vorher vorhanden) aber nicht angelegt werden. Selbst per STRG+UMSCHALT+EINGABETASTE ist dies nicht möglich, so dass das Arbeiten im Sinne von Erstellen von Matrixformeln nur in der Excel-Desktopanwendung möglich ist.
Natürlich kann ich nun auch per bedingter Formatierung statt 0 und 1 auch ein entsprechendes Symbol ausgeben. Alternativ können hier auch per WENN entsprechende Symbole verwendet werden. Dies ist auch Thema im Artikel "Excel: bedingte Formatierung mit Pfeilen (Darstellung Tendenzen bei Veränderungen)" gewesen.
Die Änderungen unter Excel für Microsoft 365 erleichtern durchaus praktisch. Nebenbei dynamischen Array-Funktionen und Überlauf-Operator sind nur in aktuellen Excel-Versionen verfügbar (Excel 2021, 2024 und Microsoft 365) in anderen Versionen würde obiges Beispiel nur mit der Matrixformel aus der Kombination von UND / IDENTISCH per STRG + UMSCH + EINGABE funktionieren.
Weitere Verbesserungen beziehungsweise für mich interessante Funktionen sind neue Verweisfunktionen (XVERWEIS) aber sicherlich auch andere Formeln und Funktionen die sich positv auswirken können. Ein guter Ansatz, und das über einige Jahr(zehnte?) schon sind hier übrigens Blogs wie tabellenexperte.de, blog.soprani.at oder auch die Beiträge von Florian Sandmann-Reetz in diversen Netzwerken.
Für Kolleg*innen, die gerade auf Office 365 gewechselt sind, verweise ich hier auch gerne auf folgende Artikel vom Tabellenexperten "Goodbye SVERWEIS, willkommen XVERWEIS!" oder "Die Raute: Ein kleines Zeichen mit großer Wirkung!".
Daneben sind für 2025 aber auch einige andere Themen und Projekte angesetzt, auf die ich mich ebenfalls sehr freue. Dabei kommen sowohl neue Onlinevideos als auch ein neues Buchprojekt auf meine Agenda und ich bin gespannt wie sich dieses umsetzen lassen wird. Das Thema Videobearbeitung wird mich (hoffentlich) nicht nur durch mein erfolgreiches Update auf Davinci Reslove 20 begleiten und auch der Bereich Autorenleben wird noch ein paar Punkte bringen. Immerhin die Steuererklärung ist nun inklusive EÜR abgegeben und dieses Jahr war ich sehr begeistert von der schnellen Übernahme in die Steuersoftware.
Hier nutze ich seit Jahren Taxman von Lexware (*) und bin froh, dass mit einer entsprechenden Vorerfassung der Belege die Abgabe der Steuererklärung erstaunlich schnell vollbracht ist.
Um jetzt aber wieder einen Bogen zur Einleitung (Controlling und Kontrolle) zu schlagen, mag ich das Thema Berichtswesen und interne Qualitätssicherung doch noch in den Fokus stellen. So schnell und technisch einfach eine Tabelle und entsprechende Eingabemasken auch bereitet sein mögen, so schnell kann es hier zu Unstimmigkeiten oder gar Abweichungen kommen, die im ersten Moment nicht auffallen.
Daher habe ich es mir angewöhnt, sowohl kleinere Kontrollen als auch direkt Analysen zu Tabellen außerhalb des Druckbereiches anzulegen, sodass bei einem späteren Bericht auf diese Ergebnisse zurückgegriffen werden kann.
Bevor eine solche Tabelle jedoch versendet wird, sollte sich versichert werden, ob auch die Person, die später mit der Tabelle arbeitet, ebenfalls die passende Officevariante einsetzt. Andernfalls kann dies zu bösen Überraschungen führen. Für meine eigenen Auswertungen und Tabellen bin ich aber zum Glück selbst verantwortlich und kann daher recht einfach davon ausgehen, dass auch aktuellere Formeln wohl funktionieren werden :-).
Hinweis: Aktuelle Buchempfehlungen besonders SAP Fachbücher sind unter Buchempfehlungen inklusive ausführlicher Rezenssionenzu finden. Mein Weiterbildungsangebot zu SAP Themen finden Sie auf unkelbach.expert.
Mit Office 365 (siehe "Microsoft Office Produkte") sind nicht nur technische Herausforderungen, sondern auch praktische neue Funktionen hinzugekommen. Auf eine davon mag ich im folgenden Artikel gerne näher eingehen. Neben einer praktischen Fragestellung werden hier auch die "Matrixformel (Arrayformel)" sowie ÜBERLAUF in Microsoft Excel für Office 365 erläutert.
Ausgangslage:
In einer Liste werden mehrere Projekte aufgeführt, die unterschiedlichen Programmlinien zugeordnet werden. Dabei unterscheiden wir die Programme A, B und C. Die Zuordnung der einzelnen Projekte zu den einzelnen Programmen erfolgt jährlich und in der Regel sollte sich an der Zuordnung nichts ändern. Es soll je nach Programmlinie die Summe der Beträge für die einzelnen Jahre ausgegeben werden.
Aber zurück zum eigentlichen Thema.Kurzer Export zur Rolle des Controlling
Ein gängiges Vorurteil gegenüber Controlling ist, dass Controlling von Kontrolle kommt, obgleich eigentlich bekannt sein sollte, dass das interne Rechnungswesen (und damit auch das Controlling) in der Betriebswirtschaftslehre (BWL) mehr mit steuern in Verbindung gebracht wird. Controlling hat also mehr eine Lotsenfunktion (daher ist auch Kirsten Lotse die Protagonistin meiner Bücher zum "Schnelleinstieg ins SAP Controlling)" und weniger von Kontrolle geprägt.
Die Betriebswirtschaftslehre sieht im Controlling die Planung, Steuerung und ja auch die Kontrolle von Unternehmensprozessen vor, um die Zielerreichung zu unterstützen. Ziel sollte weniger die Kontrolle im Sinne von Überprüfung von Ergebnissen sein, sondern auch die aktive Steuerung von Unternehmensaktivitäten.
Entsprechend ist ein Werkzeug zur Unterstützung auch die Qualitätssicherung und ja auch die Kontrolle. Dies dürfte auch eine Brücke sein, die sowohl vom externen als auch internen Rechnungswesen beschritten wird. Im SAP Umfeld ist es ohnehin so, da hier die Geschäftsbereiche (Line of Business) die ehemalige Modulsicht ablösen. So umfasst Finance sowohl FI und CO als auch Treasury and Risk Management.
Meine Tabelle hat folgenden Aufbau:
- In der Spalte "Projekt" werden die einzelnen Projekte ausgewiesen.
- In den folgenden Spalten werden je Jahr die entsprechenden Erträge ausgewiesen
- In der Hilfsspalte (Zuordnung) werden den Projekten aus der 1. Spalte entsprechende Programme zugeordnet. Zur Vereinfachung nutzen wir hier die Programme A, B und C.
- Zum Abgleich der Zuordnung habe ich die Programmzuordnung der vorherigen Jahre mit aufgeführt.
Konkret ist dies auch im Screenshot der Tabelle zu sehen.
Die Formel in Zelle C10 lautet:
=SUMMEWENN( $H$3:$H$8; $H10; C$3:C$8)
Suchbereich ist die aktuelle Zuordnung (Spalte H Zeile 3 bis 8), Suchkriterium die aktuelle Zuordnung in Spalte H für Programm A (hier in Zelle H10 "A") und Summenbereich sind die Werte für 2025 in den Zellen C3 bis C8.
Entsprechend sind die Formeln auch für die anderen Programme und Jahre aufgeführt.
Jetzt gibt es zwei Methoden, um die aktuelle Zuordnung mit der Vorjahreszuordnung zu vergleichen.
Ich nutze in beiden Methoden Funktionen, die als Ergebnis einer Überprüfung WAHR oder FALSCH ausgegeben. Wenn solche Werte in Berechnungen verwendet werden, entspricht WAHR 1 und FALSCH entspricht 0.
1.) Formel IDENTISCH (mehrere Werte nacheinander vergleichen)
Die Formel IDENTISCH prüft, ob zwei Zeichenfolgen identisch sind und gibt WAHR oder FALSCH zurück. Dabei unterscheidet sie auch zwischen Groß- und Kleinschreibung.Wie oben erwähnt werden WAHR als 1 und FALSCH als 0 in Berechnungen verwendet, sodass ich hier folgende Berechnung durchführen kann, indem ich die einzelnen Werte mit den Vorjahreswerten vergleiche.
=IDENTISCH(H3;I3) * IDENTISCH(H3;J3) * IDENTISCH(H3;K3)
Das Ergebnis zeigt sich in der ersten Kontrollspalte. Da die Werte übereinstimmen, ist der zurückgegebene Wert 1 ( WAHR * WAHR * WAHR).
Alternativ hätte ich auch mit
=IDENTISCH(H4;I4) & IDENTISCH(H4;J4) & IDENTISCH(H4;K4)
arbeiten können, dann wäre die Ausgabe in der Form "WAHRWAHRFALSCH", was zum schnellen Lesen etwas umständlich ist.
In den Zeilen 3 und 4 ist in Spalte L das Ergebnis und in Spalte M die Formel zu sehen.
2.) Kombination aus UND / IDENTISCH oder Formeln mit mehreren Ergebnissen
Der Nachteil der ersten Lösung ist, dass die Formel recht lang werden kann, daher wäre eine Matrix zum Vergleich von mehreren Zellen interessant.
Entsprechend wollte ich per
=IDENTISCH(A4; G4:I4)
einen Abgleich der Zelle A4 mit den Zellen G4 bis I4 machen.
Matrixformel (Arrayformel) in Excel
Hier unterscheidet sich das Excel in Microsoft Office 365 von vorherigen Excelfunktionen.Formeln die nicht nur einen Einzelwert sondern eine Reihe von Werten zurückgeben, geben diese Werte an benachbarte Zellen zurück. Dieses Verhalten für Array wird als Überlaufen bezeichnet. Wird obige Formel per ENTER (Eingabetaste) bestätigt, passt Excel die Größe des Ausgabebereichs dynamisch an und platziert die Ergebnisse in den folgenden Zellen.
Das Ergebnis wird in den Spalten L, M und N ausgegeben.
Können die Ergebnisse der Formel nicht vollständig ausgegeben werden, da bspw. ein Wert in den Zellen steht, gibt es eine neue Fehlermeldung für die Formel #ÜBERLAUF!. Weitere Ursachen können auch verbundene Zellen sein oder aus anderen Gründen der Platz nicht ausreicht.
Im Gegensatz zu Matrixfunktionen (per STRG + SHIFT + ENTER) sehen wir hier auch alle Ergebnisse. Das dynamische Array wird auch über einen blauen Balken hervorgehoben.
Verarbeitung von mehreren Ergebnissen einer Prüfung per UND
Um nun aber nicht mehrere Zellen lesen zu müssen, kombiniere ich das Ergebnis über folgende Formel:=UND(IDENTISCH(H7; I7:K7))
Die Formel UND prüft, ob alle übergebene Argumente WAHR sind und gibt in diesen Fall WAHR zurück. Sofern es zu Abweichungen kommt, lautet das Ergebnis FALSCH.
Auch hier könnte ich mit * 1 das Ergebnis auf 0 oder 1 verändern.
In vorherigen Excel-Versionen hätte ich diese Formel als Matrixformel nutzen müssen, indem ich zur Bestätigung STRG + UMSCHALTTASTE (Shitft) + EINGABETASTE (Enter) gedrückt hätte.
Die Matrixformel ist nach STRG + UMSCH + EINGABE anhand der geschwungene Klammer {}erkennbar.
Matrixformel (Arrayformel) unter Office 365 und Web-App
Unter Office 365 funktioniert dies durch einfaches Bestätigen der Formel.Dennoch sind weiterhin solche Matrixformeln möglich.
Um eine dynamische Arrayformel nutzen zu können reicht der Abschluss der Formel per Eingabetaste. In früheren Excel-Versionen muss die Formel als herkömmliche Matrixformel behandelt werden. Hier passt sich der Ausgabebereich nicht automatisch an und muss manuell ausgewählt werden und die Formel per STRG+UMSCHALT+EINGABETASTE abgeschlossen werden. Excel stellt diese Formeln dann automatisch mit einer geschweiften Klammer am Anfang und Ende der Formel dar. In der Web-App (Excel für das Web) können Arrayformeln angezeigt werden (sofern schon vorher vorhanden) aber nicht angelegt werden. Selbst per STRG+UMSCHALT+EINGABETASTE ist dies nicht möglich, so dass das Arbeiten im Sinne von Erstellen von Matrixformeln nur in der Excel-Desktopanwendung möglich ist.
Fazit
Im Ergebnis liefert auch diese kompakte Formel ein brauchbares Ergebnis und kann bei Abweichungen von Werten wie hier die Zuordnung direkt nachfragen, wie es zur Änderung kam oder entsprechend nachbessern.Natürlich kann ich nun auch per bedingter Formatierung statt 0 und 1 auch ein entsprechendes Symbol ausgeben. Alternativ können hier auch per WENN entsprechende Symbole verwendet werden. Dies ist auch Thema im Artikel "Excel: bedingte Formatierung mit Pfeilen (Darstellung Tendenzen bei Veränderungen)" gewesen.
Die Änderungen unter Excel für Microsoft 365 erleichtern durchaus praktisch. Nebenbei dynamischen Array-Funktionen und Überlauf-Operator sind nur in aktuellen Excel-Versionen verfügbar (Excel 2021, 2024 und Microsoft 365) in anderen Versionen würde obiges Beispiel nur mit der Matrixformel aus der Kombination von UND / IDENTISCH per STRG + UMSCH + EINGABE funktionieren.
Weitere Verbesserungen beziehungsweise für mich interessante Funktionen sind neue Verweisfunktionen (XVERWEIS) aber sicherlich auch andere Formeln und Funktionen die sich positv auswirken können. Ein guter Ansatz, und das über einige Jahr(zehnte?) schon sind hier übrigens Blogs wie tabellenexperte.de, blog.soprani.at oder auch die Beiträge von Florian Sandmann-Reetz in diversen Netzwerken.
Für Kolleg*innen, die gerade auf Office 365 gewechselt sind, verweise ich hier auch gerne auf folgende Artikel vom Tabellenexperten "Goodbye SVERWEIS, willkommen XVERWEIS!" oder "Die Raute: Ein kleines Zeichen mit großer Wirkung!".
Ein paar Worte zum Schluss und Entwicklungen hier im Blog
Ich gehe davon aus, dass auch hier im Blog das Thema Microsoft 365 und die einzelnen Office-Apps auch neben SAP eine Fülle an neuen Artikeln bescheren wird.Daneben sind für 2025 aber auch einige andere Themen und Projekte angesetzt, auf die ich mich ebenfalls sehr freue. Dabei kommen sowohl neue Onlinevideos als auch ein neues Buchprojekt auf meine Agenda und ich bin gespannt wie sich dieses umsetzen lassen wird. Das Thema Videobearbeitung wird mich (hoffentlich) nicht nur durch mein erfolgreiches Update auf Davinci Reslove 20 begleiten und auch der Bereich Autorenleben wird noch ein paar Punkte bringen. Immerhin die Steuererklärung ist nun inklusive EÜR abgegeben und dieses Jahr war ich sehr begeistert von der schnellen Übernahme in die Steuersoftware.
Hier nutze ich seit Jahren Taxman von Lexware (*) und bin froh, dass mit einer entsprechenden Vorerfassung der Belege die Abgabe der Steuererklärung erstaunlich schnell vollbracht ist.
Um jetzt aber wieder einen Bogen zur Einleitung (Controlling und Kontrolle) zu schlagen, mag ich das Thema Berichtswesen und interne Qualitätssicherung doch noch in den Fokus stellen. So schnell und technisch einfach eine Tabelle und entsprechende Eingabemasken auch bereitet sein mögen, so schnell kann es hier zu Unstimmigkeiten oder gar Abweichungen kommen, die im ersten Moment nicht auffallen.
Daher habe ich es mir angewöhnt, sowohl kleinere Kontrollen als auch direkt Analysen zu Tabellen außerhalb des Druckbereiches anzulegen, sodass bei einem späteren Bericht auf diese Ergebnisse zurückgegriffen werden kann.
Bevor eine solche Tabelle jedoch versendet wird, sollte sich versichert werden, ob auch die Person, die später mit der Tabelle arbeitet, ebenfalls die passende Officevariante einsetzt. Andernfalls kann dies zu bösen Überraschungen führen. Für meine eigenen Auswertungen und Tabellen bin ich aber zum Glück selbst verantwortlich und kann daher recht einfach davon ausgehen, dass auch aktuellere Formeln wohl funktionieren werden :-).
ein Angebot von Espresso Tutorials
unkelbach.link/et.books/
unkelbach.link/et.reportpainter/
unkelbach.link/et.migrationscockpit/
Diesen Artikel zitieren:
Unkelbach, Andreas: »Mehrere Zellen auf identische Werte in Excel prüfen - Matrixformel (Arrayformel) in Office 365 und Alternativen« in Andreas Unkelbach Blog (ISSN: 2701-6242) vom 3.7.2025, Online-Publikation: https://www.andreas-unkelbach.de/blog/?go=show&id=1382 (Abgerufen am 12.7.2025)
Keine Kommentare - Permalink - Office
Artikel datenschutzfreundlich teilen
🌎 Facebook 🌎 Bluesky 🌎 LinkedIn