5 Formules Excel Avancées Utiles Que Vous Devriez Connaître

Alors que vous continuez à utiliser Microsoft Excel pour le suivi, le budget ou l’inventaire, vous apprenez probablement beaucoup plus sur les fonctions disponibles et leurs formules. Mais cela peut encore être écrasant ou même effrayant de découvrir quelque chose de nouveau. Ce guide fournit une poignée de fonctions utiles et leurs formules pour quand vous êtes prêt à plonger dans de nouveaux territoires.

Table des matières

• 1. Rechercher des valeurs dans votre feuille : XLOOKUP
• 2. Recherche de valeur alternative dans une feuille : INDEX et MATCH
• 3. Ajouter ou compter avec des critères : SUMIF, SUMIFS, COUNTIF et COUNTIFS
• 4. Tester des données avec diverses conditions : IFS
• 5. Filtrer en utilisant plusieurs critères : FILTER
• Questions Fréquemment Posées

1. Rechercher des valeurs dans votre feuille : XLOOKUP

Lorsque vous souhaitez rechercher des données en fonction d’autres données dans un grand tableur, XLOOKUP est votre fonction de prédilection. Différent de VLOOKUP, qui ne peut rechercher des valeurs que de gauche à droite, XLOOKUP peut rechercher des valeurs de gauche à droite ou de droite à gauche, ce qui en fait un outil plus flexible.

Remarque : au moment de la rédaction, XLOOKUP n’est disponible que pour les abonnés de Microsoft 365. Pour d’autres versions d’Excel, consultez la combinaison INDEX et MATCH dans la section suivante.

La syntaxe de la fonction est :

XLOOKUP(lookup_value, lookup_range, return_range, not_found, match_mode, search_mode)

Seux les trois premiers arguments sont requis. Voici une description de chaque argument :

• Lookup_value : la valeur à rechercher.
• Lookup_range : la plage contenant la valeur à rechercher.
• Return_range : la plage contenant la valeur à retourner.
• Not_found : le texte à retourner si la valeur n’est pas trouvée. “#N/A” est la valeur par défaut si omise.
• Match_mode : le type de correspondance utilisant “0” pour une correspondance exacte et “#N/A” pour aucune trouvée (valeur par défaut si omise), “1” pour une correspondance exacte et l’élément plus petit suivant si aucune trouvée, “-1” pour une correspondance exacte et l’élément plus grand suivant si aucune trouvée, ou “2” pour une correspondance générique utilisant un point d’interrogation, un astérisque ou un tilde.
• Search_mode : le mode de recherche utilisant “1” pour commencer au premier élément (valeur par défaut si omise), “-1” pour commencer au dernier élément, “2” lorsque “lookup_range” est en ordre croissant, ou “-2” lorsque “lookup_range” est en ordre décroissant.

Par exemple, nous recherchons les ventes pour le secteur 2, donc nous utilisons la formule suivante :

=XLOOKUP(2,A19:A24,D19:D24)

En décomposant la formule, 2 est le “lookup_value”, A19:A24 est le “lookup_range”, et D19:D24 est l’argument “return_range”.

Le résultat, 74 000, est la valeur correspondante correcte pour le secteur 2.

Si vous voulez quelque chose de plus dynamique, où vous recherchez une valeur dans une cellule au lieu d’une constante, puis recevez le résultat mis à jour, chaque fois que vous changez la valeur dans cette cellule, voyez l’exemple suivant :

Nous remplaçons le “lookup_value” de 2 par la cellule F19 :

=XLOOKUP(F19,A19:A24,D19:D24)

Lorsque nous entrons 2 dans la cellule F19, nous recevons le résultat 74 000, et si nous entrons une valeur différente, comme 5, nous recevons automatiquement le résultat mis à jour, qui est 61 000.

Vous pouvez inclure l’argument “not_found” afin de ne pas avoir à regarder un message d’erreur si aucune correspondance n’est trouvée. Nous ajoutons “Pas de chance” pour l’argument.

=XLOOKUP(F19,A19:A24,D19:D24,"Pas de chance")

Lorsqu’une valeur est entrée dans la cellule F19 qui n’a pas de correspondance, vous verrez votre message au lieu de “#NA”.

2. Recherche de valeur alternative dans une feuille : INDEX et MATCH

Alors que XLOOKUP vous offre un moyen solide de rechercher des valeurs, actuellement, il n’est disponible que pour les abonnés d’Excel Microsoft 365. Si vous utilisez une autre version d’Excel, vous pouvez utiliser INDEX et MATCH pour faire la même chose.

Avec la fonction INDEX, la valeur dans une cellule est retournée en fonction de la position que vous entrez dans la formule. La syntaxe pour INDEX est :

INDEX(range, row_number, column_number)

Avec la fonction MATCH, une position pour une valeur que vous entrez dans la formule est retournée. La syntaxe pour MATCH est :

MATCH(value, range, match_type)

Pour combiner ces deux fonctions et leurs formules, placez la formule MATCH à l’intérieur de la formule INDEX comme position de recherche (“row_number” et “column_number”).

En utilisant les mêmes données que l’exemple XLOOKUP ci-dessus, nous voulons voir les ventes pour le secteur que nous entrons dans la cellule F19. La formule est :

=INDEX(D19:D24,MATCH(F19,A19:A24))

Pour décomposer cette formule, nous commençons par INDEX et son argument “range”, qui est D19:D24. C’est la plage contenant le résultat que nous voulons.

La formule MATCH utilise F19 comme argument “value” et A19:A24 comme argument “range” contenant cette valeur.

Lorsque nous entrons 4 dans la cellule F19, nous recevons le résultat 75 000, qui est correct.

Si nous entrons un secteur différent dans la cellule F19, comme 6, la formule se met à jour automatiquement pour donner la valeur correcte de 58 000.

Astuce : essayez d’entrer des fonctions dans une cellule en appuyant sur Shift + F3. Le menu “Insérer une fonction” vous permet de rechercher et de sélectionner des fonctions. Consultez notre feuille de triche sur les raccourcis clavier Excel pour plus de gain de temps.

3. Ajouter ou compter avec des critères : SUMIF, SUMIFS, COUNTIF et COUNTIFS

Les fonctions SUM et COUNT dans Excel sont conçues pour additionner des nombres et compter des cellules, mais elles sont limitées à des calculs simples. Des variations de ces fonctions vous permettent de sommer ou de compter en utilisant des critères. Par exemple, vous pouvez totaliser uniquement les nombres supérieurs à 12 ou compter uniquement les cellules contenant le nom Bill.

Avec SUMIF et COUNTIF, vous pouvez ajouter ou compter avec une condition, et avec SUMIFS et COUNTIFS, vous pouvez ajouter ou compter avec plusieurs conditions. Voici la syntaxe pour chacune :

SUMIF

SUMIF(range, criteria, sum_range)

COUNTIF

COUNTIF(range, condition)

SUMIFS

SUMIFS(sum_range, condition_range1, condition1, condition_range2, condition2,…)

COUNTIFS

COUNTIFS(condition_range1, condition1, condition_range2, condition2,…)

Voir ci-dessous un exemple de chaque fonction.

Pour ajouter les nombres dans notre plage B2 à B7 uniquement pour ceux qui sont supérieurs à 12, nous utilisons la fonction SUMIF et cette formule :

=SUMIF(B2:B7,">12")

Le résultat est 31, car la formule a ajouté 16 et 15, les seuls nombres supérieurs à 12 dans l’ensemble de données.

Dans l’exemple suivant, nous comptons les cellules avec des valeurs inférieures à 12 dans la même plage, B2 à B7, avec la formule suivante :

=COUNTIF(B2:B7,"<12")

Le résultat pour cette formule est 4, car c’est le nombre de cellules dans la plage avec des valeurs inférieures à 12.

Passons à la vitesse supérieure avec SUMIFS et utilisons deux conditions. Pour ajouter les nombres dans les cellules B2 à B7 uniquement pour ceux qui se sont inscrits à notre newsletter (Y) dans les cellules C2 à C7 et dont la ville est San Diego dans les cellules D2 à D7, nous utilisons la formule suivante :

=SUMIFS(B2:B7,C2:C7,"Y",D2:D7,"San Diego")

Le résultat pour cette formule est 19, car il n’y a que deux clients qui ont un Y pour la newsletter et San Diego pour la ville. Bill Brown a 11 articles et Sue Smith en a 8 pour un total de 19.

Pour un exemple utilisant la fonction COUNTIFS, nous comptons le nombre de cellules avec les mêmes deux conditions décrites ci-dessus. Nous comptons le nombre de clients qui se sont inscrits à la newsletter (Y) et dont la ville est San Diego.

=COUNTIFS(C2:C7,"Y",D2:D7,"San Diego")

Comme prévu, le résultat est 2 pour Bill Brown et Sue Smith, les seules inscriptions à la newsletter à San Diego.

4. Tester des données avec diverses conditions : IFS

La fonction IF dans Excel est un outil puissant pour tester des données par rapport à des conditions. Par exemple, vous pourriez l’utiliser pour afficher une note de lettre pour la note numérique d’un étudiant ou un “Oui” si un vendeur génère suffisamment de revenus pour gagner son bonus et un “Non” s’il ne le fait pas.

Le problème avec la fonction IF est que pour tester vos données par rapport à plusieurs critères, vous devez imbriquer toutes les instructions IF ensemble. Pour remédier à cette confusion massive, utilisez la fonction IFS.

Similaire à SUMIFS et COUNTIFS ci-dessus, IFS vous permet d’ajouter plusieurs critères à votre formule de manière claire et facile à lire.

La syntaxe est IFS(test1, if_test1_true, test2, if_test2_true, …), où vous pouvez tester jusqu’à 127 critères différents.

En utilisant l’un de nos scénarios d’exemple, nous commençons simple pour indiquer les bonus. Si le montant dans la cellule B2 est supérieur à 20 000, affichez “Oui”. Sinon, affichez “Non”. La formule devient :

=IFS(B2>20000,"Oui",B2<20000,"Non")

Dans le premier test, si la valeur dans B2 est supérieure à 20 000, le résultat si c’est vrai est “Oui”. Dans le deuxième test, si la valeur dans B2 est inférieure à 20 000, le résultat si c’est vrai est “Non”.

Heureusement, notre vendeur reçoit son bonus. Comme ses ventes sont de 21 000, un “Oui” va dans la colonne Bonus.

Copiez la formule si vous avez une liste comme notre exemple. Faites glisser la poignée de remplissage dans le coin inférieur droit de la cellule vers les cellules restantes pour les autres vendeurs. La formule se met à jour automatiquement pour s’adapter aux différentes références de cellules.

Pour un autre exemple IFS, nous avons notre liste de managers de service pour chaque jour de la semaine. Lorsque le jour actuel s’affiche dans la cellule D2, le nom du manager correspondant s’affiche dans la cellule E2. La formule devient :

=IFS(D2="Lundi",B2,D2="Mardi",B3,D2="Mercredi",B4,D2="Jeudi",B5,D2="Vendredi",B6)

Cela montre que si la valeur dans la cellule D2 est Lundi, affichez le nom dans la cellule B2, si la valeur dans la cellule D2 est Mardi, affichez le nom dans la cellule B3, si la valeur dans la cellule D2 est Mercredi, affichez le nom dans la cellule B3, et ainsi de suite.

Au fur et à mesure que vous changez le jour de la semaine dans la cellule D2, le nom correspondant s’affiche pour le manager de service dans la cellule E2.

Avec un petit effort pour créer la formule au départ, vous pouvez récolter les bénéfices d’une certaine automatisation dans votre feuille Excel.

5. Filtrer en utilisant plusieurs critères : FILTER

En tant qu’utilisateur d’Excel, vous savez probablement que l’application est livrée avec une fonction de filtre intégrée. Cependant, si vous souhaitez filtrer vos données en utilisant une condition ou même plusieurs critères, vous devrez utiliser la fonction FILTER. Cette fonction flexible et ses arguments peuvent vous aider à affiner de grands ensembles de données en quelques minutes.

La syntaxe de la fonction est FILTER(range, range=criteria, if_empty), où seuls les deux premiers arguments sont requis pour l’ensemble de données plus les critères et sa plage conteneur. Le troisième argument est utile si vous souhaitez retourner quelque chose en particulier si la formule ne donne aucun résultat, comme “pas de données” ou “aucun”.

Il est important de savoir comment fonctionne la fonction FILTER avec une seule condition d’abord avant d’ajouter plusieurs critères. Par exemple, vous pouvez filtrer les données dans les cellules A2 à C9 par Harold Hill qui existe dans B2 à B9. La formule pour cela est :

=FILTER(A2:C9,B2:B9="Harold Hill")

Nous avons deux résultats pour Harold Hill. Facile, non ?

Ajoutons plus de conditions à la formule. Plusieurs critères sont ajoutés en utilisant un astérisque (*) pour ET et un signe plus (+) pour OU.

Par exemple, nous filtrons à la fois Harold Hill et Électronique en utilisant la formule suivante :

=FILTER(A2:C9,(B2:B9="Harold Hill")*(A2:A9="Électronique"))

Pour décomposer la formule, A2:C9 est l’ensemble de données, B2:B9=”Harold Hill” est la première condition, l’astérisque représente ET, et A2:A9=”Électronique” est la deuxième condition.

Nous avons reçu un résultat avec notre filtre, car la formule doit correspondre aux deux conditions, Harold Hill et Électronique.

Dans un autre exemple, vous pouvez filtrer pour Vêtements ou Automobile :

=FILTER(A2:C9,(A2:A9="Vêtements")+(A2:A9="Automobile"))

En décomposant cette formule, A2:C9 est l’ensemble de données, A2:A9=”Vêtements” est la première condition, le signe plus représente OU, et A2:A9=”Automobile” est la deuxième condition.

Cette fois, nous avons reçu deux résultats, car la formule doit correspondre à l’une ou l’autre condition – pas les deux.

Astuce : pour en savoir plus sur les compétences avancées d’Excel, consultez nos guides Power Query et Power Pivot.

Questions Fréquemment Posées

Comment puis-je trouver la structure d’une formule de fonction directement dans Excel ?

Excel offre un outil intégré pour trouver la fonction dont vous avez besoin. Avec cela, vous pouvez voir la syntaxe pour la formule de cette fonction.

Sélectionnez une cellule vide, allez dans l’onglet “Formules” et choisissez “Insérer une fonction” sur le côté gauche du ruban. Tapez la fonction dans la zone de recherche en haut et cliquez sur “Go”. Lorsque vous voyez la fonction que vous voulez, sélectionnez-la pour voir sa description et sa syntaxe en bas de la boîte.

Comment puis-je déboguer une formule qui ne fonctionne pas correctement ?

Sélectionnez la formule avec laquelle vous avez des difficultés et allez dans l’onglet “Formules”. Choisissez “Évaluer la formule” dans la section d’audit des formules du ruban.

Vous verrez votre formule dans la fenêtre Évaluer la formule avec une partie de celle-ci soulignée. Cliquez sur “Évaluer” pour voir la partie soulignée calculée. Continuez ce processus pour passer par chaque partie de votre formule jusqu’au résultat. Cela vous permet de voir comment la formule est traitée et où l’erreur peut se trouver.

Comment puis-je savoir pourquoi je reçois une erreur de formule ?

Lorsque vous voyez une erreur pour une formule que vous avez créée, vous pouvez faire deux choses pour obtenir plus d’informations. Tout d’abord, cliquez sur le bouton d’erreur qui apparaît à côté de la cellule, puis sélectionnez “Aide sur cette erreur” pour obtenir plus de détails de Microsoft dans la barre latérale qui apparaît.

Alternativement, sélectionnez “Afficher les étapes de calcul” pour ouvrir la fenêtre Évaluer la formule décrite ci-dessus. Vous verrez le résultat du calcul et le processus pour y parvenir, ce qui devrait aider à déterminer la cause de l’erreur.

Crédit image : Pixabay. Toutes les captures d’écran par Sandy Writtenhouse.