Une formule dure à comprendre…
La
formule ci-dessous serait due à Laurent
Longre, qui avait entre autres créé dans
le temps une DLL appelée MoreFunc.DLL.
L’objectif
est d’identifier le premier lundi de la semaine indiquée en B2 pour l’année en
B1.
Voici
la formule placée en B4 :
=somme(mod(("1/"&B1)-{0;6};{1E+99;7})*{1;-1})+B2*7-4
=somme(mod(("1/"&B1)-{0;6};{1E+99;7})*{1;-1})+B2*7-4
Pouvez-vous
comprendre comment cette formule s’y prend pour obtenir le bon résultat ?
Les
explications sérieuses suivantes proviennent du MVP Craig Hatmaker, qui a fourni une
analyse détaillée du processus utilisé. Il a dû tirer cela de son chapeau… Les
explications plus farfelues sont de votre serviteur.
La
première date normale dans Excel est 1, pour le dimanche 1er
janvier 1900.
La
première date possible dans Excel est 0, pour le samedi précédent.
Toute valeur négative est refusée comme date.
On
voit bien là que Bill Gates est un peu un extraterrestre puisqu’il fait
commencer Excel à une date qui n’existe pas. Steve Jobs, qui n’était pas un
extraterrestre, a fait débuter Excel Mac sur le 1er janvier 1904, ce
qui fait qu’il y avait 4 ans et 1 jour d’écart entre les deux calendriers. Vous
pouvez le vérifier en passant par les options d’Excel, dans la section « Options avancées », bloc « Lors du calcul de ce classeur ».
"1/"&B1 donne le 1er
janvier de l’année en B1.
("1/"&B1)-{0;6}
donne le 1er janvier de l’année et 6 jours plus tôt.
1E+99 est un nombre si grand que
n’importe quelle date divisée par ce nombre a un reste égal à la date. Si
nous remplaçons ce grand nombre par 1000000, nous obtenons le même résultat…
{1er janvier;Reste} * {1 ;-1} donne {1er janvier;-Reste}
Somme({1er janvier;-Reste})
donne le vendredi précédant le lundi cherché
Quand
la semaine cherchée est 1, alors 7*semaine – 4 = 3 : on cherche donc le
troisième jour après le vendredi cité ci-dessus, ce qui est bien un
lundi !
En
général, dans ce blog, j’essaye de vous apprendre à faire simple.
Aujourd’hui,
pour changer, c’était l’inverse. Mais un peu de stimulation intellectuelle pour
disperser les vapeurs de l’alcool de fin d'année, c’est peut-être bien utile…
J’espère
que cela ne vous empêchera pas de bien profiter de votre réveillon…
Bonne année !
rédigé par Hervé Thiriez |
1 commentaire(s)
1 Commentaire(s):
Ce commentaire a été supprimé par l'auteur.
By
Benji, sur 4:02 PM
Enregistrer un commentaire
<< Accueil