En 2020 et 2022, Microsoft a boosté les possibilités de l'un de ses logiciels fétiches : Excel.
En pratique, ce sont déjà plus de 40 nouvelles fonctions qui ont été ajoutées au logiciel et un nouveau principe, celui de la propagation des résultats depuis une formule initiale.
Ces nouveautés qui rendent Excel beaucoup plus puissant et dynamique sont disponibles principalement dans la version 365 de Microsoft et dans l'application en ligne d'Excel.
Le but de cet article est de présenter l'essentiel de ces nouveautés, principalement de nouvelles fonctions, sans pour autant prétendre à l'exhaustivité.
Certaines nouvelles fonctions comme RECHERCHEX, FILTRE et LET seront abordées, ainsi que CHOISIRCOLS et le symbole"#", mais pour ce premier article et par souci de simplicité, nous ne nous étendrons pas sur chaque nouveauté.
1 ) Le principe de propagation
Tout d'abord, l'essentiel de ce qui différencie l'avant modern Excel de l'après modern Excel est un unique principe que l'on nomme : principe de propagation.
Jusqu'en 2020, la règle dans Excel était la suivante : une formule permet de produire un résultat dans une cellule. On se souvient qu'afin d'avoir d'autres résultats il fallait ensuite étendre la formule de la cellule initiale dans un sens. Souvent vers le bas ou vers la droite.
Dans le modern Excel, cette réalité historique du fonctionnement d'Excel n'est plus vraie. Une formule peut produire plusieurs résultats dans plusieurs cellules sans que la formule de la cellule initiale ait besoin d'être étirée.
Ce qui a 3 conséquences principales :
a) C'est plus simple de créer des formules ou plus largement, des outils Excel.
b) Le risque d'erreur est largement diminué en cas de modification, car il n'y a plus besoin de vérifier qu'une formule a été modifiée dans chaque cellule ou jusqu'en bas de sa colonne.
c) Les résultats sont dynamiques : les résultats s'auto-propagent d'eux-mêmes, sur autant de cellules que nécessaire évitant ainsi les résultats incomplets dus à des formules pas assez étendues vers le bas, par exemple.
C'est un principe général qui change tout. Qui n'a pas commencé en 2020, par exemple avec l'ajout des fonctions TRANSPOSE et HISTORIQUE.ACTIONS, mais qui restait jusqu'ici exceptionnel et lié à certaines fonctions précises.
Dans le modern Excel, le principe de propagation des résultats depuis la formule d'une cellule initiale est la règle. Si une formule peut produire plusieurs résultats dans plusieurs cellules, elle le fera. Quelles que soient les fonctions ou les symboles utilisés dans cette formule.
2 ) La fonction SEQUENCE
La fonction la plus représentative de ce modern Excel, c'est la fonction SEQUENCE. Elle permet de créer des séries de nombres dont les résultats sont propagés sur X lignes et Y colonnes. Elle remplace donc efficacement le processus lent et fastidieux d'étendre un nombre ou une formule vers le bas (ou vers la droite) ou encore l'usage de l'outil Série, qui existait dans l'onglet Accueil du ruban, mais qui était relativement peu connu.
SEQUENCE : permet de créer des séries de nombres
La syntaxe : =SEQUENCE (lignes ; [colonnes] ; [début] ; [pas] )
La fonction SEQUENCE permet, par exemple, de créer la série de tous les nombres entre 1 et 1000 sur 1000 cellules adjacentes de la même colonne. Donc, de créer 1000 résultats en une seule formule sans avoir à étendre cette formule.
A elle seule, cette fonction permet de comprendre la puissance du modern Excel par rapport à la logique "une cellule = une formule = un résultat". C'est une excellente fonction pour comprendre ce que le modern Excel introduit de nouveau au fonctionnement des formules dans Excel.
3 ) Les nouvelles fonctions de texte
Mais le modern Excel a aussi ajouté au moins 4 INCROYABLES fonctions de texte : JOINDRE.TEXTE, TEXTE.AVANT, TEXTE.APRES et FRACTIONNER.TEXTE qui rendent l'analyse des textes (mais pas seulement) plus simple et plus dynamique.
JOINDRE.TEXTE : permet de combiner des textes entre eux séparés par un délimiteur
La syntaxe : JOINDRE.TEXTE(délimiteur ; ignorer_vide ; texte1 ; ... )
TEXTE.AVANT : permet de récupérer le texte avant un délimiteur
La syntaxe : TEXTE.AVANT(texte ; délimiteur ; [instance_num] ; [match_mode] ; [match_end] ;[if_not_found] )
TEXTE.APRES : permet de récupérer le texte après un délimiteur
La syntaxe : TEXTE.APRES(texte ; délimiteur ; [instance_num] ; [match_mode] ; [match_end] ;[if_not_found] )
FRACTIONNER.TEXTE : permet de séparer un texte en plusieurs cellules
La syntaxe : FRACTIONNER.TEXTE( texte ;col_delimiter ; [row_delimiter] ; [ignore_empty] ; [match_mode] ; [pad_with] )
JOINDRE.TEXTE est le nouveau CONCATENER (et CONCAT). Cette fonction est beaucoup plus efficace pour joindre des textes de manière dynamique, en ajoutant un délimiteur entre chaque nouveau texte. Par exemple un espace ou un point-virgule.
Les fonctions TEXTE.AVANT et TEXTE.APRES sont incroyablement puissantes, notamment parce qu'elles offrent de nombreuses options dans leurs arguments facultatifs. Elles permettent de remplacer avec un énorme gain de simplicité les combinaisons de fonctions comme GAUCHE/CHERCHE ou STXT/CHERCHE.
La fonction FRACTIONNER.TEXTE peut aussi remplacer les combinaisons GAUCHE/STXT/CHERCHE, mais elle est encore plus utile comme alternative dynamique à l'outil Convertir, qui permettait de fractionner une colonne en plusieurs colonnes. Elle peut donc automatiser, par exemple, le traitement d'un fichier CSV récurrent dans des cas ou Power Query n'est pas forcément nécessaire.
4 ) La fonction UNIQUE
La fonction UNIQUE est à la fois toute simple et surpuissante. Elle n'a qu'un seul argument, aucun argument facultatif et elle est donc aussi facile à manipuler qu'une fonction SOMME ou MOYENNE.
Mais ce qu'elle permet est très différent : comme son nom l'indique elle permet de ne garder que les valeurs uniques d'un ensemble de valeurs, et donc supprimer les doublons. Elle est donc une excellente alternative à l'outil Supprimer Les Doublons. A la fois plus simple et plus dynamique.
UNIQUE : permet de récupérer les valeurs uniques.
La syntaxe : UNIQUE(matrice ; [by_col] ; [exactly_once] )
5 ) Les fonctions TRIER et TRIERPAR
Grâce au modern Excel et à ses fonctions, d'autres outils seront aussi moins utilisés qu'avant. C'est le cas pour tous les outils de tri, qui peuvent avantageusement être remplacés par la fonction TRIER et sa version plus avancée TRIERPAR.
TRIER : permet de trier des valeurs.
La syntaxe : TRIER(tableau ; [index_tri] ; [ordre_tri] ; [par_col] )
TRIERPAR : permet de trier des valeurs en fonction d'autres valeurs
La syntaxe : TRIERPAR(tableau ; partableau1 ; [ordretri1] ; ... )
6 ) La fonction FILTRE (la révolution du modern Excel)
Avant, pour filtrer un tableau, il fallait utiliser les fonctionnalités de filtres, de filtres avancés ou encore les segments. Mais on se souvient des limites : plusieurs filtres incompatibles ne pouvaient pas être affichés en même temps, et il fallait donc afficher un résultat, l'analyser, puis afficher l'autre pour enfin l'analyser lui-aussi. Tâche très fastidieuse et donc souvent source d'erreurs.
La fonction FILTRE permet de rendre obsolètes toutes ces manipulations répétitives en créant des filtres dynamiques qui peuvent être incompatibles. Une feuille avec toutes les factures payées par exemple, et une feuille avec toutes les factures impayées. Et non l'un ou l'autre ou alternativement l'un puis l'autre.
FILTRE : permet de filtrer des valeurs en fonction d'une ou plusieurs conditions.
La syntaxe : FILTRE(tableau ; inclure ; [si_vide] )
Les fonctions citées précédemment sont d'excellents exemples de fonctions qui remplaceront parfaitement les outils qui existaient avant : Série, Convertir, Supprimer les doublons et les outils de tris et de filtres. Elles automatiseront les résultats produits jusqu'alors par ces différents outils, ce qui évitera de refaire sans cesse les mêmes manipulations.
7 ) Les 9 fonctions permettant de redimensionner des tableaux/matrices
D'autres fonctions ont aussi été ajoutées pour automatiser d'autres manipulations humaines répétitives(comme le fait Power Query d'ailleurs). Notamment les manipulations visant à redimensionner un tableau. En voici une liste à peu près exhaustive :
CHOISIRCOLS : permet de récupérer seulement certaines colonnes d'un tableau, et de les réorganiser si besoin.
La syntaxe : CHOISIRCOLS(array ; col_num1 ; [col_num2] ; ... )
CHOISIRLIGNES : permet de récupérer seulement certaines lignes d'un tableau, et de les réorganiser si besoin.
La syntaxe : CHOISIRLIGNES( array ; row_num1 ; [row_num2] ; ... )
ORGA.COLS : permet de modifier un tableau en définissant un nombre de colonnes à obtenir.
La syntaxe : ORGA.COLS( vector ; wrap_count; [pad_with] )
ORGA.LIGNES : permet de modifier un tableau en définissant un nombre de lignes à obtenir.
La syntaxe : ORGA.LIGNES(vector ; wrap_count ; [pad_with] )
DANSCOL : permet de mettre toutes les valeurs d'un tableau dans une seule colonne.
La syntaxe : DANSCOL( array ; [ignore] ;[scan_by_column] )
DANSLIGNE : permet de mettre toutes les valeurs d'un tableau dans une seule ligne.
La syntaxe : DANSLIGNE(array ; [ignore] ; [scan_by_row] )
PRENDRE : permet de ne récupérer que les X premières ou dernières lignes et/ou colonnes d'un tableau.
La syntaxe : PRENDRE(array ; rows ; [columns] )
EXCLURE : permet de récupérer tout sauf les X premières ou dernières lignes et/ou colonnes d'un tableau.
La syntaxe : EXCLURE(array ; rows ; [columns] )
ETENDRE : permet d'étendre les dimensions d'un tableau sur un nombre de lignes et/ou de colonnes.
La syntaxe : ETENDRE(array ; rows ; [columns] ; [pad_with] )
Sans détailler chacune de ces fonctions, il est important de comprendre qu'elles permettent de s'appliquer à des plages entières et de modifier l'ensemble de leurs structures en une fois, plutôt que point par point voire d'effectuer les manipulations correspondantes une par une et à la main (ou via de la programmation via Power Query, VBA ou Office Scripts).
8 ) Les fonctions ASSEMB.V et ASSEMB.H
Ces 2 fonctions permettent aujourd'hui de joindre plusieurs colonnes ou plusieurs lignes de plusieurs tableaux ensemble. Comme le font les outils Combiner (Fusionner et Ajouter) dans Power Query. Avec le "V" d'ASSEMB.V pour un assemblage vertical (les tableaux en-dessous les uns des autres) et le "H" d'ASSEMB.H pour un assemblage horizontal (les tableaux à la droite les uns des autres).
ASSEMB.V : permet d'ajouter verticalement plusieurs tableaux en-dessous les uns des autres.
La syntaxe : ASSEMB.V(array1 ; [array2] ; ... )
ASSEMB.H : permet d'ajouter horizontalement plusieurs tableaux à droite les uns des autres.
La syntaxe : ASSEMB.H(array1 ; [array2] ; ... )
9 ) Les fonctions RECHERCHEX et EQUIVX
Mais même si c'est déjà beaucoup, c'est loin d'être tout. Le modern Excel a aussi ajouté, et c'était très attendu par la communauté Excel, 2 nouvelles fonctions de recherche, notamment pour remplacer la fonction RECHERCHEV, ses contraintes et ses limites.
Ce sont les fonctions RECHERCHEX et EQUIVX.
Si la seconde est juste une légère amélioration de la fonction EQUIV, la première par contre est une vraie révolution pour les utilisateurs et utilisatrices d'Excel.
RECHERCHEX : permet de récupérer une cellule ou une plage dans un tableau en fonction de la position de la première ou dernière apparition d'une valeur dans un autre tableau
La syntaxe : RECHERCHEX(valeur_cherchée ; tableau_recherche ; tableau_renvoyé ; [si_non_trouvé] ;[mode_correspondance] ; [mode_recherche] )
EQUIVX : permet de récupérer le numéro de la position de la première ou dernière apparition d'une valeur dans un tableau.
La syntaxe : EQUIVX(valeur_cherchée ; tableau_recherche ; [mode_correspondance] ; [mode_recherche])
10 ) La fonction LET
Et pour compléter cet incroyable arsenal et projeter Excel encore plus loin dans le futur, Microsoft a rajouté des fonctions très proches de celles existantes dans beaucoup de langages de programmation, dont elles reprennent le fonctionnement et le vocabulaire. Ces fonctions n'ont pour l'instant pas été traduites, et ce ne sera peut-être jamais le cas.
L'une d'elle permet de créer des variables locales de calculs à l'intérieur d'une formule, pour mieux l'organiser, la comprendre, la modifier ultérieurement si besoin, et normalement la simplifier. Cette fonction couteau-Suisse permet aussi d'éviter les multiples sélections au sein d'une même formule. Par exemple d'éviter de sélectionner plusieurs fois la même cellule ou d'écrire plusieurs fois la même condition.
LET : permet de créer des variables intermédiaires dans une formule afin d'en simplifier la compréhension et d'en réduire le temps de calcul.
La syntaxe : LET( nom1 ;valeur1 ; calcul_final_ou_nom2 ; ... )
11 ) Les fonctions de boucles
Parmi les nouveautés, 6 autres de ces fonctions permettent de faire des boucles, comme en VBA, en Python ou en JavaScript, offrant du coup d'énormes possibilités sans avoir besoin de coder quoi que ce soit. Les voici :
MAP : permet de travailler individuellement sur chaque cellule d'un ou plusieurs tableaux (ne peut s'utiliser qu'avec la fonction LAMBDA comme dernier argument).
La syntaxe : MAP(tableau1 ; fonction_lambda_ou_tableau2 ; ... )
La syntaxe complète : MAP(tableau1 ; LAMBDA( chaque_cellule_du_tableau1 ; calcul_final_par_cellule ) )
BYROW : permet de travailler individuellement sur chaque ligne d'un tableau (ne peut s'utiliser qu'avec la fonction LAMBDA comme dernier argument).
La syntaxe : BYROW(matrice ; fonction_lambda )
La syntaxe complète : BYROW( matrice ; LAMBDA( chaque_ligne_de_la_matrice ; calcul_final_par_ligne ))
BYCOL : permet de travailler individuellement sur chaque colonne d'un tableau (ne peut s'utiliser qu'avec la fonction LAMBDA comme dernier argument).
La syntaxe : BYCOL(matrice ; fonction_lambda )
La syntaxe complète : BYCOL( matrice ; LAMBDA( chaque_colonne_de_la_matrice ;calcul_final_par_colonne ) )
MAKEARRAY : permet de créer un tableau de X lignes et Y colonnes, en travailler individuellement surchaque cellule de ce tableau (ne peut s'utiliser qu'avec la fonction LAMBDA comme dernier argument).
La syntaxe : MAKEARRAY(lignes ; colonnes ; fonction_lambda )
La syntaxe complète : MAKEARRAY( lignes ; colonnes ; LAMBDA( index_de_ligne ; index_de_colonne ;calcul_final_intégrant_les_2_index ) )
SCAN : permet de travailler individuellement et itérativement sur chaque cellule d'un tableau(ne peut s'utiliser qu'avec la fonction LAMBDA comme dernier argument).
La syntaxe : SCAN(valeur_initiale ; tableau ; fonction_lambda )
La syntaxe complète :SCAN( valeur_initiale ; tableau ; LAMBDA( valeur_actuelle ; nouvelle_valeur ;calcul_final_itératif_intégrant_les_2_valeurs ) )
REDUCE : permet de travailler individuellement et itérativement sur chaque cellule d'un tableau mais n'affiche que le dernier résultat (ne peut s'utiliser qu'avec la fonction LAMBDA comme dernier argument).
La syntaxe : REDUCE(valeur_initiale ; tableau ; fonction_lambda )
La syntaxe complète : REDUCE( valeur_initiale ; tableau ; LAMBDA( valeur_actuelle ; nouvelle_valeur ;calcul_final_itératif_intégrant_les_2_valeurs ) )
(Ces 2 dernières fonctions itèrent à chaque nouvelle étape sur le résultat de l'étape précédente.)
Attention, toutes ces fonctions s'utilisent forcément combinées à la fonction LAMBDA comme dernier argument de leur syntaxe.
12 ) L'autre utilité de la fonction LAMBDA
La fonction LAMBDA a 2 utilisations possibles.
Associée à l'une des fonctions MAP/BYROW/BYCOL/MAKEARRAY/SCAN/REDUCE, elle est le dernier argument obligatoire, et permet de définir des noms de variables intermédiaires à chaque élément sur lequel ces fonctions doivent itérer.
Seule, mais utilisée dans l'outil Gestionnaire de noms d'Excel, elle permet de créer des fonctions personnalisées pouvant être utilisées dans tout le classeur auquel elles appartiennent, et ce sans avoir à utiliser du VBA.
LAMBDA : utilisée dans les Gestionnaire de noms, permet de créer des fonctions personnalisées utilisables dans tout le classeur.
La syntaxe : LAMBDA(paramètre_ou_calcul ; ... )
13 ) Le symbole de propagation"#"
Un seul caractère, mais qui permet de changer radicalement la manière de travailler dans Excel en sélectionnant une seule cellule tout en récupérant de tous les résultats propagés depuis cette cellule. Ce symbole c'est "#".
Si la cellule A1 contient une formule qui propage ses résultats sur 100 lignes, =SOMME(A1#) permettra d'obtenir la somme de toute cette plage sans avoir à sélectionner la plage de cellules. Cela peut sembler anodin comme ça, mais c'est fantastique et ça change tout.
Le symbole "#": permet de sélectionner de manière dynamique tous les résultats propagés depuis la formule d'une cellule (cf. le point 1 de cet article à propos du principe de propagation)
La syntaxe : A1# ou Dates#*
*Le symbole "#" peut autant s'appliquer à une référence normale de cellule (de type A1 ou L1C1) qu'à une référence nommée existante dans le Gestionnaire de noms, comme Dates, ListeClients, etc.
14 ) Et plein d'autres fonctions :
En vrac : ISOMMITED, IMAGE, VALEUR.EN.TEXTE, TABLEAU.EN.TEXTE, TABLEAU.ALEA, TYPE, FREQUENCE, etc...
Voilà déjà beaucoup d'informations !
Mais il en reste encore de très nombreuses autres à découvrir, sur le modern Excel.
Bien entendu, tous les éléments cités plus haut n'ont pour objectif que de mettre quelques points sur quelques "i" sur le vaste sujet du modernExcel qui en juillet 2023 est encore un work-in-progress du côté de Microsoft.
Suivez-nous sur les réseaux pour rester informé des toutes dernières nouveautés du mordern Excel !
