PDA

Vollständige Version anzeigen : Excel soll per Sverweis nähere Zahl anzeigen


reyman
28.06.2015, 22:47
Hi liebe Com,

ich suche nach einer geeigneten Lösung zu folgenden Problem.

Ich habe in der angehängten Tabelle Folgendes Problem.

Im Datenblatt 1 sind alle Daten zum Tester 1 vorhanden.

Im Datenblatt 2 ist der Rechner der die Daten auslesen soll.

Soweit funktioniert das ganze auch. Jedoch zeigt er nicht die näher liegende Zahl an, sondern die welche kleiner als die angegebene Zahl ist.

Beispiel:
So wie im Datenblatt angegeben habe ich die Zahl "360" eingegeben. In Spalte 5 zeigt er 330 an. Laut der Daten wäre aber 60% mit 387 die näherliegende Zahl in dieser Spalte.

Wie setze ich das um ? Das der Rechner sowohl nach unten als auch nach oben die Zahlen Checkt.

MFG

reyman

Pit987
29.06.2015, 07:42
Hallo reyman!
Die Lösung sollte in SVERWEIS selbst liegen.
Da gibt es einen Parameter für Übereinstimmung/annähernd.

Viel Erfolg
Pit

RPP63neu
29.06.2015, 08:07
Hallo Pit!
Ich glaube, Du hast die Problematik nicht ganz verstanden.
Wenn der SVERWEIS 360 in der Reihe 280 337 379 416,7 sucht,
ermittelt er mit dem 4. Argument
Wahr / 1 / nix, da default: 337
Falsch / 0: #NV
Er findet die 337 (Diff. zum Suchwert: -23), obwohl die 379 mit der Diff. +19 "näher dran" ist.

@reyman:
Dieses Verhalten ist normal, siehe Excel-Hilfe:
Wenn Bereich_Verweis entweder WAHR oder nicht belegt ist, wird eine genaue oder ungefähre Entsprechung zurückgegeben. Wird keine genaue Entsprechung gefunden, wird der nächstgrößere Wert zurückgegeben, der kleiner als Suchkriterium ist.
Ich muss mal schauen, ob man da mit einer Matrixformel zur Lösung kommen kann ...

Gruß Ralf

RPP63neu
29.06.2015, 08:22
Hi!
Zwischenstand:
Berechnung siehe wie folgt:

<table border='1' cellspacing='0' cellpadding='2' valign='middle' colspan='6' style='font-family:Calibri; color:#000000; background-color:#FFFFFF; font-size:11px; font-weight:normal; font-style:normal; '><colgroup><col width='28pt'><col width='36,75pt'><col width='36,75pt'><col width='21pt'><col width='50,25pt'><col width='60pt'></colgroup><tr style='background-color:#cacaca'><td>*</td><td align='middle'>A</td><td align='middle'>B</td><td align='middle'>C</td><td align='middle'>D</td><td align='middle'>E</td></tr><tr><td style='background-color:#cacaca' align='middle'>1</td><td align='right' >171,02</td><td align='right' >188,98</td><td align='right' >*</td><td align='left' >Suchwert</td><td align='right' >360</td></tr><tr><td style='background-color:#cacaca' align='middle'>2</td><td align='right' >212,93</td><td align='right' >147,07</td><td align='right' >*</td><td align='left' >Minimum</td><td align='right' >27</td></tr><tr><td style='background-color:#cacaca' align='middle'>3</td><td align='right' >254,84</td><td align='right' >105,16</td><td align='right' >*</td><td align='left' >Zeile</td><td align='right' >6</td></tr><tr><td style='background-color:#cacaca' align='middle'>4</td><td align='right' >296,75</td><td align='right' >63,25</td><td align='right' >*</td><td align='left' >Treffer</td><td align='right' >387</td></tr><tr><td style='background-color:#cacaca' align='middle'>5</td><td align='right' >330</td><td align='right' >30</td><td align='right' >*</td><td align='right' >*</td><td align='right' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>6</td><td align='right' >387</td><td align='right' >27</td><td align='right' >*</td><td align='right' >*</td><td align='right' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>7</td><td align='right' >429</td><td align='right' >69</td><td align='right' >*</td><td align='right' >*</td><td align='right' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>8</td><td align='right' >466,7</td><td align='right' >106,7</td><td align='right' >*</td><td align='right' >*</td><td align='right' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>9</td><td align='right' >499,7</td><td align='right' >139,7</td><td align='right' >*</td><td align='right' >*</td><td align='right' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>10</td><td align='right' >605</td><td align='right' >245</td><td align='right' >*</td><td align='right' >*</td><td align='right' >*</td></tr></table><br><table border='3' cellspacing='0' cellpadding='2' valign='middle' colspan='2' style='table-layout:auto; color:#000000; background-color:#FFFFFF; font-family:Calibri; font-size:10px;'><colgroup><col width='40pt'><col></colgroup><tr style='background-color:#eeaaaa'><td>Zelle</td><td>Formel</td></tr><tr><td>B1</td><td><Span style='color:#222222'>=ABS</Span><Span style='color:#0000DD'>(E$1-A1)</Span><Span style='color:#222222'></Span></td></tr><tr><td>E2</td><td><Span style='color:#222222'>=MIN</Span><Span style='color:#0000DD'>(B:B)</Span><Span style='color:#222222'></Span></td></tr><tr><td>E3</td><td><Span style='color:#222222'>=VERGLEICH</Span><Span style='color:#0000DD'>(E2;B:B;0)</Span><Span style='color:#222222'></Span></td></tr><tr><td>E4</td><td><Span style='color:#222222'>=INDEX</Span><Span style='color:#0000DD'>(A:A;E3)</Span><Span style='color:#222222'></Span></td></tr></table>

Jetzt muss das Ganze nur noch in einer Formel verschwurbelt werden ... :rolleyes:

Gruß Ralf

RPP63neu
29.06.2015, 08:42
Ein wenig stolz bin ich ja schon über meine Lösung, :p
zumal ich eigentlich kein Formelcrack bin ... :rolleyes:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:49px;" /><col style="width:28px;" /><col style="width:28px;" /><col style="width:49px;" /></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></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">171,02</td><td >&nbsp;</td><td style="text-align:right; ">360</td><td style="text-align:right; ">387</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">212,93</td><td >&nbsp;</td><td style="text-align:right; ">200</td><td style="text-align:right; ">212,93</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">254,84</td><td >&nbsp;</td><td style="text-align:right; ">190</td><td style="text-align:right; ">171,02</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">296,75</td><td >&nbsp;</td><td style="text-align:right; ">460</td><td style="text-align:right; ">466,7</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">330</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; " >6</td><td style="text-align:right; ">387</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; " >7</td><td style="text-align:right; ">429</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; " >8</td><td style="text-align:right; ">466,7</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; " >9</td><td style="text-align:right; ">499,7</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; " >10</td><td style="text-align:right; ">605</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 >D1</td><td >{=WENNFEHLER(<span style=' color:008000; '>INDEX(A:A;<span style=' color:#0000ff; '>VERGLEICH(<span style=' color:#ff0000; '>MIN(<span style=' color:#804000; '>ABS(A$1:A$10-C1)</span>)</span>+C1;A:A;0)</span>)</span>;<span style=' color:008000; '>INDEX(A:A;<span style=' color:#0000ff; '>VERGLEICH(C1;A:A)</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.8 </a>

Formel aus D1 nach unten ziehen.

Gruß Ralf

reyman
29.06.2015, 20:47
Ich habe jetzt einmal diese Formel auf die hochgeladene Datei angewendet. Hab ich alles richtig gemacht ? Weil ich bekomme bei der Zahl "330" immernoch den Wert 280 statt 337 raus.

Und im Rechner soll ja auch der Prozentwert mit der Zahl übereinstimmen.

=WENNFEHLER(INDEX(Daten!B3:B12;VERGLEICH(MIN(ABS(B$1:B$12-A3))+A3;Daten!B3:B12;0));INDEX(Daten!B3:B12;VERGLEICH(A3;Daten!B3:B12)))

Diese Formel habe ich auf die Zelle D3 im Datenblatt "Rechner" angewandt.




Aber erstmal vielen Dank für deine Mühe.

reyman
30.06.2015, 23:38
Okay habe es einfach umgedreht. Denke das stimmt so.

Ich habe deine Formel:

=WENNFEHLER(INDEX(Daten!B52:B71;VERGLEICH(MIN(ABS(Daten!B52:B71-A33))+A33;Daten!B52:B71;0));INDEX(Daten!B52:B71;VERGLEICH(A33;Daten!B52:B71)))

in D3 geschrieben und in C3 habe ich :

=SVERWEIS(D3;Daten!A3:H12;7)

geschrieben. Somit Sucht D3 nach der näheren Zahl in A3. Und C3 sucht die Zahl in D3 und ordnet sie dem richtigen Prozentwert ein.

Vielen Dank für deine Hilfe.


MFG

reyman