Calcul d'un taux pondéré

[zizoufan]

Bonjour les amis,

je voudrais calculer un taux pondéré dans une base de données avec deux conditions ( Cf. PJ). Comment je dois procéder ? Merci

 

[job75]

Re : Calcul d'un taux pondéré

Re,

Sur Excel 2007 et versions suivantes on peut utiliser NB.SI.ENS :

=N(F3)*SI(NB.SI.ENS(B:B;B3;C:C;"Voix";D:D;D3;E:E;E3;F:F;"NA");RECHERCHEV(C3;{"Dossier".0,8;"Courrier".0,2;"Voix".0};2;0);RECHERCHEV(C3;{"Dossier".0,45;"Voix".0,45;"Courrier".0,1};2;0))

Pas besoin de définir des noms, les plages ne sont pas limitées.

Fichier (2).

A+

 

[job75]

Re : Calcul d'un taux pondéré

Re,

Le fichier du post #6 montre que le tableau doit avoir plusieurs milliers de lignes.

J'ai donc testé les solutions précédentes sur un tableau de 4500 lignes.

Les durées de recalcul des formules sont assez longues (sur Win 8 - Excel 2013) :

- fichiers (1) ou (1 bis) => 11 secondes

- fichier (2) => 20 secondes.

Si l'on veut des durées plus courtes il faut passer par du VBA.

A+

 

[zizoufan]

Re : Calcul d'un taux pondéré

Super Merci. je regarde tout ca et je vous fait un retour.

 

[job75]

Re : Calcul d'un taux pondéré

Bonjour zizoufan,

Vous avez peut-être compris que si le recalcul des formules prend du temps (sur 4500 lignes) c'est parce que les recherches se font sur tout le tableau pour les fichiers (1) et (1 bis) et colonnes entières pour le fichier (2).

Mais si le tableau est toujours trié comme sur l'exemple, c'est à dire par année puis par mois, on peut réduire le tableau de recherche T au seul mois concerné.

Voyez ce fichier (3) et les noms définis BD T NA.

Edit : à part le tri il faut une 3ème condition : les "Voix" doivent se trouver à la suite des "Dossier" et "Courrier".

Sur 4500 lignes le recalcul des formules devrait prendre moins de 5 secondes, à tester.

A+

 

[job75]

Re : Calcul d'un taux pondéré

Bonjour zizoufan, le forum,

Voyez le fichier joint et cette macro dans le code de la feuille :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim P As Range, colcea%, coltype%, colmois%, colan%
Dim coltaux%, colpond%, ncol%, t, i&, a#, b#, c#, x$, y$, z$
'---préparation---
Set P = [B2].CurrentRegion 'adapter éventuellement
colcea = 1: coltype = 2: colmois = 3: colan = 4: coltaux = 5: colpond = 6
ncol = P.Columns.Count + 1 'une colonne auxiliaire en plus pour le tri
Set P = P.Resize(P.Rows.Count + 2, ncol) '2 lignes en plus par sécurité
Application.ScreenUpdating = False
P(1, ncol) = 1
P.Columns(ncol).DataSeries 'numérotation des lignes
P.Sort P(1, colan), , P(1, colmois), , , P(1, colcea), Header:=xlYes 'tri sur Année+Mois+CEA
t = P 'matrice, plus rapide
'---remplissage de la colonne colpond---
For i = 2 To UBound(t) - 2 Step 3
  If IsNumeric(t(i, coltaux)) Then a = t(i, coltaux) Else a = 0
  If IsNumeric(t(i + 1, coltaux)) Then b = t(i + 1, coltaux) Else b = 0
  If IsNumeric(t(i + 2, coltaux)) Then c = t(i + 2, coltaux) Else c = 0
  x = t(i, coltype): y = t(i + 1, coltype): z = t(i + 2, coltype)
  If t(i, coltaux) = "NA" Or t(i + 1, coltaux) = "NA" Or t(i + 2, coltaux) = "NA" Then
    t(i, colpond) = a * IIf(x = "Dossier", 0.8, IIf(x = "Voix", 0, 0.2))
    t(i + 1, colpond) = b * IIf(y = "Dossier", 0.8, IIf(y = "Voix", 0, 0.2))
    t(i + 2, colpond) = c * IIf(z = "Dossier", 0.8, IIf(z = "Voix", 0, 0.2))
  Else
    t(i, colpond) = a * IIf(x = "Dossier", 0.45, IIf(x = "Voix", 0.45, 0.1))
    t(i + 1, colpond) = b * IIf(y = "Dossier", 0.45, IIf(y = "Voix", 0.45, 0.1))
    t(i + 2, colpond) = c * IIf(z = "Dossier", 0.45, IIf(z = "Voix", 0.45, 0.1))
  End If
Next
'---restitution---
P.Columns(colpond).Resize(UBound(t) - 2) = Application.Index(t, , colpond)
P.Sort P(1, ncol), xlAscending 'tri pour remettre dans l'ordre initial
P.Columns(ncol).ClearContents
End Sub

La macro procède a un tri préalable sur l'année puis le mois puis le CEA.

Sur 4500 lignes elle s'exécute en 0,08 seconde.

A+

 

[zizoufan]

Re : Calcul d'un taux pondéré

Bonjour Job,

peux-tu stp me dire ce qui ne va pas dans mon fichier ? J'ai mis exactement la même formule mais cela ne marche pas.
cela me renvoit une erreur "#NOM"

 

[job75]

Re : Calcul d'un taux pondéré

Bonjour zizoufan,

peux-tu stp me dire ce qui ne va pas dans mon fichier ? J'ai mis exactement la même formule mais cela ne marche pas.

Je ne suis pas devin. Vous avez probablement oublié de définir un nom...

A+

 

[zizoufan]

Re : Calcul d'un taux pondéré

j'ai défini les noms mais pas de chance. la il me renvoit #Valeur. Décidément j'y arrive pas.

 

[job75]

Re : Calcul d'un taux pondéré

Re,

Définissez les noms T et NA correctement, comme dans mes fichiers !!!

Comme vous avez ajouté la colonne "Manager", si elle ne sert pas dans les calculs, définissez T par :

=DECALER(bdds!$C$1;;;EQUIV("zzz";bdds!$C:$C);6)

sinon il faudra revoir la définition de NA.

Enfin vous avez mis un 3 dans RECHERCHEV, je n'ai jamais écrit ça

A+

 

[zizoufan]

Re : Calcul d'un taux pondéré

merci job je réessayer et je reviens vers toi.