Guides et conseils sur le formatage des données : Dégrouper et fractionner les numéros de compte et les descriptions

Mise à jour 06 juillet 2023 21:48

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 :

Les problèmes

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)
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)
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)

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

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.

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 :

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 :

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.

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

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

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 :

=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. Apprenez comment fractionner les colonnes.

Fractionnement de compte plus compliqué

Apprenez comment créer 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.

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