Contact: 514-605-7112 / info@lecfomasque.com

Excel: Accueillez les nouvelles fonctions matricielles dynamiques

Publié le : 16 janvier 2020

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

TRI Tri une plage de données ou un tableau.
TRI.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.

 

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.

 


NOTRE OFFRE DE FORMATIONS


Microsoft Most Valuable Professional

Le CFO masqué vous offre un vaste choix de formations Excel et Power BI, réparties dans 8 catégories: Excel – Tableaux de bord, Excel – Modélisation financière, Excel – VBA, Excel – Power Tools, Excel – Ninja, Power BI, Finance corporative et Compléments avancés. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et ils sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.

 

Découvrez quelles formations vous conviennent

Pour info: 514-605-7112 ou info@lecfomasque.com

 

Sophie Marchand

Sophie Marchand, M.Sc., CPA, CGA, MVP, est détentrice d’une M.Sc. en finance corporative et d’un titre comptable CPA, CGA. Elle est également MVP Office Apps and Services (Excel) et MVP Data Platform (Power BI). Le titre de MVP est un titre honorifique remis par Microsoft et signifie « Most Valuable Professional ». Il est attribué à des experts de solutions Microsoft qui se distinguent non seulement par leurs compétences mais également par leur implication dans la communauté. Seuls 4 professionnels possèdent cette double-qualification dans le monde. Mme Marchand cumule de nombreuses années d’expérience dans le milieu des affaires et se spécialise en modélisation financière et en intelligence d’affaires avec Excel et Power BI. Après avoir longtemps offert ses services comme consultante, elle se consacre désormais à la formation, sous toutes ses formes (formations en classe, formations en ligne, formations en entreprise, webinaires, animation de groupe d'usagers, rédaction d'articles de blogue, animation de forum en ligne et plus encore). Vous pouvez d'ailleurs la retrouver à la tête du groupe d'usagers Montreal Modern Excel and Power BI.

Laisser un commentaire