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

Guides et conseils sur le formatage des données : Dégroupage des fichiers : dégrouper et fractionner.

  • Mise à jour

Sommaire

Ce guide a pour but de vous aider à comprendre les meilleures façons de dégrouper et de fractionner les numéros de compte et les descriptions de compte qui apparaissent dans la même colonne au sein d’un fichier. 


Résolution

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

mceclip1.png

Les problèmes

  1. Ce fichier est regroupé par compte : une seule ligne contient des informations sur le compte (lignes 7, 5695), suivies de toutes les transactions pour cette ligne (lignes 8-5691, 5696-6176) (enfreint l’exigence de la ligne 1)
  2. Les informations sur le compte sont dans la colonne A mais contiennent à la fois le numéro de compte et la description du compte séparés par " - " (caractère d’espacement plus - plus caractère d’espacement) (lignes 7, 5695) (enfreint l’exigence de la colonne 1)
  3. Il y a des lignes de total/sous-total inutiles (5692-5693, 6177-6178) et des lignes vides sous celles-ci (viole l’exigence globale 2)

La solution (formatage nécessaire)

  1. Dégroupez par compte (faites glisser les informations sur le compte vers chaque ligne de transaction)
  2. Fractionnez le numéro de compte et la description du compte en différentes colonnes.
  3. Exclure 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 (ligne 5)

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

mceclip2.png

Identifiant du compte : Cellule A7 et cellules inférieures

=IFERROR(LEFT(C7,SEARCH(" - ",C7)-1),A6)
  • RECHERCHE(" - ",C7)
    trouve la position dans la chaîne C7 où la première chaîne « - » apparaît. Nous en soustrayons 1 pour obtenir le nombre de caractères que le numéro de compte occupe (5 caractères pour le compte 11110),
  • puis nous utilisons LEFT(C7,) pour obtenir le nombre de caractères les plus à gauche de cette chaîne de compte (donc les 5 premiers caractères de la cellule C7).
  • Enfin, IFERROR(A,B) renvoie A si A n’est pas une erreur, B si A est une erreur.
      • Nous voulons extraire le numéro de compte et la description du compte uniquement des lignes avec ces informations dans la colonne C.
      • Nous remarquons que ces cellules avec le numéro de compte et la description du compte sont identifiées par la présence de «  -  » séparant le numéro de compte et la description du compte. RECHERCHE() renvoie une erreur (#VALEUR !) si la chaîne de caractères n’est pas trouvée; ainsi, pour tout le reste des lignes, #VALEUR ! est renvoyé par RECHERCHE() (qui remonte jusqu’à la formule GAUCHE() dans laquelle elle est imbriquée. Pour ces autres lignes, nous voulons simplement retourner le numéro de compte que nous avions extrait dans la ligne ci-dessus.
      • Ainsi, dans cet exemple, l’imbrication de GAUCHE() et RECHERCHE() dans la cellule A7 renvoie 1110, car la cellule C7 contient " - ".
      • Mais dans la cellule A8,
        =IFERROR(LEFT(C8,SEARCH(" - ",C8)-1),A7)
        l’imbrication de GAUCHE() et de RECHERCHER() renvoie #VALUE ! car la cellule C8 (2019-04-01) ne contient pas de " - ".
      • Cela est détecté par IFERROR(), qui, en voyant la #VALEUR!, passe à A7, qui est 1110 comme discuté ci-dessus, et donc retourne également 1110. Tout cela continue jusqu’à ce qu’il atteigne la ligne 5695, où il constate que la cellule C5695 contient «  -  » et extrait donc le compte 1-1112.

Description de compte : Cellule B7 et cellules inférieures

=IFERROR(MID(C7,SEARCH(" - ",C7)+3,LEN(C7)),B6)

Les formules de cette colonne suivent la même logique que celle de la colonne Identifiant du compte (colonne A), sauf au lieu d’utiliser GAUCHE(), elle utilise MID(), qui est utile pour extraire du texte de n’importe où, pas du début à la fin, ou pas à la fin. La syntaxe est :

MID(texte, start_num, num_chars)

où texte est la chaîne à rechercher (C7)

start_num est la position à laquelle il faut commencer. Nous utilisons

RECHERCHE(" - ",C7)+3

parce que nous voulons commencer après le « - ».

Rappel ci-dessus, RECHERCHE(« - », C7) donne la position où le « - » commence. Puisque « - » compte 3 caractères, nous en ajoutons 3 pour obtenir la position de début de la description du compte.

Enfin, le LEN(C7) donne la longueur de la ficelle dans la cellule C7, afin de s’assurer que nous l’amenons jusqu’à la fin.

Sous-totaux

Voici les formules pour les cellules G1 et H1 :

=SUBTOTAL(9,G5:G50000)
=SUBTOTAL(9,H5: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 C (Date) et nous voyons qu’il y a les dates 2020 et 2019, puis les autres sont les numéros de compte :

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 :

mceclip6.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.

mceclip8.png

Enfin, dans le nouveau classeur, veillez à formater la colonne Date comme Date au format AAAA-MM-JJ (hit Ctrl-1 sur PC ou ⌘1 sur Mac):

mceclip9.png

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

mceclip10.png

Dégroupez dans Excel, fractionnez dans MindBridge!

MindBridge offre déjà une fonction pendant le mise en correspondance des colonnes qui permet le fractionnement des colonnes, donc si vous préférez utiliser une formule plus simple uniquement pour dégrouper, sans fractionnement en même temps, vous pouvez simplement ajouter une colonne Compte qui tirera la valeur de la colonne B si elle contient « - », sinon, tirez la valeur ci-dessus :

mceclip11.png

=IF(ISERROR(SEARCH(" - ",B7)),A6,B7)

Procédez ensuite aux étapes décrites ci-dessus pour filtrer, copier et coller dans un nouveau classeur.

Commencez à importer le fichier grand livre général formaté dans MindBridge, puis à l’étape 2 pour le mappage des colonnes, cliquez sur les trois points à droite, et sélectionnez Diviser la colonne. Consultez cet article pour plus d’informations et des instructions pas à pas pour fractionner les colonnes : Fractionner les colonnes

Fractionnement de compte plus compliqué

Voyez ici un exemple plus complexe de la façon de construire une formule pour fractionner un numéro de compte.


Avez-vous autre chose en tête? Vous pouvez également clavarder avec nous ou soumettre une demande d’aide supplémentaire.

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