In diesem Beispiel zeige ich wie man die Summe eines Schnittpunktes in einer Kreuztabelle oder Matrix berechnen kann mit der SUMME Formel von Excel. Im Anschluss schauen wir uns an wie man so etwas geschickt einsetzen kann in einem Dashboard oder KPI Bericht.
Schnittmengen Berechnen mit SUMME
SUMME(Zahl1; [Zahl2]; …])
Die Syntax von SUMME kennt jeder und bedarf keiner Erklärung. Auch wissen die meisten Leute dass man mehrere Bereiche eingeben kann die dann addiert werden.
Interessant ist jedoch dass man die SUMMEN Formel auch benutzen kann um Schnittmengen zu berechnen. Dies macht man in dem man in einem Argument zwei Zellenbereiche angibt die durch ein Leerschlag getrennt sind.
Im Beispiel steht in der Zelle C10: =SUMME(B3:N3 C2:C6)
Wie man im Screenshot sieht ist in dem Bereich der sich kreuzenden Angaben der Wert 50, welches dann auch das Resultat ist dieser Formel ist.
Es können beliebig viele Bereich eingegeben werden, die Bereiche müsse nur jeweils durch ein Leerschlag getrennt werden. Zwei oder maximal drei Bereiche dürfte den meisten Leuten jedoch genügen, denn es wird ab dann auch ziemlich unübersichtlich!
So kann man Schnittmengen in Excel berechnen die in einer Tabelle stehen!
Dynamische Dashboards mit Schnittmengen
Damit das ganze aber auch beim Dashboard erstellen zum Einsatz kommen kann müssen wir noch ein wenig extra Arbeit leisten. Namentlich wollen wir dem User die Möglichkeit geben selber die Schnittmengen auszuwählen.
Damit das ganze leserlich und einfach zu verstehen ist benutze ich den Namens-Manger um den Bereichen einfach zu verstehenden Namen zu geben. Dies hat in unserem Beispiel den doppelten Zweck der Leserlichkeit der Formel und dass wir die Bereiche später auch als Auswahl in einem Dropdown-Menü den User anbieten können.
In Beispiel habe ich die Bereiche für alle Produkte und Quartale im Namens-Manger erfasst.
So ist es nun sehr einfach eine sprechende Formel zu erstellen die jeder versteht. Genau das möchten wir auch für unser kleines Dashboard.
Damit unsere User im Dashboard aber nicht jeweils die Formel anpassen müssen benötigen wir noch die INDIREKT Formel.
Die Schnittmengen Technik kombiniert mit der INDIREKT Formel erlaubt uns auf elegante Weise aggregierte Werte zu erstellen ohne den Einsatz von SUMMENPRODUKT oder anderen Verweis Formeln!
Die INDIREKT Formel gibt den Bezug eines Textes zurück. Da ich die Bereiche mit sprechenden Namen im Namens-Manager versehen habe können wir diese nun als Auswahl via Dropdown-Liste anbieten und dann mit INDIREKT in die SUMMEN Formel einfügen.
Die Formel sieht nun so aus:
=SUMME(INDIREKT(C17) INDIREKT(B17))
Wie man sieht, ist der Verweis der Formel nun nicht mehr auf den eigentlichen Zahlenbereich, sondern auf die beiden Dropdown-Listen welche in Zellen B17 und C17 enthalten sind.
Das ganze lässt sich dann nach belieben runter kopieren, und man kann dann sehr schnell einzelne Produkte und Quartale vergleichen.
Download
Zum Anschauen oder Ausprobieren könnt Ihr Euch die Datei runterladen: