Somme prod si et autres formules si besoin Le sujet est résolu

Y compris Power BI, Power Query et toute autre question en lien avec Excel
C
CatherineJobin
Jeune membre
Jeune membre
Messages : 11
Inscrit le : 25 février 2020
Version d'Excel : 2018

Message par CatherineJobin » 25 février 2020, 21:39

Bonjour,

À mon travail nous désirons sortir des statistiques.

Afin de ne pas tout faire à la mitaine, j'ai voulu commencer un tableau excel avec des formules. J'ai trouvé la formule SOMMEPROD que je trouve très intéressante, par contre, elle calcule les cellules vides, ce qui cause des erreurs dans mes résultats.

Voici ce que je souhaite faire (pièce jointe en annexe).

Je dois sortir des statistiques en prenant en considération des dates. Les données doivent être triées par mois. Donc, par exemple, le nombre de demandes entrées par mois de janvier, février, mars, etc. Seulement SOMMEPROD prend en considération les plages vides (voir dans l'exemple, j'ai mis la plage plus large des données)

Je dois également connaître le nombre de demandes fermées par mois, donc je crois bien utiliser la même formule que pour le nombre de demandes entrées par mois.

Ensuite, je dois sortir le nombre de demande toujours en traitement, je pensais prendre un SOMMEPROD SI qui dirait, Si il y une date dans la ligne de demande en traitement, mais pas dans celle de demande autorisée, c'est que la demande est en traitement.

Mais comme SOMMEPROD calcule les cellules vides (ce qu'il y aura assurément dans le document) je suis un peu perdue. Également, il y a peut-être une autre formule qui peut répondre à mes besoins, mais que je ne connais pas. Merci pour votre précieuse aide.
exempleprobleme.xlsx
(11.26 Kio) Téléchargé 5 fois
Avatar du membre
X Cellus
Membre dévoué
Membre dévoué
Messages : 922
Appréciations reçues : 127
Inscrit le : 13 octobre 2019
Version d'Excel : 2007 FR

Message par X Cellus » 25 février 2020, 22:01

Bonsoir CatherineJobin,

Ci-joint ton fichier en retour.
Voir les modifs sur les cellules vertes. Et les formules inscrites en caractères rouge.

Bonne continuation.
exempleproblemeModif.xlsx
(9.64 Kio) Téléchargé 1 fois
Avatar du membre
X Cellus
Membre dévoué
Membre dévoué
Messages : 922
Appréciations reçues : 127
Inscrit le : 13 octobre 2019
Version d'Excel : 2007 FR

Message par X Cellus » 25 février 2020, 22:11

A nouveau,


Correction, dernière ligne oubliée lors de mon récent envoi. Faire de même avec les autres cellules.

Renvoi ci-dessous.

Bonne continuation.
exempleproblemeModif.xlsx
(9.73 Kio) Téléchargé 3 fois
C
CatherineJobin
Jeune membre
Jeune membre
Messages : 11
Inscrit le : 25 février 2020
Version d'Excel : 2018

Message par CatherineJobin » 26 février 2020, 15:46

Bonjour,

Merci de m'avoir répondu si rapidement.

Malheureusement, les formules proposées ne répondent pas tout à fait à mon problème.

La formule nombre si repose sur les chiffres en-dessous des dates, alors que je ne peux avoir ces chiffres dans mon gros tableau.

et la formule de SI, repose sur les dates et non le nombre de fois qu'un mois se répète dans les dates données.

Je souhaite obtenir un tableau (le petit tableau dans l'exemple) qui aura la somme du nombre de fois qu'une date (avec le mois de novembre, par exemple) se répète dans mon tableau.

Même chose pour la demande autorisée.

Pour la demande en traitement, il comptabiliser la date s'il n'y a pas de date d'inscrit dans la ligne de demande autorisée.

J'espère avoir été plus clair dans mes explications.

Je sais que mon problème n'est pas simple.

Merci pour votre aide.
U
U. Milité
Membre impliqué
Membre impliqué
Messages : 2'026
Appréciations reçues : 143
Inscrit le : 30 novembre 2016
Version d'Excel : Office 365

Message par U. Milité » 27 février 2020, 08:58

Bonjour,

Pas certain d'avoir bien compris :scritch:

Pour Excel, extraire le mois d'une cellule vide donnera 1 (0 au format Date affichera le 0/01/1900). Sachant cela, on peut modifier la formule en B15 comme suit:
=SOMMEPROD((MOIS($B$4:$G$4)=B14)*($B$4:$G$4>0))
Est-ce que ça répond à la première partie de la question ?
C
CatherineJobin
Jeune membre
Jeune membre
Messages : 11
Inscrit le : 25 février 2020
Version d'Excel : 2018

Message par CatherineJobin » 3 mars 2020, 20:32

Bonjour,

Désolée pour la courte absence. Oui, cela fonctionne!!

La formule ne compte pas les cases vides, merci!

Est-ce que vous penser trouver pour ma deuxième question?

Si j'ai une demande en traitement qui n'a pas de date dans les demandes fermés, c'est qu'elle est toujours en traitement et il faut la calculer de la même façon que vous m'avez suggéré pour ma première question.

Merci encore!
U
U. Milité
Membre impliqué
Membre impliqué
Messages : 2'026
Appréciations reçues : 143
Inscrit le : 30 novembre 2016
Version d'Excel : Office 365

Message par U. Milité » 4 mars 2020, 08:47

Bonjour,

Ton problème n'est peut-être pas simple ... mais imagine pour nous, qui ne savons pas bien ce que tes dates représentent, pas plus que tes demandes autorisées/fermées/terminées !! :roll:

Si en ligne 17, tu utilises le même type de formules qu'en ligne 15 ... donc, en B17:
=SOMMEPROD((MOIS($B$10:$G$10)=B14)*($B$10:$G$10>0))
tu obtiens le nombre de demandes "fermées" pour chaque mois (si j'ai compris !?). Ne peut-on pas, dès lors, simplement soustraire les résultats de la ligne 17 à ceux de la ligne 15, pour connaître le nombre de demandes "en traitement" ?
C
CatherineJobin
Jeune membre
Jeune membre
Messages : 11
Inscrit le : 25 février 2020
Version d'Excel : 2018

Message par CatherineJobin » 4 mars 2020, 14:44

Bonjour,

Effectivement, lorsqu'on est en dehors du contexte ça peut être difficile de savoir.

Le tableau doit se lire comme suit : une colonne représente une demande, et indique sa date d'entrée, sa date de traitement et peut-être sa date de fermeture si c'est le cas.

Ensuite, je dois en extraire, d'un grand tableau, ses différentes dates, par mois, pour un avoir une idée de combien de travaux sont entrées en février (par exemple), combien sont encore en traitement et combien sont fermées.

Je crois que l'idée de la soustraction peut être bonne, mais je prendrais plus les lignes 16 et 17.

J'arrive à presque la bonne idée, en fait, si une demande à une date de fermeture, c'est quelle n'est plus en traitement, alors elle ne doit pas se comptabiliser dans les demandes en traitement.

Dans mon exemple en fichier joint, comme la demande 10 et 11 ont une date de fermeture, et qu'il n'y a pas d'autre demande en octobre en traitement, il ne devrait pas afficher 1 et je me demande pourquoi j'ai un problème de valeur également....

Merci!
exempleprobleme2.xlsx
(11.42 Kio) Téléchargé 2 fois
U
U. Milité
Membre impliqué
Membre impliqué
Messages : 2'026
Appréciations reçues : 143
Inscrit le : 30 novembre 2016
Version d'Excel : Office 365

Message par U. Milité » 4 mars 2020, 15:09

CatherineJobin a écrit :
4 mars 2020, 14:44
Dans mon exemple en fichier joint, comme la demande 10 et 11 ont une date de fermeture, et qu'il n'y a pas d'autre demande en octobre en traitement, il ne devrait pas afficher 1
C'est bien pour ça que je proposais, en B16, de faire simplement
=B15-B17
... Mais peut-être que je n'ai rien compris !?
C
CatherineJobin
Jeune membre
Jeune membre
Messages : 11
Inscrit le : 25 février 2020
Version d'Excel : 2018

Message par CatherineJobin » 4 mars 2020, 20:57

Oui, tu as bien compris. Merci! Des fois on voit pas la simplicité hahaha!

D'ailleurs, j'ai ajouter dans la formule l'année, car éventuellement, il y aura dans mon tableau, l'année 21, comme suit :

=SOMMEPROD((MOIS(B21:F21)=B14)*(B21:F21>0)*(ANNEE(B21:F21)=A20))

La formule ci-dessus fonctionne, par contre, la formule ci-dessous, que j'ai essayé dans mon gros tableau, me donne un résultat VALEUR!, j'ai cru que c'était à cause des cellules vides, mais j'ai essayé avec ma première formule et cela fonctionne.

=SOMMEPROD((MOIS('Extraction travaux'!$Q$2:$Q$928)=B6)*('Extraction travaux'!$Q$2:$Q$928>0)*(ANNEE('Extraction travaux'!$Q$2:$Q$928)=$A$5))

Je comprends pas, j'ai beau les regarder, la structure me semble identique...

Merci!
Répondre
  • Sujets similaires
    Réponses
    Vues
    Dernier message