Les références de cellule indirectes dans Google Sheets ne sont pas mises en évidence (avec une bordure en pointillés) lorsque vous double-cliquez sur une formule. Pour mettre en évidence les plages indirectes, vous devez utiliser une formule personnalisée dans la mise en forme conditionnelle de Google Sheets.
Cela mettra en évidence la plage avec la couleur de votre choix. Choisissez une couleur claire et le tour est joué. Lorsque vous modifiez les références de cellules dans la fonction INDIRECT, la zone mise en évidence s’ajustera automatiquement.
Il s’agit essentiellement d’une solution de contournement pour visualiser la plage de formule en double-cliquant avec des références de cellules régulières. Dans ce cas, nous utilisons une règle de mise en évidence. Gardez à l’esprit que cela ne réagira pas au double-clic, mais cela sert à mettre en évidence la plage spécifiée.
Qu’est-ce qu’une plage indirecte dans Google Sheets ?
Une « plage indirecte » fait généralement référence à l’utilisation de la fonction INDIRECT dans Google Sheets de manière à référencer dynamiquement une plage de cellules.
Par exemple, pour additionner les valeurs de A1:A10, nous utilisons généralement =SUM(A1:A10). Dans le cas de l’utilisation INDIRECT, ce serait =SUM(INDIRECT("A1:A10")) ou, alternativement, vous pouvez saisir A1:A10 dans n’importe quelle autre cellule qui ne se trouve pas dans A1:A10, comme B1, et utiliser la formule =SUM(INDIRECT(B1)).
L’utilisation de plages INDIRECT est particulièrement utile pour créer des plages dynamiques.
Exemple de plage INDIRECT dynamique dans Google Sheets
Supposons que vous souhaitiez rechercher une valeur dans une colonne et additionner les valeurs jusqu’à cette plage dans une colonne adjacente. Si la valeur à rechercher est « C » et la plage pour rechercher cette valeur est B1:B, vous pouvez utiliser XMATCH de la manière suivante :
=XMATCH("C",B1:B)
Pour additionner les valeurs jusqu’à cette ligne dans la colonne C, concaténez le résultat XMATCH avec « C1:C » pour créer une chaîne de plage dynamique.
= "C1:C"&XMATCH("C",B1:B)
Il vous suffit d’utiliser cette sortie dans INDIRECT et de l’envelopper avec SUM pour calculer le total.
Cette configuration vous permet d’ajuster dynamiquement la plage en fonction de la position de la valeur « C » dans la colonne B, offrant ainsi un moyen flexible et automatisé d’additionner les valeurs dans Google Sheets.
Comment mettre en évidence une plage indirecte dans Google Sheets
Nous pouvons explorer différentes combinaisons de fonctions pour mettre en évidence une plage indirecte dans Google Sheets. J’ai personnellement testé plusieurs options et choisi celle qui offre de meilleures performances.
Voici la formule pour mettre en évidence une plage indirecte dans Google Sheets. Je vais expliquer les ajustements à apporter pour l’adapter à votre plage, comment l’appliquer dans la mise en forme conditionnelle, puis donner une explication de la formule.
=LET(range, $D$1, AND( ISBETWEEN( ROW(), ROW(INDIRECT(CHOOSECOLS(SPLIT(range,":"), 1))), ROW(INDIRECT(CHOOSECOLS(SPLIT(range,":"), 2))) ), ISBETWEEN( COLUMN(), COLUMN(INDIRECT(CHOOSECOLS(SPLIT(range,":"), 1))), COLUMN(INDIRECT(CHOOSECOLS(SPLIT(range,":"), 2))) ) ))
Modification de la formule pour mettre en évidence une plage indirecte dans Google Sheets
Quels changements dois-je apporter à cette formule pour mettre en évidence la plage indirecte dans ma feuille ?
Dans mon exemple, la cellule D1 contient la chaîne de plage. Remplacez donc $D$1 par la cellule qui contient la chaîne de plage.
Alternativement, vous pouvez insérer la chaîne de plage directement dans la formule. Dans ce cas, remplacez $D$1 par la chaîne de plage telle que « B5:C20 ».
Application de la règle de mise en forme conditionnelle dans Google Sheets
Comment appliquer cette règle dans la mise en forme conditionnelle ?
Pour mettre en évidence la plage indirecte, utilisez la règle de mise en évidence ci-dessus de la manière suivante. Le point clé est que plus la plage est petite, meilleures sont les performances.
La règle doit être appliquée de la première ligne jusqu’à la ligne et la colonne souhaitées. Par exemple, si votre feuille contient 1000 lignes et 26 colonnes, vous pouvez utiliser A1:Z1000 comme plage pour appliquer la formule.
Mais si vous vous attendez à ce que la plage se situe entre les colonnes B et G, alors la plage d’application peut être B1:G1000. Vous pouvez améliorer les performances en réduisant encore les numéros de ligne. Si votre plage indirecte se situe toujours entre B10:G100, spécifiez B1:G100 comme plage d’application.
Même si la plage commence à partir de la 50e ligne, la plage d’application doit commencer à partir de la première ligne. Voici comment l’appliquer :
- Sélectionnez la plage, par exemple, B1:E.
- Cliquez sur Format > Mise en forme conditionnelle.
- Copiez-collez la règle de mise en évidence ci-dessus dans la formule personnalisée.
- Choisissez une couleur de remplissage claire et cliquez sur Terminé.
Cette méthode vous permet de mettre en évidence une plage indirecte dans Google Sheets. La mise en évidence s’ajustera en fonction de la chaîne de plage dans la cellule D1 ou la cellule que vous avez utilisée dans votre formule.
Anatomie de la formule (règle de mise en évidence)
La formule utilisée pour mettre en évidence une plage indirecte est simple et se compose de deux parties clés.
Partie 1 :
=ISBETWEEN( ROW(), ROW(INDIRECT(CHOOSECOLS(SPLIT(range,":"), 1))), ROW(INDIRECT(CHOOSECOLS(SPLIT(range,":"), 2))) )
Syntaxe de ISBETWEEN :
=ISBETWEEN(valeur_à_comparer, valeur_inférieure, valeur_supérieure, [valeur_inférieure_inclusive], [valeur_supérieure_inclusive])
Partie 2 :
=ISBETWEEN( COLUMN(), COLUMN(INDIRECT(CHOOSECOLS(SPLIT(range,":"), 1))), COLUMN(INDIRECT(CHOOSECOLS(SPLIT(range,":"), 2))) )
Ces deux parties permettent de vérifier si le numéro de ligne actuel se situe entre les numéros de ligne de la plage indirecte.
Enfin, la fonction AND évalue si les deux parties 1 et 2 sont VRAIES et met en évidence en conséquence.
Le rôle de LET est de nommer la chaîne de plage en tant que « range », ce qui améliore la lisibilité et simplifie la modification de la formule.
Conseils et astuces pour mettre en évidence des plages indirectes
Nous pouvons utiliser la règle qui met en évidence les plages indirectes de plusieurs manières créatives dans Google Sheets. Explorons un exemple.
Nous pouvons l’utiliser pour mettre en évidence une plage en fonction de deux valeurs de recherche.
Dans l’exemple suivant, j’ai les noms de quelques boissons dans B2:B, les numéros de table dans C2:C et les quantités dans D2:D.
Je souhaite mettre en évidence les numéros de table de 2 à 4 dans la plage B2:D. Comment pouvons-nous y parvenir ?
Saisissez les clés de recherche (numéros de table) dans les cellules E3 et E4. Ensuite, utilisez la combinaison suivante de XLOOKUP et CELL dans la cellule F3 :
=CELL("address", XLOOKUP(E3, C:C, B:B))
Le XLOOKUP renvoie le nom de l’élément « Miami Vice » correspondant à la table n°2, et la fonction CELL renvoie l’adresse de la cellule.
Ensuite, saisissez la combinaison XLOOKUP et CELL suivante dans la cellule F4 :
=CELL("address", XLOOKUP(E4, C:C, D:D))
Le XLOOKUP renvoie la quantité (Qty. 1) correspondant à la table n°4, et la fonction CELL renvoie l’adresse de la cellule.
Combinez ces deux chaînes dans la cellule F1 à l’aide de la formule :
=F3&":"&F4
Dans la règle de mise en évidence, remplacez $D$1 par $F$1. La plage d’application peut être A1:Z1000, B1:D1000 ou beaucoup plus courte, par exemple B1:D50.
Voilà tout sur la façon de mettre en évidence une plage indirecte dans Google Sheets. Merci de rester avec nous. Amusez-vous bien !
