Avez-vous des questions? Nous avons des réponses.

Guides et conseils sur le formatage des données : Dégroupement et vérification du déséquilibre

  • Mise à jour

Sommaire

Ce guide a pour but de vous aider à comprendre les meilleures façons de résoudre un déséquilibre dans les résultats de MindBridge lorsque votre fichier grand livre général est formaté et peut être facilement ingéré dans MindBridge. 

Il s’agit d’un parcours plus avancé et plus complet. Donc, si vous ne l’avez pas encore fait, nous vous recommandons d’essayer d’abord les solutions trouvées dans cet article : Les montants ou les débits et crédits ne s’équilibrent pas lors de l’importation du grand livre général dans MindBridge


 

Résolution

Considérez l’extrait suivant d’un fichier grand livre général :

mceclip0.png

 

Tout d’abord, nous allons examiner brièvement comment formater ce fichier. Si vous préférez, vous pouvez sauter cette partie et passer directement à la rubrique Dépannage du déséquilibre.

Les problèmes :

  1. Ce fichier est regroupé par compte : une seule ligne contient des informations sur le compte (lignes 6, 10, 169), suivies de toutes les transactions pour cette ligne (lignes 12-167, 171 et suivantes) (viole l’exigence de la ligne 1 ).  
  2. Le numéro de compte est généralement dans la colonne B (lignes 10 et 169) mais parfois il est dans la colonne A (ligne 6) (viole la condition 1 de la colonne) (Cependant, la description du compte est toujours dans la colonne E pour les lignes de compte)
  3. Il y a des lignes de total/sous-total inutiles (9, 168) et des lignes vides sous les lignes de numéro de compte (viole l’exigence globale 2)

La solution (formatage nécessaire) :

  1. Dégroupez par compte (faites glisser les informations sur le compte vers chaque ligne de transaction)
  2. Exclure les lignes totales/sous-totales/blanches étrangères (lignes 7-9, 11, 168-170) et les lignes étrangères (lignes 1-4) au-dessus de la ligne d’en-tête (ligne 5)

 

Nous ajoutons une colonne « Identifiant du compte » à la fin du fichier pour extraire le numéro de compte des lignes du compte et le faire glisser vers le bas :

mceclip2.png

 

Identifiant du compte : Cellule K6 et cellules inférieures

=IF(NOT(ISERROR(SEARCH("????-??-??",B6))),B6,IF(NOT(ISERROR(SEARCH("????-??-??",A6))),A6,K5))
  • RECHERCHE("????-?-?-??",B6), RECHERCHE("????-?-??",A6)
    trouvez la position dans la chaîne B6 ou A6 où une chaîne correspondant à l’expression « ????-??-?? » apparaît Si elle n’est pas trouvée, une erreur (#VALUE!) est renvoyée
  • l’expression « ????-??-?? » utilise le caractère générique ? qui correspond à un caractère unique. Alors, « ????-??-?? » signifie quatre caractères simples quelconques, suivis de -, suivis de deux caractères simples quelconques, suivis de -, suivis de deux caractères simples quelconques. Le but est de tester si un numéro de compte est trouvé dans la cellule (B6 ou A6)
  • Les expressions NOT(ISERROR()) enroulées autour de ces expressions SEARCH() indiquent simplement si les cellules B6 ou A6 contiennent des numéros de compte (c’est-à-dire que le motif « ????-??- ?? » a été trouvé). Donc,
    SI(NON(ERREUR(RECHERCHE("????-??-???",B6))),B6,
    signifie renvoyer B6 si B6 est un numéro de compte, et l’option
    SI(NON(ERREUR(RECHERCHE("????-??-??",A6))),A6,
    qui suit signifie sinon si A6 est un numéro de compte, retourner A6, et enfin, le K5 à la fin signifie sinon retourner K5 (l’Identifiant du compte calculé dans la ligne au-dessus, c’est-à-dire que c’est ce qui entraîne les numéros de compte trouvés)

Remarque : nous pourrions également ajouter une colonne « Description du compte » dans la colonne L pour extraire la description du compte, mais notez que la description du compte n’est pas nécessaire dans le grand livre général si elle est présente dans le plan de comptes; pour des raisons de brièveté, nous ne discuterons pas en détail de l’ajout de la description du compte, mais si nous voulions le faire, la logique serait la même que celle décrite ci-dessus pour l’Identifiant du compte, mais puisque la description du compte se trouve toujours dans la colonne E si la colonne A ou la colonne B contient un numéro de compte, nous utilisons la fonction OR() pour tester si B6 ou A6 est un numéro de compte, et renvoyer E6 si oui, L5 (la description du compte calculée dans la ligne au-dessus, à glisser vers le bas) pour la formule suivante dans la cellule L6 :

=IF(OR(NOT(ISERROR(SEARCH("????-??-??",B6))),NOT(ISERROR(SEARCH("????-??-??",A6)))),E6,L5)

 

Filtrer pour conserver uniquement les rangées de transaction

Avec les filtres activés, vérifiez la colonne B (Date) et nous voyons qu’il y a les dates 2020 et 1900, puis le reste sont des numéros de compte :

mceclip3.png

Il s’avère que dans ce fichier, les dates de 1900 sont également des données poubelles que nous ne voulons pas conserver, nous les exclurons donc également à l’étape suivante.

 

Copier et coller des valeurs dans un nouveau classeur

En remarquant que toutes les dates de 2020 sont les lignes de transaction, sélectionnez uniquement les dates de 2020 :

mceclip4.png

 

Sélectionnez ensuite la ligne d’en-tête et les lignes de transaction sélectionnées (c’est-à-dire la ligne 5 et les suivantes dans la vue filtrée : vous pouvez commencer à la cellule A5, appuyer sur Ctrl-Shift-End pour atteindre la fin du fichier, et redresser la position si nécessaire), puis copier-coller les valeurs spéciales dans un nouveau classeur.

 

Dépannage du déséquilibre

Nous avons donc maintenant un fichier joliment formaté qui ressemble à ceci :

mceclip5.png

Mais nous constatons qu’il est déséquilibré car les débits ne sont pas égaux aux crédits!

Extraire les sous-totaux des comptes

Heureusement, nous remarquons que ce fichier comporte des sous-totaux de comptes (des lignes qui ressemblent à la ligne 168 dans la première image en haut de cette page). Et la façon dont nous identifions ces lignes de sous-total de compte est que la date (colonne B) est vide, et que la variation nette (colonne I) est un nombre (c’est-à-dire qu’elle n’est pas vide). 

Donc, de retour dans le fichier grand livre général original où nous avons ajouté la formule à la colonne K pour extraire et faire glisser l’Identifiant du compte vers le bas, et avec les filtres toujours activés, nous filtrons sur la date (la colonne B) est vide et la colonne I n’est pas vide :

mceclip23.png

Notez que les colonnes réelles de ces lignes de sous-total de compte ne correspondent pas aux en-têtes de colonne - elles sont dans un format légèrement différent des lignes de transaction, une colonne déplacée vers la gauche - Le solde de début est en fait dans la colonne E et le solde d’ouverture est en fait dans la colonne I. Mais puisque nous ne formatons pas les sous-totaux pour l’importation dans MindBridge, c’est correct. Nous devons seulement copier et coller les valeurs des colonnes dont nous avons besoin (colonnes E-I et K) dans une nouvelle feuille de calcul du classeur avec le GL formaté que nous utiliserons pour résoudre le déséquilibre. Nous nommerons les sous-totaux de cette feuille de calcul et renommerons les en-têtes pour éviter toute confusion : 

mceclip9.png

 

Tableau croisé dynamique sur les données du grand livre général formatées

Ensuite, nous créons un tableau croisé dynamique sur les données grand livre général formatées dans une nouvelle feuille de calcul, en totalisant les Débits et les Crédits par numéro de compte :

      1. Sélectionnez toutes les données formatées (vous pouvez commencer en haut et appuyer sur Ctrl-Shift-End pour atteindre la fin du fichier et ajuster la position au besoin), allez à Insérer -> Tableaux -> Tableau croisé dynamique

        mceclip10.png

        Le tableau/la plage : indiquera automatiquement la plage que vous avez sélectionnée pour les données (dans notre cas, nous avons nommé la feuille de calcul avec les données formatées GL « formatéesgrand livre général » et les données vont de A1 à G16519)

        mceclip11.png

        Gardez le bouton radio « Nouvelle feuille de calcul » sélectionné (ce qui devrait être le cas par défaut), puis cliquez sur OK

      2. Cela vous amènera à une nouvelle feuille de travail qui ressemble à ceci :

        mceclip12.png

      3. Nous voulons totaliser le Débit et le Crédit par Identifiant du compte.

        Faites défiler la liste NOM DU CHAMP et cliquez sur « Identifiant du compte » et faites-le glisser jusqu’à la section Lignes :

        mceclip13.png

      4. Ensuite, faites glisser Débit et crédit dans la section Valeurs :

        mceclip14.png

      5. Votre table pivotante devrait maintenant ressembler à ceci :

        mceclip15.png

 

Rechercher et comparer avec les sous-totaux du compte

Nous voulons maintenant comparer ces totaux de compte avec ceux que nous avons extraits du GL original et sauvegardés dans la feuille de calcul des sous-totaux :

mceclip16.png

 

sous-totaux du compte : formule de la colonne de débit

=SUMIF(subtotals!$G:$G,$A4,subtotals!B:B)

Rappelez-vous que dans la feuille de calcul des sous-totaux, la colonne G contient l’ID du compte et les colonnes B et C les montants du Débit et du Crédit :

mceclip17.png

La colonne A de la feuille de calcul du tableau croisé dynamique contient les Identifiants du compte. Ainsi, cette formule SUMIF obtient les totaux de débit et de crédit de la feuille de calcul des sous-totaux pour chaque Identifiant du compte dans le tableau croisé dynamique.

sous-totaux du compte : formule de la colonne de crédit (il suffit de copier et coller la formule de la colonne de débit)

=SUMIF(subtotals!$G:$G,$A4,subtotals!C:C)

 

Enfin, ajoutez les formules dans les colonnes F et G :

=B4-D4
=C4-E4

 

Sélectionnez les colonnes D-G et ajoutez des filtres :

mceclip18.png

 

Nous constatons qu’il y a quelques différences dans la colonne de débit :

mceclip19.png

mceclip20.png

Maintenant, nous avons identifié les numéros de compte à examiner pour les différences

 

Comparaison des sous-totaux avec le grand livre général formaté à partir des sous-totaux

Notez que si la liste des sous-totaux des grands livres généraux d’origine contient des numéros de compte qui ne figurent pas dans le grand livre général formaté, ceux-ci n’apparaîtront pas ici mais pourraient expliquer les différences. Pour les détecter, vous pouvez exécuter des formules d’ajout à la feuille de calcul des sous-totaux SUMIF() faisant référence au grand livre général formaté pour extraire les totaux des comptes du GL formaté :

mceclip21.png

mceclip22.png


Avez-vous autre chose en tête?

Rendez-vous dans la communauté avec vos réflexions et vos questions! Vous pouvez également parler en direct avec nous ou soumettre une demande d’aide supplémentaire.


 

Cet article vous a-t-il été utile?