La fonction =RECHERCHEV()
Cette fonction requiert 4 arguments et se présente ainsi :
=RECHERCHEV(valeur_cherchée;table_matrice;no_index_col;valeur_proche)
La fonction va rechercher, dans une table_matrice, la valeur_cherchée que l’on va lui indiquer. Quand elle aura trouvé cette valeur (nombre ou texte), elle va renvoyer le contenu de la cellule se trouvant sur la même ligne que la valeur trouvée et dans la colonne désignée par no_index_col.
Si la valeur_cherchée n’existe pas dans la table_matrice, la fonction affichera :
- si on choisi le nombre 0 pour l’argument valeur_proche : un message d’erreur
- si on choisi le nombre 1 pour l’argument valeur_proche : le contenu de la colonne, déterminée par no_index_col, correspondant à la valeur précédente la plus proche de la valeur_cherchée,
Dans notre exemple :
Pour élaborer une facture, nous allons utiliser la fonction =RECHERCHEV().
Tout d’abord, nous construisons 3 tableaux, un sur chaque feuille du classeur, :
- un modèle de facture,
- un tableau des articles (nous nommerons la zone » articles » pour la réutiliser dans les formules)
- et un tableau de remises (que nous nommerons » remise « ).
La première formule à créer permettra d’afficher automatiquement la désignation de l’article (dans la colonne » désignation « ) dont le code aura été saisi en A2 (colonne » code article). Nous allons construire cette formule dans la colonne » Désignation » (en B2)
La formule, que l’on va construire dans la cellule B2, sera donc la suivante :
La formule, que l’on va construire dans la cellule B2, sera donc la suivante :
- La valeur_cherchée sera le code de l’article, préalablement saisi en A2.
- La table_matrice est la plage de cellules que l’on a préalablement nommé » Articles « .
- no_index_col sera ici le chiffre 2 qui correspond à la deuxième colonne de notre zone » Articles « , et qui contient le nom des articles.
- valeur_proche sera ici le chiffre 0 (zéro) qui permettra d’afficher un message d’erreur si le code article saisi n’existe pas.
la formule sera donc la suivante : =RECHERCHEV(A2;Articles;2;0) Le résultat sera le suivant :
La fonction construite en B2 renvoie la valeur » Armoire 1 porte « . En effet :
- la fonction recherche la valeur 1, saisie en A2, dans la zone » Articles » en se déplaçant VERTICALEMENT dans la première colonne de la zone.
- une fois la valeur_cherchée trouvée, la fonction lit la ligne correspondante dans la zone jusqu’à la colonne choisie dans no_index_col.
- la fonction renvoie la valeur de cette cellule.
Pour obtenir le prix de l’article, la fonction est la même que la précédente, mis à part le no_index_col qui est ici le chiffre 3 (troisième colonne de la zone » Articles « ). La fonction construite en D2 est la suivante :
=RECHERCHEV(A2;Articles;3;0)
- Pour obtenir le montant total, on multiplie la quantité en C2 par le prix obtenu en D2. la formule est la suivante : =C2*D2
- Ces formules de la ligne 2 sont à recopier sur les lignes suivantes.
- Le total hors taxes de la facture s’obtient en faisant la somme des lignes de la facture : =SOMME(E2:E8)
Le montant de la remise sera fonction du total hors taxes de la facture situé en E8. On applique ici un barème par tranches. Par exemple, entre 5000 € et 7000 €, on applique 5% de remise. Dès que le montant hors taxes dépasse 7000 € et jusqu’à ce qu’il atteigne 10000 €, le taux de remise sera de 8%, etc.
Dans notre tableau des remises, tous les cas de figure ne sont pas prévus. Seuls les seuils sont représentés. la fonction de recherche se basera donc sur ces seuils pour renvoyer le taux de remise à appliquer. Si le total de la facture est différent du montant d’un des seuils (cas général), la fonction renverra le taux de remise correspondant au seuil inférieur le plus proche.
La fonction (construite dans notre exemple en E9) sera donc la suivante : =RECHERCHEV(E8;Remises;2;1)*E8
- E8 est la cellule contenant le total hors taxes qui est la valeur_cherchée dans la table_matrice.
- Remises est le nom de la table_matrice (ou plage de cellules) contenant le barème des remises où la recherche sera effectuée.
- 2 est le numéro de la colonne de la table_matrice » remise » qui contient le taux de remise à appliquer.
- 1 signifie qu’en l’absence de la valeur_cherchée dans la table_matrice, la valeur inférieure la plus proche sera renvoyée.
Le résultat de la recherche est multiplié par le montant hors taxes en E8 (*E8) pour obtenir le montant de la remise.
Pour un total hors taxes de 6325 € le taux de remise applicable sera donc 5%
La fonction =INDEX()
La fonction INDEX permet de rechercher des données dans une table à l’intersection d’une ligne spécifique et d’une colonne dans Microsoft Excel.
Il existe deux formes de fonction INDEX, voici décrite la fonction INDEX tableau.
L’objectif est de trouver le code article à partir de sa désignation. Une liste déroulante sera utilisée pour obtenir l’agument no_lig utilisé dans la formule.
Lorsque la liste déroulante a été crée, le résultat du choix s’inscrira (dans cet exemple) dans la cellule E5. On va ensuite construire la formule =INDEX dans la cellule E8.
Dans « Matrice« , entrez la plage de cellules du tableau. Ici A2:C9
Dans « No_lig« , entrez la référence de cellule contenant le résultat renvoyé par la liste déroulante. Ici E5
Dans « No_col« , entrez la colonne du tableau qui contient les données à renvoyer (dans cet exemple, le but est d’afficher le code article, c’est donc la colonne 1).
La syntaxe sera donc la suivante : =INDEX(A2:C9;E5;1)
La fonction =CHOISIR()
Exemple avec des taux de remise variables en fonction d’un code (1,2 ou 3) affichant des remises de 5%, 10% ou 15 % :
La cellule A1 contient le code d’escompte (1, 2 ou 3) que vous saisissez au clavier
La formule en B1 permet d’afficher le taux de remise en fonction du code saisi en cellule A1.
La syntaxe est : =CHOISIR(A1;5%;10%;15%)
où Si le code de remise est « 1 » la valeur de B1 sera 5%, si le code de remise est « 2 » la valeur de B1 sera 10% et si le code d’escompte est « 3 », la valeur de B1 sera 15%.
La formule peut contenir autant de variables que vous le désirez.