Mithilfe von MTRANS ganze Spalte verschieben?
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?
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)
2 Antworten
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
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:
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.
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.
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.
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.
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?
![- (Formel, Microsoft Excel, Microsoft Office)](https://images.gutefrage.net/media/fragen-antworten/bilder/550307426/0_big.png?v=1720385174000)
![- (Formel, Microsoft Excel, Microsoft Office)](https://images.gutefrage.net/media/fragen-antworten/bilder/550307426/1_big.png?v=1720385174000)
![- (Formel, Microsoft Excel, Microsoft Office)](https://images.gutefrage.net/media/fragen-antworten/bilder/550307426/2_big.png?v=1720385174000)
![- (Formel, Microsoft Excel, Microsoft Office)](https://images.gutefrage.net/media/fragen-antworten/bilder/550307426/3_big.png?v=1720385174000)
![- (Formel, Microsoft Excel, Microsoft Office)](https://images.gutefrage.net/media/fragen-antworten/bilder/550307426/4_big.png?v=1720385174000)
![- (Formel, Microsoft Excel, Microsoft Office)](https://images.gutefrage.net/media/fragen-antworten/bilder/550307426/5_big.png?v=1720385174000)
![- (Formel, Microsoft Excel, Microsoft Office)](https://images.gutefrage.net/media/fragen-antworten/bilder/550307426/6_big.png?v=1720385174000)
Alles klar sehr gerne, wird aber heute nichts mehr glaube ich. Ich werde es morgen im Lauf des Tages hochladen.
Allgemein gilt intelligente Tabellen sind etwas Wartungseinfacher wenn sie Einmal funktionieren, da besser ersichtlich ist wo welcher Wert herkommt und man ganze Spalten ansprechen kann, sind aber dennoch nicht jedermanns Sache. Besonders wenn man Area Formeln verwendet können Sie auch Nachteile haben siehe hier:
Excel Eindeutig-Funktion in Dynamische Tabelle? (Funktion, Microsoft Excel, Tabelle) - gutefrage https://www.gutefrage.net/frage/excel-eindeutig-funktion-in-dynamische-tabelle
In meiner Antwort wird es ein wenig erklärt.
Die Tipps sollten nur Anregungen sein ich kenne es aus eigenen Bereichen mit denen ich mich wenig auskennen man macht etwas einfach und freut sich das geht und macht sich oft gar keine Gedanken ob das Eleganter geht. Am Ende müsst ihr damit arbeiten.
Auch die Komfort Funktionen wie Zeile gelb markieren kannst du natürlich gerne wieder raus löschen, dass war nur eine gedacht sinnvolle Idee. Ob das für dich was taugt musst du selber wissen.
hier der Link
https://we.tl/t-KCVdRE5Icg ab Stand 17.7.24 18:48 7 Tage gültig.
wenn ich dir helfen konnte freue ich mich immer über eine Hilfreichste Bewertung um auch nach außen hin als Fachmann aufzutreten.
Hallo Gipfelstürmer
Erstmal Danke für die Antwort! Ich war knapp zwei Wochen im Ausland und hatte keinen Laptop dabei. Und am Handy hats mir layouttechnisch alles über den Haufen geworden. Ich werde deine Antwort nächste Woche mal durchlesen und gebe dann sehr gerne Rückmeldung, ob es geklappt hat.
LG Trini
@Gipfelstuermer Genau das ist meine Frage - wie kann ich die Zahlen mitverschieben...?
Ich kann dir das Dokument schicken, jedoch brauche ich hierfür eine Mailadresse, oder?
bei oben genannten Dienst Wetransfer.com kannst du links, sobald du akzeptiert hast unten auf die 3 Punkte klicken anstatt Mail auf Übertragunslink erstellen klicken und diesen im Anschluss teilen. Datei einfach per Drag & Drop reinziehen, "erhalte einen Link" anklicken und fertig, im Standard ist der Link 7 Tage gültig, sollte aber für den Download reichen, ich finde die Variante besser, da es so anonym ist, wir geben unsere Mails schon viel zu oft bei tausend Diensten, Foren etc. an und viele Menschen nutzen dafür immer die gleiche Mail.
Die Seite ist super, danke! Ich habe auch eine extra Mail nur für solche Sachen, aber die Seite ist wirklich praktisch (ich hoffe, es funktioniert...)
LG
Hab eine neue Antwort gegeben. Ich hoffe mein Versuch ist diesmal besser.
Hallo Gipfelstürmer
Danke für deine schnelle Antwort!
Leider funktioniert die Formel bei mir nur bedingt - Wenn ich nämlich die Namen z.B. alphabetisch statt nach Gruppe sortiere, dann wechselt die Anzahl Teilnahmen nicht ebenfalls den Platz, sondern die bleiben in der Zelle drinnen. Ich habe in der Fragestellung zwei Bilder angehängt, wo es ersichtlich ist - Auch wenn das, soviel ich die Formel verstanden und nachgelesen habe - funktionieren müsste...? Weisst du, wo der Haken liegt?
LG Trini
Du musst natürlich die Zahlen mit verschieben beim sortieren kannst du ggf deine Tabelle mal teilen? Denn ich weiß nicht was bei dir alles noch für Formeln drin stecken. Ich nutze dafür immer den Dienst Wetransfer bitte anonymisiert
Rechtsklick auf die Datei -> Eigenschaften -> Details persönliche Informationen entfernen.
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.
Lieber Gipfelstürmer
So, jetzt hatte ich genug Zeit, deine Antwort durchzulesen und im Kopf nachzuvollziehen - in meiner Schule hatten wir leider nie Informatik.
Die Zeilen 5/6/7 sind für meine Kollegin als bessere Übersicht. Sie hat 10er Abos. So kann sie sehen, wann so ein Abo voll ist.
Die Zahlen bei den Namen waren ursprünglich nicht drinnen - das war mehr noch ein Überbleibsel von meinem Rumprobieren.
Die erste Lösung gefällt mir (bisher) besser. ich wäre dir sehr dankbar, wenn du mir das Dokument wieder zurückschicken könntest.
Liebe Grüsse und nochmals herzlichen Dank!
Trini