PDA

Vollständige Version anzeigen : Wert über mehrspaltige Tabelle ermitteln


chbahn
23.03.2009, 14:31
Hallo,

ich habe eine Tabelle mit drei Spalten: Kunde, Hersteller und Datum.
In dieser Tabelle kommt jede Kunde, Hersteller Kombination nur einmal vor.
In einem zweiten Datenblatt möchte ich mit einer Funktion das Datum ermitteln für einer bestimmten Kunden und Hersteller.
Eine Beispieltabelle habe ich beigefügt.

Danke für eure Hilfe
Christian

Backowe
23.03.2009, 14:54
Hi,

<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:80px;" /><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></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >&nbsp;</td><td >Hersteller</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Kunde</td><td style="text-align:right; ">4307965</td><td style="text-align:right; ">41906158</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">19608</td><td style="text-align:right; ">19.03.2009</td><td style="text-align:right; ">02.08.2007</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">22668</td><td style="text-align:right; ">00.01.1900</td><td style="text-align:right; ">23.03.2009</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">62774</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">115921</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 >B3</td><td >{=WENN(ISTFEHLER<span style=' color:008000; '>(INDEX<span style=' color:#0000ff; '>(Tabelle1!$C$1:$C$100;VERGLEICH<span style=' color:#ff0000; '>($A3&B$2;Tabelle1!$A$1:$A$100&Tabelle1!$B$1:$B$100;0)</span>)</span>)</span>;"";INDEX<span style=' color:008000; '>(Tabelle1!$C$1:$C$100;VERGLEICH<span style=' color:#0000ff; '>($A3&B$2;Tabelle1!$A$1:$A$100&Tabelle1!$B$1:$B$100;0)</span>)</span>)}</td></tr><tr><td >C3</td><td >{=WENN(ISTFEHLER<span style=' color:008000; '>(INDEX<span style=' color:#0000ff; '>(Tabelle1!$C$1:$C$100;VERGLEICH<span style=' color:#ff0000; '>($A3&C$2;Tabelle1!$A$1:$A$100&Tabelle1!$B$1:$B$100;0)</span>)</span>)</span>;"";INDEX<span style=' color:008000; '>(Tabelle1!$C$1:$C$100;VERGLEICH<span style=' color:#0000ff; '>($A3&C$2;Tabelle1!$A$1:$A$100&Tabelle1!$B$1:$B$100;0)</span>)</span>)}</td></tr><tr><td >B4</td><td >{=WENN(ISTFEHLER<span style=' color:008000; '>(INDEX<span style=' color:#0000ff; '>(Tabelle1!$C$1:$C$100;VERGLEICH<span style=' color:#ff0000; '>($A4&B$2;Tabelle1!$A$1:$A$100&Tabelle1!$B$1:$B$100;0)</span>)</span>)</span>;"";INDEX<span style=' color:008000; '>(Tabelle1!$C$1:$C$100;VERGLEICH<span style=' color:#0000ff; '>($A4&B$2;Tabelle1!$A$1:$A$100&Tabelle1!$B$1:$B$100;0)</span>)</span>)}</td></tr><tr><td >C4</td><td >{=WENN(ISTFEHLER<span style=' color:008000; '>(INDEX<span style=' color:#0000ff; '>(Tabelle1!$C$1:$C$100;VERGLEICH<span style=' color:#ff0000; '>($A4&C$2;Tabelle1!$A$1:$A$100&Tabelle1!$B$1:$B$100;0)</span>)</span>)</span>;"";INDEX<span style=' color:008000; '>(Tabelle1!$C$1:$C$100;VERGLEICH<span style=' color:#0000ff; '>($A4&C$2;Tabelle1!$A$1:$A$100&Tabelle1!$B$1:$B$100;0)</span>)</span>)}</td></tr><tr><td >B5</td><td >{=WENN(ISTFEHLER<span style=' color:008000; '>(INDEX<span style=' color:#0000ff; '>(Tabelle1!$C$1:$C$100;VERGLEICH<span style=' color:#ff0000; '>($A5&B$2;Tabelle1!$A$1:$A$100&Tabelle1!$B$1:$B$100;0)</span>)</span>)</span>;"";INDEX<span style=' color:008000; '>(Tabelle1!$C$1:$C$100;VERGLEICH<span style=' color:#0000ff; '>($A5&B$2;Tabelle1!$A$1:$A$100&Tabelle1!$B$1:$B$100;0)</span>)</span>)}</td></tr><tr><td >C5</td><td >{=WENN(ISTFEHLER<span style=' color:008000; '>(INDEX<span style=' color:#0000ff; '>(Tabelle1!$C$1:$C$100;VERGLEICH<span style=' color:#ff0000; '>($A5&C$2;Tabelle1!$A$1:$A$100&Tabelle1!$B$1:$B$100;0)</span>)</span>)</span>;"";INDEX<span style=' color:008000; '>(Tabelle1!$C$1:$C$100;VERGLEICH<span style=' color:#0000ff; '>($A5&C$2;Tabelle1!$A$1:$A$100&Tabelle1!$B$1:$B$100;0)</span>)</span>)}</td></tr><tr><td >B6</td><td >{=WENN(ISTFEHLER<span style=' color:008000; '>(INDEX<span style=' color:#0000ff; '>(Tabelle1!$C$1:$C$100;VERGLEICH<span style=' color:#ff0000; '>($A6&B$2;Tabelle1!$A$1:$A$100&Tabelle1!$B$1:$B$100;0)</span>)</span>)</span>;"";INDEX<span style=' color:008000; '>(Tabelle1!$C$1:$C$100;VERGLEICH<span style=' color:#0000ff; '>($A6&B$2;Tabelle1!$A$1:$A$100&Tabelle1!$B$1:$B$100;0)</span>)</span>)}</td></tr><tr><td >C6</td><td >{=WENN(ISTFEHLER<span style=' color:008000; '>(INDEX<span style=' color:#0000ff; '>(Tabelle1!$C$1:$C$100;VERGLEICH<span style=' color:#ff0000; '>($A6&C$2;Tabelle1!$A$1:$A$100&Tabelle1!$B$1:$B$100;0)</span>)</span>)</span>;"";INDEX<span style=' color:008000; '>(Tabelle1!$C$1:$C$100;VERGLEICH<span style=' color:#0000ff; '>($A6&C$2;Tabelle1!$A$1:$A$100&Tabelle1!$B$1:$B$100;0)</span>)</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>

chbahn
24.03.2009, 07:11
Vielen Dank!
Das war genau das was ich suchte!

Danke!
Christian