Všeobecný vzorec
=N(CELL("width",A1)>0)
Zhrnutie
Ak chcete počítať viditeľné stĺpce v rozsahu, môžete použiť pomocný vzorec založený na funkcii CELL s funkciou IF, potom výsledky spočítať pomocou funkcie SUM. V zobrazenom príklade je vzorec v I4:
=SUM(key)
kde „kľúč“ je pomenovaný rozsah B4: F4 a všetky bunky obsahujú tento vzorec skopírovaný naprieč:
=N(CELL("width",B4)>0)
Ak chcete vidieť zmenu počtu, musíte vynútiť výpočet pomocou klávesu F9 alebo vykonať inú zmenu pracovného hárka, ktorá spustí prepočet. Nižšie je uvedený rovnaký pracovný hárok so všetkými viditeľnými stĺpcami:
Poznámka: Na základnú myšlienku tohto vzorca som narazil na vynikajúcej stránke wmfexcel.com.
Vysvetlenie
Neexistuje žiadny priamy spôsob, ako zistiť skrytý stĺpec so vzorcom v programe Excel. Mohlo by vás napadnúť použiť funkciu SUBTOTAL, ale SUBTOTAL funguje iba s vertikálnymi rozsahmi. Výsledkom je, že prístup opísaný v tomto príklade predstavuje riešenie založené na pomocnom vzorci, ktoré je potrebné zadať v rozsahu, ktorý zahŕňa všetky stĺpce v rozsahu záujmu. V tomto príklade je tento rozsah pomenovaným rozsahom „kľúč“.
V zobrazenom príklade sú stĺpce C a E skryté. Pomocný vzorec zadaný v B4 a kopírovaný cez B4: F4 je založený na funkcii CELL:
=CELL("width",B4)>0
Funkcia CELL vráti iba šírku bunky vo viditeľnom stĺpci. Ak je stĺpec skrytý, rovnaký vzorec vráti nulu. Skontrolovaním, či je výsledok väčší ako nula, získame TRUE alebo FALSE výsledok. Funkcia N sa používa na vynútenie hodnoty TRUE na 1 a FALSE na nulu, takže konečný výsledok je 1, keď je stĺpec viditeľný, a 0, keď je stĺpec skrytý. Pekný.
Na spočítanie viditeľných stĺpcov používame v I4 funkčný vzorec SUM:
=SUM(key)
kde „kľúč“ je pomenovaný rozsah B4: F4.
Spočítajte skryté stĺpce
Na spočítanie skrytých stĺpcov je vzorec v I5:
=COLUMNS(key)-SUM(key)
Funkcia COLUMNS vráti celkový počet stĺpcov v rozsahu (5) a funkcia SUM vráti celkový počet viditeľných stĺpcov (3), takže konečný výsledok je 2:
=COLUMNS(key)-SUM(key) =5-3 =2
S inými operáciami
Keď máte zavedený „kľúč stĺpca“, môžete ho používať s inými operáciami. Môžete napríklad SUM hodnoty vo viditeľných stĺpcoch použiť pomocou SUM takto:
=SUM(key*B6:F6)
Aj keď každá bunka v B6: F6 obsahuje číslo 25, SUM vráti 75, keď sú stĺpce C a E skryté, ako je to znázornené v príklade.
Poznámka: Funkcia CELL je volatilná funkcia. Prchavé funkcie sa zvyčajne prepočítavajú pri každej zmene pracovného hárka, takže môžu spôsobiť problémy s výkonom. CELL bohužiaľ nespustí, keď je stĺpec skrytý alebo opäť viditeľný. To znamená, že neuvidíte správne výsledky, kým sa list neprepočíta, či už s normálnou zmenou, alebo stlačením F9.
