MENU
  1. Accueil
  2. MS Office
  3. Excel
  4. Excel : utiliser INDIRECT() pour référencer dynamiquement des noms définis (INDEX, performances et alternatives)

Excel : utiliser INDIRECT() pour référencer dynamiquement des noms définis (INDEX, performances et alternatives)

Excel

Besoin d’appeler automatiquement, colonne après colonne, des noms définis du type QData1, QData2, QData3… dans Excel ? Voici une méthode fiable avec INDIRECT() + INDEX(), des variantes non volatiles, et un guide pas‑à‑pas prêt à l’emploi.

Sommaire

Utiliser INDIRECT() pour référencer dynamiquement un nom défini

Supposons que vous disposiez de plusieurs noms définis dans Excel — QData1, QData2, QData3… — chacun pointant vers une colonne précise (ex. Sheet1!$C$5:$C$39, Sheet1!$D$5:$D$39, etc.). L’objectif : n’écrire qu’une seule formule en première cellule, puis la copier vers la droite pour appeler automatiquement QData1 en première colonne, QData2 en deuxième, etc., sans modifier la formule.

La formule clé

Placez la formule suivante dans la première cellule de la zone de sortie (par exemple E5) :

=INDEX(INDIRECT("QData" & (COLONNE() - 2)), 1, 1)

Si votre Excel utilise le séparateur d’arguments « ; » (paramétrage français courant), utilisez la variante suivante :

=INDEX(INDIRECT("QData"&(COLONNE()-2));1;1)
  • COLONNE() renvoie le numéro de la colonne de la cellule contenant la formule.
  • - 2 aligne ce numéro sur votre série QData1, QData2, … (ex. si la formule est en colonne E, COLONNE() = 5 ⇒ 5‑2 = 3 ⇒ texte "QData3").
  • La concaténation forme le nom cible ("QData1", "QData2", …).
  • INDIRECT() transforme ce texte en véritable référence nommée.
  • INDEX(...;1;1) retourne la valeur en 1ʳᵉ ligne / 1ʳᵉ colonne de la plage référencée.

En copiant la formule vers la droite, la partie COLONNE()-2 augmente automatiquement ; le bon nom défini est donc appelé à chaque pas.

Visualiser l’alignement colonnes ⇄ noms

Cellule (ex.)Lettre de colonneCOLONNE()COLONNE()-2Nom ciblé
E5E53QData3
F5F64QData4
G5G75QData5
H5H86QData6

Astuce : adaptez le « -2 » pour que la première colonne appelle QData1 (ou tout autre point de départ). Si votre première colonne de sortie doit cibler QData1 alors que la formule se trouve en C5, utilisez COLONNE()-2 car COLONNE(C)=3 ⇒ 3-2=1 ⇒ QData1.

Faire aussi varier la ligne (copie vers le bas)

La formule ci‑dessus fixe la 1ʳᵉ ligne de chaque QData#. Pour la recopier vers le bas et récupérer la 2ᵉ, 3ᵉ, … ligne de chaque colonne, remplacez l’argument ligne de INDEX par un calcul relatif à votre première ligne de données. Par exemple, si vos données commencent en 5 (plages $C$5:$C$39 etc.) et que votre zone de sortie débute aussi en ligne 5, utilisez :

=INDEX(INDIRECT("QData"&(COLONNE()-2)); LIGNE()-4; 1)

Ici, LIGNE()-4 renvoie 1 en ligne 5, 2 en ligne 6, etc. À adapter si votre première ligne de données est différente.

Étapes pas à pas — du nom défini à la formule

  1. Créer (ou vérifier) les noms définis :
    • Onglet Formules > Gestionnaire de noms > Nouveau…
    • Nom : QData1. Fait référence à : =Sheet1!$C$5:$C$39.
    • Répétez pour QData2 (colonne D), QData3 (colonne E), etc.
    • Important : donnez à tous les QData# le même nombre de lignes.
  2. Placer la formule : dans la première cellule de sortie (ex. E5), entrez la formule « clé » vue plus haut.
  3. Recopier :
    • Vers la droite pour changer de QData#.
    • Vers le bas si vous utilisez la variante avec LIGNE() pour parcourir les lignes.
  4. Contrôler : sélectionnez la partie "QData"&(COLONNE()-2) dans la barre de formule et appuyez sur F9 : Excel affichera le texte réellement construit (ex. "QData4"). Appuyez sur Échap pour annuler l’évaluation.

Comprendre les limites : volatilité et performances

INDIRECT() est volatile : à chaque recalcul du classeur, Excel réévalue toutes les formules qui l’emploient, même si leurs antécédents n’ont pas changé. Sur de grands modèles, cela peut dégrader les performances. Trois pistes pour éviter ou limiter l’usage d’INDIRECT :

Option A — Indexer une grande plage fixe (non volatile)

Au lieu d’appeler QData#, pointez directement sur un bloc englobant toutes les colonnes et faites varier le numéro de colonne avec COLONNE() :

=INDEX(Sheet1!$C$5:$Z$39; LIGNE()-4; COLONNE()-2)
  • Non volatile : INDEX et COLONNE ne sont pas volatiles.
  • Maintenance facile si les colonnes cibles restent contiguës dans $C:$Z.
  • Robuste si vous insérez des lignes à l’intérieur du bloc ; ajustez seulement les correctifs -4 / -2 si vous changez l’emplacement du bloc.

Option B — CHOISIR (CHOOSE) pour mapper explicitement les plages

Si vos colonnes cibles ne sont pas contiguës (ou si vous tenez absolument à réutiliser vos noms), construisez un « sélecteur » non volatil avec CHOISIR :

=INDEX(CHOISIR(COLONNE()-2; QData1; QData2; QData3; QData4; QData5); LIGNE()-4; 1)
  • Non volatile : CHOISIR + INDEX ne sont pas volatiles.
  • Lisible : la correspondance « n° de colonne de sortie → nom » est visible en un coup d’œil.
  • À privilégier si vous avez peu de colonnes (car il faut lister les noms).

Option C — Indexer par en‑tête (sans noms définis)

Si la ligne d’en‑têtes contient les libellés QData1, QData2, etc., vous pouvez retrouver la colonne à partir du texte de l’en‑tête, sans INDIRECT ni noms définis :

=INDEX(Sheet1!$C$5:$Z$39; LIGNE()-4; EQUIV("QData"&(COLONNE()-2); Sheet1!$C$4:$Z$4; 0))
  • EQUIV(...; ...; 0) recherche une égalité exacte sur la ligne d’en‑têtes ($C$4:$Z$4).
  • Non volatile, et robuste aux insertions de colonnes : tant que l’en‑tête porte le bon libellé, la formule retrouve la bonne colonne.

Rendre la formule plus claire avec LET

Pour améliorer lisibilité et maintenance, encapsulez les calculs répétitifs dans LET() (O365/Excel 2021+) :

=LET(
  n; COLONNE()-2;
  ref; INDIRECT("QData"&n);
  r; LIGNE()-4;
  INDEX(ref; r; 1)
)

Avantages : un seul calcul de COLONNE()-2, des noms explicites (n pour l’index de série, ref pour la plage ciblée) et une maintenance simplifiée.

Cas particuliers et extensions utiles

Noms avec numérotation différente

  • Numérotation qui commence à 0 : utilisez COLONNE()-3 pour que la première colonne de sortie cible QData0.
  • Noms zéros‑paddés (QData01, QData02…) : =INDEX(INDIRECT("QData"&TEXTE(COLONNE()-2;"00")); LIGNE()-4; 1)
  • Préfixe ou suffixe variable : =INDEX(INDIRECT($B$2 & (COLONNE()-2) & $B$3); LIGNE()-4; 1) Placez le préfixe (ex. "QData") en B2 et le suffixe éventuel (ex. "_EUR") en B3.

Noms non contigus / série non régulière

Si la série n’est pas 1‑2‑3‑… mais « 1‑4‑7‑10… » ou si certains noms sont manquants, revenez à l’approche CHOISIR (Option B), qui vous laisse mapper explicitement : COLONNE()-2 → QDataX, QDataY, etc.

Noms qui pointent sur des plages 2D

Si QData# pointe vers une plage multi‑colonnes, précisez l’argument num_col dans INDEX (généralement 1 si vous voulez la 1ʳᵉ colonne), ou choisissez la colonne désirée via INDEX(...; r; c) où c peut lui‑même être calculé (c = 1, ou c = EQUIV(...) si la sous‑plage a des en‑têtes).

Débogage rapide

  • F9 sur la concaténation : sélectionnez "QData"&(COLONNE()-2) dans la barre de formule et pressez F9. Excel affiche par ex. "QData4". Échap ensuite pour revenir à la formule intacte.
  • #NOM? : le nom construit n’existe pas ou contient des espaces/accents non conformes.
  • #REF! : les plages diffèrent en hauteur, ou INDEX reçoit une ligne/colonne hors limites.
  • #VALEUR! : concaténation mal formée (guillemets oubliés) ou type non attendu.
  • Gestionnaire de noms : passez en revue chaque QData#, vérifiez la cohérence des plages et le scope (classeur vs feuille).
  • Évaluation pas à pas : onglet Formules > Évaluer la formule.

Exemple complet reproductible

Imaginons la feuille Sheet1 avec une table de données à partir de la ligne 5 :

 QData1QData2QData3QData4
ligne 5128149
ligne 61071311
ligne 71591210
ligne 81161612
ligne 91381514

Définissez ensuite :

  • QData1 → =Sheet1!$C$5:$C$9
  • QData2 → =Sheet1!$D$5:$D$9
  • QData3 → =Sheet1!$E$5:$E$9
  • QData4 → =Sheet1!$F$5:$F$9

Dans la feuille de sortie, placez en C5 :

=INDEX(INDIRECT("QData"&(COLONNE()-2)); LIGNE()-4; 1)

Puis recopiez vers la droite et vers le bas : vous obtiendrez exactement les données de QData1 → QData4 sans toucher à la formule. Pour une version non volatile sur ce jeu d’essai, remplacez par :

=INDEX(Sheet1!$C$5:$F$9; LIGNE()-4; COLONNE()-2)

Travailler avec des tableaux structurés (Tables)

Si vos colonnes résident dans un Tableau (ex. T_Q) avec en‑têtes QData1, QData2… :

  • Références structurées directes : =T_Q[QData3] renvoie toute la colonne (formule « qui déborde » en Excel 365).
  • Index par en‑tête (non volatile) : =INDEX(T_Q[#Données]; LIGNE()-LIGNE(T_Q[#En-têtes]); EQUIV("QData"&(COLONNE()-2); T_Q[#En-têtes]; 0)) Cette version s’adapte au redimensionnement du Tableau (ajouts de lignes/colonnes).

Checklist de validation

  • Tous les QData# couvrent le même nombre de lignes.
  • La formule de sortie est positionnée au bon endroit pour que les correctifs -4 (lignes) et -2 (colonnes) soient exacts.
  • La concaténation du nom renvoie un libellé existant (test F9).
  • Vous avez choisi la bonne stratégie de performance : INDIRECT si pratique et peu coûteux, sinon variantes non volatiles.

Questions fréquentes

Que se passe‑t‑il si j’insère une colonne au milieu ?

Avec INDIRECT() + noms définis, vous êtes protégé : le nom continue de viser la bonne plage. Avec la grande plage fixe ($C:$Z), vérifiez que l’offset COLONNE()-2 pointe toujours la bonne colonne dans le bloc. Avec la version EQUIV sur en‑têtes, vous êtes aussi protégé.

Puis‑je empêcher l’affichage implicite (débordement) en 365 ?

Si vous renvoyez une colonne entière depuis un Tableau, Excel 365 peut « déborder ». Utilisez INDEX avec un numéro de ligne précis (comme dans nos formules) pour produire une valeur scalaire stable à recopier.

OFFSET/DECALER ne ferait‑il pas la même chose ?

DECALER (OFFSET) est également volatile. Évitez‑le si vous ciblez la performance. Préférez INDEX avec des coordonnées calculées, ou EQUIV sur en‑têtes.

Comment documenter la logique pour un collègue ?

Encapsulez la formule dans LET, nommez clairement les variables (prefixe, n, r) et ajoutez un onglet « Documentation » listant les noms définis et leur cible. Une version CHOISIR explicite est souvent la plus lisible.

Modèles de formules prêts à coller

INDIRECT + INDEX (séparateur « ; »)

=INDEX(INDIRECT("QData"&(COLONNE()-2)); LIGNE()-4; 1)

Non volatile — grande plage

=INDEX(Sheet1!$C$5:$Z$39; LIGNE()-4; COLONNE()-2)

Non volatile — mapping explicite

=INDEX(CHOISIR(COLONNE()-2; QData1; QData2; QData3; QData4; QData5); LIGNE()-4; 1)

Non volatile — en‑têtes recherchés

=INDEX(Sheet1!$C$5:$Z$39; LIGNE()-4; EQUIV("QData"&(COLONNE()-2); Sheet1!$C$4:$Z$4; 0))

Comparatif rapide des approches

ApprocheVolatilitéLisibilitéMaintenanceQuand l’utiliser
INDIRECT + INDEXVolatileBonne (nom lisible construit)Très flexibleNoms déjà en place et besoin de sélection dynamique
Grande plage + INDEXNon volatileTrès bonneFacile si colonnes contiguësPerformance prioritaire, colonnes adjacentes
CHOISIR (mapping explicite)Non volatileExcellenteÀ mettre à jour si l’on ajoute des nomsPeu de colonnes, ordre arbitraire
En‑têtes + EQUIVNon volatileBonneRobuste aux insertionsLibellés fiables en ligne d’en‑têtes

Bonnes pratiques et pièges à éviter

  • Homogénéité : même hauteur pour tous les QData#.
  • Plages absolues : dans les noms QData#, employez des $ pour éviter les décalages au recopiage.
  • Conventions de nommage : pas d’espaces ni de caractères spéciaux dans les noms définis.
  • Documenter le décalage : notez quelque part pourquoi vous utilisez -2 et -4 (colonnes/lignes d’ancrage).
  • Évolutivité : si vous devez gérer 30+ colonnes, préférez l’option « En‑têtes + EQUIV » ou la grande plage non volatile.

Conclusion

Pour référencer dynamiquement des noms définis en fonction de la position de la formule, le duo INDIRECT() + INDEX() est simple et efficace : construire le nom à partir de COLONNE(), puis l’indiriger vers la plage. Sur des classeurs conséquents, privilégiez toutefois des variantes non volatiles (grande plage + INDEX, CHOISIR, ou en‑têtes + EQUIV) pour préserver les performances et la maintenabilité. Avec les modèles ci‑dessus, vous disposez d’un socle robuste pour bâtir des tableaux qui s’auto‑renseignent proprement, colonne après colonne.

Annexe — rappel express

  • Formule « INDIRECT » minimale : =INDEX(INDIRECT("QData"&(COLONNE()-2));1;1)
  • Variante qui se recopie vers le bas : =INDEX(INDIRECT("QData"&(COLONNE()-2)); LIGNE()-4; 1)
  • Alternative non volatile (colonnes contiguës) : =INDEX(Sheet1!$C$5:$Z$39; LIGNE()-4; COLONNE()-2)
Excel
Excel Microsoft 365 index INDIRECT noms définis formules dynamiques
Sommaire