Power Query is de hete, jonge, nieuwe functie in Excel. Onthoud dat het niet is gemaakt door het Excel-team. Het Power Query-team heeft besloten dat ze niet gebonden zullen zijn aan een decennia-oude fout in VisiCalc. Dit is waarom dat problemen voor u kan veroorzaken.
Voor achtergrond worden datums in Excel opgeslagen als een serienummer. Als je de datum van vandaag - 30 maart 2018 in Excel invoert, zullen ze je een vorm van de datum laten zien, maar Excel slaat 43189 op. Dat betekent dat het vandaag 43189 dagen is sinds 1 januari 1900.
Het betekent ook dat je kunt gebruiken om naar de date van morgen te gaan =F3+1. Om het aantal dagen tussen datums te vinden, kunt u de ene datum van de andere aftrekken. Het is een goede manier om datumberekeningen toe te staan.
Voorzichtigheid
Het betekent ook dat je niet gemakkelijk datums uit de 19e eeuw kunt opnemen. Dit is slecht voor geneaologen of accountants bij 150 jaar oude bedrijven.
Het systeem is uitgevonden door Dan Bricklin en Bob Frankston toen ze VisiCalc creëerden in 1978-1979. Maar Bob en Dan maakten één fout. Laten we ons model ver terug in de tijd nemen, naar februari en maart 1900. De datum die hoort bij het serienummer 60 is 29 februari 1900.
Schrikkeldagen gaan terug naar Julius Caesar. Aangezien de aarde 365,242189 dagen nodig heeft om rond de zon te draaien, betekent een kalender van 365 dagen dat de seizoenen elke eeuw met 24 dagen zouden verschuiven. Julius Caesar bedacht een plan om elk jaar een schrikkeldag toe te voegen die deelbaar is door 4. Dat zou perfect zijn geweest als de aarde elke 365,25 dagen rond de zon zou draaien. Maar dat kleine verschil van .25 tot .242189 betekende dat de seizoenen in de loop van twee millennia nog steeds uit waren. Paus Gregorius stelde in 1582 een systeem voor waarin er drie regels waren:
- Regel 1: Een jaar dat deelbaar is door 4 zou een schrikkeljaar zijn, behalve:
- Regel 2: Een jaar dat deelbaar is door 100 zou geen schrikkeljaar zijn, behalve:
- Regel 3: Een jaar dat deelbaar is door 400, zou een schrikkeljaar zijn.
De regels werden in 1582 voorgesteld, maar werden traag aangenomen. Japan ging pas in 1873 akkoord. Bulgarije, Estland, Rusland, Griekenland en Turkije schakelden over van 1916-1927. Regel 2 is pas gebeurd in 1700, 1800 en 1900. Regel 3 vond plaats in 1600 en 2000. Als je dit leest, leefde je waarschijnlijk op 29 februari 2000, maar je hebt je misschien niet gerealiseerd dat het een uitzondering was op een uitzondering op een uitzondering. Maar in 1978 was dit 79 jaar niet gebeurd, dus het was niet algemeen bekend. VisiCalc maakte de fout 29 februari 1900 op te nemen.
Het maakt eigenlijk niet uit. Wie gaat er ooit terug om te zien of 2 februari 1900 een donderdag of een woensdag was (Excel zegt dat het een donderdag was, maar het was echt een woensdag). En wie gaat de openstaande vorderingen vanaf begin 1900 volgen? Laten we eerlijk zijn, als u op 15 februari 1900 een leverancier heeft gefactureerd en deze heeft u nog niet betaald, is het tijd om de vordering af te schrijven.
Voor compatibiliteit programmeerde Mitch Kapor dezelfde fout in Lotus 1-2-3.
Steve Jobs, die niet met opzet een fout wilde programmeren, deed de Macintosh-klok op 1 januari 1904 starten.
Bij Microsoft moest Excel compatibel zijn met de toenmalige marktleider Lotus 1-2-3, en het niet-bestaande 29 februari 1900 werd geïntroduceerd in Excel en blijft daar tot op de dag van vandaag.
Maar de architecten van Power Query zijn geen mensen van spreadsheets. Ze hebben geen standbeelden van Bricklin en Frankston in hun kantoor. Ze kennen deze geschiedenis niet. Ze hebben besloten dat hun datums het aantal dagen zijn dat is verstreken sinds 31 december 1899. Hierdoor voelen Power Query-mensen zich een klein beetje superieur aan Excel-mensen omdat de weekdagen die begin 1900 door Power Query voor 60 dagen worden gerapporteerd, correcter zijn dan in Excel.
Dit is niets om u zorgen over te maken. Niemand heeft te maken met data uit die 60 dagen.
Maar hier is iets veel gebruikelijker. Beschouw deze tabel met bekende getallen.
Ik ga je vragen "het verkeerde te doen" en per ongeluk deze stappen te volgen:
- Selecteer kolom N
- Druk op Ctrl + Shift + 3 om de kolom als datum op te maken
-
Merk niet op dat u een van deze dingen hebt gedaan.
Oeps - de cijfers zijn onbedoeld datums - Gebruik gegevens, ophalen en transformeren, uit tabel of bereik.
- Let bij Power Query op de datums in de cijferkolom. Verwijder in het vak Toegepaste stappen de stap Gewijzigde indeling.
Wanneer de gegevens terugkomen in Excel, is alles 1. Er zijn geen cirkels meer. Heinz heeft 56 rassen in plaats van 57.
Ik realiseer me dat dit onze laatste vrijdag in Excel Lent is. Ik realiseer me dat dit een gekunsteld en obscuur voorbeeld is. Hoe groot is de kans dat iemand per ongeluk een kolom met getallen in het bereik 1-60 als datums opmaakt voordat hij naar Power Query gaat? Het lijkt laag, maar het is gebeurd.
Power Query is een geweldige functie. Ik weet zeker dat de architecten dachten dat er niets mis kon gaan als ze slimmer waren dan die mensen in 1978 die een fout maakten. Maar zullen miljarden spreadsheets werken omdat we het er allemaal over eens zijn om de fout te accepteren, je scheurt een klein gaatje in de structuur van Excel.
Elke vrijdag onderzoek ik een bug of ander visachtig gedrag in Excel.
Excel-gedachte van de dag
Ik heb mijn Excel Master-vrienden om advies over Excel gevraagd. De gedachte van vandaag om na te denken:
"Ctrl + Shift + U verandert de hoogte van de formulebalk"
Bob Umlas
