Comment utiliser une plage dynamique dans Excel avec COUNTIF et INDIRECT

2024 Auteur: Abigail Brown | [email protected]. Dernière modifié: 2023-12-17 06:47

Ce qu'il faut savoir

• La fonction INDIRECT modifie la plage de références de cellule dans une formule sans modifier la formule.
• Utilisez INDIRECT comme argument pour COUNTIF pour créer une plage dynamique de cellules répondant aux critères spécifiés.
• Les critères sont établis par la fonction INDIRECT, et seules les cellules qui répondent aux critères sont comptées.

Cet article explique comment utiliser la fonction INDIRECT dans les formules Excel pour modifier la plage de références de cellule utilisée dans une formule sans avoir à modifier la formule elle-même. Cela garantit que les mêmes cellules sont utilisées, même lorsque votre feuille de calcul change. Les informations s'appliquent à Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel pour Mac et Excel Online.

Utiliser une plage dynamique avec la formule COUNTIF - INDIRECT

La fonction INDIRECT peut être utilisée avec un certain nombre de fonctions qui acceptent une référence de cellule comme argument, telles que les fonctions SOMME et NB. SI.

L'utilisation de INDIRECT comme argument pour COUNTIF crée une plage dynamique de références de cellule qui peut être comptée par la fonction si les valeurs de cellule répondent à un critère. Pour ce faire, il transforme les données textuelles, parfois appelées chaîne de texte, en une référence de cellule.

Cet exemple est basé sur les données présentées dans l'image ci-dessus. La formule COUNTIF - INDIRECT créée dans le didacticiel est:

=COUNTIF(INDIRECT(E1&":"&E2), ">10")

Dans cette formule, l'argument de la fonction INDIRECT contient:

• La cellule fait référence à E1 et E2, qui contiennent les données textuelles D1 et D6.
• L'opérateur de plage, les deux-points (:) entourés de guillemets doubles (" ") qui transforme les deux-points en texte chaîne.
• Deux esperluettes (&) utilisées pour concaténer ou joindre les deux-points 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 la transmet à la fonction NB. SI pour qu'elle soit comptée si les cellules référencées sont supérieures à 10.

La fonction INDIRECT accepte toutes les saisies de texte. Il peut s'agir de cellules de la feuille de calcul contenant du texte ou des références de cellule de texte saisies directement dans la fonction.

Modifier dynamiquement la plage de la formule

N'oubliez pas que le but est de créer une formule avec une plage dynamique. Une plage dynamique peut être modifiée sans modifier la formule elle-même.

En modifiant les données textuelles situées dans les cellules E1 et E2, de D1 et D6 à D3 et D7, la plage totalisée par la fonction peut être facilement modifiée de D1:D6 à D3: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 si elles sont supérieures à 10. Même si quatre des cinq cellules de la plage D1:D6 contiennent des données, seules trois cellules contiennent des nombres. Les cellules vides ou contenant des données textuelles sont ignorées par la fonction.

Comptage de 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 certain texte.

Pour ce faire, la formule suivante est saisie dans la cellule G2:

=COUNTIF(INDIRECT(E1&":"&E2), "deux")

Dans cette formule, la fonction INDIRECT référence les cellules B1 à B6. La fonction NB. SI totalise le nombre de cellules contenant la valeur de texte deux.

Dans ce cas, le résultat est 1.

COUNTA, COUNTBLANK et INDIRECT

Deux autres fonctions de comptage Excel sont COUNTA, qui compte les cellules contenant tout type de données 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 ces deux fonctions ont une syntaxe similaire à la fonction NB. SI, elles peuvent être remplacées dans l'exemple ci-dessus par INDIRECT pour créer les formules suivantes:

=COUNTA(INDIRECT(E1&":"&E2))
=COUNTBLANK(INDIRECT(E1&":"&E2)
Pour la plage D1:D6, COUNTA renvoie une réponse de 4, puisque 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 une fonction INDIRECT ?
L'avantage d'utiliser la fonction INDIRECT dans toutes ces formules est que de nouvelles cellules peuvent être insérées n'importe où dans la plage.
La plage se déplace dynamiquement à l'intérieur des différentes fonctions et les résultats sont mis à jour en conséquence.





Sans la fonction INDIRECT, chaque fonction devrait être modifiée pour inclure les 7 cellules, y compris la nouvelle.
Les avantages de la fonction INDIRECT sont que les valeurs de texte peuvent être insérées en tant que références de cellule et qu'elle met à jour dynamiquement les plages chaque fois que votre feuille de calcul change.
Cela facilite grandement la maintenance globale des feuilles de calcul, en particulier pour les très grandes feuilles de calcul.