Utilisez la fonction INDIRECTE dans les formules Excel pour modifier la plage des références de cellules utilisées 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.
Les instructions de cet article s’appliquent à Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel pour Mac et Excel Online.
Contenu
Utiliser une gamme dynamique avec la formule COUNTIF – INDIRECT
La fonction INDIRECTE 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 COUNTIF.
L’utilisation d’INDIRECT comme argument pour COUNTIF crée une gamme dynamique de références de cellules qui peuvent être comptées par la fonction si les valeurs des cellules répondent à un critère. Pour ce faire, il transforme les données textuelles, parfois appelées chaîne de texte, en 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 tutoriel est :
=COUNTIF(INDIRECT(E1&":"&E2),
Dans cette formule, l’argument de la fonction INDIRECTE contient :
- La cellule fait référence à E1 et E2, qui contiennent les données textuelles D1 et D6.
- L’opérateur de distance, le côlon (:) entouré de doubles guillemets ( » « ) qui transforme les deux points en une chaîne de texte.
- Deux esperluettes (&) qui sont utilisées pour concaténer, ou joindre ensemble, le côlon avec les références cellulaires 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 être comptée si les cellules référencées sont supérieures à 10.
La fonction INDIRECTE accepte toute saisie de texte. Il peut s’agir de cellules de la feuille de calcul qui contiennent du texte ou de références de cellules de texte qui sont saisies directement dans la fonction.
Changer dynamiquement la gamme de la formule
N’oubliez pas que l’objectif est de créer une formule avec une gamme dynamique. Une gamme dynamique peut être modifiée sans avoir à modifier la formule elle-même.
En changeant les données textuelles situées dans les cellules E1 et E2, de D1 et D6 à D3 et D7, la plage totale de la fonction peut être facilement modifiée de D1:D6 à D3:D7. Il n’est donc pas nécessaire de modifier directement la formule dans la cellule G1.
La fonction COUNTIF dans cet exemple ne compte les cellules contenant des nombres que s’ils sont supérieurs à 10. Même si quatre des cinq cellules de la plage D1:D6 contiennent des données, seules trois cellules contiennent des chiffres. Les cellules qui sont vides ou qui contiennent des données textuelles sont ignorées par la fonction.
Compter les textes avec COUNTIF
La fonction COUNTIF ne se limite pas à compter des données numériques. Elle compte également les cellules qui contiennent 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&":"&E
Dans cette formule, la fonction INDIRECTE fait référence aux cellules B1 à B6. La fonction COUNTIF totalise le nombre de cellules qui ont la valeur de texte deux en eux.
Dans ce cas, le résultat est de 1.
PAYS, PAYS-BLANC et INDIRECT
Deux autres fonctions de comptage d’Excel sont COUNTA, qui compte les cellules contenant tout type de données tout en ignorant uniquement les cellules vides ou blanches, et COUNTBLANK, qui compte uniquement les cellules vides ou blanches dans une plage.
Comme ces deux fonctions ont une syntaxe similaire à la fonction COUNTIF, elles peuvent être substitué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 l’intervalle.
Pourquoi utiliser une fonction INDIRECTE ?
L’avantage de l’utilisation de la fonction INDIRECTE dans toutes ces formules est que de nouvelles cellules peuvent être insérées n’importe où dans la gamme.
La gamme se déplace dynamiquement à l’intérieur des différentes fonctions, et les résultats s’actualisent en conséquence.
Sans la fonction INDIRECTE, chaque fonction devrait être modifiée pour inclure les 7 cellules, y compris la nouvelle.
Les avantages de la fonction INDIRECTE sont que les valeurs de texte peuvent être insérées comme 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.
