Résumé

Cet article vous guidera à travers le processus de division d'ensembles d'informations plus complexes en un format utilisable.

Résolution

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

Les problèmes

1. Il n'y a pas de rangée d'en-tête (viole l'exigence générale 1).
2. Ce fichier est regroupé par compte : une seule rangée contient des informations de compte (rangées 6, 3862), suivie de toutes les transactions pour cette rangée (rangées 8-3860, 3864 jusqu'au prochain numéro de compte GL) (viole l'exigence de rangée 1).
3. Les informations de compte sont dans la colonne A mais contiennent "Numéro de compte GL : " suivi du numéro de compte suivi de deux caractères d'espace suivis de la description du compte (rangées 6, 3862) (viole l'exigence de colonne 1).
4. Il y a des rangées de total/sous-total inutiles (rangée 3861) et des rangées vides sous l'en-tête du numéro de compte GL qui commence un nouveau compte (viole l'exigence générale 2).

La solution (mise en forme nécessaire)

1. Ajoutez une rangée d'en-tête (la rangée 5 est un endroit approprié pour mettre les noms de colonne).
2. Dissociez par compte (faites glisser les informations de compte vers chaque rangée de transaction).
3. Divisez le numéro de compte et la description du compte en différentes colonnes.
4. Excluez les rangées de total/sous-total/inutiles et les rangées inutiles (rangées 1-4) au-dessus de la nouvelle rangée d'en-tête créée (rangée 5).

Insérez deux colonnes, "ID du compte" et "Description du compte", avant le début du fichier pour diviser le numéro de compte et la description du compte et les faire glisser vers le bas.

ID du compte : Cellule A6 et ci-dessous

=IF(LEFT(C6,19)="GL Account Number: ",MID(C6,20,SEARCH(" ",C6,20)-20),A5)

Les rangées d'informations de compte peuvent être identifiées par les valeurs de la colonne C qui commencent par "GL Account Number: ". Le 19 dans la formule représente le fait que le texte "GL Account Number: " est long de 19 caractères, et le 20 représente la position où commence le numéro de compte (juste après le "GL Account Number: ")

SEARCH(" ",C6,20)

....trouve la position du premier caractère d'espace dans la chaîne C6, à partir de la position 20 (où commence le numéro de compte); en d'autres termes, nous essayons de découvrir où se termine le numéro de compte et où commence la description du compte. Ensuite, nous soustrayons 20 de cette position pour trouver la longueur du numéro de compte, à passer à MID() ensuite :

MID(C6,20,SEARCH(" ",C6,20)-20)

...est ce qui extrait le numéro de compte. La syntaxe de la fonction MID() est :
MID(texte, start_num, num_chars)

• où texte est la chaîne à rechercher (C6)
• start_num est la position de départ (20 parce que "GL Account Number: " est long de 19 caractères et le numéro de compte commence juste après)
• et num_chars est le nombre de caractères à inclure

SEARCH(" ",C6,20)-20

...c'est-à-dire, la longueur du numéro de compte comme discuté ci-dessus

IF(LEFT(C6,19)="GL Account Number: ", dit de retourner l'ID du compte (le résultat de la fonction MID() ci-dessus) si C6 commence par "GL Account Number: ", A5 (la cellule calculée ci-dessus) sinon - c'est ce qui fait le glissement vers le bas (dissociation) du numéro de compte à toutes les transactions en dessous.

Description du compte : Cellule B6 et ci-dessous

=IF(LEFT(C6,19)="GL Account Number: ",TRIM(MID(C6,SEARCH(" ",C6,20)+2,LEN(C6))),B5)

Les formules de cette colonne suivent la même logique que celle de la colonne ID du compte (colonne A), sauf qu'elle extrait le texte après le numéro de compte (c'est-à-dire, après le premier espace après "GL Account Number: "). LEN(C6) garantit que tout le texte restant jusqu'à la fin est retourné.

Sous-totaux

Voici les formules pour les cellules G1 et H1 :

=SUBTOTAL(9,G7:G50000)
=SUBTOTAL(9,H7:H50000)

Nous utilisons 50000 parce que ce fichier particulier a moins de 50000 rangées. Changez cela pour un nombre suffisamment grand pour accueillir le nombre total de rangées que votre fichier contient. Nous ajoutons ces formules afin de vérifier que le fichier est équilibré lorsque nous faisons l'étape finale de filtrage pour ne garder que les rangées de transaction (suivant).

Filtrer pour ne garder que les rangées de transaction

Après avoir activé les filtres, vérifiez la colonne D (date) et nous voyons qu'il y a des dates en 2020, et des blancs.

Copier et coller les valeurs dans un nouveau carnet de travail

Notant que toutes les dates sont les rangées de transaction, sélectionnez uniquement les dates (décochez les blancs).

Notez que les résultats de nos formules SUBTOTAL() dans les cellules G1 et H1 montrent que le total des transactions est équilibré (hourra!)

Notre dernière étape est de sélectionner la rangée d'en-tête et les rangées de transaction sélectionnées (c'est-à-dire, la rangée 5 et au-delà dans la vue filtrée — vous pouvez commencer à la cellule A5, appuyer sur Ctrl-Shift-End pour atteindre la fin du fichier, et ajuster la position si nécessaire), puis copier, puis coller des valeurs spéciales dans un nouveau carnet de travail.

Enfin, dans le nouveau carnet de travail, assurez-vous de formater la colonne Date en format Date AAAA-MM-JJ.

Et maintenant, nous avons un fichier magnifiquement formaté, prêt à être importé dans MindBridge !

Quelque chose d'autre en tête ? Discutez avec nous ou soumettez une demande pour une assistance supplémentaire.