Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

Formule sommeprod ou autre?

Créer une discussion
  • Initiateur de la discussion Initiateur de la discussion JJ1
  • Date de début Date de début

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

S'inscrire
J

JJ1

Guest
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.
 

Pièces jointes

Trier par date Trier par votes
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é
 
Vote positif 0 Vote négatif
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.
 
Dernière édition:
Vote positif 0 Vote négatif
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)) )
 
Dernière édition:
Vote positif 0 Vote négatif
Re : Formule sommeprod ou autre?

Bonsoir à tous


Code: 
=cpt($G$1:$K$1;$A$2:$E$14;4)
en L1 et
Code: 
=cpt($G$1:$K$1;$A$2:$E$14;5)
en M1 où cpt est la fonction
VB: 
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
 
Vote positif 0 Vote négatif
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.
 
Vote positif 0 Vote négatif
Re : Formule sommeprod ou autre?

Bonjour à tous,

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

- en L3 :

Code: 
=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 :

Code: 
=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 :

Code: 
=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+
 

Pièces jointes

Dernière édition:
Vote positif 0 Vote négatif
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
 
Vote positif 0 Vote négatif
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
 

Pièces jointes

Vote positif 0 Vote négatif
Re : Formule sommeprod ou autre?

Re,


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 :

Code: 
=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+
 

Pièces jointes

Dernière édition:
Vote positif 0 Vote négatif
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
 
Vote positif 0 Vote négatif
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 :

Code: 
=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+
 

Pièces jointes

Vote positif 0 Vote négatif
Identifiez-vous ou inscrivez-vous pour participer.
Rejoins nos membres "Supporter XLD" et booste ton expérience Excel !
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD
Assurez vous de marquer un message comme solution pour une meilleure transparence.

Discussions similaires

L
  • Résolu(e)
Microsoft 365 Calcul dans un TCD ou PowerPivot
Réponses
4
Affichages
631
Laurent78
L
  • Résolu(e)
XL 2021 Une aide pour optimiser du code VBA
2
Réponses
16
Affichages
705
jeff1494
A
  • Question Question
XL 2010 Problème ou alternative à SOMPRODUCT en VBA
Réponses
14
Affichages
845
bruno lemaire
B
O
  • Résolu(e)
Microsoft 365 Effacer le contenu de cellules si une autre cellule est vide
Réponses
9
Affichages
607
Olihoes
O
XLS : Fonctionnement des chiffres Unicode
Réponses
0
Affichages
469
Lu76Fer
S
  • Résolu(e)
Microsoft 365 Formule qui me permet de regrouper les résultats de plusieurs lignes en fonction d'une recherche par nom
Réponses
9
Affichages
522
Sylvia57
S
EXCEL :: TRIER - TRIER.PAR - SOMMEPROD - CHOISIR - INDEX - SOMME.SI - NB.SI - DECALER - SEQUENCE : Tris dynamiques, regroupements et somme
Réponses
0
Affichages
549
oguruma
  • Question Question
Autres =si ou = sommeprod je suis perdu
Réponses
7
Affichages
1 K
rites
J
  • Question Question
XL 2016 Optimisation SOMMEPROD (résolu)
Réponses
11
Affichages
1 K
tbft
C
  • Résolu(e)
Microsoft 365 Doublons : mise en surbrillance erronée ?
Réponses
10
Affichages
921
TooFatBoy
A
  • Question Question
XL 2016 Probleme SommeProd sur Excel et sur VBA
Réponses
4
Affichages
595
aurelio.ewane
A
W
  • Question Question
Microsoft 365 Formule combinant multiples critères et OU
Réponses
4
Affichages
669
Wiil85
W
A
  • Résolu(e)
XL 2016 Plage de données fixes pour calcul dans formule
Réponses
3
Affichages
553
sylvanu
  • Question Question
XL 2021 Comment supprimer des noms du gestionnaire de noms dans un classeur
Réponses
7
Affichages
1 K
jeff1494
P
  • Résolu(e)
XL 2016 Besoin d'aide pour Formule Excel : Avoir une condition dans les calculs de SOMME
Réponses
3
Affichages
712
sylvanu
S
  • Résolu(e)
Microsoft 365 - Conseils pour la structuration d'une base de données -
2 3
Réponses
44
Affichages
4 K
crocrocro
C
  • Résolu(e)
XL 2021 recherche supprimer les espaces '"si valeur cherchée est numérique" sinon NON
Réponses
7
Affichages
879
Usine à gaz
P
  • Résolu(e)
XL 2016 Demande d'avis sur la possibilité d'un système de recherche basé sur une base de données dans Excel
2 3 4
Réponses
45
Affichages
3 K
Pauuul35
P
  • Résolu(e)
XL 2010 Remplissage cellules planning avec dates
Réponses
5
Affichages
984
Orson83
F
  • Question Question
Microsoft 365 Remplissage automatique d'un tableau d'après des tableaux de plusieurs feuilles différentes.
Réponses
8
Affichages
1 K
Fëa
F
Menu
Connexion
S'inscrire
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…