Rob fragte sich, ob es eine Möglichkeit gibt, die Anzahl der Zellen mit Formeln in einer Zeile oder Spalte zu zählen. Die Antwort ist ganz einfach, wenn Sie die Funktion „Gehe zu“ von Excel verwenden. Befolgen Sie diese Schritte:

  1. Zeigen Sie das Arbeitsblatt an, für das Sie eine Zählung wünschen.

  2. Wählen Sie die Zeile oder Spalte aus, in der Sie Formeln zählen möchten.

  3. Drücken Sie F5 oder Strg + G. Excel zeigt das Dialogfeld Gehe zu an.

  4. Klicken Sie auf die Schaltfläche Spezial. Excel zeigt das Dialogfeld Gehe zu Spezial an.

(Siehe Abbildung 1.)

  1. Stellen Sie sicher, dass das Optionsfeld Formeln ausgewählt ist.

  2. OK klicken.

Das ist es. Excel wählt alle Zellen in der Zeile oder Spalte aus, die Formeln enthalten. (Wenn Sie Schritt 2 überspringen, wählt Excel alle Formeln im gesamten Arbeitsblatt aus.) Am unteren Bildschirmrand in der Statusleiste sehen Sie die Anzahl der ausgewählten Zellen. (Siehe Abbildung 2.)

image

Abbildung 2. Die Statusleiste zeigt eine Anzahl ausgewählter Zellen.

Wenn in der Statusleiste aus irgendeinem Grund keine Zählung angezeigt wird, sollten Sie überprüfen, ob Ihre Statusleiste so konfiguriert ist, dass Zählungen angezeigt werden.

Klicken Sie einfach mit der rechten Maustaste auf eine leere Stelle in der Statusleiste und wählen Sie Zählen aus den resultierenden Optionen.

Wenn Sie Excel 2013 oder eine neuere Version verwenden, können Sie auch eine Formel verwenden, um herauszufinden, wie viele Formeln sich in einem Zellbereich befinden, wie hier gezeigt:

=SUMPRODUCT(--ISFORMULA(A:A))

In diesem Beispiel wird die Anzahl aller Formeln in Spalte A zurückgegeben. Sie können genauso gut einen anderen Zellbereich in der Formel ersetzen. Unabhängig davon, welchen Bereich Sie angeben, sollte er nicht die Zelle enthalten, in der Sie diese bestimmte Formel platzieren. Dies würde zu einem Zirkelverweis und einem wahrscheinlichen Fehler führen.

Falls gewünscht, können Sie auch ein Makro verwenden, um die Anzahl zu bestimmen. Das folgende Beispiel verwendet denselben Ansatz zum Bestimmen einer Anzahl, wie er in den vorherigen Schritten manuell beschrieben wurde:

Sub CountFormulas1()

Dim Source As Range     Dim iCount As Integer

Set Source = ActiveSheet.Range("A:A")

iCount = Source.SpecialCells(xlCellTypeFormulas, 23).Count     ActiveSheet.Range("D1") = iCount End Sub

Diese Unterroutine gibt sehr schnell eine Zählung aller formelhaltigen Zellen in Spalte A zurück und stopft diesen Wert in Zelle D1.

Es wäre sehr hilfreich, wenn dieser Ansatz in eine benutzerdefinierte Funktion wie die folgende umgewandelt werden könnte:

Function CountFormulas2(Source As Range)

CountFormulas2 = Source.SpecialCells(xlCellTypeFormulas, 23).Count End Function

Dies wird jedoch nicht funktionieren. Die Funktion gibt immer die Anzahl der Zellen im Quellbereich zurück, nicht die Anzahl der Zellen, die Formeln enthalten. Es ist ein esoterischer Fehler in Excel’s VBA, dass SpecialCells in Funktionen nicht funktioniert. es funktioniert nur in Unterprogrammen. Microsoft hat diesen nicht einmal dokumentiert (den ich finden kann), daher beziehe ich mich darauf als „Fehler“ anstatt als „Einschränkung“.

Es gibt jedoch eine tatsächliche Einschränkung der Funktionsweise der SpecialCells-Methode: Sie kann nur einen Bereich von bis zu 8.192 Zellen enthalten. Sie können einen Bereich analysieren, der viel größer ist (wie es der Fall ist, wenn Sie eine ganze Spalte betrachten), aber die resultierende Teilmenge – der resultierende Bereich – kann nur bis zu 8.192 Zellen enthalten. Wenn es mehr enthält, „schlägt“ SpecialCells fehl und gibt einen Bereich (und damit eine Anzahl) zurück, der der Anzahl der Zellen im ursprünglichen Bereich entspricht.

Wenn Sie eine benutzerdefinierte Funktion erstellen möchten, um die Anzahl zu bestimmen, müssen Sie sich auf etwas anderes als die SpecialCells-Methode verlassen.

Hier ist ein Ansatz, der die HasFormula-Eigenschaft verwendet:

Function CountFormulas3(Source As Range)

Dim c As Range     Dim iCount As Integer

iCount = 0     For Each c In Source         If c.HasFormula Then iCount = iCount + 1     Next     CountFormulas3 = iCount End Function

Wenn Sie festlegen, dass dieses Makro eine ganze Spalte oder eine ganze Zeile auswertet, müssen Sie ein wenig warten. Es kann eine Weile dauern, bis das Makro jede Zelle in einer Spalte oder Zeile durchläuft. Die SpecialCells-Methode leitet Ergebnisse viel schneller ab als das Durchlaufen jeder Zelle.

_Hinweis: _

Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.

Klicken Sie hier, um diese spezielle Seite in einem neuen Browser-Tab zu öffnen.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (13330) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365.