Rectification d'une formule

[kabale53]

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]

Re : Rectification d'une formule

Bonjour à tous,

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

JHA

 

[Modeste]

Re : Rectification d'une formule

Bonsoir , JHA,

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

=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]

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]

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]

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]

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]

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)

=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]

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]

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]

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:

=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]

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]

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]

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]

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 😉