fonction NB.SI() intégrant des fonctions de date : MOIS() ou ANNEE()

Version imprimable

13/03/2021, 06h21
Togno

fonction NB.SI() intégrant des fonctions de date : MOIS() ou ANNEE()

Bonjour,

Est-il possible de faire des test logique avec la famille des 'si' en utilisant les fonctions pour extraire des morceaux de date ?

Je fais un test avec NB.SI()

Ma plage contient des dates,

J'utilise MOIS() pour n'avoir que le mois, et je souhaite compter les mois de janvier

J'ai donc

NB.SI(
MOIS(K2:K5);
1)

Si je clique sur le petit f pour voir la formule; excel me retourne bien les valeurs attendues (1,2,3,4 pour mes dates qui sont janvier février mars avril), mon critère est bien 1

Je devrais donc avoir 1 en résultat (on a trouvé un mois de janvier)

Pourtant impossible de valider la formule. Quelque chose cloche. Je ne comprend pas quoi.

http://puu.sh/HoGeJ/25d9e63734.png

Je vois beaucoup de gens contourner en utilisant sommeprod() mais dans mon cas ce ne serait pas pratique.

Merci.
13/03/2021, 08h00
78chris

Bonjour

NB.SI & co ne permettent pas d'appliquer de calcul à la plage de critères

Il faut utiliser NB.SI.ENS et utiliser 2 critères

Code:

=NB.SI.ENS(K2:K5;">="&DATE(2021;1;1);K2:K5;"<="&FIN.MOIS(DATE(2021;1;1);0))
13/03/2021, 08h05
galopin01

Bonjour,
NB.SI attend comme 1er argument une plage :
MOIS(K2:K5) n'est pas une plage !
Il faut créer une colonne intermédiaire (L2:L5 par exemple) qui contiendra =Mois(K2)
et ensuite :
=NB.SI(L2:L5;1)
A+
13/03/2021, 12h32
Pierre Fauconnier

2 pièce(s) jointe(s)

Salut.

De mon point de vue, NB.SI est obsolète. J'utiliserais plus volontiers NB.SI.ENS⁽¹⁾ qui permet plusieurs critères, ce qui sera utile dans le cas illustré par Chris, puisque tu dois tester que la date est comprise dans une période.

Cela dit, si tu veux comme tu le dis dans ta question compter "les mois de janvier", NB.SI.ENS ne sera pas adapté puisque que devra additionner les NB.SI.ENS récupérant Janvier pour chaque année présente dans ton tableau. Tu n'auras d'autres choix que d'utiliser SOMMEPROD.

Pièce jointe 593332

Citation:

Envoyé par Togno

[...]
Je vois beaucoup de gens contourner en utilisant sommeprod() mais dans mon cas ce ne serait pas pratique.[...]

SOMMEPROD est en fait la formule générique des XXX.SI.ENS et qui peut le plus peut le moins. Je comprends donc difficilement que SOMMEPROD soit "moins pratique". Elle permet des conditions plus fines puisque, a contrario des XXX.SI.ENS, SOMMEPROD permet
de travailler sur des résultats de formules appliquées sur les valeurs, alors que XXX.SI.ENS travaillent uniquement sur tout ou partie⁽¹⁾ des valeurs des cellules (éventuellement reconstituées par formule). Perso et probablement par habitude, je la préfère aux XXX.SI.ENS;
d'utiliser l'arithmétique booléenne et donc des expressions du type Critère1 OU Critère 2 (et encore bien plus) là où les fonctions XX.SI.ENS ne permettent que Critère 1 ET Critère 2

⁽¹⁾ On peut utiliser les jokers * et ? avec XXX.SI.ENS comme pour les filtres automatiques

Pièce jointe 593333

⁽¹⁾ Dans les classeurs que je crée, j'utilise SOMMEPROD qui fait sauter les limites des XXX.SI.ENS.
13/03/2021, 17h36
Togno

Citation:

Envoyé par galopin01

Bonjour,
NB.SI attend comme 1er argument une plage :
MOIS(K2:K5) n'est pas une plage !
Il faut créer une colonne intermédiaire (L2:L5 par exemple) qui contiendra =Mois(K2)
et ensuite :
=NB.SI(L2:L5;1)
A+

Bonjour,

Merci pour ta réponse. Malheureusement mon fichier est très important et je ne peux pas me permettre d'ajouter 50 colonnes dedans pour faire des étapes de calcul intermédiaire. Cela ne conviendra donc pas.

Est-ce que tu peux m'expliquer pourquoi mois(k2:k5) n'est pas une plage ? Une plage n'est pas une suite de nombre ? (ou de valeur quelconques)

Citation:

Envoyé par Pierre Fauconnier

Salut.

Pour mois, NB.SI est obsolète. Je préfère utiliser NB.SI.ENS qui permet plusieurs critères, ce qui sera utile dans le cas illustré par Chris, puisque tu dois tester que la date est comprise dans une période.

Cela dit, si tu veux comme tu le dis dans ta question compter "les mois de janvier", NB.SI.ENS ne sera pas adapté puisque que devra additionner les NB.SI.ENS récupérant Janvier pour chaque année présente dans ton tableau. Tu n'auras d'autres choix que d'utiliser SOMMEPROD.
|...]

Bonjour,

Merci pour ta réponse.

J'ai vu NB.SI.ENS également, a vrai dire que je comptais utiliser celui là (car je dois tester le mois et l'année), mais je procède par étape en testant chaque fonction :D

Suite a vos conseils, j'ai tenté de faire ce que je veux avec sommeprod(), mais cela ne semble pas fonctionner comme je veux. sommeprod(mois( k2 : k5 )*1) ne me donne pas 1 (rappel k2 : k5 sont les mois de l'année) mais 0.

EDIT : une paire de parenthèse de plus et je suis le roi du monde, yes ! :ptdr:

Ecore EDIT : par contre ça m'ajoute les cellules vides. :calim2:

EDIT : et je veux bien apprendre a faire des "OU" avec sommeprod, tant qu'a faire. Cela semble intéressant.
13/03/2021, 18h20
Pierre Fauconnier

3 pièce(s) jointe(s)

Non... mois(k2:k5) n'est pas une plage. Dans une formule matricielle, ce sera une matrice, mais une plage, au sens Excel du terme, c'est un ensemble de cellules, pas un ensemble de valeurs. Une matrice est un ensemble de valeurs (issues éventuellement d'une plage). Mais les fonctions XXX.SI.ENS n'acceptent que des plages, pas des matrices.

Il faudrait voir tes valeurs pour comprendre ce qui ne fonctionne pas chez toi. Si tu regardes la copie d'écran que j'ai fournie, tu verras comment utiliser SOMMEPROD avec la fonction MOIS... Peut-être pourrais-tu nous mettre une copie d'écran?

En algèbre booléenne, le ET correspond à la multiplication et le OU à l'addition. Avant d'illustrer le OU, il faut rappeler que les valeurs booléennes VRAI et FAUX sont transformées en valeurs arithmétiques (VRAI=> 1 et FAUX => 0) lors d'une opération arithmétique. Je parle bien ici d'une opération (opérande Opérateur Opérande). Une fonction telle que SOMME n'est pas une opération arithmétique... Je dis bien transformées et je ne n'ai pas dit que "VRAI vaut 1 et FAUX vaut 0". Et j'insiste sur le fait qu'il faut une opération arithmétique pour que le transtypage soit réalisé⁽¹⁾.

Pièce jointe 593364

Pour illustrer le OU, je vais sommer les valeurs qui correspondent à "Pierre" ou "Martine" avec SOMME.SI.ENS puis avec SOMMEPROD. Avec SOMME.SI.ENS, je n'ai d'autre choix, sans colonne complémentaire, d'additionner deux SOMME.SI.ENS, alors que l'addition booléenne va me permettre de le faire "en une passe".

Pièce jointe 593358

Si l'on veut pouvoir rendre la liste des personnes à prendre en compte variable à souhait, on devra se tourner vers SOMMEPROD qui permet, sans modifier la formule, de prendre en compte autant de personnes que l'on veut là où SOMME.SI.ENS imposerait une modification de la formule pour libeller autant de SOMME.SI.ENS qu'il y a de personnes à prendre en compte...

Pièce jointe 593359

⁽¹⁾ En Excel 365, la validation matricielle (SHIFT+CTRL+ENTER qui ajoute les {}) n'est pas obligatoire, Excel comprenant la syntaxe comme étant matricielle.
13/03/2021, 21h31
Togno

Citation:

Envoyé par Pierre Fauconnier

Non... mois(k2:k5) n'est pas une plage. Dans une formule matricielle, ce sera une matrice, mais une plage, au sens Excel du terme, c'est un ensemble de cellules, pas un ensemble de valeurs. Une matrice est un ensemble de valeurs (issues éventuellement d'une plage). Mais les fonctions XXX.SI.ENS n'acceptent que des plages, pas des matrices.

Il faudrait voir tes valeurs pour comprendre ce qui ne fonctionne pas chez toi. Si tu regardes la copie d'écran que j'ai fournie, tu verras comment utiliser SOMMEPROD avec la fonction MOIS... Peut-être pourrais-tu nous mettre une copie d'écran?

En algèbre booléenne, le ET correspond à la multiplication et le OU à l'addition. Avant d'illustrer le OU, il faut rappeler que les valeurs booléennes VRAI et FAUX sont transformées en valeurs arithmétiques (VRAI=> 1 et FAUX => 0) lors d'une opération arithmétique. Je parle bien ici d'une opération (opérande Opérateur Opérande). Une fonction telle que SOMME n'est pas une opération arithmétique... Je dis bien transformées et je ne n'ai pas dit que "VRAI vaut 1 et FAUX vaut 0". Et j'insiste sur le fait qu'il faut une opération arithmétique pour que le transtypage soit réalisé⁽¹⁾.

Merci de votre réponse.

J'ai réussi à m'en sortir. En fait une cellule vide, si prise en date, est du premier janvier 1900 (elle n'est donc pas considérée comme vide pour un teste sur une date), ce qui me provoquait une erreur. J'ai ajouté un 2nd test pour que ces cellules soient refusées.

Par contre je veux bien, en dernier demande, un lien ou un explication sur comment faire une fonction OU avec sommeprod. Car autant je commence a bien comprendre comment empiler des conditions, autant je ne vois pas comment travailler avec deux conditions dont une seule suffit.

Merci pour votre aide !
13/03/2021, 22h31
78chris

Bonjour à tous

Heu, des lunettes ?

Citation:

Envoyé par Pierre Fauconnier

Pour illustrer le OU, je vais sommer les valeurs qui correspondent à "Pierre" ou "Martine" avec SOMME.SI.ENS puis avec SOMMEPROD. Avec SOMME.SI.ENS, je n'ai d'autre choix, sans colonne complémentaire, d'additionner deux SOMME.SI.ENS, alors que l'addition booléenne va me permettre de le faire "en une passe".

Pièce jointe 593358
14/03/2021, 10h33
Pierre Fauconnier

3 pièce(s) jointe(s)

Bah, Chris... Une lecture trop rapide, sans doute... ;) (parfois on se demande à quoi ça sert de donner des réponses détaillées ^^)

Trois choses à ajouter.

1) Le OU simulé par l'addition de deux SOMME.SI.ENS n'est réalisable que si les deux conditions s'excluent mutuellement, autrement dit si l'une renvoie FAUX lorsque l'autre renvoie VRAI, comme c'est le cas de mon illustration avec "Pierre" ou "Martine" car c'est l'un ou l'autre mais il est impossible que l'on soit à la fois Pierre et Martine. Dans les autres cas, ça devient pratiquement impossible de pouvoir s'en sortir avec SOMME.SI.ENS puisque des lignes risquent d'être comptabilisées plusieurs fois. Il faudra dans ce cas déduire les lignes qui remplissent plusieurs conditions grâce à la soustraction d'un troisième SOMME.SI.ENS. On se rend compte vite que SOMMEPROD est alors à privilégier, et que dans des cas un peu complexes, il n'y aura pas de possibilités avec les fonctoins XXX.SI.ENS.

Pièce jointe 593380

2) Voici quelques exemples d'arithmétique booléenne effectuée ligne par ligne

Pièce jointe 593381

3) Les mêmes conditions qu'au point 2, mais en dénombrant les lignes qui remplissent les conditions. On voit ici le *1 dans certaines formules pour forcer la somme des produits. Si l'on avait voulu non pas le nombre de lignes mais la somme des valeurs en fonction des conditions, on aurait dû multiplier par Tableau1[A] à l'intérieur de SOMMEPROD. Attention que les parenthèses ont leur importance pour bien séquencer les calculs...

Pièce jointe 593382
14/03/2021, 17h22
Togno

Bonjour,

Oui pardon Pierre, en effet je suis passé en coup de vent et je n'ai lu que le début avant de répondre. Mais je vous assure que vos réponses détaillées avec exemple etc sont TRES utiles et sont appréciées à la hauteur de ce qu'elles méritent !

Merci beaucoup pour votre aide. Je prend une journée pour vérifier que tout roule comme prévu et je passe le topic en résolu !
17/03/2021, 16h50
Pierre Fauconnier

Citation:

Envoyé par galopin01

[...]
Il faut créer une colonne intermédiaire (L2:L5 par exemple) qui contiendra =Mois(K2)[...]

Perso, je préfère une matricielle à une colonne intermédiaire, lorsque je peux m'en passer. A force de créer des colonnes intermédiaires, on se retrouve avec des tableaux contenant beaucoup trop de colonnes que l'on ne sait plus gérer et on se demande pourquoi les fichiers deviennent lourds.

C'est juste mon avis.