Comment utiliser la plage dynamique avec COUNTIF et INDIRECT dans Excel
ce qu’il faut savoir
- La fonction INDIRECT modifie la plage de références de cellules dans une formule sans modifier la formule.
- Utilisez INDIRECT comme argument de NB.SI pour créer une plage dynamique de cellules répondant aux critères spécifiés.
- La condition est établie par la fonction INDIRECT et seules les cellules qui remplissent la condition sont comptées.
Cet article explique comment utiliser la fonction INDIRECT dans une formule Excel pour modifier la plage de références de cellule utilisée dans la formule sans modifier la formule elle-même. Cela garantit que les mêmes cellules sont utilisées même si votre feuille de calcul change. Les informations s’appliquent à Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel pour Mac et Excel Online.
Utiliser la plage dynamique avec la formule COUNTIF – INDIRECT
La fonction INDIRECT peut être utilisée avec de nombreuses fonctions qui acceptent des références de cellule comme paramètres, telles que les fonctions SOMME et NB.SI.
L’utilisation de INDIRECT comme argument de NB.SI crée une plage dynamique de références de cellules que la fonction peut compter si la valeur de la cellule répond aux critères. Pour ce faire, il convertit les données textuelles (parfois appelées chaînes de texte) en références de cellule.
capture d’écran
Cet exemple est basé sur les données présentées dans la figure ci-dessus. La formule NB.SI – INDIRECT créée dans ce tutoriel est :
=COUNTIF(INDIRECT(E1& »: »&E2), »>10″) data-type= »code »>
Dans cette formule, les paramètres de la fonction INDIRECT contiennent :
- Les références de cellule E1 et E2 contiennent des données de texte D1 et D6.
- Opérateur de plage, deux-points (:) avec des guillemets doubles ( » « ) pour convertir les deux-points en une chaîne de texte.
- Deux esperluettes (&) sont utilisées pour joindre ou joindre le côlon avec les références de cellule E1 et E2.
Le résultat est qu’INDIRECT convertit la chaîne de texte D1:D6 en une référence de cellule, et si la cellule référencée est supérieure à 10, elle est transmise à la fonction NB.SI pour comptage.
La fonction INDIRECT accepte n’importe quelle saisie de texte. Il peut s’agir de cellules d’une feuille de calcul contenant du texte ou des références de cellule de texte saisies directement dans la fonction.
Modifier dynamiquement la plage d’une formule
N’oubliez pas que l’objectif est de créer des formules avec des plages dynamiques. La plage dynamique peut être modifiée sans modifier la formule elle-même.
La plage du total de la fonction peut facilement être modifiée de D1:D6 à D3:D7 en changeant les données de texte dans les cellules E1 et E2 de D1 et D6 à D3 et D7. Cela élimine le besoin de modifier directement la formule dans la cellule G1.
La fonction NB.SI dans cet exemple ne compte que les cellules contenant des nombres supérieurs à 10. Même si quatre des cinq cellules de la plage D1:D6 contiennent des données, seules trois cellules contiennent des nombres. La fonction ignore les cellules vides ou contenant des données textuelles.
Compter le texte avec COUNTIF
La fonction NB.SI ne se limite pas au comptage de données numériques. Il compte également les cellules contenant du texte en vérifiant si elles correspondent à un texte spécifique.
Pour cela, saisissez la formule suivante dans la cellule G2 :
=COUNTIF(INDIRECT(E1& »: »&E2), »deux ») data-type= »code »>
Dans cette formule, la fonction INDIRECT fait référence aux cellules B1 à B6. La fonction NB.SI totalise le nombre de cellules avec des valeurs de texte deux parmi eux.
Dans ce cas, le résultat est 1.
Comptage, Comptage Blanc et Indirect
Deux autres fonctions de comptage Excel sont COUNTA, qui compte les cellules contenant tout type de données tout en ignorant uniquement les cellules vides ou vides, et COUNTBLANK, qui ne compte que les cellules vides ou vides dans une plage.
Étant donné que la syntaxe de ces deux fonctions est similaire à la fonction NB.SI, elles peuvent être remplacées dans l’exemple ci-dessus par INDIRECT pour créer la formule suivante :
=COUNTA(indirect(E1& »: »&E2))
=COUNTVIDE(Indirect(E1& »: »&E2)
Pour la plage D1:D6, NBVAL renvoie une réponse de 4 car quatre des cinq cellules contiennent des données. COUNTBLANK renvoie une réponse de 1 car il n’y a qu’une seule cellule vide dans la plage.
Pourquoi utiliser la fonction INDIRECT ?
L’avantage d’utiliser la fonction INDIRECT dans toutes ces formules est que vous pouvez insérer de nouvelles cellules n’importe où dans la plage.
La portée change dynamiquement à l’intérieur des différentes fonctions et les résultats sont mis à jour en conséquence.
capture d’écran
Sans la fonction INDIRECT, vous auriez besoin de modifier chaque fonction pour inclure les 7 cellules, y compris la nouvelle cellule.
L’avantage de la fonction INDIRECT est que les valeurs de texte peuvent être insérées en tant que références de cellule et qu’elle met à jour dynamiquement la plage à mesure que la feuille de calcul change.
Cela facilite la maintenance de l’ensemble de la feuille de calcul, en particulier pour les très grandes feuilles de calcul.
Merci de nous en informer!
Recevez les dernières nouvelles technologiques publiées quotidiennement
abonnement
Dites-nous pourquoi !
D’autres détails ne sont pas assez difficiles à comprendre
