Excel – Kreuztabelle per Formel in Liste umwandeln

In Excel-Video # 468 http://youtu.be/kXcRr0VkTKo wurde die Aufgabe gestellt, eine Kreuztabelle in eine strukturierte Liste zu verwandeln. Die Wahl der Mittel ist beliebig. In Video Excel # 470 http://youtu.be/_ZuvBjhCu9A wird eine Lösung über das Add-In Power Query (für Excel 2010/2013) beschrieben.

Hier folgt nun eine Formellösung, die eine kreuztabellierte Liste in eine strukturierte Liste wandelt. Dabei wird dynamisch darauf reagiert, ab welcher Spalte die Daten beginnen, wie viele Spalten es in den Daten der Kreuztabelle gibt und welche Spalten vorne fest in jedem Listeneintrag stehen sollen.

472_1

Achtung. Bei dieser Formel wird eine leere Zelle als 0 ausgegeben. Man hätte zusätzlich mit WENN(ISTLEER(… arbeiten können.

472

472_3

Relevant sind die Funktionen GANZZAHL und REST, die für die Funktion INDEX benötigt werden, um die Werte einer m Spalten * n Zeilen großen Tabelle untereinander aufzulisten. GANZZAHL nimmt den nächsten kleinen ganzzahligen Wert einer Rechenoperation. Teile ich zum Beispiel 2/7 ist der Wert 0. Teile ich 15 durch 7 ist der Wert 2. REST teilt ebenfalls durch einen Wert und ignoriert, wie oft der Wert in die Zahl passt, sondern guckt nur, welcher Rest bei der Division übrig bleibt.

Die genaue Wirkungsweise der Funktion wird später in einem Video oder hier beschrieben.

Hier eine Variante, in der zusätzlich noch mit ISTLEER auf leere Zellen geprüft wird. Ansonsten käme jeweils eine 0 bei Zeilen mit leeren Zellen in der Kreuztabelle.

472_d

Hier ist die Lösungsdatei mit Formeln:
Excel 468 – Formellösung

Statt so vieler verschachtelter Wenn-Funktionen könnte man die passende Funktion auch mit WAHL ermitteln.

472g

Hier ist die Lösungsdatei mit der Funktion WAHL:
Excel 468 – Formellösung 2

Und das passende Video dazu:

Videolink: http://youtu.be/wO0IZmGIq0k

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s