Welches Datum ist das Naheste zu einem Referenz-Datum?

In einem Forum wurde die Auf­gabe dargelegt, dass zu einem beste­hen­den Ref­erenz-Datum (Spalte A) das näch­st­gele­gene frühere Datum gefun­den wer­den sollte. Im End­ef­fekt soll die Zeitspanne zwis­chen der let­zten Aktiv­ität vor einem Abschuß und der Ver­trags-Unter­schrift selb­st berech­net und auf der Basis dieser Dif­ferenz die Tage der let­zten Aktiv­ität durch eine far­bliche Hin­ter­legung gekennze­ich­net wer­den. Ich zitiere hier ein­fach ein­mal einen Teil des Eröffnugs-Beitrages:

… ich habe eine Her­aus­forderung und hoffe, hier Hil­fe zu find­en. Ich habe einen sehr großen Roh­daten­satz mit sehr vie­len Dat­en. Es gibt pro Zeile jew­eils ein Datum ein­er Zusage auf eine Ein­ladung und es soll raus­ge­fun­den wer­den, welche Maß­nahme dafür ver­ant­wortlich war. Es gibt sowohl Anruf als auch Briefe, wobei auch bei­des noch nach der Zusage stat­tfind­en kann. Zum Einen soll somit das let­zte Datum vor Zusage far­big markiert wer­den und zum anderen in ein­er neuen Spalte berech­net wer­den, wie viel Zeit zwis­chen der let­zten Maß­nahme vor Zusage und Zusage ver­strichen sind. Ist das irgend­wie möglich?

Und ja, es gibt mehrere Möglichkeit­en. Ich habe hier bewusst den Weg mit Pow­er Query beschrit­ten. Ins­beson­dere wegen des Hin­weis­es, dass es sich um einen sehr großen Roh­daten­satz han­delt. Und es hat­te mich auch gereizt, weil bis zu meinem ersten Beitrag in diesem Thread nie­mand auch nur in die Rich­tung PQ „geblinzelt” hat. 😉

Bleibt an dieser Stelle noch der Ver­weis auf die Mus­ter­datei mit der Wun­schlö­sung, die Sie hier (als Kopie des Orig­i­nals) herun­ter­laden kön­nen. Und falls Sie jet­zt am über­legen sind, wie ich die Zellen mit den Tre­f­fern so „schön” markieren werde: Nein, das geht nicht mit Pow­er Query, das passiert in der Endrunde per Benutzerdefinierte For­matierung. Oder alter­na­tiv eine ganz andere Lösung, die ich Ihnen in diesem Beitrag auch aufzeige.

Betra­cht­en Sie zu Beginn erst ein­mal die Muster-Lösung, welche vom Fragesteller teil­weise von Hand erstellt wor­den ist:

Die Muster-Dat­en, teils von Hand berech­net und far­blich her­vorge­hoben

Sie wis­sen vielle­icht, dass eine ordentliche Tabelle nur 1 Über­schrift-Zeile hat, die naturgemäß über mehrere Spal­ten gehen kann. Darum wer­den Sie für den Import auss­chließlich den Bere­ich mit den Dat­en von A2:H8 markieren und beispiel­sweise per StrgT oder StrgL als „Intel­li­gente” Tabelle for­matieren. Sor­gen Sie anschließend dafür, dass eine beliebige Zelle inner­halb der Tabelle (oder auch die kom­plette Tabelle) markiert ist. Gehen Sie über Dat­en | Aus Tabelle/Bereich und die Dat­en wer­den umge­hend in den Pow­er Query-Edi­tor importiert.¿  Danach wird Ihnen gewiss auf­fall­en, dass alle kalen­darischen Dat­en automa­tisch mit der Uhrzeit 00:00:00 verse­hen wor­den sind.

Nicht nur aus Grün­den der Optik son­dern auch um einen sin­nvollen Daten­typ zu haben, soll­ten alle Datum-Spal­ten zu einem (Nur-) Datum umge­wan­delt wer­den. Dazu Klick­en Sie in die Über­schrift der ersten Spalte Datum Zusage, Shift und ein Klick in die let­zte Über­schrift Datum 2. Brief. Nun sind alle Spal­ten der Abfrage-Tabelle markiert. Strg und ein Klick in die Über­schrift der 2. Spalte (Zeit zwis­chen…) und diese Spalte wird von der Markierung ausgenom­men. Nun im Menü Start | Daten­typ: Datum/Uhrzeit ▼ erweit­ern und im Kon­textmenü ein Klick auf Datum. Bestäti­gen Sie die Sicher­heitsabfrage und in den entsprechen­den Spal­ten ste­ht das reine Datum mit dem entsprechen­den Daten­typ.

Nun soll Pow­er Query irgend­wie fest­stellen, welch­es das let­zte Datum in der jew­eili­gen Zeile ist, welch­es noch vor dem Datum in Datum Zusage oder auch am gle­ichen Tag ist. Alle anderen kalen­darischen Werte der jew­eili­gen Zeile sind nicht rel­e­vant. Um diesen Stich­tag her­aus zu bekom­men gehen Sie einen kleinen Umweg. 😎 

Nor­maler­weise wird sich der Edi­tor so darstellen, dass links der Dat­en-Tabelle das Wort Abfra­gen senkrecht dargestellt ist. Klick­en Sie auf dieses Wort und der schmale Bere­ich wird sich zu einem kom­plet­ten Seit­en­fen­ster erweit­ern.¿ Und das stellt sich im Anschluss so dar:

Das linke Seit­en­fen­ster zeigt alle existieren­den Abfra­gen dies Files auf

Prinzip­iell finde ich den Tabellen- bzw. Abfra­ge­na­men Tabelle1 (oder ähn­lich) ein­fach zu nichtssagend. Darum klicke ich entwed­er im linken oder recht­en Seit­en­fen­ster auf den Namen der Abfrage, F2 oder Recht­sklick und Umbe­nen­nen und vergebe dann einen sin­nvollen Namen; ich ziehe die inter­na­tionale Beze­ich­nung Raw­Da­ta (Roh­dat­en) vor, Sie kön­nen natür­lich einen beliebi­gen Namen wählen. 😎 Bei der Gele­gen­heit: Ich vergebe meist bere­its bei der Erstellung/Formatierung der Intel­li­gen­ten Tabelle in Excel einen sin­nvollen Namen für diesen Bere­ich; dieser Name wird dann automa­tisch als Abfrage-Name in PQ über­nom­men.

▲ nach oben …

So weit die Vor­bere­itun­gen. Und es ist an der Zeit, sich ein­mal mit den Werten in der 2. Spalte auseinan­der zu set­zen. Prinzip­iell sind das ja jene Ergeb­nisse, die zur Ermit­tlung des am nah­est liegen­den Datum zum Tag der Zusage liegen. Und wie wur­den in diesem Fall die Werte in Excel berech­net? Gute Frage, ein­fach kurz ein­mal nach­se­hen… Also ein Klick in das Fen­ster der ursprünglichen Excel-Tabelle und … es tut sich nichts. 🙄

Des Rät­sels Lösung: Die Pow­er Query-Abfrage muss zuerst geschlossen wer­den. Ich ziehe es vor, sie „unsicht­bar” zu schließen, also die bish­eri­gen Ergeb­nisse nicht in eine Excel-Tabelle zu schreiben son­dern im Spe­ich­er zu behal­ten. Dazu gehe ich diesen Weg: Klick in den Text Schließen & laden (oder Menüpunkt Datei), dann Schließen & laden in… und Nur Verbindung erstellen.

Die Abfrage wird geschlossen und Sie haben vollen Zugriff auf das Arbeits­blatt. Spätestens jet­zt erken­nen Sie, dass die Werte in Spalte B von Hand einge­tra­gen wor­den sind. Und in B1 ist ja auch ver­merkt, dass genau dieser hier von Hand einge­tra­gene Wert per Formel berech­net wer­den soll. Na gut, Pow­er Query kann das auch sehr gut und auch das ist ja schließlich das Ziel dieser Übung. 😉 Aber erst ein­mal lassen Sie nach dem wieder öff­nen der Pow­er Query-Abfrage die Spalte mit den händisch berech­neten Werten zu Ver­gle­ich­szweck­en beste­hen. Ich mache nun die Spalte sehr schmal, um in der nor­malen Ansicht mehr von den restlichen Dat­en zu sehen. Dass dann von der Über­schrift nur noch Frag­mente zu sehen sind, ist ohne Bedeu­tung.

Markieren Sie nun durch einen Klick die Über­schrift Datum Zusage, Shift oder Strg und danach die zweite Spalte. Einen Recht­sklick in eine der bei­den Über­schriften und wählen Sie im Kon­textmenu die Zeile Andere Spal­ten ent­piv­otieren:

Nach dem Ent­piv­otieren wer­den die Dat­en deut­lich anders dargestellt

Die Abfrage beste­ht nun nur noch aus 4 Spal­ten, ist aber wegen erhe­blich mehr Zeilen deut­lich länger gewor­den. Die Inhalte sind nur anders verteilt. Um zu gewährleis­ten, dass später in jedem Falle genau diese Rei­hen­folge einge­hal­ten wird, Klick­en Sie nun im Menü auf  Spalte hinzufü­gen und wählen im Menüband Indexs­palte. Automa­tisch wird eine neue Spalte erzeugt und ein mit 0 (null) begin­nen­der Index gener­iert. Bleiben Sie in diesem Menüpunkt und Klick­en Sie auf das Sym­bol Benutzerdefinierte Spalte. Es öffnet sich ein Dialogfen­ster wo Sie in das Feld Neuer Spal­tenname beispiel­sweise den Wert Check ein­tra­gen. In das Feld Benutzerdefinierte Spal­tenformel kommt nun eine Formel mit welche sie bes­tim­men, ob der Wert in der Spalte Wert klein­er oder eben nicht klein­er dem in der Spalte Datum Zusage ist. Geben Sie dazu diese Formel nach dem Gle­ich­heit­sze­ichen ein:

= [Wert] <= [Datum Zusage]¿

… und als Ergeb­nis wird je nach Auswer­tung des Ver­gle­ichs entwed­er True oder False aus­gegeben. True bedeutet, dass das Datum in der Spalte Wert anschließend aus­gew­ertet wer­den soll. Erweit­ern Sie die Über­schrift Check und ent­fer­nen Sie im Dia­log das Häkchen bei FALSE, um nach TRUE zu fil­tern. Die Abfrage verklein­ert sich dadurch von 36 auf 31 Zeilen.

Löschen Sie die Spalte Check beispiel­sweise über das Kon­textmenü oder nach Markierung der Über­schrift durch Klick auf die Taste Entf, denn sie hat ihre Schuldigkeit getan. 💡 

Markieren Sie die Spalte Datum Zusage. Auch wenn diese bere­its auf­steigend sortiert ist: Sortieren Sie noch ein­mal expliz­it Auf­steigend, damit diese Sortierung Vor­rang hat. Nun ein Klick in Wert, wo ja das Datum der jew­eili­gen Aktion ver­merkt ist. Erweit­ern Sie die Über­schrift und wählen Absteigend sortieren.

Machen Sie gerne an dieser Stelle eine gedankliche Pause. „Sortieren” Sie für sich sel­ber, was bis zu diesem Punkt geschaf­fen, erre­icht wor­den ist. Machen Sie sich klar, dass immer noch 31 statt 7 Zeilen in der Abfrage enthal­ten sind.

Markieren Sie (der besseren Erkennbarkeit wegen) nun zuerst die Spalte Datum Zusage und dann per Strg die Spalte Wert. Ver­gle­ichen Sie nun in der jew­eils ersten Zeile jedes Datums der ersten Spalte mit den dazu gehören­den Werten der Spalte Wert. Sie wer­den erken­nen, dass in Zeile 1, Zeile 6, Zeile 10, … jew­eils das jüng­ste, das am näch­sten liegende Datum in Rela­tion zu Datum Zusage stim­mig ist. Was liegt also näher, als in der ersten Spalte Dup­likate ent­fer­nen auszulösen?

Ken­nen Sie den Satz: „Unver­hofft kommt oft”? Das trifft gewiss auch hier zu. Die Ergeb­nisse sind gewiss irri­tierend:

… das entspricht gewiss nicht Ihren Erwartun­gen

Wieso das? Wenn Sie schon etwas PQ-Erfahrung mit dem löschen von Dup­likat­en haben wer­den Sie davon aus­ge­hen, dass in der markierten Spalte stets die erste Zeile erhal­ten bleibt und die darauf fol­gen­den Dup­likate (in den darunter liegen­den Zeilen) gelöscht wer­den. Aber das passt hier ja nun ganz und gar nicht. Woran liegt die uner­wün­schte, prinzip­iell falsche Löschung?

Die Spalte Index sollte Ihnen einen deut­lichen Hin­weis geben. Offen­sichtlich hat Pow­er Query erst nach dem Index sortiert und dann die Dup­likate ent­fer­nt. 😕 Und ich ver­sichere Ihnen: Wenn Sie diesen Schritt im recht­en Seit­en­fen­ster bei Angewen­dete Schritte ent­fer­nen, wer­den auch die richti­gen, gewün­scht­en Dup­likate ent­fer­nt. Eine Möglichkeit… aber zugegeben, das kann es doch nicht sein! Schließlich wurde die Spalte Index einge­fügt, um später wieder exakt die gewün­schte Sortierung der Zeilen zu erhal­ten.

Falls Sie den Schritt mit der Index-Spalte aus Erprobungs­grün­den nachvol­l­zo­gen haben, fügen Sie an der entsprechen­den Stelle wieder eine Index-Spalte ein. – Und natür­lich gibt es einen Ausweg aus diesem Dilem­ma. Klick­en Sie im recht­en Seit­en­fen­ster auf den vor­let­zten Ein­trag Sortierte Zeilen, im Haupt­menü Spalte hinzufü­gen | Indexs­palte, bestäti­gen Sie im Dia­log, dass eine Zeile einge­fügt wer­den soll und es wird eine weit­ere Spalte Index.1 erstellt. Wenn Sie im recht­en Seit­en­fen­ster nun auf die let­zte Zeile Ent­fer­nte Dup­likate Klick­en wer­den sie gewiss voller Freude fest­stellen, dass jet­zt die kalen­darischen Dat­en in der Spalte Wert kor­rekt sind. 💡

Die zweite Index-Spalte hat ihren Dienst getan, darum soll­ten Sie diese auch löschen. Und da sie nicht genau wis­sen, ob die Dat­en bei den fol­gen­den Berech­nun­gen noch ein­mal umsortiert wer­den, lassen Sie die Spalte Index erst ein­mal noch ste­hen.

Typ­is­cher­weise sind Sie immer noch im Menü Spalte hinzufü­gen. Sie erstellen eine Benutzerdefinierte Spalte und vergeben als Über­schrift (Neuer Spal­tenname) beispiel­sweise Dif­ferenz-Tage. In Excel wür­den sie nun ganz ein­fach die Spalte Wert von der Spalte Datum Zusage sub­trahieren und Pow­er Query ist da auch ganz „lock­er”; da bietet sich diese Formel an:

= [Datum Zusage]-[Wert]

Pro­bieren Sie es gerne ein­mal aus und das Ergeb­nis wird sie höchst­wahrschein­lich deut­lich über­raschen­den:

Irgend­wie sieht das anders aus als erwartet… 😯

Irgend­wie stimmt das Ergeb­nis ja ganz offen­sichtlich, aber die Darstel­lung ist alles andere als gewohnt und wahrschein­lich abwe­ichend von ein­er von Ihnen prog­nos­tizierten Lösung. Sie wer­den ‑genau wie ich- gewiss nur eine Ganz­zahl, einen numerischen Wert erwartet haben. Was sie aber hier sehen ist die Darstel­lung, welche prinzip­iell dem Daten­typ Dauer (Dura­tion) entspricht. Und ja, der eigentliche Daten­typ ist Beliebig, warum auch immer…

Die ein­fach­ste Möglichkeit wäre, es bei dieser Darstel­lung zu belassen. Sie ver­schieben diese Spalte mit der Maus an die zweite Posi­tion und ver­gle­ichen, ob die Zahl vor dem Punkt mit dem in der (nun) drit­ten Spalte übere­in­stimmt. Das trifft zu, also löschen Sie die Spalte Zeit zwis­chen … Anschließend soll­ten Sie ein­fach ein­mal testen, wie das Ergeb­nis denn in Plain Excel aussieht. Es kön­nte ja sein, dass sich das schon ganz pass­abel darstellt. Falls Sie zu Beginn die Abfrage nicht als Nur Verbindung gespe­ichert hat­ten gehen Sie beispiel­sweise über Datei und dort Schließen & laden in… Die Auswahl Tabelle ist okay, markieren Sie Beste­hen­des Arbeits­blatt und posi­tion­ieren Sie den Cur­sor im Blatt Tabelle1 auf die Zelle A12. OK und das sieht dann so aus:

Eigentlich kön­nte das so bleiben, oder?

Wenn Sie zu Beginn dieser Übung die Abfrage bere­its ein­mal als Nur Verbindung gespe­ichert hat­ten, dann wer­den Sie gewiss nicht sehen, dass eine Tabelle gespe­ichert wer­den kann. Schauen Sie ein­mal hier nach, wie Sie dann vorge­hen kön­nen, um den hierüber beschriebe­nen Weg doch zu beschre­it­en.

Nach meinem Geschmack ist das Ergeb­nis deut­lich trans­par­enter als das Wun­schergeb­nis des Fragestellers im Forum. Und da die Rei­hen­folge in der Spalte Index zwar stimmt aber nicht in das Ergeb­nis gehört, würde ich die Abfrage noch ein­mal öff­nen und diese Spalte löschen. Danach noch ein­mal ein Klick auf Schließen & laden und mein per­sön­lich­es Ziel wäre erre­icht. Aber die Vor­gabe war eine andere, darum ist noch etwas mehr Arbeit ange­sagt. Das einzige „Manko”: Die Über­schrift Wert sollte in Pow­er Query noch auf beispiel­sweise Stich­tag angepasst wer­den.

▲ nach oben …

„Hätte ich doch früher an das zu erre­ichende Endergeb­nis und dessen Wun­schdarstel­lung gedacht!”, diesen Satz höre ich doch öfter und ich räume ein, dass auch ich mich in eige­nen Pro­jek­ten davon nicht freis­prechen kann. Und Sie wer­den sich denken kön­nen, dass diese Aus­sage auch hier zutrifft. Aber dank Pow­er Query ist das alles nicht ganz so schlimm, wie es vielle­icht ohne dieses Tool wäre. 😉 

Der gravierende Unter­schied zu mein­er eben vorgestell­ten Lösung ist, dass in der Wun­schlö­sung prak­tisch der Orig­i­nalzu­s­tand der Tabelle dargestellt wird (also alle Spal­ten gezeigt wer­den) und die jew­eili­gen ger­ing­sten Zeit­d­if­feren­zen far­blich markiert wer­den sollen. Wie bere­its erwäh­nt hat die far­bliche Her­vorhe­bung aber nichts mit PQ zu tun. Den­noch gilt: Die Gesamtheit der Spal­ten anzuzeigen, das ist sehr wohl mit oder auch trotz Pow­er Query mach­bar.

Um das zu erre­ichen, erstellen Sie erst ein­mal eine Kopie, ein Dup­likat der existieren­den Abfrage.¿ Diese Query bekommt dann automa­tisch den Namen Raw­Da­ta (2). Bleiben Sie in dieser Abfrage und suchen Sie im recht­en Seit­en­fen­ster jenen Schritt, wo zulet­zt alle Spal­ten dargestellt wer­den. Wenn Sie noch nicht so rou­tiniert in Pow­er Query sind bietet es sich an, im recht­en Seit­en­fen­ster von oben nach unten auf die Ein­träge mit den einzel­nen Schrit­ten zu Klick­en und im Haupt­fen­ster zu beobacht­en, wann der Wech­sel auftritt. Vielle­icht wer­den Sie sich aber auch daran erin­nern oder Ihre Erfahrung sagt Ihnen, dass durch das Ent­piv­otieren der Schnitt erfol­gte. Also ein Recht­sklick auf diese Zeile und Bis zum Ende löschen auswählen und bestäti­gen.

Wech­seln Sie erforder­lichen­falls zum Menüpunkt Start. Löschen Sie als näch­sten Schritt die Spalte Zeit zwis­chen let­ztem Kon­takt und Zusage. Im Menüband wählen Sie Kom­binieren | Abfra­gen zusam­men­führen und im Dialogfen­ster wählen Sie dann im einzeili­gen Textfeld zwis­chen den größeren Rah­men die Abfrage Raw­Da­ta. Markeieren Sie nun im oberen als auch unteren „Kas­ten” die Spalte Datum Zusage:

Erste Schritt zum Anfü­gen der neuen Dat­en

Nach einem OK wird in die Abfrage eine neue Spalte mit dem Namen Raw­Da­ta ange­fügt. Der Inhalt ist durchgängig Table. Erweit­ern Sie diese Spalte durch einen Klick auf den Dop­pelpfeil in der Über­schrift und ent­fer­nen Sie (wirk­lich) alle Häkchen, ausgenom­men bei Dif­ferenz-Tage. Ziehen Sie diese Spalte dann an die zweite Posi­tion, also nach Datum Zusage.

Ganz zu Beginn haben Sie ja auch der Optik wegen die Spal­ten mit den kalen­darischen Dat­en als „echt­es” Datum, also ohne Uhrzeit for­matiert. Lässt dich das vielle­icht auch mit der Spalte Dif­ferenz-Tage machen? Aber klar… 😎 Menü (nicht Gruppe) Trans­formieren | Dauer | Tage und bin­go, das Ergeb­nis stimmt. Und das war’s fast auch schon in Sachen Pow­er Query. Nur noch Schließen & laden und die Tabelle wird in ein neues Tabel­len­blatt geschrieben. Hin­weis: Wahrschein­lich hat­ten Sie ja zu Beginn die Abfrage Raw­Da­ta schon ein­mal als Nur Verbindung gespe­ichert. Au dem Grunde wird dann auch diese Query gnaden­los im gle­ichen Modus gesichert. Wie Sie dieses gewollte Ergeb­nis den­noch in ein Tabel­len­blatt brin­gen, lesen Sie beispiel­sweise hier im Blog nach.

▲ nach oben …

Jet­zt ste­ht beispiel­sweise im Blatt Tabelle2 die Tabelle mit den berech­neten Dif­ferenz-Tagen. Im let­zten Schritt sollen jene Zellen markiert wer­den, welche vom Datum aus gese­hen dem let­zten Kon­takt vor der Zusage entsprechen. Wie schon erwäh­nt, das geht nicht mit Pow­er Query son­dern (sin­nvoller­weise) mit Plain Excel (oder für Freaks unter Ihnen auch mit VBA 😛 ).

Die gespe­icherte Tabelle umfasst den Bere­ich von A1:H7.

• Wech­seln Sie erforder­lichen­falls zum Menü Start.
• Markieren Sie den Bere­ich C2:H7.
• Im Menüband Bed­ingte For­matierung | Neue Regel… | Formel zur Ermit­tlung der zu for­matieren­den Zellen ver­wen­den auswählen.
• Tra­gen Sie im Dia­log diese Formel (mit dem führen­den Gle­ich­heit­sze­ichen) in das Textfeld Werte for­matieren, für die diese Formel wahr ist:
  =C2+$B2=$A2
• Klick aus die Schalt­fläche For­matieren… und wählen Sie im Reg­is­ter Aus­füllen beispiel­sweise das präg­nante Gelb aus.
• OK und freuen. 😉 

Das war’s nun wirk­lich. Die „Auf­gabe” ist mit Pow­er Query gelöst wor­den. Und ja ich gebe zu, dass dieses oder jenes deut­lich „ele­gan­ter” hätte gelöst wer­den kön­nen. Ein­er­seits finde ich, dass Ele­ganz auf den Lauf­steg gehört und ander­er­seits ist es mir wichtig, Ihnen möglichst viel an Basisken­nt­nis­sen zu ver­mit­teln. Und manch­mal auch die kleinen oder auch größeren Fall­stricke aufzuzeigen, denn als Auto­di­dakt weiß ich dur­chaus, was logisch erscheint aber im Sinne des Pow­er Query keineswegs ist. Und last but not least wer­den Sie sich in Erin­nerung rufen, dass Sie die Index-Spalte gar nicht für die Sortierung gebraucht haben… Aber Solch eine Weisheit erlange auch ich meist erst im Rück­blick. Und mir ist dieser eine, zusät­zliche und im Nach­hinein als unnötig erkan­nte Schritt lieber als am Schluß festzustellen, dass die Rei­hen­folge der Dat­en anders ist als im Ursprung.

▲ nach oben …