Formule sommeprod ou autre?

[JJ1]

Bonsoir,


Je recherche un nombre de lignes contenant un nombre de chiffres (4 ou 5) de la plage recherche G1:K x.
J'ai voulu adapter un sommeprod mais sans succès, du fait du mélange des chiffres et de leur répétition.
Peut-être avec une fonction décaler?
Pas simple du tout !
Je joins un bout de fichier.
Merci de votre aide précieuse.
Bonne soirée à tous.

 

[CISCO]

Re : Formule sommeprod ou autre?

Bonjour

Ton fichier réel a t'il plus de lignes que le fichier mis en pièce jointe ?

@ plus

 

[bruno66]

Re : Formule sommeprod ou autre?

bonjour... ?????????????????????,comme peut être d'autre j'ai rien compris a ta question..
que veut tu dire par 4 ou 5 chiffre il nous faut un résultat de ce que tu veux ton fichier nous montre rien de concret désolé

 

[CISCO]

Re : Formule sommeprod ou autre?

Bonsoir

Ligne 8, on a 1, 0, 0, 0, 0, à comparer à 0, 1, 0, 0,1, 0. On trouve donc en commun le premier 1 et trois 0, soit au total 4 chiffres.
Ligne 11, on a 0, 1, 0, 0, 1 à comparer à 0, 1, 0, 0,1, 0. On trouve donc en commun trois 0 et deux 1, soit au total 5 chiffres.

Soit un autre exemple : 1,2,3,4,5 à comparer à à 0, 1, 0, 0,1, 0. On trouve donc en commun un 1, et c'est tout, soit au total 1 chiffre.
Un petit dernier pour la route.
1,1,3,4,5 à comparer à à 0, 1, 0, 0,1, 0. On trouve donc en commun deux 1, et c'est tout, soit au total 2 chiffres.
Un autre encore :
1,1,1,4,5 à comparer à à 0, 1, 0, 0,1, 0. On trouve donc en commun deux 1, et c'est tout, soit au total 2 chiffres.

Je mettrai bien des couleurs pour mieux expliquer cela, mais chez moi, cela ne fonctionne plus. Grrrr.

@ plus

P.S : Ceci dit, je ne vois vraiment pas comment traiter cela avec des formules. Avec une macro, c'est certainement faisable, mais, vu mon niveau en VBA...

ReP.S : Et ben, apparemment, notre ami mapomme a trouvé une solution. Chapeau.

 

[mapomme]

Re : Formule sommeprod ou autre?

Bonsoir,

Pb intéressant ! Avec une colonne auxilliaire peut-être ?
(mais ça ne répond pas à la question d'une formule unique)

 

[mapomme]

Re : Formule sommeprod ou autre?

Bonsoir,


Je peux expliquer la formule aujourd'hui car demain je suis pas certain de retrouver la logique.

Dans un premier temps, on calcule le nombre d'occurence de chaque chiffre de la séquence de référence $G$1:$K$1 (ex 0,1,0,0,1).

Pour l'exemple on prend la colonne H (qui correspond à la valeur 1) : nb.si($G$1:$K$1;H$1) (qui donne 2 puisque deux 1 dans la séquence)

Pour ne pas compter les mêmes valeurs plusieurs fois, on ne calcule ce nombre d'occurence qu'à la première apparition de la valeur dans la séquence de référence soit la condition : NB.SI($G$1:H$1;H$1)=1

Si ce n'est pas la première occurence, on retourne 0. Ce qui donne :
SI(NB.SI($G$1:H$1;H$1)=1;NB.SI($G$1:$K$1;H$1);0)

Le nombre d'occurence correspondant à 0,1,0,0,1 donne respectivement 3,2,0,0,0 (3 fois le 0, 2 fois le 1, les autres valeurs sont nulles car elles figurent précédemment dans la séquence)



Maintenant on peut compter le nombre de chaque valeur d'une ligne des colonnes A:E, pour l'exemple (ligne A9:E9 ==> 1,0,0,0,0).
Si on veut compter le nombre de 1, on calcule :NB.SI($A9:$E9;H$1)

Cependant il ne faut prendre en compte ce nombre de 1 que s'il est inférieur à ou égal au nombre de 1 de la séquence de référence d'où la formule:
MIN( NB.SI($A9:$E9;H$1) ; (nombre de 1 dans la séquence de référence) )
soit MIN( NB.SI($A9:$E9;H$1) ; SI(NB.SI($G$1:H$1;H$1)=1;NB.SI($G$1:$K$1;H$1);0))

Si on prend la valeur 0, elle apparaît 4 fois dans 1,0,0,0,0 mais comme il n'y a que 3 fois 0 dans la séquence de référence, le min(4,3) donne bien 3.

Si on traite une valeur de la séquence de référence qui apparaît pour la seconde fois ou plus (pour l'exemple colonne K où figure le 2ième 1), on a dit plus haut qu'on retournait une valeur nulle pour le nombre d'occurence dans cette séquence de référence ( cf SI(NB.SI($G$1:K$1;K$1)=1;NB.SI($G$1:$K$1;K$1);0) ==> égal 0 )

MIN( NB.SI($A9:$E9;K$1) ; SI(NB.SI($G$1:K$1;K$1)=1;NB.SI($G$1:$K$1;K$1);0)) devient
MIN( NB.SI($A9:$E9;K$1) ; 0) et est donc égal à 0.
On s'assure ainsi de ne pas compter plusieurs fois les occurences d'une même valeur.

Et on fait cela pour chaque colonne de la séquence de référence. ce qui donne pour une ligne en mettant les $ qui vont bien pour pouvoir copier la formule vers le bas:
=SOMME( MIN(NB.SI($A9:$E9;G$1);SI(NB.SI($G$1:G$1;G$1)=1;NB.SI($G$1:$K$1;G$1);0)) ; MIN(NB.SI($A9:$E9;H$1);SI(NB.SI($G$1:H$1;H$1)=1;NB.SI($G$1:$K$1;H$1);0)) ; MIN(NB.SI($A9:$E9;I$1);SI(NB.SI($G$1:I$1;I$1)=1;NB.SI($G$1:$K$1;I$1);0)) ; MIN(NB.SI($A9:$E9;J$1);SI(NB.SI($G$1:J$1;J$1)=1;NB.SI($G$1:$K$1;J$1);0)) ; MIN(NB.SI($A9:$E9;K$1);SI(NB.SI($G$1:K$1;K$1)=1;NB.SI($G$1:$K$1;K$1);0)) )

 

[CISCO]

Re : Formule sommeprod ou autre?

Bonjour

Une variante, toujours avec une colonne intermédiaire

@ plus

 

[ROGER2327]

Re : Formule sommeprod ou autre?

Bonsoir à tous

=cpt($G$1:$K$1;$A$2:$E$14;4)

en L1 et

=cpt($G$1:$K$1;$A$2:$E$14;5)

en M1 où cpt est la fonction

Function cpt&(pat, dat, nb&)
'Application.Volatile '(selon les goûts...)
Dim i&, j&, k&, kp(), ip(), pt(), pl As New Dictionary
    pt = pat.Value
    For j = 1 To UBound(pt, 2)
        If pl.Exists(pt(1, j)) Then pl(pt(1, j)) = 1 + pl(pt(1, j)) Else pl.Add pt(1, j), 1
    Next
    kp = pl.Keys
    ip = pl.Items
    pt = dat.Value
    For i = 1 To UBound(pt, 1)
        pl.RemoveAll
        For j = 1 To UBound(pt, 2)
            If pl.Exists(pt(i, j)) Then pl(pt(i, j)) = 1 + pl(pt(i, j)) Else pl.Add pt(i, j), 1
        Next j
        k = 0
        For j = 0 To UBound(kp)
            If pl.Exists(kp(j)) Then k = k + WorksheetFunction.Min(ip(j), pl(kp(j)))
        Next j
        cpt = cpt - (k = nb)
    Next i
    Set pl = Nothing
End Function

ROGER2327
#5435

Dimanche 8 Gueules 139 (Fête de la Chandelle Verte - fête Suprême Tierce)
13 Pluviôse An CCXX, 1,2165h - laurier
2012-W05-4T02:55:11Z

 

[JJ1]

Re : Formule sommeprod ou autre?

Bonjour à tous,
Merci pour toutes vos solutions que je testerai ce soir (je n'ai pas excel ici)
Déjà la formule de Mapomme est impressionnante...
Je précise q'une colonne intermédiaire ne pose pas problème.

ps: merci Roger pour ton code, les nombres "recherche" sont fournis par une macro alea.
Il me faudrait donc intégrer mon bout de code alea dans ta macro


Bonne journée et encore merci.

je vous tiens au courant.

 

[job75]

Re : Formule sommeprod ou autre?

Bonjour à tous,

On peut adapter la formule de CISCO pour éviter la colonne intermédiaire :

- en L3 :

=SOMMEPROD(N(4=5-NBCAR(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(G3&H3&I3&J3&K3;A5:A17;"";1);B5:B17;"";1);C5:C17;"";1);D5:D17;"";1);E5:E17;"";1))))

- en M3 :

=SOMMEPROD(N(5=5-NBCAR(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(G3&H3&I3&J3&K3;A5:A17;"";1);B5:B17;"";1);C5:C17;"";1);D5:D17;"";1);E5:E17;"";1))))

Fichier joint.

Edit : on économise un degré d'imbrication de fonction en écrivant en L3 :

=SOMMEPROD(--(4=5-NBCAR(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(G3&H3&I3&J3&K3;A5:A17;"";1);B5:B17;"";1);C5:C17;"";1);D5:D17;"";1);E5:E17;"";1))))

Le nombre d'imbrications est en effet limité à 8 sur Excel 2003.

A+

 

[CISCO]

Re : Formule sommeprod ou autre?

Bonjour à tous

Bien vu Job75, l'utilisation de SOMMEPROD dans ce cas pour éviter l'utilisation de la colonne intermédiaire.

"Malheureusement", mapomme m'a fait remarqué que ma proposition ne donne pas les bons résultats si il y a des nombres dans les cellules, et pas uniquement des chiffres. Histoire de traiter cette possibilité, il va donc falloir chercher encore un peu.

@ plus

 

[CISCO]

Re : Formule sommeprod ou autre?

Rebonjour à tous

Ci-joint, une variante fonctionnant (je l'espère) avec des nombres, avec ou sans colonne intermédiaire (dans ce dernier cas à partir de la méthode proposée par Job75).

@ plus

 

[job75]

Re : Formule sommeprod ou autre?

Re,

Malheureusement", mapomme m'a fait remarqué que ma proposition ne donne pas les bons résultats si il y a des nombres dans les cellules, et pas uniquement des chiffres. Histoire de traiter cette possibilité, il va donc falloir chercher encore un peu.

J'ai cherché en encadrant les valeurs par des espaces : attention, 2 espaces entre les valeurs des cellules G3:K3.

On supprime chaque valeur avec les espaces à gauche et à droite.

A la fin on compte le nombre d'espaces supprimés et l'on divise par 2.

Je pense donc que cette formule en L3 est correcte :

=SOMMEPROD(--(4=5-(NBCAR(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(" "&G3&"  "&H3&"  "&I3&"  "&J3&"  "&K3&" ";" "&A5:A17&" ";"";1);" "&B5:B17&" ";"";1);" "&C5:C17&" ";"";1);" "&D5:D17&" ";"";1);" "&E5:E17&" ";"";1))-NBCAR(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(" "&G3&"  "&H3&"  "&I3&"  "&J3&"  "&K3&" ";" "&A5:A17&" ";"";1);" "&B5:B17&" ";"";1);" "&C5:C17&" ";"";1);" "&D5:D17&" ";"";1);" "&E5:E17&" ";"";1);" ";"")))/2))

Fichier (2).

Edit : non ça ne va pas : entrez 3 en J3...

A+

 

[CISCO]

Re : Formule sommeprod ou autre?

Bonjour

Presque la même idée de mon coté :

Je met deux virgules entre chacun des nombres, et une au début et à la fin.
Je remplace les termes communs par x.
Je compte le nombre de x, c'est à dire le nombre de nombres qu'on a pu remplacer.

@ plus

 

[job75]

Re : Formule sommeprod ou autre?

Re,

En fait si, la formule de mon post #13 est OK.

Mais à mon avis il vaut mieux replacer = par <= en L3 :

=SOMMEPROD(--(4<=5-(NBCAR(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(" "&G3&"  "&H3&"  "&I3&"  "&J3&"  "&K3&" ";" "&A5:A17&" ";"";1);" "&B5:B17&" ";"";1);" "&C5:C17&" ";"";1);" "&D5:D17&" ";"";1);" "&E5:E17&" ";"";1))-NBCAR(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(" "&G3&"  "&H3&"  "&I3&"  "&J3&"  "&K3&" ";" "&A5:A17&" ";"";1);" "&B5:B17&" ";"";1);" "&C5:C17&" ";"";1);" "&D5:D17&" ";"";1);" "&E5:E17&" ";"";1);" ";"")))/2))

Fichier (3).

A+