Microsoft Excel

Herbers Excel/VBA-Archiv

Funktion funktioniert nicht mit Datum

Betrifft: Funktion funktioniert nicht mit Datum von: Markus
Geschrieben am: 28.10.2014 18:39:25

Hallo zusammen,

ich habe im englischen microsoft-forum eine tolle Funktion gefunden (MVP Jeeped).

Summe über mehrere Tabellenblätter mit variablen Namen.

  • 
    Public Function fcnSumIfMySheets(crit As Range, strt As Long, fnsh As long) As Double
        On Error Resume Next
        Dim dCondSum As Double
        Dim lBgn As Long
        Dim lEnd As Long
        lCondSum = 0
        lBgn = strt
        lEnd = fnsh
        For i = lBgn To lEnd    'e.g. from Sheetx to Sheety
            If Not Worksheets(i) Is Nothing Then
                dCondSum = dCondSum + Application.SumIf(Worksheets(i).Range("A:A"), crit.Value,  _
    Worksheets(i).Range("C:C"))
            Else
                fcnSumIfMySheets = dCondSum 
                Exit Function
            End If
        Next
        fcnSumIfMySheets = dCondSum
    End Function


  • Das funktioniert auch ganz gut, nur habe ich das Problem dass das Format im Suchbereich (Range("A:A") bsp. 01.01.2015 -01.04.2015 ist und ich wie beim Summenprodukt alle Werte des Jahres 2015 (Range("C:C")) summieren möchte.

    Kann mir da jemand einen Tipp geben?

    Danke.

      

    Betrifft: Das ist ja auch kein Wunder, ... von: Luc:-?
    Geschrieben am: 28.10.2014 19:56:30

    …Markus,
    denn diese für einen MVP doch reichlich primitive UDF (benutzerdefinierte Fkt) arbeitet intern ja auch mit der XlStandard­Fkt SumIf (SUMMEWENN), die keine Daten­Felder in ihren Argumenten zulässt. Mit JAHR(A:A) würdest du aber ein solches erzeugen! Und das ist eben primär keine Frage des Formats, sondern eine der notwendigen Auswertung, die auch keinen (hier alternativ möglichen) Like-Vgl vorsieht. Sie addiert quasi nur die Einzel­Ergebnisse von SumIf, was hier nicht ausreicht.
    Mein Vorschlag → Vergiss die UDF und verwende folgd Fml:
    =SUMMENPRODUKT(N(INDIREKT("Tabelle"&SPALTE(A:B)&"!C"&ZEILE(1:10))) *(JAHR(N(INDIREKT("Tabelle"&SPALTE(A:B)&"!A"&ZEILE(1:10))))=2015))
    Hierbei steht SPALTE(A:B) (→hier 1…2) für die reguläre Durch­Nummerierung deiner Blätter, die auch alle den gleichen Namens­Text aufweisen sollten (sonst müssen alle einzeln aufgeführt wdn → der einzige Vorteil der UDF, die die flfd Nr benutzt). ZEILE(1:10) steht für die Werte Insgesamt pro Blatt, wobei 10 der (möglichen) Maximal­Werte­Anzahl einer Spalte entsprechen sollte (falls ein Blatt weniger enthält, sollten die überzähligen leer/nicht-numerisch sein). Die Fkt N „normiert“ quasi das (numerische) Ergebnis von INDIREKT und ist deshalb auch hier unverzichtbar!
    Gruß, Luc :-?

    Besser informiert mit …


      

    Betrifft: AW: Das ist ja auch kein Wunder, ... von: markus
    Geschrieben am: 28.10.2014 21:14:04

    Hallo Luc :-?,

    vielen dank für Deine ausführliche Antwort.
    Das Problem ist dass meine Tabellennamen aus zellwerten erstellt werden und diese ganz unterschiedlich sind. Deshalb meine wahl für die UDF.

    ursprünglich wollte ich auch über eine Formel mit Summenprodukt und indirekt.

    Grüße


      

    Betrifft: mit Zwischensumme von: Tino
    Geschrieben am: 28.10.2014 20:33:28

    Hallo,
    kannst mal so versuchen.

     EFGH
    1Jahrvon Tabellebis TabelleSumme
    2201513670

    Formeln der Tabelle
    ZelleFormel
    H2=SumJahr(E2;F2;G2)


    Code kommt in ein Modul

    Function SumJahr(intJahr%, TabVon%, TabBis%)
    Dim i%, SumTmp, ZWSum
    Dim vonDate&, bisDate&
    Application.Volatile
    On Error GoTo ErrorHandler:
    
    vonDate = DateSerial(intJahr, 1, 1)
    bisDate = DateSerial(intJahr, 12, 31)
    
    
    For i = TabVon To TabBis
         With Worksheets(i)
            SumTmp = Application.WorksheetFunction.SumIf(.Columns(1), ">" & bisDate, .Columns(3))
            ZWSum = Application.WorksheetFunction.SumIf(.Columns(1), ">=" & vonDate, .Columns(3))
            ZWSum = ZWSum - SumTmp
         End With
         SumJahr = SumJahr + ZWSum
    Next i
    
    ErrorHandler:
    End Function
    Gruß Tino


      

    Betrifft: AW: mit Zwischensumme von: Markus
    Geschrieben am: 28.10.2014 21:15:43

    Hallo Tino,

    das funktioniert perfek, Danke.

    Zwischenzeitlich habe ich mir mit einer Hilfsspalte (Jahr(A2) beholfen, aber so ist mir das schon lieber.

    Super!


     

    Beiträge aus den Excel-Beispielen zum Thema "Funktion funktioniert nicht mit Datum"