VBA ou Formules ?

Bonjour,

J'ai un document Excel qui fonctionne essentiellement avec des formules.

Il y a de multiples onglets avec plusieurs bases de données et des onglets servant à divers traitements avant de fournir un résultat.

Les formules sont assez nombreuses, font référence à d'autres formules qui font référence à d'autres formules...

Globalement ça marche bien, mais contrairement à ce que j'espérais, le fonctionnement du document n'est pas tellement automatique.

Ainsi, j'ai des formules qui fonctionnent mais qui affichent une information erronée. Il me suffit de copier-coller la formule à la même place (j’inscris donc exactement ce qui était déjà inscrit) et là le résultat est valide et permet ensuite au document de fonctionner correctement.

Je ne sais pas d'où ça provient, mais ce que je constate c'est que le document est plutôt lourd et demande de la ressource à mon ordinateur.

En connaisseurs d'Excel, vous seriez probablement choqués par la complexité des formules pour des tâches probablement simples si l'on utilise les macro.

Avant de tenter certaines choses, j'aimerais poser une question :

Dans Excel et dans le cadre de tâche identiques, l'utilisation de formules requiert-elle davantage de ressources que le VBA ? Assez pour que ça se ressente.

Si je passe par des macro pour appliquer systématiquement des formules à plusieurs colonnes de divers onglets, cela sera-t il plus rapide et moins gourmand en ressources ?

J'envisage d'appliquer les mêmes formules qu'aujourd'hui, mais en utilisant des macros. Où, si je trouve comment faire, procéder à des requêtes mieux élaborées sans passer par des formules.

Bonne journée !

bonjour

Il me suffit de copier-coller la formule à la même place (j’inscris donc exactement ce qui était déjà inscrit) et là le résultat est valide et permet ensuite au document de fonctionner correctement.

quand une formule ne fonctionne pas, avant de la copier/coller sur place, fais F9 pour voir si des fois le calcul est passé en manuel (dans les options d'Excel)

note : VBA apporte plus de questions que de réponses, ne pas l'utiliser

note 2 : quand on a trop de formules, c'est qu'on a mal organisé son fichier, ou qu'on ne connaît pas les TCD.

joins un extrait du fichier

à te relire

Bonsoir,

jmd a écrit :

note : VBA apporte plus de questions que de réponses, ne pas l'utiliser

mais il a oublié de terminer sa phrase : « (sauf en cas d'urgence). »

dhany

Bonjour,

Merci de vos éclaircissements,

note 2 : quand on a trop de formules, c'est qu'on a mal organisé son fichier, ou qu'on ne connaît pas les TCD.

joins un extrait du fichier

J'aimerais beaucoup que vous puissiez y jeter un œil, mais le document fait 21mo et son fonctionnement repose sur 2 bases de données qui sont déjà très lourdes, les réduire conduirait à des erreurs.

Pour essayer de faire simple :

J'ai 2 formulaires

• onglet 1 : formulaire "statique" qui contient un code "parent". [+ des infos en rapport avec un habitat, un lieu, une étude, etc.]
• onglet 2 : formulaire contenant des listes de codes espèces + un code "enfant"

=SUBSTITUE($K2;"  ";" ")

et

=(SUPPRESPACE($C2))

Via des formules, je fait correspondre les codes "parent" avec les codes "enfant" de manière à ce que les formulaires "statiques" [habitat, lieu, étude, etc.] corresponde avec les informations qui y sont associées [codes espèces, effectifs, etc.].

J'utilise donc, entre autres, la formule

=RECHERCHEV

à 6 reprises

J'ai ensuite 2 onglets avec 2 base de données de 22 000 et 57 000 lignes.

Des codes et leurs correspondances

Puis, j'utilise un nouvel onglet (le 6ème) pour effectuer des recherches. C'est là que prend tout l'intérêt de mon document.

Les 2 formulaires de départ peuvent changer, augmenter en taille, être modifiés, etc. mais il ne changent jamais de format.

Les données sont ensuite compilées dans le 3ème onglet.

Ce 6ème onglet fonctionne avec le 9ème onglet (on ne tient pas compte du 7ème et 8ème)

Lorsque j’inscris dans un champ de recherche (zone de texte - controle activeX) un numéro d'étude, j'ai plusieurs formules qui vont alors rechercher toutes les lignes qui contiennent ce numéro d'étude.

Onglet 6 :

Colonne A :

=ET(Correspondances!$A$2<>"";ESTNUM(CHERCHE(Correspondances!$A$2;'Centralisation des données'!$B:$B;1)))

Recherche le code inscrit dans l'onglet 9 "correspondance" dans l'onglet 3 "centralisation des données" et indique VRAI si c'est un numéro.

Colonne B :

=SI($A2;1;0)

Pour convertir "Vrai" en "1" (Car je ne sais pas utiliser les VRAI dans mes formules ; ce n'est ni du texte, ni du numéro)

Colonne C :

SI($B2=1;'Centralisation des données'!$B2;"")

Si 1 alors faire apparaître les codes situés dans les mêmes lignes, dans l'onglet 3 "centralisation des données" (puisque les lignes ne changent pas de place).

Ensuite via 10 formules

RECHERCHEV

; je vais chercher toutes les données qui correspondent au code inscrit (les autres codes n'apparaissant pas, il ne peut être recherché que les bonnes infos).

Enfin, dans l'onglet 9 (toujours) je demande à copier - coller les valeurs trouvées de l'onglet 6 (seulement les valeurs) et je les tri de A à Z.

Pour chaque code espèce, Excel me donne une correspondance (via la BDD de l'onglet 4 : "Database complete") et m'indique des infos complémentaires dans une colonne pour identifier les erreurs :

Correspondance des codes espèce :

=SI(NB.SI('Database complete'!AP$2:AQ$22124;B15)>1;"choisir ->";SIERREUR(RECHERCHEV(B15;'Database complete'!AP$2:AQ$22124;2;FAUX);$C:$C))

Si, pour ce qui est inscrit en colonne B il existe 2 valeurs identiques dans la BDD (onglet 4) indiquer "choisir ->", si la cellule en colonne B est une erreur afficher ce qui est inscrit en colonne C, si non, afficher la correspondance exacte en recherchant dans la BDD (onglet 4)

Infos sur les codes :

SI($W2="Cellule vide";"Cellule vide";SI($X2="Erreur de saisie";"Erreur de saisie"; SI($Y2="Code valide";"Code valide";SI($Y2="Synonyme";"Synonyme";Y2))))

[Cette formule fait référence à 3 autres formules :

=SIERREUR($B2;"Cellule vide")

=SIERREUR(INDEX('Database synonymes complete'!AQ:AR;EQUIV($B2;'Database synonymes complete'!AR:AR;0);1);SIERREUR(INDEX('Database complete'!$AP$2:$AP$124998;EQUIV($B2;'Database complete'!$AP$2:$AP$124998;0);1);"Erreur de saisie"))

=SI($W2="Cellule vide";"Cellule vide";SI($X2="Erreur de saisie";"Erreur de saisie";SIERREUR(RECHERCHEV($B2;'Database synonymes complete'!$AR:$AS;2;FAUX);RECHERCHEV($B2;'Database complete'!$AP:$AS;4;FAUX))))

car je ne sais pas faire fonctionner tout ça dans une seule formule]

Lorsqu'un code possède plusieurs correspondances il affiche "choisir ->" et je demande une liste déroulante :

=DECALER('Database complete'!$AQ$1; EQUIV(Correspondances!$B2;'Database complete'!$AP:$AP;0)-1; ; EQUIV(Correspondances!$B2;'Database complete'!$AP:$AP;1) - EQUIV(Correspondances!$B2;'Database complete'!$AP:$AP;0) +1)

Lorsqu'un code ne donne pas de résultat, il peut s'agir d'un synonyme, je lui demande donc de me proposer le code valide :

=DECALER('Database synonymes complete'!$AQ:$AQ;EQUIV($B2;'Database synonymes complete'!$AR:$AR;0)-1;;EQUIV($B2;'Database synonymes complete'!$AR:$AR;0)-EQUIV($B2;'Database synonymes complete'!$AR:$AR;0)+1)

Voilà tout, il y a encore quelques petites formules, un petit peu de VBA pour nettoyer les feuilles ; mais vous avez globalement toute l'explication, que je ne parviendrais peut-être pas à faire plus simple sans enlever des infos...

Globalement, vous savez comment mon document fonctionne ; j'aimerais vraiment qu'un connaisseur puisse y jeter un oeil, me dire si (et c'est sûr) il y a moyen de faire plus simple, plus rapide, moins lourd. Ou même qu'il me dise si ce n'est tout simplement pas Excel qu'il faut utiliser pour ce genre d'opérations.

Bonne journée !

re

vu la complexité et le nombre de lignes et les connexions :

mon avis : passe sous un SGBD (Access ou OOO gratuit ou autre)

note : si tu restes avec Excel, ne pas faire de "formulaires de recherche", mais utiliser des segments

à te relire

Ah je vois, merci.

J'envisageais effectivement d'utiliser Access, mais ne m'en suis encore jamais servis. Ce sera l'occasion d'apprendre à utiliser un nouvel outil.

note : si tu restes avec Excel, ne pas faire de "formulaires de recherche", mais utiliser des segments

L'utilisation des segments demande à réaliser des opérations manuelles sur Excel non ?

En l'occurrence, ce document est destiné à être utilisé par différentes personnes qui ne doivent pas modifier le document de base, sinon on risque d'avoir des erreurs. Le mieux étant qu'ils n'aient qu'à inscrire le numéro d'une étude et ils peuvent exporter toutes les infos associées en un simple clic (au delà ça marcherait probablement, mais on irait au devant d'erreurs).

Bonne journée !

re

pour faire un soft destiné à des personnes totalement novices en informatique, Excel n'est pas la solution.

Access sera mille fois mieux.

mébon, donner les rudiments nécessaires pour que les gens ne fassent pas de bourdes sur Excel est assez rapide

prends le soin de verrouiller tout ce qui doit l'être.

note : "exporter" ?

à quoi sert de filtrer avant d'exporter ?

en général on exporte tout, et le logiciel de destination fera les tris et filtres. Non ?

à te relire

Le Drosophile a écrit :

Si je passe par des macro pour appliquer systématiquement des formules à plusieurs colonnes de divers onglets, cela sera-t il plus rapide et moins gourmand en ressources ?

J'envisage d'appliquer les mêmes formules qu'aujourd'hui, mais en utilisant des macros. Où, si je trouve comment faire, procéder à des requêtes mieux élaborées sans passer par des formules.

Bonjour

Pour moi, la première règle en VBA c'est ... savoir s'en passer ! Il y a des outils pour cela (TCD, segments, filtres)

ben vu l'nombre de codes VBA qu'y'a sur ce forum, y'en a beaucoup qui arrivent pas à s'en passer, hein ?

comme dirait Coluche : « circulez, y'a rien à voir ! »

dhany

Bonjour,

Merci beaucoup pour vos réponses. Je vais effectivement passer par Access, voire par un portail internet où seront stockés les formulaires et où il suffira d'inscrire un numéro pour extraire les données souhaitées.

note : "exporter" ?

à quoi sert de filtrer avant d'exporter ?

en général on exporte tout, et le logiciel de destination fera les tris et filtres. Non ?

J'ai mal utilisé le mot, quand je disais exporter, je voulais dire que le logiciel va créer un tableau propre, trié qu'il suffit de copier pour l'utiliser dans une présentation (PP, Word, autres) ou autre.

Bonne fin de journée !

re

pour créer un tableau propre, utilise un TCD dans ton Excel

et dans Word ou PP, "insérer objet".

note : un fichier Excel de 21 Mo, c'est déraisonnable

suggestion en restant sur Excel : menu Données/Obtenir et rechercher tes bases. Ne pas "charger", mais "ajouter au modèle de données". Ensuite tu peux faire tous les traitements dont tu as besoin

S'il faut faire de la saisie (de données autres que celles des bases), crée un fichier de saisie. Et à nouveau Données / Obtenir pour les rapatrier dans le fichier d'analyse.

note 2 : un peu pareil, sauf que tu fais tes analyses dans Power BI Desktop (gratuit). A essayer d'urgence.

amitiés à tous

dhany a écrit :

ben vu l'nombre de codes VBA qu'y'a sur ce forum, y'en a beaucoup qui arrivent pas à s'en passer, hein ?

comme dirait Coluche : « circulez, y'a rien à voir ! »

dhany

salut dhany

oui, de nombreuses personnes croient de bonne foi que VBA est la méthode qui les sauvera.

malheureusement, c'est un peu comme se promener dans la savane accompagné d'un lion pour éloigner les hyènes. Certes les hyènes se tiendront à l'écart, mais le lion va te bouffer

il vaut mieux apprendre

apprenons Excel et ses menus, surtout Accueil. Oui, oui, on méconnaît ce premier menu

et le menu Données, si important. Rappelons que 99,99% des fichiers Excel s'occupent de données.

VBA n'est plus nécessaire depuis 10 ans.

amitiés excelliennes à toi

salut jmd,

tu a écrit :

Certes les hyènes se tiendront à l'écart, mais le lion va te bouffer

mais non, t'inquiètes pas ! j'ai aucun risque de m'faire bouffer par le lion qui m'accompagne, car le lion, c'est lui :

si tu vois mal la photo, c'est pas pa'c'qu'elle est un peu floue, c'est pa'c'que tu dois pas loucher comme Clarence !

tu a écrit :

VBA n'est plus nécessaire depuis 10 ans.

mais paradoxalement, même actuellement, ça fait toujours autant plaisir de s'en servir !

surtout toutes les fois où y'a pas d'autre solution, hein ?

et bizarrement, quand c'est l'patron (ou un maître de stage) qui veut absolument une solution VBA, ça marche jamais quand on lui répond : « mais jmd, lui, il veut pas d'solution VBA ! »

solution : faire démissionner l'patron Haroun El Poussah pour mettre à la place jmd-Iznogoud

dhany