Discussion :

[morgan75]

Bonsoir,

je tente de rapprocher 2 tableaux, transfert du contenu d'une colonne du 1er tableau dans un 2ème .
Mais je me heurte à un problème de concordance avec le champ qui est sensée être commun aux 2 tableaux.
En effet, dans le 1er tableau les comptes sont des comptes de référence qui contiennent un nombre de caractères inférieur ou égal au nombre de caractères des comptes du tableau 2 (comptes d'execution). Malheureusement je ne dispose pas d'un 2ème champ pour discreminer. J'ai tenté RECHERCHEV, NB.SI, EQUIV/INSEX avec des tests logiques mais ma maitrise de ces fonctions semble insuffisante.

Pouvez vous m'éclairer sur une approche possible afin que je puisse résoudre mon problème.

En vous remerciant.

[hben1961]

Bonjour,

Un fichier exemple serait le bienvenu...

Cordialement

[morgan75]

Bonjour,

En effet, je vous transmets un fichier, ca devrait être plus parlant. les 2 tableaux sont réunis sur le même onglet (rapprochement tableaux) du classeur.

[curt]

Salut Morgan,

sans un champ commun aux deux tableaux, ça risque d'être insoluble.

Curt

[morgan75]

j'avais pensé qu'en identifiant le nombre de caractère des comptes du 1er tableau , il aurait été possible de le comparer à la colonne de comptes du 2ème tableau contenant le même nombre de caractères. Mais je n'arrive pas à construire la formule

[Pierre Fauconnier]

Salut.

Tu pourrais donner un exemple de valeur à récupérer dans la colonne S?

[morgan75]

1er exemple:Je considère dans le 1er tableau, le 1er compte de référence (colonne A): 942663 et la SA:9314 (colonne E) qui lui est rattachée, dans la colonne S du 2ème tableau on doit trouver cette SA sur les lignes contenant le compte 942663 (colonne N=6 caractères). J'ai indiqué sur le fichier ou devait apparaître cette SA 9314 (surlignée en jaune)

idem pour le compte 6281 (3eme compte de la colonne A du 1er tableau), la colonne S contient la SA 9314 (surligné en vert)

Merci

M75

[Marc-L]

Bonjour, bonjour !

Deux tableaux ? Faudrait-il encore au moins une colonne totalement vide les séparant ‼



______________________________________________________________________________________________________
Je suis Paris, …

[Pierre Fauconnier]

Salut.

J'ai bien une solution, mais tu dois d'abord retravailler ton tableau.

1. Les données en A doivent être du texte et non des nombres (quitte à les aligner à droite).

2. en I8:R10368, tu ne peux pas répéter une donnée plusieurs fois sur la même ligne. Soit tu vides les colonnes pour les comptes en G qui contiennent moins de caractères que ce que la colonne devrait contenir, soit tu ajoutes des espaces avant pour compléter. De cette manière, en cherchant dans la colonne A une occurrence d'une valeur de la ligne, on ne trouve qu'une seule fois cette occurrence. J'ai évidemment dû considérer que dès que l'on trouvait une valeur, on ne pouvait plus trouver une valeur pour un compte avec moins de caractères sur la même ligne (Ex: pour la ligne 8, si on trouve 63112 dans la colonne A, alors, on ne peut pas trouver 6311, 631 ou 63). Apparemment, les résultats trouvés valident cette hypothèse.

Pour réaliser cela, j'ai écrit la formule =SI(NBCAR($G8)>(-COLONNE()+19);GAUCHE($G8;(-COLONNE()+20));REPT(" ";(-COLONNE()+20)-NBCAR($G8))&$G8) en I8 et l'ai recopiée en I8:R10368. Cette formule ajoute des espaces avant le contenu de G pour les colonnes qui doivent contenir un nombre de caractères plus important que celui de la valeur en G et prend le nombre souhaité de caractères à gauche de la valeur de G pour les colonnes qui doivent contenir moins de caractères que le nombre de caractères de la valeur de G. (ex: pour la ligne 8, la formule ajoute 6 espaces à gauche pour la colonne I, 5 pour la colonne J, ..., 0 pour la colonne O, puis prend les quatre premiers caractères de G8 pour la colonne P , 3 pour Q et 2 pour R). J'ai aligné à droite pour respecter ta présentation.

3. Après, en S8, j'utilise la formule matricielle =SI(SOMME(SIERREUR(EQUIV(I8:S8;$A$8:$A$158;0);0))<>0;INDEX($E$8:$E$158;SOMME(SIERREUR(EQUIV(I8:S8;$A$8:$A$158;0);0)));NA()) (Attention, formule matricielle, à valider avec CTRL+SHIFT+ENTER) qui additionne les positions trouvées en A8:A158 pour chaque valeur de la I8:R8. Vu le postulat énoncé plus haut, on va donc additionner soit 9 zéros et une valeur (lorsqu'une correspondance est trouvée, soit 10 zéros si aucune correspondance n'est trouvée). Si une valeur est trouvée, alors on utilise INDEX pour récupérer la valeur correspondante en E8:E158, sinon en envoie #N/A. Cette formule est recopiée en T8:T10368.

Note que tu as 8733 lignes sans correspondance...

Personnellement, je privilégierais, et de très loin, un tableau exhaustif des correspondances... La conception de ton classeur y gagnerait beaucoup.

[morgan75]

Je tiens à vous remercier pour la rapidité de vos réponses, ce problème a reussi à me reveiller la nuit derniere. Merci Pierre, Je vais tenter de comprendre votre raisonnement et vos formules demain. Je reviens vers vous dès que j'ai été touché par la grace

M75

[Pierre Fauconnier]

Pour "voir" comment une formule fonctionne et intégrer les étapes du calcul, tu as un outil malheureusement trop souvent méconnu, l'audit de formule (onglet Formules/Vérification des formules/Evaluer la formule). En te plaçant sur une cellule contenant une formule puis en cliquant sur cet outil, tu pourras visualiser chaque étape du calcul et comprendre mon raisonnement

[Pierre Fauconnier]

Sans faire de modifs dans ta feuille, tu peux aussi imbriquer 9 SI... Un peu sur base de ce que tu avais fait en T2. Attention alors à essayer de mettre la suite des SI au bon endroit pour éviter de devoir répéter trop de fois la même séquence au sein de la formule.

=INDEX($E$8:$E$158;EQUIV(SI(NBCAR(G8)=11;I8;SI(NBCAR(G8)=10;J8;SI(NBCAR(G8)=9;K8;SI(NBCAR(G8)=8;L8;SI(NBCAR(G8)=7;M8;SI(NBCAR(G8)=6;N8;SI(NBCAR(G8)=5;O7;SI(NBCAR(G8)=4;P8;SI(NBCAR(G8)=3;Q8;R8)))))))));$A$8:$A$158;0))Perso, je trouve cela moins élégant, et même si tu évites ainsi de devoir transformer tes données en texte, tu n'évites pas le problème rencontré aux alentours des lignes 90 sur la ligne G, avec un passage automatique en notation scientifique.

Je reste cependant persuadé que la conception serait à revoir, notamment en passant un tableau de correspondance exhaustif.

[papouclo]

Bonjour,
Un essai basé sur l'utilisation de la fonction CHERCHE qui permettrait de supprimer les 10 colonnes découpant les comptes sans budget en chaines de 2, 3, ... 11 caractères.
C'est ce qui a été fait dans le classeur joint.
Pour ne pas avoir une formule interminable, une partie est contenue dans le nom test :
En se plaçant dans une cellule de la ligne 7, définir le nom test par :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=MIN(SI(('Rapprochement tableau'!$A$7:$A$157<>"")*SIERREUR(CHERCHE('Rapprochement tableau'!$A$7:$A$157;'Rapprochement tableau'!$G7);0)=1;LIGNE('Rapprochement tableau'!$A$7:$A$157);""))

Dans la cellule I7 (ou la cellule S7 si l'on n'a pas supprimé les dix colonnes I:R ) écrire la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(test>0;INDEX(E:E;test);"")

et la recopier vers le bas.

Un gros problème tout de même dans le premier tableau :
La non unicité des comptes SPE 94224 et 61113 qui ont chacun deux occurrences correspondant l'un comme l'autre pour ces occurrences à des libellés distincts "Labo et centre de tri" et "Anatomopathologie" mais surtout à des codes SPA distincts 93231 et 93232.
J'ai donc défini un deuxième nom testbis par :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SIERREUR(PETITE.VALEUR(SI(('Rapprochement tableau'!$A$7:$A$157<>"")*SIERREUR(CHERCHE('Rapprochement tableau'!$A$7:$A$157;'Rapprochement tableau'!$G105);0)=1;LIGNE('Rapprochement tableau'!$A$7:$A$157);"");2);0)

et une autre formule dans la colonne K : en K7 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(testbis>0;INDEX(E:E;testbis);"")

de façon à mettre en évidence le problème lorsqu'il se présentait.
Cordialement
Claude

[morgan75]

Merci pour vos réponses et vos approches différentes. Je tiens tout particulièrement à remercier Pierre et Claude qui avec des stratégies différentes semblent avoir réussi à obtenir des résultats analogues (j'ai bien dit semble !). Si je n'ai pas réussi à décrypter pleinement vos formules et surtout l'association des fonctions, je ne désespère pas d'y arriver...La deuxième formule proposé par Pierre et qui m'a laissé envisagé un instant que j'étais doué d'intelligence semble renvoyé uniquement les SA des comptes en colonne A disposant du même nombre de caractère que ceux en colonne G (formule ci-dessous). Encore merci

=INDEX($E$8:$E$158;EQUIV(SI(NBCAR(G8)=11;I8;SI(NBCAR(G8)=10;J8;SI(NBCAR(G8)=9;K8;SI(NBCAR(G8)=8;L8;SI(NBCAR(G8)=7;M8;SI(NBCAR(G8)=6;N8;SI(NBCAR(G8)=5;O7;SI(NBCAR(G8)=4;P8;SI(NBCAR(G8)=3;Q8;R8)))))))));$A$8:$A$158;0))

Morgan75