MENU
  1. Accueil
  2. MS Office
  3. Excel
  4. Excel : afficher automatiquement la semaine choisie avec une liste déroulante et XLOOKUP (planning 8 semaines)

Excel : afficher automatiquement la semaine choisie avec une liste déroulante et XLOOKUP (planning 8 semaines)

Excel

Besoin d’un planning Excel récurrent sur 8 semaines, commandé par une liste déroulante, qui actualise automatiquement les données affichées ? Voici un guide pas‑à‑pas (avec formules prêtes à copier) pour une solution robuste, compatible 365/2021 et alternatives pour versions plus anciennes.

Sommaire

Objectif de la discussion

L’objectif est de permettre à un classeur Excel d’afficher, toujours au même endroit, les valeurs correspondant à la semaine choisie via une liste déroulante Week 1 … Week 8. Le contexte typique est un planning cyclique de 8 semaines où l’on veut, pour chaque « identifiant » (ex. site, équipe, machine), voir les « Rewards » (ou indicateurs) de la semaine sélectionnée sans devoir coller/couper des colonnes ou refaire des filtres.

Organisation conseillée des données

Feuille Data (table de référence)Feuille principale (interface)
Colonne A : Identifiant (ex. nom du site)Cellule M2 : liste déroulante Week 1 … Week 8
Colonnes B : Q : en‑têtes concaténés « Week n Reward x »Ligne 5 : intitulés Reward 1, Reward 2, …
Lignes 2 à 11 et + : valeurs réellesPlages O6:Q? et S6:U? : résultats dynamiques

Pourquoi ce design ? En nommant chaque colonne « Week N Reward X », la recherche devient triviale : on assemble la semaine choisie et le libellé du Reward pour identifier la colonne exacte, quelle que soit sa position.

Exemple minimal de la feuille Data

A — IdentifiantB — Week 1 Reward 1C — Week 1 Reward 2D — Week 2 Reward 1E — Week 2 Reward 2…P — Week 8 Reward 1Q — Week 8 Reward 2
Site A128107…159
Site B11998…1410
Site C136126…168

Sur la feuille principale, placez en N la liste verticale des identifiants (ex. N6:N10), en M2 la liste déroulante des semaines, en O5:Q5 les libellés « Reward 1 » / « Reward 2 » / etc., puis réservez O6:Q? pour les résultats.

Mise en place de la liste déroulante et des noms utiles

  1. Créez la liste des semaines sur une petite zone (ex. AA2:AA9) avec Week 1 à Week 8, puis définissez la plage nommée WeekList pointant sur =FeuilX!$AA$2:$AA$9.
  2. Validez les données sur M2 via Données > Validation des données > Liste et source =WeekList.
  3. Convertissez la table source (feuille Data) en Tableau (Ctrl+T) pour profiter d’une expansion automatique.
  4. Soignez les en‑têtes : chaque colonne B:Q doit se nommer exactement Week N Reward X (majuscules/espaces identiques à la feuille principale).

Méthodes proposées

Recherche imbriquée 100 % XLOOKUP (Excel 365/2021+)

La formule suivante renvoie l’intersection identifiant × colonne en combinant deux recherches : d’abord la colonne cible (via l’en‑tête « Week + Reward »), puis la valeur de la ligne correspondante à l’identifiant.

Formule à coller en O6 puis à recopier vers la droite et vers le bas :

=XLOOKUP($N6,
    Data!$A$2:$A$11,
    XLOOKUP($M$2&" "&O$5, Data!$B$1:$Q$1, Data!$B$2:$Q$11),
    0)

Équivalent francisé (Excel FR : séparateur « ; », fonction RECHERCHEX) :

=RECHERCHEX($N6;
    Data!$A$2:$A$11;
    RECHERCHEX($M$2&" "&O$5; Data!$B$1:$Q$1; Data!$B$2:$Q$11);
    0)

Pourquoi ça marche ?

  • $M$2&" "&O$5 reconstruit exactement l’en‑tête ciblé (ex. Week 3 Reward 1).
  • Le premier XLOOKUP retourne la colonne entière correspondant à cet en‑tête.
  • Le second XLOOKUP cherche l’identifiant dans Data!$A$2:$A$11 et renvoie la valeur à l’intersection.

Version lisible avec LET (améliore la maintenance) :

=LET(
  id; $N6;
  entete; $M$2&" "&O$5;
  corps; Data!$B$2:$Q$11;
  RECHERCHEX(id; Data!$A$2:$A$11; RECHERCHEX(entete; Data!$B$1:$Q$1; corps); "")
)

Avantages : une seule formule recopiable partout, résistante aux insertions/suppressions de colonnes, compatible avec les plages étendues (agrandissez simplement $A$11 et $Q$11).

Astuce pro : si votre table Data est un Tableau nommé DataTbl, utilisez des références structurées :

=RECHERCHEX($N6; DataTbl[Identifiant];
  RECHERCHEX($M$2&" "&O$5; DataTbl[#En-têtes]; DataTbl);
"")

INDEX + INDIRECT (toutes versions d’Excel)

Quand XLOOKUP/RECHERCHEX n’est pas disponible (Excel 2016 et antérieurs), on peut viser une zone pré‑nommée par semaine et y indexer la cellule voulue.

  1. Créez 8 plages nommées : Week1, Week2, … Week8, chacune couvrant uniquement les colonnes de la semaine dans Data. Exemple : si chaque semaine comporte 2 Rewards, Week1 = Data!$B$2:$C$11, Week2 = Data!$D$2:$E$11, etc.
  2. Associez la liste déroulante : M2 retourne « Week 1 », « Week 2 », … ; on va en déduire la plage nommée homonyme.
  3. Collez la formule en O6 et recopiez :
=INDEX(INDIRECT(SUBSTITUTE($M$2," ","")); ROW()-ROW($O$6)+1; COLUMN()-COLUMN($O$6)+1)

Ici SUBSTITUTE($M$2," ","") transforme Week 1 en Week1 (nom de plage). ROW()-ROW($O$6)+1 et COLUMN()-COLUMN($O$6)+1 assurent l’offset relatif dans le bloc.

Avantages : fonctionne partout. Limites : INDIRECT est volatile (recalcule souvent), fige les références et casse lors de certains renommages. À privilégier si vous ne pouvez pas moderniser Excel.

XLOOKUP « déversement » pour afficher une colonne complète

Si vous souhaitez afficher une colonne entière correspondant à la semaine (ex. uniquement « Reward 1 »), exploitez le déversement vertical (Excel 365) :

=XLOOKUP($F$1, Data!$A$1:$Q$1, Data!$A$2:$Q$999)

Dans $F$1, placez le nom exact de l’en‑tête (ex. Week 3 Reward 1). La plage de sortie se remplit automatiquement vers le bas.

Alternative sans imbriquer XLOOKUP : double correspondance INDEX + XMATCH

Approche performante et claire :

=INDEX(Data!$B$2:$Q$11;
    XMATCH($N6; Data!$A$2:$A$11; 0);
    XMATCH($M$2&" "&O$5; Data!$B$1:$Q$1; 0))

Affichage de plusieurs Rewards d’un coup

Pour afficher un bloc de 2 ou 3 Rewards côte à côte (ex. Reward 1 à Reward 3) selon la semaine, utilisez CHOOSECOLS (Excel 365) pour sélectionner plusieurs colonnes d’un seul coup. Exemple avec des en‑têtes strictement structurés :

=LET(
  base; Data!$B$2:$Q$11;
  ent1; $M$2&" Reward 1";
  ent2; $M$2&" Reward 2";
  ent3; $M$2&" Reward 3";
  idx1; XMATCH(ent1; Data!$B$1:$Q$1; 0);
  idx2; XMATCH(ent2; Data!$B$1:$Q$1; 0);
  idx3; XMATCH(ent3; Data!$B$1:$Q$1; 0);
  RECHERCHEX($N6; Data!$A$2:$A$11; CHOOSECOLS(base; idx1; idx2; idx3); "")
)

Recopiez vers le bas pour tous les identifiants : les 3 colonnes se mettent à jour ensemble quand M2 change.

Conseils pratiques & pièges courants

Point de vigilanceExplication / Remède
Correspondance exacte des identifiantsLes libellés de Data!A doivent être identiques à ceux de la feuille principale. Utilisez SUPPRESPACE ou NETTOYER au besoin pour éliminer les espaces parasites.
Plages dynamiquesPréférez les Tableaux (Ctrl+T) et références structurées. Vos formules s’étendent automatiquement quand de nouvelles lignes sont ajoutées.
Gestion des erreursUtilisez l’argument « si non trouvé » de XLOOKUP ("", 0 ou NA()) ou encapsulez avec IFERROR/ SI.NON.DISP pour un affichage propre.
Alignement visuelN’insérez pas de cellules vides pour aérer : cela décale les plages et casse les recherches. Utilisez le formatage (hauteur de lignes, largeurs, styles).
Versions d’ExcelXLOOKUP/RECHERCHEX est absent d’Excel 2019−. Sur 2016 ou antérieur, combinez INDEX+EQUIV ou la méthode INDEX+INDIRECT.
Validation des donnéesPour la liste déroulante de M2, créez une plage nommée WeekList et référez‑y la validation (=WeekList). Évitez la saisie libre.
Espaces & casseLa clé « Semaine + Reward » doit exactement correspondre (mêmes espaces). Faites‑vous aider de listes déroulantes aussi pour les en‑têtes si nécessaire.
Données numériques stockées en texteConvertissez via Données > Convertir ou --valeur dans une colonne auxiliaire si des sommes ne se calculent pas.
PerformanceÉvitez INDIRECT à grande échelle (fonction volatile). Préférez XLOOKUP et INDEX/XMATCH + LET pour limiter les recalculs.

Dépannage express

  • #N/A sur toute une ligne : l’identifiant de N6:N? n’existe pas dans Data!A:A ou contient un espace en fin. Vérifiez avec =EXACT(N6;Data!A2) pour tester un cas.
  • #N/A sur toute une colonne : l’en‑tête calculé (M2&" "&O5) n’existe pas dans Data!B1:Q1. Inspectez la casse/espaces et les « Reward » orthographiés différemment.
  • #REF! avec INDIRECT : la plage nommée n’existe plus (renommage). Réparez la définition dans le Gestionnaire de noms.
  • #SPILL! (déversement) avec 365 : la zone cible n’est pas libre. Supprimez les obstacles (valeurs, fusion de cellules) en dessous/à droite.
  • Valeurs incohérentes : assurez‑vous que l’unité est la même d’une semaine à l’autre (%, unités, format heures). Standardisez les formats.

Bonnes pratiques de structuration

  • Uniformisez les en‑têtes : Week 1 Reward 1 partout, jamais Week1Reward1 dans une colonne et Week 1 Reward 1 dans une autre.
  • Ajoutez un horodatage de dernière mise à jour (=MAINTENANT()) dans un coin de la feuille principale pour la traçabilité.
  • Séparez données et interface : feuille Data pour la base, feuille(s) UI pour l’affichage et la saisie. Évitez de mélanger.
  • Utilisez des noms (Gestionnaire de noms) pour IdentCol (Data!$A$2:$A$11), HeadRow (Data!$B$1:$Q$1), Body (Data!$B$2:$Q$11) afin de clarifier les formules.

Power Query, TCD et autres accélérateurs

Power Query : si vos données proviennent d’un autre fichier ou d’une base, importez, dépliez/transformez (pivot/dépivot) et chargez vers Data. Avec des en‑têtes « Semaine + Reward », la formule XLOOKUP reste inchangée. Rafraîchissez en un clic.

Tableaux croisés dynamiques : pour analyser (somme/moyenne par site, comparaison de semaines), insérez un TCD. Filtrez sur « Semaine » pour zoomer sur la période voulue.

Macros VBA : non nécessaires ici. Les fonctions natives suffisent et sont plus faciles à maintenir/collaborer.

Pas à pas complet sur la feuille principale

  1. En M2, installez la liste déroulante liées à WeekList.
  2. Placez vos identifiants en N6:N100 (ou autant que nécessaire).
  3. Renseignez O5:Q5 avec Reward 1, Reward 2, Reward 3 (adaptez au nombre réel).
  4. Collez la formule XLOOKUP imbriquée en O6 (version FR ou EN selon votre Excel), puis recopiez vers la droite et vers le bas pour couvrir tous les identifiants et Rewards.
  5. Formatez les cellules (nombre, pourcentage, barres de données) sans toucher au contenu des en‑têtes ni aux plages sources.

Annexe : formules prêtes à l’emploi

Imbriquée XLOOKUP (EN)

=XLOOKUP($N6, Data!$A$2:$A$11, XLOOKUP($M$2&" "&O$5, Data!$B$1:$Q$1, Data!$B$2:$Q$11), "")

Imbriquée RECHERCHEX (FR)

=RECHERCHEX($N6; Data!$A$2:$A$11; RECHERCHEX($M$2&" "&O$5; Data!$B$1:$Q$1; Data!$B$2:$Q$11); "")

Alternative INDEX + XMATCH

=INDEX(Data!$B$2:$Q$11; XMATCH($N6; Data!$A$2:$A$11; 0); XMATCH($M$2&" "&O$5; Data!$B$1:$Q$1; 0))

INDEX + INDIRECT (ancré sur O6)

=INDEX(INDIRECT(SUBSTITUTE($M$2," ","")); ROW()-ROW($O$6)+1; COLUMN()-COLUMN($O$6)+1)

Déversement d’une colonne complète

=XLOOKUP($F$1, Data!$A$1:$Q$1, Data!$A$2:$Q$999)

Check‑list de fiabilisation

  • ✔︎ Les en‑têtes Data respectent strictement le format Week N Reward X.
  • ✔︎ La liste déroulante M2 pointe sur WeekList (et non du texte libre).
  • ✔︎ Les plages de Data couvrent bien toutes les lignes utiles (pensez au Tableau).
  • ✔︎ La formule en O6 a été recopiée sans briser les références mixtes ($ bien placés).
  • ✔︎ Les identifiants en N sont uniques (ou définissez la règle d’agrégation si non uniques).

FAQ courte

Peut‑on utiliser des libellés « S1, S2, …, S8 » au lieu de « Week 1…8 » ?
Oui, à condition que les en‑têtes et la liste déroulante utilisent exactement les mêmes libellés. Ajustez simplement $M$2&" "&O$5 si vous changez l’espace ou la syntaxe.

Comment étendre à 12 semaines ?
Ajoutez les colonnes correspondantes dans Data, prolongez la plage HeadRow/Body (ou laissez le Tableau gérer), complétez WeekList jusqu’à Week 12 : les formules n’ont pas besoin d’être modifiées.

Et si je veux un calendrier réel avec dates ?
Conservez la logique d’en‑têtes, ou passez à un modèle « long » (colonnes Identifiant, Semaine, Reward, Valeur) avec un TCD filtré par semaine : flexible pour l’analyse.

Conclusion

En structurant vos données avec des en‑têtes concaténés « Semaine + Reward » et en pilotant l’affichage via une liste déroulante (M2), vous obtenez un tableau de bord instantanément réactif. La solution XLOOKUP imbriqué est la plus claire et la plus robuste pour Excel 365/2021, tandis que INDEX + INDIRECT garantit une compatibilité maximale. Les conseils de validation, de nommage, et de dépannage ci‑dessus vous aideront à garder un classeur propre, extensible et prêt pour de nouveaux Rewards ou semaines sans retoucher les formules.

Récapitulatif ultra‑court

  • Liste déroulante en M2 alimentée par WeekList.
  • En‑têtes Data au format Week N Reward X.
  • Formule type en O6 (FR) :
    =RECHERCHEX($N6;Data!$A$2:$A$11;RECHERCHEX($M$2&" "&O$5;Data!$B$1:$Q$1;Data!$B$2:$Q$11);"")
  • Recopiez à droite (Rewards) et en bas (identifiants).
Excel
Excel Power Query XLOOKUP Validation des données INDEX/INDIRECT
Sommaire