[Résolu] Valeur la plus fréquente par ensemble de lignes

[tomasi]

Bonjour,

J'ai un fichier excel composé de la manière suivante (les valeurs sont des exemples) :

Identifiant |  Valeur 1  | Valeur 2 | Valeur 3
A           | 2          | 1        | 1
A           | 1          | 1        | 2
A           | 1          | 3        | 3
B           | 2          | 2        | 4
B           | 2          | 3        | 5
B           | 3          | 3        | 5
C           | 2          | 3        | 6
C           | 1          | 3        | 7

Je voudrais pour chaque identifiant distinct récupérer la valeur la plus fréquente pour chaque colonne pour chaque identifiant donné.

Par exemple pour l'identifiant A et la colonne "valeur 1" récupérer le chiffre 1,
pour l'identifiant B et la colonne "valeur 2" récupérer le chiffre 3.

Si quelqu'un a une idée pour réaliser cela en passant par des formules excel ou par une macro...

Merci d'avance pour vos conseils.

 

[JCGL]

Re : Valeur la plus fréquente par ensemble de lignes

Bonjour à tous,
Bienvenue sur XLD,

Merci pour ton tableau mais un fichier XL nous aiderait à tenter de t'aider.

As-tu lu la Charte XLD ?

A + à tous

 

[Tibo]

Re : Valeur la plus fréquente par ensemble de lignes

Bonsoir,

Un essai avec la fonction MODE en mode matriciel :

=MODE(SI($A$1:$A$8="A";$B$1:$D$8))

Formule matricielle à valider par CTRL + MAJ + ENTREE

plage de cellules à adapter à ton vrai fichier

@+

Edit : Salut Jean-Claude 🙂. Un rappel à la charte s'impose effectivement. Une chance pour notre demandeur que le tableau était simple

@+

Edit 2 : adaptation après relecture du message :

=MODE(SI(($A$2:$A$9="A")*($B$1:$D$1="Valeur 1");$B$2:$D$9))

Toujours matricielle

Toujours à adapter faute de fichier joint

@+

 

[JCGL]

Re : Valeur la plus fréquente par ensemble de lignes

Bonjour à tous,
Salut mon ami Tibo,

Nos bonnes résolutions concernant XLD, c'était avant ou après la verveine de Dany... Arf... Arf...

A++ l'ami
A + à tous

 

[tomasi]

Re : Valeur la plus fréquente par ensemble de lignes

bonsoir et merci beaucoup pour vos réponses (très) rapides

ça fonctionne plutôt pas mal, je ne connaissais pas la fonction "MODE" dans excel

j'ai testé ça dans un fichier excel (que je joint au message cette fois 🙂 )

par contre la formule ne fonctionne pas pour rechercher les valeurs des identifiants B et C alors que cela fonctionne pour A...

 

[Tibo]

Re : Valeur la plus fréquente par ensemble de lignes

re,

Petites précisions sur la fonction MODE :

il faut qu'il y ait au moins 2 valeurs dans le champs de cette fonction (ce n'est pas le cas pour B : 1 seule valeur)

il faut qu'il y ait une valeur qui soit présente au moins deux fois (ce n'est pas le cas pour C : 3 valeurs différentes)


Pour le premier point, on peut tester s'il y a plus d'une valeur en colonne ID

Pour le deuxième point, quelle valeur retenir s'il y a 3 valeurs différentes (toutes présentes qu'une seule fois)

@+

 

[tomasi]

Re : Valeur la plus fréquente par ensemble de lignes

effectivement il faudrait pouvoir gérer ces cas particuliers :
- dans le cas où il n'y qu'une seule valeur, est-il possible de la renvoyer ?
- dans le cas ou il y a plusieurs valeurs différentes mais pas 2 fois la même, il faudrait en renvoyer une de préférence la première rencontrée (mais cela n'a pas trop d'importance)

par contre une petite question, dans le cas où l'on a 2 fois 2 valeurs identiques, la fonction retourne la valeur de la première correspondance rencontrée c'est bien ça ?

 

[Robert]

Re : Valeur la plus fréquente par ensemble de lignes

Bonsoir le fil, bonsoir le forum,

Un proposition VBA. Une usine à gaz qui ne gère pas les ex-aequo...

Option Explicit
 
Sub Macro1()
 
Dim dico As Object 'déclare la variable dico (dictionary)
Dim dl As Integer 'déclare la variable dl (Dernière Ligne)
Dim col As Byte 'déclare la variable col (COLonne)
Dim pl As Range 'déclare la variable dico (PLage)
Dim plc As Range 'déclare la variable pl1 (PLage Colonne)
Dim cel As Range 'déclare la variable cel (CELlule)
Dim temp As Variant 'déclare la variable temp (TEMPoraire)
Dim temp1 As Variant 'déclare la variable temp (TEMPoraire1)
Dim no As Integer 'déclare la variable no (Nombre d'Occurrences)
Dim nomax As Integer 'déclare la variable nomax (Nombre d'Occurrences MAXimum)
Dim nm As Integer 'déclare la variable nm (Nombre Maximum)
Dim x As Byte 'déclare la variable x (incrément de colonne)
Dim i As Integer 'déclare la variable i
Dim j As Integer 'déclare la variable j
 
Set dico = CreateObject("Scripting.Dictionary") 'déclare la variable dico
dl = Cells(Application.Rows.Count, 1).End(xlUp).Row 'définit la dernière ligne dl
Set pl = Range(Cells(2, 1), Cells(dl, 1)) 'définit la plage pl
For Each cel In pl 'boucle sur toutes les cellules cel de la plage pl
    dico(cel.Value) = "" 'alimente le dictionnaire
Next cel 'prochaine cellule de la la boucle
temp = dico.keys 'récupère les valeurs sans dooublons
 
Set dico = CreateObject("Scripting.Dictionary") 'redéfinit la variabole dico
For x = 2 To 4 'boucle 1 : sur les colonne B à D
    Set plc = Range(Cells(2, x), Cells(dl, x)) 'définit la plage plc
    For Each cel In plc 'boucle sur toutes les cellules cel de la plage plc
        dico(cel.Value) = "" 'alimente le dictionnaire
    Next cel 'prochaine cellule de la la boucle
    temp1 = dico.keys 'récupère les valeurs de la colonne x sans dooublons
    For i = 0 To UBound(temp) 'boucle 2 : sur tous les identifiants
        nomax = 0 'initialise la variable nomax
        For j = 0 To UBound(temp1) 'boucle 3 : sur toutes les valeurs de la colonne
            no = 0 'initialise no
            For Each cel In pl 'boucle 4 : sur toutes les cellules de la plage pl
                'si la valeur de la cellule est égale à l'identifiant de la boucle 2 et
                'si la valeur de la cellule correspondante colonne x est égale à la valeur de la boucle 3
                'le nombre d'occurrence no s'incrément de +1
                If cel.Value = temp(i) And cel.Offset(0, x - 1).Value = temp1(j) Then no = no + 1
            Next cel 'prochaine cellule de la boucle 4
            If no > nomax Then 'condition : si no est supérieur à nomax
                nomax = no 'définit nomaxo
                nm = temp1(j) 'définit nm
            End If 'fin de la condition
        Next j 'prochaine valeur de la colonne de la boucle 3
        'place la valeur x-1 la plus récurente de l'identifiant
        Cells(Application.Rows.Count, x).End(xlUp).Offset(1, 0).Value = "V" & x - 1 & "/" & temp(i) & " : " & nm
    Next i 'prochain identifiant'de la boucle 2
Next x 'prochaine colonne de la boucle 1
End Sub

 

[Tibo]

Re : Valeur la plus fréquente par ensemble de lignes

Bonjour tomasi, salut Robert,

Une proposition formule pour gérer les #N/A :

=SI(NB.SI($A$2:$A$9;A14)=0;"";SI(ESTNA(MODE(SI(($A$2:$A$9=A14)*($B$1:$D$1="Valeur "&B14);$B$2:$D$9)));INDEX($B$2:$D$9;EQUIV(A14;$A$2:$A$9;0);EQUIV("Valeur "&B14;$B$1:$D$1;0));MODE(SI(($A$2:$A$9=A14)*($B$1:$D$1="Valeur "&B14);$B$2:$D$9))))

En A14, j'ai mis l'ID et en B14, le n° de Valeur

Je te laisse tester

@+

 

[tomasi]

Re : Valeur la plus fréquente par ensemble de lignes

Merci à tous pour votre aide et vos propositions.

Tibo, la formule semble avoir le comportement attendu dans le document excel de test, je vais regarder avec de vraies valeurs.

Encore une fois merci beaucoup pour ton aide.

 

[tomasi]

Re : Valeur la plus fréquente par ensemble de lignes

J'en profite pour vous soumettre une autre question :

le calcul précédent permet de définir une ligne générale par identifiant avec les valeurs les plus courantes,

il faudrait maintenant comparer chaque ligne (avec le même identifiant que la ligne générale) et comparer les valeurs :
- si la valeur est la même, on retourne vide
- si la valeur est différente, on retourne cette valeur

une idée sur comment effectuer comparaison ?

Merci encore.

 

[Tibo]

Re : Valeur la plus fréquente par ensemble de lignes

Bonjour,

Il serait peut-être préférable de créer un nouveau fil dès lors que le problème est différent.

Il faudrait également joindre un petit bout de fichier exemple, car je n'ai pas bien compris ton souhait.

A te (re)lire

@+