Problème avec le tri de Excel

[belangy]

Bonjour,

Je cherche à trier des nombres qui sont saisis sous la forme suivante:

1.0.1.1
1
2.1.1.3
2.3.4.5
1.2.3.4
1.3
3.2.1.3 a)

À l'origine, ces nombre (en format texte) étaient saisis dans une seule colonne.

Évidemment, le tri de Excel pose des problèmes. Notamment en raison de la présence des point (.), des espaces blancs et des caractères alphabétiques - puisqu' Excel trie en fonction de la valeur ASCII du caractère.

À priori, la solution auquelle j'ai pensé est de transposer ces nombres sur plusieurs colonnes en supprimant les point (.).

Ainsi la série de nombres précédent ressemble à l'image exempletriu.jpg



Encore là, le tri d'Excel, bien qu'il soit correct en fonction de son algorithme de tri, ne correspond pas au besoin.

Une autre solution semble plus prometteuse en ce sens qu'il suffit de remplir les cellules vides par des zéro (0) comme dans l'image exempletri2.jpg



Le tri (sur 5 colonnes) d'Excel sur le tableau de l'image précédente fonctionne correctement, à l'exception de la dernière colonne (caractères alphanumériques). Heureseument, cette dernière colonne n'est pas utilisée souvent.

Suite à cette problématique, avez-vous des idées sur la façon de trier ce genre de nombres ?

Car la méthode que j'ai trouvé est correcte mais est un peut contraignante pour les usagers, sans compte le fait que visuellement, la présence de zéros (0) dans plusieurs cellules peut être irritant.

En vous remerciant d'avance.

[U. Milité]

Bonsoir,

Peut-être un peu "tortueux", mais si le premier n° est en A1, tester ce qui suit:

Code :

=SI(ESTERREUR(CNUM(SUBSTITUE(A1;".";"")));CNUM(STXT(SUBSTITUE(A1;".";"");1;TROUVE(" ";SUBSTITUE(A1;".";"");1)-1))*CHOISIR(NBCAR(SUBSTITUE(A1;".";""))-3;100000;10000;1000;100;10);CNUM(SUBSTITUE(A1;".";""))*CHOISIR(NBCAR(SUBSTITUE(A1;".";""));100000;10000;1000;100;10))

La formule supprime les '.' (points) des numéros de départ.
Si le n° se termine par des caractères non numériques après une espace, l'espace et ce qui suit sont ignorés.
Les numéros sont ensuite multipliés par 10, 100, 1000, etc jusqu'à obtenir uniquement des nombres sur 6 positions.
Le tri semble pouvoir ensuite être fait sur cette formule ... À tester soigneusement

[belangy]

Bonjour,

J'ai testé la formule. Elle semble fonctionner, à l'exception des caractères alphanumérique du type a) ou b) qui peuvent se retrouver à la suite des nombres.

Peut-être ajouter du code pour transformer ce caractère en numérique en tenant compte de sa position dans l'alphabet ?

[U. Milité]

Bonjour, bonsoir,

Avant de creuser la question, il serait prudent de s'assurer:
- que les lettres ne seront jamais qu'en fin de "chaîne". Si on risque -à terme- de trouver des mentions telles que 1.1.B.4.a) ... ça va se compliquer un brin.
- d'autre part, il faudrait pouvoir déterminer le nombre maximum de niveaux qu'on pourrait trouver dans la hiérarchie.

Si on peut répondre affirmativement aux 2 questions, le problème reste gérable par formule. Si pas, il faudra passer par une macro (fonction personnalisée ou macro événementielle)

Tant qu'on en est aux questions, les lettres seront-elles uniquement présentes en minuscules?

[Daranc]

ce qui serait pas mal non plus c'est de savoir précisément à quoi doit ressembler le tableau trié .

[Pierre Fauconnier]

Salut.

Sans comprendre la logique des codes (y en-a-til une?), les réponses ne pourront qu'être empiriques et seront donc forcément sujettes à caution.

Je suis effaré de voir qu'en 2011, les personnes responsables de la mise au point de codes et de références ne pensent pas à l'informatisation (Codes commençant par 0, syntaxes de codes exotiques plutôt que systématiques, ...), obligeant dès lors à des créations de formules plus ou moins complexes pour répondre à leurs besoins, alors qu'il s'agit, une fois de plus, d'un problème de conception...

[belangy]

Bonjour,

Je vais tenter de répondre aux questions.

Primo, je joins l'image qui montre ce à quoi ressemble le fichier original. Certaines parties ont été masquées (informations confidentielles).


En ce qui concerne la position des caractères alphanumériques, sur une même ligne, les caractères se retrouvent toujours à la fin, mais pas nécessairement à la même position.

Secondo, ils seront toujours en minuscules.

Tertio, pour ce qui est de la logique du code, en fait il s'agit d'une même série de nombres que les listes (puces) à numéro dans Word, ou d'une table des matières.

P.S. Comme on voit sur l'image, dans certaines cellules, on compte jusqu'à 2 nombres du type 1.2.1. Cette façon de faire complique grandement les choses. Ce n'est pas très logique, mais enfin...

Quoiqu'il en soit, il sera demandé à la personne responsable des modifications de ce fichier de modifier cette façon de faire.

J'espère que cela répond à vos questions.

En vous remerciant d'avance pour vos réponses.

[U. Milité]

Bonjour, bonsoir,

les caractères se retrouvent toujours à la fin, mais pas nécessairement à la même position

Désolé, mais ... ils sont à la fin ... ou non??

L'image jointe ne permet pas (en tout cas pas à moi!) de voir quoi que ce soit ... Au point que je ne sais même pas dans quelle colonne figurent les n° dont il est question!?

Au jugé donc, une modif de la première proposition, qui tient compte de la suggestion

transformer ce caractère en numérique en tenant compte de sa position dans l'alphabet

... enfin, il me semble:

Code :

=SI(ESTERREUR(CHERCHE(" ";SUBSTITUE(A1;".";"")));SUBSTITUE(A1;".";"");GAUCHE(SUBSTITUE(A1;".";"");CHERCHE(" ";SUBSTITUE(A1;".";""))-1)&ABS(96-CODE(STXT(SUBSTITUE(A1;".";"");CHERCHE(" ";SUBSTITUE(A1;".";""))+1;1))))*CHOISIR(NBCAR(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;")";"");" ";"");".";""));100000;10000;1000;100;10)

Le tout, si la lettre est en avant-dernière position (précédée d'une espace et suivie d'une parenthèse fermante)

[Pierre Fauconnier]

Les images jointes sont illisibles et ne servent donc à rien. Pourquoi n'utilises-tu pas la fonctionnalité du forum pour les joindre (Gérer les pièces jointes, un peu en dessous du cadre du message lorsque tu le rédiges...)


Je pense que la seule solution pro et viable est de repenser le système de codage de façon à systématiser. Il me parait évident que sans systématisation (syntaxe) des codes, tu auras inévitablement des soucis...

Pour essayer de s'en sortir, il faut au moins dégager des règles:

• combien y-a-til de groupes de caractères possibles (1.2 = 2 groupes, 1.3.5.4.a = 5 groupes)?
• combien y-a-t-il au maximum de caractères pour chaque groupe?
• par quoi remplace-t-on les caractères manquants au sein d'un groupe?
• si il manque des groupes, considère-t-on qu'ils manquent à droite ou à gauche?
• il faut obligatoirement supprimer le problème des codes en double

En répondant à ces questions et en tenant compte de la dernière remarque, on pourra dégager un algo (éventuellement par VBA au sein d'une fonction perso) pour "remplir les trous" et renvoyer un code complété au sein d'une cellule, et le tri deviendra un jeu d'enfant. Il me paraît personnellement inepte d'éclater cela sur plusieurs colonnes.

[Daranc]

en fin de compte tu veux une table des matières
en suivant la discution :

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
48
49
50
51
52
53
54
55
56
57
Sub trieuse()
With Sheets(2)      ' à positionné sur la feuille de travail
lg_deb = ActiveCell.Row
col_deb = ActiveCell.Column
col_fin = .Cells(lg_deb, col_deb).End(xlToRight).Column
lg_fin = .Cells(lg_deb, col_deb).End(xlDown).Row
tabl = .Range(.Cells(10, 1), .Cells(lg_fin, col_fin)).Value
End With
'suppression des points
For t = 1 To UBound(tabl, 1)
For e = 1 To Len(tabl(t, 1))
If Mid(tabl(t, 1), e, 1) = "." Then
nouveau = nouveau
Else
nouveau = nouveau + Mid(tabl(t, 1), e, 1)
End If
Next
tabl(t, 1) = nouveau
nouveau = ""
Next
'amorce de longueur et recherche du plus long
g = Len(tabl(1, 1))
For t = 1 To UBound(tabl, 1)
If Len(tabl(t, 1)) > g Then g = Len(tabl(t, 1))
Next
'complément des chaines
For t = 1 To UBound(tabl, 1)
    If Len(tabl(t, 1)) < g Then
    i = g - Len(tabl(t, 1))
    For f = 1 To i
    tabl(t, 1) = tabl(t, 1) & "0"
    Next
    End If
    Next
'tri du tableau
For num = 1 To UBound(tabl, 1) - 1
For num2 = num + 1 To UBound(tabl, 1)
If tabl(num, 1) > tabl(num2, 1) Then
For t = 1 To UBound(tabl, 2)
old = tabl(num, t)
old2 = tabl(num2, t)
tabl(num, t) = old2
tabl(num2, t) = old
Next
End If
Next num2
If num <> 1 Then
If tabl(num, 1) = tabl(num - 1, 1) Then GoTo suite
End If
suite:
Next num
'fin de tri alphabétique----------
'pose du tableau
With Sheets(2)
.Range(Cells(lg_deb, col_deb), Cells(lg_fin, col_fin)).Value = tabl
End With
End Sub

sélectionner sur la première cellule du tableau (en haut à gauche)
et lancer la macro
j'ai pas retrouver comment repeter un nombre de caractere en fin de chaine alors j'ai fait une boucle

[belangy]

Envoyé par Pierre Fauconnier

Les images jointes sont illisibles et ne servent donc à rien. Pourquoi n'utilises-tu pas la fonctionnalité du forum pour les joindre (Gérer les pièces jointes, un peu en dessous du cadre du message lorsque tu le rédiges...)

Désolé, je ne connaissais pas cette fonctionnalité.

Voici donc une image plus "parlante":

ExempleTableau.JPG

Je pense que la seule solution pro et viable est de repenser le système de codage de façon à systématiser. Il me parait évident que sans systématisation (syntaxe) des codes, tu auras inévitablement des soucis...

Le fichier Excel recense l'information provenant des différents procès-verbaux et autres documents qui sont archivés depuis un certain nombre d'années. Il est impossible de repenser un nouveau système, puisque ce dernier fait référence au système utilisé à l'époque.

Au niveau des règles:

1. Il y a 5 groupes possibles

2. Chaque groupe peut contenir un maximum de 2 caractères

3. À l'origine, il n'y a rien qui remplace un caractère manquant (à moins que j'ai mal compris la question)

4. Si un groupe est manquant, il l'est à droite.

5. Il faut effectivement supprimer les codes en double (dans une même cellule)

Il me paraît personnellement inepte d'éclater cela sur plusieurs colonnes

Je reconnais effectivement que de placer une série du type 1.2.3.4.5 sur plusieurs colonnes est "irritant". C'est pourquoi je cherche une autre solution qui pourrait fonctionner.

[belangy]

Bonjour,

Je vais tester la solution de Daranc. Je vous en donne des nouvelles.

Merci.

[Pierre Fauconnier]

Pour moi, la solution la plus simple, pour éviter des formules à rallonge, c'est de passer par du vba.

Tu crées la fonction suivante dans un module standard

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Function CodeReecrit(CodeDepart As String)
  Dim Tableau
  Dim i As Integer
  Dim CodeTemp As String
 
  If InStr(1, CodeDepart, " ") <> 0 Then CodeDepart = Left(CodeDepart, InStr(1, CodeDepart, " "))
  Tableau = Split(CodeDepart, ".")
  For i = 0 To UBound(Tableau)
    If i > 4 Then Exit For
    If Len(Tableau(i)) = 1 Then
      CodeTemp = CodeTemp & "0" & Tableau(i) & ";"
      Else
      CodeTemp = CodeTemp & Tableau(i) & ";"
    End If
  Next i
  CodeReecrit = Left(CodeTemp, Len(CodeTemp) - 1)
End Function

ET tu l'utilises dans une cellule, en lui passant le code à transformer. En retour, tu reçois le code avec des "0" à la place du caractère manquant d'un groupe. Tu pourras alors trier sur la colonne récupérée

[Daranc]

au vu du tableau tu as des colonnes vides et une ligne de titre il faudra modifier la macro pour en tenir compte. prendre la largeur du tableau en fonction de la ligne de titre .pointer la premier cellule à trier. le reste devrait tourner normalement