Référence Excel
Wiki Excel
Fonctions, formules, tableaux croisés, Power Query et VBA — la référence du tableur.
- fiches
- 76
- catégories
- 11
- Focus
- Tableur
Bases
Références, opérateurs et priorités, noms définis et codes d'erreur.
- Références de cellulesUne référence désigne une cellule par sa colonne et sa ligne (B4), ou un bloc par ses deux coins (B2:D20). C'est la base de toute formule : on calcule sur des cellules, pas sur des valeurs recopiées.
- Références absolues et mixtesLe signe $ empêche une partie de la référence de bouger quand on recopie la formule. Indispensable dès qu'une formule recopiée doit toujours lire la même cellule, comme un taux de TVA.
- Opérateurs de formuleLes opérateurs calculent (+, -, *, /, ^), assemblent du texte (&) et comparent (=, <>, <, >…). Connaître leur priorité évite des résultats faux qui ne déclenchent aucune erreur.
- Noms définisUn nom défini donne un libellé parlant à une cellule ou à une plage (TauxTVA au lieu de $E$1). Les formules se lisent comme des phrases et restent justes quand on les recopie.
- Erreurs courantesUn code d'erreur indique pourquoi une formule n'aboutit pas : valeur introuvable, référence détruite, division par zéro… Lire le code donne la piste de correction avant de penser à le masquer.
Maths & statistiques
Sommes, moyennes, comptages conditionnels, arrondis, classements et pourcentages.
- SOMMESOMME additionne des cellules, des plages ou des constantes. C'est le réflexe pour totaliser une colonne de montants, et elle ignore le texte au lieu de produire une erreur.
- MOYENNE et MEDIANEMOYENNE calcule la moyenne arithmétique des nombres d'une plage en ignorant les cellules vides et le texte. Elle résume un niveau typique : panier moyen, note moyenne, délai moyen.
- NB, NBVAL et NB.VIDETrois fonctions de comptage : NB compte les nombres, NBVAL toutes les cellules non vides, NB.VIDE les cellules vides. Elles mesurent la taille d'une liste ou son taux de remplissage.
- SOMME.SI, NB.SI et MOYENNE.SICes trois fonctions additionnent, comptent ou moyennent seulement les lignes qui respectent un critère. C'est la version à une condition, avant les fonctions .ENS qui en acceptent plusieurs.
- SOMME.SI.ENSSOMME.SI.ENS additionne les valeurs des lignes qui remplissent toutes les conditions données. C'est l'outil des tableaux de bord : CA par région et par mois, dépenses par service et par poste.
- NB.SI.ENSNB.SI.ENS compte les lignes qui remplissent toutes les conditions indiquées. Elle répond aux questions du type « combien de commandes livrées en retard ce mois-ci ? ».
- SOMMEPRODSOMMEPROD multiplie des plages ligne à ligne puis additionne les produits. Elle calcule un total de facture ou une moyenne pondérée en une formule, et accepte des conditions sur mesure.
- SOUS.TOTAL et AGREGATSOUS.TOTAL calcule un total qui suit les filtres : les lignes filtrées sont exclues. AGREGAT peut en plus ignorer les erreurs. Idéales sous un tableau que l'on filtre souvent.
- ARRONDI, ENT et TRONQUEARRONDI arrondit au plus proche avec le nombre de décimales voulu ; ENT et TRONQUE suppriment la partie décimale. On arrondit une TVA, un prix ou une durée pour éviter les écarts de centimes.
- MIN, MAX et valeurs de rangMIN et MAX renvoient la plus petite et la plus grande valeur d'une plage ; PETITE.VALEUR et GRANDE.VALEUR donnent la k-ième. Utiles pour un podium, un plafond ou un plancher.
- EQUATION.RANG (classement)EQUATION.RANG donne la place d'une valeur dans une liste : 1 pour la plus grande par défaut. On s'en sert pour classer des commerciaux, des produits ou des magasins selon leurs résultats.
- Calculer des pourcentagesUn pourcentage est un rapport affiché au format %. Trois calculs couvrent l'essentiel : la part d'un total, l'évolution entre deux périodes, l'application d'une hausse ou d'une remise.
Logique
SI, ET/OU, SI.CONDITIONS, SI.MULTIPLE, gestion des erreurs et tests EST.
- SISI renvoie une valeur quand un test est vrai et une autre quand il est faux. C'est la base de toute règle métier dans un tableau : remise, statut, alerte ou prime selon un seuil.
- ET, OU et NONET exige que toutes les conditions soient vraies, OU qu'au moins une le soit, et NON inverse un test. Placées dans un SI, elles traduisent des règles comme « cadre et plus de 5 ans ».
- SI imbriqués et SI.CONDITIONSSI.CONDITIONS teste plusieurs conditions dans l'ordre et renvoie la valeur de la première qui est vraie. Elle remplace les SI imbriqués pour les barèmes : tranches de prime, mentions, niveaux.
- SI.MULTIPLESI.MULTIPLE compare une seule expression à une liste de valeurs exactes et renvoie le résultat associé. C'est l'équivalent de SWITCH en anglais, idéal pour traduire un code en libellé.
- SIERREUR et SI.NON.DISPSIERREUR renvoie une valeur de secours quand une formule produit une erreur, et le résultat normal sinon. À réserver aux erreurs attendues, comme un code absent ou un objectif pas encore saisi.
- Fonctions EST (ESTVIDE, ESTNUM…)Les fonctions EST renvoient VRAI ou FAUX selon le contenu d'une cellule : vide, nombre, texte ou erreur. Elles servent de test dans un SI pour contrôler une saisie avant de calculer.
Recherche
RECHERCHEV, RECHERCHEX, INDEX, EQUIV et remodelage de tableaux.
- RECHERCHEVRECHERCHEV cherche une valeur dans la première colonne d'un tableau et renvoie l'information d'une autre colonne sur la même ligne. Elle reste présente dans d'innombrables classeurs existants.
- RECHERCHEXRECHERCHEX cherche une valeur dans une colonne et renvoie la ligne correspondante d'une autre, à gauche comme à droite. Elle remplace RECHERCHEV et gère les absences sans SIERREUR.
- INDEXINDEX renvoie la valeur située à un numéro de ligne et de colonne dans une plage. Seule, elle sert peu ; associée à EQUIV, elle devient l'un des outils de recherche les plus souples d'Excel.
- EQUIVEQUIV renvoie la position d'une valeur dans une ligne ou une colonne, par exemple 3 pour la 3e cellule. Elle fournit à INDEX le numéro de ligne ou de colonne à lire et vérifie qu'une valeur existe.
- INDEX + EQUIV (combo)INDEX+EQUIV combine deux fonctions : EQUIV trouve la ligne, INDEX lit la valeur dans la colonne voulue. Le duo cherche dans tous les sens et résiste aux insertions de colonnes.
- ASSEMB.V, CHOISIRCOLS, PRENDRE et EXCLURECes fonctions remodèlent un tableau par formule : ASSEMB.V empile des plages, CHOISIRCOLS garde certaines colonnes, PRENDRE et EXCLURE gardent ou retirent des lignes en tête ou en fin.
- DECALER et INDIRECTDECALER renvoie une plage décalée à partir d'une cellule de départ ; INDIRECT transforme un texte en référence. Utiles pour des plages variables, à réserver aux cas sans meilleure solution.
Texte
Extraction, découpage, assemblage, formatage et nettoyage des chaînes.
- GAUCHE, DROITE et STXTGAUCHE, DROITE et STXT extraient une partie d'un texte : les premiers caractères, les derniers ou un fragment à partir d'une position. Elles découpent des codes, des références ou des numéros.
- NBCARNBCAR compte les caractères d'un texte, espaces compris. On l'utilise pour contrôler la longueur d'une saisie (code postal, SIRET) ou pour calculer une position d'extraction.
- CHERCHE et TROUVECHERCHE et TROUVE renvoient la position d'un texte dans un autre. CHERCHE ignore la casse et accepte les jokers, TROUVE est strict. Le résultat alimente souvent GAUCHE ou STXT.
- CONCAT et JOINDRE.TEXTECONCAT assemble des textes ou des plages bout à bout ; JOINDRE.TEXTE insère un séparateur et peut sauter les cellules vides. Elles construisent des libellés, des adresses et des listes.
- TEXTE (formatage)TEXTE convertit un nombre ou une date en texte selon un format choisi. On l'emploie pour insérer une valeur bien présentée dans une phrase, un libellé ou un nom de fichier.
- SUPPRESPACE, MAJUSCULE et NOMPROPRESUPPRESPACE retire les espaces superflus ; MAJUSCULE, MINUSCULE et NOMPROPRE changent la casse. Ce sont les premiers réflexes pour nettoyer des données importées avant de les comparer.
- TEXTE.AVANT, TEXTE.APRES et FRACTIONNER.TEXTETEXTE.AVANT et TEXTE.APRES extraient ce qui précède ou suit un séparateur ; FRACTIONNER.TEXTE découpe un texte en plusieurs cellules. Plus besoin de calculer des positions avec CHERCHE.
Dates & heures
Calculs de dates, durées, échéances et jours ouvrés.
- AUJOURDHUI & MAINTENANTAUJOURDHUI() renvoie la date du jour et MAINTENANT() la date avec l'heure. Elles servent aux échéances, retards et anciennetés qui doivent se mettre à jour toutes seules.
- ANNEE / MOIS / JOURANNEE, MOIS et JOUR extraient chaque partie d'une date, et DATE la recompose. Ce duo sert à regrouper par période, à trouver un premier jour de mois ou à réparer des dates importées.
- DATEDIF (durées)DATEDIF compte les années, mois ou jours complets entre deux dates. C'est la fonction de référence pour un âge, une ancienneté ou la durée d'un contrat exprimée en années et en mois.
- JOURSEMJOURSEM renvoie le numéro du jour de la semaine d'une date. On l'utilise pour repérer les week-ends, planifier des tournées ou comparer les ventes selon le jour de la semaine.
- FIN.MOIS & MOIS.DECALERMOIS.DECALER avance ou recule une date d'un nombre de mois, et FIN.MOIS renvoie le dernier jour du mois visé. Elles construisent les échéanciers, clôtures et dates de paiement.
- NB.JOURS.OUVRES & SERIE.JOUR.OUVRENB.JOURS.OUVRES compte les jours ouvrés entre deux dates, et SERIE.JOUR.OUVRE ajoute n jours ouvrés à une date, fériés exclus. Elles calculent délais de livraison et dates d'échéance.
Données & tableaux
Tableaux structurés, tri, filtres, validation, protection et simulations.
- Tableaux structurésUn tableau structuré (Ctrl+L) transforme une plage en objet nommé qui s'agrandit tout seul. Les formules y citent les colonnes par leur nom, ce qui les rend lisibles et robustes aux ajouts.
- Tri (multi-niveaux)Le tri réordonne les lignes selon une ou plusieurs colonnes, chaque niveau départageant les égalités du précédent. Il sert à classer un fichier client, un planning ou un palmarès de ventes.
- Filtres (automatiques / avancés)Le filtre automatique masque les lignes qui ne répondent pas aux critères choisis dans les en-têtes ; le filtre avancé applique des critères complexes et peut copier le résultat ailleurs.
- Validation des donnéesLa validation des données limite ce qu'on peut saisir : liste déroulante, nombre borné, date, longueur de texte. Elle fiabilise un fichier partagé en refusant les saisies hors règles.
- Mise en forme conditionnelleLa mise en forme conditionnelle colore une cellule ou une ligne selon sa valeur : seuils, doublons, barres de données, icônes ou formule. Elle fait ressortir retards et dépassements d'un coup d'œil.
- Supprimer les doublonsSupprimer les doublons efface les lignes répétées selon les colonnes cochées. C'est l'outil de nettoyage rapide d'un fichier de contacts ; pour garder l'original intact, préférez UNIQUE.
- Protéger une feuille ou un classeurProtéger une feuille empêche de modifier ses cellules verrouillées, protéger le classeur fige ses onglets. Cela évite les fausses manipulations, mais ce n'est pas une sécurité contre un tiers.
- Valeur cible, scénarios & SolveurValeur cible trouve la valeur d'entrée qui donne le résultat voulu, le Solveur optimise plusieurs variables sous contraintes. Ils répondent à « quel prix pour atteindre l'équilibre ? ».
Matrices dynamiques
UNIQUE, TRIER, FILTRE, GROUPER.PAR, SEQUENCE, LET, LAMBDA et erreurs de débordement.
- UNIQUEUNIQUE renvoie les valeurs distinctes d'une plage dans une matrice qui déborde sur les cellules voisines et se met à jour seule. Idéale pour alimenter une liste déroulante ou une synthèse.
- TRIER & TRIERPARTRIER renvoie une copie triée d'une plage, recalculée à chaque changement, sans toucher à la source. TRIERPAR trie selon une autre colonne, même si elle n'apparaît pas dans le résultat.
- FILTREFILTRE extrait les lignes qui respectent une ou plusieurs conditions, dans une matrice recalculée en direct. C'est le filtre automatique en formule, idéal pour un extrait ou un tableau de bord.
- GROUPER.PAR & PIVOTER.PARGROUPER.PAR (GROUPBY) et PIVOTER.PAR (PIVOTBY) agrègent une table par catégorie en une formule, comme un TCD qui se recalcule seul. Idéales pour une synthèse toujours à jour dans un rapport.
- SEQUENCESEQUENCE génère une suite de nombres sur n lignes et colonnes, avec un départ et un pas. Elle numérote, crée des calendriers et sert de compteur aux autres formules de matrices dynamiques.
- LETLET donne un nom aux résultats intermédiaires d'une formule. Elle devient lisible et plus rapide, car un calcul réutilisé n'est évalué qu'une fois ; le dernier argument est le résultat.
- LAMBDALAMBDA transforme une formule en fonction personnalisée. Nommée dans le Gestionnaire de noms, elle s'appelle comme une fonction native et évite de recopier une logique complexe partout.
- #PROPAGATION!, #CALC!, opérateurs # et @Une matrice dynamique déborde sur les cellules voisines. Cette fiche explique ses erreurs, #PROPAGATION! et #CALC!, et les opérateurs # et @ qui citent ou réduisent une plage débordée.
Graphiques & restitution
Choisir le bon graphique et préparer une impression lisible.
- Choisir un graphiqueUn bon graphique répond à une question : comparer, montrer une évolution, une part ou une relation. Cette fiche aide à choisir le type adapté et à éviter les représentations trompeuses.
- Mise en page et impressionPréparer l'impression évite les pages coupées et les colonnes orphelines : zone d'impression, titres répétés, mise à l'échelle. Indispensable pour diffuser un rapport en PDF ou sur papier.
Tableaux croisés
Synthétiser et visualiser avec les TCD.
- Créer un tableau croisé dynamiqueUn tableau croisé dynamique (TCD) résume une grande table en quelques clics : totaux, moyennes et comptages par catégorie, sans formule. Il se construit de préférence sur un tableau structuré.
- Zones (Lignes / Colonnes / Valeurs / Filtres)Un TCD s'organise en quatre zones : Lignes et Colonnes forment les axes, Valeurs porte les calculs et Filtres restreint tout le rapport. Bien les répartir donne une synthèse lisible.
- Regroupement de datesDans un TCD, un champ de dates se regroupe par mois, trimestre ou année, et un champ numérique par tranches. Excel crée des niveaux que l'on déplie pour passer de l'année au mois.
- Champ calculéUn champ calculé ajoute au TCD une colonne issue d'une formule sur les champs existants, sans toucher à la source. Il affiche directement une marge, un taux ou un écart entre deux montants.
- Segments & chronologieLes segments sont des boutons de filtre visuels pour un TCD ou un tableau, et la chronologie fait de même pour les dates avec un curseur. Ils rendent un tableau de bord utilisable par tous.
- Graphique croisé dynamiqueLe graphique croisé dynamique représente un TCD et se réorganise avec lui. Associé à des segments et à une chronologie, il constitue la brique de base d'un tableau de bord interactif.
Power Query, DAX & automatisation
ETL, modèle de données, VBA, Office Scripts, Python dans Excel et Copilot.
- Power Query (importer / transformer)Power Query importe des données (fichiers, dossiers, bases, web), les nettoie par étapes enregistrées et les charge dans Excel. Une actualisation rejoue tout le nettoyage sur les nouvelles données.
- Langage MLe langage M est le code que Power Query écrit pour chaque requête. Le lire permet de corriger une étape, de paramétrer un chemin ou d'écrire une transformation absente de l'interface.
- Power Pivot & modèle de donnéesPower Pivot charge plusieurs tables dans un modèle de données et les relie comme une base relationnelle. Un seul TCD croise alors ventes, clients et calendrier sans aucune RECHERCHEV.
- DAX (mesures, CALCULATE)DAX est le langage des mesures du modèle de données : une mesure se recalcule selon les filtres du TCD. CALCULATE en est la clé, car elle évalue une expression sous des filtres modifiés.
- Macros (enregistreur et sécurité)L'enregistreur de macros traduit vos clics en code VBA rejouable. C'est la porte d'entrée de l'automatisation pour une mise en forme ou un nettoyage répété chaque semaine.
- VBA (Sub / Range / Cells)VBA est le langage de programmation d'Excel, édité avec Alt+F11. Une procédure Sub enchaîne des instructions sur des cellules (Range, Cells) pour automatiser un traitement récurrent.
- Office ScriptsOffice Scripts automatise Excel en TypeScript, à commencer par Excel pour le web où VBA ne s'exécute pas. Un script s'enregistre ou s'écrit, se partage et se lance depuis Power Automate.
- Python dans Excel (PY)Python dans Excel exécute du code Python (pandas, Matplotlib) dans une cellule grâce à la fonction PY. Utile pour une analyse ou un graphique hors de portée des formules, sans installer Python.
- Copilot dans ExcelCopilot dans Excel propose formules, colonnes calculées, mises en forme et analyses à partir d'une demande en français. Il fait gagner du temps, à condition de vérifier chaque résultat.