Sommeprod qui me résiste !!! ça alors

Lolote83

XLDnaute Accro
Bonjour à tout le forum,
Après quelques mois d’absences (très gros projet en perceptive) mes apparitions sur ce forum se sont fait plus rares et du coup mes réponses aussi.
Mais aujourd'hui, c'est bien moi qui ai besoin de vous.
Je pense sincèrement qu'avec un "sommeprod", on devrait s'en sortir mais .....
Vous trouverez toutes les explications nécessaires dans le fichier (onglet explication)
Cordialement
Lolote83
 

Fichiers joints

Mytå

XLDnaute Occasionnel
Re : Sommeprod qui me résiste !!! ça alors

Salut le Forum

En F34
Code:
=SOMMEPROD(((($C$3:$C$27=1)+($D$3:$D$27=1))>0)*(F$3:F$27=1)*(E$3:E$27=0))
En G34
Code:
=SOMMEPROD(((($C$3:$C$27=1)+($D$3:$D$27=1)+($E$3:$E$27=1))>0)*(G$3:G$27=1)*(F$3:F$27=0))
En H34
Code:
=SOMMEPROD(((($C$3:$C$27=1)+($D$3:$D$27=1)+($E$3:$E$27=1)+($F$3:$F$27=1))>0)*(H$3:H$27=1)*(G$3:G$27=0))
Mytå
 

chris

XLDnaute Barbatruc
Re : Sommeprod qui me résiste !!! ça alors

Coucou Myta

Quand on parle du loup...
J'ai justement évoqué un de tes posts aujourd'hui à propos des filtres avancés.
Cela fait plaisir de te voir sur XLD.
 

ROGER2327

XLDnaute Barbatruc
Re : Sommeprod qui me résiste !!! ça alors

Bonjour à tous.


À Lolote83 : je tourne en rond depuis un bon moment, incapable de comprendre pourquoi le résultat attendu en F32 est 5. Pouvez-vous éclairer ma lanterne ?

Merci d'avance.

(Si, par hasard, le résultat réellement attendu était 3, je proposerais
Code:
=SOMMEPROD((($C3:$C27+$D3:$D27*(COLONNE()>COLONNE($E:$E))+$E3:$E27*(COLONNE()>COLONNE($F:$F))+$F3:$F27*(COLONNE()>COLONNE($G:$G))+$G3:$G27*(COLONNE()>COLONNE($H:$H)))>0)*(D3:D27<>1)*E3:E27)
en E32, à recopier à droite jusqu'en I32.)
Dommage qu'il faille trouver 5...


ROGER2327
#6691


Mardi 3 Tatane 140 (Sainte Crapule, puriste et Saint Fantomas, archange - fête Suprême Quarte)
28 Messidor An CCXXI, 0,7295h - vesce
2013-W29-2T01:45:03Z
 

Lolote83

XLDnaute Accro
Re : Sommeprod qui me résiste !!! ça alors

Bonjour à tous,
Merci pour vos précieuses réponses.
Je me renseigne auprès de mon collègue pour savoir si c'est bien cela qu'il voulait car la réflexion de ROGER2327 me laisse maintenant un doute.
je tourne en rond depuis un bon moment, incapable de comprendre pourquoi le résultat attendu en F32 est 5. Pouvez-vous éclairer ma lanterne ?
Je pense d'ailleurs que c'est lui qui a raison. Bien vu l'artiste mais je me renseigne quand même et vous tiens au courant.
C'est beau l'entraide.
A très vite pour valider (ou malheureusement invalider vos réponses si Fred me dit que c'est faux !!!! Sacré Fred.)
Cordialement
Lolote83
 

MJ13

XLDnaute Barbatruc
Re : Sommeprod qui me résiste !!! ça alors

Re

Moi, j'aurais plutôt dit sacré Lolote, c'est quand même Lolote qui a mis les explications sans les comprendre (c'est vrai que c'était pas très clair ;)).
 

ROGER2327

XLDnaute Barbatruc
Re : Sommeprod qui me résiste !!! ça alors

Bonsoir à tous.


(...)
Je me renseigne auprès de mon collègue pour savoir si c'est bien cela qu'il voulait car la réflexion de ROGER2327 me laisse maintenant un doute.
(...)
Je précise la raison de mon doute.
Du préambule (onglet Explications), je déduis que :
Dans chaque colonne, 1 indique qu'un individu est adhérent pour l'année correspondante.

De
"Pour 2005 cela donnerait :
Nombre de personne(s) inscrites en 2005, non inscrites en 2004, mais qui ont été inscrit(e)s au moins une fois de 2000 à 2003"
et
"Pour 2006 cela donnerait :
Nombre de personne(s) inscrites en 2006, non inscrites en 2005, mais qui ont été inscrit(e)s au moins une fois de 2000 à 2004"
,​


j'induis que le problème général peut s'énoncer comme suit :


Pour l'année A (>2001), trouver le nombre d'individus présentant la conjonction des critères suivants :
  1. Ils sont adhérents pour l'année A.
  2. Ils ne sont pas adhérents pour l'année A-1.
  3. Ils sont adhérents au moins une année dans la période allant des années 2000 à A-2 (2000 et A-2 inclus).


En supposant que j'ai compris, et prenant le cas de l'année 2003, je trouve que les trois seuls Messieurs Toto 13, Toto 15 et Toto 23 répondent aux trois conditions.

Le nombre d'individus attendu étant cinq (les trois Totos susnommés plus Messieurs Toto 10 et Toto 20, si j'en crois l'onglet 2003), je dois rejeter ma supposition et conclure que je n'ai pas compris.

En fait, le résultat attendu pour l'année A semblent être le nombre d'individus adhérents l'année A qui n'étaient pas adhérents l'année A-1.

Nonobstant mon erreur, je poursuis avec ces formules matricielles :
Code:
=SOMME((E3:E27=1)*(D3:D27<>1)*(PRODUITMAT(--($C3:C27=1);TRANSPOSE(COLONNE($A:A)))>0))
ou simplement :
Code:
=SOMME(E3:E27*(D3:D27<>1)*(PRODUITMAT(--$C3:C27;TRANSPOSE(COLONNE($A:A)))>0))
ou encore :
Code:
=SOMMEPROD(E3:E27;(D3:D27<>1)*(PRODUITMAT(--$C3:C27;TRANSPOSE(COLONNE($A:A)))>0))
en E32, à recopier à droite autant qu'il convient. (C'est plus convenable que le bricolage proposé au message #5.)​


Bonne nuit.


ROGER2327
#6693


Jeudi 5 Tatane 140 (Saint Arsouille, patricien - fête Suprême Quarte)
30 Messidor An CCXXI, 9,7419h - chalémie
2013-W29-4T23:22:50Z
 

Lolote83

XLDnaute Accro
Re : Sommeprod qui me résiste !!! ça alors

Re bonjour à tous,
Salut Roger2327,
J'ai bien essayé d'adapter ta fonction dans le fichier original mais je me retrouve avec des #valeurs.
J'ai du coup essayé de comprendre la fonction Produitmat combiné avec la fonction transpose mais apparemment cela ne fonctionne pas toujours. Sur le fichier exemple transmis (ExempleFred) ça fonctionne, mais sur l'original, j'ai un souci.
Encore une petite aide ????
Merci à vous tous
Cordialement
Lolote83
 

Fichiers joints

ROGER2327

XLDnaute Barbatruc
Re : Sommeprod qui me résiste !!! ça alors

Re...


(...)
J'ai bien essayé d'adapter ta fonction dans le fichier original mais je me retrouve avec des #valeurs.
J'ai du coup essayé de comprendre la fonction Produitmat combiné avec la fonction transpose mais apparemment cela ne fonctionne pas toujours. Sur le fichier exemple transmis (ExempleFred) ça fonctionne, mais sur l'original, j'ai un souci.
(...)
Comme cela arrive souvent, nous sommes en présence d'une demande de solution pour un problème donné suivi d'une application de la solution (correcte pour le problème donné) à un autre problème.

En effet, dans le problème original, la plage de données ne contient que valeurs assimilables à des nombres. Dans le cas présent, le nombre 1 et des cellules vides assimilables au nombre 0.

Dans le nouveau problème, la plage de données contient, outre les valeurs ci-dessus, une ou plusieurs valeurs inassimilables à un nombre. Par exemple une chaîne de caractères de longueur non-nulle, comme en I190.

La fonction PRODUITMAT ne s’appliquant qu'à des matrices numériques ne peut que renvoyer une valeur d'erreur si vous lui demandez de traiter une chaîne de caractères.

Remèdes :
Il en existe au moins deux :
  1. Si on peut se passer des données non-numériques, nettoyer la plage de données en les virant.
  2. Si les données non-numériques sont nécessaires, écrire les formules autrement.


La première solution étant pénible manuellement, on peut la réaliser en exécutant la routine
VB:
Sub nett()
Dim Cel As Range
  For Each Cel In Range("E6:AM358").Cells
    If Not IsNumeric(Cel.Value) Then Cel.Value = Empty
  Next
End Sub
dans la feuille Histo CED. Après quoi les formules, nourries des données pour lesquelles elles sont écrites, ne renvoient plus de valeur d'erreur.


La deuxième solution consiste à modifier les formules comme suit :
  1. En G364 :
    Code:
    =SOMME((G6:G358=1)*(F6:F358<>1)*(PRODUITMAT(--($E6:E358=1);TRANSPOSE(COLONNE($A:A)))>0))
  2. En G365 :
    Code:
    =SOMMEPROD(G6:G358;(F6:F358<>1)*(PRODUITMAT(--($E6:E358=1);TRANSPOSE(COLONNE($A:A)))>0))
    (Ne pas oublier de valider par Ctrl Maj Entrée !)

  3. Sélectionner ces deux cellules et recopier vers la droite autant qu'il convient.

Fonctionnement :
Soit la plage A1:C3
A​
B​
C​
1toto10
22327VRAI
31FAUX-1
Utilisée dans la fonction PRODUITMAT elle provoque une erreur (à cause, par exemple, de toto en A1).

Si on écrit la formule
Code:
=A1:C3=1
on obtient la matrice
FAUXVRAIFAUX
FAUXFAUXFAUX
VRAIFAUXFAUX
A1:C3=1 ne convient pas encore à PRODUITMAT... Il faut une matrice numérique, qu'on obtient en écrivant
Code:
=1*(A1:C3=1)
ou
Code:
=--(A1:C3=1)
On obtient alors la matrice
010
000
100
que PRODUITMAT digère sans vomir.


Remarque : On ne peut pas mettre n'importe quoi dans la plage de données ; avec, par exemple
A​
B​
C​
1#N/A10
2#DIV/0!VRAI
3#NOMBRE!#VALEUR!-1
Code:
=--(A1:C3=1)
renvoie
#N/A10
#DIV/0!00
#NOMBRE!#VALEUR!0


Bonne soirée.


ROGER2327
#6702


Mardi 10 Tatane 140 (Saints Pieds Nickelés, trinité - fête Suprême Quarte)
5 Thermidor An CCXXI, 5,9890h - bélier
2013-W30-2T14:22:25Z
 

Lolote83

XLDnaute Accro
RESOLU = Sommeprod qui me résiste !!! ça alors

Salut ROGER2327,
Je te remercie beaucoup pour ta réponse nette,rapide et hyper bien expliquée.
Je pensais bien à une cellule non vide mais je n'ai pas eu la présence d'esprit de la chercher comme tu l'as fait. J'avais simplement sélectionné la base et mis le format nombre mais je n'ai pas pensé à un espace.
Bref, effectivement, une fois la cellule I190 rectifiée, cela fonctionne à merveille. Je ne suis pas si nul que ça du coup car javais bien compris ta formule et su la retranscrire.
Je continue donc grâce à tes explications à apprendre......Super merci
@+ Lolote83
 

Discussions similaires


Haut Bas