Preisberechnung mit mehrfachen Kombinationen

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
MsgBox
Bild

Betrifft: Preisberechnung mit mehrfachen Kombinationen
von: Frank
Geschrieben am: 16.11.2015 12:21:59

Hallo liebe Excel Profis,
bei einer Preisberechnung stoße ich leider an meine Grenzen. Am liebsten wäre mir eine Lösung in Form eines Makros aber auch über eine Formelbasierte Lösung würde ich mich sehr freuen.
Bei der Berechnung von Fahrzeugausstattungspreisen (Tabelle Berechnung) stehen in den Zellen der Spalte „N“ dreistellige PR-Codes (je Zelle unterschiedlich viele aber nicht mehr als 30). Die Informationen zu den Preisen sind in der Tabelle „PR-Codes“ zu finden.
Ziel ist es, entweder die Preise jedes einzelnen PR-Codes auszugeben und diese dann anschließend zu summieren, oder direkt die Summe der PR-Codes in der Spalte "BS" auszugeben.
Zu beachten gibt es, dass gleiche PR-Codes unterschiedliche Preise haben können wenn:
• Sie anderes Modelljahr haben
• Sie einer anderen Modellgruppe zugehörig sind
• eine Kombination mit einem bestimmten anderen PR-Code vorliegt
• eine Kombination mit einem bestimmten Modellcode haben
Diese Regeln sind im Tabellenblatt PR-Preise hinterlegt und werden immer wieder erneuert und erweitert.
Die Datensätze in der Tabelle „Berechnung“ (A-BP) werden immer wieder ausgetauscht (können bis zu 40.000 sein) und die Berechnung muss dann jeweils neu ausgeführt werden.
Aufgrund der Vielzahl an Kombinationen komme ich leider mit Verweisen und WENN-Formeln nicht mehr weiter. Die Berechnung der Modell- und der Farbcodes war kein Problem.
Vielleicht findet jemand von Ihnen eine Lösung, ich wäre sehr dankbar.
Viele Grüße
Frank
https://www.herber.de/bbs/user/101553.xlsx

Bild

Betrifft: AW: nachgefragt ...
von: ... neopa C
Geschrieben am: 16.11.2015 12:57:02
Hallo Frank,
... welche Bedeutung haben die "/" in der Spalte N?
Ist mit "Modellcode" die "MODELLCOMBI" aus PR-Preise!F:F gemeint?
Kannst Du mal in BS2 und BS7 die von Dir "händisch" ermittelten Preise angeben?
Gruß Werner
.. , - ...

Bild

Betrifft: AW: nachgefragt ...
von: Frank
Geschrieben am: 16.11.2015 13:33:31
Hallo Werner,
danke für deine schnelle Hilfsbereitschaft. Die "/" in Spalte "N" haben im Prinzip keine Bedeutung, hätte ich gleich dazu schreiben sollen, sorry. In meinen bisherigen Versuchen, habe ich diese auch immer entfernt.
BS2 = 1.058,82 € (P20=655,46 €, P4P=403,36 €, PH1,PS1,PSD jeweils 0 € da in Kombinaion mit P20)
BS7 = 1.453,78 € (P23=936,97 €, PBA=50,42 €, P4P=403,36 €, PFP=63,03 €, PFH,PH1,ZB2,PS2 jeweils 0 € da in Kombination mit P23)
Ich hoffe ich habe auf die schnelle hier jetzt keinen Rechenfehler eingebaut.
Die "MODELLCOMBI" aus PR-Preise!F:F müsste eigentlich "MODELLCODE-KOMBI" heißen. Hier muss ein Abgleich mit dem Feld K:K in der Tabelle "Berechnung" erfolgen.
Viele Grüße
frank





Bild

Betrifft: AW: da ergeben sich schon die nächsten Frage ...
von: ... neopa C
Geschrieben am: 16.11.2015 14:09:09
Hallo Frank,
... wenn wie in Deiner Datei für P20 und P4P in PR-Preise!F:F keine Angabe steht in Berechnung!K2 aber schon, dann soll PR-Preise!F:F nicht gewertet werden? kann dass auch anderweitig noch vorkommen?
Was hat dann welche Priorität?
Wie viele Datensätze hat PR-Preise max?
Gruß Werner
.. , - ...

Bild

Betrifft: AW: da ergeben sich schon die nächsten Frage ...
von: Frank
Geschrieben am: 16.11.2015 15:11:34
Hallo Werner,
in "PR-Preise" "F:F" stehen ja nur Modellcodes (6-stellig) welche in der Tabelle "Berechnung" in der Spalte "K" stehen.
Schau dir als Beispiel mal bitte Zeile 706 und Zeile 707 in der Tabelle "PR-Preise" an.
Die Regel sollte wie folgt lauten:
wenn Modellcode "5F111X" (in Tabelle Berechnung K:K) in Kombination mit "PJB" (gleiche Zeile in Tabelle Berechnung K:K), dann 634,45 € für "PJB" (siehe Zeile 706 in der Tabelle "PR-Preise"),
wenn aber Modellcode "5F12FV" (in Tabelle Berechnung K:K) in Kombination mit "PJB" (gleiche Zeile in Tabelle Berechnung K:K), dann 147,06 € für "PJB" (siehe Zeile 707 in der Tabelle "PR-Preise").
Im Grunde heißt das, dass nicht nur innerhalb der jeweiligen Zellen von N:N auf Kombinationen geprüft werden muss sondern auch nach Kombinationen mit den jeweiligen Werten von K:K.
Ich hoffe ich drücke mich einigermaßen verständlich aus. Berechnet werden muss jeweils die Kombination innerhalb der jeweiligen Zeile.
Noch ein Beispiel:
In der Tabelle "Berechnung" "N2" wurde der PR-Code P20 gefunden. Es muss nun geprüft werden, ob eine Kombination mit dem Modellcode aus "K2" in der Tabelle "PR-Preise" hinterlegt ist. Wenn JA, weiter prüfen, ob es noch weitere Einschränkungen in H:H oder I:I in der Tabelle "PR-Preise" gibt.
Wenn NEIN, ebenfalls prüfen, ob es weitere Einschränkungen in H:H oder I:I in der Tabelle "PR-Preise" gibt.
Übrig bleiben kann eigentlich immer nur ein eindeutiger Preis.
In "PR-Preise" könnte grob geschätzt bis zu 20.000 Datensätze enthalten sein.

Gruß
Frank




Bild

Betrifft: AW: für mich noch nicht eindeutig ...
von: ... neopa C
Geschrieben am: 16.11.2015 16:03:24
Hallo Frank,
... betreffs meine erste Frage. Filtere mal die hoch geladene Datei in PR-Preise nach P20 und P4P. Da finden sich keine dazugehörigen Angaben in Spalte F und H:I. In Berechnung!K2 steht jedoch eine Modellcodevorgabe. Demzufolge sollen leere Zellen in PR-Preise!F:F ..H.I wie "Joker" betrachtet werden?
Oder?
In Deiner Aussage: "... nicht nur innerhalb der jeweiligen Zellen von N:N auf Kombinationen geprüft werden muss" ist mir "Kombinationen" nicht verständlich. Bzgl. der Summe in der Spalte BS interessieren doch immer nur die PR-Nummern aus der jeweiligen Zeilen-Zelle der Spalte N oder?
Gruß Werner
.. , - ...


Bild

Betrifft: AW: für mich noch nicht eindeutig ...
von: Frank
Geschrieben am: 16.11.2015 17:02:58
Hallo Werner,
so langsam verstehe ich deine Frage.
In der Tabelle "Berechnung" in "K2" bzw. generell in K:K stehen immer Modellcodes (jedes Kind hat einen Namen). Den Preis für den Modellcode berechne ich in der Spalte "BR" mit den Informationen aus der Tabelle "Modellpreise".
Meine Aufgabe ist es alle Preise in "BS" zu berechnen die in abhängigkeiten zu anderen PR-Codes oder in Abhängikeit zu Modellcodes stehen können.
Für unser Beispiel in "BS2" müsste man den Filter in "PR-Preise" wie folgt setzen:
1. MODELYEAR = 2015 (steht im Datensatz in der Tabelle "Berechnung" in "O2".
2. MODELGROUP = KF1 (steht im Datensatz in der Tabelle "Berechnung" in "AC2".
3. PRCODE = P20 (erster dreistelliger PR-Code im Datensatz in der Tabelle "Berechnung" in "N2".
Hier wird jetzt nur noch ein Ergebnis gefunden, der Preis ist in diesem Moment also eindeutig.
Es gibt aber viele Fälle bei denen das Ergebnis hier noch nicht so eindeutig ist.
Nehme mal den Datensatz aus Zeile 23 in der Tabelle "Berechnung", am beispiel vom sechsten PR-Code "PH1" und filter wieder entsprechend in der Tabelle "PR-Preise":
1. MODELYEAR = 2015 (steht im Datensatz in der Tabelle "Berechnung" in "O2".
2. MODELGROUP = KF1 (steht im Datensatz in der Tabelle "Berechnung" in "AC2".
3. PRCODE = PH1 (sechster dreistelliger PR-Code im Datensatz in der Tabelle "Berechnung" in "N2".
Es bleiben fünf Ergebnisse die weitere Prüfungen verlangen.
Enthält "N2" (Tabelle "Berechnung" einen der vier übrigen Codes (P20,P24,P21 oder P23) in H:H (Tabelle PR-Preise), wenn JA nimm den jeweiligen Preis (in diesem Beispiel immer 0€), wenn NEIN, dann nimm den Preis ohne PACKAGECOMBI1, also 470,59 €.
Weiter könnte es unterschiedliche Preise geben, sofern in der Tabelle "PR-Preise in F:F noch Modellcodes oder in I:I weitere PR-Codes eingetragen wären.
Mit der Filter-Methode kann es für jedes Fahrzeug immer nur einen Preis geben.

Ich hoffe jetzt ist es eindeutiger.
Gruß
Frank

Bild

Betrifft: AW: erste Teil ist jetzt klar, Rest verwirrend ...
von: ... neopa C
Geschrieben am: 16.11.2015 17:46:49
Hallo Frank,
... obwohl Du in unterem Teil von der Auswertung der Zeile 23 schreibst, beziehst Du Dich auch hier wieder auf O2, AC2 und N2. Oder ist da nur ein Schreibfehler und es sollte da O23, AC23 und N23 lauten?
Wenn letzteres zutrifft bleibt für mich noch die Unklarheit, dass in diesem Deinen Beispiel die "PACKAGECOMBI1" gar nicht zum Tragen kommt und somit der Preis von 470,59€ in die Rechnung eingeht. Oder?
Welcher Preis muss den für BS23 nun herauskommen.
Hast Du mal ein Beispiel wo eine "PACKAGECOMB" zum Tragen kommt?
Eins lässt sich schon jetzt ab schätzen. Das könnte eine "wüste" Rechnerei werden. Ob dann bei diesen Datenmengen noch eine Formellösung günstig ist, steht noch in Frage. Wie auch immer. Das ist schon ein kleines Projekt für sich.
Ich schau dann Morgen noch einmal.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: erste Teil ist jetzt klar, Rest verwirrend ...
von: Frank
Geschrieben am: 16.11.2015 20:07:21
Hallo Werner,
ja Schreibfehler, alles 23er... Sorry. Ich hoffe, es ist auch nicht zu verwirrend, dass in der Berechnung die Überschriften teilweise deutsch, in der PR-Preise aber englisch sind.
Der Datensatz aus Zeile 23 liefert Beispiele mit und ohne Kombinationen.
Modelljahr = 2015
Modellgruppe = KF1
gefundene PR-Codes in N23 (Tabelle Berechnung) = PMB PS2 PBB PCO PFP PH1 PT1 PW1
Prüfung PMB = keine Kombinationen eingetragen also eindeutig 1.693,28 €
Prüfung PS2 = 6 x Kombinationsmöglichkiten vorhanden (PACKAGECOMBI1 leer, PBM, P21, P24, PMB, P23)
Kombination PS2+PMB = 84,03 €
Prüfung PBB = keine Kombinationen eingetragen also eindeutig 159,66 €
Prüfung PCO = keine Kombinationen eingetragen also eindeutig 126,05 €
Prüfung PFP = keine Kombinationen eingetragen also eindeutig 63,03 €
Prüfung PH1 = 5 x Kombinationsmöglichkiten vorhanden (PACKAGECOMBI1 leer, P20, P24, P21, P23)
Kombination PH1+leer = 470,59 €
Prüfung PT1 = 2 x Kombinationsmöglichkiten vorhanden (PACKAGECOMBI1 leer, P21)
Kombination PT1+leer = 315,13 €
Prüfung PW1 = keine Kombinationen eingetragen also eindeutig 193,28 €
Bei allen PR-Codes keine weiteren Einträge in MODELCOMBI oder PACKAGECOMBI2.
Preis in Zelle BS23 in Tabelle Berechnung = 3.105,05 €
Das es schwer wird ist mir leider bewusst, versuche dieses Problem schon seit 3 Jahren zu lösen und gebe immer wieder nach einer Weile auf. Ein Makro denke ich ist besser als eine Formel-Lösung.

Bild

Betrifft: AW: ich stell den thread offen ...
von: ... neopa C
Geschrieben am: 17.11.2015 19:04:39
Hallo Frank,
... es scheint für eine Formellösung wirklich zu komplex, zumal ja noch zwei weitere PACKAGECOMBI zu berücksichtigen wären. Wie ich zwischen rein schon vermutete, dass ist ein kleines Projekt für sich.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: da ergeben sich schon die nächsten Frage ...
von: Frank
Geschrieben am: 16.11.2015 15:11:56
Hallo Werner,
in "PR-Preise" "F:F" stehen ja nur Modellcodes (6-stellig) welche in der Tabelle "Berechnung" in der Spalte "K" stehen.
Schau dir als Beispiel mal bitte Zeile 706 und Zeile 707 in der Tabelle "PR-Preise" an.
Die Regel sollte wie folgt lauten:
wenn Modellcode "5F111X" (in Tabelle Berechnung K:K) in Kombination mit "PJB" (gleiche Zeile in Tabelle Berechnung K:K), dann 634,45 € für "PJB" (siehe Zeile 706 in der Tabelle "PR-Preise"),
wenn aber Modellcode "5F12FV" (in Tabelle Berechnung K:K) in Kombination mit "PJB" (gleiche Zeile in Tabelle Berechnung K:K), dann 147,06 € für "PJB" (siehe Zeile 707 in der Tabelle "PR-Preise").
Im Grunde heißt das, dass nicht nur innerhalb der jeweiligen Zellen von N:N auf Kombinationen geprüft werden muss sondern auch nach Kombinationen mit den jeweiligen Werten von K:K.
Ich hoffe ich drücke mich einigermaßen verständlich aus. Berechnet werden muss jeweils die Kombination innerhalb der jeweiligen Zeile.
Noch ein Beispiel:
In der Tabelle "Berechnung" "N2" wurde der PR-Code P20 gefunden. Es muss nun geprüft werden, ob eine Kombination mit dem Modellcode aus "K2" in der Tabelle "PR-Preise" hinterlegt ist. Wenn JA, weiter prüfen, ob es noch weitere Einschränkungen in H:H oder I:I in der Tabelle "PR-Preise" gibt.
Wenn NEIN, ebenfalls prüfen, ob es weitere Einschränkungen in H:H oder I:I in der Tabelle "PR-Preise" gibt.
Übrig bleiben kann eigentlich immer nur ein eindeutiger Preis.
In "PR-Preise" könnte grob geschätzt bis zu 20.000 Datensätze enthalten sein.

Gruß
Frank




Bild

Betrifft: mit VBA
von: Michael
Geschrieben am: 18.11.2015 17:12:06
Hi zusammen,
die Lösung (wenn sie denn bei mehr als dem 1. Datensatz richtig läuft) ist etwas tricky.
Ich habe in PR_Preise zwei Spalten angefügt:
O heißt "SortKombi" und enthält in O2 die Formel

=B2&E2&G2

Nach dieser Spalte wird sortiert; die jetzige Sortierreihenfolge ist etwas "seltsam", derweil etwa vorhandene PackageCombi zuerst aufsteigen sortiert sind, dann aber erst die eine nicht vorhandene kommt (zum besseren Verständnis in "Tabelle1" kopiert und markiert).
P heißt "Anzahl" und enthält in P2 die Formel
=WENN(O2<>O3;1;P3+1)

Diese Formel sorgt dafür, daß alle Zeilen mit gleichem "SortKombi" von *unten nach oben* durchnummeriert werden.
Sieh Dir z.B. (PR_Preise) die Zeilen 1939 bis 1943 an; hier sind zwei Dinge zu sehen: in Spalte P die besagten Nummern und in den Spalten F-H die gelbe Markierung, die ich testhalber gesetzt habe (also wenn die Const debuggen = true ist), um die Funktionsweise zu überprüfen.
Grundsätzlich ist es (für uns Menschen) nett, den Filter zu verwenden, um Sachen "auszusortieren", aber wie das Wort schon sagt, *programmiere* ich das lieber "sortiert" - nicht "gefiltert".
Das Makro setzt aus den Angaben in "Berechnung" die SortKombi zusammen, springt sie mit einem simplen Suchbefehl an und ...
- liest den *einen* vorhandenen Wert, wenn in Spalte I eine 1 steht oder ...
- kopiert den gelb markierten Bereich in ein Array, eine VBA-interne Speicherstruktur und sucht dort nach Kombi bzw. Modell.
Das Makro:
Option Explicit
Sub Preis_Ermitteln()
Dim i&, k&, z&, anzahl&, B_max&
Dim P_von&, P_bis&, P_max&
Dim c As Range
Dim erster$
Dim finden$
Dim a As Variant, aa As Variant, b As Variant, wert As Variant
Dim preis As Double
Dim gefunden As Boolean, fehler As Boolean
Const debuggen = True  ' ********** auf false setzen, wenn es richtig laufen soll
MsgBox "Debuggen (Testen) = " & debuggen
P_max = PrPreise.Range("A" & Rows.Count).End(xlUp).Row
B_max = Berechnung.Range("BU" & Rows.Count).End(xlUp).Row
If debuggen Then B_max = 2 ' solange debuggen true ist, wird B_max auf 2 gesetzt
For z = 2 To B_max
  fehler = False
  a = Berechnung.Range("K" & z & ":AC" & z)
  ' 1: Modellcode
  ' 4: PrNummern
  ' 5: Modeljahr
  '19: ModellGruppe
  a(1, 4) = Replace(a(1, 4), "/", "")
  aa = Split(a(1, 4), " ")
'  MsgBox LBound(aa) & " " & UBound(aa)
  preis = 0#
  For i = LBound(aa) To UBound(aa)
  If Trim(aa(i)) <> "" Then
  finden = a(1, 5) & a(1, 19) & aa(i)
  If debuggen Then Debug.Print "Zeile Nr. " & z & " Suchen nach: " & finden
  With PrPreise
   Set c = .Range("O2:O" & P_max).Find(finden, LookIn:=xlValues)
   If Not c Is Nothing Then
    P_von = c.Row
    P_bis = .Range("P" & P_von)
    If P_bis = 1 Then
       preis = preis + .Range("N" & P_von).Value
     Else
       b = .Range("F" & P_von & ":H" & P_von + P_bis - 1)
'       MsgBox .Range("F" & P_von & ":H" & P_von + P_bis - 1).Address
       If debuggen Then
       .Range("F" & P_von & ":H" & P_von + P_bis - 1).Interior.Color = vbYellow
       End If
       gefunden = False
       k = 1
       Do
'        Stop
        If (b(k, 3) <> "") And (InStr(1, a(1, 4), b(k, 3)) > 0) Then
            preis = preis + .Range("N" & P_von + k - 1)
            gefunden = True
        End If
          If (b(k, 1) <> "") And (InStr(1, a(1, 1), b(k, 1)) > 0) Then
            preis = preis + .Range("N" & P_von + k - 1)
            gefunden = True
          End If
        k = k + 1
       Loop Until gefunden Or k > P_bis
       If Not gefunden Then preis = preis + .Range("N" & P_von)
     End If
    Else
     fehler = True
   End If
   If debuggen Then Debug.Print preis
  End With
  End If
  Next
  If fehler Then
    Berechnung.Range("BS" & z).Value = "n.v."
   Else
    Berechnung.Range("BS" & z).Value = preis
  End If
Next
End Sub
Die Datei: https://www.herber.de/bbs/user/101624.zip
Happy Exceling,
Michael
P.S.: Das hat so rund 3 Stunden gedauert - mehr nur gegen Kohle.

Bild

Betrifft: Funktioniert es?
von: Michael
Geschrieben am: 20.11.2015 18:56:12
Hallo Frank,
funktioniert's?
Melde Dich doch mal,
Michael

Bild

Betrifft: AW: Funktioniert es?
von: Frank
Geschrieben am: 20.11.2015 21:32:31
Hallo Michael,
zuerst möchte ich mich bei dir für deine Mühe bedanken und mich dafür entschuldigen, dass ich noch nicht geantwortet habe. Frag nicht warum, aber irgendwie habe ich es nicht mehr geschafft mich einzuloggen. Durch deine Nachricht und die Email-Benachrichtigung mit Link ging es dann jetzt.
Dein Makro funktioniert wohl. Ich habe allerdings noch nicht verstanden warum nur ein Wert für eine Zelle ausgegeben wird.
Ich habe zwischenzeitlich auch eine Alternative per Formeln hinbekommen, die zwar nicht schön (über 700 verformelte Hilfsspalten) und schon gar nicht gut für die Excel Performance ist, dafür funktioniert sie. Ich stelle die Datei nächste Woche mal online, ich glaube dein Makro verfolgt eine ähnliche Grundidee.
Vielleicht magst es dir ja noch mal ansehen, ich wäre an beiden Lösungsansätzen interessiert. Will ja auch noch was lernen :-)
Viele Grüße
Frank

Bild

Betrifft: ok,
von: Michael
Geschrieben am: 21.11.2015 00:15:15
Frank,
gut, daß ich nachgehakt habe.
Du hast nicht verstanden warum nur ein Wert für eine Zelle ausgegeben wird? Mehr brauchst Du doch nicht?
Ehrlich gesagt, ich habe lange genug an der VBA-Geschichte rumprobiert, daß ich mir keine 700 Hilfsspalten ansehe, danke.
Zum Verständnis: Gehe das Makro mal mit der F8-Taste durch, dann wird es zeilenweise abgearbeitet, und Du kannst zwischenrein immer in die Tabellen sehen, wo es sich gerade befindet.
Ziemlich mittendrin steht die Zeile

If debuggen Then Debug.Print "Zeile Nr. " & z & " Suchen nach: " & finden
Damit Du siehst, was hier ausgegeben wird, mußt Du im VB das "Direktfenster" öffnen (unter Ansicht oder mit der Tastenkombi Strg+g); dann siehst Du, wonach das Makro gerade sucht.
Natürlich nur, wenn debuggen=true ist.
Schöne Grüße,
Michael

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Preisberechnung mit mehrfachen Kombinationen"