Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1640to1644
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

Wert in Intervall suchen + weitere Bedingung

Wert in Intervall suchen + weitere Bedingung
22.08.2018 15:51:12
Gode
Hallo,
ich suche eine Formel, die mir über 2 Kriterien aus einem Bereich einen Wert zurückgibt.
a) der nächst kleinere Intervallwert
b) der Wert der meinem Suchwert am nächsten liegt
Siehe Beispiel:
https://www.herber.de/bbs/user/123499.xlsx
Wäre toll wenn hier jemand eine Idee hätte!
Vielen Dank im Voraus!
Gode

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: mit AGGREGAT() ...
22.08.2018 16:04:47
neopa
Hallo Gode,
.... so in G2: =AGGREGAT(14;6;B2:B99/(B2:B99
=AGGREGAT(15;6;B2:B99/(B2:B99>E2);1)

Gruß Werner
.. , - ...
AW: richtig gestellt ...
22.08.2018 16:09:59
neopa
Hallo,
... so:
 ABCDEFG
1IDMenge     
2A1 A8Rückgabe:5
3A5     
4A10 A8Rückgabe10
5A100     
6B2     
7B5     
8B20     
9B80     
10       

Formeln der Tabelle
ZelleFormel
G2=AGGREGAT(14;6;B2:B99/(B2:B99<E2)/(A2:A99=D2); 1)
G4=AGGREGAT(15;6;B2:B99/(B2:B99>E4)/(A2:A99=D4); 1)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
Anzeige
AW: richtig gestellt ...
22.08.2018 16:35:52
Gode

Hallo Werner,
vielen Dank schonmal! Passt auf alle Fälle super für meinen ersten Fall!
Beim 2. hätte ich aber gerne den Wert zurück, der am nächsten am Suchwert dran ist:
z.B. D4=B und E4=8 -> sollte mir ebenfalls den Wert 3 zurückliefern, da 3 näher an 8 ist ...  _
ich bekomme aber den nächst höheren Wer, also 20 zurück ... vll. gibts ja dafür auch einen Weg
BTW ... scheint mir die Aggregat-Funktion recht mächtig ... werde ich mich auf jeden Fall mal  _
mit beschäftigen.
Und vielen Dank nochmal für die schnelle Hilfe!
Gruß
Gode

AW: Wert der am nächsten dem Suchwert ist ...
22.08.2018 16:49:54
neopa
Hallo Gode,
... z.B. (vorab, es geht mit einer echten klassischen Matrixformel etwas kürzer, es geht aber auch) so:
 ABCDEFG
2A1 A8Rückgabe:5
3A5     
4A10 B8Rückgabe3
5A100     
6B2     
7B3     
8B20     
9B80     
10       

Formeln der Tabelle
ZelleFormel
G2=AGGREGAT(14;6;B2:B99/(B2:B99<E2)/(A2:A99=D2); 1)
G4=AGGREGAT(15;6;B2:B9/(AGGREGAT(15;6;ABS(B2:B9-E4)/(A2:A9=D4); 1)=ABS(B2:B9-E4))/(A2:A9=D4); 1)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
Anzeige
AW: Wert der am nächsten dem Suchwert ist ...
22.08.2018 17:26:39
Gode
Hallo Werner,
puhhh ... ich hab die Formel in meinem Excel so implementiert und funktioniert!! ... aber verstehen tu ich's ehrlich gesagt nicht ... vll. mal in Muße und Ruhe anschauen und nicht in der Bürohektik!
Auf alle Fälle nochmal vielen Dank für deine Hilfe!!!!!
Gruß
Gode
AW: dazu viellieicht folgendes ...
22.08.2018 17:54:38
neopa
Hallo Gode,
... eine derartige Formel ist für jemanden, der sein Excellevel mit "Basiswissen" angibt, sicherlich auch nicht leicht zu verstehen.
Wenn Du Dich wirklich damit mal auseinandersetzen willst, dann wisse, es handelt sich hier im engeren Sinn um eine Matrixformel, die sich von einer klassischen Matrixformel dadurch unterscheidet, dass sie hier nicht den spez. Matrixformelabschluss bedarf.
Mehr zu klassischen Matrixformeln sieh mal hier:
https://www.online-excel.de/excel/singsel.php?f=26 Dazu nimm Dir aber die notwendige Zeit.
Anschließend betrachte zunächst die einzelnen Formelteile: also (A2:A9=D4) dann ABS(B2:B9-E4) z.B. durch Markierung in der Eingabezeile und der Betätigung mittels [F9]
Danach sieh in der MSO-Hilfe, was da zu AGGREGAT() steht. Leider nicht wirklich ausreichend genug) aber ausreichend für die Erklärung die ersten beiden Argumente.
Dann schreib mal in eine Extra-Zelle die "innere" AGGREGAT()-Formel so:
=(AGGREGAT(15;6;ABS(B2:B9-E4)/(A2:A9=D4);1) . Diese Teilformel dient, wie Du leicht siehst dazu, die kleinste Absolut-Differenz aller entsprechenden Mengenwerte zum Vorgabewert zu ermitteln. Dieses Zwischenergebnis wird in der "äußeren AGGREGAT() -Formel mit allen Mengen-Differenzen der jeweiligen ID gemäß D4 zum Vorgabewert in E4 verglichen. Danach erhält man die gesuchte Menge.
Mach Dir keine Gedanken, wenn Du dazu viele Stunden und mehr brauchen solltest. Das ging den meisten so.
Gruß Werner
.. , - ...
Anzeige
ohne Aggregat wohl verständlicher
22.08.2018 19:48:28
WF
Hi,
folgende Arrayformeln:
{=MAX((B2:B19<E2)*(A2:A19=D2)*B2:B19)}
und:
{=MIN(WENN(A2:A19="A";WENN(ABS(B2:B19-E2)=MIN(WENN(A2:A19="A";ABS(B2:B19-E2)));B2:B19)))}
WF

305 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige