Conteúdo
Calendário de produção, ou seja, uma lista de datas, onde todos os dias úteis e feriados oficiais são marcados de acordo – uma coisa absolutamente necessária para qualquer usuário do Microsoft Excel. Na prática, você não pode ficar sem ele:
- nos cálculos contábeis (salário, tempo de serviço, férias…)
- na logística – para a correta determinação dos prazos de entrega, levando em consideração finais de semana e feriados (lembra do clássico “vem depois dos feriados?”)
- em gerenciamento de projetos – para a estimativa correta de prazos, levando em consideração, novamente, dias úteis e não úteis
- qualquer uso de funções como DIA DE TRABALHO (DIA DE TRABALHO) or TRABALHADORES PUROS (DIAS DE REDE), porque eles exigem uma lista de feriados como argumento
- ao usar funções de inteligência de tempo (como TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR etc.) no Power Pivot e no Power BI
- … etc. etc. – muitos exemplos.
É mais fácil para quem trabalha em sistemas ERP corporativos como 1C ou SAP, pois o calendário de produção está embutido neles. Mas e os usuários do Excel?
Você pode, é claro, manter esse calendário manualmente. Mas então você terá que atualizá-lo pelo menos uma vez por ano (ou até com mais frequência, como no “alegre” 2020), inserindo cuidadosamente todos os fins de semana, transferências e dias não úteis inventados pelo nosso governo. E então repita este procedimento a cada ano seguinte. Tédio.
Que tal enlouquecer e fazer um calendário de fábrica “perpétuo” no Excel? Um que se atualiza, pega os dados da Internet e sempre gera uma lista atualizada de dias não úteis para uso posterior em algum cálculo? Tentador?
Para fazer isso, na verdade, não é nada difícil.
Fonte de dados
A questão principal é onde obter os dados? Em busca de uma fonte adequada, passei por várias opções:
- Os decretos originais são publicados no site do governo em formato PDF (aqui, um deles, por exemplo) e desaparecem imediatamente – informações úteis não podem ser extraídas deles.
- Uma opção tentadora, à primeira vista, parecia ser o “Portal de Dados Abertos da Federação”, onde existe um conjunto de dados correspondente, mas, examinando mais de perto, tudo acabou por ser triste. O site é terrivelmente inconveniente para importar para Excel, o suporte técnico não responde (auto-isolado?) e os próprios dados ficam desatualizados há muito tempo – o calendário de produção para 2020 foi atualizado pela última vez em novembro de 2019 (uma vergonha!) e , claro, não contém o nosso “coronavírus” e o fim de semana de ‘votação’ de 2020, por exemplo.
Desiludido com as fontes oficiais, comecei a cavar as não oficiais. Existem muitos deles na Internet, mas a maioria deles, novamente, são completamente inadequados para importar para o Excel e fornecem um calendário de produção na forma de belas imagens. Mas não cabe a gente pendurar na parede, né?
E no processo de pesquisa, uma coisa maravilhosa foi descoberta acidentalmente - o site http://xmlcalendar.ru/
Sem “frescuras” desnecessárias, um site simples, leve e rápido, afiado para uma tarefa – dar a todos um calendário de produção para o ano desejado em formato XML. Excelente!
Se, de repente, você não estiver sabendo, XML é um formato de texto com conteúdo marcado com
Por precaução, entrei em contato com os autores do site e eles confirmaram que o site existe há 7 anos, os dados nele são constantemente atualizados (eles até têm uma filial no github para isso) e não vão fechá-lo. E não me importo que você e eu carreguemos dados dele para qualquer um de nossos projetos e cálculos no Excel. É grátis. É bom saber que ainda existem pessoas assim! Respeito!
Resta carregar esses dados no Excel usando o suplemento Power Query (para versões do Excel 2010-2013, ele pode ser baixado gratuitamente no site da Microsoft e nas versões do Excel 2016 e mais recentes já está embutido por padrão ).
A lógica das ações será a seguinte:
- Solicitamos o download de dados do site para qualquer ano
- Transformando nosso pedido em uma função
- Aplicamos esta função à lista de todos os anos disponíveis, a partir de 2013 e até o ano atual – e obtemos um calendário de produção “perpétuo” com atualização automática. Voilá!
Etapa 1. Importar um calendário por um ano
Primeiro, carregue o calendário de produção para qualquer ano, por exemplo, para 2020. Para fazer isso, no Excel, vá para a guia Data (ou Consulta de energiase você o instalou como um complemento separado) e selecione Da internet (Da Web). Na janela que se abre, cole o link do ano correspondente, copiado do site:
Depois de clicar em OK uma janela de visualização aparece, na qual você precisa clicar no botão Converter dados (Transformar dados) or Para alterar os dados (Editar dados) e chegaremos à janela do editor de consultas do Power Query, onde continuaremos trabalhando com os dados:
Imediatamente você pode excluir com segurança no painel direito Solicitar Parâmetros (Configurações de consulta) passo tipo modificado (Tipo Alterado) Nós não precisamos dele.
A tabela na coluna de feriados contém códigos e descrições de dias não úteis – você pode ver seu conteúdo “caindo” duas vezes clicando na palavra verde mesa:
Para voltar, você terá que excluir no painel direito todas as etapas que apareceram de volta para fonte (Fonte).
A segunda tabela, que pode ser acessada de maneira semelhante, contém exatamente o que precisamos – as datas de todos os dias não úteis:
Resta processar esta placa, a saber:
1. Filtre apenas as datas de feriados (ou seja, as) pela segunda coluna Atributo:t
2. Exclua todas as colunas, exceto a primeira - clicando com o botão direito do mouse no cabeçalho da primeira coluna e selecionando o comando Excluir outras colunas (Remover outras colunas):
3. Divida a primeira coluna por ponto separadamente para mês e dia com comando Coluna Dividida - Por Delimitador aba Transformação (Transformar — Dividir coluna — Por delimitador):
4. E finalmente crie uma coluna calculada com datas normais. Para isso, na aba Adicionando uma coluna clique no botão Coluna personalizada (Adicionar coluna — coluna personalizada) e digite a seguinte fórmula na janela que aparece:
=#datado(2020, [#»Atributo:d.1″], [#»Atributo:d.2″])
Aqui, o operador #date tem três argumentos: ano, mês e dia, respectivamente. Após clicar em OK obtemos a coluna necessária com datas normais de fim de semana e excluímos as colunas restantes como na etapa 2
Etapa 2. Transformando a solicitação em uma função
Nossa próxima tarefa é converter a consulta criada para 2020 em uma função universal para qualquer ano (o número do ano será seu argumento). Para isso, fazemos o seguinte:
1. Expandindo (se ainda não expandido) o painel Inquéritos (Consultas) à esquerda na janela do Power Query:
2. Depois de converter a solicitação em uma função, a capacidade de ver as etapas que compõem a solicitação e editá-las com facilidade, infelizmente, desaparece. Portanto, faz sentido fazer uma cópia do nosso pedido e brincar já com ele, e deixar o original em reserva. Para fazer isso, clique com o botão direito do mouse no painel esquerdo em nossa solicitação de calendário e selecione o comando Duplicar.
Clicar com o botão direito do mouse novamente na cópia resultante do calendar(2) selecionará o comando Rebatizar (Renomear) e digite um novo nome - seja, por exemplo, fxAno:
3. Abrimos o código-fonte da consulta na linguagem interna do Power Query (é sucintamente chamado de “M”) usando o comando Editor Avançado aba Avaliações(Visualizar — Editor Avançado) e fazer pequenas alterações lá para transformar nosso pedido em uma função para qualquer ano.
Isso foi:
Depois:
Se você estiver interessado nos detalhes, então aqui:
- (ano como número)=> – declaramos que nossa função terá um argumento numérico – uma variável ano
- Colando a variável ano para o link da web na etapa fonte. Como o Power Query não permite colar números e texto, convertemos o número do ano em texto dinamicamente usando a função Número.ParaTexto
- Substituímos a variável ano por 2020 na penúltima etapa #”Adicionado objeto personalizado«, onde formamos a data a partir dos fragmentos.
Depois de clicar em Acabamento nosso pedido se torna uma função:
Etapa 3. Importar calendários para todos os anos
A última coisa que resta é fazer a última consulta principal, que fará upload de dados para todos os anos disponíveis e adicionará todas as datas de feriados recebidas em uma tabela. Por esta:
1. Clicamos no painel de consulta esquerdo em um espaço vazio cinza com o botão direito do mouse e selecionamos sequencialmente Nova solicitação – Outras fontes – Solicitação vazia (Nova consulta — de outras fontes — consulta em branco):
2. Precisamos gerar uma lista de todos os anos para os quais solicitaremos calendários, ou seja, 2013, 2014… 2020. Para isso, na barra de fórmulas da consulta vazia que aparece, digite o comando:
Estrutura:
={NúmeroA..NúmeroB}
… no Power Query gera uma lista de inteiros de A a B. Por exemplo, a expressão
={1..5}
… produziria uma lista de 1,2,3,4,5.
Bem, para não ficarmos presos rigidamente a 2020, usamos a função DateTime.LocalNow() – análogo da função Excel HOJE (HOJE) no Power Query – e extrair dele, por sua vez, o ano atual pela função Data.Ano.
3. O conjunto de anos resultante, embora pareça bastante adequado, não é uma tabela para Power Query, mas um objeto especial – Lista (Lista). Mas convertê-lo em tabela não é problema: basta clicar no botão Para mesa (Para mesa) no canto superior esquerdo:
4. Linha de chegada! Aplicando a função que criamos anteriormente fxAno à lista de anos resultante. Para isso, na aba Adicionando uma coluna aperte o botão Chamar função personalizada (Adicionar coluna — invocar função personalizada) e defina seu único argumento - a coluna Column1 ao longo dos anos:
Depois de clicar em OK nossa função fxAno a importação funcionará por sua vez para cada ano e obteremos uma coluna onde cada célula conterá uma tabela com as datas dos dias não úteis (o conteúdo da tabela fica claramente visível se você clicar no fundo da célula ao lado a palavra mesa):
Resta expandir o conteúdo das tabelas aninhadas clicando no ícone com setas duplas no cabeçalho da coluna Datas (marcação Use o nome da coluna original como prefixo pode ser removido):
… e depois de clicar em OK conseguimos o que queríamos – uma lista de todos os feriados de 2013 até o ano atual:
A primeira coluna, já desnecessária, pode ser excluída e, para a segunda, defina o tipo de dados dados (Encontro: Data) na lista suspensa no cabeçalho da coluna:
A consulta em si pode ser renomeada para algo mais significativo do que Pedido1 e, em seguida, faça o upload dos resultados para a planilha na forma de uma tabela dinâmica “inteligente” usando o comando fechar e baixar aba INÍCIO (Início - Fechar e carregar):
Você pode atualizar o calendário criado no futuro clicando com o botão direito do mouse na tabela ou consultando no painel direito através do comando Atualizar e salvar. Ou use o botão Atualize tudo aba Data (Data - Atualizar tudo) ou atalho de teclado Ctrl+outro+F5.
Isso é tudo.
Agora você nunca mais precisa perder tempo e raciocínio procurando e atualizando a lista de feriados – agora você tem um calendário de produção “perpétuo”. De qualquer forma, desde que os autores do site http://xmlcalendar.ru/ apoiem seus filhos, o que, espero, será por muito, muito tempo (graças a eles novamente!).
- Importar taxa de bitcoin para excel da internet via Power Query
- Encontrando o próximo dia útil usando a função WORKDAY
- Encontrando a interseção de intervalos de datas