Connaître les couples de produits qui se vendent ensemble

Bonjour à toutes et à tous,

Attention cette question me semble assez complexe et nécessite un très bon niveau d'excel (voir de gestion de bdd)

J'ai une base de données (excel) des articles commandés avec le n° de commande sur la ligne, ainsi que la quantité, comme ceci :

n° cmd | n° produit | qte

Je cherche à identifier les couples (de deux ou plus de 2 articles) qui se vendent le plus ensemble au sein d'une même commande.

Exemple :

n°cmd | n°produit | qte

-----------------------------

1 | A | 1

1 | B | 1

1 | C | 1

2 | C | 1

2 | A | 1

3 | A | 1

3 | C | 1

Résultats : Les couples les plus vendues ensemble :

1 - Couple AC : avec 3 counts

2 - Couple ABC : avec 1 count

Je cherche à effectuer cet exercice via un automatisme (formule excel ou macro) car j'ai une base de données de plus de 100.000 lignes.

Qu'en pensez-vous ? Est-ce possible avec Excel ? Suis-je obligé de le traité informatiquement via du SQL en injectant les données dans une BDD ?

Merci d'avance pour vos pistes et vos idées !

Bonjour Son1.

Avec une macro ça doit être réalisable.

Il nous faudrait un exemple de ton fichier, avec un nombre suffisant de situations, sans données sensibles.

Bonne idée ! Voici un exemple de jeu de données en pièce jointe

Sachant que le résultat devrait être :

Couples :

TOP1 : A1-A2 , COUNT = 4

TOP2 : B1-B2-B3, COUNT = 2

TOP3 : B1-B2, COUNT = 2

Les produits numériques ne sont pas à prendre en compte ?

Sisi, tout type de référence produit est à prendre en compte.

Bonjour

Avec PowerQuery

48topcouples.xlsx (28.77 Ko)

Bonsoir,

Merci pour votre résultat, mais malheureusement il ne donne qu'une partie infime de la réponse.

Je cherche les couples = 2 ET supérieur à 2 (du coup je ne sais pas si couple est le bon terme...).

Mais je cherche à identifier les top couples solutions : dans votre solution vous proposez seulement les binomes (ce que je savais déjà faire).

Dans votre résultat le TOP Couple : B1-B2-B3 n'est pas connu. (On pourrait imaginer un couple : x^n : B1-B2-B3-B4-B5-...-Bn)

Avez-vous peut-être une piste pour avoir ce résultat ?

Merci encore pour votre travail.

Re

Par requêtes, cela nécessite autant de requêtes que de nombres à associer : 2 pour les couples, 3 pour les trios etc.

VBA dans ce cas est sans doute aussi bien.

Oui je vois aussi cette solution là, mais ce n'est pas non plus optimale car en faisant ça on va casser l'ordre du top couple.

Nous allons dire que le couple A1-A2 est TOP1 couple de la solution dans la première requête et que B1-B2-B3 est le TOP14 couple solution de l'autre requête, or le bon résultat est TOP1 : A1-A2, et TOP2: B1-B2-B3.

Désolé de pousser un peu le bouchon un peu loins, mais l'exercice est complexe ^^. (et il n'est peut être pas réalisable dans Excel)

Bonjour 78chris.

Bonjour Son1.

J'imagine le traitement ainsi :

. Enregistrement dans un tableau virtuel de tes données (traitement beaucoup plus rapide).

. Boucle du tableau sur les n° de commandes.

. Pour chaque commande, tri alphabétique/numérique des produits (pour toujours conserver le même ordre de couple).

. Incrémentation des items du dictionnaire. Les clefs étant les couples, à chaque nouvelle apparition du couple on augmente d'un.

Je pense qu'il doit exister des procédures déjà réalisées pour sortir toutes les probabilités.

Une fois une procédure de ce type trouvée, ou créée (mais j'avoue ne pas être le plus apte à la réaliser), nous pourrions trouver une réponse.

Une fois la boucle terminée, nous exportons la liste des clefs du dictionnaire (les couples des produits) avec leurs items (nombre de commandes de ces couples).

Bonjour thebenoit59 !

Super idée ! C'est ce que je commencais à imaginer de mon côté vu la complexité du problème.

J'ai les compétences techniques pour réaliser ce genre d'algo, j'imaginais soit :

  • Un traitement en PL/SQL avec création d'une table dédiée aux couples "trouvés" pour incrémenter le dictionnaire, puis une recherche dans toute les données de ce nouveau couple
    Un traitement en JAVA avec des List et des Map<clé;valeur>

La solution en Java me paraît la plus rapide à réaliser, cependant j'ai peur de la performance, car j'ai 1 million de ligne à traiter dans ma BDD... Niveau PL/SQL cela devrait aller plus vite mais j'ai quand même peur des perfs ! à voir !

Donc à chaque création de couple : couples qui peuvent être au nombre de 500.000 (nombre un peu donné au pif : faut que je le calcul), je dois rechercher dans 1 million de lignes.

Donc on va avoir 500.000 dans 1 million de lignes.

Je ne connais rien en SQL, ni en Java.

Je ne pourrai pas t'aider.

Pas de problèmes je ne demande pas d'aide à ce niveau là !

L'aide m'a déjà été apporté en me confortant dans le choix d'attaquer le problème via des dictionnaires (clé-valeur).

Merci beaucoup à tous pour votre aide.

J'écrirais ici le fonctionnement final. (si ça fonctionne ^^)

Bonjour

Son1 a écrit :

Oui je vois aussi cette solution là, mais ce n'est pas non plus optimale car en faisant ça on va casser l'ordre du top couple.

Nous allons dire que le couple A1-A2 est TOP1 couple de la solution dans la première requête et que B1-B2-B3 est le TOP14 couple solution de l'autre requête, or le bon résultat est TOP1 : A1-A2, et TOP2: B1-B2-B3.

Désolé de pousser un peu le bouchon un peu loins, mais l'exercice est complexe ^^. (et il n'est peut être pas réalisable dans Excel)

Non car c'est une requête plus une union qui peut être classée.

Mais je préfère jouer à cela dans un SGBD que dans EXCEL.

Je vais le faire un SGBD également... en me servant d'une nouvelle table comme d'un dictionnaire. J'ai peur que le faire en Java avec des dictionnaires, ça soit trop lent... !

Je pense également que c'est en fait faisable avec une belle requête ! Mais je me suis déjà cassé la tête des jours sur ce genre de requête alors qu'un bloc PL/SQL qui donne le même résultat est fait en beaucoup moins de temps... !

thebenoit59 a écrit :

Je pense qu'il doit exister des procédures déjà réalisées pour sortir toutes les probabilités.

Une fois une procédure de ce type trouvée, ou créée (mais j'avoue ne pas être le plus apte à la réaliser), nous pourrions trouver une réponse.

bonjour,

procédure pour générer toutes les combinaisons pour les éléments d'un tableau donné. attention devient vite très lent (>12 éléments dans le tableau)

Function combine(v)
' retourne un tableau avec toutes les combinaisons des éléments du tableau V
    Dim c()    'tableau des combinaisons
    genere v, c, s
    combine = c
End Function
Sub genere(ByRef v, ByRef c, ByRef s, Optional niveau = 1, Optional fi = -1, Optional ts)
' procédure recursive qui génére toutes les combinaisons des éléments du tableau v
' v tableau des valeurs
' c tableau des combinaisons
' s nombre de combinaisons
' niveau
' fi = première valeur pour la boucle i
' ts tableau des valeurs en cours

    Dim tst()    'tableau de la combinaison en cours triée
    If fi = -1 Then fi = LBound(v) - 1
    li = UBound(v)
    lin = UBound(v) - LBound(v) + 1
    If niveau = 1 Then
        ReDim ts(1 To lin)
    Else
        ReDim Preserve ts(1 To lin)
    End If
    ReDim tst(1 To lin)
    For i = fi + 1 To li
        ts(niveau) = v(i)
        s = s + 1
        ReDim Preserve c(s)
        tst = ts
        For i1 = 1 To niveau - 1
            For j1 = i1 + 1 To niveau
                If tst(j1) < tst(i1) Then a = tst(j1): tst(j1) = tst(i1): tst(i1) = a
            Next j1
        Next i1
        For i1 = 1 To niveau
            c(s) = c(s) & IIf(c(s) <> "", "-", "") & tst(i1)
        Next i1
        If niveau < lin Then
            genere v, c, s, niveau + 1, i, ts
        End If
        ts(i) = ""
    Next i
End Sub

Sub test()
'exemple d'appel de la fonction
    Dim a(1 To 5)
    a(1) = "A"
    a(2) = "B"
    a(3) = "C"
    a(4) = "D"
    a(5) = "E"
    sol = combine(a)
    Cells(1, 1).Resize(UBound(sol) - LBound(sol) + 1, 1) = Application.Transpose(sol)
End Sub

re-bonjour,

utilisation de la fonction pour le problème posé

Sub aargh()
    With Sheets("sheet1")
        dl = .Cells(Rows.Count, 1).End(xlUp).Row
        Set dico = CreateObject("scripting.dictionary")
        cmd = ""
        For i = 2 To dl + 1
            If .Cells(i, 1) <> cmd Then
                If cmd <> "" Then
                    v = Application.Transpose(.Range("C" & fr & ":C" & lr))
                    If IsArray(v) Then
                        sol = combine(v)
                        For j = 1 To UBound(sol)
                            If dico.exists(sol(j)) Then
                                dico(sol(j)) = dico(sol(j)) + 1
                            Else
                                dico.Add sol(j), 1
                            End If
                        Next j
                    Else
                        If dico.exists(v) Then
                            dico(v) = dico(v) + 1
                        Else
                            dico.Add v, 1
                        End If
                    End If
                End If
                fr = i
                cmd = .Cells(i, 1)
            End If
            lr = i
        Next i

        Sheets.Add
        Cells(1, 1).Resize(dico.Count) = Application.Transpose(dico.keys)
        Cells(1, 2).Resize(dico.Count) = Application.Transpose(dico.items)
        Range("A1:B" & dico.Count).Sort key1:=Range("B1"), order1:=xlDescending, Header:=xlNo
    End With
End Sub
h2so4 a écrit :

bonjour,

procédure pour générer toutes les combinaisons pour les éléments d'un tableau donné. attention devient vite très lent (>12 éléments dans le tableau)

Wohw ! Sacré niveau de VBA ^^ ! Merci infiniment pour l'algo, je vais l'adapter en PL/SQL pour des problèmes de performance... (en fait je travaille sur une DB de 5 millions de ligne... !)

Petite question : si dans votre algo je veux seulement les couples > à 1, c'est possible en changeant un paramètre ?

Pour A, B, C, seulement avoir les solutions :

A-B

A-B-C

A-C

B-C

(par exemple).

Rechercher des sujets similaires à "connaitre couples produits qui vendent ensemble"