Blockweise nach 1. Zeile im Block sortieren

Mehrere Blöcke nach der jeweils 1. Zeile sortieren

Dem Einfallsreichtum unterschiedlicher Anwender (aber auch Programmen, die Daten exportieren) ist keine Grenze gesetzt. 🙄 So habe ich in einem Forum folgenden Beitrag gefunden, den ich auszugsweise zusätzlich zu einem von mir erstellten Screenshot hier einmal zitiere:

…die Tabelle umfasst insgesamt 50 Namen und die jeweiligen Unterfelder jede Woche kommt ein Eintrag in einer weiteren Spalte hinzu.
Meine Frage wäre jetzt kann ich die Namen irgendwie von A-Z Sortieren so das die Zahlen in den Spalten sich mit verschieben Aber Zeile 3-9 in Spalte A die Begriffe nicht betroffen sind?

Screenshot aus dem Forum, noch unsortiert

Screenshot aus dem Forum, noch unsortiert

Da relativ schnell ein sehr fähiger Antworter-Kollege reagiert hat und darlegte, dass die Struktur der Daten, so wie sie existiert ausgesprochen suboptimal ist, habe ich mich in diesem Thread im Hintergrund gehalten und für mich und natürlich auch für Sie drei Lösungsansätze erarbeitet. Die endgültige (Zwischen-) Lösung bedarf anschließend noch einer Darstellung als PivotTable. Und die Daten können Sie hier von unserem Server herunterladen.

▲ nach oben …

Möglichkeit 1: In den Name-Zeilen sind alle Datum-Spalten garantiert leer

Bei solchen Herausforderungen gilt es erst einmal ein Kriterium herauszufinden, welches die Erkennung der unterschiedlichen Blöcke für den Computer, für Excel möglich macht. Dass es in der Realität garantiert nicht das Wort Name mit einer angehängten Ziffer ist, versteht sich von alleine. Das wäre ja zu einfach. 😆 Aber es ist sehr wahrscheinlich, dass in den Spalten rechts des jeweiligen Namens nichts eingetragen ist, die Datum-Spalten in dieser Zeile also wirklich leer sind. Und „wirklich leer“ bedeutet, dass auch keine Formel in der Zelle steht.

Auch wenn hier nur 3 von wahrscheinlich wesentlich mehr kalendarischen Daten in Spalten erfasst sind ist davon auszugehen, dass in weiteren eingetragenen Tagen die Zelle jeweils leer ist. Darum werde ich für eine Prüfung, ob in Spalte A nun ein Name oder ein Attribut (Produkt) steht, nur Spalte B:D auswerten. Und das werde ich (wie übrigens auch bei den anderen beiden Möglichkeiten) mit Power Query erledigen. Ich importiere also die Tabelle in den Power Query Editor und das stellt sich nun so dar:

Die eingelesenen Daten nach dem Import

Die eingelesenen Daten nach dem Import

Sie erkennen, dass in den Zeilen mit den Namen alle weiteren Felder leer sind, also den Wert null enthalten. Darauf baut auch die Logik dieser 1. Lösung auf. Um zu vermeiden, dass zufällig eine der ersten 3 Datumsspalten leer ist, obwohl dort in Spalte1 ein Nahrungsmittel eingetragen ist, überprüfe ich einfach ob alle 3 Spalten wirklich leer sind. (OK, ein Rest-Risiko bleibt dennoch, wenn auch ein geringes.)

Ein Klick auf den Menüpunkt Spalte hinzufügen | Benutzerdefinierte Spalte und Sie tragen im Dialog bei Neuer Spaltenname beispielsweise Name und bei Benutzerdefinierte Spaltenformel folgende Formel ein:
if [15.02.2018]=null and [18.02.2018]=null and [25.02.2018]=null then [Spalte1] else null
wobei sie die Spaltennamen (das jeweilige Datum) durch einen Doppelklick im Bereich Verfügbare Spalten (rechter Kasten) einfügen können. Das stellt sich nun so dar:

Die im Dialogfenster eingetragene Formel

Die im Dialogfenster eingetragene Formel

Sie achten dabei auf exakt die gezeigte Groß- Kleinschreibung. Als Ergebnis wird in die neu erstellte Spalte Name entweder der Name oder der Wert null eingetragen. Anschließend erstellen Sie eine weitere Benutzerdefinierte Spalte und erarbeiten sich beispielsweise selbst die Formel, wo sie die Spalte Name auf null prüfen und falls das zutrifft den Wert aus [Spalte1] eintragen lassen oder aber null. Der Name der neuen Spalte ist Attribut. Alternativ wählen Sie Spalte hinzufügen | Bedingte Spalte und geben dort im Dialog diese Werte ein:

Der Dialog mit den ausgefüllten Werten

Der Dialog mit den ausgefüllten Werten

Allerdings werden sie keinen Erfolg haben, wenn sie bei Ausgabe den Text Spalte1 eingeben. Um genau diesen Eintrag dort einzufügen klicken Sie auf das Symbol einer Tabelle links des Kombinationsfeldes, wählen dort Spalte auswählen aus und nach einem Klick in das Kombinationsfeld wählen Sie dort den Eintrag Spalte1.

In beiden Fällen erreichen Sie das Ziel, dass in der neu erstellten Spalte entweder der Wert null steht oder der Name des entsprechenden Lebensmittels/Produkts. Damit sind sie fast am Ende des Zwischenschritts angelangt. Löschen Sie nun Spalte1, markieren sie die beiden letzten Spalten und verschieben diese an den Anfang. Das ist zwar funktionell nicht wichtig, dient aber der Optik. Markieren Sie die Überschrift Name, Rechtsklick in die Überschrift und im Kontextmenü Ausfüllen | Nach unten. Ruckzuck steht in jeder Zeile der Spalte Name der korrekte Wert.

Jetzt kommt wirklich der Endspurt … 😎  Filtern Sie die Spalte Attribut dergestalt, dass die Zeilen mit dem Wert null entfernt werden. Markieren Sie die Spalten Name und Attribut, Rechtsklick in eine der beiden Überschriften und Andere Spalten entpivotieren. Ruckzuck sind aus den bislang 21 Zeilen mit 5 Spalten nun 63 Zeilen in 4 Spalten geworden. Jetzt nur noch die Überschrift Attribut.1 zu Datum ändern.Und das ist nun (fast) eine geeignete Datenbasis für die später zu erstellende PivotTable.

▲ nach oben …

Möglichkeit 2: Die Anzahl der Begriffe/Produkte ist bei jedem Namen gleich

In diesem Beispiel ist es ja so, dass unterhalb jedes Namens die gleiche Anzahl von Argumenten bzw. Produkten aufgeführt ist. Wenn das gewährleistet ist, bietet sich eine aus der Excel-Sicht optimale Möglichkeit. Da es stets 7 Produkte sind kann gesagt werden, dass jeweils die 1., die 9., Die 17. Zeile, etc. den Namen in Spalte A enthält, alle anderen Zeilen des Datenbereichs die Bezeichnung des Nahrungsmittels. Darauf aufbauend lässt sich hervorragend berechnen, ob nun in der entsprechenden Zeile ein Name steht oder nicht.

Nach dem Import der Daten in Power Query geht der Weg wie schon oben beschrieben über Spalte hinzufügen | Benutzerdefinierte Spalte und im 1. Schritt erzeugen Sie eine Indexspalte, in dem sie einfach auf die gleichnamige Schaltfläche klicken. Danach eine weitere Benutzerdefinierte Spalte und als Neuer Spaltenname geben Sie Name ein. Fügen Sie darunter folgende Formel ein:
if Number.Mod([Index],8)=0 then[Spalte1] else null
und umgehend wird in der neu erzeugten Spalte der Name (aus der ersten Spalte) eingetragen oder null. Achten Sie auch hier auf die Groß- Kleinschreibung, sonst handeln Sie sich eine Fehlermeldung ein. – Eine weitere neue Spalte wird eine Bedingte Spalte sein, die sie mit diesen Werten ausfüllen bzw. auswählen:

Der Dialog mit den ausgefüllten Werten

Der Dialog mit den ausgefüllten Werten

Wie bereits im ersten Part erwähnt dürfen Sie im Feld Ausgabe nicht den Text Spalte1 eintragen sondern Sie klicken auf das Symbol direkt links des Kombinationsfeldes, wählen dort im Kontextmenü Spalte auswählen, klicken dann in das leere Textfeld und wählen dort im Kontextmenü den Namen der gewünschten Spalte, hier: Spalte1.

Markieren Sie nun die Spalte Name durch einen Klick in die Überschrift. Rechtsklick in die Überschrift und im Kontextmenü wählen Sie Ausfüllen | Nach unten. Nun ist jeder Zeile der korrekte Name zugeordnet.

Markieren Sie nun die Spalten Spalte1 und Index und löschen Sie diese auf beliebige Weise. Der besseren Optik wegen markieren Sie nun nacheinander die Spalten Name und Attribut und verschieben diese gemeinsam an den Anfang; das geht entweder über das Menü Transformieren oder indem sie in eine der markierten Überschriften einen Rechtsklick machen und die beiden Spalten dann An den Anfang verschieben. Und ja, es geht auch mit der Maus …  😉 

Wie auch im 1. Durchgang sind die Zeilen, wo nur der Name enthalten ist flüssiger als flüssig (überflüssig). 😉 Da durch die mathematische Auswertung garantiert wird, dass die richtigen Zeilen mit dem entsprechenden Wert ausgefüllt wurden, können Sie die Spalte Attribut als Argument für den Filter zum löschen überflüssiger Zeilen verwenden. Filtern Sie diese Spalte so, dass die null – Werte entfernt werden.

Markieren Sie die ersten beiden Spalten (Name und Attribut) und anschließend über einen Rechtsklick in eine der beiden Überschriften Andere Spalten entpivotieren. Dann nur noch die Überschrift Attribut.1 auf Datum ändern und sie sind erst einmal am Ziel, nachdem sie Schließen & laden ausgeführt haben.

▲ nach oben …

Möglichkeit 3: Unterschiedliche Zahl der Einträge und mehr …

„Sauber“ ist es gewiss nicht, wenn die Zahl der Einträge unterhalb der einzelnen Namen unterschiedlich ist. Zumindest ist es übersichtlicher und statistisch klarer, wenn einem Namen stets die gleiche Zahl an Argumenten zugeordnet ist. Aber mal sind es vielleicht nur 2 Produkte oder 7 oder 10 … Hier stellt sich die Frage, ob da mit vertretbaren Mitteln eine Auswertung möglich ist. In solchen Fällen fällt dann automatisch der vorher geschilderte Weg der Berechnung aus. Aber Power Query ist in vielen Lebenslagen doch einen sehr fleißiges und dank des Könnens der Person vor dem Bildschirm auch ein intelligentes Helferlein.  💡 

Das Prinzip der gleich beschriebenen Vorgehensweise ist sehr ähnlich dem der im ersten Beispiel, allerdings hier mit mehr Aufwand verbunden. In dieser Datei habe ich nicht nur sinnvollere Namen verwendet sondern auch unterhalb der Namen Einträge entfernt und auch neue hinzugefügt. Nach dem Import der Tabelle werden Sie als erstes ein Duplikat der Abfrage erstellen. Das geht beispielsweise im Menü Start | Verwalten | Duplizieren.

Im folgenden Schritt überprüfen Sie, ob in einer der ersten 3 Datum-Spalten jeder Zeile mindestens ein Feld wirklich leer ist. Dazu nach bekanntem Muster eine Spalte hinzufügen | Benutzerdefinierte Spalte und dort diese Formel eintragen:
[15.02.2018]=null or [18.02.2018]=null or [25.02.2018]=null
und mit OK bestätigen. Das Ergebnis ist TRUE oder FALSE und sollte wiedergeben, ob in Spalte1 ein Name steht oder nicht. Jetzt filtern Sie diese neu erstellte Spalte, damit ausschließlich der Wert FALSE erhalten bleibt.

Löschen Sie nun alle Spalten ausgenommen der Spalte1 und entfernen Sie auf beliebige Weise alle Duplikate. Ich würde mir die Liste aller verbliebenen Unikate nun entweder abschreiben oder vorzugsweise ausdrucken, denn genau diese Bezeichnungen brauche ich (und natürlich auch Sie)  im folgenden Schritt. Danach können Sie auch diese Abfrage löschen, sie wird nicht mehr benötigt. Es schadet aber auch nicht, wenn sie aus Gründen der Sicherheit die Abfrage bestehen lassen.

Wechseln Sie zu Tabelle1 und fügen Sie dort eine Benutzerdefinierte Spalte ein. Die folgende Formel ist recht lang, jedoch vom Aufbau logisch: Es wird abgefragt, ob einer der in Tabelle1 (2) gefilterten Begriffe in Spalte1 dieser Abfrage enthalten ist. Wenn das der Fall ist, dann wird dieses Produkt übernommen sonst kommt null in das Feld hinein:
= if [Spalte1]="Apfel" or [Spalte1]="Birne" or [Spalte1]="Kartoffel" or [Spalte1]="Nudel" or [Spalte1]="Erdbeere" or [Spalte1]="Heidelbeere" or [Spalte1]="Stachelbeere" or [Spalte1]="Erdnuss" or [Spalte1]="Karotte"
then [Spalte1] else null

Falls Sie der Spalte im Formel-Dialog noch keine Namen für die neue Spalte vergeben haben, können Sie das jetzt auch in der Abfrage nachholen; das könnte hier beispielsweise Produkte sein.

Um die Namen in eine getrennte Spalte zu bekommen, wiederum Spalte hinzufügen | Benutzerdefinierte Spalte, Neuer Spaltenname sollte Name sein und als Formel geben Sie ein:
if [Produkte]=null then [Spalte1] else null
Übrigens: wenn Sie jetzt bei Angewendete Schritte auf diesen zuletzt ausgeführten Schritt einen Doppelklick durchführen werden sie nicht den Dialog mit der Formel sehen sondern jenen Dialog der sich auf tun, wenn sie auf Bedingte Spalte klicken.

Der Rest ist prinzipiell wie gehabt: Löschen Sie Spalte1 und verschieben sie die Spalten Produkte und Name dergestalt an den Anfang, dass die Spalte mit den Namen zu Anfang steht (das geht bei diesen wenigen Spalten übrigens auch recht gut per Hand). Das hat zwar keine Auswirkung auf die endgültige Auswertung, macht sich aber in meinen Augen besser so. Nun wieder die Spalte Name nach bekanntem Muster nach unten ausfüllen. Entpivotieren und auch dieses Ziel ist erreicht. Aber bevor sie Schließen & laden anklicken werden sie natürlich den Namen der vorletzten Spalte von Attribut auf Datum ändern.

Und fällt da etwas auf? Ein kalendarisches Datum, welches links ausgerichtet ist bedeutet normalerweise Text und kein Datum. So ist das auch jeder Fall, darum ändern Sie den Datentyp auf Datum. Und das mit dem Datentyp gilt naturgemäß auch für die beiden vorherigen Arbeitsabläufe.  💡 

Fertig. Oder doch nicht? Nein natürlich nicht! Denn der Ausgangspunkt, die eigentliche Aufgabe war ja, dass die Namen alphabetisch aufsteigend sortiert sein sollen. Und egal, welchen der 3 Wege sie beschritten haben, dieser entscheidende Schritt fehlte. Darum sortieren sie in der künftig zu verwendenden Abfrage oder auch in allen anderen Queries die Spalte Name noch einmal aufsteigend. Jetzt haben Sie wirklich fertig.  😎 

▲ nach oben …

Die Wunsch-Darstellung

Auch wenn in Excel ganz klar der Satz „Form follows function“ gilt lässt sich manchmal mit legitimen Bordmitteln und ohne bedeutende Einbußen der Performance das Aussehen der Daten dem Wunschdenken mehr oder sogar praktisch vollkommen erfüllen.

Egal welchen Weg Sie gegangen sind, die Daten sind so aufbereitet, dass in 4 Spalten der Name der Person, des Produkts, das Datum und der entsprechende Wert stehen. Und das ist die optimale Basis für eine PivotTable. Und wenn Sie direkt nach dem Speichern der Abfrage ins Menüband schauen wird Ihnen auffallen, dass es dort den Punkt Mit PivotTable zusammenfassen bereits gibt. Ein Klick darauf und sie übernehmen einfach erst einmal die Vorgaben des Dialogs.

Ziehen Sie nun im rechten Seitenfenster das Feld Name in den Bereich Zeilen, gleichermaßen Produkte. Das Feld Datum ziehen Sie in den Bereich Spalten und das verbleibende Feld Wert kommt in den Bereich Werte. Prinzipiell ist die Auswertung nun fertig. Ich werde mit Ihnen nun noch einige kleine Korrekturen vornehmen, damit das endgültige Bild weitgehend den Vorstellungen des Fragestellers entspricht.

Klicken Sie erst einmal auf den Menüpunkt Entwurf | Gesamtergebnisse | Für Zeilen und Spalten deaktiviert. Damit verschwinden schon einmal die automatisch berechneten Gesamtsummen. Nun Teilergebnisse | Teilergebnisse nicht anzeigen und das sieht schon richtig schick aus. Und das Tüpfelchen auf dem i ist gewiss, wenn sie Berichtslayout | Im Gliederungsformat anzeigen wählen. Je nach Geschmack können Sie nun noch die Zeile 3 Ausblenden und die Breite der Spalte B auf das notwendige Maß verringern:

Das Endgültige Ergebnis als PivotTable

Das Endgültige Ergebnis als PivotTable

Ich bin fest davon überzeugt, dass dieses Ergebnis den Wünschen des Fragestellers sehr, sehr nahe kommt, wenn nicht sogar seine Vorstellung komplett erfüllt.

▲ nach oben …

Dieser Beitrag wurde unter Allgemein, Verschiedenes, Filtern & Sortieren, Foren-Q&A, Power Query, PQ-Formeln (Sprache M), Text-Behandlung abgelegt und mit , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.