Statistiques des CA en fonction du jour de la semaine

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


Sujets que vous pourriez aussi aimer :


Chiffres d'affaires et jours de semaine

Nous l'avons appris, les calculs matriciels permettent de réaliser de puissantes extractions groupées là où les techniques classiques requièrent de poser des calculs intermédiaires. Mais ils permettent aussi de réaliser des statistiques fines là où les formules standards ne délivrent plus de solution.



La solution finalisée est illustrée par la capture ci-dessus. Les chiffres d'affaires d'un magasin sont recensés dans un premier tableau, pour tous les jours de l'année. Une première statistique est livrée dans un deuxième tableau. Elle consolide tous les chiffres d'affaires réalisés au cours de l'année entière pour chaque même jour de semaine. Ces informations sont particulièrement intéressantes pour connaître les pics de fréquentation récurrents, s'ils existent. Enfin, les chiffres d'affaires consolidés pour chaque mois de l'année sont calculés dans un troisième tableau.

Source et présentation
Pour démontrer l'intérêt et la puissance de ces solutions, nous suggérons d'établir ces travaux sur la base d'informations existantes.

• Télécharger le classeur chiffres-affaires-matriciels.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Tous les chiffres d'affaires journaliers du magasin sont énumérés en colonne C, en regard des dates respectives en colonne B, pour l'ensemble de l'année. Une première synthèse doit permettre de livrer ces chiffres consolidés, en fonction du jour de la semaine en colonne G. Une seconde consolidation doit être produite en colonne K mais cette fois, pour chaque mois de l'année.



En déployant la liste déroulante de la zone Nom en haut à gauche de la feuille Excel, vous remarquez que les deux colonnes de la source de données sont reconnues par leur titre de champ. Nous exploiterons ces noms pour simplifier la construction des formules matricielles.

CA consolidés par jour de la semaine
Pour additionner tous les chiffres d'affaires en fonction du jour de semaine, un critère doit être vérifié sur la plage des dates. A chaque fois qu'il est concordant, ce sont les chiffres d'affaires respectifs qui doivent être consolidés.

La fonction Somme.Si semble a priori dédiée. Mais, dans le tableau de synthèse, ce sont les numéros de semaine que nous devons faire correspondre avec les dates de la source de données. Pour cela, la fonction JourSem est nécessaire. Mais, la fonction Somme.Si n'est pas en mesure d'analyser ce critère de correspondance sur une matrice globale selon une syntaxe que nous pourrions être tentés de construire : =Somme.Si(JourSem(Dates);F6;CA).

C'est alors que le raisonnement matriciel intervient : {=SOMME(SI(JOURSEM(Dates)=F6;CA))}.

Avec cette syntaxe, nous réalisons la somme des chiffres d'affaires pour lesquels la correspondance avec le numéro de semaine est avérée. Il s'agit donc bien d'une somme conditionnelle. L'analyse est enclenchée sur la matrice des dates. La consolidation est opérée sur la matrice des chiffres d'affaires, lorsque le jour de semaine concorde, grâce à la fonction Excel Si. Bien sûr, pour établir un raisonnement matriciel, cette formule doit être validée par le raccourci clavier CTRL + MAJ + Entrée. Puis, la poignée du résultat doit être tirée sur les lignes du dessous pour consolider chaque jour. C'est la raison pour laquelle la cellule F6, utilisée dans le critère, n'est pas figée. Tous les jours de semaine sont ainsi considérés.

Mais, une solution encore plus triviale consiste à exploiter la fonction SommeProd. Elle permet d'additionner les valeurs multipliées entre les lignes de matrices respectives. Ici, chaque chiffre d'affaires correspondant au jour de semaine doit être multiplié par 1 pour qu'ils soient tous consolidés à l'issue quand dans le même temps les autres doivent être ignorés. Pour cela, nous devons multiplier la matrice des chiffres d'affaires par un critère posé sur la matrice des dates. Pour chaque jour concordant, le critère répondra par Vrai, traduit par le chiffre 1.

• Sélectionner la première consolidation à livrer, soit la cellule G6,
• Taper le symbole égal (=) pour initier la formule matricielle,
• Saisir la fonction matricielle suivie d'une parenthèse, soit : SommeProd(,
• A gauche de la barre de formule, cliquer sur le bouton Insérer une fonction,

Grâce à lui, nous allons comprendre plus facilement le raisonnement et l'effet de la condition à poser.

• Dans la zone Matrice1, ouvrir une parenthèse pour accueillir la matrice conditionnelle,
• Inscrire la fonction donnant le jour de la semaine, suivie d'une parenthèse, soit : JourSem(,
• Désigner la plage des dates par son nom de matrice, soit : Dates,
• Fermer la parenthèse de la fonction JourSem,
• Taper le symbole égal (=) pour annoncer la condition à honorer,
• Désigner le premier numéro de semaine du tableau de synthèse avec ses coordonnées : F6,

Ainsi, l'ensemble de la matrice des dates va être scrutée à la recherche de celles dont le numéro de semaine concorde avec le premier jour, puis les suivants par réplication. En effet, nous n'avons pas figé cette cellule F6.

• Fermer la parenthèse de la matrice conditionnelle,

Aussitôt des indicateurs booléens viennent se greffer sur la droite de la boîte de dialogue, en regard de la zone Matrice1. Les mentions Vrai dévoilent la position des jours de semaine en accord avec le critère posé. En multipliant ces résultats par les chiffres d'affaires, ces booléens vont se transformer en chiffres : 1 pour Vrai et 0 pour Faux. De fait, tous les chiffres des jours non concordants vont être ignorés.

• Taper le symbole de l'étoile (*) pour annoncer la multiplication à entreprendre,
• Désigner la matrice des chiffres d'affaires par son nom, soit : CA,
• Cliquer sur le bouton Ok de l'assistant fonction pour valider la formule matricielle,
• Double cliquer sur la poignée du résultat pour répliquer la formule sur les autres jours,

Toutes les consolidations matricielles sont ainsi livrées. Le critère s'adapte au jour de la semaine énoncé par le tableau de bord au fil de la réplication. Sans grandes surprises, nous constatons que les Vendredis et Samedis sont les jours les plus prolifiques pour ce magasin, sans écarter les Mercredis qui sortent du lot.

Et c'est une syntaxe matricielle relativement simple qui offre la solution qu'une formule classique n'aurait pas pu aboutir : =SOMMEPROD((JOURSEM(Dates)=F6)*CA).

CA consolidés par Mois
Pour livrer la synthèse des chiffres d'affaires réalisés chaque mois de l'année, le principe est exactement le même. La contrainte doit cependant être posée sur le mois de chaque date à comparer. Et pour cela, nous devons remplacer la fonction JourSem par la fonction Excel Mois.

• En cellule J6, il convient donc de construire et valider la formule suivante :

=SOMMEPROD((MOIS(Dates)=J6)*CA)

• Ensuite, il s'agit de double cliquer sur la poignée du résultat,

Ainsi, nous obtenons toutes les consolidations.



Si vous sélectionnez l'ensemble des chiffres pour un mois de l'année, vous pouvez consulter la somme des données sélectionnées dans la barre d'état, en bas à gauche de la fenêtre Excel. Et comme vous pouvez le voir, les résultats de synthèse matricielle concordent parfaitement.