Obecný vzorec
=N(CELL("width",A1)>0)
souhrn
Chcete-li spočítat viditelné sloupce v rozsahu, můžete použít pomocný vzorec založený na funkci CELL s IF, poté výsledky sčítat pomocí funkce SUM. V zobrazeném příkladu je vzorec v I4:
=SUM(key)
kde „klíč“ je pojmenovaný rozsah B4: F4 a všechny buňky obsahují tento vzorec zkopírovaný napříč:
=N(CELL("width",B4)>0)
Chcete-li zobrazit změnu počtu, musíte vynutit výpočet pomocí klávesy F9 nebo provést jinou změnu listu, která spustí přepočet. Níže je stejný list se všemi viditelnými sloupci:
Poznámka: Základní myšlenku tohoto vzorce jsem narazil na vynikající stránku wmfexcel.com.
Vysvětlení
Neexistuje žádný přímý způsob detekce skrytého sloupce pomocí vzorce v aplikaci Excel. Možná vás napadne použít funkci SUBTOTAL, ale SUBTOTAL funguje pouze se svislými rozsahy. Výsledkem je, že přístup popsaný v tomto příkladu je řešením založeným na pomocném vzorci, který musí být zadán v rozsahu, který zahrnuje všechny sloupce v rozsahu zájmu. V tomto příkladu je tento rozsah pojmenovaným rozsahem „klíč“.
V zobrazeném příkladu jsou sloupce C a E skryté. Pomocný vzorec zadaný v B4 a kopírovaný přes B4: F4 je založen na funkci CELL:
=CELL("width",B4)>0
Funkce CELL vrátí pouze šířku buňky ve viditelném sloupci. Když je sloupec skrytý, stejný vzorec vrátí nulu. Zkontrolováním, zda je výsledek větší než nula, získáme výsledek PRAVDA nebo NEPRAVDA. Funkce N se používá k vynucení PRAVDA na 1 a NEPRAVDA na nulu, takže konečný výsledek je 1, když je sloupec viditelný, a 0, když je sloupec skrytý. Pěkný.
Chcete-li spočítat viditelné sloupce, použijeme vzorec funkce SUM v I4:
=SUM(key)
kde „klíč“ je pojmenovaný rozsah B4: F4.
Počítat skryté sloupce
Chcete-li spočítat skryté sloupce, je vzorec v I5:
=COLUMNS(key)-SUM(key)
Funkce COLUMNS vrací celkový počet sloupců v rozsahu (5) a funkce SUMA vrací součet viditelných sloupců (3), takže konečný výsledek je 2:
=COLUMNS(key)-SUM(key) =5-3 =2
S dalšími operacemi
Jakmile budete mít „klíč sloupce“ na místě, můžete jej použít s dalšími operacemi. Můžete například SUM hodnoty ve viditelných sloupcích použít pomocí SUM takto:
=SUM(key*B6:F6)
Ačkoli každá buňka v B6: F6 obsahuje číslo 25, SUM vrátí 75, když jsou sloupce C a E skryty, jak je znázorněno v příkladu.
Poznámka: Funkce CELL je volatilní funkce. Těkavé funkce se obvykle přepočítávají při každé změně listu, takže mohou způsobit problémy s výkonem. CELL se bohužel nespustí, když je sloupec skrytý nebo znovu viditelný. To znamená, že neuvidíte správné výsledky, dokud se list nepočítá, buď s normální změnou, nebo stisknutím klávesy F9.
