Fusionner plusieurs listes de nombres afin d'établir un classement général de ces nombres

Je donne ci-dessous pour une meilleure compréhension le fichier de base de ma question :

https://www.cjoint.com/c/MAzkowrAepA 

pelletim

Bonjour pelletim,

ton fichier en retour : https://www.cjoint.com/c/MAAbBMP1i10

1) pour ta colonne "moyenne" : en AM32, ta formule était :

=SOMME(C32+D32+E32+F32+G32+H32+I32+J32+K32+L32+M32+N32+O32+P32+Q32
+R32+S32+T32+U32+V32+W32+X32+Y32+Z32+AA32+AB32+AC32+AD32+AE32+AF32
+AG32+AH32+AI32+AJ32)/34

tu peux commencer par la simplifier ainsi : =SOMME(C32:AJ32)/34

mais il y a encore plus simple : =MOYENNE(C32:AJ32)

j'ai mis un arrondi avec 2 chiffres après la virgule
➯ formule en AM32 (étirée ensuite jusque AM48) :
=ARRONDI(MOYENNE(C32:AJ32);2)

résultats : mêmes moyennes qu'avant (sauf que c'est arrondi).

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

2) à droite, j'ai ajouté une colonne au tableau "classement" : c'est maintenant la plage
AO31:AQ48 ; à part l'en-tête, ce tableau est vide : il n'y a aucune donnée.

remonte au-dessus pour voir la plage sur fond vert AM12:AM28 : elle est vide aussi :
il n'y a aucune donnée.

redescend en dessous pour voir en entier tout le tableau "classement" ; fais Ctrl k
➯ travail effectué ; vérifie les données en AO32:AQ48 ; les nombres rouges ont
été copiés sur la plage verte AM12:AM28.

code VBA de Module1 :

Option Explicit

Sub SetClassement()
  Dim v#, r As Byte, i As Byte: Application.ScreenUpdating = 0
  For i = 1 To 17
    r = i + 31
    With Cells(r, "AO")
      v = .Offset(, -2)
      .Value = WorksheetFunction.Rank(v, [AM32:AM48], 1)
      .Offset(, 1) = i: .Offset(, 2) = v
    End With
  Next i
  [AO32:AQ48].Sort [AO32]
  [AP32:AP48].Copy: [AM12].PasteSpecial -4163
  Application.CutCopyMode = 0: [AO12].Select
End Sub

rhodo

Ce que tu m'as fait est excellent et je t'en remercie. 

 Il y a une opération que j'aimerais rendre automatique par une formule, macro ou autre : c'est la confection du tableau des positions de chaque nombre dans les listes. Ce serait formidable si lorsque je remplie le tableau de base (lignes 12 à 28 - colonnes  C à AJ) le tableau du dessous "positions de chaque nombre dans les listes" se remplisse simultanément. Je précise que le tableau de base est sans cesse modifié et que le travail de recherche des positions des nombres est assez long.

Est-ce possible ?

pelletim

Bonjour pelletim,

merci pour ton retour.  :)  voici la version v2 :

https://www.cjoint.com/c/MAEeZ1Un8T0

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

1) tu peux voir que la plage C32:AJ48 est entièrement vide ; pour cette raison,
j'ai dû modifier la formule de AM32 pour éviter le message d'erreur #DIV/0! ;
nouvelle formule en AM32 (étirée ensuite jusque AM48) :

=SIERREUR(ARRONDI(MOYENNE(C32:AJ32);2);"")

ce que j'ai ajouté apparaît en gras (début et fin).

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

2) monte au-dessus pour voir la plage sur fond vert AM12:AM28 : elle est vide :
il n'y a aucune donnée.

descend en dessous pour voir en entier tout le tableau "classement" ; fais Ctrl k
➯ la plage C32:AJ48 des positions a été remplie automatiquement ; la colonne
"moyenne" indique les bonnes moyennes ; le tableau "classement" a été rempli
(plage AO32:AQ48), ainsi que la plage du classement général (sur fond vert).

Attention

pour le fichier précédent, je n'avais pas vérifié tes données de position ! or il se
trouve que tu avais fait quelques erreurs, ce qui explique que les résultats de
ce fichier sont un peu différents ! d'où : moyennes différentes, et classement
général différent !  ;)

tu as écrit : « le tableau de base est sans cesse modifié » ; note bien que faire
les calculs après chaque modification d'un nombre n'a pas vraiment de sens !
c'est pourquoi procède ainsi : fais d'abord dans le tableau de base toutes les
modifications nécessaires ; et ensuite, quand tout est ok, fais Ctrl k.

remarque : la modification d'une seule liste (parmi les 34 listes) peut modifier
la colonne "moyenne", et donc le classement ; c'est pour ça qu'il faut faire
Ctrl k en tout dernier (après que les 34 listes sont ok).

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

code VBA de Module1 :

Option Explicit

Sub SetClassement()
  Dim v#, r As Byte, i As Byte, j As Byte
  Application.ScreenUpdating = 0: Application.Calculation = -4135
  For i = 3 To 36
    For j = 1 To 17
      r = Cells(j + 11, i): Cells(r + 31, i) = j
    Next j
  Next i
  Application.Calculation = -4105
  For i = 1 To 17
    r = i + 31
    With Cells(r, "AO")
      v = .Offset(, -2)
      .Value = WorksheetFunction.Rank(v, [AM32:AM48], 1)
      .Offset(, 1) = i: .Offset(, 2) = v
    End With
  Next i
  [AO32:AQ48].Sort [AO32]
  [AP32:AP48].Copy: [AM12].PasteSpecial -4163
  Application.CutCopyMode = 0: [AO12].Select
End Sub

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

à te lire pour avoir ton avis.  :)

rhodo

Bonjour rhodo,

Ton calcul des classements et des moyennes est très judicieux et je te remercie pour ce résultat et aussi pour les corrections que tu y as apportées.

Cependant, comme je te l'ai dis précédemment, c'est au niveau de la confection du tableau inférieur, celui des positions des nombres dans chaque liste. Existe-t-il un moyen de le remplir automatiquement ? Je m'explique : lorsque l'on remplit le tableau de base, chaque liste peut-elle être convertie dans le tableau du bas en liste des positions des nombres ? Cela peut-il se faire automatiquement ? 

J'ai cherché si une formule EXCEL pouvait convenir pour ce travail. Je n'ai rien trouvé, mais je ne suis pas un expert......

pelletim

@pelletim

EDIT du 02/02/23 à 04:20

finalement, j'ai supprimé la version v3 ; je l'ai remplacée par la version v4,
qui est dans mon post du 31 janvier à 11:08 (voir plus bas).

rhodo

Ah oui, effectivement !  Je ne pensais pas que la chose était si compliquée ...

Je regarde tout cela de près et je te tiens au courant.

Merci encore pour ton travail fabuleux.

pelletim

Tu n'aurais pas oublié de joindre le nouveau  tableau modifié (version 3) par hasard ?

pelletim

@pelletim

désolé, mais ça a été beaucoup plus compliqué que prévu !  :(

EDIT du 02/02/23 à 04:20

finalement, j'ai supprimé la version v3 ; je l'ai remplacée par la version v4,
qui est dans mon post du 31 janvier à 11:08 (voir plus bas).

rhodo

rhodo tu as fait un travail remarquable.

J'ai testé les tableaux. Je n'utiliserai que la version 2 qui fonctionne à merveille. Dans mes listes, il ne pourra jamais y avoir de doublon. Par conséquent cette version fera l'affaire. Merci pour l'info des raccourcis "Ctrl r " et  "Ctrl k" que je ne connaissais pas.

Un grand merci pour ce que tu as fait et le temps que tu y as passé. J'espère que ce post servira à d'autres usagers d'EXCEL. Encore bravo !

pelletim

Bonjour pelletim,

merci pour ton retour ;  j'ai supprimé la version v3 ; voici la v4 (qui est très fiable) :

https://www.cjoint.com/c/MAFjcMEHnr0

note : la v2 écrit les positions "après-coup", quand toutes les données ont été saisies en
C12:AJ28 ; la v4 écrit les positions au fur et à mesure pour une seule colonne à la fois,
celle où on saisit ou on modifie une donnée dans la plage C12:AJ28.

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

1) attention : la ligne 29 est très importante ! ne rien modifier !

pour la 1ère liste "Liste 1" :

a) la plage de cellules correspondantes pour la saisie est : C12:C28.

b) tu dois modifier une de ces cellules.

* une modif dans C12:C28 fait que ça efface la plage AM12:AM28, AO32:AQ48 ;
plus tard, il faudra faire Ctrl k quand les 34 listes seront pleines ET quand AO29
sera à 0 ; pour AO29, ça va être décrit au point d).

* si tu vois un "#" en C29, ça signifie que la plage C12:C28 n'est pas complète :
il manque un des nombres 1 à 17, donc il y a au moins une cellule vide dans
C12:C28 ; mais au départ, quand le tableau entier des données est vide :
C12:C28 est vide, sans "#" en C29 (ce qui ne gênera pas du tout).

* si tu effaces un nombre avec la touche Suppression : "#" en C29, et toutes
les positions de C32:C48 sont effacées (ça sera écrit de nouveau lors de la
prochaine saisie d'un nombre valide ; c'est grâce à cela que c'est très fiable,
même quand tu remplaceras un nombre valide par un autre nombre valide).

* si tu saisis une autre valeur que 1 à 17 (par exemple 20) : nb refusé et effacé
& "#" en C29 ; là aussi, toutes les positions de C32:C48 sont effacées (ça sera
écrit de nouveau lors de la prochaine saisie d'un nombre valide) ; idem pour
une valeur qui ne tient pas dans la plage d'un byte (ex : 300) ; rappel : pour
un byte (octet), la plage des nombres est : de 0 à 255.

* si tu saisis un doublon : nb refusé et effacé & "#" en C29 ; et là aussi, toutes
les positions de C32:C48 sont effacées (ça sera écrit de nouveau lors de la
prochaine saisie d'un nombre valide) ➯ y'a plus aucun problème concernant
les doublons vu qu'ils ne sont pas acceptés.  :)  de plus, si par exemple tu as
mis 5 en C18 et que tu veux le déplacer ailleurs (dans C12:C28), commence
par effacer ce 5 avant de le saisir de nouveau (sinon : doublon refusé).

* voici quand il n'y a pas de "#" en C29 (et donc pas d'erreur) : il n'y a pas de
cellule vide dans C12:C28 ; chaque cellule de C12:C28 contient un nombre ;
ce nombre est compris entre 1 et 17 inclus ; il n'y a pas de répétitions, donc
comme chaque nombre est unique, ça signifie qu'il y a les nombres 1 à 17
dans C12:C28 (quelque soit leur ordre) ; bien sûr, la somme de 1 à 17 est
toujours 153 (c'était le résultat de ton ancienne formule de C51).

* si un nombre saisi en C12:C28 est correct, ça met ce qu'il faut en C32:C48
(c'est-à-dire les positions pour chaque nombre de C12:C28) ; c'est très
fiable dans tous les cas (même pour un remplacement).

c) à droite de la colonne C (colonnes D à AJ), c'est pareil pour toutes
les autres colonnes de listes (Liste 2 à Liste 34).

d) en AO29 : =NBVAL(C29:AJ29) ; ça retourne le nombre de "#" de la ligne ;
attention : ce nombre sera testé par la sub SetClassement() ; il ne faut donc
surtout pas effacer cette formule !

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

2) quand y'a aucun "#" dans C29:AJ29 :

* toutes les colonnes de données (C12:AJ28) sont bonnes : y'a aucune erreur.
* la plage C29:AJ29 est vide, et AO29 contient 0.

* Ctrl k ➯ ça remplit le tableau "classement" et la plage sur fond vert si le nombre de
données de C12:AJ28 = 578 (car 34 listes × 17 nbs = 578 nbs) ET si AO29 = 0.

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

3) quand y'a un "#" ou plusieurs "#" dans C29:AJ29 :

* une des colonnes au moins de C12:AJ28 n'est pas complète et AO29 > 0.

* Ctrl k ➯ ça vide le tableau "classement" et la plage sur fond vert si le nombre de
données de C12:AJ28 est différent de 578 (= 34 listes × 17 nbs) OU si AO29 > 0.

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

4) à propos de tout ce que j'ai décrit au point 1) :

pour que ça marche correctement, les événements d'Excel doivent être activés,
ce qui est déjà le cas par défaut ; mais si jamais ça n'agit pas comme d'habitude,
c'est probable que les événements ont été désactivés (en cas de plantage ou de
mauvaise manipulation) ; dans ce cas, fais Ctrl r pour réactiver les événements
d'Excel : ça appelle la sub Réactiv().

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

5) code VBA du module de Feuil1 (40 lignes) ; comme les doublons sont refusés,
j'ai pu faire plusieurs simplifications ; j'ai aussi ajouté plusieurs commentaires.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  On Error GoTo 1 'c'est pour une valeur hors byte
  Dim T(1 To 17) As Byte, n As Byte, c%, s$
  Dim v As Byte, r As Byte, k As Byte, i As Byte
  With Target
    If .CountLarge > 1 Then Exit Sub
    If Intersect(Target, [C12:AJ28]) Is Nothing Then Exit Sub
    c = .Column: s = .Value
  End With
  With Application
    .ScreenUpdating = 0: .EnableEvents = 0
    [AM12:AM28, AO32:AQ48].ClearContents: Cells(29, c) = "#"
    Cells(32, c).Resize(17).ClearContents: v = Val(s)
    If v < 1 Or v > 17 Then
      'refus d'une valeur hors bornes, ou d'une valeur causant
      'un dépassement de capacité (ne tenant pas dans un byte)
1     Target.ClearContents: GoTo 2
    Else
      For i = 1 To 17
        k = Cells(i + 11, c): T(i) = k
        If k = v Then
          'refus d'une valeur si elle existe déjà => pas de doublon
          n = n + 1: If n = 2 Then Target.ClearContents: GoTo 2
        End If
      Next i
      'écrire toutes les positions (lignes 32 à 48, même colonne c) ;
      'mais peut y'avoir des vides (si vide en lignes 12 à 28, col c)
      For i = 1 To 17
        k = T(i): If k > 0 Then Cells(k + 31, c) = Cells(i + 11, 2)
      Next i
      'comme y'a pas de doublons : si y'a 17 nombres, ils sont tous présents
      'une seule fois (nbs 1 à 17) => on peut effacer le "#" de la colonne c
      If .Count(Cells(12, c).Resize(17)) = 17 Then Cells(29, c).ClearContents
    End If
2   .EnableEvents = -1 'on réactive les événements d'Excel
  End With
End Sub

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

6) code VBA de Module1 (25 lignes) ; c'est idem que pour l'ancienne v3 :
je n'y ai fait aucun changement.

Option Explicit

Sub SetClassement()
  Dim n%: Application.ScreenUpdating = 0
  n = Application.Count([C12:AJ28])
  If n <> 578 Or [AO29] > 0 Then _
    [AM12:AM28, AO32:AQ48].ClearContents: Exit Sub
  Dim v#, r As Byte, i As Byte
  For i = 1 To 17
    r = i + 31
    With Cells(r, "AO")
      v = .Offset(, -2)
      .Value = WorksheetFunction.Rank(v, [AM32:AM48], 1)
      .Offset(, 1) = i: .Offset(, 2) = v
    End With
  Next i
  [AO32:AQ48].Sort [AO32]
  [AP32:AP48].Copy: [AM12].PasteSpecial -4163
  Application.CutCopyMode = 0: [AO12].Select
End Sub

Sub Réactiv()
  Application.EnableEvents = -1
End Sub

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

à propos des raccourcis claviers Ctrl r et Ctrl k : c'est normal que tu ne les
connaissais pas car c'est moi qui ai choisi ces lettres pour appeler les 2
subs que j'ai créées (situées dans Module1).

* r est l'initiale de Réactiv() : pour réactiver les événements d'Excel.

* k ressemble phonétiquement au c de classement et appelle la sub
SetClassement() ; j'ai évité volontairement le Ctrl c car il est souvent
utilisé pour faire le copier (d'un copier / coller).

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

à toi de faire plusieurs tests.  :)

rhodo