PDA

Vollständige Version anzeigen : sverweis, wenn-fkt, Schleife?


humbah
18.07.2014, 12:15
Hallo,

ich habe ein Problem mit meiner Excel-Tabelle: Es handelt sich um Lagerverwaltung mit 3 Registern (Wareneingang, Warenausgang und Bestand), die Datei ist im Anhang.

Leider wiederholt die Formel die Anweisung nicht. Also wenn ich einen Wareneingang von 40 Pizzen habe und am nächsten Tag 10 wieder rausgehen, habe ich noch 30. Bis hierher funktioniert es noch. Wenn ich aber einen Tag später wieder 10 Pizzen im Warenausgang habe, registriert die Formel das nicht.

Ich brauche dringend Hilfe :(

jack_D
18.07.2014, 12:21
Hallo Humba

Kann auch nicht gehen. WArum?

Der Sverweis sucht nach dem ersten Treffer. Den gibt er dir wieder... Alles was unterhalb ist ignoriert er.

So in einem ersten Schuss mal ein Lösungsansatz

<br/>
<b><em>Bestand</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:120px;" /><col style="width:100px;" /><col style="width:106px;" /><col style="width:106px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>Bezeichnung</b></td><td style="color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>Menge WE</b></td><td style="color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>Menge WA</b></td><td style="color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>Bestand</b></td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:left; ">pizza</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">10</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">5</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">5</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:left; ">eis</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">20</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">7</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">13</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:left; ">Toaster</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">30</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">30</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">5</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:left; ">Smart</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">15</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">1</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">14</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">6</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:left; ">Roller</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">10</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">5</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">5</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">7</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:left; ">Glätteisen</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">9</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">9</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">8</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">0</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">9</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">10</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px;font-size:11pt; background-color:#ffffff; width:800px;padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td colspan="3" > verwendete Formeln </td><td> </td></tr><tr valign="top" style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td> Zelle </td><td> Formel</td><td> Bereich </td><td>R1C1 für Add In</td></tr><tr><td>D2: D7</td><td>=WENN(C2="";B2;B2-C2)</td><td>&nbsp;</td><td>&nbsp;=IF(RC[-1]="",RC[-2],RC[-2]-RC[-1])</td></tr><tr><td>B2:B8</td><td>=SUMMEWENN(WE!A:A;Bestand!A2;WE!B:B)</td><td>&nbsp;</td><td>&nbsp;=SUMIF(WE!C[-1],Bestand!RC[-1],WE!C)</td></tr><tr><td>C2:C8</td><td>=SUMMEWENN(WA!A:A;Bestand!A2;WA!B:B)</td><td>&nbsp;</td><td>&nbsp;=SUMIF(WA!C[-2],Bestand!RC[-2],WA!C[-1])</td></tr><tr><td>A2:A7,A9:A10</td><td>=WENN(ISTNV(SVERWEIS(WE!A2;WE!A:B;1;0));"";SVERWEIS(WE!A2;WE!A:B;1;0))</td><td>&nbsp;</td><td>&nbsp;=IF(ISNA(VLOOKUP(WE!RC,WE!C:C[1],1,0)),"",VLOOKUP(WE!RC,WE!C:C[1],1,0))</td></tr></table><br/><a href='http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip' >http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip</a><br/><a href='http://Hajo-Excel.de/tools.htm' >http://hajo-excel.de/tools.htm</a><br/>XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007<br/>Add-In-Version 14.15 einschl 64 Bit<br/>

Grüße

Mc Santa
18.07.2014, 12:22
Hallo,

Sverweis findet immer nur einen Eintrag. Diese Formel kannst du hier nicht benutzen.

So würde es gehen:
<br/><b><em>Bestand</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:120px;" /><col style="width:100px;" /><col style="width:106px;" /><col style="width:106px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; "><b>Bezeichnung</b></td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; "><b>Menge WE</b></td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; "><b>Menge WA</b></td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; "><b>Bestand</b></td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">pizza</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">10</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">5</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">5</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">eis</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">20</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">7</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">13</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Toaster</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">30</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">30</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">5</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Smart</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">15</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">14</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">6</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Roller</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">10</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">5</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">5</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">7</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Glätteisen</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">9</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">0</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">9</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px;font-size:11pt; background-color:#ffffff; width:800px;padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td colspan="3" > verwendete Formeln </td><td> </td></tr><tr valign="top" style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td> Zelle </td><td> Formel</td><td> Bereich </td> <td>N/A</td></tr><tr><td>A2:A7</td><td>=WENN(ISTNV(SVERWEIS(WE!A2;WE!A:B;1;0));"";SVERWEIS(WE!A2;WE!A:B;1;0))</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>B2:B7</td><td>=SUMMENPRODUKT(N(A2=WE!$A$2:$A$25);WE!$B$2:$B$25)</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>C2:C7</td><td>=SUMMENPRODUKT(N(A2=WA!$A$2:$A$25);WA!$B$2:$B$25)</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>D2:&nbsp;D7</td><td>=B2-C2</td><td>&nbsp;</td><td>&nbsp;</td></tr></table><table cellspacing="0" cellpadding="0"><tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href="http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip" >Excel-Inn.de</a></td></tr> <tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href='http://Hajo-Excel.de/tools.htm' >Hajo-Excel.de</a></td></tr><tr style="text-align:left; font-weight:bold;" ><td style="text-align:left; font-size: xx-small" >XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007</td></tr><tr style="text-align:left; font-weight:bold; " ><td style="text-align:left; font-size: xx-small" > Add-In-Version 18.01 einschl. 64 Bit</td></tr></table><br/>

Hilft dir das?
VG

humbah
23.07.2014, 10:19
:grins: es funktioniert! Vielen Dank!