Rectification d'une formule

kabale53 · 26 Décembre 2010

Bonjour
Dans la plage B10:F10 , j’ai les valeurs -------------11-1-8-3-6
Dans la plage B11:F11 , j’ai les valeurs -------------6-9-1-5-2
Dans la plage G11:L11, je voudrai coller les valeurs qui appartiennent a la plage B10:F10 et n'appartiennent pas a la plage B11:F11 ------------11-8-3

j’ai pu élaborer la solution (un peu lourde) suivante :
=SI(B10=$B11;"";SI(B10=$C11;"";SI(B10=$D11;"";SI(B10=$E11;"";SI(B10=$F11;"";B10)))))
Formule en G11 a recopier vers la droite.

Le résultat affiché est le suivant suivant :
En G11(11)
en H11(cellule vide)
en I11(8)
en J11(3)
en K11(cellule vide)

Tandis que le résultat souhaité serai :
En G11(11)
en H11(8)
en I11(3)
en J11(cellule vide)
en K11(cellule vide)

Pourriez-vous m’aider a écrire correctement la formule en question.
Merci .

fichier joint:

Regarde la pièce jointe copier coller valeurs.xlsx

JHA · 26 Décembre 2010

Re : Rectification d'une formule

Bonjour à tous,

Un essai en 2 temps en attendant mieux, tu peux masquer les colonnes "G à K".

JHA

Modeste · 26 Décembre 2010

Re : Rectification d'une formule

Bonsoir , JHA,

Un essai en une seule fois 😛 ... mais avec message d'erreur au-delà du dernier trouvé

Code:

=INDEX($B$10:$F$10;1;PETITE.VALEUR(SI(NB.SI($B$11:$F$11;$B$10:$F$10)=0;COLONNE($B$10:$F$10)-1);COLONNES($B10:B10)))

... à valider avec Ctrl+Shift+Enter et recopier à droite

JHA · 26 Décembre 2010

Re : Rectification d'une formule

Bonsoir Modeste,

Bravo, avec un test d'erreur
=SIERREUR(INDEX($B10:$F10;1;PETITE.VALEUR(SI(NB.SI($B11:$F11;$B10:$F10)=0;COLONNE($B10:$F10)-1);COLONNES($B10:B10)));"")
Tjs matricielle

JHA

david84 · 26 Décembre 2010

Re : Rectification d'une formule

Bonsoir,
à tester :

=SI(COLONNES($A:A)<=NB(SI(NB.SI($B$11:$F$11;$B$10:$F$10)=0;$B$10:$F$10));GRANDE.VALEUR(SI(NB.SI($B$11:$F$11;$B$10:$F$10)=0;$B$10:$F$10);COLONNES($A:A));"")

Validation matricielle (Ctrl, Maj et entrée).

Sinon, si le but est d'obtenir le résultat dans l'ordre d'apparition des nombres :

=SI(COLONNES($A:A)<=NB(SI(NB.SI($B11:$F11;$B10:$F10)=0;COLONNE($B10:$F10)-COLONNE($B10)+1));INDEX($B10:$F10;PETITE.VALEUR(SI(NB.SI($B11:$F11;$B10:$F10)=0;COLONNE($B10:$F10)-COLONNE($B10)+1);COLONNES($A:A)));"")

Validation matricielle (Ctrl, Maj et entrée).
A+
Edit : en fait, ma proposition donne le même résultat que celle de Modeste🙂, en plus compliquée donc choisir celle de Modeste, peut-être en y incluant un test logique permettant de cacher les valeurs d'erreur :

=SI(COLONNES($A:A)<=NB(SI(NB.SI($B11:$F11;$B10:$F10)=0;COLONNE($B10:$F10)-1));INDEX($B10:$F10;PETITE.VALEUR(SI(NB.SI($B11:$F11;$B10:$F10)=0;COLONNE($B10:$F10)-1);COLONNES($A:A)));"")

A+

kabale53 · 26 Décembre 2010

Re : Rectification d'une formule

Bonsoir JHA , Modeste , david84
Modeste et david84 quand je tire les formules que vous m'avez proposées vers le bas ;elles affichent les memes valeurs que celles affichées précédemment.
JHA la formule avec un test d'erreur n'affiche aucune valeur.
Merci pour l'interet porté a ma demande d'aide.

david84 · 26 Décembre 2010

Re : Rectification d'une formule

Re

Modeste et david84 quand je tire les formules que vous m'avez proposées vers le bas ;elles affichent les memes valeurs que celles affichées précédemment.

Je n'ai pas compris ta remarque. Est-ce que tu voulais ?
Si pas cela, placer les formules sur ton fichier et dire ce que tu attends.
A+

Modeste · 26 Décembre 2010

Re : Rectification d'une formule

re-bonsoir,

Quel plaisir de vous retrouver!
La solution sera peut-être un joyeux mélange de toutes les propositions? Si tu veux recopier vers le bas, la seconde proposition de JHA utilisait des références relatives pour les n° de lignes (pas de '$' devant les 10 et les 11, mais bien devant les lettres des colonnes) ... avec la suite du test d'erreur (restée dans le clavier)

Code:

=SI(ESTERREUR(INDEX($B10:$F10;1;PETITE.VALEUR(SI(NB.SI($B11:$F11;$B10:$F10)=0;COLONNE($B10:$F10)-1);COLONNES($B10:B10))));"";INDEX($B10:$F10;1;PETITE.VALEUR(SI(NB.SI($B11:$F11;$B10:$F10)=0;COLONNE($B10:$F10)-1);COLONNES($B10:B10))))

... C'est toujours une formule matricielle, of course

kabale53 · 26 Décembre 2010

Re : Rectification d'une formule

re,

La derniere proposition de Modeste répond exactement a ce que j'attendais.
Merci a vous tous.
Vous etes les meilleurs !

kabale53 · 27 Décembre 2010

Re : Rectification d'une formule

Bonjour
J'ai besoin encore de vos lumieres pour mieux comprendre le fonctionnement de la formule proposée par Modeste ;notamment cette partie :

(INDEX($B10:$F10;1;PETITE.VALEUR(SI(NB.SI($B11:$F11;$B10:$F10)=0;COLONNE($B10:$F10)-1);COLONNES($B10:B10))))

est ce que ce qui suit est correct?

les plages où se trouve la valeur souhaitée.
($B10:$F10;1;PETITE.VALEUR(SI(NB.SI($B11:$F11;$B10:$F10)=0

Numéro de la ligne de la plage où la valeur est
COLONNE($B10:$F10)-1)

Numéro de colonne dans les plages où la valeur est.
COLONNES($B10:B10)

Merci

Modeste · 27 Décembre 2010

Re : Rectification d'une formule

Bonjour kabale53,

Pour rendre à César ce qui lui appartient, tu noteras que la 3ème formule proposée par David (message #5) donnait le bon résultat aussi (cette fois, c'est lui qui était le plus concis et le lecteur attentif notera ici, que je m'abstiens de formuler les choses en termes de "il a la plus courte" ... ou inversement)
Pour répondre à ta question, le n° de colonne est donné par:

Code:

=PETITE.VALEUR(SI(NB.SI($B11:$F11;$B10:$F10)=0;COLONNE($B10:$F10)-1);COLONNES($B10:B10))

Le n° de ligne est fourni par la partie en vert, ci-dessous:

INDEX($B10:$F10;1;

🙂

Un petit test à réaliser (moi ça m'aide -parfois 😛- à comprendre ... tu verras bien ce qu'il en est pour toi)
Sélectionne la cellule dans laquelle tu as inséré la formule d'hier; dans la barre de formule, sélectionne cette partie-ci de la formule:

SI(NB.SI($B11:$F11;$B10:$F10)=0;COLONNE($B10:$F10)-1)

une fois que cette portion est en surbrillance, appuye sur la touche F9 du clavier; la partie de la formule que tu avais sélectionnée est remplacée par:

{1.FAUX.3.4.FAUX}

... tu obtiens bien là les n° des colonnes (dans la plage de B10 à F10) où on retrouve les valeurs qui sont absentes de la plage de B11 à F11
Attention: Appuyer sur 'Escape' pour sortir, sans modifier la formule.
Tu peux répéter la manipulation sur n'importe quelle portion de formule qu'Excel peut évaluer.

kabale53 · 27 Décembre 2010

Re : Rectification d'une formule

Bonjour Modeste

Merci pour les éclaircissements qui m'ont permis de mieux distinguer les différentes parties de la formule.

Cependant , lorsque j'effectue le petit test tel que tu me l'as indiqué,j'ai un message me signalant que des parentheses sont manquantes et qu'il faudrait controler la formule puis ajouter les parentheses aux endroits appropriés.

Je vous remercie tous JHA,david84 et toi pour votre disponibilité et l'aide que vous apportez aux débutants comme moi.
Je vous en suis tres reconnaissant.

Modeste · 27 Décembre 2010

Re : Rectification d'une formule

Bonsoir kabale53,

Pour le petit test proposé, il faut veiller à sélectionner précisément depuis le 'S' du SI(...) jusqu'à la parenthèse fermante, après le '-1'

SI(NB.SI($B11:$F11;$B10:$F10)=0;COLONNE($B10:$F10)-1)

... pas un caractère de plus, ni de moins!

kabale53 · 27 Décembre 2010

Re : Rectification d'une formule

re,
C'est tout a fait ca,maintenant le test est bon.
Est-ce que cela sert seulement pour repérer les n° des colonnes où il ya des cellules dont les valeurs sont absentes ?ou ca permet d'evaluer autre chose?

Modeste · 27 Décembre 2010

Re : Rectification d'une formule

re-bonsoir,

Tu peux, comme je le disais plus tôt, répéter la manipulation sur n'importe quelle partie de formule qu'Excel peut évaluer (d'où l'importance de bien sélectionner!).
Dans la formule proposée, tu pourrais faire la même chose avec :

COLONNE($B10:$F10)-1

... autant qu'avec

ESTERREUR(INDEX($B10:$F10;1;PETITE.VALEUR(SI(NB.SI($B11:$F11;$B10:$F10)=0;COLONNE($B10:$F10)-1);COLONNES($B10:B10))))

Bon amusement 😉

Rectification d'une formule

kabale53

XLDnaute Occasionnel

Pièces jointes

JHA

XLDnaute Barbatruc

Pièces jointes

Modeste

XLDnaute Barbatruc

JHA

XLDnaute Barbatruc

david84

XLDnaute Barbatruc

kabale53

XLDnaute Occasionnel

david84

XLDnaute Barbatruc

Modeste

XLDnaute Barbatruc

kabale53

XLDnaute Occasionnel

kabale53

XLDnaute Occasionnel

Modeste

XLDnaute Barbatruc

kabale53

XLDnaute Occasionnel

Modeste

XLDnaute Barbatruc

kabale53

XLDnaute Occasionnel

Modeste

XLDnaute Barbatruc

Discussions similaires

Rectification d'une formule

XLDnaute Occasionnel

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Occasionnel

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Occasionnel

XLDnaute Occasionnel

XLDnaute Barbatruc

XLDnaute Occasionnel

XLDnaute Barbatruc

XLDnaute Occasionnel

XLDnaute Barbatruc

Discussions similaires

Privacy & Transparency

Privacy & Transparency