Mithilfe von MTRANS ganze Spalte verschieben?

Question

Hallo zusammen
Ich steh mal wieder vor einem Excel-Problem.
Ich habe eine Liste gemacht, wo ich Teilnehmer für ein Yoga-Studio eintragen kann sowie deren Gruppe (Blatt Namensliste). Da immer mal wieder neue dazukommen, habe ich diese mithilfe von MTRANS auf die restlichen Blätter (Januar-Dezember) übernommen. 
Damit die Teilnehmer nach Gruppe oder Namen sortiert werden können, habe ich die Funktion "Filtern" eingerichtet. Glücklich, dass alles klappt, habe ich es ausprobiert und dann schnell einen sehr blöden Fehler gefunden: Die Namen werden zwar neu sortiert, aber die Liste mit den Teilnahmen (Zahl) wird natürlich nicht ebenfalls neu sortiert.

Ich habe jetzt verschiedenes versucht mit Hilfe des XVERWEISes (Auf Blatt "Namensliste": =XVERWEIS (A3 Name in der Namensliste) ;Januar!C3:S3;SUMME(Januar!C9:C39))), aber da kommt der Fehler #WERT!. Kann der XVERWEIS nicht in einer horizontalen Matrix suchen...? Auch "SUMMEWENN" nützt mir nichts. Wenn ich die Namen neu sortiere, dann muss die ganze Spalte ebenfalls neu sortiert werden. 
Kann mir jemand dabei helfen?

Gipfelstuermer · Answer

alte Antwort komplett entfernt, da irrelevant:

lies das mal Bitte durch und sag ob das was für dich ist, dann schicke ich die Datei zurück, wenn nicht dann muss ich nochmal nachdenken. Ich hab das so gut wie möglich versucht zu dokumentieren.

Ich habe erstmal die einfachste und komfortabelste Lösung genommen, die allerdings nur in Excel 2021 und 365 geht.

Basierend auf deiner Datei, habe ich dir 2 Varianten eingefügt, die nahezu identisch sind dabei habe ich allerdings im Gegensatz zu dir die Namenstabelle unberührt gelassen und arbeite mit einem weiteren Tabellenblatt, das sich Auswertung nennt, womit sich die Teilnehmer nach deinen Vorgaben sortieren lassen.

Denn das Problem des Sortierens ist, die "1"en in der Tabelle haben keinerlei Bezug, weder zu den Namen, noch zur Ursprungstabelle, da sie ja händisch eingetragen sind, daher gibt es auch keine Möglichkeit die mit zu sortieren, da Excel in einer Formel nicht weiß, was wo vorher war und wo es jetzt ist und andere Dinge entsprechend nicht mit umsortiert.

Vor allem Fehlt Excel aber die Info nach was sortiert wird, da dies ja nur über den Filter realisiert wird und das wird in keiner Zelle irgendwo hinterlegt, andernfalls könnte man über die Funktion Sortieren/Sortieren nach aushelfen. @alle wenn hier einer einen Weg kennt nur zu!

Variante 1

Bild zum Beitrag

Es wurde ein zusätzliches Tabellenblatt mit dem Titel Auswertungen angelegt und dort die Tabellengrundstruktur, Spaltenbeschriftungen etc. "kopiert", zusätzlich wurde in A2, B2, C2, D2 ein Dropdown eingefügt, die Datenbasen sind die folgenden:

A2: A3:A30; B2: B3:B30; C2: A3:B3 D2: Aufsteigend, Absteigend (manuell eingetragen)

Dabei kommt die folgende Formel in A4 zum Einsatz:

=SORTIEREN(FILTER(Namensliste!A3:N30;(Namensliste!A3:A30<>"")*(WENN(B2="";1;Namensliste!B3:B30=B2));"");WENN(C2="Gruppe";2;1);WENN(D2="Absteigend";-1;1);FALSCH)

Kurz erklärt von innen nach Außen:

Die Funktion Filter greift auf deine original Datenliste zu (Namensliste!A3:N30)

Und gibt nur die Daten aus bei denen in der Spalte A etwas drin steht. (Namensliste!A3:A30<>"")

zusätzlich wird geprüft und das ist so eine Art Filter, ob in der Zelle B2 etwas drin steht, ist dies der Fall werden nur die Datensätze ausgegeben, in denen das gleiche wie in der Spalte B steht (WENN(B2="";1;Namensliste!B3:B30=B2)), in der Zelle B2 sind die jeweiligen Kurse hinterlegt also Yoga Mo, Yoga Mi, Einzel), somit lassen sich diese gezielt einzeln anzeigen. z.B. so:

Bild zum Beitrag

zusätzlich habe ich noch 3 weiter Dropdowns hinterlegt A2 (dies ist ein reines Komfort Feature), hier können einzeln Namen der Teilnehmer, ausgewählt werden, die in diesem Filter enthalten sind, falls man mal einen Teilnehmer hervorheben will. In der langen Liste könnte ich mir vorstellen, dass man ggf. mal in der Zeile verrutscht. Diese wird dann einfach nur gelb hinterlegt.

Bild zum Beitrag

Nun greift die Funktion Sortieren, die den Filter umschließt. Die Funktion sortieren, erwartet nach der Matrix als ersten Parameter den Spaltenindex ( WENN(C2="Gruppe";2;1) ), Ist also Gruppe ausgewählt wird nach Gruppe (2 Spalte), sortiert, ansonsten der Standard (Name), der nächste Parameter legt die Sortierreihenfolge fest ( WENN(D2="Absteigend";-1;1) -1 ist Absteigend, 1 Aufsteigend.

Wenn du irgendwann mal mehr Spalten hast, nach den Sortiert werden soll, müsstest du die hier nachpflegen, hier würde sich dann besser die WENNS Funktion eigenen, da die zum abfragen mehrerer Werte besser geeignet ist. Die Syntax wäre:

=WENNS(Prüfung 1;Wenn Wahr 1;Prüfung 2; Wenn Wahr 2;1; Standard Wert)

In C2 steht demzufolge der Eintrag Gruppe oder Kundenname. In D2 Auf- oder Absteigend.

Bild zum Beitrag

Das ist auch schon alles, wenn man es einmal verstanden hat ist es gar nicht so kompliziert. Und macht vieles viel leichter, in alten Excel Versionen muss man sich leider die Filter Funktion sehr mühsam mit einer Kombination aus Kkeinste, Zeile, Zählenwenn und Index zusammen bauen und macht sie alles andere als Übersichtlich.

Und wie gut das dann mit sortieren klappt hab ich noch nie probiert.

Variante 2

Wie oben schon angedeutet, ist die prinzipiell identisch, mit dem einzigen Unterschied, dass bei Variante 2 die Namensliste als "intelligente" Tabelle formatiert ist, dadurch wird der Filter automatisch angepasst, wenn sich die Tabelle vergrößert, da dieser nun über die Spaltennamen direkt angesprochen werden kann anstatt A3:A30 also Teilnehmerliste[Kundennamen] auch wenn man nun Spaltennamen umbenennt, wird die dahinterliegende Filter Funktion automatisch mit angepasst, ändert man also als Beispiel Kundennamen in Teilnehmer, dann ändert sich in der Funktion auch alles direkt mit siehe hier:

dazu muss man sich lediglich irgendwo in der Tabelle befinden und drückt entweder strg + t oder geht auf Einfügen -> Tabelle, im Anschluss wird man noch gefragt ob die Tabelle Spaltenköpfe enthält, in aller Regel wird hier der Bereich vollkommen richtig erkannt, nur bei vollständigen Leerspalten kommt es da mal zu Problemen, aber dann ändert man das eben händisch ab. Bild zum Beitrag

Nun ist oben rechts neben Hilfe ein weitere Reiter Tabellenentwurf entstanden, ganz links kann man der Tabelle dort einen sinnvollen Namen geben, ich habe sie hier Teilnehmerliste genannt.

Bild zum Beitrag

Der Rest ist wie gesagt alles identisch bis auf geänderte Namen für die Bezüge.

Tipps meinerseits:

ich würde bei reinen Anwesenheitslisten (deine Jan bis Dez Listen) also wo es nur ja oder nein gibt, nicht mit 1 oder 0 arbeiten, in deinem Falle hier kein Problem aber oft kommt es bei anderen Prüfungen / Wahrheiten oder zählen dann zu Problemen, da eben 1 auch als wahr interpretiert werden kann, ich würde z.B. X eintragen und Zählenwenn oder Summenprodukt nutzen

=ZÄHLENWENN(c9:c39;"x")
=SUMMENPRODUKT((c9:c39="x")*1)

und man umgeht zusätzlich das Problem versehentlich mal 2 einzutragen und dann ein falsches Ergebnis zu bekommen. Deine Liste ist recht überschaubar, aber kann schneller passieren als man denkt.

Außerdem hat mich Excel nach dem öffnen direkt gewarnt, das zahlreiche ungenutzte Zellbereiche formatiert waren und das war tatsächlich so Tabellenrahmen etc. Bei deiner Namensliste hab ich das gelassen, da ich nicht weiß ob da unten vielleicht irgendwelche Daten enthalten sind, auf die du dich beziehst, die außerhalb des Sichtfelds ein sollen. Ich benutze sowas z.B. gerne, wenn ich mir DropDown Menüs erzeuge dann lege ich die Texte dafür bei A1000 und folgende ab, so viele Zeilen hat selten eine Tabelle. Vielleicht ist es aber auch einfach im Zuge der Vorbereitung für das Zuschicken der Tabelle entstanden. Wenn nicht einfach alles mal bereinigen was unterhalb des genutzten Bereichs ist. Kostet nur Speicher und ggf. Rechenleistung.

Bei der duplizierten Tabelle für die Intelligente Tabelle, hab ich alles entfernt.

Frage:

Wozu genau dient eigentlich die Zeile 5/6/7 in den Monaten direkt, wenn du schon die Ergebnisse in der Namensliste hast, brauchst du dir die ja nicht nochmal dort ausrechnen lassen, oder soll es der Übersicht dienen?

GutenTag2003 · Answer

Ergänzung 21.6: Wenn ich die Namen neu sortiere (z.B. alphabetisch statt nach Gruppe), bleibt die Anzahl hinten im gleichen Feld

1x ist Michael Ende im Januar 5x, im Februar 4x (nach Gruppe sortiert),

1x ist Jessica Keller im Januar 5x, im Februar 4x (Alphabetisch sortiert)

Dieses Problem liegt darin, dass Du nur die Spalte Name und Gruppe sortierst, nicht aber die Zahlen der einzelnen Monate in die Sortierung einbeziehst. Damit bleiben die Werte der folgenden Spalten - unsortiert - in ihren Zellen.

Vor dem Sortieren alles Spalte mit einbeziehen, dann bleiben die Werte auch bei den jeweiligen Kunden.

Mithilfe von MTRANS ganze Spalte verschieben?

2 Antworten