Excel a changé : découvrez les fonctions modernes qui simplifient vraiment vos données

Excel évolue en permanence. Pourtant, nos habitudes, elles, ont parfois tendance à rester bien installées.

Copier-coller plusieurs tableaux pour les réunir, supprimer manuellement les doublons, construire des formules à rallonge pour extraire quelques mots d’une cellule ou encore créer un tableau croisé dynamique pour obtenir une simple synthèse.

Autant d’opérations que les fonctions modernes d’Excel permettent aujourd’hui de réaliser beaucoup plus simplement.

Avec Microsoft 365, une nouvelle génération de fonctions permet désormais de filtrer, réorganiser, combiner et synthétiser des données directement à l’aide de formules dynamiques.

Voici quelques-unes des fonctions qui peuvent réellement changer votre façon de travailler avec Excel.

1. UNIQUE, TRIER et FILTRE : créer des listes qui se mettent à jour automatiquement

Commençons par trois fonctions particulièrement pratiques au quotidien : UNIQUE, TRIER et FILTRE.

UNIQUE : éliminer les doublons sans toucher aux données

Vous disposez par exemple d’une colonne contenant les pays de vos clients et souhaitez obtenir la liste des différents pays représentés.

Une seule formule suffit :

=UNIQUE(A2:A13)

Excel génère automatiquement la liste des valeurs uniques.

Contrairement à la commande Supprimer les doublons, vous ne modifiez pas les données d’origine : vous créez un nouveau résultat qui reste lié à la source.

Exemple :

Dans cet exemple, l’objectif est d’obtenir la liste unique des pays de la colonne A :

Sélectionnez la fonction UNIQUE puis entrez simplement la plage de cellules des pays pour obtenir la liste unique des pays :

=UNIQUE(A2:A13)

Et pourquoi ne pas les trier au passage ?

Les fonctions dynamiques peuvent être combinées entre elles :

=TRIER(UNIQUE(A2:A13))

Vous obtenez ainsi une liste sans doublons, triée et dynamique.

Si les données sources évoluent, le résultat évolue lui aussi.

Exemple :

Pour obtenir une liste triée des pays, vous pouvez ajouter la fonction TRIER :

=TRIER(UNIQUE(A2:A13))

FILTRE : n’afficher que les données qui vous intéressent

FILTRE permet quant à elle d’extraire les lignes répondant à un ou plusieurs critères.

On peut ainsi créer une vue contenant uniquement les commandes d’un client, les factures non payées ou les ventes correspondant à une catégorie donnée.

Ces trois fonctions constituent déjà une excellente boîte à outils pour créer des listes et des vues dynamiques sans multiplier les manipulations manuelles.

Comment utiliser la fonction FILTRE ?

La fonction FILTRE n’a besoin que de 2 paramètres pour retourner un résultat.

1. La colonne ou les colonnes à renvoyer filtrées

Comme premier argument vous pouvez mettre autant de colonnes adjacentes que vous voulez

2. Le critère de filtrage sur une colonne

Ici, vous devez sélectionner une nouvelle colonne, pas nécessaire une colonne qui a été sélectionnée en premier argument et le critère pour lequel vous voulez faire un filtre

3. [Optionnel] le résultat à afficher quand il n’y a pas de résultat

Si le filtrage ne retourne aucune valeur, plutôt que de laisser une erreur, vous pouvez afficher un message personnel

Par exemple, nous voulons trouver les informations relatives au conseiller Cornu. Il nous suffit tout d’abord de sélectionner l’ensemble des données.

=FILTRE(F2:F85;E2:E85=I2)

Toutes les lignes correspondantes au conseiller Cornu sont retournées par la fonction

Résultat à afficher s’il n’y a pas de résultat

Si la fonction ne retourne aucune donnée, vous pouvez écrire ce que la fonction doit retourner dans cette situation en renseignant le 3e paramètre.

=FILTRE(F2:F85;E2:E85=I2; »Aucune valeur »)

2. CHOISIRCOLS et CHOISIRLIGNES : créer vos propres vues d’un tableau

Vous disposez d’un grand tableau contenant de nombreuses informations, mais seules quelques colonnes vous intéressent pour votre analyse ?

Au lieu de copier le tableau puis de supprimer tout ce qui est inutile, utilisez CHOISIRCOLS.

Prenons un tableau de commandes comportant dix colonnes. Pour ne conserver que les colonnes 1, 3 et 5 :

=CHOISIRCOLS(Commandes!A:J;1;3;5) 

Excel génère un nouveau tableau contenant uniquement les informations demandées.

CHOISIRLIGNES fonctionne selon le même principe, mais permet cette fois de sélectionner certaines lignes :

=CHOISIRLIGNES(Commandes!A:J;18;19;34) 

L’intérêt de ces fonctions ne réside pas seulement dans leur simplicité.

Les tableaux obtenus sont dynamiques : les données sources ne sont pas modifiées et leurs mises à jour peuvent se répercuter automatiquement dans le résultat.

Vous pouvez ainsi construire différentes vues d’une même source de données sans multiplier les copies.

3. ASSEMB.V et ASSEMB.H : réunir plusieurs tableaux avec une formule

Voilà deux fonctions susceptibles d’éviter quelques belles séances de copier-coller.

Imaginez trois tableaux de même structure contenant, par exemple, les données de différents films.

Nous souhaitons ne faire qu’un seul tableau global avec toutes ces données, sans passer par Power Query. Pour réaliser cela, nous utilisons la formule ASSEMB.V (le « V » pour assembler verticalement les données). La syntaxe de la formule est extrêmement simple car les seuls arguments à renseigner sont :

• array 1 : le seul argument obligatoire, fait référence au premier tableau
• array 2 : le deuxième tableau, qui s’ajoutera au premier.
• array n : Sélectionnez un autre tableau. La fonction peut accueillir jusqu’à 254 tableaux !

Il est fortement recommandé d’utiliser le Mode Tableau pour chacune des plages sélectionnées, et de les renommer dans la zone de texte Création de Tableau > Nom du Tableau

De cette façon

1. Le renommage permettra d’identifier plus facilement chaque tableau.
2. Il sera plus facile de sélectionner toutes vos cellules avec un clic en haut à gauche de chaque tableau 

Écrire la formule

La formule ASSEMB.V (tout comme ASSEMB.H) est très simple à écrire comme vous allez le constater. MAIS il y a juste une astuce à prendre en compte avec le tout premier tableau.

• Le premier tableau DOIT prendre en compte les entêtes
• La sélection des autres tableaux doit se faire SANS entête

Pour sélectionner le contenu ET les entêtes, il faut cliquer 2 fois quand la flèche est en diagonale. Une fois sélectionne seulement les données.

Voici la syntaxe de la fonction :

=ASSEMB.V(Films1[#Tout];Films2;Films3)

Voici donc le résultat :

Dans mes exemples de formation, j’utilise notamment cette fonction pour réunir plusieurs tableaux sans passer par Power Query.

Et si vous souhaitez assembler les données horizontalement ?

C’est le rôle de ASSEMB.H, qui permet d’ajouter des données les unes à côté des autres.

Ces fonctions deviennent particulièrement intéressantes lorsqu’elles sont associées aux Tableaux Excel : si de nouvelles données sont ajoutées aux tableaux sources, les références peuvent s’étendre automatiquement et faciliter ainsi la mise à jour du tableau consolidé.

Pour certains besoins de consolidation simples, cela peut constituer une alternative particulièrement légère à des manipulations plus complexes.

4. TEXTE.AVANT et TEXTE.APRES : des extractions de texte enfin plus lisibles

Voici deux fonctions que j’apprécie particulièrement pour leur simplicité.

Les habitués d’Excel connaissent probablement GAUCHE, DROITE, STXT ou CHERCHE.

Elles restent utiles, mais dès que l’on souhaite extraire une partie précise d’une chaîne de caractères, les formules peuvent rapidement devenir sportives.

Prenons une cellule contenant :

Marc DUPONT 12 Rue de la Gare 1003 LAUSANNE

Pour extraire simplement le premier élément situé avant un espace :

=TEXTE.AVANT(A2; » « )

Résultat :

Marc

Et si nous souhaitons récupérer le prénom et le nom :

=TEXTE.AVANT(A2; » « ;2)

Résultat :

Marc DUPONT

Dans cet exemple, obtenir le même résultat avec GAUCHE et CHERCHE nécessite d’imbriquer plusieurs fonctions.

TEXTE.APRES fonctionne selon la logique inverse : elle permet de récupérer ce qui se trouve après un délimiteur.

Ce sont de petites fonctions, mais elles illustrent parfaitement une tendance de l’Excel moderne : faire plus simplement ce que nous faisions auparavant avec des formules beaucoup plus complexes.

5. GROUPERPAR : créer une synthèse sans tableau croisé dynamique

Nous arrivons maintenant à une fonction particulièrement intéressante : GROUPERPAR.

Son objectif est de regrouper des données selon un critère et d’effectuer un calcul sur chaque groupe : somme, moyenne, nombre, etc.

Imaginons un tableau de commandes et une question toute simple :

Quel est le chiffre d’affaires réalisé pour chaque article ?

On peut utiliser une formule de ce type :

=GROUPERPAR(Commandes[Article]; Commandes[Prix total avec rabais]; SOMME)

Excel génère alors automatiquement un tableau contenant les différents articles et le total des ventes correspondant à chacun d’eux.

Le résultat est dynamique : aucune donnée source n’est modifiée et les résultats s’adaptent aux changements du tableau.

On retrouve ainsi une partie de la logique d’un tableau croisé dynamique, mais directement dans une formule.

GROUPERPAR est donc particulièrement intéressante pour construire des synthèses automatisées et réutilisables.

6. PIVOTER.PAR : quand le tableau croisé dynamique entre dans la barre de formule

PIVOTER.PAR va encore plus loin.

Cette fonction permet de réaliser des analyses de type tableau croisé dynamique directement dans une cellule : elle peut regrouper les données en lignes et en colonnes, puis leur appliquer une fonction d’agrégation.

Prenons par exemple un tableau de commandes.

Nous pouvons vouloir connaître le total des ventes :

• pour chaque secteur d’activité ;
• selon que la facture est payée ou non.

PIVOTER.PAR permet de croiser ces deux dimensions et d’additionner les montants correspondants. Le résultat est alors un véritable tableau de synthèse généré par une formule. La fonction propose également des arguments permettant notamment de gérer les totaux et sous-totaux, le tri ou encore le filtrage.

Utiliser la fonction PIVOTER.PAR

Syntaxe de base :

=PIVOTER.PAR((row_fields,col_fields,values,function,[field_headers],[row_total_depth],[row_sort_order],[col_total_depth],[col_sort_order],[filter_array],[relative_to])

Les arguments PIVOTER.PAR en français :

• 1 – champs de lignes ;
• 2 – champs de colonnes ;
• 3 – colonnes des valeurs à agréger ;
• 4 – Fonction d’agrégation ;
• 5 – en-têtes de lignes ;
• 6 – totaux et sous-totaux de lignes ;
• 7 – ordre de tri des lignes ;
• 8 – totaux et sous-totaux de colonnes ;
• 9 – ordre de tri des colonnes ;
• 10 – filtre des lignes ;
• 11 – relatif à

Ainsi, pour obtenir le total des ventes par secteur d’activité et par statut de facture payée (Oui/Non), on regroupe par “Secteur d’activité” et “Facture payée”, et on additionne les “Prix total avec rabais”.

Le résultat est un tableau croisé dynamique “en formule”, qui affiche pour chaque secteur et chaque statut (Oui/Non) le total des ventes.

Est-ce la fin des tableaux croisés dynamiques ?

Pas du tout.

Le tableau croisé dynamique classique conserve un énorme avantage : son interface permet d’explorer les données très facilement et de déplacer visuellement les champs pour modifier une analyse.

PIVOTER.PAR répond à un autre besoin.

Elle devient particulièrement intéressante lorsque l’on souhaite créer une analyse automatisée, intégrée à une feuille de calcul et pouvant être combinée avec d’autres fonctions.

Ce n’est donc pas forcément un remplacement du tableau croisé dynamique, mais une nouvelle manière de penser certaines analyses.

Ce qui change vraiment dans Excel

Pris séparément, chacun de ces outils peut sembler n’être qu’une fonction supplémentaire dans l’immense catalogue d’Excel.

Mais lorsqu’on les regarde ensemble, une évolution beaucoup plus intéressante apparaît.

Excel permet désormais de construire avec des formules des tableaux entiers de résultats dynamiques.

On peut :

• extraire les valeurs uniques avec UNIQUE ;
• filtrer et trier les données ;
• sélectionner certaines lignes ou colonnes ;
• assembler plusieurs tableaux ;
• découper plus simplement du texte ;
• regrouper des données ;
• produire des tableaux de synthèse.

Et surtout, tout cela peut rester connecté aux données d’origine.

Moins de copier-coller, moins de tableaux intermédiaires et moins de formules inutilement complexes.

Bien sûr, ces nouvelles fonctions ne remplacent pas tous les outils existants. Power Query reste extrêmement puissant pour importer et transformer des données, et les tableaux croisés dynamiques restent remarquables pour explorer rapidement une base de données.

Mais les frontières bougent.

Des opérations qui nécessitaient autrefois plusieurs étapes peuvent aujourd’hui être réalisées avec une seule formule.

Et c’est peut-être cela, finalement, la transformation la plus intéressante d’Excel : la formule ne sert plus seulement à calculer une valeur. Elle peut désormais construire, transformer et analyser tout un ensemble de données.

Si vous utilisez Excel régulièrement, il est donc peut-être temps de revisiter quelques-unes de vos anciennes habitudes.

Votre prochain =SI(...) à rallonge vous en remerciera.

Envie d’aller plus loin ?

Vous souhaitez vous former ou former vos équipes pour utiliser Microsoft SharePoint online intelligemment ?

📅 Me contacter ici : Me contacter

Je propose des formations et accompagnements sur mesure, à distance ou en présentiel. Toujours avec une dose de pédagogie, un soupçon d’humour, et un zeste d’esprit critique.

Découvrir les formations Microsoft 365

Pour aller plus loin, découvrez aussi mon article Fusionner des listes SharePoint avec Power Query dans Excel : la méthode simple pour analyser vos données