Les 8 (+1 incroyable) fonctions Excel à maîtriser absolument en 2024

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

Dans ce tutoriel, nous allons découvrir les huit fonctions avancées d'Excel qui vont considérablement améliorer votre efficacité dans la manipulation et l'analyse de données en 2024.

En fin de tuto, je vous propose de découvrir une neuvième fonction incroyable, qui va vous permettre de filtrer des données en seulement quelques secondes !

Que vous soyez débutant ou utilisateur expérimenté, ces astuces vous permettront d'exploiter pleinement le potentiel d'Excel pour vos besoins professionnels ou personnels.

 

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

 

Tutoriel Vidéo

 

 

1. Présentation

Pour illustrer ce tutoriel, nous allons partir du petit tableau suivant dans lequel nous retrouvons les informations concernant un petit commerce de détail avec des succursales dans différentes villes.

Excel formation - 9FonctionsExcel - 01

Comme vous pouvez les constater, nous y retrouvons les données de vente de produits dans chaque ville, et nous souhaitons analyser ces données pour prendre des décisions stratégiques et améliorer vos performances commerciales.

 

2. Fonctions de Logique

 

Les fonctions logiques permettent de réaliser des tests et de prendre des décisions en fonction des résultats obtenus.

 

   1.1. La fonction SI()

 

La fonction SI est la reine des fonctions Excel à savoir maîtriser absolument !

Celle-ci est en effet utilisée pour effectuer un test logique, afin de retourner un résultat en fonction de ce que ce test va retourner.

Un test logique comme celui-ci que nous souhaitons mettre en place ici ne peut retourner que deux résultats, que nous appelons un résultat booléen. Cela signifie qu’il ne pourra être égal que à VRAI ou à FAUX.

Si résultat est VRAI, alors la fonction SI va retourner la valeur passée dans le deuxième argument, et s’il est égal à FAUX, alors ce sera la valeur passée dans le troisième et dernier argument.

Ainsi les trois arguments attendus par la fonction SI sont

  • Test_logique : La condition que vous souhaitez évaluer.
  • Valeur_si_vrai : La valeur à retourner si le test logique est vrai.
  • Valeur_si_faux : La valeur à retourner si le test logique est faux.

Pour en revenir à notre exemple, nous allons souhaiter déterminer quelles sont les ventes qui répondent à l’objectif fixé de réaliser un taux de marque d’au moins 30%

Pour calculer le taux de marque, nous commençons par calculer la différence entre le prix de vente et le prix d’achat, et nous divisons ce résultat par le prix de vente.

Nous nous plaçons donc sur la cellule E8, afin d’y saisir la formule suivante :

 =(C8-D8)/C8 

Excel formation - 9FonctionsExcel - 02

Ici, le résultat est de 34%, l’objectif est donc rempli.

Maintenant, nous allons utiliser ce résultat en tant que test de la fonction SI, en l’encapsulant en tant que premier argument :

 =SI((C8-D8)/C8>30%;"Objectif  atteint";"Objectif non atteint") 

Excel formation - 9FonctionsExcel - 03

Maintenant, il ne reste plus qu’à étendre la formule sur toutes les cellules de la colonne pour savoir quelles sont les ventes pour lesquelles l’objectif n’a pas été atteint :

Excel formation - 9FonctionsExcel - 04

 

   1.2. SI.MULTIPLE et SI.CONDITIONS

 

Maintenant que nous savons comment utiliser la fonction SI, il faut savoir qu’il est également possible d’encapsuler des fonctions SI à l’intérieur des fonctions SI (en tant qu’argument Valeur_si_vrai, ou Valeur_si_faux).

Cela offre une grande flexibilité dans la prise de décision en permettant d'évaluer plusieurs conditions et de retourner des résultats différents en fonction de ces conditions.

Cette approche permet de construire des tests logiques complexes et de rendre les formules plus modulaires et faciles à comprendre.

Ainsi, si nous souhaitons insérer un deuxième test pour déterminer si l’objectif est partiellement atteint, nous pouvons modifier la formule de la manière suivante :

 =SI((C8-D8)/C8>30%;"Objectif  atteint";SI((C8-D8)/C8>25%;"Objectif pariellement  atteint";"Objectif non atteint")) 

Nous pouvons constater que cette formule évalue deux tests logiques imbriqués pour déterminer le résultat à retourner.

Elle évalue d'abord si la marge bénéficiaire est supérieure à 30%, et si c'est le cas, elle renvoie "Objectif atteint".

Sinon, elle évalue si la marge bénéficiaire est supérieure à 25%, et si c'est vrai, elle renvoie "Objectif partiellement atteint".

Sinon, elle renvoie "Objectif non atteint".

Cependant, cette approche présente des limites en termes de lisibilité et de maintenabilité.

Si nous devons ajouter de nouveaux critères ou modifier les valeurs de seuil, la formule peut rapidement devenir complexe et difficile à suivre.

C’est pour cela qu’Excel propose deux fonctions alternatives aux fonctions SI imbriquées.

Il s’agit des fonctions SI.MULTIPLE et SI.CONDITIONS qui permettent de gérer plusieurs conditions et d'obtenir des résultats variés en fonction de ces conditions.

Pour commencer, voyons comment la fonction SI.CONDITIONS va nous permettre de simplifier la fonction SI imbriquée que nous venons de mettre en place.

Celle-ci permet en effet de spécifier directement chaque condition et sa valeur associée.

Les arguments de la fonction SI.MULTIPLE sont constitués de paires de conditions et de valeurs à retourner si les conditions sont vraies (Test_logique1, valeur_si_vrai1).

Ainsi, la formule devient :

 =SI.CONDITIONS((C8-D8)/C8>30%;"Objectif  atteint";(C8-D8)/C8>25%;"Objectif partiellement  atteint";1;"Objectif non atteint") 

Ainsi, une petite astuce consiste à saisir « 1 », ou VRAI dans le dernier test afin d’être sûr que celui-ci sera toujours VRAI et puisse retourner une valeur par défaut :

Excel formation - 9FonctionsExcel - 05

Comme vous pouvez le constater ici, cette fonction est relativement plus simple à saisir, et surtout à relire car chaque condition est explicitement définie avec son résultat associé.

Maintenant, découvrons une autre variante de la fonction SI, il s’agit de la fonction SI.MULTIPLE, qui permet elle aussi de gérer plusieurs conditions et de retourner des valeurs différentes en fonction de ces conditions.

Par contre, contrairement à la fonction SI.CONDITIONS, qui prend en charge des conditions distinctes et leurs résultats associés, SI.MULTIPLE fonctionne en associant chaque condition à sa valeur correspondante.

Cependant, une limitation importante de la fonction SI.MULTIPLE est qu'elle ne prend pas en charge les opérateurs de comparaison tels que "<" ou ">".

Les valeurs à tester doivent donc correspondre exactement au résultat du test.

Cette limitation signifie que SI.MULTIPLE convient mieux aux situations où les tests sont basés sur des valeurs spécifiques plutôt que sur des comparaisons.

Dans notre exemple, étant donné que nous souhaitons évaluer le taux de marque des ventes, et nous avons besoin de comparer le résultat d'un calcul avec des seuils spécifiques, la fonction SI.MULTIPLE ne conviendra donc pas.

Les opérateurs de comparaison sont essentiels pour évaluer si une condition est vraie ou fausse en fonction de valeurs numériques ou de pourcentages.

Cependant, la fonction SI.MULTIPLE est extrêmement utile dans d'autres contextes.

Par exemple, imaginons que nous souhaitions obtenir le jour de la semaine correspondant à chaque date, la fonction SI.MULTIPLE est parfaitement adaptée.

Dans cette formule, nous utilisons la fonction JOURSEM pour obtenir le numéro du jour de la semaine à partir de la date dans la cellule E8, puis nous utilisons SI.MULTIPLE pour associer chaque numéro de jour à son nom correspondant :

 =SI.MULTIPLE(JOURSEM(E8;2);1;"Lundi";2;"Mardi";3;"Mercredi";4;"Jeudi";5;"Vendredi";6;"Samedi";7;"Dimanche")  

Cette formule permet de renvoyer le nom du jour de la semaine en fonction du numéro associé à chaque date dans la cellule E8.

Avec SI.MULTIPLE, nous pouvons facilement gérer plusieurs conditions et leurs résultats associés, ce qui rend cette fonction très pratique dans de nombreux scénarios d'analyse de données.

 

3. Fonctions de Calcul Avancées

 

Les fonctions de calcul avancées offrent une puissance supplémentaire pour traiter les données de manière précise et efficace.

Dans cette section, nous explorerons deux fonctions essentielles : SOMME.SI.ENS et NB.SI.ENS, ainsi que la fonction AGREGAT.

 

   3.1. SOMME.SI.ENS et NB.SI.ENS

 

Les fonctions SOMME.SI.ENS et NB.SI.ENS sont des outils indispensables pour additionner ou compter des cellules qui répondent à plusieurs critères.

Elles sont particulièrement utiles lorsque vous devez effectuer des calculs basés sur des conditions multiples.

Pour commencer, intéressons nous à la fonction SOMME.SI.ENS, dont les arguments sont les suivants :

- Plage_somme : La plage de cellules à additionner.

- Critère1, Plage_critère1, ... : Paires de critères et de plages correspondantes.

Par exemple, supposons que vous vouliez additionner les ventes de produits pour une ville donnée, nous pouvons alors utiliser la fonction SOMME.SI.ENS de la manière suivante :

 =SOMME.SI.ENS(C8:C37;A8:A37;J9;B8:B37;J10) 

Ici, le nom du produit est saisi en cellule J10 et le nom de la ville en cellule J9 :

Excel formation - 9FonctionsExcel - 06

Ainsi, au cours du mois de février, le magasin de Lille à vendu pour 320€ de Sac à main.

Maintenant, si nous souhaitons connaître le nombre de vente que cela représente, nous pouvons utiliser la fonction NB.SI.ENS :

 =NB.SI.ENS(A8:A37;J9;B8:B37;J10) 

La seule différence ici au niveau des arguments, c’est qu’il n’est pas nécessaire de spécifier de plage de données à dénombrer, étant donné que la fonction NB.SI va compter le nombre de fois que les conditions sont remplies :

Excel formation - 9FonctionsExcel - 07

 

   3.2. AGREGAT

 

Maintenant, découvrons une fonction peu connue et peu utilisée sur Excel : la fonction AGREGAT.

Celle-ci permet d'effectuer différents types de calculs sur des données tout en ignorant les valeurs cachées ou filtrées.

Introduite dans Excel à partir de la version 2010, AGREGAT offre une flexibilité accrue dans l'analyse des données complexes.

Par exemple, si nous souhaitons obtenir le montant des ventes en tenant compte de filtres mis en place sur la base, nous allons utiliser la fonction AGREGAT de la manière suivante :

 =AGREGAT(9;5;C8:C37) 

Dans cette formule, 9 représente la fonction permettant d’obtenir la somme des cellules passées en argument (C8 à C37), tandis que 5 indique à la fonction d'ignorer les cellules masquées.

Maintenant, nous pouvons appliquer un filtre sur la base en cliquant sur le bouton « Filtre » du menu « Données », après avoir sélectionné l’une des cellules du tableau :

Excel formation - 9FonctionsExcel - 08

Puis, nous sélectionnons uniquement les ventes réalisées au cours des trois premiers jours de février :

Excel formation - 9FonctionsExcel - 09

Et nous pouvons alors constater que le résultat retourné par la fonction va s’adapter automatiquement pour nous retourner uniquement le montant des ventes correspondantes à cette période :

Excel formation - 9FonctionsExcel - 10

En revanche, la valeur calculée précédemment, tient toujours compte de toutes les ventes de la base !

 

4. Fonctions de Recherche et de Filtrage

 

   4.1. RECHERCHEX et INDEX-EQUIV

 

Historiquement, la fonction RECHERCHEV était largement utilisée pour effectuer des recherches dans une base de données.

Cependant, elle présentait plusieurs limitations, notamment sa sensibilité aux modifications de structure des données et sa capacité limitée à rechercher des valeurs à gauche de la colonne de référence.

C'est pourquoi Microsoft a introduit la fonction RECHERCHEX, offrant une alternative plus flexible et puissante.

La fonction RECHERCHEX permet de rechercher une valeur dans une liste et de récupérer une valeur correspondante dans une colonne.

Elle offre plusieurs avantages par rapport à RECHERCHEV, tels que la possibilité de rechercher des valeurs à gauche, la prise en charge de recherches approximatives et exactes, ainsi que la gestion des erreurs avec plus de précision.

Les paramètres de la fonction RECHERCHEX sont les suivants :

  • valeur_cherchée (obligatoire) : Il s'agit de la valeur que nous souhaitons rechercher dans le tableau_recherche.
  • tableau_recherche (obligatoire) : C'est le tableau ou la plage où la recherche sera effectuée.
  • tableau_renvoyé (obligatoire) : Il représente le tableau ou la plage où se trouvent les valeurs à renvoyer.
  • [si_non_trouvé] (facultatif) : Ce paramètre est facultatif permet de spécifier le texte à renvoyer si valeur_cherché n’est pas trouvé dans tableau_recherche.
  • mode_correspondance (facultatif) : Permet de spécifier le type de correspondance souhaitée (exact ou approximatif).
  • mode_recherche (facultatif) : Permet de spécifier le mode de recherche à utiliser, à savoir s'il faut commencer la recherche à partir du premier élément ou de la fin du tableau.

Ainsi, la fonction RECHERCHEX propose une variété d'options pour répondre à différents besoins.

Par exemple, dans notre cas, nous pouvons l'utiliser pour obtenir le nom de la ville dans laquelle la seule Jupe à été vendue :

 =RECHERCHEX(I15;B8:B37;A8:A37) 

Excel formation - 9FonctionsExcel - 11

Les utilisateurs avancés d’Excel n’ont pas attendu cette évolution de la fonction RECHERCHEV pour combler ses lacunes.

En effet, une alternative proposée depuis toujours consiste à utiliser les deux fonctions INDEX et EQUIV imbriquée l’une dans l’autre.

Dans ce cas d’usage, ces deux fonctions sont tellement complémentaires, que nous pouvons parler d’une fonction à part entière INDEX-EQUIV !

Ici, la fonction EQUIV permet d’obtenir la position d’un élément à l’intérieur d’une liste.

Puis, la fonction INDEX va permettre d’aller récupérer la valeur située à la même position d’une autre liste.

Nous retrouvons donc le même fonctionnement que celui utilisé dans les fonctions de recherche classique :

 =INDEX(A8:A37;EQUIV(I15;B8:B37;)) 

Ici, la fonction EQUIV trouve la position de la valeur recherchée dans la plage B8:B37, puis la fonction INDEX récupère la valeur correspondante dans la plage A8:A37.

 

   4.2. FILTRE

 

La fonction FILTRE est une fonction très puissante, qui permet d’extraire des lignes ou des colonnes d'une plage de données en fonction de critères spécifiques.

Elle permet de filtrer les données rapidement et efficacement, offrant ainsi un moyen pratique d'analyser des ensembles de données volumineux.

Ces arguments sont les suivant :

  • Plage : La plage de données à filtrer.
  • Inclure : Les conditions que les données doivent remplir pour être incluses dans le filtre.

Par exemple, pour filtrer les données afin d'obtenir uniquement les ventes effectuées à Lille, nous pouvons utiliser la fonction FILTRE de la manière suivante :

 =FILTRE(A8:E37;A8:A37=P5) 

Attention, étant donné qu’il s’agit d’une fonction matricielle, celle-ci doit être saisie uniquement dans la première cellule du tableau d’affichage, et elle s’étendra automatiquement sur toutes les cellules de destination. Cela permet d'appliquer le filtre à l'ensemble des données de manière cohérente et efficace.

Excel formation - 9FonctionsExcel - 12

Étant donné qu’ici nous utilisons la cellule P5 pour saisir le nom de la ville à tester, celle-ci est dynamique, et il suffit de sélectionner un autre nom de ville pour adapter le résultat obtenu :

Excel formation - 9FonctionsExcel - 13

 

 



Articles qui pourraient vous intéresser

Les 8 (+1 incroyable) fonctions Excel à maîtriser absolument en 2024
Percez les secrets de la fonction SOMME (SOMME.SI/SOMMEPROD) et des ces déclinaisons pour boostez votre productivité sur Excel ?
TOP3 des méthodes pour extraire des données par date dans Excel
Comment identifier la date la plus ancienne ou la plus récente en fonction de critères sur Excel ?
Comment calculer une moyenne sans tenir compte des zéros sur Excel ?
Comment fusionner des tableaux, les trier et supprimer les doubles avec une seule formule Excel ? (ASSEMB.V et ASSEMB.H)
Arrêtez d'imbriquer des fonctions SI() sur Excel ! Découvrez SI.MULTIPLE() et SI.CONDITIONS()
Comment obtenir efficacement le numéro de semaine d’une date donnée sur Excel ?
Comment identifier la cellule qui se répète le plus de fois sur Excel ? (Fonction MODE)
Comment améliorer votre gestion de données avec les fonctions base de données d’Excel ?
Créer une somme intelligente et dynamique sur Excel sans VBA
Quelle fonction permet de lister le nom des feuilles de calcul Excel sans VBA ?

Contact - Plan du site - À propos - Contact

© Excelformation.fr 2018-2024 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.