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

 

[Misange]

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 ?!

Tu parles de la cellule K71 plutot non ?
C'est parce que dans ta feuille d'importation, je ne sais pas comment tu fais cette importation mais tu as des lignes "aucun compte" pour lesquelles la colonne racine des comptes contient une valeur vide. Si tu supprimes ces lignes, cette valeur vide disparait aussi dans la feuille 2

- 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

parce que tu n'as pas procédé correctement pour mettre en place ta mefc...
Sur le classeur que tu as joint, clique dans la cellule L5 et affiche les conditions de la mefc :
on lit
=ET(ESTNA(L69);NON(ESTNA(K69)))
alors qu'il devrait y avoir
=ET(ESTNA(L5);NON(ESTNA(K5)))

et là ça fonctionne.

Je reprends donc :
1) tu cliques dans L5
2) ensuite seulement tu sélectionnes avec la souris toute la colonne en faisant un clic dans le haut de la colonne du tableau (un des auters gros avantages des tableaux, tu n'as pas besoin de défiler jusqu'en bas de la page)
3) ensuite tu ouvres la boite de dialogue mefc et dans formule tu mets
=ET(ESTNA(L5);NON(ESTNA(K5)))
et tu valides.
Tu dois vérifier alors dans mefc/gérer les règles que la mefc s'applique bien à toute la colonne de la table, soit $L$5:$L$107

 

[Misange]

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

Comme tu as l'air de vouloir faire des choses qui vont te simplifier la vie avec excel, tu ne couperas pas à un peu de formation pour progresser, sinon très vite ça va devenir :
c'est super mais est-ce qu'on pourrait pas aussi faire ceci, puis ceci puis cela
j'ai mis en ligne plein de tutos et d'exemples sur excelabo, notamment celui-ci sur decaler, une fonction vraiment indispensable à comprendre.
Ce lien n'existe plus

Tu travailles avec excel 2010. Le nombre de lignes est considérable. Si tu cèdes à la tentation de travailler sur des plages très très larges pour être sur que tes données y tiennent, tu ralentis considérablement le temps de calcul. Autant prendre de suite les bonnes habitudes et ne travailler qu'avec les plus petites plages possibles.
Les tableaux sont de ce oint de vue géniaux, ils sont automatiquement dynamiques et tu n'as pas besoin de définir tes plages dynamiquement car cela se fait tout seul. Pour la page d'importation de données de fait, ne pouvant utiliser un tableau tu dois définir ce nom avec la fonction decaler.

tu as aussi des tutos sur le Ba-BA du VBA qui te seront je pense utiles si tu te lances un peu dans ce domaine.

Dans le classeur joint je t'ai corrigé la mefc
modifié le tableau de la feuille d'importation pour le transformer en plage et définir le nom dynamique qui est réutilisé dans le tableau de la feuille 2 (racine_comptes).
maintenant, à toi d'étudier tout ça et de comprendre comment ça marche. Je suis convaincue que tu trouveras très vite des tas d'autres situations dans lesquelles un classeur excel bien conçu te fera gagner énormément de temps.

 

[lesurjul]

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

Tu parles de la cellule K71 plutot non ?
C'est parce que dans ta feuille d'importation, je ne sais pas comment tu fais cette importation mais tu as des lignes "aucun compte" pour lesquelles la colonne racine des comptes contient une valeur vide. Si tu supprimes ces lignes, cette valeur vide disparait aussi dans la feuille 2

C'était pour essayer, comme je ne peux pas faire de tableaux sur les données importées, de prévoir au cas ou il y est une importation plus grandes en nombre de lignes .. mais du coup j'ai cette ligne de valeur vide qui apparait dans mon tableau en feuille 2 et qui fausse le ESTNA()

parce que tu n'as pas procédé correctement pour mettre en place ta mefc...
Sur le classeur que tu as joint, clique dans la cellule L5 et affiche les conditions de la mefc :
on lit
=ET(ESTNA(L69);NON(ESTNA(K69)))
alors qu'il devrait y avoir
=ET(ESTNA(L5);NON(ESTNA(K5)))

et là ça fonctionne.

Merci de ton explication, j'avais une erreur car je travail sur le fichier complet et la feuille n'est pas tout a fait organisée de la même manière, c'est pour cela, je suis arrivé à "ruser" en rajoutant au tableau en feuille 2 une colonne nommée Besoin affectation qui renvoi VRAI ou FAUX selon la fonction ESTNA, du coup j'avait mit une meFC là dessus et ça fonctionnait plutôt bien, ça me permettra d'expliquer le jour de ma soutenance

. Autant prendre de suite les bonnes habitudes et ne travailler qu'avec les plus petites plages possibles.

Je suis pour mais mon mettre de stage qui est expert comptable m'a dit que s'il réaliser ce classeur il aurait fait en prenant par exemple 2500 lignes, l'activité ne pouvant être guerre supérieure ... la fonction décaler serait la solution ? J'ail rapîdement lu ton tuto sur la fonction DECALER() qui est très interessant, je vais approfondir un peu plus là, "Utilisation de DECALER pour définir des plages de façon dynamique : définir la plage qui va de A1 jusqu'à la dernière cellule non vide de la colonne A." me semble être pertinent.. je vais m'y coller

Je regarde ta pièce jointe /D

Merci beaucoup encore

 

[lesurjul]

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

Je viens de bien approfondirla fonction DECALER() qui dans l'utilisation que tu proposes me serait utile, par contre je me demande maintenant si je dois nommer toutes les colonnes avec cette fonction pour que le nombre de ligne soit dynamique ?ou si raisonner sur une celle colonne dynamique est suffisant ? merci

 

[Misange]

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

C'était pour essayer, comme je ne peux pas faire de tableaux sur les données importées, de prévoir au cas ou il y est une importation plus grandes en nombre de lignes .. mais du coup j'ai cette ligne de valeur vide qui apparait dans mon tableau en feuille 2 et qui fausse le ESTNA()

il ne fausse rien du tout ! tu as juste une valeur vide en plus dans le tableau de répartition de la clé c'est tout.

Merci de ton explication, j'avais une erreur car je travail sur le fichier complet et la feuille n'est pas tout a fait organisée de la même manière, c'est pour cela, je suis arrivé à "ruser" en rajoutant au tableau en feuille 2 une colonne nommée Besoin affectation qui renvoi VRAI ou FAUX selon la fonction ESTNA, du coup j'avait mit une meFC là dessus et ça fonctionnait plutôt bien, ça me permettra d'expliquer le jour de ma soutenance

Je trouve que cette colonne est moche personellement et c'est bien plus démonstratif de supprimer une valeur dans le tableau de départ pour montrer que la couleur s'allume tout de suite et s'éteint quand on rentre l'information.

Je suis pour mais mon mettre

Aie !

de stage qui est expert comptable m'a dit que s'il réaliser ce classeur il aurait fait en prenant par exemple 2500 lignes, l'activité ne pouvant être guerre supérieure ...

Ca montre juste que ton maître de stage n'est pas top sur excel et que tu peux lui apprendre cette façon de faire.

la fonction décaler serait la solution ? J'ail rapîdement lu ton tuto sur la fonction DECALER() qui est très interessant, je vais approfondir un peu plus là, "Utilisation de DECALER pour définir des plages de façon dynamique : définir la plage qui va de A1 jusqu'à la dernière cellule non vide de la colonne A." me semble être pertinent.. je vais m'y coller

Je regarde ta pièce jointe /D

tu aurais pu commencer par là avant de répondre....

Oui decaler est la solution pour créer une plage dynamique quand on ne peut pas passer par un tableau créé avec la fonction "mettre sous forme de tableau". Tu n'as besoin de rendre dynamique dans le cas présent que la colonne contenant les infos dont tu as besoin sur la feuille 2, en l'occurrence la colonne racin_comptes. MAis si tu avais ouvert le classeur avant de répondre tu aurais vu cela dedans.

 

[lesurjul]

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

Tu n'as besoin de rendre dynamique dans le cas présent que la colonne contenant les infos dont tu as besoin sur la feuille 2

Désolé pour l'orthographe, j'essai de répondre rapidement

Sauf que dans le TCD en bas de la feuille 2 j'utilise toute la plage d'importation de la feuille 1 en source du TCD donc puis-je nommer tout mon "tableau" (qui ne peut en être un ^^) avec la fonction DECALER ? ça reglerait ainsi le probleme d'ajout de ligne pour les nouvelles importations ?

 

[Misange]

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

Oui bien sur tu peux nommer par exemple
bdd
=DECALER('Importation SAGE'!$B$1;;;NBVAL('Importation SAGE'!$C:$C);NBVAL('Importation SAGE'!$1:$1))

en mettant le nombre de valeurs de la colonne C comme 3° paramètres, tu évites de prendre en compte les lignes dont le "racine de compte" est vide. Tu peux du reste modifier le nom défini racine_comptes avec la même astuce ce qui règlera le pb de tes cellules vides dans la feuille 2

=DECALER('Importation SAGE'!$B$1;1;;NBVAL('Importation SAGE'!$C:$C)-1)

Mais tu aurais pu trouver ça tout seul avec le tuto

 

[lesurjul]

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

=DECALER('Importation SAGE'!$B$1;;;NBVAL('Importation SAGE'!$C:$C);NBVAL('Importation SAGE'!$1:$1))

Je comprends tout à fait pourquoi tu mets la ligne 1 en 5eme paramètre mais pourquoi en hauteur (4eme parametre) tu t'arrêtes à la colonne C, es-ce possible d'aller jusqu'à la colonne I pour mon TCD en feuille 2 ? Un TCD peut-il avoir comme source une fonction DECALER() ? =DECALER('Importation SAGE'!$B$1;;;NBVAL('Importation SAGE'!$I:$I);NBVAL('Importation SAGE'!$1:$1)) ?
Ne faut-il pas plutôt modifier le 1er parametre ? (la référence)

J'ai encoire du mal à bien assimiler DECALER() je l'ai jamais utilisé jusque là je ne la connaissait même pas, c'est bien le NBVAL() qui permet de faire que le nom est dynamique au final ?

 

[Misange]

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

As tu regardé quelle était la plage sélectionnée ?
Tu vas dans le gestionnaire de nom, tu cliques dans la formule en bas et tu as un petit pointillé qui entoure les données sélectionnées...
Lis le tuto sur decaler que j'ai pris la peine d'écrire en détails avant de me demander de te réexpliquer ici tout ce qui est déjà dedans et expliqué à fond...

 

[lesurjul]

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

Oui j'ai regardé, j'ai bien compris que dans le fichier que tu as mis en piece jointe c'est le colonne des Racines (B) qui est ciblée, je te demandais juste si le TCD pouvait prendre sa source à partir d'une fonction DECALER() nommée.

J'ai essayé avec la colonne ça fonctionne, je ne vois pas pourquoi ça marcherait pas avec plusieurs colonnes, et c'est là que je te demandais ce qu'il fallait changer dans la formule pour prendre en compte de manière synamique le tableau complet ? la référence ou la longueur par exemple ?

 

[Misange]

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

JE T'AI REPONDU... sur ce point au post #22
c'est toi qui es en stage ou c'est moi ?

 

[lesurjul]

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

Je crois qu'il y a un malentendu, ou alors c'est moi qui m'exprime mal...
J'ai déjà mis en place la formule :

Tu peux du reste modifier le nom défini racine_comptes avec la même astuce ce qui règlera le pb de tes cellules vides dans la feuille 2

=DECALER('Importation SAGE'!$B$1;1;;NBVAL('Importation SAGE'!$C:$C)-1)

pour régler le de cellules vides dans la feuille 2


La source de données du TCD en feuille 2 est toujours SAGE.DonneeTcd --> si tu vas voir, tu verras que c'est une plage large en terme de ligne ce que tu ne me conseilles pas.
Je suis persuadé qu'il est possible de placer ici un DECALER() POUR TOUT LE TABLEAU de la colonne B à la I de la feuille 1, c'est de ça que je parlais. Si tu avais compris ça, je vais chercher tout seul ne t'en fait pas.

Merci

 

[Misange]

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

J'ai créé le nom défini, à toi de l'utiliser comme source de données du tcd... tu écris = bdd c'est tout.

 

[Misange]

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

Oui bien sur tu peux nommer par exemple
bdd
=DECALER('Importation SAGE'!$B$1;;;NBVAL('Importation SAGE'!$C:$C);NBVAL('Importation SAGE'!$1:$1))

en mettant le nombre de valeurs de la colonne C comme 3° paramètres, tu évites de prendre en compte les lignes dont le "racine de compte" est vide.

tu as choisi de citer la deuxième partie de mon message seulement, je me demande pourquoi...

 

[lesurjul]

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

Oh ! autant pour moi j'avais vraiment pas vu que tu avais créer le nom bdd en mettant là formule dedant .. même apparement ça ne veut pas la prendre en compte : "La référence à la source de données n'est pas valide", que je mette =bbd ou =DECALER('Importation SAGE'!$B$1;;;NBVAL('Importation SAGE'!$C:$C);NBVAL('Importation SAGE'!$1:$1))