XL 2019 Nettoyer un fichier avec des lignes dupliquées ?

[DukeDevlin]

Bonjour,

J'ai une nouvelle interrogation concernant Excel : Est-il possible d'automatiser le cleaning d'un fichier Excel ? Je m'explique. Certaines lignes ayant des informations identiques sont dupliquées, et pour chacune d'entre-elles, il y a un chiffrage. L'idée c'est de passer de X ligne avec ces informations identiques à une seule ligne avec la somme du chiffrage en question pour ce sujet précis.

Si on prend mon exemple, SI (ID Sujet Statuts Type Cat) sont identiques et qu'il existe plusieurs lignes contenant ces informations ALORS faire qu'une seule ligne avec ces informations communes et sommer les charges pour chaque année.

Et si je généralise : Si j'ai X colonnes, que ces X colonnes sont identiques, alors faire une seule ligne avec ces X informations tout en sommant les colonnes ayant une valeur.

Est-ce quelque chose de possible ou faut-il faire ce nettoyage à la main ? Merci.

 

[MP59]

Bonjour DukeDevlin, un tableau croisé dynamique devrait faire l'affaire.
sur la base l'ajout de ligne sera intégré car il s'agit d'un tableau (que j'ai renommé Base).
pour le TCD un clic droit actualiser.

 

[chris]

Bonjour à tous

Un tableau à double entrée n'étant pas idéal comme source de TCD (champ de valeur à ajouter manuellement en cas d'ajout d'année), une solution PowerQuery (intégré à Excel) qui s'auto ajuste au nombre d'années

Actualiser par Données, Actualiser Tout

 

[DukeDevlin]

Bonjour à tous

Un tableau à double entrée n'était pas idéal comme source de TCD (champ de valeur à ajouter manuellement en cas d'ajout d'année), une solution PowerQuery (intégré à Excel) qui s'auto ajuste au nombre d'années

Actualiser par Données, Actualiser Tout

Merci comment as-tu fait ça ? Et pour répondre sur le TCD, impossible pour moi. En effet, là, je parle de nettoyer mon jeu de données, c'est avec ce jeu de données "propre" qu'ensuite je consolide avec des TCD et autres.

 

[chris]

RE

• Mettre la source sous forme de tableau (structuré) nommé Data
• se placer dans une cellule du tableau, Données, A partir d'un Tableau : ce qui ouvre PowerQuery
• à droite dans la liste des actions, supprimer la seconde action, Type modifié
• sélectionner toutes les colonnes autres que les années, clic droit, Dépivoter les autres colonnes
• sélectionner la colonne Attribut, Transformer, Pivoter : colonne Valeur,
  Options avancées : Somme
• Sortir par Fermer et charger dans, Table et choisir l'emplacement

 

[DukeDevlin]

RE

• Mettre la source sous forme de tableau (structuré) nommé Data
• se placer dans une cellule du tableau, Données, A partir d'un Tableau : ce qui ouvre PowerQuery
• à droite dans la liste des actions, supprimer la seconde action, Type modifié
• sélectionner toutes les colonnes autres que les années, clic droit, Dépivoter les autres colonnes
• sélectionner la colonne Attribut, Transformer, Pivoter : colonne Valeur,
  Options avancées : Somme
• Sortir par Fermer et charger dans, Table et choisir l'emplacement

Merci.

Donc je sélectionne mes données avec les entêtes, je fais : Insertion -> Tableau que j'appelle "Data". Et ensuite Données -> A partir d'un tableau ? Je ne vois pas "A partir d'un tableau" dans l'onglet "Données", est-ce normal ? Dois-je faire quelque chose avant pour activer PowerQuery ?

 

[chris]

Oui dans l'onglet Données. C'est la 4ème icône (sauf si tu travailles sur un petit écran et si ton ruban est plié : il faut alors trouver l'icône sans avoir le texte affiché dans le groupe Récupérer et transformer)

PowerQuery est totalement intégré à 2019 donc rien à activer

 

[mapomme]

Bonjour @DukeDevlin , à tous ,

Une méthode par VBA. Le code est dans module1.
Au début du code figurent quatre constantes à modifier selon le cas (leur nom est parlant).
Le tableau source doit débuter en A1 et avoir une ligne d'en-têtes.

Cliquez sur le bouton Hop!

 

[DukeDevlin]

Bonjour @DukeDevlin , à tous ,

Une méthode par VBA. Le code est dans module1.
Au début du code figurent quatre constantes à modifier selon le cas (leur nom est parlant).
Le tableau source doit débuter en A1 et avoir une ligne d'en-têtes.

Cliquez sur le bouton Hop!

Je suis bête, je viens de regarder que j'avais Excel 2013 et non 2019. Tout s'explique pour PowerQuery. Donc ta solution m'arrange énormément. J'essaie de comprendre le code mais il semble complexe ! :O Dis-moi, pour l'appliquer à mon cas, quelles données je dois modifier ? Par exemple si mon tableau a plusieurs colonnes (plus que dans l'exemple) et là où je définis quand sommer ou non, pour que je puisse l'appliquer à mon cas (et non plus à l'exemple). Merci !

 

[chris]

Re

Je suis bête, je viens de regarder que j'avais Excel 2013 et non 2019. Tout s'explique pour PowerQuery.

En add on sur 2013, à récupérer sur le site Microsoft et installer

 

[mapomme]

Dis-moi, pour l'appliquer à mon cas, quelles données je dois modifier ? Par exemple si mon tableau a plusieurs colonnes (plus que dans l'exemple) et là où je définis quand sommer ou non, pour que je puisse l'appliquer à mon cas (et non plus à l'exemple). Merci !

@chris t'a indiqué comment avoir accès à PowerQuery...

Pour ma part:

J'ai considéré que tes données sources étaient sur la feuille dont le nom est la constante NomFeuilSource. Cette constante est au début du code et vous la modifiez en fonction de votre cas.

J'ai considéré que les premières colonnes () partir de la colonne A) était la clef de chaque ligne.
La dernière colonne faisant partie de la clef est la colonne définie par la constante DerColonneIdent. Cette constante est au début du code et vous la modifiez en fonction de votre cas.

J'ai considéré ensuite que toutes les autres colonnes au delà de la colonne DerColonneIdent étaient à sommer. La constante DerColonneNombre indique la dernière colonne de votre tableau source et donc aussi la dernière colonne à sommer. Cette constante est au début du code et vous la modifiez en fonction de votre cas.

Enfin, par mesure de sécurité, le résultat est affiché sur une autre feuille. Le nom de cette feuille est la constante NomFeuilResult. Cette constante est au début du code et vous la modifiez en fonction de votre cas.

Donc recopiez le code du module1 dans un module de votre classeur. Modifiez les constantes. Et Hop! (enfin théoriquement).

Évidemment, si vos données ne sont pas présentées comme je les ai décrites, alors ça coincera.
Quand on désire une aide, on ne demande le fichier original juste un petit fichier exemple (sans donnée personnelle) mais représentatif de la structure réelle de vos données.

A+

 

[DukeDevlin]

Je vais le tester, merci. J’ai peur que mon grand nombre de colonnes, et de colonnes à sommer, fausse un peu le code. Je vais tenter de l’adapter. Merci.

 

[mapomme]

Re,

Sinon venez poster un échantillon de votre données en décrivant bien les colonnes qui doivent partie de la clef et celles qui sont à sommer (quelques lignes suffisent)

 

[DukeDevlin]

Re,

Sinon venez poster un échantillon de votre données en décrivant bien les colonnes qui doivent partie de la clef et celles qui sont à sommer (quelques lignes suffisent)

Re,

Merci beaucoup. Je ne sais pas si ça peut t'aider ça : Mon tableau va de la colonne A jusqu'à la colonne CS. Il y a 81 colonnes. Si ma colonne Q, V, X, AD et AM sont identiques (Cela veut dire qu'elles ont été dupliquées) alors supprimer toutes les lignes dupliquées et garder qu'une seule ligne avec les colonnes AO, AP, AQ, AR, AS, AT, BE, BF, BG, BH, BI, BJ, BS, BT, BU, BV, BW, BX, CF et CG sommées. Sur un excel, ça se traduit comme ça : En jaune les colonnes qui doivent être identiques et en bleu celle à sommer. Merci de ton aide.