Remplir deux cellules à la fois

jeudi 22 février 2018, par ID

Dans un tableau, quand les données d’une cellule dépendent d’une autre, la fonction RECHERCHEV se révèle très pratique pour que la deuxième cellule se remplisse automatiquement quand une information est entrée dans la première. Mais évidemment, pour gagner en fiabilité, au lieu de saisir les données avec nos petits doigts en risquant de faire des fautes, il vaut mieux choisir les éléments de la première cellule dans une liste déroulante. Après ce tutoriel, vous aussi vous saurez le faire.

Dans le cadre de ce tutoriel, on utilisera les fonctionnalités plages et expressions nommées (noms définis dans Excel) et les options de validité (ou de validation) des cellules ainsi que la fonction RECHERCHEV. Les captures d’écran sont faites à partir de Calc de LibreOffice 6 mais cela fonctionne à l’identique pour les autres versions et de façon similaire, à quelques nuances près dans Excel. Le point de départ de ce tutoriel est plus ou moins fictif puisqu’il s’agissait d’établir un devis de façon quasi-automatique. Le devis exemple et parfaitement fictif est offert en téléchargement au format ods.

 Le fonctionnement

La première feuille, appelée « devis », est celle où l’on remplit les données. En pratique, on choisit un élément dans la liste déroulante de la première colonne (A), on saisit la date dans la colonne B, la troisième colonne (C) affiche automatiquement le montant que le tableur va chercher aussi dans la deuxième feuille, la feuille « tarifs ».

Le devis
Le tableau final mis en forme

 Les données et la structure

Les données : ici une liste des salles avec les variantes qui conditionnent les tarifs ainsi que les montants de location. Lesquels, soit dit en passant sont calculés à partir du tarif de base de chaque salle.

Les types de données : du texte pour les noms des salles, des dates mises en forme pour faire apparaitre le nom du jour, des pourcentages et des euros pour déterminer le cout des salles.

La structure : deux feuilles, la première est celle du devis, la seconde, qui peut être masquée, est celle qui contient tous les éléments de calculs : noms des salles, tarifs, calculs des majorations. Dans le cadre de ce tutoriel, je ne m’étendrai ni sur le prix des salles, ni sur leur calcul.

 Créer les listes

Dans la feuille « tarifs », saisir les noms des salles avec les variantes.

👓 Très important pour la suite et éviter des erreurs : taper des espaces dans les cellules A2 et B2, cela leur donne un contenu invisible qui permet au tableur de faire des recherches dedans sans afficher d’erreur et, par la suite, de calculer les totaux du devis.

Saisir les montants, ou les calculer ou les deux.

Les listes
La plage A2:A21 servira pour le remplissage de la feuille « devis ».

👓 À noter : pour rendre le fichier plus évolutif, les tarifs se calculent automatiquement à partir des tarifs de base. Tant les tarifs de base, cellules D6 à D9, que les taux, cellules D2 à D3 sont autant d’expressions nommées et ce sont ces noms qui nous servent dans les calculs.

Sélectionner les cellules, ici de A2 à B18 les trier pour ordonner la colonne A dans l’ordre alphabétique croissant. C’est extrêmement important pour la fonction RECHERCHEV.

Sélectionner la colonne A, de A2 à A21 [1], et la nommer, l’appeler « Salles ». Dans Calc, la fonctionnalité de nommage se trouve dans le menu Feuille > Plages ou expressions nommées, dans Excel, c’est dans l’onglet Formule, groupe Gestionnaire de noms. Sélectionner également l’étendue A2:B21, en faire une plage de la même façon. On l’appellera « tarification »

À partir de maintenant, on n’ira plus sur cette feuille « tarifs ».

Dans la feuille « devis », sélectionner les cellules concernées de la colonne A, ici, de A4 à A8. Aller dans Données > Validité (dans Excel onglet Données > Validation des données). Dans le cartouche Autoriser, choisir Plage de cellules (dans Excel sélectionner Liste), indiquer le nom de la plage, ici « Salles ». Au besoin, on peut ajouter un message d’aide à la saisie ou un message d’erreur, ce qui est très conseillé quand plusieurs personnes doivent travailler sur ce genre de tableau.

Critère de validité
Choix d’une plage pour la liste déroulante.

Une flèche, typique des listes déroulantes, apparait dans la cellule quand elle est activée, sinon non, et le contenu de la liste s’affiche, il ne reste plus qu’à choisir.

La liste déroulante
Quand on clique sur la flèche, le contenu de la liste apparait.

👓 À noter : l’autocomplétion fonctionne aussi dans ce type de cellules.

 La fonction RECHERCHEV

La fonction RECHERCHEV permet de rechercher des éléments par ligne. Elle va chercher une valeur dans la première colonne de la plage, qui n’est pas nécessairement celle du tableau.

Se placer sur la cellule qui doit accueillir la fonction, ici C4.

Le Critère de recherche (ou Valeur cherchée) est l’élément de référence, celui qui est forcément dans la première colonne de la plage. Ici, il s’agit de la cellule A4 du devis : la désignation de la salle en première ligne.

La Matrice (ou Table matrice) est la plage de référence de la feuille « tarifs » qu’on a précédemment appelée « tarification ».

L’Indice est le numéro de la colonne dans laquelle le tableur doit chercher les données à afficher. C’est le numéro de la colonne du tableau, pas de la plage. Ici, il s’agit de la deuxième colonne de la feuille « tarifs ».

L’Ordre de tri (Valeur proche) indique si la première colonne de la matrice, ici la plage « tarification », est triée en ordre croissant. Ce qui est conseillé pour des questions de rapidité et pour avoir une valeur proche si le tableur ne trouve pas la correspondance exacte [2], dans ce cas, laisser le critère vide. Quand les listes ne sont pas triées, la valeur de recherche doit être absolument identique, sinon le tableur renvoie un message d’erreur, dans ce cas indiquer FAUX. C’est un paramètre facultatif.

La fonction RECHERCHEV
Paramètres de la fonction dans l’assistant du même nom.

Il ne reste plus qu’à étendre la formule aux autres cellules concernées, ici elle s’applique aux cellules C2 à C8.

Voilà c’est fini !


Pour compléter : Tableurs : travailler avec des noms


[1Je suggère, dans ce cas, de sélectionner toujours un peu plus de cellules que le strict nécessaire pour rendre le tableau encore plus facilement évolutif.

[2Et parce que sinon, on peut avoir des résultats curieux.