Este é o problema do orçamento do bombeiro. As pessoas em um corpo de bombeiros estão fazendo seus orçamentos errados no Excel. Uma incrível transformação do Power Query fornece a solução.
Assistir vídeo
- Steve tem que somar os números que foram inseridos em uma coluna de texto
- Existem várias linhas em cada célula, separadas por alt = "" + Enter
- É necessário dividir essas linhas em linhas e, em seguida, analisar o valor em dólares do meio de cada célula
- Resumir por centro de custo
- Construir uma tabela de pesquisa
- Obtenha os totais da tabela de pesquisa, usando IFNA para ignorar os erros na linha em branco
- Bônus: adicione uma macro de evento para atualizar a planilha quando eles mudarem uma célula.
Transcrição de vídeo
Aprenda Excel com, Podcast Episódio 2160: SUM Data That Been Alt + Entered.
Ei. Bem-vindo de volta ao netcast. Sou Bill Jelen. Eu não estou inventando isso. Eu tenho uma pergunta de alguém que tem dados - dados de orçamento - que se parecem com isto. Agora, eu coloquei palavras falsas aqui para que não tenhamos suas informações de orçamento, mas uma pessoa é nova no departamento de contabilidade, foi para uma empresa, e essa empresa há anos faz seus orçamentos assim. Eles não são contadores que fazem o orçamento, são pessoas de linha, mas é assim que eles estão fazendo, e ele não pode fazer com que mudem. Então, aqui está nosso objetivo. Ele diz que isso é tão ruim quanto digitar o orçamento no Word.
Bem, quase, mas felizmente, graças à consulta de energia, isso vai salvar nosso problema. Aqui está nosso objetivo. Para cada CENTRO DE CUSTO aqui, queremos relatar o total de todos esses números. Então, há o nome da despesa, um -, rotineiramente um -, então um sinal $, e então, apenas para tornar a vida interessante, de vez em quando, uma nota aleatória depois; não todas as vezes, apenas algumas vezes. Linha em branco entre cada um. Toneladas e toneladas de dados.
Então, aqui está o que vou fazer. Vou descer até o fundo, a última célula, vou selecionar tudo isso, incluindo os títulos. Vou criar um NAME. Vou chamá-lo de MyData. MyData, assim, ok? Tudo bem. Agora vamos usar a consulta avançada, que é gratuita em 2010 ou 2013, incorporada ao Office 365 de 2016 e 2016. Isso virá de uma TABELA OU FAIXA. Tudo bem. Em primeiro lugar, sempre que tivermos esses espaços em branco na COLUNA A, todos os NULLS que quisermos nos livrar. Portanto, vou desmarcar NULL. Impressionante. OK. Realmente, nestes dados, nesta versão dos dados, porque vou construir um VLOOKUP, não precisamos desta coluna. Então, vou clicar com o botão direito e me livrar dessa coluna, REMOVER coluna.
Tudo bem. Agora, é aqui que a maldita mágica vai acontecer. Escolha esta coluna, DIVIDIR COLUNA POR UM DELIMITADOR, e definitivamente iremos para AVANÇADO. O delimitador será um caractere especial e vamos dividir cada ocorrência do delimitador. Então, aqui, eu acho que eles realmente já descobriram porque eu expandi, mas vou mostrar a vocês. INSERIR CARÁTER ESPECIAL. Vou dizer que é um ALIMENTAÇÃO DE LINHA, certo, então, a cada ocorrência do ALIMENTAÇÃO DE LINHA, vou DIVIDIR EM LINHAS. Tudo bem, e o que vai acontecer aqui é, 1, 2, 3, 4, 5, vou obter 5 linhas ou vou dizer 1001, mas, em cada linha, vai ter um diferente linha desta célula. Isso é incrível. Existe 1, 2, 3, 4, 5, 1001. Tudo bem. Agora só precisamos analisar esse bad boy. Tudo bem,então, escolha essa coluna, COLUNA DIVIDIDA POR UM DELIMITADOR. Desta vez, um delimitador será um sinal $. Isso é perfeito, uma vez, no primeiro sinal $ que encontramos, apenas no caso de haver um sinal $ lá na parte futura. Vamos Dividir EM COLUNAS. Clique OK. Tudo bem. Portanto, existem detalhes. Aqui está nosso dinheiro.
Agora, vou dividir isso no SPACE. Então, escolha esta coluna, SPLIT COLUMN BY A DELIMITER, e o delimitador vai ser um ESPAÇO, sim, uma vez no DELIMITER ESQUERDO, clique em OK, e eu não preciso desses comentários aí, então esses comentários nós ' vai REMOVER. Na verdade, não preciso disso também porque estou apenas tentando obter um total de todas essas coisas, então vou REMOVER.
Agora, transforme-se. GRUPO POR CENTRO DE CUSTO, NOME DA NOVA COLUNA vai se chamar TOTAL, a OPERAÇÃO vai ser a SOMA, e em qual coluna vamos SOMAR? O DETAILS2.1. Bonito. Clique em OK, tudo bem, e o que terminaremos é uma linha por CENTRO DE CUSTO com o TOTAL de todos esses itens de linha. HOME, CLOSE & LOAD. Provavelmente vai inserir uma nova planilha. Espero que ele insira uma nova planilha, e insere, e essa planilha se chama MYDATA_1. MYDATA_1.
Tudo bem. Agora vamos voltar aqui aos dados originais e fazer essas etapas. No primeiro, = VLOOKUP de 1001 em nossos resultados. Isso é como configurar uma referência circular, mas não nos dará uma referência circular. , 2, FALSO. Eu quero a correspondência exata. Tudo bem, mas não vamos querer fazer isso para as células em branco. Então, vou dizer, bem, na verdade, vamos apenas copiá-lo por completo. CONTROL + C, vá até o fim apenas para ver o que estamos obtendo. Talvez estejamos recebendo N / As e eu possa me livrar disso com o IFNA. Sim, lindo, certo. Então, vamos nos livrar dos N / As. Se for N / A, então queremos apenas “”. Não queremos nada lá. CONTROL + ENTER. Tudo bem. Agora, esse deve ser o TOTAL. Vamos ver se podemos encontrar um curto e apenas fazer as contas. = 627,37 + 7264,25 + 6066.01 + 4010,66 + 9773,94, e o TOTAL, 27742,23 é isso. Muito bom. (= IFNA (PROCV (A2, MeusDados_1,2, FALSO), “”))
Agora, o negócio é o seguinte. Então, temos aquelas pessoas de linha que estão aqui mudando coisas, certo, e então vamos dizer que eles vão e mudam o orçamento, 40294,48, e eles vêm aqui e mudam este para 6000, assim, e eles adicionam um novo, ALT + ENTER, ALGO - $ sinal, $ 1000 acabou de adicionar. Tudo bem. Agora, é claro, quando eu pressiono ENTER, este número, 40294.48, não vai atualizar, certo, mas o que temos que fazer é ir para a guia DATA e queremos ATUALIZAR TODOS. Portanto, 40294,48. Observe, observe, observe, observe. ATUALIZE TUDO. Incrivelmente incrível.
Eu amo consulta de poder. A consulta avançada é a coisa mais incrível. Esses dados, que são essencialmente como dados de palavras em uma célula, agora estão sendo atualizados. Você provavelmente poderia até fazer algum tipo de macro que diga que toda vez que alguém muda algo na COLUNA C, vamos em frente e clica em ATUALIZAR TUDO usando a macro e apenas temos esses resultados constantemente, atualizando constantemente.
Que pergunta horrível enviada. Me sinto mal por Steve, que tem que lidar com isso, mas agora, usando consulta avançada no Office 365 ou baixado para 2010 ou 2013, você tem uma maneira muito, muito fácil de resolver isso.
Esperar. Ok, um adendo: vamos torná-lo ainda melhor. Esta planilha é chamada de DATA e salvei a pasta de trabalho como habilitada para macro, portanto, xlsm. Se você for xlsx, não pule o salvamento como xlsm. ALT + F11. Encontre a pasta de trabalho chamada DATA, clique duas vezes, canto superior esquerdo, PLANILHA e ALTERAR sempre que mudarmos a planilha, diremos ACTIVEWORKBOOK.REFRESHALL e feche, certo, e agora vamos tentar. Vamos editar algo. Então, pegaremos aquelas framboesas que estão atualmente em 8.000 e mudaremos para 1.000, então estamos reduzindo em 7.000. Quando pressiono ENTER, quero que 42.000 caiam para 35.000. Ah. Impressionante.
Bem, ei. É aqui que costumo implorar que compre o meu livro, mas, hoje, vou pedir-lhe que compre o livro dos meus amigos - Ken Puls e Miguel Escobar - M é para (DATA) MACACO. Tudo que aprendi sobre consulta de energia, aprendi neste livro. É um livro incrível. Dê uma olhada.
Conclusão do episódio: Steve tem números para somar que foram inseridos em uma coluna de texto; várias linhas em cada célula, separadas por ALT + ENTER; precisa dividir essas linhas em linhas e, em seguida, analisar o valor em dólares do meio de cada célula; resumir por COST CENTER; construir uma tabela de pesquisa; obter totais da tabela de pesquisa, usando IFNA para ignorar os erros na linha em branco; e então, um bônus, macro no final, uma macro de evento para atualizar a planilha quando eles mudarem uma célula.
Quero agradecer a Steve por enviar essa pergunta e estou muito feliz por ter uma resposta - antes da consulta de energia, teria sido muito, muito difícil - e quero agradecer a você por passar por aqui. Nos vemos na próxima vez para outro netcast de.
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2160.xlsm
