[Hilfe] Aus einer Liste von Werten einen bestimmten Wert auslesen

[strg+1]

Hallo zusammen,

ich möchte schon seit längerem eine Automatisierung in Excel umsetzen, weiß aber leider nicht wie ich das am besten machen soll. Daher hoffe ich hier auf Hilfe :)

Beispiel:

Ich habe eine Liste von 2455 Werten in Spalte B, beginnend mit Zeile 3. Diese sind nicht aufsteigend sortiert, und bewegen sich in einem Bereich von -0,011 bis 88,754 (wobei die Negativwerte eigentlich zu vernachlässigen sind). Diese Rohdaten besitzen dabei relativ viele Nachkommastellen. Nun hätte ich gerne, dass Excel mir automatischen den Wert x ausliest und mir die Zeile, in welchem dieser Wert steht, wiedergibt. Wert x hängt von zwei Bedingungen ab:

1. Wie erwähnt sind die Rohdaten nicht sortiert, d.h. sie steigen und fallen. Der Wert x soll aus dem ersten Anstieg der Werte entnommen werden
   
2. Der Wert x soll der Wert mit der geringsten Abweichung zu 9,00 sein. Aber, wie in 1. formuliert, eben nicht aus allen 2455 Werten sondern aus dem ersten Anstieg der Werte von 0 -> 9
   

Mit ein wenig Google-Recherche konnte ich mir breits einen Befehl kopieren, der mir aus allen Werten den Wert mit der geringsten Abweichung zu 9,00 liefert.
Dieser sieht wie folgt aus: =KKLEINSTE(B:B;ZÄHLENWENN(B:B;"<"&I24)+ZÄHLENWENN(B:B;I24+MIN(ABS(I24-KKLEINSTE(B:B;HÄUFIGKEIT(B:B;I24)+{0.1})))))     // I24 ist hierbei der Bezug zu 9,00

In meinem Beispiel wird nun "leider" der Wert aus Zeile 1179 ausgegeben. Nach manueller Suche zeigt sich, der erste Anstieg in Richtung 9,0 findet in den Zeilen 274 und 275 statt:
Zeile: 274 -> Wert: 8,904568 -> Differenz: 9,0 - 8,904568 = 0,0954320000000006
Zeile: 275 -> Wert: 9,035229 -> Differenz: 9,035229 - 9 = 0,0352289999999993 -> ~0,035 < 0,095 -> Zeile 275 maßgebend

Um das ganze etwas zu veranschaulichen Füge ich einen Screenshot meines Beispiels mit an. In diesem habe ich ein Diagramm eingebunden, welches den steigenden und fallenden Verlauf der Werte darstellt.

Wichtig:

• die Position der Rohdaten darf nicht verändert werden, sprich keine Sortierung o.ä.
  
• hier handelt es sich wirklich nur um ein Beispiel. Im tatsächlichen Anwendungsfall beginnt der Anstieg der Werte nicht zwingend wie hier bei etwa Zeile ~160-170. Das heißt eine Begrenzung der Funktion auf den entsprechenden Zeilenbereich in welchem hier der Anstieg stattfindet, würde mich nicht zum Ziel bringen, da dies sonst in unterschiedlichen Anwendungsfällen wieder manuell angepasst werden müsste
  
• schön wäre, wenn sich die Funktion allgemein auf B:B bezieht, da die Liste u.U. auch mehr als 2455 Werte umfassen kann
  

In meiner Vorstellung müsste Excel in Zelle B3 mit dem Prüfen beginnen und sich nach unten arbeiten (B4, B5, usw.) bis das erste mal ein Wert > 9,0 ist. Dann müsste Excel nurnoch den Wert aus der Zeile in der gestoppt wurde (>9) sowie aus der Zeile davor (<9) mit 9,0 differenzieren und den Wert mit der geringsten Abweichung wählen. Also so, wie ich manuell vorgegangen bin. Ist das mit "normalem" Excel möglich oder bedarf es dafür vba?

Über Hilfe und Anregungen wäre ich echt froh!

Besten Dank :)

[Fennek]

Hallo,

bei den Anforderungen und der Formel rechnet Excel sicher richtig.

Aber: in der Graphik wird der erste Schnittpunkt mit der 9-er Achse gesucht. Dafür braucht es aber eine andere Formel.

Also bleiben für mich noch einige Fragen, bevor nach passenden Formeln bzw Hilfsspalten gesucht wird.


mfg

[strg+1]

Hallo,

bei den Anforderungen und der Formel rechnet Excel sicher richtig.

Aber: in der Graphik wird der erste Schnittpunkt mit der 9-er Achse gesucht. Dafür braucht es aber eine andere Formel.

Also bleiben für mich noch einige Fragen, bevor nach passenden Formeln bzw Hilfsspalten gesucht wird.


mfg

Vielleicht habe ich mich unklar ausgedrückt bzw. evtl. ist das rote "falsch" im Screenshot missverständlich.
Du hast natürlich vollkommen recht, die Funktion =KKLEINSTE(B:B;ZÄHLENWENN(B:B;"<"&I24)+ZÄHLENWENN(B:B;I24+MIN(ABS(I24-KKLEINSTE(B:B;HÄUFIGKEIT(B:B;I24)+{0.1}))))) macht genau was sie soll und Excel rechnet richtig. Alle Werte (2455) aus Spalte B:B werden betrachtet und der Wert mit der geringsten Abweichung zu 9,0 wird ausgegeben.

Nur ist das eben nicht die Lösung für mein Problem, da dieser Wert nicht Anforderung 1. & 2. erfüllt.

Wenn du mir die für dich offenen Fragen nennst, könnte ich ja versuchen mehr Infos beizusteuern? :)

[Fennek]

Meine erste Reaktion beim Lesen war "warum so eine komplizierte Formel und nicht (mehrere) Hilfsspalten".

Um den ersten Anstieg zu untersuchen, braucht es eine Hilfsspalte die den ersten Wechsel von <9 zu >9 erfaßt.

Ohne Prüfung:

Code:

In Spalte C ab C2: "=und(B1<9;B2<9; C1=1)"

[strg+1]

Meine erste Reaktion beim Lesen war "warum so eine komplizierte Formel und nicht (mehrere) Hilfsspalten".

Um den ersten Anstieg zu untersuchen, braucht es eine Hilfsspalte die den ersten Wechsel von <9 zu >9 erfaßt.

Ohne Prüfung:

Code:

In Spalte C ab C2: "=und(B1<9;B2<9; C1=1)"

Ah ok. Ich hab das Beispiel oben auf die relevante Spalte B:B begrenzt, um das ganze etwas vereinfachter darzustellen. In Wirklichkeit stehen in den Spalten C bis ... (kommt drauf an) weitere Rohdaten, wobei sich die Werte in jeder Zeile aufeinander beziehen. Daher würde ich ungern noch weitere Hilfsspalten hinzufügen, sonst wird die Tabelle nur noch unübersichtlicher.

Ich hab in der Realität zwei Fälle:
a) die Werte steigen bis zu einem Peak an und das wars. Hierfür benutze ich bereits die "komplizierte Formel". Mit dieser Suche ich mir den gewünschten Wert heraus (also z.B. der Wert mit der geringsten Abweichung zu 9,0) und lasse mir dann mit =VERGLEICH("BEZUG";B:B;0) die entsprechende Zeile ausgeben in welcher der Wert steht. Dann nutze ich die Index-Funktion um mir aus der selben Zeile die zugehörigen Werte aus den Spalten C, D, E, usw... ausgeben, da ich diese für weitere Berechnungen brauche.
b) der Fall aus dem Beispiel. Manchmal habe ich eben Werte in dieser steigenden und sinkenden Abfolge. Ursprünglich wollte ich meine bereits funktionierende Excel-Datei für Fall a) auf den Fall b) anwenden. Dabei tritt eben häufiger das Problem auf, dass sich der Wert mit der geringsten Abweichung nunmal nicht im ersten Anstieg befindet. Dieser Wert ist aber leider für mich der relevante.

Und wie gesagt, manchmal sind die Rohdaten nochmals umfangreicher und ich habe bis zu 5000 Werte. Daher mein Wunsch nach einer Formel die sich ebenfalls komplett auf B:B bezieht ohne das manuell ein Suchbereich eingegrenzt werden muss oder Hilfsspalten hinzugefügt und oder angepasst werden müssen. Im Ablauf kopiere ich die Rohdaten aus einer Text-Datei in eine Excel-Vorlage ein, diese Vorlage würde ich gerne so weit es geht automatisieren.

Aber evtl. funktioniert das auch überhaupt nicht, kann ja auch die "Lösung" der Frage sein

[Fennek]

Ist es möglich ca 30-50 Zeilen des echten Datensatzes um den kritischen Wert von 9 herum zu zeigen?

[strg+1]

Sorry für die späte Rückmeldung, war die Tage mit anderem Kram beschäftigt.

In dem Screenshot der Beispieldatei habe ich alles "unnötige" rausgenommen, die Werte stammen allerdings aus meinem tatsächlichen Arbeitsumfeld. Möchte ungern weitere Rohdaten veröffentlichen, hoffe das ist verständlich. Ich konnte die Sache um übrigen nun selbst mithilfe von VBA lösen. Da ich relativ selten mit VBA arbeite, weiß ich nicht ob der Code unfassbar charmant ist aber es funktioniert immerhin. Für alle die es interessieren sollte:

Code:

Sub Test()

Dim Zeile As Integer
Dim x As Double

Dim x1 As Double
Dim x2 As Double

Dim Delta1 As Double
Dim Delta2 As Double

Zeile = 3

Do
    x = Worksheets("Tabelle1").Cells(Zeile, 2).Value        'Wert in Zelle B3 auslesen
    Zeile = Zeile + 1                                       'Zeile schrittweise erhöhen
   
Loop Until x > 9                                            'Abbruchkriterium

x1 = Worksheets("Tabelle1").Cells(Zeile - 1, 2).Value       'x1 = erster Wert < 9 in der aktuell gespeicherten Variable "Zeile"-1
x2 = Worksheets("Tabelle1").Cells(Zeile, 2).Value           'x2 = erster Wert >9 in der aktuell gespeicherten Variable "Zeile"

Delta1 = 9 - x1                                             'Differenz aus dem Wert <9 und 9
Delta2 = x2 - 9                                             'Differenz aus dem Wert >9 und 9

If Delta1 < Delta2 Then                                     'Prüfen welche Differenz kleiner ist
    Sheets("Tabelle1").Range("T18").Value = x1              'Ausgabe von x1 in Zelle T18, falls die erste Differenz kleiner ist
    Sheets("Tabelle1").Range("U18").Value = Zeile - 1       'Ausgabe der zugehörigen Zeile in Zelle U18, falls die erste Differenz kleiner ist
Else
    Sheets("Tabelle1").Range("T18").Value = x2              'Ausgabe von x2 in Zelle T18, falls die zweite Differenz kleiner ist
    Sheets("Tabelle1").Range("U18").Value = Zeile           'Ausgabe der zugehörigen Zeile in Zelle U18, falls die zweite Differenz kleiner ist
End If

End Sub

Damit ist das Ding durch ;)