Calcul Variance de prix avec quantité

[Anaiiis]

Bonjour à tous et à toutes
Je suis confrontée à un problème que je n'arrive pas à résoudre, et pour lequel jusqu'à présent personne n'a pu me venir en aide
Voici mon probleme:
J'aimerais calculer la variance des prix unitaires de vente de chaque produit vendu par l'entreprise. Je possède pour cela une base de donnée avec toutes les lignes de facturation effectuées par la l'entreprise. Actuellement la base fait plus de 300.000 lignes, chaque ligne étant une ligne de facturation presentée comme suit:

colonne1: reference du produit
colonne2: Quantité vendue (sur cette ligne de facturation)
colonne3: montant en euros ( pour la quantité de produit vendu)
colonne4: Prix Unitaire du produit pour cette ligne de facturation ( que j'ai calculé: montant/quantité)

cette base est une base importée sur excel et actualisée via une connexion ODBC

j'ai premièrement pensé le faire via un tableau croisé dynamique... or le calcul de la formule de variance d'excel ne permet pas de prendre en compte la colonne quantité.

par exemple si j'avais
1€
1€
2€
2€
2€
var(1,1,2,2,2) est faisable
mais moi j'ai
prix quantité
1€ 2
2€ 3
donc la formule n'est pas applicable et il n'est donc pas possible de le faire directement par un tableau croisé dynamique.

J'ai donc pensé le faire en rajoutant une colonne QuantitéTotale de produit vendu (quelque soit le prix) (grace à un somme.si avec pour critère le code produit), une colonne PrixMoyen par produit( grace à un somme.si qui additione tous les montants pour un meme produit et je divise cette somme par la quantité totale vendue de ce produit)
Grace à ces deux colonnes je serais en mesure d'en creer une troisième qui serait:
(Quantité/QuantitéTotale)*(PrixUnitaire-PrixMoyen)^2

Et là un beau tableau dynamique qui me sommerait cette derniere colonne pour chaque produit me donnerait la fameuse variance que je recherche et qui s'actualiserait à chaque actualisation de la base de donnée

Mais vu la taille de la base mon ordinateur met des heures à effectuer les calculs, heures que je pourrais consacrer à vernir mes ongles... mais ca ne serait pas tres serieux!

Je penses que vu la taille de la base un programme VBA ne serait pas non plus la meilleure solution...

Si quelqu'un arrive à déchiffrer mon explication et avait une idée pour m'aider à calculer ces variances beaucoup plus rapidement je ferais un gros bisou sur son pseudo!!!!

(ci-joint une illustration de ma table en très très très réduite)

 

[Anaiiis]

Re : Calcul Variance de prix avec quantité

oui mais le fait de rajouter la condition au programme vba lui fait faire des taches de vérification en plus non?

 

[Habitude]

Re : Calcul Variance de prix avec quantité

En fait la condition enlève le calcul sur l'élément.
Donc, tu remplace le calcul par une tâche beaucoup plus rapide.

As-tu tester les temps ?

Sans les données difficile à dire mais tu peux remplacer par ceci

Private Sub CommandButton1_Click()
Dim obj As New ObjetDonnees
For Each cell In Range("A2").Resize(UsedRange.Rows.Count, 1).SpecialCells(xlCellTypeConstants)
    If Trim(CStr(cell)) <> "" And CDbl(cell.Offset(, 1)) <> 0 And CDbl(cell.Offset(, 2)) <> 0 Then obj.Add CStr(cell), CDbl(cell.Offset(, 1)), CDbl(cell.Offset(, 2))
Next cell
obj.CalculVar
With Sheets("Hoja2").Range("A2"): .Resize(UsedRange.Rows.Count, 2).ClearContents: .Resize(obj.Count, 2) = obj.Affichage: End With
End Sub

 

[Anaiiis]

Re : Calcul Variance de prix avec quantité

ok ok je pensais que la condition était une tache lourde,
je vais essayer de modifier le code avec ce que tu m'as donné.
Cependant avec le fichier excel que tu m'as donné cela ne semble pas fonctionner lorsqu'il y a beaucoup de lignes, j'ai rajouté 100.000lignes pour tester, et cela me m'affiche une erreur, et ca me surligne cette partie:

Private Sub CommandButton1_Click()
Dim obj As New ObjetDonnees
For Each cell In Range("A2").Resize(UsedRange.Rows.Count, 1).SpecialCells(xlCellTypeConstants): obj.Add CStr(cell), CDbl(cell.Offset(, 1)), CDbl(cell.Offset(, 2)): Next cell
obj.CalculVar
With Sheets("Hoja2").Range("A2"): .Resize(UsedRange.Rows.Count, 2).ClearContents: .Resize(obj.Count, 2) = obj.Affichage: End With
End Sub



j'ai essayé de regarder si les variables étaient bien définie et tout a l'air ok... je dois avoir des mèches blondes cachées

 

[Anaiiis]

Re : Calcul Variance de prix avec quantité

bon beh ca a l'air de fonctionner avec ma base de 300.000lignes, j'en ai la larme à l'oeil
je vais essayer de voir combien de temps l'opération met exactement afin de te le dire.... mais sincèrement merci, je vais essayer de bien comprendre ton code afin de pouvoir réutiliser la méthode par la suite car c'est incroyablement efficace!!!!
MERCI BEAUCOUP VRAIMENT

 

[Anaiiis]

Re : Calcul Variance de prix avec quantité

bon beh je vais devenir folle j'ai actualisé ma base et ça ne fonctionne plus, nouveau message d'erreur au bout de 45secondes
"division par zéro"

 

[Anaiiis]

Re : Calcul Variance de prix avec quantité

ce qui me parait bizarre c'est que quand le message d'erreur apparait, on a CStr(cell)=473000002 (et donc cell aussi)

 

[Habitude]

Re : Calcul Variance de prix avec quantité

Re

UsedRange.row.count est capricieux. Tu peux toujours mettre 1000000 à la place.

Tu as ajouté les conditions en #17 ?

Si tu préfère, envoie ton fichier sur un site hébergeur comme "ci-joint" et donne moi le lien.

 

[Anaiiis]

Re : Calcul Variance de prix avec quantité

Bonjour

changer le usedrange.row.count ne change malheureusement rien

 

[Habitude]

Re : Calcul Variance de prix avec quantité

Condition #17

et /ou ton fichier

Je ne peux pas faire mieux

 

[Anaiiis]

Re : Calcul Variance de prix avec quantité

re
La condition 17 ne résoud malheureusement pas le probleme, pour ce qui est du fichier je peux le joindre mais je ne pense pas qu'il soit d'une grande utilité sachant que ce ne sont que 3 colonnes, une avec la reference (alphanumérique, ex: fa11/12/1361324), une avec les quantités qui ne sont pas forcément entières, et une colonne avec le montant, il arrive parfois qu'une cellule soit vide dans une des colonnes, peu importe la colonne. Et la base fait 300.000Lignes...
Le truc c'est que les données sont confidentielles donc si je modifie les données il n'y a plus d'interet, j'ai essayé avec une base recréée de 300.000lignes avec des données aleatoires, des celules vides...et le problème est le même
C'est pas faute d'essayer de comprendre mais la je m'avoue vaincue

 

[Habitude]

Re : Calcul Variance de prix avec quantité

Re

En fait, il te faut tester tes 3 colonnes et de modifier les données au besoin avant de faire l'ajout dans l'objet.
Exemple, si une cellule quantité est vide, tu la change pour Zéro.