Office: (Office 2019) Subtabelle erstellen

Guten Tag,
ich bekomme automatisiert eine Reihe von Daten die ich gerne in subtabellen (neues Tabellenblatt) aufteilen möchte. Natürlich könnte ich einfach einen Filter setzen aber ich möchte es nicht händisch machen sondern "automatisieren". Wichtig ist das alle werte übernommen werden.

Ich habe eine Tabelle als Beispiel angehangen. Hier möchte ich die Daten nach den Orten aufgliedern. Es soll entsprechend in der Tabelle 2(Düsseldorf) die Werte (Entfernung, Kosten und Auto) aufgelistet werden. Die Werte die aktuell bei den Städten stehen habe ich für das Beispiel kopiert damit man sieht wie es aussehen soll.

Mit dem XVerweis bekomme ich nur eine Zeile. Es sollen aber alle Zeilen in den Düsseldorf auftauchen aufgelistet werden. Im Idealfall alle werte der Zeile. Die Anordnung der Daten ist leider nicht veränderbar da es automatisch generiert wird.

Ich hoffe das Thema ist einigermaßen zu verstehen und bedanke mich vorab schonmal für die Hilfe.

 

Hallo Mojo,

was du da vorhast, ist keine optimale Arbeitsweise. Mit dem Autofilter kannst du jederzeit deine Liste nach dem gewünschten Ort filtern. Gleichartige Daten gehören grundsätzlich in eine Liste.

 

Hallo Mojo,

mit der Version 365, siehe Datei.

Gruß
Roger

 

Danke erstmal für deine Antwort. Das Konstrukt ist sicherlich nicht perfekt aber so gewollt.

 

Vielen Dank auch für deine Antwort. Ich werde versuchen die Formel entsprechend nachzustellen und würde falls ich nicht weiter komme nochmal auf dich zukommen.

 

Ok leider habe ich kein Office 365 wodurch ich die Formel nicht verwenden kann. Gibt es eine alternative für "_xlfn.CHOOSECOLS"?

 

Hallo

Anbei zwei Lösungen wie man es auch noch tun könnte.
A) PQ
B) Pivot

 

Hallo,

Statt CHOOSECOLS(Bereich;SpalteNr) bzw. zu Deutsch SPALTENWAHL(Bereich;SpalteNr) kann man auch
INDEX(Bereich;0;SpalteNr) verwenden.

Weiters habe ich dir in der beiliegenden Datei die "altbackene" Lösung deines Problems beigefügt.

1) Im Arbeitsblatt "Daten" habe ich deine Entfernungsdaten in die strukturierte Tabelle "tbEntfernungen" umgewandelt. Das hat den Vorteil, dass man beliebig viele Zeilen anfügen kann, ohne an den Formeln, die darauf zugreifen, etwas ändern zu müssen.

2) Ich habe in den Namensmanager den Namen "ListeOrte" aufgenommen mit folgender Formel: =tbEntfernungen[Ort]
Diesen Namen habe ich in den DropDowns in Zelle G2 aller Arbeitsblätter verwendet, womit sich die DropDowns automatisch an die Zeilenanzahl der Tabelle "tbEntfernungen" anpassen.

3) In den Arbeitsblättern für die Orte (Düsseldorf, Köln, Frankfurt, etc.) habe ich dir in Zelle G2 das DropDown aus Punkt 2) eingefügt. Ferner habe ich in Zelle A2 folgende Formel eingegeben:
=WENNFEHLER(INDEX(tbEntfernungen; AGGREGAT(15;6;ZEILE(tbEntfernungen[Ort])-ZEILE(tbEntfernungen[#Kopfzeilen])/(tbEntfernungen[Ort]=$G$2);ZEILE(A1));SPALTE(A1));"")
Diese habe ich durch Formelkopieren (!!) in den restlichen Zellbereich von A2:D20 kopiert. Diese 19x4 Formeln verwenden dabei die DropDown-Auswahl aus Zelle G2.

4) Falls in deinen Daten (Tabelle "tbEntfernungen") weitere Orte dazukommen, brauchst du nur eines der Orte-Arbeitsblätter zu kopieren und die DropDown-Auswahl auf diesen Ort umzustellen und der Rest müsste ohne weitere Änderung sofort funktionieren. Mit "München" habe ich dir das beispielhaft getestet.

 

Vielen Dank für die Hilfe und vor allem die super Erklärung.
ich habe in der zwischenzeit natürlich auch etwas "probiert" und arbeite mit folgender Formel.
=FILTER(Daten!A:D;Daten!B:B="Düsseldorf") Für Köln/Frankfurt dann entsprechend.
Es werden definitiv nur diese 3 Städte zur Auswahl stehen.
Vielleicht denke ich zu einfach aber wieso sollte ich meine Formel nicht verwenden?

 

Hallo,

wenn die FILTER-Funktion in Excel2019 bereits funktioniert - warum solltest du sie dann nicht verwenden?
Diese Funktion hat das Formelungeheuer, das man vor FILTER() benötigte, überflüssig gemacht. Da ich nicht wusste, ob sie schon funktioniert bzw. in welcher Weise sie gegebenenfalls funktioniert in Excel2019 , habe ich die frühere Variante genommen.