Power Query är den heta, unga, nya funktionen i Excel. Kom ihåg att det inte skapades av Excel-teamet. Power Query-teamet bestämde att de inte kommer att vara bundna av ett decennier gammalt misstag i VisiCalc. Här är varför det kan orsaka problem för dig.
Som bakgrund lagras datum i Excel som ett serienummer. Om du lägger in dagens datum - 30 mars 2018 i Excel, kommer de att visa dig någon form av datumet, men Excel lagrar 43189. Det betyder att det idag är 43189 dagar sedan 1 januari 1900.
Det betyder också att du kan använda för att komma till morgondagens datum =F3+1. För att hitta antalet dagar mellan datum kan du dra ett datum från ett annat. Det är ett bra sätt att tillåta datumberäkningar.
Varning
Det betyder också att du inte enkelt kan spela in datum från 1800-talet. Detta är dåligt för geneaologer eller revisorer på 150 år gamla företag.
Systemet uppfanns av Dan Bricklin och Bob Frankston när de skapade VisiCalc 1978-1979. Men Bob och Dan gjorde ett misstag. Låt oss ta vår modell tillbaka i tiden, till februari och mars 1900. Datumet associerat med serienumret 60 är den 29 februari 1900.
Skottdagar går tillbaka till Julius Caesar. Eftersom det tar jorden 365,242189 dagar att gå runt solen betyder det att ha en kalender på 365 dagar att årstiderna skiftar med 24 dagar varje sekel. Julius Caesar skapade en plan för att lägga till en språngdag i varje år som kan delas med 4. Det skulle ha varit perfekt om jorden gick runt solen var 365,25 dagar. Men den lilla skillnaden mellan .25 och .242189 innebar att årstiderna fortfarande var av under två årtusenden. Påven Gregorius föreslog ett system 1582 där det fanns tre regler:
- Regel 1: Ett år som kan delas med 4 skulle vara ett skottår, förutom:
- Regel 2: Ett år som kan delas med 100 skulle inte vara ett skottår, förutom:
- Regel 3: Ett år som kan delas med 400 skulle vara ett skottår.
Reglerna föreslogs 1582 men antogs långsamt. Japan gick inte med förrän 1873. Bulgarien, Estland, Ryssland, Grekland och Turkiet bytte från 1916-1927. Regel # 2 har bara hänt 1700, 1800 och 1900. Regel 3 hände 1600 och 2000. Om du läser detta levde du troligen den 29 februari 2000, men du kanske inte har insett att det var ett undantag från ett undantag från ett undantag. Men tillbaka 1978 hade detta inte hänt på 79 år, så det var inte allmänt känt. VisiCalc gjorde misstaget att inkludera den 29 februari 1900.
Det är egentligen inte en stor sak. Vem kommer någonsin tillbaka för att se om den 2 februari 1900 var en torsdag eller en onsdag (Excel säger att det var en torsdag, men det var verkligen en onsdag). Och vem kommer att spåra öppna fordringar från början av 1900? Låt oss inse det, om du fakturerade en säljare den 15 februari 1900 och de inte har betalat dig ännu, är det dags att skriva av fordran.
För kompatibilitet programmerade Mitch Kapor samma fel in i Lotus 1-2-3.
Steve Jobs, som inte avsiktligt vill programmera ett fel, fick Macintosh-klockan att starta den 1 januari 1904.
På Microsoft måste Excel vara kompatibelt med dåvarande marknadsledare Lotus 1-2-3, och den obefintliga 29 februari 1900 introducerades i Excel och förblir där till denna dag.
Men Power Query-arkitekterna är inte kalkylark. De har inte statyer av Bricklin och Frankston på sitt kontor. De känner inte till den här historien. De fattade beslutet att deras datum skulle vara antalet dagar som gått sedan 31 december 1899. Detta gör att Power Query-människor känner sig lite överlägsna Excel-människor eftersom de vardagar som Power Query rapporterade i 60 dagar i början av 1900 är mer korrekta än i Excel.
Det här är inget att oroa sig för. Ingen har att göra med datum från de 60 dagarna.
Men här är något mycket vanligare. Tänk på den här tabellen med välkända siffror.
Jag kommer att be dig att "göra fel saker" och följa dessa steg av misstag:
- Välj kolumn N
- Tryck på Ctrl + Skift + 3 för att formatera kolumnen som ett datum
-
Lägg inte märke till att du gjorde någon av dessa saker.
Oj - siffrorna är av misstag datum - Använd data, få och transformera, från tabell eller intervall.
- När du kommer till Power Query, lägg märke till datumen i nummerkolumnen. Ta bort steget Ändrat format i rutan Tillämpade steg.
När data kommer tillbaka till Excel är allt avstängt med 1. Cirklar finns inte längre. Heinz har 56 sorter istället för 57.
Jag inser att det här är vår sista fredag i Excel-fastan. Jag inser att detta är ett konstruerat och dunkelt exempel. Vilka är oddsen som någon av misstag skulle formatera en kolumn med siffror i intervallet 1-60 som datum innan de går till Power Query? Det verkar lågt, men det har hänt.
Power Query är en fantastisk funktion. Jag är säker på att arkitekterna ansåg att ingenting kunde gå fel om de var smartare än de människor 1978 som gjorde ett misstag. Men kommer miljarder kalkylark att fungera för att vi alla är överens om att acceptera misstaget, du sliter ett litet hål i Excel-tyget.
Varje fredag undersöker jag ett fel eller annat fiskigt beteende i Excel.
Excel-tanke på dagen
Jag har frågat mina Excel Master-vänner om deras råd om Excel. Dagens tanke att fundera på:
"Ctrl + Skift + U växlar höjden på formelfältet"
Bob Umlas
