Synthèses des chiffres d'affaires au clic

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


Sujets que vous pourriez aussi aimer :


Statistiques au clic de la souris

Livrer des statistiques consolidées sur des critères recoupés au clic de la souris, est l'un des enjeux de cette formation.



Sur l'exemple finalisé illustré par la capture, l'utilisateur a cliqué sur l'une des cellules de la base de données. Dans le tableau de synthèse sur la droite, les données sur le mois et le jour de la semaine sont instantanément récupérées. Et en fonction de ces informations, des statistiques consolidées sont livrées. Nous calculons le chiffre d'affaires cumulé pour tous les mêmes jours de la semaine au cours de l'exercice. Nous faisons de même pour consolider ces chiffres sur le mois désigné. Et enfin, nous affinons la synthèse en recoupant le jour de la semaine avec le mois.

Source et présentation
Pour mener à bien cette étude, nous suggérons de débuter les travaux à partir d'une base de données existante.

• Télécharger le classeur ca-consolides-clic-souris.xlsm 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é,

Les chiffres d'affaires journaliers du magasin sont énumérés en colonne C pour chaque jour de l'année, mentionnés en colonne B. Les calculs de synthèse sont attendus en cellules F7, F9 et F11.



En F7, il s'agit de consolider tous les chiffres pour les jours de semaine correspondant à celui cliqué. En F9, est attendue la consolidation pour le mois désigné. En F11, il convient d'affiner ce résultat en recoupant le jour de semaine répété sur le mois spécifié par le clic.

Si vous déployez la zone Nom en haut à gauche de la feuille Excel, vous notez que les deux colonnes de la base de données sont reconnues par leurs titres respectifs. Ces noms seront utiles pour désigner les matrices dans la construction des formules matricielles.

Coordonnées de la cellule cliquée
Pour récupérer les informations sur le jour et le mois de la date, encore faut-il être en mesure de connaître la ligne de la cellule cliquée. De cette façon, nous pourrons pointer sur la colonne B pour décortiquer la date désignée. Ce calcul peut s'opérer facilement grâce aux fonctions Excel Adresse et Cellule. La première retourne les coordonnées d'une cellule en fonction de son indice de ligne, dépendant du clic, et de son indice de colonne, la deuxième ici pour la date. Nous proposons de réaliser un essai dans une cellule intermédiaire.

• Sélectionner la cellule E13 par exemple,
• Inscrire et valider la formule suivante : =ADRESSE(CELLULE('ligne');2),

C'est la fonction Cellule avec le paramètre ligne qui est susceptible de transmettre l'indice variable à la fonction Adresse. Ici, après avoir validé le calcul par le raccourci clavier CTRL + Entrée pour conserver la cellule active, le résultat retourné est : $B$13. Les coordonnées pointent donc sur la date de la ligne où le calcul est écrit. Mais nous souhaitons analyser son contenu pour en extraire le jour de semaine et le mois. Pour cela, nous devons exploiter la fonction Indirect.

• En cellule E13, adapter la précédente formule comme suit :

=INDIRECT(ADRESSE(CELLULE('ligne');2))

Le résultat obtenu correspondant pour l'instant à un numéro de série d'une date non formatée.

• Sélectionner de nouveau la cellule E13,
• Dans la section Nombre du ruban Accueil, déployer la liste déroulante des formats,
• Dans la liste des propositions, choisir le format Date longue,

Désormais, c'est bien la date inscrite en cellule B13 qui est restituée.

Déclencher le calcul au clic
Pour que cette fonction Adresse, sous l'impulsion de la fonction Cellule, soit en mesure de pointer sur la date de la ligne désignée, cette formule doit s'actualiser au clic de l'utilisateur. Or par défaut, le recalcul n'est enclenché que sur des modifications de cellules. Pour palier ce souci, nous devons exploiter un petit bout de code VBA sur un événement bien précis.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur Visual Basic Excel,
• Dans l'explorateur de projet sur la gauche, double cliquer sur l'élément Feuil1 (CA),

Ainsi, nous affichons sa feuille de code au centre de l'écran.

• En haut de la feuille de code, déployer la liste déroulante de gauche,
• Dans la liste, certes pauvre en suggestions, choisir l'objet Worksheet,

Ainsi, nous créons la procédure événementielle Worksheet_SelectionChange. L'objet Target lui est passé en paramètre. Il désigne la cellule active au moment de l'événement. Cet événement correspond entre autres, au clic de la souris sur une autre cellule. Grâce à cet événement, nous allons pouvoir forcer le recalcul comme nous le souhaitions.

• Entre les bornes de la procédure événementielle, ajouter l'instruction VBA suivante :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate
End Sub

C'est la méthode Calculate de l'objet VBA Application qui ordonne le recalcul sur cet événement. Désormais donc, nous devrions être en mesure de récupérer les coordonnées de la cellule cliquée. Néanmoins, en tant que puristes, nous devrions affiner le code VBA comme suit :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ligne As Integer: Dim colonne As Byte

ligne = Target.Row: colonne = Target.Column

If (ligne >= 6 And ligne <= 371 And colonne >= 2 And colonne <= 3) Then
Application.Calculate
End If

End Sub

En exploitant les propriétés Row et Column de l'objet Target, nous ordonnons ce recalcul uniquement dans la mesure ou la cellule cliquée figure bien dans les bornes de la base de données.

• Enregistrer les modifications par le raccourci clavier CTRL + S par exemple,
• Puis, fermer l'éditeur de code pour revenir sur la feuille Excel,
• Cliquer alors sur l'un des chiffres d'affaires en colonne C de la base de données,

Instantanément, notre calcul réagit en cellule E13. Il pointe sur la ligne cliquée et restitue la date en colonne B.

Jour et mois d'une date cliquée
Avant de livrer les résultats statistiques, nous devons les annoncer explicitement. C'est la raison de la présence des cellules E7, E9 et E11. Nous devons adapter les intitulés dynamiquement. Ils expliciteront les résultats à suivre sur la consolidation des chiffres pour les jours de semaine, mois et recoupement sur les deux. Pour cela, nous devons exploiter le précédent calcul impliquant les fonctions Indirect, Adresse et Cellule. Elles reproduisent la date de la ligne cliquée. Mais nous souhaitons affiner l'information selon le cas. Pour ce faire, nous pouvons exploiter la fonction Texte. Nous l'avions notamment démontrée dans la formation complète sur les dates et les heures avec excel. Il suffit de lui passer un code de format personnalisé en second argument.

• En cellule E7, construire et valider la formule suivante :

=NOMPROPRE(TEXTE(INDIRECT(ADRESSE(CELLULE('ligne'); 2)); 'jjjj')) & 's'

Nous englobons donc la précédente syntaxe dans la fonction Texte. En second argument, le code jjjj permet de ne conserver que l'information sur le jour de semaine en version textuelle. Le tout est englobé dans la fonction NomPropre pour passer la première lettre en majuscule. Une concaténation est entreprise avec la lettre s afin de stipuler qu'il s'agit de tous les jours identiques à celui cliqué.

• De la même façon, construire et valider la formule suivante en cellule E9:

=NOMPROPRE(TEXTE(INDIRECT(ADRESSE(CELLULE('ligne'); 2)); 'mmmm'))

Le format de la fonction Texte change de manière à ne conserver que l'information textuelle sur la date du mois. De plus, nous supprimons la concaténation pour indiquer qu'il s'agit bien de la consolidation d'un mois et non de plusieurs.

Le dernier intitulé en cellule E11 est un assemblage de ces résultats. Il suffit de les concaténer comme suit : =E7 & ' / ' & E9.



Désormais, en cliquant sur l'un des chiffres d'affaires dans la base de données, les intitulés transformés et formatés sont parfaitement restitués. Ils annoncent les calculs statistiques que nous devons bâtir sur des critères à émettre sur la matrice des dates. Cette dernière doit être recoupée avec la matrice des chiffres d'affaires afin de fournir les bilans consolidés adaptés à chacune des attentes.

Consolider les chiffres d'affaires
Les chiffres doivent désormais être cumulés en tenant compte des contraintes importées, en commençant par le jour de la semaine. La fonction SommeProd est de mise. Elle doit émettre le critère sur la matrice des dates. Chaque date correspondant au jour de la semaine mentionné dans le tableau de bord, sera marquée par un indicateur booléen. En multipliant ces résultats par la matrice des chiffres d'affaires, nous obtiendrons bien le cumul des CA uniquement pour les jours correspondants.

• Sélectionner le premier résultat de synthèse à trouver, soit la cellule F7,
• Taper le symbole égal (=) pour initier la formule,
• Inscrire la fonction matricielle suivie d'une parenthèse, soit : SommeProd(,
• Ouvrir une nouvelle parenthèse pour accueillir la matrice conditionnelle,
• Inscrire la fonction pour le jour de la semaine, suivie d'une parenthèse, soit : JourSem(,
• Désigner la matrice des dates par son nom, soit : Dates,
• Fermer la parenthèse de la fonction Joursem,
• Taper le symbole égal (=) pour annoncer la condition à respecter sur le jour de semaine,
• Inscrire de nouveau la fonction du jour de semaine, suivie d'une parenthèse, soit : JourSem(,
• Répliquer la syntaxe du calcul pour récupérer la date sur la ligne cliquée, soit :

INDIRECT(ADRESSE(CELLULE('ligne'); 2))

• Fermer la parenthèse de la fonction JourSem,
• Fermer la parenthèse de la matrice conditionnelle,
• Taper le symbole de l'étoile (*) pour la multiplication par les CA,
• Désigner la plage des chiffres d'affaires par son nom, soit : CA,
• Fermer la parenthèse de la fonction SommeProd,
• Puis, valider la formule par la touche Entrée du clavier,
• Cliquer ensuite sur l'un des chiffres d'affaires du tableau, par exemple la cellule C8,

Aussitôt, la consolidation est opérée. Tous les chiffres d'affaires réalisés un Vendredi sont cumulés. Il s'agit d'un bon moyen de connaître rapidement les jours où l'affluence est propice. Bien sûr, si vous cliquez sur une autre ligne, la synthèse s'ajuste pour additionner les chiffres sur le jour de semaine correspondant.

Pour obtenir la statistique cumulée sur le mois désigné, la syntaxe doit simplement être ajustée sur le critère à honorer. Il suffit de remplacer la fonction JourSem par la fonction Mois.

• En cellule F9, adapter la syntaxe comme suit :

=SOMMEPROD((MOIS(Dates) = MOIS(INDIRECT(ADRESSE(CELLULE('ligne'); 2))))*CA)

Pour la synthèse affinée en cellule F11, il s'agit de recouper la contrainte sur le jour de la semaine et le numéro du mois. Nous devons donc recouper les deux matrices conditionnelles des précédentes formules dans un même calcul. Nous obtiendrons ainsi la somme des chiffres pour un jour de semaine cumulé dans le mois.

• En cellule F11, ajuster la syntaxe comme suit :

=SOMMEPROD((JOURSEM(Dates) = JOURSEM(INDIRECT(ADRESSE(CELLULE('ligne'); 2))))*(mois(Dates) = mois(INDIRECT(ADRESSE(CELLULE('ligne'); 2))))*CA)



Dans l'exemple illustré par la capture, nous avons cliqué sur la cellule C9. Il s'agit du chiffre d'affaires pour le Samedi 4 Janvier 2020. En cellule F11, il en résulte la consolidation de tous les chiffres d'affaires réalisés un Vendredi au cours du mois de Janvier.

Repérer visuellement les jours de semaine
Pour parachever la solution statistique, nous prévoyons de repérer visuellement les lignes des jours de semaine correspondant au clic de l'utilisateur. Nous renforcerons ainsi la pertinence des résultats recoupés par ces indicateurs. Il s'agit donc de bâtir une règle de mise en forme conditionnelle sur l'intégralité du tableau. Ces règles ne savent pas opérer de raisonnement matriciel. Elles raisonnent chronologiquement. Nous allons donc poser le critère sur la première date pour vérifier l'égalité sur le jour de la semaine cliqué. Elles seront ainsi toutes passées en revue.

• Sélectionner l'intégralité du tableau, soit la plage de cellules B6:C371,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• Tout 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 ...,
• Dans la zone de saisie juste en-dessous, construire la syntaxe suivante :

=JOURSEM($B6) = JOURSEM(INDIRECT(ADRESSE(CELLULE('ligne'); 2)))

Nouscomparons donc le jour de semaine de la première date avec le jour de semaine récupéré suite au clic de l'utilisateur. Pour que la ligne entière soit repérée en cas de concordance, nous figeons cette date en colonne ($B6). En revanche, nous libérons sa ligne pour qu'elles soient toutes comparées dans le processus chronologique de la règle.

• Cliquer maintenant 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 pâle pour le fond des cellules,
• Activer désormais l'onglet Police de la boîte de dialogue,
• Avec la liste déroulante, choisir un bleu foncé pour la couleur du texte,
• Puis, valider ces attributs de format dynamique avec le bouton Ok,

Nous sommes de retour sur la première boîte de dialogue. Elle offre un résumé sans équivoque sur le comportement de cette règle de mise en forme conditionnelle. Chaque ligne dont le jour de semaine est identique à celui de la ligne cliquée doit se parer d'un fond bleu pâle avec un texte bleu foncé.

• Cliquer sur le bouton Ok pour valider la création de la règle de mise en forme conditionnelle,

Désormais, au clic de l'utilisateur, en même temps que les données statistiques s'accordent, les lignes correspondantes du tableau sont automatiquement surlignées.