Retour à la FAQ
Les numéros de semaine par Laurent Longre
et en fin de page une fonction d'Yves Chevalier
Je dédie ce roman à JPS, Zaza, Catherine, mon chien Arthur, Nicolas,
HPhaneSte et toute la clique.
Mes remerciements à Iznogood, dont les questions empreintes d'une
angoisse démesurée m'ont donné l'idée du scénario:
"SOS Laurent !
Je comprends pas la formule qui renvoie le n° de la semaine extraite de
ton site:
=ENT((D+5-SOMME(MOD(DATE(ANNEE(D-MOD(D-2;7)+3);1;2);{1E+99;7})*{1;-1}))/7)
A quoi sert le SOMME ?
C'est quoi ce {1E+99;7} ?
C'est quoi ce {1,-1} ?
A quoi servent les accolades ?"
[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.
Laurent
=====================================
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 nombre
de 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 inclu)?
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 suffire. 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)
... et voilà, c'est la formule qui intriguait Iznogood !
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)/7)
==========
THE END
==========
(PS : la prochaine fois, Iznogood, demande-moi plutôt comment on fait
une addition sous Excel, là j'ai besoin de me reposer un peu la tête...
;-)
============================
J'ai sélectionné une plage de 3 lignes et 3 colonnes (9+ cellules) et j'ai
placé ta ta formule ={1.2.3;4.5.6;7.8.9}*{10.9.8;7.6.5;4.3.2}et valider par
Ctrl+Shift+Entrée.
Oh surprise, Excel m'avertit qu'il y a une erreur et il me la corrige
lorsque je valide..
Par contre je n'ai plus la formule de départ, elle est devenue :
={1.23;4.56;7.89}*{10.98;7.65;4.32}
et les résultats ne sont pas ceux escomptés.
? ? ? ? ? ? ? Où est l'erreur ?
Tu utilises probablement le point comme séparateur décimal ?
Daas ce cas, le séparateur de colonnes ne pouvant plus être le point,
Excel le remplace automatiquement par l'antislash. Il faut alors
utiliser la formule ={1\2\3;4\5\6;7\8\9}*{10\9\8;7\6\5;4\3\2}.
Laurent
============================
Ci-dessous proposition d’une fonction VBA calculant le N° de semaine d’une date quelconque.
Exercice, en amicale réponse à LAURENT LONGRE dont j’ai apprécié la belle formule,
traitant du même sujet, avec ses 15 pages d’explications très précises. Bravo à lui.
Pour info « DATEENCOURS » provient d’une quelconque cellule (par exempleA1) la formule sera donc,
si la programmation ci-dessous est positionnée dans PERSO.XLS
=PERSO.XLS!NIEMESEMAINE(A1)
Yves Chevalier
FUNCTION NIEMESEMAINE(DATEENCOURS)
'CALCUL DU NUMERO DE SEMAINE SUIVANT NORME ISO
'LE 29/10/2002 YC
'DEFINITION DU SEMESTRE CONCERNE
DATEJUIN = CDATE("30/06/" & YEAR(DATEENCOURS))
IF DATEENCOURS >= DATEJUIN THEN
INDEX = 1
ELSE
INDEX = -1
END IF
'DEFINITION DU JOUR DE L'AN DE L'ANNEE CONCERNEE
JOURDELAN = "01/01/" & YEAR(DATEENCOURS)
'DEFINITION DU N° DU JOUR DE L'AN DE L'ANNEE CONCERNEE
NUMJOURDELAN = WEEKDAY(JOURDELAN, VBMONDAY)
'DEFINITION DU PREMIER LUNDI DE L'ANNEE CONCERNEE
IF NUMJOURDELAN < 4 THEN
DATEPRELUNDIDELAN = DATEADD("D", -(NUMJOURDELAN - 1), JOURDELAN)
ELSE: DATEPRELUNDIDELAN = DATEADD("D", (8 - NUMJOURDELAN), JOURDELAN)
END IF
'DEFINITION DU JOUR DE L'AN DE L'ANNEE ADJACENTE (ADJ)
JOURDELANADJ = "01/01/" & (YEAR(DATEENCOURS) + INDEX)
'DEFINITION DU N° DU JOUR DE L'AN DE L'ANNEE ADJACENTE (ADJ)
NUMJOURDELANADJ = WEEKDAY(JOURDELANADJ, VBMONDAY)
'DEFINITION DU PREMIER LUNDI DE L'ANNEE ADJACENTE (ADJ)
'SI LE 01/01 EST AU PLUS TARD UN JEUDI ...
IF NUMJOURDELANADJ < 4 THEN
DATEPRELUNDIDELANADJ = DATEADD("D", -(NUMJOURDELANADJ - 1), JOURDELANADJ)
ELSE: DATEPRELUNDIDELANADJ = DATEADD("D", (8 - NUMJOURDELANADJ), JOURDELANADJ)
END IF
'SI DATEENCOURS SE SITUE DANS LE PREMIER SEMESTRE,
'LES CALCULS SE FONT AVEC AN ET AN-1
IF INDEX > 0 THEN
IF DATEENCOURS >= DATEPRELUNDIDELANADJ THEN
NIEMESEMAINE = INT(ABS(DATEDIFF("D", DATEENCOURS, DATEPRELUNDIDELANADJ, VBMONDAY, VBFIRSTFOURDAYS)) / 7) + 1
ELSE
NIEMESEMAINE = INT(ABS(DATEDIFF("D", DATEENCOURS, DATEPRELUNDIDELAN, VBMONDAY, VBFIRSTFOURDAYS)) / 7) + 1
END IF
'SI DATEENCOURS SE SITUE DANS LE DEUXIEME SEMESTRE
'LES CALCULS SE FONT AVEC AN ET AN+1
ELSE
IF DATEENCOURS < DATEPRELUNDIDELAN THEN
NIEMESEMAINE = INT(ABS(DATEDIFF("D", DATEENCOURS, DATEPRELUNDIDELANADJ, VBMONDAY, VBFIRSTFOURDAYS)) / 7) + 1
ELSE
NIEMESEMAINE = INT(ABS(DATEDIFF("D", DATEENCOURS, DATEPRELUNDIDELAN, VBMONDAY, VBFIRSTFOURDAYS)) / 7) + 1
END IF
END IF
END FUNCTION
[top]