PQ: Wer hat heute Geburtstag?

Von un­se­rem Serv­er kön­nen Sie hier eine Lis­te mit 1000 Na­men herun­ter­laden. Jed­er die­ser Per­so­n­en habe ich per ZU­FALLS­BE­REICH()-Funk­tion ei­nen Geburt­stag zuge­ord­net. Da­bei gibt es je­doch 4 Na­men, die eine Beson­der­heit dar­stel­len: Car­olin Lenz, Pe­ter Som­mer, Ina Herb­st und John Win­ter; die­sen Da­men und Her­ren habe ich per For­mel eine Geburt­stag „ver­passt”, der im­mer am ak­tu­el­len, heuti­gen Tag je­doch in ver­schiede­nen Jah­ren liegt. So ist gewährleis­tet, dass auf je­den Fall die­se Per­so­n­en als Tre­f­fer aus­gegeben wer­den. Da ich aber auch den Ge­burts-Jahr­gang per Zu­fall berech­nen las­se, wird das berech­nete Al­ter bei ih­ren Tests ge­wiss von mei­nen bild­li­chen Darstel­lun­gen mehr oder we­ni­ger abwe­ichen.

Um fest­zu­stel­len ob (beispiel­sweise) ein Vere­ins­mit­glied heu­te Geburt­stag hat muss ja vom Geburts­da­tum der Tag und der Mo­nat mit den gle­ichen Para­me­tern des heuti­gen Da­tums ver­glichen wer­den. In Plain Ex­cel gibt es recht vie­le Möglichkeit­en, die­sen Ver­gle­ich durch­zu­füh­ren. Ich habe mir vorgenom­men, mit Pow­er Que­ry eine sin­nvolle und prak­tik­able Lö­sung zu find­en.

Natür­lich gibt es auch in PQ meh­re­re Wege, zum Ziel zu gelan­gen. Prinzip­iell gilt für mei­ne Vorge­hensweise, dass ich den min­i­mal­is­tis­chen Weg so gut wie nie beschre­ite; ich gehe meist den „gold­e­nen Mit­tel­weg”, weil die­ser in den meis­ten Fäl­len bess­er nachvol­lziehbar ist. Und oft ist es auch ein­fach nur eine Geschmacks­frage.

▲ nach oben …

Im­por­tie­ren sie erst ein­mal mei­ne Beispiel­d­atei in den Pow­er Que­ry-Edi­tor. Da mich die An­ga­be der Uhr­zeit in den kalen­darischen Dat­en in die­sem und auch den aller­meis­ten an­de­ren Fäl­len ein­fach nur nervt, än­de­re ich als ers­tes den Daten­typ auf Da­tum. Sie kön­nen das auf dem üb­li­chen Weg über das Men­üband oder per Recht­sklick in die Über­schrift Geburt­stag und dann im Kon­textmenü Daten­typ erledi­gen, ich habe mir angewöh­nt, in der Edi­tierzeile den Daten­typ di­rekt zu än­dern, in­dem ich die let­zten 4 Zei­chen beim Schlüs­sel­be­griff datetime lö­sche und nur date ste­hen bleibt:

Mein Weg, den Daten­typ kom­fort­a­bel anzu­passen

Die­se Vorge­hensweise hat den Vor­teil, dass ich nicht noch ein­mal das än­dern des Daten­typs bestäti­gen muss. Im näch­sten Schritt wer­den sie Tag und Mo­nat aus dem Feld mit der Über­schrift Geburt­stag ex­tra­hie­ren. Ich gehe dazu so vor:

• Wech­sel zum Menü Spal­te hinzufü­gen
• Im Men­üband ein Klick auf Be­nut­zer­de­fi­nier­te Spal­te
• Bei Neu­er Spal­tenname tra­ge ich TT.​MM. Geb­Tag ein.
• Als Be­nut­zer­de­fi­nier­te Spal­tenformel schrei­be ich
  Date.ToText([Geburtstag], "dd.MM.")
  was sich dann im Dia­log so dar­stellt:

Ge­ben Sie die­se For­mel in ex­akt diert Groß- / Klein­schrei­bung ein

Und den­ken Sie dar­an, dass in For­meln die Groß- Klein­schrei­bung be­ach­tet wer­den muss. Pow­er Que­ry hat an­schlie­ßend eine neue Spal­te vom Daten­typ:  Be­lie­big gener­iert, wo­bei ich der Ein­deutigkeit we­gen den Daten­typ auf Text an­pas­se. Und ich räu­me ein, dass ich bei ei­nem Kun­den-Pro­jekt in die­sem Fal­le ein echt­es Da­tum des Geburt­stages des ak­tu­el­len Jah­res für den Ver­gle­ich in die­ser Spal­te ver­wen­den wür­de und nicht nur Tag und Mo­nat. Die­ser Daten­typ wäre dann natür­lich auch Da­tum. Soll­ten Sie zu den Usern ge­hö­ren, die nach Mög­lich­keit kei­ne For­meln bzw. Funk­tio­nen di­rekt ein­ge­ben mö­gen, son­dern lie­ber per Maus­klick zum Ziel gelan­gen, gibt es noch fol­gende Mög­lich­keit:

• • Mar­kie­ren Sie die Spal­te Geburt­stag,
  • Recht­sklick in die Über­schrift und im Kon­textmenü Spal­te aus Beispie­len hinzufü­gen. Alter­na­tiv geht das auch über das Menü Spal­te hinzufü­gen.
  • Tra­gen Sie in die weit rechts ste­hende Spal­te bei Spal­te1 das Da­tum in der gewün­scht­en Form ein, also hier 15.06.

Viel­fach eine be­que­me Mög­lich­keit, Dat­en ohne For­meln umzuwan­deln (ähn­lich Blitz­vor­schau)

Ver­größert­er Auss­chnitt der recht­en Spal­te

• Las­sen Sie sich von den Vorschlä­gen nicht irri­tieren, ein­fach nur mit Re­turn (En­ter) bestäti­gen.
• Da ver­mut­lich hier oder da noch ein­mal auch das Jahr bei den in grau vorgeschla­ge­nen Ergeb­nis­sen an­ge­zeigt wird, tra­gen Sie in die zwei­te Zei­le auch noch ein­mal das Wun­schergeb­nis im richti­gen For­mat, hier 07.06. ein.
• Der Ti­tel ober­halb der recht­en Bei­spiel-Spal­te hat sich auf Be­nut­zer­de­fi­niert geän­dert.
• Eine kur­ze Kon­trolle wird Ih­nen zei­gen, dass nun alle vorgeschla­ge­nen Datum­swerte ih­rem Wun­sch ent­spre­chen und sie bestäti­gen mit OK.
• An­schlie­ßend wäre es hil­fre­ich, die Über­schrift den Gegeben­heit­en anzu­passen.

Im näch­sten Schritt er­stel­le ich wie­der­um eine neue, Be­nut­zer­de­fi­nier­te Spal­te und tra­ge dort nach dem gle­ichen Mus­ter wie vor­her im Formel-Edi­tor eine For­mel zur Berech­nung des heuti­gen Ta­ges ein. Also auch nur Tag und Mo­nat, wo­bei das Daten­for­mat mit dem des Geburt­stages die­ses Jah­res übere­in­stim­men muss.

Nun wer­den Sie sich ge­wiss fra­gen, wie ich automa­tisch das ak­tu­el­le Tages­da­tum in die­se Spal­te ein­füge. Eine fer­tige Funk­tion mit dem Na­men heu­te, to­day oder ähn­lich gibt es nicht in Pow­er Que­ry. Aber natür­lich gibt es in PQ eine Mög­lich­keit, das heu­ti­ge Da­tum als Zell­in­halt zu gener­ieren. Und hier ha­ben Sie zwei, drei Möglichkeit­en, das durch­zu­füh­ren:

1. Sie er­stel­len eine ei­ge­ne, Be­nut­zer­de­fi­nier­te Funk­tion in der Spra­che M, die dann beispiel­sweise fn_Heu­te hei­ßt.
2. Sie schrei­ben in das Excel-Arbeits­blatt die Funk­tion HEU­TE() und ref­eren­zieren auf die­se Zel­le.
3. Sie ge­hen den für Ein­steiger ge­wiss leicht­esten Weg und wäh­len Spal­te hinzufü­gen | Be­nut­zer­de­fi­nier­te Spal­te und tra­gen dort erst ein­mal die­se For­mel ein:
   = DateTime.LocalNow()
4. Ver­ge­ben sie ei­nen sin­nvollen Spal­tenna­men, be­vor sie auf OK Klick­en.
5. Pas­sen Sie nun die Spal­te auf die Gegeben­heit­en der vorheri­gen Spal­te an. Ste­ht dort ein 6‑stel­li­ger Text mit Tag und Da­tum im For­mat Text, dann muss das hier gle­icher­maßen der Fall sein. Ste­ht der links benach­barten Spal­te ein echt­es Da­tum, müsse­nen Sie die Spal­te Heu­te zwin­gend als Daten­typ:  Da­tum einzuricht­en.

Ich selb­st ma­che es mir et­was leich­ter. Ich öff­ne noch ein­mal den Dia­log des let­zten Schritts durch Dop­pelk­lick auf den Ein­trag Hinzuge­fügt Be­nut­zer­de­fi­nier­te Spal­te im recht­en Seit­en­fen­ster und er­gän­ze die dort einge­tra­gene For­mel so:
DateTime.ToText(DateTime.LocalNow(), "dd.MM.")

… und an­schlie­ßend wei­se ich die­ser Spal­te den gle­ichen Daten­typ zu wie in der Spal­te links da­ne­ben. Natür­lich ist die Spal­te links der schon ange­sproch­ene 6‑stel­li­ge Text. Im näch­sten Schritt wer­den Sie wie­der­um eine Be­nut­zer­de­fi­nier­te Spal­te er­stel­len. Auch wenn die Über­schrift so blei­ben kön­nte nen­ne ich sie aus Grün­den der Trans­parenz check oder Ver­gle­ich. Un­ter der An­nah­me, dass die Spal­te mit den Geburt­sta­gen des ak­tu­el­len Jah­res die Über­schrift TT.​MM. Geb­Tag hat gebe ich hier die­se For­mel ein:
[TT.MM. GebTag]=[Heute]

Ver­mut­lich wer­den sie zu An­fang nur den Ein­trag FAL­SE in den Fel­dern se­hen. Das ist auch rich­tig so, denn es wäre rein­er Zu­fall, wenn in den ers­ten Zei­len ein­er der „Kan­di­dat­en” ger­ade heu­te Geburt­stag hät­te. Erweit­ern Sie die Spal­te check und ent­fer­nen Sie das Häk­chen bei FAL­SE, da­mit nur noch jene Ein­träge mit dem Ergeb­nis TRUE sicht­bar sind. Sie wer­den min­destens die vier bere­its oben aufge­führten „Geburt­stagskinder” se­hen, wahrschein­lich sog­ar den ei­nen oder an­de­ren Na­men mehr:

Die ge­fil­ter­ten Dat­en des ak­tu­el­len (heuti­gen) Ta­ges

Nach ein­er vi­su­el­len Kon­trolle wer­den Sie vielle­icht mit Aus­nahme der Spal­ten Name und Vor­name und even­tu­ell Geburt­stag alle an­de­ren Spal­ten lö­schen und über Schlie­ßen & la­den bzw. Schlie­ßen & la­den in… an dezi­diert­er Stel­le spe­ich­ern. Da­mit ist dann der ei­gent­li­che, wichtig­ste Teil der „Auf­gabe” ge­löst.

Hin­weis: Die­ses ist nur ein­er von meh­re­ren mög­li­chen We­gen. Ins­beson­dere wenn Sie ger­ne mit For­meln arbeit­en wer­den Sie im Fun­dus der Spra­che M im Bere­ich der Datum-Funk­tio­nen fün­dig. – Und selb­stre­dend ist es auch mög­lich, mit ein­er klei­nen Anpas­sung den Datums­bere­ich Ih­ren Wün­schen ent­spre­chend anzu­passen.

▲ nach oben …

Das Al­ter be­rech­nen

Als zwei­te Fragestel­lung war ge­ge­ben, das am Tage des Geburt­stages erre­ichte Al­ter durch Pow­er Que­ry berech­nen zu las­sen. Wenn Sie auf der eben erstell­ten Ab­fra­ge auf­bauen acht­en Sie bit­te dar­auf, das die Spal­te Geburt­stag zu Be­ginn unbe­d­ingt einge­blendet sein muss. Von der Idee her muss ja „nur” das Jahr der Ge­burt vom ak­tu­el­len Jahr sub­trahiert wer­den, um das kor­rek­te Ergeb­nis ab dem Tag des Geburt­stags zu bekom­men. Er­stel­len Sie eine Be­nut­zer­de­fi­nier­te Spal­te mit der Über­schrift Al­ter und ver­wen­den Sie die­se For­mel:
= Date.Year(DateTime.LocalNow())-Date.Year([Geburtstag])

Das Ergeb­nis stimmt. Sie kön­nten die­ses Ergeb­nis inner­halb Pow­er Que­ry mit dem Zu­satz ” Jah­re” verse­hen und so­mit als Daten­typ Text ein­richt­en; ich wür­de solch eine For­matierung aber in Ex­cel über das Zahlen­for­mat durch­führen, denn wenn in ein­er Zel­le wirk­lich eine Zahl ste­ht, wel­che ger­ne durch ein be­nut­zer­de­fi­nier­tes Zahlen­for­mat ihr Ausse­hen än­dert, kann die­ser Bere­ich auch sor­tiert wer­den oder auch in eine Berech­nung ein­be­zo­gen wer­den.

▲ nach oben …