Synthèses dynamiques Excel sur des matrices extensibles

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


Sujets que vous pourriez aussi aimer :


Calculs matriciels sur des champs dynamiques

Avec les calculs matriciels, il est naturellement possible d'obtenir des résultats de synthèse sur des plages de données dynamiques. En d'autres termes, une fois les formules matricielles établies, même si des enregistrements venaient à être ajoutés à la suite de la base de données, ils seraient intégrés et consolidés par les calculs. C'est ce que nous proposons de démontrer ici.



Sur l'exemple illustré par la capture ci-dessus, des clients sont référencés dans une base de données. Le tableau de bord sur sa droite, dresse deux statistiques selon la civilité choisie. A chaque nouveau client ajouté à la suite du tableau, ces données de synthèse s'actualisent automatiquement.

Source et présentation du concept
Ces travaux doivent porter sur des données que nous proposons de réceptionner.

• Télécharger le classeur calculs-matriciels-dynamiques.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous découvrons aussitôt la base de données des clients accompagnée de son tableau de bord sur sa droite.



A ce titre, vous notez la présence d'une liste déroulante en cellule H6. C'est en fonction de la civilité ainsi définie que les résultats de synthèse doivent être délivrés en cellules respectives H9 et H10. Nous connaîtrons ainsi la proportion des femmes par rapport aux hommes et le volume des salaires ajoutés par sexe.

Mais comme nous le disions, ces résultats doivent évoluer automatiquement. En effet, de nouveaux clients sont amenés à être ajoutés régulièrement à la suite de la base de données. Les formules doivent donc être construites sur des matrices extensibles.

Matrices extensibles
L'idée consiste donc premièrement à représenter la matrice du critère à dénombrer, celle de la civilité, et la matrice des données à sommer, celle des salaires, par des noms. Ensuite, il s'agira de faire évoluer en hauteur ces deux matrices au gré des nouveaux clients ajoutés à la suite, dans la base de données. Pour cela, il suffit de retravailler ces noms grâce à la fonction Excel Decaler.

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

Son quatrième argument qui est facultatif est celui qui nous intéresse. Il permet de spécifier la hauteur de la plage. Nous lui passerons la fonction NbVal sur la colonne du champ. Elle comptabilise toutes les cellules non vides. De fait, à chaque nouveau client, elle augmente la hauteur de la plage d'une unité.

• Sélectionner le point de départ des civilités, soit la cellule B5,
• Dans la zone Nom en haut à gauche de la feuille, taper l'intitulé : Civ,
• Puis, le valider nécessairement par la touche Entrée du clavier,

Pour l'instant, ce nom ne fait référence qu'au point de départ de la plage de cellules que nous devons rendre extensible en hauteur. C'est pourquoi, dans un deuxième temps, nous le retravaillerons par formule, à l'aide du gestionnaire de noms. Mais avant cela, nous devons aussi préparer la plage de cellules des salaires.

• Sélectionner le point de départ des salaires, soit la cellule E5,
• Dans la zone Nom, taper l'intitulé : Sal,
• Puis, valider nécessairement ce nom par la touche Entrée du clavier,
• Dans l'enchaînement, en haut de la fenêtre Excel, cliquer sur l'onglet Formules,
• Dans la section Noms définis du ruban, cliquer sur le bouton Gestionnaire de noms,

Nous affichons ainsi la boîte de dialogue du gestionnaire de noms. Nous y retrouvons les deux intitulés que nous avons attribués aux points de départ des plages de cellules.

• Cliquer sur le nom Civ pour sélectionner sa plage,
• Dans la zone Fait référence à, adapter sa syntaxe comme suit :

=DECALER(Champs_dynamiques!$B$5; 0; 0; NBVAL($B:$B)-2)

Attention, les dollars sont impératifs pour figer la plage définie par la colonne. Il est donc conseillé de cliquer sur l'étiquette de cette dernière puis de l'ajuster si nécessaire, à cause des fusions de cellules.



Nous passons donc le point de départ précédemment défini en premier argument de la fonction Decaler. Les deux suivants sont neutralisés à zéro (0) pour n'opérer aucun décalage, ni en ligne, ni en colonne. Dans l'argument facultatif de la hauteur, nous comptons toutes les cellules non vides de la colonne B, grâce à la fonction NbVal. A ce résultat, nous retranchons deux unités pour exclure les deux titres présents dans cette colonne.

• Valider la syntaxe en cliquant sur le bouton à la coche verte,
• Sélectionner maintenant le second intitulé : Sal,
• Adapter sa syntaxe comme suit :

=Decaler(Champs_dynamiques!$E$5; 0; 0; NbVal($E:$E)-1)

Nous ne retranchons qu'une seule unité cette fois. Un seul titre est en effet présent dans cette colonne.

• Puis, la valider à l'aide du bouton à la coche verte,
• Cliquer ensuite sur le bouton Fermer de la boîte de dialogue pour revenir sur la feuille Excel,

Pour constater que ces plages sont devenues extensibles, nous avons besoin de les engager dans des calculs.

Synthèses matricielles et plages dynamiques
Le premier résultat attendu consiste à connaître le nombre de personnes du genre défini en H6. Ce calcul doit intervenir en cellule H9. Il consiste à scruter la matrice des civilités à la recherche de correspondances. Pour une donnée aussi triviale, nous pourrions exploiter la fonction Nb.Si de dénombrement. Mais nous poursuivons l'apprentissage et l'assimilation des techniques matricielles. Donc, nous allons utiliser la fonction SommeProd qui a la capacité d'analyser toutes les lignes respectives des matrices qui lui sont passées en arguments.

• Sélectionner la cellule H9 puis taper le symbole égal (=) pour initier la formule matricielle,
• Saisir la fonction matricielle suivie d'une parenthèse, soit : SommeProd(,
• Ouvrir une nouvelle parenthèse pour accueillir la matrice des civilités,
• La désigner en tapant son nom dynamique, soit : Civ,
• Taper le symbole (=) pour engager la condition à respecter sur cette colonne,
• Sélectionner la civilité définie en cliquant sur sa cellule H6,
• Fermer la parenthèse de cette matrice conditionnelle,

A ce stade, toutes les données correspondantes sont repérées par des valeurs booléennes : Vrai et Faux le cas échéant. Nous avons déjà constaté ce fonctionnement à l'aide de l'assistant fonction. Pour obtenir le dénombrement, nous devons convertir ces données booléennes en valeurs numériques : 1 pour Vrai et 0 pour Faux. Ainsi, elles seront ensuite naturellement additionnées par la fonction SommeProd. De fait, nous obtiendrons bien le dénombrement statistique escompté.

• Pour cela, taper le symbole de l'étoile suivi du chiffre 1, soit : *1,

C'est ainsi que nous forçons la conversion des booléens en chiffres.

• Fermer la parenthèse de la fonction SommeProd,
• Puis, valider la formule matricielle par la touche Entrée du clavier,

Pour la civilité Madame, notre calcul compte 5 personnes. Pour l'instant, dans cette petite base de données, les hommes et les femmes sont aussi nombreux. Nous le testerons après avoir résolu le second calcul.



La syntaxe complète de la petite formule matricielle que nous avons construite est la suivante :

=SOMMEPROD((Civ=H6)*1)

Sur cette base, le calcul suivant est trivial. Dans la matrice précédente, chaque enregistrement concordant avec la civilité choisie est marqué d'un chiffre 1 et du chiffre 0 dans le cas contraire. Avant de produire la somme, nous devons multiplier ces résultats par les salaires respectifs. Lorsqu'ils ne correspondront pas, ils seront multipliés par 0, donc éliminés. Les autres seront calculés et additionnés par la suite, puisqu'intégrés dans la fonction SommeProd.

• En cellule H10, adapter le calcul matriciel précédent comme suit :

=SOMMEPROD((Civ=H6)*(Sal))

La multiplication par 1 n'est plus nécessaire. Elle servait à forcer la conversion en valeurs numériques. Maintenant que nous multiplions les données booléennes par les salaires respectifs, la conversion est imposée naturellement et le calcul s'opère.

Nous obtenons ainsi la somme des salaires pour les cinq femmes répertoriées dynamiquement.

• En cellule H6, remplacer la civilité Madame par la civilité Monsieur,

Nous l'avions dit, les hommes sont pour l'instant au même nombre que les femmes. En revanche, la somme de leurs salaires conduit à un résultat légèrement inférieur à celui des femmes.

Nous avons donc bâti des calculs matriciels de synthèse sur des plages dynamiques. De fait, ces résultats sont susceptibles de s'actualiser au gré des nouvelles données ajoutées. Donc, nous proposons de constater que ces matrices sont extensibles.

• En bas de le fenêtre Excel, cliquer sur l'onglet Extension pour activer sa feuille,
• Sélectionner la première donnée du petit tableau, soit la cellule B2,
• Réaliser le raccourci clavier CTRL + A pour étendre la sélection à tout le tableau,
• Copier la sélection par le raccourci clavier CTRL + C,
• En bas de la fenêtre Excel, cliquer sur l'onglet Champs_dynamiques pour revenir sur sa feuille,
• Sélectionner la première cellule vide à la suite du tableau, soit B15,
• Coller la sélection précédemment copiée par le raccourci clavier CTRL + V,

Aussitôt, les statistiques matricielles s'actualisent en intégrant automatiquement les nouveaux enregistrements ajoutés. Les hommes sont désormais plus nombreux. Et forcément, la somme de leurs salaires conduit à un résultat plus important.

Repérer les données concordantes
Pour parachever l'application, nous proposons de bâtir une règle de mise en forme conditionnelle sur ce tableau. Elle viendra renforcer l'impact des résultats de synthèse calculés. Mais elle doit elle aussi s'adapter aux données potentiellement ajoutées. Elle doit donc s'appliquer sur un tableau extensible en hauteur. Pour cela, il suffit de désigner l'intégralité des colonnes sur lesquelles il s'étend.

• Sélectionner les colonnes B à E par les étiquettes, soit la plage de cellules B:E,
• En haut de la fenêtre Excel, cliquer sur l'onglet Accueil pour revenir sur son ruban,
• Dans la section Styles du ruban, 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 ...,
• Puis, cliquer dans la zone de saisie qui se propose juste en dessous,
• Taper alors la syntaxe suivante : =ET($H$6<>''; $B1=$H$6),

Deux conditions à recouper sont donc nécessaires pour déclencher les effets de cette règle de mise en forme dynamique. La cellule de la civilité choisie ne doit pas être vide bien entendu. Dans le même temps, la civilité en cours d'analyse dans la base de données doit être identique à celle stipulée par l'utilisateur. Si ces deux conditions répondent favorablement, nous devons faire réagir les cellules concernées, par des attributs de format adaptés. Comme vous l'avez constaté, du fait de la chronologie à respecter et de la sélection intégrale, nous débutons l'analyse à partir de la cellule B1.

• 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 gris très clair,
• Activer alors l'onglet Police de la boîte de dialogue,
• Avec la liste déroulante, choisir un bleu foncé pour la couleur du texte,
• Valider ces attributs de format par le bouton Ok,

De fait, nous sommes de retour sur la première boîte de dialogue. Elle indique l'apparence que prendront dynamiquement les lignes de la base de données, dès lors que les conditions sont réunies.

• Valider la création de cette règle de format dynamique par le bouton Ok,

Si vous ajoutez de nouveaux enregistrements à la suite de la base de données, en même temps qu'ils sont intégrés dans les calculs matriciels, ils sont gérés et mis en valeur par la règle de mise en forme conditionnelle. Il existe néanmoins un décalage de réglage, notamment au niveau des alignements. Pour corriger le défaut, il suffit de régler ces attributs statiques en amont sur l'intégralité des colonnes.