Excel : Les nouvelles fonctions matricielles dynamiques

Publié le 16 janvier 2020
par Sophie Marchand M.Sc.
SOMME.SI.ENS MAINTENANT

Lorsque Microsoft lance de nouvelles fonctions ou de nouvelles fonctionnalités dans les versions Excel de O365, il y a toujours une période de déploiement. Donc, à moins d’utiliser la version “Insider, c’est-à-dire la version qui reçoit toutes les mises à jour au fur et à mesure qu’elles sont prêtes, vous devez attendre que les nouvelles fonctions et fonctionnalités soient disponibles dans votre région. Et bien, les fonctions matricielles dynamiques, dont Microsoft a annoncé l’arrivée, il y a plus d’un an, sont enfin arrivées dans ma région, il y a quelques semaines. Enfin! Cet article vise à vous introduire aux fonctions matricielles dynamiques. Notez qu’une série d’autres articles présenteront aussi individuellement chacune de ces fonctions.

 

Les anciennes fonctions matricielles d’Excel

Excel dispose depuis longtemps de fonctions matricielles et la plupart des usagers avaient une relation amour-haine avec ce type de fonctions. Pourquoi? Et bien, parce que ces fonctions sont très puissantes et permettent de faire des calculs qui sont par ailleurs très ardus avec l’usage de fonctions régulières. On peut penser par exemple à la fonction matricielle qui permet de retourner la liste des onglets d’un fichier.

Les deux limites principales de ces anciennes fonctions matricielles, c’est que l’usager devait sélectionner le bon nombre de cellules pour créer une plage de réception des résultats et que ces formules n’étaient pas dynamiques, i.e. qu’elles ne s’ajustaient pas lorsque les données sources évoluaient.

Voici ces deux limitations en exemple, à l’aide de la fonction TRANSPOSE.

La fonction TRANSPOSE permet de transposer une plage de données, de ligne à colonne ou de colonne à ligne. Par exemple, ci-dessous, j’ai des chiffres en colonne dans les cellules B4 à B8 et je souhaite les obtenir en ligne dans les cellules D4 à H4. Je dois donc, dans un premier temps, sélectionner les plages D4 à H4 et ensuite, aller dans la barre de formules insérer ma fonction TRANSPOSE.

Fonction TRANSPOSE

 

Mais ce n’est pas tout! Je sois aussi cliquer sur les touches CTRL+SHIFT+ENTER (CSE) au lieu de cliquer sur ENTER. Ceci ajoute des accolades autour de la fonction et indique qu’il s’agit d’une fonction matricielle traditionnelle. Les cellules D4 et H4 ne peuvent plus, par la suite, être modifiées. On peut supprimer le contenu de ces cellules en sélectionnant la plage au complet ou on peut modifier la source de données (par exemple, si on voulait référer à A4:A8 plutôt qu’à B4:B8) mais c’est tout.

Fonction TRANSPOSE

 

Les nouvelles fonctions matricielles dynamiques

Les nouvelles fonctions matricielles dynamiques sont beaucoup plus souples. D’abord, nul besoin d’utiliser les touches CSE pour créer la formule et nul besoin non plus de sélectionner une plage de cellules à dimensions définies. Je dois seulement inscrire ma formule dans la première cellule, donc dans l’exemple ci-dessous, dans la cellule D5. Excel affiche d’ailleurs un message pour indiquer que la formule sera propagée aux cellules voisines. N’est-ce pas magnifique!

Fonction TRANSPOSE

 

Si vous cliquez dans les cellules voisines, vous verrez effectivement, dans la barre de formules, que la formule s’est propagée. Elle est toutefois affichée en gris pâle puisque si vous avez besoin de modifier la formule, vous devez la modifier à partir de la première cellule de la plage de données, dans ce cas-ci, dans la cellule D5, qui elle, affiche la formule en foncé.

Fonction TRANSPOSE

 

Et pour moi, la magie opère vraiment lorsque vous mettez vos données d’origine sous forme de tableau, puisque dès lors, votre formule matricielle sera réellement dynamique et considèrera l’ajout de données dans la table d’origine, ce que ne font pas les fonctions matricielles traditionnelles.

Fonction TRANSPOSE

 

On voit bien qu’en ajoutant 6 dans ma table d’origine, ma fonction TRANSPOSE retourne également un 6. Si j’avais utilisée une fonction matricielle traditionnelle, ce 6 ne se serait pas affiché puisque je n’aurais pas eu de fonction TRANSPOSE dans la colonne I. Les nouvelles fonctions matricielles dynamiques, elles, s’ajustent et se propagent en conséquence.

Fonction TRANSPOSE

 

Quelles sont ces nouvelles fonctions matricielles dynamiques?

Vous avez donc compris, en lisant les paragraphes précédents, que les nouvelles fonctions matricielles dynamiques s’appliquent à l’ensemble des fonctions matricielles traditionnelles et les rendent plus performantes et plus faciles à utiliser. Mais ce n’est pas tout. Microsoft a également pensé à nous offrir de toutes nouvelles fonctions, pour des besoins courants. Je partagerai donc avec vous, au cours des prochaines semaines, des articles individuels sur chacune de ces fonctions.

 

NOUVELLES FONCTIONS MATRICIELLES DYNAMIQUES

TRIER Tri une plage de données ou un tableau.
TRIER.PAR Tri les données en fonction d’une ou de plusieurs colonnes.
FILTRE Filtre une plage de données.
UNIQUE Génère une liste de valeurs distinctes.
SEQUENCE Génère une séquence de nombres.
TABLEAU.ALEA Génère une liste de nombres aléatoires.
@ Génère la valeur d’une intersection implicite.
* À utiliser à l’intérieur d’un même fichier.
** Sinon, utiliser Power Query.

 

Améliorations des fonctions existantes

En développant les nouvelles fonctions matricielles dynamiques, Microsoft s’est trouvée à bonifier des fonctions existantes. Parmi elles, on retrouve des fonctions matricielles mais aussi des fonctions régulières.

 

Fonction FREQUENCE

Comme on l’a vu précédemment, les nouvelles fonctions matricielles dynamiques viennent améliorer considérablement l’expérience des fonctions matricielles traditionnelles. La fonction matricielle que j’avais négligée le plus, dans le passé, étant donné ses limitations, et que j’ai recommencé à utiliser, c’est la fonction FREQUENCE, qui calcule le nombre de fois qu’un item apparaît dans une plage.

Par exemple, ci-dessous, je l’utilise pour savoir combien d’employés ont reçu des notes de 0 à 24, puis de 25 à 49 et ainsi de suite. À noter dans ce cas que l’on n’a pas besoin de la colonne F. La fonction FREQUENCE considèrera toutes les valeurs inférieures à celles indiquées dans la colonne G. De même, j’ai seulement besoin d’insérer ma formule dans la cellule I17, pour qu’elle se propage d’elle-même dans les autres lignes.

Fonction FREQUENCE

 

Pour vérifier si j’obtiens bien 9 employés au total, je peux ensuite utiliser la formule suivante:

=SOMME(I17#)

 

En ajoutant le # à la fin de ma cellule de référence dans ma formule, j’indique à Excel de prendre la plage de données complète retournée par ma fonction matricielle dynamique de la cellule I17.

 

Habitué de travailler avec Excel et besoin de vous mettre à jour avec les nouvelles fonctionnalités qu’offre maintenant Excel 365 ? Pour concevoir des solutions en phase avec les développements technologiques actuels, jetez un œil à nos formations sur Excel 365.

 

Fonctions SOMME.SI et SOMME.SI.ENS

Traditionnellement, pour faire la somme des ventes du vendeur A ET la somme des ventes du vendeur B, je devais utiliser deux fonctions SOMME.SI et les additionner.

SOMME.SI AVANT

 

Maintenant, je peux plutôt utiliser la structure suivante:

SOMME.SI APRES

 

Même chose avec la fonction SOMME.SI.ENS. Avant, si je voulais faire la somme des ventes du vendeur A et du vendeur B pour la région sud, je devais faire l’addition suivante:

SOMME.SI.ENS AVANT

 

À présent, je peux simplement utiliser la formule suivante:

SOMME.SI.ENS MAINTENANT

 

Si vous étiez sous l’impression qu’Excel avait cessé d’évoluer, détrompez-vous!!! Excel continue encore et toujours de nous offrir des nouveautés emballantes et fort utiles.

 


Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

 

Formation complémentaire

Afin d’approfondir vos connaissances des fonctions avancées d’Excel nous vous suggérons la formation Excel O365 – Bienvenue dans l’ère moderne.

 

Voici quelques commentaires d’apprenants ayant suivi la formation – Excel O365 – Bienvenue dans l’ère moderne

Commentaire d'apprenant - Excel O365 – Bienvenue dans l’ère moderne

CFO-Masque_Formations-en-ligne_FBLa mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Retour en haut