Test logique formule matricielle, renvoie VRAI seul, mais FAUX avec fonction SI

Question

Bonjour,

Alors, j'ai une formule bien trop compliquée pour ce qu'elle est censée faire, mais bon, elle fonctionne. En gros, j'ai un tableau avec des valeurs vides et cette formule s'insère dans un autre tableau qui pour chaque colonne me fait la liste des ID de lignes où il y a une valeur renseignée (tout à la suite). Avec des petits ajustement de présentation. Enfin bref, compliqué.

La formule fonctionnait bien jusqu’à ce que j'essaie de remplacer mes Ranges par des INDIRECT (pour pouvoir coller dans d'autres classeurs sans qu'il m’ennuie à faire référence au premier).

Ma formule est la suivante (matricielle) :

{=SI(GAUCHE(SIERREUR(INDEX(CompFiliere;PETITE.VALEUR(SI(NON(ESTVIDE(DECALER(CompFiliere;0;EQUIV(GAUCHE($B$3;NBCAR($B$3)-11);INDIRECT("COMPETENCES!$2:$2");0)-1)));LIGNE(DECALER(CompFiliere;0;EQUIV(GAUCHE($B$3;NBCAR($B$3)-11);INDIRECT("COMPETENCES!$2:$2");0)-1));"");NB.SI(DECALER(Code;0;0;LIGNE()-LIGNE(Code));"><")+2));"");1)*1=MAX(DECALER(INDIRECT("$E$"&EQUIV("Domaine";INDIRECT("$E:$E");0));0;0;LIGNE()-EQUIV("Domaine";INDIRECT("$E:$E");0)))*1;"VRAI";"FAUX")}

Mon problème : la formule du test logique renvoie bien VRAI... mais dès que je rajoute une fonction SI, il me renvoie la valeur FAUX (donc le test logique deviens faux ?) et je ne comprends pas pourquoi.

J'adorerais fournir un exemple mais je ne peux pas envoyer mon fichier de travail, et je vais avoir du mal à faire un exemple qui reproduise ce problème, je pense...

Quelqu'un a-t-il une idée théorique de ce qui peut se passer ?

Answer 1

Il semble que la fonction INDIRECT ne supporte pas les matrices en argument.

Au point où tu en es, ajoutes une fonction INDEX pour extraire la valeur de la matrice

=INDIRECT( INDEX (....);1) )

Answer 2

Bonjour,

Pour les conversions numérique/texte, j'avoue que au fil de l'eau je fait un peu au petit bonheur, un coup d'un coté un coup de l'autre, en texte ou en numérique, l'important étant qu'au final des deux coté ce soit pareil.

J'ai du mettre "*1" des deux coté par inadvertance, mais mieux vaut trop de "forçage" de format que pas du tout j'imagine.

Dans la troisième formule, je ne fait pas la conversion en numérique, je force la conversion en texte du résultat de ma fonction MAX avec ' &"" '.

Pour la gestion du nombre de ligne nécessaire, j'ai tablé sur 150 en "dur" et je me suis mis une MFC "au cas où" qui me notifie si la dernière ligne est remplis pour que j'agrandisse la zone, c'est pas grave si j'ai des valeurs vide à la fin de chaque liste, Elle disparaitront toute seul lors de l'export en csv, pour alimenter une BDD. mais les vides entre les valeurs c'est plus embêtant.

Quand au types ils ne dépasseront pas 9, l'ID est construit de manière a ce que le premier chiffre renvoie le type. Si jamais un jour ça dépasse 9 je n'aurait qu'à changer mon Gauche en "2", et en amont il auront changé la nomenclature pour passer les 1 en 01. Mais ça c'est pas de mon ressort. pour l'instant on a que 4 type donc on a le temps de voir venir je pense lol.

Et les valeurs dans abc sont du texte (des loooooongs textes de description de plus de 2000 caractères on adore), donc non pas de valeur à 0. seulement des valeurs vides.

Oui pris indépendamment tout les argument renvoie bien la valeur attendue, je me suis même fait un tableau décomposant fonction par fonction cette formule. et encore une fois, ce qui me laisse perplexe c'est que en prenant exactement la même formule, et juste en rajoutant SI([...];"VRAI";"FAUX") le résultat logique s'inverse.

En essayant de tout reprendre a zéro, j'en arrive à cette formule :

=SI(GAUCHE(INDEX(CompFiliere;PETITE.VALEUR(SI(NON(ESTVIDE(DECALER(CompFiliere;0;EQUIV(GAUCHE($B$3;NBCAR($B$3)-11);INDIRECT("COMPETENCES!$2:$2");0)-1)));LIGNE(DECALER(CompFiliere;0;EQUIV(GAUCHE($B$3;NBCAR($B$3)-11);INDIRECT("COMPETENCES!$2:$2");0)-1));"");NB.SI(INDIRECT("C"&EQUIV("Code comp";C:C;0)&":C"&(LIGNE()-1));"*?")+2));1)=MAX(INDIRECT("$E$"&EQUIV("Domaine";E:E;0)&":$E"&55))&"";INDEX(CompFiliere;PETITE.VALEUR(SI(NON(ESTVIDE(DECALER(CompFiliere;0;EQUIV(GAUCHE($B$3;NBCAR($B$3)-11);INDIRECT("COMPETENCES!$2:$2");0)-1)));LIGNE(DECALER(CompFiliere;0;EQUIV(GAUCHE($B$3;NBCAR($B$3)-11);INDIRECT("COMPETENCES!$2:$2");0)-1));"");NB.SI(INDIRECT("C"&EQUIV("Code comp";C:C;0)&":C"&(LIGNE()-1));"*?")+2));"")

Qui fonctionne bien pour type 1 (1 renseigné en E55), mais quand j'essaie de rendre ça dynamique (pour que la valeur de MAx change une fois la ligne X contenant le type suivant est atteint) en remplaçant 55 par Ligne() là il veut plus...

Pourtant pareil, quand je test tout seul =MAX(INDIRECT("$E$"&EQUIV("Domaine";E:E;0)&":$E"&Ligne()))&"", la bonne valeur est renvoyée. (en l'occurrence 56 pour construire une range indirect $E$54:$E56

Détail qui me rends dingue, si je remplace 55 par la référence de la cellule qui contient cet argument testé séparément... ça fonctionne :D

C56

=SI(GAUCHE(INDEX(CompFiliere;PETITE.VALEUR(SI(NON(ESTVIDE(DECALER(CompFiliere;0;EQUIV(GAUCHE($B$3;NBCAR($B$3)-11);INDIRECT("COMPETENCES!$2:$2");0)-1)));LIGNE(DECALER(CompFiliere;0;EQUIV(GAUCHE($B$3;NBCAR($B$3)-11);INDIRECT("COMPETENCES!$2:$2");0)-1));"");NB.SI(INDIRECT("C"&EQUIV("Code comp";C:C;0)&":C"&(LIGNE()-1));"*?")+2));1)=H56;INDEX(CompFiliere;PETITE.VALEUR(SI(NON(ESTVIDE(DECALER(CompFiliere;0;EQUIV(GAUCHE($B$3;NBCAR($B$3)-11);INDIRECT("COMPETENCES!$2:$2");0)-1)));LIGNE(DECALER(CompFiliere;0;EQUIV(GAUCHE($B$3;NBCAR($B$3)-11);INDIRECT("COMPETENCES!$2:$2");0)-1));"");NB.SI(INDIRECT("C"&EQUIV("Code comp";C:C;0)&":C"&(LIGNE()-1));"*?")+2));"")

et H56

=MAX(INDIRECT("$E$"&EQUIV("Domaine";E:E;0)&":$E$"&LIGNE()))&""

Mais vu que le but c'est de ne ne plus avoir aucune référence "brut" .. ce n'est pas une solution satisfaisante (en plus de rajouter une colonne de calcul inutile.)

Je me doute que c'est pas évident sans le fichier de travail mais tu m'aide déjà énormément en prenant le temps d'essayer de comprendre mon problème, merci.

Answer 3

Bonjour,

Un peu difficile de te répondre sans tester en vrai et n'ayant pas la même version que toi (365 donc déjà en "matricielle").

Mais j'ai vu ça :

Dans la 1ère formule de ton message

La valeur de GAUCHE est convertie en numérique (*1) pour la comparer avec MAX (pourquoi *1 pour le max ?, ce me semble inutile)

Dans la 3e formule de ton message

La valeur de GAUCHE n'est pas convertie en numérique donc on compare du numérique avec de l'alphanumérique !

Pour la 2e formule de ton message

A priori je soupçonnerais une erreur au niveau de la fonction DECALER :

Par exemple LIGNE()-EQUIV(...) te donne bien le résultat attendu ?

Sinon comment fais-tu pour gérer le nombre variable de lignes nécessaires vu que la colonne Type est en "dur" ? Tu "tables" sur un max de 4 par exemple ?

Les types dépassent-ils la valeur 9 ?

Les valeurs en colonnes a, b, c peuvent être à 0 ?

Answer 4

Bonjour,

Merci pour ta réponse, J'ai mis VRAI et FAUX dans les résultats d'argument pour faire court parce-que ce que dans les deux cas je calcul d'autres choses, qui fonctionnent elles très bien, c'est la validation de la valeur logique qui pose problème, peu importe le résultat.

=GAUCHE(SIERREUR(INDEX(CompFiliere;PETITE.VALEUR(SI(NON(ESTVIDE(DECALER(CompFiliere;0;EQUIV(GAUCHE($B$3;NBCAR($B$3)-11);INDIRECT("COMPETENCES!$2:$2");0)-1)));LIGNE(DECALER(CompFiliere;0;EQUIV(GAUCHE($B$3;NBCAR($B$3)-11);INDIRECT("COMPETENCES!$2:$2");0)-1));"");NB.SI(DECALER(Code;0;0;LIGNE()-LIGNE(Code));"><")+2));"");1)*1=MAX(DECALER(INDIRECT("$E$"&EQUIV("Domaine";INDIRECT("$E:$E");0));0;0;LIGNE()-EQUIV("Domaine";INDIRECT("$E:$E");0)))*1

Renvoie bien Vrai. Mais :

=SI(GAUCHE(SIERREUR(INDEX(CompFiliere;PETITE.VALEUR(SI(NON(ESTVIDE(DECALER(CompFiliere;0;EQUIV(GAUCHE($B$3;NBCAR($B$3)-11);INDIRECT("COMPETENCES!$2:$2");0)-1)));LIGNE(DECALER(CompFiliere;0;EQUIV(GAUCHE($B$3;NBCAR($B$3)-11);INDIRECT("COMPETENCES!$2:$2");0)-1));"");NB.SI(DECALER(Code;0;0;LIGNE()-LIGNE(Code));"><")+2));"");1)*1=MAX(DECALER(INDIRECT("$E$"&EQUIV("Domaine";INDIRECT("$E:$E");0));0;0;LIGNE()-EQUIV("Domaine";INDIRECT("$E:$E");0)))*1;"VRAI";"FAUX")

Renvoie faux.

Alors que de base, sans passer ma fonction "MAX" en indirect cette formule :

=SI(GAUCHE(SIERREUR(INDEX(CompFiliere;PETITE.VALEUR(SI(NON(ESTVIDE(DECALER(CompFiliere;0;EQUIV(GAUCHE($B$3;NBCAR($B$3)-11);INDIRECT("COMPETENCES!$2:$2");0)-1)));LIGNE(DECALER(CompFiliere;0;EQUIV(GAUCHE($B$3;NBCAR($B$3)-11);INDIRECT("COMPETENCES!$2:$2");0)-1));"");NB.SI(DECALER(Code;0;0;LIGNE()-LIGNE(Code));"><")+2));"");1)=MAX(DECALER(Domaine;0;0;LIGNE()-LIGNE(Domaine)))&"";"VRAI";"FAUX")

Me renvoie bien vrai...

(Je précise qu'en testant seul ma fonction MAX en passant par indirect au lieu d'un référence nommé, le bon résultat est obtenu...)

Je suis sur Excel office standard 2019, mais je confirme que si je ne valide pas en matricielle il me renvoie un #Valeur.

Voici un exemple de l'utilité de la formule, pour visualiser sa fonction, mais en beaucoup plus simple.

https://collect.wetransfer.com/board/s9e9irkqyiozx1sxp20231107101356/latest?token=8ae2e591-d376-45d8-8201-847e67fb7492

Dans la réalité la colonne ID fait de milliers de ligne, alors que par "type" il n'y a "que" une centaine de données saisie, ou pas. C'est ces "ou pas" qui me rendent fou puisque je doit extraire par type (ID commençant par 1 2 3 Etc.) et par classe (a b c etc.) les ID de toutes les données renseignés.

La fonction max est là pour faire "sauter" des ligne vides jusqu'au début du type suivant, pour classer les 1 avec les 1 les 2 avec les 2 etc. ligne que je compte avec NB.SI pour que la formule (tirée) "attende" sur la même kième PETITE.VALEUR jusqu'au début du "type" suivant" (les cellule renvoyant vide ne faisant pas augmenté K contrairement a une formule de type "ligne()".

En temps normal j'aurais fait du nettoyage à la main, mais là il y a vraiment trop de colonne/ligne, et surtout de mise a jours, pour que ce soit envisageable.

Answer 5

Bonjour,

Ta formule ne me "parle" pas (on ne sait pas si c'est avant ou après modification).

Déjà si tu es en 365 ou >2019 la validation matricielle n'est plus utile.

La 1ere fonction Si ne sert à rien

Tu as :

=si (formule étant soit VRAI ou FAUX ; VRAI ; FAUX )

donc = formule étant soit VRAI ou FAUX est suffisant

Tu dis ajouter un Si mais ou ? Pourquoi un SI par rapport à INDIRECT ?

Tu ne voudrais pas faire un petit exemple de ce que fait ta formule ?