XL 2010 Création d'une liste synthèse à partir de 2 tableaux

[loulou14]

Bonjour

Je vous expose mon besoin.

Je dispose de 2 sources de données indépendantes que je regroupe sous 2 onglets distincts dans le même classeur :

- onglet TRIPLETS regroupant les LOCALISATIONS associées aux NATURES. La liste peut variée

- onglet BIBLIO regroupant les CODES NATURES pour chaque nature (plusieurs codes natures possibles par nature). La liste peut variée

Objectif :

Créer un onglet SYNTHESE intégrant pour chaque CODE NATURE de BIBLIO la liste des LOCALISATIONS associées (démultiplier la liste Triplets à chaque code nature)

Je joins un fichier exemple pour illustrer mon besoin. En réalité les données sont beaucoup plus nombreuses (presque 300000 lignes dans l'onglet TRIPLET)


J'ai essayé avec RECHERCHEV mais la donnée NATURE renvoie plusieurs CODE NATURE. Bref je n'y arrive pas sans passer par d'autres méthodes très lourdes (1 onglet par CODE NATURE avec TCD, puis regroupement de tous les onglets dans un seul tableau....ayant plusieurs dizaines d'onglets à générer, le fichier devient vite très "lourd").

 

[CISCO]

Bonjour

D'où proviennent les 217 dans la colonne SYNTHESE!D : D ?

@ plus

 

[loulou14]

Bonjour

Merci d'avoir pris le temps de regarder ce sujet.

Effectivement, il y a une erreur dans l'onglet synthèse (résultat que je souhaite obtenir).

Les données de la colonne SYNTHESE!D : D sont celles des colonnes Triplets!C:C et BIBLIO!C:C.

Je joins le fichier corrigé

Cordialement

 

[CISCO]

Bonjour

Est-ce que tu peux tester la validité des formules dans les colonnes SYNTH!C:C et SYNTH!D : D ?

La formule dans la colonne SYNTH!D : D est matricielle. Il faut donc la valider avec les 3 touches Ctrl+maj+entrer. Celle de la colonne C dépend du résultat dans la colonne D. C'est donc cette dernière qui est la plus importante.

@ plus

 

[loulou14]

Merci pour votre retour

Peut être ai-je mal formulé mon besoin.

Je me permet de reformuler :
Onglet TRIPLETS : des LOCALISATIONS (colB) associées à des NATURE (colC). Chaque Localisation peut être associée à plusieurs Natures
Onglet BIBLIO : des NATURE (colC) associées à des CODES NATURE (colA). Chaque Nature peut être associée à plusieurs Codes Nature

Objectif : associer les LOCALISATIONS (Triplet!B) avec les CODES NATURE (Biblio!A).
Autrement dit pour chaque paire LOCALISATION/NATURE, obtenir les trio (LOCALISATION/NATURE/CODE NATURE) possibles
C'est ce que l'on voit dans l'onglet Synthèse, par exemple :
- pour le couple AAA1/58, il y a 3 Codes Localisation associée : AAA1/58/DF01 puis AAA1/58/DF02 puis AAA1/58/DF03
- il existe 1 couple AAA1/58 dans triplet
- il existe 3 Codes Nature associés à la Nature 58
- donc je souhaite obtenir 3 lignes correspondant aux associations AAA1/58/DF01 puis AAA1/58/DF02 puis AAA1/58/DF03

Si je ne suis pas assez clair, je peux encore essayer d'expliquer

Merci d'avance

 

[CISCO]

Bonjour

Si, si, tu as bien expliqué ton besoin... Pour autant, pourrais-tu me dire où le bas blesse dans mon dernier fichier ? Si les formules que je t'ai proposées ne donnent pas toujours le résultat souhaité, donne moi quelques exemples dans une nouvelle pièce jointe.


J'ai l'impression que c'est bon, si les deux conditions suivantes sont respectées :
* Tous les n° de NATURE sont classés dans la colonne Triplets!C : C (tous les 58 ensemble, tous les 72 ensemble...)
* Il ni a pas deux CODE NATURE + n° NATURE identique dans la feuille BIBLIO (pas deux lignes avec DF01 dans la colonne BIBLIO! A : A et 58 dans la colonne BIBLIO! C : C par ex).

@ plus

 

[CISCO]

Rebonjour

J'ai complété la colonne SYNTH! A : A, CODE NATURE, dans la pièce jointe. Dis moi si c'est correct.

@ plus

 

[loulou14]

Merci de votre retour

Cet exemple correspond à l'idée générale exprimée. Merci.
Je vais essayer d'adapter votre solution à mon projet (le nombre de données est beaucoup important).

Ce type de formule est-il réalisable en VBA ?

Cordialement

 

[CISCO]

Bonsoir

Merci de votre retour
...
Ce type de formule est-il réalisable en VBA ?
Cordialement

Bien sûr que c'est faisable avec une macro en VBA, et cela sera certainement plus rapide si tu as beaucoup de données. Toutefois, comme j'en suis plutôt au niveau B-A-BA coté VBA, je ne pourrais t'aider davantage sur cette voie dans l'immédiat.

@ plus

 

[loulou14]

Bonsoir

Pour ma part, je ne pas spécialiste des formules matricielles.
Le premier essai que j'ai souhaité réalisé sur la base du fichier exemple, c'est ajouté des valeurs dans les tableau Triplets et Biblio...sans que le tableau SYNTH ne s'incrémente.
Pouvez-vous me donner une explication ? J'ai essayé de changer les valeurs dans les formules sans parvenir à obtenir le résultat souhaité.

Cordialement

 

[CISCO]

Bonsoir

La formule importante est dans la colonne D.

Dans D2, on a

SI(D1="Nature";BIBLIO!C$2;

permet de commencer par la BIBLIO!C$2 sur la première ligne en dessous de l'entête "Nature"

SI(NB.SI(SYNTH!D$1:D1;SYNTH!D1)<(NB.SI(BIBLIO!C$2:C$10;SYNTH!D1)*NB.SI(Triplets!C$2:C$8;D1));D1;

permet de recopier ce qui est juste au dessus tant qu'on n'a pas répété assez souvent le nombre "Nature" juste au dessus.

SI(SOMME((NB.SI(SYNTH!D$1:D1;BIBLIO!C$2:C$10)=0)*1)=0;"";

permet de ne rien afficher lorsqu'on a déjà affiché tous les N° "Nature" listé dans la colonne BIBLIO!C.

Et voilà la partie la plus importante

INDEX(BIBLIO!C$1:C$10;MIN(SI(NB.SI(SYNTH!D$1:D1;BIBLIO!C$2:C$10)=0;LIGNE($2:$10)))))))

Comme il s'agit d'une formule matricielle NB.SI(SYNTH!D$1 : D1;BIBLIO!C$2:C$10) renvoie des 0 et des 1.
SI(NB.SI(SYNTH!D$1 : D1;BIBLIO!C$2:C$10)=0 renvoie des FAUX et des VRAI.
SI(NB.SI(SYNTH!D$1 : D1;BIBLIO!C$2:C$10)=0;LIGNE($2:$10)) renvoie des FAUX et des n° de lignes à la place des VRAI
MIN(SI(NB.SI(SYNTH!D$1 : D1;BIBLIO!C$2:C$10)=0;LIGNE($2:$10))) renvoie le plus petit des n° de lignes correspondant à un VRAI.
INDEX(BIBLIO!C$1:C$10;MIN(SI(NB.SI(SYNTH!D$1 : D1;BIBLIO!C$2:C$10)=0;LIGNE($2:$10))))))) renvoie le contenu de la colonne BIBLIO!C$1:C$10 sur la ligne précédente.

Autrement dit :
Si on est en D2, la formule écrit le contenu de BIBLIO!C2, 58.
Si on est en D3, comme on n'a pas écrit assez de 58, on en écrit un autre.
Si on est dans D8, comme on a écrit assez de 58, on écrit la première valeur en dessous de 58, différente de 58, pris dans la colonne BIBLIO!C:C.
Si on est dans D20, comme on a écrit assez de 58 et assez de 72, on écrit la première valeur en dessous de 58 et de 72, différente de 58 et de 72, pris dans la colonne BIBLIO!C : C.
Si on est dans D24, comme on a écrit assez de 58, assez de 72 et assez de 231, on n'écrit rien.

Pour voir comment cela fonctionne, sélectionne une des cellule de cette colonne D, puis clique sur l'onglet "Formule", puis sur "Evaluation de formule", puis "Suivant", puis...

Recommence avec une autre cellule de cette colonne D.

Pour voir les résultats donnés par une partie d'une formule, tu peux aussi surligner cette partie (sans oublier les parenthèses correspondantes) dans la barre de formule, puis F9. Pour revenir à l'écriture précédente, Echap.

Comme tout cela n'est qu'un résumé, si tu as besoin d'autres explications, dis le moi.

@ plus

 

[loulou14]

Bonjour et merci de consacrer du temps à ce projet

Je pense avoir compris le principe de la formule.
Maintenant techniquement, j'essaye d'ajouter des lignes à l'onglet TRIPLETS sans aucun effet sur le résultat dans SYNTH.

J'ai identifié pouvoir modifier la plage de sélection dans la formule : ....NB.SI(Triplets!C$2:C$8;D1))....que je remplace par NB.SI(Triplets!C$2:C$9;D1)) par exemple dans le cas où je n'ajoute qu'une seule ligne à TRIPLETS.
Pour modifier, je sélectionne la plage de formule matricielle : SYNTH!D227, je modifie et je fais CTRL+MAJ+ENTREE
J'obtiens la valeur 58 dans toutes les cellules de la plage D227.

Quelles sont les régles pour pouvoir étendre ou modifier les formules ?

Cordialement

 

[CISCO]

Bonjour/Bonsoir

Il y a deux méthodes pour valider les formules matricielles :
* Dans le premier cas, tu écris ta formule dans une cellule et tu la valides avec Ctrl+maj+entrer. Tu étends la formule sur la plage utile, ou sur un peu plus, ensuite. C'est la méthode à utiliser ici.
* Dans le second, tu sélectionnes la plage à utiliser, tu écris ta formule et tu la valides avec Ctrl+maj+entrer.

La formule à utiliser n'est pas la même dans les deux cas. La seconde méthode donne des résultats plus rapidement, mais je ne sais pas l'utiliser dans tous les cas.

@ plus

 

[loulou14]

Voici la formule modifié (comme expliqué ci-dessus, j'ai étendu la plage Triplets C:C)) que j'ai modifié
=SI(D1="Nature";BIBLIO!C$2;SI(NB.SI(SYNTH!D$11;SYNTH!D1)<(NB.SI(BIBLIO!C$2:C$10;SYNTH!D1)*NB.SI(Triplets!C$2:C$9;D1));D1;SI(SOMME((NB.SI(SYNTH!D$11;BIBLIO!C$2:C$10)=0)*1)=0;"";INDEX(BIBLIO!C$1:C$10;MIN(SI(NB.SI(SYNTH!D$11;BIBLIO!C$2:C$10)=0;LIGNE($2:$10)))))))

J'applique votre 1ére méthode :
Je sélection la cellule SYNTH D2, je la modifie, je valide avec Ctrl+maj+entrer et je tire cette cellule sur la colonne SYNTH D!27.
Résultat : n'affiche que la valeur "58" dans SYNTH!D227

 

[CISCO]

Bonsoir

Mets ton fichier en pièce jointe, avec tes modifications.

@ plus