Rechercher une valeur max dans une plage de cellules variable

Version imprimable

Bonjour,
Voici tout d'abord une présentation du tableau dans lequel je doit travailler :

'     A       B        C   '
'   FAUX      0      MAX1  ' Ensenmble1
'   VRAI     10       10   ' Ensenmble1
'   VRAI     18        8   ' Ensenmble1
'   VRAI     30       12   ' Ensenmble1
'   VRAI     45       15   ' Ensenmble1
'   VRAI     61       16   ' Ensenmble1
'   VRAI     89       38   ' Ensenmble1
'   FAUX      0      MAX2  ' Ensenmble2
'   VRAI     15       15   ' Ensenmble2
'   VRAI     26       11   ' Ensenmble2
'   VRAI     47       21   ' Ensenmble2
'   VRAI     85       38   ' Ensenmble2
'   FAUX      0      MAX3  ' Ensenmble3
'   ...     ...     ...    ' Ensenmble3
'   ...     ...     ...    ' Ensenmble3

La colonne A est tout simplement un test pour savoir si l'unité en colonne C fait partie d'un même ensemble si « VRAI » on additionne si « FAUX » changement d'ensemble on repart de 0.
La colonne B est le résultat de l'addition des unités faisant partie d'un même ensemble.
Enfin en colonne C là où il y a changement d'ensemble donc « FAUX » en colonne A je dois fournir le résultat maximum de chaque ensemble.
C'est là mon problème, car selon les données entrées par l'utilisateur la taille d'un ensemble varie.
Comment pourrais-je obtenir alors les valeurs Max1, Max2, Max3... ?
Sachant que je ne sait pas non plus combien d'ensembles vont être créés.
Merci ;)

J'ai trouvé quelque chose qui pourrait être utile, en effet j'ai un problème similaire pour ce qui est de compter le nombre d’éléments par ensemble.
Et j'ai réussit à écrire un code qui fonctionne :

Code:

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
'Numérotation automatique par rapport à la dernière saisie et comptage du nombre d'items par ensemble
Private Sub Worksheet_Change(ByVal Target As Range)
 
If Target.Column = 4 And Target.Count = 1 Then
    If Target.Value <> "" And Right(Target.Offset(0, 1).Value, 3) <> "ENS" Then
        If Target.Offset(0, 6).Value = "" Then Target.Offset(0, 6).Value = Application.Max(Range("J:J")) + 1
    ElseIf Right(Target.Offset(0, 1).Value, 3) = "ENS" Then
        i = 1
        If (Right(Target.Offset(i, 1).Value, 3) <> "ENS") Then
            Target.Offset(0, 6).Value = 0
                While (Right(Target.Offset(i, 1).Value, 3) <> "ENS" And Target.Row < Range("D65536").End(xlUp).Row)
                    If (Target.Offset(i, 0).Value <> "") Then
                        Target.Offset(0, 6).Value = Target.Offset(0, 6).Value + 1
                    End If
                    i = i + 1
                Wend
        Else
            Target.Offset(0, 6).Value = 0
        End If
    Else
        Target.Offset(0, 6).ClearContents
    End If
End If
End Sub
 
'Sub
'Si une cellule Dx est sélectionnée en colonne D
    'Si l'on entre une valeur non nulle et que ce n'est pas la définition du S/E
        'Si la colonne J n'à pas déja de valeur affectée alors Jx = Max(J:J)+1 (indexation nouvel item)
    'Sinon si définition de l'ensemble
        'Initialisation variable comptage i
        'Si ligne suivante (i) n'est pas une définition d'ensemble
            'Initialisation valeur (Nb Items)
            'Tant que la ligne suivante n'est pas une définition d'ensemble et que l'on est pas en fin de tableau
                'Si cellule non vide (Item définit)
                    'Nombre items dans l'ensemble = Nb Items + 1
                'Fin Si
                'Incrémentation variable comptage i
            'Fin tant que
        'Sinon
            'Nombre d'items dans l'ensemble = 0
        'Fin Si
    'Autres cas
        'On efface Jx
    'Fin si
'Fin si
'Fin Sub

Je vais donc me pencher la dessus est essayer d'adapter ce code a mon autre problème de valeur Max.

Edit : Par contre il y a un souci ligne 11 lorsque j'arrive en fin de tableau je récupère bien le nombre d'items mais j'ai un message d'erreur : Erreur 1004 , erreur définie par l'application ou par l'objet. :calim2:.
Ca à l'air de venir du test :

Code:

Target.Row < Range("D65536").End(xlUp).Row

19/05/2011, 09h39
Daranc

une simple formule matricielle avec des plage nommées

Code:

=somme(si(plage_ensemble=$g1;plage_valeur;0))

nommer pLage_ensemble
Insertion
Nom
Définir
dans le Haut mettre plage ensemble
dans le champs du bas

Code:

=decaler(premier-cellulede la plage;0;0;nbval(premier-cellulede la plage: meme colonne ligne+1000;1)

ceci va creer une plage variable en fonction du nombre de données
si plage commence en C3

Code:

=decaler(feuil1!$C$3;0;0;nbval(feuil1!$C$3:$c$1000);1)

refaire la même chose pour les valeur ( $B$3 a la place de $C$3 et $B$100 etc)

en G1 ,G2,G3 tu met les valeurs que prennent les ensembles (ensemble1 , 2,..N)

ce qui doit te donner comme formule matricielle en f1

Code:

=somme(si(plage_ensemble =$g1;plage_valeur;0))

à valider par Ctrl+shift+entrée
tu tires la formule vers le bas

Merci, pas mal ta solution mais je me suis mal expliqué en faite Ensemble1,2,... n'existe pas je l'ai est ajoutés pour aider à la compréhension en fait le véritable tableau serait plutôt comme ceci :
|     A   |    B  |    C   |     D        |           E           |
|---------|-------|--------|--------------|-----------------------|
|   FAUX  |    0  |   MAX1 |     ENS      |    Nom de l'ensemble  |
|   VRAI  |   10  |    10  |   quantité   |        Pièce 1        |
|   VRAI  |   18  |     8  |   quantité   |        Pièce 2        |
|   VRAI  |   30  |    12  |   quantité   |        Pièce 3        |
|   VRAI  |   45  |    15  |   quantité   |        Pièce 4        |
|   VRAI  |   61  |    16  |   quantité   |        Pièce 5        |
|   VRAI  |   89  |    38  |   quantité   |       Pièce 6         |
|   FAUX  |    0  |   MAX2 |     ENS      |    Nom de l'ensemble  |
|   VRAI  |   15  |    15  |   quantité   |        Pièce 1        |
|   VRAI  |   26  |    11  |   quantité   |        Pièce 2        |
|   VRAI  |   47  |    21  |   quantité   |        Pièce 3        |
|   VRAI  |   85  |    38  |   quantité   |        Pièce 4        |
|   FAUX  |    0  |   MAX3 |     ENS      |    Nom de l'ensemble  |
|   VRAI  |    9  |     9  |   quantité   |        Pièce 1        |
|   ...   |   ... |   ...  |      ...     |          ...          |
Dommage sinon sa aurai été exactement ça :ccool:
Le problème c'est que l'utilisateur ne renseigne que le nom de l'ensemble et ne lui attribue pas de numéro.

J'ai corrigé mon exemple avec le nombre d'items par ensemble il fallait mettre en ligne 11 :

Code:

While (Right(Target.Offset(i, 1).Value, 3) <> "ENS" And Target.Offset(i, 0).Row <= Range("D65536").End(xlUp).Row)

J'attaque la recherche de valeur max maintenant

19/05/2011, 19h34
Daranc

malgrès tout je ne m'emm...rais pas avec VBA les formules peuvent faire le travail

Code:

=SI(B3=0; SOMME($B$2:B3)-SOMME($D$2:D2);0)

a tirer vers le bas , en face de chacun de tes Max tu auras la somme de l'ensemble , une mise en forme sur la colonne pour que les zéro de la colonne soit dans la couleur du fond (ou ne pas afficher les valeurs 0) , simple et sans macros