Células mescladas são o problema hoje, mas essas são células mescladas verticais. Dê uma olhada na figura abaixo. Se houver 7 linhas para a região Centro-Oeste, a palavra Centro-Oeste aparecerá apenas na primeira linha. Alguém está então mesclando as seis células em branco restantes com a célula do meio-oeste para criar uma célula mesclada de 7 células de altura.
Esta é uma forma horrível de enviar dados. Se alguém é proficiente em Excel, pode querer classificar os dados, adicionar subtotais e usar tabelas dinâmicas. As células mescladas evitam isso.
Para piorar a situação, você recebe um novo arquivo da sede todos os dias estruturado assim. A HQ está enviando arquivos semelhantes para 500 filiais. Sendo apenas um pequeno posto avançado, você não tem muita chance de convencer o HQ de que esta é uma maneira horrível de enviar dados.
O artigo de hoje é sobre como resolver rapidamente esse problema com o Power Query. A solução Power Query será mais fácil nos dias 2 a 9999 do que as seguintes etapas no Excel:
Os gurus do Excel podem sugerir estas etapas todos os dias:
- Selecione todas as células escolhendo o retângulo acima e à esquerda de A1.
- Clique no iniciador de diálogo no canto inferior direito do grupo Alinhamento da guia Início.
- Clique na caixa Mesclar células duas vezes para desmarcá-la.
- Clique em OK para fechar a janela de Formatar Células
- Selecione do final da coluna A de volta para A1.
- Home, Find & Select, Go To Special, Blanks, OK
- Tipo = UpArrow. Pressione Ctrl + Enter
- Selecione do final da coluna A de volta para A1.
- Ctrl + C para copiar. Clique com o botão direito e cole os valores
Mas existe uma maneira muito mais fácil. As novas ferramentas do Power Query são integradas às versões do Windows do Office 365 e Excel 2016. Se você tiver uma versão do Windows do Excel 2010 ou Excel 2013, pode baixar o Power Query gratuitamente da Microsoft.
Aqui está a estratégia com o Power Query:
- Faça um plano para salvar a pasta de trabalho de cada dia do HQ na mesma pasta com o mesmo nome.
- Abra uma nova pasta de trabalho em branco que conterá os dados fixos do HQ.
-
Na pasta de trabalho em branco, escolha Dados, Obter dados, Do arquivo, Da pasta de trabalho.
Inicie o processo do Power Query - Navegue até a pasta e o arquivo da etapa 1.
-
Quando a janela Power Query abrir, observe que as células mescladas sumiram. Você tem Midwest na linha 1 e, em seguida, seis células que contêm "null". Selecione esta coluna clicando no título Região.
O Power Query remove automaticamente as células mescladas -
No Power Query, selecione a guia Transformar. Escolha Preencher, Abaixo. A palavra Centro-Oeste é copiada da linha 1 para as linhas 2 a 7. Transformações semelhantes acontecem em todo o conjunto de dados.
Preencha as células nulas com o valor acima -
Home, Close & Load. O Power Query será fechado. Em cerca de 10-20 segundos, os dados limpos do HQ aparecerão em uma nova planilha na pasta de trabalho.
Retorne os dados para o Excel - Salve a pasta de trabalho com um nome como CleanedDataFromHQ.xlsx.
- Quando os dados são selecionados, você deve ver o painel Consultas e conexões no lado direito. Se você não vir o painel, vá para Dados, Consultas e Conexões.
-
Clique com o botão direito na consulta no painel Consultas e conexões. Escolha Propriedades.
Abra as propriedades para esta consulta -
Escolha "Atualizar dados ao abrir o arquivo". Clique OK.
Defina a consulta para ser executada sempre que você abrir a pasta de trabalho.
Seu fluxo de trabalho então se torna: (a) Receber a pasta de trabalho do HQ por e-mail. (b) Salve o anexo na pasta correta com o nome correto. (c) Abra a pasta de trabalho CleanedDataFromHQ.xlsx. Os novos dados serão carregados na pasta de trabalho.
Assistir vídeo
Transcrição de vídeo
Aprenda Excel com o Podcast, Episódio 2221: Preencha células mescladas para baixo.
Ei, bem-vindo de volta ao netcast. Eu sou Bill Jelen, eu estava em Calumet City, Elizabeth aparece e diz que ela recebe este arquivo-- nosso arquivo, como este arquivo-- todos os dias e é horrível. Aqui na coluna A, não é apenas porque eles colocaram o meio-oeste e deixaram um monte de espaços em branco, é uma célula mesclada. Tudo bem? Eles estão enviando o … HQ está enviando esses arquivos para locais ao redor do mundo com essas células mescladas por aqui. Células mescladas são más.
Tudo bem, aqui está como se livrar disso. Primeiro, vou clicar com o botão direito do mouse em Mover ou Copiar, Criar uma cópia e movê-la para o novo livro. Então fingimos que é como a pasta de trabalho que Elizabeth está recebendo. Arquive, salve como e sempre que Elizabeth receber uma dessas pastas de trabalho, quero que ela a coloque exatamente no mesmo local com exatamente o mesmo nome. Tudo bem? Exatamente o mesmo nome - perfeito. Portanto, agora temos apenas aquele único arquivo, uma única folha, vou fechar o arquivo. E então vamos criar uma nova pasta de trabalho. Vou apenas inserir uma nova planilha aqui e essa nova planilha vai se chamar Relatório. E neste espaço em branco, nosso relatório fará dados, obter dados, de arquivo, de uma pasta de trabalho, apontar para o arquivo em que vamos salvá-lo - então, com exatamente o mesmo nome todas as vezes - clique em Importar , escolha a primeira planilha,e clique em Editar. Tudo bem. Em primeiro lugar, isso é incrível - a consulta de energia não terá nenhum desses absurdos de merge cell, eles convertem instantaneamente as células de merge em células individuais e todas essas células são nulas. Escolha aquela coluna, ela já está escolhida aqui, e então Transform, Fill, Fill Down, assim, e então Home, Close & Load. Tudo bem, então, aqui está minha pasta de trabalho.
Agora, vejo que alguns têm algumas colunas extras aqui. Deixe-me clicar com o botão direito e vamos editar.
E parece que qualquer coisa à direita do custo precisa ser eliminada. Quanta coisa extra lá fora? Clique com o botão direito e remova essas colunas - perfeito - Fechar e carregar. Tudo bem, agora, o fluxo de trabalho torna-se, cada vez que Elizabeth recebe um novo relatório da sede, ela vai salvá-lo no mesmo local com um nome confiável ou qualquer outra coisa - nós o chamamos, e então abrimos esta pasta de trabalho, e em seguida, clique aqui em Atualizar.
Ou, se quisermos trabalhar automaticamente, clique com o botão direito do mouse aqui, vá até Propriedades, fora da tela, na parte inferior, e diga: Sempre que eu abrir este arquivo, atualize os dados. Portanto, toda vez que abro esta apostila, ela vai para a nova apostila que recebemos da sede, substituirá aquelas células mescladas em branco pelos valores que deveriam estar ali, e a vida é ótima assim.
O Power Query é abordado em meu livro, mas também é abordado neste livro por Ken Puls e Miguel Escobar, M é para (DATA) MACACO.
Tudo bem, então, Elizabeth: Como faço para me livrar das células de mesclagem vertical? Minha sede continua me enviando esses arquivos todos os dias. Portanto, o plano: vamos salvar a pasta de trabalho em um local confiável com um nome confiável; crie uma pasta de trabalho de relatório em branco, Dados, Obter dados, Do arquivo, Da pasta de trabalho, especifique a planilha, BAM! As células de mesclagem sumiram. Eu seleciono essa coluna e Preencher, Fechar e Carregar; então, toda vez que você obtiver uma nova pasta de trabalho, salve-a em um local confiável com o mesmo nome confiável; Abro a pasta de trabalho Relatórios e atualizo, ou apenas configuramos a pasta de trabalho Relatórios para Atualizar automaticamente ao abrir.
Para baixar a pasta de trabalho do vídeo de hoje, é o URL na descrição do YouTube.
Quero agradecer a Elizabeth por estar em meu seminário na cidade de Calumet, quero agradecer a sua visita. Vejo você na próxima vez para outro netcast de.
Baixar arquivo Excel
Para baixar o arquivo excel: fill-merged-cells-down.xlsx
O Power Query é uma ferramenta incrível - permite que você resolva uma variedade de
Saeed Alimohammadi
