Hallo Excel Team,
starte nochmals einen neuen Thread mit meiner letzten Frage - da der "alte" Thread etwas verwirrende geworden ist.
Beigefügt eine Tabelle
[
attachment=25904]
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.
Hoffe, hier gibt´s ne einfache Lösung.
Gruß
Volker
Hallo Will wissen,
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?
Danke an alle.
Volker
Hi Volker,
ausprobiert hast du wahrscheinlich nix?
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
http://www.herber.de/excelformeln und bitte suchen .../formeln.html?welcher=224
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!
Volker
Hallo Volker,
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.
Code:
=WENNFEHLER(REST(AGGREGAT(15;6;(RUNDEN(($G$12:$G$3025)*100;0)+ZEILE($G$12:$G$3025)%%)/(($B$13:$B$3025=$K$7)*($C$12:$C$3025=$K$10));ZEILE(N1));1)/1%%;"")
Hallo Ego,
Du scheinst mir ja der Logik und Mathe Crack zu sein.
Da verstehe ich nur noch Bahnhof und sehe doppelschwarz!!
Aber funktioniert.
Verstehen tu ich hier nix mehr
Danke
Gruß Volker
Hallo Volker,
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.
Hallo Helmut,
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.
[
attachment=25909]
Ich brauch wahrscheinlich noch Stunden / Tage um das Problem zu lösen.
Gruß und einen sonnigen Sonntag
Volker
Hi
Code:
=WENNFEHLER(REST(AGGREGAT(15;6;(RUNDEN(($G$12:$G$3025)*100;0)+ZEILE($G$12:$G$3025)%%)/(($B$13:$B$3025=$K$7)*($C$12:$C$3025=$K$10));ZEILE(N1));1)/1%%;"")
ich glaube hier ist @Ego nur ein kleiner Schreibfehler passiert.
Ändere mal in der Formel $B$13 in $B$12 um.
Gruß Elex