Utilisation de la fonction recherche dans l’analyse commerciale avec Excel
■ Comment puis-je écrire une formule pour calculer les taux d’imposition en fonction du revenu?
■ Étant donné un ID de produit, comment puis-je rechercher le prix du produit?
■ Supposons que le prix d’un produit change avec le temps. Je connais la date de vente du produit. Comment puis-je écrire une formule pour calculer le prix du produit?
| Syntaxe des fonctions de recherche
Les fonctions de recherche vous permettent de rechercher des valeurs dans des plages de feuilles de calcul. Dans Microsoft Excel , vous pouvez effectuer des recherches verticales (en utilisant la fonction RECHERCHEV) et des recherches horizontales (en utilisant la fonction RECHERCHEH). Dans une recherche verticale, l’opération de recherche commence dans la première colonne d’une plage de feuille de calcul. Dans une recherche horizontale, l’opération démarre dans la première ligne d’une plage de feuille de calcul. Étant donné que la majorité des formules utilisant des fonctions de recherche impliquent des recherches verticales, cette article se concentre sur les fonctions RECHERCHEV. Syntaxe RECHERCHE La syntaxe de la fonction RECHERCHEV est la suivante. Les crochets ([]) indiquent des arguments facultatifs. RECHERCHEV (valeur recherchée, plage , index de colonne, [recherche de plage]) ■ La valeur recherchée est la valeur que vous souhaitez rechercher dans la première colonne de la plage du tableau. ■ La plage est la plage qui contient la table de recherche entière. La plage comprend la première colonne, dans laquelle vous essayez de faire correspondre la valeur de recherche, et toutes les autres colonnes dans lesquelles vous souhaitez rechercher les résultats de formule. ■ L’index de colonne est le numéro de colonne dans la plage de tables à partir de laquelle la valeur de la fonction de recherche est obtenue. ■ La recherche de plage est un argument facultatif. Le point de recherche de plage est de spécifier une correspondance exacte ou approximative. Si l’argument de recherche de plage est Vrai ou omis, la première colonne de la plage de table doit être dans l’ordre numérique croissant. Si l’argument de recherche de plage est Vrai ou omis et qu’une correspondance exacte avec la valeur de recherche est trouvée dans la première colonne de la plage de table, Excel base la recherche sur la ligne de la table dans laquelle la correspondance exacte est trouvée. Si l’argument de recherche de plage est Vrai ou omis et qu’une correspondance exacte n’existe pas, Excel base la recherche sur la plus grande valeur de la première colonne qui est inférieure à la valeur de recherche. Si l’argument de recherche de plage est False et qu’une correspondance exacte avec la valeur de recherche est trouvée dans la première colonne de la plage de table, Excel base la recherche sur la ligne de la table dans laquelle la correspondance exacte est trouvée. Si aucune correspondance exacte n’est obtenue, Excel renvoie une réponse # N / A (non disponible). Notez qu’un argument de recherche de plage de 1 est équivalent à True, tandis qu’un argument de recherche de plage de 0 est équivalent à False. Syntaxe RECHERCHEH Dans une fonction RECHERCHEV, Excel essaie de localiser la valeur de recherche dans la première ligne (pas la première colonne) de la plage de table. Pour une fonction RECHERCHEH, utilisez la syntaxe RECHERCHEV et changez la colonne en ligne. Maintenant, explorez quelques exemples intéressants de fonctions de recherche. |
Comment puis-je écrire une formule pour calculer les taux d’imposition en fonction du revenu?
L’exemple suivant montre comment fonctionne une fonction RECHERCHEV lorsque la première colonne de la plage de tableaux se compose de nombres dans l’ordre croissant. Supposons que le taux d’imposition dépend du revenu, comme le montre le tableau .
TABLEAU 1 Taux d’imposition sur le revenu
Pour voir un exemple d’écriture d’une formule qui calcule le taux d’imposition pour n’importe quel niveau de revenu, ouvrez le fichier , illustré à la figure 1.
FIGURE 1: Utilisez une fonction de recherche pour calculer un taux de taxe. Les nombres de la première colonne de la plage du tableau sont triés par ordre croissant.
Tout d’abord, les informations pertinentes (taux d’imposition et points de rupture) ont été saisies dans la plage de cellules D6: E9. La plage de tables est appelée recherche D6: E9. Il est recommandé de toujours nommer les cellules que vous utilisez comme plage de tables. Si vous le faites, vous n’avez pas besoin de vous souvenir de l’emplacement exact de la plage de table et lorsque vous copiez une formule impliquant une fonction de recherche, la plage de recherche sera toujours correcte. Pour illustrer le fonctionnement de la fonction de recherche, certains revenus ont été entrés dans la fourchette D13: D17. En copiant la formule RECHERCHEV (D13, Recherche, 2, Vrai) de E13 à E14: E17, le taux d’imposition a été calculé pour les niveaux de revenu répertoriés dans D13: D17. Examinez le fonctionnement de la fonction de recherche dans les cellules E13: E17. Notez que parce que l’index de colonne dans la formule est 2, la réponse vient toujours de la deuxième colonne de la plage du tableau.
■ En D13, le revenu de – 1 000 $ donne # N / A parce que – 1 000 $ est inférieur au niveau de revenu le plus bas de la première colonne de la fourchette du tableau. Si vous souhaitez un taux d’imposition de 15% associé à un revenu de – 1 000 $, remplacez le 0 dans D6 par un nombre inférieur ou égal à -1 000.
■ En D14, le revenu de 30 000 $ correspond exactement une valeur dans la première colonne de la plage du tableau, la fonction renvoie donc un taux de taxe de 34%.
■ Dans D15, le niveau de revenu de 29 000 $ ne correspond pas exactement à une valeur dans la première colonne de la plage du tableau, ce qui signifie que la fonction de recherche s’arrête au plus grand nombre inférieur à 29 000 $ dans la première colonne de la plage, 10 000 $ dans ce cas. Cette fonction renvoie le taux de taxe dans la colonne 2 de la plage du tableau en face de 10 000 $, ou 30%.
■ En D16, le niveau de revenu de 98 000 $ ne donne pas une correspondance exacte dans la première colonne de la plage du tableau. La fonction de recherche s’arrête au plus grand nombre inférieur à 98 000 $ dans la première colonne de la plage du tableau. Cela renvoie le taux d’imposition dans la colonne 2 de la plage du tableau en face de 30 000 à 34%.
■ En D17, le niveau de revenu de 104 000 $ ne donne pas une correspondance exacte dans la première colonne de la plage du tableau. La fonction de recherche s’arrête au plus grand nombre inférieur à 104 000 $ dans la première colonne de la plage de tableau, ce qui renvoie le taux de taxe dans la colonne 2 de la plage de tableau en face de 100 000 à 40%.
Dans F13: F17, la valeur de l’argument de recherche de plage est passée de Vrai à Faux et la formule RECHERCHE (D13, Recherche, 2, Faux) a été copiée de F13 à F14: F17. La cellule F14 génère toujours un taux d’imposition de 34% car la première colonne de la plage du tableau contient une correspondance exacte à 30 000 $. Toutes les autres entrées de F13: F17 affichent # N / A car aucun des autres revenus de D13: D17 n’a de correspondance exacte dans la première colonne de la plage du tableau.
Étant donné un ID de produit, comment puis-je rechercher le prix du produit?
Souvent, la première colonne d’une plage de tableaux ne se compose pas de nombres dans l’ordre croissant. Par exemple, la première colonne de la plage de tableaux peut répertorier les codes d’identification de produit ou les noms d’employés. J’ai constaté que beaucoup de gens ne savent pas comment gérer les fonctions de recherche lorsque la première colonne de la plage du tableau ne se compose pas de nombres dans l’ordre croissant. Dans ces situations, souvenez-vous d’une seule règle simple: utilisez False comme valeur de l’argument de recherche de plage.
Voici un exemple. Dans la figure ci-dessous, vous pouvez voir les prix de cinq produits, répertoriés par leur code ID de produit. Comment écrivez-vous une formule qui prend un code d’identification de produit et renvoie le prix du produit?
FIGURE : Recherchez les prix à partir des codes d’identification des produits.
Lorsque la plage du tableau n’est pas triée par ordre croissant, entrez Faux comme dernier argument dans la formule de la fonction de recherche.
Beaucoup de gens entrent la formule comme dans la cellule I18: RECHERCHEV (H18, Recherche2,2). Toutefois, notez que lorsque vous omettez le quatrième argument (l’argument de recherche de plage), la valeur est supposée être Vrai.
Étant donné que les ID de produit dans la plage de table RECHERCHE2 (H11: I15) ne sont pas répertoriés par ordre alphabétique, un prix incorrect (3,50 $) est renvoyé. Si vous entrez la formule RECHERCHEV (H18, Recherche2,2, Faux) dans la cellule I18, le prix correct (5,20 $) est renvoyé.
Vous utiliseriez également False dans une formule conçue pour trouver le salaire d’un employé en utilisant le nom de famille ou le numéro d’identification de l’employé.
À propos, vous pouvez voir sur la figure 2 que les colonnes A à G sont masquées. Pour masquer des colonnes, vous commencez par sélectionner les colonnes que vous souhaitez masquer. Cliquez sur l’onglet Accueil du ruban. Dans le groupe Cellules, choisissez Format, pointez sur Masquer et afficher (sous Visibilité), puis choisissez Masquer les colonnes.
Supposons que le prix d’un produit change avec le temps. Je connais la date de vente du produit. Comment puis-je écrire une formule pour calculer le prix du produit?
Supposons que le prix d’un produit dépend de la date à laquelle le produit a été vendu. Comment pouvez-vous utiliser une fonction de recherche dans une formule qui récupérera le prix correct du produit? Plus précisément, supposons que le prix d’un produit soit comme indiqué dans le tableau suivant:
Écrivez une formule pour déterminer le prix correct du produit pour toute date à laquelle le produit est vendu en 2005. Pour la variété, utilisez une fonction RECHERCHEV. Les dates auxquelles les prix changent sont dans la première ligne de la plage du tableau. Voir le fichier , illustré à la figure ci-dessous.
FIGURE :Utilisez une fonction RECHERCHEH pour déterminer un prix qui change en fonction de la date de vente.
La formule RECHERCHEH (B8, recherche, 2, VRAI) est copiée de C8 à C9: C11. Cette formule essaie de faire correspondre les dates de la colonne B avec la première ligne de la plage B2: D3. À toute date entre le 01/01/05 et le 30/04/05, la fonction de recherche s’arrête au 01/01/05 et renvoie le prix en B3; pour toute date entre le 5/1/05 et
31/07/05, la recherche s’arrête au 01/05/05 et renvoie le prix en C3; et pour toute date postérieure au 01/08/05, la recherche s’arrête au 01/08/05 et renvoie le prix en D3.