Excel pour les Data Analysts
Les formules et techniques Excel avancées pour manipuler, analyser et visualiser tes données (2026)
En data analyse, Excel est souvent sous-estimé par rapport à Python, SQL ou Tableau. Mais la réalité, c'est que tu reçois des fichiers Excel tous les jours : exports CRM, données marketing, rapports financiers, fichiers CSV de production. Et avant de charger les données dans un outil avancé, tu fais un premier nettoyage, une exploration rapide et une validation dans Excel. Pour un dataset de 5 000 lignes qu'on te demande d'analyser 'pour cet après-midi', ouvrir Excel est souvent plus rapide qu'écrire un script Python. Excel reste ton couteau suisse pour l'analyse exploratoire et le prototypage.
Le vrai pouvoir d'Excel pour un data analyst, ce sont les formules matricielles dynamiques (FILTRE, UNIQUE, TRIER), les combinaisons INDEX/EQUIV pour les recherches complexes, et surtout Power Query pour l'ETL. Avec ces outils, tu nettoies, transformes et analyses des datasets de 100 000+ lignes directement dans Excel, sans écrire une seule ligne de code. La plupart des data analysts qui débutent ne connaissent que RECHERCHEV et les TCD, alors qu'Excel 365 offre des fonctionnalités qui rivalisent avec pandas pour 80% des cas d'usage courants.
Ce guide te présente les 10 formules les plus utiles pour un data analyst, avec un focus sur les fonctions avancées et les nouvelles formules dynamiques d'Excel 365. Chaque formule est illustrée avec des cas concrets d'analyse : extraction de campagnes marketing, segmentation de clients, agrégation de données de conversion, nettoyage de datasets hétérogènes. Des techniques de pro avec des données réalistes, pas des bases de débutant.
Les 10 formules indispensables pour les Data Analysts
1. INDEX - Extraire des données par coordonnées
INDEX est la pièce maîtresse de toute extraction dynamique. Combinée avec EQUIV, elle remplace RECHERCHEV avec bien plus de flexibilité : recherche vers la gauche, critères multiples, valeur au croisement ligne/colonne. Par exemple, tu veux trouver la campagne qui a généré le plus de conversions dans un dataset de 10 000 lignes. Avec INDEX/EQUIV/MAX, tu obtiens le nom en une seule formule, sans trier ni filtrer. C'est la formule que tout data analyst devrait maîtriser avant toutes les autres. Astuce : pour une recherche multi-critères, concatène les colonnes de critères dans EQUIV avec le séparateur '|' pour créer une clé composite.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | ID | Conversions | Campagne | Source |
| 2 | 001 | 245 | Newsletter Q1 | |
| 3 | 002 | 512 | Campagne SEO | |
| 4 | 003 | 189 | Social Ads FR | |
| 5 | 004 | 334 | Retargeting | |
| 6 | 005 | 128 | Affiliation | Partenaire |
| 7 | 006 | 467 | Google Ads | |
| 8 | 007 | 95 | LinkedIn Ads | |
| 9 | 008 | 210 | Newsletter Q2 | |
| 10 | 009 | 378 | Social Ads BE |
=INDEX(C2:C10;EQUIV(MAX(B2:B10);B2:B10;0))2. EQUIV - Localiser une valeur dans une plage
EQUIV renvoie la position d'une valeur dans une plage. Seule, elle sert à vérifier l'existence d'une valeur dans un dataset (si EQUIV retourne un nombre, la valeur existe). Avec INDEX, elle forme le duo le plus puissant d'Excel pour les recherches complexes. Le troisième argument (0 pour exact, 1 pour approché, -1 pour approché décroissant) te donne le contrôle total sur le type de correspondance. En data analyse, EQUIV est particulièrement utile pour localiser la position d'un maximum, d'un minimum ou d'une valeur cible dans une série triée. Astuce : =SIERREUR(EQUIV(valeur;plage;0);"Non trouvé") te permet de vérifier proprement si une valeur existe.
3. RECHERCHEX - La recherche moderne et polyvalente
RECHERCHEX remplace RECHERCHEV et RECHERCHEH avec une syntaxe plus claire et plus puissante. Elle cherche dans n'importe quelle direction (vers la gauche, vers le haut), gère les valeurs non trouvées avec un message personnalisé au lieu d'afficher #N/A, et supporte les correspondances approximatives triées en ordre décroissant. En data analyse, tu l'utilises pour enrichir un dataset en allant chercher des informations dans une table de référence : ajouter un nom de pays à partir d'un code ISO, compléter un CA à partir d'un identifiant client. Si tu as Excel 365, c'est ta formule de recherche par défaut. Astuce : le 5e argument te permet de chercher depuis la fin, utile pour trouver la dernière occurrence d'une valeur.
| A | B | C | |
|---|---|---|---|
| 1 | Région | Code | CA |
| 2 | France | FR | 1 250 000 |
| 3 | Belgique | BE | 340 000 |
| 4 | Suisse | CH | 480 000 |
| 5 | Luxembourg | LU | 95 000 |
| 6 | Canada | CA | 210 000 |
| 7 | Maroc | MA | 125 000 |
| 8 | Tunisie | TN | 68 000 |
| 9 | Allemagne | DE | 520 000 |
| 10 | Espagne | ES | 185 000 |
=RECHERCHEX("FR";B2:B10;C2:C10;"Non trouvé")4. FILTRE - Extraire un sous-ensemble dynamique
FILTRE extrait les lignes qui correspondent à un critère, et le résultat se déverse automatiquement sur plusieurs cellules. Tu filtres un dataset de 10 000 lignes pour ne garder que les lignes où le statut est 'Actif' et le CA > 1000, sans toucher aux données source. C'est le SELECT WHERE d'Excel. La beauté de FILTRE, c'est que le résultat est dynamique : si tu ajoutes des lignes qui correspondent aux critères, elles apparaissent automatiquement dans l'extraction. Sans FILTRE, tu copies-colles les résultats d'un filtre automatique, et c'est à refaire à chaque mise à jour. Astuce : combine FILTRE avec TRIER pour obtenir un résultat trié par conversions décroissantes.
| A | B | C | |
|---|---|---|---|
| 1 | Campagne | Conversions | CPA |
| 2 | Newsletter Q1 | 245 | 12,50 |
| 3 | Campagne SEO | 512 | 8,20 |
| 4 | Social Ads FR | 189 | 22,00 |
| 5 | Retargeting | 334 | 15,40 |
| 6 | Affiliation | 128 | 18,90 |
| 7 | Google Ads | 467 | 11,30 |
| 8 | LinkedIn Ads | 95 | 35,00 |
| 9 | Newsletter Q2 | 210 | 10,80 |
| 10 | Social Ads BE | 378 | 19,50 |
=FILTRE(A2:C10;B2:B10>300)5. UNIQUE - Extraire les valeurs distinctes
UNIQUE extrait les valeurs uniques d'une colonne, c'est l'équivalent du SELECT DISTINCT en SQL. Tu reçois un export de 50 000 transactions et tu veux la liste des catégories de produits, des sources de trafic ou des pays clients ? UNIQUE te les donne en une formule, et la liste se met à jour automatiquement quand les données changent. Par exemple, tu analyses un fichier de conversions avec 8 000 lignes et 12 sources différentes : UNIQUE te donne la liste des 12 sources en une cellule. Combinée avec FILTRE et TRIER, tu construis des vues dynamiques sans TCD. Astuce : =NB.VAL(UNIQUE(plage)) te donne le nombre de valeurs distinctes, l'équivalent du COUNT(DISTINCT).
6. TRIER - Trier dynamiquement sans toucher aux données
TRIER crée une copie triée de tes données sans modifier la source. Tu peux trier par n'importe quelle colonne, en ordre croissant ou décroissant. Par exemple, =TRIER(FILTRE(données;source="Google");2;-1) te donne toutes les lignes Google triées par conversions décroissantes, sans toucher au dataset original. Combinée avec FILTRE et UNIQUE, elle te permet de construire des vues dynamiques sur tes données, comme des requêtes SQL dans Excel. Le trio FILTRE+TRIER+UNIQUE est le remplacement moderne du TCD pour les analyses simples et reproductibles. Astuce : utilise le 3e argument (-1) pour un tri décroissant.
7. SOMME.SI.ENS - Agrégation multi-critères
SOMME.SI.ENS est ta fonction d'agrégation quand tu n'as pas de TCD sous la main ou que tu veux une formule reproductible. Elle totalise les valeurs qui respectent plusieurs conditions : source + période + pays. C'est le GROUP BY + SUM d'Excel, et elle fonctionne même sur d'anciennes versions sans les formules dynamiques. En data analyse, tu l'utilises pour construire des tableaux croisés manuels avec des totaux par segment. Par exemple, le CA total des clients français qui ont acheté en mars via Google. Astuce : pour une moyenne conditionnelle, utilise MOYENNE.SI.ENS au lieu de diviser SOMME.SI.ENS par NB.SI.ENS.
8. NB.SI.ENS - Comptage multi-critères
NB.SI.ENS compte les lignes qui correspondent à plusieurs critères simultanément. C'est le COUNT(*) WHERE du data analyst. Combien de transactions de plus de 100 euros, en France, au mois de mars ? Combien de clients actifs avec un panier moyen supérieur à 50 euros ? Une seule formule au lieu d'un filtre multicritère qu'il faudrait refaire à chaque analyse. En data analyse, NB.SI.ENS est aussi utile pour les contrôles de qualité : combien de lignes ont une date vide et un montant non nul (signe d'un problème dans les données) ? Astuce : utilise des critères avec des opérateurs (">100", "<>0") pour des conditions numériques.
9. SIERREUR - Gérer les erreurs proprement
SIERREUR intercepte les erreurs (#N/A, #DIV/0!, #REF!) et les remplace par une valeur de ton choix. En data analyse, les données sont rarement propres : des RECHERCHEV qui ne trouvent pas de correspondance, des divisions par zéro quand un segment n'a pas de données, des références cassées après un import. SIERREUR te permet de construire des formules robustes qui ne plantent pas et qui affichent un résultat exploitable dans tous les cas. Par exemple, =SIERREUR(ventes/visites;0) renvoie 0 au lieu de #DIV/0! quand il n'y a pas de visites. Astuce : en Excel 365, préfère SI.NON.DISP pour ne capturer que les erreurs #N/A et laisser les autres erreurs visibles (elles signalent souvent un vrai problème).
10. SOMMEPROD - Calculs conditionnels avancés
SOMMEPROD multiplie des plages élément par élément et additionne les résultats. C'est l'arme secrète du data analyst pour les calculs conditionnels complexes que SOMME.SI.ENS ne peut pas faire : moyennes pondérées (prix moyen pondéré par le volume), comptages avec conditions dynamiques basées sur des formules, et même des corrélations simples entre deux séries. Par exemple, =SOMMEPROD((source="Google")*(mois="Mars")*CA) totalise le CA Google de mars en une formule matricielle. Plus ancien que FILTRE mais compatible avec toutes les versions d'Excel, ce qui en fait un choix sûr quand tu partages des fichiers avec des collègues qui n'ont pas Excel 365.
Ta fiche mémo est prête
Nous avons résumé les formules et raccourcis essentiels aux Data Analysts dans 1 PDF. Imprime-le et garde-le à côté de ton écran !
Télécharger le PDF gratuitLes fonctionnalités Excel clés pour les Data Analysts
Power Query (ETL intégré)
Importe, nettoie et transforme tes données sans écrire de code. Connexion à des fichiers CSV, des bases de données SQL, des API REST et des dossiers entiers de fichiers. Les étapes de transformation (supprimer les doublons, pivoter, fusionner, changer les types) sont enregistrées et rejouables en un clic. C'est le pandas d'Excel, avec une interface graphique et la reproductibilité en prime.
Tableaux Croisés Dynamiques (TCD)
Le TCD est l'outil d'exploration le plus rapide pour un data analyst. Glisse-dépose les champs pour croiser n'importe quelles dimensions, ajoute des mesures calculées (somme, moyenne, pourcentage du total), filtre par segments ou par timeline. En 30 secondes, tu passes d'un dataset brut à un tableau croisé source x pays x mois. Plus rapide qu'écrire une requête SQL pour une analyse exploratoire, et tu peux changer de perspective en temps réel.
Formules matricielles dynamiques
FILTRE, TRIER, UNIQUE, SEQUENCE, ASSEMB.V, ASSEMB.H : les formules dynamiques d'Excel 365 transforment Excel en outil d'analyse moderne. Les résultats se déversent automatiquement sur plusieurs cellules, plus besoin de Ctrl+Shift+Entrée. Tu peux chaîner les formules pour construire des pipelines de transformation complets en une seule cellule, comme un enchaînement de fonctions en Python.
Power Pivot et modèle de données
Crée des relations entre plusieurs tables (comme des JOIN en SQL), ajoute des mesures DAX pour des calculs avancés (year-over-year, running totals, pourcentages cumulés) et construis des rapports sur des millions de lignes sans ralentissement. C'est le pont entre Excel et la BI professionnelle (Power BI), avec la même syntaxe DAX.
Comparaisons de formules
RECHERCHEV vs RECHERCHEX
RECHERCHEX remplace RECHERCHEV mais n'est pas disponible partout.
INDEX vs RECHERCHEV
INDEX/EQUIV est plus flexible que RECHERCHEV, mais plus complexe à écrire.
NB.SI vs NB.SI.ENS
NB.SI.ENS gère plusieurs critères là où NB.SI n'en accepte qu'un seul.
SOMME.SI vs SOMME.SI.ENS
SOMME.SI.ENS permet de sommer avec plusieurs critères simultanément.
EQUIV vs CHERCHE
EQUIV trouve la position d'une valeur dans une plage, CHERCHE trouve la position d'un texte dans une chaine.
NB.SI vs FILTRE
NB.SI compte les lignes qui matchent, FILTRE les extrait toutes dans un tableau.
Questions fréquentes
Excel est-il encore pertinent pour un data analyst ?
Oui, à 100%. Même les data analysts qui codent en Python ou SQL utilisent Excel quotidiennement. Pour les explorations rapides, les petits datasets, les présentations à des non-techniques et le prototypage d'analyses, Excel est souvent plus rapide. Power Query + TCD + formules dynamiques en font un outil très puissant.
Quelles formules Excel avancées pour la data analyse ?
INDEX/EQUIV (recherches flexibles), RECHERCHEX (recherche moderne), FILTRE + UNIQUE + TRIER (manipulation dynamique), SOMME.SI.ENS et NB.SI.ENS (agrégations), SOMMEPROD (calculs conditionnels avancés) et SIERREUR (gestion d'erreurs). Avec Power Query pour l'ETL, tu couvres 90% des besoins d'analyse.
Power Query ou Python pour le nettoyage de données ?
Power Query est plus accessible et suffisant pour 80% des cas : imports, jointures, pivots, nettoyage de texte, transformations de dates. Python (pandas) prend le relais pour les traitements complexes, les boucles, les API et les datasets de plusieurs millions de lignes. Beaucoup de data analysts utilisent les deux.
Comment analyser un gros fichier (100 000+ lignes) dans Excel ?
Charge les données via Power Query (pas de copier-coller). Utilise un TCD pour l'exploration. Évite les RECHERCHEV sur 100 000 lignes (préfère INDEX/EQUIV ou RECHERCHEX). Active le modèle de données pour les calculs inter-tables. Et travaille en .xlsx, pas en .xls (la limite à 65 536 lignes, c'est fini).
RECHERCHEV ou INDEX/EQUIV pour un data analyst ?
INDEX/EQUIV est objectivement plus puissant : recherche vers la gauche, critères multiples (avec EQUIV sur des colonnes concaténées), et meilleures performances sur les gros volumes. RECHERCHEX (Excel 365) combine le meilleur des deux. Mais RECHERCHEV reste utile pour les cas simples et la lisibilité.
Comment nettoyer un dataset dans Excel avant analyse ?
Commence par Power Query pour les transformations lourdes : supprimer les doublons, standardiser les formats de date, fusionner les colonnes. Dans la feuille, utilise SUPPRESPACE pour enlever les espaces, NOMPROPRE pour normaliser les noms, et SUBSTITUE pour corriger les caractères parasites. SIERREUR enveloppe tes formules pour gérer les valeurs manquantes. NB.VIDE te donne le taux de complétude par colonne. Travaille toujours sur une copie, jamais sur les données brutes.
Comment créer un dashboard interactif dans Excel ?
Utilise les TCD comme moteur de données et les segments (slicers) pour le filtrage interactif. Connecte plusieurs TCD aux mêmes segments pour synchroniser les filtres sur tout le dashboard. Ajoute des graphiques liés aux TCD qui se mettent à jour quand tu cliques sur un segment. Pour les KPI en haut de page, utilise des SOMME.SI.ENS qui pointent vers les données source. Garde un onglet pour les données, un pour les TCD, et un pour le dashboard visible.
Découvre aussi Excel pour les...
Envie de passer au niveau supérieur en data analyse ?
Rejoins Le Dojo Club pour accéder à des formations complètes, des cas pratiques d'analyse de données et une communauté de data analysts qui partagent leurs techniques.
Essayer pendant 30 jours