Top 10 des formules Excel à maîtriser pour les analystes

(Last Updated On: )

L’autre jour un collègue me demande : “Lionel, quelles sont les formules que tu utilises le plus dans Excel ?”. En effet, chacun a ses formules favorites, mais certaines formules sont incontournables pour analyser des données.

Voici mon Top 10 de formules Excel qui selon moi tout analyste devrait connaître.

SIERREUR
SOMME.SI et SOMME.SI.ENS
RECHERCHEV
INDEX-EQUIV
NB.SI
MIN/MAX et PETITE/GRANDE.VALEUR
LIREDONNEESTABCROISEDYNAMIQUE
Formules Dates
Formules Texte
Formules Statistiques

Bonus : AGREGAT

1. SIERREUR

La fonction SIERREUR est très pratique ! Notamment pour gérer les #DIV/0! (division par zéro), les #N/A renvoyés par une RechercheV non fructueuse, ~~les pannes de machine à café~~

etc…

La syntaxe est : SIERREUR(formule ; que faire en cas d’erreur)

Utilisez-la comme ceci par exemple :

SIERREUR(RECHERCHEV(recherche susceptible de livrer des erreurs) ; valeur alternative)

Mais attention à ne pas trop en abuser, à trop l’utiliser vous pourriez passer à côté d’erreurs très instructives 😉

2. SOMME.SI et SOMME.SI.ENS

N’avez-vous jamais entendu un boss demander :

“Combien avons-nous fait de chiffre d’affaire” => SOMME(ventes)

“Combien avons-nous fait de chiffre d’affaire sur la région EST, sur le premier semestre” => il n’y a pas de réponse directe comme précédemment, à moins de filtrer vos données au préalable sur la région et la période en question.

Heureusement, il existe sur Excel les fonctions de somme conditionnelle SOMME.SI avec une condition, et SOMME.SI.ENS avec plusieurs conditions.

Utilisez-les comme ceci :

Pour une somme avec un critère :

SOMME.SI(plage critère ; critère ; plage somme)

et pour plusieurs critères :

SOMME.SI.ENS(plage somme ; plage critère 1 ; critère 1 ; plage critère 2 ; critère 2 ; etc)

3. RECHERCHEV

S’il y a bien une Formule magique dans le monde de l’analyse des données (avec Excel), c’est celle-là. Non parce que c’est la plus efficace, mais parce que c’est la plus répandue, que même votre boss qui pourtant ne maîtrise pas Excel aussi bien que vous connaît.

La formule RECHERCHEV permet de chercher des valeurs dans des colonnes adjacentes ou dans d’autres tables, et peut trouver à peu près tout et n’importe quoi si elle est mal employée.

Voici comment bien l’utiliser :

RECHERCHEV(valeur cherchée ; plage de recherche ; numéro de colonne ; correspondance)

La contrainte de cette formule est que la valeur cherchée doit se trouver dans la première colonne de la plage de recherche.

Le numéro de colonne indique ensuite la colonne adjacente qui contient la valeur associée à votre recherche.

L’argument “correspondance” indique si l’on souhaite une correspondance approximative (valeur 1) ou exacte (valeur 0). A moins de savoir exactement ce que vous faîtes, je vous conseille de toujours utiliser la correspondance exacte.

Exemple : Quel montant de vente a été enregistré le 01/04/2015 ?

Ici la première colonne contient les dates de ventes, et la septième colonne les montants des ventes.

Autre contrainte : si plusieurs résultats de recherche sont possibles, Excel renvoie la première occurrence trouvée. Donc attention à bien délimiter votre recherche.

4. INDEX+EQUIV

Voici la formule ou combinaison de formules la plus flexible pour effectuer une recherche de valeurs.

Malheureusement, elle ne semble pas aussi répandue dans le monde de l’analyse des données que la RECHERCHEV.

J’ai parlé tout à l’heure d’une limitation de la formule RECHERCHEV, il s’agit de l’impossibilité de trouver une valeur dans une colonne à gauche de la colonne de recherche. Ce qui oblige souvent à modifier l’ordre des colonnes, ou pire à dupliquer les colonnes de recherche pour les positionner le plus à gauche des données…

Avec INDEX-EQUIV, vous pouvez rechercher n’importe quelle colonne et retourner la valeur correspondante de n’importe quelle autre colonne.

Pour une utilisation encore plus efficace de INDEX-EQUIV, convertissez votre plage de données en Table Excel, cela permet de faire référence aux colonnes par leur nom. Ainsi, si vous modifiez après coup l’ordre des colonnes de vos données, la recherche avec INDEX-EQUIV fonctionnera toujours, ce qui n’aurait pas été le cas avec RECHERCHEV.

Voici donc la syntaxe d’une recherche avec INDEX-EQUIV :

=INDEX (liste des valeurs ; EQUIV(valeur cherchée ; colonne de recherche ; correspondance exacte ? ))

La liste des valeurs est la colonne contenant la valeur correspondante de la recherche.
La colonne de recherche est la colonne contenant la valeur cherchée.
L’argument correspondance a plusieurs valeurs :

1 pour rechercher la valeur la plus proche inférieure à la valeur cherchée
0 pour rechercher la valeur exacte et retourner une erreur sinon
-1 pour rechercher la valeur la plus proche supérieure à la valeur cherchée

Exemple : Quel item a un prix unitaire de 1,99 ?

Pour en savoir plus, vous pouvez voir mon article présentant un exemple concret d’utilisation de la formule INDEX-EQUIV.

5. NB.SI

La formule NB.SI permet de compter le nombre de cellules qui répondent à un critère, par exemple pour compter le nombre de ventes pour un responsable donné.

Syntaxe :

=NB.SI (plage de recherche ; critère de recherche)

Exemples :

Combien de ventes réalisées après le 01/06/2017 ?	=NB.SI(DimVentes[Date];”>01/06/2017″)
Combien de ventes réalisées par le vendeur Marc ?	=NB.SI(DimVentes[Responsable];”Marc”)
Combien de ventes supérieures à 1 000 €	=NB.SI(DimVentes[Montant ventes];”>1000″)
Combien de ventes dont le prix unitaire est égal à 1,99 € ?	=NB.SI(DimVentes[Prix unitaire];1,99)

Notez que le critère peut être du texte (entre “guillemets”) ou des valeurs numériques.

Pour une recherche multi-critères, utilisez la formule NB.SI.ENS :

=NB.SI.ENS ( plage critère 1 ; critère 1 ; plage critère 2 ; critère 2 ; etc.)

6. MIN / MAX, PETITE.VALEUR / GRANDE.VALEUR, RANG

Utilisez ces formules de classement pour répondre à ce type de questions :

PETITE.VALEUR : utilisé pour trouver la ni-ème plus petite valeur d’une liste =PETITE.VALEUR(plage de valeurs ; n)
GRANDE.VALEUR : utilisé pour trouver la n-ième plus grande valeur d’une liste =GRANDE.VALEUR(plage de valeurs ; n)
MIN : donne la valeur minimale d’une liste =MIN(plage de valeurs)
MAX : donne la valeur maximale d’une liste =MAX(plage de valeurs)
RANG : recherche le rang d’une valeur dans une liste =RANG(valeur; dans cette liste; ordre décroissant 0 ou croissant -1)

Pour aller plus loin, lisez mon article pour créer des classements TOP/FLOP avec les formules PETITE.VALEUR et GRANDE.VALEUR.

7. LIREDONNEESTABCROISEDYNAMIQUE

Pour explorer et synthétiser des données, il est difficile de faire mieux qu’un tableau croisé dynamique. Par contre, pour réaliser un tableau de bord, un TCD n’est pas toujours adapté car sa structure est figée.

Cependant, il y a une méthode courante pour récupérer des informations d’un TCD et les synthétiser dans un tableau de bord. Il s’agit de la formule LIREDONNEESTABCROISEDYNAMIQUE qui permet de lire toutes les données affichées dans un TCD.

Pour en savoir plus sur son utilisation, lisez mon article dédié à la formule LIREDONNEESTABCROISEDYNAMIQUE.

8. Formules Dates

Voici quelques formules très utiles pour manipuler les dates dans les données :

DATE : Permet de constituer une date à partir de l’année, mois, jour spécifiés =DATE(Année ; Mois ; Jour)

MOIS.DECALER : Permet d’obtenir une date en décalant de x mois (en négatif ou positif) par rapport à une date de référence, très utile pour constituer un calendrier ou un planning =MOIS.DECALER(date de référence ; nombre de mois)

NB.JOURS.OUVRES : Permet d’obtenir le nombre de jours ouvrés entre deux dates, prend en compte les jours fériés en option = NB.JOURS.OUVRES(date début ; date fin ; [Liste des jours fériés])

Pour en savoir plus sur cette dernière formule, ne loupez-pas cet article phare que j’ai rédigé pour calculer les dates des jours fériés automatiquement sous Excel.

9. Formules Texte

Voici quelques formules très utiles pour manipuler les chaînes de caractères dans les données:

GAUCHE/DROITE/STXT : pour extraire des portions de texte à gauche, à droite et au milieu

SUPPRESPACE : pour supprimer les espaces au début et à la fin d’un texte

SUBSTITUE : pour remplacer un caractère par un autre dans un texte

10. Fonctions statistiques

Voici quelques formules statistiques très utiles pour mieux connaître la distribution d’une variable :

MOYENNE : calcule la moyenne arithmétique d’un ensemble de valeurs

MEDIANE : calcule la médiane de la plage, c’est-à-dire que la moitié des valeurs sont inférieures à la médiane et l’autre moité supérieure à la valeur médiane

ECARTTYPE.STANDARD : calcule l’écart-type, soit l’éloignement par rapport à la moyenne d’une plage de valeurs. Plus il est important, plus les valeurs sont dispersées.

Vous pouvez également utiliser un graphique en histogramme dans Excel 2016 pour visualiser rapidement la distribution d’une valeur.

En BONUS : la fonction AGREGAT

La fonction AGREGAT permet comme son nom l’indique d’agréger des données de différentes manières. On peut faire des sommes, des produits, des moyennes, trouver le minimum/maximum, etc… L’intérêt par rapport aux fonctions classiques est qu’elle permet de filtrer les données sur plusieurs critères : lignes masquées, erreurs, sous-totaux. Pour en savoir plus, vous pouvez lire mon article sur l’utilisation de la fonction AGREGAT.

Selon vous, quelles sont les formules les plus utiles pour analyser des données?

Partagez vos idées et astuces dans les commentaires 👏👏

📥TELECHARGER LE FICHIER D’EXEMPLE

Téléchargez le classeur Excel pour ce tutoriel. Seule votre adresse e-mail vous sera demandée dans la prochaine étape. Vous recevrez immédiatement le lien de téléchargement par e-mail.