PDA

Vollständige Version anzeigen : Public Function DSum


tomica
11.07.2014, 11:07
In einer 2-spaltigen Excel(2003)-Tabelle („Area“) werden Kunden mit entsprechenden Beträgen aufgelistet. Da es Kunden mit mehreren Einträgen und unterschiedlichen Beträgen gibt, möchte ich in per Makro-Funktion den größten Gesamtbetrag feststellen, der ein Kunde auf sich vereint.

Der folgende Funktion funktioniert...

Public Function DB_Summe(Area As Range, Feld As Variant, Criterion As Variant) As Double
DB_Summe = WorksheetFunction.DSum(Area, Feld, Criterion)
End Function

... aber man muss vorher ins Kriterion-Feld den Kundennamen schreiben.

Um dies zu automatisieren, habe ich folgendes versucht...

Public Function DB_Summe(Area As Range, Feld As Variant, Criterion As Variant) As Double
Application.Volatile
..For Each c In Range("Kunde")
Cells(67, 11) = c
X = Application.WorksheetFunction.DSum(Range("Area"), 2, Range("Criterion"))
DB_Summe = WorksheetFunction.DSum(Area, Feld, Criterion)
If DB_Summe > X Then
DB_Summe = X
End If
Next
End Function

...was aber nicht geklappt hat (#WERT!) und ich frage mich wieso.
Range("Kunde") = Spalte mit den Kundennamen
Cells(67, 11) = Zelle unter "Kunde" im Kriteriumbereich
D.h. das Makro soll für jeden Kunden eine DBSUMME errechnen: wenn diese höher ist als der vorherig, sie dann festhalten.

In einem Sub () funktioniert das Konzept, nur man muss das Makro jedes Mal auslösen.
Ich verwende andere Public Functions mit dem For Each…Next-Methode

Für Hinweise vielen Dank im Voraus.

R J
11.07.2014, 12:18
Hi Tomica,

hier
Public Function DB_Summe(Area As Range

übergibst Du bereits ein Range mit dem Namen Area. Dann musst Du es auch verwenden:
..For Each c In Area ' <- so, und nicht so -> Range("Kunde")


Auch bin ich mir nicht sicher, ob Deine ForEach Anweisung so läuft, wie Du Dir das vorstellst. Nicht einmal, ob sie überhaupt notwendig ist! Denn DBSum ist quasi eine Datenbankfunktion. Die bedarf gar keiner Schleife. Und vorallem benötigt sie kein Makro. Die kannst Du fix und fertig nutzen.

Stell am Besten mal eine Beispielmappe ein...

R J
11.07.2014, 12:46
...hab Dir mal ein Beispiel gemacht...

Zai-Ba
11.07.2014, 12:46
Hallo tomica,

ich hab versucht mich in dein Problem einzudenken.
Hast Du dich schon mal mit Pivot-Tabellen auseinander gesetzt? hier kannst Du dir z.B. den Maximalwert jedes Kunden ausgeben lassen.

Um dir konkret helfen zu können, bräuchten wir aber tatsächlich eine Beispiel-Mappe.

Gruß, David

tomica
12.07.2014, 10:50
Hi David und Ralf,

vielen Dank für die Rückmeldungen und das Beispiel. Ja, ich weiß, man kann’s auch mit der Pivot-Funktion machen.

Ich hab’s jetzt mit folgendem Makro bewerkstelligt, das ich auf Ralfs Arbeitsblatt zugeschitten habe.

Sub Total()
summe = 0
For Each c In Range("A1:A13")
[f3] = c
x = Application.WorksheetFunction.DSum(Range("A1:B13"), 2, Range("Bedingung1"))
If x > summe Then
summe = x
[i6] = x
End If
Next
End Sub

Mit einem Klick zeigt I6, dass der betragsgrößte Empfänger 34 auf sich vereint.
G3 muss leer sein
Der Zusatz [i5] = c im Makro (nicht in der Function) zeigt auch welcher Empfänger die 34 erbringt.

Offensichtlich verträgt sich „Public Function DB_Summe“ mit „For Each...“ nicht.

Ralf, Bei DBSumme muss man Kriterien festlegen ist. Ich will Namen solange in die Kriteriumzelle [f3] eingeben, bis fest steht, welcher Empfänger die größte DSum auf sich vereint. Mein Bereich „Area“ umfasst die 2 Spalten „Kunde“ und „Betrag“, Range("Kunde") = nur die Spalte mit den Kundennamen.

Beste Grüsse

R J
12.07.2014, 13:23
Hi Tomica,

ah... jetzt ist klar, was Du wolltest...:)

naja... 'ne DB-Funktion und damit verbundene Kriterienbereiche wären da nicht zwangsläufig erforderlich. Eine Summewenn Funktion wäre da ausreichend...
Sub Total2()
Dim summe&, c, x&
summe = 0
For Each c In Range("A2:A13")
x = Application.WorksheetFunction.SumIf(Range("A2:A13"), c, Range("B2:B13"))
If x > summe Then summe = x
Next

[i6] = x
End Sub

tomica
13.07.2014, 10:30
Hi Ralf,

vielen Dank für Deinen Vorschlag. Es hat geklappt, allerdings im letzten Abschnitt muss es ...[i6] = summe...heißen, ich hatte versehentlich x

If x > summe Then
summe = x
[i5] = c ‘zeigt wer das meiste auf sich vereint
[i6] = summe
End If
Next

Deine Idee mit SumIf hat mich auf die Idee gebracht, DB_Summe in SUMMEWENN (d.h. WorksheetFunction.SumIf) umzufunktionieren und siehe da, es klappt auch mit For Each...!

Public Function DB_Summe(Kunde As Range, Betrag As Range) As Double
summe = 0
For Each c In Range("A2:A13")
DB_Summe = WorksheetFunction.SumIf(Range("A2:B13"), c, Range("B2:B13"))
If DB_Summe > summe Then
summe = DB_Summe
End If
DB_Summe = summe
Next
End Function

Bei der Eingabeaufforderung gibt man bei Kunde und Betrag "A2:B13" bzw. "B2:B13" ein.

Die Makros sind praktischer, zumal man kann sie ausbauen. Man muss nur auf dem Arbeitblatt ein Aktivierungsknopf einbauen.

Da bleibt nur noch die Frage, warum in eine Makro-Funktion WorksheetFunction.SumIf mit For Each funktioniert aber WorksheetFunction.DSum nicht. Ich benutze Excel 2003 in Vista Pro SP2 - möglicher Grund?

Wenn man DBSUMME(…) als ganz normale Formel in einem Arbeitsblatt verwendet, muss nicht das innere Windows-Excel-Rechenwerk eine Art „For Each…“ Verfahren zur Ergebnisermittlung verwenden?

Beste Grüsse und Vielen Dank nochmals.

tomica

R J
13.07.2014, 11:41
Hi Tomica,

einen 'Aktivierungsknopf' brauchst Du nicht unbedingt. Lagere die Funktionen in ein allgemeines Modul aus. Deklariere sie als Public . Dann kannst Du im Menü Makros -> Bearbeiten das Makro markieren und über Optionen einen Shortcut zuweisen.
Aber selbst das ist nicht einmal notwendig. Denn wenn Du sie in ein allgemeines Modul auslagerst, dann kannst Du sie genau wie jede andere Excelfunktion in der Berarbeitungsleiste aufrufen.
Bsp.:
Das Makro im allg. Modul:
'Mit dynamischen Bereich
Public Function Total3(Namensbereich As Range, Wertebereich As Range) As Double
Dim summe&, c, x&
summe = 0
For Each c In Namensbereich
x = Application.WorksheetFunction.SumIf(Namensbereich, c, Wertebereich)
If x > summe Then summe = x
Next

Total3 = summe
End Function

(ist wie Deine Funktion, nur eben in einem allg. Modul)

Dann klickst Du im Arbeitsblatt in die Zelle, in der das Ergebnis angezeigt werden soll und gibst ein:
=Total3(A2:A13;B2:B13)

...voila... und schon hast Du das Ergebnis...:)

...hmmm.... wie erkläre ich jetzt, warum DB-Funktionen nicht mit foreach funktioneren? Ich versuchs mal... DB-Funktionen sind wie SQL-Abfragen. Und SQL basiert auf der Mengenlehre. Zeige mir aus Menge A alle Datensätze an, die meiner Bedingung entsprechen. Und Menge A lässt sich halt nicht über eine Schleife anders definieren. Und wenn sich die Bedingung (und somit die Zielmenge) oder auch die Ausgangsmenge ändern soll, wird eine neue Abfrage benötigt.
Die DB-Funktion selbst kann nicht mittels Schleife durchlaufen werden.

Im Grunde funktioniert SummeWenn auch nicht viel anders, mit dem Unterschied, dass ich hier keinen festen Bereich für die Bedingungen im Blatt definieren muss. Hier wird (wie in unserem Beispiel) jede Zeile der Spalte A durchlaufen und die Summe aller Zeilen aus Spalte B gebildet, die dem aktuellen Wert der Zeile aus Spalte A entsprechen.

In Deinem speziellen Fall war das besondere, dass Du mehrere Abfragen brauchtest. Schaue nach, welche Summe Kunde A hat, dann, welche Summe Kunde B hat.... usw.... Diese Veränderungen waren durch die Änderung in der Schleife möglich, indem Du den Feldwert(c) per Schleife geändert hattest. Die DB-Funktion an sich jedoch nicht, nur einen ihrer Parameter.

...hach.... man... ist wohl doch ziemlich verworren...;)

...nimms einfach hin, so wie es ist...:)

tomica
14.07.2014, 09:01
Hi Ralph,

vielen herzlichen Dank für den letzten Tipp und die Erklärung - die Function funktioniert perfekt!

Beste Grüsse

tomica

tomica
15.07.2014, 08:12
Hi Ralph,

nochmals vielen Dank für die Tipps zur DBSUMME/DSum, die sehr hilfreich waren. Ich setze auch SUMME in diversen {Array}-Formelformaten ein, insbes. zwei oder mehr Bedingungen, typische Beispiele...

{=SUMME(WENN(JAHR($F$2:$F$162)=L$1;WENN($K9=MONAT($F$2:$F$162);$B$2:$B$162;0)))}

{=SUMME(WENN(ZÄHLENWENN($A$1:$A$162;$A$1:$A$162)>1;$B$1:$B$162;0))}

{=SUMME(WENN(ZÄHLENWENN($A$2:$A$162;$A$2:$A$162)>1;1/ZÄHLENWENN(A2:A162;A2:A162);0))}

{=SUMME(WENN(D2:D161<>"";1/ZÄHLENWENN(D2:D161; D2:D161); 0))}

Was mir aber bisher nicht gelungen ist, ist die Verwendung von „ODER“ in dieser Konstellation etwa im 2. Beispiel...

....WENN(ODER(ZÄHLENWENN($A$1:$A$162;$A$1:$A$162)<100; ZÄHLENWENN($A$1:$A$162;$A$1:$A$162)>1000);...

Weißt Du. ob dies möglich ist?

Beste Grüsse

tomica

R J
15.07.2014, 10:09
Hi Tomica,

für UND oder ODER - Verknüpfungen eigenen sich SummeWennS oder ZählenWennS - Funktionen.

Zur Anwendung in Matrixformeln, s. hier... (http://www.excelformeln.de/formeln.html)

tomica
15.07.2014, 20:26
Hi Ralph,

vielen Dank für den Link-Hinweis, dort sind einige sehr interessante Formeln. Ich konnte aber keine SUMMEWENNS mit ODER finden. Selbst hier, um Deinem Vorschlag nachzugehen...

http://forum.chip.de/office/excel-2010-man-summewenns-verknuepfen-1748809.html

http://www.*************************353479_0_0_asc.php

...wird immer auf SUMMENPRODUKT zurückgegriffen - wie siehst Du’s?

Beste Grüsse

tomica

R J
16.07.2014, 06:19
Hi Tomica,

stimmt. Wie Du ODER bei SummeWenn verwendest wird hier (http://support.microsoft.com/?id=267982) verdeutlicht.
Falls mehr Fragen zu diesem Thema bestehen, mach bitte einen neuen Thread auf, denn so langsam weichen wir von der Ursprungsfrage ab...:)

tomica
17.07.2014, 09:32
Hi Ralph,

vielen herzlichen Dank nochmals - der Trick mit dem „+“ Zeichen war das, was ich suchte.

Beste Grüsse