Comparaison de fichiers

[Gege352]

Bonjour à tous,

Chaque mois, j'effectue des vérifications entre plusieurs fichiers. Le travail est trés lourd et fastidieux.
Je pense qu'il doit ête possible de l'automatiser mais je ne sais pas faire. Pourriez-vous m'aider SVP ?!

Au départ, je récupère les différents états à comparer, le fichier se présente comme suit (cf fichier 1) :
1er onglet : Etat ZSD
2nd onglet : Etat BAL C1
3ème onglet : Etat BAL C2
4ème onglet : Etat BAL C3
5ème onglet : Etat BAL C4
6ème onglet : Etat BAL Ci
7ème onglet : Etat BAL Dx
8ème onglet : Centralisation
la colonne A reprend l'ensemble des n° que l'on retrouve colonne A des fichiers BAL C1 - BAL C2 - BAL C3, etc...

Ensuite, dans l'onglet "Centralisation", j'ajoute les formules qui vont bien pour comparesr les données issues de la feuille "ZSD" avec les différents "BAL" (cf fichier 2). C'est ce traitement que je voudrais automatiser.

La colonne B devant afficher les données de la colonne "41188000" de l'état ZSD
C devant afficher les données de la colonne 9 de l'état BAL C1
D affiche les écarts entre B et C

F devant afficher les données de la colonne "41500000" de l'état ZSD
G devant afficher les données de la colonne 9 de l'état BAL C2
H affiche les écarts entre E et F

J devant afficher les données de la colonne "41170000" de l'état ZSD
K devant afficher les données de la colonne 9 de l'état BAL C3
L affiche les écarts entre E et F

N devant afficher les données de la colonne "41110000" de l'état ZSD
O devant afficher les données de la colonne 9 de l'état BAL C4
P affiche les écarts entre E et F

R devant afficher les données de la colonne "18107000" de l'état ZSD
S devant afficher les données de la colonne 9 de l'état BAL Ci
T affiche les écarts entre E et F

V devant afficher les données de la colonne "41600000" de l'état ZSD
W devant afficher les données de la colonne 9 de l'état BAL Dx
X affiche les écarts entre E et F

Sachant que : le tableau "ZSD" est un tableau croisé dynamique. La longueur de ce tableau est variable d'un mois sur l'autre.
Le nombre de colonnes est également variable, les comptes ne sont pas toujours situés dans la même colonne.
Une analyse des écarts est nécessaire uniquement si les totaux sont différents (Etat "ZSD" avec les différents "BAL").

Est-il possible d'automatiser ce traitement ?
Est-il possible de sélectionner les comparaisons à faire (En fonction des écarts constatés sur les totaux) ?

J'espère que mes explications sont suffisantes et claires.
Je vous remercie par avance de vos conseils.


Gégé

 

[Gege352]

Re : Comparaison de fichiers

Bonjour à tous,

Je n'ai eu aucune réponse. Mon message est-il trop confus ? Ma demande est-elle trop complexe ? N'ai-je aucun espoir de trouver une solution ?

S'il-vous-plait !!! Help !!! Je veux bien aprticiper à la mise en place de qqchose, mais en suis incapable toute seule...

Gégé

 

[dixit]

Re : Comparaison de fichiers

bonjour,
je viens de prendre connaissance du sujet. j'essaie d'y travailler dans la journée.

 

[Gege352]

Re : Comparaison de fichiers

Ha ! Super ! Une lueur d'espoir ! Merci d'avance.

 

[dixit]

Re : Comparaison de fichiers

bonjour à tous,
pas simple mais on va y arriver.
voici un fichier (lien cjoint car trop important pour être attaché directement)
http://cjoint.com/?fzlZv4xzZw
je pars sur l'idée suivante (partiellement réalisée dans ce classeur) :
- une macro (à réaliser mais ne devrait pas poser de problème) positionne les N° de tiers dans la colonne A de la feuille centralisation, ce à partir d'une liste à déterminer (une des listes du classeur par exemple) et reproduit les formules sur les lignes éventuellement manquantes.
- des formules avec RECHERCHEV reportent les données (c'est l'exemple joint comportant également des Mises en forme conditionnelles).
J'ai des questions :
- des n° de tiers n'existent pas dans ZSD : surprenant
- il faut imaginer que le classeur soit polyvalent par rapport au mois concerné et par ex le nommer en fonction du mois mais pas nommer des onglets en fonction du mois.
- gros problème : pourquoi les comptes risquent-ils de changer de colonne ? : je n'ai pas compris cette remarque dans la question ; si on ne peut pas éviter cela, je crois que tout le traitement devra être fait par macro : dur dur ...
- sans indiscrétion, il serait utile de savoir pourquoi il faut faire toutes ces comparaisons ; ne pourrait-on pas simplifier la présentation qui est très lourde ?
voilà pour l'instant des questions et peu de réponses mais on va avancer.
mon WE est bien chargé mais j'essaie de continuer.
à suivre

 

[Gege352]

Re : Comparaison de fichiers

Bien cher Dixit, mon sauveur !

Je te remercie 1000 fois de te pencher sur mon problème. Le fichier que tu m’as adressé me parait pas mal du tout !
Voici qqs précisions qui pourront, j’espère, t’aider.
Le but de la manœuvre est de pouvoir justifier des comptes de TVA.
Le compte comptable me donne un solde mais ne me permet pas de connaître le détail des tiers qui doivent tel ou tel montant de TVA.
Pour cela je récupère l’état «ZSD » qui mentionne les sommes dues par les clients (comptes 411xxxxx), que je compare à la compta (BAL C1, BAL C2, etc…). Si le sommes dues par les tiers dans le ZSD correspondent à la compta, je considère que la contrepartie que je n’ai que dans le ZSD est OK (compte de TVA notamment).

Pour répondre à tes questions :
- L'état ZSD peut comporter des erreurs : certaines écritures sont mal reprises.
C'est pourquoi il peut manquer des tiers dans cet état.
- Pour d'autres tiers, ce sont les montants qui peuvent être erronés.
- l'état ZSD reprend les comptes mouvementés sur la période. D’un mois sur l’autre, certains comptes peuvent être soldés et n’apparaissent plus. Voilà pourquoi l’état n’a pas toujours la même structure.
Si ça t’arrange, je peux toujours faire en sorte que le tableau ait toujours la même structure, en récupérant les données du mois concerné et en laissant à blanc les comptes soldés ?

OK pour nommer le fichier en fonction de la période et garder des onglets identiques d’un mois sur l’autre.
Je ne suis pas contre simplifier la présentation. Comment vois-tu la chose ?

Concernant la macro que tu m’as envoyée, peut-on supprimer les mentions « NON TROUVE » ? Dans ce tableau, j’ai juste besoin d’identifier les écarts. En effet, j’ai une liste de tiers (colonne A) unique pour les différents codes (C1, C2… DX). Certains tiers sont concernés par plusieurs codes mais d’autres 1 seul.

Existe-t-il un moyen de lancer le traitement uniquement pour le code C1 ? Ou C1 et DX ? ou Tous (pour les codes qui mentionnent un écart au niveau des totaux) ?

J’espère que ces éléments t’aideront. N’hésite pas si tu veux plus de précisions. Je suis sincèrement ravie d’imaginer que ce traitement pourra se faire automatiquement.

Bonne soirée et bon week-end à tous. Bon courage à Dixit !

Gégé

 

[dixit]

Re : Comparaison de fichiers

bonsoir,
précision au sujet de la 1ère version : le traitement n'est pas fait par macro mais avec des formules : voir dans les cellules.
voici une 2è version toujours en formules avec les fonctions EQUIV et INDEX.
j'ai créé une feuille INFO pour quelques explications. Pour des informations sur ces 2 fonctions voir l'aide d'Excel ou sur mon site perso avec classeurs exemples(mais ce n'est peut-être pas très clair ...)
à suivre
http://cjoint.com/?fCtbTXpdB0
ps : pour la feuille Centralisation, il serait possible d'imaginer une mise à jour automatique de la liste des tiers avec insertion de lignes si besoin et recopie des formules.

 

[Gege352]

Re : Comparaison de fichiers

Bonjour,

Merci encore pour ce travail. Une question : comment cela se passe pour effectuer le traitement d'un mois sur l'autre ? Devrai-je utiliser un fichier "type" et le mettre à jour avec les données du mois ? Nommer les zones (de taille différente d'un mois sur l'autre) ? Adapter les formules selon la longueur des différents états ?

Bonne journée à tous.

Gégé

 

[dixit]

Re : Comparaison de fichiers

bonjour,
a priori, je pense que le fichier "type" est la meilleure solution ; il suffit ensuite de le sauvegarder sous un nom comportant le mois pas exemple.
avant de se lancer dans le travail, il faut savoir d'où sont extraites les données :
- est-ce ZSD qui est "pilote" ?
- pour les autres listes C1 C2 etc ... : les formules pourraient aller chercher directement dans des fichiers séparés ; cette méthode est peut-être plus simple que d'avoir à les positionner manuellement dans le classeur de synthèse : il suffit d'avoir un classeur nommé C1, un C2 etc ... chacun présenté de la même manière en position lignes et colonnes ; la question est de savoir comment ils pourraient être alimentés : extraction d'un logiciel de compta, je suppose ...
En conclusion : une bonne analyse du problème et ensuite un coup de baguette magique et c'est fait (enfin presque ...)

 

[Gege352]

Re : Comparaison de fichiers

Re !

Que puis-je t'apporter comme élément supplémentaire pour te permettre de bien saisir les différents aspects ?

J'aimerais, si c'est possible, avoir le moins de manipulation possible, source d'erreur. J'aimerais aussi pouvoir confier ce travail de comparaison de fichiers à une personne qui n'est pas forcément familiarisée avec les formules, ni les fonctions d'excel. Si tout est prévu et qu'il n'y a que les infos du mois à insérer dans le fichier "type", c'est parfait !

Qu'entends-tu par état "pilote" ?
En fait, le but de cette analyse est de vérifier l'état ZSD, seul état qui me donne le détail de la contre-partie des sommes dues, et donc le montant de la TVA due par chaque tiers. Mais cet état est une extraction extra-comptable.
Les données "officielles" sont celles que l'on retrouve dans les états C1, C2... directement issues de la compta. Mais cet état ne me donne pas la contre-partie...
Est-ce que cela t'éclaire ?!

Merci encore !

Gégé

 

[dixit]

Re : Comparaison de fichiers

bonsoir à tous,
voici une nouvelle version
http://www.cjoint.com/?fDtUYc43fF

- les reports sur la feuille Centralisation sont faits par formules en utilisant des plages nommées (seulement les colonnes pour C1 mais on pourra continuer ensuite)
- la dernière ligne mentionnée dans les coordonnées de la plage BALAUXC1 peut être mise à jour par macro en cliquant sur le bouton de la feuille C1, ceci pour s'adapter au nombre de lignes
- lire les nouvelles informations (feuille INFO)
- les formules ont été modifiées dans la feuille Centralisation : si le tiers n'est pas trouvé dans les plages de recherche, la formule retourne désormais 0 et non "", ceci pour éviter une anomalie comme celle constatée pour le tiers 11418.
L'idée est donc de partir chaque mois soit d'un classeur modèle soit du classeur du mois précédent puis mise à jour, changement de nom et sauvegarde : à déterminer selon le degré d'automatisation qu'il sera possible d'envisager.
Faire des tests sur un classeur d'essais avec la feuille C1 et contrôler les résultats ; si c'est satisfaisant, on pourra étendre aux autres feuilles.

ps : est-il impératif de commencer la feuille C1 en ligne 17 ?

 

[Gege352]

Re : Comparaison de fichiers

Bonjour à tous, bonjour Dixit,

J'ai testé le fichier que tu m'as adressé sur une période complète (ce que je t'ai envoyé n'étant que l'extrait d'une période).

Voici mes commentaires concernant l'onglet "centralisation" :
- la période que j'ai testée comportant plus de tiers, j'ai du ajouter les formules sur les lignes supplémentaires.
C'est le problème que j'aurais car les fichiers n'auront pas le même nombre de ligne. Faudra t-il adapter les formules à la taille des états ?
Je dois également refaire les totaux et les vérifs. avec les états correspondants.

- la colonne B (état ZSD) mentionne l'erreur "#N/A" sur les lignes qui comportent tes formules et vide sur les lignes supplémentaires (celles qui j'ai ajoutées) ???

- la colonne C (BAL C1) est OK.

Le but de la macro "majc1" est-il bien de définir la plage de la feuille ?

Concernant les fichiers C1, C2, etc... la ligne 17 est la 1ère ligne si je garde l'extraction en l'état, ce qui m'arrange. Je pourrais trés bien les supprimer mais par simplification je préfèrerais conserver cette présentation. Est-ce possible ? (la 1ère cellule de données sera toujours en A17).

As-tu besoin que je t'adresse le fichier pour mieux comprendre ?
Merci encore.

Gégé

 

[dixit]

Re : Comparaison de fichiers

bonjour à tous,
- nombre de tiers sur la feuille Centralisation : oui c'est bien là le problème ; à quel moment cette liste de tiers dans cette feuille est-elle modifiée ? Je suis bloqué sur ce point car pour le moment, j'ai des difficultés à comprendre le déroulement du processus.
- pour ta remarque au sujet de l'erreur dans la colonne B (état ZSD), je ne comprends pas : le fichier serait utile.
- j'ai complété la mise à jour par macros des plages nommées et des formules sur Centralisation dans une nouvelle version (non jointe en attendant de résoudre le problème des N/A)
- la macro majc1 est destinée effectivement à mettre à jour les coordonnées des plages nommées dans C1
- pas de problème pour commencer la feuille C1 en ligne 17
à suivre

 

[dixit]

Re : Comparaison de fichiers

bonjour,
voici une nouvelle version [fichier supprimé remplacé par celui du post suivant]voir les informations sur la feuille INFO
on ne devrait plus être très loin du but (pour le traitement par rapport à C1 puisque seul celui-ci a été prévu)
une question : pourquoi certaines colonnes sont sautées dans la feuille Centralisation ? ce qui ne donne pas une structure homogène à la feuille.

 

[Gege352]

Re : Comparaison de fichiers

Chouette ! Ca avance ! C'est super !

Pour l'onglet "centralisation", la liste évolue lorsque l'on a de nouveaux tiers.
Pour les erreurs que j'obtiens en faisant une simulation, je t'envoie ci-joint le fichier que j'obtiens.
Quant aux colonnes "sautées" dans l"onglet "centralisation" c'est uniquement pour aérer les colonnes et séparer chacun des codes C1, C2, etc...

Je ne sais comment te remercier, c'est super !
Bonne soirée.

Gégé