Generische Formel
=N(CELL("width",A1)>0)
Zusammenfassung
Um sichtbare Spalten in einem Bereich zu zählen, können Sie eine Hilfsformel verwenden, die auf der CELL-Funktion mit IF basiert. Anschließend werden die Ergebnisse mit der SUMME-Funktion gezählt. In dem gezeigten Beispiel lautet die Formel in I4:
=SUM(key)
Dabei ist "Schlüssel" der benannte Bereich B4: F4, und alle Zellen enthalten diese Formel, kopiert über:
=N(CELL("width",B4)>0)
Um die Zähländerung zu sehen, müssen Sie die Berechnung mit F9 erzwingen oder eine andere Arbeitsblattänderung durchführen, die eine Neuberechnung auslöst. Unten ist das gleiche Arbeitsblatt mit allen sichtbaren Spalten:
Hinweis: Ich bin auf der ausgezeichneten Website wmfexcel.com auf die Kernidee für diese Formel gestoßen.
Erläuterung
Es gibt keine direkte Möglichkeit, eine versteckte Spalte mit einer Formel in Excel zu erkennen. Sie könnten die SUBTOTAL-Funktion verwenden, aber SUBTOTAL funktioniert nur mit vertikalen Bereichen. Daher ist der in diesem Beispiel beschriebene Ansatz eine Problemumgehung, die auf einer Hilfsformel basiert, die in einem Bereich eingegeben werden muss, der alle Spalten im interessierenden Bereich enthält. In diesem Beispiel ist dieser Bereich der benannte Bereich "Schlüssel".
Im gezeigten Beispiel sind die Spalten C und E ausgeblendet. Die in B4 eingegebene und über B4: F4 kopierte Hilfsformel basiert auf der CELL-Funktion:
=CELL("width",B4)>0
Die CELL-Funktion gibt nur eine Breite für eine Zelle in einer sichtbaren Spalte zurück. Wenn eine Spalte ausgeblendet ist, gibt dieselbe Formel Null zurück. Wenn wir prüfen, ob das Ergebnis größer als Null ist, erhalten wir ein TRUE- oder FALSE-Ergebnis. Die N-Funktion wird verwendet, um TRUE auf 1 und FALSE auf Null zu zwingen. Das Endergebnis ist also 1, wenn eine Spalte sichtbar ist, und 0, wenn eine Spalte ausgeblendet ist. Nett.
Um sichtbare Spalten zu zählen, verwenden wir die SUM-Funktionsformel in I4:
=SUM(key)
Dabei ist "Schlüssel" der benannte Bereich B4: F4.
Zähle versteckte Spalten
Um versteckte Spalten zu zählen, lautet die Formel in I5:
=COLUMNS(key)-SUM(key)
Die Funktion COLUMNS gibt die Gesamtspalten im Bereich (5) und die Funktion SUM die Summe der sichtbaren Spalten (3) zurück. Das Endergebnis ist also 2:
=COLUMNS(key)-SUM(key) =5-3 =2
Mit anderen Operationen
Sobald Sie den "Spaltenschlüssel" eingerichtet haben, können Sie ihn für andere Vorgänge verwenden. Sie können beispielsweise SUM-Werte in sichtbaren Spalten mithilfe von SUM wie folgt verwenden:
=SUM(key*B6:F6)
Obwohl jede Zelle in B6: F6 die Nummer 25 enthält, gibt SUM 75 zurück, wenn Spalte C und E ausgeblendet sind, wie im Beispiel gezeigt.
Hinweis: Die CELL-Funktion ist eine flüchtige Funktion. Flüchtige Funktionen werden normalerweise bei jeder Änderung des Arbeitsblatts neu berechnet, sodass sie Leistungsprobleme verursachen können. Leider wird CELL nicht ausgelöst, wenn eine Spalte ausgeblendet oder wieder sichtbar gemacht wird. Dies bedeutet, dass Sie erst dann korrekte Ergebnisse sehen, wenn das Arbeitsblatt entweder mit einer normalen Änderung oder durch Drücken von F9 neu berechnet wird.
