calcul de soustotal avec sommeprod

[BBdoc]

Bonjour à tous !

C'est mon premier post, alors j'espère que je l'aurais fait dans les règles de l'art.

Voilà , j'essaye d'automatiser un calcul via une macro depuis plus d'un mois.

il y a peut-être une meilleure solution que la macro, mais je n'ai pas trouvé.

Je n'arrive pas à faire une formule ou une macro qui me permettrait de calculer le prix d'une nomenclature à multiple niveau.

Pour raison de confidentialité, j'ai bidouillé les données.
Donc, on a la première partie du tableau où je fais un recherchev pour trouver mes prix qui se situent en feuil2 combiné avec un sierreur quand je n'ai pas prix dans la colonne D.
Après quelques manips on obtient le résultat qui se trouve en L.

Je n'arrive pas à fusionner le sommeprod dans la formule donc je le fais manuellement.

Auriez vous une solution pour moi?

Merci.

 

[ROGER2327]

Re : calcul de soustotal avec sommeprod

Bonjour BBdoc

(...)
Auriez vous une solution pour moi?
(...)

Peut-être, mais la pièce jointe n'est pas passée...​

A plus tard...
ROGER2327
#2899

 

[BBdoc]

Re : calcul de soustotal avec sommeprod

je n'avais pas vu, mon fichier fait 2.11 Mo donc il ne passe pas !

voici le lien :

Ce lien n'existe plus

 

[ROGER2327]

Re : calcul de soustotal avec sommeprod

Re...

Pas de chance, décidément ! Je n'ai pas fait attention que cette discussion est sur le forum spécial 2007. N'ayant qu'Excel2003 à ma disposition, je ne peux suivre la discussion.
J'espère que des gens plus modernes que moi vont prendre le relais...​

Bon courage !
ROGER2327
#2906

 

[Tibo]

Re : calcul de soustotal avec sommeprod

Bonjour BBdoc, bonjour Roger,

Après un petit passage par la boule de cristal, une proposition en L2 :

=SIERREUR(RECHERCHEV(B2;Feuil2!A:B;2;FAUX);SOMMEPROD((
DECALER(I2;1;3;SI(NB.SI(I3:I44;I2)>0;EQUIV(I2;I3:I44;0)-1;NBVAL(I3:I44)))*
(DECALER(I2;1;;SI(NB.SI(I3:I44;I2)>0;EQUIV(I2;I3:I44;0)-1;NBVAL(I3:I44)))=CAR(
CODE(I2)+1))*(DECALER(I2;1;2;SI(NB.SI(I3:I44;I2)>0;EQUIV(I2;I3:I44;0)-1;
NBVAL(I3:I44)))))))

plage de cellules à adapter

à recopier vers le bas

Je te laisse tester

P.S. : ton fichier aurait pu être considérablement allégé. Il n'y avait rien de la ligne 45 à 65000 ...

Voir fichier joint qui fait désormais moins de 20 ko)

@+

 

[BBdoc]

Re : calcul de soustotal avec sommeprod

Ca marche impeccablement !

J'ai limité la limite basse avec un $

Merci beaucoup.

Maintenant, il me reste plus qu'à travailler la mise en page avec les grouper et insérer la formule en automatique via une macro.

Encore merci.

 

[BBdoc]

Re : calcul de soustotal avec sommeprod

Bon sur ma base de test, ca marche bien mais sur mes données réelles c'est autre chose !
voilà la formule que j'utilise pour mes données :
=SIERREUR(RECHERCHEV(E5;'[recapitulatif prix pièces.xls]Prix'!$A:$B;2;FAUX);SOMMEPROD((DECALER(B5;1;11;SI(NB.SI($B6:B$50;B5)>0;EQUIV(B5;$B6:B$50;0)-1;NBVAL($B6:B$50)))*(DECALER(B5;1;;SI(NB.SI($B6:B$50;B5)>0;EQUIV(B5;$B6:B$50;0)-1;NBVAL($B6:B$50)))=CAR(CODE(B5)+1))*(DECALER(B5;1;8;SI(NB.SI($B6:B$50;B5)>0;EQUIV(B5;$B6:B$50;0)-1;NBVAL($B6:B$50)))))))

j'ai modifié les variables du décaler car mes données ne sont pas comme dans l'exemple.

Mes niveaux de nomenclature se trouvent en colonne B
Mes articles en colonne E
Mes quantités en colonne G
et ma formule en colonne M

 

[Tibo]

Re : calcul de soustotal avec sommeprod

Bonjour,

Essaye en modifiant le 11 en 6 et le 8 en 5

Je te laisse tester

@+

 

[BBdoc]

Re : calcul de soustotal avec sommeprod

j'ai fait comme vous avez dit mais ca ne marche pas.
J'ai mis l'exemple en feuil3

 

[Tibo]

Re : calcul de soustotal avec sommeprod

Bonsoir,

en M2 :

=SIERREUR(RECHERCHEV(E2;Feuil2!A:B;2;0);SOMMEPROD((DECALER(B2;1;10;SI(NB.SI(
B3:$B$50;B2)>0;EQUIV(B2;B3:$B$50;0)-1;NBVAL(B3:$B$50)))*(DECALER(B2;1;;SI(
NB.SI(B3:$B$50;B2)>0;EQUIV(B2;B3:$B$50;0)-1;NBVAL(B3:$B$50)))=CAR(CODE(B2)+1))*
(DECALER(B2;1;5;SI(NB.SI(B3:$B$50;B2)>O80;EQUIV(B2;B3:$B$50;0)-1;
NBVAL(B3:$B$50)))))))

à recopier vers le bas

je te laisse tester

@+

 

[BBdoc]

Re : calcul de soustotal avec sommeprod

j'ai testé ce matin, ca marche !
Les valeurs à changer était 11 et 5.

Merci pour le coup de main.

 

[BBdoc]

Re : calcul de soustotal avec sommeprod

Question peut remplacer la variable $B$50 par une formule "Range("b1").End(xlDown).Row"?
sinon pas la formule =CONCATENER("$B$";NBVAL(B:B)+NB.VIDE(B1:B10)+1)

=SIERREUR(RECHERCHEV(E2;Feuil2!A:B;2;0);SOMMEPROD((DECALER(B2;1;10;SI(NB.SI(
B3:$B$50;B2)>0;EQUIV(B2;B3:$B$50;0)-1;NBVAL(B3:$B$50)))*(DECALER(B2;1;;SI(
NB.SI(B3:$B$50;B2)>0;EQUIV(B2;B3:$B$50;0)-1;NBVAL(B3:$B$50)))=CAR(CODE(B2)+1))*
(DECALER(B2;1;5;SI(NB.SI(B3:$B$50;B2)>O80;EQUIV(B2;B3:$B$50;0)-1;
NBVAL(B3:$B$50)))))))