Die letzten 10 kalendarischen Daten auswerten

Foren-Geflüster

Kurzform der Anfrage eines Forums: Die jüngsten 10 Einträge einer Liste zählen und mehrspaltig darstellen. Ein Stichwort in Spalte_B soll separiert, die anderen Einträge zusammengefasst werden. Wegen steter Erneuerung der Liste soll das dynamisch geschehen.

In einem Forum kam diese Anfrage (Teil-Zitat):

In Spalte A ist das Datum der vergangenen 30 Tage. In Spalte B stehen die folgenden Kommunikationswege, über welche Anfragen reingekommen sind: E-Mail, Telefon, Chat und Persönlich. Jeder Kontakt, egal über welchen Weg, steht als eine Zeile mit Datum eingetragen.

Mich interessiert nun die Summe der Kontakte „Persönlich“ der vergangenen 10 Tage. Die übrigen Kommunikationskanäle (E-Mail, Telefon und Chat) sollen als „Übrige“ für die vergangenen 10 Tage zusammengezählt werden. Also für das Datum 11/05/2018 in meiner Beispielmappe: Persönlich 4 / Übrige 11.

Die Liste wird immer täglich aktualisiert, deshalb wäre es notwendig, dass die Lösung – 10 Tage jeweils dynamisch berechnet und die Auswertung vornimmt.

Die dazugehörige Datei können Sie hier herunterladen. Bemerkenswert ist, dass die kalendarischen Daten in Spalte A als Text formatiert sind und trotz des Schrägstrichs als Trenner der Europäischen Reihenfolge TT MM JJJJ folgen. Weiterhin ist anzumerken, dass die durch den Fragesteller berechneten Werte für den 11.5.2018 nicht korrekt sind: Die korrekte Summe für Übrige ist nicht 11 sondern 13. Weiterhin ist es eine Bemerkung Wert, dass nicht der Zeitraum von 10 (kalendarischen) Tagen gemeint ist sondern die jüngsten 10 Einträge unterschiedlichen Datums in der Liste ausgewertet werden sollen.

In einem der Antwortbeiträge wird eine reine (wohl auch funktionierende) Formel-Lösung aufgezeigt, ich stelle hier eine Lösungsmöglichkeit mit Power Query ohne eine einzige Formel vor. Im ersten Schritt werden Sie die Daten per StrgL oder StrgT in ein List-Objekt (mit existierenden Überschriften) umwandeln und anschließend in Power Query importieren. Ich gehe zwar wegen der Schreibweise des Datums von der Annahme aus, dass die realen Daten als CSV-Datei vorliegen, das ist in diesem Beitrag jedoch nicht berücksichtigt. Die Musterdatei war schließlich im Format *.xlsx.

Sollten Sie beim Umwandeln in eine Intelligente Tabelle oder beim direkten Import den Vorgaben des PQ gefolgt sein und darum keine „echten“ Überschriften sehen, dann haben Sie nun Gelegenheit, Erste Zeile als Überschrift verwenden anzuklicken. Sie werden sofort erkennen, dass Power Query die kalendarischen Daten der Spalte A im korrekten Datums-Format formatiert hat. Bereits an dieser Stelle ist es hilfreich, per Klick auf den Text Schließen & laden und anschließender Auswahl Schließen & laden in… diese Abfrage als Nur Verbindung erstellen zu sichern. Im Dialog wird sich nach einem Klick auf die Schaltfläche Laden die Abfrage schließen und Sie werden die Abfrage sofort wieder auf beliebige Weise öffnen. Das geht beispielsweise durch einen Rechtsklick auf den Eintrag im rechten Seitenfenster und anschließend Bearbeiten oder einen Doppelklick auf die grün hinterlegte Fläche.

Im folgenden Schritt werden sie nun die Spalte KontaktKanal so aufbereiten, dass der Eintrag Persönlich so bestehen bleibt, alle anderen Werte jedoch zu Übrige umgeändert werden. Gehen Sie dazu so vor:

  • Markieren Sie die Spalte durch einen Klick in die Überschrift KontaktKanal.
  • Menü Spalte hinzufügen | Bedingte Spalte und tragen Sie hier folgende Werte ein:
    • Spaltenname/Wenn: KontaktKanal auswählen
    • Operator: ist gleich (Vorgabe belassen)
    • Wert: Persönlich
    • Ausgabe: Persönlich
    • Andernfalls: Übrige

So ausgefüllt stellt sich der Dialog so dar:

Per Dialog eine WENN-Bedingung einfügen

Per Dialog eine WENN-Bedingung einfügen

… und nach einem OK gibt es eine neue Spalte mit der Überschrift Benutzerdefiniert, wo ausschließlich die beiden gewollten Begriffe drin enthalten sind. Löschen (entfernen) Sie nun Spalte KontaktKanal und geben Sie der eben erstellten Spalte den Namen der eben gelöschten Spalte: KontaktKanal (oder nach Belieben einen anderen Namen).

Obwohl die Spalte KontaktDatum absteigend sortiert zu sein scheint sortieren sie diese  noch einmal explizit Absteigend; das ist wichtig, falls in den Quelldaten am Ende der Liste noch einmal Werte von Hand eingetragen werden. Und siehe da, der erste Eindruck bezüglich der Sortierung war auch nicht richtig; zu Beginn war es doch der 11. Mai, welcher in den ersten Zeilen dargestellt worden ist und Sie erkennen nun, dass der 14.05.2018 das jüngste Datum ist.  💡 

Diese Abfrage werden sie nun duplizieren. Dazu Start | Verwalten | Duplizieren und es wird automatisch eine statische Kopie diese Abfrage erstellt. Im sich eben geöffneten linken Seitenfenster ist der Eintrag Tabelle1 (2) markiert. Rechtsklick darauf, Umbenennen und geben Sie dieser Query den Namen Übrige. Anschließend markieren Sie den Eintrag darüber und vergeben den Namen Persönlich. Bleiben Sie in diese Abfrage und filtern Sie nun die Spalte KontaktKanal so, dass jeweils nur Persönlich erhalten bleibt. In der Query Übrige werden sie natürlich die entsprechenden Werte filtern.

Bleiben Sie erst einmal in der Abfrage Übrige. Achten Sie darauf, dass die Spalte KontaktDatum markiert ist. Nun Start | Gruppieren nach und übernehmen Sie einfach die Vorgaben wie dargestellt:

Die Vorgaben für das Gruppieren einfach so übernehmen

Die Vorgaben für das Gruppieren einfach so übernehmen

… und nach einem OK sieht das schon wesentlich „aufgeräumter“ 😎 aus:

Für jeden Tag fertig berechnete Gruppierung

Für jeden Tag fertig berechnete Gruppierung

Die kalendarischen Daten sind in jeweils einer Zeile zusammengefasst und in der 2. Spalte sind die Anzahl der Tage automatisch berechnet worden. Was Sie hier jetzt noch ändern können bzw. sollten: Die Überschrift Anzahl ändern Sie zu Übrige. Speichern Sie nun auch diese Abfrage über den Punkt Schließen & laden in… als Verbindung.

Wechseln Sie nun die Abfrage Persönlich und gehen Sie hier fast gleichermaßen vor; die einzigen Änderungen zur vorhergehenden Aktion: Im Dialogfenster werden sie bei Neuer Spaltenname statt Anzahl gleich den Wert Persönlich eintragen. Dadurch brauchen Sie die Überschrift im folgenden Schritt nicht noch anzupassen. Und das Speichern & laden in… erübrigt sich natürlich, da dieser Vorgang ja bereits geschehen ist.


Immer noch in der Query Persönlich: Menü Start | Kombinieren | Abfragen zusammenführen  durch Klick auf das Dreieck erweitern und Abfragen als neue Abfrage zusammenführen. Im Dialogfenster wählen Sie unterhalb der Liste im noch leeren Kombinationsfeld Übrige und anschließend bei Join-Art den Eintrag Vollständige äußerer Join (alle Zeilen aus beiden):

So sollen die Felder verknüpft werden

So sollen die beiden Abfragen verknüpft werden

Danach markieren Sie in beiden Listen das Feld KontaktDatum:

Über diese Spalten erfolgt die Verknüpfung

Über diese Spalten erfolgt die Verknüpfung

Nach einem Klick auf OK stellt sich die Abfrage so dar:

In der neu erzeugten Spalte ist ausschließlich der Eintrag "Table"

In der neu erzeugten Spalte ist ausschließlich der Eintrag „Table“

Es gibt eine neue Spalte mit der Überschrift Übrige und dem durchgängigen Inhalt Table. Erweitern Sie diese Spalte durch einen Klick auf den Doppelpfeil Doppelpfeil und entfernen Sie im Dialog die Häkchen bei KontaktDatum und bei Ursprünglichen Spaltennamen … Unter Umständen müssten Sie noch die Überschrift Übrige.1 auf Übrige ändern. Nun noch einmal die Spalte KontaktDatum absteigend sortieren.

Jetzt bleibt nur noch, die neuesten 10 kalendarischen Daten herauszufiltern. Dazu ein Klick auf das Symbol Zeilen verringern | Zeilen beibehalten | Erste Zeilen beibehalten und im Dialog tragen Sie bei Anzahl von Zeilen den Wert 10 ein. Wenn Sie möchten können Sie dieser Abfrage noch einen neuen Namen geben, beispielsweise Letzte 10 Tage. Schließen & laden und es wird automatisch die gewünschte Tabelle mit den aktuellen Daten erstellt. Diese können Sie dann naturgemäß an die gewünschte Position verschieben.

Wie bei Power Query typisch werden veränderte Quelldaten durch einen Klick auf die Schaltfläche Aktualisieren auf den neuesten Stand gebracht. Wenn also ein neuer Tag in die Daten eingefügt wird, werden die letzten 10 kalendarischen Daten natürlich entsprechend angepasst.  😎 

▲ nach oben …

Dieser Beitrag wurde unter Daten zusammenführen, Datum & Zeit, Filtern & Sortieren, Foren-Q&A, Power Query abgelegt und mit , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.