Spalte nach Wert durchsuchen und nächsthöheren Eintrag ausgeben.

[Tobias Reißenweber]

Hallo zusammen,

eins vorweg, ich habe gegoogelt. Ich habe auch diverses ausprobiert, aber nichts hat bisher funktioniert.

Hier die Problemstellung: Ich arbeite in einem Spirituosenbetrieb. Zur Ermiuttlung des Alkoholgehalts werden amtliche Tafelnd verwendet. Diese sind absteigend sortiert. Jedem Eintrag ist eine Dichte zugeordnet. Das sieht dann z. B. so aus (das ist ein Auszug, die vollständige Tabelle hat über 2000 Einträge):

% vol | Dichte

96,59 | 805,0
96,57 | 805,1
96,54 | 805,2
96,52 | 805,3
96,49 | 805,4
96,47 | 805,5

Ein Messergebnis von 96,5 % vol liegt dann zwischen den Werten für 96,52 und 96,49 und muss interpoliert werden. Die 96,49 finde ich zur Not über sverweis. Die 96,52 finde ich nicht.

Ich habe es probiert mit: =VERGLEICH(N6;P6:P11;-1)    Wobei N6=Messergebnis und P6:P11 = 96,59 bis 96,47. Excel liefert das Ergebnis "4" ???? Excel ermittelt also, dass es 4 Einträge mit Werten größer dem Suchwert 96,5 gibt. Schön. ich hätte aber gerne, das mir Excel den nächstgrößeren Wert angibt, also 96,52.

Ich habe auch diverses mit =index und =kkleinster ausprobiert, komme aber nicht weiter. Ich bitte um Hilfe.

[WillWissen]

Hallo,

Excel liefert das Ergebnis "5" ???? Excel ermittelt also, dass es 5 Einträge mit größeren Werten gibt.

falsch Euer Ehren. Im Vergleich gibt XL mit diesem Wert die Zeile aus der angegebenen Matrix aus, in welcher es das Ergebnis gefunden hat.

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E
1	96,59	805	Gesucht	Ergebnis
2	96,57	805,1	96,5	4	Zeile
3	96,54	805,2		96,52	%-Wert
4	96,52	805,3		805,3	Dichte
5	96,49	805,4
6	96,47	805,5

Zelle	Formel
D2	=VERGLEICH(C2;$A$1:$A$6;-1)
D3	=INDEX($A$1:$A$6;VERGLEICH(C2;$A$1:$A$6;-1))
D4	=INDEX($B$1:$B$6;VERGLEICH(C2;$A$1:$A$6;-1))

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

[Tobias Reißenweber]

Vielen Dank ! Die Formel in Zelle D3 macht was ich möchte. :19:

Damit kann ich arbeiten. Aber, dass ich auch was lerne - könnte ich erklärt bekommen, welcher Teil der Formel hier was genau macht ? =Index und =Vergleich wurden hier ja verknüpft...

[WillWissen]

Hi,

dass VERGLEICH() die Zeile in der durchsuchten Matrix angibt, habe ich dir geschrieben. Mit INDEX() liest du in einer Spalte einen gesuchten Wert aus. Die Syntax lautet: =INDEX(Matrix;Zeile;[Spalte]).
Benutze auch die Formelauswertung unter Formeln im Menü Formelüberwachung.

[Tobias Reißenweber]

ok, ich denke ich hab das verstanden. =Vergleich ermittelt die Position und =index gibt an, was in der Zelle steht.  Tip-Top :19: 

Ich habe die Formel in die Tat umgesetzt und prinzipiell auch das erhalten, was ich möchte. Für den Wert 96,5 und alle anderen Werte, die nicht in der Matrix stehen funktioniert auch alles einwandfrei. Probleme treten auf bei Messwerten, die auch in der Matrix stehen. Aber der Reihe nach.

Mir geht es letztendlich um die Ermittlung der genauen Dichte, weil ich bei schwankendem Alkoholgehalt zu meinem Kollegen sagen möchte "Bei einer Alkoholstärke von XY % vol musst du soundsoviel kg in den Behälter auf der Waage pumpen um ein bestimmtes Volumen, z. B. 1000 Liter zu erreichen."

Dazu müssen die Werte für % vol und für die Dichte interpoliert werden. um beim Beispiel von 96,5 zu bleiben: Die untere Interpolationsgrenze finde ich über =sverweis in dem ich meine Matrix in aufsteigender Reihenfolge sortiere. In diesem Beispiel ist das 96,49 % vol. Die obere Interpolationsgrenze finde ich über deine Formel in meiner absteigend sortierten Matrix. In diesem Beispiel 96,52 % vol. Soweit so gut (und wirklich hilfreich, nochmal danke). Die unter Interpolationsgrenze für die Dichte find eich über sverweis, hier 0,8054. Dann rechne ich (bzw. Excel): 0,8054+((96,52-96,5)/(96,52-96,49)/10000) und erhalte das Ergebnis 0,805467 - super, alles wie es sein soll (ich tippe natürlich nicht die Zahlen sondern lasse Excell das über die Zellen machen).

Wenn ich jetzt einen Messwert verwende, der in der Matrix steht, z. B. 96,52 statt 96,50 dann erhalte ich als Ergebnis leider #DIV/0!

Was kann ich tun ? Ist mein Kauderwelsch nachvollziehbar oder lieber eine Tabelle mit Formeln?


EDIT: Ich habs selber gefunden: =WENN(96,52-96,52=0; ....

das funktioniert !