Power Query est la nouvelle fonctionnalité récente d'Excel. N'oubliez pas qu'il n'a pas été créé par l'équipe Excel. L'équipe Power Query a décidé qu'elle ne serait pas liée par une erreur vieille de plusieurs décennies dans VisiCalc. Voici pourquoi cela peut vous causer des problèmes.
Pour le fond, les dates dans Excel sont stockées sous forme de numéro de série. Si vous entrez la date d'aujourd'hui - 30 mars 2018 dans Excel, ils vous montreront une forme de date, mais Excel stocke 43189. Cela signifie qu'aujourd'hui 43189 jours se sont écoulés depuis le 1er janvier 1900.
Cela signifie également que pour arriver à la date de demain, vous pouvez utiliser =F3+1. Pour trouver le nombre de jours entre les dates, vous pouvez soustraire une date d'une autre. C'est un bon moyen de permettre les calculs de date.
Mise en garde
Cela signifie également que vous ne pouvez pas facilement enregistrer les dates des années 1800. C'est mauvais pour les généologues ou les comptables dans des entreprises vieilles de 150 ans.
Le système a été inventé par Dan Bricklin et Bob Frankston lorsqu'ils ont créé VisiCalc en 1978-1979. Mais Bob et Dan ont fait une erreur. Reprenons notre modèle dans le temps, jusqu'en février et mars 1900. La date associée au numéro de série 60 est le 29 février 1900.
Les jours bissextiles remontent à Jules César. Comme il faut à la Terre 365,242189 jours pour faire le tour du soleil, avoir un calendrier de 365 jours signifie que les saisons changeraient de 24 jours chaque siècle. Jules César a créé un plan pour ajouter un jour bissextile chaque année divisible par 4. Cela aurait été parfait si la Terre faisait le tour du Soleil tous les 365,25 jours. Mais cette infime différence entre 0,25 et 0,242189 signifiait qu'au cours de deux millénaires, les saisons étaient toujours éteintes. Le pape Grégoire a proposé un système en 1582 où il y avait trois règles:
- Règle 1: Une année divisible par 4 serait une année bissextile, sauf:
- Règle 2: Une année divisible par 100 ne serait pas une année bissextile, sauf:
- Règle 3: Une année divisible par 400 serait une année bissextile.
Les règles ont été proposées en 1582 mais tardent à être adoptées. Le Japon ne fut d'accord qu'en 1873. La Bulgarie, l'Estonie, la Russie, la Grèce et la Turquie passèrent de 1916 à 1927. La règle n ° 2 ne s'est produite qu'en 1700, 1800 et 1900. La règle n ° 3 s'est produite en 1600 et 2000. Si vous lisez ceci, vous étiez probablement en vie le 29 février 2000, mais vous n'avez peut-être pas réalisé que c'était une exception à une exception à une exception. Mais en 1978, cela ne s'était pas produit depuis 79 ans, donc ce n'était pas largement connu. VisiCalc a commis l'erreur d'inclure le 29 février 1900.
Ce n’est pas vraiment grave. Qui revient jamais pour voir si le 2 février 1900 était un jeudi ou un mercredi (Excel dit que c'était un jeudi, mais c'était vraiment un mercredi). Et qui va suivre les créances ouvertes à partir du début de 1900? Regardons les choses en face, si vous avez facturé un fournisseur le 15 février 1900 et qu'il ne vous a pas encore payé, il est temps de radier la créance.
Pour des raisons de compatibilité, Mitch Kapor a programmé la même erreur dans Lotus 1-2-3.
Steve Jobs, ne voulant pas programmer intentionnellement une erreur, fit démarrer l'horloge du Macintosh le 1er janvier 1904.
Chez Microsoft, Excel devait être compatible avec le leader du marché Lotus 1-2-3, et l'inexistant 29 février 1900 a été introduit dans Excel et y reste encore aujourd'hui.
Mais les architectes Power Query ne sont pas des tableurs. Ils n'ont pas de statues de Bricklin et Frankston dans leur bureau. Ils ne connaissent pas cette histoire. Ils ont décidé que leurs dates correspondraient au nombre de jours écoulés depuis le 31 décembre 1899. Cela fait que les utilisateurs de Power Query se sentent un peu supérieurs aux utilisateurs d'Excel car les jours de la semaine signalés par Power Query pendant 60 jours au début de 1900 sont plus corrects que dans Excel.
Il n'y a pas de quoi s'inquiéter. Personne ne traite des dates de ces 60 jours.
Mais voici quelque chose de bien plus courant. Considérez ce tableau de nombres bien connus.
Je vais vous demander de "faire la mauvaise chose" et de suivre accidentellement ces étapes:
- Sélectionnez la colonne N
- Appuyez sur Ctrl + Maj + 3 pour formater la colonne sous forme de date
-
Ne remarquez pas que vous avez fait l'une ou l'autre de ces choses.
Oups - les chiffres sont des dates par inadvertance - Utiliser des données, obtenir et transformer, à partir d'une table ou d'une plage.
- Lorsque vous accédez à Power Query, notez les dates dans la colonne numérique. Dans la zone Étapes appliquées, supprimez l'étape Format modifié.
Lorsque les données reviennent dans Excel, tout est décalé de 1. Les cercles n'existent plus. Heinz a 56 variétés au lieu de 57.
Je sais que c'est notre dernier vendredi dans le Carême d'Excel. Je me rends compte que c'est un exemple artificiel et obscur. Quelles sont les chances qu'une personne formate accidentellement une colonne de nombres compris entre 1 et 60 en tant que dates avant d'accéder à Power Query? Cela semble faible, mais c'est arrivé.
Power Query est une fonctionnalité géniale. Je suis sûr que les architectes ont pensé que rien ne pouvait mal tourner s'ils étaient plus intelligents que ces gens en 1978 qui ont fait une erreur. Mais des milliards de feuilles de calcul fonctionneront-elles parce que nous sommes tous d'accord pour accepter l'erreur, vous déchirez un petit trou dans le tissu d'Excel.
Tous les vendredis, j'examine un bug ou un autre comportement louche dans Excel.
Excellente pensée du jour
J'ai demandé à mes amis Excel Master leurs conseils sur Excel. Pensée d'aujourd'hui à méditer:
"Ctrl + Maj + U bascule la hauteur de la barre de formule"
Bob Umlas
