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

Guide de mise en forme des données : Dissociation et vérification du déséquilibre

  • Mise à jour

Résumé

Ce guide est conçu pour vous aider à comprendre les meilleures façons de résoudre un déséquilibre dans les résultats de MindBridge lorsque votre fichier GL est formaté et peut être facilement ingéré dans MindBridge. 

Il s'agit d'un guide plus avancé et complet. Donc, si vous ne l'avez pas encore fait, nous vous recommandons d'essayer les solutions trouvées dans Les montants ou les débits et crédits ne s'équilibrent pas lors de l'importation du GL dans MindBridge.


Résolution

Considérez l'extrait suivant d'un fichier GL :

mceclip0.png

Tout d'abord, nous allons brièvement passer en revue comment formater ce fichier. Si vous préférez, vous pouvez passer cette partie et aller directement à la résolution du déséquilibre.


Les problèmes :

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

La solution (formatage nécessaire) :

  1. Dissocier par compte (faire glisser les informations du compte vers chaque rangée de transaction)
  2. Exclure les rangées de total/sous-total/inutiles (rangées 7-9, 11, 168-170) et les rangées inutiles (rangées 1-4) au-dessus de la rangée d'en-tête (rangée 5)

Nous ajoutons une colonne "ID du compte" à la fin du fichier pour extraire le numéro de compte des rangées de compte et le faire glisser vers le bas :

mceclip2.png

ID du compte : Cellule K6 et en dessous

=IF(NOT(ISERROR(SEARCH("????-??-??",B6))),B6,IF(NOT(ISERROR(SEARCH("????-??-??",A6))),A6,K5))
  • SEARCH("????-??-??",B6),   SEARCH("????-??-??",A6)
    trouver 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 à n'importe quel caractère unique. Donc "????-??-??" signifie n'importe quels quatre caractères uniques, suivis de -, suivis de n'importe quels deux caractères uniques, suivis de -, suivis de n'importe quels deux caractères uniques. Le but est de tester si un numéro de compte est trouvé dans la cellule (B6 ou A6)
  • Le NOT(ISERROR()) enveloppé autour de ces expressions SEARCH() indique simplement si les cellules B6 ou A6 contiennent des numéros de compte (c'est-à-dire que le motif "????-??-??" a été trouvé). Donc
    IF(NOT(ISERROR(SEARCH("????-??-??",B6))),B6,
    signifie renvoyer B6 si B6 est un numéro de compte, et le
    IF(NOT(ISERROR(SEARCH("????-??-??",A6))),A6,
    qui suit signifie sinon si A6 est un numéro de compte, renvoyer A6, et enfin, le K5 à la fin signifie sinon renvoyer K5 (l'ID du compte calculé dans la rangée au-dessus, c'est-à-dire que cela fait glisser les numéros de compte trouvés)
Note : 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 GL si elle est présente dans le plan comptable ; pour des raisons de concision, 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 ci-dessus pour l'ID du compte, mais puisque la description du compte est toujours dans la colonne E si soit la colonne A soit la colonne B a un numéro de compte, nous utilisons la fonction OR() pour tester si soit B6 soit A6 est un numéro de compte, et renvoyer E6 si oui, L5 (la description du compte calculée dans la rangée au-dessus, pour faire glisser) pour la formule suivante dans la cellule L6 :
=IF(OR(NOT(ISERROR(SEARCH("????-??-??",B6))),NOT(ISERROR(SEARCH("????-??-??",A6)))),E6,L5)

Filtrer pour ne garder que 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 indésirables que nous ne voulons pas conserver, nous les exclurons donc également à l'étape suivante.

Copier et coller les valeurs dans un nouveau carnet de travail

En notant que toutes les dates de 2020 sont les rangées de transaction, sélectionnez uniquement les dates de 2020 :

mceclip4.png

Ensuite, sélectionnez la rangée d'en-tête et les rangées de transaction sélectionnées (c'est-à-dire, la rangée 5 et suivantes dans la vue filtrée - vous pouvez commencer à la cellule A5, appuyer sur Ctrl-Maj-Fin pour atteindre la fin du fichier, et ajuster la position si nécessaire), puis copier, puis coller les valeurs spéciales dans un nouveau carnet de travail.


Résolution du déséquilibre

Nous avons maintenant un fichier bien 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 a des sous-totaux de compte (des rangées qui ressemblent à la rangée 168 dans la première image en haut de cette page). Et la façon dont nous identifions ces rangées de sous-total de compte est que la Date (colonne B) est vide, et le Changement net (colonne I) est un nombre (c'est-à-dire, non vide). 

Donc, dans le fichier GL original où nous avons ajouté la formule à la colonne K pour extraire et faire glisser l'ID du compte vers le bas, et avec les filtres toujours activés, nous filtrons sur Date (colonne B) est vide et la colonne I n'est pas vide :

mceclip23.png


Notez que les colonnes réelles pour ces rangées 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 rangées de transaction, une colonne décalée vers la gauche - le Solde initial est en fait dans la colonne E et le Solde final est en fait dans la colonne I. Mais comme nous ne formatons pas les sous-totaux pour l'importation dans MindBridge, cela va. Nous avons seulement besoin de copier et coller les valeurs des colonnes dont nous avons besoin (colonnes E-I et K) dans une nouvelle feuille de calcul dans le carnet de travail avec le GL formaté que nous utiliserons pour résoudre le déséquilibre.

Nous nommerons cette feuille de calcul sous-totaux et renommerons les en-têtes pour éviter toute confusion : 

mceclip9.png

 

Tableau croisé dynamique sur les données GL formatées

Ensuite, nous créons un tableau croisé dynamique sur les données GL formatées dans une nouvelle feuille de calcul, totalisant les Débits et 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-Maj-Fin pour atteindre la fin du fichier, et ajuster la position si nécessaire), allez à Insertion -> Tableaux -> Tableau croisé dynamiquemceclip10.png

    La Table/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 GL formatées "formattedGL" et les données vont de A1 à G16519)mceclip11.png

  2. Gardez le bouton radio "Nouvelle feuille de calcul" sélectionné (ce qui devrait être par défaut), puis cliquez sur OK.
    Cela vous amènera à une nouvelle feuille de calcul ressemblant à ceci :mceclip12.png

    Nous voulons totaliser Débit et Crédit par ID du compte
    .

  3. Faites défiler la liste des NOMS DE CHAMP et cliquez et faites glisser l'"ID du compte" vers la section Rangées :mceclip13.png

  4. Ensuite, faites glisser Débit et Crédit vers la section Valeurs :mceclip14.png

  5. Maintenant, votre tableau croisé dynamique devrait ressembler à ceci :mceclip15.png

Rechercher et comparer avec les sous-totaux des comptes

Maintenant, nous voulons comparer ces totaux de compte avec ceux que nous avions extraits du GL original et enregistrés dans la feuille de calcul sous-totaux :

mceclip16.png

Sous-totaux des comptes : formule de la colonne débit

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

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

mceclip17.png


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

Sous-totaux des comptes : formule de la colonne crédit (copiez et collez simplement la formule de la colonne 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 voyons qu'il y a quelques différences dans la colonne débit :

mceclip19.png

mceclip20.png


Nous avons maintenant identifié les numéros de compte à examiner pour les différences.

Comparer les sous-totaux avec le GL formaté à partir des sous-totaux

Notez que s'il y a des numéros de compte dans la liste des sous-totaux des GLs originaux qui ne sont pas dans le GL formaté, ceux-ci n'apparaîtront pas ici mais pourraient expliquer les différences. Pour les détecter, vous pouvez ajouter à la feuille de calcul des sous-totaux des formules SUMIF() référencées au GL formaté pour extraire les totaux de compte du GL formaté :

mceclip21.png

mceclip22.png


Autre chose en tête ? Discutez avec nous ou soumettez une demande pour obtenir de l'aide supplémentaire.

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