Formule générique
=COUNTIF(INDIRECT("'"&sheetname&"'!"&"range"),criteria)
Sommaire
Pour rechercher une valeur dans plusieurs feuilles de calcul dans un classeur et renvoyer un décompte, vous pouvez utiliser une formule basée sur les fonctions COUNTIF et INDIRECT. Avec une configuration préliminaire, vous pouvez utiliser cette approche pour rechercher dans un classeur entier une valeur spécifique. Dans l'exemple illustré, la formule en C5 est:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),$C$4)
Contexte - exemples de données
Le classeur contient 4 feuilles de calcul au total. Sheet1 , Feuil2 et Feuil3 contiennent chacun 1000 prénoms aléatoires qui ressemblent à ceci:
Explication
La plage B7: B9 contient les noms de feuille que nous voulons inclure dans la recherche. Ce ne sont que des chaînes de texte, et nous devons faire un certain travail pour les faire reconnaître comme références de feuille valides.
En travaillant de l'intérieur vers l'extérieur, cette expression est utilisée pour créer une référence de feuille complète:
"'"&B7&"'!"&"1:1048576"
Les guillemets simples sont ajoutés pour autoriser les noms de feuille avec des espaces, et le point d'exclamation est une syntaxe standard pour les plages qui incluent un nom de feuille. Le texte «1: 1048576» est une plage qui comprend chaque ligne de la feuille de calcul.
Une fois B7 évalué et les valeurs concaténées, l'expression ci-dessus renvoie:
"'Sheet1'!1:1048576"
qui entre dans la fonction INDIRECT comme argument 'ref_text'. INDIRECT évalue ce texte et renvoie une référence standard à chaque cellule de Sheet1 . Cela entre dans la fonction COUNTIF comme plage. Le critère est fourni comme une référence absolue à C4 (verrouillé pour que la formule puisse être copiée dans la colonne C).
COUNTIF renvoie alors un décompte de toutes les cellules avec une valeur égale à "mary", 25 dans ce cas.
Remarque: COUNTIF n'est pas sensible à la casse.
Contient vs égal
Si vous souhaitez compter toutes les cellules contenant la valeur en C4, au lieu de toutes les cellules égales à C4, vous pouvez ajouter des caractères génériques aux critères comme ceci:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),"*"&C4&"*")
À présent, COUNTIF comptera les cellules avec la sous-chaîne "John" n'importe où dans la cellule.
Performance
En général, il n'est pas recommandé de spécifier une plage qui inclut toutes les cellules de la feuille de calcul. Cela peut entraîner des problèmes de performances, car la plage comprend des millions et des millions de cellules. Dans cet exemple, le problème est aggravé, car la formule utilise la fonction INDIRECT, qui est une fonction volatile. Les fonctions volatiles recalculent à chaque changement de feuille de calcul, de sorte que l'impact sur les performances peut être énorme.
Lorsque cela est possible, limitez les plages à une taille raisonnable. Par exemple, si vous savez que les données n'apparaîtront pas après la ligne 1000, vous pouvez rechercher uniquement les 1000 premières lignes comme ceci:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1000"),$C$4)
