Utilisez la fonction INDIRECT dans les formules Excel pour modifier la plage de références de cellules 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 est modifiée.
Remarque: Les instructions de cet article 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 en tant qu'argument, telles que les fonctions SUM et COUNTIF.
Utiliser INDIRECT comme argument pour COUNTIF crée une plage dynamique de références de cellules pouvant être comptées par la fonction si les valeurs de cellule répondent à un critère. Pour ce faire, il transforme les données de texte, parfois appelées chaînes de texte, en références 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 la suivante:
= COUNTIF (INDIRECT (E1 & ":" & E2), "> 10")
Dans cette formule, l'argument de la fonction INDIRECT contient:
- La cellule référence E1 et E2, qui contient les données de texte D1 et D6.
- L’opérateur de plage, le colon (:) entouré de guillemets doubles (' ') qui transforme les deux points en une chaîne de texte.
- Deux esperluettes (&) utilisées pour concaténer 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 la transmet à la fonction COUNTIF pour qu'elle soit comptée si les cellules référencées sont supérieures à 10.
La fonction INDIRECT accepte toutes les entrées de texte. Il peut s'agir de cellules dans la feuille de calcul contenant du texte ou des références de cellule de texte entrées directement dans la fonction.
Changer dynamiquement la plage de la formule
N'oubliez pas que l'objectif 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 de texte situées dans les cellules E1 et E2, de D1 et D6 à D3 et D7, la plage totalisée par la fonction peut facilement être modifiée de D1: D6 à D3: D7. Cela élimine le besoin de modifier directement la formule dans la cellule G1.
La fonction COUNTIF dans cet exemple ne compte les cellules contenant des nombres que si elles sont plus grandes que 10. Même si quatre des cinq cellules de la plage de D1: D6 contiennent des données, seules trois cellules contiennent des nombres. Les cellules vides ou contenant des données de texte sont ignorées par la fonction.
Compter le texte avec COUNTIF
La fonction COUNTIF n'est pas limitée au comptage de données numériques. Il compte également les cellules contenant du texte en vérifiant si elles correspondent à un texte donné.
Pour ce faire, la formule suivante est entrée dans la cellule G2:
= COUNTIF (INDIRECT (E1 & ":" & E2), "deux")
Dans cette formule, la fonction INDIRECT référence les cellules B1 à B6. La fonction COUNTIF totalise le nombre de cellules ayant la valeur de texte deux en eux.
Dans ce cas, le résultat est 1.
COUNTA, COUNTBLANK et INDIRECT
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 d'une plage, sont deux autres fonctions de comptage Excel
Étant donné que la syntaxe de ces deux fonctions est similaire à celle de la fonction COUNTIF, vous pouvez les remplacer par l'exemple ci-dessus avec INDIRECT pour créer les formules suivantes:
= COUNTA (INDIRECT (E1 & ":" & E2))= COUNTBLANK (INDIRECT (E1 & ":" & E2)
COUNTA renvoie une réponse de 4 pour la plage D1: D6, car quatre des cinq cellules contiennent des données. COUNTBLANK renvoie une réponse de 1 puisqu'il n'y a qu'une seule cellule vide dans la plage. 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 de manière dynamique à l'intérieur des diverses fonctions et les résultats sont mis à jour en conséquence. Sans la fonction INDIRECT, chaque fonction aurait besoin d'ê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 de manière dynamique les plages à chaque modification de votre feuille de calcul. Cela facilite grandement la maintenance générale des feuilles de calcul, en particulier pour les très grandes feuilles de calcul. Pourquoi utiliser une fonction indirecte?
