Calcul en VBA puis écriture dans une cellule (puis plage de celulle)
Bonjour à tous,
Je m'inscris aujourd'hui car j'arrive au bout du forum sans trouver la réponse et surtout il me manque certainement des connaissances de VBA.
J'ai un tableau (ci-joint) avec 532 lignes et 39 colonnes. Dans chacune des cellules (20 748) j'ai une formule qui fait appelle à une grande liste dans les feuilles annexes.
Mon fichier original fonctionne bien mais j'ai du désactiver les calculs automatiques et réaliser un bouton de mise à jour quand on change la base de donnée. Le calcul reste long (10 minutes) environ mais bon tout fonctionne très bien.
Mon idée est d'améliorer le temps de mise à jour en ne faisant pas calculer par Excel mais en utilisant une macro qui calcul la valeur de chaque cellule et qui la copie dans la bonne cellule.
Question 1 : Pensez-vous que ça ira plus vite ?
Question 2 : Pour ma colonne "Poste en cours", chaque cellule de la colonne doit effectuer ce calcul.
=SOMME.SI.ENS((DECALER(BD_OE_encours!$U$1;0;0;NBVAL(Nombre_de_postes_total_OE_en_cours)-1));(DECALER(BD_OE_encours!$O$1;0;0;NBVAL(ROME_OE_encours)-1));"*"&[@ROME]&"*";(DECALER(BD_OE_encours!$N$1;0;0;NBVAL(Etat_offre_OE_encours)-1));"*"&"COURS"&"*")
Pouvez-vous m'aider à traduite ça en VBA sachant que je ne fais que bricoler des macros mais que là ca nécessite des boucles et des connaissances que je n'ai pas. Mais je peux apprendre vite :)
Merci infiniment pour l'aide,
Damien
Bonjour,
Il y a 2 façons de résoudre des calculs chronophages :
- faire un TCD
- ou calculer par macro en chargeant les données dans des tableaux (array) - dans un exemple nous sommes passés de plusieurs jours à quelques secondes !
Difficile de répondre à la question 2 n'ayant pas l'onglet BD_OE_encours
Merci Steelson de prendre le temps de regarder.
Ci-joint le fichier avec l'onglet BD_OE_encours , j'ai modifié les données ne servant pas dans l'exemple pour éviter des données personnelles.
Cas du TCD : Veux tu dire que je devrais réaliser un TCD intermédiaire dans une autre feuille qui se calcule beaucoup plus rapidement et reprendre les valeurs de ce TCD dans les celulles de la feuille TB_diag ?
Cas des macros pour tableau "array" : Je ne connais pas du tout mais si le tableau peut se calculer en quelques minutes mais avec beaucoup plus de données alors je suis preneur de cette solution car potentiellement je peux avoir une BD de 70 000 lignes à traiter de la même manière que l'exemple des offres en cours.
Merci beaucoup pour les éclairages et sources éventuelles à regarder pour comprendre,
Damien
J'ai regardé le principe des tableaux et j'ai réussi à remplir via le bouton de macro dans chaque cellule. Je suis assez fier de moi.
Par contre je trouve que le lancement du bouton me fait "planter" un peu excel, grosse lenteur sur 4/5 secondes et ensuite comment bien écrire ma formule et réaliser le calcul plutôt que d'écrire bêtement le numéro de la ligne ?
Fichier joint avec mon début de macro.
Version TCD, à voir si c'est plus rapide !
Cela ne nécessite pas de passer en calcul manuel, a contrario le TCD doit être actualisé.
=LIREDONNEESTABCROISDYNAMIQUE("Nombre de postes total";TCD!$A$3;"Etat offre";"EN COURS";"ROME";[@ROME])+
LIREDONNEESTABCROISDYNAMIQUE("Nombre de postes total";TCD!$A$3;"Etat offre";"PREV.COURS";"ROME";[@ROME])J'ai regardé le principe des tableaux et j'ai réussi à remplir via le bouton de macro dans chaque cellule. Je suis assez fier de moi.
Par contre je trouve que le lancement du bouton me fait "planter" un peu excel, grosse lenteur sur 4/5 secondes et ensuite comment bien écrire ma formule et réaliser le calcul plutôt que d'écrire bêtement le numéro de la ligne ?
je n'ai pas vu ni bouton ni macro
pour la partie macro, je regarderai demain en utilisant scripting.dictionary
le résultat doit être un déversement en bloc d'un tableau-array, mais je me demande si la version TCD n'est pas plus pertinente
Merci pour les TCD, en effet cela semble top mais j'ai continué mes colonnes (total poste sur 12 mois) avec une autre base de 2500 offres et en activant les calculs automatiques, cela rame trop et cela rend l'utilisation du fichier trop compliqué. Peut-être que m'y prends mal mais je pense que l'astuce réside dans la macro.
J'avais pas mis le bon fichier plus haut. Le voici avec la macro que j'ai faîte.
Merci pour les TCD, en effet cela semble top mais j'ai continué mes colonnes (total poste sur 12 mois) avec une autre base de 2500 offres et en activant les calculs automatiques, cela rame trop et cela rend l'utilisation du fichier trop compliqué. Peut-être que m'y prends mal mais je pense que l'astuce réside dans la macro.
A quel moment cela rame-t-il ? Lors de l'actualisation du TCD ? ou bien y a-t-il d'autres formules ?
Version macro
Sub calculer()
Dim dico As Object, resultat()
' chargement des données
donnees = Sheets("BD_OE_encours").Range("A1").CurrentRegion.Value
' nb de postes colonne 21 | cours colonne 14 | ROME colonne 15
' comptage
Set dico = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(donnees)
If donnees(i, 14) Like "*COURS*" Then dico(donnees(i, 15)) = dico(donnees(i, 15)) + donnees(i, 21)
Next
' chargement des données et récupération du décompte
rome = Sheets("TB_Diag").Range("B3:B" & Sheets("TB_Diag").Range("B" & Rows.Count).End(xlUp).Row).Value
ReDim resultat(1 To UBound(rome) - 1)
For i = 1 To UBound(rome) - 1
resultat(i) = dico(rome(i, 1))
Next
' tranfert du résultat
Sheets("TB_Diag").Range("D3").Resize(UBound(resultat), 1) = WorksheetFunction.Transpose(resultat)
End SubBonjour Steelson,
J'ai cherché sur Internet comment comprendre la macro qui me semble toute simple et j'ai essayé de l'adapter pour calculer la colonne "Postes des 12 derniers mois" mais je n'y arrive pas à inscrire quoi que ce soit. Je ne comprends pas la macro et le principe (tu peux regarder le code).
Du coup je me suis penché sur les TCD et ça a moins ramé ceci-dit. Je voulais essayer de remplir les 14 colonnes de droites avec la Base BD_DE_123456 et passant un TCD pour voir si ça ramait mais en fait plusieurs soucis me poseront problèmes :
- Les 14 colonnes correspondent à des cantons qui doivent pouvoir changer en fonction de la base de donnée soit par une action de l'utilisateur qui renomme ses en-têtes, soit automatiquement mais cela nécessite une macro).
- Je ne peux pas calculer dans le TCD ce que je veux ... car dans les données je n'ai pas les ROME bruts mais les ROME + intitulés, une impasse.
BREF je sens que le TCD est une impasse, et pourtant j'y ai cru ...
Je reviens vers toi pour comprendre la macro et je me dis que si j'ai bien compris la macro alors en une macro on peut lui demander de calculer les 37 colonnes et transposer les résultats. Cela évite d'adapter 37 macros. Est-ce que mon raisonnement est bon ?
Je n'ai pas compris dans la macro comment faire pour adapter cette partir qui serait la seule qui changerait entre les 37 macro.
For i = 2 To UBound(donnees)
If donnees(i, 14) Like "*COURS*" Then dico(donnees(i, 15)) = dico(donnees(i, 15)) + donnees(i, 21)
NextPeut-on cumuler les LIKE (en cumulant les critères ?). Dans la macro pour les postes des 12 mois du fichier joint j'ai essayé de changer sans aucun LIKE mais je ne comprends pas.
La formule de base de la colonne postes en cours c'est la Somme des postes qui sont sur le code ROME tous simplement.
Dans la cas où une macro suffit pour remplir les 37 colonnes (37 formules) est-ce que je peux te donner les formules à incorporer ? Est-ce que dans cette macro il serait possible d'identifier les cantons différents présents dans la colonne CANTON de BD_DE_123678 et d'ajuster le nombre de colonne des cantons (14 dernières) en fonction ?
PS : je n'arrive pas à charge le fichier (service indisponible). J'essaie dans un second message.
Bon en fait le fichier devient trop lourd avec les données. J'ai du en supprimer ainsi que les TCD ...
Le voici.
Je reviens vers toi pour comprendre la macro et je me dis que si j'ai bien compris la macro alors en une macro on peut lui demander de calculer les 37 colonnes et transposer les résultats. Cela évite d'adapter 37 macros. Est-ce que mon raisonnement est bon ?
La formule de base de la colonne postes en cours c'est la Somme des postes qui sont sur le code ROME tous simplement.
Dans la cas où une macro suffit pour remplir les 37 colonnes (37 formules) est-ce que je peux te donner les formules à incorporer ? Est-ce que dans cette macro il serait possible d'identifier les cantons différents présents dans la colonne CANTON de BD_DE_123678 et d'ajuster le nombre de colonne des cantons (14 dernières) en fonction ?
Je pense que c'est possible, je vais regarder ton fichier ... mais pas forcément ce soir !
Explique moi en effet un ou deux calculs supplémentaires (sur un ou deux cantons -on se croirait en Suisse dont je reviens- !)
Je t'expliquerai aussi la macro plus en détail. Tu pourras adapter et être autonome (c'est mon but).
Merci Steelson, c'est super sympathique de te pencher sur mon problème et prendre le temps de m'aider.
Les colonnes H à X font les calculs depuis la feuille de base de donnée : BD_OE_12M (base des offres d'emploi des 12 derniers mois).
Colonne H (Total des postes)=SOMME.SI.ENS(DECALER(BD_OE_12M!$O$1;0;0;NBVAL(Nombre_de_postes_total_12M)-1);DECALER(BD_OE_12M!$L$1;0;0;NBVAL(ROME_12m)-1);"*"&[@ROME]&"*")
Colonne I (postes pourvus par MER)=SOMME.SI.ENS(DECALER(BD_OE_12M!$P$1;0;0;NBVAL(MER_12M)-1);DECALER(BD_OE_12M!$L$1;0;0;NBVAL(ROME_12m)-1);"*"&[@ROME]&"*")
Colonne J - Addition de Somme.si.ens (postes sans MER + Postes pourvus en interne)=SOMME.SI.ENS(DECALER(BD_OE_12M!$Q$1;0;0;NBVAL(Postes_satisfaits_sans_MER)-1);DECALER(BD_OE_12M!$L$1;0;0;NBVAL(ROME_12m)-1);"*"&[@ROME]&"*")+SOMME.SI.ENS(DECALER(BD_OE_12M!$R$1;0;0;NBVAL(Postes_pourvus_en_interne)-1);DECALER(BD_OE_12M!$L$1;0;0;NBVAL(ROME_12m)-1);"*"&[@ROME]&"*")
Colonne K - Postes retirés=SOMME.SI.ENS(DECALER(BD_OE_12M!$S$1;0;0;NBVAL(Postes_retirés_12M)-1);DECALER(BD_OE_12M!$L$1;0;0;NBVAL(ROME_12m)-1);"*"&[@ROME]&"*")
Colonne L - Moyenne des délais de statisfaction=SIERREUR(MOYENNE.SI.ENS(BD_OE_12M!$N$1:$N$4000;BD_OE_12M!$L$1:$L$4000;"*" & [@ROME]& "*");0) Je me rends compte que je n'ai pas changé avec la fonction DECALER
De M à X
C'est plus compliqué car en fait je fais un calcul intermédiaire pour connaitre la date de création qui est la La date d'annulation (colonne T) - le délai d'annulation (colonne N).
J'ai réalisé un macro qui me calcul à la mise à jour les dates sur une colonne à l'extérieur du tableau et qui me positionne le résultat en AD. Peut-être que ce calcul finalement peut-être stocké dans la macro. OU sinon il s'agit d'utiliser la colonne AD.
Du coup il s'agit toujours pareil du nombre de postes, par ROME créé dans le mois de Janvier / Février / .... En plus moi je prends référence les en-têtes de colonnes dans la formule mais l'idéal serait de pouvoir les classer par mois directement.=SOMME.SI.ENS((DECALER(BD_OE_12M!$O$1;0;0;NBVAL(Nombre_de_postes_total_12M)-1));(DECALER(BD_OE_12M!$L$1;0;0;NBVAL(ROME_12m)-1));"*"&[@ROME]&"*";(DECALER(BD_OE_12M!$AD$1;0;0;NBVAL(Date_de_création)-1));"<"&Tableau2[[#En-têtes];[février-19]];(DECALER(BD_OE_12M!$AD$1;0;0;NBVAL(Date_de_création)-1));">="&Tableau2[[#En-têtes];[janvier-19]])
Les colonnes de Y à AQ s"intéressent aux demandeurs d'emploi : feuille BD_DE_123678
Je pense que pour les 4 colonnes Y à AB je dois pouvoir le faire tout seul en comprenant la mécanique sachant qu'en fait on utilise NB.SI.ENS dans les formules et pas la SOMME.SI.ENS. Peut-être qu'un exemple de macro sur la colonne Y me permettrait de comprendre la différence.
Les colonnes AC à AQ sont en fait plus sensibles car même si j'arrive à adapter en ayant compris le principe, dans l'idéal il faudrait que le nombre de colonne s'adapte au nombre de cantons différents présents dans BD_DE_123678 ... ce fichier est censé être utilisé aussi par d'autres agences géographiques. Pour le moment c'est moi qui effectue manuellement la suppression de colonnes en fonction de la base de donnée mais peut-être que la macro peut détecter la liste des valeurs uniques et remplir les colonnes et supprimer/créer en fonction des résultats trouvés.
Si j'arrive à bien comprendre comment ça fonctionne je sens que je pourrais faire plein de documents ! Merci
Bonjour,
je ne suis pas sûr qu les colonnes correspondent bien à ta macro. Je suis reparti de celle-ci et tu y étais presque
Sub calculerpostes12mois()
Dim compteur12M As Object, resultat12M()
' chargement des données
donnees12M = Sheets("BD_OE_12M").Range("A1").CurrentRegion.Value
' nb de postes colonne 15 | ROME colonne 12
' comptage
Set compteur12M = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(donnees12M)
compteur12M(donnees12M(i, 12)) = compteur12M(donnees12M(i, 12)) + donnees12M(i, 15)
Next
' chargement des données et récupération du décompte
rome12M = Sheets("TB_Diag").Range("B3:B" & Sheets("TB_Diag").Range("B" & Rows.Count).End(xlUp).Row).Value
ReDim resultat12M(1 To UBound(rome12M) - 1)
For i = 1 To UBound(rome12M) - 1
resultat12M(i) = compteur12M(rome12M(i, 1))
Next
' tranfert du résultat
Sheets("TB_Diag").Range("G3").Resize(UBound(resultat12M), 1) = WorksheetFunction.Transpose(resultat12M)
End SubJ'ai changé dico en compteur afin de peut-être faciliter la compréhension et changé ceci ...
For i = 2 To UBound(donnees12M)
compteur12M(donnees12M(i, 12)) = compteur12M(donnees12M(i, 12)) + donnees12M(i, 15)
NextEn fait le "dictionnaire" ou compteur va enregistrer les noms ROME dans ses "mots" (ici la valeur trouvée en colonne 12), va créer ce "mot" s'il il n'existe pas, et incrémenter sa "définition" (valeur numérique ici) par la valeur trouvée en colonne 15.
Cela revient à écrire :
Set compteur12M = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(donnees12M)
mot = donnees12M(i, 12)
valeur = donnees12M(i, 15)
compteur12M(mot) = compteur12M(mot) + valeur
NextCe qui est plus simple c'est que cette fois-ci il n'y a pas la condition sur une autre colonne comme précédemment.
On reprend ensuite ces "définitions"-valeurs pour les affecter en colonne G en face de chaque ROME. Je les affecte d'abord dans un tableau resultat pour que ce soit plus rapide, ce tableau (à une dimension) est transposé de une ligne en une colonne et recopié en bloc dans la colonne G.
Si c'est suffisamment clair, je te laisse la main, sinon n'hésite pas à revenir ... j'ai quelques occupations urgentissimes ces jours-ci mais je reste quand même à l'écoute.
Wouah merci c'est beaucoup plus clair, j'ai compris ce que ça faisait. Du coup j'ai fais un test sur une autre colonne où je n'ai pas de valeurs numériques à ajouter comme les postes.
Donc pour ma colonne X j'ai fais ce code mais çà ne me ressort aucun résultat. Je pense que ça vient du fait qu'il ajouter les valeurs ROME qui n'ont pas de sens numérique et donc j'ai 0 en résultat.
Comment faire en sorte qu'il prenne le nombre d’occurrences dans la colonne et pas la somme des veleurs ?
Est-ce que je dois déclarer une autre variable en plus de donneeDE qui prend les Valeurs ?
Voici mon code
Sub calculerDE()
Dim compteurDE As Object, resultatDE()
' chargement des données
donneesDE = Sheets("BD_DE_123678").Range("A1").CurrentRegion.Value
' ROME colonne 14
' comptage
Set compteurDE = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(donneesDE)
compteurDE(donneesDE(i, 14)) = compteurDE(donneesDE(i, 14)) + donneesDE(i, 14)
Next
' chargement des données et récupération du décompte
romeDE = Sheets("TB_Diag").Range("B3:B" & Sheets("TB_Diag").Range("B" & Rows.Count).End(xlUp).Row).Value
ReDim resultatDE(1 To UBound(romeDE) - 1)
For i = 1 To UBound(romeDE) - 1
resultatDE(i) = compteurDE(romeDE(i, 1))
Next
' tranfert du résultat
Sheets("TB_Diag").Range("X3").Resize(UBound(resultatDE), 1) = WorksheetFunction.Transpose(resultatDE)
End SubMerci infiniment pour l'apprentissage
En fait je suis en train de me dire que le problème ne vient pas forcément de la formule car je pourrais mettre ainsi pour le compter non ?
compteurDE(donneesDE(i, 14)) = donneeDE(i,14) + 1Mais ca vient du fait que dans les cellules de ma base je n'ai pas le ROME tout seul, il est dans une chaîne de caractère et donc il ne peut pas le trouver. Comment puis-je résoudre ça du coup ... c'est pas facile tout ça !
Mon raisonnement est-il juste ?
Je n'ai pas bien compris ce que tu cherchais à mettre en X.
Si ce n'est pas un décompte mais une information issue de la colonne 14, alors
Sub calculerDE()
Dim donneeDE As Object, resultatDE()
' chargement des données
donneesDE = Sheets("BD_DE_123678").Range("A1").CurrentRegion.Value
' ROME colonne 14
' comptage
Set donneeDE = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(donneesDE)
maCle = Replace(donneesDE(i, 14), "Non renseigné", "")
maCle = Left(maCle, 5)
valeur = Replace(donneesDE(i, 14), "Non renseigné", "")
valeur = Replace(valeur, maCle & " - ", "")
donneeDE(maCle) = valeur
Next
' chargement des données et récupération du décompte
romeDE = Sheets("TB_Diag").Range("B3:B" & Sheets("TB_Diag").Range("B" & Rows.Count).End(xlUp).Row).Value
ReDim resultatDE(1 To UBound(romeDE) - 1)
For i = 1 To UBound(romeDE) - 1
resultatDE(i) = donneeDE(romeDE(i, 1))
Next
' tranfert du résultat
Sheets("TB_Diag").Range("X3").Resize(UBound(resultatDE), 1) = WorksheetFunction.Transpose(resultatDE)
End SubCe que je voudrais avoir en X3 c'est le nombre de fois où le code Rome en B3 apparaît dans la colonne Métier recherché (colonne 14 de BD_DE_123657 ).
Mais dans la colonne N, quand tu regardes il y a une chaîne de caractère, pas que le code Rome tout seul.
Je regarde boisgontierjacques.free.fr pour m'aider à comprendre mais je nage ... je nage ....
Retire de N uniquement le ROME comme ceci
maCle = Replace(donneesDE(i, 14), "Non renseigné", "")
maCle = Left(maCle, 5)Ce que je voudrais avoir en X3 c'est le nombre de fois où le code Rome en B3 apparaît dans la colonne Métier recherché (colonne 14 de BD_DE_123657 ).
Mais dans la colonne N, quand tu regardes il y a une chaîne de caractère, pas que le code Rome tout seul.
Dans ce cas ... compteurDE(maCle) = compteurDE(maCle) + 1
Sub calculerDE()
Dim compteurDE As Object, resultatDE()
' chargement des données
donneesDE = Sheets("BD_DE_123678").Range("A1").CurrentRegion.Value
' ROME colonne 14
' comptage
Set compteurDE = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(donneesDE)
maCle = Replace(donneesDE(i, 14), "Non renseigné", "")
maCle = Left(maCle, 5)
compteurDE(maCle) = compteurDE(maCle) + 1
Next
' chargement des données et récupération du décompte
romeDE = Sheets("TB_Diag").Range("B3:B" & Sheets("TB_Diag").Range("B" & Rows.Count).End(xlUp).Row).Value
ReDim resultatDE(1 To UBound(romeDE) - 1)
For i = 1 To UBound(romeDE) - 1
resultatDE(i) = compteurDE(romeDE(i, 1))
Next
' tranfert du résultat
Sheets("TB_Diag").Range("X3").Resize(UBound(resultatDE), 1) = WorksheetFunction.Transpose(resultatDE)
End Sub