Index de valeurs non comprises dans tableau

H

hoerwind

XLDnaute Barbatruc

• 4 Novembre 2010

• #2

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.

 

Pièces jointes

• IndexValeursDifférentesDuTableau.zip

  7.6 KB · Affichages: 23

Vote positif 0 Vote négatif

Calvus

XLDnaute Barbatruc

• 4 Novembre 2010

• #3

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é.

Code:

=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

 

Vote positif 0 Vote négatif

D

david84

XLDnaute Barbatruc

• 4 Novembre 2010

• #4

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)))))&""

Cliquez pour agrandir...

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+

 

Vote positif 0 Vote négatif

Calvus

XLDnaute Barbatruc

• 4 Novembre 2010

• #5

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

Cliquez pour agrandir...

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...

Cliquez pour agrandir...

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

Merci

A bientôt

 

Vote positif 0 Vote négatif

M

Monique

Nous a quitté

Repose en paix

• 4 Novembre 2010

• #6

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

 

Vote positif 0 Vote négatif

Calvus

XLDnaute Barbatruc

• 4 Novembre 2010

• #7

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

 

Vote positif 0 Vote négatif

D

david84

XLDnaute Barbatruc

• 4 Novembre 2010

• #8

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)))

Cliquez pour agrandir...

A+

 

Vote positif 0 Vote négatif

Calvus

XLDnaute Barbatruc

• 4 Novembre 2010

• #9

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+

 

Vote positif 0 Vote négatif

D

david84

XLDnaute Barbatruc

• 4 Novembre 2010

• #10

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+

 

Pièces jointes

• Index valeurs différentes du tableau_Calvus.xls

  24.5 KB · Affichages: 46

Vote positif 0 Vote négatif

Calvus

XLDnaute Barbatruc

• 4 Novembre 2010

• #11

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.

 

Vote positif 0 Vote négatif

H

hoerwind

XLDnaute Barbatruc

• 5 Novembre 2010

• #12

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.

 

Vote positif 0 Vote négatif

Calvus

XLDnaute Barbatruc

• 6 Novembre 2010

• #13

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.

 

Vote positif 0 Vote négatif

H

hoerwind

XLDnaute Barbatruc

• 6 Novembre 2010

• #14

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.

 

Pièces jointes

• IndexValeursDifférentesDuTableauV2.zip

  8.9 KB · Affichages: 16

Vote positif 0 Vote négatif

Calvus

XLDnaute Barbatruc

• 6 Novembre 2010

• #15

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

 

Vote positif 0 Vote négatif