Résumé
Cet article vous guidera à travers le processus de division de jeux d'informations plus complexes en un format utilisable.
Résolution
Considérez l'extrait suivant d'un fichier GC :
Les problèmes
- Il n'y a pas de rangée d'en-tête (viole exigence générale 1).
- Ce fichier est regroupé par compte : une seule rangée contient des informations sur le compte (rangées 6, 3862), suivie de toutes les transactions pour cette rangée (rangées 8-3860, 3864 jusqu'au prochain numéro de compte GC) (viole exigence de rangée 1).
- Les informations sur le compte se trouvent dans la colonne A mais contiennent "Numéro de compte GC : " suivi du numéro de compte suivi de deux caractères d'espace suivis de la description du compte (rangées 6, 3862) (viole exigence de colonne 1).
- Il y a des rangées de total/sous-total inutiles (rangée 3861) et des rangées vides sous l'en-tête du numéro de compte GC qui commence un nouveau compte (viole exigence générale 2).
La solution (formatage nécessaire)
- Ajouter une rangée d'en-tête (la rangée 5 est un endroit approprié pour mettre les noms de colonne).
- Dissocier par compte (faire glisser les informations du compte vers chaque rangée de transaction).
- Diviser le numéro de compte et la description du compte en différentes colonnes.
- Exclure les rangées de total/sous-total/inutiles et les rangées inutiles (rangées 1-4) au-dessus de la nouvelle rangée d'en-tête créée (rangée 5).
Insérez 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.
ID du compte : Cellule A6 et en dessous
=IF(LEFT(C6,19)="Numéro de compte GC : ",MID(C6,20,SEARCH(" ",C6,20)-20),A5)
Les rangées d'informations sur le compte peuvent être identifiées par les valeurs de la colonne C qui commencent par "Numéro de compte GC : ". Le 19 dans la formule représente le fait que le texte "Numéro de compte GC : " fait 19 caractères de long, et le 20 représente la position où commence le numéro de compte (juste après "Numéro de compte GC : ").
SEARCH(" ",C6,20)
....trouve la position du premier caractère d'espace dans la chaîne C6, en commençant à la position 20 (où commence le numéro de compte); en d'autres termes, nous essayons de découvrir 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, à passer ensuite à MID() :
MID(C6,20,SEARCH(" ",C6,20)-20)
...c'est ce qui extrait le numéro de compte. La syntaxe de la fonction MID() est :
MID(texte, start_num, num_chars)
- où texte est la chaîne à rechercher (C6)
- start_num est la position de départ (20 car "Numéro de compte GC : " fait 19 caractères de long et le numéro de compte commence juste après)
- et num_chars est le nombre de caractères à inclure
SEARCH(" ",C6,20)-20
...c'est-à-dire la longueur du numéro de compte comme discuté ci-dessus
IF(LEFT(C6,19)="Numéro de compte GC : ", dit de retourner l'ID du compte (le résultat de la fonction MID() ci-dessus) si C6 commence par "Numéro de compte GC : ", A5 (la cellule calculée ci-dessus) sinon - c'est ce qui fait le glissement vers le bas (dissociation) du numéro de compte à toutes les transactions en dessous.
Description du compte : Cellule B6 et en dessous
=IF(LEFT(C6,19)="Numéro de compte GC : ",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 ID 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 GC : "). LEN(C6) garantit que tout le texte restant jusqu'à la fin est retourné.
Sous-totaux
Voici les formules pour les cellules G1 et H1 :
=SUBTOTAL(9,G7:G50000)
=SUBTOTAL(9,H7: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 effectuons l'étape finale de filtrage pour ne conserver que les rangées de transaction (suivant).
Filtrer pour ne conserver que les rangées de transaction
Après avoir activé les filtres, vérifiez la colonne D (date) et nous voyons qu'il y a des dates en 2020, et des vides.
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 (décochez les vides).
Notez que les résultats de nos formules SUBTOTAL() dans les cellules G1 et H1 montrent que le total des transactions est équilibré (yay!)
Notre dernière étape consiste à 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 suivantes 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.
Enfin, dans le nouveau carnet de travail, assurez-vous de formater la colonne Date en Date au format AAAA-MM-JJ.
Et maintenant, nous avons un fichier magnifiquement formaté, prêt à être importé dans MindBridge !
Autre chose en tête ? Discutez avec nous ou soumettez une demande pour obtenir de l'aide supplémentaire.