EX11 | Tableaux dynamiques
Document fictif à usage pédagogique.
Énoncé
Contexte
- Entreprise : Brévalis Industries (fictive).
- Demande : pour la région choisie en B3, lister les clients actifs, leur CA, leur classement et les grosses commandes, avec des formules qui se mettent à jour seules.
- Durée : 30 min | Niveau : avancé | Version : Microsoft 365 ou Excel 2021.
- Départ : EX11_depart.xlsx (Ventes/tblVente, Analyse) | Fin : EX11_corrige.xlsx.
Travail demandé
- Liste triée des régions.
- Clients actifs de la région, CA de chaque client, classement par CA décroissant.
- Provoquer puis corriger une erreur #PROPAGATION!.
- Commandes de plus de 5 000 € de la région, puis changer de région.
Simulation
- Simulation interactive EX11 (8 étapes guidées, validation automatique) : voir la sous-page EX11 | Simulation.
Corrigé
E00 | Ouvrir le fichier de départ
- Fichier > Ouvrir > Parcourir >
Documents\excel-lab\depart\EX11_depart.xlsx.
Capture : boîte Ouvrir, dossier depart, fichier EX11_depart.xlsx sélectionné
E01 | UNIQUE
- Analyse!A6 :
=UNIQUE(tblVente[Region]): 4 régions propagées en A6:A9.
Capture : plage propagée encadrée en bleu
E02 | TRIER
- A6 :
=TRIER(UNIQUE(tblVente[Region])).
E03 | FILTRE
- C6 :
=TRIER(UNIQUE(FILTRE(tblVente[CodeClient];tblVente[Region]=B3))).
E04 | Référence propagée C6
- D6 :
=SOMME.SI.ENS(tblVente[MontantHT];tblVente[CodeClient];C6#;tblVente[Region];B3): un CA par client. - C6# désigne toute la plage propagée depuis C6.
E05 | TRIERPAR
- F6 :
=TRIERPAR(C6#;D6#;-1); G6 :=TRIERPAR(D6#;D6#;-1).
E06 | #PROPAGATION!
- Taper x en C8 : C6 affiche #PROPAGATION! (et les formules qui utilisent C6# passent en erreur).
- Supprimer C8 : la liste réapparaît.
Capture : message d'erreur de propagation
E07 | FILTRE à deux critères
- I6 :
=FILTRE(tblVente[[NumCommande]:[CodeClient]];(tblVente[Region]=B3)*(tblVente[MontantHT]>5000);"Aucune"). - Le produit des deux conditions vaut ET ; leur somme vaudrait OU.
E08 | Changer de région
- B3 > Normandie : toutes les listes se recalculent.
- F12 > EX11_corrige.
Points de vigilance
- Ne rien saisir sous ou à droite d'une formule propagée.
- Une formule propagée ne peut pas être placée dans un tableau Excel (tblXxx) : #PROPAGATION!.
- Ouvert dans Excel 2016 ou 2019, le fichier affiche des formules matricielles figées ou des erreurs #NOM?.