formateur informatique

Statistiques des CA en fonction du jour de la semaine

Accueil  >  Bureautique  >  Excel  >  Excel Avancé  >  Statistiques des CA en fonction du jour de la semaine
Livres à télécharger


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

Abonnez-vous gratuitement à la chaîne Youtube Rossetti Stéphane .
Sujets et formations similaires :


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.

Statistiques matricielles Excel sur chiffres affaires selon jour semaine et mois

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. 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.

Noms des colonnes Excel pour exploitation dans les formules matricielles

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,
Assistant pour la fonction Excel SommeProd afin de livrer les résultats au fil de la construction

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,
Concordances sur critère matriciel indiquées aux emplacements par assistant fonction Sommeprod

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,
Consolidation des chiffres affaires par jour de semaine grâce à la fonction Excel SommeProd

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.

Consolider les chiffres d-affaires pour chaque mois de l-année grâce à la fonction Excel SommeProd

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.

 
Sur Facebook
Sur Youtube
Les livres
Contact
Mentions légales



Abonnement à la chaîne Youtube
Partager la formation
Partager sur Facebook
Partager sur Twitter
Partager sur LinkedIn