Fragen? Wir haben die Antworten.

Suche

  1. MindBridge: German (Germany)
  2. Erste Schritte
  3. Dateien importieren
  4. Daten formatieren

Guide de mise en forme des données : Dissocier et diviser les numéros de compte et les descriptions

  • Aktualisiert

Résumé

Ce guide est conçu pour vous aider à comprendre les meilleures façons de désagréger et de diviser 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 GL :

mceclip1.png

Les problèmes

  1. Ce fichier est regroupé par compte : une seule rangée contient des informations de compte (rangées 7, 5695), suivie de toutes les transactions pour cette rangée (rangées 8-5691, 5696-6176) (viole exigence de rangée 1)
  2. Les informations de 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 espace plus - plus caractère espace) (rangées 7, 5695) (viole exigence de colonne 1)
  3. Il y a des rangées de total/sous-total inutiles (5692-5693, 6177-6178) et des rangées vides en dessous (viole exigence globale 2)

La solution (mise en forme nécessaire)

  1. Désagréger par compte (faire glisser les informations de compte vers chaque rangée de transaction)
  2. Diviser le numéro de compte et la description du compte en différentes colonnes
  3. Exclure les rangées de total/sous-total/rangées vides superflues et les rangées superflues (rangées 1-4) au-dessus de la rangée d'en-tête (rangée 5)

Nous insérons 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 :

mceclip2.png

ID du compte : Cellule A7 et en dessous

=SIERREUR(GAUCHE(C7,CHERCHE(" - ",C7)-1),A6)
  • CHERCHE(" - ",C7)
    trouve la position dans la chaîne C7 où apparaît la première chaîne " - ". Nous soustrayons 1 de cela pour obtenir le nombre de caractères que le numéro de compte occupe (5 caractères pour le compte 11110),
  • puis nous utilisons GAUCHE(C7,) pour obtenir le nombre de caractères le plus à gauche de cette chaîne de compte (donc les 5 premiers caractères dans la cellule C7).
  • Enfin, SIERREUR(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 rangées avec cette information dans la colonne C.
      • Nous remarquons que ces cellules avec numéro de compte et description de compte sont identifiées par la présence de " - " séparant le numéro de compte et la description du compte. CHERCHE() renvoie une erreur (#VALEUR!) si la chaîne n'est pas trouvée ; ainsi, pour toutes les autres rangées, #VALEUR! est renvoyé par CHERCHE() (qui remonte à la formule GAUCHE() dans laquelle elle est imbriquée. Pour ces autres rangées, nous voulons simplement renvoyer le numéro de compte que nous avions extrait dans la rangée au-dessus.
      • Donc dans cet exemple, le GAUCHE() et CHERCHE() imbriqués dans la cellule A7 renvoient 1110, car la cellule C7 contient " - ".
      • Mais dans la cellule A8, 
        =SIERREUR(GAUCHE(C8,CHERCHE(" - ",C8)-1),A7)
        le GAUCHE() et CHERCHE() imbriqués renvoient #VALEUR! car la cellule C8 (2019-04-01) ne contient pas " - ".
      • Cela est capturé par SIERREUR(), qui, voyant le #VALEUR!, passe à A7, qui est 1110 comme discuté ci-dessus, et renvoie donc également 1110. Cela continue jusqu'à ce qu'il atteigne la rangée 5695, lorsqu'il trouve que la cellule C5695 contient " - " et extrait donc le compte 1-1112

Description du compte : Cellule B7 et en dessous

=SIERREUR(STXT(C7,CHERCHE(" - ",C7)+3,LONGUEUR(C7)),B6)

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

STXT(texte, no_début, nb_car)

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

no_début est la position de départ. Nous utilisons...

CHERCHE(" - ",C7)+3

...parce que nous voulons commencer après le " - ".

Rappelez-vous d'en haut, CHERCHE(" - ",C7) donne la position où commence le " - ". Comme " - " fait 3 caractères de long, nous ajoutons 3 pour obtenir la position où commence la description du compte.

Enfin, le LONGUEUR(C7) donne la longueur de la chaîne dans la cellule C7, pour s'assurer que nous la prenons jusqu'à la fin.

Sous-totaux

Voici les formules pour les cellules G1 et H1 :

=SOUS.TOTAL(9,G5:G50000)
=SOUS.TOTAL(9,H5:H50000)

Nous utilisons 50000 car 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 C (Date) et nous voyons qu'il y a les dates 2020 et 2019, puis le reste sont des numéros de compte :

mceclip3.png

 

Copier et coller les valeurs dans un nouveau carnet de travail

En notant que toutes les dates sont les rangées de transaction, sélectionnez uniquement les dates :

mceclip6.png

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 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-Maj-Fin 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

mceclip8.png

Enfin, dans le nouveau carnet de travail, assurez-vous de formater la colonne Date en tant que Date au format AAAA-MM-JJ (appuyez sur 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ésagréger dans Excel, diviser dans MindBridge

MindBridge propose déjà une fonctionnalité lors de la cartographie des colonnes qui permet la division des colonnes, donc si vous préférez utiliser une formule plus simple uniquement pour désagréger, sans diviser en même temps, vous pouvez simplement ajouter une colonne de compte qui tirera la valeur de la colonne B si elle contient " - ", sinon tirer la valeur trouvée au-dessus :

mceclip11.png

=SI(ESTERREUR(CHERCHE(" - ",B7)),A6,B7)

Puis procédez avec les étapes décrites ci-dessus pour filtrer, copier et coller dans un nouveau carnet de travail.

Commencez à importer le fichier GL formaté dans MindBridge, puis à l'étape 2 pour la cartographie des colonnes, cliquez sur les trois points à droite, et sélectionnez Colonne divisée. Apprenez à diviser les colonnes.

Division de compte plus compliquée

Apprenez à construire une formule pour diviser un numéro de compte.

Autre chose en tête ? Discutez avec nous ou soumettez une demande pour obtenir une assistance supplémentaire.

War dieser Beitrag hilfreich?