Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Spalte nach Wert durchsuchen und nächsthöheren Eintrag ausgeben.
#1
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.
Antworten Top
#2
Hallo,

Zitat: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'
ABCDE
196,59805GesuchtErgebnis
296,57805,196,54Zeile
396,54805,296,52%-Wert
496,52805,3805,3Dichte
596,49805,4
696,47805,5

ZelleFormel
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
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
[-] Folgende(r) 1 Nutzer sagt Danke an WillWissen für diesen Beitrag:
  • Tobias Reißenweber
Antworten Top
#3
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...
Antworten Top
#4
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.
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
Antworten Top
#5
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 !
Antworten Top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste