Index de valeurs non comprises dans tableau

[Calvus]

Bonjour,

Je joins la structure d'un tableau.

J'aimerais indexer des valeurs (texte) non comprises dans une table définie.

J'ai réussi à le faire ( à voire s'il est d'ailleurs possible d'alléger la formule 🙂 )

Le problème est que la formule ne fonctionne que si les valeurs sont adjacentes, je ne comprends pas pourquoi.
Si une ligne dans le tableau C6:C41 est omise, ça ne fonctionne plus.

Le fichier sera certainement plus explicatif.

Merci

 

[hoerwind]

Re : Index de valeurs non comprises dans tableau

Bonjour,

Sous la pièce jointe, une proposition sans formule matricielle, mais avec une colonne intermédiaire.

Cette colonne intermédiaire peut être supprimée si il n'y a pas de doublons dans la plage C6:C41 et que la formule en ligne 43 est adaptée en conséquence.

 

[Calvus]

Re : Index de valeurs non comprises dans tableau

Re,

Hoerwind; merci pour ta réponse.

Afin de pouvoir coller jusqu'à la colonne U, comme dans le fichier initialement joint, j'ai modifié la formule comme ceci. Sinon #Nombre est renvoyé.

=SI(ESTTEXTE(RECHERCHEV(PETITE.VALEUR($A$1:$A$41;COLONNE()-11);$A:$C;3;0));RECHERCHEV(PETITE.VALEUR($A$1:$A$41;COLONNE()-11);$A:$C;3;0);"")

Alors,

1/ Peut on faire plus court ?

2/ Tu dis qu'on peut supprimer la colonne intermédiaire, mais je n'ai pas trouvé comment.

3/ N'est il pas possible d'adapter ma formule initiale ?

4/ Pouvez vous m'expliquer pourquoi ma formule ne fonctionne pas correctement ?

Merci

 

[david84]

Re : Index de valeurs non comprises dans tableau

Bonsoir Clavus, salut Hoerwind,
concernant ta formule, ci-joint les modifications :

=INDEX($C$1:$C$41;MIN(SI($C$6:$C$41<>"";SI(NB.SI($K$42:K$42;$C$6:$C$41)=0;LIGNE($C$6:$C$41)))))&""

Tu dois spécifier dans la formule que tu ne prends pas en compte les lignes vides.
Ceci-dit, ta formule est faite pour éliminer les doublons d'une plage. Or ton exemple n'en comporte pas...
A+

 

[Calvus]

Re : Index de valeurs non comprises dans tableau

Re;

Bonsoir David,

La formule que tu me proposes n'est pas celle dont j'ai besoin.
Je ne veux indexer que les cellules ne figurant pas dans la plage nommée Factures.
Or ce que tu me proposes fait la liste de toutes les valeurs comprises en col C.

La formule d' Hoerwind fonctionne très bien, mais j'aurais aimé pouvoir supprimer la colonne intermédiaire.

Et j'aurais aimé comprendre l'erreur dans ma formule.

Tu dois spécifier dans la formule que tu ne prends pas en compte les lignes vides

J'avais essayé, avant de créer ce post :

=INDEX($C$1:$C$41;MIN(SI(NB.SI($K$43:K$43;$C$6:$C$41)=0;SI(ET($C$6:$C$41<>"");NB.SI(Factures;$C$6:$C$41)=0;LIGNE($C$6:$C$41)))))&""

mais ça me donne un résultat erroné.

Ceci-dit, ta formule est faite pour éliminer les doublons d'une plage. Or ton exemple n'en comporte pas...

Tu as raison, mais il pourrait s'en trouver dans le fichier original.

Merci

A bientôt

 

[Monique]

Re : Index de valeurs non comprises dans tableau

Bonjour,

A essayer :

En L46 puis copier-coller vers la droite :
=INDEX($C$1:$C$41;MIN(SI($C$6:$C$41<>"";SI(NB.SI($K46:K46;$C$6:$C$41)=0;SI(NB.SI(Factures;$C$6:$C$41)=0;LIGNE($C$6:$C$41))))))&""

En L46 idem 3 fois rien plus court
=INDEX($C$1:$C$41;MIN(SI(($C$6:$C$41<>"")*(NB.SI($K48:K48;$C$6:$C$41)=0)*(NB.SI(Factures;$C$6:$C$41)=0);LIGNE($C$6:$C$41))))&""

Une fois les plages nommées, ça donne
En L50
=INDEX(ColNoms;MIN(SI(Noms<>"";SI(NB.SI($K50:K50;Noms)=0;SI(NB.SI(Factures;Noms)=0;LIGNE(Noms))))))&""

En L52
=INDEX(ColNoms;MIN(SI((Noms<>"")*(NB.SI($K52:K52;Noms)=0)*(NB.SI(Factures;Noms)=0);LIGNE(Noms))))&""

La formule élimine les doublons, oui mais...
elle fait en sorte que les valeurs déjà présentes dans les cellules de gauche ne reviennent pas
SI(NB.SI($K50:K50;Noms)=0

 

[Calvus]

Re : Index de valeurs non comprises dans tableau

Re bonsoir à tous,

Monique,

Merci. 🙂 Cette formule fonctionne en effet. Je tournais autour, sans réussir à formuler correctement. Me voilà à nouveau éclairé.
Merci des explications.

Merci à tous de vos réponses. Je n'ai que l'embarras du choix.

Bonne soirée.

PS : à Hoerwind, si tu lis ce message, je serais intéressé par la modification de ta formule sans colonne intermédiaire

 

[david84]

Re : Index de valeurs non comprises dans tableau

Re
histoire de varier les plaisirs :

=INDEX(noms;PETITE.VALEUR(SI((noms<>"")*(NB.SI(Factures;noms)=0);SI(EQUIV(noms;noms;0)=LIGNE(INDIRECT("1:"&LIGNES(noms)));LIGNE(INDIRECT("1:"&LIGNES(noms)))));COLONNES($A:A)))

A+

 

[Calvus]

Re : Index de valeurs non comprises dans tableau

Re

David, il y a un problème dans ta formule, en tout cas chez moi.
Les résultats sont répétés 3 fois, et ensuite on a #nombre.

A+

 

[david84]

Re : Index de valeurs non comprises dans tableau

Re
Ci-joint fichier. Je te la livre à titre d'info car celle de Monique est bien plus courte.
Cela est notamment due à la longueur de la partie conditionnelle que l'on doit peut être pouvoir raccourcir (mais bon j'ai fait au plus vite pour te la livrer "en état de marche"😛.
A+

 

[Calvus]

Re : Index de valeurs non comprises dans tableau

Re,

Merci, ça fait un paquet de formules tout ça. Plein de trucs à étudier.

A bientôt.

 

[hoerwind]

Re : Index de valeurs non comprises dans tableau

Re, salut les autres,

Ta correction de la formule peut être simplifiée comme suit :
=SI(COLONNE()-11>MAX($A:$A);"";RECHERCHEV(PETITE.VALEUR($A$1:$A$41;COLONNE()-11);$A:$C;3;0))

Quant à la suppression de la colonne intermédiaire, j'ai été un peu trop optimiste, ce n'est faisable que par formule matricielle, ce qui ne répond plus à ma tentative d'éviter les matricielles.

 

[Calvus]

Re : Index de valeurs non comprises dans tableau

Bonjour,

Hoerwind, merci. Ta formule a l'avantage d'être plus simple et plus courte.

Celles de Monique et David ont l'avantage d'éviter les doublons.

Pour l'instant, j'ai mis 2 lignes superposées où j'ai les 2 formules, pour voir à l'usage laquelle je conserverai.

Merci à tous.

 

[hoerwind]

Re : Index de valeurs non comprises dans tableau

Re,

Je n'ai pas vu de doublons sous ton exemple et ne me souviens pas avoir lu qu'il fallait les éliminer.

Mais ce n'est pas fort difficile, vois la pièce jointe.

 

[Calvus]

Re : Index de valeurs non comprises dans tableau

Re,

Merci.
J'ai juste mis un peu de temps à comprendre que c'est la colonne A que tu avais modifié. 😕

Bonne journée