Bonjour,
ça va être un peu long mais TRES complet :
Pour rappel, NO.SEMAINE (formule excel qui requiert que l'utilitaire d'analyse soit chargé) renvoie le numéro de semaine selon la norme américaine : la semaine 1 est celle qui inclut le 1er janvier. Si vous êtes à l'ouest de la grande bleue, cette fonction est donc pour vous.
Alors que pour la norme européenne, la semaine 1 est la première semaine qui comporte au moins 4 jours dans l'année : il faut que le 1er janvier soit au plus tard un jeudi pour qu'il soit en semaine 1.
Certaines années les semaines 1 des deux normes ne sont donc pas les mêmes (par ex en
2000, le 1er janvier était, selon la norme européenne, en semaine 52 de l'année 1999...) LA formule classique et incontournable de Laurent Longre pour obtenir à partir d'une date le numéro de semaine à la norme *européenne* (la date étant en A1) :
(sur une seule ligne !)
=ENT((A1-SOMME(MOD(DATE(ANNEE(A1-MOD(A1-2;7)+3);1;2);{1E+99;7})*{1;-1})+5)/7)
Requiert le calendrier 1900. (voir toutes les explications dédtaillées ci-dessous !)
Plus courte mais avec limitation, nécessite aussi absolument de passer au calendrier 1900 :
=ENT(MOD(ENT((A1-2)/7)+0,6;52+5/28))+1
Elle est basée sur la régularité du calendrier, qui redevient identique tous les 28 ans;
elle n'est donc plus valable après 2100, qui ne sera pas bissextile (après le 28 décembre 2104 exactement) : Microsoft a 100 ans pour s'adapter à l'Europe !
En revanche les deux formules ci-dessous sont valides quel que soit le calendrier, 1900 ou 1904 :
De Laurent toujours :
=ENT((A1-(DATE(ANNEE(A1-JOURSEM(A1-1)+4);1;3)-JOURSEM(DATE(ANNEE(A1-JOURSEM(A1-1)+4);1;3))5)/7)
Ou celle-ci :
(sur une seule ligne !)
=1+ENT(MIN(MOD(A1-DATE(ANNEE(A1)+{-1;0;1};1;5)+JOURSEM(DATE(ANNEE(A1)+{-1;0;1};1;3));734))7)
**********************
Les explications ...
[AVERTISSEMENT : le récit suivant n'est pas destiné à nos amis québecois, chez qui, si je ne m'abuse, les numéros de semaine sont calculés 'à l'américaine', alors que 'nos' numéros de semaine me semblent plutôt d'origine chinoise, vu l'aspect complètement tordu de leur mode de calcul.]
===============================================================
CHAPITRE 1 : qu'est-ce qu'un numéro de semaine au sens 'ISO' ?
===============================================================
Selon cette définition, la semaine 1 d'une année donnée est la première semaine de cette année qui comporte au moins 4 jours, c'est à dire qui commence au plus tard un jeudi.
- Si le jour de l'an ('JA') est <= jeudi, JA se situe dans la semaine 1
- Si JA est > jeudi, il se situe dans la dernière semaine de l'année précédente, et la semaine 1 de l'année en cours est la semaine qui suit.
On voit tout de suite que la donnée de base de ce calcul est la date du lundi qui précède le premier jeudi de l'année ('J'). En effet, c'est ce lundi-ci qui est le 'jour de l'an' au sens 'semaine ISO'.
Quelques exemples valant mieux qu'un long discours...
1) Année 1997 :
- jour de l'an ('JA') = mercredi 1/1/97
- premier jeudi de l'année ('J') = jeudi 2/1/97
- lundi précédant le 1er jeudi de l'année ('PL') = lundi 30/12/96
=> la semaine 1 de l'année 1997 commence le lundi 30/12/96
2) Année 1999 :
- jour de l'an ('JA') = vendredi 1/1/99
- premier jeudi de l'année ('J') = jeudi 7/1/99
- lundi précédant le 1er jeudi de l'année ('PL') = lundi 04/01/99
=> la semaine 1 de l'année 1999 commence le lundi 04/01/99
Du moment que l'on connaît la date de ce lundi ('PL'), on peut calculer le NS de n'importe quel jour 'D' par la formule:
=ENT((D-PL)/7+1
Et pour éviter le '+1', avec L=PL-7 (donc L = avant-dernier lundi précédant le
premier jeudi de l'année), cette formule devient:
=ENT((D-L)/7).
Comme L=J-10, finalement tout repose sur J (le premier jeudi) et le NS s'obtient par:
NS(D)=ENT((D-J-10)/7).
[... enfin presque. Ca ne marche pas pour les semaines 'à cheval' entre deux
années, comme par exemple pour le NS du 31/12/96, qui n'est pas égal à 53 mais à 1 (c'est la semaine 1 de l'année 97, qui débute le lundi 30/12/96 comme mentionné plus haut). Mais peu importe. Ce problème particulier sera traité plus tard.]
Je résume : le calcul du numéro de semaine (NS) d'une date D repose sur le calcul de la date du premier jeudi de l'année (J), selon la formule
NS(D)=ENT((D-J-10)/7).
Comment diable obtenir cette date J par formule ?
C'est ce que vous apprendrez au prochain épisode.
=====================================
CHAPITRE 2 : premier jeudi de l'année
=====================================
Résumé du chapitre précédent : sachant que le numéro de semaine NS(D) peut être calculé par la formule NS(D)=ENT((D-J-10)/7), où J est le premier jeudi de l'année, comment calculer cette date J?
Pour obtenir cette date, on peut partir du jour de l'an ('JA'). Celui-ci s'obtient de manière très simple:
JA = DATE(ANNEE(D);1;1)
Il peut s'agir de n'importe quel jour de la semaine. Comment, à partir de cette date, obtenir le jeudi suivant (JA inclu)?
La première étape consiste à savoir sur quel jour de la semaine tombe JA. On pourrait utiliser la fonction JOURSEM, mais la fonction MOD est plus souple d'utilisation.
Sous Excel, les dates sont en fait des numéros de jours. En divisant une date par 7 et en prenant le reste (=MOD(D;7)), on obtient donc un nombrede 0 à 6 qui indique le numéro du jour de la semaine correspondant à cette date, un peu comme le fait la fonction JOURSEM.
Avec le calendrier 1900 (numéro de série du dimanche 1/1/1900 = 1) :
Si D = dimanche => MOD(D;7) = 1
Si D = lundi => MOD(D;7) = 2
Si D = mardi => MOD(D;7) = 3
Si D = mercredi => MOD(D;7) = 4
Si D = jeudi => MOD(D;7) = 5
Si D = vendredi => MOD(D;7) = 6
Si D = samedi => MOD(D;7) = 0
Que faut-il ajouter à MOD(D;7) pour obtenir le jeudi qui suit la date D (D inclus)?
Si D est un dimanche, MOD(D;7)=1, il faut donc ajouter 4 (puisque jeudi=5). Si D est un lundi, MOD(D;7)=2 et il faut ajouter 3, etc. Les valeurs à ajouter à MOD(D;7) sont donc les suivantes:
D = dimanche => jeudi suivant = D+MOD(D;7)+4
D = lundi => jeudi suivant = D+MOD(D;7)+3
D = mardi => jeudi suivant = D+MOD(D;7)+2
D = mercredi => jeudi suivant = D+MOD(D;7)+1
D = jeudi => jeudi suivant = D+MOD(D;7)+0
D = vendredi => jeudi suivant = D+MOD(D;7)+6
D = samedi => jeudi suivant = D+MOD(D;7)+5
Il faut donc passer de la série 1-2-3-4-5-6-0 (MOD(D;7)) à la série 4-3-2-1-0-6-5 pour avoir la valeur à ajouter à D+MOD(D;7) qui permet d'obtenir le jeudi suivant.
En ajoutant 1 à D (cad en décalant D d'un jour), MOD(D+1;7) donne la série 2-3-4-5-6-0-1. Pour passer de 2-3-4-5-6-0-1 à 4-3-2-1-0-6-5, il suffit de retrancher cette série du nombre 6, ce qui donne la formule 6-MOD(D+1;7): c'est la valeur à ajouter à D que nous cherchons.
Donc, la formule permettant (au calendrier 1900) d'obtenir le jeudi qui suit la date D (D inclus) est:
J=D+6-MOD(D+1;7), soit J=D-MOD(D+1;7)+6
[Et d'une manière générale, pour avoir le premier jour X (X=1 pour lundi, 2 pour mardi etc.) qui suit D (inclus), la formule serait
=D-MOD(D-X+5;7)+6]
Comme le jour de l'an est donné par DATE(ANNEE(D);1;1), on obtient donc le premier jeudi de l'année par la formule:
J=DATE(ANNEE(D);1;1)-MOD(DATE(ANNEE(D);1;1)+1;7)+6
... et l'avant-dernier lundi avant J par :
L=DATE(ANNEE(D);1;1)-MOD(DATE(ANNEE(D);1;1)+1;7)+6-10, soit :
L=DATE(ANNEE(D);1;1)-MOD(DATE(ANNEE(D);1;1)+1;7)-4
...ce qui donne une première formule très approximative pour le numéro de semaine (qui ne marchera pas pour un grand nombre de semaines n°1, 52 ou 53, comme indiqué précédemment):
NS(D)=ENT((D-DATE(ANNEE(D);1;1)+MOD(DATE(ANNEE(D);1;1)+1;7)+4)/7)
--------------------------------
Au prochain épisode : comment tenir compte des semaines 'à la marge' (semaines 1, 52 et 53) pour lesquelles cette formule ne fonctionne pas?
======================================
CHAPITRE 3 : cas des semaines 'limite'
======================================
Or donc, cette formule est à peu près correcte :
=ENT((D-DATE(ANNEE(D);1;1)+MOD(DATE(ANNEE(D);1;1)+1;7)+4)/7)
... mais elle ne prend pas en compte les cas particuliers des premières et dernières semaines de l'année.
Par exemple, pour D=31/12/97, le numéro de semaine est 1, et non pas 53. En effet, il ne s'agit pas, dans la définition ISO, de la dernière semaine de l'année 1997, mais de la *première* semaine de l'année suivante (1998), qui commence le lundi 29/12/97. A l'inverse, le numéro de semaine du 1/1/99 n'est pas 1, mais 53 (c'est la 53ème semaine de l'année 1998, la semaine 1 de 1999 commençant le lundi 4/1/99).
Pour ces cas particuliers, la règle est simple :
1) si la date D se situe à l'extrême fin de l'année A et que son jour de la semaine est entre lundi et mercredi, le numéro de semaine est 1 (1ère semaine de l'année A+1).
2) si la date D se situe au début de l'année A et qu'elle est entre vendredi et dimanche, son numéro de semaine est le dernier de l'année précédente (52 ou 53 selon l'année concernée).
Dans le cas 1), au lieu de soustraire à la date le premier lundi de l'année A, il faut soustraire celui de A+1. Dans le cas 2), il faut au contraire soustraire celui de A-1.
Ceci revient à prendre, à la place de ANNEE(D) :
- l'année du premier jeudi ('JS') qui suit D, si lundi <=D <=mercredi (cas 1)
- ou alors l'année du premier jeudi ('JS') qui précède D, si vendredi <=D <=dimanche.
De cette manière, l'année est ajustée correctement pour ces semaines 'limites' se situant en fin ou en début d'année (incrémentée ou décrémentée). Pour les autres, le fait de prendre le jeudi précédant ou suivant n'aura aucune incidence sur A.
La formule permettant d'obtenir JS à partir de D repose également sur la fonction MOD (même genre de manipulation que précédemment):
JS = D-MOD(D-2;7)+3
En remplaçant ANNEE(D) par ANNEE(D-MOD(D-2;7)+3) dans la formule de départ, on obtient donc une formule qui renvoie le numéro de semaine en tenant compte des cas particuliers:
=ENT((D-DATE(ANNEE(D-MOD(D-2;7)+3);1;1)+MOD(DATE(ANNEE(D-MOD(D-2;7)+3);1;1)+1;7)+4)/7)
... ce qui est déjà bien plus élégant et court qu'une formule qui reposerait sur une batterie de SI() imbriqués, mais reste quand-même un peu lourdingue.
Mon prochain (et dernier, pour soulager JPS) message montre comment simplifier ce type de formule.
Bien entendu, à l'aide d'une formule matricielle !
Le numéro de semaine ISO est donc obtenu par :
=ENT((D-DATE(ANNEE(D-MOD(D-2;7)+3);1;1)+MOD(DATE(ANNEE(D-MOD(D-2;7)+3);1;1)+1;7)+4)/7)
Cette formule est parfaitement correcte et pourrait suffir. Mais tant qu'on y est, autant continuer à s'amuser un peu et essayer de la rendre plus compacte...
Déjà une petite simplification :
=ENT((D-DATE(ANNEE(D-MOD(D-2;7)+3);1;2)+MOD(DATE(ANNEE(D-MOD(D-2;7)+3);1;2);7)+5)/7)
Dans cette formule, on retrouve deux fois la même expression:
DATE(ANNEE(D-MOD(D-2;7)+3);1;2). En la remplaçant par 'J':
=ENT((D-J+MOD(J;7)+5)/7), ou encore =ENT((D-(J-MOD(J;7))+5)/7).
Est-il possible de ne calculer qu'une seule fois J dans cette formule?
C'est possible en recourant à une formule matricielle. En supposant que l'on ait J et MOD(J;7) dans les 2 cellules A1 et A2, on peut les soustraire par la formule A1-A2, mais aussi par la formule
=SOMME(A1:A2*{1;-1}).
--------
Petite explication :
Les {} indiquent la présence d'un tableau de constantes. A l'intérieur de ce tableau, les lignes sont séparées par des points-virgules et les colonnes par des points. Par exemple, {1.2.3;4.5.6;7.8.9} correspond au tableau suivant:
1 2 3
4 5 6
7 8 9
Ces tableaux peuvent être additionnés entre eux, multipliés, divisés... Par exemple, la formule ={1.2.3;4.5.6;7.8.9}*{10.9.8;7.6.5;4.3.2} multiplie les éléments correspondants de chaque matrice et renvoie
{10.18.24;28.30.30;28.24.18}:
1 2 3 10 9 8 10 18 24
4 5 6 * 7 6 5 => 28 30 30
7 8 9 4 3 2 28 24 18
Si on met cette formule dans une plage de 3 lignes sur 3 colonnes et qu'on valide par Ctrl-Maj-Entrée (formule matricielle), on obtient ces 9 valeurs. Et, entrée dans une seule cellule, la formule
=SOMME({1.2.3;4.5.6;7.8.9}*{10.9.8;7.6.5;4.3.2})
multiplie les deux tableaux entre eux, fait la somme du tableau qui résulte de cette multiplication et renvoie cette somme (210).
Les formules matricielles qui ne reposent que sur des matrices de constantes {}, et pas sur des plages, n'ont pas besoin d'être saisies par Ctrl-Maj-Entrée.
--------
=SOMME(A1:A2*{1;-1}) renvoie donc la même chose que SOMME(A1*1;A2*-1), c'est à dire A1-A2.
Si A1=J et A2=MOD(J;7), J-MOD(J;7) pourrait être remplacé par SOMME(A1:A2*{1;-1}).
Maintenant, on n'a pas A1:A2, mais d'un côté la valeur J, et de l'autre MOD(J;7). Comment transformer ces deux valeurs en tableau? Pour cela, il faudrait utiliser la formule MOD de manière matricielle:
MOD(J;{X;7})
... qui devrait renvoyer les deux valeurs :
J (= MOD(J;X))
MOD(J;7)
Il faut donc trouver une valeur X telle que MOD(J;X) soit toujours égal à J. Pour cela, il suffit de s'assurer que X est supérieur à la valeur maximale que peut prendre J. Si B>A, MOD(A;B ) est en effet toujours égal à A. J étant une date, elle ne peut pas dépasser la valeur 2958465
(31/12/9999), donc il suffit que X soit supérieur à ce nombre. X=1E99 remplit cette condition et est un nombre rapide à saisir.
Ce qui donne la formule MOD(J;{1E99;7}), qui renvoie le tableau des deux valeurs recherché:
J
MOD(J;7)
En multipliant ces deux valeurs par {1;-1} et en faisant la somme, on obtient la même chose que J-MOD(J;7):
SOMME(MOD(J;{1E99;7})*{1;-1})
La formule =ENT((D-(J-MOD(J;7))+5)/7) (numéro de semaine) devient donc
=ENT((D-SOMME(MOD(J;{1E99;7})*{1;-1})+5)/7), et en remplaçant J par
DATE(ANNEE(D-MOD(D-2;7)+3);1;2):
=ENT((D-SOMME(MOD(DATE(ANNEE(D-MOD(D-2;7)+3);1;2);{1E99;7})*{1;-1})+5)/7)
Par rapport à la première formule, celle-ci est tout aussi 'illisible' au premier abord, mais un peu plus courte et élégante. Son défaut est de nécessiter le calendrier 1900. Au calendrier 1904, la valeur renvoyée sera fantaisiste.
Pour que le résultat ne dépende pas de ce paramètre, il faut recourir à la fonction JOURSEM et une formule un peu plus lourde, du type:
=ENT((D-(DATE(ANNEE(D-JOURSEM(D-1)+4);1;3)-JOURSEM(DATE(ANNEE(D-JOURSEM(D-1)+4);1;3)))+5)/)
========================================
Voilà, vous êtes arrivés au bout !
A plus,
Message édité par: groussel, à: 20/11/2005 10:59
