PDA

Vollständige Version anzeigen : Ausdruck, wenn Bedingung in abhängigkeit mit zellwert erfüllt ist


Der_Markus
15.12.2011, 15:15
Hallo Exceljunkies,

ich schlag mich mal wieder mir nem Problem rum:

<b>Tabelle5</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:154px;" /><col style="width:73px;" /><col style="width:63px;" /><col style="width:80px;" /><col style="width:145px;" /><col style="width:80px;" /><col style="width:80px;" /></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></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:9pt; text-align:right; ">Mi., 30. Nov 2011 </td><td style="font-size:9pt; ">&nbsp;</td><td style="font-size:9pt; ">&nbsp;</td><td style="font-size:10pt; ">&nbsp;</td><td style="font-size:10pt; ">&nbsp;</td><td rowspan="3" style="font-size:10pt; text-align:center; ">nur<br />Nacht- schicht</td><td rowspan="3" style="font-size:10pt; text-align:center; ">Fr&uuml;h-<br />oder<br />Sp&auml;tschicht</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:9pt; text-align:right; ">Do., 01. Dez 2011 </td><td style="font-weight:bold; font-size:9pt; text-align:left; ">Fr&uuml;h</td><td style="font-size:9pt; text-align:right; ">06:00</td><td style="font-size:10pt; ">&nbsp;</td><td style="font-size:10pt; ">&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:9pt; text-align:right; ">Fr., 02. Dez 2011 </td><td style="font-weight:bold; font-size:9pt; ">Fr&uuml;h</td><td style="font-size:9pt; text-align:right; ">06:00</td><td style="font-size:10pt; ">&nbsp;</td><td rowspan="2" style="font-weight:bold; font-size:10pt; text-align:center; ">Ver&auml;nderbares &nbsp;Datum:</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:9pt; text-align:right; ">Sa., 03. Dez 2011 </td><td style="font-weight:bold; font-size:9pt; ">&nbsp;</td><td style="font-size:9pt; ">&nbsp;</td><td style="font-size:10pt; ">&nbsp;</td><td style="font-size:10pt; ">&nbsp;</td><td style="font-size:10pt; ">&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:9pt; text-align:right; ">So., 04. Dez 2011 </td><td style="font-weight:bold; font-size:9pt; ">Nacht</td><td style="font-size:9pt; text-align:right; ">22:00</td><td style="font-size:10pt; ">&nbsp;</td><td style="font-weight:bold; font-size:10pt; text-align:right; ">Fr., 02. Dez. 2011 </td><td style="background-color:#ffcc99; font-weight:bold; font-size:10pt; text-align:right; ">06:00</td><td style="background-color:#969696; font-weight:bold; font-size:10pt; ">&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:9pt; text-align:right; ">Mo., 05. Dez 2011 </td><td style="font-weight:bold; font-size:9pt; ">Nacht</td><td style="font-size:9pt; text-align:right; ">22:00</td><td style="font-size:10pt; ">&nbsp;</td><td style="font-weight:bold; font-size:10pt; text-align:right; ">Di., 06. Dez. 2011 </td><td style="background-color:#ffcc99; font-weight:bold; font-size:10pt; text-align:right; ">22:00</td><td style="background-color:#969696; font-weight:bold; font-size:10pt; ">&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:9pt; text-align:right; ">Di., 06. Dez 2011 </td><td style="font-weight:bold; font-size:9pt; ">Nacht</td><td style="font-size:9pt; text-align:right; ">22:00</td><td style="font-size:10pt; ">&nbsp;</td><td style="font-weight:bold; font-size:10pt; text-align:right; ">Mo., 28. Nov. 2011 </td><td style="background-color:#ffcc99; font-weight:bold; font-size:10pt; ">&nbsp;</td><td style="background-color:#969696; font-weight:bold; font-size:10pt; ">&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:9pt; text-align:right; ">Mi., 07. Dez 2011 </td><td style="font-weight:bold; font-size:9pt; ">Nacht</td><td style="font-size:9pt; text-align:right; ">22:00</td><td style="font-size:10pt; ">&nbsp;</td><td style="font-size:10pt; ">&nbsp;</td><td style="font-size:10pt; ">&nbsp;</td><td style="font-size:10pt; ">&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:9pt; text-align:right; ">Do., 08. Dez 2011 </td><td style="font-weight:bold; font-size:9pt; ">Nacht</td><td style="font-size:9pt; text-align:right; ">22:00</td><td style="font-size:10pt; ">&nbsp;</td><td style="font-size:10pt; ">&nbsp;</td><td style="font-size:10pt; ">&nbsp;</td><td style="font-size:10pt; ">&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:9pt; text-align:right; ">Fr., 09. Dez 2011 </td><td style="font-weight:bold; font-size:9pt; ">Nacht</td><td style="font-size:9pt; text-align:right; ">22:00</td><td style="font-size:10pt; ">&nbsp;</td><td style="font-size:10pt; ">&nbsp;</td><td style="font-size:10pt; ">&nbsp;</td><td style="font-size:10pt; ">&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:9pt; text-align:right; ">Sa., 10. Dez 2011 </td><td style="font-weight:bold; font-size:9pt; ">&nbsp;</td><td style="font-size:9pt; ">&nbsp;</td><td style="font-size:10pt; ">&nbsp;</td><td style="font-size:10pt; ">&nbsp;</td><td style="font-size:10pt; ">&nbsp;</td><td style="font-size:10pt; ">&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:9pt; text-align:right; ">So., 11. Dez 2011 </td><td style="font-weight:bold; font-size:9pt; ">&nbsp;</td><td style="font-size:9pt; ">&nbsp;</td><td style="font-size:10pt; ">&nbsp;</td><td style="font-size:10pt; ">&nbsp;</td><td style="font-size:10pt; ">&nbsp;</td><td style="font-size:10pt; ">&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:9pt; text-align:right; ">Mo., 12. Dez 2011 </td><td style="font-weight:bold; font-size:9pt; ">Sp&auml;t</td><td style="font-size:9pt; text-align:right; ">14:00</td><td style="font-size:10pt; ">&nbsp;</td><td style="font-size:10pt; ">&nbsp;</td><td style="font-size:10pt; ">&nbsp;</td><td style="font-size:10pt; ">&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:9pt; text-align:right; ">Di., 13. Dez 2011 </td><td style="font-weight:bold; font-size:9pt; ">Sp&auml;t</td><td style="font-size:9pt; text-align:right; ">14:00</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-size:9pt; text-align:right; ">Mi., 14. Dez 2011 </td><td style="font-weight:bold; font-size:9pt; ">Sp&auml;t</td><td style="font-size:9pt; text-align:right; ">14:00</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-size:9pt; text-align:right; ">Do., 15. Dez 2011 </td><td style="font-weight:bold; font-size:9pt; ">Sp&auml;t</td><td style="font-size:9pt; text-align:right; ">14:00</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="font-size:9pt; text-align:right; ">Fr., 16. Dez 2011 </td><td style="font-weight:bold; font-size:9pt; ">Sp&auml;t</td><td style="font-size:9pt; text-align:right; ">14:00</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="font-size:9pt; text-align:right; ">Sa., 17. Dez 2011 </td><td style="font-weight:bold; font-size:9pt; ">&nbsp;</td><td style="font-size:9pt; ">&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="font-size:9pt; text-align:right; ">So., 18. Dez 2011 </td><td style="font-weight:bold; font-size:9pt; ">Nacht</td><td style="font-size:9pt; text-align:right; ">22:00</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="font-size:9pt; text-align:right; ">Mo., 19. Dez 2011 </td><td style="font-weight:bold; font-size:9pt; ">Nacht</td><td style="font-size:9pt; text-align:right; ">22:00</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="font-size:9pt; text-align:right; ">Di., 20. Dez 2011 </td><td style="font-weight:bold; font-size:9pt; ">Nacht</td><td style="font-size:9pt; text-align:right; ">22:00</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="font-size:9pt; text-align:right; ">Mi., 21. Dez 2011 </td><td style="font-weight:bold; font-size:9pt; ">Nacht</td><td style="font-size:9pt; text-align:right; ">22:00</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td style="font-size:9pt; text-align:right; ">Do., 22. Dez 2011 </td><td style="font-weight:bold; font-size:9pt; ">Nacht</td><td style="font-size:9pt; text-align:right; ">22:00</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="font-size:9pt; text-align:right; ">Fr., 23. Dez 2011 </td><td style="font-weight:bold; font-size:9pt; ">Nacht</td><td style="font-size:9pt; text-align:right; ">22:00</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td style="font-size:9pt; text-align:right; ">Sa., 24. Dez 2011 </td><td style="font-weight:bold; font-size:9pt; ">&nbsp;</td><td style="font-size:9pt; ">&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</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 >F5</td><td >=WENN(ISTFEHLER<span style=' color:008000; '>(E5=SVERWEIS<span style=' color:#0000ff; '>(E5;$A$1:$A$33;1)</span>)</span>;"";WENN<span style=' color:008000; '>(SVERWEIS<span style=' color:#0000ff; '>(E5;A1:C33;3)</span>&gt;0;SVERWEIS<span style=' color:#0000ff; '>(E5;A1:C33;3)</span>;"")</span>)</td></tr><tr><td >F6</td><td >=WENN(ISTFEHLER<span style=' color:008000; '>(E6=SVERWEIS<span style=' color:#0000ff; '>(E6;$A$1:$A$33;1)</span>)</span>;"";WENN<span style=' color:008000; '>(SVERWEIS<span style=' color:#0000ff; '>(E6;A1:C33;3)</span>&gt;0;SVERWEIS<span style=' color:#0000ff; '>(E6;A1:C33;3)</span>;"")</span>)</td></tr><tr><td >F7</td><td >=WENN(ISTFEHLER<span style=' color:008000; '>(E7=SVERWEIS<span style=' color:#0000ff; '>(E7;$A$1:$A$33;1)</span>)</span>;"";WENN<span style=' color:008000; '>(SVERWEIS<span style=' color:#0000ff; '>(E7;A1:C33;3)</span>&gt;0;SVERWEIS<span style=' color:#0000ff; '>(E7;A1:C33;3)</span>;"")</span>)</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>

Ich möchte halt das in Spalte F nur die Nachtschichtzeit bzw. in Spalte G nur Früh- oder Spätschicht steht natürlich in Abhängigkeit des Datums in Spalte E.

Danke für Eure Hilfen

EarlFred
15.12.2011, 16:00
Hallo Markus,

kleiner Tipp: Beim SVerweis nie den 4. Parameter auf 1 setzen (oder weglassen), wenn eine genaue Übereinstimmung (wie meistens und so auch in diesem Fall) gesucht wird.

Anstelle Sverweis würde ich, da Uhrzeiten (=Zahlenwerte) zurückgegeben werden sollen, Summenprodukt nehmen:
Nachtschicht
=SUMMENPRODUKT(
($A$1:$A$25=E5)*
($B$1:$B$25="Nacht")*
($C$1:$C$25))
Früh- und Spätschicht
=SUMMENPRODUKT(
($A$1:$A$25=E5)*
(($B$1:$B$25="Früh")+($B$1:$B$25="Spät"))*
($C$1:$C$25))
Ich gehe mal davon aus, dass in Spalte A kein Datum doppelt vorkommen kann, sonst würde die 2. Formel ggf. falsche Ergebnisse ausgeben.

Um Nullwerte zu unterdrücken, habe ich für Zellen mit den Formeln folgendes Format vorgegeben:
hh:mm:ss;;
Die beiden Semikola am Ende sind wichtig - also nicht weglassen!

Dass die Datumsangaben als Datumswert vorliegen, setze ich dabei voraus. Wenn es Text wäre, würdest Du sonst eh keine Übereinstimmung finden, da die Formate abweichen.

Grüße
EarlFred