[Résolu] Vérifier la présence d'un terme dans plusieurs listes de valeurs possibles

[PierreB35]

Bonjour à tous !

On m'a demandé un petit service sur Libre Office Calc, qui sur le moment me paraissait simple, mais dans les faits je rame un peu . Dans le tableur fourni je dispose :

en feuille 1 : d'une liste de centaines de personnes (une ligne par personne),associées à leur commune de résidence (une seule commune par personne)
Il y a pas mal de communes possibles, toutes n'appartiennent pas à la même communauté de communes.

- en feuille 2 : toutes les communautés de communes qui regroupent les communes de la feuille 1 (une colonne par ComCom).

Je joins un fichier .ods pour l'exemple.

L'objectif est de regrouper rapidement les personnes de la feuille 1 par communauté de commune. On peut le faire avec des filtres mais c'est un peu long. Mon idée était d'utiliser une formule, en fin de chaque ligne de la feuille 1, du type :

Code : Tout sélectionner

si la commune est comprise dans colonne 1 de la feuille 2, alors afficher "Nom de la ComCom de la colonne 1" ; 
Si est comprise dans la colonne 2 de la feuille 2, alors afficher "Nom de la ComCom de la colonne 2", 
Si est comprise dans la colonne 3 de la feuille 3, [i]etc[/i].

J'ai tâtonné avec des formule Si, Si imbriqué, Si.Multiple, NB.SI mais il y a toujours des erreurs que ne je comprends pas. La seule formule qui fonctionne pour l'instant est celle-ci (cf tableur exemple) :

Code : Tout sélectionner

=SI(NB.SI($Feuille2.$A$2:$A$3;B2);"CC Truc Communauté";0)

Ca fonctionne seulement pour la plage renseignée (A2:A3) donc seulement pour la première CC. Y aurait-il un moyen de développer la formule en imbriquant les tests pour que la recherche se fasse, dans une seule formule, pour chaque colonne de la feuille 2 (donc pour chaque ComCom) ?

Sinon je peux toujours créer autant de colonnes pour ma formule que de ComCom (8 au total), et changer à chaque fois la plage de recherche, mais il doit y avoir moyen de tout regrouper...

Merci d'avance !

[yclik]

Bonjour
Une proposition en ordonnant les données de la la feuille 2 autrement.

communautés.ods

[Jean-Louis Cadeillan]

Bonjour,
Une solution peut passer par la fonction SI() (ici en C2 du fichier joint) :

Code : Tout sélectionner

=INDEX(Feuille2.$A$1:$H$1;1;MAX(SI(B2=Feuille2.$A$1:$J$20;COLONNE(Feuille2.$A$1:$J$20);"")))

C'est une formule matricielle, donc validée après saisie par Maj+Ctrl+Entrée. Pour recopier cette formule matricielle vers le bas :

• il suffit de positionner la souris dans le coin inférieur droit de la cellule : le curseur se transforme en croix (+), puis, il faut cliquer/glisser vers le bas tout en appuyant simultanément sur la touche Ctrl ;
• une autre manière de faire est de copier la cellule C2(Ctrl+C), puis de sélectionner la plage des cellules qui doivent contenir la même formule, puis de coller (Ctrl+V).

Si je traduis en langage courant ce que fait la formule :

• Si la commune en cours (B2) est égale à une des communes de la plage Feuille2.$A$1:$J$20 (j'ai prévu 10 communautés de 19 communes au maximum, mais on peut l'adapter en fonction), alors retourner le n° de la colonne concernée, sinon ne rien retourner
• MAX() retourne ce N° de colonne, vu que c'est forcément la plus haute valeur de la plage retournée par SI()
• INDEX() va chercher le nom de la ComCom correspondant.

NB : je n'ai pas ajouté de contrôle d'erreur (par exemple dans le cas où la commune de la Feuille1 n'est pas présente en Feuille2), mais on pourrait le faire…
À tester ci-joint.
Cordialement,
Jean-Louis

[PierreB35]

Vous êtes géniaux merci beaucoup !

yclick : je me sens bête de ne pas y avoir pensé, ça fonctionne nickel !

Jean-Louis Cadeillan : Effectivement la formule ne détecte pas les erreurs (de nombreuses communes de la feuille 1 ne sont pas présentes dans la feuille 2, la faute au fichier qu'on m'a originellement transmis). Ce n'est pas un réel problème car la formule de yclick les met bien en évidence, mais par curiosité quelle sera la modification à apporter à la formule matricielle pour ajouter un contrôle d'erreur ?

[Jean-Louis Cadeillan]

Bonjour,

quelle sera la modification à apporter à la formule matricielle pour ajouter un contrôle d'erreur

Par exemple, si la fonction MAX retourne 0, alors la formule n'affiche rien (ici en C2) :

Code : Tout sélectionner

=SI(MAX(SI(B2=Feuille2.$A$1:$J$20;COLONNE(Feuille2.$A$1:$J$20);""))=0;"";INDEX(Feuille2.$A$1:$H$1;1;MAX(SI(B2=Feuille2.$A$1:$J$20;COLONNE(Feuille2.$A$1:$J$20);""))))

À tester ci-joint,
Cordialement,
Jean-Louis

[PierreB35]

Bonjour,

Testé et validé, la formule affiche désormais une cellule vide en cas de communes en feuille 1 non répertoriée en feuille 2.

Je n'ai pas encore tout-à-fait compris la logique de la formule mais je vais continuer à étudier ça, et les formules matricielles en général qui semblent vraiment utiles dans certains cas.

Encore merci !

[Jean-Louis Cadeillan]

Je n'ai pas encore tout-à-fait compris la logique de la formule

Tu peux faire un test très simple : tu entres cette formule dans une cellule (dans une zone libre à droite et en dessous) de Feuille1 :

Code : Tout sélectionner

=SI(B2=Feuille2.$A$1:$J$20;COLONNE(Feuille2.$A$1:$J$20);"")

en validant par Ctrl+Maj+Entrée, donc comme formule matricielle (la fonction SI() ne travaille pas nativement avec des plages, on la force ici à le faire).
Le résultat sera une plage de 10 colonnes et 19 lignes, vide, sauf la cellule à la 3me ligne et 1re colonne qui contiendra un 1. C'est la seule valeur qui t'intéresse, ce 1 désigne la première colonne de Feuillle2, donc la première ComCom : SI() a prospecté toutes les communes de Feuille 2 (plage A1:J20) et a trouvé la correspondance avec la commune contenue dans B2 de Feuille1, d'où le 1.
Il faut donc s'imaginer que cette petite partie de la formule totale est l'équivalent d'une plage !
MAX() est une fonction qui sait nativement utiliser des plages de cellules : elle va retourner, de la plage virtuelle, imaginaire, créée par SI(), la valeur maximale… c'est-à-dire 1.
Pour analyser le fonctionnement d'une formule matricielle, c'est donc une bonne méthode que de prendre certaines parties de la formule générale et de les tester dans des zones vides, pour comprendre leur comportement…
Cordialement,
Jean-Louis