Als Tabelle formatieren (II)

Letzte Woche habe ich das Thema „Als Tabelle formatieren“ begonnen und einen Bericht im Einproduktunternehmen gestaltet. Heute nun Teil 2 mit dieser Fragestellung:

Ein Bericht im Mehrproduktunternehmen

In der Regel wird ein Produktionsunternehmen mehr als ein Produkt erzeugen. Die nachfolgende Abbildung zeigt Menge, Preis und Umsatz von zwei Produkten. Für die daraus zu formatierende Tabelle benötige ich in der vorgeschlagenen Lösung namentliche Bereiche. Diese habe ich farblich gekennzeichnet. Die vergebenen Namen stehen darüber.

Tabelle17

Diese Tabelle lege ich im Arbeitsblatt „Daten1“ ab, darauf will ich später zugreifen.
Im Blatt „Focus1“ erstelle ich daraus analog dem Vorangegangenen eine formatierte Tabelle.

Tabelle5

Darin sind diese Formeln enthalten:

Umsatz1: =Tabelle7[[#Diese Zeile];[Menge1]]*Tabelle7[[#Diese Zeile];[Preis1]]
Umsatz2: =Tabelle7[[#Diese Zeile];[Menge2]]*Tabelle7[[#Diese Zeile];[Preis2]]
Summe Umsatz: =TEILERGEBNIS(9;Tabelle7[[#Diese Zeile];[Umsatz1]];Tabelle7[[#Diese Zeile];[Umsatz2]])

In der Ergebniszeile steht in der Spalte Menge1 die Formel =TEILERGEBNIS(109;[Menge1]), analog in den anderen Spalten.

Der Bericht soll zum einen die Auswahl von Monaten über den Filter und zum anderen die Auswahl eines der beiden Produkte oder die Anzeige des gesamten Umsatzes ermöglichen.
Die Auswahl der Monate kann ich analog dem oben Geschilderten vornehmen.

Für die Auswahl eines Produktes wäre ein horizontales Filtern notwendig. Das aber ist keine Standard-Funktion in Excel.

Lukas Rohr von ExcelNova allerdings hat darüber nachgedacht und eine Lösung gefunden. Diese bezeichnet er selbst jedoch als ineffizient, so dass er eine VBA-Lösung vorgezogen hat.

Hier der Link zu ExcelNova:

http://excelnova.org/2014/04/wie-man-eine-excel-tabelle-horizontal-filtert/

Ich möchte aber keine VBA-Lösung, sondern versuchen, nur mit Excel mein Ziel zu erreichen.

Zunächst erstelle ich eine Liste mit dem Namen „Produktliste“. Darin enthalten sind die Werte 1, 2 und Alle.

Nun erstelle ich eine neue formatierte Tabelle mit den Spalten Monat, Menge, Preis und Umsatz.

Darüber richte ich ein Auswahlfeld ein und lasse über die Datenüberprüfung eine Liste mit der Quellangabe =ProduktListe zu.

Tabelle7

Im Bild ist zu sehen, dass das Produkt 1 gewählt wurde und die Tabelle bereits Werte enthält. Wie kommen diese nun dahin?

Die Spalten C, D und E enthalten diese Formeln:

C186
=WENN($C$183=“Alle“;““;INDEX(WerteTabelle;VERGLEICH($B186;MonatListe;0);VERGLEICH(C$185&$C$183;KopfListe;0)))

D186
=WENN($C$183=“Alle“;““;INDEX(WerteTabelle;VERGLEICH($B186;MonatListe;0);VERGLEICH(D$185&$C$183;KopfListe;0)))

E186
=WENN($C$183=“Alle“;INDEX(Summe_Umsatz;VERGLEICH($B186;MonatListe;0);1);Tabelle11[[#Diese Zeile];[Menge]]*Tabelle11[[#Diese Zeile];[Preis]])

C198
=WENN($C$183=“Alle“;““;TEILERGEBNIS(109;[Menge]))

E198
=TEILERGEBNIS(109;[Umsatz])

Damit habe ich alles erschlagen. Ich kann eines der beiden Produkte auswählen und Menge, Preise und Umsatz sehen, ich kann aber nur den Umsatz gesamt abfragen.
So sieht die Tabelle bei Auswahl von „Alle“ aus:

Tabelle8

Da mehrere Produkte in den gesamten Umsatz einfließen, wird hier keine Menge und kein Preis ausgewiesen.

Zusätzlich habe ich natürlich auch noch die Möglichkeit, mit dem Filter Monate auszuwählen.

Tabelle18

Das war es, was ich zu diesem Thema für dieses Mal zeigen wollte.

Advertisements

2 Kommentare zu „Als Tabelle formatieren (II)“

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