O Excel combina várias planilhas em uma única planilha. Cada planilha pode ter um número diferente de registros de um dia para o outro, portanto, as fórmulas não são o caminho a percorrer. Em vez disso, uma ferramenta pouco conhecida chamada Power Query permitirá que você mescle os dados de forma simples e rápida.
Assistir vídeo
- Doug: Como combinar quatro folhas onde cada uma tem um número diferente de linhas?
- Use o Power Query
- Formate cada planilha como uma Tabela com Ctrl + T
- Renomear as tabelas
- Para cada tabela, nova consulta Da Tabela. Adicione uma coluna personalizada para a região
- Em vez de Fechar e Carregar, escolha Fechar e Carregar para … Apenas Criar uma Conexão
- Use New Query, Combine Query, Append. 3 ou mais tabelas. Escolha as tabelas e adicione
- Feche e carregue e os dados aparecem em uma nova planilha
- Para a tabela com coluna extra: os dados aparecem apenas para os registros daquela planilha
- Para a tabela em que as colunas estavam na ordem errada: o Power Query funcionou corretamente!
- Fácil de atualizar mais tarde
Transcrição gerada automaticamente
- Aprenda Excel com Episódio de Podcast
- 2178 mesclagem para planilhas de hoje
- pergunta via YouTube de Doug tem isso
- situação temos quatro folhas onde cada
- folha é uma região com dados de vendas e
- o número de registros muda mensalmente e
- agora Doug está tentando usar fórmulas
- mas quando o número de linhas mudou isso
- se torna um pesadelo, então eu disse ei
- Doug podemos usar consulta avançada se você tiver
- Excel 2010 ou Excel 2013 é gratuito
- baixe da Microsoft ou é construído
- em 2016 e no Office 365 diz sim todos
- certo então aqui está o que temos, temos
- quatro relata a região central a leste
- região região sul e oeste
- região e cada um tem um diferente
- número de registros como aqui no sul
- Região temos 72 registros no leste
- região 193 registros e isso vai para
- mudar certo
- toda vez que executamos este relatório,
- um número diferente de registros agora
- fez algumas suposições aqui primeiro que
- não há uma coluna chamada central e
- então também vou ser completamente mau
- aqui e pegue a região Sul eu quero
- tente estragar tudo, vou pegar o
- coluna de lucro, corte e cole como
- inverta-os e tudo bem, então nós
- tem um onde as colunas são invertidas
- e então outro onde vamos
- adicione uma coluna extra por cento do lucro bruto
- então isso vai ser o lucro dividido por
- receita em um mundo ideal, tudo isso é
- com a mesma forma, mas como aprendi
- recentemente eu estava dando um seminário em
- Carolina do Norte se eles não estão bem
- alguém teve uma situação bem, você conhece
- no meio do ano as coisas mudaram
- e eles adicionaram uma nova coluna ou moveram
- colunas em torno de nós ficamos muito felizes em
- ver que a consulta de energia foi capaz de lidar
- com isso tudo bem, então vamos pegar
- cada um desses relatórios e torná-los
- um formato de tabela oficial como tabela então
- isso é chá de controle ou você poderia usar um
- intervalo de nome para mim controle t é o
- maneira mais fácil de ir e o que eles fazem aqui
- eles chamam esta mesa eu vou
- renomeie isso para ser central e
- em seguida, vamos para o controle leste T clique em OK
- e isso vai se chamar Leste agora hey
- em um podcast anterior, mostrei como se
- estes são quatro arquivos separados que nós
- poderia ter usado consulta avançada apenas para
- combinar arquivos, mas isso não funciona quando
- são quatro ou quatro planilhas separadas
- no mesmo livro tão bem lá nós
- vai e então
- como este controle - é um pouco tedioso para
- configure isso da primeira vez, mas garoto
- vai ser incrível
- toda vez que você tem que atualizar isso mais tarde
- então vamos fazer é vamos
- para escolher esta primeira mesa central
- região e se você estiver em 2010 ou 2013 e
- baixado power query você vai
- vai ter sua própria guia, mas em 16 em
- Excel 2016 está realmente recebendo
- transformado, que é o segundo grupo em
- Office 365 agora está se transformando
- que é o primeiro grupo e então nós somos
- vai dizer que eles iriam criar este
- dados de uma tabela ou intervalo certo e
- there is our data now we don't have a
- region field and the combined files
- would have added the region field so in
- this case I'm just gonna add a column a
- new custom call the headings gonna be
- region and this one is going to be what
- was this central right like that
- click OK alright now here's the
- important part when we're done this with
- this we're gonna go home not choose
- close and load we're gonna open the
- drop-down close and load to only create
- a connection click OK
- perfect we have our connection only now
- the next thing we have to do is repeat
- these steps for the next three regions
- and now that would be really a bit
- boring to you so let's just speed up the
- video to 10x for this
- alright there we are for connections set
- up now here's where we're going to do
- the magic I'm gonna insert a new blank
- worksheet and I'm gonna say get data
- combine queries and I want to append two
- queries from this workbook and I'm gonna
- say three or more tables and the
- available tables are Central through
- West click Add BAM click OK and then we
- can close and load and what we have here
- is we have a superset of all of the
- records in all of the tables all right
- and where we tried to screw it up where
- I purposely tried to screw up by
- reversing cost of goods sold and profit
- down in what was that that was Central
- East South in the South Region I'll just
- go check those right and it looks like
- yeah generally feels right they used the
- heading to figure it out because the
- profit is always higher than cost of
- goods sold and so that worked and then
- down here in the West where we added
- gross profit percent we actually get
- that data for the tables that had it and
- for the tables that didn't have it we
- just get null which is perfect alright
- now duck
- here's what you're gonna do so the next
- time that you have some more data and
- I'll just let's create some some extra
- records here we'll just add some ABC
- with a date of today and all retail and
- it's called Doug's new records and just
- some garbage out here let's just put in
- a hundred all the way across in the
- interest of time okay so now because
- this is a table the table automatically
- expands to the new records which is
- beautiful had they been named range I
- would have had a redefine that's why I
- really like the table instead of the
- name range but we come back here to the
- resulting workbook with 563 rows loaded
- and I click refresh
- and bam now I have 572 Rose loaded
- including let's see if we can find them
- in here
- Doug's new records right there at the
- end of the South Region
- isn't that just an awesome awesome way
- to go yes it definitely takes longer to
- set up the first day we're up to seven
- minutes already if I hadn't sped that up
- to 10x but once it's set up now life is
- gonna be super super easy from here on
- out way this is where I usually promote
- my own book but no this time let's talk
- about this awesome book Emma's for data
- monkey by Ken polls in Miguel Escobar
- everything I learned about power query I
- learned from this book look at the eye
- on the top right hand corner for more
- information about that book all right
- wrap up topics in this episode Doug how
- to combine four sheets where each sheet
- has a different number of rows we can
- use power query make sure to format each
- worksheet as a table with ctrl T or use
- named ranges but I prefer ctrl T rename
- the tables from each table choose new
- query from table add a custom column for
- a region and then instead of close and
- load choose close and load to only
- create a connection do that for all four
- queries and then new query combined
- query append choose three or more tables
- choose the tables and click Add
- now some older versions of power query
- you couldn't do three or more tables you
- have to do two and then do another query
- to add the third one and then do another
- query to add the fourth one either way
- it would be more hassle that way I'm
- glad that they added the three or more
- tables close and load this time close
- and load to the worksheet and and then
- later on if you add more data to any of
- the four tables just go back to your
- query and click refresh and you're good
- to go
- power query and amazing new feature from
- Microsoft I love it I thank Doug for
- dizendo essa pergunta bem, obrigado
- por passar por aqui nos vemos na próxima vez
- para outro lançamento de rede de
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2178.xlsm
