Ian em Nashville obtém dados todos os dias de um download do sistema. A coluna de data contém Data + Hora. Isso faz com que a tabela dinâmica tenha várias linhas por dia, em vez de um resumo de uma célula por dia.
Este artigo mostra três maneiras de resolver o problema.
A figura abaixo mostra a coluna Data com Data mais hora.
Uma solução é corrigi-lo na tabela dinâmica.
- Arraste o campo Data para a área Linhas. Ele aparece com Data + Hora.
- Selecione qualquer célula que contenha uma data e hora.
- Na guia Analisar ferramentas de tabela dinâmica, escolha Campo de grupo.
- Na caixa de diálogo Agrupamento, selecione Dias. Desmarque meses. Quando você clicar em OK, a tabela dinâmica mostrará uma linha por dia.
Outra solução tediosa é adicionar uma nova coluna após o download. Conforme mostrado nesta figura, a fórmula na coluna J é =INT(D2).
Uma data + hora é um dia inteiro seguido por uma hora decimal. Usando a função INT para obter a parte inteira de data + hora, você isola a data.
Isso funcionará bem, mas agora você terá que adicionar a nova coluna sempre que fizer download dos dados.
Minha solução preferida é usar o Power Query. O Power Query funciona apenas nas edições Windows do Excel. Você não pode usar o Power Query no Android, IOS ou Mac.
O Power Query é integrado ao Excel 2016. Para o Excel 2010 e 2013, você pode baixar o Power Query da Microsoft.
Primeiro, converta os dados baixados em uma tabela usando Ctrl + T. Em seguida, na guia Dados, escolha Da tabela / intervalo.
Na grade do Power Query, escolha a coluna Data. Vá para a guia Transformar. Abra a lista suspensa Tipo de data e escolha Data. Se você receber uma mensagem sobre Substituir Etapa ou Nova Etapa, escolha Substituir Etapa.
Na guia Home do Power Query, escolha Close and Load.
Seu resultado aparecerá em uma nova folha. Crie a tabela dinâmica a partir dessa folha.
Na próxima vez que você obtiver novos dados, siga estas etapas:
- Cole os novos dados sobre o conjunto de dados original.
- Vá para a planilha com o resultado do Power Query. Amplie o painel Consulta para ver Atualizar. Clique em Atualizar.
- Atualize a tabela dinâmica.
Assistir vídeo
Transcrição de vídeo
Aprenda Excel com o Podcast, Episódio 2203: Truncate Date and Time to Just a Date.
Ei, bem-vindo de volta ao netcast, sou Bill Jelen. A pergunta de hoje de Ian em meu seminário Nashville Power Excel. Veja isto: Ian está obtendo um download de seu sistema todos os dias, e tem - na coluna Data - tem data e hora, e isso está bagunçando as coisas porque quando Ian cria uma tabela dinâmica e coloca datas junto do lado esquerdo, em vez de apenas obter uma linha por data, ele obterá uma linha para cada data e hora.
Agora, seria possível chegar aqui e dizer, Agrupar Campo, e agrupar isso para Dia, clique em OK. E, você sabe, são alguns cliques extras que não acho que temos que fazer. E, então, para este Podcast, os próximos Podcasts, vamos dar uma olhada no Power Query. Agora, ele está integrado ao Excel 2016 aqui, em Get & Transform Data. Se você tiver 2010 ou 2013 em execução no Windows, não em um Mac, pode baixar o Power Query gratuitamente no Excel. E o Power Query deseja trabalhar em uma tabela ou intervalo.
Vou escolher uma célula de dados, pressionar Ctrl + T para Tabela, perfeito. E então, na minha guia Power Query, Data, From Table / Range, vou escolher a coluna Date e Transform, e dizer que não quero que seja uma data e hora, mas apenas uma data. Escolherei Replace current e, em seguida, Home, Close & Load, e obteremos um novo formulário de planilha que tem os dados convertidos. Agora, a partir daqui, é claro, podemos resumir com um pivô e ficará perfeito.
A vantagem, eu acho, deste método, Power Query, é quando Ian obtém mais dados … Então, aqui eu tenho novos dados, vou pegar esses novos dados, copiar os dados e apenas voltar aqui para meus dados antigos e cole logo abaixo. E, observe que o marcador de fim de tabela agora está na Linha 474. Quando colo, o marcador de fim de tabela desce para a parte inferior dos novos dados. Tudo bem? E essa consulta é escrita com base na tabela. Tudo bem? Então, conforme a mesa cresce.
Agora, um incômodo aqui é, se você estiver usando o Power Query pela primeira vez, o Power Query começa com essa largura - apenas metade da largura - e você não pode ver o ícone Atualizar. Então, você tem que arrastá-lo para cá assim e obter aquele ótimo ícone Atualizar, e apenas clicar em Atualizar. No momento, temos 473 linhas carregadas e agora 563 linhas carregadas. Crie sua tabela dinâmica e a vida será ótima.
Agora, olhe, eu sei que teria sido possível adicionar uma nova coluna aqui, = INT (D2) - --da data-- copie, copie e cole valores especiais. Mas então você está prestes a fazer isso todos os dias. Tudo bem? Então, embora haja pelo menos três maneiras diferentes de resolvê-lo neste vídeo, para mim, o Power Query e, apenas, a capacidade de clicar em Atualizar, é o caminho a percorrer.
Power Query, falo sobre isso em meu novo livro, MrExcel LIVe, The 54 Greatest Excel Tips of All Time.
Conclusão do episódio de hoje: Ian em Nashville obtém dados de um sistema todos os dias, a coluna Date irritantemente tem data e hora - bagunça suas tabelas dinâmicas, então ele tem que … Bem, três coisas possíveis: Um, use a função INT, copiar, colar valores; em segundo lugar, deixe-o como Data e Hora e agrupe a tabela dinâmica por Data; ou, uma solução melhor, Power Query, transforma o conjunto de dados baixados em uma tabela com Ctrl + T e, em seguida, na guia Dados, escolha Da Tabela, selecione a coluna Data e hora, vá para a guia Transformar no Power Query, converta para um Date, Close & Load, construa a tabela dinâmica a partir disso. Na próxima vez que você obtiver dados, cole na tabela original, vá para o painel Consulta, clique em Atualizar e pronto.
Ei, para baixar a pasta de trabalho do vídeo de hoje, visite o URL na descrição do YouTube.
Quero agradecer a Ian por aparecer no meu seminário em Nashville, quero agradecer a você por passar por aqui. Vejo você na próxima vez para outro netcast de.
Baixar arquivo Excel
Para baixar o arquivo excel: date-time-to-date.xlsx
Para melhorar ainda mais o processo do Power Query:
- Certifique-se de que a pasta de trabalho de download seja sempre salva na mesma pasta com o mesmo nome.
- Comece com uma pasta de trabalho em branco. Salvar como com um nome como DownloadedFileTransformed.xlsx
- Da pasta de trabalho em branco, Data, From File, From Workbook. Aponte para o arquivo da Etapa 1.
- Escolha Limpar dados. Transforme a data / hora em uma data. Fechar e carregar.
- Clique com o botão direito do mouse no Painel de consulta e escolha Propriedades. Escolha Atualizar dados ao abrir o arquivo.
- Cada dia, vá trabalhar. Pegue seu café. Certifique-se de que um novo arquivo para o nº 1 esteja na pasta.
- Abra sua pasta de trabalho DownloadFileTransformed.xlsx. Ele terá os novos dados e as datas estarão corretas.
Excel Pensamento do Dia
Pedi conselhos aos meus amigos do Excel Master sobre o Excel. O pensamento de hoje para ponderar:
"A validação é sua amiga."
Duane Aubin
