Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1676to1680
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Finde ersten Werte der größer ist

Finde ersten Werte der größer ist
01.03.2019 14:37:34
Tim
Hallo liebe Herber-Excel-Gemeinde,
zuerst möchte ich mich kurz bei allen hier bedanken die sich in diesem Forum aktiv einbringen!
Ich nutze dieses Forum sehr häufig und ich konnte meine Fragen BISHER immer selbst lösen. Danke dafür!
Leider bin ich gestern auf ein Problem gestoßen bei dem ich nicht mehr weiter komme und entweder die Lösung hier nicht finde, oder nicht in der Lage bin die richtige Lösung als solche zu erkennen ;)
Mein Problem ist folgendes:
Ich habe eine aufsteigende Liste mit Preisranges (kann Lücken und Dopplungen aufweisen), ich muss nun herausfinden bei welcher Range ein Vergleichswert das erste mal überschritten wird.
Wenn ich meine Liste ohne Dopplungen aufsteigend aufbaue, habe ich mittels der Funktionen Vergleich kein Problem das richtige Ergebnis zu bekommen.
Vergleichswert 501 ==> Ergebnis Eintrag 4 (da ich immer mit dem ersten Wert der Range vergleiche.
Liste:
0 - 100 TEUR
> 100 - 250 TEUR
> 250 - 500 TEUR
> 500 - 1.000 TEUR
> 5.000 - 50.000 TEUR
Allerdings bei dem unteren Beispiel liege ich mit meinem Ergebnis immer daneben.
Beispiel:
Vergleichswert 501 ==> Ergebnis Eintrag 2 (ich verstehe auch warum ich eine 2 erhalte, da ich mit Vergleich und Vergleichstyp = 1 arbeite).
Ich benötige allerdings hier als Ergebnis Eintrag
Liste mit Ranges:
> 250 - 500 TEUR
> 250 - 500 TEUR
> 5.000 - 50.000 TEUR
> 5.000 - 50.000 TEUR
> 5.000 - 50.000 TEUR
Nun muss ich allerdings trotzdem irgendwie heraus bekommen, dass ich mit 501 das Ergebnis 3 bekomme.
Die Liste die hier aufgebaut wird kann immer unterschiedlich sein. Sie ist immer aufsteigend, kann aber Sprünge von Range zu Range haben und kann doppelte Einträge haben.
Ich hoffe ich konnte mich verständlich ausdrücken.
Meine jetzige Vorgehensweise sieht wie folgt aus:
1.) ich baue mir für die Liste mit Teilstrings ein Hilfsliste auf, die nur die linken Werte der Ranges beinhaltet:
250
250
5000
5000
5000
2.) anschließend gehe ich mit einem Vergleich auf diese Liste.
Meine Formel:
=VERGLEICH(schwellWfin-0,0000001;Hilfsspalte_finanzielle_Auswirkungen;1)
Erläuterungen zur Formel:
schwellWfin = Name für meinen Vergleichswert
-0,0000001 = damit ich einfach auf größer und nicht größer/gleich vergleichen kann
Hilfsspalte_finanzielle_Auswirkungen = ist meine Hilfsspalte ;)
Besten Dank für eure Hilfe!
Gruß Tim

14
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: sollte kein Problem sein ...
01.03.2019 16:17:54
neopa
Hallo Tim,
... doch warum stellst Du nicht (D)eine Beispielarbeitsmappe hier ein? Nachstellen ist einfach etwas zu aufwendig.
Gruß Werner
.. , - ...
AW: sollte kein Problem sein ...
01.03.2019 16:30:40
Tim
Ich habe die Datei als XLS abspeichern müssen, ich hoffe damit gingen keine Formeln oder Namen kaputt.
Ich probiere mich gerade auf den Zellen D27 bzw. E27 aus (die Zellen darüber sind quasi Hilfszellen, damit meine Formeln lesbar bleiben).
Im Endeffekt gebe ich in B17 meinen Schwellwert ein und das Ergebnis muss die Range aus A3:A12 ausgegeben werden BEVOR der Schwellwert überschritten wird.
In der aktuellen Konstellation dann eben:
6000: Range 4-30
500: Range 0-4 (da kleinste Range)
Datei: https://www.herber.de/bbs/user/128030.xls
Anzeige
AW: dazu nachgefragt ...
01.03.2019 16:44:27
neopa
Hallo Tim,
... normalerweise lade ich mir xls-Dateien nicht mehr aus dem Internet. Wenn doch, dann sorge ich dafür, das evtl. Makros eliminiert werden. Sind solche in Deiner Datei vorhanden und spielen eine Rolle?
Gruß Werner
.. , - ...
AW: dazu nachgefragt ...
04.03.2019 09:27:15
Tim
Hi neopa C,
sorry dafür! Nein für die Berechnung ist kein Makro nötig. Ich mache (derzeit) alles mit Excel-eigenen Formeln.
Da ich hier an ein Problem gestoßen bin, welches ich (momentan) nicht alleine bewältigen kann, habe ich schon überlegt mich via VBA zu behelfen. Aber wie gesagt, an der Stelle bin ich noch nicht.
Hier ist eine Makro-freie Datei: https://www.herber.de/bbs/user/128080.xls
Besten Dank und Gruß,
Tim
Anzeige
AW: dazu nachgefragt ...
04.03.2019 09:29:00
Tim
Sorry,anstatt neopa C meine ich natürlich Werner ;)
AW: dazu nachgefragt ...
04.03.2019 14:22:35
Tim
Ich habe mal eine Beispiel Grafik mit meinen erwarteten Ergebnissen zusammengestellt:
Ziel ist es die Range zu erhalten, bei der der Schwellwert gerade noch nicht erreicht wird (Ausnahme die kleinste Range).
AW: noch unklar ...
05.03.2019 08:59:38
neopa
Hallo Tim
... u.a. wieso ist in Deiner Grafik das "x" für 500 in Zeile 2 für 50000 in Zeile 4 und für 50001 in Zeile 6?
Gruß Werner
.. , - ...
AW: noch unklar ...
05.03.2019 09:30:45
Tim
Hallo Werner,
das liegt daran weil ich immer die Range suche bei der der Schwellwert gerade noch nicht erreicht wird.
Der Schwellwert von 500 wird mit der dritten Range erreicht, daher muss mein Ergebnis Range zwei sein.
Der Schwellwert von 501 wird mit der dritten Range nicht erreicht und die vierte Range liegt weit über dem Schwellwert, daher wird hier die dritte Range ausgegeben.
Ah, du hast Recht. Bei 50000 ist mir ein Fehler unterlaufen. Hier erwarte ich die Range drei - mein Fehler!
Bei Schwellwert 50001 erwarte ich Range 6, da die letzte verfügbare Range den Schwellwert noch nicht überschreitet.
Ich habe mein Problem übrigens gestern teilweise lösen können (teilweise daher, weil ich meinen Schwellwert nur mit der rechten Seite der Range vergleiche, daher bin ich blind für Werte die am linken Rand der Ranges liegen und ich gleichzeitig Sprünge zwischen den Ranges habe - hier werde ich aber einfach nur definieren, dass man Schwellwerte auswählen soll, die am rechten Rand der Ranges liegen, da ich sonst ohne weiteren größeren Aufwand das Problem nicht lösbar sehe)
Ich arbeite mit einer Matrixformel:
{=WENN(MIN(WENN(schwellWfin<=Hilfsspalte_finanzielle_Auswirkungen; ZEILE(Hilfsspalte_finanzielle_Auswirkungen);""))-18=0;1;MIN(WENN(schwellWfin< =Hilfsspalte_finanzielle_Auswirkungen;ZEILE(Hilfsspalte_finanzielle_Auswirkungen);""))-18) }
Im Endeffekt lasse ich mir in einem Array alle rechten Zahlen meiner Ranges ausgeben, die größer oder gleich meinem Schwellwert sind, lasse mir davon dann die Zeilen ID ausgeben und nehme dann mit MIN() die kleinste Zeile.
Anzeige
AW: noch unklar ...
05.03.2019 09:50:12
Tim
Hat jemand eine Idee wie ich das ganze ohne Hilfsspalte lösen kann?
In der Hilfsspalte stehen momentan einfach nur die rechten Zahlen meiner Ranges...
AW: dies ist möglich
05.03.2019 11:52:00
neopa
Hallo Tim,
... wenn Du die zutreffenden Werte in der Formel mit WECHSELN(WECHSELN(TEIL(B$3:B$7;FINDEN("-";B$3:B$7)+1;99);"TEUR";"");".";"")+0 ermittelst.

Ich würde allerdings anstelle einer klassischen Matrixformel mit INDEX() und AGGREGAT() auswerten.
Gruß Werner
.. , - ...
AW: dies ist möglich
06.03.2019 14:40:14
Tim
Hallo Werner,
Ich hatte ursprünglich mit INDEX und VERGLEICH gearbeitet, aber die Funktion VERGLEICH hat mir leider bei der Kombination aus doppelten Ranges und Sprüngen zwischen den Ranges immer fehlerhafte Ergebnisse ausgeworfen.
Wie würdest du denn AGGREGAT anwenden? Ich habe mir die Office Hilfe dazu durchgelesen, finde aber keine Möglichkeit den >= Vergleich zu machen, womit ich ja den Schwellwert und die Range vergleichen muss.
Gruß Tim
Anzeige
AW: dazu ...
06.03.2019 19:46:07
neopa
Hallo Tim
... das gleiche Ergebnis wie Deine klassische Matrixformel ermittelt folgende Formel:
=WENNFEHLER(AGGREGAT(14;6;ZEILE(A1:A12)/(schwellWfin&gt=WECHSELN(WECHSELN(TEIL(B$3:B$12;FINDEN("-";B$3:B$12)+1;99);"TEUR";"");".";"")+ZEILE(B$3:B$12)%%);1);1)
die nicht den spez. Formelabschluss benötigt.
Gruß Werner
.. , - ...
AW: dazu ...
07.03.2019 14:46:46
Tim
Hi Werner,
das Ding ist echt erstaunlich! Es hat mich allerdings ein paar Stunden gebraucht bis ich durch die Logik deiner Formel durchgestiegen bin! Mit der Division durch Wahrheitswerte und den Zeilenbereich A1 - A12 einfach nur zu nutzen um die höchste Zeilenzahl der Wahrheitswerte als numerischen Wert zu bekommen, ist super kreativ!
Danke für deine Hilfe! Somit brauche ich keine Hilfsspalten mehr und keine Matrixformel...
Ich könnte mir auch vorstellen, dass das Ganze nun performanter läuft als vorher (nicht, dass das hier tatsächlich einen Unterschied machen würde)...
Ich habe die Formel nun auch für die immateriellen Auswirkungen angepasst und es funktioniert ebenso hervorragend!
Besten Dank und Daumen hoch ;)
Anzeige
AW: bitte, freut mich owT
07.03.2019 15:57:36
neopa
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige