En bref
- Les fonctions qui rapportent le plus au travail : RECHERCHEX (ou INDEX/EQUIV), SI combinée à ET/OU, les fonctions de texte et les tableaux croisés dynamiques.
- FILTRE, UNIQUE et TRIER sont des fonctions de la version 2021 : vérifiez votre version avant de les partager dans un fichier.
- Apprenez-les une par une, sur un cas réel, plutôt qu'en parcourant toute la liste de Microsoft.
- On retient une formule en la tapant : des exercices corrigés en temps réel valent mieux qu'une lecture passive.
Table des matières
Ce qu'on appelle une fonction Excel avancée
Excel compte des centaines de fonctions. Microsoft les présente dans une liste alphabétique où l'on peut chercher par nom ou par description, pour Microsoft 365, Excel 2024, 2021, 2019, 2016 et Excel Web. Personne n'a besoin de toutes les connaître.
En pratique, une fonction est « avancée » quand elle remplit l'une de ces conditions :
- elle va chercher une information ailleurs dans le classeur (recherche et référence) ;
- elle prend une décision à votre place (fonctions logiques) ;
- elle nettoie ou découpe des données mal formatées (texte) ;
- elle renvoie plusieurs résultats d'un coup (tableaux dynamiques) ;
- elle s'imbrique avec d'autres sans casser.
Si vous cherchez une vue d'ensemble par catégorie avec des exemples, notre
guide des fonctions Excel les classe pour vous.
Rechercher et récupérer des données
C'est la famille que les recruteurs et les managers testent en premier, parce qu'elle sert à tout : rapprocher une liste de ventes et un tarif, retrouver le nom d'un client à partir d'un code, croiser deux exports.
Microsoft décrit les fonctions intégrées pour retrouver des données dans une table ou une plage : RECHERCHEV, LOOKUP, INDEX associée à EQUIV, ou OFFSET associée à EQUIV. Les notions à comprendre sont toujours les mêmes : la valeur cherchée, la plage de recherche, la plage de résultat et le type de correspondance, exacte ou approximative.
RECHERCHEX, le réflexe moderne
Selon la page de Microsoft, RECHERCHEX cherche dans une plage ou un tableau et renvoie l'élément correspondant à la première correspondance trouvée. Elle peut chercher dans une colonne et renvoyer une valeur d'une autre colonne, quel que soit le côté où se trouve la colonne de retour. Elle utilise par défaut une correspondance exacte, peut afficher un résultat personnalisé quand rien n'est trouvé, et propose des modes approximatif, générique et de recherche inversée ou binaire.
Ces trois détails règlent les plus gros défauts de RECHERCHEV :
- plus de colonne de retour qui doit se trouver à droite ;
- plus de numéro de colonne à compter à la main ;
- plus de #N/A brut quand la valeur n'existe pas : vous écrivez le message voulu.
Exemple :
=RECHERCHEX(A2;Tarifs!A:A;Tarifs!C:C;"Code inconnu") renvoie le prix de la colonne C pour le code en A2, ou « Code inconnu ».
Pour la prendre en main, faites les exercices corrigés sur RECHERCHEX. Si vous travaillez encore sur d'anciens fichiers, les exercices RECHERCHEV et RECHERCHEH restent utiles : ces formules sont partout dans les classeurs hérités.
INDEX et EQUIV, la combinaison qu'on retrouve partout
Le guide Essential Spreadsheets de l'Université de York explique qu'INDEX et MATCH (EQUIV en français) se combinent pour rechercher une valeur et récupérer une donnée associée, et que cette paire se rencontre encore dans les feuilles de calcul des autres. Vous en croiserez donc, même si vous écrivez vous-même du RECHERCHEX.
Le principe : EQUIV trouve la position de la valeur, INDEX renvoie la cellule à cette position. =INDEX(C2:C500;EQUIV(A2;A2:A500;0)). Le 0 impose la correspondance exacte. Entraînez-vous avec les exercices INDEX EQUIV.
Microsoft recense aussi, dans sa référence des fonctions de recherche et de référence, XMATCH, FILTRE, UNIQUE, TRIER et ASSEMB.V/ASSEMB.H. Nous y revenons plus bas.
Appliquer des conditions : SI, ET, OU, NON
Microsoft explique dans sa page sur les formules conditionnelles que SI, ET, OU et NON testent des conditions et retournent des résultats différents selon qu'elles sont vraies ou fausses. SI peut être combinée avec ET ou OU pour décider selon plusieurs critères.
Trois cas de terrain :
| Besoin | Formule type |
| Prime si objectif atteint ET ancienneté suffisante | =SI(ET(B2>=100;C2>=2);"Prime";"") |
| Alerte si retard OU montant élevé | =SI(OU(D2>30;E2>10000);"À relancer";"OK") |
| Statut inverse d'une condition | =SI(NON(F2="Payé");"Impayé";"Soldé") |
Le piège classique : imbriquer cinq SI les uns dans les autres. Dès que la formule devient illisible, découpez en colonnes intermédiaires, ou passez par une table de correspondance et une recherche. Une formule qu'un collègue ne peut pas relire est une formule qu'on finira par casser.
Pour combiner conditions et recherche sur un même cas, essayez la série SI, RECHERCHEV et raccourcis.
Nettoyer et extraire du texte
Les exports de logiciels métier arrivent rarement propres : espaces en trop, codes collés à des libellés, noms et prénoms dans une seule cellule. Quatre fonctions règlent la majorité des cas.
- SUBSTITUE remplace des occurrences de texte, REMPLACER remplace une portion à un emplacement donné, et NBCAR compte les caractères, comme le détaille cet article sur les fonctions utiles en entreprise.
- TEXTE.AVANT renvoie le texte situé avant un caractère ou une chaîne donnés, TEXTE.APRES celui situé après, d'après la liste par catégorie de Microsoft.
Exemple : pour extraire le nom de domaine d'une adresse e-mail en A2,
=TEXTE.APRES(A2;"@"). Pour supprimer les tirets d'un numéro :
=SUBSTITUE(A2;"-";""). Les
exercices SUBSTITUE vous font manipuler ce type de données.
Un conseil : nettoyez dans une colonne à part, jamais par-dessus la donnée source. Vous gardez une trace et vous pouvez recommencer si l'export change.
Résumer et analyser pour produire un rapport
Pour les rapports, Microsoft indique que les tableaux croisés dynamiques résument les données avec Somme, Nombre, Moyenne, Maximum et d'autres fonctions d'agrégation. C'est l'outil le plus rapide pour passer de 20 000 lignes à un tableau de cinq lignes lisible par un directeur.
Quelques habitudes qui évitent les mauvaises surprises :
- partez d'un tableau propre : une ligne par enregistrement, des en-têtes uniques, pas de lignes vides ;
- changez la fonction d'agrégation selon la question (somme des ventes, nombre de commandes, moyenne du panier) ;
- rafraîchissez le croisé après toute modification de la source.
Pour isoler un sous-ensemble avant de résumer, les
exercices sur les filtres de tableau sont un bon point de départ.
Tableaux dynamiques et fonctions récentes : attention aux versions
C'est là que se joue le piège le plus fréquent : vous écrivez une formule élégante, votre collègue l'ouvre et voit une erreur. Selon Microsoft, FILTRE, UNIQUE et TRIER sont des fonctions de 2021, et ASSEMB.V est une fonction de 2024.
Ce qu'elles font, d'après la même page :
- FILTRE filtre une plage selon des critères ;
- UNIQUE renvoie les valeurs distinctes ;
- TRIER trie une plage ;
- ASSEMB.V et ASSEMB.H ajoutent des tableaux verticalement et horizontalement.
Leur intérêt : une seule formule renvoie une liste entière qui se met à jour toute seule.
=TRIER(UNIQUE(FILTRE(B2:B500;A2:A500="Paris"))) donne la liste triée des valeurs distinctes de la colonne B pour les lignes de Paris, sans copier de formule vers le bas.
Avant de diffuser un fichier, demandez quelle version utilisent ses destinataires. Si certains sont sur Excel 2019 ou plus ancien, restez sur INDEX/EQUIV et sur les tableaux croisés dynamiques.
Combiner les fonctions : un exemple complet
Un cas typique de manager : un export de commandes (client, produit, montant, date) et un fichier de tarifs. Vous voulez la liste des gros clients de la région, avec leur remise.
- Nettoyer : =SUBSTITUE(A2;" ";"") retire les espaces parasites dans les codes clients.
- Rechercher : =RECHERCHEX(A2;Remises!A:A;Remises!B:B;0) récupère le taux de remise, ou 0 si le client est absent.
- Décider : =SI(ET(C2>5000;D2>0);"Gros compte remisé";"Standard") classe la ligne.
- Extraire : =FILTRE(A2:E500;F2:F500="Gros compte remisé") liste uniquement ces lignes.
- Résumer : un tableau croisé dynamique donne le total par région.
Chaque étape est simple. La puissance vient de l'enchaînement, et c'est ce qu'il faut s'entraîner à faire. Autre aide : nommer une plage de cellules rend la formule plus lisible et conserve ses références absolues à la recopie, ce que rappellent les
Éditions ENI dans leur ouvrage sur les calculs avancés.
Copilot peut aussi proposer des formules à partir d'instructions en langage courant, comme le montre Microsoft dans son guide de formules essentielles. Relisez toujours le résultat : si vous ne comprenez pas la formule proposée, vous ne saurez pas la corriger le jour où les données changeront.
Notre point de vue : apprendre en tapant
Nous construisons LogicExcel autour d'une idée simple : on ne retient pas une formule en la lisant, on la retient en l'écrivant, en se trompant, puis en recevant un retour immédiat. Les tutoriels passifs donnent l'impression de comprendre, puis on se retrouve devant une cellule vide le lundi matin.
Notre conseil, pour les analystes, les managers et les personnes en reconversion que nous croisons :
- choisissez une fonction par semaine, liée à une vraie tâche de votre travail ;
- tapez la formule vous-même, même si Copilot ou un collègue pourrait la fournir ;
- apprenez RECHERCHEX avant RECHERCHEV, mais sachez lire les deux ;
- gardez les raccourcis clavier pour plus tard : la logique d'abord, la vitesse ensuite.
Les leçons que nous écrivons visent un besoin opérationnel concret, pas la théorie. C'est la différence entre connaître le nom d'une fonction et oser l'utiliser devant un fichier de 10 000 lignes.
> — Thomas Houza
S'entraîner gratuitement, sans inscription
Sur LogicExcel, vous tapez vos formules dans une interface qui simule Excel et la correction est instantanée, sans compte à créer. Commencez par les exercices Excel gratuits avec réponses, puis suivez les cours Excel du débutant à l'avancé si vous voulez un parcours structuré. Pour viser une qualification, la préparation à la certification Microsoft Excel couvre les examens MO-200 et MO-201. Les guides d'apprentissage complètent le tout avec des astuces par thème.
Un plan d'apprentissage réaliste en quatre semaines
Plutôt que de tout survoler, voici un ordre qui fonctionne pour la plupart des profils professionnels.
Semaine 1 : les conditions. SI, ET, OU, puis NON. Objectif : écrire une formule de statut sur un de vos vrais fichiers, sans imbrication au-delà de deux niveaux.
Semaine 2 : la recherche. RECHERCHEX, puis INDEX/EQUIV pour savoir relire les fichiers des autres. Objectif : remplacer un copier-coller manuel que vous faites chaque semaine.
Semaine 3 : le texte. SUBSTITUE, TEXTE.AVANT, TEXTE.APRES. Objectif : nettoyer un export sans retaper une seule cellule.
Semaine 4 : la synthèse. Tableau croisé dynamique, puis FILTRE, UNIQUE et TRIER si votre version les prend en charge. Objectif : un petit rapport qui se met à jour en rafraîchissant.
À chaque étape, mesurez le gain : combien de minutes la tâche prenait avant, combien elle prend maintenant. C'est ce chiffre, plus que la liste des fonctions connues, qui montre votre progression à un manager ou à un recruteur.
Erreurs fréquentes avec les fonctions avancées
- Plages décalées : une recherche dont la plage de recherche et la plage de résultat n'ont pas la même taille renvoie des valeurs fausses sans toujours afficher d'erreur. Vérifiez les deux.
- Correspondance approximative par oubli : avec les anciennes formules, oublier de demander la correspondance exacte peut renvoyer un résultat plausible mais faux. Sur les chiffres de facturation, c'est le pire cas.
- Nombres stockés comme du texte : un code « 00123 » collé comme texte ne trouve pas son homologue numérique. Nettoyez d'abord.
- Références relatives : une plage qui glisse en recopiant la formule vers le bas. Fixez-la avec $ ou donnez-lui un nom.
- Formules trop clever : si vous mettez plus de dix secondes à relire votre formule demain, simplifiez-la.
Pour chaque erreur, un test rapide : changez une valeur d'entrée à la main et vérifiez que le résultat bouge comme prévu.
FAQ
Quelles fonctions Excel faut-il connaître en priorité ?
Commencez par SI avec ET/OU, une fonction de recherche (RECHERCHEX ou INDEX/EQUIV), quelques fonctions de texte et les tableaux croisés dynamiques. Ces quatre familles couvrent la majorité des tâches de reporting. Ajoutez FILTRE, UNIQUE et TRIER si votre version d'Excel les propose.
Comment voir la liste de toutes les fonctions disponibles ?
Microsoft publie une liste alphabétique consultable par nom ou par description, valable pour Microsoft 365, Excel 2024, 2021, 2019, 2016 et Excel Web. Dans Excel, le bouton « Insérer une fonction » à côté de la barre de formule permet aussi de parcourir les catégories. Notre guide des fonctions les regroupe avec des exercices.
RECHERCHEX remplace-t-elle RECHERCHEV ?
Dans la plupart des cas oui, car RECHERCHEX cherche dans les deux sens et gère le résultat « introuvable » sans formule supplémentaire. RECHERCHEV reste à connaître, parce qu'elle est présente dans de très nombreux fichiers existants. Vérifiez que vos collègues disposent d'une version qui prend RECHERCHEX en charge avant de la généraliser.
Pourquoi ma formule FILTRE ou UNIQUE affiche-t-elle une erreur chez un collègue ?
Ces fonctions sont indiquées par Microsoft comme des fonctions de 2021. Une version plus ancienne d'Excel ne les reconnaît pas. Dans ce cas, utilisez INDEX/EQUIV ou un tableau croisé dynamique.
Peut-on apprendre ces fonctions gratuitement ?
Oui. LogicExcel propose des exercices corrigés en temps réel et des cours accessibles sans inscription. Vous pouvez commencer par une seule fonction, sans créer de compte.
Sources
- Fonctions Excel (par ordre alphabétique), Microsoft Support
- Use Excel built-in functions to find data in a table or a range of cells, Microsoft Support
- Fonction RECHERCHEX, Microsoft Support
- T4: Processing, Essential Spreadsheets, Université de York
- Lookup and reference functions (reference), Microsoft Support
- Create conditional formulas, Microsoft Support
- 20 fonctions Excel à bien maîtriser en entreprise, HubSpot
- Fonctions Excel (par catégorie), Microsoft Support
- Calculate values in a PivotTable, Microsoft Support
- Excel (versions 2024 et Microsoft 365), Calculs avancés, Éditions ENI
- 30 formules Excel avec des exemples et des invites Copilot, Microsoft
Recommandations
