Die Auf­ga­be

Eine Auflis­tung (Lis­te) von Rech­nun­gen soll über­prüft wer­den, ob das Fäl­ligkeits­da­tum bere­its über­schrit­ten ist oder nicht. Alle über­fäl­li­gen Rech­nun­gen sol­len in ir­gend ein­er Form deut­lich an­ge­zeigt wer­den.

Hin­wei­se

Für Übungs­zwe­cke und zur bes­se­ren Nachvol­lziehbarkeit ha­ben wir Ih­nen die­se Da­tei zum Down­load bere­it ge­stellt. Nach dem öff­nen des Files se­hen Sie 20 Rech­nun­gen mit unter­schiedlichen Dat­en. Ih­nen wird auf­fall­en, dass das Rech­nungs­da­tum dur­chaus in zeitlich­er Nähe zum ak­tu­el­len Da­tum und kei­nes­wegs äl­ter als 45 Tage ist. Das ha­ben wir u.a. per Zufalls­funk­tion so ge­re­gelt. Ähn­lich ver­hält es sich mit weit­eren Fel­dern. Wenn Sie die­se Hin­ter­gründe nicht so sehr inter­essieren, dann ge­hen Sie ein­fach di­rekt zum The­ma Vor­bere­itun­gen und gle­ich da­nach geht es mit den ge­nau da­mit und den ver­schiede­nen Lösungsmöglichkeit­en weit­er.

Rechn.Da­tum

Bei all die­sen Erk­lärun­gen nut­zen wir die Zei­le 2 zur Ana­ly­se und Darstel­lung der For­meln. Es ver­ste­ht sich, dass die jew­eilige For­mel stets nach un­ten ko­piert wird. Die For­mel für das Rech­nungs­da­tum:
=HEUTE()-ZUFALLSBEREICH(0;45)
sorgt da­für, dass dort ein zufäl­liges Da­tum einge­tra­gen wird, welch­es 0 bis 45 Tage vor dem ak­tu­el­len Tag liegt. Die Funk­tion HEU­TE() gibt im­mer den ak­tu­el­len Tag zu­rück. Mit der Funk­tion ZU­FALLS­BE­REICH() wird eine zufäl­lige Zahl zurück­gegeben, wel­che wel­che durch die bei­den Argu­mente 0 und 45 begren­zt wird. Le­sen Sie hier mehr dazu.

Rechn.Nr.

Eine Rech­nungsnum­mer wird ja viel­fach so er­stellt, dass die ers­ten bei­den Zif­fern das Jahr repräsen­tieren und dann fol­gt eine (beispiel­sweise) vier­stel­lige Num­mer. Die­se hier ver­wen­de­ten Rech­nungsnum­mern kön­nen nicht der Real­ität ent­spre­chen, weil es in Deutsch­land zwin­gend vor­ge­schrie­ben ist, dass die Rech­nun­gen eine fort­laufende Num­mer ha­ben müs­sen und kei­ne undoku­men­tierten Lück­en exis­tie­ren dür­fen. Beacht­en Sie das bit­te bei Ver­wen­dung der ver­schiede­nen Funk­tio­nen.

Da­mit die Num­mern halb­wegs prax­is­nah sind, Sie aber den­noch et­was von den ver­schiede­nen Excel-Funk­tio­nen prof­i­tieren kön­nen, ha­ben wir et­was get­rickst:
=RECHTS(JAHR(HEUTE());2)&TEXT(MONAT(B2);"00")&TEXT(TAG(B2)+ZUFALLSBEREICH(0;68);"00")

Ana­ly­se: RECHTS(JAHR(B2) gibt die bei­den let­zten Zif­fern des Jah­res der Rech­nungser­stel­lung zu­rück. Durch das &TEXT(MONAT(B2);"00") wird der Mo­nat des Rech­nungs­da­tums als zweis­tel­lige Zif­fer­n­folge in ein­er Zeichen­kette (Text) aus­gegeben, also beispiel­sweise "04" für den April. Mit &TEXT(TAG(B2)+ZUFALLSBEREICH(0;68);"00") ver­hält es sich ähn­lich. Zu dem Tag des Rech­nungs­da­tums (auch zweis­tel­lig) wird eine Zahl zwis­chen 0 und 68 dazu ad­diert. War­um ger­ade 68? Nun, da­mit wird gewährleis­tet, dass die let­zten bei­den Zif­fern höch­stens 99 sind, also im­mer zweis­tel­lig: 68 plus 31 (Tage) = 99. 🙂 Und das Zei­chen & ver­ket­tet die einzel­nen Ele­mente der ver­schiede­nen Funk­tio­nen.

Ziel­Ta­ge

Ver­schiedene Kun­den ha­ben ein unter­schiedlich­es Zah­lungs­ziel, also die An­zahl der Tage, bis die Rech­nung fäl­lig ist. Auch hier habe ich das Zufall­sprinzip wal­ten las­sen:
=WAHL(ZUFALLSBEREICH(1;4);5;10;21;30)
Mit der Funk­tion WAHL() wird aus den Argu­menten, wel­che dem ers­ten fol­gen, ein bes­timmtes aus­gewählt und als Funk­tion­sergeb­nis zurück­gegeben. Wel­che Posi­tion ge­wählt wird, bes­timmt das ers­te Argu­ment, das hier eine Zufall­szahl zwis­chen 1 und der An­zahl der fol­gen­den Argu­mente (hier: 4) ist.

Ziel­Da­tum

Das be­darf prinzip­iell kein­er Erk­lärung. Zum Rech­nungs­da­tum wer­den die ZielT­age hin­zu ad­diert. Natür­lich wird der jew­eilige Zufall­swert, welch­er sich nach jed­er Neu­berech­nung der Ta­bel­le än­dert, als Grund­lage der Berech­nung ge­nutzt.

▲ nach oben …

Vor­be­rei­tung

Wir emp­feh­len Ih­nen, die her­un­ter ge­la­de­ne Da­tei jet­zt, be­vor Sie et­was mit der Map­pe anfan­gen, un­ter ei­nem an­de­ren Na­men zu spe­ich­ern. Das hat den Vor­teil, dass Sie auch zu ei­nem spä­te­ren Zeit­punkt die Mög­lich­keit ha­ben, mit dem dann ak­tu­el­len Da­tum zu arbeit­en. Ein weit­er­er Grund: Da­mit Sie sich nicht stets mit neu­en Wer­ten „herum­schla­gen” müs­sen, soll­ten Sie nach dem spe­ich­ern die For­meln in Wer­te umwan­deln. Entwed­er Sie ge­hen ei­nen Ih­nen bekan­nten und ver­traut­en Weg oder Sie ge­hen beispiel­sweise so vor:

• Mar­kie­ren Sie den kom­plet­ten Daten­bere­ich
• Bear­beit­en | Ko­pie­ren, bzw. über Recht­sklick im Kon­textmenü Ko­pie­ren oder die Tas­tenkom­bi­na­tion StrgC
• Ein­fü­gen | Wer­te oder über das Kon­textmenü die Wer­te ein­fü­gen.

Da­mit ist dann gewährleis­tet, dass die­se Zah­len sich nicht mehr verän­dern und Sie kön­nen in Ruhe und ohne Irri­ta­tio­nen an den unter­schiedlichen Lösun­gen arbeit­en.

▲ nach oben …

Lö­sung 1

Über­sicht

Bei die­ser Lö­sung han­delt es sich um eine ein­fache Bed­ingte For­matierung. Automa­tisch wer­den alle Rech­nun­gen, die über­fäl­lig sind, far­blich her­vorge­hoben. Rot­er Hin­ter­grund und der bes­se­ren Les­barkeit we­gen wei­ße Schrift. Das Gan­ze kön­nte dann so ausse­hen:

Hier noch ein­mal der Hin­weis, dass Sie mit Sicher­heit an­de­re Zah­len und Dat­en ha­ben wer­den, weil we­gen der Zufalls­berech­nun­gen im­mer neue Dat­en kre­iert wer­den. Aber das Prin­zip zählt hier mehr als der Ver­gle­ich der Dat­en.

Vor­ge­hens­wei­se

Und auch hier noch­mals der Hin­weis, dass Sie sich we­sent­lich leich­ter tun, wenn Sie mit sta­tis­chen Dat­en arbeit­en, also die ge­sam­ten Dat­en in Wer­te umge­wan­delt ha­ben.

Ge­hen Sie fol­gen­der­maßen vor, um zum Ziel zu kom­men:

• Mar­kie­ren Sie die (rei­nen) Dat­en, also A2:F21
• Acht­en Sie dar­auf, dass A2 die ak­ti­ve Zel­le ist, also (nor­maler­weise) mit ei­nem hel­le­ren Hin­ter­grund als die an­de­ren mar­kier­ten Zel­len.
• Klick­en Sie auf die Schalt­fläche Bed­ingte For­matierung und wäh­len Sie dort den Me­nü­punkt Neue Re­gel…
• Im fol­gen­den Fen­ster ak­ti­vie­ren Sie den Punkt For­mel zur Ermit­tlung der zu for­matieren­den Zei­len ver­wen­den und tra­gen Sie fol­gende For­mel ein: =$2<=HEUTE()

• Klick­en Sie nun auf For­matieren… und wäh­len als Hin­ter­grund­farbe (Aus­füllen) rot und als Schrift­farbe weiß:

• OK und das Gan­ze soll­te nun so ausse­hen:

• Jet­zt noch ein­mal OK, dann wer­den Sie umge­hend den Er­folg se­hen. Durch die For­matierung wer­den alle über­fäl­li­gen Rech­nun­gen sehr deut­lich her­vorge­hoben. Sie­he die ober­ste Abbil­dung.

▲ nach oben …

Lö­sung 2

Über­sicht

Bei die­ser Lö­sung blen­den Sie auf recht ein­fache Art und Wei­se alle noch nicht fäl­li­gen Rech­nun­gen aus. Sie nut­zen eine so genan­nte Dynamis­che Ta­bel­le (früher­er Name: Lis­te), wo aus­giebige Fil­ter- und Sortier­möglichkeit­en ge­ge­ben sind. Das kön­nte dann beispiel­sweise so ausse­hen:

Vor­ge­hens­wei­se

Auch wenn es auf den ers­ten Blick nicht so aus­sieht, Sie wer­den rasch­er und ge­wiss auch ele­gan­ter zum Ziel gelan­gen als bei der ers­ten Lö­sung. Das liegt schon al­lei­ne dar­an, dass die For­matierung vol­lau­toma­tisch vorgenom­men wird.

• Sor­gen Sie da­für, dass sie wie­der­um mit ei­nem unfor­matierten Daten­blatt arbeit­en, ent­spre­chend dem Reg­is­ter Rech­nun­gen.
• Klick­en Sie irgend­wo in die Dat­en, also in den Bere­ich A1:F21
• Drück­en Sie (ab Win­dows 7) StrgT oder in frü­he­ren Ver­sio­nen StrgL

Ex­cel wird nun automa­tisch vorschla­gen, in dem zusam­men­hän­gen­den Bere­ich eine Dynamis­che Ta­bel­le zu er­stel­len:

• Acht­en Sie dar­auf, dass das Käst­chen Ta­bel­le hat Über­schriften auch mit ei­nem Häk­chen verse­hen ist (das ist Stan­dard).
• Klick­en Sie auf OK und ma­chen Sie sich ein we­nig mit den Möglichkeit­en der Über­schriften­zeile ver­traut.
• Ide­al­er­weise las­sen Sie die Spal­tenbre­ite automa­tisch an­pas­sen.
• Klick­en Sie in der Über­schrift­szeile im Feld Ziel­D­a­tum auf das Fil­ter­sym­bol, wäh­len Datums­fil­ter und an­schlie­ßend Vor…:

• Im fol­gen­den sich auftuen­den Fen­ster ge­ben Sie entwed­er das gewün­schte Da­tum per Hand ein oder…

• … Sie Klick­en auf das klei­ne Kalen­der­sym­bol, da­mit sich ein Kalen­der-Steuerele­ment öff­net:

• Hier ist der ak­tu­el­le Tag meist vor­markiert, alter­na­tiv ge­nügt ein Klick auf die Schalt­fläche Heu­te. Oder aber Sie wäh­len durch ei­nen Klick das gewün­schte Da­tum aus.
• OK und das Ergeb­nis ist so­fort sicht­bar.

▲ nach oben …

Lö­sung 3

Über­sicht

Die Bed­ingte For­matierung ha­ben Sie ja schon weit­er oben ken­nen gel­ernt. Alle über­fäl­li­gen Rech­nun­gen wa­ren rot mar­kiert. Die­se Funk­tion­al­ität kön­nen Sie aber noch mit ei­nem Fein­tun­ing verse­hen. Min­destens drei Far­bge­bun­gen sind für (beispiel­sweise) drei Zeit­räu­me mög­lich, ab Ex­cel 2007 erhe­blich mehr.

Vor­ge­hens­wei­se

Angenom­men, Sie wol­len die ers­ten zwei Tage Über­fäl­ligkeit nicht her­vorheben, da­nach ab 3, 10 und 28 Ta­gen eine Farbab­stu­fung von hell nach dun­kel. Dann ge­hen Sie ge­nau so vor wie oben be­schrie­ben; Sie begin­nen mit dem niedrig­sten Wert und arbeit­en sich dann hoch. Nach Fer­tig­stel­lung sieht das bei un­se­rem Mus­ter so aus:

Das Ergeb­nis se­hen Sie im Tabel­len­blatt Lö­sung 3. – Natür­lich kön­nen Sie ei­nen zusät­zlichen Fil­ter anwen­den, um auss­chließlich die entsprechen­den Rech­nun­gen an­zu­zei­gen.

▲ nach oben …

 Lö­sung 4

Über­sicht

Wenn Sie das Tabel­len­blatt Lö­sung 4 öff­nen, dann wer­den Sie ge­wiss zu Be­ginn et­was ver­wirrt sein. Spal­ten G:H enthal­ten ei­nen Wahr­heits­wert, ob die ent­spre­chen­de Rech­nung über­fäl­lig ist oder nicht. Die Spal­ten I:L enthal­ten offen­sichtlich alle den gle­ichen Wert, näm­lich die An­zahl der Tage, die eine Rech­nung über­fäl­lig ist. Teil­weise wird die Null an­ge­zeigt, teil­weise nicht. Und Spal­te M ent­hält entwed­er ein Da­tum der ers­ten Tage des vorheri­gen Jahrhun­derts oder ei­nen „Garten­za­un”.

Hier geht es nur um ver­schiedene Wege zum Ziel, welch­es ja im­mer gle­ich ist. Klei­ne Vari­a­tio­nen wie beim Wahr­heits­wert oder das Unter­drück­en der Null­w­erte sind nur mar­gin­al wer­den aber dur­chaus disku­tiert.

Vor­ge­hens­wei­se

Alle hier aufge­führten For­meln und Funk­tio­nen be­zie­hen sich auss­chließlich auf die Zei­le 2 und sie wer­den ganz nor­mal nach un­ten ko­piert.

Spal­te G,WAHR/FALSCH

Die For­mel ist schlicht und ein­fach: =F2<HEUTE(). Das Ergeb­nis kann nur WAHR oder FALSCH sein, und ge­nau das ist dann auch das Ergeb­nis.

Spal­te H, Ja/Nein

Die­se For­mel unter­schei­det sich kaum von der vorheri­gen. Das ist auch ziem­lich ein­leuch­t­end, denn es soll ja auch nur ein Wahr­heits­wert aus­gegeben wer­den: =N(F2<HEUTE()). Durch die Funk­tion N() wird ein WAHR in 1 und ein FALSCH in eine 0 umge­wan­delt.

Aber war­um ste­ht dort statt der Zah­len ein Text? Und das ohne jede WENN()-Funk­tion? Das ist nur eine Fra­ge des Zahlen­for­mats. Beim be­nut­zer­de­fi­nier­ten Zahlen­for­mat ist fol­gende For­matierung einge­tra­gen wor­den:

Eine kur­ze Erk­lärung: Die drei ers­ten Grup­pen der For­matierung, jew­eils durch Semi­ko­la getren­nt, stel­len das For­mat für po­si­tiv;ne­ga­tiv;null dar. Und da nicht nur Zif­fern wie­der­ge­ge­ben wer­den kön­nen, ha­ben wir hier in Anführungsze­ichen einge­fasste Tex­te ver­wen­det: "- Ja -";(leer);"-Nein-".

Spal­te I, WENN()

Auf den ers­ten Blick scheint die­se For­mel ganz ein­fach zu sein:
=WENN(F2<HEUTE();--HEUTE()-F2;"")
… und auf den zweit­en Blick wer­den Sie vielle­icht über das dop­pelte Mi­nus stol­pern. Mein Tipp: Pro­bieren Sie es ein­mal in ein­er an­de­ren, frei­en Zel­le ohne das Dop­pelmi­nus aus und find­en Sie die Erk­lärung dann hier im Blog.

Eine kur­ze Erk­lärung zum Rest: Wenn das Ziel­d­a­tum vor dem heuti­gen Tage liegt, dann be­rech­ne HEU­TE() mi­nus Ziel­d­a­tum, son­st gebe ei­nen Leer­text zu­rück. We­gen des son­st-Teils bleibt die Zel­le leer, wenn die Zah­lung noch nicht im Ver­zug ist.

Spal­te J, Mul­ti­pli­ka­ti­on [1]

Sie se­hen so­fort, dass hier auch die Null­w­erte an­ge­zeigt wer­den. Die For­mel, um das Ziel zu erre­ichen ist die­se:
=N(HEUTE()>F2)*(HEUTE()-F2)

Der Ver­gle­ich N(HEUTE()>F2) er­gibt 1, wenn HEU­TE() grö­ßer ist als das Fäl­ligkeits­da­tum, anson­sten 0. Die­ses Ergeb­nis wird dann mit der Dif­ferenz in Ta­gen Ziel­d­a­tum zu heu­te mul­ti­pliziert. Angenom­men, es sind 5 Tage, dann wird entwed­er (bei WAHR) 1*5 zu­rück ge­ge­ben, anson­sten 0*5.

Spal­te K, MAX()

Ei­gent­lich ist die­se For­mel ge­ni­al ein­fach:
=MAX(0;HEUTE()-F2)

Es wird das Max­i­mum der Wer­te Null auf der ei­nen Sei­te und den Dif­feren­z­ta­gen ander­er­seits zu­rück ge­ge­ben. Zum zweit­en Argu­ment: Ist die Rech­nung erst in der Zukun­ft fäl­lig, dann er­gibt es eine neg­a­tive Zahl. Ist sie ge­nau heu­te fäl­lig, dann ist das Ergeb­nis eine Null. Und ist das Fäl­ligkeits­da­tum in der Ver­gan­gen­heit, dann ist der berech­nete Wert pos­i­tiv, also in je­dem Fall grö­ßer als 0. Bei neg­a­tiv­en Wer­ten ist 0 grö­ßer, pos­i­tive Wer­te sing im­mer grö­ßer als 0 und bil­den so­mit das Max­i­mum.

Es bleibt die Fra­ge, war­um die Nul­len nicht an­ge­zeigt wer­den, die pos­i­tiv­en Wer­te aber sehr wohl. Hier ist ein we­nig Ihr Engage­ment ge­for­dert. Schau­en Sie sich ein­mal das Zahlen­for­mat die­ser Spal­te an und Sie wer­den ge­wiss die Ur­sa­che find­en.

Spal­te L, Mul­ti­pli­ka­ti­on [2]

Erken­nen Sie den Unter­schied zur ers­ten Mul­ti­p­lika­tion? Die N() – Funk­tion fehlt und der ers­te Teil ist die um­ge­kehr­te Lo­gik.
=(F2<HEUTE())*(HEUTE()-F2)

„War­um nicht gle­ich so?” Das scheint der ide­ale Weg zu sein. Nun ja, das Zahlen­for­mat spielt da aber auch eine Rol­le. Schau­en Sie ein­fach ein­mal nach. Oder Sie konzen­tri­eren sich auf die Spal­te M, die gar nicht so unähn­lich ist …

Spal­te M, Ein­fachs­te Rech­nung

Wirk­lich er­staun­lich, die­se Ergeb­nisse fall­en ja wirk­lich to­tal aus dem Rah­men. Entwed­er ein über 100 Jah­re al­tes Da­tum oder ein „Lat­ten­za­un”. Und wo­durch wird das aus­gelöst?
=HEUTE()-F2

Vielle­icht den­ken Sie nun: „Bei so ein­er bil­li­gen For­mel kann ja auch nichts bes­se­res her­aus kom­men.” Na ja, es ist wirk­lich die kür­zes­te, ein­fach­ste und meinethal­ben auch prim­i­tivste For­mel. Aber sie ist den­noch kor­rekt. Mit Ih­rer Hil­fe wird dort in weni­gen Sekun­den das gle­iche Ergeb­nis sicht­bar sein wie in der vorheri­gen Spal­te.

Schritt für Schritt zum Ergeb­nis. Erst ein­mal: War­um ste­ht dort teil­weise ein Da­tum und bei den FALSCH-Wer­ten die­se Rei­he von Rau­te-Zei­chen? Die Ant­wort ist ganz schlüs­sig: Ex­cel gibt als (sicht­bares) Ergeb­nis ein Da­tum zu­rück, wenn zwei kalen­darische Dat­en berech­net wer­den. Und die # er­schei­nen im­mer, wenn ein neg­a­tiv­er Da­tum- bzw. Zeit­wert dar­ge­stellt wer­den müss­te. Hin­weis: Das gilt nicht für die Mac-Ver­sion des Ex­cel, da wird stets der kor­rek­te Wert aus­gegeben (wie auch bei den Zeit­en). Faz­it: Im Bere­ich M2:M21 ste­hen kalen­darische Dat­en.

Nun sind Sie dran: Mar­kie­ren Sie Spal­te M und for­matieren Sie die­se ab Zei­le 2 ge­nau so wie Spal­te K. Das geht auch sehr schön mit dem For­mat-Pin­sel. Und Sie se­hen, das Ergeb­nis passt. 🙂 

▲ nach oben …

Lö­sung 5

Über­sicht

Noch eine recht ein­fache Bed­ingte For­matierung. Prinzip­iell wie die ers­te Lö­sung, nur mit an­de­ren Far­ben. Der gra­vie­ren­de Unter­schied ist, dass Anwen­der hier in Zel­le I1 per Hand ein Da­tum ein­ge­ben und die Bed­ingte For­matierung dann so­fort die­sem Wert an­ge­passt wird.

Vor­ge­hens­wei­se

Wie Sie die Bedin­gung bei der Bed­ingten For­matierung ein­ge­ben, ist bere­its in Lö­sung 1 be­schrie­ben. Bleibt nur noch die For­mel, und die ist wirk­lich kurz, knapp, präg­nant:
=$F2<$I$1

▲ nach oben …

Lö­sung n

Es gibt noch zig weit­ere Möglichkeit­en. Aber das wür­de hier zu weit füh­ren. Eine beson­ders inter­es­sante Vari­ante kann eine Piv­ot-Ta­bel­le sein, dazu be­darf es aber ei­ni­ges an Er­fah­rung mit Ex­cel. Wenn Sie exper­i­men­tier­freudig sind, soll­ten Sie sich da­mit ein­mal be­fas­sen.

[NachOben­Let­zte Ver­weis=„ML: Zahlungskon­trolle”]