Kurz­form der Anfrage eines Forums: Die jüng­sten 10 Ein­träge ein­er Liste zählen und mehrspaltig darstellen. Ein Stich­wort in Spalte_B soll separi­ert, die anderen Ein­träge zusam­menge­fasst wer­den. Wegen steter Erneuerung der Liste soll das dynamisch geschehen.

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

In Spalte A ist das Datum der ver­gan­genen 30 Tage. In Spalte B ste­hen die fol­gen­den Kom­mu­nika­tion­swege, über welche Anfra­gen reingekom­men sind: E‑Mail, Tele­fon, Chat und Per­sön­lich. Jed­er Kon­takt, egal über welchen Weg, ste­ht als eine Zeile mit Datum einge­tra­gen.

Mich inter­essiert nun die Summe der Kon­tak­te „Per­sön­lich” der ver­gan­genen 10 Tage. Die übri­gen Kom­mu­nika­tion­skanäle (E‑Mail, Tele­fon und Chat) sollen als „Übrige” für die ver­gan­genen 10 Tage zusam­mengezählt wer­den. Also für das Datum 11/05/2018 in mein­er Beispielmappe: Per­sön­lich 4 / Übrige 11.

Die Liste wird immer täglich aktu­al­isiert, deshalb wäre es notwendig, dass die Lösung – 10 Tage jew­eils dynamisch berech­net und die Auswer­tung vorn­immt.

Die dazuge­hörige Datei kön­nen Sie hier herun­ter­laden. Bemerkenswert ist, dass die kalen­darischen Dat­en in Spalte A als Text for­matiert sind und trotz des Schrägstrichs als Tren­ner der Europäis­chen Rei­hen­folge TT MM JJJJ fol­gen. Weit­er­hin ist anzumerken, dass die durch den Fragesteller berech­neten Werte für den 11.5.2018 nicht kor­rekt sind: Die kor­rek­te Summe für Übrige ist nicht 11 son­dern 13. Weit­er­hin ist es eine Bemerkung Wert, dass nicht der Zeitraum von 10 (kalen­darischen) Tagen gemeint ist son­dern die jüng­sten 10 Ein­träge unter­schiedlichen Datums in der Liste aus­gew­ertet wer­den sollen.

In einem der Antwort­beiträge wird eine reine (wohl auch funk­tion­ierende) Formel-Lösung aufgezeigt, ich stelle hier eine Lösungsmöglichkeit mit Pow­er Query ohne eine einzige Formel vor. Im ersten Schritt wer­den Sie die Dat­en per StrgL oder StrgT in ein List-Objekt (mit existieren­den Über­schriften) umwan­deln und anschließend in Pow­er Query importieren. Ich gehe zwar wegen der Schreib­weise des Datums von der Annahme aus, dass die realen Dat­en als csv-Datei vor­liegen, das ist in diesem Beitrag jedoch nicht berück­sichtigt. Die Mus­ter­datei war schließlich im For­mat *.xlsx.

Soll­ten Sie beim Umwan­deln in eine Intel­li­gente Tabelle oder beim direk­ten Import den Vor­gaben des PQ gefol­gt sein und darum keine „echt­en” Über­schriften sehen, dann haben Sie nun Gele­gen­heit, Erste Zeile als Über­schrift ver­wen­den anzuk­lick­en. Sie wer­den sofort erken­nen, dass Pow­er Query die kalen­darischen Dat­en der Spalte A im kor­rek­ten Datums-For­mat for­matiert hat. Bere­its an dieser Stelle ist es hil­fre­ich, per Klick auf den Text Schließen & laden und anschließen­der Auswahl Schließen & laden in… diese Abfrage als Nur Verbindung erstellen zu sich­ern. Im Dia­log wird sich nach einem Klick auf die Schalt­fläche Laden die Abfrage schließen und Sie wer­den die Abfrage sofort wieder auf beliebige Weise öff­nen. Das geht beispiel­sweise durch einen Recht­sklick auf den Ein­trag im recht­en Seit­en­fen­ster und anschließend Bear­beit­en oder einen Dop­pelk­lick auf die grün hin­ter­legte Fläche.

Im fol­gen­den Schritt wer­den sie nun die Spalte Kon­tak­tKanal so auf­bere­it­en, dass der Ein­trag Per­sön­lich so beste­hen bleibt, alle anderen Werte jedoch zu Übrige umgeän­dert wer­den. Gehen Sie dazu so vor:

• Markieren Sie die Spalte durch einen Klick in die Über­schrift Kon­tak­tKanal.
• Menü Spalte hinzufü­gen | Bed­ingte Spalte und tra­gen Sie hier fol­gende Werte ein:
  • Spal­tenname/Wenn: Kon­tak­tKanal auswählen
  • Oper­a­tor: ist gle­ich (Vor­gabe belassen)
  • Wert: Per­sön­lich
  • Aus­gabe: Per­sön­lich
  • Andern­falls: Übrige

So aus­ge­füllt stellt sich der Dia­log so dar:

Per Dia­log eine WENN-Bedin­gung ein­fü­gen

… und nach einem OK gibt es eine neue Spalte mit der Über­schrift Benutzerdefiniert, wo auss­chließlich die bei­den gewoll­ten Begriffe drin enthal­ten sind. Löschen (ent­fer­nen) Sie nun Spalte Kon­tak­tKanal und geben Sie der eben erstell­ten Spalte den Namen der eben gelöscht­en Spalte: Kon­tak­tKanal (oder nach Belieben einen anderen Namen).

Obwohl die Spalte Kon­tak­t­Da­tum absteigend sortiert zu sein scheint sortieren sie diese  noch ein­mal expliz­it Absteigend; das ist wichtig, falls in den Quell­dat­en am Ende der Liste noch ein­mal Werte von Hand einge­tra­gen wer­den. Und siehe da, der erste Ein­druck bezüglich der Sortierung war auch nicht richtig; zu Beginn war es doch der 11. Mai, welch­er in den ersten Zeilen dargestellt wor­den ist und Sie erken­nen nun, dass der 14.05.2018 das jüng­ste Datum ist.  💡 

Diese Abfrage wer­den sie nun duplizieren. Dazu Start | Ver­wal­ten | Duplizieren und es wird automa­tisch eine sta­tis­che Kopie diese Abfrage erstellt. Im sich eben geöffneten linken Seit­en­fen­ster ist der Ein­trag Tabelle1 (2) markiert. Recht­sklick darauf, Umbe­nen­nen und geben Sie dieser Query den Namen Übrige. Anschließend markieren Sie den Ein­trag darüber und vergeben den Namen Per­sön­lich. Bleiben Sie in diese Abfrage und fil­tern Sie nun die Spalte Kon­tak­tKanal so, dass jew­eils nur Per­sön­lich erhal­ten bleibt. In der Query Übrige wer­den sie natür­lich die entsprechen­den Werte fil­tern.

Bleiben Sie erst ein­mal in der Abfrage Übrige. Acht­en Sie darauf, dass die Spalte Kon­tak­t­Da­tum markiert ist. Nun Start | Grup­pieren nach und übernehmen Sie ein­fach die Vor­gaben wie dargestellt:

Die Vor­gaben für das Grup­pieren ein­fach so übernehmen

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

Für jeden Tag fer­tig berech­nete Grup­pierung

Die kalen­darischen Dat­en sind in jew­eils ein­er Zeile zusam­menge­fasst und in der 2. Spalte sind die Anzahl der Tage automa­tisch berech­net wor­den. Was Sie hier jet­zt noch ändern kön­nen bzw. soll­ten: Die Über­schrift Anzahl ändern Sie zu Übrige. Spe­ich­ern Sie nun auch diese Abfrage über den Punkt Schließen & laden in… als Verbindung.

Wech­seln Sie nun die Abfrage Per­sön­lich und gehen Sie hier fast gle­icher­maßen vor; die einzi­gen Änderun­gen zur vorherge­hen­den Aktion: Im Dialogfen­ster wer­den sie bei Neuer Spal­tenname statt Anzahl gle­ich den Wert Per­sön­lich ein­tra­gen. Dadurch brauchen Sie die Über­schrift im fol­gen­den Schritt nicht noch anzu­passen. Und das Spe­ich­ern & laden in… erübrigt sich natür­lich, da dieser Vor­gang ja bere­its geschehen ist.

Immer noch in der Query Per­sön­lich: Menü Start | Kom­binieren | Abfra­gen zusam­men­führen  durch Klick auf das Dreieck erweit­ern und Abfra­gen als neue Abfrage zusam­men­führen. Im Dialogfen­ster wählen Sie unter­halb der Liste im noch leeren Kom­bi­na­tions­feld Übrige und anschließend bei Join-Art den Ein­trag Voll­ständi­ge äußer­er Join (alle Zeilen aus bei­den):

So sollen die bei­den Abfra­gen verknüpft wer­den

Danach markieren Sie in bei­den Lis­ten das Feld Kon­tak­t­Da­tum:

Über diese Spal­ten erfol­gt die Verknüp­fung

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

In der neu erzeugten Spalte ist auss­chließlich der Ein­trag „Table”

Es gibt eine neue Spalte mit der Über­schrift Übrige und dem durchgängi­gen Inhalt Table. Erweit­ern Sie diese Spalte durch einen Klick auf den Dop­pelpfeil und ent­fer­nen Sie im Dia­log die Häkchen bei Kon­tak­t­Da­tum und bei Ursprünglichen Spal­tenna­men … Unter Umstän­den müssten Sie noch die Über­schrift Übrige.1 auf Übrige ändern. Nun noch ein­mal die Spalte Kon­tak­t­Da­tum absteigend sortieren.

Jet­zt bleibt nur noch, die neuesten 10 kalen­darischen Dat­en her­auszu­fil­tern. Dazu ein Klick auf das Sym­bol Zeilen ver­ringern | Zeilen beibehal­ten | Erste Zeilen beibehal­ten und im Dia­log tra­gen Sie bei Anzahl von Zeilen den Wert 10 ein. Wenn Sie möcht­en kön­nen Sie dieser Abfrage noch einen neuen Namen geben, beispiel­sweise Let­zte 10 Tage. Schließen & laden und es wird automa­tisch die gewün­schte Tabelle mit den aktuellen Dat­en erstellt. Diese kön­nen Sie dann naturgemäß an die gewün­schte Posi­tion ver­schieben.

Wie bei Pow­er Query typ­isch wer­den verän­derte Quell­dat­en durch einen Klick auf die Schalt­fläche Aktu­al­isieren auf den neuesten Stand gebracht. Wenn also ein neuer Tag in die Dat­en einge­fügt wird, wer­den die let­zten 10 kalen­darischen Dat­en natür­lich entsprechend angepasst.  😎 

▲ nach oben …