PDA

Vollständige Version anzeigen : sverweis mit Ausschlusskriterium


Baika
15.07.2014, 07:44
Hallo,

ich möchte gerne mit einem sverweis und Ausschlusskriterium arbeiten. Kann mir jemand bei der Formel helfen?
Ich habe dem Bereich einen Namen gegeben z.B. Einkommen. Suchen tue ich nach Spalte A. Aber wie kann ich ausdrücken, dass er den Wert nur ziehen soll, wenn in einer 2. Spalte z.B. „D“ noch eine bestimmte Bezeichnung steht wie z.b. „Privat“? Also: suche nach Bezeichnung in Spalte A und ziehe den Wert nur, wen in „D“ „privat“ steht?

Danke und Gruß
Baika

olerostock
15.07.2014, 07:57
Hallo Baika,

Wo sollen die Werte hin? Neue Tabelle oder andere Spalte? Ich glaube nicht, dass Deine Angaben ausreichen. Vielleicht Datei hochladen oder Deine Formel zeigen.

Gruß Olerostock

Mc Santa
15.07.2014, 08:01
Hallo,

ich habe folgenden Vorschlag:
<br/><b><em>Tabelle1</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:54px;" /><col style="width:54px;" /><col style="width:54px;" /><col style="width:75px;" /><col style="width:75px;" /><col style="width:75px;" /><col style="width:75px;" /><col style="width:75px;" /><col style="width:75px;" /></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><td>E</td><td>F</td><td>G</td><td>H</td><td>I</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Wert</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Bla</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">BlaBla</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Status</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Gesucht</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Wert</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Status</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Haus</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">4</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">5</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Öffentlich</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">2000</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FF9900 ;; text-align:center; ">Haus</td><td style=" color:#000000; border-color:#000000; background-color:#FF9900 ;; text-align:center; ">Privat</td><td style=" color:#000000; border-color:#000000; background-color:#FFCC99 ;; text-align:center; ">10000</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Haus</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">5</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">6</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Öffentlich</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">3000</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Wurm</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">6</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">7</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Geheim</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">4000</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">5</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Auto</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">7</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">8</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Privat</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">5000</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">6</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Wurm</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">8</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">9</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Öffentlich</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">6000</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">7</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Wurm</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">9</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">10</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Geheim</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">7000</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">8</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Auto</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">10</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">11</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Geheim</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">8000</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">9</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Auto</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">11</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">12</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Privat</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">9000</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">10</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Haus</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">12</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">13</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Privat</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">10000</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&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>N/A</td></tr><tr><td>I2</td><td>=VERWEIS(2;1/($A$1:$A$10&amp;$D$1:$D$10=G2&amp;H2);E:E)</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/>

Eine Erklärung zur Formel gibt es hier:
http://www.excelformeln.de/formeln.html?welcher=30

Hilft dir das weiter?
VG