Calculs de synthèse sur une période variable

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


Sujets que vous pourriez aussi aimer :


Calculs sur la recherche d'un mois

Nous avons déjà exploité à bon escient de nombreux paramètres de la fonction Excel Decaler. Ainsi, nous avons utilisé l'argument du décalage en ligne, celui du décalage en colonne et enfin celui de la hauteur variable. Tous ces paramètres, définis par rapport à une position de départ en premier argument, nous ont permis d'entreprendre des calculs de synthèse en fonction de contraintes dynamiques, visant à déplacer les bornes des plages à analyser. Mais, nous n'avons pas encore exploité son dernier argument possible, celui de la largeur variable. Dans cette formation, nous allons démontrer son intérêt.



L'exemple finalisé à reproduire est illustré par la capture ci-dessus. Sur une base de données des ventes réalisées par article et par mois, nous effectuons la somme et la moyenne de ces ventes sur un nombre variable de mois. Tout dépend du mois choisi par l'utilisateur dans une liste déroulante située sur la droite du tableau de données. Mais contrairement au cas de la formation précédente, l'information sur le mois est textuelle. Nous ne pouvons donc par réaliser un décalage correspondant au numéro fourni.

Source et présentation
Nous proposons tout d'abord de récupérer le tableau de données afin de concentrer l'étude sur les techniques de calcul.

• Télécharger le classeur calculs-sur-recherche-mois.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 récupérons donc un tableau rappelant les ventes réalisées par article pour chacun des mois de l'année. Et précisément, une liste déroulante en cellule R3, permet de désigner l'un de ces mois, non plus sous forme de numéro mais sous forme de texte. C'est en fonction de cette information que les synthèses doivent être établies pour chacun des articles. Il s'agit premièrement de calculer la somme des ventes du premier mois de l'année jusqu'au mois désigné. De la même façon, la moyenne doit être calculée pour ces N premiers mois.

Sommer les N premiers mois
Pour opérer sur une plage de cellules de largeur variable, nous devons exploiter la fonction Excel Decaler.

=Decaler(Position_de_départ; Decalage_ligne; Decalage_colonne; [Hauteur]; [Largeur])

Pour déterminer cette largeur, nous devons trouver la position du mois choisi dans l'énumération en ligne 5 de l'entête du tableau de données. Puisque cette information est livrée sous forme de texte, nous devons la rechercher avec la fonction Equiv. Celle-ci retournera la position trouvée en colonne. Nous en déduirons dynamiquement la largeur de la plage pour le calcul.

• Sélectionner la première somme variable à calculer en cliquant sur sa cellule Q6,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction d'addition suivie d'une parenthèse, soit : Somme(,
• Inscrire la fonction d'ajustement suivie d'une parenthèse, soit : Decaler(,
• Cliquer sur le premier chiffre pour le premier article, soit la cellule C6,

Ainsi, nous désignons explicitement le point de départ de la plage à construire pour la somme variable du premier article. Par le jeu de la réplication de la formule, nous en déduirons les points de départ pour les autres articles.

• Taper quatre points-virgules consécutifs, soit : ;;;;,

Cette notation est tolérée. Plutôt que de fixer respectivement chacun de ces paramètres à zéro, nous les ignorons. La fonction Excel Decaler s'en accommode. En effet, nous ne souhaitons opérer aucun décalage, ni en ligne, ni en colonne. De plus, nous ne souhaitons pas agir sur la hauteur de la plage. Pour chaque article, la somme doit être réalisée sur une seule et même ligne. Nous sommes donc désormais positionnés dans l'argument de la largeur variable à définir.

• Inscrire la fonction donnant la position d'une valeur cherchée, suivie d'une parenthèse : Equiv(,
• Désigner le mois choisi en cliquant sur sa cellule R3,
• Puis, enfoncer la touche F4 du clavier pour la figer, ce qui donne : $R$3,

En effet, le calcul est destiné à être répliqué sur les lignes du dessous pour les autres articles. Mais pour chacun d'entre eux, la largeur de la plage peut se calculer grâce à cette donnée immuablement fixée dans sa case. Elle ne doit donc pas suivre le déplacement de la formule.

• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Sélectionner tous les mois du tableau de données, soit la plage de cellules C5:N5,
• Enfoncer la touche F4 du clavier pour figer cette plage, soit : $C$5:$N$5,

La raison est similaire à la précédente. Malgré la réplication sur les lignes du dessous, la recherche du mois doit toujours se faire sur cette ligne.

• Taper un point-virgule suivi du chiffre zéro, soit : ;0, pour réaliser une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Fermer la parenthèse de la fonction Decaler,
• Puis, fermer la parenthèse de la fonction Somme,
• Enfin, valider le calcul par le raccourci clavier CTRL + Entrée,

De cette manière, nous conservons active la cellule du résultat.

• Cliquer et glisser sa poignée jusqu'en ligne 15 pour calculer toutes les sommes variables,

Si vous sélectionnez les chiffres d'un article sur la période désignée, vous pouvez rapidement constater que toutes ces sommes variables sont parfaitement correctes. Pour cela, il suffit de consulter les indications de synthèse fournies par Excel pour la sélection dans la barre d'état. Et les sommes coïncident donc. La syntaxe de la formule que nous avons construite est la suivante :

=SOMME(DECALER(C6; ; ; ; EQUIV($R$3; $C$5:$N$5; 0)))

Pour toute opération, la fonction Decaler s'imbrique facilement afin d'ajuster la plage du calcul. Donc, pour la moyenne, le principe est identique. Il suffit simplement de remplacer la fonction Somme.

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

=MOYENNE(DECALER(C6; ; ; ; EQUIV($R$3; $C$5:$N$5; 0)))

• La valider à l'aide du raccourci clavier CTRL + Entrée,
• Puis, double cliquer sur la poignée du résultat pour la répliquer sur la hauteur du tableau,

Surligner la période du calcul
Pour une solution plus pertinente, nous suggérons de surligner automatiquement les plages variables utilisées pour les calculs des sommes. Pour cela, nous devons faire la correspondance entre la colonne du mois dans le tableau de données et celui choisi par le biais de la liste déroulante. Ce dernier doit de nouveau être cherché. Donc, dans la syntaxe de la règle de mise en forme conditionnelle, nous devons de nouveau exploiter la fonction Equiv.

• Sélectionner tous les chiffres du tableau, soit la plage de cellules C6:N15,
• 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, construire la syntaxe suivante :

=COLONNE(C6)-2<=EQUIV($R$3; $C$5:$N$5; 0)

Une mise en forme conditionnelle raisonne de façon chronologique. C'est la raison pour laquelle nous débutons l'analyse à partir du premier chiffre en cellule C6. Tant que l'indice de colonne est inférieur à la position trouvée pour le mois désigné, nous devons appliquer des attributs de format explicitement différents. Notez néanmoins que nous retranchons deux unités à la position en colonne dans le critère. En effet, la fonction Equiv débute son analyse à partir de la colonne C et va retourner une position relative. La fonction Colonne raisonne quant à elle de façon absolue. Pour corriger l'écart des deux cellules situées en amont, nous retranchons fort logiquement deux unités.

• 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 vert pâle pour le fond des cellules,
• Activer alors l'onglet Police de cette même boîte de dialogue,
• Avec la liste déroulante, choisir un vert foncé pour la couleur du texte,
• Puis, valider ces attributs de format en cliquant sur le bouton Ok,

Nous sommes de retour sur la première boîte de dialogue. Elle rappelle les attributs de format à appliquer dynamiquement lorsque la règle de mise enforme conditionnelle est vérifiée.

• Cliquer sur le bouton Ok pour valider la création de cette règle,

De retour sur la feuille, toutes les plages variables utilisées pour les calculs sont parfaitement surlignées. Et bien entendu, si vous modifiez le mois dans la liste déroulante en cellule R3, la mise en forme dynamique s'ajuste aussitôt. Il s'agit d'un bon moyen de renforcer visuellement les données consolidées par les résultats des calculs.

Pour parfaire la solution, il paraît judicieux de faire ressortir les valeurs seuilles dans le tableau de synthèse. Pour une même période, la plus grande somme conduit nécessairement à la meilleure moyenne. De la même façon, la plus petite somme conduit obligatoirement à la pire moyenne. Nous pouvons donc réaliser un tir groupé. Les fonctions Max et Min sont préconisées dans la construction de ces deux règles. Avant de les bâtir, la plage de cellules Q6:R15 doit être présélectionnée. Les deux règles sont les suivantes :

=$Q6=MAX($Q$6:$Q$15)
=$Q6=MIN($Q$6:$Q$15)

A la première, il convient d'associer un fond bleu pâle avec un texte bleu foncé. A la seconde, le fond orange pâle et le texte orange foncé sont préconisés. Dans les deux cas, la première somme est figée en colonne pour que la mise en forme concerne les deux cellules de la même ligne. En revanche, la cellule est libérée en ligne pour que toutes les sommes soient étudiées. Elles sont tantôt comparées au résultat le plus grand sur cette plage figée (Max) et au résultat le plus petit.



Il en résulte des couleurs qui se déplacent au gré de la sélection effectuée pour la période. Ces indicateurs visuels permettent de mettre la lumière sur les articles les plus prolifiques comme les moins intéressants.