Fonctions Excel

Créer une formule

Une formule commence toujours par =. Vient ensuite un calcul avec des cellules, comme =B2*C2, ou une fonction, comme =SOMME(B2:B6). Si un nombre change, Excel recalcule aussitôt.

Recopier des formules

Faites glisser la poignée de recopie, le petit carré en bas à droite de la cellule, vers le bas, et Excel adapte la formule à chaque ligne : =B2*C2 devient =B3*C3. Un double-clic va encore plus vite.

Ordre des opérations

Excel calcule d'abord ce qui est entre parenthèses, puis les puissances, puis les multiplications et divisions, et seulement ensuite les additions et soustractions.

Références absolues ($)

Un $ fige une partie d'une référence, pour qu'elle ne se décale pas quand vous recopiez une formule. $A$1 reste toujours A1. F4 passe d'une forme à l'autre.

Calculer des pourcentages

Pour Excel, 20 % n'est rien d'autre que 0,2. Un pourcentage d'un montant s'écrit =montant*20%, et le montant augmenté de 20 % =montant*(1+20%).

Travailler avec plusieurs feuilles

Pour utiliser une cellule d'une autre feuille, écrivez le nom de la feuille, un point d'exclamation et la cellule : =Prix!B2. =SOMME(Janv:Mars!B2) additionne la même cellule sur plusieurs feuilles.

Mettre en forme les nombres

Un format de nombre change l'aspect d'un nombre (montant, pourcentage ou date) sans changer le nombre lui-même. Vous le choisissez dans l'onglet Accueil ou avec Ctrl + 1.

SOMME

En anglais : SUM

SOMME additionne tout ce que vous sélectionnez, par exemple =SOMME(C2:C6). Le texte et les cellules vides sont simplement ignorés.

Soustraire, multiplier, diviser

Pour soustraire, multiplier ou diviser dans Excel, vous n'utilisez pas de fonction mais un opérateur : =A2-B2, =A2*B2 et =A2/B2. Seule l'addition a sa fonction, SOMME.

MOYENNE

En anglais : AVERAGE

MOYENNE donne la moyenne de ce que vous sélectionnez. Une cellule vide ne compte pas, un 0 si.

MIN et MAX

MIN donne la plus petite et MAX la plus grande valeur d'une série de nombres.

ABS, PUISSANCE et RACINE

En anglais : ABS, POWER, SQRT

ABS donne un nombre sans son signe moins, PUISSANCE élève un nombre à une puissance et RACINE donne la racine carrée.

NB et NBVAL

En anglais : COUNT, COUNTA

NB compte les cellules qui contiennent un nombre. NBVAL compte toutes les cellules non vides, texte compris.

NB.SI

En anglais : COUNTIF

NB.SI vous dit combien de cellules correspondent à ce que vous demandez, par exemple =NB.SI(A2:A6;"Lyon").

NB.SI.ENS

En anglais : COUNTIFS

NB.SI.ENS compte les lignes qui remplissent plusieurs conditions à la fois. Pour chaque condition, vous donnez une plage et une valeur.

SOMME.SI

En anglais : SUMIF

SOMME.SI n'additionne que les lignes voulues : d'abord où chercher, puis quoi chercher, puis quoi additionner.

SOMME.SI.ENS

En anglais : SUMIFS

SOMME.SI.ENS additionne avec plusieurs conditions. La plage à additionner vient en premier, suivie des paires de plage et de critère.

MOYENNE.SI

En anglais : AVERAGEIF

MOYENNE.SI calcule la moyenne des seules lignes qui remplissent une condition, comme le CA moyen de Lyon.

MAX.SI.ENS et MIN.SI.ENS

En anglais : MAXIFS

MAX.SI.ENS donne le plus grand nombre parmi les seules lignes qui remplissent vos conditions, comme le CA le plus élevé de Lyon. MIN.SI.ENS donne le plus petit.

Caractères génériques

Avec * vous cherchez zéro, un ou plusieurs caractères, et avec ? exactement un. "PC*" trouve tout ce qui commence par PC.

VRAI et FAUX

Une comparaison comme =B2>100 donne VRAI ou FAUX. Vous utilisez ce résultat dans SI, dans NB.SI et pour filtrer.

SI

En anglais : IF

Avec SI, Excel choisit : si le test est vrai, vous obtenez un résultat, sinon l'autre. Par exemple =SI(C2>500;"Haut";"Bas").

SI.CONDITIONS

En anglais : IFS

SI.CONDITIONS vérifie une série de conditions l'une après l'autre et donne le résultat de la première qui est vraie. Vous évitez ainsi les SI imbriqués.

ET et OU

En anglais : AND, OR, NOT

ET donne VRAI si toutes les conditions sont remplies. OU donne VRAI si au moins une l'est. On les utilise le plus souvent dans SI.

SIERREUR

En anglais : IFERROR

SIERREUR affiche une valeur de votre choix quand une formule donne une erreur, et sinon simplement le résultat.

ESTNUM et ESTVIDE

En anglais : ISNUMBER, ISBLANK

ESTNUM donne VRAI si une cellule contient un nombre, ESTVIDE si elle est vide. On les utilise surtout dans SI, pour vérifier avant de calculer.

RECHERCHEV

En anglais : VLOOKUP

Avec RECHERCHEV, Excel cherche dans un tableau : il trouve votre code à gauche et lit le prix ou le nom à côté. Terminez par FAUX pour une correspondance exacte.

RECHERCHEH

En anglais : HLOOKUP

RECHERCHEH cherche une valeur dans la première ligne d'un tableau et donne ce qui se trouve plus bas dans la même colonne. Pratique quand vos données sont côte à côte, comme des mois.

RECHERCHEX

En anglais : XLOOKUP

Avec RECHERCHEX, vous indiquez où Excel doit chercher et où se trouve la réponse. Elle fonctionne dans les deux sens et cherche une correspondance exacte par défaut. Il faut Excel 2021 ou 365.

INDEX et EQUIV

En anglais : INDEX, MATCH

EQUIV vous donne la position d'une valeur, et INDEX va chercher ce qui se trouve à cette position. Ensemble, elles cherchent dans tous les sens, dans toutes les versions d'Excel.

EQUIVX

En anglais : XMATCH

EQUIVX donne la position d'une valeur dans une liste, comme EQUIV, mais cherche par défaut une correspondance exacte et peut commencer par le bas. Fonctionne dans Excel 2021 et 365.

GAUCHE, DROITE et STXT

En anglais : LEFT, RIGHT, MID

GAUCHE et DROITE prennent des caractères au début ou à la fin d'un texte. STXT prend un morceau au milieu.

SUPPRESPACE

En anglais : TRIM

SUPPRESPACE supprime les espaces au début et à la fin, et remplace les espaces doubles au milieu par des simples.

NBCAR

En anglais : LEN

NBCAR compte le nombre de caractères d'une cellule, espaces et signes de ponctuation compris.

MAJUSCULE, MINUSCULE et NOMPROPRE

En anglais : UPPER, LOWER, PROPER

MAJUSCULE met un texte en majuscules, MINUSCULE en minuscules, et NOMPROPRE met une majuscule au début de chaque mot.

CHERCHE et TROUVE

En anglais : SEARCH, FIND

Les deux fonctions donnent la position d'un texte dans un autre texte. CHERCHE ignore les majuscules et connaît les caractères génériques, TROUVE non.

Assembler du texte

En anglais : CONCAT, TEXTJOIN

Assemblez des cellules avec le signe &, par exemple =A2&" "&B2. Pour de longues listes, utilisez JOINDRE.TEXTE.

SUBSTITUE

En anglais : SUBSTITUTE, REPLACE

SUBSTITUE remplace un morceau de texte par un autre, partout où il apparaît.

TEXTE.AVANT et TEXTE.APRES

En anglais : TEXTBEFORE, TEXTAFTER

TEXTE.AVANT donne tout ce qui précède un délimiteur et TEXTE.APRES tout ce qui le suit. Disponible dans Excel 365.

FRACTIONNER.TEXTE

En anglais : TEXTSPLIT

FRACTIONNER.TEXTE répartit un texte sur des colonnes ou des lignes, à chaque séparateur. Une seule formule, et les morceaux se propagent tout seuls. Disponible dans Excel 365 et 2024.

TEXTE

En anglais : TEXT

Avec TEXTE, vous décidez de l'aspect d'un nombre ou d'une date dans un texte, par exemple =TEXTE(A1;"jj/mm/aaaa").

CNUM

En anglais : VALUE

CNUM transforme un nombre stocké comme texte en vrai nombre, pour que vous puissiez calculer avec.

ARRONDI

En anglais : ROUND

Avec ARRONDI, vous choisissez combien de décimales il reste. Le format ne change que l'affichage, ARRONDI change la valeur elle-même.

PLAFOND et PLANCHER

En anglais : CEILING, FLOOR

PLAFOND arrondit au multiple supérieur, comme un quart d'heure ou un carton de 6. PLANCHER arrondit au multiple inférieur.

MOD et ENT

En anglais : MOD, INT

ENT arrondit un nombre à l'entier inférieur, et MOD donne le reste d'une division. Ensemble, elles donnent une division avec reste.

GRANDE.VALEUR et PETITE.VALEUR

En anglais : LARGE, SMALL

GRANDE.VALEUR donne la deuxième, la troisième ou la n-ième plus grande valeur. PETITE.VALEUR fait de même par le bas. Idéal pour un top 3.

MEDIANE et MODE

En anglais : MEDIAN, MODE

MEDIANE donne la valeur du milieu et MODE la valeur qui revient le plus souvent. Les deux sont moins sensibles aux valeurs extrêmes que la moyenne.

SEQUENCE et ALEA.ENTRE.BORNES

En anglais : SEQUENCE, RANDBETWEEN

SEQUENCE crée d'un coup une suite de nombres, comme 1 à 10. ALEA.ENTRE.BORNES donne un nombre entier au hasard entre deux bornes.

VPM, VA et VC

En anglais : PMT, PV, FV

VPM donne la mensualité d'un emprunt, VA combien vous pouvez emprunter avec un montant fixe par mois, et VC ce que vaut votre épargne après un certain temps.

DATE

DATE construit une vraie date à partir d'une année, d'un mois et d'un jour. L'ordre est toujours année, mois, jour.

ANNEE, MOIS et JOUR

En anglais : YEAR, MONTH, DAY

ANNEE, MOIS et JOUR extraient l'année, le mois ou le jour d'une date. Avec JOURSEM et NO.SEMAINE.ISO, vous trouvez le jour de la semaine et le numéro de semaine.

AUJOURDHUI

En anglais : TODAY

AUJOURDHUI donne la date du jour et se met à jour toute seule. Les parenthèses restent vides.

DATEDIF

DATEDIF calcule l'écart entre deux dates en années, en mois ou en jours, par exemple =DATEDIF(A2;B2;"Y") pour un âge.

NB.JOURS.OUVRES

En anglais : NETWORKDAYS, WORKDAY

NB.JOURS.OUVRES compte les jours ouvrés entre deux dates. Les week-ends ne comptent pas et vous ajoutez vous-même les jours fériés.

Calculer avec les heures

Pour Excel, une heure est une fraction de journée : 12:00 vaut 0,5. Vous calculez ainsi combien de temps quelqu'un a travaillé, avec =C2-B2, et avec *24 vous en faites des heures.

TEMPS, HEURE et MINUTE

En anglais : TIME, HOUR, MINUTE

TEMPS crée une heure à partir d'heures, de minutes et de secondes. HEURE et MINUTE en extraient ces parties, comme l'heure 17 de 17:45.

Trier et filtrer

Trier change l'ordre de tout le tableau, filtrer masque des lignes pour un temps. Cliquez dans votre tableau et utilisez les boutons de l'onglet Données.

SOUS.TOTAL

En anglais : SUBTOTAL

SOUS.TOTAL ne calcule qu'avec les lignes visibles après un filtre. Le numéro 9 additionne, 1 donne la moyenne et 3 compte les cellules remplies.

Créer un graphique

Sélectionnez vos libellés et vos nombres avec leurs en-têtes, puis choisissez Insertion > Graphiques. Une courbe montre une tendance, des colonnes comparent, un secteur montre les parts d'un tout.

Créer un tableau croisé dynamique

Un tableau croisé dynamique transforme une longue liste en synthèse par groupe. Cliquez dans vos données, choisissez Insertion > Tableau croisé dynamique et faites glisser les champs vers Lignes et Valeurs.

Travailler avec des tableaux

Transformez votre liste en tableau Excel avec Insertion > Tableau. Il s'agrandit avec les nouvelles lignes, reçoit des boutons de filtre et une ligne des totaux, et vos formules utilisent les noms de colonnes.

Mise en forme conditionnelle

Avec la mise en forme conditionnelle, Excel colore une cellule de lui-même quand elle remplit une règle, comme rouge sous 0. Si la valeur change, la couleur change aussi. Elle se trouve dans l'onglet Accueil.

Plages nommées

Donnez un nom à une cellule ou à une plage, comme Taux ou Points, et utilisez ce nom dans vos formules : =SOMME(Points) au lieu de =SOMME(C2:C6). C'est plus lisible, et un nom ne se décale jamais.

Créer une liste déroulante

Avec une liste déroulante, vous choisissez la valeur d'une cellule dans une liste au lieu de la taper. Vous évitez ainsi les fautes de frappe. Elle se crée dans Données > Validation des données > Liste.

Figer des lignes et des colonnes

Avec Figer les volets, votre ligne de titres ou votre première colonne reste en place pendant que vous faites défiler. Vous le trouvez dans l'onglet Affichage.

Collage spécial

Avec le collage spécial, vous ne collez que ce dont vous avez besoin : les valeurs sans formules, seulement la mise en forme, ou les lignes et colonnes inversées. Appuyez sur Ctrl + Alt + V après avoir copié.

Imprimer une feuille

Regardez d'abord l'aperçu dans Fichier > Imprimer, ou avec Ctrl + P. Dans l'onglet Mise en page, choisissez le mode paysage, une zone d'impression et une ligne de titres répétée sur chaque page.

Convertir le texte en colonnes

Avec Données > Convertir, vous répartissez une colonne sur plusieurs colonnes, à chaque espace, virgule ou point-virgule. Pratique pour des noms ou un export collé.

Remplissage instantané

Le remplissage instantané complète une colonne d'après l'exemple que vous tapez, comme le prénom tiré d'un nom complet. Vous le trouvez dans Données, ou avec Ctrl + E.

Supprimer les doublons

Avec Données > Supprimer les doublons, vous retirez les lignes qui figurent plus d'une fois dans votre liste. Vous choisissez les colonnes qu'Excel compare.

Rechercher et remplacer

Avec Accueil > Rechercher et sélectionner > Remplacer, ou Ctrl + H, vous modifiez un mot ou un caractère dans toutes les cellules à la fois. Pour chercher sans remplacer : Ctrl + F.

UNIQUE

UNIQUE donne une liste sans doublons. Vous tapez la formule dans une seule cellule et le résultat se propage tout seul. Disponible dans Excel 2021 et 365.

TRIER

En anglais : SORT

TRIER donne une copie triée de vos données, qui se met à jour quand la source change. Disponible dans Excel 2021 et 365.

FILTRE

En anglais : FILTER

FILTRE récupère toutes les lignes que vous voulez voir, alors que RECHERCHEV s'arrête à la première. Il faut Excel 2021 ou 365.

SOMMEPROD

En anglais : SUMPRODUCT

SOMMEPROD multiplie des colonnes ligne par ligne et additionne les résultats. Pratique pour un total sans colonne auxiliaire et pour une moyenne pondérée.

CHOISIRCOLS

En anglais : CHOOSECOLS

CHOISIRCOLS n'affiche que les colonnes d'un tableau que vous choisissez, dans l'ordre que vous voulez. Le résultat se propage et se met à jour tout seul. Disponible dans Excel 365 et 2024.

LET

Avec LET, vous donnez un nom à un résultat intermédiaire dans votre formule. Excel le calcule une seule fois, et une longue formule devient plus lisible. Fonctionne dans Excel 2021 et 365.

#VALEUR!

#VALEUR! signifie qu'une formule reçoit le mauvais type de données, le plus souvent du texte là où Excel attend un nombre.

#N/A

#N/A signifie « non disponible » : une fonction de recherche comme RECHERCHEV ne trouve pas la valeur.

#DIV/0!

#DIV/0! signifie que vous divisez par zéro ou par une cellule vide.

#NOM?

#NOM? signifie qu'Excel ne reconnaît pas quelque chose dans votre formule : le plus souvent une fonction mal orthographiée, ou du texte sans guillemets.

#REF!

#REF! signifie qu'une formule fait référence à une cellule qui n'existe plus, le plus souvent parce que vous avez supprimé une ligne, une colonne ou une feuille.

#PROPAGATION!

#PROPAGATION! signifie qu'une formule veut remplir plus de cellules qu'il n'y en a de libres. Vous la voyez avec UNIQUE, TRIER et FILTRE.

#NOMBRE!

#NOMBRE! signifie qu'un calcul ne donne pas de nombre valable, comme la racine carrée d'un nombre négatif ou un résultat trop grand.

#CALC!

#CALC! signifie qu'une fonction récente comme FILTRE donne un résultat vide : aucune ligne ne remplit votre condition.

#####

##### n'est pas une vraie erreur : la colonne est trop étroite pour afficher le nombre ou la date. Élargissez-la en double-cliquant sur la bordure à droite de la lettre de la colonne.

Corriger une référence circulaire

Une référence circulaire est une formule qui renvoie à sa propre cellule, comme =SOMME(B2:B6) en B6. Excel vous avertit et ne peut pas calculer le résultat. Retirez cette cellule de la formule.