Conteúdo
Muitos processos de negócios (e até negócios inteiros) nesta vida envolvem o cumprimento de pedidos por um número limitado de executores em um determinado prazo. O planejamento nesses casos ocorre, como dizem, “do calendário” e muitas vezes há a necessidade de transferir os eventos nele previstos (pedidos, reuniões, entregas) para o Microsoft Excel – para posterior análise por fórmulas, tabelas dinâmicas, gráficos, etc.
Claro, eu gostaria de implementar essa transferência não por cópia estúpida (o que não é difícil), mas com atualização automática de dados para que no futuro todas as alterações feitas no calendário e novos pedidos sejam exibidos em tempo real Excel. Você pode implementar essa importação em questão de minutos usando o suplemento Power Query integrado ao Microsoft Excel, a partir da versão 2016 (para Excel 2010-2013, ele pode ser baixado do site da Microsoft e instalado separadamente do link) .
Suponha que usemos o Google Agenda gratuito para planejamento, no qual eu, por conveniência, criei um calendário separado (o botão com um sinal de mais no canto inferior direito ao lado de Outros calendários) com o título Atividades:. Aqui inserimos todos os pedidos que precisam ser concluídos e entregues aos clientes em seus endereços:
Ao clicar duas vezes em qualquer pedido, você pode visualizar ou editar seus detalhes:
Observe que:
- O nome do evento é Gerentequem cumpre esta ordem (Elena) e Número do pedido
- Indicado endereço Entrega
- A nota contém (em linhas separadas, mas em qualquer pedido) os parâmetros do pedido: tipo de pagamento, valor, nome do cliente, etc. no formato Parâmetro=Valor.
Para maior clareza, as ordens de cada gerente são destacadas em sua própria cor, embora isso não seja necessário.
Etapa 1. Obtenha um link para o Google Agenda
Primeiro, precisamos obter um link da web para o nosso calendário de pedidos. Para fazer isso, clique no botão com três pontos Trabalho de opções de calendário ao lado do nome do calendário e selecione o comando Configurações e compartilhamento:
Na janela que se abre, você pode, se desejar, tornar o calendário público ou abrir o acesso a ele para usuários individuais. Também precisamos de um link para acesso privado ao calendário no formato iCal:
Etapa 2. Carregar dados do calendário no Power Query
Agora abra o Excel e na guia Data (se você tiver o Excel 2010-2013, na guia Consulta de energia) escolha um comando Da internet (Dados — Da Internet). Em seguida, cole o caminho copiado no calendário e clique em OK.
O iCal Power Query não reconhece o formato, mas é fácil de ajudar. Essencialmente, o iCal é um arquivo de texto simples com dois pontos como delimitador, e dentro dele se parece com isso:
Então você pode apenas clicar com o botão direito do mouse no ícone do arquivo baixado e selecionar o formato que mais se aproxima do significado CSV – e nossos dados sobre todos os pedidos serão carregados no editor de consultas do Power Query e divididos em duas colunas por dois pontos:
Se você olhar de perto, você pode ver claramente que:
- As informações sobre cada evento (ordem) são agrupadas em um bloco começando com a palavra BEGIN e terminando com END.
- As datas de início e término são armazenadas em strings rotuladas DTSTART e DTEND.
- O endereço de entrega é LOCATION.
- Nota de pedido – campo DESCRIÇÃO.
- Nome do evento (nome do gerente e número do pedido) — campo SUMÁRIO.
Resta extrair essas informações úteis e transformá-las em uma tabela conveniente.
Etapa 3. Converter para visualização normal
Para fazer isso, execute a seguinte cadeia de ações:
- Vamos excluir as 7 principais linhas que não precisamos antes do primeiro comando BEGIN Página inicial — Excluir linhas — Excluir linhas superiores (Início — Remover linhas — Remover as linhas superiores).
- Filtrar por coluna Column1 linhas contendo os campos que precisamos: DTSTART, DTEND, DESCRIPTION, LOCATION e SUMMARY.
- Na guia Avançado Adicionando uma coluna escolher Coluna de índice (Adicionar coluna — coluna de índice)para adicionar uma coluna de número de linha aos nossos dados.
- Bem ali na aba. Adicionando uma coluna escolha um time Coluna condicional (Adicionar coluna — coluna condicional) e no início de cada bloco (ordem) exibimos o valor do índice:
- Preencha as células vazias na coluna resultante Bloquearclicando com o botão direito do mouse em seu título e selecionando o comando Preencher (Preencher).
- Remover coluna desnecessária Índice.
- Selecione uma coluna Column1 e execute uma convolução dos dados da coluna Column2 usando o comando Transformar - Coluna Pivot (Transformar — coluna dinâmica). Certifique-se de selecionar nas opções Não agregue (Não agregue)para que nenhuma função matemática seja aplicada aos dados:
- Na tabela bidimensional (cruzada) resultante, limpe as barras invertidas na coluna de endereço (clique com o botão direito do mouse no cabeçalho da coluna – Substituindo valores) e remova a coluna desnecessária Bloquear.
- Para transformar o conteúdo das colunas DTSTART и DEND em uma data-hora completa, destacando-os, selecione na guia Transformar – Data – Executar Análise (Transformar — Data — Analisar). Em seguida, corrigimos o código na barra de fórmulas substituindo a função Data. De on DataHora.Depara não perder valores de tempo:
- Então, clicando com o botão direito do mouse no cabeçalho, dividimos a coluna DESCRIÇÃO com parâmetros de ordem por separador – símbolo n, mas ao mesmo tempo, nos parâmetros, selecionaremos a divisão em linhas e não em colunas:
- Mais uma vez, dividimos a coluna resultante em duas separadas – o parâmetro e o valor, mas pelo sinal de igual.
- Selecionando uma coluna DESCRIÇÃO.1 execute a convolução, como fizemos anteriormente, com o comando Transformar - Coluna Pivot (Transformar — coluna dinâmica). A coluna de valor neste caso será a coluna com valores de parâmetro − DESCRIÇÃO.2 Certifique-se de selecionar uma função nos parâmetros Não agregue (Não agregue):
- Resta definir os formatos para todas as colunas e renomeá-las conforme desejado. E você pode carregar os resultados de volta para o Excel com o comando Home — Fechar e carregar — Fechar e carregar em… (Início — Fechar e carregar — Fechar e carregar para…)
E aqui está nossa lista de pedidos carregados no Excel do Google Agenda:
No futuro, ao alterar ou adicionar novos pedidos ao calendário, bastará atualizar nossa solicitação com o comando Dados - Atualizar tudo (Dados — Atualizar tudo).
- Calendário de fábrica no Excel atualizado da internet via Power Query
- Transformando uma coluna em uma tabela
- Criar um banco de dados no Excel