PDA

Vollständige Version anzeigen : Anhand von Einträgen das Datum ermitteln


DCStefano
19.03.2009, 07:18
Hallo zusammen,

ich stehe vor folgendem Problem:

Ich habe eine MA-Liste in der die Urlaubstage mit "U" eingetragen werden. Das Layout der Liste habe ich mit Beispieleinträgen angehängt.

Mit der Funktion "Zählenwenn" wird die Anzahl der Urlaubstage im entsprechenden Monat ermittelt, soweit so gut. Nun soll aber auch ermittel werden, an welchen Tagen der MA Urlaub hat, sprich alle Tage aufgezählt werden.

Am besten erklärt die Problemstellung das angehängte Beispiel, grün ist markiert wie es später aussehen soll, der Rest ist die Ausgangsliste.

Danke.

Backowe
19.03.2009, 08:13
Hi Stefano,

mal ein kleiner Denkanstoß, mehr Zeit habe ich im Moment nicht, ansonsten heute Abend wieder.

<table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:210px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >&nbsp;</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:center; ">01.04.2009</td><td style="text-align:center; ">02.04.2009</td><td style="text-align:center; ">03.04.2009</td><td style="text-align:center; ">04.04.2009</td><td style="text-align:center; ">05.04.2009</td><td style="text-align:center; ">06.04.2009</td><td style="text-align:center; ">07.04.2009</td><td style="text-align:center; ">08.04.2009</td><td style="text-align:center; ">09.04.2009</td><td style="text-align:center; ">10.04.2009</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:center; ">u</td><td >&nbsp;</td><td style="text-align:center; ">u</td><td style="text-align:center; ">u</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >01.04.2009;03.04.2009;04.04.2009</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >K3</td><td >{=VERKETTEN(TEXT<span style=' color:008000; '>(INDEX<span style=' color:#0000ff; '>(1:1;KKLEINSTE<span style=' color:#ff0000; '>(WENN<span style=' color:#804000; '>(2:2="u";SPALTE<span style=' color:#ff7837; '>(1:1)</span>)</span>;SPALTE<span style=' color:#804000; '>(A1)</span>)</span>)</span>;"TT.MM.JJJJ")</span>;";"&TEXT<span style=' color:008000; '>(INDEX<span style=' color:#0000ff; '>(1:1;KKLEINSTE<span style=' color:#ff0000; '>(WENN<span style=' color:#804000; '>(2:2="u";SPALTE<span style=' color:#ff7837; '>(1:1)</span>)</span>;SPALTE<span style=' color:#804000; '>(B1)</span>)</span>)</span>;"TT.MM.JJJJ")</span>;";";TEXT<span style=' color:008000; '>(INDEX<span style=' color:#0000ff; '>(1:1;KKLEINSTE<span style=' color:#ff0000; '>(WENN<span style=' color:#804000; '>(2:2="u";SPALTE<span style=' color:#ff7837; '>(1:1)</span>)</span>;SPALTE<span style=' color:#804000; '>(C1)</span>)</span>)</span>;"TT.MM.JJJJ")</span>)}</td></tr></table></td></tr><tr><td ><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>Enth&auml;lt Matrixformel:</span><span style=' font-family:Arial,Arial; font-size:9pt;'><br />Umrandende </span><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>{ }</span><span style=' font-family:Arial,Arial; font-size:9pt;'> nicht miteingeben,<br />sondern Formel mit STRG+SHIFT+RETURN abschlie&szlig;en!</span></td></tr><tr><td ><a href="http://www.online-excel.de/excel/singsel.php?f=26" style="font-family:Arial; font-size:8pt;font-weight:bold;color:#000080;" target='_blank'>Matrix verstehen</a></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel Tabellen im Web darstellen &gt;&gt; </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de" target="_blank"> Excel Jeanie HTML 4 </a>

DCStefano
19.03.2009, 08:32
Danke für den Denkanstoß, aber wenn ich in deinem Beispiel in der Zelle A1 den Eintrag lösche dann wird in der Zelle K3 nicht wie erwartet "03.04.2009;04.04.2009" angezeigt, sondern "#Zahl".

Außerdem verstehe ich auch nicht wieso das Detaum "04.04.2009" mitausgegeben wird, da doch nur A1, B1 und C1 in der Formel berücksichtigt werden, aber ich werde mal mit der Variante experimentieren, Danke schon mal.

Für andere Vorschläge oder Tipps bzgl. der Matrix bin ich weiterhin dankbar.

Backowe
19.03.2009, 16:02
Hallo Stefano,

heute während der Schulung ist mir folgende Idee gekommen:

<b>Tabelle1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:80px;" /><col style="width:21px;" /><col style="width:26px;" /><col style="width:24px;" /><col style="width:25px;" /><col style="width:26px;" /><col style="width:28px;" /><col style="width:19px;" /><col style="width:21px;" /><col style="width:26px;" /><col style="width:24px;" /><col style="width:25px;" /><col style="width:26px;" /><col style="width:28px;" /><col style="width:21px;" /><col style="width:21px;" /><col style="width:26px;" /><col style="width:24px;" /><col style="width:25px;" /><col style="width:26px;" /><col style="width:28px;" /><col style="width:21px;" /><col style="width:21px;" /><col style="width:26px;" /><col style="width:24px;" /><col style="width:25px;" /><col style="width:26px;" /><col style="width:28px;" /><col style="width:21px;" /><col style="width:21px;" /><col style="width:26px;" /><col style="width:80px;" /><col style="width:420px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >&nbsp;</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td><td >P</td><td >Q</td><td >R</td><td >S</td><td >T</td><td >U</td><td >V</td><td >W</td><td >X</td><td >Y</td><td >Z</td><td >AA</td><td >AB</td><td >AC</td><td >AD</td><td >AE</td><td >AF</td><td >AG</td><td >AH</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td rowspan="3" style="font-weight:bold; text-align:center; ">PersNr.</td><td rowspan="3" style="font-weight:bold; text-align:center; ">Name</td><td colspan="30" style="font-weight:bold; text-align:center; ">April</td><td rowspan="3" style="font-weight:bold; text-align:center; ">Gesamt</td><td rowspan="3" style="background-color:#ccffcc; text-align:center; ">Urlaubstage</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >MI</td><td >DO</td><td >FR</td><td >SA</td><td >SO</td><td >MO</td><td >DI</td><td >MI</td><td >DO</td><td >FR</td><td >SA</td><td >SO</td><td >MO</td><td >DI</td><td >MI</td><td >DO</td><td >FR</td><td >SA</td><td >SO</td><td >MO</td><td >DI</td><td >MI</td><td >DO</td><td >FR</td><td >SA</td><td >SO</td><td >MO</td><td >DI</td><td >MI</td><td >DO</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:center; ">1</td><td style="text-align:center; ">2</td><td style="text-align:center; ">3</td><td style="text-align:center; ">4</td><td style="text-align:center; ">5</td><td style="text-align:center; ">6</td><td style="text-align:center; ">7</td><td style="text-align:center; ">8</td><td style="text-align:center; ">9</td><td style="text-align:center; ">10</td><td style="text-align:center; ">11</td><td style="text-align:center; ">12</td><td style="text-align:center; ">13</td><td style="text-align:center; ">14</td><td style="text-align:center; ">15</td><td style="text-align:center; ">16</td><td style="text-align:center; ">17</td><td style="text-align:center; ">18</td><td style="text-align:center; ">19</td><td style="text-align:center; ">20</td><td style="text-align:center; ">21</td><td style="text-align:center; ">22</td><td style="text-align:center; ">23</td><td style="text-align:center; ">24</td><td style="text-align:center; ">25</td><td style="text-align:center; ">26</td><td style="text-align:center; ">27</td><td style="text-align:center; ">28</td><td style="text-align:center; ">29</td><td style="text-align:center; ">30</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">000011</td><td >Mustermann</td><td style="text-align:center; ">U</td><td style="text-align:center; ">U</td><td style="text-align:center; ">U</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td style="text-align:center; ">3</td><td style="background-color:#ccffcc; ">01.04.2009;02.04.2009;03.04.2009</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">200123</td><td >Musterfrau</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td style="text-align:center; ">U</td><td style="text-align:center; ">U</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td style="text-align:center; ">U</td><td style="text-align:center; ">U</td><td style="text-align:center; ">U</td><td style="text-align:center; ">U</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td style="text-align:center; ">6</td><td style="background-color:#ccffcc; ">06.04.2009;07.04.2009;14.04.2009;15.04.2009;16.04.2009;17.04.2009</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">000024</td><td >Beckenbauer</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td style="text-align:center; ">0</td><td style="background-color:#ccffcc; ">&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">432100</td><td >Becker</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td style="text-align:center; ">U</td><td style="text-align:center; ">U</td><td style="text-align:center; ">U</td><td style="text-align:center; ">U</td><td style="text-align:center; ">4</td><td style="background-color:#ccffcc; ">27.04.2009;28.04.2009;29.04.2009;30.04.2009</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">123456</td><td >Schmidt</td><td style="text-align:center; ">U</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td style="text-align:center; ">U</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td style="text-align:center; ">U</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td style="text-align:center; ">U</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td style="text-align:center; ">U</td><td >&nbsp;</td><td style="text-align:center; ">5</td><td style="background-color:#ccffcc; ">01.04.2009;08.04.2009;15.04.2009;22.04.2009;29.04.2009</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >AG4</td><td >=Z&Auml;HLENWENN(C4:AF4;"U")</td></tr><tr><td >AH4</td><td >=ZustandAuflisten($C4:$AF4;"u")</td></tr><tr><td >AG5</td><td >=Z&Auml;HLENWENN(C5:AF5;"U")</td></tr><tr><td >AH5</td><td >=ZustandAuflisten($C5:$AF5;"u")</td></tr><tr><td >AG6</td><td >=Z&Auml;HLENWENN(C6:AF6;"U")</td></tr><tr><td >AG7</td><td >=Z&Auml;HLENWENN(C7:AF7;"U")</td></tr><tr><td >AH7</td><td >=ZustandAuflisten($C7:$AF7;"u")</td></tr><tr><td >AG8</td><td >=Z&Auml;HLENWENN(C8:AF8;"U")</td></tr><tr><td >AH8</td><td >=ZustandAuflisten($C8:$AF8;"u")</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel Tabellen im Web darstellen &gt;&gt; </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de" target="_blank"> Excel Jeanie HTML 4 </a>

<div><link href="http://www.ms-office-forum.net/forum/externals/codeconv.css" rel="stylesheet"><pre><span class="TOKEN">Function</span> ZustandAuflisten(Bereich <span class="TOKEN">As</span> Range, Zustand <span class="TOKEN">As</span> <span class="TOKEN">String</span>)
<span class="REM">'Code in ein Modul</span>
<span class="TOKEN">Dim</span> Zelle <span class="TOKEN">As</span> Range
Application.Volatile
<span class="TOKEN">For</span> <span class="TOKEN">Each</span> Zelle <span class="TOKEN">In</span> Bereich
<span class="TOKEN">If</span> LCase(Zelle.Value) = Zustand <span class="TOKEN">Then</span>
ZustandAuflisten = ZustandAuflisten &amp; <span class="TOKEN">CDate</span>(Cells(3, Zelle.Column) &amp; &quot;.&quot; &amp; Cells(1, &quot;C&quot;) &amp; &quot;.&quot; &amp; Year(Date)) &amp; &quot;;&quot;
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">Next</span>
ZustandAuflisten = Left(ZustandAuflisten, Len(ZustandAuflisten) - 1)
<span class="TOKEN">End</span> <span class="TOKEN">Function</span></pre></div>
Code eingefügt mit dem MOF Code Converter (http://www.ms-office-forum.net/forum/codeconverter.php)

Backowe
19.03.2009, 16:33
Hi Stefano,

noch eine kleine Änderung, wenn der Zustand nicht vorhanden ist:

<div><link href="http://www.ms-office-forum.net/forum/externals/codeconv.css" rel="stylesheet"><pre><span class="TOKEN">Function</span> ZustandAuflisten(Bereich <span class="TOKEN">As</span> Range, Zustand <span class="TOKEN">As</span> <span class="TOKEN">String</span>)
<span class="REM">'Code in ein Modul</span>
<span class="TOKEN">Dim</span> Zelle <span class="TOKEN">As</span> Range
Application.Volatile
<span class="TOKEN">For</span> <span class="TOKEN">Each</span> Zelle <span class="TOKEN">In</span> Bereich
<span class="TOKEN">If</span> LCase(Zelle.Value) = Zustand <span class="TOKEN">Then</span>
ZustandAuflisten = ZustandAuflisten &amp; <span class="TOKEN">CDate</span>(Cells(3, Zelle.Column) &amp; &quot;.&quot; &amp; Cells(1, &quot;C&quot;) &amp; &quot;.&quot; &amp; Year(Date)) &amp; &quot;;&quot;
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">Next</span>
<span class="TOKEN">If</span> ZustandAuflisten &lt;&gt; &quot;&quot; <span class="TOKEN">Then</span>
ZustandAuflisten = Left(ZustandAuflisten, Len(ZustandAuflisten) - 1)
<span class="TOKEN">Else</span>
ZustandAuflisten = &quot;&quot;
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">End</span> <span class="TOKEN">Function</span></pre></div>
Code eingefügt mit dem MOF Code Converter (http://www.ms-office-forum.net/forum/codeconverter.php)

DCStefano
20.03.2009, 06:53
Danke Jürgen für deine Hilfe funktioniert wunderbar.