[Résolu] Personnaliser une valeur de validité

[Maxime Pierre]

Bonjour,

Je cherche à créer un outil de création de table de rappel suivant le Major System.
Pour faire court :
- Une table de rappel est une table qui à chaque nombre (ici entre 0 et 99) associe un mot ;
- Le Major System est un système qui donne une sorte de méthode pour choisir ces association, via la phonétique. Chaque chiffre est associé à une consonne (par exemple, 0 c'est "s" ou "z", 1 c'est "d" ou "t").
Je voudrais donc créer un tableau avec, en première colonne, le numéro, et en seconde, dans chaque cellule, une liste déroulantes avec tous les mots valides.
Exemple : pour le nombre 10, il faut un mot qui ait comme consonnes phonétiques "d" ou "t" puis "s" ou "z" (on ne se préoccupe pas des voyelles) : "tasse" ou encore "indice" sont des mots valides.

Pour cela, j'ai importé dans une autre feuille la base de données Lexique 3, qui recense les mots de la langue française, avec notamment leur écriture phonétique.
À présent, je voudrais mettre une valeur de validité dans les cellules de ma deuxième colonne, afin de n'accepter que les mots français (colonne A de ma 2e feuille) qui correspondent aux critères phonétiques définis par les nombres de la première colonne. Problème : je ne comprends pas bien quelle fonction(s) je dois utiliser ni comment les utiliser, et l'aide LibreOffice ne m'avance pas beaucoup...

J'espère que j'ai été assez clair.

D'avance merci !
Bon après-midi.
Mapi

[micmac]

Bonjour,

LibreOffice 5.1.4 sous Windows 10

Cette version de LibO est obsolète.
Si c'est une obligation, mentionnez-le dans votre signature par Obligation de version.

Afin de profiter des dernières améliorations, il est fortement recommandé d'être à jour de la dernière version stable

Accès direct à votre signature pour la corriger.

[Maxime Pierre]

C'était une erreur que j'ai corrigée.

[Jean-Louis Cadeillan]

Bonsoir,

leur écriture phonétique

Laquelle ? Quel alphabet phonétique utilises-tu ?
Comme ce système mnémotechnique n'est basé que sur les consonnes, il va falloir quelque part enlever les voyelles parasites des mots de ton lexique...
Cordialement,
Jean-Louis

[Maxime Pierre]

leur écriture phonétique

Laquelle ? Quel alphabet phonétique utilises-tu ?

Je joins l'alphabet phonétique à ce post.

Comme ce système mnémotechnique n'est basé que sur les consonnes, il va falloir quelque part enlever les voyelles parasites des mots de ton lexique...

De ce côté-là je voudrais utiliser les expressions régulières. C'est le plus pratique pour vérifier qu'une chaîne de caractères remplit des conditions assez complexes.
Et je sais déjà ce que je veux : si je reprends l'exemple du 10, l'expression à utiliser est :

Code : Tout sélectionner

^[aiyuoOeE°2951@§3j8w]*[sz]*[aiyuoOeE°2951@§3j8w]*[sz]*[aiyuoOeE°2951@§3j8w]*$

Je traduis.
^ signifie "début de mot et $ "fin de mot". Autrement dit, il ne peux pas y avoir de caractères avant et après.
[abc] signifie "a, b ou c".
* signifie "le caractère suivant, répété 0, 1 ou plusieurs fois".
Donc on cherche ici un mot composé d'une chaîne de voyelles (aiyuoOeE°2951@§3j8w étant la liste des sons de voyelles et semi-voyelles), puis d'une chaîne de consonnes consonnes formant le son d ou t (afin d'accepter les double consonnes), puis d'une autre chaîne de voyelles, puis d'une chaîne de consonnes formant les sons s ou z, et enfin d'une dernière chaîne de voyelles.
Chaque chaîne pouvant être composée de 0, 1 ou plusieurs caractères.

LibreOffice Calc prend en charge ce type de valeurs, il suffit de lui dire dans les paramètres. Ce qui me pose problème, c'est plutôt d'accéder à ces vérifications-là depuis la validation des cellules...

[Jean-Louis Cadeillan]

Comme le but est de récupérer tous les mots qui correspondent à un nombre donné, je suis resté sur l'idée de faire une formule de substitution. La voici (en supposant que le mot phonétique est en B2):

Code : Tout sélectionner

=SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(B2;"a";"");"i";"");"y";"");"u";"");"o";"");"O";"");"e";"");"E";"");"°";"");"2";"");"9";"");"5";"");"1";"");"@";"");"§";"");"3";"");"j";"");"8";"");"w";"");"z";"0");"s";"0");"d";"1");"t";"1");"n";"2");"m";"3");"r";"4");"R";"4");"l";"5");"S";"6");"Z";"6");"g";"7");"G";"7");"k";"7");"v";"8");"f";"8");"b";"9");"p";"9");"N";"2")

Ensuite il suffit de copier cette formule aussi loin que nécessaire, de remplacer la formule par la valeur (copier/collage spécial), d'enlever la colonne phonétique, de mettre les valeurs Nombre textuelles en première colonne et les mots en deuxième colonne. Dans la solution ci-joint, j'ai créé une feuille Consultation qui permet en entrant un nombre (en A2), d'avoir les 390 premiers mots (s'ils existent) en visuel sur la page. J'ai ajouté le son gn comme 2. La feuille Nombre-Mot recense tous les mots de la langue française de Lexique 3.
La formule sur la feuille consultation (ici en B3) est :

Code : Tout sélectionner

=SI(ESTERR(INDEX(Mot;PETITE.VALEUR(SI(Nb=A$2;LIGNE(Nb);"");LIGNE(A1))));"";INDEX(Mot;PETITE.VALEUR(SI(Nb=A$2;LIGNE(Nb);"");LIGNE(A1))))

Elle devrait marcher tant dans LibO que AOO. C'est une formule matricielle. Mot et Nb sont des plages nommées, respectivement $'Nombre-Mot'.$B$1:$B$142460 et $'Nombre-Mot'.$A$1:$A$142460. Elle est un peu lente, il doit y avoir moyen d'optimiser le résultat (je n'ai pas trop creusé). Bien sûr, si on demande moins de mots, ça ira plus vite...

Ajout : Une seconde version qui va au moins deux fois plus vite au chargement et dix fois plus vite à l'exécution. Il n'y a plus qu'une seule formule matricielle, pour trouver le dernier mot d'un nombre, en A4 de Consultation, du coup la formule pour chercher le nième mot est simplifiée au maximum et n'est plus matricielle...

Cordialement,
Jean-Louis

[Maxime Pierre]

Wow beau travail !
Pour ma part je suis resté sur un tableau avec une formule de validité. Seul bémol : comme j'utilise la validité personnalisée et pas par plage de données, je ne peux pas insérer de liste déroulante... Et le chargement est beaucoup plus long que dans ta version. Je vais m'en inspirer pour l'améliorer. Merci beaucoup !

Mapi

[Jean-Louis Cadeillan]

Bonsoir,
La version 3 utilise aussi une liste déroulante en C1 de Consultation. La formule de validité est la suivante :

Code : Tout sélectionner

DECALER(Mot;A3-1;0;A4-A3+1)

et utilise la propriété que DECALER() a de créer en sortie une plage de cellules : le deuxième argument indique le décalage (basé sur le n° de ligne où se trouve le premier mot valable, le quatrième la longueur de la plage-vecteur (basé sur le n° de ligne où se trouve le dernier mot valable) .
L'avantage de la liste déroulante est qu'elle donne tous les mots valables, l'inconvénient est qu'elle ne les affiche pas tous simultanément.
A contrario, l'affichage par tableau d'un maximum de mots sur l'écran permet de repérer plus vite le mot qui conviendra pour la phrase recherchée, mais dans certains cas, la liste des mots valables étant trop grande, des mots ne sont pas affichés. Toutefois, cet inconvénient est minime, il est très facile d'étendre la formule cellulaire à droite : dans le fichier joint, je l'ai étendue jusqu'à la colonne Z sans difficulté...
Cordialement,
Jean-Louis

[Maxime Pierre]

Je ne comprends pas vraiment ta case A4. À quoi sert-elle ? Et surtout, c'est quoi cette formule ? x)

[Jean-Louis Cadeillan]

Bonsoir,
En A4 de Consultation, il y a cette formule :

Code : Tout sélectionner

=MAX(SI(Nb=A2;LIGNE(Nb);""))

C'est une fonction matricielle comme tu peux le voir avec les {} dans le champ de saisie. Elle est matricielle, parce qu'elle va ainsi forcer la fonction SI() à retourner une matrice et non pas une seule valeur.
En A2, il y a le nombre dont on veut tous les mots équivalents dans le système majeur. Nb est une plage nommée de tous les nombres associés aux mots recensés par Lexique 3. La fonction SI() va donc créer une plage-vecteur verticale avec dans chaque cellule soit le n° de ligne correspondant quand le nombre est égal au nombre en cours (A2) dans le système majeur, soit rien quand le nombre est différent. SI() retourne donc une colonne de cellules qui contiennent des n° de ligne ou rien.
MAX() va extraire de cette colonne (plage-vecteur) le nombre maximum, c'est-à-dire le dernier n° de ligne non vide, qui correspondra donc au dernier mot valable, vu que, sur la feuille Nombre-Mot, la plage est classée sur le nombre. Je récupère ainsi en A4 le n° de ligne du dernier mot valable... et en A3, le premier (grâce à la fonction EQUIV()).
Ces premier et dernier n° de ligne de mots valables vont me servir pour la formule de la plage B2:Z31 : le premier mot valable (dans ce cas, $A$3+LIGNE(A1)+(COLONNE(A1)-1)*30-1 = $A$3 +1 +(1-1)*30-1 = $A$3), puis le deuxième, etc. jusqu'à ce que $A$3+LIGNE(A1)+(COLONNE(A1)-1)*30-1)>$A$4 (le n° de ligne du dernier mot), auquel cas la formule retourne une cellule vide.
Cordialement,
Jean-Louis

[Maxime Pierre]

Mais pourquoi les {} encadrent toute la formule alors qu'ils ne doivent s'appliquer qu'à la fonction SI ? La fonction MAX ne renvoie bien qu'un nombre...
J'ai essayé de reproduire la même formule de mon côté et il me sort une erreur 539 (Contenu de matrice intégré non pris en charge.)... Ça a probablement un rapport avec le fait que je n'ai pas compris pourquoi tu précises
[quote=Jean-Louis Cadeillan]En A4 de Consultation[/quote]


Pour le reste, c'est bon j'ai compris ! Et les LIGNE(A1) et COLONNE(A1) dans la formule de la plage B2:Z31 servent juste à créer un incrément pour dérouler tous les mots jusqu'à la fin !
Merci !

Mapi

[Jean-Louis Cadeillan]

pourquoi les {} encadrent toute la formule alors qu'ils ne doivent s'appliquer qu'à la fonction SI ?

Parce que l'interpréteur de formule (le « parseur ») de Calc doit comprendre que SI() doit travailler en mode matriciel et comme cet interpréteur reçoit en entrée toute la formule, le moyen de lui faire comprendre cela est de « matricialiser » l'ensemble de la formule : la syntaxe cellulaire ne permet pas comme pour un langage classique, de faire des déclarations de variable à l'intérieur de la formule. Le seul moyen dont on dispose est de valider la formule en mode matriciel, soit en validant par Ctrl+Maj+Entrée, soit en cochant la case Matrice en bas à gauche de l'assistant Fonction.
Tu peux faire un test très simple, tu mets dans une cellule (par exemple en AA1 de Consultation, A2 étant égal à 11) cette partie de formule :

Code : Tout sélectionner

=SI(Nb=A2;LIGNE(Nb);"")

puis tu valides par Ctrl+Maj+Entrée : tu viens de créer un vecteur matrice de 142 460 cellules dont seule la plage AA7844:AA8502 est non vide ! La fonction MAX() récupère, à partir de ce gros vecteur, le nombre le plus élevé, c'est-à-dire 8502 (le n° de ligne où se trouve le dernier mot codant 11).

il me sort une erreur 539

Je suppose que tu as saisi ces fameuses accolades, or on ne doit pas les saisir, le fait de valider par Ctrl+Maj+Entrée suffit à les faire apparaître dans le champ de saisie, ce n'est qu'une aide visuelle pour distinguer les formules matricielles des formules non matricielles. D'ailleurs, l'accolade ouvrante est avant le signe = et l'accolade fermante en dernière position.
Bien sûr, tu peux aussi créer de toutes pièces des matrices à l'intérieur de formule avec les accolades, par exemple :

Code : Tout sélectionner

={1;2;3}

en validation matricielle, ou bien :

Code : Tout sélectionner

={1.2.3}

aussi en validation matricielle, tu verras qu'après validation, les fameuses accolades d'aide visuelle viennent encadrer toute la formule... en plus des accolades que tu as saisies ! Ce type de matrice saisi directement n'a d'intérêt que si la liste est courte et avec uniquement des valeurs. Essaie :

Code : Tout sélectionner

={1+1;2;3}

tu vas obtenir la fameuse erreur 539 !
Cordialement,
Jean-Louis