Guide complet : Maîtriser les six fonctions révolutionnaires de Microsoft Excel

Microsoft Excel a connu sa plus grande transformation depuis des décennies avec l’introduction des tableaux dynamiques. Ces nouvelles fonctionnalités permettent à une seule formule de générer plusieurs résultats dans des cellules adjacentes et de se redimensionner automatiquement. Cette révolution a donné naissance à six fonctions essentielles qui transforment radicalement l’expérience utilisateur d’Excel.

Matériel nécessaire

Pour suivre ce guide, l’utilisateur aura besoin de :

• Excel pour Microsoft 365, Excel 2021 ou version ultérieure
• Excel pour le web
• Application Excel mobile ou tablette
• Un jeu de données d’exemple pour pratiquer
• Connaissances de base des formules Excel

Important : Ces fonctions ne sont pas disponibles dans les versions antérieures d’Excel.

1. Fonction FILTER : Extraction dynamique de sous-ensembles de données

Comprendre la syntaxe FILTER

La fonction FILTER révolutionne l’extraction de données en permettant de filtrer un tableau selon des critères spécifiques. Sa syntaxe est la suivante :

=FILTER(tableau, critère, valeur_si_vide)

Où :

• tableau (obligatoire) : le tableau à filtrer
• critère (obligatoire) : l’argument booléen qui retourne VRAI ou FAUX
• valeur_si_vide (facultatif) : la valeur à retourner si aucun résultat

Mise en pratique de la fonction FILTER

Étape 1 : Créer un tableau de données avec les colonnes Nom, Âge et Score.

Étape 2 : Dans une cellule vide, saisir la formule suivante pour extraire les noms et âges des personnes ayant obtenu plus de 70 points :

=FILTER(T_Résultats[[Nom]:[Âge]],T_Résultats[Score]>70)

Étape 3 : Valider la formule en appuyant sur Entrée. Le résultat s’affiche automatiquement dans plusieurs cellules.

Utilisation de critères multiples

Étape 4 : Pour appliquer une logique OU, utiliser le symbole plus (+) :

=FILTER(T_Résultats[Nom],(T_Résultats[Âge]>35)+(T_Résultats[Score]>65))

Étape 5 : Pour appliquer une logique ET, utiliser le symbole multiplication (*) :

=FILTER(T_Résultats[Nom],(T_Résultats[Âge]>35)*(T_Résultats[Score]>65))

2. Fonction RANDARRAY : Génération de tableaux de nombres aléatoires

Syntaxe et paramètres de RANDARRAY

La fonction RANDARRAY combine les capacités des fonctions ALEA et ALEA.ENTRE.BORNES en une seule fonction dynamique :

=RANDARRAY(lignes, colonnes, min, max, entier)

Où tous les arguments sont facultatifs :

• lignes : nombre de lignes à retourner
• colonnes : nombre de colonnes à retourner
• min : valeur minimale
• max : valeur maximale
• entier : FAUX pour décimales, VRAI pour nombres entiers

Application pratique de RANDARRAY

Étape 1 : Pour générer un tableau de 7 lignes et 1 colonne de nombres entiers entre 1 et 40 :

=RANDARRAY(7,1,1,40,VRAI)

Étape 2 : Créer une version flexible en référençant des cellules :

=RANDARRAY(D1,D2,D3,D4,D5)

Attention : RANDARRAY est une fonction volatile qui recalcule à chaque modification du classeur.

Randomisation d’un tableau existant

Étape 3 : Pour randomiser l’ordre d’un tableau existant :

=TRIER.PAR(T_Groupes[Groupe],RANDARRAY(NB(T_Groupes[Groupe])))

3. Fonction SEQUENCE : Création de séquences numériques dynamiques

Syntaxe de la fonction SEQUENCE

La fonction SEQUENCE génère automatiquement des séquences de nombres :

=SEQUENCE(lignes, colonnes, début, pas)

Mise en œuvre de SEQUENCE

Étape 1 : Créer une séquence simple de 1 à 10 :

=SEQUENCE(10)

Étape 2 : Générer une séquence avec un pas personnalisé :

=SEQUENCE(5,1,2,3)

Cette formule produit : 2, 5, 8, 11, 14

Étape 3 : Créer des dates séquentielles :

=SEQUENCE(7)+AUJOURDHUI()-1

4. Fonction SORTBY : Tri dynamique basé sur des critères

Comprendre SORTBY

SORTBY permet de trier un tableau selon les valeurs d’un autre tableau :

=SORTBY(tableau, par_tableau, ordre)

Application de SORTBY

Étape 1 : Trier les noms par score décroissant :

=SORTBY(T_Résultats[Nom],T_Résultats[Score],-1)

Étape 2 : Tri multicritères (par âge puis par score) :

=SORTBY(T_Résultats[Nom],T_Résultats[Âge],1,T_Résultats[Score],-1)

5. Fonction UNIQUE : Extraction de valeurs distinctes

Syntaxe et utilisation d’UNIQUE

La fonction UNIQUE retourne une liste de valeurs uniques :

=UNIQUE(tableau, par_col, exactement_une_fois)

Mise en pratique d’UNIQUE

Étape 1 : Extraire les valeurs uniques d’une colonne :

=UNIQUE(T_Résultats[Ville])

Étape 2 : Compter les valeurs uniques :

=LIGNES(UNIQUE(T_Résultats[Ville]))

Étape 3 : Obtenir uniquement les valeurs qui apparaissent exactement une fois :

=UNIQUE(T_Résultats[Ville],,VRAI)

6. Fonction XLOOKUP : Recherche avancée et dynamique

Syntaxe complète d’XLOOKUP

XLOOKUP remplace et améliore les fonctions RECHERCHEV et RECHERCHEH :

=XLOOKUP(valeur_cherchée, tableau_recherche, tableau_retour, si_non_trouvé, mode_correspondance, mode_recherche)

Utilisation d’XLOOKUP

Étape 1 : Recherche exacte simple :

=XLOOKUP(E2,T_Résultats[Nom],T_Résultats[Score])

Étape 2 : Recherche avec valeur par défaut :

=XLOOKUP(E2,T_Résultats[Nom],T_Résultats[Score],”Non trouvé”)

Étape 3 : Retourner plusieurs colonnes :

=XLOOKUP(E2,T_Résultats[Nom],T_Résultats[[Âge]:[Score]])

Étape 4 : Recherche approximative (plus proche valeur inférieure) :

=XLOOKUP(E2,T_Prix[Quantité],T_Prix[Prix_Unitaire],,-1)

Points importants sur les fonctions de tableaux dynamiques

Gestion des erreurs de débordement

Attention : Si les cellules adjacentes contiennent des données, Excel affiche une erreur

DÉBORDEMENT!. Il faut libérer l’espace nécessaire.

Conversion en valeurs fixes

Étape 1 : Sélectionner le tableau dynamique généré

Étape 2 : Appuyer sur Ctrl+C pour copier

Étape 3 : Cliquer sur Collage spécial > Valeurs

Optimisation des performances

Pour maintenir de bonnes performances :

• Éviter l’utilisation excessive de fonctions volatiles comme RANDARRAY
• Utiliser des références de tableaux structurés quand possible
• Limiter la taille des tableaux dynamiques dans les gros classeurs

Conclusion

Ces six fonctions révolutionnaires transforment fondamentalement l’approche de l’analyse de données dans Excel. Elles offrent une flexibilité et une puissance inégalées pour manipuler, filtrer et analyser les données. La maîtrise de ces outils permet d’automatiser de nombreuses tâches répétitives et d’obtenir des résultats dynamiques qui s’adaptent automatiquement aux modifications des données source.

L’adoption de ces fonctions représente un investissement stratégique pour tout utilisateur d’Excel souhaitant optimiser sa productivité et ses capacités d’analyse. Leur intégration dans les flux de travail quotidiens permet de créer des solutions robustes et évolutives pour la gestion et l’analyse de données.

Sandrine Marcos

Rédactrice spécialisée en édition de site. Formation de journaliste et passionnée par les nouvelles technologies, l’intelligence artificielle et la rédaction web.