5 formules Excel avancées utiles que vous devez connaître

Avec une utilisation continue Microsoft Excel suivre ou Budgétisation Ou d'inventaire, vous en apprendrez probablement beaucoup sur les fonctions disponibles et leurs formules. Mais découvrir quelque chose de nouveau peut être déroutant ou intimidant. Ce guide présente quelques fonctions utiles et leurs formules lorsque vous êtes prêt à plonger dans un nouveau territoire. Voici 5 formules Excel avancées utiles que vous devriez connaître.

1. Trouvez les valeurs dans votre feuille de calcul : XLOOKUP

Lorsque vous souhaitez rechercher des données basées sur d'autres données dans une grande feuille de calcul, XLOOKUP est votre fonction incontournable. autre que RECHERCHEV , qui ne peut rechercher que des valeurs 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 d'écrire ces lignes, XLOOKUP n'est disponible que pour les abonnés Microsoft 365. Pour les autres versions d'Excel, consultez la combinaison d'INDEX et de MATCH dans la section suivante.

La formule de la fonction est :

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

Seuls les trois premiers arguments sont requis. Vous trouverez ci-dessous 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 à renvoyer.
Pas trouvé: Le texte à renvoyer si la valeur est introuvable. "#N/A" est la valeur par défaut si omis.
Match_mode : Type de correspondance utilisant "0" pour une correspondance exacte et "#N/A" pour non trouvé (par défaut si omis), "1" pour une correspondance exacte et l'élément suivant plus petit si aucun n'a été trouvé, "-1" pour une correspondance exacte et plus élément Suivant s'il est introuvable, ou '2' pour faire correspondre un caractère générique à l'aide d'un point d'interrogation, d'un astérisque ou d'un tilde.
Mode_recherche : Mode de recherche avec "1" pour commencer au premier élément (par défaut si omis), "-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 des ventes pour le segment 2, nous utilisons donc la formule suivante :

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

En décomposant la formule, 2 est la "lookup_value", A19:A24 est la "lookup_range" et D19:D24 est l'argument "return_range".

Le résultat, 74000, 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 modifiez la valeur dans cette cellule, consultez l'exemple suivant :

Nous remplaçons "lookup_value" 2 par la cellule F19 :

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

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

Vous pouvez inclure l'argument "pas trouvé" Vous n'avez donc pas à regarder l'erreur de message si aucune correspondance n'est trouvée. Nous ajoutons "ne répondez pas" à l'argument.

=XLOOKUP(F19,A19:A24,D19:D24,"No dice")

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. Trouver la valeur alternative dans une feuille : INDEX et MATCH

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

Lisez aussi: Qu'est-ce que le changement de compte dans Facebook et Messenger

Avec la fonction INDEX, la valeur de la cellule est renvoyée en fonction de la position que vous avez entrée dans la formule. La formule INDEX est :

INDEX(plage, numéro_ligne, numéro_colonne)

Avec la fonction MATCH, la position de la valeur que vous entrez dans la formule est renvoyée. La formule MATCH est :

MATCH(valeur, plage, match_type)

Pour combiner ces deux fonctions et leurs formules, placez la formule MATCH dans la formule INDEX comme emplacement de recherche (« numéro_ligne » et « numéro_colonne »).

En utilisant les mêmes données que l'exemple XLOOKUP ci-dessus, nous voulons voir les ventes de segment 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 "plage", qui est D19: D24. C'est la plage qui contient le résultat que nous voulons.

La formule MATCH utilise F19 comme argument "valeur" et A19:A24 comme argument "plage" contenant cette valeur.

Lorsque nous entrons 4 dans la cellule F19, nous obtenons le résultat 75000, ce qui est correct.

Si nous entrons dans un secteur différent dans la cellule F19, tel que 6, la formule est automatiquement mise à jour pour donner la valeur correcte de 58000.

3. AJOUTER OU COMPTER AVEC DES CRITÈRES : SUMIF, SUMIFS, COUNTIF, COUNTIFS

Les fonctions SUM et COUNT dans Excel sont conçues pour additionner des nombres et compter des cellules, mais elles sont limitées à de simples opérations mathématiques. Des variantes de ces fonctions vous permettent d'ajouter ou de compter à l'aide de critères. Par exemple, vous ne pouvez ajouter que des nombres supérieurs à 12 ou compter les cellules contenant le nom Bill.

Avec SUMIF et COUNTIF, vous pouvez ajouter ou compter avec une seule condition, et avec SUMIFS et COUNTIFS, vous pouvez ajouter ou compter avec plusieurs conditions. Vous trouverez ci-dessous la syntaxe de chacun :

SUMIF

SUMIF(plage, critères, somme_plage)

COUNTIF

NB.SI(plage, condition)

SUMIFS

SOMME(plage_somme, plage_condition1, condition1, plage_condition2, condition2,…)

COUNTIFS

NB.SI(condition_plage1, condition1, condition_plage2, condition2,…)

Voir ci-dessous un exemple de chaque fonction.

Pour additionner uniquement les nombres de notre plage B2 à B7 pour les nombres supérieurs à 12, nous utilisons la fonction SUMIF et cette formule :

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

Le résultat est 31, puisque la formule a ajouté 16 et 15, les deux 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 :

=NB.SI(B2:B7,"<12")

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

Reprenons avec SUMIFS et utilisons deux conditions. Pour ajouter les chiffres dans les cellules B2 à B7 uniquement pour ceux qui se sont abonnés à 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 de cette formule est de 19, puisque seuls deux clients ont un Y pour la newsletter et un San Diego pour la ville. Bill Brown en a 11 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 deux mêmes conditions décrites ci-dessus. Nous comptons le nombre de clients inscrits à la newsletter (Y) et leur ville de San Diego.

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

Comme prévu, le score était de 2 pour Bill Brown et Sue Smith, le seul abonnement à la newsletter de San Diego.

Lisez aussi: Comment créer votre propre type de données dans Microsoft Excel

4. Tester les données dans différentes conditions : IFS

La fonction IF d'Excel est un outil puissant pour tester les données par rapport aux conditions. Par exemple, vous pouvez l'utiliser pour afficher une note alphabétique pour le score numérique d'un étudiant ou "oui" si le vendeur a généré suffisamment de revenus pour gagner sa prime et "non" s'il ne l'a pas fait.

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

Semblable à 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 benchmarks différents.

En utilisant l'un de nos exemples de scénarios, nous avons simplement commencé à faire référence aux récompenses. Si le montant de la cellule B2 est supérieur à 20000 XNUMX, afficher "Oui". Sinon, montrez "Non". La formule devient :

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

Dans le premier test, si la valeur en B2 est supérieure à 20000, le résultat si vrai est « oui ». Dans le second test, si la valeur en B2 est inférieure à 20000, le résultat si vrai est "Non".

Heureusement, notre vendeur reçoit sa récompense. Comme il a des ventes de 21000 XNUMX, il est entré "Oui" 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 des autres commerciaux. La formule est automatiquement mise à jour pour s'adapter aux différentes références de cellules.

Pour un autre exemple d'IFS, nous avons une liste de responsables de service pour chaque jour de la semaine. Lorsque le jour en cours est affiché dans la cellule D2, le nom du responsable correspondant est affiché dans la cellule E2. La formule devient :

=IFS(D2="Monday",B2,D2="Tuesday",B3,D2="Wednesday",B4,D2="Thursday",B5,D2="Friday",B6)

Cela dit 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, et si la valeur dans la cellule D2 est mercredi, affichez le nom dans la cellule B3, etc.

Lorsque vous modifiez le jour de la semaine dans la cellule D2, le nom correspondant du responsable de service s'affiche dans la cellule E2.

Avec une petite quantité de travail pour créer la formule à l'avance, vous pouvez récolter les fruits d'une certaine automatisation dans votre feuille Excel.

5. Filtrez sur plusieurs critères : filtre

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 à l'aide d'une condition ou même de plusieurs critères, vous devrez utiliser la fonction de filtrage. Cette fonctionnalité flexible et ses arguments peuvent vous aider à réduire de grands ensembles de données en quelques minutes seulement.

La syntaxe de la fonction est FILTER(range, range=criteria, if_empty), où seuls les deux premiers arguments de l'ensemble de données plus les critères et sa plage de conteneurs sont nécessaires. Le troisième argument est utile si vous voulez retourner quelque chose en particulier si la formule ne retourne pas un résultat nul, par exemple "Il n'y a pas de données" ou "Aucun".

Lisez aussi: Correction de la souris sans fil Logitech ne fonctionnant pas

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

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

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

Ajoutons des termes plus à la formule. Plusieurs critères sont ajoutés à l'aide d'un astérisque (*) pour ET et d'un signe plus (+) pour OU.

Par exemple, nous filtrons à la fois Colline Harold و Electronique en utilisant la formule suivante :

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

Pour diviser la formule, A2:C9 est l'ensemble de données, B2 : B9 = "Harold Colline" est la première condition, et l'astérisque représente AND , et A2 : A9 = "Électronique" est la deuxième condition.

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

Dans un autre exemple, vous pouvez filtrer par vêtements ou voitures :

=FILTER(A2:C9,(A2:A9="Apparel")+(A2:A9="Automotive"))

En divisant par cette formule, A2:C9 est l'ensemble de données, A2 : A9 = "Vêtements" est la première condition, et le signe plus représente OR , et A2 : A9 = "Automatique" est la deuxième condition.

Cette fois, nous avons reçu deux résultats, où la formule doit correspondre à l'une ou l'autre des conditions - pas aux deux.

Foire Aux Questions

Q1 : Comment puis-je trouver la structure de formule d'une fonction directement dans Excel ?
Répondre: Excel fournit un outil intégré pour trouver la fonctionnalité dont vous avez besoin. Avec lui, vous pouvez voir la syntaxe de la syntaxe de cette fonction.

Sélectionnez une cellule vide, accédez à l'onglet Formules et choisissez Insérer une fonction sur le côté gauche du ruban. Tapez le travail dans la zone de recherche en haut et cliquez sur Go. Lorsque vous voyez la fonction souhaitée, sélectionnez-la pour voir sa description et sa syntaxe au bas de la boîte.

Q2 : Comment puis-je déboguer une formule qui ne fonctionne pas correctement ?
Répondre: Sélectionnez la formule que vous rencontrez et accédez à l'onglet Formules. Choisissez "Évaluation du format" dans la section Audit du format du ruban.

Vous verrez votre formule dans la fenêtre Évaluer la formule avec une partie soulignée. Cliquez sur "Évaluer" pour voir le calcul de la partie soulignée. Continuez ce processus pour parcourir chaque partie de la formule jusqu'au résultat. Cela vous permet de voir comment la formule est traitée et où se trouve l'erreur.

Q3 : Comment savoir pourquoi j'obtiens une erreur de formule ?
Répondre: Lorsque vous voyez une erreur dans 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.

Vous pouvez également sélectionner Afficher les étapes de calcul pour ouvrir la fenêtre d'évaluation de la formule illustrée ci-dessus. Vous verrez le résultat du calcul et le processus pour y arriver, ce qui devrait aider à déterminer la cause de l'erreur.