XL 2016 Contôle de données entre 2 feuilles

[Pecquerie]

Bonjour!

Je suis plutôt novice sur Excel et j'aurais besoin de vos lumières pour du contrôle de données.

Je vous explique rapidement le contexte; je suis gestionnaire de paie et mon client m'a envoyé des tableaux avec des éléments à intégrer dans mon logiciel de paie. Comme les tableaux avaient des données dans tous les sens, j'ai fait un croisé dynamique consolidé reprenant mes différents onglets et j'ai saisie les résultats.

Comme j'avais beaucoup de données à enregistrer, j'ai fait une extraction de données en Excel depuis mon logiciel de paie de pour vérifier mes montants saisies avec ceux de mon croisé dynamique. Au niveau des totaux j'ai des écarts sur 3 colonnes.

J'aimerais automatiser via une formule le contrôle entre les données des 3 colonnes sur mon TCD et l'extraction de mon logiciel, mais je ne sais pas quelle formule serait la plus adapté.

Par exemple, sur mes 2 feuilles j'ai un écart au niveaux des colonnes "Participation nette versée". Ce qui me faudrait c'est une formule qui compare ligne par ligne cette colonne sur les 2 feuilles de calcul et me fait ressortir l'écart s'il y en a un.

J'ai pensé à une formule SI mais je ne maitrise pas assez pour arriver à un résultat.

Pouvez-vous m'aider ?

Merci.

Bonne journée

 

[chris]

Bonjour

La seule comparaison fiable devrait se faire non sur la base topologique (telle ou telle ligne) mais sur l'Identifiant salarié...
As-tu ces éléments dans les sources de tes TCD ?

 

[Pecquerie]

Merci pour ce retour. Oui j’ai Nom et Prénom dans mon TCD et mon extraction

 

[chris]

Re

Pas d'homonymes ?
Si non, tu peux utiliser soit la feuille TCD, soit l'autre pour comparer par formules.

Sinon avec 2016, tu peux copier/coller les résultats du TCD dans un tableau et faire le calcul d'écart par PowerQuery.

Joins un exemple anonymisé : 15 lignes à 20 lignes de TCD, 15 à 20 lignes d'extraction en remplaçant les noms par Mickey, Donald, Picsou et autres... pour qu'on te donne les bases d'une requêtes PowerQuery

 

[Pecquerie]

Non je n'ai pas d'homonyme.

J'ai réduit aux premières lettres les noms et prénom. Je sais pas si ça conviendra.

Sur le TCD, les colonne concernées sont celles qui n'ont pas "Somme" dans le titre. Et dans l'extraction les colonnes correspondantes ont le même nom.

Merci

 

[chris]

Re
Voir ci-joint.

Si cela te semble cohérent je t'expliquerai les étapes PowerQuery

 

[Pecquerie]

Bonjour,

Désolé pour la réponse tardive, je n'ai pas eu le temps de me pencher sur le tableur plus tôt.

Oui ça me parait cohérent, les erreurs de sens ne sont pas voué à ce reproduire pour les prochaines années (là je régularisais des éléments déjà versés, alors qu'à l'avenir je ferais les versements à la suite de la saisie).

Je veux bien que tu m'expliques comment faire

Merci

 

[chris]

Bonjour

Je pourrais en fin de journée

 

[chris]

RE

J'ai :

• copié le TCD et l'ai collé en collage spécial valeur sur un onglet, puis éliminé les colonnes inutiles
• mis cette liste de données ainsi qu'Extrac sous forme de tableaux que j'ai nommés
• basculé ces 2 tableaux dans PowerQuery (se placer dans une cellule du tableau, Données, A partir d'un Tableau/d'une plage)
  (chaque bascule ouvre l'interface PowerQuey : sortir après la 1ère par le 1er Bouton en haut à gauche, Fermer et charger dans, Connexion seulement)
• dans PowerQuery, après basculement du second tableau :
  • supprimé les colonnes inutiles de Extrait
  • décroisé les 2 tableaux (Extrait et TCD) : sélectionner toutes les colonnes sauf nom et prénom, puis Transformer, Supprimer le tableau croisé des colonnes
  • en partant de Extrait, Accueil, Fusionner des requêtes : sélectionné Nom, prénom, Attribut dans Extrait, en-dessous, sélectionné TCD, puis les 3 mêmes colonnes, en bas jointure Externe Gauche
  • à la colonne TCD ajoutée, cliqué sur la double flèche en haut à droite du titre de la colonne et n'ai gardé coché que Valeur
  • depuis l'onglet Ajouter une colonne, Colonne personnalisée, intitulé Ecart et formule [Valeur]-[TCD.Valeur]
  • filtré la colonne écart pour éliminé les 0 et autres pouillèmes
• une fois sorti de PQ par Fermer et charger, dans un onglet vierge d'Excel, Données, Connexions existantes , sélectionné Extrait et choisi l'emplacement.

Dans PowerQuery, les actions effectuées sont listés dans le volet de droite de chaque requête : les rouages en bout de ligne permettent d'avoir le détail par double clic.