Les 8 (+1 incroyable) fonctions Excel à maîtriser absolument en 2024
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 :
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.
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
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")
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 :
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 :
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 :
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 :
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 :
Puis, nous sélectionnons uniquement les ventes réalisées au cours des trois premiers jours de février :
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 :
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)
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.
É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 :
