Formule de répartition.

CISCO

XLDnaute Barbatruc

• 25 Décembre 2012

• #2

Re : Formule de répartition.

Bonsoir

STP, pourrais-tu détailler davantage ta demande ?

@ plus

 

Vote positif 0 Vote négatif

J

JJ1

Guest

• 25 Décembre 2012

• #3

Re : Formule de répartition.

Bonsoir Cisco,

je voudrais trouver 5 tranches de répartition (ligne 2 )des nombres en ligne A:E qui seraient la meilleure (le plus de nombres qui s'adaptent aux tranches trouvées)
exemple simple en 2 tranches et 3 nombres:
10 11 30
9 31 10
11 30 31
les 2 meilleures tranches seraient de 9 à 11 et de 30 à 31 (c'est cette formule donnant de ...à que je cherche)
est ce plus clair?


merci
bonne soirée

 

Vote positif 0 Vote négatif

JCGL

XLDnaute Barbatruc

• 26 Décembre 2012

• #4

Re : Formule de répartition.

Bonjour à tous,

Peux-tu essayer avec :

Code:

=SOMMEPROD(($A4:$E4>=GAUCHE(F$2;2)*1)*($A4:$E4<=DROITE(F$2;2)*1))

Il est nécessaire de revoir les bornes (tu as des espaces intempestives)

A + à tous

 

Pièces jointes

• JC Classement.xls

  27 KB · Affichages: 44
• JC Classement.xls

  27 KB · Affichages: 49
• JC Classement.xls

  27 KB · Affichages: 52

Vote positif 0 Vote négatif

J

JJ1

Guest

• 28 Décembre 2012

• #5

Re : Formule de répartition.

Bonjour J Claude, cisco
je vais appliquer ta formule à mon fichier réel pour voir le résultat, mais ce soir car je bosse maintenant et mon chef ne serait pas tout à fait d'accord.......
Bon vendredi.
Merci

 

Vote positif 0 Vote négatif

J

JJ1

Guest

• 29 Décembre 2012

• #6

Re : Formule de répartition.

Bonjour à tous,

Jean Claude je viens de regarder le fichier que tu as complété.

En fait je recherche l'inverse (et c'est la difficulté ! ) : trouver ces fameuses bornes A B C D E
A < x
B entre x et y
C entre y+1 et z
D entre z+1 et u
E > u

je cherche donc les valeurs x y z et u donnant la meilleure répartition.

Ce n'est pas urgent, surtout en ce moment de fêtes !

Merci à toi.

 

Vote positif 0 Vote négatif

CISCO

XLDnaute Barbatruc

• 29 Décembre 2012

• #7

Re : Formule de répartition.

Bonjour à tous, bonjour JJ1 et jCGL

Et, dans ces conditions, à quoi servent les valeurs sur les lignes 4 et 5 dans ton fichier exemple ?

@ plus

 

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 29 Décembre 2012

• #8

Re : Formule de répartition.

Bonjour JJ1, CISCO, Jean-Claude,

Avec cette formule en F4 les espaces n'ont plus aucune importance :

Code:

=NB.SI($A4:$E4;"<="&SUPPRESPACE(STXT(F$2;TROUVE("à";F$2)+1;9)))-NB.SI($A4:$E4;"<"&SUPPRESPACE(GAUCHE(F$2;TROUVE("à";F$2)-1)))

A tirer à droite et vers le bas.

A+

 

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 29 Décembre 2012

• #9

Re : Formule de répartition.

Re,

Ou comme Jean-Claude avec SOMMEPROD :

Code:

=SOMMEPROD(($A4:$E4>=--SUPPRESPACE(GAUCHE(F$2;TROUVE("à";F$2)-1)))*($A4:$E4<=--SUPPRESPACE(STXT(F$2;TROUVE("à";F$2)+1;9))))

On gagne en effet quelques octets [Edit] 91 au lieu de 96.

A+

 

Dernière édition: 29 Décembre 2012

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 29 Décembre 2012

• #10

Re : Formule de répartition.

Re,

En fait avec la dernière formule pas besoin de SUPPRESPACE :

Code:

=SOMMEPROD(($A4:$E4>=--GAUCHE(F$2;TROUVE("à";F$2)-1))*($A4:$E4<=--STXT(F$2;TROUVE("à";F$2)+1;9)))

On passe à 85 octets.

A+

 

Vote positif 0 Vote négatif

J

JJ1

Guest

• 29 Décembre 2012

• #11

Re : Formule de répartition.

Rebonjour,

J'avoue ne pas comprendre à quoi sert la fonction sommeprod avec la suppressios de l'espace? (sinon créer la borne mais il faut trouver les valeurs optimales).

je pense m'$etre mal expliqué: ma base de données en A:E est grande avec des nombres allant de 1 à 100 (les nombres pouvant se répéter sur une ligne) et je cherche 5 bornes pour les répartir au mieux.
A B C D E (A avec un < à ) et E avec un > à pour plus de simplicité car au delà de 15 environ les valeurs sont éparses d'où le > à x

je mets un exemple de fichier.

Merci à vous.
Bonne soirée

 

Pièces jointes

• borne.xls

  68 KB · Affichages: 62
• borne.xls

  68 KB · Affichages: 56
• borne.xls

  68 KB · Affichages: 50

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 29 Décembre 2012

• #12

Re : Formule de répartition.

Re,

J'avais mal compris le problème.

En fait il faut faire un tri croissant pour chaque ligne du tableau.

Ci-joint 2 solutions, par formules seules et par VBA.

La solution VBA fait le tri avec cette fonction macro :

Code:

Function CLASLIG(plage As Range)
Dim tablo, ub%, i&, t, j%
tablo = plage 'matrice
ub = UBound(tablo, 2)
For i = 1 To UBound(tablo)
  t = Application.Index(tablo, i, 0)
  For j = 1 To ub
    tablo(i, j) = Application.Small(t, j) 'PETITE.VALEUR
  Next
Next
CLASLIG = tablo
End Function

A+

 

Pièces jointes

• Classement par formule(1).xls

  25.5 KB · Affichages: 49
• Classement par vba(1).xls

  38.5 KB · Affichages: 64

Dernière édition: 29 Décembre 2012

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 29 Décembre 2012

• #13

Re : Formule de répartition.

Re,

J'ai édité mon post précédent car les fichiers étaient erronés.

Ci-joint le fichier borne avec la solution VBA.

A+

 

Pièces jointes

• borne par VBA(1).xls

  174 KB · Affichages: 68

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 29 Décembre 2012

• #14

Re : Formule de répartition.

Re,

Je crois comprendre ce que JJ1 entend par meilleure répartition :

Les nombres d'items des 5 classes doivent être les plus voisines possibles.

Cliquez pour agrandir...

Alors cette formule en F2 à tirer vers la droite :

Code:

=SI(COLONNES($F:F)=1;MIN($A2:$E608);STXT(E2;TROUVE("à";E2)+1;9)+1)&" à "&PETITE.VALEUR($A2:$E608;ENT(NB($A2:$E608)*COLONNES($F:F)/5))

Bien entendu l'égalité parfaite est impossible.

Noter que VBA est ici totalement inutile.

Fichier (2).

Edit : eh oui Jean-Claude, et je viens encore de simplifier le calcul des minima 🙂

A+

 

Pièces jointes

• borne par formule(2).xls

  90.5 KB · Affichages: 48

Dernière édition: 29 Décembre 2012

Vote positif 0 Vote négatif

JCGL

XLDnaute Barbatruc

• 29 Décembre 2012

• #15

Re : Formule de répartition.

Bonjour à tous,

Mon bon Gérard, j'adore ta propension à faire les demandes et les réponses...

Au plaisir de lire encore longtemps tes "remises sur l'ouvrage"

A++ l'ami
A + à tous

 

Vote positif 0 Vote négatif