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.

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.

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.

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:

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.

Das war es, was ich zu diesem Thema für dieses Mal zeigen wollte.
Entdecke mehr von Clevercalcul
Melde dich für ein Abonnement an, um die neuesten Beiträge per E-Mail zu erhalten.
Hi Gerhard, mir gefällt Deine clevere Lösung! Danke auch für den Link! Gruss, Lukas
Hi Lukas, danke für deinen positiven Kommentar, freut mich, dass es dir gefällt. Eine Filterlösung würde ich aber bevorzugen.
Grüße
Gerhard