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

Dégrouper et fractionner : Extraire des motifs complexes

  • Mise à jour

Sommaire

Cet article vous guidera dans le processus de fractionnement d’ensembles d’informations plus complexes dans un format utilisable.


Résolution

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

mceclip1.png

Les problèmes :

  1. Il n’y a pas de ligne d’en-tête (viole l’exigence globale 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 jusqu’au numéro de compte Grand livre général suivant) (viole l’exigence de la ligne 1).
  3. Les informations sur le compte figurent dans la colonne A, mais contiennent « Numéro de compte Grand livre général : ». suivi du numéro de compte suivi de deux caractères d’espacement suivi de la description du compte (lignes 6, 3862) (viole l’exigence de la colonne 1).
  4. Il y a des lignes de total/sous-total inutiles (ligne 3861) et des lignes vides sous l’en-tête Numéro de compte Grand livre général qui crée un nouveau compte (viole la condition 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 mettre les noms des colonnes).
  2. Dégroupez par compte (faites glisser les informations sur le compte vers chaque ligne de transaction).
  3. Fractionnez le numéro de compte et la description du compte en différentes colonnes.
  4. Excluez les lignes totales/sous-totales/blanches étrangères et les lignes étrangères (lignes 1-4) au-dessus de la ligne d’en-tête nouvellement créée (ligne 5).

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

mceclip2.png

Identifiant du compte : Cellule A6 et cellules inférieures

=IF(LEFT(C6,19)="GL Account Number: ",MID(C6,20,SEARCH(" ",C6,20)-20),A5)
  • Les lignes d’informations sur les comptes peuvent être identifiées par les valeurs de la colonne C qui commencent par « Numéro de compte Grand livre général » : « . Le 19 dans la formule représente le fait que le texte « Numéro de compte Grand livre général : » est composé de 19 caractères, et le 20 représente la position où le numéro de compte commence (juste après le « Numéro de compte Grand livre général : »)
  • RECHERCHE(" ",C6,20)
    trouve la position du premier caractère d’espacement dans la chaîne C6, en partant de la position 20 (où commence le numéro de compte); en d’autres termes, nous essayons de trouver où se termine le numéro de compte et où commence la description du compte. Puis 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 la suivante :
    MID(texte, start_num, num_chars)
    • où texte est la chaîne à rechercher (C6)
    • start_num est la position à laquelle il faut commencer (20 parce que « Numéro de compte Grand livre général : » est composé de 19 caractères et que le numéro de compte commence juste après).
    • et num_chars est le nombre de caractères à inclure,
      RECHERCHE(" ",C6,20)-20
      c’est-à-dire la longueur du numéro de compte comme indiqué ci-dessus
  • IF(LEFT(C6,19)="GL Account Number : ", dit de renvoyer l’Identifiant 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 glisser vers le bas (dégroupage) le numéro de compte vers toutes les transactions qui lui sont inférieures.

Description de compte : Cellule B6 et cellules inférieures

=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 Identifiant 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 "Numéro de compte GL : "). LEN(C6) garantit que tout le texte restant jusqu’à 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 parce que ce fichier particulier a moins de 50 000 lignes. Changez cela en un nombre suffisamment grand pour tenir compte du nombre total de lignes de votre fichier. Nous ajoutons ces formules afin de vérifier que le fichier s’équilibre lorsque nous effectuons l’étape finale de filtrage pour ne garder que les lignes de transaction (suivant).

Filtrer pour conserver uniquement les rangées de transaction

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

mceclip3.png

Copier et coller des valeurs dans un nouveau classeur

En remarquant que toutes les dates sont les lignes de transaction, sélectionnez uniquement les dates (décochez les blancs).

mceclip5.png

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

Notre dernière étape consiste à sélectionner 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.

mceclip0.png

Enfin, dans le nouveau classeur, veillez à formater la colonne Date comme Date au format AAAA-MM-JJ.

mceclip7.png

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

mceclip8.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?