Excel 2016: Formelergebnisse auf Dropdown-Ergebnisse automatisch anpassen?
Hallo,
ich habe momentan ein recht spezielles Problem. Und zwar würde ich gern in einer Zelle eine Rechenformel stehen haben, gleichzeitig aber auch ein Dropdown-Menü direkt in dieser Zelle haben und die Rechenwerte der Formel sollen dann auf einen Wert des Dropdown-Menüs automatisch angepasst werden.
Kleines Beispiel: im Feld F6 soll die Formel: =(0,5E7+0,5E8) stehen. Jedoch habe ich gleichzeitig noch in F6 über "Daten->Reiter Datentools->Datenüberprüfung" ein Dropdown-Menü erstellt und dieses mit 11 auswählbaren Werten gefüttert. Jetzt kommt durch die Formel ein Rechenergebnis von z.B. 2,8 heraus. Im Dropdown-Menü stehen jedoch nur 2,3 ; 2,7 ; 3,0 ; etc zur Verfügung.
Wie bekomme ich es hin, dass Excel das Rechenergebnis dann automatisch auf den korrekten Dropdownwert (hier: 2,7) anpasst?
Ich hoffe, ihr könnt mir helfen.
mfg,
3 Antworten
Ich denke das ich das Problem richtig verstanden habe - oder?
Du willst in E6 eine Funktion eingeben, aber gleichzeitig in der Selben Zelle ein DropDown haben welches dir nach Eingabe der Funktion das Ergebnis liefert?
Das geht nicht!
Mir wäre spontan ein Dynamischer DropDown eingefallen, dieser passt sich aber auch nicht automatisch an!
DropDown sind Gültigkeitsvorgänge wie du schon selbst beschrieben hast!
Alles was in der Gültigkeitsprüfung steht, darf in die Zelle! Alles andere nicht!
Die Auswahlliste ist nur ein kleines Gimmek welches dir eine schönere Übersicht geben kann!
Sprich, die Auswahl muss von dir vorgenommen werden, da die Werte FIX hinterlegt sind!
Aller höchstens könntest du dir hier mit VBA behelfen!
Grüße,
iMPerFekTioN
ok, schade.
Soweit ich das verstanden habe, nützt mir ein dynamischer Dropdown ja auch nichts, weil der nur den errechneten Wert zur Liste hinzufügt.
Mein Problem ist halt, dass ich etwas von Excel berechnen lassen will, weil ich nicht selbst rechnen will. Dieses Ergebnis MUSS aber dann zwingend in einen spezifischen Wert gerundet werden. Also der Befehl "runden" würde mir hier nicht helfen, weil man damit nur regulär runden kann.
Das Dropdown-Menü ist nicht zwingend, aber das war meiner Meinung nach die einfachste Methode, um die möglichen Werte vorzugeben.
Eine andere Idee wäre noch: Ich verschiebe ab F6 den Rest der Tabelle um eine Spalte nach rechts, lasse in F6 die Formel und somit das Rechenergebnis und muss das dann irgendwie in G6 auf die vorgegebenen Werte ummünzen. Dazu müsste ich aber wissen, wie? F6 kann ich ja dann einfach ausblenden, oder? Weil ich lösche ja die Spalte F nicht, sondern blende sie nur aus. Rechnen müsste es dann ja aber trotzdem noch, oder?
Und zu deinem Vorschlag: Was ist VBA?
Gruß,
Etherion
Ok, was könnte ich damit anstellen?
Eine Idee wäre ich noch, dass ich mit der WENN-Funktion runde. Dazu muss ich aber zwingend noch eine Spalte einsetzen. Rechnet Excel denn im Hintergrund noch, wenn ich die Spalte, in der die Rechenformel und das Rechenergebnis stehen, ausblende?
Inwiefern wäre die Wahl-Funktion hier anwendbar? Wenn bei WAHL hätte man laut meinem Verständnis nur einen festen Indexwert und keinen Indexbereich.
Erst mal eine kleine Rückfrage:
Welche Werte sollen denn errechnet werden und welche Ergebnisse können vorkommen?
Kannst DU mir mal zusammenschreiben, welche Ergebnisse es alle geben darf und wie diese erreicht werden sollen?
Ok, mache ich. Aber vorweg schonmal: ich habe das Problem jetzt gelöst (Beschreibung kommt gleich).
der errechnete Wert entsteht zum Beispiel so:
F6: "=(0,8*E7+0,2*E8)" (die Felder und Multiplikatoren sind je nach Rechenfeld unterschiedlich).
Dann kommt in F6 zum Beispiel 2,84 heraus. Diesen Wert will ich dann in G6 in einen der folgenden Werte nach regulären Rundungsregeln (nur mit dem Unterschied, dass halt nur auf diese speziellen Werte gerundet werden darf) runden: 0,0_1,0_1,3_1,7_2,0_2,3_2,7_3,0_3,3_3,7_4,0_5,0 *a
Diese Werte sind auch die Eingabewerte für das dropdown Menü in den Felder E7 und E8.
Nun zu meiner Lösung: ich habe für jeden der o.g. Werte die untere Grenze festgelegt, ab der dem Feld G6 diesen Wert zugeordnet werden soll. Die Grenzwerte sind:
0,00_1,00_1,16_1,51_1,86_2,16_2,51_2,86_3,16_3,51_3,86_4,01 *b
Und dann habe ich mit:
G6: "=VERWEIS(F6;L6:L18;K6:K17)"
in G6 bezogen auf die Werte aus F6 die erwünschten Werte (oben unter *a) zugewiesen. Dabei sind in Spalte L die Grenzwerte (*b) und in Spalte K die erwünschten Werte (*a)
somit hat es jedem Grenzwertbereich eine Wunschzahl zugewiesen (z.b. wenn die Zahl in F6 den Wert 0,00 bis 0,99 hat, wird der Wert 0,0 zugewiesen. Wenn F6 einen Wert von 1,00 bis 1,15 hat, wird in G6 1,00 zugewiesen usw.). F6 wurde darauf überprüft, in welchem Grenzwert es liegt und in G6 wurde dann der zugewiesene Wert eingetragen.
So verstehe ich es zumindest. Und das Ausblenden hat auch funktioniert.
Ich hoffe, das war soweit ganz verständlich.
Na, dann brauchst du ja gar keine Hilfe mehr ;:)
Freut mich!
Du könntest hier aber auch mit einer kleineren einfachen eignen Matrix arbeiten :)
ja, ich bin auch froh, hab für die Tabelle ja lange genug gebraucht^^
Wie ist das mit der Matrix gemeint? Matrizen habe ich in den Mathevorlesungen leider nie verstanden :(
Leicht erklärt:
Eine Matrix ist ein Tabellenbereich mit enthaltenen Werten!
Du hast mindestens 2 Spalten ODER 2 Zeilen
A B
oder
1
2
So, du hast in einer Matrix jetzt mehrere Werte, die du nach belieben anpassen kannst!
Es gibt z.B. einen Index, ein Suchkriterium usw.
A B
1 1 Du
2 2 Ich
Hier werden 2 Spalten und 2 Zeilen beschrieben! Spalte 1 Enthält 2 Zahlen (1 und 2) Spalte 2 enthält 2 Textteile (Du und Ich)
In Abhängigkeit des 1. Wertes kann aus dieser Matrix mithilfe des Spaltenindexes 2 der Textteil ausgefiltert werden!
Sprich, Der Spaltenindex 2 besteht aus folgenden Werten:
Wert1=Du
Wert2=Ich
Fertig :)
Um dir das mit der Matrix Lösung zu erklären, bräuchte ich deine Schwellwerte!
Du sprichst hier von diesen Werten:
0,0_1,0_1,3_1,7_2,0_2,3_2,7_3,0_3,3_3,7_4,0_5,0
Ab wann wird welcher Wert eingesetzt?
Kannst du das in dieser Form erläutern?:
<0 = 0,0
>0 = 1,0
>1 = 1,3
>1,5 = 1,7
...
Dann kann ich dir gerne weiterhelfen :)
ah okay, sowas hatte ich schon gelesen, dass das geht.
Ja klar (wert x ist hier der Rechenwert aus F6).
0,00 <= x < 1,00 --> Wert 0,0 wird zugewiesen
1,00 <= x <= 1,15 --> Wert 1,0 wird zugewiesen
1,15 < x <= 1,50 --> Wert 1,3 wird zugewiesen
1,50 < x <= 1,85 --> Wert 1,7 wird zugewiesen
1,85 < x <= 2,15 --> Wert 2,0 wird zugewiesen
2,15 < x <= 2,50 --> Wert 2,3 wird zugewiesen
2,50 < x <= 2,85 --> Wert 2,7 wird zugewiesen
2,85 < x <= 3,15 --> Wert 3,0 wird zugewiesen
3,15 < x <= 3,50 --> Wert 3,3 wird zugewiesen
3,50 < x <= 3,85 --> Wert 3,7 wird zugewiesen
3,85 < x <= 4,00 --> Wert 4,0 wird zugewiesen
4,00 < x <= 5,00 --> Wert 5,0 wird zugewiesen
Dann kannst du mit der SVERWEIS()-Funktion und einer eigenen Matrix arbeiten :)
Der Errechnete Wert steht in A1, sprich die Formel zur Berechnung in A1
In B1 kannst du dann folgende Formel eintragen:
=SVERWEIS(A1;{0.0;1.1;1,15.1,3;1,5.1,7;1,85.2;2,15.2,3;2,5.2,7;2,85.3;3,15.3,3;3,5.3,7;3,85.4;4.5};2)
Grüße,
iMPerFekTioN
Ok und dann nur zum Verständnis: die 2 ganz am ende gibt die Anzahl der spalten an, korrekt?
Und bei zB. " ;2,85.3; " steht 2,85 in der ersten Spalte und 3 in der zweiten Spalte, korrekt? Heißt im Klartext: "ab dem Wert von 2,85 wird der Wert 3 zugewiesen". sehe ich das richtig?
Dann müssten dürften die Werte aber nicht stimmen, weil das ja heißen würde, dass wenn 2,85 in A1 steht, dieser Wert dann in B1 auf 3 gerundet wird und nicht auf 2,7, wie er eigentlich sollte. Also einfach die 2,85 in eine 2,86 ändern und dann sollte das passen, denke ich, oder?
Richtig :)
Du hast sozusagen ein SVERWEIS() mit einer Matrix als zahl angegeben und nicht als Zellbezug!
Ja du hast recht mit der 2 und dem ganzen Rest!
Ausgesprochen heißt es eigentlich nur Werte von 0 bis 1,14 werden einer 0 zugewiesen, ab 1,15 bis 1,50 dann die 1 usw.
Also kannst du den Wert einfach überall erhöhen wo du es geändert haben möchtest :)
Grüße,
iMPerFekTioN
Alles klar, danke. Das ist dann im Prinzip exakt das gleiche, wie meine Lösung, nur dass man den langen SVERWEIS in jedes einzelne gewünschte Feld bringen muss und ich diesen Befehl durch den Umweg der einzelnen Listen einfach nur abgekürzt habe.
Das Problem beim SVERWEIS wäre dann nämlich, dass ich das in jedem der 10 oder 20 Felder ändern müsste, wenn sich an den Grenzwerten etwas ändern sollte. Bei meiner Lösung brauche ich das einfach nur in der Liste ändern.
Aber ja, SVERWEIS scheint ja sehr beliebt zu sein.
Danke für die Lösung:)
Du kannst die Schwellwerte auch in Zellen packen und diese Verlinken!
Sprich, in A5 kannst du den Wert 0 eintragen, in A6 den Wert 1,15 usw.
Dann nimmst du statt dieser Formel:
=SVERWEIS(A1;{0.0;1.1;1,15.1,3;1,5.1,7;1,85.2;2,15.2,3;2,5.2,7;2,85.3;3,15.3,3;3,5.3,7;3,85.4;4.5};2)diese hier:
=SVERWEIS(A1;{A5.0;A6.1,3;1,5.1,7;1,85.2;2,15.2,3;2,5.2,7;2,85.3;3,15.3,3;3,5.3,7;3,85.4;4.5};2)dann kannst du (falls sich Werte ändern) einfach die eine Zahl abändern und diese wird auf alle Zellen übernommen!
Dazu müssen die Zellen eben noch Absolut Adressiert werden (mit den $ vor Buchstabe und Zahl)
=SVERWEIS(A1;{$A$5.0;$A$6.1,3;1,5.1,7;1,85.2;2,15.2,3;2,5.2,7;2,85.3;3,15.3,3;3,5.3,7;3,85.4;4.5};2)Ja stimmt, wäre auch eine Option. die $ Zeichen muss man ja aber nicht machen, weil man die betreffende Zelle auch einfach anklicken kann, oder? Und kann das ganz normal mit Enter bestätigt werden oder muss das mit strg+shift+enter gemacht werden?
Aber es ist ja im Endeffekt die exakt gleiche Lösung, wie meine. zumindest funktionieren sie exakt gleich.
aber ich habe gleich mal noch eine andere Frage:
und zwar, wie kann ich schon auf den ersten Blick anzeigen lassen, dass es sich bei einer Zelle um ein Dropdown-Menü handelt?
Also praktisch soll der Dropdown-Pfeil dauerhaft sichtbar sein, damit man das auch erkennt. Das Dropdown-Menü ist mit der Zelle verbunden und ist somit kein verschiebbares Kombinationsfeld.
Ist das DropDown per Gültigkeit angelegt?
Dann geht das nicht!
Zwecks der Lösung! Die $ Zeichen brauchst du, um die Formel kopieren zu können ohne das sich die Werte ändern!
So hast du z.B. den Vorteil, wenn du von A1:A12 deine Ergebnisse hast und die Formel in B1:B12 schreiben möchtest, dass du die Formel einmal in B1 schreibst und dann mit dem kleinen Viereck unten Rechts in der Zelle nach unten ziehen kannst!
Die Zellbezüge werden nicht verändert, nur die Suchzelle und somit hast du weniger Arbeit :)
Ich denke ja, also es ist ein Zellendropdown, welches über den Reiter Daten -> Datenüberprüfung angelegt wurde.
Ist ja schade, dass das nicht geht.
Achso, aber das hätte mir anscheinend auch nicht viel genützt, weil ich alle 2-3 Zeilen eine Zelle habe, in der nichts stehen darf. Also mit ziehen ist da nichts. Wenn ich direkt kopieren könnte, wäre es natürlich eine Erleichterung. Das probiere ich beim nächsten mal dann.
Danke dir.
Hallo, durch kopieren wird der Zellbezug auch nicht geändert!
Solange du nicht den Text kopierst!
Du klickst die Zelle mit der Formel an, drückst STRG+C, markierst jede 3. Zelle und drückst dann STRG+V.
Jetzt werden alle Bezüge angepasst außer die, wo du deine $ vorangestellt hast!
Uńd Fertig :)
Danke für den Stern!
Was du machen könntest, wäre die Liste in einer Spalte zu führen und dann den Wert mit kleinster Differenz zum berechneten Wert finden.
Wie ist das gemeint? müsste ich dann in F6 das Rechenergebnis stehen lassen und dann in G6 eine Formel nehmen nach der Art "Vergleiche das Rechenergebnis aus F6 mit allen Werten der Liste in Spalte XY"?
Wenn ja, wie würde die Formel denn dann in Excel aussehen?
Das würde so funktionieren. Du schreibst in eine Spalte deine 11 Werte. In meinem Beispiel habe ich mal die Spalte A genommen.
In der Zelle F6 steht deine Berechnung. Jetzt brauchst du noch eine zusätzliche Zelle für die Auswerteformel. In diese Zelle schreibst du dann:
=MIN(WENN(ABS(A1:A11-F6)=MIN(ABS(A1:A11-F6));A1:A11))
Die Formel gibt nun den Wert aus der Liste zurück, der dem Ergebnis der Rechnung am nächsten kommt. Ist das genaue Ergebnis in der Liste enthalten, so wird natürlich das genaue Ergebnis zurückgegeben.
Achtung: Die Formel so eingeben aber mit Shift+Strg Return abschließen, da es sich um eine Matrixformel handelt (Excel setzt die ganze Formel dann automatisch in geschweifte Klammern). Auch jedes Mal wenn du etwas an der Formel änderst musst du sie wieder mit Shift+Strg Return abschließen.
Ich denke eine Hilfsspalte für die Liste und eine zusätzliche Zelle für den Vergleich sollten sich finden lassen.
das wäre eine Option, aber kannst du mir eventuell nochmal die Syntax dieser Formel erklären? Ich blicke da nicht so ganz durch, was hier was ist. Außerdem sieh es so aus, als ob du zur "WENN-Funktion" keinen dritten Wert (den "sonst"-Wert") angegeben hast. Kann man den denn einfach weglassen?
naja, in der Tabelle selbst will ich ja keine extra Spalte. Würde excel denn trotzdem noch rechnen, wenn ich die Rechenspalte ausblende (also nicht löschen, sondern über die Funktion "ausblenden")?
ok, habe es gerade mit der Formel probiert, aber es funktioniert nicht. Die Zelle gibt mir den Fehler "#WERT!" an. Und in der Anzeige der Berechnungsschritte unterstreicht er meine Liste (die betreffenden Zellen habe ich in der Formel natürlich durch meine Zellen ersetzt)
Also im Einzelnen:
- Ja, man kann den Sonst-Zweig weglassen, dann wird in diesem Falle ein "Falsch" ausgegeben. In diesem konkreten Fall kann man ihn weglassen, weil es ja immer eine Zahl mit kleinster Differenz zum Suchwert aus der Liste gibt (angenommen, die Liste endete mit 5 und der Suchwert sei 1000, so hat 5 eben die kleinste Differenz zum Suchwert).
- Ja, du kannst die Spalte auch ausblenden. Excel rechnet auch dann mit den korrekten Werten weiter.
- Hast du die Formel tatsächlich mit Strg-Shift Return abgeschlossen? Ist deine Formel in geschweifte Klammern gesetzt? Diese Klammern darfst du nicht selbst eingeben, die werden von Excel automatisch gesetzt, wenn die Formel mit Strg-Shift Return abgeschlossen wird. Und nochmal zur Erinnerung: Auch nach jeder Änderung in der Formel musst du sie erneut so abschließen.
- Zum Ausprobieren der Formel schlage ich vor, dass du nicht deine konkrete Excel-Tabelle nützt, sondern eine eigene Testtabelle anlegst. Schreibe hier z.B. in A1-A5 5 beliebige Werte, die natürlich auch Werte aus deiner Liste sein können. In B1 schreibst du nun den Suchwert und in C1 die obige Formel. Erst wenn das soweit funktioniert, solltest du Änderungen an deinem Original-Dokument vornehmen.
- Obwohl ich die Formel selbst im Einsatz habe, habe ich sie mit obigem kleinem Beispiel nochmal durchprobiert und kann versichern, dass sie (bei mir) so funktioniert.
- Sollte noch jemand hier mitlesen, wäre es schön, wenn es noch jemand so testen und ggf. bestätigen könnte.
1. und 2. super, danke.
3. ach, vergessen. Daran lag es dann wohl.
4. Ja, das hatte ich sowieso gemacht.
Ich habe jetzt aber sogar einen anderen Lösungsweg gefunden, der für mich einfacher zu verstehen ist. Den Lösungsweg habe ich in der Antwort von "iMPerFekTioN" als Kommentar gepostet. Ich wollte ihn hier nicht doppelt posten.
Aber deinen Lösungsweg werde ich ggf. auch mal noch ausprobieren. Danke auf jeden Fall.
Imho, geht das schlicht nicht.
ok, schade. Gibt es noch eine andere Möglichkeit? Ich möchte von Excel berechnen lassen und darf aber dann nur bestimmte, festgeschriebene Werte in der Tabelle haben. Und das Rechenergebnis soll auf diese spezifischen Werte gerundet werden.
Visual Basic, das is die Makro Sprache von Office!
Wie wäre es mit der WAHL()-Funktion?