Recherche l'utilisation la plus fréquente

Bonjour,

Nouveau sur ce forum, je m'empresse de poster ma première demande

Je vais tenter de vous expliquer clairement mon besoin.

J'ai un tableau de + de 19000 lignes avec des nomenclatures

J'ai donc des réf de composants qui sont utilisés sur différents objets dont l'utilisation diffère => je souhaite savoir automatiquement pour quelle utilisation le composant est utilisé le plus souvent

En image :

Le composant "Corde" est le plus souvant utilisé "Assis"

Qu'elle formule ou outil peut me permettre de déterminer cela de façon automatique

Si je cherche des doublons, je prend le premier ou le dernier par exemple, mais c'est arbitraire, je préfèrerais que la "Position" principale soit le résultat de la plus fréquente

Bonjour,

Un vrai fichier Excel aurait été préférable à une image dont on ne peut pas faire grand chose.

Un essai avec une formule matricielle (avec tes données de A2 à C6 :

=INDEX($A$2:$A$6;EQUIV(MIN(SI($C$2:$C$6="Corde";(1/NB.SI($A$2:$A$6;$A$2:$A$6))));SI($C$2:$C$6="Corde";(1/NB.SI($A$2:$A$6;$A$2:$A$6)));0))

Plage de cellules à adapter

Formule matricielle à valider par CTRL + MAJ + ENTREE

A tester quand même en grand

Attention : une formule matricielle est gourmande en temps de calcul. Donc sur 19000 lignes, ça risque de prendre .... un certain temps ...

Essaye et dis nous

Si problème adaptation, reviens avec un extrait de ton vrai fichier Excel (une vingtaine de lignes devraient suffire)

A te (re)lire

@+

Voici un petit fichier avec quelques donnés :

http://escargot-archi.eu/upDocs/casier_escargot/Excel/test_plus_frequent.xls

J'ai remis en haut les colonnes de mon exemple en image

La colonne G contient les références

La D les utilisation de cette référence

Je souhaite avoir en H l'utilisation la plus fréquente de cette référence

Je fais un test en tentant d'adapter ta formule dans ce tableau (si j'y arrive, je suis vraiment très mauvais avec les formules)

re,

Toujours avec ce que j'ai compris :

=INDEX($D$10:$D$173;EQUIV(MIN(SI($G$10:$G$173="1";(1/NB.SI($D$10:$D$173;$D$10:$D$173))));SI($G$10:$G$173="1";(1/NB.SI($D$10:$D$173;$D$10:$D$173)));0))

Formule toujours matricielle, donc validation par CTRL + MAJ + ENTREE

Le "1" est à adapter pour les autres références de maquette

Je te laisse essayer et tester

@+

Si je comprend bien, il faudrait adapter cette formule pour toutes les références ?

Pas moyen qu'il trouve les doublons lui même et qu'il "adapte la formule" à chaque nouvelle référence ?

La question est surement idiote puisque trop complexe ou carrément irréalisable, mais partant du principe que si ce n'est pas possible il faut que je me le fasse à la main => + de 19000 lignes pour environ 8000 références => Je vais pêter les plombs

re,

Non, tu peux adapter ainsi :

en H10 :

=INDEX($D$10:$D$173;EQUIV(MIN(SI($G$10:$G$173=$G10;(1/NB.SI($D$10:$D$173;$D$10:$D$173))));SI($G$10:$G$173=$G10;(1/NB.SI($D$10:$D$173;$D$10:$D$173)));0))

Ne pas oulier la validation matricielle (CTRL + MAJ + ENTREE)

Puis recopier vers le bas

Ne pas oublier qu'une fomrule matricielle sur 9000 lignes, ça peut prendre ... un peu de temps de recalcul.

@+

Bonjour,

un petit essai, avec une macro

Dans ton exemple, le code va copier en colonne J les références, en colonne K, les familles et en colonne L, le nombre d'utilisation de cette famille dans cette référence...

Seul le nombre le plus élevé, (ou les ex-aequo), apparaissent

Clique sur le rectangle jaune

Regarde et dis quoi..

Ce sera peut-être plus rapide qu'une formule...

J'ai fait un test sur 20 000 lignes (avec seulement 52 références, il est vrai...), ça m'a pris un peu plus d'une seconde....

N'oublie pas de valider les macros...

Bon courage

https://www.excel-pratique.com/~files/doc2/test_plus_frequent_v1.xls

Code mis en H10 + (CTRL + MAJ + ENTREE) = #N/A

cousinhub a écrit :

Bonjour,

un petit essai, avec une macro

Dans ton exemple, le code va copier en colonne J les références, en colonne K, les familles et en colonne L, le nombre d'utilisation de cette famille dans cette référence...

Seul le nombre le plus élevé, (ou les ex-aequo), apparaissent

Clique sur le rectangle jaune

Regarde et dis quoi..

Ce sera peut-être plus rapide qu'une formule...

J'ai fait un test sur 20 000 lignes (avec seulement 52 références, il est vrai...), ça m'a pris un peu plus d'une seconde....

N'oublie pas de valider les macros...

Bon courage

https://www.excel-pratique.com/~files/doc2/test_plus_frequent_v1.xls

C'est nikel juste les doublons ex-aequo à valider à la main... (mise en gras ou couleur possible ?)

Je vais voir avec un collègue comment adapter le code pour qu'il fonctionne avec mon tableau d'origine de 19000 ligne...

Un très grand merci à vous deux

re,

Je pense que si tu as NA, c'est que tu as mal validé.

Il faut appuyer sur CTRL (maintenu) puis sur MAJ (maintenu) et enfin Entrée

Et alors on lâche tout.

Après avoir validé de cette façon, des accolades apparaissent de chaque côté de la formule dans la barre de formules.

Je te joins ton fichier avec la formule

https://www.excel-pratique.com/~files/doc2/Escargot.zip

@+

Re-,

Remplace le code par celui-ci :

Sub References()
Dim Cel As Range
Dim Concat As String
Dim NbLig As Long, I As Long
Dim Maquettes As Object, Maquettes2 As Object
Application.ScreenUpdating = False
Set Maquettes = CreateObject("Scripting.Dictionary")
Range("J2:L" & [J65000].End(xlUp).Row + 1).Clear
For Each Cel In Range("G2:G" & [G65000].End(xlUp).Row)
    Concat = Cel.Value & ";" & Cel.Offset(0, -3).Value
    If Not Maquettes.Exists(Concat) Then
        Maquettes.Add Concat, 1
    Else
        Maquettes.Item(Concat) = Maquettes.Item(Concat) + 1
    End If
Next Cel
NbLig = Maquettes.Count
[J2].Resize(NbLig, 1).Value = Application.Transpose(Maquettes.Keys)
Range("J2:J" & NbLig + 1).TextToColumns Destination:=Range("J2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, Semicolon:=True
[L2].Resize(NbLig, 1).Value = Application.Transpose(Maquettes.Items)
Range("J1:L" & NbLig + 1).Sort Key1:=Range("J2"), Order1:=xlAscending, Key2:=Range("L2") _
        , Order2:=xlAscending, Header:=xlYes
Set Maquettes2 = CreateObject("Scripting.Dictionary")
For I = NbLig + 1 To 2 Step -1
    If Not Maquettes2.Exists(Cells(I, 10).Value) Then
        Maquettes2.Add Cells(I, 10).Value, Cells(I, 10).Value
    Else
        If Cells(I + 1, 12).Value <> Cells(I, 12).Value Then
            Cells(I, 10).Resize(1, 3).Delete Shift:=xlUp
        Else
            Cells(I, 10).Resize(2, 3).Interior.ColorIndex = 4
        End If
    End If
Next I
Columns("J:L").HorizontalAlignment = xlCenter
End Sub

thibo,

Pour la référence 74 en bac de colonne, il met "Poignet" alors que c'est "Cou" qui est le plus fréquent

cousinhub,

Et pour avoir le café et les croissant, faut ajouter quoi

Sans rire, faut maintenant que je comprenne comment changer ou il prend les infos et ou il les mets...

Pensant faire plus simple, j'ai copier les colonnes concernées dans ton tableau mais j'ai un message d'erreur et quand j'ouvre de débogueur la ligne suivante est surlignée en jaune :

Concat = Cel.Value & ";" & Cel.Offset(0, -3).Value

re,

J'ai regardé plus en détail et en effet, la formule n'est pas correcte.

Je regarde dès que j'ai un peu de temps

@+

Re-,

D'où la nécessité de joindre un fichier le plus près possible de la réalité...

Remet un fichier avec la structure exacte de ton fichier réel....(et les mêmes données)

J'étais dessus, le temps de le préparer

Ce fichier contient l'ensemble des lignes => 19101

J'ai viré tout le reste (il faisait + de 6Mo)

Tu as donc juste les donnés à traiter => je ferais un copier/collé dans le miens ensuite... merci d'avance

J'ai vraiment la nette impression d'abuser largement....

http://escargot-archi.eu/upDocs/casier_escargot/Excel/Moyenne_Type_Porte.xls

Re-,

tu avais 8 erreurs dans la colonne A..

Lignes : 765, 1357, 3700, 4767, 4773, 7735, 7736 et 15981

Pour 6, j'ai remplacé par la valeur de la cellule du dessus

Par contre, pour A7735 et A7736, comme la Ref est unique, j'ai mis "Cousin" (je ne savais pas quoi mettre....)

Avec ce code, 9163 Références, temps de traitement de ton fichier : un peu plus de 6 secondes (sur mon PC)

J'ai mis en début de code :

t = Timer

et en fin de code :

MsgBox Timer - t

pour calculer le temps de traitement.... Tu peux bien évidemment les enlever

Le code :

Sub References()
Dim Cel As Range
Dim Concat As String
Dim NbLig As Long, I As Long
Dim Maquettes As Object, Maquettes2 As Object
Application.ScreenUpdating = False
t = Timer
Set Maquettes = CreateObject("Scripting.Dictionary")
Range("E2:G" & [E65000].End(xlUp).Row + 1).Clear
For Each Cel In Range("C2:C" & [C65000].End(xlUp).Row)
    Concat = Cel.Value & ";" & Cel.Offset(0, -2).Value
    If Not Maquettes.Exists(Concat) Then
        Maquettes.Add Concat, 1
    Else
        Maquettes.Item(Concat) = Maquettes.Item(Concat) + 1
    End If
Next Cel
NbLig = Maquettes.Count
[H1] = NbLig
[E2].Resize(NbLig, 1).Value = Application.Transpose(Maquettes.Keys)
Range("E2:E" & NbLig + 1).TextToColumns Destination:=Range("E2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, Semicolon:=True
[G2].Resize(NbLig, 1).Value = Application.Transpose(Maquettes.Items)
Range("E1:G" & NbLig + 1).Sort Key1:=Range("E2"), Order1:=xlAscending, Key2:=Range("G2") _
        , Order2:=xlAscending, Header:=xlYes
Set Maquettes2 = CreateObject("Scripting.Dictionary")
For I = NbLig + 1 To 2 Step -1
    If Not Maquettes2.Exists(Cells(I, 5).Value) Then
        Maquettes2.Add Cells(I, 5).Value, Cells(I, 5).Value
    Else
        If Cells(I + 1, 7).Value <> Cells(I, 7).Value Then
            Cells(I, 5).Resize(1, 3).Delete Shift:=xlUp
        Else
            Cells(I, 5).Resize(2, 3).Interior.ColorIndex = 4
        End If
    End If
Next I
Columns("E:G").HorizontalAlignment = xlCenter
MsgBox Timer - t
End Sub

PS, je pense que l'heure des croissants est passée...

Suis vraiment une bille...

J'ai donc ouvert le nouveau fichier que j'avais mis en ligne

J'ai suis allé dans menu Outils Macro Microsoft Visual Basic J'ai double cliqué sur Feuil1 copier/coller le code dedans enregistré le tout

Fermer le fichier, ouvrir le fichier et activer les macro rien, ni bouton jaune ni autre chose...

trl + Maj + Entrer rien

F9 rien

j'suis vraiment nul

Re-,

dans le fichier que tu as mis en ligne...

Tu fais Alt + F11, tu arrives dans l'éditeur VBE

Tu fais Insertion/Module

Tu colles le code que je viens de donner

dans la feuille excel

tu insères un dessin de la barre d'outils Dessin

Clic droit/Affecter une macro

tu choisis "Références", puis OK

Tu sélectionnes une cellule au hasard

Quand tu repasses la souris sur ton dessin, elle ressemble à une main

Tu cliques...

PS, si tu as de nouveau un msg d'erreur, tu cliques sur Fin, et tu corriges les erreurs que je t'ai signalées (si tu ne l'as pas encore fait, sinon, fait-le avant de déclencher le code)

Bon courage