Rob s’est demandé s’il existe un moyen de compter le nombre de cellules contenant des formules dans une ligne ou une colonne. La réponse est assez simple, en utilisant la fonction Aller à d’Excel. Suivez ces étapes:

  1. Affichez la feuille de calcul pour laquelle vous souhaitez un décompte.

  2. Sélectionnez la ligne ou la colonne dans laquelle vous souhaitez compter les formules.

  3. Appuyez sur F5 ou Ctrl + G. Excel affiche la boîte de dialogue Atteindre.

  4. Cliquez sur le bouton Spécial. Excel affiche la boîte de dialogue Aller à spécial.

(Voir la figure 1.)

  1. Assurez-vous que le bouton radio Formules est sélectionné.

  2. Cliquez sur OK.

C’est ça. Excel sélectionne toutes les cellules de la ligne ou de la colonne qui contiennent des formules. (Si vous ignorez l’étape 2, Excel sélectionne toutes les formules dans la feuille de calcul entière.) Au bas de l’écran, dans la barre d’état, vous pouvez voir un décompte du nombre de cellules sélectionnées. (Voir la figure 2.)

image

Figure 2. La barre d’état affiche un nombre de cellules sélectionnées.

Si, pour une raison quelconque, vous ne voyez pas de décompte dans la barre d’état, vous devez vérifier que votre barre d’état est configurée pour afficher les décomptes.

Cliquez avec le bouton droit de la souris sur un espace vide de la barre d’état et choisissez Compter parmi les options résultantes.

Si vous utilisez Excel 2013 ou une version plus récente, vous pouvez également utiliser une formule pour déterminer le nombre de formules dans une plage de cellules, comme indiqué ici:

=SUMPRODUCT(--ISFORMULA(A:A))

Cet exemple renvoie le nombre de toutes les formules de la colonne A; vous pouvez tout aussi facilement remplacer une plage de cellules différente dans la formule. Quelle que soit la plage que vous spécifiez, elle ne doit pas inclure la cellule dans laquelle vous placez cette formule particulière – cela entraînerait une référence circulaire et une erreur probable.

Vous pouvez également, si vous le souhaitez, utiliser une macro pour déterminer le nombre. L’exemple suivant utilise la même approche pour déterminer un décompte que celle décrite manuellement dans les étapes précédentes:

Sub CountFormulas1()

Dim Source As Range     Dim iCount As Integer

Set Source = ActiveSheet.Range("A:A")

iCount = Source.SpecialCells(xlCellTypeFormulas, 23).Count     ActiveSheet.Range("D1") = iCount End Sub

Ce sous-programme renvoie, très rapidement, un décompte de toutes les cellules contenant la formule de la colonne A et remplit cette valeur dans la cellule D1.

Il serait très utile que cette approche soit transformée en une fonction définie par l’utilisateur, telle que celle-ci:

Function CountFormulas2(Source As Range)

CountFormulas2 = Source.SpecialCells(xlCellTypeFormulas, 23).Count End Function

Cela ne fonctionnera pas, cependant. La fonction renvoie toujours le nombre de cellules de la plage Source, et non le nombre de cellules contenant des formules. C’est un bogue ésotérique dans le VBA d’Excel que SpecialCells ne fonctionne pas dans les fonctions; cela ne fonctionne que dans les sous-programmes. Microsoft n’a même pas documenté celui-ci (que je peux trouver), donc ma référence à lui comme un « bogue » au lieu de comme une « limitation ».

Cependant, il existe une limitation réelle à ce que la méthode SpecialCells peut faire: elle ne peut contenir qu’une plage de 8 192 cellules maximum. Vous pouvez analyser une plage beaucoup plus grande (comme c’est le cas lorsque vous regardez une colonne entière), mais le sous-ensemble résultant, la plage résultante, ne peut contenir que 8 192 cellules. S’il en contient plus, SpecialCells « échouera » et renverra une plage (et par conséquent un nombre) égal au nombre de cellules de la plage d’origine.

Si vous souhaitez créer une fonction définie par l’utilisateur pour déterminer le nombre, vous devrez vous fier à autre chose que la méthode SpecialCells.

Voici une approche qui utilise la propriété HasFormula:

Function CountFormulas3(Source As Range)

Dim c As Range     Dim iCount As Integer

iCount = 0     For Each c In Source         If c.HasFormula Then iCount = iCount + 1     Next     CountFormulas3 = iCount End Function

Si vous choisissez que cette macro évalue une colonne entière ou une ligne entière, alors soyez prêt à attendre un peu. Cela peut prendre un certain temps avant que la macro ne parcoure chaque cellule d’une colonne ou d’une ligne. La méthode SpecialCells est beaucoup plus rapide pour obtenir des résultats que pour parcourir chaque cellule.

_Note: _

Si vous souhaitez savoir comment utiliser les macros décrites sur cette page (ou sur toute autre page des sites ExcelTips), j’ai préparé une page spéciale qui comprend des informations utiles.

lien: / excelribbon-ExcelTipsMacros [Cliquez ici pour ouvrir cette page spéciale dans un nouvel onglet de navigateur].

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (13330) s’applique à Microsoft Excel 2007, 2010, 2013, 2016, 2019 et Excel dans Office 365.