Vous utilisez Excel tous les jours, mais vous avez l’impression de n’exploiter que 10% de l’outil. SOMME, MOYENNE, peut-être un TCD de temps en temps - et c’est à peu près tout. Le problème, c’est que vos collègues qui maîtrisent les bonnes formules bouclent en 5 minutes ce qui vous prend une demi-heure.
Bonne nouvelle : pas besoin de devenir un expert VBA. Vingt fonctions bien choisies suffisent à couvrir la grande majorité des besoins professionnels.
Les fonctions de recherche - fini le copier-coller entre onglets#
RECHERCHEX (le remplacement de RECHERCHEV)#
Si vous n’apprenez qu’une seule nouvelle fonction aujourd’hui, c’est celle-là. RECHERCHEX remplace avantageusement la vieille RECHERCHEV qu’on traînait depuis les années 90.
=RECHERCHEX(F2; A2:A500; C2:C500; "Non trouvé")Cette formule cherche la valeur de F2 dans la colonne A, et renvoie la valeur correspondante en colonne C. Si rien n’est trouvé, elle affiche “Non trouvé” au lieu d’une erreur.
Pourquoi c’est mieux que RECHERCHEV :
- Cherche dans n’importe quelle direction (pas seulement vers la droite)
- Gère nativement les valeurs non trouvées
- Plus lisible, moins de paramètres obscurs
Attention : RECHERCHEX est disponible depuis Excel 2021 et Microsoft 365. Si vous êtes sur une version antérieure, vous devrez rester sur RECHERCHEV.
INDEX + EQUIV - le duo de choc#
Pour les situations complexes où RECHERCHEX ne suffit pas :
=INDEX(C2:C500; EQUIV(F2; A2:A500; 0))EQUIV trouve la position de la valeur cherchée. INDEX renvoie la valeur à cette position. Combiné ensemble, ça fait une recherche flexible dans n’importe quelle direction, sur n’importe quel critère.
Honnêtement, une fois qu’on a compris ce duo, on ne revient plus jamais à RECHERCHEV.
Les fonctions conditionnelles - analyser avec précision#
SOMME.SI.ENS et NB.SI.ENS#
Le “ENS” à la fin, c’est pour “ensemble” - plusieurs critères en même temps.
=SOMME.SI.ENS(D2:D500; B2:B500; "Marketing"; E2:E500; ">1000")Traduction : additionne la colonne D pour les lignes où la colonne B vaut “Marketing” ET la colonne E dépasse 1000. En une formule, vous filtrez et calculez.
=NB.SI.ENS(B2:B500; "Marketing"; E2:E500; ">1000")Même logique, mais compte le nombre de lignes au lieu de faire la somme.
SI.CONDITIONS - la lisibilité avant tout#
Avant, pour tester plusieurs conditions, on empilait des SI imbriqués illisibles :
=SI(A2>90;"Excellent";SI(A2>70;"Bien";SI(A2>50;"Moyen";"Insuffisant")))Avec SI.CONDITIONS :
=SI.CONDITIONS(A2>90;"Excellent"; A2>70;"Bien"; A2>50;"Moyen"; VRAI;"Insuffisant")Nettement plus clair. Chaque condition et son résultat sont côte à côte.
SIERREUR - la formule de sécurité#
Rien de pire qu’un tableau rempli de #N/A ou #DIV/0!. Enveloppez vos formules sensibles :
=SIERREUR(RECHERCHEX(F2;A2:A500;C2:C500); "-")Si la recherche échoue, la cellule affiche un tiret propre au lieu d’un message d’erreur agressif.
Les fonctions de texte - nettoyer les données sales#
Si vous avez déjà reçu un export CSV avec des espaces partout, des noms en majuscules et des formats inconsistants, cette section est pour vous.
SUPPRESPACE et EPURAGE#
=SUPPRESPACE(A2)Supprime les espaces en trop (début, fin, doubles espaces). Indispensable après un import.
EPURAGE va plus loin et vire les caractères non imprimables qui se glissent parfois dans les exports de bases de données.
GAUCHE, DROITE, STXT - découper du texte#
=GAUCHE(A2; 3) // Les 3 premiers caractères
=DROITE(A2; 4) // Les 4 derniers caractères
=STXT(A2; 5; 8) // 8 caractères à partir du 5èmeCas concret : extraire le code département d’un code postal.
=GAUCHE(A2; 2) // "75" pour "75008"CONCAT et JOINDRE.TEXTE#
CONCAT remplace l’ancien CONCATENER :
=CONCAT(A2; " "; B2) // "Jean" + " " + "Dupont"JOINDRE.TEXTE est encore mieux pour assembler des plages :
=JOINDRE.TEXTE(", "; VRAI; A2:A10)Résultat : toutes les valeurs de A2 à A10 séparées par des virgules, en ignorant les cellules vides.
Les fonctions de date - arrêter de calculer à la main#
DATEDIF - la fonction cachée#
Excel ne la propose pas dans l’autocomplétion, mais elle existe et elle est précieuse :
=DATEDIF(A2; AUJOURDHUI(); "y") // Nombre d'années entre A2 et aujourd'hui
=DATEDIF(A2; AUJOURDHUI(); "m") // En mois
=DATEDIF(A2; B2; "d") // En jours entre deux datesParfait pour calculer l’ancienneté d’un salarié, la durée d’un contrat ou l’âge d’un client.
FIN.MOIS et NB.JOURS.OUVRES#
=FIN.MOIS(A2; 0) // Dernier jour du mois de la date en A2
=FIN.MOIS(A2; 3) // Dernier jour, 3 mois plus tard=NB.JOURS.OUVRES(A2; B2) // Jours ouvrés entre deux datesCette dernière exclut automatiquement les week-ends. Ajoutez un troisième paramètre avec une plage de jours fériés pour un calcul encore plus précis.
Les fonctions dynamiques - le futur d’Excel#
Ces fonctions sont apparues avec Microsoft 365 et changent profondément la façon de travailler.
FILTRE#
=FILTRE(A2:D500; B2:B500="Marketing")Renvoie automatiquement toutes les lignes où la colonne B vaut “Marketing”. Pas besoin de filtre manuel ni de tableau croisé. Le résultat se met à jour en temps réel.
TRIER#
=TRIER(A2:D500; 3; -1)Trie la plage par la 3ème colonne, en ordre décroissant (-1). Combiné avec FILTRE :
=TRIER(FILTRE(A2:D500; B2:B500="Marketing"); 3; -1)Filtrage + tri en une seule formule. Essayez de faire ça proprement avec les anciens outils.
UNIQUE#
=UNIQUE(B2:B500)Extrait les valeurs uniques d’une colonne. Très utile pour générer dynamiquement une liste de catégories, de départements ou de noms de clients sans doublons.
Mise en pratique : un tableau de bord en 5 formules#
Prenons un cas concret. Vous avez un onglet “Ventes” avec les colonnes : Date, Commercial, Région, Montant, Statut.
Dans un onglet “Dashboard”, vous pouvez construire :
// Chiffre d'affaires total du mois en cours
=SOMME.SI.ENS(Ventes!D:D; Ventes!E:E; "Signé"; Ventes!A:A; ">="&DEBUT.MOIS(AUJOURDHUI()))
// Top commercial
=INDEX(Ventes!B:B; EQUIV(MAX(Ventes!D:D); Ventes!D:D; 0))
// Nombre de deals par région (à côté d'une liste de régions)
=NB.SI.ENS(Ventes!C:C; G2; Ventes!E:E; "Signé")
// Liste des deals en attente, triés par montant
=TRIER(FILTRE(Ventes!A:D; Ventes!E:E="En attente"); 4; -1)Quatre formules, et vous avez un reporting qui se met à jour tout seul.
Pour aller plus loin : si vous voulez pousser vos compétences en analyse de données au-delà d’Excel, découvrez notre guide Python pour débutants - Python avec la bibliothèque pandas gère nativement ce qu’Excel fait difficilement au-delà de 100 000 lignes.
Les raccourcis qui vont avec#
Maîtriser les fonctions c’est bien, mais sans les raccourcis clavier associés, vous perdez du temps :
| Raccourci | Action |
|---|---|
| Ctrl + Shift + L | Activer/désactiver les filtres |
| Ctrl + T | Convertir en tableau structuré |
| Alt + = | Somme automatique |
| F4 | Basculer référence relative/absolue ($) |
| Ctrl + ; | Insérer la date du jour |
| Ctrl + Shift + : | Insérer l’heure actuelle |
| Ctrl + ` | Afficher les formules au lieu des résultats |
Par où commencer#
Inutile d’apprendre les 20 fonctions d’un coup. Voici une progression logique :
- Cette semaine : RECHERCHEX et SIERREUR - vous allez les utiliser immédiatement
- Semaine prochaine : SOMME.SI.ENS et NB.SI.ENS - elles remplacent beaucoup de manipulations manuelles
- Le mois prochain : FILTRE, TRIER, UNIQUE - le trio qui rend les tableaux croisés dynamiques obsolètes pour les cas simples
Le plus efficace, c’est de prendre un fichier sur lequel vous travaillez vraiment et de remplacer vos manipulations manuelles par ces formules, une par une. En trois semaines, vous aurez intégré l’essentiel.
