Somme des ventes Excel sur un mois variable

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


Sujets que vous pourriez aussi aimer :


Bilan consolidé sur un mois variable

Nous avons appris à dresser des bilans efficaces et dynamiques avec Excel et les calculs matriciels. Mais cette gymnastique est aussi simplifiée grâce à l'exploitation de la fonction Decaler.



Dans l'exemple finalisé illustré par la capture, l'utilisateur choisit un numéro de mois à isoler par le biais d'une liste déroulante. Aussitôt, les résultats sont consolidés pour chaque groupe d'articles vendus. Et dans le même temps, une mise en forme conditionnelle repère le mois désigné dans la base de données.

Source et présentation de la problématique
Nous allons appuyer ces travaux sur une source de données existante.

• Télécharger le classeur bilan-consolide-mois-variable.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é,

Nous découvrons donc la base de données des ventes réalisées par article pour chaque mois de l'année. Elle s'étend entre les colonnes B et N et entre les lignes 5 et 28. Ces chiffres ont été retranscrits au coup par coup. C'est la raison pour laquelle, un même article est repéré à de nombreuses reprises. Malgré l'ensemble des lignes énumérées, ils sont seulement trois au total. Il s'agit des articles Ar01, Ar02 et Ar03. Il est donc nécessaire de consolider et de synthétiser ces chiffres sur demande. C'est la raison de la présence d'une liste déroulante en cellule Q5. Elle permet à l'utilisateur de désigner un mois par son numéro. En fonction de ce choix, les résultats cumulés doivent être calculés pour chaque article en cellules Q7, Q8 et Q9. La problématique consiste donc à analyser une colonne variant en fonction du choix émis.

En déployant la zone Nom en haut à gauche de la feuille Excel, vous constatez que la première colonne de la base est nommée articles. Elle désigne toutes les références énumérées. Elle est primordiale pour les critères à honorer afin de fournir les consolidations compartimentées. Nous l'exploiterons donc dans les formules.

Somme conditionnelle sur colonne variable
Les additions que nous devons produire sont bien soumises à conditions. Non seulement, elles dépendent de la référence article rappelée dans le tableau de synthèse, mais elles dépendent aussi du choix du mois. La fonction Somme.Si est donc de mise :

=Somme.Si(Plage_du_critère1; Critère1; Plage_du_critèreN; CritèreN; Somme_plage)

En fonction d'un ou plusieurs critères émis sur une ou plusieurs plages, elle réalise la somme correspondante sur une autre plage à désigner en dernier argument. Ici, le critère est unique mais variable. Il s'agit de la référence article à répliquer avec le calcul sur les lignes du dessous. Donc, la plage d'analyse est unique. Il s'agit de la colonne des articles reconnue par son nom. La plage à utiliser pour la somme correspondante dépend du numéro choisi pour le mois avec la liste déroulante. Nous l'exploiterons pour déplacer l'analyse de la fonction sur la bonne rangée. Vous l'avez compris, la fonction Excel Decaler doit intervenir dans ce dernier argument de la fonction Somme.Si.

• Sélectionner le premier résultat à fournir pour la référence Ar01, soit la celluleQ7,
• Taper le symbole égal (=) pour initier la formule,
• Inscrire la fonction pour l'addition conditionnelle, suivie d'une parenthèse, soit : Somme.Si(,
• Désigner la plage du critère à honorer par son nom, soit : articles,
• Taper un point-virgule (;) pour passer dans l'argument du critère,
• Désigner la référence en regard du calcul par ses coordonnées, soit : P7,
• Taper un point-virgule (;) pour passer dans l'argument de la plage pour la somme,

C'est précisément la colonne de cette plage qui doit s'ajuster au numéro spécifié par le biais de la liste déroulante. La colonne articles est la première de cette base de données. Elle est considérée comme la rangée de référence. Par rapport à ce point de départ, nous devons déplacer l'analyse d'un certain nombre de colonnes vers la droite. Ce déplacement doit correspondre au numéro indiqué :

=Decaler(Point_de_départ; Décalage_ligne; Décalage_colonne; [Hauteur]; [Largeur])

Une fois n'est pas coutume, nous allons donc implémenter le troisième argument de cette fonction Decaler. Il concerne le décalage en colonne, soit le déplacement à l'horizontal par rapport au point de départ renseigné.

• Inscrire la fonction de déplacement suivie d'une parenthèse, soit : Decaler(,
• Désigner la colonne des articles par son nom, soit : articles,

Nous définissons ainsi le point de départ de l'analyse dont la borne doit s'ajuster en fonction du choix dynamique.

• Taper un point-virgule suivi du chiffre zéro, soit : ;0,

En effet, nous ne souhaitons observer aucun décalage en ligne par rapport à ce point de départ. Toutes les références doivent être analysées, en partant de la première.

• Taper un point-virgule (;) pour passer dans l'argument du décalage en colonne,
• Sélectionner le nombre choisi en cliquant sur sa cellule Q5,
• Puis, enfoncer la touche F4 du clavier pour la figer, ce qui donne : $Q$5,

En effet, nous allons répliquer cette formule sur les deux lignes du dessous pour produire la consolidation des deux autres références. Mais chaque consolidation doit observer un décalage en fonction de l'indication fournie par cette cellule qui ne doit donc pas suivre le déplacement.

• Fermer la parenthèse de la fonction Decaler,

Nous ne renseignons pas les deux derniers arguments de la fonction Decaler. Ils sont en effet facultatifs. Ils concernent respectivement la hauteur et la largeur de la plage. Ces dimensions doivent être fidèles à celles de la colonne articles passée en référence pour le point de départ de l'analyse. Donc, nous ne les touchons pas.

• Puis, fermer la parenthèse de la fonction Somme.Si,
• Enfin, valider le calcul par le raccourci clavier CTRL + Entrée,

Cette technique permet de conserver active la cellule du résultat afin de l'exploiter dans la foulée.

• Tirer la poignée du résultat sur les deux lignes du dessous,

Les trois résultats consolidés sont parfaitement calculés. Et bien sûr, si vous changez le numéro du mois en cellule Q5, les cumuls se recalculent instantanément en conséquence.



La formule que nous avons construite est donc la suivante :

=SOMME.SI(articles; P7; DECALER(articles; 0; $Q$5))

Vous notez que le chiffre consolidé le plus important est automatiquement mis en valeur dans des attributs de mise en forme explicitement différents. Il s'agit d'une règle de mise en forme conditionnelle en vigueur sur ces trois cellules.

• Sélectionner l'un des trois montants consolidés, par exemple la cellule Q8,
• 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 Gérer les règles,
• Dans la boîte de dialogue qui suit, cliquer sur le bouton Modifier la règle,

Nous accédons ainsi à sa syntaxe : =Q7=MAX($Q$7:$Q$9). La fonction Max est utilisée pour déterminer quelle est la plus grande des trois valeurs. Une fois décelée par la règle, sa cellule est parée dynamiquement des attributs de mise en forme que nous avons constatés.

• Cliquer sur le bouton Annuler pour revenir sur la première boîte de dialogue,
• Puis, cliquer sur le bouton Fermer du gestionnaire de règles pour revenir sur la feuille Excel,

Un calcul trivial nous attend désormais. Il s'agit de consolider les montants recoupés pour le mois désigné. Ce total est attendu en cellule Q11. Il implique naturellement la fonction Excel Somme.

• En cellule Q11, construire et valider la formule suivante : =SOMME(Q7:Q9),

Pour une vérification rapide, vous pouvez sélectionner tous les montants de la colonne désignée par le numéro du mois. En consultant la barre d'état en bas à droite de la feuille Excel, vous constatez que le montant consolidé conduit à la même somme que celle que nous avons calculée. Ce recoupement confirme par la même occasion que les consolidations compartimentées par articles sont justes.

Repérer la colonne du mois choisi
Nous souhaitons maintenant surligner dynamiquement tous les montants dans la colonne correspondant au numéro de mois spécifié avec la liste déroulante. Ainsi, nous viendrons recouper et renforcer les résultats des calculs précédents, pour une meilleure interprétation des données. Nous devons simplement bâtir une règle de mise en forme conditionnelle établissant la correspondance entre les numéros de mois.

• Sélectionner toutes les données du tableau, soit la plage de cellules C6:N28,
• 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 ...,
• Dans la zone de saisie du dessous, bâtir la syntaxe suivante : =Mois(C$5)= $Q$5 ,

Grâce à la fonction Excel Mois, nous cherchons à établir la correspondance entre les dates en entête du tableau, soit en ligne 5, et la date choisie avec la liste déroulante en Q5. Malgré l'inscription abrégée en apparence, ce sont bien des dates complètes qui sont inscrites en titre de chaque colonne. C'est un format personnalisé qui restreint leur affichage. Il suffit de consulter la barre de formule de chacune pour le constater.

Notez que la cellule C5 est figée en ligne mais pas en colonne. Pour chaque valeur du tableau, la comparaison avec la référence doit être faite sur la date, immuablement placée en ligne 5. En revanche, toutes les dates doivent être comparées. Donc, nous laissons la cellule se déplacer en colonne au gré de l'analyse chronologique effectuée par la mise en forme conditionnelle. La cellule du numéro choisi ne bouge jamais. C'est la raison pour laquelle nous la conservons complètement figée ($Q$5).

Lorsque la correspondance est établie, nous devons surligner l'intégralité des données de la colonne du mois correspondant.

• Pour cela, 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 jaune pâle par exemple,
• Activer ensuite l'onglet Police de la boîte de dialogue,
• Avec la liste déroulante, choisir un orange foncé pour le texte,
• Valider ces attributs de format par le bouton Ok,

Nous sommes de retour sur la première boîte de dialogue. Elle résume la situation à adapter. Lorsque les numéros de mois concordent, l'intégralité de la colonne doit se remplir d'un fond jaune pâle avec des textes formatés en orange foncé.

• Valider la création de cette règle de mise en forme conditionnelle par le bouton Ok,

De retour sur la feuille, la colonne du mois en vigueur est effectivement surlignée. Et bien sûr, si vous changez le numéro en Q5, le focus se déplace pour faire ressortir la colonne du mois correspondant.



Repérer le meilleur article
Pour parfaire la solution, en colonne B du tableau de données, nous souhaitons surligner toutes les références répétées de l'article ayant conduit au meilleur résultat pour le mois désigné. Il s'agira d'une bonne façon de recouper visuellement les résultats avec les calculs de consolidation.

La règle à bâtir est forcément particulière. Elle doit faire la correspondance entre les références énumérées en colonne B et la référence du meilleur résultat dans le tableau de synthèse. Il s'agit donc de les rechercher à l'aide des fonctions Index et Equiv. Mais pour que la position de la référence à retourner soit trouvée par la fonction Equiv, nous devons engager la recherche sur le meilleur résultat. C'est pourquoi nous devons lui imbriquer la fonction Max.

• Sélectionner toutes les références de la colonne B, soit la plage de cellules B6:B28,
• 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 ...,
• Dans la zone de saisie juste en-dessous, bâtir la syntaxe suivante :

=B6=INDEX($P$7:$P$9; EQUIV(MAX($Q$7:$Q$9); $Q$7:$Q$9; 0))

Ainsi, nous passons en revue chaque référence (B6) du tableau. Nous souhaitons établir la correspondance avec la référence du tableau de synthèse ($P$7:$P$9). Mais cette correspondance ne doit être validée que dans la mesure où il s'agit bien de la référence ayant conduit au meilleur résultat consolidé (EQUIV(MAX($Q$7:$Q$9)). Lorsque le critère est honoré, nous proposons de faire réagir les cellules concernées dans les mêmes attributs de mise en forme que précédemment.

• 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 jaune pâle par exemple,
• Activer ensuite l'onglet Police de la boîte de dialogue,
• Avec la liste déroulante, choisir un orange foncé pour le texte,
• Valider ces attributs de format par le bouton Ok,
• Valider ensuite la création de la règle de nouveau avec le bouton Ok,

De retour sur la feuille, vous notez que toutes les cellules de la référence pour le meilleur chiffre consolidé, sont repérées automatiquement. Cette seconde alerte visuelle vient encore renforcer la pertinence de la solution apportée. En changeant le numéro de mois en cellule Q5, si le meilleur score est attaché à une autre référence, les indicateurs visuels se déplacent automatiquement pour les repérer dans la source de données.



Pour une vérification d'usage, ces indicateurs vous permettent de repérer facilement les données à sélectionner. A l'issue, en consultant la barre d'état en bas à droite de la fenêtre Excel, vous constatez que le total livré recoupe le résultat consolidé pour la référence.