Eigene Pivot-Tabelle bauen mit VBA

Einige Leute lieben die Funktion, aber die meisten Leute mögen die Funktion Pivot-Tabelle in Excel überhaupt nicht. Ich selber habe dazu eine ganze Weile gebraucht, bis ich die Funktion einigermaßen nutzen konnte. Ich habe in regelmäßigen Abständen mir damit die merkwürdigsten Ergebnisse zusammengestellt. Deshalb zeige ich in diesem Beitrag, wie über eine kleine Funktion in VBA ein Teil der Funktion der Pivot-Tabelle aus Excel nachgebaut wird. Ich setze dem ganzen noch die Krone auf und baue es so, dass die VBA-Funktion als Formel eingesetzt werden kann.

Aber nun zur Ausgangssituation. Du hast eine Tabelle mit einer oder mehreren Spalten, sei es sortiert oder unsortiert, wie das folgende Bild zeigt :

So, Du möchtest jetzt nun wissen, wie oft die einzelnen Städte in der Spalte vorkommen. Dazu würdest Du jetzt im Normalfall auf die Excel Funktion Pivot-Tabelle zurückgreifen. Aber wir erweitern diese Tabelle jetzt um eine VBA Funktion, die genau das gleiche Ergebnis zurück liefert, wie die Funktion von Excel. Dazu öffne bitte als nächstes den VBA-Editor über den Reiter Entwicklungstools oder über die Tastenkombination ALT+F11. Nun lege ein neues Modul an und füge die folgende Funktion ein :

Public Function AnzahlEintraege(Spalte As Integer, Suchbegriff As String)
    Dim Anzahl As Integer
    Dim MaxZeile As Integer
    Dim Zeile As Integer
    
    Anzahl = 0
    MaxZeile = Worksheets("Tabelle1").Cells(Rows.Count, Spalte).End(xlUp).Row
    Debug.Print "Anzahl der Zeilen : " & MaxZeile
    For Zeile = 1 To MaxZeile
        If Worksheets("Tabelle1").Cells(Zeile, Spalte).Value = Suchbegriff Then
            Anzahl = Anzahl + 1
        End If
    Next Zeile
    AnzahlEintraege = Anzahl
End Function

Die beiden Befehle Public Function machen diese Funktion in einer Formeleingabezeile sichtbar. Dadurch kann die Funktion als Formel innerhalb den Zellen genutzt werden. An die Funktion wird die Spalte und der Suchbegriff übergeben. Die Spalte als Zahl und der Suchbegriff entweder als String oder durch eine Angabe einer Zelle. Ein Aufruf der Formel könnte zum Beispiel so aussehen :

Beispiel : =AnzahlEintraege(1;C3) oder =AnzahlEintraege(1;“Hamburg“)

Dieses Beispiel sucht in Spalte A nach dem Suchbegriff aus Spalte C3 und gibt dann die Anzahl aus, wie das folgende Bild zeigt :

In den Spalten D4 bis D7 steht ebenfalls die Formel drin und gibt damit die Anzahl der anderen Städte aus. Dieses kann auf alle anderen Spalten ebenfalls ausgeführt werden. Dazu ändere nur die übergebenen Parameter in der Formel AnzahlEintraege.