Variable tableau comportant des variables tableaux? Avec MATCH ou FINDNEXT?

Bonjour à tous

Réflexion sur le traitement de correspondance entre bases de données.

Le but est de trouver à partir de la donnée de chaque cellule de la colonne A de DATA1, les références des lignes dont la donnée de chaque cellule de la colonne A de DATA2 correspondantes.
Il faudra stoker les données de chaque ligne des deux DATA pour les restituer au final dans une base de donnée, celle-ci n’est pas fixe. J’en ai fait un schéma par rapport à ce que je sais coder. Mais elle n’est pas encore figée.

Voici la forme de la première base : DATA1

A	B10	CCC
A	D20	EEE
A	F30	GGGG
K	H40	III
K	J10	MMM
L	N86	OOO

Et la seconde DATA2

A	Z10
A	Z20
A	Z30
P	Z40
P	Z50
K	Z60
K	Z70
K	Z80
K	Z90
P	Z100
A	Z110

Voici un résultat possible :

	TITRE 1		TITRE 2
A	B10	CCC	Z10
	D20	EEE	Z20
	F30	GGGG	Z30
			Z110
K	H40	III	Z60
	J10	MMM	Z70
			Z80
			Z90
L	N86	OOO

Pourquoi ce résultat, voici mon raisonnement :
Les bases de données vont être de minimum 50 000 lignes. Je me dis qu’il faut traiter ce sujet par des variables tableaux dynamiques.
Je sais utiliser la fonction Match, il me faut donc éliminer dans les colonnes A, les doublons, les triplons…. Ainsi je suis certain de n’avoir qu’une seule fois la même donnée dans les deux bases de données de départ.
Je peux donc utiliser la fonction MATCH.

Ma problématique actuelle est la façon dont je peux stoker le résultat de cette fonction.
Lorsque je regarde ma façon de présenter le résultat, je montre que je vais tenter de gérer des variables tableaux dynamiques.
Ce qui serait formidable c’est de pouvoir créer une variable tableau dynamique dont chaque case de ce tableau est lui-même un tableau. Ce point, je n’arrive pas à le trouver sur Google. Pouvez-vous m’indiquer si cela existe et le nom que cela porte que je regarde le fonctionnement ?
Une autre indication, svp, pour guider mes recherches. Comment créer des variables tableaux, et comment les nommer, en fonction d’une variable qui sera différente à chaque base de donné ?
Car si cela fonctionne, je pense créer des variables tableaux dès l’importation des bases de données et ensuite utiliser la fonction Match avec la référence de la première case de chaque variable tableau.

Il y a une autre méthode, mais je ne sais pas dire si cela va être très lent ou non.
Je pense aux fonctions FIND et FINDNEXT. Que pensez-vous de la notion de rapidité en utilisant ces fonctions par rapport à la fonction MATCH?

Merci à vous.

Salut.

Je n'ai pas du tout compris la règle qui présidait à l'agrégation des données... ^^



Pour ce qui est du tableau de tableaux, ou du tableau dynamique de tableaux...

Une cellule d'un tableau non typé peut contenir "n'importe quoi". Tu peux donc y pousser du texte, du numérique, des dates, mais aussi des objets (des feuilles de calcul,...) ou des tableaux. C'est toi qui décides.

Voici un code qui charge des feuilles de calcul dans un tableau, et la fenêtre des variables locales illustre bien que chaque cellule du tableau contient une feuille
Pièce jointe 581608


Voici un autre code qui illustre que les cellules du tableau contiennent chacune un tableau
Pièce jointe 581609


En VBA, on parle de tableau dynamique lorsque le tableau grandit au fur et à mesure du déroulement du code. Je donne justement un code qui utilise cela dans cette discussion de tout à l'heure. Lorsque l'on veut faire grandir le tableau en préservant les données qui s'y trouvent, on ne peut modifier que la dernière dimension du tableau. Dans un tableau à deux dimensions (les plus utilisés, je pense), cela veut dire que c'est "le nombre de colonnes"* qui augmente. Il faudra transposer le tableau pour le récupérer "bien formé" dans une plage. Mon message cité plus haut illustre ce principe. Cette transposition peut se faire grâce à Application.Transpose d'Excel ou par une fonction perso, le VBA ne disposant pas nativement d'une fonction permettant la transposition.

Peux-tu nous en dire plus (et mieux? :?) sur les règles qui président au regroupement de tes données?

PS: En fonction de ta version d'Excel, des pistes pourraient être envisagées en Power Query...


* L'orientation d'un tableau à deux dimensions est une pure vue de l'esprit humain. Parler de lignes et de colonnes nous aide, nous humains, à nous représenter le tableau, mais dans les faits, cela n'a pas d'importance. Il suffit de savoir comment le tableau est "orienté" pour boucler sur le première dimension ou sur la seconde.

Merci Pierre de ce retour très instructif.

Commençons par le début.
Je vais tenter de mieux définir l’agrégation des données.

Je vais travailler avec deux fichiers qui seront ouverts avant de les utiliser dans un troisième fichier et qui resteront ouvert.

Data 1 est un fichier que je vais recevoir tous les jours.
Il comporte des colonnes nommées : REFERENCE 1, CODE 1, DONNE 1, il peut y avoir plusieurs colonnes de données, mais celle-ci n’ont pas d’incidence sur la fonction de recherche, elles auront de l’importance, dans la programmation, sur la création du tableau structurés dynamique correspondant lors de l’importation de DATA 1 et de sa transformation en un tableau structuré qui ne sera pas DATA1 mais un autre tableau pour garder les données d’origines intactes.

Pièce jointe 581818

Il me faudra donc le transformer en tableaux structuré automatiquement lors de la programmation.
Or dans le tutoriel sur les tableaux structuré vba (tutoriel n°2 sut TS), Pierre Fauconnier écrit : « A peine plus intéressante pourrait être la technique de transformer une plage existante en tableau structuré. La technique revient dans les faits à ajouter un ListObject à la collection des ListObjects en s’appuyant sur la plage existante. »

Je me demande alors comment faire pour effectuer cette tranformation, y a t il une façon de le faire automatiquement avec Power Query? (je crois avoir compris que c'est le meilleur outils pour ce faire)

Data 2 est une base de donnée (un fichier que je vais créer en tableau structuré dès le départ) m’appartenant et qui n’évoluera qu’en nombre de lignes au fur et à mesure du temps.
Ce tableau structuré ne comprendra que deux colonnes : REFERENCE 2 et CODE 2

Pièce jointe 581819


Le résultat auquel je pense se présente sous « un tableau » avec une présentation comme sur la photo suivante.
Le but du jeu est de réussir à visionner facilement quelle sont les produits ayant comme caractéristiques les références 2 identiques aux référence 1, tout en affichant par groupe les données de chaque tableau correspondant. Je me dis que regrouper sous forme de famille serait bien. Mais je l’accorde que ce n’est pas au final un tableau structuré. Il me faudra peut-être repenser ma présentation finale. Ou bien cette présentation sera simplement un affichage particulier qui se base sur un tableau structuré.

Pièce jointe 581820

Est-ce que cela est plus clair ? Si non, je continue à tenter d’expliquer jusqu’à ce que mes mots soient justes et précis.

Un grand merci.

Suite à la lecture des tuto de Pierre sur les tableaux structurés, je comprends que ma présentation finale se basera sur un tableau structuré.

Je n'ai pas accès à Power Query du travail ces jours. Je sais que dès que je vais investir dans un ordinateur, je prendrai excel 2016 pour avoir Power Query car aux vues des tutos, cela à l'air formidable comme outils.

En VBA, j'ai réussi à importer mes données à partir de fichier qui doivent être ouvert avant cette action et à transformer le tout en tableaux structuré.

Est ce que vous voulez voir le code au fur et à mesure? Histoire de me corriger dans mes mauvaises pratiques?

Il me faut donc trouver un moyen de faire une recherche par rapport chaque élément du Data2 première colonne dans le DATA1 première colonne. Sachant que les retours seront multiples.

Si je dis que je vais faire un Find et Find next, Pierre va me faire les gros yeux... car il l'a dit dans un autre poste, il n'aime pas :

Citation:

---

Perso, je n'aime pas les Find (d'une façon générale et dans ce cas-ci en particulier). Je préfère un Evaluate (qui permet en plus une matricielle implicite)
Pour choper le listrow complet d'un tableau sur base de deux valeurs, je ferais ceci, sur base du tableau suivant qui s'appelle Tableau2.

---

Je vais regarder la fonction Evaluate si celle-ci peut m'aider dans ma recherche.

Si vous avez une autre idée, je suis bien entendu preneur. ?

Merci de vos retours.

Salut.

Je vais d'abord nuancer et commenter mes propos et prises de position, en commençant par dire que le tuto 2 sur les tableaux structurés n'est pas terminé. Il ne devrait normalement pas être accessible, mais ce n'est pas très grave.

J'y dis que normalement, on ne transforme pas une plage de données en tableau structuré en VBA. Ce que je veux dire par là, c'est que, normalement, le tableau structuré préexiste à un traitement VBA car on importe les données externes dans un cadre que l'on maîtrise, et on ne crée par ce cadre par programmation. Cela veut dire que tes données quotidiennes ne devraient pas selon moi être transformées en TS, mais bien importées dans un TS préexistant.

Quid des données journalières qui contiendraient un nombre fluctuant de colonnes? Je ne connais pas la structure de tes données, mais pour moi, il faudrait alors les importer dans une feuille temporaire puis préparer cette feuille pour l'incorporation dans le TS résultant. Avec 99% de chances de ne pas me tromper dans mon analyse, ce n'est pas pertinent d'avoir un tableau final qui fluctue en nombre de colonnes.

Evaluae et pas FIND: Pourquoi? FIND "singe" le comportement d'Excel et est sensible au format des données. C'est ainsi que les dates passent relativement mal les recherches avec FIND. L'intérêt de ramener les données dans le fichier de travail est qu'effectivement, tu peux utiliser EVALUATE avec EQUIV. Ca pourrait être une piste intéressante.

N'ayant toujours pas compris l'agrégation que tu souhaites, je sais difficilement aider plus. Toutefois, le résultat que tu montres avec les tableaux en couleurs pose question quant à l'utilisation d'un TS.

En effet, si je regarde ta dernière illustration avec le bloc TITRE 1, le fait de ne pas répéter les A dans les deuxième et troisième lignes du bloc pose problème, car dans un TS, les lignes sont par nature indépendantes les unes des autres (à de très rares exceptions près, comme dans les tableaux de tranches, par exemple). Il découle de cette indépendance des lignes que l'on doit pouvoir trier le tableau sans perte d'infos, ce qui n'est pas le cas avec la configuration que tu présentes.

Pourrais-tu expliquer comment tu passes du tableau 1 (celui que tu reçois quotidiennement, j'imagine) aux différents tableaux colorés?

Pierre,

En effet, le tableau de résultat final n’est pas sous « une bonne » présentation. Je vais à partir de ce jour réfléchir autrement, c’est-à-dire en TS (en tout cas tenté de le faire).

Je vais donc transformer en TS le premier tableau (que je reçois) et le nommer t_data1, et le tableau que je vais me confectionner t_data2. Le tableau dynamique final sera appelé t_final.

Suite à la réflexion des traitements des TS, je vais tenter d’expliquer l’agrégation des données.

En prenant chaque donnée des cellules de t_data2[REFERENCE2], il faut regarder dans t_data1[REFERENCE1] si la donnée est la même, si c’est le cas, on recopie la ligne de t_data2 (ou comme tu as dit dans un autre poste Pierre, on pousse la paire REFERENCE2/CODE 2 dans le tableau dynamique) et sur la même ligne on recopie la ligne (où a été trouvé la donnée) le ligne de t_data1.

On continue à parcourir la colonne t_data1[REFERENCE1], si une autre valeur identique a été trouvé, on recopie la ligne de t_data2 et la nouvelle ligne t_data1, le tout sur la ligne suivante de t_final.

Si aucune valeur n’est trouvé dans t_data1[REFERENCE1], alors on recopie la ligne de t_data2 et pour ne pas laisser vide des cellules on y place le mot « AUCUN ». Peut être que dans un second temps, j’éliminerai ces lignes, et donc si aucune valeur ne sera trouvé, je ne placerai rien dans t_final. Mais cela n'est pas encore certain, alors je vais partir sur le remplissage avec "AUCUN".

Pour plus de clarté, à la fin je supprimerai la colonne t_final[REFERENCE2]. sur le tableau suivant en vert. Je me dis que ce sera plus simple de travailler avec des lignes entières et d'éliminer une colonne à la fin que de travailler avec des cellules dès le départ.

Ce qui nous donnera le tableau t_final avant l’élimination de t_final[REFERENCE2].

Pièce jointe 582073

En partant de t-data1 :

Pièce jointe 582074

Et de t_data2

Pièce jointe 582075

Aurais je réussi à être plus clair?