Mise à jour de donnée et clé de répartition

[lesurjul]

Bonjour,

Je réalise actuellement un calcul de coût à partir d'une importation du logiciel SAGE.
J'ai une petite question pratique.

Dans le dossier en pièce jointe, vous allez trouver :

- dans une premiere feuille une balance comptable importée automatiquement par macro (tableau violet) avec en colonne A l'extraction de la racine des comptes (par la formule =GAUCHE) ainsi que dans la dernière colonne l'affectation d'une catégorie par rapport à cette racine (par la formule =RECHERCHEV).

-ce processus se fait grâce à des clés de répartitions affeté par liste déroulanteen feuille 2 pour me permettre finallement de dégager un TCD des valeurs de chaque catégories choisies.

Cependant si j'importe une nouvelle balance (en feuille 1) avec d'autres racines de comptes non déjà affectées (feuille 2)je ne sais pas comment faire pour que la liste des racines de comptes sans doublon en feuille 2 s'actualise automatiquement ?

J'aimerais savoir si c'est possible d'extraire seulement les nouveaux comptes pour leur donner juste à eux une catégorie (peut être par MSGBOX si possible ?)... sans devoir ré-affecter une catégorie pour tout les comptes, même les anciens à chaque nouvelle importation.

Merci de votre aide.

Julien

 

[lesurjul]

Re : Mise à jour de donnée et clé de répartition

Up !

Je cherche depuis tout à l'heure une solution, peut être réside t'elle dans le fait de donner une valeur, ici "pas de clé affectation pour cette racine":
=SI(ESTERREUR(RECHERCHEV($B$3:$B$111;SAGE.CléRépart;2;FAUX));SI(C6="";"aucun compte";"pas de clé affectation pour cette racine");RECHERCHEV($B$3:$B$111;SAGE.CléRépart;2;FAUX))

Une fois que ce texte s'affiche, il me faudrait alors pouvoir isoler tout les comptes qui affiche cette valeur "pas de clé affectation pour cette racine" et permettre leur modification avec une liste déroulante non ?

Merci

 

[lesurjul]

Re : Mise à jour de donnée et clé de répartition

En résumé comment faire pour que :
Si une/des cellules de la colonne I = "pas de clé d'affectation pour cette racine"
Comment copier les racines de comptes liés dans la feuille 2 pour leur donner une affectation grâce à la liste déroulante ?

 

[lesurjul]

Re : Mise à jour de donnée et clé de répartition

Personne pour m'aider ?

 

[Misange]

Re : Mise à jour de donnée et clé de répartition

Bonjour,
résumons : ta questions est-elle bien juste celle-ci :
"comment faire pour que la liste des racines de comptes sans doublon en feuille 2 s'actualise automatiquement ?"
Tu peux récupérer à partir de la feuille d'importation la liste sans doublon par formule :
tu commences par transformer en vrai tableau les données importées en feuille 1 (tu peux ajouter cela dans ta macro, utilise l'enregistreur de macro pour récupérer le code)
ceci permet d'avoir des noms définis automatiquement dynamiques.
ensuite la formule matricielle
=INDEX(Tableau1[Racine comptes];EQUIV(0;NB.SI($K$3:K3;Tableau1[Racine comptes]);0))

entrée en K3 de la feuille 2, validée par ctrl+maj+entrée PUIS tirée vers le bas t'établit la liste des éléments uniques de la première colonne racine de ton tableau.

avec un index+equiv, tu mets en face la liste des clés analytiques
(là encore j'ai transformé en vrai tableau celui de correspondance. C'est pas juste pour faire joli, c'est surtout super pratique pour faire des formules dynamiques sans passer 3 plombes à définir les noms avec les formules decaler (que j'adore mais quand même il y a des limites )

 

[lesurjul]

Re : Mise à jour de donnée et clé de répartition

Bonjour misange,

Oui en gros c'est bien ça la question, sauf que je voudrais que lorsque un compte n'a pas de clé définie en feuille 2, (il s'inscrit alors "pas de clé d'affectation pour cette racine" dans la colonne I de la feuille. [Tu peux essayer en rajoutant 81000000 par exemple en C106 dans la feuille 1.
J'aimerais que dans ce cas, la racine 81000 "s'actualise automatiquement en feuille 2 et qu'on puisse choisir par liste déroulante la catégorie (cl") à affecter..

L'idée de ta formule matricielle INDEX+EQUIV est intéressante mais elle ne s'actualise pas automatiquement chez moi, comme tu peux le voir si tu fait la manipulation que je t'ai indiqué dans le premier paragraphe, l'érreur NA reste il faut tirer vers le bas, c'est ce qui est dommage ...

 

[lesurjul]

Re : Mise à jour de donnée et clé de répartition

Autant pour moi ta formule matricielle fonctionne parfaitement et m'enleve les doublons, c'est parfait. L'erreur NA disparait je sais pas pourquoi ça ne fonctionnait pas à la premiere ouverture ?

2 petites question :
- Si j'importe une balance (feuille 1) plus longue, le tableau va t'il s'agrandir tout seul ?
- Est-il possible, peut être par maccro ? de signaler à l'utilisateur (par un MSG BOX) que les nouveaux comptes n'ayant pas de clé d'affectation doivent être définies en feuille 2 et les mettre en couleur ? En gros si la colonne de clé est vide pour la nouvelle racine, le signaler et demander a l'utilisateur de saisir une clé (en utilisant la liste déroulante comme dans le tableau que j'avais fait à côté)

Merci de ton aide

 

[Misange]

Re : Mise à jour de donnée et clé de répartition

Non le tableau des valeurs sans doublons ne s'agrandira pas tout seul. Tu peux le tirer vers le bas sur un nombre de ligne assez grand (pas trop quand même : ce sont des formules matricielles et il faut toujours limiter les plages sur lesquelles on les utilise car ça finit par bouffer de la ressourece). Tu as une idée du nombre maxi de comptes que tu peux avoir ? Si cette liste devait s'agrandir de façon démesurée, tu pourrais la générer par macro.

Dans la table racine/clé, mets une mefc sur la clé :
sélection de la colonne du tableau à la souris/ MEFC avec la formule :
=ET(ESTNA(L4);NON(ESTNA(K4)))
et tu mets ça en rouge. Ca te surligne les racines n'ayant pas de clé.

ensuite tu peux dans ta macro d'importation filtrer ce tableau sur la couleur rouge et ajouter un msgbox demandant de vérifier si il y a des cellules en rouge dans ce tableau et d'y mettre la valeur de la clé. Dès que celle-ci sera rentrée, la couleur disparaitra.

 

[lesurjul]

Re : Mise à jour de donnée et clé de répartition

Non le but est qu'il ne s'agradira pas énormément. Je l'ai donc tiré sur 20 lignes en dessous.¨
Pour la MeFC pas de gros souci, à quoi sert le NON() dans la formule ?

"tu peux dans ta macro d'importation filtrer ce tableau sur la couleur rouge et ajouter un msgbox demandant de vérifier si il y a des cellules en rouge dans ce tableau et d'y mettre la valeur de la clé. Dès que celle-ci sera rentrée, la couleur disparaitra. "
C'est exactement ce qu'il faudrait, mais je ne sais pas du tout écrire de tels langage en VBA, peut être pourraît tu me donner la macro qui ferait ça en VBA et je la collerai avec le End Sub() dans ma formule d'importation ?

 

[lesurjul]

Re : Mise à jour de donnée et clé de répartition

Alors là c'est fort, j'ai mis la MeFC mais ça me met en rouge, malgrès le =ET(ESTNA(L8);NON(ESTNA(K8))) qui revoie de manière correct VRAI ou FAUX des cellules en rouge complètement différente ... Je vais continuer d'essayer.

 

[Misange]

Re : Mise à jour de donnée et clé de répartition

Non() inverse la proposition :
estna(truc) renvoie vrai si truc est n/a
non(estna(truc)) renvoie faux

Utilise l'enregistreur de macro, là j'ai pas le temps.
pour le msgbox c'est
msgbox("le message que tu veux")
à mettre en fin de macro.

 

[lesurjul]

Re : Mise à jour de donnée et clé de répartition

C'est bien ce que je me disais pour NON()

J'essaye de voir depuis tout à l'heure pour les macros mais hélas c'est au dessus de mes moyens, nref' je vais pas te faire perdre encore un peu plus ton temps..

J'ai essayé de remettre ce que tu m'as dit dans mon fichier et j'ai 2 soucis, quand tu auras 5min peux tu me dire :
- Pourquoi la cellule H71 de la feuille 2 est vide alors qu'elle devrait être NA normalement ?!
- Pourquoi la MeFC ne fonctionne pas et me met en rouge les 3 première ligne du tableau et pas celle ou =ET(ESTNA(L69);NON(ESTNA(K69))) est VRAI ?!

Merci encore

 

[lesurjul]

Re : Mise à jour de donnée et clé de répartition

Et troisieme qestion, tu me conseils de faire des tableaux pour pouvoir rendre les forumles dynamiques sauf qu'une fois en tableau je ne peux plus actualiser mes importations qui sont des données externes puisqu'effectivement il y a maintenant un tableau qui l'empêche .. comment faire alors ?

 

[Misange]

Re : Mise à jour de donnée et clé de répartition

Ah ça c'est une bonne question. Tu as raison c'est une des limites des tableaux (en tous cas pour l'instant, espérons que ça change)
J'avais oublié ce point, n'ayant rien pour le tester.
Dans la page d'importation dans ce cas, transforme le tableau en plage et définis un nom dynamique (par exemple racine) pour les racines de compte
si ils sont en colonne A avec l'étiquette en A1
=decaler($A$1;1;0;nbval($A:$A)-1)
remplace alors dans la formule du tableau de la feuille 2 la référence au tableau de la feuille importation colonne racine des comptes par "racine" (sans les guillemets)

Pour le reste je regarde tout à l'heure ou ce soir.

 

[lesurjul]

Re : Mise à jour de donnée et clé de répartition

Si tu veux vraiment le classeur dans son ensemble je pex l'envoyer sur ton mail..

Je comprends à peu près pour le nom dynamique même si je ne sais comment faire, pourquoi ne pas définire carrément une plage très large ?

Par contre je n'ai jamais utiliser la fonction DECALER() et ne sais même pas à quoi elle sert ? à renommer une plage dans une formule ?

Merci beaucoup de regarder alors