Contenido
Calendario de producción, es decir, una lista de fechas, en la que se marcan todos los días laborables y festivos oficiales, algo absolutamente necesario para cualquier usuario de Microsoft Excel. En la práctica, no puedes prescindir de él:
- en los cálculos contables (salario, antigüedad, vacaciones…)
- en logística – para la correcta determinación de los plazos de entrega, teniendo en cuenta fines de semana y festivos (recuerdan el clásico “¿vamos después de las vacaciones?”)
- en gestión de proyectos – para la correcta estimación de los plazos, teniendo en cuenta, nuevamente, los días hábiles-inhábiles
- cualquier uso de funciones como WORKDAY (DÍA DE TRABAJO) or TRABAJADORES PUROS (DÍAS LABORABLES), porque requieren una lista de días festivos como argumento
- al usar funciones de inteligencia de tiempo (como TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, etc.) en Power Pivot y Power BI
- … etc. etc. – muchos ejemplos.
Es más fácil para quienes trabajan en sistemas ERP corporativos como 1C o SAP, ya que el calendario de producción está integrado en ellos. Pero, ¿qué pasa con los usuarios de Excel?
Por supuesto, puede mantener dicho calendario manualmente. Pero luego tendrá que actualizarlo al menos una vez al año (o incluso más a menudo, como en el "jolly" 2020), ingresando cuidadosamente todos los fines de semana, transferencias y días no laborables inventados por nuestro gobierno. Y luego repita este procedimiento cada próximo año. Aburrimiento.
¿Qué tal volverse un poco loco y hacer un calendario de fábrica “perpetuo” en Excel? ¿Uno que se actualice, tome datos de Internet y genere siempre una lista actualizada de días no laborables para su uso posterior en cualquier cálculo? ¿Tentador?
Hacer esto, de hecho, no es nada difícil.
Fuente de datos
La pregunta principal es ¿dónde obtener los datos? En busca de una fuente adecuada, pasé por varias opciones:
- Los decretos originales se publican en el sitio web del gobierno en formato PDF (aquí, uno de ellos, por ejemplo) y desaparecen inmediatamente, no se puede sacar información útil de ellos.
- Una opción tentadora, a primera vista, parecía ser el "Portal de datos abiertos de la Federación", donde hay un conjunto de datos correspondiente, pero, tras un examen más detenido, todo resultó triste. El sitio es terriblemente incómodo para importar a Excel, el soporte técnico no responde (¿se autoaisla?) y los datos en sí están desactualizados allí durante mucho tiempo: el calendario de producción para 2020 se actualizó por última vez en noviembre de 2019 (¡desgracia!) y , por supuesto, no contiene nuestro "coronavirus" ni el fin de semana de "votación" de 2020, por ejemplo.
Desilusionado con las fuentes oficiales, comencé a investigar las no oficiales. Hay muchos de ellos en Internet, pero la mayoría de ellos, nuevamente, son completamente inadecuados para importar a Excel y brindan un calendario de producción en forma de bellas imágenes. Pero no nos corresponde a nosotros colgarlo en la pared, ¿verdad?
Y en el proceso de búsqueda, accidentalmente se descubrió algo maravilloso: el sitio http://xmlcalendar.ru/
Sin "adornos" innecesarios, un sitio simple, ligero y rápido, diseñado para una sola tarea: brindar a todos un calendario de producción para el año deseado en formato XML. ¡Excelente!
Si, de repente, no está al tanto, entonces XML es un formato de texto con contenido marcado con especial
Por si acaso, contacté a los autores del sitio y me confirmaron que el sitio existe desde hace 7 años, los datos se actualizan constantemente (incluso tienen una sucursal en github para esto) y no lo van a cerrar. Y no me importa en absoluto que tú y yo carguemos datos de él para cualquiera de nuestros proyectos y cálculos en Excel. Está libre. ¡Es bueno saber que todavía hay gente así! ¡Respeto!
Queda por cargar estos datos en Excel utilizando el complemento Power Query (para las versiones de Excel 2010-2013 se puede descargar de forma gratuita desde el sitio web de Microsoft, y en las versiones de Excel 2016 y posteriores ya está integrado de forma predeterminada ).
La lógica de actuación será la siguiente:
- Hacemos una solicitud para descargar datos del sitio para cualquier año.
- Convirtiendo nuestra petición en una función
- Aplicamos esta función a la lista de todos los años disponibles, a partir de 2013 y hasta el año en curso, y obtenemos un calendario de producción "perpetuo" con actualización automática. ¡Voila!
Paso 1. Importa un calendario para un año
Primero, cargue el calendario de producción para cualquier año, por ejemplo, para 2020. Para hacer esto, en Excel, vaya a la pestaña Datos (o Power Querysi lo instaló como un complemento separado) y seleccione Desde Internet (De la web). En la ventana que se abre, pegue el enlace al año correspondiente, copiado del sitio:
Después de hacer clic en OK aparece una ventana de vista previa, en la que debe hacer clic en el botón Convertir datos (Transformar datos) or Para cambiar los datos (Editar datos) y llegaremos a la ventana del editor de consultas de Power Query, donde seguiremos trabajando con los datos:
Inmediatamente puede eliminar de forma segura en el panel derecho Parámetros de solicitud (Configuración de consulta) paso tipo modificado (Tipo cambiado) No lo necesitamos.
La tabla en la columna de días festivos contiene códigos y descripciones de días no laborables; puede ver su contenido al "caer" dos veces haciendo clic en la palabra verde Mesa:
Para volver, tendrás que borrar en el panel derecho todos los pasos que te han aparecido Fuente (Fuente).
La segunda tabla, a la que se puede acceder de forma similar, contiene exactamente lo que necesitamos: las fechas de todos los días no laborables:
Queda por procesar esta placa, a saber:
1. Filtre solo las fechas de vacaciones (es decir, las) por la segunda columna Atributo:t
2. Elimine todas las columnas excepto la primera: haga clic con el botón derecho en el encabezado de la primera columna y seleccione el comando Eliminar otras columnas (Eliminar otras columnas):
3. Divida la primera columna por punto por separado para el mes y el día con el comando Columna dividida: por delimitador de la pestaña. (Transformar — Dividir columna — Por delimitador):
4. Y finalmente cree una columna calculada con fechas normales. Para ello, en la pestaña Agregar una columna haga clic en el botón columna personalizada (Agregar columna — Columna personalizada) e ingrese la siguiente fórmula en la ventana que aparece:
=#con fecha de(2020, [#»Atributo:d.1″], [#»Atributo:d.2″])
Aquí, el operador #fecha tiene tres argumentos: año, mes y día, respectivamente. Después de hacer clic en OK obtenemos la columna requerida con fechas de fin de semana normales y eliminamos las columnas restantes como en el paso 2
Paso 2. Convertir la solicitud en una función
Nuestra próxima tarea es convertir la consulta creada para 2020 en una función universal para cualquier año (el número de año será su argumento). Para ello, hacemos lo siguiente:
1. Expandiendo (si no está ya expandido) el panel Consultas (Consultas) a la izquierda en la ventana de Power Query:
2. Después de convertir la solicitud en una función, lamentablemente desaparece la capacidad de ver los pasos que componen la solicitud y editarlos fácilmente. Por lo tanto, tiene sentido hacer una copia de nuestra solicitud y retozar ya con ella, y dejar el original en reserva. Para hacer esto, haga clic derecho en el panel izquierdo en nuestra solicitud de calendario y seleccione el comando Duplicar.
Al hacer clic con el botón derecho nuevamente en la copia resultante del calendario (2), se seleccionará el comando rebautizar (Rebautizar) e ingrese un nuevo nombre, que sea, por ejemplo, añofx:
3. Abrimos el código fuente de la consulta en el lenguaje interno de Power Query (se llama sucintamente “M”) usando el comando Editor avanzado de la pestaña. Revisar(Ver — Editor avanzado) y hacer pequeños cambios allí para convertir nuestra solicitud en una función para cualquier año.
Era:
Después:
Si estás interesado en los detalles, entonces aquí:
- (año como número) => – declaramos que nuestra función tendrá un argumento numérico – una variable año
- Pegar la variable año al enlace web en el paso Fuente. Dado que Power Query no le permite pegar números y texto, convertimos el número del año en texto sobre la marcha usando la función Número.AlTexto
- Sustituimos la variable año por 2020 en el penúltimo paso #”Objeto personalizado agregado«, donde formamos la fecha a partir de los fragmentos.
Después de hacer clic en Acabado nuestra solicitud se convierte en una función:
Paso 3. Importar calendarios para todos los años
Lo último que queda es hacer la última consulta principal, que cargará datos para todos los años disponibles y agregará todas las fechas de vacaciones recibidas en una tabla. Para esto:
1. Hacemos clic en el panel de consulta izquierdo en un espacio gris vacío con el botón derecho del mouse y seleccionamos secuencialmente Solicitud nueva – Otras fuentes – Solicitud vacía (Nueva Consulta — De otras fuentes — Consulta en blanco):
2. Necesitamos generar una lista de todos los años para los que solicitaremos calendarios, es decir, 2013, 2014… 2020. Para ello, en la barra de fórmulas de la consulta vacía que aparece, ingrese el comando:
Estructura:
={NúmeroA..NúmeroB}
… en Power Query genera una lista de enteros de A a B. Por ejemplo, la expresión
={1..5}
… produciría una lista de 1,2,3,4,5.
Bueno, para no estar atados rígidamente a 2020, usamos la función FechaHora.LocalNow() – análogo de la función de Excel HOY (HOY) en Power Query – y extraer de él, a su vez, el año actual por la función Fecha.Año.
3. El conjunto de años resultante, aunque parece bastante adecuado, no es una tabla para Power Query, sino un objeto especial: lista (Lista). Pero convertirlo en una tabla no es un problema: simplemente haga clic en el botón A la mesa (A la mesa) en la esquina superior izquierda:
4. ¡Línea de meta! Aplicando la función que creamos anteriormente añofx a la lista de años resultante. Para ello, en la pestaña Agregar una columna presiona el botón Llamar a la función personalizada (Agregar columna — Invocar función personalizada) y establecer su único argumento: la columna Column1 A través de los años:
Después de hacer clic en OK nuestra función añofx la importación funcionará a su vez para cada año y obtendremos una columna donde cada celda contendrá una tabla con las fechas de los días no laborables (el contenido de la tabla es claramente visible si hace clic en el fondo de la celda al lado de la palabra Mesa):
Queda por expandir el contenido de las tablas anidadas haciendo clic en el ícono con flechas dobles en el encabezado de la columna Fechas (garrapata Utilice el nombre de la columna original como prefijo se puede quitar):
… y después de hacer clic en OK Obtuvimos lo que queríamos: una lista de todos los días festivos desde 2013 hasta el año en curso:
La primera columna, ya innecesaria, se puede eliminar y, para la segunda, establecer el tipo de datos datos (Fecha) en la lista desplegable en el encabezado de la columna:
La consulta en sí se puede renombrar algo más significativo que Solicitud1 y luego cargue los resultados en la hoja en forma de una tabla dinámica "inteligente" usando el comando cerrar y descargar de la pestaña. Inicio (Inicio — Cerrar y cargar):
Puede actualizar el calendario creado en el futuro haciendo clic derecho en la tabla o consulta en el panel derecho a través del comando Actualizar y guardar. O usa el botón Refrescar todo de la pestaña. Datos (Fecha — Actualizar todo) o atajo de teclado Ctrl+otro+F5.
Eso es todo.
Ahora nunca más tendrá que perder el tiempo y el pensamiento buscando y actualizando la lista de días festivos: ahora tiene un calendario de producción "perpetuo". En cualquier caso, siempre y cuando los autores del sitio http://xmlcalendar.ru/ apoyen a su descendencia, lo que, espero, será por mucho, mucho tiempo (¡gracias a ellos nuevamente!).
- Importe la tasa de bitcoin para sobresalir desde Internet a través de Power Query
- Encontrar el siguiente día hábil usando la función WORKDAY
- Encontrar la intersección de intervalos de fechas