Automatiser une saisie sur Libre Calc avec une table de correspondance

Passage en revue de deux fonctions bien pratiques pour automatiser la saisie d'informations sur Libre Calc, avec cas concret.

Hier, c'était le rush à partir de 23h pour bricoler le plus rapidement possible des cartes de Strasbourg par bureau de vote et du Bas-Rhin par commune.

J'avais déjà les tracés géographiques de toutes les zones, et le squelette des fichiers de résultats qui allaient être envoyés. La seule difficulté allait être de déterminer rapidement le vainqueur de chaque entité, pour lui rattacher ensuite une couleur hexadécimale.

Concrètement, si dans la commune X le parti Y est arrivé en tête, il faut que dans la colonne "couleur" corresponde à la teinte prévue pour lui.

Y a-t-il une fonction qui peut nous aider dans cette tâche ? Il y en a en fait deux : RECHERCHE() et RECHERCHEV().

Afficher le nom du vainqueur avec RECHERCHE()

Prenons le cas concret ci-dessous. J'ai séparé mes résultats en brut d'un côté et en pourcentage de l'autre, et décidé d'insérer entre les deux une colonne "Vainqueur" et une colonne "Code couleur" :

Cliquez pour agrandir l'image

Pour afficher convenablement le nom du vainqueur, il faut bien décomposer les trois facteurs de la fonction RECHERCHE() :

• d'abord, le terme recherché. Cela peut être un nombre, chaîne de caractères, etc... Mais également une formule, ce qui nous intéresse franchement dans ce cas. Par exemple MAX(B2:I2) nous renverra le maximum de la ligne 2 entre les colonnes B et I. On l'applique donc sur les cellules correspondant aux résultats bruts des candidats. On pourrait aussi le faire pour des pourcentages, la logique est la même
• ensuite, la zone de recherche. Dans notre cas, on sélectionne la zone scrutée avant
• enfin, la zone de résultat. Là, il faut survoler les noms des candidats concernés sur la première ligne.

Dans notre cas,on écrirait donc dans la première cellule vide de la colonne "Vainqueur" :

=RECHERCHE(MAX(J2:AF2),J2:AF2,J1:AF1)

On peut alors vérifier que le nom renvoyé est bien celui de la ligne J1:AF1 correspondant au maximum de la ligne J2:AF2.

L'homme pressé ne manquera alors pas de saisir le coin inférieur droit de sa cellule remplir pour dupliquer la formule à la ligne suivante, sauf que s'il fait ça il se retrouvera avec un chiffre, et non un nom.

Cliquez pour agrandir l'image

Ceci parce que Calc pensera que la colonne de référence des noms J1:AF1 est maintenant J2:AF2. Il faut donc lui expliquer que cette ligne ne bouge jamais, grâce au symbole $.

Notre formule finale sera donc :

=RECHERCHE(MAX(J2:AF2),J2:AF2,J$1:AF$1)

Elle renverra exactement le même résultat pour la première cellule, en revanche on n'aura plus un nombre mais bien un nom sur la deuxième :

Cliquez pour agrandir l'image

Etonnant, non ?

Une table de correspondance avec RECHERCHEV()

Bien, la première étape est réglée. Nous allons maintenant tenter d'utiliser cette table de correspondance, placée juste en-dessous de nos données :

Cliquez pour agrandir l'image

Première sécurité : transformer le résultat de la formule en vrai texte. Pour cela, le plus simple est de copier la colonne "Vainqueur" dans une nouvelle colonne, en faisant un clic droit et en sélectionnant "Collage spécial" :

Cliquez pour agrandir l'image

De là, on va simplement décocher "Tout insérer", ne laisser que "Texte" coché, et cliquer sur OK :

Cliquez pour agrandir l'image

On peut alors vérifier dans la nouvelle colonne qu'on a bien du texte et plus une formule, ce qui va nous permettre de supprimer l'autre :

Cliquez pour agrandir l'image

On peut maintenant utiliser la formule RECHERCHEV(), dont la syntaxe est la suivante :

• d'abord, le terme recherché. Dans ce cas, il va correspondre au nom du vainqueur
• la matrice de référence. Dans notre cas, il va s'agir de la table de correspondance décrite avant
• troisièmement, l'indice de tri. Il correspond à la colonne de référence pour le résultat que l'on souhaite. Dans notre cas, on va choisir 2, puisque c'est dans la seconde colonne de la matrice que se trouvent les codes couleur
• enfin l'ordre de tri. On le paramètre à 0 pour bien stipuler qu'il faut que la valeur recherchée corresponde précisément à l'exact premier terme rencontré

Appliquée à notre cas, cette formule donne :

=RECHERCHEV(AG2,A$150:B$155,2,0)

On a placé des $ sur les bords de la matrice pour les mêmes raisons que les précédentes. Un cliqué-glissé après, voilà le résultat :

Cliquez pour agrandir l'image

Emballé, c'est pesé !

Remerciements

Merci à Joël Matriche, véritable "excpert", qui m'a aiguillé vers ces formules bien pratiques !