Contidos
Calendario de produción, é dicir, unha lista de datas, onde todos os días laborables e festivos oficiais están marcados en consecuencia, algo absolutamente necesario para calquera usuario de Microsoft Excel. Na práctica, non pode prescindir del:
- en cálculos contables (salario, antigüidade, vacacións...)
- en loxística: para a correcta determinación dos prazos de entrega, tendo en conta os fins de semana e os festivos (lembras do clásico "vén despois das vacacións?")
- na xestión de proxectos – para a correcta estimación dos prazos, tendo en conta, de novo, os días laborables e non laborables
- calquera uso de funcións como XORNADA DE TRABALLO (DÍA DE TRABALLO) or PUROS TRABALLADORES (DÍAS DE REDE), porque requiren unha lista de vacacións como argumento
- ao usar funcións Time Intelligence (como TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, etc.) en Power Pivot e Power BI
- … etc. etc. – moitos exemplos.
É máis fácil para aqueles que traballan en sistemas ERP corporativos como 1C ou SAP, xa que o calendario de produción está integrado neles. Pero que pasa cos usuarios de Excel?
Por suposto, podes manter un calendario deste tipo manualmente. Pero entón terás que actualizalo polo menos unha vez ao ano (ou aínda máis a miúdo, como no "jolly" 2020), introducindo coidadosamente todos os fins de semana, traslados e días non laborables inventados polo noso goberno. E despois repita este procedemento cada ano. Aburrimento.
Que tal volverse un pouco tolo e facer un calendario de fábrica "perpetuo" en Excel? Un que se actualiza por si mesmo, toma datos de Internet e xera sempre unha lista actualizada de días inhábiles para o seu posterior uso en calquera cálculo? Tentador?
Para iso, de feito, non é nada difícil.
Fonte de datos
A pregunta principal é onde obter os datos? Na procura dunha fonte adecuada, pasei por varias opcións:
- Os decretos orixinais publícanse na páxina web do goberno en formato PDF (aquí, un deles, por exemplo) e desaparecen inmediatamente; non se pode sacar información útil deles.
- Unha opción tentadora, a primeira vista, pareceu ser o “Portal de datos abertos da Federación”, onde hai un conxunto de datos correspondente, pero, tras un exame máis detallado, todo resultou triste. O sitio é terriblemente inconveniente para importar a Excel, o soporte técnico non responde (¿auto-illado?) , por suposto, non contén o noso "coronavirus" e a fin de semana de "votación" de 2020, por exemplo.
Desilusionado coas fontes oficiais, comecei a cavar outras non oficiais. Hai moitos deles en Internet, pero a maioría deles, de novo, son completamente inadecuados para importar a Excel e ofrecen un calendario de produción en forma de fermosas imaxes. Pero non nos corresponde colgalo na parede, non?
E no proceso de busca, descubriuse accidentalmente unha cousa marabillosa: o sitio http://xmlcalendar.ru/
Sen adornos innecesarios, un sitio sinxelo, lixeiro e rápido, perfeccionado para unha tarefa: ofrecer a todos un calendario de produción para o ano desexado en formato XML. Excelente!
Se, de súpeto, non estás ao tanto, entón XML é un formato de texto con contido marcado con especial
Por se acaso, púxenme en contacto cos autores do sitio e confirmaron que o sitio existe desde hai 7 anos, os datos sobre el están constantemente actualizados (incluso teñen unha rama en github para iso) e non o van pechalo. E non me importa para nada que ti e eu carguemos datos del para calquera dos nosos proxectos e cálculos en Excel. É gratuíto. Dá gusto saber que aínda hai xente así! Respecto!
Queda por cargar estes datos en Excel mediante o complemento Power Query (para as versións de Excel 2010-2013 pódese descargar gratuitamente desde o sitio web de Microsoft, e nas versións de Excel 2016 e posteriores xa está integrado por defecto). ).
A lóxica de actuación será a seguinte:
- Facemos unha solicitude para descargar datos do sitio durante un ano calquera
- Convertendo a nosa petición nunha función
- Aplicamos esta función á lista de todos os anos dispoñibles, a partir de 2013 e ata o ano en curso, e obtemos un calendario de produción "perpetuo" con actualización automática. Voila!
Paso 1. Importa un calendario durante un ano
En primeiro lugar, cargue o calendario de produción para calquera ano, por exemplo, para 2020. Para facelo, en Excel, vai á pestana Data (Ou Consulta de enerxíase o instalou como complemento separado) e seleccione Desde Internet (Da web). Na xanela que se abre, pega a ligazón ao ano correspondente, copiada do sitio:
Despois de premer OK aparece unha xanela de vista previa, na que cómpre facer clic no botón Converter datos (Transformar datos) or Para cambiar os datos (Editar datos) e chegaremos á xanela do editor de consultas de Power Query, onde seguiremos traballando cos datos:
Inmediatamente podes eliminar con seguridade no panel dereito Parámetros de solicitude (Configuración de consulta) paso tipo modificado (Tipo modificado) Non o necesitamos.
A táboa da columna de vacacións contén códigos e descricións dos días inhábiles; podes ver o seu contido "caiéndoo" dúas veces facendo clic na palabra verde Táboa:
Para volver, terás que eliminar no panel dereito todos os pasos aos que apareceron fonte (Fonte).
A segunda táboa, á que se pode acceder dun xeito similar, contén exactamente o que necesitamos: as datas de todos os días non laborables:
Queda por procesar esta placa, a saber:
1. Filtra só as datas de vacacións (é dicir, as) pola segunda columna Atributo: t
2. Elimina todas as columnas, excepto a primeira, facendo clic co botón dereito sobre o título da primeira columna e seleccionando o comando Elimina outras columnas (Eliminar outras columnas):
3. Divide a primeira columna por punto por separado para mes e día co comando Dividir columna: por delimitador aba Transformación (Transformar — Dividir columna — Por delimitador):
4. E, finalmente, crea unha columna calculada con datas normais. Para iso, na pestana Engadindo unha columna prema no botón Columna personalizada (Engadir columna — Columna personalizada) e introduza a seguinte fórmula na xanela que aparece:
=#fechada(2020, [#»Atributo:d.1″], [#»Atributo:d.2″])
Aquí, o operador #date ten tres argumentos: ano, mes e día, respectivamente. Despois de facer clic en OK obtemos a columna necesaria con datas normais de fin de semana e eliminamos as columnas restantes como no paso 2
Paso 2. Converter a solicitude nunha función
A nosa seguinte tarefa é converter a consulta creada para 2020 nunha función universal para calquera ano (o número do ano será o seu argumento). Para iso, facemos o seguinte:
1. Ampliando (se aínda non se expandiu) o panel Información (Consultas) á esquerda na xanela de Power Query:
2. Despois de converter a solicitude nunha función, a posibilidade de ver os pasos que compoñen a solicitude e editalos facilmente, desafortunadamente, desaparece. Polo tanto, ten sentido facer unha copia da nosa solicitude e xogar xa con ela, e deixar o orixinal en reserva. Para iso, fai clic co botón dereito do rato no panel esquerdo da nosa solicitude de calendario e selecciona o comando Duplicar.
Facendo clic co botón dereito de novo na copia resultante do calendario(2), seleccionarase o comando Rebatizar (Renomear) e introduza un nome novo, que sexa, por exemplo, fxAno:
3. Abrimos o código fonte da consulta na linguaxe interna de Power Query (chámase succintamente "M") usando o comando Editor avanzado aba revisión(Ver — Editor avanzado) e facer alí pequenos cambios para converter a nosa solicitude nunha función para calquera ano.
Foi:
Despois:
Se estás interesado nos detalles, aquí:
- (ano como número) => – declaramos que a nosa función terá un argumento numérico: unha variable ano
- Pegando a variable ano á ligazón web no paso fonte. Dado que Power Query non che permite pegar números e texto, convertemos o número do ano en texto sobre a marcha usando a función Número.AoTexto
- Substituímos a variable de ano por 2020 no penúltimo paso #"Engadiuse un obxecto personalizado«, onde formamos a data a partir dos fragmentos.
Despois de premer Rematar a nosa solicitude convértese nunha función:
Paso 3. Importa calendarios para todos os anos
O último que queda é facer a última consulta principal, que cargará os datos de todos os anos dispoñibles e engadirá todas as datas de vacacións recibidas nunha soa táboa. Para isto:
1. Facemos clic no panel de consulta esquerdo nun espazo baleiro gris co botón dereito do rato e seleccionamos secuencialmente Nova solicitude – Outras fontes – Solicitude baleira (Nova consulta — Desde outras fontes — Consulta en branco):
2. Necesitamos xerar unha lista de todos os anos para os que solicitaremos calendarios, é dicir, 2013, 2014 … 2020. Para iso, na barra de fórmulas da consulta baleira que aparece, introduza o comando:
Estrutura:
={NúmeroA..NúmeroB}
… en Power Query xera unha lista de números enteiros de A a B. Por exemplo, a expresión
={1..5}
... produciría unha lista de 1,2,3,4,5.
Ben, para non estar rixidamente ligados a 2020, usamos a función DateTime.LocalNow() – análogo da función Excel HOXE (HOXE) en Power Query – e extrae dela, á súa vez, o ano actual pola función Data.Ano.
3. O conxunto de anos resultante, aínda que parece bastante adecuado, non é unha táboa para Power Query, senón un obxecto especial: lista (Lista). Pero convertelo nunha táboa non é un problema: basta con facer clic no botón Á mesa (Á táboa) na esquina superior esquerda:
4. Liña de meta! Aplicando a función que creamos anteriormente fxAno á lista de anos resultante. Para iso, na pestana Engadindo unha columna prema o botón Chamar a función personalizada (Engadir columna — Invocar función personalizada) e establece o seu único argumento: a columna Column1 ao longo dos anos:
Despois de premer OK nosa función fxAno a importación funcionará á súa vez para cada ano e obteremos unha columna onde cada cela conterá unha táboa coas datas dos días non laborables (o contido da táboa é claramente visible se fai clic no fondo da cela ao lado de a palabra Táboa):
Queda por ampliar o contido das táboas aniñadas facendo clic na icona con dobres frechas na cabeceira da columna Datas (marcar Use o nome orixinal da columna como prefixo pódese eliminar):
… e despois de premer OK conseguimos o que queriamos: unha lista de todas as vacacións desde 2013 ata o ano actual:
A primeira columna, xa innecesaria, pódese eliminar e, para a segunda, establecer o tipo de datos data (Data) na lista despregable do título da columna:
A propia consulta pódese renomear algo máis significativo que Solicitude 1 e despois carga os resultados na folla en forma de táboa dinámica "intelixente" usando o comando pechar e descargar aba casa (Inicio — Pechar e cargar):
Podes actualizar o calendario creado no futuro facendo clic co botón dereito na táboa ou consultando no panel dereito mediante o comando Actualizar e gardar. Ou use o botón Actualiza todo aba Data (Data — Actualizar todo) ou atallo de teclado Ctrl+alt+F5.
Iso é todo.
Agora nunca máis necesitas perder o tempo e pensar en combustible buscando e actualizando a lista de vacacións; agora tes un calendario de produción "perpetuo". En calquera caso, sempre que os autores do sitio http://xmlcalendar.ru/ apoien aos seus fillos, o que, espero, será por moito, moito tempo (¡Grazas a eles de novo!).
- Importe a taxa de bitcoins para excel de Internet a través de Power Query
- Busca o seguinte día laborable mediante a función WORKDAY
- Busca a intersección de intervalos de datas