Compter les colonnes visibles et masquées avec Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Compter les colonnes visibles

A des fins statistiques sur de grosses bases de données, cette astuce Excel montre comment comptabiliser les colonnes masquées et par déclinaison, les colonnes visibles.



C'est ce que propose l'exemple illustré par la capture. Compter les lignes cachées est un jeu d'enfant grâce aux fonctions Excel Sous.Total et Agregat. Mais, nous allons le voir, concernant les colonnes, le problème est tout autre.

Classeur source
Pour réaliser ces travaux, nous proposons de récupérer un classeur hébergeant une source d'informations assez dense.

• Télécharger le classeur compter-cellules-visibles.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous réceptionnons une base de données assez conséquente. Elle archive des activités de sorties. Si vous consultez attentivement les étiquettes de colonne, vous constatez que deux d'entre-elles sont effectivement masquées.



Des sauts sont à observer dans l'énumération des lettres attribuées aux colonnes. Ce tableau composé en apparence de quatre colonnes en compte six en réalité. Avant d'entrer dans le vif du sujet, nous proposons de faire une petite remarque riche d'enseignement.

• Sélectionner la cellule I4 et taper le symbole égal (=) pour initier le calcul,
• Inscrire la fonction d'opérations sur les lignes filtrées avec une parenthèse, soit : =agregat(,

Une liste déroulante se déclenche aussitôt. Et à ce titre, vous constatez que les possibilités de calcul sont nombreuses.

• Choisir le décompte avec la fonction NbVal en inscrivant le chiffre 3,
• Taper un point-virgule (;) pour passer dans l'argument des options,

Comme vous pouvez le voir, le décompte entrepris peut se faire entre autres en ignorant les lignes masquées. Mais il n'en est rien concernant les colonnes.

• Enfoncer la touche Echap du clavier pour abandonner le calcul,

Fort de ces constatations, nous allons donc devoir développer une autre solution.

Repérer les colonnes masquées
L'idée consiste à exploiter la fonction Excel Cellule sur une plage arbitraire. Celle-ci renseigne sur une propriété à définir de chaque cellule parcourue. Parmi ces propriétés figure la largeur. Si la largeur est nulle, nous saurons que la colonne est masquée. C'est ainsi que nous pourrons les repérer pour ensuite les dénombrer.

• Sélectionner par exemple et arbitrairement la cellule J9,
• Taper le symbole égal (=) pour débuter la syntaxe de la formule,
• Inscrire la fonction renseignant sur les attributs des cases, suivie d'une parenthèse : Cellule(,
• Dans la liste qui se propose, double cliquer sur l'argument Largeur, soit : "largeur",
• Taper un point-virgule (;) pour passer dans l'argument des références de la cellule,

Sur une ligne arbitraire, nous devons parcourir le tableau à l'horizontale. C'est ainsi que nous pourrons observer les largeurs de chaque colonne. Pour recomposer les coordonnées des cellules à parcourir, nous devons employer la fonction Excel Adresse. Pour interpréter les coordonnées retournées par cette dernière, nous devons utiliser la fonction Indirect.

• Inscrire la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Inscrire la fonction pour les coordonnées suivie d'une parenthèse, soit : Adresse(,
• Désigner la ligne de titre du tableau par le chiffre 3,
• Taper un point-virgule (;) pour passer dans l'argument de l'indice de colonne,

Celui-ci doit varier en même temps que nous allons répliquer le calcul vers le bas. Et pour que cet indice progresse, nous pouvons exploiter la fonction ligne à partir de la cellule A2 par exemple. Elle retournera tout d'abord l'indice 2 pour la colonne B puis le 3 pour la C etc...

• Inscrire la fonction pour les indices suivie d'une parenthèse, soit : Ligne(,
• Désigner la cellule A2 puis fermer la parenthèse de la fonction Ligne,
• Fermer ensuite la parenthèse de la fonction Adresse,
• Fermer alors la parenthèse de la fonction Indirect,
• Fermer enfin la parenthèse de la fonction Cellule,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée,

Comme vous le savez, cette technique permet de conserver active la cellule du résultat pour l'exploiter dans la foulée. La première donnée tombe et indique que la première colonne de ce tableau est large de 34 pt.

• Tirer la poignée de ce résultat sur une hauteur suffisante, par exemple jusqu'en ligne 23,

Comme vous pouvez le voir, chaque colonne masquée est sanctionnée par une largeur nulle. A ce stade, il n'y en a que deux. Elles sont donc parfaitement identifiées et nous n'avons plus qu'à les compter. La syntaxe de la formule que nous avons bâtie est la suivante :

=CELLULE("largeur"; INDIRECT(ADRESSE(3; LIGNE(A2))))

Compter les colonnes masquées
Pour dénombrer les colonnes cachées, la fonction Nb.Si est dédiée sur la colonne du précédent calcul. Elle doit comptabiliser toutes les cellules rendant un résultat nul, synonyme de colonne masquée.

• En cellule I7, construire la formule suivante : =NB.SI(J:J;0),
• Puis, la valider avec la touche entrée du clavier,

Nous prévoyons large en cas d'évolution du tableau. Nous plaçons l'analyse sur l'intégralité de la colonne J. Nous aurions très bien pu la limiter à la plage J9:J23. Le résultat est parfaitement cohérent. Il indique que deux colonnes de ce tableau sont masquées.

Compter les colonnes visibles
Vous l'avez constaté, grâce au premier calcul pour identifier les largeurs des colonnes du tableau, le plus dur est fait. Le dernier résultat à fournir s'obtient d'une façon triviale. Pour connaître le nombre de colonnes visibles dans le tableau, il suffit de faire la différence entre le nombre total de colonnes et le nombre de colonnes masquées, dévoilé par le dernier calcul. C'est la fonction Excel Colonnes qui renseigne sur le nombre total de colonnes sur une plage désignée.

• En cellule I4, construire et valider le calcul suivant : =COLONNES(B3:G3)-NB.SI(J:J; 0),

Nous faisons agir la fonction Colonnes sur la ligne de titres du tableau. Il en résulte le nombre de colonnes qu'il contient. Nous lui soustrayons le résultat du calcul précédent, à savoir le nombre de colonnes masquées. Nous obtenons effectivement le nombre de colonnes visibles dans ce tableau.

Voyons maintenant comment réagissent ces données de calcul.

• Cliquer avec le bouton droit de la souris sur l'étiquette de colonne E,
• Dans le menu contextuel, choisir la commande Masquer,

Nous cachons ainsi la colonne des activités. Pourtant les résultats des calculs n'ont pas changé. Ils indiquent toujours le même nombre de colonnes masquées et de colonnes visibles. La raison est simple. La fonction Cellule est une fonction Volatile. Elle ne se recalcule pas automatiquement comme toutes les autres fonctions enregistrées dans la bibliothèque Excel. Nous devons lui donner un coup de pouce.

• Enfoncer la touche F9 du clavier pour forcer le recalcul de toutes les fonctions,

Cette fois, tous les résultats s'actualisent parfaitement et ils sont bien cohérents.



Ce sont bien trois colonnes qui ont été masquées tandis que trois autres demeurent visibles par voie de conséquence.