Introduction

L’objectif de cette activité est d’apprendre à utiliser les plages de cellules nommées et l’utilisation de la fonction recherchev. Les concepts étudiés aux missions 1 à 3 sont également révisés.

Cahier des charges

L’objectif de cette mission est de mettre au point un classeur comprenant un catalogue de produits et une feuille de calcul du montant d’un devis. Un classeur à compléter est fourni en ressource.

Contraintes

  • Les feuilles de calcul doivent être renommées “Catalogue” et “Devis” et celle inutile supprimée.
  • Les cellules de saisies doivent être mises en évidence.
  • Aucun message d’erreur ne doit apparaître en l’absence de saisie.
  • Il faut un bouton pour réinitialiser la feuille de calcul (effacer les saisies).
  • Le classeur doit être protégé contre l’effacement involontaire de formules (la saisie doit rester possible).

Fonctionnement de la recherche

La fonction recherchev est utilisée pour rechercher une valeur dans la première colonne d’un tableau pour renvoyer la valeur dans une autre colonne de la même ligne. Exemple, soit l’extrait de classeur suivant :

A B
1 Id Libellé
2 1 Dune
3 2 Ravage
4 3 Arkane
5 4 Fondation
6 5 Endymion

La cellule D1 (par exemple) permet de saisir un identifiant de livre ; si le développeur souhaite que le libellé correspondant soit affiché en E1, il doit écrire la formule suivante (en E1) : =recherchev(D1;$A$2:$B$6;2) qui signifie recherche dans la première colonne de la table A2:B6 (avec références fixes) la valeur saisie dans la cellule D1, et renvoie le contenu de la deuxième colonne.

La syntaxe de la fonction recherchev est la suivante :

recherchev(valeur_cherchée; plage_de_cellules; index_colonne; colonne_1_triée)

Le dernier paramètre de cette fonction est un booléen (VRAI par défaut) qui indique si les valeurs de la première colonne sont triées par ordre croissant ; si ce n’est pas le cas, il faut mettre FAUX, mais l’algorithme de recherche n’est alors plus optimisé (dichotomique -> séquentielle).

Fonctionnalité Bonus (approfondissement) - Liste déroulante

On peut forcer l’utilisateur à ne saisir que des valeurs valides comme référence d’article en utilisant la fonctionnalité de validité des données. Cela améliore l’ergonomie (il se voit proposer une liste déroulante) mais demande plusieurs modifications :

  • ajouter une colonne au tableau contenant la liste des produits (avec pour titre “Liste déroulante” par exemple). La formule pour cette colonne doit permettre de concaténer l’identifiant et le libellé à affiché ; exemple : =CONCATENER(A2;" - "; B2)
  • nommer cette plage (une seule colonne) de cellule (liste_produits par exemple)
  • sélectionner les cellules de saisie des références, et ajouter une contrainte de validité des données (menu Données -> Validité). Indiquer la plage des valeurs autorisées (liste_produits).
  • la formule d’affichage du libellé peut être supprimée (il apparaît dans les résultats de la liste déroulante) et ces cellules peut être fusionnée avec celles de saisie de la référence.
  • La formule d’affichage du prix unitaire doit être adaptée, car la cellule de saisie de la référence contient désormais également le libellé : elle fait intervenir les fonctions additionnelles suivantes :
    • CHERCHE pour connaître la position du séparateur (“-”)
    • GAUCHE pour obtenir la sous-chaîne (la référence - qui précède le séparateur),
    • CNUM pour convertir la chaîne de caractères en nombre.
=SI(ESTVIDE($A5);"";RECHERCHEV(CNUM(GAUCHE(A5;CHERCHE("-";A5)-1));produits;SI(D$1=1;3;4)))