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

Guides et conseils sur le formatage des données : Dégroupement simple de plusieurs colonnes, remplissage de numéros de compte vides (exemple courant dans QuickBooks).

  • Mise à jour

Sommaire

Apprenez à dégrouper un fichier de données en remplissant plusieurs colonnes pour produire un fichier pris en charge par MindBridge. C’est un problème courant dans de nombreuses exportations de données provenant de systèmes comptables comme QuickBooks. 

Cette méthode peut même être utilisée si toutes les colonnes n’ont pas de valeur sur la première ligne du groupe, et pour remplir des numéros de compte vides lorsque la description du compte est présente mais pas le numéro de compte. 

Remarque : Si possible, essayez d’abord de configurer une connexion directe ou demandez à votre client de se connecter pour tirer les données directement dans MindBridge, ou suivez ces instructions pour exporter à partir de QuickBooks Online ou QuickBooks Desktop afin d’éviter d’avoir à dégrouper les données. Ce guide est destiné aux situations dans lesquelles il est impossible de mettre en place une connexion directe ou d’obtenir une meilleure exportation.


Résolution

Considérez l’extrait suivant d’un fichier du grand livre de QuickBooks :

mceclip1.png

Les problèmes

  1. Ce fichier est regroupé par transaction; la Date, le Type de transaction, le Numéro et le Nom ne sont présents que sur la première ligne du groupe de transactions et doivent être déplacés vers le bas.
  2. Le numéro de compte n’est pas toujours présent (voir par exemple les lignes 8, 13, 17, 25), mais le compte l’est. Le compte peut être mis en correspondance au code du compte, mais le numéro de compte ne peut pas l’être, à moins que les espaces vides ne soient remplis.
  3. Il y a des lignes de sous-total de transaction inutiles (9, 14, 19, 26)

La solution (formatage nécessaire)

  1. Faites glisser la Date, le Type de transaction, le Numéro et le nom vers le bas jusqu’à chaque ligne de transaction
  2. Remplissez le champ vide Nº de compte avec la valeur dans Compte
  3. Exclure les lignes totales/sous-totales/blanches étrangères (9, 14, 19, 26) et les lignes étrangères (lignes 1-4) au-dessus de la ligne d’en-tête (ligne 5).

Nous mettrons les colonnes révisées à la fin du fichier.

  • La Date et le Type de transaction sont toujours présents sur la première rangée du groupe de transactions, mais le Numéro de transaction et le Nom sont parfois vides pour un groupe. Voir les 3e et 4e groupes de transactions qui commencent à partir des lignes 16 et 21.
  • Notre logique consistera à prendre la valeur de la colonne originale si nous sommes sur la première rangée de la transaction, ou la valeur précédente que nous avons trouvée si nous ne sommes pas sur la première rangée de la transaction.

Date de révision : Cellule L6 et cellules inférieures

mceclip3.png

=IF(LEN($B6)>0,IF(LEN(B6)>0,B6,""),L5)
  • LEN($B6)>0
    vérifie s’il y a une valeur dans la colonne B en testant si la longueur est supérieure à 0. Puisque, comme nous l’avons observé, la date est toujours présente sur la première rangée du groupe de transactions, cela nous sert à vérifier si nous sommes sur la première rangée de la transaction. Nous mettons $ devant le B pour faciliter la copie de la formule dans le reste des colonnes pour la faire glisser vers le bas (Type de transaction, Numéro, Nom), car nous vérifierons toujours la présence de Date pour vérifier si nous sommes sur la première rangée de la transaction.
  • IF(LEN(B6)>0,B6,"")
    retourne la date si elle est présente, « » (chaîne vide) si elle ne l’est pas. Cela peut être simplifié par un simple B6 pour la colonne Date, car la Date est toujours présente sur la première rangée du groupe de transactions, mais cette logique est utilisée pour pouvoir copier et coller les mêmes formules pour le Numéro et le Nom, qui, comme indiqué ci-dessus, ne sont pas toujours présents. Étant donné qu’Excel affichera 0 au lieu d’être vide lors de la référence d’une cellule vide, nous devons lui dire de retourner « » pour qu’elle affiche une cellule vide.

Remarque : Les formules ci-dessus vérifiant la présence de chaînes vides supposent que les cellules qui semblent vides le sont effectivement dans Excel. Si ces cellules vides contiennent en fait des espaces, des espaces insécables ou des données fantômes, remplacez toutes les occurrences de B6 par le texte suivant dans la fonction LEN() :

TRIM(CLEAN(SUBSTITUTE(B6,CHAR(160)," ")))
  • La fonction TRIM() supprime les espaces
  • CHAR(160) indique le caractère d’espace insécable, et la formule SUBSTITUTE() qui l’entoure supprime les caractères d’espace insécables (remarque : sur un Mac, utilisez CHAR(202) au lieu de CHAR(160)).

La formule finale serait donc :

=IF(LEN(TRIM(CLEAN(SUBSTITUTE($B6,CHAR(160)," "))))>0,IF(LEN(TRIM(CLEAN(SUBSTITUTE(B6,CHAR(160)," "))))>0,B6,""),L5)

 

Type de transaction révisée, Numéro, Nom

Copiez et collez et faites glisser vers le bas la formule trouvée pour la date révisée.

mceclip4.png

Les formules dans les cellules M6, N6 et O6 doivent être les suivantes :

=IF(LEN($B6)>0,IF(LEN(C6)>0,C6,""),M5)
=IF(LEN($B6)>0,IF(LEN(D6)>0,D6,""),N5)
=IF(LEN($B6)>0,IF(LEN(F6)>0,F6,""),O5)

N° de compte révisé

Notre logique pour remplir les espaces vides pour le Numéro de compte dans la colonne P sera de prendre le Numéro de compte original de la colonne H s’il est présent (la longueur est supérieure à 0), le Compte de la colonne I si ce n’est pas le cas (la longueur n’est pas supérieure à 0) :

mceclip5.png

=IF(LEN(H6)>0,H6,I6)

Filtrer pour conserver uniquement les rangées de transaction

Nous observons que chaque rangée de transaction dans le fichier original a un compte. Donc, avec les filtres activés, filtrez les blancs de la colonne I (Compte) :

mceclip6.png

Copier et coller des valeurs dans un nouveau classeur

Sélectionnez ensuite 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 B5, 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.


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.


Articles connexes

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