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 :
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 :
- 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).
- 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)
- 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) :
- Dissocier par compte (faire glisser les informations du compte vers chaque rangée de transaction)
- 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 :
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 leIF(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)
=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 :
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 :
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 :
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 :
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 :
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 :
- 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é dynamique
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)
- 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 :
Nous voulons totaliser Débit et Crédit par ID du compte.
- Faites défiler la liste des NOMS DE CHAMP et cliquez et faites glisser l'"ID du compte" vers la section Rangées :
- Ensuite, faites glisser Débit et Crédit vers la section Valeurs :
- Maintenant, votre tableau croisé dynamique devrait ressembler à ceci :
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 :
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 :
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 :
Nous voyons qu'il y a quelques différences dans la colonne débit :
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é :
Autre chose en tête ? Discutez avec nous ou soumettez une demande pour obtenir de l'aide supplémentaire.