Remplissage tableau cartésien automatique
Bonjour à tous, je vous souhaite une excellente année 2024.
Je vous remercie par avance pour vos réponses
Ci-joint j'ai créé un fichier d'exemple avec à la fois un exemple des données dont je dispose et du résultat que j'aimerais obtenir.
J'ai une liste de clients et une liste de réunions auxquelles ils ont assisté. Si ils ont assistés à la réunion la cellule contient un 1 sinon un 0.
Mon objectif et de pouvoir identifier les clients ayant participé ensemble à des réunions et combien de fois cela s'est produit. Pour ce faire j'ai un second tableau qui me permettra de croiser leurs informations et de donner le nombre d'interactions entre eux.
Ma problématique est le nombre de traitements à faire pour obtenir le résultat. J'ai envisagé la formule suivante :
=NB.SI.ENS($B$4:$E$4;"=1";$B5:$E5;"=1") (pour l'intéraction client 1 / client 2)
Celle-ci fonctionne mais ne permet pas de traiter efficacement une grande quantité de donnée puisque je ne peux pas la manipuler à la fois sur les colonnes et les lignes automatiquement. (Le fichier d'origine représente 5000 clients et 17000 réunions)
auriez-vous une idée de formule me permettant de réaliser cela ? Je ne maitrise pas VBA, mais si c'est la seule façon de faire j'essayerai. Je suis aussi ouvert à repenser totalement ma méthode et oublier ce second tableau si une autre solution vous parait plus efficace.
A nouveau merci pour vos réponses, n'hésitez pas à me demander des précisions si besoin.
bonjour Dalex,
comme ceci ? Mais 5.000 clients * 17.000 réunions, peut-être que cela plante excel ? On commence avec 100 clients et 1.000 réunions ? Excel n'a que +16.300 colonnes, donc vous devez déjà transponer le premier tableau
Avec VBA, cela sera plus facile
Bonjour Bart,
Merci beaucoup pour ta réponse, et avoir pris le temps de le faire avec VBA en plus. Je vais m'y mettre si cela facilite ce genre d'opérations.
Effectivement je pense aussi qu'il va falloir que je divise ce tableau en plusieurs parties pour soulager Excel.
Encore un grand merci
Alex
Bonjour Chris,
Merci beaucoup pour ta réponse. J'ignorais pouvoir faire ça avec PowerQuery, aurais-tu le temps de m'expliquer comment cela est fait ? J'admets n'utiliser PowerQuery que pour récupérer de la donnée entre deux fichiers actuellement..
Alex
RE
Si tu ouvres PowerQyery tu peux voir les étapes à droite :
- décroisement du tableau initial
- typage de la date (ce n'est obligatoire mais je préfère pour ma part)
- filtre pour ne garder que les 1 : on nomme cette étape C1
- ajout d'une colonne C1 : cela créé un produit cartésien que l'on développe
- ajout d'une colonne conditionnelle Nb pour y mettre 1 ou 0 selon que la date est commune ou non
- suppression des colonnes autres que Client, Client.1 et Nb
- croisement du résultat avec somme de Nb
re, salut 78Chris,
avec VBA, je ne pense pas que c'est nécessaire de diviser en plusieurs parties, le seul problème est quand vous auriez +16.300 reunions, alors il faut transposer les données ou commencer un autre tableau, la solution qui est la plus facile pour vous. Vos données, c'est quoi , un ou plusieurs fichier texte, excel, ... ou ????
Bonjour Chris,
Merci, je ne m'en étais jamais servi. J'ai beaucoup à apprendre de PowerQuery.
Je comprends la mécanique de ta solution, je vais la tester au plus vite sur mon set complet de données, je m'interroge sur la capacité d'Excel (ou de mon ordinateur) à supporter l'étape que tu as appelée "personnalisé développé" quand le nombre de ligne correspondra au nombre de clients * (le nombre moyen d'événements auxquels chacun participe * le nombre de clients). Cela va m'amener autour de 85M.
Je te tiens informé, en tout cas merci pour la découverte de fonctionnalités PowerQuery je vais m'y intéresser d'avantage.
Bonjour Bart,
Merci pour votre nouvelle réponse.
Mes données de base sont rassemblées sur une feuille en deux colonnes, chaque ligne a en colonne A l'identifiant du participant et en colonne B un texte, identifiant unique d'une réunion à laquelle le client a participé. Ce volume provient de 5 années de données, je peux facilement le diviser par année pour respecter le nombre de colonne si je ne parvenais pas à transposer les transposer.
Je me rends aussi compte, avec la solution que vous m'avez proposée, que partir de cette donnée de base peut-être aussi efficace.
Cordialement,
Alex
re,
un nouveau essai avec la mise en page comme vous l'avez dit, sauf colonne B est une date au lieu d'une description, mais comme çà, c'est possible de compter entre 2 dates, par exemple. Donc, si vous voulez, on peut filtrer ...
RE
Je comprends la mécanique de ta solution, je vais la tester au plus vite sur mon set complet de données, je m'interroge sur la capacité d'Excel (ou de mon ordinateur) à supporter l'étape que tu as appelée "personnalisé développé" quand le nombre de ligne correspondra au nombre de clients * (le nombre moyen d'événements auxquels chacun participe * le nombre de clients). Cela va m'amener autour de 85M.
PowerQuery peut traiter des millions de lignes.
Combien y a t-il de clients différents ?
Car comme avec la solution VBA la limite est le nombre de colonnes
Bonjour Bart,
Merci à nouveau pour cet exemple. Malheureusement je ne peux pas trier par date uniquement car deux événements peuvent avoir lieu le même jour. J'ai donc créé un identifiant unique sous forme de texte reprenant la date transformée en texte (44916 pour le 22 décembre 2022 par exemple) et un texte en lettre reprenant un critère particulier d'un événement pour assurer l'unicité de l'identifiant.
Je pense que la solution employable immédiatement par rapport à mes connaissance est la première proposée avec sommeprod, même si je dois aménager temporairement le set de données pour rendre l'opération faisable. Je pourrais toujours rassembler les résultats par la suite et apprendre à utiliser les autres solutions proposées pour rendre ce travail plus efficace.
Bonjour Chris,
Merci, j'imaginais que les limites en lignes étaient les mêmes qu'une feuille Excel.
J'ai un total de 4580 clients pour 17800 événements, c'est pourquoi je pense qu'en mettant les clients en colonnes je n'aurai pas de problèmes en dehors de la durée de traitement.
Chris,
Et autour de 55000 participations (un peu plus de trois participations par client)
Bonjour
SOMMEPROD sur ces volumes ramera bien plus que PowerQuery à mon avis
re,
oui, je pense aussi qu sommeproduit ramera.
un essai avec 17.000 participations et 5.000 clients en 2 minutes