Hallo zusammen,
ich arbeite seit gut einer Woche an einer Lösung, doch leider finde ich keine..Ich hoffe, ihr könnt mir helfen!
Folgende Problemstellung:
Suchwert = Mengenstaffel (definiert)
Spalte A = 1. Suchkriterium, Werte können mehrfach gleich vorkommen (Materialnummer)
Spalte B = 2. Suchkriterium, Werte können nur 1 mal vorkommen, abhängig von Anzahl 1. Suchkriterium (Mengenstaffel)
Spalte C = Ergebnis (Preis)
Das Problem liegt darin, dass im 2. Suchkriterium entweder
1. der gleiche Suchwert in Spalte B gefunden werden soll
2. der nächst größere Wert in Spalte B gefunden werden soll, falls nicht vorhanden
3. oder falls Suchwert >MAX-Wert in Spalte B, dann soll der MAX-Wert aus Spalte B gefunden werden
Ich habe hierzu eine Beispieldatei hinzugefügt.
Danke allen schon mal vorab!
Grüße Heily
Hallo
In Spalte A / B / C stehen keine Werte
ein Suchkriterium steht in der Zelle J3.
Wenn ich das richtig verstehe, so soll der gesuchte Werte aus Spalte F größer oder gleich diesem Wert sein.
Aber
in welcher Zelle steht das andere Suchkriterium?
Wo soll das andere Suchkriterium gesucht werden? in Spalte E?
Gruß Holger
Hallo Holger,
vielen Dank für deine Nachricht.
Ich habe eben nochmal die Beispieldatei aktualisert und hochgeladen.
Das 1. Suchkriterium ist J3
Das 2. Suchkriterium ist I3
Wenn die Materialnummer gefunden wurde, dann soll die dazugehörige Mengenstaffel gefunden werden. Allerdings sind diese unterschiedlich. Falls in Spalte F der Wert in J3 1:1 gefunden wird, dann soll der Preis ausgegeben werden. Falls J3 > als MAX-Wert in Spalte F, dann soll der Preis zum MAX-Wert der unter I3 gesuchten Mengenstaffel wiedergegeben werden.Falls J3 zwischen 2 Mengenstaffeln liegen sollte, dann den Preis zur nächst höheren Preisstaffel...
Ich hoffe, ich konnte es erklären.
Danke und Gruß
Hallo zusammen,
hier mal ein Beispiel, wie die Ergebnisse lauten sollten...
Moin
In Tabelle1 müssen die Daten sortiert sein:
1. Materialnummer aufsteigend sortiert
2. Mengenstaffel aufsteigend sortiert
Code:
=INDEX(Table1[Preis];AGGREGAT(15;6;(ZEILE(Table1[Preis])-ZEILE(Table1[[#Kopfzeilen];[Preis]]))/(Table1[Materialnummer]=I3)/(Table1[Mengenstaffel]>=J3);1))
Hallo
ich würde das dann aber in drei kleinen Schritten lösen. Finde ich eleganter wie so eine Mammut-Formel.
Gruß Holger
Nachtrag:
Enthält Matrixformel: Umrandende { geschweifte Klammern } nicht mit eingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Hallo, warum nicht einfach(er)..?
PHP-Code:
=MAX(INDEX(G2:G10*((E2:E10=I3)*F2:F10>=J3);;))
oder..:
PHP-Code:
=MMULT(AGGREGAT({14;15};6;G2:G10/(F2:F10>=J3)/(E2:E10=I3);1);{1.-1})
Zitat:3. oder falls Suchwert >MAX-Wert in Spalte B, dann soll der MAX-Wert aus Spalte B gefunden werden
Hallo Jörg ,die erste Formel erfüllt die Bedingung nicht (Ergebnis 0), die Zweite gibt dann #Zahl zurück
Gruß Holger
Hi Holger, okay, okay, aber ich denke trotzdem relativ einfach machbar..:
PHP-Code:
=MAX(INDEX(G2:G10*((E2:E10=I3)*F2:F10>=MIN(J3;MAX(INDEX(F2:F10*(E2:E10=I3);))));;))
| Arbeitsblatt mit dem Namen 'Sheet1' |
| E | F | G | H | I | J | K | L | M | N | O | P | Q |
| 1 | Materialnummer | Mengenstaffel | Preis | | | | | | | | | | AAB |
| 2 | AAB | 100 | 5,00 € | | Materialnummer | Mengenstaffel | Preis | | Max-Wert (AAB) | >= 501 | | gefunden in Zeile: | EER |
| 3 | AAB | 80 | 6,00 € | | AAB | 501 | 2,00 € | | 500 | 100.000 | | 3 | QQW |
| 4 | AAB | 500 | 2,00 € | | | | | | | | | | UUT |
| 5 | EER | 350 | 5,00 € | | | Alles in einer Formel | 2,00 € | | | | | | |
| 6 | EER | 150 | 10,00 € | | | | 2,00 € | | | | | | |
| 7 | PPU | 200 | 9,00 € | | | | | | | | | | |
| 8 | QQW | 40 | 100,00 € | | | | | | | | | | |
| 9 | QQW | 1000 | 30,00 € | | | | | | | | | | |
| 10 | UUT | 200 | 10,00 € | | | | | | | | | | |
| 11 | | | | | | | | | | | | | |
| Zelle | Formel |
| M2 | ="Max-Wert ("&I3&")" |
| N2 | =">= "&J3 |
| K3 | {=INDEX(Table1[Preis];VERGLEICH(I3&"#"&WENN(N3>M3;M3;N3);Table1[Materialnummer]&"#"&Table1[Mengenstaffel];0))} |
| M3 | {=MAX((Table1[Materialnummer]=I3)*Table1[Mengenstaffel])} |
| N3 | {=MIN(WENN((Table1[Materialnummer]=I3)*(Table1[Mengenstaffel]>=J3);Table1[Mengenstaffel];100000))} |
| P3 | {=VERGLEICH(I3&"#"&WENN(N3>M3;M3;N3);Table1[Materialnummer]&"#"&Table1[Mengenstaffel];0)} |
| K5 | {=INDEX(Table1[Preis];VERGLEICH(I3&"#"&WENN(MIN(WENN((Table1[Materialnummer]=I3)*(Table1[Mengenstaffel]>=J3);Table1[Mengenstaffel];100000))>MAX((Table1[Materialnummer]=I3)*Table1[Mengenstaffel]);MAX((Table1[Materialnummer]=I3)*Table1[Mengenstaffel]);MIN(WENN((Table1[Materialnummer]=I3)*(Table1[Mengenstaffel]>=J3);Table1[Mengenstaffel];100000)));Table1[Materialnummer]&"#"&Table1[Mengenstaffel];0))} |
| K6 | =MAX(INDEX(G2:G10*((E2:E10=I3)*F2:F10>=MIN(J3;MAX(INDEX(F2:F10*(E2:E10=I3);))));;)) |
| Achtung, Matrixformel enthalten! |
| Die geschweiften Klammern{} werden nicht eingegeben. |
| Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine. |
| Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016 |
| Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg |
Hallo shift-del ,
vielen Dank! Die Lösung werde ich mir anschauen!