Hallo Lukas,
mit CountIfs kommst du da nicht weiter. Denn damit kannst du immer nur einen Kunden und ein Jahr in Kombination auswerten. Beides musst du zuvor wissen um dich in der Formel darauf beziehen zu können. Ich empfehle dir folgende Vorgehensweise: Zunächst solltest du in einer Hilfsspalte das Jahr ermitteln (z.B. =JAHR(H24)) . Dann erstellst du auf Basis deiner Datentabelle eine Pivot-Tabelle und ziehst die Felder Kunde und Jahr in das Zeilenfeld, sowie nochmals Jahr in das Wertefeld. Nun nur noch die Darstellung auf Anzahl von Jahr ändern und schon hast du die gewünschte Auswertung. Das Blatt kannst du natürlich auch exportieren und an eine Email anhängen. Das ganze ist in ca. 3 Minuten erledigt. Ganz ohne VBA!
Wenn du eine VBA-Lösung bevorzugst, kannst du natürlich auf Hilfsspalte und Pivot-Tabelle verzichten. Allerdings solltest du dich dann ein wenig mit Klassenprogrammierung auskennen. Denn diese ist hierfür am geeignetsten um die Daten verständlich zu ermitteln. Allerdings auch etwas aufwendig. Ausnahmsweise hab ich da mal ein kleines Beispiel erstellt. Um den Code möglichst simpel und gut verständlich zu halten, habe ich hier auf viele von mir üblicherweise verwendete Standards verzichtet. Falls du dennoch Fragen hast, einfach melden.
Erstelle ein neues Klassenmodul und gib diesem den Namen "Kunden". Gib dort dann den folgenden Code ein:
Dim iKunden As New Collection
Public Function Hinzu(Name As String) As Kunde
If Not Exists(Name) Then
iKunden.Add New Kunde, Name
Set Hinzu = iKunden(iKunden.Count)
Hinzu.ID = iKunden.Count
Hinzu.Name = Name
Else
Set Hinzu = iKunden(Name)
End If
End Function
Public Property Get Item(index) As Kunde
Set Item = iKunden(index)
End Property
Public Property Get Count() As Long
Count = iKunden.Count
End Property
Public Property Get Exists(Name) As Boolean
On Error Resume Next
Exists = iKunden(Name).ID <> 0
End Property
Erstelle ein zweites Klassenmodul "Kunde" und gib dort diesen Code ein:
Public ID As Long
Public Name As String
Public Jahre As New Jahre
Erstelle ein drittes Klassenmodul "Jahre" und gib dort diesen Code ein:
Dim iJahre As New Collection
Public Property Get Count() As Long
Count = iJahre.Count
End Property
Public Function Hinzu(Jahr As Integer) As Jahr
If Not Exists(Jahr) Then
iJahre.Add New Jahr, CStr(Jahr)
Set Hinzu = iJahre(iJahre.Count)
Hinzu.ID = iJahre.Count
Hinzu.Jahr = Jahr
Else
Set Hinzu = iJahre(CStr(Jahr))
End If
End Function
Public Property Get Item(index) As Jahr
Set Item = iJahre(index)
End Property
Public Function Exists(Jahr As Integer) As Boolean
On Error Resume Next
Exists = iJahre(CStr(Jahr)).ID <> 0
End Function
Erstelle ein viertes Klassenmodul "Jahr" und gib dort diesen Code ein:
Public ID As Long
Public Jahr As Integer
Public AnzVorgaenge As Long
Erstelle nun noch ein Standardmodul "Modul1" und gib dort dein eigentliches Makro ein. Nehmen wir mal an die Kunden stünden in Spalte A und die Datumsangaben in Spalte B:
Sub DatenSammeln()
Dim Kunden As New Kunden
Dim lastrow As Long, actKunde As Kunde, actJahr As Jahr, Ausgabe As String
'Durchläuft die Datentabelle und sammelt die Daten
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lastrow
Set actKunde = Kunden.Hinzu(Cells(i, "A"))
Set actJahr = actKunde.Jahre.Hinzu(Year(Cells(i, "B")))
actJahr.AnzVorgaenge = actJahr.AnzVorgaenge + 1
Next i
'Durchläuft die gesammelten Daten und gibt diese aus.
For k = 1 To Kunden.Count
Ausgabe = "Kunde: " & Kunden.Item(k).Name
For j = 1 To Kunden.Item(k).Jahre.Count
Ausgabe = Ausgabe & ", Jahr: " & Kunden.Item(k).Jahre.Item(j).Jahr & " - Vorgänge: " & Kunden.Item(k).Jahre.Item(j).AnzVorgaenge
Next j
MsgBox Ausgabe
Next k
End Sub
In diesem Code wird der String "Ausgabe" aus den Daten Kunde und Jahr zusammengesetzt. Diesen kannst du nun in deiner Email weiterverwenden oder, wie hier gezeigt, in einer Dialogbox ausgeben.
Die Item-Eigenschaft ist im Normalfall als Standard gesetzt und muss bei Excel-Eigenen Objekten nicht mit angegeben werden. So ist z.B. Worksheets("Tabelle1") das Gleiche wie Worksheets.Item("Tabelle1"). Sowas kannst du bei deinen selbst erstellen Objekten auch machen, ist aber ziemlich umständlich umzusetzen. Auch eine For Each-Schleife kannst du bei eigenen Aufzählungsobjekten nur mit einem Trick ausführen. Wie man beides einstellt, erkläre ich auf meiner Website, die sich allerdings noch im Aufbau befindet. Es gibt aber im Internet bereits viele Beispiele dafür.
Natürlich geht es auch ohne diese Komfortfunktionen. Man muss halt nur die .Item-Eigenschaft mit angeben und statt For Each eine For-To-Next-Schleife verwenden. So wie im Beispiel gezeigt.
PS: 10 ungelöste 8D-Reporte beim gleichen Kunden, die teilweise bereits seit 2018 offen sind? Das ist ne Menge. Das solltet ihr unbedingt näher untersuchen und die beanstandeten Probleme schnellstens lösen, wenn ihr diesen Kunden nicht verlieren wollt.
Gruß Mr. K.
|