Microsoft Excel

Herbers Excel/VBA-Archiv

Informationsabhängige Formeln

Betrifft: Informationsabhängige Formeln von: Robin
Geschrieben am: 25.09.2020 12:31:47

Hallo zusammen,

ich bin auf ein Problem gestoßen, welches ich nach einigem hin und her überlegen noch nicht lösen konnte und hoffe nun auf Hilfe in diesem Forum.

Zu Info vorab eine kleine Ansichtsdatei. So oder so ähnlich soll dann auch das Endergebnis aussehen.

https://www.herber.de/bbs/user/140432.xlsx

Ich möchte gern abhängig davon welche Informationen ich habe in einer Tabelle Preise und Mengen kalkulieren.

Also je nach dem welche Informationen ich vom Lieferanten bekomme möchte ich zB den EK-Gesamtpreis eintragen und dann berechnen sich automatisch EK-Einzelpreis und EK-Preis (Preis je Quadratmeter). Wenn ich vom Lieferanten nur den Quadratmeterpreis bekomme, dann sollen sich automatisch EK-Einzel- und EK-Gesamtpreis berechnen.

Ebenso wäre es schön, wenn ich den Aufschlag eintrage, dass sich automatisch die VK-Preise eintragen bzw. wenn ich den VK-Preis eintrage sich automatisch der Aufschlag einträgt.

Die Formeln zur Berechnung an sich kenne ich. Ich bekomme nur logischerweise Zirkelbezüge, da die Preise ja immer von einander abhängig sind.

Für einen Lösungsansatz wäre ich sehr dankbar. Wenn das in Excel direkt zu lösen ist umso besser - ansonsten auch einen Umweg über VBA.

Vielen Dank vorab!

Gruß
Robin

Betrifft: AW: Informationsabhängige Formeln
von: Yal
Geschrieben am: 25.09.2020 16:39:51

Hallo Robin,

es ist nicht ganz klar: sprichst vno Einzelne Zeilen oder von der Ergebnis-Zeile?

VG Yal

Betrifft: AW: Informationsabhängige Formeln
von: Robin
Geschrieben am: 25.09.2020 16:46:13

Hallo Val,

es soll jeweils entschieden werden - also in jeder Zeile einzeln.

Ich hoffe es ist klar wie ich das meine - sonst muss ich das noch einmal genauer schildern.

Gruß
Robin

Betrifft: AW: Informationsabhängige Formeln
von: Robin
Geschrieben am: 25.09.2020 16:51:20

Entschuldige bitte - ich meine natürlich "Yal".
Da waren die Finger schneller als die Augen.

Betrifft: AW: Informationsabhängige Formeln
von: Yal
Geschrieben am: 25.09.2020 16:52:54

Jein. Es gibt einfach zu viele Spalten in deinem Quelldatei.

Gibt für eine normale Zeile der Eingangsgrösse und die gewünschte Ergebnisse. Nutze dafür genau die passende Spalten-Überschrift.z.B.:
_ EK-Preis --> EK-Stück als Fläche * EK-Preis und EK-Gesamt as Gesamtfläche * EK-Preis
_ ...

Dasselbe für die Summenzeile.

VG Yal

Betrifft: AW: Informationsabhängige Formeln
von: Robin
Geschrieben am: 25.09.2020 17:09:57

Genau das ist mein Problem.
Ich hatte gehofft, dass es vielleicht einen Profi-Tipp gibt, den ich einfach noch nicht kenne.

Wie gesagt die Formeln für die einzelnen Berechnungen sind jetzt nicht so komplex. Nur das Ganze variabel zu gestalten, dass mal der Einzelpreis und beim nächsten Mal der Gesamtpreis oder Quadratmeterpreis eingetragen werden kann, bekomme ich einfach (noch) nicht hin.

Aber die Hoffnung habe ich noch nicht aufgegeben.

LG
Robin

Betrifft: AW: Informationsabhängige Formeln
von: Yal
Geschrieben am: 25.09.2020 17:35:26

Hallo Robin,

Dann geben ich Dir ein "Muster", den Du anschauen und anpassen sollst.
Diese ist in der Codepane vom Tabelle1 zu legen, weil es den Event "Worksheet_Change" nutzt (in Module funktioniert es nicht).
Die Berechnungen in 1.1 und 1.2 sollten passen. 2.1 und 2.2 nicht. Andere die Ziele und Quelle.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim T, Z, Q
On Error GoTo Catch
    Application.EnableEvents = False
    Set T = Me.ListObjects(1)
    
    Select Case Target.Column
    '1. wird der Einzelpreis gegeben (Preis pro Qm) --> EK-Stück, EK-Gesamt
    Case T.ListColumns("EK-Preis").Column
        '1.1 EK-Stück setzen
        Z = T.ListColumns("EK-Stück").Column 'Ziel
        Q = T.ListColumns("Fläche").Column   'Quelle
        Me.Cells(Target.Row, Z).Value = Me.Cells(Target.Row, Q).Value * Target.Value
        '1.2 EK-Gesamt setzen
        Z = T.ListColumns("EK-Gesamt").Column
        Q = T.ListColumns("Gesamtfläche").Column
        Me.Cells(Target.Row, Z).Value = Me.Cells(Target.Row, Q) * Target.Value
    '2. wird der Gesamtpreis gegeben
    Case T.ListColumns("EK-Gesamt").Column
        '2.1 EK-Preis setzen
        Z = T.ListColumns("EK-Gesamt").Column
        Q = T.ListColumns("EK-Gesamt").Column
        Me.Cells(Target.Row, Z) = Me.Cells(Target.Row, Q) / Target.Value
        '2.2 EK-Gesamt setzen
        Z = T.ListColumns("EK-Gesamt").Column
        Q = T.ListColumns("EK-Gesamt").Column
        Me.Cells(Target.Row, Z) = Me.Cells(Target.Row, Q) / Target.Value
    End Select
    
    GoTo Finally
Catch:
    'No Error Trap
Finally:
    Application.EnableEvents = True
End Sub
Die Berechnungen in der Summen/Ergebniszeil sind klar: nur absolute Werte (Menge gesamt, Fläche gesamt, Preis gesamt) lassen sich summieren ("vertikaler" Bezug) und alles anderes aus diesen Summen ableiten ("horizontaler" Bezug).

Viel Erfolg
Yal

Betrifft: AW: Informationsabhängige Formeln
von: Robin
Geschrieben am: 25.09.2020 23:35:54

Hey Yal,
vielen Dank für die Infos und den Code.
Ich war dann nicht mehr im Büro - werde aber sicher noch am Wochenende den Code mal testen, wenn die Zeit es zulässt.

Vielen Dank und ein erholsames Wochenende!

Besten Gruß
Robin

Betrifft: GELÖST: Informationsabhängige Formeln
von: Robin
Geschrieben am: 29.09.2020 09:25:42

Hallo Yal,

vielen Dank für Deine Hilfe und den Lösungsansatz. Das hat mir wirklich gut weitergeholfen.
Ich konnte aus Deinem Code meine Lösung entwickeln. Das ist bestimmt vom Coding her nicht die feinste Art, aber es funktioniert.

Hier noch meine Lösung falls es Dich interessiert oder jemand anderes beim Suchen auf diesen Thread stößt.

https://www.herber.de/bbs/user/140506.xlsm

Viele Grüße und eine schöne Woche!
Robin

Betrifft: AW: Informationsabhängige Formeln
von: Yal
Geschrieben am: 25.09.2020 17:38:18

... sollte es dazu kommen, dass Du den Code abbrichst, bleibt Excel in dem Zustand
Application.EnableEvents = False

stehen!

Reinitialisiere, in dem Du in der Direktfenster (Strg+g)
Application.EnableEvents = True

eingibst.

Beiträge aus dem Excel-Forum zum Thema "Informationsabhängige Formeln"