Positionner dynamiquement un calcul de synthèse Excel

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


Sujets que vous pourriez aussi aimer :


Positionnement dynamique d'un calcul statistique

Pour renforcer la pertinence des résultats d'une feuille de calculs de synthèse, nous souhaitons offrir à l'administrateur, des données statistiques calculées qui se placent automatiquement à la suite des plages dynamiquement construites. Dans la formation précédente, nous avions bâti une application Excel facilitant la gestion de la logistique.



Au choix d'une référence par l'utilisateur dans une liste déroulante, nous avions réussi, par formules Excel, à extraire tous les enregistrements correspondant à ces numéros de lots répétitifs. De fait, les lignes de détail importées varient en fonction de la référence choisie. Et comme l'illustre la capture ci-dessus, nous souhaitons positionner un résultat de synthèse, dans le flux, soit directement à la suite de ces informations de base de données. Mais l'emplacement du calcul ne peut être défini à l'avance en raison de ces plages de données variables.

Source et présentation du concept
Comme des formules d'extraction sont déjà en place, l'astuce consiste à basculer la nature du calcul dans une même formule, selon un indicateur à mettre en place. Cet indicateur consiste à calculer la hauteur de la plage de données importée. Cette hauteur permettra de positionner le résultat de synthèse à la suite des valeurs extraites, soit dans la parfaite continuité de la lecture suggérée. Nous proposons donc de débuter depuis le résultat obtenu par l'application précédente.

• Télécharger le classeur position-dynamique-calcul.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer la modification du bandeau de sécurité,

Ce classeur est constitué de trois feuilles. La feuille Console est l'outil dédié au responsable. C'est en B5, par le biais d'une liste déroulante qu'il choisit un numéro de lot à étudier. Cette liste déroulante est dynamique dans la mesure où elle a été construite sur une source de données variables, purgée de ses doublons. Les données sont alors importées dynamiquement, dans les petits tableaux prévus à cet effet, entre les colonnes B et C et entre les colonnes E et K. Le nombre de lignes importées varie en fonction de la référence choisie.

• A l'aide de la liste déroulante en cellule B5, choisir la référence F91518,

Instantanément, toutes les données qui lui sont attachées, sont importées dans les deux tableaux. Et comme vous le remarquez, les nombres d'enregistrements diffèrent avec la référence précédente. En conséquence, la hauteur de chacun des deux tableaux a varié.

Ces importations sont réalisées sur les bases de données situées dans les feuilles Stock et OF. Dans les deux cas, des calculs intermédiaires avaient été réalisés afin de marquer toutes les répétitions de la référence choisie, à l'aide d'un numéro incrémenté.

• Cliquer sur l'onglet Stock en bas de la fenêtre Excel pour activer sa feuille,
• Cliquer sur les étiquettes de colonne F et I en haut de la feuille pour les désigner,
• Réaliser un clic droit sur la sélection et choisir Afficher dans le menu contextuel,

Deux nouvelles colonnes, H et I apparaissent. Celle qui nous intéresse ici est la colonne H.



Comme l'illustre la capture ci-dessus, le calcul consiste à repérer la référence choisie (C3=Console!$B$5), par un numéro auto-incrémenté (MAX($H$2:H2)+1). C'est le plus grand de ces nombres résultants sur cette plage, qui nous permettra de déterminer la hauteur des données importées, dans le premier petit tableau de la feuille Console. Le même calcul est répliqué pour le tableau de la feuille OF, mais en colonne X cette fois. La fonction Max sur cette dernière, permettra donc de connaître la hauteur des données importées, dans le second tableau de la feuille Console.

Détecter les bornes d'une plage par Calcul
Nous proposons de débuter par l'inscription dynamique des titres, en colonnes A et D de la feuille Console, annonçant le calcul statistique dynamique. Bien sûr, cette étiquette doit être correctement positionnée. Son insertion doit intervenir juste après la dernière ligne des données importées, pour chacun des deux tableaux.

• Cliquer sur l'onglet Console en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner la cellule A10 et taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction conditionnelle suivi d'une parenthèse, soit Si(,
• Taper la fonction pour déterminer le plus grand d'une plage, suivi d'une parenthèse, soit Max(,
• Cliquer sur l'onglet Stock en bas de la fenêtre Excel pour activer sa feuille,
• Désigner la colonne H par son étiquette, soit Stock!H:H,
• Fermer la parenthèse de la fonction Max,
• Taper le symbole = pour l'égalité à vérifier dans la zone de critère de la fonction Si,
• Taper la fonction déterminant la ligne d'une cellule suivi d'une parenthèse, soit Ligne(,
• Cliquer sur l'onglet Console en bas de la fenêtre Excel pour revenir sur sa feuille,
• Sélectionner alors la première cellule de l'extraction soit B10,
• Fermer la parenthèse de la fonction Ligne et taper le symbole moins (-) pour la soustraction,
• Saisir de nouveau la fonction pour la ligne d'une cellule suivi d'une parenthèse, soit Ligne(,
• Sélectionner de nouveau la cellule B10 et enfoncer la touche F4 pour la figer,
• Fermer la parenthèse de la fonction Ligne,
• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Saisir l'étiquette de titre entre guillemets, soit 'Sous Total',
• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Taper deux guillemets ('') pour garder la cellule vide lorsque la fin de la plage n'est pas atteinte,
• Fermer la parenthèse de la fonction Si et valider le calcul par le raccourci clavier CTRL + Entrée,
• Double cliquer sur la poignée de la cellule pour répercuter la formule sur les lignes du dessous,

Comme vous le constatez, la borne inférieure de la plage de données issue de l'extraction, semble parfaitement détectée. L'étiquette de titre vient s'inscrire précisément sur la ligne directement située après la borne inférieure de la plage. Elle annonce donc le calcul de la somme à venir. Pour vérifier le bon dynamisme de cette détection, il suffit de réaliser quelques essais.

• Dans la liste déroulante en B5, sélectionner la référence G03472,

Instantanément, l'étiquette qui était positionnée sur la ligne 11 se trouve rejeté à la ligne 19. L'extraction dans ce cas se poursuit en effet jusqu'en ligne 18. Le processus est donc parfaitement fonctionnel pour cette première étape.



La formule que nous avons construite est la suivante :

=SI(MAX(Stock!H:H)=LIGNE(B10) - LIGNE($B$10); 'SousTotal'; '')

Comme nous l'avons évoqué lors de la présentation, la hauteur de la plage de données extraites est retournée par la fonction Max, appliquée sur la colonne H de la feuille Stock. Nous comparons ce résultat avec le nombre de cellules parcourues par la formule répliquée. Pour ce faire, nous réalisons la différence entre l'indice de ligne en cours (LIGNE(B10)) et celui du départ de la plage (LIGNE($B$10)). C'est la raison pour laquelle, dans ce deuxième cas, la référence de B10 est figée. Si l'égalité est vérifiée, nous en déduisons que nous avons atteint la fin de la plage extraite. En conséquence, nous inscrivons en lieu et place le Titre qui appelle le calcul de synthèse à venir. Dans tous les autres cas bien sûr, nous conservons la cellule vide ('').

Le même principe doit être répliqué en colonne D afin de positionner dynamiquement l'étiquette de titre, pour le deuxième tableau. Bien entendu, le calcul doit être adapté aux références de cellules et aux champs de la base de données, située sur la feuille OF.

• En cellule D5 de la feuille Console, saisir la formule suivante :

=SI(MAX(OF!X:X)=LIGNE(E5) - LIGNE($E$5); 'SousTotal'; '')

• Après avoir validé le calcul, double cliquer sur la poignée de la cellule pour le répliquer,

Mission accomplie cette fois aussi puisque le titre vient dynamiquement se placer sur la ligne située juste après la dernière rangée de la plage extraite. Dans les deux cas, vous l'avez remarqué, une mise en forme conditionnelle prédéfinie met en valeur la cellule de titre et son futur résultat associé.

Positionner automatiquement un calcul dynamique
Nous avons préparé le terrain grâce à ces étiquettes qui indiquent les positions à partir desquelles les résultats de synthèse, doivent être placés. Le calcul, dans les deux cas, consiste à réaliser la somme des quantités, définies en Kilos. Il s'agit bien d'une opération dynamique puisque le nombre des cellules à additionner n'est pas connu à l'avance. En même temps que le résultat doit se positionner dynamiquement, la plage de calcul doit elle aussi être définie de façon dynamique, pour s'adapter à toutes les extractions, selon la demande utilisateur.

Comme nous l'avions vu dans la formation sur l'aide à la gestion de la logistique, nous allons exploiter la fonction Excel Decaler, selon la syntaxe suivante :

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

En désignant le point de départ de la plage et la hauteur dynamique retournée par la fonction Max, la fonction Decaler permettra de fournir à la fonction Somme, les bornes précises sur lesquelles l'addition doit se faire. Les deux arguments intermédiaires (Decalage_ligne et Decalage_colonne) sont à ignorer dans notre cas en fixant leur valeur à Zéro.

• Sélectionner la cellule B10 pour adapter son calcul,
• A la place des deux guillemets dans le second argument de la fonction SiErreur, taper le nom de la fonction conditionnelle suivi d'une parenthèse, soit Si(,
• Cliquer sur la cellule A10 pour la désigner dans la formule,
• Taper le symbole = pour l'égalité à vérifier dans le critère,
• Saisir le nom de l'étiquette de titre entre guillemets, soit 'Sous Total',
• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,

Si cette condition est satisfaite, cela signifie que le calcul a atteint la limite de la plage, référencée par l'étiquette de titre, positionnée dynamiquement par le calcul précédent. En conséquence, la somme des valeurs précédentes doit être enclenchée, en redéfinissant les bornes de la plage par la fonction Decaler.

• Taper la fonction pour réaliser l'addition suivi d'une parenthèse, soit Somme(,
• Saisir alors la fonction pour définir dynamiquement les bornes de la plage de cellules, suivi d'une parenthèse, soit Decaler(,
• Taper la référence en cours comme point de départ de la plage, soit B10,
• Enfoncer la touche F4 du clavier pour la figer dans le calcul, ce qui donne : $B$10,
• Taper un point-virgule suivi du chiffre zéro pour ne pas faire de décalage en ligne, soit ;0,
• Taper encore un point-virgule suivi du chiffre zéro pour ne pas décaler en colonne, soit ;0,
• Taper un nouveau point-virgule pour accéder à l'argument de la hauteur dynamique à définir,
• Saisir le nom de la fonction retournant la plus grande valeur, suivi d'une parenthèse, soit Max(,
• Cliquer sur l'onglet Stock en bas de la fenêtre Excel pour désigner sa feuille,
• Cliquer sur l'étiquette H en haut de la feuille pour désigner la colonne intégrale,
• Fermer la parenthèse de la fonction Max,
• Fermer la parenthèse de la fonction Decaler,
• Fermer la parenthèse de la fonction Somme,
• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Saisir deux guillemets ('') pour garder la cellule vide lorsque le critère n'est pas vrai,
• Après la parenthèse de la fonction Si, fermer la parenthèse pour la fonction SiErreur qui la contient,
• Valider la formule par le raccourci CTRL + Entrée,
• Double cliquer sur la poignée de la cellule pour reproduire le calcul sur les cellules du dessous,

La formule que nous avons adaptée est la suivante :

=SIERREUR(INDEX(Stock!B:F; EQUIV(LIGNE(A1); Stock!H:H; 0); 4); SI(A10='Sous Total'; SOMME( DECALER( $B$10; 0; 0; MAX(Stock!H:H))); ''))

La partie mise en gras représente le tronçon que nous venons d'ajouter en lieu et place des guillemets de la formule d'extraction d'origine.



Comme vous le remarquez et comme l'illustre la capture ci-dessus, le résultat de la somme se déclenche parfaitement et précisément après les données résultant de l'extraction. L'enjeu intéressant a consisté à réaliser deux calculs diamétralement opposés dans une même formule. Tant que des données à extraire existent, les fonctions importent les enregistrements correspondant à la répétition de la référence choisie. Lorsqu'ils ont tous été extraits, la calcul bascule sur l'addition qui a la faculté de considérer les cellules du dessus, en fonction de leur nombre variable. Nous devons désormais répliquer la même adaptation sur la formule du second tableau d'extraction des données.

• En cellule E5 de la feuille Console, adapter la formule d'extraction comme suit :

=SIERREUR(INDEX(OF!A:W; EQUIV(LIGNE(A1); OF!X:X; 0); 6); SI(D5 = 'Sous Total'; SOMME(DECALER($E$5; 0;0; MAX(OF!X:X)));''))

• Valider le calcul et le répliquer par double clic sur la poignée de la cellule,

Même constat que précédemment, le résultat statistique vient se greffer juste en dessous des cellules de l'extraction.

• A l'aide de la liste déroulante en B5, sélectionner de nouveau la référence D95831,

L'extraction produit moins de lignes. Les calculs de synthèse remontent automatiquement pour se greffer sous les zones respectives d'importation. De plus, les sommes s'adaptent en ajustant les cellules à additionner en fonction de leur nombre revu à la baisse. Le résultat est très satisfaisant puisque nous avons réussi à bâtir un calcul synthétique complètement dynamique et qui, cerise sur le gâteau, se positionne seul en détectant automatiquement les bornes des plages, pour offrir une vue claire et exploitable.

Indicateurs d'alertes dynamiques
Pour renforcer la pertinence de l'application et simplifier encore la gestion de la logistique, nous proposons d'exploiter les résultats dynamiques précédents, pour alerter visuellement le responsable lorsqu'un problème est détecté. La somme des quantités du second tableau ne peut jamais dépasser la somme des quantités du premier. Si un tel cas se présentait, l'administrateur devrait en être averti sans équivoque. Et pour cela, nous proposons de faire ressortir les deux cellules fusionnées de titre (B2 et E2), sur fond de couleur rouge. Cette symbolique négative permettra d'alerter instantanément le décisionnaire pour remonter à la source du problème et agir.

Pour envisager une telle mise en valeur dynamique sur les deux cellules fusionnées, la mise en forme conditionnelle doit donc comparer les résultats des deux sommes. Mais ces résultats ne sont jamais placés aux mêmes endroits. Nous proposons donc d'extraire ces sommes dynamiques dans des cellules situées sur la droite de la feuille. Nous les masquerons à l'issue pour conserver une console ergonomique. Mais elles pourront être utilisées pour bâtir le critère de la mise en forme conditionnelle. Une rechercheV fera l'affaire. Elle consiste à rechercher le titre (Sous Total) de l'étiquette dans une colonne et à restituer la somme correspondante lorsqu'elle est trouvée. La syntaxe de la fonction RechercheV est la suivante  :

=RechercheV(valeur_cherchée ; Tableau_de_recherche ; num_colonne_retour ; Faux)

• Sélectionner la cellule O4 et taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction de recherche suivi d'une parenthèse, soit recherchev(,
• Entre guillemets, saisir le texte à rechercher, soit 'Sous Total',
• Puis, taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Désigner les deux premières colonnes comme tableau de recherche, soit A:B,
• Taper un point-virgule pour passer dans l'argument du numéro de colonne à retourner,
• Saisir le chiffre 2 pour désigner la deuxième colonne de la sélection,
• Taper un nouveau point-virgule pour passer au dernier argument de la fonction RechercheV,
• Saisir le texte Faux pour indiquer une recherche selon une correspondance exacte,
• Fermer la parenthèse de la fonction RechercheV et valider le calcul,

Comme vous le constatez, le résultat de la somme dynamique est parfaitement extrait. Il est désormais placé dans une cellule fixe. Nous devons réaliser le même processus pour extraire l'addition dynamique du deuxième tableau. Une fois fixé lui aussi dans une cellule connue, le critère de la mise en forme conditionnelle pourra être bâti simplement. La formule que nous avons construite est la suivante :

=RECHERCHEV('SousTotal'; A:B; 2; FAUX)

• En cellule P4 cette fois, taper la formule suivante :

=RECHERCHEV('SousTotal'; D:E; 2; FAUX)

• Avec la liste déroulante en B5, sélectionner la référence G04771,

Les données extraites varient, les sommes dynamiques s'ajustent et se positionnent. Les extractions de ces dernières quant à elles, se réalisent parfaitement en cellules O4 et P4. Il ne reste plus qu'à les comparer pour déclencher l'alerte temps réel. Le résultat en P4 ne doit jamais être supérieur au résultat en O4.

• Cliquer sur la cellule fusionnée B2 pour la sélectionner,
• Avec la touche CTRL enfoncée, ajouter la cellule fusionnée E2 à la sélection,
• Cliquer alors sur le bouton Mise en forme conditionnelle dans le ruban Accueil,
• Dans la liste, choisir Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le dernier type : Utiliser une formule pour...,
• Puis, cliquer dans la zone de saisie du critère, située juste en dessous,
• Taper le symbole = pour débuter le calcul de comparaison,
• Cliquer sur la cellule P4 de la seconde somme extraite pour la désigner,
• Taper le symbole supérieur (>) pour la comparaison,
• Puis, cliquer sur la cellule O4 de la première somme extraite,
• Cliquer alors sur le bouton Format pour régler la mise en valeur associée à ce critère,
• Dans l'onglet Remplissage de la boîte de dialogue qui suit, choisir un rouge foncé,
• Dans l'onglet Police, définir un texte gras de couleur gris clair,
• Valider ces réglages par Ok pour retourner sur la première boîte de dialogue,

L'aperçu illustre parfaitement le format susceptible de se déclencher automatiquement, lorsque la seconde somme dépasse la première.

• Pour le vérifier, valider de nouveau par Ok cette première boîte de dialogue,
• Sélectionner les colonnes O et P par leurs étiquettes,
• Cliquer avec le bouton droit de la souris sur la sélection,
• Dans le menu contextuel, choisir Masquer,

Comme vous l'avez remarqué, rien n'a visuellement changé pour l'instant puisque les données importées pour la référence choisie sont cohérentes.

• A l'aide de la liste déroulante en B5, choisir le numéro de lot F91518,

L'extraction des enregistrements correspondants se produit, faisant varier le nombre de lignes dans les deux tableaux d'importation. Les calculs dynamiques des sommes se réajustent et se repositionnement parfaitement en conséquence. Et cette fois, l'alerte est donnée. Les deux cellules de titre basculent dans une mise en valeur sur fond rouge qui ne passe pas inaperçue. En effet, l'incohérence des sommes des poids est détectée automatiquement. La seconde somme dynamique est largement supérieure à la première, ce qui n'est pas possible.



Nous avons donc développé une application efficace et complètement automatisée pour simplifier grandement la gestion de la logistique de l'entreprise. Les synthèses sautent aux yeux et les prises de décision coulent de source. Le gain de temps est lui aussi considérable. Désormais, grâce à l'imbrication des fonctionnalités Excel que nous avons apprises au travers des différentes formations, nous sommes en mesure de développer de véritables applications professionnelles d'entreprise.