EX08 | Calculs conditionnels
Document fictif à usage pédagogique.
Énoncé
Contexte
- Entreprise : Brévalis Industries (fictive).
- Demande : un tableau de bord des ventes 2025 par région, gamme et site, sans TCD, pour qu'il se mette à jour sans actualisation.
- Durée : 30 min | Niveau : intermédiaire.
- Départ : EX08_depart.xlsx (Tableau de bord à compléter, Ventes en plage simple A1:M501) | Fin : EX08_corrige.xlsx.
Travail demandé
- Nombre de ventes par région (F5:F8).
- Grille CA HT région x gamme (B5:E8) en une seule formule recopiée.
- Par site (A13:A15) : CA HT, panier moyen, nombre de ventes de 20 unités et plus.
- CA HT sur une période (I4 à I5), puis passer au 3e trimestre.
Simulation
- Simulation interactive EX08 (6 étapes guidées, validation automatique) : voir la sous-page EX08 | Simulation.
Corrigé
E00 | Ouvrir le fichier de départ
- Fichier > Ouvrir > Parcourir >
Documents\excel-lab\depart\EX08_depart.xlsx.
Capture : boîte Ouvrir, dossier depart, fichier EX08_depart.xlsx sélectionné
E01 | NB.SI
- F5 :
=NB.SI(Ventes!$G$2:$G$501;$A5), recopier jusqu'en F8. Bretagne : 155 ventes.
E02 | Grille en une formule
- B5 :
=SOMME.SI.ENS(Ventes!$M$2:$M$501;Ventes!$G$2:$G$501;$A5;Ventes!$F$2:$F$501;B$4). - Recopier vers la droite jusqu'en E5, puis B5:E5 vers le bas jusqu'à la ligne 8.
$A5garde la colonne des régions,B$4garde la ligne des gammes.
Capture : formule SOMME.SI.ENS dans la barre de formule
E03 | CA par site
- B13 :
=SOMME.SI.ENS(Ventes!$M$2:$M$501;Ventes!$L$2:$L$501;$A13), recopier.
E04 | Panier moyen par site
- C13 :
=MOYENNE.SI.ENS(Ventes!$M$2:$M$501;Ventes!$L$2:$L$501;$A13), recopier.
E05 | Grosses ventes
- D13 :
=NB.SI.ENS(Ventes!$L$2:$L$501;$A13;Ventes!$H$2:$H$501;">=20"), recopier. - S-03 (services et logistique) n'a aucune vente de 20 unités : 0 est le bon résultat.
E06 | Période
- I6 :
=SOMME.SI.ENS(Ventes!$M$2:$M$501;Ventes!$B$2:$B$501;">="&I4;Ventes!$B$2:$B$501;"<="&I5). - Remplacer les dates par 01/07/2025 et 30/09/2025 : 437 778,02 €.
- F12 > EX08_corrige.
Capture : grille de synthèse complète
Points de vigilance
- Dans SOMME.SI.ENS, la plage à additionner vient en premier ; dans SOMME.SI, en dernier.
- Toutes les plages doivent avoir la même taille.
">=I4"chercherait le texte « I4 » : il faut">="&I4.