Formule NB.SI.ENS ou SOMMEPROD avec dates et autre critère
Bonjour à tous,
j'ai cherché en vain une solution à mon problème sur internet avant de me décider à m'inscrire ici et poster mon problème. J'espère que vous pourrez m'aider.
Je fais un tableau de statistiques pour des parties de jeu de plateau, et mon problème est sur le décompte du nombre de parties jouées par un joueur en particulier au cours d'un mois spécifique.
J'ai un onglet "Stats" avec toutes les infos:
colonne A: date sous le format jj/mm/aaaa
colonne B: nom du jeu
colonne C: durée sous le format hh:mm:ss
colonne D: nombre de joueurs
colonne E: noms des joueurs
J'ai tenté cette première formule:
=SOMMEPROD((ANNEE(Stats!$A$2:A233)=2015)*(MOIS(Stats!$A$2:A233)=9)*(Stats!$E$2:E233="*Jean*"))
j'obtiens un résultat de 0, et j'ai réussi à identifier le problème, il vient du dernier critère, le nom du joueur avec les "*". En effet, ma colonne E correspond aux joueurs participants, et tous les noms sont inscrits. Afin que cette formule fonctionne je dois rentrer exactement les combinaisons de noms de joueurs. Or j'aimerais rentrer uniquement le nom du joueur qui m'intéresse pour ne pas à devoir modifier sans cesse avec de nouvelles combinaisons de joueurs!
J'ai essayé une autre formule:
=NB.SI.ENS(ANNEE(Stats!$A$2:A233)=2015;MOIS(Stats!$A$2:A233)=9;Stats!$E$2:E233;"*Jean*")
et cette fois-ci je ne parviens pas à rentrer la formule. L'erreur doit provenir de l'écriture des dates dans la formule.
J'espère avoir été suffisamment clair.
Merci par avance pour votre aide.
Bonsoir,
Voir en annexe le fichier Excel avec la correction de ta formule SOMMEPROD.
J'en ai profité pour rendre cela paramétrable : tu peux désigner l'année, le mois et le nom désiré et la formule calculera le résultat.
Merci de confirmer si cela correspond à tes attentes.
Chris
NB : la prochaine fois joins le fichier : c'est plus facile à comprendre et à trouver l'erreur d'une part et cela nous évite de devoir construire un fichier exemple
Bonjour Chris,
merci pour ton aide. Malheureusement, la formule que tu me donnes, est quasiment la même que celle que j'ai employée.
Dans le fichier exemple que tu as créé (et d'ailleurs, merci beaucoup pour cela) ma formule fonctionnerait aussi.
Mon problème vient de l'association des joueurs! En effet, les parties ne se font pas en "solo", et donc lorsque l'on rentre plusieurs noms de joueurs dans la cellule alors la formule ne fonctionne plus.
J'ai mis à jour le fichier exemple, ce sera plus clair avec!
ps: désolé de ne pas avoir mis mon fichier.
Evidemment...
Mais il y a toujours un moyen avec Excel : astuce : créé une colonne supplémentaire pour détecter le nom cherché et... mettre cette colonne dans le SOMMEPROD.
Voir fichier en retour.
Je continue quand même à regarder s'il n'y a pas moyen de faire autrement mais tu es déjà dépanné ainsi ou alors il faut passer au VBA ou cela ne posera aucun problème.
A+
Chris
Merci pour l'astuce.
Je viens de faire un fichier simplifié. Les tableaux que je souhaite faire se trouvent dans les onglets de joueurs (Julien et Eri).
Ton astuce ne colle pas tout à fait avec ce que je recherche. Mais comme tu n'avais pas vu le fichier avant, c'est normal, mea culpa!
N'est-il pas possible d'inclure des condition datées avec la fonction "NB.SI.ENS" sinon? Avec cette fonction, le "*Julien*" fonctionnera par exemple...
Merci encore pour ton aide.
Sorry mais je ne te suis plus.
Tu avais demandé de tester L'ANNEE et LE MOIS et le nom et maintenant tu me montres un tableau dans lequel, si je comprend bien, tu cherches à calculer en testant le nom du jeu avec le nom du joueur ce qui en soit est plus simple.
Est ce que c'est bien cela : tu cherches à remplir les plages B2 à b19 et c2 à c19 en testant le nom du jeu et le nom du joueur ??
Avant de recommencer encore une fois je veux être certain...
A+
Chris
Il fallait descendre un peu: B24 à B33 posent problème. Tout le reste fonctionne déjà comme tu peux le voir!
Bonsoir,
Cette fois je pense avoir compris où tu voulais en venir.
Je te joins mon projet en annexe.
Ce qui a été modifié :
0 Création d'un onglet multi joueur qui permet de sélectionner le joueur désiré grâce à une liste créée en AA11 à AA38 : tu as de la place pour en créer de nouveaux : si tu dépasses AA38 il faudra adapter la liste de données.
Cette plage de cellule s'appelle NOMS. Le choix s'effectue en cellule B1 grâce à la liste déroulante.
1 Il me semblait en effet préférable de ne pas devoir pour chaque onglet modifier toutes les formules en fonction du nom de la personne. J'ai donc changé toutes tes formules pour les rendre paramétrables (tu verras la syntaxe dans le tableur). La fonction NB.SI.ENS convient en effet mieux car elle permet l'utilisation de la recherche dans une plage alpha par (*Eri*). Ce que ne sais pas faire la fonction SOMMEPROD (du moins je n'ai pas trouvé comment...)
J'ai donc insérer une ligne pour pouvoir définir le nom recherché que j'ai mise en B1 et cette cellule a été nommée NOM
2 Les formules ont été adaptées en B3:B19 et C3:C19 pour tester sur le Nom ainsi que sur le jeu (tant qu'on y était il valait mieux aussi rechercher le nom du jeu dans la colonne B plutôt que de le taper - ainsi si tu en ajoutes ou modifie, cela s'adaptera automatiquement)
3 les formules en B20 et C20 sont différentes puisqu'il fallait tester les "autres jeux" càd le reste de ce qui n'a pas été nommé avant.
4 les formules de B25 à B34 ont été adaptées en testant le Nom comme expliqué plus haut et en cherchant l'année et le mois en testant >= au 1 jour de l'année et du mois de la colonne A et un deuxième test pour <= au dernier jour du mois (tu verras la syntaxe dans le tableur - la fonction NB.SI.ENS ne permettant pas d'imbriquer les fonctions ANNEE et MOIS - ce qu'aurait pu faire SOMMEPROD
5 les titres des graphiques ont été calculées en cellule AA1 et AA2 de manière à pouvoir les insérer dans tes graphiques et les rendre ainsi paramétrables sur le nom de la personne désirée.
6 L'impression de l'onglet joueur a été paramétrée pour tenir sur une page et ne pas tenir compte de; la colonne AA
7 Tant que j'y étais, j'ai rendu paramétrable l'onglet Fréquence : si tu ajoutes des dates, cela se calculera automatiquement
8 Les limites sont adaptées à la ligne 240 : si tu augmentes la capacité : il faudra revoir les formules.
Voilà j'espère que cela répond à tes attentes.
Je pense que l'on peut supprimer les onglets spécifiques à un nom mais si tu préfères avoir tous les onglets visibles en même temps, il te suffit de dupliquer l'onglet Joueur et de désigner dans chaque copie le nom désiré.
J'espère avoir été clair dans mes explications sinon reviens moi avec tes questions.
Merci en tous cas de confirmer si cela convient et aussi merci pour le cas qui m'a appris encore de nouvelles possibilités (et contraintes) sur notre cher Excel.
A+
Chris
Bonjour Chris,
c'est super ce que tu as fait! Je n'aurai pas pu trouver une solution aussi efficace sans toi! Je n'ai pas encore pris le temps de bien tout regarder. Il semblerait que le second graphe "camembert" ne fonctionne plus. Mais je vais regarder cela plus attentivement.
J'aurai sans doute quelques questions à te poser!
A très vite, et encore merci!!!
Bonjour Chris,
désolé pour le long délai. J'ai été un peu occupé ces derniers temps. J'ai pris le temps de me remettre dans le fichier ce matin. J'ai corrigé les quelques problèmes que j'avais. Tu n'avais pas fait de modifications sur la colonne E pour les durée. Je m'en suis chargé, c'était très simple.
Tout fonctionne parfaitement! Et, tu as raison, c'est une très bonne idée d'avoir mis sur un seul onglet.
Merci beaucoup pour ton aide!
Désolé pour la colonne E : je l'avais totalement oubliée...
Mais comme tu as su la corriger, c'est que tu as bien saisi le système (je ne l'ai pas fais exprès pour te tester ...
Content que cela convienne
A la prochaine
Chris