Power Query es la nueva y novedosa característica de Excel. Recuerde que no fue creado por el equipo de Excel. El equipo de Power Query decidió que no van a estar atados por un error de décadas en VisiCalc. He aquí por qué eso puede causarle problemas.
Para el fondo, las fechas en Excel se almacenan como un número de serie. Si ingresa la fecha de hoy: 30 de marzo de 2018 en Excel, le mostrarán alguna forma de la fecha, pero Excel almacena 43189. Eso significa que hoy son 43189 días desde el 1 de enero de 1900.
También significa que para llegar a la fecha de mañana, podría utilizar =F3+1. Para encontrar el número de días entre fechas, puede restar una fecha de otra. Es una buena forma de permitir cálculos de fechas.
Precaución
También significa que no puede registrar fácilmente fechas del siglo XIX. Esto es malo para los genetistas o contadores de empresas de 150 años.
El sistema fue inventado por Dan Bricklin y Bob Frankston cuando crearon VisiCalc en 1978-1979. Pero Bob y Dan cometieron un error. Retrocedamos nuestro modelo en el tiempo, hasta febrero y marzo de 1900. La fecha asociada con el número de serie 60 es el 29 de febrero de 1900.
Los días bisiestos se remontan a Julio César. Dado que la Tierra tarda 365,242189 días en dar la vuelta al sol, tener un calendario de 365 días significa que las estaciones cambiarían 24 días cada siglo. Julio César creó un plan para agregar un día bisiesto en cada año divisible por 4. Eso habría sido perfecto si la Tierra girara alrededor del Sol cada 365,25 días. Pero esa pequeña diferencia de .25 a .242189 significó que en el transcurso de dos milenios, las estaciones aún estaban apagadas. El Papa Gregorio propuso un sistema en 1582 donde había tres reglas:
- Regla 1: Un año divisible por 4 sería un año bisiesto, excepto:
- Regla 2: Un año divisible por 100 no sería un año bisiesto, excepto:
- Regla 3: Un año divisible por 400 sería un año bisiesto.
Las reglas se propusieron en 1582 pero tardaron en adoptarse. Japón no estuvo de acuerdo hasta 1873. Bulgaria, Estonia, Rusia, Grecia y Turquía cambiaron de 1916-1927. La regla n. ° 2 solo sucedió en 1700, 1800 y 1900. La regla n. ° 3 sucedió en 1600 y 2000. Si está leyendo esto, probablemente estaba vivo el 29 de febrero de 2000, pero es posible que no se haya dado cuenta de que era una excepción a una excepción a una excepción. Pero en 1978, esto no había sucedido durante 79 años, por lo que no era ampliamente conocido. VisiCalc cometió el error de incluir el 29 de febrero de 1900.
No es gran cosa, de verdad. Quién se remonta a ver si el 2 de febrero de 1900 fue jueves o miércoles (Excel dice que fue jueves, pero en realidad fue miércoles). ¿Y quién va a realizar un seguimiento de las cuentas por cobrar abiertas desde principios de 1900? Seamos realistas, si facturó a un proveedor el 15 de febrero de 1900 y aún no le ha pagado, es hora de cancelar la cuenta por cobrar.
Por compatibilidad, Mitch Kapor programó el mismo error en Lotus 1-2-3.
Steve Jobs, al no querer programar deliberadamente un error, hizo que el reloj de Macintosh comenzara el 1 de enero de 1904.
En Microsoft, Excel tenía que ser compatible con el entonces líder del mercado Lotus 1-2-3, y el inexistente 29 de febrero de 1900 se introdujo en Excel y permanece allí hasta el día de hoy.
Pero los arquitectos de Power Query no son gente de hojas de cálculo. No tienen estatuas de Bricklin y Frankston en su oficina. No conocen esta historia. Tomaron la decisión de que sus fechas serían el número de días transcurridos desde el 31 de diciembre de 1899. Esto hace que las personas de Power Query se sientan un poco superiores a las personas de Excel porque los días de la semana informados por Power Query durante 60 días a principios de 1900 son más correctos que en Excel.
Esto no es nada de lo que preocuparse. Nadie está tratando con fechas de esos 60 días.
Pero aquí hay algo mucho más común. Considere esta tabla de números conocidos.
Te voy a pedir que "hagas lo incorrecto" y sigas accidentalmente estos pasos:
- Seleccione la columna N
- Presione Ctrl + Shift + 3 para formatear la columna como una fecha
-
No note que hizo ninguna de estas cosas.
Vaya, los números son fechas inadvertidamente - Utilice datos, obtener y transformar, de tabla o rango.
- Cuando llegue a Power Query, observe las fechas en la columna de números. En el cuadro Pasos aplicados, elimine el paso Formato modificado.
Cuando los datos regresan a Excel, todo está apagado en 1. Los círculos ya no existen. Heinz tiene 56 variedades en lugar de 57.
Me doy cuenta de que este es nuestro último viernes en Excel Cuaresma. Me doy cuenta de que este es un ejemplo complicado y oscuro. ¿Cuáles son las probabilidades de que alguien formatee accidentalmente una columna de números en el rango del 1 al 60 como fechas antes de ir a Power Query? Parece bajo, pero ha sucedido.
Power Query es una característica asombrosa. Estoy seguro de que los arquitectos pensaron que nada podría salir mal si eran más inteligentes que aquellas personas en 1978 que cometieron un error. Pero si miles de millones de hojas de cálculo funcionan porque todos estamos de acuerdo en aceptar el error, estás haciendo un pequeño agujero en la estructura de Excel.
Todos los viernes, examino un error u otro comportamiento sospechoso en Excel.
Pensamiento del día de Excel
Les he pedido a mis amigos de Excel Master sus consejos sobre Excel. Pensamiento de hoy para reflexionar:
"Ctrl + Shift + U cambia la altura de la barra de fórmulas"
Bob Umlas
