Generieke formule
=N(CELL("width",A1)>0)
Samenvatting
Om zichtbare kolommen in een bereik te tellen, kunt u een hulpformule gebruiken op basis van de functie CELL met IF, en vervolgens de resultaten optellen met de functie SOM. In het getoonde voorbeeld is de formule in I4:
=SUM(key)
waarbij "sleutel" het benoemde bereik B4: F4 is, en alle cellen deze formule bevatten, gekopieerd naar:
=N(CELL("width",B4)>0)
Om de telwijziging te zien, moet u de berekening forceren met F9 of een andere wijziging in het werkblad uitvoeren waardoor de herberekening wordt geactiveerd. Hieronder ziet u hetzelfde werkblad met alle kolommen zichtbaar:
Opmerking: ik kwam het kernidee voor deze formule tegen op de uitstekende site wmfexcel.com.
Uitleg
Er is geen directe manier om een verborgen kolom met een formule in Excel te detecteren. Je zou kunnen denken aan het gebruik van de SUBTOTAAL-functie, maar SUBTOTAAL werkt alleen met verticale bereiken. Als gevolg hiervan is de benadering die in dit voorbeeld wordt beschreven een tijdelijke oplossing op basis van een hulpformule die moet worden ingevoerd in een bereik dat alle kolommen in het bereik van interesse omvat. In dit voorbeeld is dit bereik het benoemde bereik "sleutel".
In het getoonde voorbeeld zijn de kolommen C en E verborgen. De hulpformule, ingevoerd in B4 en gekopieerd over B4: F4, is gebaseerd op de CELL-functie:
=CELL("width",B4)>0
De functie CEL retourneert alleen een breedte voor een cel in een zichtbare kolom. Als een kolom verborgen is, retourneert dezelfde formule nul. Door te controleren of het resultaat groter is dan nul, krijgen we een WAAR of ONWAAR resultaat. De functie N wordt gebruikt om TRUE naar 1 en FALSE naar nul te dwingen, dus het eindresultaat is 1 wanneer een kolom zichtbaar is en 0 wanneer een kolom verborgen is. Leuk.
Om zichtbare kolommen te tellen, gebruiken we de SOM-functieformule in I4:
=SUM(key)
waarbij "key" het benoemde bereik is B4: F4.
Tel verborgen kolommen
Om verborgen kolommen te tellen, is de formule in I5:
=COLUMNS(key)-SUM(key)
De functie COLUMNS retourneert het totale aantal kolommen in het bereik (5) en de functie SOM retourneert de som van zichtbare kolommen (3), dus het uiteindelijke resultaat is 2:
=COLUMNS(key)-SUM(key) =5-3 =2
Met andere operaties
Zodra u de "kolomtoets" op zijn plaats heeft, kunt u deze gebruiken met andere bewerkingen. U kunt bijvoorbeeld SOM-waarden in zichtbare kolommen gebruiken door SUM als volgt te gebruiken:
=SUM(key*B6:F6)
Hoewel elke cel in B6: F6 het getal 25 bevat, retourneert SUM 75 als kolom C en E verborgen zijn, zoals in het voorbeeld wordt getoond.
Opmerking: CELL-functie is een vluchtige functie. Vluchtige functies worden normaal gesproken opnieuw berekend bij elke wijziging van het werkblad, zodat ze prestatieproblemen kunnen veroorzaken. Helaas vuurt CELL niet wanneer een kolom wordt verborgen of weer zichtbaar wordt gemaakt. Dit betekent dat u de juiste resultaten niet ziet totdat het werkblad opnieuw wordt berekend, hetzij met een normale wijziging, hetzij door op F9 te drukken.
