Microsoft Excel

Herbers Excel/VBA-Archiv

Formel optimieren

Betrifft: Formel optimieren von: Michael
Geschrieben am: 30.07.2020 07:26:18

Hallo Zusammen,
ich habe für eine komplizierte Preisermittlung folgende Formel erstellt:

=(WENN($A$3<$B8;0;WENN($A$3>$C8;$C8-$C7;$A$3-$C7))*D8)+(WENN($A$3<$B9;0;WENN($A$3>$C9;$C9-$C8; $A$3-$C8))*D9)+(WENN($A$3<$B10;0;WENN($A$3>$C10;$C10-$C9;$A$3-$C9))*D10)+(WENN($A$3<$B11;0; WENN($A$3>$C11;$C11-$C10;$A$3-$C10))*D11)+(WENN($A$3<$B12;0;WENN($A$3>$C12;$C12-$C11;$A$3-$C11))*D12)

Alle meine Versuche, diese Formel zu optieren (SUMMENPRODUKT oder eine Matrixformel?!?) scheiterten kläglich.
Habt Ihr eine Idee, wie man dieses "Monster" etwas übersichtlicher gestlaten kann?
Hier der Link zur Dummy-Datei: https://www.herber.de/bbs/user/139382.xlsx
Vielen Dank für Eure Unterstützung
Michael

Betrifft: AW: mit Hilfe einer Formel von Sepp Burch ...
von: neopa C
Geschrieben am: 30.07.2020 08:09:09

Hallo Michael,

... so:

Arbeitsblatt mit dem Namen 'Preisermittlung'
 ABCD
2Anzahl Samenkörner Gesamtpreis
332.000.000 26.165,00 €
7Preis-
Gruppe
Menge vonMenge bisEP pro Gruppe
8101.000.0000,002770
921.000.0012.500.0000,001910
1032.500.0015.000.0000,001400
1145.000.00110.000.0000,001030
12510.000.00110.000.000.0000,000540
13    

ZelleFormel
D3{=SUMME((C8:C12)-(C7:C11))*(A$3>C7:C11)*D8:D12)-MIN(WENN((C8:C12-A3)*D8:D12>0;(C8:C12-A3)*D8:D12))}
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 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg


Gruß Werner
.. , - ...

Betrifft: Fehlermeldung
von: Michael
Geschrieben am: 30.07.2020 08:44:34

Hallo Werner,
vielen Dank für Deine Formel.
Leider bekomme eine Fehlermeldung: "Mit dieser Formel gibt es ein Problem."
Die Eingabe habe ich STRG + Shift + Enter korrekt abgeschlossen.
Gruß
Michael

Betrifft: AW: da hat das Tool eine Klammer "geschluckt" ...
von: neopa C
Geschrieben am: 30.07.2020 08:59:51

Hallo Michael,

... wie auch immer das passieren konnte. Sorry.

Richtig lautet die Formel so:

{=SUMME(((C8:C12)-(C7:C11))*(A$3>C7:C11)*D8:D12)-MIN(WENN((C8:C12-A3)*D8:D12>0;(C8:C12-A3) *D8:D12)) }


Gruß Werner
.. , - ...

Betrifft: Prima, kleine Frage noch...
von: Michael
Geschrieben am: 30.07.2020 09:20:02

Hallo Werner,
na das funktioniert ja prächtig.
1000 Dank auch für Deine Unterstützung!
Ich habe noch eine Frage:
Um die Formel noch besser "lesen" zu können, wollte ich die Mustertabelle (A7:D12) in eine intelligente Tabelle umwandeln. (wegen der Namen)
Dabei geht jedoch der "Trick" mit der Null in der Überschrift nicht mehr, da als Überschrift bei den intelligenten Tabellen keine Zahlen erlaubt sind.
Das ist sicherlich nicht mehr zu korrigieren?
Wäre aber kein Problem, die Gesamtsumme passt und die Formel ist 100-mal besser, als meine "Monster-Formel".
Gruß
Michael

Betrifft: AW: theoretisch könnte man zwar ...
von: neopa C
Geschrieben am: 30.07.2020 09:56:34

Hallo Michael,

... das benutzerdefinierte Zahlenformat in C7 dadurch umgehen, das man nach der Überschriftenzeile eine Datenzeile mit lauter 0 Werten einfügt. Dann könntest Du eine "intelligente" Tabelle erzeugen. Für dei Formelauswertung muss man dann nicht mehr auf eine Zelle der Überschriftenzeile zugegriffen werden. Dies kann dann aber trotzdem nicht richtig genutzt werden, weil in der Formel ja noch ein Zeilenversatz in der Spalte C abgebildet werden muss. Das wiederum könnte man mit Hilfe von INDEX() auf Basis der "Feldnamen" auch abbilden, dadurch wird aber die Formel wieder sehr lang und somit unübersichtlicher.

Gruß Werner
.. , - ...

Betrifft: Danke
von: Michael
Geschrieben am: 30.07.2020 10:02:17

Hallo Werner,
vielen Dank für Deine ausführliche Antwort.
Ich verwende "normale" Bereichsnamen. Das erfüllt seinen Zweck.
Gruß und nochmals Danke
Michael

Betrifft: AW: bitteschön owT
von: neopa C
Geschrieben am: 30.07.2020 10:08:39

Gruß Werner
.. , - ...

Betrifft: AW: eben festgestellt ...
von: neopa C
Geschrieben am: 30.07.2020 14:46:17

Hallo Michael,

... das bei Dir ja in Spalte C ja "bis-Werte" stehen.
Dann müsste in der von mir angegebenen Formel natürlich noch ein Gleichheitszeichen eingefügt werden.

Also dann so:

=SUMME(((C8:C12)-(C7:C11))*(A$3>=C7:C11)*D8:D12)-MIN(WENN((C8:C12-A3)*D8:D12>0;(C8:C12-A3) *D8:D12))

Gruß Werner
.. , - ...



Betrifft: "Burch und gut" ... aber Lupo1 noch kürzer :-)
von: lupo1
Geschrieben am: 30.07.2020 14:27:27

I7: =MIN(A3*{277.191.140.103.54}/10^5+{0.860.2135.3985.8885}) ohne Hilfszellen, oder ...

I7: =SUMME((A3>B8:B12)*(A3-B8:B12)*(D8:D12-D7:D11)) ... bei wem es noch so sein muss, dann als {}-Fml

Achtung:
D7 muss für letztere Formel - natürlich! - leer sein.
Und B8:B12 bitte die korrekten runden Anzahlen, nicht +1.
Was tut man nicht für ein bisschen Würze ... äh, Kürze ...

Die zweite Formel toleriert übrigens auch eine Funktionsumkehr.

Betrifft: AW: ja, die 2. Formel ist wirklich Spitze ...
von: neopa C
Geschrieben am: 30.07.2020 15:02:45

Hallo lupo1,

... Deine 1. Formel wäre mir dagegen zu "statisch", um diese einzusetzen.

Die 2 Formel ist dagegen einfach(er) nachvollziehbar und eine wirkliche weitere Vereinfachung gegenüber der Formel von Sepp Burch.

Für Mit-/und Nachleser: lupo1 setzt voraus, dass nicht nur in C7 sondern auch in D7 eine benutzerdefiniert formatierte 0 steht.

Gruß Werner
.. , - ...

Betrifft: Kurze Formeln "benötigen oft Platz" ...
von: lupo1
Geschrieben am: 30.07.2020 15:12:44

... und das ist meist eine Leerzeile und/oder -spalte davor. Also nix da mit benDefFmt.

Warum brauchen sie Platz? Weil sie gegenüber längeren Formeln keine Ausnahmen akzeptieren.

Beiträge aus dem Excel-Forum zum Thema "Formel optimieren"