XL 2013 Amalgame de données

[BER2D2]

Bonjour à tous,

Je suis nouveau sur ce forum et j'ai besoin d'aide.
Mon problème est le suivant :
J'ai un tableau avec une colonne représentant des noms de villes et une autres des quantités liés a ces villes.
A coté de ce tableau j'ai 2 données (H5=le palier maxi et H6=le nombre d'amalgame maxi)

A B
Ville Quantité
Ville 1 10 000
Ville 2 11 000
Ville 3 12 000
Ville 4 10 000
Ville 5 20 000
Ville 6 22 000
Ville 7 20 000
Ville 8 21 000
Ville 9 30 000
Ville 10 20 500
Ville 11 22 000
Ville 12 40 000
Ville 13 41 000
Ville 14 50 000
Ville 15 43 000
Ville 16 40 500

H5 : 3000
H6: 4

Sachant que H5 peut varier, H6 peut aussi varié et le nombre de ville aussi .... dans mon exemple il ni en a que 16

L'objectif est de pouvoir :
1 - trouver la quantité mini -> ça c'est ok
2 - amalgamer (mettre ensemble) par H6 maximum le nombre de ville dont les quantité sont égale a H5 prêt.
Exemple, ici on devrait avoir :
Amalgame 1 : Ville 1 - 10 000, Ville 4 - 10 000, Ville 2 - 11 000, Ville 3 - 12 000
Amalgame 2 : Ville 5 - 20 000, Ville 7 - 20 000, Ville 10 - 20 500, Ville 8 - 21000
Amalgame 3 : Ville 6 - 22 000, Ville 11 - 22 000
Amalgame 4 : Ville 9 - 30 000
Amalgame 5 : Ville 12 - 40 000, Ville 16 - 40 500, Ville 13 - 41 000, Ville 15 - 43 000
Amalgame 6 : Ville 14 - 50 000

Dans cette exemple, si H6 avait été égale à 6 , Amalgame 2 aurait été : Ville 5 - 20 000, Ville 7 - 20 000, Ville 10 - 20 500, Ville 8 - 21000, Ville 6 - 22 000, Ville 11 - 22 000
et il ni aurait eut que 5 amalgames au lieu de 6

Est ce que quelqu'un permis vous est capable de m'expliquer comment je pourrais faire ça avec des formules Excel ?

Merci d'avance pour votre aide

 

[djidji59430]

Bonjour à tous,

h6, ça s'exprime en quelle unité ?
Quelle en est sa définition ?


Crdlmt

 

[BER2D2]

Bonjour à tous,

h6, ça s'exprime en quelle unité ?
Quelle en est sa définition ?


Crdlmt

Bonjour
H6 est toujours un chiffre compris entre 2 et 8

 

[job75]

Bonjour BER2D2, djidji59430,

Moi c'est sur H5 que je m'interroge, est-ce :

- l'écart maximum entre 2 quantités successives d'un même amalgame

- ou l'écart maximum entre la 1ère et la dernière quantité de l'amalgame ?

A+

 

[JHA]

Bonjour à tous,

Un premier essai par formules

JHA

 

[BER2D2]

Bonjour BER2D2, djidji59430,

Moi c'est sur H5 que je m'interroge, est-ce :

- l'écart maximum entre 2 valeurs successives d'un même amalgame

- ou l'écart maximum entre la 1ère et la dernière valeur de l'amalgame ?

A+

Bonjour Job75,

H5 et l'écart maxi entre la 1er et la dernière valeur de l'amalgame
Si 1er valeur = 10 000 et H5 =3 000, on pourra trouver dans l'amalgame les valeurs entre 10 000 et 13 000

Merci

 

[BER2D2]

Bonjour à tous,

Un premier essai par formules

JHA

Bonjour JHA,

Merci pour ton 1er essai
Par contre j'ai un peu du mal a comprendre le raisonnement, car je ne vois pas toutes les données, exemple C11 n’apparaît jamais, et le chiffre 10 000 y est 3 fois alors que je ne l'ai que 2 fois dans les données

 

[JHA]

Bonjour à tous,

Avec ce que je comprends.

JHA

 

[BER2D2]

Bonjour à tous,

Avec ce que je comprends.

JHA

Wouaw c'est plutot pas mal ca
Le seul truc c'est que :
1 - je ne dois pas pouvoir faire de tri sur la colonne B, mais la prendre tel quelle est
2 - la cellule H8 doit être paramétrable manuellement ..... entre 2 est 8 ... c'est ce que j'appelais "nombre d'amalgame maxi"

Mais sinon le principe est la vous êtes super balaise

 

[JHA]

Bonjour à tous,

Sans le tri, ce sera difficile par formules, peut-être par VBA mais je passe la main aux pros du code.

JHA

 

[BER2D2]

Bonjour à tous,

Sans le tri, ce sera difficile par formules, peut-être par VBA mais je passe la main aux pros du code.

JHA

Justement, j'aimerai éviter le VBA ....... merci en tout cas, c'est une super base

 

[djidji59430]

Re
A mon avis, h5 et h6 sont forcement liés.
Si tu prends H5=8000, et tu veux 4 amalgames, on fait comment ?

 

[BER2D2]

Re
A mon avis, h5 et h6 sont forcement liés.
Si tu prends H5=8000, et tu veux 4 amalgames, on fait comment ?

En fait je me suis peu être mal exprimer du coup ..... si tu indiques 4 amalgames, ça signifie qu'il faut maxi 4 quantités par ligne ... si H5 = 8000 ou plus, tu créer des lignes supplémentaires avec a chaque fois maxi 4 amalgames par ligne

 

[job75]

Re, salut JHA,

Pour ceux que le VBA n'effraie pas :

Sub Resultat()
Dim ecart#, nombre%, tablo, ub&, resu(), deb&, maxi#, i&, j%, n&
ecart = [H5]: nombre = [H6] 'à adapter
Application.ScreenUpdating = False
With [Tableau1] 'tableau structuré
    .Sort .Columns(2), xlAscending, Header:=xlYes 'tri sur les quantités
    tablo = .Resize(, 2) 'matrice, plus rapide
    .Sort .Columns(1), xlAscending 'tri sur les villes
End With
ub = UBound(tablo)
ReDim resu(1 To ub, 1 To 1)
deb = 1
1 maxi = tablo(deb, 2) + ecart
For i = deb To ub
    If tablo(i, 2) <= maxi And j < nombre Then
        j = j + 1
        If j = 1 Then n = n + 1: resu(n, 1) = "Amalgame " & n & " : "
        resu(n, 1) = resu(n, 1) & IIf(j = 1, "", ", ") & tablo(i, 1) & " - " & tablo(i, 2)
    Else
        j = 0
        deb = i
        GoTo 1
    End If
Next
'---restitution---
With Feuil1.[D8] 'CodeName de la feuille et cellule à adapter
    If n Then .Resize(n) = resu
    .Offset(n).Resize(.Parent.Rows.Count - n - .Row + 1).ClearContents 'RAZ en dessous
End With
End Sub

A+

 

[BER2D2]

Re, salut JHA,

Pour ceux que le VBA n'effraie pas :

Sub Resultat()
Dim ecart#, nombre%, tablo, ub&, resu(), deb&, maxi#, i&, j%, n&
ecart = [H5]: nombre = [H6] 'à adapter
Application.ScreenUpdating = False
With [Tableau1] 'tableau structuré
    .Sort .Columns(2), xlAscending, Header:=xlYes 'tri sur les quantités
    tablo = .Resize(, 2) 'matrice, plus rapide
    .Sort .Columns(1), xlAscending 'tri sur les villes
End With
ub = UBound(tablo)
ReDim resu(1 To ub, 1 To 1)
deb = 1
1 maxi = tablo(deb, 2) + ecart
For i = deb To ub
    If tablo(i, 2) <= maxi And j < nombre Then
        j = j + 1
        If j = 1 Then n = n + 1: resu(n, 1) = "Amalgame " & n & " : "
        resu(n, 1) = resu(n, 1) & IIf(j = 1, "", ", ") & tablo(i, 1) & " - " & tablo(i, 2)
    Else
        j = 0
        deb = i
        GoTo 1
    End If
Next
'---restitution---
With Feuil1.[D8] 'CodeName de la feuille et cellule à adapter
    If n Then .Resize(n) = resu
    .Offset(n).Resize(.Parent.Rows.Count - n - .Row + 1).ClearContents 'RAZ en dessous
End With
End Sub

A+

La vache !!!! vous êtes des dieux pour moi !!!!! C'est impeccable !!!! Merci merci merci merci