in den rechten grünen Feldern M15 / M18 und M21 möchte ich immer Wert aus Spalte I haben der dann zum Ergebnis aus K15 / K18 und K21 passt.
Hier soll keine Wertung mehr vorgenommen werden sondern lediglich der Inhalt der Zelle die halt zwei Spaltenfelder weiter rechts steht ausgegeben werden.
Das wäre dann bei Auswahl blau in "K7" und 3 in "K10" bezogen auf das Ergebnis 14 aus G19 als günstigster Preis dann 77,77 aus I19
hab versucht das mit Adresse und Bereich.Verschieben zu lösen (ermitteln der Adresse und den Wert dann über Bereich.Verschieben auszulesen) leider zeigt mir dann die Adresse eine für mich unlogische Adresse (AB...) an
gibt es keine Funktion die auf das dynamische Ergebnis reagiert?
Mit =BEREICH.VERSCHIEBEN(K15;0;2) wird die Reale K15 angesprochen und nicht die dynamisch gefundene Adresse.
leider nicht ganz so,
mit dem sverweis wird ja nur der Wert (hier 14) gesucht und dann der Preis zwei Felder weiter ausgegeben. Ohne die zuvor per Kriterium ermittelte Position zu berücksichtigen.
Wollte das aber so haben, dass die Zellposition aus dem ermittelten Aggregat (Kriterien aus K7 und K10) als Ausgangszelle genommen wird.
Was aber schon zur nächsten Problematik führt, wie kann ich denn falls es zwei gleiche Preise als günstigst / zweitgünstigst und drittgünstigst berücksichtigen - kann ich bei K16 dann auch zwei Lieferant B+C+n ausgeben lassen?
Zitat:mit dem sverweis wird ja nur der Wert (hier 14) gesucht und dann der Preis zwei Felder weiter ausgegeben. Ohne die zuvor per Kriterium ermittelte Position zu berücksichtigen.
Ändere mal in G14 den Preis auf 3 und beobachte, was in Spalte K und M passiert.
Zitat:wie kann ich denn falls es zwei gleiche Preise als günstigst / zweitgünstigst und drittgünstigst berücksichtigen
Hallo Will Wissen,
probiert hab ich schon - indem ich einfach weiter oben nochmals den gleichen Wert eingegeben habe und dann natürlich die Problematik der doppelten Werte aufgekommen ist.
Ich schau mir den Link gleich mal an.
Danke schon mal!
anstatt in jeder Zelle die Preise zu durchsuchen kann man in einer Hilfsspalte die Zeile ermitteln und mit der Index-Funktion die Werte.
Das Problem mit den doppelten Werten ist damit auch behoben.
Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität. Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.
Folgende(r) 1 Nutzer sagt Danke an Ego für diesen Beitrag:1 Nutzer sagt Danke an Ego für diesen Beitrag 28 • volker_xy
Um eine Formel zu verstehen sollte man sie von Innen nach Aussen nach vollziehen.
Vorab
Das Ergebnis soll die Zeile sein, die den kleinsten Preis hat. Ich benötige in der Formel also Informationen über die Preise und Zeilen. Jetz von Innen nach Aussen:
A:= RUNDEN(($G$12:$G$3025)*100;0)
Ergibt eine Liste der Preise in Cent (da *100)
B:= ZEILE($G$12:$G$3025)
Ist eine Liste der Zeilennummern
C:= B%%
"%%" ist eine verkürzte Schreibweise von "/10000". Alle Zeilennummern werden also durch 10.000 geteilt. (für Zeile 14 bekomme ich den Wert 0,0014)
D:= A+C
Ich habe jetzt eine Liste von Zahlen, bei denen vor dem Komma der Preis und nach dem Komma die Zeilennummer steht.
E:=(($B$13:$B$3025=$K$7)*($C$12:$C$3025=$K$10))
Ergibt eine Liste von 0en und 1en. Eine 1 ist nur für die Zeilen vorhanden, die mit der ausgewählten Farbe und Dicke übereinstimmen.
F:= D/E
An den Stellen, an denen in E eine 1 steht, bleibt in F der Wert aus D (da geteilt durch1).
An den Stellen, an denen in E eine 0 steht, wird in F ein Feheler eingetragen (da geteilt durch 0).
G:=AGGREGAT(15;6;F;ZEILE(N1));1)
Ergibt den einen Wert aus F, der dem kleinsten Preis entspricht (Da die Preise vor dem Komma stehen).
H:= Rest(G;1)
Entfernt den Vorkommabereich (den Preis) aus der Zahl G und behält nur den Nachkommabereich (die Zeile geteilt durch 10000).
I:= H/1%%
Entspricht H*10000. Ich erhalte also die gesuchte Zeile.
Bedingungen
Die Formel geht so nur solange
1. die Genauigkeit in Cent ausreicht um das Minimum zu bestimmen,
2. die Liste nicht über die Zeile 9999 hinausragt und
3. der Wert des Preises nicht über 999.999€ ist.
(da die Rechengenauigkeit für Zahlen in Excel bei zwölf Ziffern liegt)
Wenn eine der Bedingungen nicht erfüllt ist und eine der anderen Bedingungen nicht entsprechen gekürzt werden kann, würde ich eine programmierte Lösung empfehlen.
helmut
Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität. Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.
Folgende(r) 1 Nutzer sagt Danke an Ego für diesen Beitrag:1 Nutzer sagt Danke an Ego für diesen Beitrag 28 • volker_xy
habe gestern und heute morgen schon versucht einen Fehler zu lokalisieren.
Leider steigt die Funktion beim 3. günstigsten Preis aus - zu zwar wenn die Informationen bei Position Zeilennummer größer 999 gefunden wird.
der günstigste und 2. günstigste Wert läuft weiter ohne Fehler.
Ich hab Dir mal die Datei nochmals mit dem Fehler angehängt - vielleicht gibt´s ja eine Möglichkeit auf Zeile 9999 zu erweitern was definitiv völlig ausreichend wäre.
999 reicht mir aber leider nicht.
Versuch2.xlsx (Größe: 73,36 KB / Downloads: 3)
![[-]](https://www.clever-excel-forum.de/images/collapse.png)
