[Excel] Power Query - Atualização Automática de arquivos - Gaste Seu Tempo com o que Importa
Summary
TLDRIn this tutorial, the host Rubens, a consultant from Aline, demonstrates how to enhance productivity in Excel by automating the process of merging multiple spreadsheets. The video guides viewers through eliminating daily manual labor of updating spreadsheets by using Excel's Power Query feature. Rubens shows how to import data from various files into a single Excel sheet, filter and combine them, and perform calculations to create a consolidated report. This method saves time and allows for more efficient work, which can be beneficial for those dealing with repetitive tasks in their daily jobs.
Takeaways
- 😀 The video is a tutorial on increasing productivity in Excel by automating repetitive tasks.
- 🔄 It discusses the common issue of having to download and manually update spreadsheets daily for reporting to a supervisor.
- 📚 The presenter, Rubens, introduces the use of Power Query, an Excel extension, to streamline the process of handling multiple spreadsheets.
- 💡 Power Query can import data from various sources such as CSV, XLSX, XML, JSON, and databases, which can be combined within Excel.
- 🔍 The script demonstrates how to use Power Query to import and combine data from multiple Excel files into a single workbook.
- 🗂️ It explains the steps of using 'Get & Transform' in Power Query to filter and combine data, avoiding the need for manual daily updates.
- 📈 The tutorial includes a practical example of creating a consolidated sales report by merging data from different months.
- 🧩 The process involves filtering out unnecessary columns, applying transformations, and using calculations to derive new data such as total sales.
- 🔑 The script highlights the importance of understanding the structure of imported data and adjusting the settings in Power Query accordingly.
- ♻️ It shows how to refresh the data automatically using Power Query's 'Refresh All' feature, ensuring the workbook is always up to date.
- 🚀 The video concludes by emphasizing the time-saving benefits of automating Excel tasks with Power Query, leading to increased productivity.
Q & A
What is the main topic of the video?
-The main topic of the video is about increasing productivity in Excel by automating repetitive tasks such as downloading daily spreadsheets and combining them without the need for daily manual updates.
Who is the presenter of the video?
-The presenter of the video is Rubens, a consultant from Aline.
What is the purpose of using Power Query in this video?
-The purpose of using Power Query is to automate the process of importing and combining data from multiple Excel files into a single spreadsheet, reducing the need for repetitive manual work.
How does Power Query help in automating the task of combining spreadsheets?
-Power Query helps by allowing the user to create connections to multiple Excel files, transform the data as needed, and then combine the data into a single consolidated table that can be easily updated.
What is the first step in using Power Query to automate the task?
-The first step is to access Power Query from the Data tab in Excel, then select 'Get Data' and choose the source files from the working directory.
How does the video demonstrate the process of importing data from an Excel file using Power Query?
-The video demonstrates by showing the presenter navigating to the 'Data' tab, selecting 'Get Data', choosing 'From Workbook', and then selecting the desired Excel file to import.
What is the significance of the 'Transform Data' option in Power Query?
-The 'Transform Data' option in Power Query allows the user to modify the data structure, such as adding or removing columns, changing data types, and applying filters, before loading the data into the main Excel workbook.
How does the video show the process of combining data from multiple Excel files into one?
-The video shows this by creating a new query for each additional Excel file, then using the 'Combine Files' feature in Power Query to merge the data into a single table.
What is the benefit of using Power Query's 'Combine Files' feature?
-The 'Combine Files' feature simplifies the process of merging data from multiple sources into a single, unified table, which can then be easily updated and maintained.
How does the video address the issue of repetitive tasks in daily work?
-The video addresses the issue by showing how to set up Power Query to automate the process of combining spreadsheets, thus reducing the need for daily manual updates and allowing for more efficient use of time.
What additional functionality does the presenter mention about Power Query that could be covered in future lessons?
-The presenter mentions that there are more advanced features and capabilities of Power Query that could be covered in future lessons, such as automating processes and handling more complex data transformations.
Outlines
📊 Introduction to Excel Productivity Techniques
This paragraph introduces the video's aim to teach viewers how to increase productivity in Excel by making daily tasks more efficient. The speaker, Rubens, a consultant, explains that the video will guide through automating the process of downloading daily spreadsheets, making changes, and stacking them on top of each other. The goal is to perform this repetitive task only once, thus reducing daily workload. The introduction also encourages viewers to subscribe and share the video with others who might benefit from these Excel automation techniques.
🔍 Exploring Power Query for Data Consolidation
The speaker demonstrates the use of Power Query, an Excel extension, to automate data extraction and consolidation. He explains how to navigate through Power Query to import data from various sources such as CSV, XLSX, XML, and JSON files. The process includes understanding the data structure, identifying headers, and assigning appropriate data types to columns. The speaker guides through creating a new query to import multiple spreadsheets and emphasizes the importance of transforming data before loading it into the main workbook to ensure accuracy and efficiency.
🔧 Power Query Transformation and File Consolidation
In this section, the speaker focuses on refining the use of Power Query to filter and consolidate data from multiple files. He illustrates how to filter out files based on specific criteria, such as file names starting with 'extração', and how to remove unnecessary columns to streamline the data. The speaker then proceeds to merge the filtered data into a single workbook, which will serve as a comprehensive report for management. He also discusses the process of updating this consolidated data with new files added to the folder, showcasing the automation capabilities of Power Query.
📈 Finalizing the Data and Enhancing Productivity
The final paragraph wraps up the video by showing the completed consolidated data and emphasizing the time-saving benefits of using Power Query. The speaker demonstrates how to perform calculations within the consolidated data, such as multiplying quantity by unit price to get total values. He also explains how to refresh the data automatically with new files, ensuring that the report remains up-to-date without manual intervention. The video concludes with an invitation for viewers to subscribe to the channel, share the video, and leave comments for further inquiries, hinting at future lessons on advanced Excel automation.
Mindmap
Keywords
💡Productivity
💡Excel
💡Automation
💡Power Query
💡Data Sources
💡Spreadsheets
💡Transformation
💡Merge
💡Headers
💡Formulas
💡Refresh
Highlights
Introduction to a productivity-enhancing Excel tutorial.
Learning to automate repetitive tasks in Excel to reduce daily rework.
Using Excel's built-in features to consolidate daily downloaded spreadsheets.
Understanding how to work with Power Query, an Excel extension.
Importing data from various sources using Power Query.
Transforming data within Power Query for better organization.
Creating a single consolidated spreadsheet from multiple files.
Filtering and selecting specific data within Power Query.
Combining multiple spreadsheets into one using Power Query.
Automating the process of updating spreadsheets with new data.
Creating a base spreadsheet for monthly data consolidation.
Using Power Query to automatically refresh data from multiple files.
Performing calculations within the consolidated spreadsheet.
Updating the base spreadsheet with new data without manual re-entry.
The impact of automation on improving productivity and reducing repetitive work.
Encouraging viewers to subscribe and share the video for those facing similar repetitive tasks.
Invitation to learn more about Power Query's capabilities in future tutorials.
Conclusion emphasizing the time-saving benefits of Excel automation.
Transcripts
seja bem-vindo a lilisco no vídeo de
hoje nós vamos aprender a ter
produtividade no Excel Como assim a
gente vai fazer algumas alterações
dentro do próprio Excel que se você
tiver que baixar planilhas diárias e
fazer alimentação colocar uma planilha
em cima da outra e fazer isso todo dia
para entregar seu chefe a gente vai
deixar montado para fazer uma única vez
e você não vai precisar mais ter esse
retrabalho de todos os dias tem que
ficar alimentando planilha para o
planilha Então vem comigo
[Música]
Olá eu sou Rubens só se consultor da
Aline E hoje nós vamos aprender a
automatizar uma tarefa que pode ser
muito desgastante para você no seu dia a
dia de trabalho Vamos mostrar o que a
gente vai fazer imagine o seguinte a
gente tem aqui ó que faz uma extração
ela pode ser mensal pode ser diária pode
ser semanal que você precisa fazer em um
sistema da sua empresa e aí você precisa
fazer o você vai baixar a planilha 1 aí
você vai fazer as alterações dela e ela
fica na sua base depois você vai baixar
uma outra planilha e aí você vai pegar
essa planilha copiar na base depois você
vai fazer isso para todas as planilhas
que você tiver que fazer mas a gente vai
aprender a tratar isso aqui dentro do
próprio Excel para que você possa fazer
esse serviço uma única vez e usar o seu
tempo para fazer outras atividades que
podem melhorar a sua produtividade no
dia a dia e melhorar as entregas que
você tem para o seu chefe bom antes de
mais nada já clica no Sininho aqui se
inscreva no nosso canal e mande esse
vídeo para o máximo de amigos que você
sabe que faz esse trabalho repetitivo
dia a dia então vamos dar uma olhada lá
o que a gente vai fazer aqui eu vou vir
para minha outra planilha aqui vou
iniciar uma pasta de trabalho e o que
que nós vamos fazer nós vamos trabalhar
com Power que é uma extensão que a gente
tem aqui dentro do próprio Excel Aonde
ele fica ele fica aqui ó na guia dados
aí você vai vir aqui clicar em dados E
aí vai vir aqui ó em Obter dados certo
vindo aqui em obterdados aí a gente tem
aqui ó a gente pode obter dados de pasta
de trabalho de csv XL XML Jason a gente
pode pegar banco de dados pode vir da
éger de algumas outras plataformas
serviços onlines e outras fontes aqui
que a gente pode combinar Aqui as nossas
tabelas Mas vamos dar uma olhada aqui ó
eu tenho dentro desse arquivo os
arquivos que eu extrai Vamos considerar
que cada um foi extraído no primeiro dia
de cada mês então tem aqui ó mês um dono
de 23 um do dois um dois um do 4 e assim
sucessivamente e dentro dessa própria
pasta Eu tenho um arquivo que eu tô
gravando aula para vocês isso aqui não
vai interferir no que a gente for fazer
então vamos dar uma olhada aqui primeiro
vamos aqui importar um arquivo de Excel
para a gente entender como que funciona
o Power Então você vai vir aqui ó em
dados Obter dados e vamos pegar aqui ó
de pasta de trabalho
ele vai carregar eu vou escolher aqui a
minha pasta e vou pegar aqui primeiro
aqui
ele vai abrir essa tela aqui não precisa
se assustar tá aqui ele vai mostrar o
que ele conseguiu entender já com o
arquivo que a gente importou então vou
clicar aqui em planilha e aqui ele já
vai dar uma prévia do que que tá
acontecendo aqui do
item que ele tá lendo aí a gente vai
fazer o quê vamos clicar aqui ó
transformar dados tá a gente pode ter
aquele nosso instinto de clicar aqui
carregar mas primeiro a gente clica em
transformar dados que aí sim a gente vai
conhecer o Power
pronto abriu aqui deixa eu dar uma
ajustada para a gente conseguir ver ele
na tela e
só ajustar aqui um pouquinho para cá e
falta aparecer uma parte importante
e pronto esse aqui é o nosso Power
Coelho aqui em cima a gente tem guias
também onde a gente tem aqui ó os
quadrantes onde a gente vai fazer
algumas alterações a gente não vai
entrar em detalhes aqui dentro do Power
se você quiser fazer saber um pouco mais
sobre essa ferramenta comenta aqui eu
quero que aí eu trago uma aula mostrando
um pouco de tudo que essa ferramenta
pode trazer para gente dentro do Excel
Tá bom então vamos ver aqui ó eu tenho
desse lado aqui eu tenho as minhas
consultas então eu tenho aqui a minha
planilha 1 que é o que foi feito desse
lado aqui ó a gente tem as propriedades
eu tenho aqui o nome da planilha então
se eu quiser mudar aqui para 01 01 2023
e da Anitta ele já muda aqui do lado
aqui ó o nome do meu arquivo aqui é uma
umas etapas aplicadas que acontecem
dentro do Power então o que que ele fez
a primeira etapa você vai consultar a
fonte a segunda etapa ele já fez uma
navegação aqui e entender o que isso
aqui é uma planilha aí tá vendo a
primeira linha aqui é são os cabeçalhos
então ele já entende que são cabeçalhos
e ele coloca esses cabeçalhos para cima
promovendo eles deixando eles fixo como
cabeçalho e aqui a gente tem o tipo
alterado que ele já colocou cada coluna
com o tipo que ele achou adequado para
ele conseguir fazer essa alteração Tá
bom então isso daqui é a parte do Power
a gente fez essa primeira consulta e já
dentro dele a gente consegue colocar uma
outra consulta então botão direito aqui
Nova consulta de arquivo aí eu posso
pegar uma outra pasta de trabalho aí eu
posso vir aqui pegar um outro arquivo
ele vai fazer a mesma coisa ele vai
abrir essa tela aqui na frente eu vou
selecionar aqui ele não vai dar a opção
lá de carregar porque a gente já tá
dentro do Power então ele só vai dar
opção de dar um OK
ele dá ok então isso aqui é a outra
tabela aqui é do mês 2 Aqui é do mês 1 e
se eu vier aqui e colocar
0102/2023 então enter pronto eu tenho as
duas se eu vier aqui fechar e carregar
ele já vai trazer aqui para nossa
planilha mas ele tá trazendo aqui ó uma
aba Ele trouxe uma planilha e na outra
aba Ele trouxe outro isso daqui não é o
que a gente quer isso aqui é
praticamente o que a gente já faz no dia
a dia que a gente tem que abrir planilha
trabalhar e colocar tudo dentro de uma
base de dados não vai ser isso que eu
preciso fazer então o que que eu vou
fazer eu vou voltar lá para o Power
ah como eu voltei desculpa deixa eu
voltar aqui deixa eu ajustar
[Música]
Vou colocar aqui beleza quando a gente
fizer isso vai abrir essa linha aqui de
consultas e conexões
eu posso clicar duas vezes aqui ó ele já
abre para gente então o que eu vou fazer
esses dois aqui ó não precisa de nenhum
nenhum vou deletar simplesmente assim
e agora agora nós vamos começar a fazer
a mágica que é o intuito dessa aula
vamos ver aqui ó lembra que eu mostrei
que tinha algumas opções que a gente
pode fazer conexões lá no Excel deixa eu
fechar aqui carregar
esses dois itens aqui eu vou selecionar
um dois botão direito aqui ó excluir vou
excluir e vamos olhar aqui ó guia dados
obterdados de arquivo bom daqui o que
que a gente vai fazer a gente tem passe
de trabalho csv xlm Jason PDF e pasta
isso mesmo a gente consegue fazer uma
conexão aqui diretamente de uma pasta
Então a gente vai trabalhar com isso
daqui para a gente conseguir fazer a
nossa
colocar uma planilha embaixo da outra
Quando a gente tiver trabalhando e
colocando os arquivos dentro de uma
pasta você já vai entender com que a
gente vai fazer beleza Portal dados
arquivo pasta Beleza vou pegar aqui o
caminho da pasta esse caminho aqui ó
feito isso eu vou colar aqui em cima
aí ele vai aqui o nome da pasta eu vou
abrir
aí ele tá trazendo o quê ele já leu aqui
ela tem um dois três quatro cinco
arquivos lá dentro aí eu tenho aqui o
arquivo que eu tô gravando e os itens da
extração Então a gente vai vir aqui
transformar dados tá não clique em
combinar Vamos transformar beleza
transformei vim aqui em transformar os
dados OK agora vamos fazer aqui um
pequeno tratamento para que a gente
consiga colocar os arquivos lá dentro
ele atualizaram automaticamente Tá bom
então vamos lá passo a passo primeiro
aqui a gente tem uma coluna de conteúdo
e aqui a gente tem uma de nome de
arquivo sempre que a gente vai fazer a
instalação de algum sistema ele vai
trazer um nome padrão de alguma forma ou
vai ser um código ou vai ser tudo junto
aqui no caso todos têm aqui ó extracão
como início Então esse aula Excel Power
aqui eu não quero que ele seja Unificado
na hora que nós tivermos fazendo aqui a
nossa transformação a nossa unificação
de planilha então eu vou fazer o
seguinte aqui em cima eu posso aplicar
nesse botãozinho aqui ó e ele vai me dar
opção de fazer aqui alguns filtros tá ou
eu posso vir aqui e tirar esse áudio
Excel porém se eu inserir Mais Um item
aqui por exemplo se eu colocar aqui ó
Se eu colocar a planilha do dia
01 01 05 2023 eu vou ter que abrir aqui
de novo o Power e selecionar ela porque
eu tô fazendo aqui um uma display aqui
né disfregando aqui é manual então a
gente não vai fazer isso que que a gente
vai fazer eu vir aqui a filtro aí eu
quero filtrar que contém tá então a
gente já viu que aqui o que ele contém é
o comecinho extracão Então vou vir aqui
beleza contém contém o quê esse
uma coisa muito importante
então se eu colocar uma letra maiúscula
Se eu colocar minúscula entende como
minúsculo Então vou ver aqui ó
contém extracão vou dar um OK
Pronto ele sumiu e tem aqui apenas os
itens que começam com extracão agora o
que que a gente vai fazer para não
perder muito tempo aqui porque como eu
disse para a gente entrar a fundo aqui
você vai ter que comentar aqui eu quero
porque aí eu vou trazer umas tratativas
legais que a gente pode fazer aqui
dentro vamos clicar nessa coluna aqui
que tem o conteúdo que ele tem essas
duas setinhas para baixo botão direito
eu vou remover outras colunas o que que
ele fez ele deixou apenas a coluna que
eu tô selecionado Vamos dar um furo aqui
ele entendo que esses dois botõezinhos
aqui eu vou clicar em combinar arquivos
ele vai fazer aqui alguns trabalhos ele
vai dar uma pré-visualização do que tá
fazendo e eu vou dar um OK
[Música]
esperando um pouquinho Pronto ele já fez
vou dar um zoom menos então o que que
nós temos aqui agora Ele criou aqui
essas consultas você pode fechar essa
daqui não precisa se apegar com ela que
isso aqui são transformações que ele faz
automaticamente e aqui é o nosso arquivo
então a gente pode chamar isso aqui de
base fase vendas tá então isso aqui é a
base que eu preciso extrair para mostrar
lá para o nosso chefe então o que que eu
tenho aqui eu tenho mais dinheiro
produto eu tenho aqui as datas ó tem um
mês um eu tenho aqui ó mês dois
mês quatro mês três Então os quatro
arquivos que estão lá eu já consegui
trazer eles para cá aqui embaixo ele já
mostra que eu tenho o que eu tenho cinco
colunas
cinco colunas e 92 linhas tá gravem isso
cinco colunas 92 linhas Então beleza
feito isso o que eu vou fazer eu posso
fazer contas aqui dentro também então se
eu tenho aqui a quantidade e o valor
unitário eu vou fazer aqui uma continha
de Total eu posso vir aqui ó na guia
adicionar coluna eu vou selecionar
segurando o shift o a quantidade e o
valor unitário aqui nesse item de
calculadora eu vou clicar aqui e vou
clicar em multiplicar beleza
multipliquei ele fez aqui a
multiplicação e tá vendo aqui ó essa
barra de fórmulas aqui Aqui tá vendo ó
multiplicação é um nome
Opa é o nome da minha coluna ele acabou
mudando porque ó tá vendo entre as
chaves aqui ele tem um tracinho que eu
coloquei sem querer o que que eu vou
fazer dentro dele aqui eu vou colocar
aqui ó
ele já alterou aqui e deixou com uma
etapa a menos caso ela precisasse
alterar alguma coisa bom tá feito a
gente viu que já estão as quatro
português que a gente tem na nossa pasta
aqui dentro Qualquer coisa a gente tem
que fazer é ver aqui ó página inicial
fechar e carregar
beleza tenho todos os arquivos aqui para
eu começar a trabalhar ele e mostrar
para o meu chefe o que ele precisa tá
mais uma vez isso aqui realmente vai
funcionar Vamos ver eu vou vir aqui ó
vamos selecionar se eu pegar daqui até
aqui tirando o cabeçalho eu tenho aqui ó
uma contagem de 92 Ok ou eu posso olhar
aqui do lado
e ver aqui que eu tenho 92 minutos no
total Beleza vou vir para minha pasta pô
chegou um mês cinco eu vou dar um
control c control V aqui tem o meu
arquivo
mudar um F2 para renomear
aí eu venho aqui e mês 5 Então beleza
ele tá aqui ó com o padrão do início
extracão e a gente tem cinco arquivos
agora fiz isso chegou no dia
Liguei meu computador esse trem meu
arquivo que isso não vou precisar fazer
manual mesmo eu vou chegar aqui beleza
se eu já tenho tudo feito no power eu
preciso fazer eu posso vir aqui ó em
dados e clicar nesse botão aqui ó
atualizar tudo ó vamos olhar aqui do
lado
92 eu vou tirar até um print aqui
e deixar ele colado aqui do lado para a
gente ver eu vou vir aqui ó já fizemos
lá a inserção de mais uma tabela e vou
dar um atualizar tudo no que eu
atualizei olha aqui
Opa
115 linhas a gente tinha 92 então com
isso Se eu olhar aqui eu dobrei aqui no
caso a quantidade no mês 4 porque eu não
fiz uma extração de arquivo novo mas
sempre que você precisar fazer esse tipo
de inserção de inclusão de tabela dentro
de uma base que você tem ele vai fazer
automaticamente e aí com essa base aqui
você consegue trabalhar para deixar do
jeito que você precisa para mostrar para
o seu chefe bom nessa aula que eu tinha
que mostrar era isso para vocês você vão
conseguir ganhar muito tempo fazendo
essas fazendo esse tratamento dentro do
Power E aí você vai conseguir que você
tem para tomar um cafezinho para estudar
um pouquinho mais e vai ter muito mais
produtividade dentro do seu serviço
Beleza se você ficou até aqui não se
esqueça clique aqui embaixo se inscreva
no nosso canal Mande esse vídeo para os
seus amigos que fazem trabalho
repetitivo também e se precisar de
alguma coisa deixa aqui nos comentários
e não esqueça quer aprender mais sobre
comida que eu quero que eu trago uma
aula mostrando para vocês
lá dentro que você vai se impressionar
com a capacidade dessa ferramenta de
automatizar processos dentro do Excel
beleza muito obrigado por sua atenção e
tamo junto
Voir Plus de Vidéos Connexes
5.0 / 5 (0 votes)