Generisk formel
=N(CELL("width",A1)>0)
Sammanfattning
För att räkna synliga kolumner i ett intervall kan du använda en hjälpformel baserad på CELL-funktionen med IF och sedan stämma med SUM-funktionen. I exemplet som visas är formeln i I4:
=SUM(key)
där "nyckel" är det namngivna området B4: F4, och alla celler innehåller denna formel, kopierad över:
=N(CELL("width",B4)>0)
För att se räkneändringen måste du tvinga beräkningen med F9 eller utföra en annan kalkylbladändring som utlöser omberäkning. Nedan finns samma kalkylblad med alla kolumner synliga:
Obs: Jag stötte på kärnidén för denna formel på den utmärkta webbplatsen wmfexcel.com.
Förklaring
Det finns inget direkt sätt att upptäcka en dold kolumn med en formel i Excel. Du kanske tänker använda SUBTOTAL-funktionen, men SUBTOTAL fungerar bara med vertikala intervall. Som ett resultat är tillvägagångssättet som beskrivs i detta exempel en lösning baserad på en hjälparformel som måste anges i ett intervall som inkluderar alla kolumner inom ramen för intresset. I detta exempel är detta intervall namngivna intervall "tangent".
I det visade exemplet är kolumnerna C och E dolda. Hjälparformeln, angiven i B4 och kopierad över B4: F4, är baserad på CELL-funktionen:
=CELL("width",B4)>0
CELL-funktionen returnerar bara en bredd för en cell i en synlig kolumn. När en kolumn är dold kommer samma formel att returnera noll. Genom att kontrollera om resultatet är större än noll får vi ett SANT eller FALSKT resultat. N-funktionen används för att tvinga SANT till 1 och FALSKT till noll, så det slutliga resultatet är 1 när en kolumn är synlig och 0 när en kolumn är dold. Trevlig.
För att räkna synliga kolumner använder vi SUM-funktionsformeln i I4:
=SUM(key)
där "nyckel" är det namngivna området B4: F4.
Räkna dolda kolumner
För att räkna dolda kolumner är formeln i I5:
=COLUMNS(key)-SUM(key)
Funktionen COLUMNS returnerar de totala kolumnerna i intervallet (5) och SUM-funktionen returnerar summan av synliga kolumner (3), så det slutliga resultatet är 2:
=COLUMNS(key)-SUM(key) =5-3 =2
Med andra operationer
När du har "kolumnnyckeln" på plats kan du använda den med andra åtgärder. Du kan till exempel SUM-värden i synliga kolumner genom att använda SUM så här:
=SUM(key*B6:F6)
Även om varje cell i B6: F6 innehåller siffran 25 kommer SUM att returnera 75 när kolumn C och E är dolda, som visas i exemplet.
Obs! CELL-funktionen är en flyktig funktion. Flyktiga funktioner beräknas normalt vid varje ändring av kalkylbladet så att de kan orsaka prestandaproblem. Tyvärr avfyras inte CELL när en kolumn är dold eller synlig igen. Det betyder att du inte ser korrekta resultat förrän kalkylbladet beräknar om, antingen med en normal förändring, eller genom att trycka på F9.
