Live-Forum - Die aktuellen Beiträge
Datum
Titel
24.04.2024 00:20:14
Bestimmte Spalten als xls exportieren per Makro
23.04.2024 22:08:10
Auflisten in Spalte VBA
23.04.2024 21:33:46
Nur farbige/nicht farbige Zellen markieren
23.04.2024 21:29:56
Bestimmte Zellen auf andere Arbeitsblätter kopieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
972to976
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
972to976
972to976
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Aus komplexer Formel Funktion erstellen?

Aus komplexer Formel Funktion erstellen?
28.04.2008 22:35:03
Peter
Guten Abend
Zwecks Berechnung von Salden aus einer grossen Datenmenge habe ich mir folgende Formel geschrieben:
=(SUMMENPRODUKT((Ref=$I15)*((LINKS(_C)="C")*(_A0))*_A) +SUMMENPRODUKT((RefW=$I15)*((LINKS(_C)="D")*(_A0))*_A))
Anmerkungen:
- Die Formel steht in Klammer, da ich ab und zu das Ergebnis als negative Zahl brauche (dann muss ich nur nach dem ersten Gleich (=) Zeichen ein Minus voranstellen
- Die Bezeichnungen Ref, _C; _A; und RefW sind alles Spaltenbereihe einer Tabelle
- Der einzige Bezug im Tabellenblatt, in dem diese Formel mehrfach verwendet wird, steht in Spalte I (hier Zeile 15)
Diese Formel wird in meinem Team immer wieder gebraucht - "leider" sieht sie etwas kompliziert aus. Deshalb habe ich mir überlegt, ob diese Formel nicht in eine Funktion gepackt werden könnte.
Meine Fragen:
- Ist das möglich / sinnvoll?
- Wenn ja: könnte ich jeweils nur die Bezugszelle (hier I15) übergeben oder müssen auch die Bereiche als Argumente in der Funktion mitgegeben werden (Alternativ: in Funktionscode einbauen).
- Kann mir jemand eine Starthilfe geben? (eine sehr vereinfachte Musterdatei habe ich hochgeladen).
Vielen Dank, Peter
https://www.herber.de/bbs/user/51942.xls

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Aus komplexer Formel Funktion erstellen?
28.04.2008 22:59:17
Daniel
Hi
naja, so furchtbar komplex ist die Funktion auf jeden fall nicht, aber wenn sie häufig benötigt wird, ist es sicherlich einfacher, sie als feste Funktion zu schreiben.
zur 2. Frage:
- beides ist möglich, du kannst sowohl alle Parameter in der Formel übergeben, musst aber nicht, wenn sich die Funktion immer auf die gleichen Zellbezüge bezieht, kann man diese auch fest in die Formel einprogrammieren.
Was Sinnvoller ist, hängt davon ab, wie flexiblel die Funktion sein soll.
wenn du nur den einen Parameter (I15) übergibst, dürfen sich die anderen Werte und Zellbezüge nicht ändern, dh. wenn du später mal REF in ABC umbenennen willst, müsstest du dann die Programmierung ändern.
Wird der Zellbezug als Parameter übergeben, kann es der Anwender selbst in der Formel festlegen.
zur 3. Frage
was weißt du schon und was benötigtst du als Starthilfe?
Gruß, Daniel

Anzeige
AW: Aus komplexer Formel Funktion erstellen?
29.04.2008 07:11:00
Peter
Hallo Daniel
Vielen Dank für deine Antworten. Die Namen der benannten Namen sind immer gleich.
zur 3. Frage:
Ich müsste wissen, wie man den Bezug als Übergabewert dimensioniert und wie man die benannten Bereiche in der neuen Funktion an SUMMENPRODUKT übergibt - weitere Fragen ergeben sih dann wohl noch beim weiteren Erarbeiten.
Gruss, Peter

AW: Aus komplexer Formel Funktion erstellen?
28.04.2008 23:08:00
Renee
Hallo Peter,
Ich würde die Formel nicht als Funktion schreiben. VBA ist immer langsamer als Intrisinc-Functions von Excel. Die Formel ist nicht trivial, weil Summenproduktformeln implizite Array-Funktionen beinhalten. Und ja, wenn du sie als Funktion schreiben willst müsstest du auch die anderen Referenzen mitgeben können, ausser die Funktion kann davon ausgehen, das die benannten Bereiche immer als solche fixen Namen bestehen. Wenn letzteres der Fall ist (wie in deinem Beispiel) könnte folgendes Makro (in ein Modul) die Formel einfach in die aktive Zelle schreiben, wobei sie vorher nach der Referenzzelle und dem Vorzeichen fragt, probiers mal in deinem Beispiel aus:

Sub SuperFormel()
Const sTitle = "Peter's SuperFormel"
Dim rInpRef As Range
If Selection.Count > 1 Then
MsgBox "Es darf nur eine Zelle aktiv sein!", vbOKOnly + vbExclamation, sTitle
Exit Sub
End If
Set rInpRef = Application.InputBox("Klicken sie auf die Referenzzelle:", _
sTitle, "$A$1", , , , , 8)
If rInpRef Is Nothing Then Exit Sub
Select Case MsgBox("Ja   = Positiv" & vbCrLf & _
"Nein = Negativ", vbYesNoCancel, sTitle)
Case vbYes
Selection.Formula = "=(SUMPRODUCT((Ref=" & rInpRef.Address & _
")*((LEFT(_C)=""C"")*(_A0))*_A)+SUMPRODUCT((RefW=" & rInpRef.Address & _
")*((LEFT(_C)=""D"")*(_A0))*_A))"
Case vbNo
Selection.Formula = "=-(SUMPRODUCT((Ref=" & rInpRef.Address & _
")*((LEFT(_C)=""C"")*(_A0))*_A)+SUMPRODUCT((RefW=" & rInpRef.Address & _
")*((LEFT(_C)=""D"")*(_A0))*_A))"
End Select
End Sub


GreetZ Renée

Anzeige
AW: Aus komplexer Formel Funktion erstellen?
29.04.2008 07:04:00
Peter
Hallo Renée
Vielen Dank. Ich möchte gerne mal deine Lösung weiterziehen und dann paralell mal die Funktion ausprobieren.
Ich habe den Code ausprobiert und es hat auf Anhieb geklappt. Ich konnte auch noch je eine zusätzliche Select Case einfügen, da (in meiner Frage nicht erwähnt), aktuelle Zahlen im Bereich "_A" und Vorjahreszahlen im Bereich "_V" zu finden sind (vgl. ganz unten).
Nun habe ich noch eine Frage zu der Codezeile
Set rInpRef = Application.InputBox("Klicken sie auf die Referenzzelle:", _
sTitle, "$A$1", , , , , 8)
Ich möchte die Referenzzeile gerne automatisch übernehmen. Dies würde nach folgender Logik funktionieren:
Angenommen, der Cursor steht auf C8, dann ich die Referenzzeile die letzte Zelle in Zeile 8 mit Inhalt
Kannst du mir sagen, wie diese Abfrage erfolgen muss?
Vielen Dank, Peter

Sub SuperFormel()
Const sTitle = "Peter's SuperFormel"
Dim rInpRef As Range
If Selection.Count > 1 Then
MsgBox "Es darf nur eine Zelle aktiv sein!", vbOKOnly + vbExclamation, sTitle
Exit Sub
End If
Set rInpRef = Application.InputBox("Klicken sie auf die Referenzzelle:", _
sTitle, "$A$1", , , , , 8)
If rInpRef Is Nothing Then Exit Sub
Select Case MsgBox("Ja   = Positiv" & vbCrLf & _
"Nein = Negativ", vbYesNoCancel, sTitle)
Case vbYes
Select Case MsgBox("Ja   = Aktuelles_Jahr" & vbCrLf & _
"Nein = Vorjahr", vbYesNoCancel, sTitle)
Case vbYes
Selection.Formula = "=(SUMPRODUCT((Ref=" & rInpRef.Address & _
")*((LEFT(_C)=""C"")*(_A0))*_A)+SUMPRODUCT((RefW=" & rInpRef.Address & _
")*((LEFT(_C)=""D"")*(_A0))*_A))"
Case vbNo
Selection.Formula = "=(SUMPRODUCT((Ref=" & rInpRef.Address & _
")*((LEFT(_C)=""C"")*(_V0))*_V)+SUMPRODUCT((RefW=" & rInpRef.Address & _
")*((LEFT(_C)=""D"")*(_V0))*_V))"
End Select
Case vbNo
Select Case MsgBox("Ja   = Aktuelles_Jahr" & vbCrLf & _
"Nein = Vorjahr", vbYesNoCancel, sTitle)
Case vbYes
Selection.Formula = "=-(SUMPRODUCT((Ref=" & rInpRef.Address & _
")*((LEFT(_C)=""C"")*(_A0))*_A)+SUMPRODUCT((RefW=" & rInpRef.Address & _
")*((LEFT(_C)=""D"")*(_A0))*_A))"
Case vbNo
Selection.Formula = "=-(SUMPRODUCT((Ref=" & rInpRef.Address & _
")*((LEFT(_C)=""C"")*(_V0))*_V)+SUMPRODUCT((RefW=" & rInpRef.Address & _
")*((LEFT(_C)=""D"")*(_V0))*_V))"
End Select
End Select
End Sub


Anzeige
AW: Aus komplexer Formel Funktion erstellen?
29.04.2008 09:52:44
Renee
Hi Peter,
Ich hab das Makro mal angepasst, so das die Referenzzelle aus der Spalte mit dem letzten Eintrag in der aktuellen Zelle geholt wird. Ebenso hab das Ganze auf nur eine Abfrage verkürzt (das scheint mir benutzerfreundlicher). Versuch mal diesen Code:

Sub SuperFormel()
Const sTitle = "Peter's SuperFormel"
Dim sInpRef As String,  sCase As String, sSign As String, sYear As String
If Selection.Count > 1 Then
MsgBox "Es darf nur eine Zelle aktiv sein!", vbOKOnly + vbCritical, sTitle
Exit Sub
End If
sInpRef = ActiveSheet.Cells(Selection.Row, ActiveSheet.Columns.Count).End(xlToLeft).Address
If Selection.Row > ActiveSheet.Range(sInpRef) Then
MsgBox "Rechts von dieser Zelle befindet sich keine Referenz!", _
vbOKOnly + vbCritical, sTitle
Exit Sub
End If
sCase = InputBox("Geben Sie den Fall an:" & vbCrLf & _
"1 = AKTUELLES Jahr (POSITIV)" & vbCrLf & _
"2 = AKTUELLES Jahr (NEGATIV)" & vbCrLf & _
"3 = VORjahr (POSITIV)" & vbCrLf & _
"4 = VORjahr (NEGATIV)", sTitle, "1")
If sCase = "" Then Exit Sub
Select Case sCase
Case "1"
sYear = "_A"
Case "2"
sYear = "_A": sSign = "-"
Case "3"
sYear = "_V"
Case "4"
sYear = "_V": sSign = "-"
Case Else
MsgBox "Ungültige Auswahl!" & vbCrLf & _
"Keine Formel eingefügt!", vbOKOnly + vbCritical, sTitle
End Select
If sYear  "" Then
Selection.Formula = "=" & sSign & "(SUMPRODUCT((Ref=" & sInpRef & _
")*((LEFT(_C)=""C"")*(" & sYear & "0))*" & sYear & ")+SUMPRODUCT((RefW=" & sInpRef & _
")*((LEFT(_C)=""D"")*(" & sYear & "0))*" & sYear & "))"
End If
End Sub


GreetZ Renée

Anzeige
AW: Aus komplexer Formel Funktion erstellen?
29.04.2008 10:16:43
Peter
Hallo Renée
Das ist natürlich eine komfortable Lösung. Vielen Dank. Ich habe lediglich in untenstehender Codezeile noch (0,0) eingefügt, damit ich die Formeln bei Bedarf innerhalb der Spalte kopieren kann.
Gruss, Peter
sInpRef = ActiveSheet.Cells(Selection.Row, ActiveSheet.Columns.Count).End(xlToLeft).Address(0, 0)

AW: Aus komplexer Formel Funktion erstellen?
29.04.2008 10:20:51
Peter
Hallo Renée
Noch eine Verständnisfrage. Ist : (Doppelpunkt) eine zulässige Abgrenzung, wenn man mehrere Zuweisungen machen will?
Gruss, Peter
Case 2
sYear = "_A": sSign = "-"

AW: Aus komplexer Formel Funktion erstellen?
29.04.2008 10:24:00
Renee
Hi Peter,
Ist : (Doppelpunkt) eine zulässige Abgrenzung...
Doppelpunkt ist eine Abgrenzung um mehrere Befehle auf der gleichen Zeile zu schreiben, Also nicht nur Zuweisungen.
GreetZ Renée

Anzeige
AW: Vielen Dank!, owT
29.04.2008 10:26:00
Peter

Links zu Excel-Dialogen

InputBox

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige