Discussion :

[facteur]

Bonjour,

J'ai un tableau contenant plusieurs colonnes dont une colonne mois (colonne B) et une colonne repas (colonne AG) .
je souhaiterais insérer une formule dans la cellule AG3 qui me donnerait le nombre de cellule contenant le chiffre 3 de la colonne AG .
Je connais la formule NB.SI qui dans le cas du mois de novembre serait

Code :

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

NB.SI(AG461:AG491;"3")

.Or cette formule ne fonctionne plus si je change de mois et je sélectionne le mois de décembre.
La formule devrait être

Code :

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

NB.SI(AG127:AG157;"3")

mais elle reste sur les données du mois de décembre

Pour résumé comment faire pour que la formule NB.SI se réactualise en fonction du filtre mois de la colonne B ?
Merci de votre aide

[geogeo70]

Je ne vois aucune référence au mois dans la formule?

mais je n'ai peut-être pas compris ton problème...

geogeo

[dev_ggy]

Bonjour Facteur,

Ta question n'étant pas très bien formalisé, je ne le comprends pas très bien, mais les formules ne tiennent pas compte des filtres.

Si tu fais un somme et que tu filtres sur la somme, tu auras toujours le même résultat.

Cordialement.

[facteur]

Bonjour,

geogeo70, effectivement, je ne fais pas référence au mois.
Justement, Je ne sais pas comment mettre dans ma formule NB.SI(AG127:AG157;"3"), une référence à la colonne "mois".


dev_ggy, comment alors faire en sorte que ma formule NB.SI(AG127:AG157;"3"), tiennent en compte le filtre mis dans la colonne "mois"

[78chris]

Bonjour

Voir # 7 de ce fil http://www.developpez.net/forums/d13...e/#post7201149

[casefayere]

Bonjour,

et cette formule ne suffirait pas ?

Code :

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

=NB.SI.ENS(AG127:AG157;"3";B127:B157;12)

'le 12 pourrait être remplacer par une cellule contenant le chiffre du mois désiré, exemple en B1, le mois à compter

Code :

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

=NB.SI.ENS(AG127:AG157;"3";B127:B157;$B$1)

[Philippe Tulliez]

Bonjour,
Je pense qu'il serait préférable d'expliquer ce que tu veux filtrer.
Ensuite soit tu utilises le filtre simple qui te permettra de filtrer le mois souhaité ainsi qu'un nombre de repas maximum ou minium ou égal à etc. soit tu optes pour le filtre avancé qui offre plus de possibilités et notamment avec les critères calculés.

[facteur]

Bonjour Phillipe et casefayere ,
Je vais essyer d'etre plus explicite...
Mon tableau contient plusieurs colonnes mais on va se focaliser sur les deux colonnes qui nous concernent :
- la colonne B qui contient les mois de l'année ( janvier à décembre)
- la colonne AG contient le nombre de repas (les cellules de la colonne AG peut contenir les valeurs de 0 ou 1 ou 3).

Je souhaiterais connaitre le nombre de cellules différent de 0 (ce qui revient à avoir le nombre de repas ( 1 ; 2 ou 3) et ceci en fonction du mois selectionné de la colonne B:
La formule serait inserée en AG3 .

Si je reprend la formule de PHILIPPE , je devrais avoir une formule du type :

Pour le mois de novembre , la formule de philippe serait

=NB.SI.ENS(AG431:AG460;"3";B431:B460;11)

Et si je selectionne le mois de décembre dans la colonne B , la formule devrait etre

=NB.SI.ENS(AG461:AG491;"3";B461:B491;12)

or cette formule ne change pas et reste sur le mois de novembre

D'ailleurs philippe, pourrais tu m'expliquer ta formule . J'ai du mal à l'interpreter

=NB.SI.ENS(AG127:AG157;"3";B127:B157;12)

[facteur]

J'ai saisie la forumle de Philppe..il n'est jamais trop tard..
dans mon cas , elle s'ecrirait ainsi pour le mois de décembre :

=NB.SI.ENS(AG5:AG3000;"<>0";B5:B3000;"décembre")

Mais j'ai aurais toujours le soucis lorsque je selectionnerais un autre mois que celui de décembre.
Si en colonne B, je sélectionne le mois de novembre, je devrais changer ma formule :

=NB.SI.ENS(AG5:AG3000;"<>0";B5:B3000;"novembre")

ne peut on pas automatiser la formule pour que celle-ci prend en compte automatiquement le changement de mois ?...

[Philippe Tulliez]

Bonjour,
Si tu connais le filtre simple d'excel, il n'y a pas besoin il me semble d'utiliser une formule pour ton cas bien précis.
Un filtre sur le mois et un autre sur la colonne contenant les repas.
Soit dans la colonne contenant les dates décocher sélectionner tout et ensuite sélectionner le mois choisi et ensuite dans la colonne contennant le nombre de repas sélectionner les filtres numériques et choisir la règle adéquate (je suppose dans ton cas Entre....

[facteur]

Philippe,

Justement, je ne souhaite pas filtrer dans la colonne AG (colonne repas) le données numériques.
Je vais utiliser ta formule..elle est trés bien..

[Philippe Tulliez]

Bonjour,

ne peut on pas automatiser la formule pour que celle-ci prend en compte automatiquement le changement de mois ?.

S'il s'agit du mois de la date du jour et que la colonne en question est une véritable date ce qui ne semble pas être le cas, il suffirait de remplacer le texte du mois par MOIS(AUJOURDHUI()).
Une autre option serait de prévoir une liste avec les mois dans une feuille séparée, nommer la plage par exemple lstMois, prévoir une cellule où tu places une liste basée sur l'outil Validation de données et faire référence à cette cellule dans la formule.

[EDIT]
Variante sans liste de choix (Validation de données) mais toujours avec la plage de données nommée lstMois contenant la liste des mois classée par ordre croissant des mois.
En remplaçant la constante alphanumérique du mois par cette formule

Code :

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

INDEX(lstMois;MOIS(AUJOURDHUI()))

[facteur]

Philippe,
Pourrais tu etre plus claire lorsque tu dis .. :

Une autre option serait de prévoir une liste avec les mois dans une feuille séparée, nommer la plage par exemple lstMois, prévoir une cellule où tu places une liste basée sur l'outil Validation de données et faire référence à cette cellule dans la formule

Car effectivement, j'ai commencé a metre en place ton idée :
Dans un onglet initulé "tarif", j'ai crée une liste de mois (janvier (I16) à décembre (I27).
Dans l'onglet où se trouve mon tableau, je voulais inserer en B3, une formule du genre que tu as décrit mais que je n'arrive pas mettre en pratique..

[Philippe Tulliez]

Bonjour,
Pour nommer une plage
1) Tu sélectionnes la plage (dans ton cas $I$16:$I$27 de la feuille nommée [Tarif])
2) Tu sélectionnes la référence à la cellule active (ici ce sera I16) dans la zone des noms. La zone des noms se trouve à gauche de la barre des formules)
3) Tu insères le nom (par exemple lstMois) et tu valides par la touche Entrée
Une autre façon d'y arriver est de passer par le gestionnaire des noms. Onglet [Formules], groupe Noms définis, commande Gestionnaire de noms.
La formule avec la plage nommée

Code :

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

INDEX(lstMois;MOIS(AUJOURDHUI()))

La formule sans nommer la plage

Code :

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

INDEX(Tarif!$I$16:$I$27;MOIS(AUJOURDHUI()))

[Didier Gonard]

Bonjour,

Encore une autre façon de nommer, vu que tu as 2010, il est intéressant pour ce genre de plage nommées d'utiliser les "tableaux" (Onglet Accueil / Groupe Style / bouton Mettre sous forme de tableau apparus en 2007.

• ça crée automatiquement une plage nommée pour les données (sans les en-têtes). Le nom est modifiable aisément dans l'onglet contextuel ad-hoc.
• Cette plage est dynamique, donc si on rajoute une donnée en dessous, c'est pris en compte (pas utile ici car au-delà de 12 mois...)
• Le nom créé ne peut-être effacé accidentellement par l'utilisateur via une mauvaise manip dans le gestionnaire de noms ni y être redimensionné et il a un icône différent des noms de plage utilisateurs ce qui en facilite la gestion quand multiples tableaux

en dernier lieu, ça initie à ce type de tableau au moins aussi méconnu que les filtres élaborés (clin d’œil à Philippe)

cordialement,

Didier

[facteur]

Avant tout je vous souhaite à vous deux une excellente année 2015.


Bonjour Philippe,
Pour evenir à notre sujet..
Comment dois je lire ta forumule

=INDEX(lstMois;MOIS(AUJOURDHUI()))

car celle-ci m'affiche le mois en cours .Or, ce que je souhaite, c'est que dés que en B3 s'affiche le mois que j'aurais selectionné dans la colonne B.
Pour rappel, la colonne B contient les mois de l'année.

Bonjour Didier,
l'utilisation de tes tableaux ne me merpet pas de voir afficher en B3, le mois que j'aurais selectionné sur la colonne B..

[Philippe Tulliez]

Bonjour,
Comme expliqué dans ma réponse (fil 12)
Il y avait deux possibilités soit avec une liste déroulante soit en tenant compte du mois en cours.
Donc avec le mois en cours, c'est

Code :

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

=INDEX(lstMois;MOIS(AUJOURDHUI()))

Comme la plage nommée lstMois est une liste des mois de l'année classée par ordre croissant des mois Janvier en premier, puis février etc.
La formule

Code :

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

MOIS(AUJOURDHUI())

renvoyant le n° du mois d'aujourd'hui soit pour ce jour, le chiffre 1
donc cela revient à avoir

Code :

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

=INDEX(lstMois;1)

La formule renverra donc Janvier qui est le premier élément de la liste lstMois

Maintenant si tu souhaites obtenir le mois sélectionné dans la liste alors il faut passer par l'outil Validation de données-Liste en faisant référence à la plage nommée lstMois.
Sélectionner au préalable la cellule ou la plage de cellules qui doit contenir la liste

Et dans la formule il faudra faire référence à la cellule qui contient la validation de données ainsi tu auras le mois sélectionné dans ladite liste

[facteur]

Philippe,
je comprend mieux les deux formules.
je m'apercois que mon tableau a été mal construit

Je m'explique :
Mon tableau récupére des données depuis 2013.
Dans la colonne D (colonne DATE) , j'ai une date avec pour format : jj/mm/aaaa.
Dans la colonne B (colonne MOIS), je retrouve les mois grace à la formule rechercheV qui va rechercher les mois de la plage lstMois

RECHERCHEV(MOIS(D5);lstmois;2;0)

Ainsi un même mois se trouve sur plusieurs années.

Le sousis est le suivant :
Si je souhaite connaitre le nombre de repas servie pour le mois de décembre 2014
en B3, je vais selectionner le mois de décembre
la formule :

NB.SI.ENS(AG5:AG3000;"<>0";B5:B3000;B3)

me donnera le nombre de repas servi en décembre 2013 et 2014 alors que je desire que celui de 2014.

Comment contourner le problème ?..

rappel: la colonne AG est la colonne qui contient le nombre de repas

[Philippe Tulliez]

Bonjour,
Je me tournerais vers la formule SOMMEPROD mais j'avoue ne pas comprendre la raison pour laquelle tu ne te tourne pas vers un filtre automatique ou un filtre élaboré ou encore une formule de la catégorie base de données comme BDSOMME
Reste également le tableau croisé dynamique.

[facteur]

Le filtre automatique ne me donnerais pas le nombre de repas en fonction du mois selectionné ?