Matrice de pondération : classement de données
Bonjour à tous.
Je cherche à réaliser une matrice de pondération à partir d'un fichier excel pour classer des données suivant 2 critères simultanés. Pour être plus claire voici un exemple de ce que je veux réaliser. Bien mon vrai fichier possède 1500 lignes !
https://www.excel-pratique.com/~files/doc/hXOydexemple.xls
Pourriez-vous m'aider à ce sujet ? Merci d'avance à tous les experts Excel de ce forum car j'ai fait des recherches avant et vous avez l'air sacrément câlés !!
Bonsoir,
ce qui me dérange dans ton fichier, c'est que le résultat attendu ne correspond pas aux données......
En effet, tu mets dans le tableau "Résultats attendu", l'arbre 2 (de fréquence 5) dans un critère strictement inférieur à 5.....
Puis tu mets les arbres 3 et 4 dans le critère "entre 5 et 10 $", alors qu'ils ne font que 3 et 4 $.....
Peux-tu mettre un fichier cohérent, cela sera plus facile pour comprendre la logique....
Et apporter des précisions sur les "strictement inférieur" ou "inférieur ou égal", concernant les critères
Bonsoir,
Tu pourras l'effetuer par le biais de filtre élaboré.
Cdlt,
Désolé c'était juste un exemple fait à la vas-vite. Voici le nouveau fichier corrigé :
https://www.excel-pratique.com/~files/doc/DHhNrexemple.xls
Merci
Re-,
J'aurais encore deux petites questions concernant ton fichier :
- Est-ce que tous les critères sont sur l'exemple?
a) 3 critères de fréquence :inférieur ou égal à 5, de 5 à 7 inclus, et supérieur à 7, ou
existe-til d'autres intervalles?
b) idem pour les prix : moins de 5, de 5 à 7 et supérieur à 7
s'il y en a plus, il serait bien de connaitre la logique
- Faut-il que toutes les données correspondant aux mêmes critères soient dans la même cellule?
PS: cela ne va pas être simple, il te faudra un peu de patience....
1ère réponse :
Il n'y a que 3 critères pour chaque colonne : 3 pour le pris et 3 pour la fréquence
2ème réponse : classement des données
--> le top serait d'avoir une cellule pour chaque référence de pièces
mais pour cela je suppose qu'il va falloir passer par une macro ?
--> Sinon, il est peut-être possible de séparer chaque nom de réference par une virgule (ou autre ?) dans une même cellule
Rq sur la forme du résultat :
A pire des cas si la mise en forme des résultats est trop compliqué à réaliser, la création d'une colonne pour chaque critère peut me suffire, exemple (dernier tab : résultat possible)
Re-,
j'avais fait un premier jet avant ton nouveau fichier....
Regarde si cela t'intéresse...
le code :
Sub traitement()
Application.ScreenUpdating = False
For Each cel In Range("A4:A" & [A65000].End(xlUp).Row)
Select Case cel.Offset(0, 1)
Case Is <= 5
prix = 2
Case Is <= 7
prix = 3
Case Else
prix = 4
End Select
Select Case cel.Offset(0, 2)
Case Is <= 5
freq = 7
Case Is <= 7
freq = 8
Case Else
freq = 9
End Select
With Cells(prix, freq)
.Value = IIf(.Value = "", cel, .Value & vbLf & cel)
End With
Next cel
End Sub
les variables prix et freq correspondent respectivement à des numéros de lignes et colonnes
https://www.excel-pratique.com/~files/doc/DHhNrexemplev1.zip
Merci pour ton aide ! avec mon fichier d'exemple ca fonctionne parfaitement.
Je vais voir si je peux l'appliquer à ma "base de données réelle" et je te tiens au courant. En tout cas, chapeau l'artiste et merci pour ta réactivité !
A plus, j'essaie et je te tiens au courant !
J'ai essayé de transposer ta solution à mon fichier : aie !!!
J'ai quelques questions :
- 1er problème : en inscrivant les résultats dans une seule case, Exce limite très rapidement la taille de la case et donc le nombre de réponses (en effet j'ai 1500 lignes dans le fichier)
- -> il faudrait faire la même chose mais créer pour chaque référence une nouvelle cellule
2ème question : comment fait-on pour écrire le résultat dans une autre feuille que celle où se trouvent les données (et oui je suis un novice en macro !) et même choisir la cellule de départ de l'écriture
Mais le début est très prometteur (vu la rapidité de la réponse). J'ai donc confiance en toi pour trouver la solution à mon problème. Merci encore.
PS : ne t'inquiète pas si je ne répond à tes réponses demain matin, car je suis en Californie et il est GMT - 8h --> 12h chez toi = 3h du matin chez moi
Bonsoir, (ou bonjour pour toi....
Une autre approche...
J'ai rajouté 3 onglets, en fonction du prix
on copie les données dans chaque onglet respectif, et en fonction de la fréquence
regarde le fichier joint
Le code :
Sub traitement()
Application.ScreenUpdating = False
Dim sh As Object, cel As Range, feuille As Integer, colonne As Integer
For Each sh In Sheets
If sh.Name <> "base" Then sh.Range("A2:C65000").ClearContents
Next sh
For Each cel In Range("A4:A" & [A65000].End(xlUp).Row)
Select Case cel.Offset(0, 1)
Case Is <= 5
feuille = 2
Case Is <= 7
feuille = 3
Case Else
feuille = 4
End Select
Select Case cel.Offset(0, 2)
Case Is <= 5
colonne = 1
Case Is <= 7
colonne = 2
Case Else
colonne = 3
End Select
With Sheets(feuille)
.Cells(.Cells(65536, colonne).End(xlUp).Row + 1, colonne).Value = cel
End With
Next cel
End Sub
https://www.excel-pratique.com/~files/doc/DHhNrexemplev2.zip
Bonsoir !
ta solution est intéressante ! En fait ce matin (en attendant de tes news), j'ai décidé
d'adopter une nouvelle approche, plus facile pour moi sans passer par une macro.
J'ai utilisé des fonctions "SI" avec test logique. Au lieu d'obtenir une matrice 3*3, j'obtiens
9 colonnes côte à côte.
Par contre, avec cette solution, j'ai bien entendu des cellules vides ! Peut-être pourrait-tu m'aider à faire une macro qui supprime les "cellules vides" de chaque colonne ? Je t'envoie le fichier et dis-moi si tu comprends et auquel cas, peux tu me proposer une solution.
Je suppose que cette macro sera plus facile à réaliser...
Ensuite je vais retranscrire les résultats sous forme de matrice au pire.
https://www.excel-pratique.com/~files/doc/ex_traitement_donnees.zip
PS : j'ai raccourci le fichier car il ne passait pas sur le site !
- Messages
- 9'246
- Excel
- Vista Office 2007FR
- Inscrit
- 08/12/2007
- Emploi
- retraité Sce.Méthodes
bonsoir à tous,
rem, ici filtre élaboré
je ne comprend pas tes "résultats possibles" !
https://www.excel-pratique.com/~files/doc/rem.xls
voir feuille3
amicalement
Claude.
Bonjour Claude,
je suis désolé mais je n'ai pas compris le fichier que tu as envoyé
Le mieux pour comprendre mon fichier est que tu regardes le dernier fichier que j'ai envoyé.
--> https://www.excel-pratique.com/~files/doc/ex_traitement_donnees.zip
Tu peux y trouver les différents critères et le premier tri que j'ai pu faire (cf post précédent).
Le tri avec les fonctions "SI" fonctionne très bien. Par contre je me retrouve avec des cellules vides que je voudrais supprimer (uniquement les cellules vides de chaque colonne).
Merci quand même pour ta solution précédente.
Bonsoir,
en regardant tes formules, il me semble qu'il manque les fréquences de 12 à 24....
En fréquence F, tu mets >12, en fréquence M, tu mets compris entre 24 et 56 et en fréquence F, tu mets > 56....
je crois comprendre que ce n'est pas 12, mais 24...
A suivre....
- Messages
- 9'246
- Excel
- Vista Office 2007FR
- Inscrit
- 08/12/2007
- Emploi
- retraité Sce.Méthodes
re,
j'ai dû mal comprendre la question ! excuse.
PS: tes derniers liens ne sont pas valides avec mon "winzip"
amicalement
Claude.
Re-,
et pour une autre précision....
Est-ce obligatoire les colonnes vides entre chaque "Famille" (colonne D et H dans ton exemple)?
Bonsoir Claude
[quote="felix"]Re-,
et pour une autre précision....
Est-ce obligatoire les colonnes vides entre chaque "Famille" (colonne D et H dans ton exemple)?
Non
felix a écrit :Bonsoir,
en regardant tes formules, il me semble qu'il manque les fréquences de 12 à 24....
En fréquence F, tu mets >12, en fréquence M, tu mets compris entre 24 et 56 et en fréquence F, tu mets > 56....
je crois comprendre que ce n'est pas 12, mais 24...
A suivre....
Bien jouer ! Vous avez sacrément l'oeil sur ce forum ! Vous ne négligez pas les détails c'est hallucinant !
Voici les critères corrects :
Price <= 24 24<Price<=126 Price>126 Freq <= 12 12<Freq<=56 Freq > 56
Re-,
j'ai continué avec mes macros, en négligeant les formules que tu avais savamment élaborées.....
J'ai rajouté une feuille de travail, appelée "transit", que tu peux masquer dans ton fichier (Format/Feuille/Masquer), cela n'est pas gênant....
Regarde le fichier, je mets quand même un peu plus d'un centième de seconde pour remplir tes tableaux dans la feuille "traitement donnees"
Le code :
Sub traitement()
Application.ScreenUpdating = False
Dim pl As Range, DerLig As Long, DerLig1 As Long
Set pl = Sheets("donnees").Range("A5:C" & Sheets("donnees").Range("A65536").End(xlUp).Row)
pl.Name = "base"
Sheets("traitement donnees").Range("A14:I65000").ClearContents
With Sheets("transit")
For i = 1 To 3
For j = 1 To 3
.[C2].FormulaR1C1 = _
"=AND(donnees!R[4]C[-1]>" & .Cells(i, 5) & ",donnees!R[4]C[-1]<=" & .Cells(i + 1, 5) & ", donnees!R[4]C>" & .Cells(j, 6) & ",donnees!R[4]C<=" & .Cells(j + 1, 6) & ")"
Range("base").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range( _
"C1:C2"), CopyToRange:=.Range("A1"), Unique:=False
DerLig = Sheets("traitement donnees").Cells(65536, i * j).End(xlUp).Row + 1
DerLig1 = .[A65536].End(xlUp).Row
If DerLig1 = 1 Then GoTo suite
Sheets("traitement donnees").Cells(DerLig, i * j).Resize(DerLig1 - 1, 1).Value = _
.Range("A2:A" & DerLig1).Value
suite:
Next j
Next i
.[A2:A65000].ClearContents
End With
End Sub
Dans la feuille "transit", qui est masquée (pour démasquer, Format/Feuille/Afficher), tu peux modifier les critères dans les cellules E1 à F4
E1 et F1 sont à 0 (zéro ), valeur minimale
E2 à E4, les critères pour le prix
F2 à F4, les critères pour la fréquence
Dans le code, la zone de données est définie automatiquement, par la définition de la plage nommée "pl"
Ensuite, on fait des boucles, et en fonction de la valeur des indices, la formule en C2 de la feuille "transit" évolue
Pour regarder, clique sur C2, puis déroule la macro en mode pas à pas (ouvre l'éditeur VBE, puis appui sur F8)
Puis, un filtre élaboré, comme suggéré au début du fil par Raja, qui extrait vers la feuille transit, les données correspondantes
Ces données sont ensuite copiées vers la feuille ("traitement donnees")
Regarde si cela te convient, et reviens si problème
https://www.excel-pratique.com/~files/doc/DHhNrexemplev3.zip
Re-,
j'ai vu une erreur dans mon code, remplace par celui-ci :
Sub traitement()
Application.ScreenUpdating = False
Dim pl As Range, DerLig As Long, DerLig1 As Long, NbCrit As Long
Set pl = Sheets("donnees").Range("A5:C" & Sheets("donnees").Range("A65536").End(xlUp).Row)
pl.Name = "base"
Sheets("traitement donnees").Range("A14:I65000").ClearContents
x = 1
With Sheets("transit")
For i = 1 To 3
For j = 1 To 3
.[C2].FormulaR1C1 = _
"=AND(donnees!R[4]C[-1]>" & .Cells(i, 5) & ",donnees!R[4]C[-1]<=" & .Cells(i + 1, 5) & ", donnees!R[4]C>" & .Cells(j, 6) & ",donnees!R[4]C<=" & .Cells(j + 1, 6) & ")"
Range("base").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range( _
"C1:C2"), CopyToRange:=.Range("A1"), Unique:=False
DerLig = Sheets("traitement donnees").Cells(65536, x).End(xlUp).Row + 1
DerLig1 = .[A65536].End(xlUp).Row
If DerLig1 = 1 Then GoTo suite
Sheets("traitement donnees").Cells(DerLig, x).Resize(DerLig1 - 1, 1).Value = _
.Range("A2:A" & DerLig1).Value
suite:
x = x + 1
Next j
Next i
.[A2:A65000].ClearContents
End With
End Sub
le fichier modifié :
https://www.excel-pratique.com/~files/doc/DHhNrexemplev4.zip