quelle formule Excel 2007: recherche de mois Résolu/Fermé

Question

Bonjour, je cherche une formule de calcul sous excel :

colonne D                        colonne E                    colonne F
inscription          évaluation prévue          par
01/07/2001                          juin                            Germaine
25/09/2005                          aout                           beatrice

voila à quoi ressemble mon tableau, alors ma question. sur une cellule de "synthèse" je souhaite faire : 
combien de fois germaine a t'elle d'évaluation à faire en juin, juillet, aout, etc.

l'inscription est une date complète, par contre les évaluations se calculent par mois chaque année. il faut juste que je sache qu'une personne inscrite en juillet 1999 par exemple doit être évaluée toutes les années en juin.
en sachant que ma cellule E2 (juin) est obtenu par une formule (MOIS.DECALER(D2;11). soit 11 mois après le mois d'inscription. comme ça j'ai facilement le mois que je recherche.

le GROS problème, pour rechercher : Germaine et Juin = 1 ; ou Beatrice et aout = 12 j'ai essayé la formule suivante : =NB.SI.ENS(E:E;"la je ne sais pas quoi mettre";F:F; "Germaine") 

dans E:E;"...." j'ai essayé plusieurs arguments mais ça ne fonctionne pas, du style ">=01/07&<01/08" pour obtenir toutes les cellules en E incluant uniquement le mois de juillet.

voilà, j'espère avoir été clair dans mes explications, et si un pro d'excel peut m'aider sur ce coup ça serait vraiment sympa. en sachant que je souhaite juste une formule et pas de VBA car je ne sais pas trop les faire fonctionner. merci encore d'avance.
Bendao

ponpon · Answer

Bonjour,
combien de fois germaine a t'elle d'évaluation à faire en juin, juillet, aout, etc
Formule : =SOMMEPROD((E2:E7="juin")*1;(F2:F7="Germaine")*1)

bendao · Answer

merci de ta réponse, mais ça ne fonctionne pas. je crois savoir pourquoi, et je vais essayer d'être clair :

ma colonne E avec les mois janv, fev, mar, avr, etc. ne sont pas notés en tant que "texte" mais issu d'une formule (MOIS.DECALER;11) par rapport à la colonne D ou il y a une date d'inscription. et j'ai mis le format de la cellule E en personnalisé : "mmmm" pour faciliter la lecture. donc je pense  qu'en demandant de reconnaitre "janv" dans la formule ((E:E="janv)*1), il ne le reconnait pas le terme. Ai-je raison ? 
et si oui, y-a-t'il une autre formule pour reconnaitre ce resultat MOIS.DECALER;11 : en janvier, ou février, ou mars, etc.
merci du temps que vous pourrez prendre pour me dépanner, car j'avoue sécher depuis quelques jours sur cette formule.... :-)

tontong · Answer

Bonjour,
Essayez avec celle-ci: =SOMMEPROD((MOIS(E2:E7)=6)*(F2:F7="Germaine")*1)

bendao · Answer

désolé, ça ne fonctionne pas (#valeur). j'ai essayé juste en mettant (MOIS(E:E)=6) puis (E5:E266). Avec (E:E) ça me dit donc #VALEUR, et avec (E5:E266) ça me donne 0, alors qu'il y a pas mal de mois de juin présent.

et une fonction presque identique avec NB.SI.ENS ? est-ce-que ça peut marcher ? j'essaie en attendant vos réponses. merci encore

tontong · Answer

Il est "normal" que ça ne fonctionne pas avec une colonne entière E:E car la fonction sommeprod n'admet pas ce genre de champ. Il faut de plus que tous les champs aient la même taille. Si vous définissez E5: E256 il faudra utiliser F5:F256 pour l'autre critère.
La fonction NB.SI.ENS n'est apparue qu'avec excel 2007, je ne peux donc pas vérifier avec la version 2003 dont je dispose.
Comme vous l'avez bien perçu il est fort possible que vous rencontriez des problèmes en utilisant le nom des mois de la colonne E comme critère. Cette colonne contient des dates qui sont des nombres et non pas du texte.

bendao · Answer

ok, merci. je viens d'essayer aussi avec (E5:E266) et (F5:F266). la valeur est à 0. que cela veut il dire. en gros, la condition germaine ET juin n'est jamais réuni.

donc mon gros problème réside, je pense, dans le fait de faire reconnaitre ma colonne E de mois (janv, fev, etc) avec la formule. puisque cette colonne n'est pas rentré manuellement (sinon pas de pb) mais est le résultat d'une formule du style (MOIS.DECALER;11). le résutat est juin ou aout ou etc.

comment puis-je faire reconnaitre ce résultat de formule dans SOMMEPROD, ou NB.SI.ENS.

car faire MOIS=1 ou MOIS=2 etc n'a pas l'air de fonctionner non plus, je patauge.

tontong · Answer

Déposez un fichier dépourvu de données confidentielles sur : https://www.cjoint.com/   et joignez le lien généré à votre prochain post. L' analyse et la communication s'en trouveront améliorés.

tontong · Answer

Si vous entrez 1 2 3    ...12  de H14 à H25 après avoir supprimé la fusion des cellules et que vous entrez:en J15
=SOMMEPROD((MOIS($E$2:$E$28)=$H14)*($F$2:$F$28=J$13)*1) vous pourrez obtenir votre tableau en jaune par recopie.

bendao · Answer

merci, je vais essayer ça tout de suite, je vous tiendrai au courant....

tontong · Answer

En plus explicite: 	https://www.cjoint.com/?jrqX5eGcDE

ponpon · Answer

Bonjour,
si  j'ai bien compris $xx$ désigne une colonne et xx$ une cellule... bon c'est pratique
 Non se n’est pas cela, voici l’explication :
Différence entre les références absolues, relatives et mixtes
Références relatives      Dans une formule, une référence de cellule relative, par exemple A1, est basée sur la position relative de la cellule qui contient la formule et de la cellule à laquelle la référence renvoie. Si la position de la cellule qui contient la formule change, la référence est modifiée. Si vous copiez ou remplissez la formule dans des lignes ou dans des colonnes, la référence est automatiquement adaptée en conséquence. Par défaut, les nouvelles formules utilisent des références relatives. Par exemple, si vous copiez une référence relative de la cellule B2 dans la cellule B3 ou si vous remplissez cette référence, elle est automatiquement adaptée de =A1 à =A2.
Références absolues      Dans une formule, une référence de cellule absolue, par exemple $A$1, fait toujours référence à une cellule se trouvant à un endroit spécifique. Si la position de la cellule qui contient la formule change, la référence absolue reste inchangée. Si vous copiez ou remplissez la formule dans des lignes ou dans des colonnes, la référence absolue n’est pas adaptée en conséquence. Par défaut, les nouvelles formules utilisent des références relatives qu’il peut être nécessaire de transformer en références absolues. Par exemple, si vous copiez une référence absolue de la cellule B2 dans la cellule B3 ou si vous remplissez cette référence, elle reste la même dans les deux cellules : =$A$1.
Références mixtes      Une référence mixte comprend soit une colonne absolue et une ligne relative, soit une ligne absolue et une colonne relative. Une référence de colonne absolue s’affiche sous la forme $A1, $B1, etc. Une référence de ligne absolue s’affiche sous la forme A$1, B$1, etc. Si la position de la cellule qui contient la formule change, la référence relative est modifiée, alors que la référence absolue ne l’est pas. Si vous copiez ou remplissez la formule dans des lignes ou dans des colonnes, la référence relative est automatiquement adaptée en conséquence, alors que la référence absolue ne l’est pas. Par exemple, si vous copiez une référence mixte de la cellule A2 dans la cellule B3 ou si vous remplissez cette référence, elle est adaptée pour passer de =A$1 à =B$1.

bendao · Answer

ok, l'explication est complète mais un peu nébuleuse pour moi. je pense avoir bien compris l'ensemble et ce que veut dire référence mixte, relative ou absloues. 
par contre pour le réutiliser tout seul ça risque d'être plus compliqué. je m'entrainerai pourle maitriser car c'est un outils formidable. 
comme quoi on ne se doute pas de l'étendue des capacités des logiciels.
En tout cas merci de votre aide à tous. mon problème est désormais résolu........ jusqu'au prochain  :-)

bendao · Answer

oupps, j'ai parlé un peu vite.

message pour tontong : dans la formule que tu m'a faite. j'ai reussi sans problème à l'adapter pour mon tableau initial.
par contre petit bug : j'ai quelques lignes où les usagers n'ont pas de dates d'inscription (pas retrouvées), donc normalement ma colonne suivante (évaluation = MOIS.DECALER(D80;11)) ne devarit rien mettre ou alors un message du style #VALEUR. ben là ça me note Nov par défaut (toutes les cellules vides ont ce mois de l'année) bizarre non??!!
si tu as une explication, merci d'avance.

tontong · Answer

Bonjour,
L'explication est simple: le calendrier excel commence le 01/01/1900 cette date correspond à la valeur 1 ( c'est cette valeur qui s'affiche en format standard). La valeur 12 correspond au 12 janvier 1900.
Quand une cellule est vide excel l'interprète comme un 0 ce qui correspondrait au 0janvier1900 qui n'existe pas mais dont excel va se servir quand on lui demandera de se décaler de 11 mois. On se retrouvera donc au 30 novembre 1900. C'est la date qui serait affichée si la colonne E était au format jj/mmmm/aaaa.
Le plus gênant :je n'ai pas dans l'immédiat de solution pour contourner cette difficulté en cas de cellule vide en colonne D.

bendao · Answer

ok merci de l'explication, si tu as la réponse à ce petit désagrement pense à me le faire passer.
Merci

tontong · Answer

Une solution consiste à créer une colonne intermédiaire qui calcule le mois et le met à 0 si aucune valeur numérique n'est entrée en Dx:	https://www.cjoint.com/?jspWwLSOuC

Quelle formule Excel 2007: recherche de mois

16 réponses

Discussions similaires