Zelle mit mehreren Funktionen belegen

Forumthread: Zelle mit mehreren Funktionen belegen

15.07.2020 15:56:32

Tobias

Hallo zusammen,
ich möchte in eine Zelle entweder einen Wert eingeben oder eine Formel hinterlegen.
Evtl. mit einer WENN Funktion? Ich scheitere leider in der Umsetzung :)
Vielleicht könnt ihr mir helfen.
Es geht um die Kalkulation eines Verkaufspreis. Ich kalkuliere hier mit Prozentsätzen je nach Artikel. Dadurch entstehen äußerst "krumme" VK's, diese werden anschließend manuell angepasst um einen schönen Preis zu erzielen. Nach der Anpassung stimmt der Deckungsbeitrag% allerdings nicht mehr. Hierfür hätte ich gerne eine schicke Lösung. Die aktuelle Lösung findet ihr hier
https://www.herber.de/bbs/user/139076.xlsx
Grundsätzlich soll in Zelle H3 der VK mit der Formel =-E3/(G3-1) berechnet werden.
Die Zellen D3, E3 und G3 werden manuell befüllt.
Wenn in H3 aber eine Zahl eingetragen wird soll G3 die Formel =100%-(E4/H4) umsetzten.
Die Zellen G3 und H3 müssen also alternativ eine Formel oder eine Eingabe annehmen.
Bei Rückfragen gerne melden.
Vielen Dank euch.
Tobias

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Entweder 2 Zellen mehr oder ein Event nehmen

15.07.2020 16:28:05

lupo1

AW:Formel ODER fester Wert, keine Alternative

15.07.2020 16:31:58

EtoPHG

Hallo Tobias,
Die Frage wurde schon Myriaden mal gestellt und immer negativ beantwortet.
Bei deiner Levelangabe müsste das eigentlich bekannt sein!
Mit VBA kann man das vlt. ""hinwürgen, aber ich würde ein solche "Kalkulation" als nicht brauchbar ablehnen. Das Problem kann man mit Hilfsspalten umgehen und die müssen dem Anwender ja nicht unbedingt gezeigt werden.
Gruess Hansueli

AW: AW:Formel ODER fester Wert, keine Alternative

15.07.2020 16:37:44

Tobias

Hi Hansueli,
welches Problem siehst du bei VBA? Würde die Kalkulation verfälscht werden?
Mit Hilfsspalten ist das ganze vermutlich nicht mehr so schön...
Gruß Tobias

Das es ein Gewürge ist, wie gesagt!

15.07.2020 16:44:23

EtoPHG

Tobias,
Muss denn deine Lösung schön und schick sein?
Es zählt also nicht Funktion, Sicherheit, Verständlichkeit, Nachvollziehbarkeit?
Gruess Hansueli

AW: Das es ein Gewürge ist, wie gesagt!

15.07.2020 17:54:41

Tobias

In erster Linie steht der Komfort für den Bediener. Es soll selbsterklärend und einfach sein.
Die Datei wird von mehreren Kollegen genutzt. Deshalb möchte ich nicht mit vielen Spalten für Verwirrung sorgen.
Hast du einen konkreten Vorschlag?
Gruß Tobias

Wenn eine Eingabe sowohl in G als auch H ...

15.07.2020 20:26:36

Luc:?

…möglich sein soll, Tobias,
wäre die sauberste KompromissLösung wohl, beide Spalten wie EingabeSpalten zu behandeln und dort keine Fmln, sondern nur ihre Ergebnisse einzutragen. Das hieße, Lupos 2.Vorschlag aufzugreifen und mit der VBA-EreignisProzedur Worksheet_Change zu arbeiten. Dann könnte man auch wechselseitig solange Werte eintragen bis das Ergebnis gefällt. D.h., die Berechnung wird in die Prozedur verlagert. Wird ein Wert in G eingetragen, wird sofort der passende Wert in gleicher Zeile von H ermittelt und eingetragen und umgekehrt.
Sieh dir das mal an!
Gruß, Luc :-?
Die universelle Befähigung zur Unfähigkeit macht jede menschliche Leistung zu einem unglaublichen Wunder. Stapps ironisches Paradoxon
Nichtsdestotrotz Durchblick verbessern mit

Alternative mit mehr Nutzerverantwortung

16.07.2020 13:18:28

Luc:?

Es geht auch mit Fmln, Tobias,
aber die dürfen nicht in den Zellen stehen, sondern müssen in benannte Fmln mit definierten Namen ausgelagert wdn. Das erfordert dann allerdings, dass der Nutzer nicht nur in einer Spalte eine Zahl, sondern in der anderen dann auch einen Verweis auf die benannte Fml in FmlForm einträgt. In deinem Bsp sähe das so aus:
G3: 10,00% → H3: =Rechne
H3: 555,56 → G3: =Rechne
Die benannte Fml sähe dann so aus:
defName: Rechne
Bezug: =WAHL(SPALTE()-6;RUNDEN(100%-$E3/$H3;4);RUNDEN(-$E3/($G3-1);2))
Bei ihrer Definition sollte eine Zelle in Zeile 3 ausgewählt sein!
In diesem Fall ist es sicher angeraten, einen möglichst kurzen und prägnanten Namen zu wählen, der auf beide Ermittlungen passt. Dann kann man genauso vorgehen wie bei einer VBA-EreignisProzedur-Variante, hat nur nicht soviel Komfort und dafür etwas mehr Arbeit und Eigenverantwortung
Luc :-?

Korr: In der 2.Zeile natürlich H4 und G4! owT

16.07.2020 13:31:29

Luc:?

:-?

AW: Wenn eine Eingabe sowohl in G als auch H ...

16.07.2020 13:43:14

Tobias

Hi Luc,
da wären wir dann beim Thema "VBA bescheiden." :)
Ich hatte mal was begonnen - aber nicht erfolgreich.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Zelle As Range
Dim f As Variant
Dim Zeilenzahl As Long
Zeilenzahl = WorksheetFunction.Max(ActiveSheet.Cells(Rows.Count, 6).End(xlUp).Row, _
ActiveSheet.Cells(Rows.Count, 7).End(xlUp).Row)
For Each Zelle In ActiveSheet.Range("G7:G" & Zeilenzahl)
f = Zelle.Offset(0, -1).Value
If a = "" Or IsNumeric(a) = False Then
Zelle.Value = ""
Else
If Zelle.Value = "" Then Zelle.Formula = "100%-(RC(-1)/RC(+2))"
End If
Next Zelle
End Sub

Grüße Tobias

Der Vollständigkeit halber noch 1 pass'de EvProz

19.07.2020 01:31:47

Luc:?

Mit folgender EreignisProzedur sollte es gehen, Tobias:

Private Sub Worksheet_Change(ByVal Target As Range)
Const adRelBer$ = "G3:H7"
Static relCols(1) As Long
If Target.Cells.Count = 1 And Not Intersect(Target, _
Me.Range(adRelBer)) Is Nothing Then
If relCols(0) = 0 Or relCols(1) = 0 Then
relCols(0) = Me.Range(adRelBer).Columns(1).Column
relCols(1) = Me.Range(adRelBer).Columns(2).Column
End If
If Not IsEmpty(Target) Then
With Application
.EnableEvents = False
With Target
Select Case .Column
Case relCols(0): .Offset(0, 1) = _
Round(-.Offset(0, -2) / (.Value2 - 1), 2)
Case relCols(1): .Offset(0, -1) = _
Round(1 - .Offset(0, -3) / .Value2, 4)
End Select
End With
.EnableEvents = True
End With
End If
End If
End Sub

Die Konstante adRelBer kannst du so festlegen wie erforderlich. Der Abstand der in die Berechnungen eingehenden Spalten sollte gleich bleiben. Anderenfalls müssten auch die Target.Offset-Werte geändert wdn.
Und falls du mal mitten aus dem Programm aussteigst, noch eine EventFlipFlop-Prozedur, die ausgeschaltete Ereignisse ein- und eingeschaltete ausschaltet:

Sub EFF()
With Application: .EnableEvents = Not .EnableEvents: End With
End Sub

Morhn, Luc :-?

AW: Der Vollständigkeit halber noch 1 pass'de EvProz

20.07.2020 17:08:04

Tobias

Hi Luc,
du bist ein geiler Typ - da bleibt mir nur zu sagen GOOD JOB!
Ist genau wie ich es mir vorgestellt habe. Vielen Dank dafür :)
Beste Grüße
Tobias

Beliebteste Forumthreads (12 Monate)

Zelle mit mehreren Funktionen belegen

Forumthread: Zelle mit mehreren Funktionen belegen

11 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

11
Beiträge zum Forumthread

Beiträge zu diesem Forumthread