Résumé

Cet article vous guidera tout au long du processus de fractionnement d’ensembles d’informations plus complexes dans un format utilisable.

Résolution

Considérez l’extrait suivant d’un fichier du GL :

Les problèmes :

1. Il n’existe pas de ligne d’en-tête (contraire à l’exigence générale 1).
2. Ce fichier est groupé par compte : une seule ligne contient des informations sur le compte (lignes 6, 3862), suivies de toutes les transactions pour cette ligne (lignes 8 à 3860, 3864 au numéro de compte du GL suivant) (contraire à l’exigence 1 relative à la ligne).
3. Les informations du compte se trouvent dans la colonne A, mais contiennent « Numéro de compte du GL : » suivi du numéro de compte suivi de deux caractères d’espace suivis de la description du compte (lignes 6, 3862) (contraire à l’exigence 1 relative à la colonne).
4. Il existe des lignes de total/sous-total inutiles (ligne 3861) et des lignes vides sous l’en-tête du numéro de compte du GL qui commence un nouveau compte (contraire à l’exigence générale 2).

La solution (formatage nécessaire) :

1. Ajoutez une ligne d’en-tête (la ligne 5 est un endroit approprié pour placer les noms de colonne).
2. Dissociez par compte (glissez les informations du compte en bas, vers chaque ligne de transaction).
3. Fractionnez en colonnes différentes le numéro de compte et la description du compte.
4. Excluez les lignes de total/sous-total/vides superflues et les lignes supplémentaires (lignes 1 à 4) au-dessus de la ligne d’en-tête nouvellement créée (ligne 5).

Insérez deux colonnes, « Identifiant de compte » et « Description du compte », avant le début du fichier pour fractionner le numéro de compte et la description de compte et les faire glisser vers le bas.

Identifiant de compte : Cellule A6 et inférieure

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

• Les lignes d’informations sur le compte peuvent être identifiées par les valeurs de la colonne C qui commencent par « Numéro de compte du GL :  ». Le 19 se trouvant dans la formule représente le fait que le texte « Numéro de compte du GL : » comprend 19 caractères, et le 20 représente la position où commence le numéro de compte (juste après le « Numéro de compte du GL : »)

• SEARCH(" ",C6,20)

  trouve la position du premier caractère d’espace dans la chaîne C6, en commençant par la position 20 (où commence le numéro de compte) ; en d’autres termes, nous essayons de savoir 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, afin de passer ensuite à MID() :

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

  permet d’extraire le numéro de compte. La syntaxe de la fonction MID() est :
  MID(text, start_num, num_chars)
  • où « text » représente la chaîne à rechercher (C6)
  • start_num est la position de départ (20, car « Numéro de compte du GL : » contient 19 caractères, et le numéro de compte commence juste après)
  • et num_chars représente le nombre de caractères à inclure,

    SEARCH(" ",C6,20)-20

    c’est-à-dire la longueur du numéro de compte comme indiqué ci-dessus
• IF(LEFT(C6,19)="GL Account Number:", invite à renvoyer l’identifiant de compte (le résultat de la fonction MID() ci-dessus) si C6 commence par « Numéro de compte du GL : », A5 (la cellule calculée ci-dessus) dans le cas contraire. C’est ce qui permet de faire glisser (dissocier) le numéro de compte vers toutes les transactions en dessous.

Description du compte : Cellule B6 et inférieure

=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 celles de la colonne Identifiant de compte (colonne A), sauf qu’elles permettent d’extraire le texte après le numéro de compte (c.-à-d. après le premier espace qui suit le texte « Numéro de compte du GL : »). LEN(C6) garantit que tout le texte restant à la fin est renvoyé.

Sous-totaux

Voici les formules pour les cellules G1 et H1 :

=SOUS-TOTAL(9,G7:G50000)
=SOUS-TOTAL(9,H7:H50000)

Nous utilisons 50000 car ce fichier particulier comporte moins de 50 000 lignes. Modifiez ce chiffre par un autre suffisamment grand pour correspondre au nombre total de lignes de votre fichier. Nous ajoutons ces formules afin de vérifier que le fichier s’équilibre lorsque nous exécutons l’étape finale de filtrage pour ne conserver que les lignes de transaction (suivant).

Filtrer pour conserver uniquement les lignes de transaction

Après avoir activé les filtres, vérifiez la colonne D (date). Nous remarquons la présence de dates dans l’année 2020, ainsi que des espaces vides.

Copier et coller des valeurs dans un nouveau classeur

En notant que toutes les dates représentent les lignes de transaction, sélectionnez uniquement les dates (espaces vides non cochés).

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

Notre étape finale consiste à sélectionner la ligne d’en-tête et les lignes de transaction sélectionnées (c.-à-d., ligne 5 et suivantes dans l’affichage filtré - vous pouvez commencer à la cellule A5, appuyer sur Ctrl-Maj-Fin pour arriver à la fin du fichier et rectifier la position si nécessaire), puis à copier et coller des valeurs spéciales dans un nouveau classeur.

Enfin, dans le nouveau classeur, assurez-vous de formater la colonne Date au format AAAA-MM-JJ.

Nous disposons désormais d’un fichier correctement formaté, prêt à être importé dans MindBridge !

Quelque chose d’autre vous vient à l’esprit ?

Rejoignez la communauté pour faire part de vos réflexions et demandes de renseignements ! Vous pouvez également discuter avec nous ou soumettre une demande d’assistance supplémentaire.