Microsoft 365 Répartition d'un montant en fonction du poids

[ivan27]

Bonjour à tous

En pièce jointe un classeur contenant 2 feuilles ''Regroupement'' et ''BD''

Le classeur de production contient environ 400000 lignes par feuille.

Je cherche à répartir, en fonction du poids, les montants inscrits sur la feuille ''Regroupement'' colonne ''L'' sur les lignes de la feuille ''BD'', colonne ''N''.

L'identification des lignes communes sur les 2 feuilles se fait par le numéro de groupe.

Pour chaque numéro (colonne A - feuille Regroupement), il faut chercher les numéros identiques (Colonne B - Feuille BD) puis proratiser le montant en fonction du poids.

Exemple : si sur la feuille regroupement j'ai une ligne à 80 € pour un poids de 50 kg
Je trouve 2 lignes avec le même numéro de groupe sur la feuille BD
Ligne 1 : 22 kg
Ligne 2 : 28 kg
J'affecte à la ligne 1 le montant de (80/50) X 22 = 35,20 €
J'affecte à la ligne 2 le montant de (80/50) X 28 = 44,80 €

J'ai renseigné manuellement quelques ligne à titre d'exemple.

Merci d'avance pour votre aide.

Bien cordialement,

 

[job75]

Bonjour ivan27,

Entrez en N2 de la feuille BD :

=SIERREUR(M2*RECHERCHEV(B2;Regroupement!A:L;12;0)/RECHERCHEV(B2;Regroupement!A:L;11;0);"")

A+

 

[Hasco]

Bonjour,

Après avoir transformé vos tableaux en tableaux structurés et nommés 'T_Grps' pour celui de la feuille regroupement et 'T_BD' pour celui de la feuille BD et comme vous êtes sur excel 365 :

=LET(Tarif;RECHERCHEX([@[N°
Group.]];T_Grps[N°
Group.];T_Grps[Tarif]/T_Grps[Poids];0;0);SI(Tarif>0;Tarif*[@poids];0))

Les sauts de lignes dans la formule sont dus aux sauts de lignes dans les entêtes !

voir le format personnalisé pour cacher les valeurs 0

Cordialement

 

[ivan27]

Re bonjour, le forum, job75, Hasco,
Merci beaucoup pour vos propositions
En terme de rapidité sur 400000 lignes, est-ce qu'il y a une solution à privilégier par rapport à l'autre ?
Bien cordialement,

 

[job75]

En terme de rapidité sur 400000 lignes, est-ce qu'il y a une solution à privilégier par rapport à l'autre ?

Pour tester j'ai d'abord rendu les formules volatiles avec ENT(ALEA()) :

=SIERREUR((ENT(ALEA())+M2)*RECHERCHEV(B2;Regroupement!A:L;12;0)/RECHERCHEV(B2;Regroupement!A:L;11;0);"")

puis copié la plage A2:N21 sur 400 000 lignes.

Le recalcul des formules se fait chez moi en 0,20 seconde, c'est quasi immédiat.

 

[Hasco]

Re,

La solution par formule de mon précédent message, pourrait être remplacée par une solution power query (voir fichier joint)

En terme de rapidité sur 400000 lignes, est-ce qu'il y a une solution à privilégier par rapport à l'autre ?

A vous de tester.

Cordialement

P.S. je ne vois pas les messages de l'autre participant.

 

[job75]

P.S. je ne vois pas les messages de l'autre participant.

Oui Hasco m'ignore, ce n'est pas moi qui en souffre

 

[ivan27]

job75, hasco,
Encore merci pour vos propositions
Géniale la proposition avec PowerQuery.
Bonne fin de journée
Ivan