Discussion :

[Brigitte15]

Bonjour à tous,

Malgré mes recherches, je n'arrive pas à trouver ou à adapter une formule permettant de résoudre mon problème. C'est pour cette raison que je sollicite votre expertise.

En effet, je souhaite compter le nombre d'occurrences uniques selon 2 critères (mois et année) permettant dans l'exemple ci-joint d'identifier le nombre de nouveaux produits chaque mois sachant que les produits vendus chaque mois ne sont pas tous nouveaux.

J'ai essayé d'utiliser une formule SommeProd pour résoudre ce problème, en vain. Pourriez-vous m'aider à résoudre ce problème s'il vous plaît ?

Bien cordialement,

Produit	Mois	Années
produit A	12	2017
produit A	1	2018
produit A	2	2018
Produit B	1	2018
Produit B	12	2017
Produit C	12	2017
Produit D	3	2018
Produit D	2	2018
Produit E	2	2018
Produit F	2	2018
Produit G	1	2018

[Menhir]

Comme beaucoup de participants à ce forum, je n'ouvre pas les classeurs joints.
Cependant, je pense que la solution à ton problème pourrait être la fonction NB.SI.ENS().

[Brigitte15]

Bonjour,

Je vous remercie pour votre retour. J'ai utilisé la formule NB.SI.ENS comme ceci =NB.SI.ENS(B6:B16;B6:B16;C6:C16;C6:C16<I5;D6:D16;D6:D16<I6) et cela ne fonctionne pas.

Bien cordialement,

[Menhir]

J'ai utilisé la formule NB.SI.ENS comme ceci =NB.SI.ENS(B6:B16;B6:B16;C6:C16;C6:C16<I5;D616;D616<I6) et cela ne fonctionne pas.

La syntaxe n'est pas correcte.
Je te conseille de lire ceci : https://support.office.com/fr-FR/art...c-aa8c2a866842

[Brigitte15]

Bonjour Menhir,

Je vous remercie pour votre retour.


J'ai bien lu le lien concernant la formule NB.SI.ENS et je me suis permise de l'ajuster : =NB.SI.ENS(B6:B16;B6:B16;C6:C16;"<I5";D6:D16;"<I6"

Mais cela ne fonctionne pas. Néanmoins je ne suis pas sûr que se soit la bonne formule, en effet je ne souhaite pas appliquer de critère pour la plage 1.


Bonne journée

[Menhir]

Lis mieux les explications de la page que j'ai mis en lien.
Les critères ne peuvent pas êtres des plages donc ton B6:B16;B6:B16 est forcément faux.
De plus, si "<I5" est supposer signifier "inférieur au contenu de la cellule I5, alors il faut l'écrire "<"&I5.
Idem pour le critère suivant.

je ne souhaite pas appliquer de critère pour la plage 1.

Dans ce cas, pourquoi l'avoir mis dans la fonction ?

[Pierre Fauconnier]

Salut.

Si tu veux compter le nombre de lignes correspondant à un mois et une année donnée, tu peux utiliser la formule suivante, basée sur un tableau structuré appelé Tableau1



Tu peux aussi réaliser un tableau croisé dynamique qui te donne l'info pour tous les mois de toutes les années (par défaut, présentes dans ton tableau) sans besoin de formules.

[Brigitte15]

Bonjour,

Merci pour vos réponses et, je vous prie de m’excuser, car je pense avoir mal exposé mon problème.

@Menhir*: Je souhaite connaître le nombre de nouveaux produits et pas savoir combien il y a de produits A. En conséquence, je ne souhaite donc pas appliquer un critère sur la colonne produit.

@Pierre Fauconnier*: Votre solution me permet de connaître le nombre de produit selon le mois et l’année, mais je souhaite aller plus loin. En effet, j’aimerais pouvoir identifier selon le mois et l’année le nombre de nouveaux produits. Entendu qu’un nouveau produit est un produit qui n’a pas été vendu les mois précédents. Ainsi en 12/2017, il y a 3 nouveaux produits (car pas de données antérieures) ; en 1/18 il y a 1 nouveau produit ; en 2/18 il y a 3 nouveaux produits et en 03/18, il n’y a aucun nouveau produit (mais il y a eu des ventes de produits sortis précédemment).


Merci d'avance pour votre aide

[papouclo]

Bonjour,
La difficulté de traitement provient de la disjonction mois - années dans deux colonnes séparées au lieu de travailler sur des dates.
En appelant liste_mois et liste_années les plages des colonnes C et D où sont notés les mois (1 à 12) et les années, si l’on s’intéresse aux produits nouveaux par exemple au mois de mai 2018, il faudra éliminer les produits qui sont apparus avant mai 2018.
Or, pour l’emploi de la fonction NB.SI.ENS., on ne peut pas traduire le critère « être antérieur » à mai 2018 (avec 5 en N5 et 2018 en N6 ) par :
NB.SI.ENS(… ; liste_mois ; "<"&N5 ; liste_années ; "<" &N6).
"Etre antérieur à mai 2018" n’équivaut pas à : "le mois est inférieur à 5" ET "l’année est inférieure à 2018".
En rajoutant au tableau source une colonne E où une date est créée par formule à partir du mois et de l’année sur chaque ligne, on pourra ensuite ne travailler que sur ce seul critère.

La formule en E6 est :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=FIN.MOIS(DATE(D6;C6;1);0)

à recopier vers le bas en colonne E.
Les noms produits et finmois désignant respectivement les plages B6 :B16 et E6 :E16, la formule matricielle en I7 est :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMME(SI(finmois<=FIN.MOIS(DATE(I$6;I$5;1);0);1/NB.SI.ENS(produits;produits;finmois;"<="&FIN.MOIS(DATE(I$6;I$5;1);0))))-SOMME($H7:H7)

A valider par CTRL + MAJ + Entrée et à recopier vers la droite en ligne 7.
Avec l’incrémentation, la formule en K7 devient :
{=SOMME(SI(finmois<=FIN.MOIS(DATE(K$6;K$5;1);0);1/NB.SI.ENS(produits;produits;finmois;"<="&FIN.MOIS(DATE(K$6;K$5;1);0))))-SOMME($H7:J7)}
La première partie de la formule SOMME(SI(finmois<=FIN.MOIS(DATE(I$6;I$5;1);0);1/NB.SI.ENS(produits;produits;finmois;"<="&FIN.MOIS(DATE(I$6;I$5;1);0))))
donne le nombre de produits différents vendus jusqu’au mois de février 2018 inclus soit 7 (obtenu avec la touche F9 après avoir sélectionné cette partie de formule).
La seconde partie SOMME($H7:J7) donne le nombre de produits déjà vus les mois précédents soit 4.
La différence donne donc le nombre de produits nouveaux apparus en février 2018 c'est à dire 3.
Cordialement
Claude

[Menhir]

@Menhir*: Je souhaite connaître le nombre de nouveaux produits et pas savoir combien il y a de produits A. En conséquence, je ne souhaite donc pas appliquer un critère sur la colonne produit.

Je le répète : dans ce cas, ne mets pas de recherche sur cette données dans ton NB.SI.ENS().

[Brigitte15]

@Paclouclo : Parfait ! Merci beaucoup pour votre aide et vos explications !

Mon niveau Excel est modeste, pourriez-vous m'expliquer pourquoi à la fin de cette formule, on fait SOMME($H7:J7) et pas SOMME($I7:J7) puisque H7 est une case textuelle ?

Par ailleurs, je souhaiterais pouvoir appliquer la formule sur un ensemble de colonnes afin d'automatiser l'opération. J'ai utilisé la formule SI EST VIDE afin que votre formule ne prenne pas en compte les cases vides, mais cela ne fonctionne pas...

Merci d'avance

PS : J'aimerais bien m'améliorer, auriez-vous un MOOC/livre/tuto à me conseiller pour progresser en travaillant un peu tout les deux jours ?

[papouclo]

Bonjour,

...pourriez-vous m'expliquer pourquoi à la fin de cette formule, on fait SOMME($H7:J7) et pas SOMME($I7:J7) puisque H7 est une case textuelle ?

Il suffit de lire l'aide en ligne de la fonction SOMME à la rubrique Recommandations lors de l’utilisation de la fonction SOMME :
La fonction SOMME ignore les valeurs de texte et calcule la somme des valeurs numériques uniquement.
La cellule H7 contenant du texte, SOMME($H7:J7) calcule exactement la même chose que SOMME($I7:J7) mais ce choix d'écriture de formule permet de n'avoir qu'une seule formule pour toutes les cellules de la ligne 7 à partir de I7. Sinon, il aurait fallu écrire une formule spéciale pour la cellule I7 elle-même (pas question d'écrire I7 dans la formule sous peine de créer une référence circulaire).

Par ailleurs, je souhaiterais pouvoir appliquer la formule sur un ensemble de colonnes afin d'automatiser l'opération. J'ai utilisé la formule SI EST VIDE afin que votre formule ne prenne pas en compte les cases vides, mais cela ne fonctionne pas...

Je pense qu'il s'agit d'écrire à l'avance la formule sur de nombreuses cellules de la ligne 7 même si les cellules correspondantes des lignes 5 et 6 sont vides.
Si l'une des deux cellules des lignes 5,6 est vide, le calcul ne pourra se faire et il faudra demander que la cellule en ligne 7 contenant la formule semble vide, c'est à dire qu'elle affiche "".
Si les deux cellules contiennent des valeurs numériques, on peut faire le calcul du nombre de nouveaux produits.
Une façon rapide de tester si les deux cellules I5 et I6 par exemple sont non vides est de tester si : NBVAL(I5:I6) = 2
d'où la formule matricielle en I7 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(NBVAL(I5:I6)=2;SOMME(SI(finmois<=FIN.MOIS(DATE(I$6;I$5;1);0);1/NB.SI.ENS(produits;produits;finmois;"<="&FIN.MOIS(DATE(I$6;I$5;1);0))))-SOMME($H7:H7);"")

toujours à valider par Ctrl + Maj + Entrée et à recopier en ligne 7 sur la droite aussi loin que l'on veut.
Cordialement
Claude

[DjeFi]

Bonjour,

Solution alternative: Elle permet d'identifier facilement la liste des nouveaux produits.

La formule suivante à ajouter en D2 affiche New ou Old en face de la combinaison Produit,Mois Annee.
=IF(C2*100+B2=MIN(IF((--(NR_ProdAREA=$A2))*(100*NR_AnneeArea+NR_MoisArea)=0;999999;(--(NR_ProdAREA=$A2))*(100*NR_AnneeArea+NR_MoisArea)));"New";"Old")

Remplacer:
NR_ProdAREA par ta plage de Produit (ou colonne A)
NR_MoisArea par ta plage de Mois (ou colonne B)
NR_AnneeArea par ta plage d'annee (colonne C)

Formule à valider avec CTL + SHiFT + ENTER


Deux petites astuces:
- (100*NR_AnneeArea+NR_MoisArea): cela te creer une range au format AAAAMM soit 4 2017 devient 201704. (meme astuce pour C2*100+B2)
- Quand le resultat est 0 (le produit n est pas le meme) on remplace par 999999 ce qui permet ne jamais etre retourne par la fonction MIN.

Il ne te reste plus qu'à compter le nombre de "New" par periode.

A noter: Je ne sais pas comment est constituée la liste mais si elle peut etre triée par produit,annee,mois decroissant, la solution est beaucoup plus simple.
Il suffirait de flaguer chaque changement de produit en affichant New si le produit de la ligne precedente est different.