chargement d'une formule trop long. souhaite employer une macro

[pasquetp]

bonjour,

je bute sur un probleme tres embettant et je souhaite de tout coeur trouver une solution

je telecharge au boulot une base de donnees logistique assez consequente: plus de 40,000 lignes

pour analyser les donnees j'emploie un tableau dynamique

toutefois ce dernier a une limite

j'ai donc contourne la difficulte en ajoutant a la base de donnees une colonne supplementaire avec la formule suivante:


=IF(COUNTIFS($G$2:G2,G2,$H$2:H2,H2)=1,1,0)

en gros ce que cette formule nous dit c'est :

si g(x) et h(y) ont deja ete identifie: j'insiste sur le mot deja. donc si la valeur en g5 et h5 par exemple n'ont jamais ete identifie alors AH5 aura la valeur 1. si par contre en g6 et h6 on retrouve une valeur deja enregistre par eexemple en g2 et h2, alors ah6=0

jusque la je pense que c'est clair

le souci cest que le remplacement de la base de donnees ( que je suis bien sur oblige de faire plusieurs fois par jour CAR la base de donnees evolue sans arret avec entrees et sorties), ca fait rammmer a mort l'ordi a cause de cette maudite formule

ma requete est a simple a dire ( pas a faire)

je veux que la formule soit transforme en valeur ou plutot que la valeur resultant de la formule soit toujours la mais SANS formule

je pense donc aux macros

donc les colonnes a noter sont : G ; H et AH

quelqu'un pourait il (elle ) m'aider?

votre aide me sauve mon poste

Merci infiniment

Pierre

ps si newcessaire je peux envoyer un fichier echantillon si necessaire.

 

[pierrejean]

Re : chargement d'une formule trop long. souhaite employer une macro

Bonjour pasquetp

Un fichier échantillon sera plus qu'utile (faire une copie de l'original , sélectionner quelques lignes typiques (1 dizaine suffira surement) ,les rendre anonymes et poster
A bientot

 

[pasquetp]

Re : chargement d'une formule trop long. souhaite employer une macro

bonjour Mr Pierrejean,

c'est une magnifique nouvelle que de vous lire.

je vous compte parmi les meilleurs en toute honnetete. le nombre de lectures que j'ai lu et vos fichiers sont juste geniaux

je m'y emploie a vous remettre le fichier

 

[pasquetp]

Re : chargement d'une formule trop long. souhaite employer une macro

re

voici l'echantillon

j'ai du retirer toute donnees sensible.

si quelque chose n'ait pas claire, je serai ravi de le clarifier pour vous aider

Merci encore

 

[pasquetp]

Re : chargement d'une formule trop long. souhaite employer une macro

je vous donnerai aussi la raison de cette donnees en colonne AH: c'est pour arriver a avoir dans le tableau dynamique la nombre de reference par emplacement et inversement: le nombre d'emplacement par article

sans cette colonne le tableau dynamique ne peut arriver au résultat escompté.

 

[job75]

Re : chargement d'une formule trop long. souhaite employer une macro

Bonjour pasquetp,

Pour ceux qui ne savent pas COUNTIFS c'est NB.SI.ENS.

Le calcul ne sera probablement pas plus rapide avec une macro VBA.

Mais si vous voulez éviter que les formules se recalculent chaque fois que les colonnes G et H sont modifiées, mettez le fichier en mode de calcul Manuel (sur ordre).

Pour obtenir le recalcul vous appuierez sur la touche F9.

Et avant de fermer le fichier passez en mode de calcul Automatique.

Edit : bonjour Pierre, je n'avais pas rafraîchi.

A+

 

[mapomme]

Re : chargement d'une formule trop long. souhaite employer une macro

Bonsoir pasquetp, à tous,

Un essai par macro dans le module de code de la feuille "Feuil1":

Sub DejaLa()
Dim n&, i&, dico, res(), tablo, s

  n = Cells(Rows.Count, "g").End(xlUp).Row - 1
  Set dico = CreateObject("scripting.dictionary")
  If n > 0 Then
    tablo = Range("g2:h" & n + 1)
    ReDim res(1 To n, 1 To 1)
    Range("ah2:ah" & Rows.Count).ClearContents
    For i = 1 To n
      s = tablo(i, 1) & tablo(i, 2)
      If Not dico.Exists(s) Then
        dico.Add s, ""
        res(i, 1) = 1
      Else
        res(i, 1) = 0
      End If
    Next i
    Cells(2, "ah").Resize(UBound(tablo)).Value = res
  End If
End Sub

Edit: une version v2 plus automatisée qui lance la macro dès qu'une valeur des colonnes g ou h est modifiée.

 

[job75]

Re : chargement d'une formule trop long. souhaite employer une macro

Re, bonjour mapomme,

C'est bien ce que je pensais.

Sur le tableau présenté les formules se calculent en 0,40 milliseconde.

Alors que le code VBA s'exécute en 1,20 millisecondes.

Bien sûr il faudrait comparer sur 40000 lignes.

A+

 

[pierrejean]

Re : chargement d'une formule trop long. souhaite employer une macro

Re

Vois si cela te convient
NB: Dans la macro tu peux adapter AI

PS: Merci pour les compliments

Edit: A la bourre moi !!!
Il semble mapomme que nous ayons eu la même intuition

 

[pasquetp]

Re : chargement d'une formule trop long. souhaite employer une macro

Merci a tous pour votre soutien

c'est vraiement sympathique a vous

je suis en train de tester toutes les methodes proposees sur un fichier de 42,000

je vous tiens au courant dans la soiree

 

[pasquetp]

Re : chargement d'une formule trop long. souhaite employer une macro

voici les resultats sur un nombre de 42,378 lignes:

methode Pierre Jean = 0,234375 seconde

methode mapomme = 0,1875 seconde

c'est juste halluciant autant de donnees et si peu de temps c'est incroyable

quelque soit le vainqueur, je vous remercie infiniment vous m'avez vraiment retire une sale épine du pied.

je souhaiterai si ca ne vous derange pas avoir votre avis sur le probleme numero 2 ( il n'y a pas d'autres probleme je vous rassure) que je rencontre.

ce probleme est sans doute plus delicat a resoudre

je tente

voila la macro que vous avez fait permet de traiter l'analyse des donnees. chose faite

toutefois comme je l'ai note a un moment je dois telecharger la base de donnees regulierement

je vais sur le site intranet via VPN. une fois sur le site de gestion des stocks, je fais une recherche du stock a telecharger: une fois que j'ai accede au stock que je veux plus tard analyser il faut bien entendu le telecharger via un bouton de telechargement et ensuite le mettre sur le fichier contenant la macro

J'ai bien entendu le lien de telechargement: ce qui complique cest de un vous ne pouvez pas y acceder vu le VPN en place et de deux cest trop des donnees sensibles. il faudra donc se passer du lien jen ai peur

loperation en elle meme c'est tres repetitif: est ce qu'il serait possible de faire un bouton vba afin de auto telcharger la base de donnes depuis directement le fichier macro

le benefice qu'on en tire: on aurait ainsi un fichier excel avec possibilite d'avoir le contenu du stock en temps casi reel

je n'attends pas une formule magique, plutot des avis

Merci encore

 

[job75]

Re : chargement d'une formule trop long. souhaite employer une macro

Re,

J'avais vraiment mal raisonné.

Chaque formule devant étudier toutes les lignes au-dessus c'est forcément bien plus long que le VBA.

Bonne fin de soirée.

 

[pasquetp]

Re : chargement d'une formule trop long. souhaite employer une macro

Qulqu'un aurait une idee?