Datenschutzerklärung


Direktnachricht



Ihre Software
Details
Excel/VBA 🔍
Add-Ins

Suche in Beispielen und Tipps zu Excel und VBA

Suchbegriff(e) mit Leerzeichen getrennt:

Matrixformel: Zahlen in Datumsspanne addierenFormellösungArrayfunktion/Matrixfunktion

Kategorien: Datum/Zeit ▸ Datum und Tabelle ▸ Matrix

(Tipp 327) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich Zahlen zu Datumsangaben, die in einem bestimmten Bereich liegen, addieren?

Ab Excel 365: Funktion FILTER()

Gegeben:

  • Spalte A im Datumsformat. Daten von 01.01.99 bis 31.12.99
  • Spalte B im Zahlenformat. Zahlen wie 2.340,00
  • Spalte C im Textformat. Immer vierstellige Zahlen wie 9000

Anforderung:

Summe der Zahlen in B, wenn das Datum in A in einem bestimmten Zeitraum liegt und in C eine bestimmte Zahl steht.

Beispiel: In E1 und E2 stehen Beginn und Ende der gewünschten Zeitspanne, in E3 die geforderte Zahl, die in C enthalten sein soll.

Formel:

Die Arrayfunktion FILTER(B1:B35;(A1:A35>=E1)*(A1:A35<=E2)*(C1:C35=E3);0) liefert die Zahlen zu den Zeilen, die den Kriterien entsprechen. Das heißt, dass diese nur addiert werden müssen:

=SUMME(FILTER(B1:B35;(A1:A35>=E1)*(A1:A35<=E2)*(C1:C35=E3);0))

Wochendaten zur Zahl einer Kalenderwoche bestimmenMakro/Sub/ProzedurUDF - benutzerdefinierte Funktion

Kategorie: Datum/Zeit ▸ Datum

(Tipp 430) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich aus der Zahl einer Kalenderwoche die entsprechenden Daten der Woche errechnen?

Die folgende Funktion stellt ersten und letzten Tag der Kalenderwoche fest und gibt auf dieser Basis den Zeitraum aus:

Function Zeitraum(ByVal Jahr As Integer, ByVal KW As Variant) As String Dim datBeg As Date, lngT As LongPtr Application.Volatile Zeitraum = "" If IsNumeric(Jahr) And IsNumeric(KW) Then lngT = DateSerial(Jahr, 1, 4) lngT = lngT - Weekday(lngT, 2) + 7 * KW - 7 If (Year(lngT + 4) = Jahr) Then datBeg = lngT + 1 Zeitraum = Format(datBeg, "DD.MM.YYYY") & " - " & Format(datBeg + 6, "DD.MM.YYYY") End If End Function

In die Zelle muss dann nur noch:

=Zeitraum(Jahr;KW)

Soll es ohne Funktion in der Zelle funktionieren und der Zeitraum sofort eingetragen werden, kann folgender Code in das Klassenmodul der jeweiligen Tabelle (Doppelklick auf Tabelle im VBA-Editor):

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row >= 4 And Target.Column = 1 Then Application.EnableEvents = False Target = Zeitraum(Range("B1"), Target) Application.EnableEvents = True End If End Sub

Der Code wird ausgeführt, wenn die Eingabezelle in Spalte 1 und Zeile >= 4 liegt; verwendet wird das Jahr aus Zelle B1. Application.EnableEvents = False ist hier wichtig, weil die eingegebene KW durch den Zeitraum überschrieben wird und das Change-Ereignis sonst immer wieder aufgerufen würde.