Statistiques recoupées et plages de dates

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Statistiques et plages de dates

Pouvoir isoler et consolider des informations sur une plage de dates variable est un enjeu important dans l'élaboration de statistiques, précieuses pour la compréhension et l'anticipation des données commerciales par exemple.



Dans l'exemple finalisé illustré par la capture ci-dessus, le responsable doit évaluer les chiffres des commerciaux de l'entreprise. Pour cela, il peut actionner deux leviers en choisissant un nom et un mois de l'année. Aussitôt, les résultats consolidés de ces ventes doivent s'actualiser.

Source et présentation du concept
Ces statistiques doivent opérer sur des données que nous proposons de récupérer.

• Télécharger le classeur statistiques-matricielles-dates.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous réceptionnons un classeur muni de deux feuilles nommées respectivement CA et Sources. Le tableau de la feuille CA, situé entre les colonnes B et E, énumère au coup par coup, les chiffres d'affaires réalisés par les commerciaux. Ces chiffres listés en colonne D sont confrontés à des objectifs individuels définis en colonne E.

Un panneau de contrôle est proposé sur la droite de la base de données entre les colonnes H et J. Il permet de désigner l'un des quatre commerciaux, grâce à une liste déroulante en cellule J9. Puis, il permet de définir un mois de l'année à isoler, toujours par le biais d'une liste déroulante, en cellule J11 cette fois.

Ces mois sont en réalité des dates complètes, 01/01/2019 par exemple pour le mois de Janvier. C'est un format personnalisé qui permet de ne conserver que la précision sur le nom du mois à l'affichage. Cette remarque est importante. C'est grâce à cette astuce que nous pourrons facilement comparer le mois défini avec les dates des ventes des commerciaux.

Le pourcentage réalisé par le commercial désigné par rapport aux objectifs demandés doit être calculé en cellule J7. Enfin, des résultats de synthèse sont attendus dans le petit tableau en ligne 14.

Identifier les matrices
Pour résoudre ce cas, nous allons déclencher des calculs matriciels selon des techniques que nous avons apprises lors des formations précédentes. Elles permettent de rechercher et de recouper des critères sur des plages de cellules pour lesquelles les lignes sont analysées entre elles. Et pour simplifier la syntaxe et la construction de ces formules matricielles, nous proposons d'attribuer des noms à ces matrices. Les quatre colonnes du tableau sont concernées. La méthode automatique de nommage est donc préconisée.

• Sélectionner l'intégralité de la petite base de données, soit la plage de cellules B6:E54,
• En haut de la fenêtre Excel, cliquer sur l'onglet Formules pour activer son ruban,
• Dans la section Noms définis du ruban, cliquer sur le bouton Depuis sélection,
• Dans la boîte de dialogue, décocher la case Colonne de gauche,
• En revanche, conserver cochée la case Ligne du haut,

De cette manière, chaque colonne du tableau sera désignée par le titre de son champ.

• Valider la création automatique des noms en cliquant sur le bouton Ok,
• En haut à gauche de la feuille Excel, déployer la liste déroulante de la zone Nom,

Vous remarquez la présence de toutes les plages ainsi nommées en fonction des titres du tableau. Si vous choisissez l'une d'entre elles, l'intégralité de la colonne correspondante est sélectionnée dans le tableau.

Résultats sur objectifs
Le premier calcul à réaliser en cellule J7 n'implique pas des conditions croisées. Il consiste à diviser la somme des chiffres d'affaires du commercial par la somme des objectifs proposés. Mais ces additions doivent être faites sur chaque enregistrement du commercial choisi avec la liste déroulante. La fonction Excel Somme.Si est donc dédiée pour réaliser une somme conditionnelle. De plus, pour ne générer aucune erreur, nous devons aussi nous assurer que le commercial est bien désigné. Nous devons intégrer le calcul dans une fonction conditionnelle Si.

• Sélectionner la cellule du premier résultat à trouver, soit J7,
• Taper le symbole égal (=) pour initier la syntaxe du calcul,
• Saisir la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Sélectionner la cellule du commercial désigné, soit J9,
• Taper le symbole inférieur suivi du symbole supérieur, suivi de deux guillemets, soit : <>'',

Grâce à cette inégalité, nous nous assurons que la cellule n'est pas vide, donc que le nom du vendeur est bien spécifié.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Saisir la fonction d'addition conditionnelle suivie d'une parenthèse, soit : Somme.Si(,
• Désigner la plage des commerciaux par son nom, soit : Vendeur,
• Taper un point-virgule (;) pour passer dans l'argument du critère à vérifier sur cette plage,
• Sélectionner de nouveau la cellule J9 pour repérer le commercial,
• Taper un point-virgule (;) pour passer dans l'argument de la plage pour la somme,
• Désigner la plage des chiffres réalisés par son nom, soit : Réalisé,
• Fermer la parenthèse de la fonction Somme.Si,
• Taper le symbole slash (/) pour enclencher la division,
• Saisir la fonction d'addition conditionnelle suivie d'une parenthèse, soit : Somme.Si(,
• Désigner de nouveau la plage des commerciaux par son nom, soit : Vendeur,
• Taper un point-virgule (;) pour passer dans l'argument du critère,
• Cliquer sur la cellule J9 du commercial à repérer,
• Taper un point-virgule (;) pour passer dans l'argument de la plage pour la somme,
• Désigner la plage des objectifs par son nom, soit : Objectif,
• Fermer la parenthèse de cette seconde fonction Somme.Si,
• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Saisir deux guillemets ('') pour garder la cellule vide lorsque le commercial n'est pas défini,
• Fermer la parenthèse de la fonction Si,
• Enfin, valider le calcul à l'aide de la touche Entrée,

Le résultat tombe et le pourcentage réalisé par le commercial s'affiche. Si vous changez de vendeur avec la liste déroulante en J9, le calcul conditionnel s'actualise fort naturellement. Il devra en être de même pour les trois autres résultats de synthèse attendus dans le petit tableau.

La syntaxe de la formule que nous avons construite est la suivante :

=SI(J9<> ''; SOMME.SI(Vendeur; J9; Réalisé)/SOMME.SI(Vendeur; J9; Objectif); '')



Résultats statistiques croisés
Désormais, pour livrer les calculs suivants, nous devons analyser plusieurs conditions à recouper sur des matrices respectives. Et nous proposons de commencer par la synthèse la plus simple à évaluer, celle du nombre de ventes réalisées par le commercial, en cellule J14. A chaque fois que le nom du commercial est repéré sur la plage Vendeur et que le mois spécifié concorde sur la plage Date, nous devons comptabiliser chaque vente.

Dans les formations précédentes, nous avons appris à exploiter la puissante fonction SommeProd, dans des contextes particuliers. A l'origine, elle multiplie les valeurs respectives de plusieurs matrices puis somme l'ensemble des résultats. Mais elle permet aussi de recouper des critères sur des matrices. Il en résulte des valeurs booléennes indiquant les emplacements trouvés. Lorsqu'elles sont multipliées, ces valeurs booléennes sont traduites en chiffres : 1 pour Vrai et 0 pour Faux. C'est ainsi que nous allons pouvoir compter les ventes du commercial pour un mois déterminé.

• Cliquer sur la cellule J14 pour la sélectionner,
• Taper le symbole égal (=) pour initier la formule matricielle,
• Saisir la fonction matricielle suivie d'une parenthèse, soit : SommeProd(,
• Ouvrir une nouvelle parenthèse pour accueillir la première matrice conditionnelle,
• Désigner la plage des commerciaux par son nom, soit : Vendeur,
• Taper le symbole égal (=) pour la condition à suivre,
• Sélectionner la cellule du commercial choisi, soit J9,
• Fermer la parenthèse de la matrice conditionnelle,
• Taper le symbole de l'étoile (*) pour la recouper avec une seconde condition matricielle,
• Ouvrir une nouvelle parenthèse pour accueillir la seconde matrice conditionnelle,
• Taper la fonction pour donner le mois d'une date, suivie d'une parenthèse, soit : Mois(,
• Désigner la matrice des dates par son nom, soit : Date,
• Fermer la parenthèse de la fonction Mois,
• Taper le symbole égal (=) pour le critère à satisfaire,
• Saisir de nouveau la fonction pour le mois, suivie d'une parenthèse, soit : Mois(,
• Désigner le mois choisi par l'utilisateur en cliquant sur sa cellule J11,
• Fermer la parenthèse de la fonction Mois,
• Fermer la parenthèse de la secondematrice conditionnelle,
• Fermer la parenthèse de la fonction SommeProd,
• Puis, valider le calcul matriciel par la touche Entrée du clavier,

Le nombre de ventes relatives aux deux conditions croisées s'inscrit aussitôt dans la cellule.



En adaptant le commercial et le mois, vous constatez que ce résultat s'actualise parfaitement. La formule matricielle que nous avons construite est la suivante :

=SOMMEPROD((Vendeur=J9)*(MOIS(Date) = MOIS(J11)))

Les résultats suivants s'obtiennent très facilement en déclinant le précédent calcul. Il s'agit de multiplier le dénombrement obtenu par la matrice contenant les chiffres dont il s'agit de livrer la synthèse.

• En cellule I14, adapter la précédente syntaxe comme suit :

=SOMMEPROD((Vendeur=J9)*(MOIS(Date) = MOIS(J11))*(Objectif))

• En cellule H14, adapter la syntaxe comme suit :

=SOMMEPROD((Vendeur=J9)*(MOIS(Date) = MOIS(J11))*(Réalisé))

Les résultats booléens du précédent calcul, transcrits en chiffres, sont parfaitement multipliés par les montants de chaque matrice désignée. Il en résulte la synthèse du chiffre d'affaires réalisé, confronté au total des objectifs posés, pour le vendeur et le mois désignés.



Mise en valeur dynamique
Pour renforcer la pertinence des résultats de synthèse livrés par les calculs matriciels, nous suggérons de repérer dynamiquement tous les enregistrements concordants dans la base de données. Il s'agit donc de créer une règle de mise en forme conditionnelle à appliquer sur tout le tableau. Cette règle doit tout d'abord s'assurer que le commercial et le mois sont bien définis. Elle doit ensuite vérifier la concordance dans les colonnes respectives. Il y a donc quatre conditions croisées à satisfaire. Pour les énumérer, nous devons exploiter la fonction Et.

• Sélectionner toutes les données du tableau, soit la plage de cellules B7:E54,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir l'option Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Puis, cliquer dans la zone de saisie juste en dessous pour l'activer,
• Taper alors la syntaxe suivante :

=ET($J$9<>''; $J$11<>''; $C7=$J$9; MOIS($B7)=MOIS($J$11))

Nous vérifions bien que les conditions sont spécifiées. Puis, nous établissons les critères de correspondance. Notez que les cellules du tableau sont libérées en ligne et figées en colonne ($C7 et $B7). En effet, chaque ligne doit être passée en revue. Le critère quant à lui ne peut être vérifié que dans une colonne spécifiée.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un bleu clair,
• Activer ensuite l'onglet Police de la boîte de dialogue,
• Avec la liste déroulante, choisir un bleu foncé pour la couleur du texte,
• Valider ces attributs dynamiques par le bouton Ok,
• De retour sur la première boîte de dialogue, valider la création de la règle par le bouton Ok,

Désormais, les enregistrements réagissent dynamiquement et appuient parfaitement la synthèse fournie par les formules matricielles.