Classement A B C

**Kloun** · 12/02/2007, 10h18

Bonjour,

J'ai une question concernant la faisabilité d'une fonction de classement.
Je voudrais classer en 3 parties A, B et C des données.
En supposant que ces données soient des CA, je voudrais que la fonction me dise dans quelle partie se retrouve mon CA, sachant que les classes A, B et C se décompose de la façon suivante :
On trie les données de façon décroissante, dans la partie A se trouve les données faisant partie des X premiers % de mon CA, dans la partie B, les Y% suivant et dans C, les Z% qui restent.
Un petit exemple
A1 - 100
A2 - 50
A3 - 300
A4 - 150
A5 - 50
A6 - 50
A7 - 50
A8 - 200
A9 - 30
A10 - 20
Si X = 80%, Y = 15% et Z = 5%, la fonction devrait me retourner :
A1 - 100 - A
A2 - 50 - A
A3 - 300 - A
A4 - 150 - A
A5 - 50 - B
A6 - 50 - B
A7 - 50 - B
A8 - 200 - A
A9 - 30 - C
A10 - 20 - C
Est-ce faisable facilement ? Comment ?

Merci.

**loufab** · 12/02/2007, 11h01

Bonjour,

Regarde la fonction Partition() dans l'aide...

Cordialement,

**Kloun** · 12/02/2007, 12h26

Merci pour la première réponse.
Je pense que je me fais mal comprendre.

J'ai trouvé une définition plus correcte (et pus explicite) :

Analyse ABC (ABC) : Méthode de classement d'un référentiel par ordre décroissant des sorties. Cette méthode consiste à diviser le référentiel en trois groupes :
- le groupe A est composé des références constituant 80% des sorties (représentant généralement 20% des références),
- le groupe B est composé des références constituant 15% des sorties (représentant généralement 30% des références),
- et le groupe C est composé des références constituant 5% des sorties (représentant généralement 50% des références).

Cette méthode permet de connaître les références qui méritent une attention particulière.

Le classement peut également être effectué pour constituer des groupes en fonction d'autres critères tels que les volumes, les achats, les CA, etc.

Donc la fonction partition ne convient pas.
En gros, il faudrait trier la population selon le CA (ou autre) descendant, faire la somme totale, ensuite, traiter de façon séquentielle les données triées pour voir si la somme partielle est dans les 80, 15 ou 5 % et stocker le résultat.
C'est un peu lourd. Y'aurait pas plus simple ?

**Heureux-oli** · 12/02/2007, 12h53

Pour ton Pareto, un simple iif dans une requête mise à jour ne suffit-il pas ?

**Kloun** · 12/02/2007, 13h11

Peut-être, mais je ne vois pas comment le construire, vu qu'il faut tenir compte de la somme partielle ...

**Tofalu** · 12/02/2007, 13h33

Bonjour

Prenons cet exemple de table

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
NumSortie	DateSortie	RefProduit	Qte
1	12/12/2001	0012	32
2	13/12/2004	0013	44
3	14/12/2005	0014	22
4	13/12/2002	0012	21
5	14/12/2001	0001	3
6	14/12/2002	0001	2

La requête suivante retourne la somme des qte sortie par produit :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT RefProduit,Sum(Qte) as sqte FROM tblCA
GROUP By Refproduit

La requête suivante donne la somme des produits par référence

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT Sum(Qte) FROM TblCa

On peut donc facilement avoir le pourcentage de chaque référence :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT RefProduit,Sum(Qte) * 100 / (SELECT Sum(Qte) FROM TblCa) as Pourcentage
FROM tblCA
GROUP By Refproduit

Pour ce qui est des groupes :

Plusieurs cas de figures :

- Une simple imbrication de IIF
- Une fonction VBA
- Une gestion plus souple avec la définition des bornes des groupes dans une tables. Là ce sera une solution Full SQL

**Kloun** · 12/02/2007, 13h46

Oui, ça c'est facile.

Sauf que c'est pas ça que je veux.

Là, on détermine le %age de CA total de chaque produit.

Ce que je veux c'est la liste des articles représentant 80% des ventes.
Donc, OK, il me faut les ventes par articles et les ventes totales.
Mais comment trouver la liste des top articles dont la somme des vente représente 80% de mes ventes totales (puis la liste des articles pour arriver à 95%, puis la liste des autres articles) ?

Dois-je en conclure que c'est pas simple ?

Après y avoir réfléchi, le seul truc que je vois, c'est la création d'une table, qui reprend la structure de la requête sur laquelle on veut faire le tri avec un champ en plus qui serait le classement.
Une fonction calculerait la valeur de ce champ de la façon suivante :
- Récupération du total de la requête pour le champ dont on veut le classement.
- Création d'un recordset équivalent à la requête, avec un classement par ordre décroissant selon le champ à classer.
- Traitement des lignes une à une, avec incrémentation d'une somme partielle sur le champ à classer. Tant que la somme est < 80% du total, on est sur du A, entre 80 et 95%, on est sur du B, sinon on est sur du C.
A chaque ligne, insertion de la ligne dans la table de résultat.

Un peu lourd, mais je vois rien d'autre.

**micniv** · 13/02/2007, 11h56

Bonjour ,
une solution en 2 requetes ... et sans doute une 3eme pour coller tes noms de categories :

la premiére r2 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT [article], [CA], CA/ (Select sum(CA) from ventes) AS [% CA]
FROM ventes
ORDER BY 3 DESC;

Puis la 2 eme construite sur la précédente :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT [article], [CA], [% CA], (SELECT sum([% CA]) from r2 RB WHERE RB.[% CA]>= RA.[% CA]) AS [%cumul]
FROM r2 AS RA;

reste tes etiquettes avec IIF
Bonne journée

**random** · 13/02/2007, 12h12

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT a.monca, Sum(b.monca) AS cumul, Sum([B].[monca]/(select sum(monca) as tot from matable)*100) AS [cumul%],
iif(Sum([B].[monca]/(select sum(monca) as tot from matable)*100)  <80,"a",iif(Sum([B].[monca]/(select sum(monca) as tot from matable)*100)  <90,"b","c"))  as tranche
FROM matable AS a, matable AS b
WHERE (((b.monca)>=[a].[monca]))
GROUP BY a.monca
ORDER BY a.monca DESC;

**micniv** · 13/02/2007, 14h02

Well done, master Random !

**Tofalu** · 13/02/2007, 14h07

Ce que je veux c'est la liste des articles représentant 80% des ventes.

Et alors ? La clause Having est fait pour ça non ?

Bref problème mal exprimé :

Mais comment trouver la liste des top articles dont la somme des vente représente 80% de mes ventes totales

Désolé, là je ne comprend rien

**Kloun** · 13/02/2007, 16h28

Je pense que la définition que j'ai donné pose bien le problème :

Analyse ABC (ABC) : Méthode de classement d'un référentiel par ordre décroissant des sorties. Cette méthode consiste à diviser le référentiel en trois groupes :
- le groupe A est composé des références constituant 80% des sorties (représentant généralement 20% des références),
- le groupe B est composé des références constituant 15% des sorties (représentant généralement 30% des références),
- et le groupe C est composé des références constituant 5% des sorties (représentant généralement 50% des références).

Cette méthode permet de connaître les références qui méritent une attention particulière.

Le classement peut également être effectué pour constituer des groupes en fonction d'autres critères tels que les volumes, les achats, les CA, etc.

La solution de random fonctionne sans doute. Mais j'ai tué le process au bout d'une heure ...

**micniv** · 13/02/2007, 16h44

La solution de random fonctionne sans doute. Mais j'ai tué le process au bout d'une heure ...

Combien de lignes as-tu dans ta table ?
Auparavant, as tu essayé de l'adapter sur une table de ... quelques lignes ?

**random** · 13/02/2007, 17h15

il y a moyen d'améliorer considérablement les performances,
encore faut il donner une idée de la volumétrie pour trouver une solution adaptée

**Kloun** · 13/02/2007, 17h36

Je confirme que la requête fonctionne. Merci Random

J'ai testé sur une partie de ma table (1000 lignes) et ça sort un résultat assez rapidement.
Mais ma table fait un peu plus de 30000 lignes.
Et là, le produit cartésien, ça fait mal.

**micniv** · 13/02/2007, 17h48

Déjà créer un index sur le CA ...

Et si pas suffisant, eclater en 2 requetes

**philben** · 14/02/2007, 07h02

bonjour,

le problème est que le classement ABC nécessite de calculer 30000 fois une somme sur 1 enregistrement jusqu'à 30000 enregistrements dans votre cas. Le temps nécessaire augmente exponentiellement avec le nombre d'enregistrements.

Soit une table source appellée <MaTable> et qui contient la colonne <Source> pour A1, A2,... et la colonne <CA> pour le chiffre d'affaire.

La requête ci-dessous détermine le classement ABC mais elle est utilisable lorsque le nombre d'enregistrement est faible (< 1000).
Comme chacun le sait, pour obtenir une meilleure performance, il vaut mieux indexer la ou les colonnes qui interviennent dans la clause WHERE

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
SELECT Alias.Source, Alias.CA, (SELECT SUM(CA) FROM MaTable WHERE CA>=Alias.CA) AS CurSom, (SELECT SUM(CA) FROM MaTable) AS 
TotSom, CurSom/TotSom AS Rapport, 
IIf([Rapport]<=0.8,"A",IIf([Rapport]<=0.95,"B","C")) AS Classement
FROM Matable AS Alias
GROUP BY Alias.Source, Alias.CA
ORDER BY Alias.CA DESC;

La meilleure solution que j'ai trouvé, est de créer une table que je nomme <MaTableABC> constituée de 2 colonnes : La colonne <Source> avec clef primaire et la colonne <ABC> - Texte de longueur 1.

Le principe est est de remplir cette table avec le classement ABC de la table <MaTable> via VBA par le code suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
 
Public Function DoABC() As Boolean
   On Error GoTo errortag
   Dim oDb As DAO.Database
   Dim oRs As DAO.Recordset
   Dim oRsABC As DAO.Recordset
   Dim dSumCA As Double, dCumCA As Double
   Dim sABC As String * 1
   Set oDb = CurrentDb
 
   oDb.Execute "DELETE * FROM maTableABC", dbFailOnError
   dSumCA = DSum("CA", "MaTable")
 
   Set oRs = oDb.OpenRecordset("SELECT Source,CA FROM MaTable ORDER BY CA DESC", dbOpenForwardOnly)
   Set oRsABC = oDb.OpenRecordset("MaTableABC", dbOpenTable)
   Do Until oRs.EOF
      dCumCA = dCumCA + oRs.Fields("CA")
      Select Case dCumCA / dSumCA
      Case Is <= 0.8
         sABC = "A"
      Case Is <= 0.95
         sABC = "B"
      Case Else
         sABC = "C"
      End Select
      oRsABC.AddNew
      oRsABC.Fields("Source") = oRs.Fields("Source")
      oRsABC.Fields("ABC") = sABC
      oRsABC.Update
      oRs.MoveNext
   Loop
   CalcSum = True
fin:
   Set oRs = Nothing
   Set oRsABC = Nothing
   Set oDb = Nothing
   Exit Function
errortag:
   MsgBox "Erreur n°" & Err.Number & vbCrLf & "Description : " & Err.Description, vbCritical, "Erreur..."
   Resume fin
End Function

Ce qui réclame moins de 2 secondes sur mon ordi pour 30000 enregistrements.

Puis la requête suivante permet d'afficher instantanément le résultat :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
SELECT MaTable.Source, MaTable.CA, MaTableABC.ABC
FROM MaTable INNER JOIN MaTableABC ON MaTable.Source = MaTableABC.Source
ORDER BY MaTable.CA DESC;

L'inconvénient de ce système est qu'après chaque modification des données de la table source, il faut relancer cette fonction VBA pour rafraichir le résultat.

Bon courage,

Philippe

**Kloun** · 14/02/2007, 08h59

Philben : j'étais parti sur la même idée d'une table de travail, avec quelques modification :
- l'utilisateur crée sa requête, sur n'importe quelle table, avec n'importe quels champs, la seule obligation étant d'avoir un champ dont le nom contient "AClasserABC".
- Il la sauvegarde avec un nom prédéfini, par exemple "A Classer ABC".
- La fonction crée une table de travail comportant les mêmes champs que la requête et ajoute un champ "Classement ABC" et fait ensuite la même chose que ta focntion DoABC.

Avantages :
- tout est sur un formulaire qui peut être importer dans n'importe quelle base
- Pas besoin d'être un pro du SQL pour utiliser le truc, il suffit de cliquer sur un bouton du formulaire pour avoir les résultats du classement, sur le périmêtre qui a été défini par l'utilisateur
Inconvénients :
Il y en a sans doute.

**random** · 14/02/2007, 09h15

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
 
Function borne(montant As Double, nomtable As String, nomchamp As String, tra As Single, trb As Single) As String
Static trana As Double
Static tranb As Double
Dim cuma As Double
Dim cumb As Double
Dim total As Double
Dim cumul As Double
If trana = 0 Then
Dim mabase As DAO.Database
Dim monrec As DAO.Recordset
Dim sql As String
Set mabase = CurrentDb()
sql = "select sum(" & nomchamp & ") as total from " & nomtable & ";"
Set monrec = mabase.OpenRecordset(sql)
total = monrec![total]
Debug.Print "total" & total
cuma = total * tra / 100
cumb = total * trb / 100
sql = "select " & nomchamp & " from " & nomtable & " order by " & nomchamp & " desc ;"
Set monrec = mabase.OpenRecordset(sql)
monrec.MoveFirst
Do Until cumul > cuma
cumul = cumul + monrec(nomchamp)
monrec.MoveNext
Loop
trana = monrec(nomchamp)
Debug.Print "trana :" & trana
Do Until cumul > cumb
cumul = cumul + monrec(nomchamp)
monrec.MoveNext
Loop
tranb = monrec(nomchamp)
Debug.Print "tranb " & tranb
Set mabase = Nothing
Set monrec = Nothing
End If
If montant < tranb Then
borne = "c"
Else
    If montant >= trana Then
    borne = "a"
    Else
    borne = "b"
    End If
End If
End Function