Curso completo para iniciantes sobre análise de dados

1. Aula 1 Introdução ao curso: Olá a todos, bem-vindos ao curso Excel for Beginners Data Analytics. Meu nome é Sherman, e vou orientá-lo neste curso hoje Este curso foi desenvolvido para dois tipos de alunos iniciantes que nunca usaram o Excel antes e usuários intermediários que sabem como usar a plataforma, mas querem entender o pensamento por trás da análise de dados Neste curso, exploraremos as estruturas lógicas que as empresas usam para avaliar suas habilidades analíticas, seja para uma entrevista de emprego, uma avaliação ou uma tarefa no trabalho Um pouco sobre minha experiência em Excel, comecei a usar o Excel em 2016, no meu primeiro trabalho cooperativo. Quando descobri tabelas dinâmicas e macros. E desde então, tenho usado o Excel em todas as minhas funções. E existem muitos com empregos cooperativos e empregos em tempo integral combinados. Trabalhei em muitos setores diferentes, como bancos e finanças, análise de dados, consultoria, educação , construção e imóveis nos quais estou atualmente. Ao longo dos anos, também ensinei Excel para centenas de usuários, especialmente quando trabalhava como assistente de ensino de ciência de dados de iniciantes a profissionais. Este curso é baseado no princípio 80 20, os 20% da lógica e das funções que impulsionam 80% dos resultados do mundo real. Em vez de abarcar todas as soluções e funções possíveis, neste curso, você aprenderá a pensar e apresentar esse pensamento usando o Excel Ao longo do curso, usarei um Mac, mas compartilharei os equivalentes do Windows para cada atalho O Excel é melhor no Windows, mas eu tenho um Mac. Agora que você sabe o que esperar, vamos começar entendendo o que realmente significa análise de dados. 2. Aula 2 - Introdução à análise de dados: Então, o que exatamente é análise de dados? Simplificando, a análise de dados é o processo de transformar números brutos em insights significativos que ajudam as empresas a tomar decisões. Sempre que você abre um conjunto de dados, você não está apenas olhando linhas e colunas, está procurando uma história As etapas que você seguirá normalmente incluem entender o problema, limpar e explorar o conjunto de dados, analisá-lo e apresentar insights que impulsionam a ação Algo que me ajudou tremendamente em entrevistas e avaliações técnicas, e algo que agora incentivo todos a fazerem é adicionar uma guia de estrutura lógica Vou te mostrar o que quero dizer quando mudamos para o meu laptop. Sempre que você receber uma tarefa, seja no Excel, no Power BI ou em qualquer outra linguagem de codificação, encontre uma maneira de demonstrar sua lógica ou sua estrutura No Excel, por exemplo, você pode adicionar uma guia separada para sua lógica. No Power BI, você pode adicionar uma caixa de texto, em qualquer linguagem de codificação, você pode Mostre ao entrevistador o que você pensa e você quer levá-lo no processo com você Isso faz duas coisas. Mostra ao entrevistador que você pensa logicamente e ajuda a estruturar sua abordagem E ajuda se você esquecer uma fórmula ou ficar sem tempo porque seu raciocínio ainda está visível Eu aprendi isso durante uma entrevista técnica com a empresa Bell. Esqueci a sintaxe da sequência porque estava em pânico, para ser sincero, e também não havia tempo suficiente, mas escrevi toda a minha lógica nos Eu tinha 100% de certeza de que falhei, mas passei a rodada para minha grande surpresa, e o entrevistador me disse que a razão pela qual eu passei foi porque minha lógica era muito clara Ele me incentivou a sempre fazer essa prática, e agora eu digo a todos que façam o mesmo. É claro que alguém que entenda a lógica, seja capaz de demonstrá-la e conheça muito bem a plataforma, sempre terá a vantagem, mas essa é uma dica que você deve usar de qualquer maneira. Na próxima lição, nos familiarizaremos com o Excel, entendendo a área de trabalho e o layout antes de mergulhar nas funções Então, vamos agora para o meu laptop. 3. Lição 3 - Se familiarizar com o Excel: Comece se familiarizando com o layout do Excel. Eu uso o HachiPT para reunir os dados, e ele é pequeno o suficiente para que você possa aprender tudo sem ficar Portanto, o arquivo que você tem à sua frente é chamado de pasta de trabalho e cada guia separada é chamada de planilha Nesse caso, pedidos, produtos e dados do cliente são todas as planilhas da pasta de trabalho E isso é útil quando você começa a automatizar tarefas usando macros VBA Aqui no topo, temos o que é chamado de fita. Esse é o menu que contém a maioria das opções de comando, como formatação de células, inserção de tabelas dinâmicas, uso de filtros, etc E a maioria desses comandos tem atalhos. Você não precisa memorizar todos eles, mas ao longo do curso, mostrarei os que eu mais uso Para ser sincero, não sou especialista em atalhos, então uso meu mouse de vez em quando , mas tento limitar o uso o máximo possível Isso ocorre porque, se você estiver usando atalhos, aumentará sua velocidade e produtividade no Excel Quando estiver focado na análise de seus dados, você não perderá seu processo de pensamento porque precisa mouse e isso simplesmente retarda tudo É muito importante conhecer os principais atalhos e o que você não sabe, pode pesquisar no Google Ok, seguindo abaixo da faixa de comando estão suas linhas de grade Elas são compostas por várias células, e cada célula é referenciada pela letra da coluna e pelo número da linha Então, por exemplo, o ID do pedido está na célula A 1. E se você não se lembra qual é a referência da célula, você sempre pode conferir aqui no canto superior esquerdo onde a referência da célula mudará à medida que você se move pela grade. Você quer se familiarizar com a compreensão das referências das células porque suas funções usarão as referências das células. Então esse é o layout do Excel. O objetivo desta lição é muito simples. Foi só para apresentar a plataforma em si. Então, a seguir, começaremos com a essência do Excel, as funções e os atalhos para que você possa começar a ordenar que os dados funcionem para 4. Aula 4 - Funções e atalhos: Agora que você está confortável com a plataforma em si, vamos começar com funções e atalhos Uma função é um comando que instrui o Excel a realizar uma tarefa específica, por exemplo, somar números ou pesquisar valores. Todas as funções começam com o sinal de igual e existem basicamente dois tipos de funções: funções celulares agregadas e funções celulares individuais Algumas das funções agregadas mais comuns são algumas médias, mínimas e máximas Eles fornecem um valor com base em todo o seu conjunto de dados. E quando você está analisando dados e usando tabelas dinâmicas e pesquisas, você pode não usar essas funções agregadas, mas elas são muito úteis quando você precisa fornecer informações rápidas apresentar dados em uma reunião ou usar números para usar É aí que essas funções são úteis, mas você as usará principalmente quando estiver usando sua planilha do Excel como um item independente Então, se olharmos nosso conjunto de dados, a primeira função agregada que quero mostrar é a função soma Como mencionei antes, para indicar ao Excel que você vai usar uma função, você começa pressionando o sinal de igual e depois começa a digitar a Se eu escrever a soma, você verá que tenho várias opções diferentes e, assim que escrevo a função, preciso abrir parênteses e você verá que Excel informa os parâmetros que está procurando Para sair de uma função, basta pressionar a tecla escape, e eu só quero mostrar uma maneira mais rápida de digitar isso Você pressiona iguais para indicar que esta é uma função, digita a soma e, em seguida, pode pressionar a tecla tab no teclado E ele aparece automaticamente sem que você precise colocar os parênteses Agora, é aqui que você usará referências de células. O que eu quero fazer aqui é somar o preço dos meus primeiros cinco itens, para o preço dos meus primeiros cinco itens, que eu possa simplesmente selecionar meus primeiros cinco itens, fechar os parênteses e Assim, você pode ver que o total dos cinco primeiros itens é 460 Agora, toda vez que você usa uma função e cria um valor agregado ou qualquer número, você deve sempre se perguntar o que esse número significa Agora, se eu copiasse esse número e colocasse em uma apresentação ou o enviasse ao meu gerente, eles não saberiam o que é 460 porque não há uma unidade anexada a esse número Então, novamente, sempre que você vê um número, você sempre quer se perguntar: o que isso significa? No nosso caso, esse é o preço total dos primeiros cinco itens, então eu quero adicionar uma unidade e adicionarei o cifrão aqui, e agora está muito claro que esse é o valor de $1. Da mesma forma, se eu fosse calcular a média dos primeiros cinco itens, clicaria no sinal de igual, começaria a digitar minha função e você veria que o Excel exibe opções diferentes Vou pressionar a tecla tab para que a função se preencha sozinha na célula E, semelhante à função de soma, Excel informará os parâmetros que está procurando para essa função. Novamente, vou selecionar os primeiros cinco itens, fechar os parênteses e pressionar Enter, e você pode ver que a média dos primeiros cinco itens O que esse número significa? Esse número é o total de passos que você percorre hoje ou o número de maçãs que você comprou em uma loja? 92 seria muito, para ser honesto. Mas qual é esse número? O que isso faz? Esse é um preço médio. Então, vamos até essa célula e clicamos no cifrão. Há mais duas maneiras de formatar suas células no Excel. Vou pressionar o Comando Z para desfazer a etapa que acabei Em casa, posso acessar essa opção aqui e selecionar moeda ou contabilidade. E isso traz o cifrão aqui. Outra forma de fazer isso é clicar com o botão direito do mouse na célula. Clique em Formatar vendas. Você pode escolher a categoria que quiser. Nesse caso, podemos usar moeda ou contabilidade. Eu vou usar a moeda e você pode clicar. OK. Outras funções agregadas comuns incluem mínimo e máximo, e você mesmo pode experimentá-las Na realidade, você usaria funções como alguma média para calcular sua receita total ou seu lucro total ou sua receita média um mês ou ano específico. Nesse caso, eu só queria mostrar como usar a função em si. Há muitas maneiras diferentes de usar várias funções diferentes. E se você tiver algumas ideias na cabeça, pode pesquisar algumas funções no Google e experimentá-las sozinho. Por um lado, onde as funções agregadas ajudam você com a parte de análise exploratória de dados, sobre a qual falaremos posteriormente no curso, as funções individuais ajudam você limpeza e análise de parte real do seu conjunto de Então, vamos entrar nisso. Uma prática muito comum é separar o ano e o mês de um campo de data para que você possa observar as tendências ao longo do tempo, e é aqui que funcionam o ano e o mês , essas são funções celulares individuais e, embora essa não seja a primeira etapa que você fará em sua análise, estou abordando isso agora porque realmente quero mostrar a diferença. funções individuais e funções agregadas e como usar diferentes atalhos e algumas dicas de formatação que você deseja usar antes de realmente entrar na análise Portanto, neste curso, aplicaremos funções antes de realmente limpar nosso conjunto de dados. Clique no sinal de igual, comece a digitar aqui. Você pode ver que a opção já aparece para você. Vou pressionar a tecla tab no meu teclado e, usando a tecla de seta, vou para o meu campo de data. Feche os parênteses e você verá que extraímos o ano do campo de data Agora, se eu quiser arrastar e soltar a fórmula, uma opção para fazer isso com o mouse, que eu realmente não recomendo e realmente deixa você mais lento, mas quero mostrar a você de qualquer maneira, é passar o mouse sobre a parte inferior direita da célula e você verá que o cursor muda um pouco E essa é sua indicação de que você está pronto para arrastar sua fórmula até o final. Vou apenas arrastar a fórmula para a primeira instância da célula em branco que temos. Então, você verá que a fórmula foi aplicada a todas as células. Comande Z para voltar. Uma maneira rápida de fazer isso usando o teclado é ir até a coluna mais próxima, à esquerda ou à direita, que estiver preenchida, e pressionar o comando com a tecla de seta para baixo Isso vai para a última célula antes de uma célula em branco. Isso pode ser porque há espaços em branco no seu conjunto ou porque isso leva você até a parte inferior do seu conjunto de Em seguida, você pode voltar para a coluna na qual deseja arrastar as fórmulas para baixo. Você pressiona Command Shift e a tecla de seta para cima, e ela seleciona todo o intervalo em que você deseja preencher suas células com a fórmula, e você pode pressionar Command Command D arrastar sua fórmula para baixo até toda a Eu quero fazer a mesma coisa durante o mês. Então, como eu posso voltar sem usar o mouse é pressionar o comando na tecla de seta para cima, ela vai até o topo da sua planilha nesse caso, não temos espaços em branco , por isso foi para o Se você tivesse espaços em branco no seu conjunto de dados, ele iria para a primeira célula antes de uma linha em branco Igual, comece a digitar no mês, pressione a tecla tab no teclado, vá para a E depois feche os parênteses. Agora, vou voltar para a coluna mais próxima, pressionar o comando e a tecla de seta para baixo para ir a última célula antes de uma linha em branco ou no final da planilha, e depois voltar para a coluna em que estou interessado, Command shift e, em seguida, a tecla de seta para cima para obter o intervalo de células para o qual eu quero arrastar minha fórmula e, em seguida, pressionar Command D. Então é assim que e, em seguida, pressionar Command D. você pode aplicar funções e use atalhos para preencher seu conjunto de dados Agora, isso foi principalmente para mostrar como usar atalhos rápidos Mas se você estiver realmente aplicando fórmulas, a melhor prática é converter suas células em uma tabela, e eu vou te mostrar o porquê Vamos converter nossas células de dados em uma tabela simplesmente usando atalhos Para selecionar uma coluna inteira, você pode pressionar a tecla Controle e Espaço, e isso selecionará toda a coluna. Em seguida, usando o Command Shift e a tecla AERO direita, você pode selecionar todas as colunas preenchidas que você deseja converter em uma E então, para realmente converter esse conjunto de dados em uma tabela, você pode pressionar o Comando T, que seleciona o conjunto que você converterá E então, se você pressionar Enter, verá que seu conjunto foi convertido em uma tabela e verá que o nome da tabela aqui é tabela dois O que posso fazer aqui é simplesmente chamá-lo de pedidos. Agora, voltando ao que fizemos antes, vou aplicar uma função para ano e mês e você verá que o Excel reconhece automaticamente que isso faz parte da sua análise e, portanto, adicionou outra coluna à sua tabela. Então, podemos começar a digitar a função pressionando iguais, e então eu posso digitar , pressionar a tecla tab, selecionar minha célula, que está ao lado dela, onde eu quero extrair o ano, onde eu quero extrair o ano, fechar parênteses E agora você verá que a coluna inteira é preenchida automaticamente e você não precisa arrastar e soltar fórmulas E é por isso que usar uma tabela é tão importante quando você está analisando seus dados, e isso será ainda mais útil quando começarmos a usar tabelas dinâmicas. À medida que avançamos neste curso, apresentarei novas funções que serão muito úteis durante o processo de análise de dados. Mas agora que você conhece as principais funções e atalhos, passaremos para a próxima lição e exploraremos como filtros e linhas em branco afetam a qualidade dos dados 5. Aula 5 - Filtragem e linhas em branco: Agora que conhecemos as principais funções e atalhos, é hora de passar para filtros e blancros, pois a forma como você filtra e estrutura seus dados determina a precisão de tudo o que A filtragem é importante porque permite encontrar rapidamente grupos específicos de dados que compartilham os mesmos recursos Por exemplo, todos os pedidos feitos no ano de 2024 ou todos os itens que foram devolvidos Assim, você pode agrupar as coisas rapidamente e há duas maneiras de aplicar um filtro. A primeira está na guia inicial, em classificar e filtrar. E a segunda maneira é entrar na guia de dados e clicar em filtrar. E você saberá que um filtro foi aplicado ao ver as setas nas pequenas caixas nos cabeçalhos Agora, a forma como apliquei um filtro não é melhor prática porque há espaços em branco em nosso conjunto Você precisa ter muito cuidado quando se trata de linhas em branco, porque na minha demonstração, meu conjunto de dados é muito pequeno Você pode ver claramente que a linha 17 é uma linha em branco, com exceção do sim na devolução do item, que era claramente apenas uma falha na entrada de dados Mas quando você tem grandes conjuntos de dados, talvez não saiba se há espaços em branco no seu. Se você simplesmente aplicar um filtro e você simplesmente aplicar um filtro começar a trabalhar no seu conjunto de dados, não saberá que não está aplicando as mesmas fórmulas em todo o Porque quando você simplesmente clica no filtro, o filtro só é aplicado ao primeiro conjunto de dados antes do Corvo em branco Então, o que você quer fazer, não importa quão pequeno seja seu conjunto de dados, porque você quer desenvolver a força de fazer as coisas da maneira certa, você quer clicar na seta no canto superior esquerdo E o que isso faz é selecionar todo o seu conjunto de dados. Outra coisa que você pode fazer é pressionar o Comando A e depois o Comando A novamente para selecionar a planilha inteira e clicar em Filtrar. Os filtros serão aplicados somente às células ativas em sua planilha No meu caso, eu tinha alguns dados nas colunas INJ. Eu estava testando algo. E é por isso que os filtros foram aplicados nas colunas INJ Normalmente, quando você seleciona todo o conjunto de dados, as únicas colunas às quais os filtros serão aplicados serão suas colunas ativas Agora, eu poderia ter adicionado isso como uma indicação rápida em um dos meus outros vídeos, mas optei por manter esse tópico separado por causa da importância e da dor que me causou nos meus primeiros anos de uso do Excel Eu teria um grande conjunto de dados quando trabalhava em uma startup de construção. E eu simplesmente cliquei no filtro. Trabalhei muito e apliquei tantas fórmulas no meu conjunto de dados apenas para descobrir que só fiz isso em menos da metade , porque não conseguia ver que havia linhas em branco Isso realmente faz a diferença, especialmente quando você está classificando as coisas, quando você está filtrando tudo Portanto, quanto maior for o seu conjunto de dados, mais confuso ele pode ser Por isso, você quer desenvolver a força de fazer as coisas da maneira certa para que, não importa quão pequeno ou grande seja seu conjunto de dados, você sempre tenha certeza de que suas práticas de dados são precisas e de que está chegando às conclusões certas e ao resultado certo E por esse motivo, vou repetir rapidamente o que eu disse sempre que você quiser aplicar filtros, para garantir que todo o conjunto de dados seja selecionado Você pode fazer isso pressionando o Comando A duas vezes ou clicando na seta no canto superior esquerdo. Outro atalho é pressionar Control e Shift. Para selecionar sua coluna inteira, você pode clicar em Command Shift e na seta para a direita para selecionar todas as colunas ativas e pressionar Command Shift F para aplicar filtros usando atalhos Depois que seus filtros forem aplicados, você poderá verificar algumas colunas para garantir que seus dados estejam corretos. Por exemplo, posso inserir valor e ver se tenho um não. Como abordaremos a limpeza de dados em breve em um dos próximos vídeos, não vou fazer isso agora, mas a filtragem é uma ótima maneira também identificar erros de verificação Tudo bem, agora você aprendeu o básico. Você sabe onde está tudo no Excel. Na próxima lição, definiremos uma declaração de problema real para unir tudo o que aprendemos até agora e ver como a lógica orienta a análise do início ao fim. 6. Lição 6 - Declaração do problema da análise de dados: Agora que aprendemos a usar filtros e limpar estradas em branco, vamos passar para a próxima etapa mais importante na análise de dados, que é definir a descrição do problema. Sempre que você obtém um conjunto de dados, seja para uma entrevista, um estudo de caso ao vivo ou até mesmo para seu trabalho diário, a primeira coisa que você deve fazer é fazer a pergunta: o que exatamente estou tentando resolver Quando eu costumava receber entrevistas técnicas ou avaliações do Excel e, mais tarde, quando estava criando avaliações do Excel para entrevistar outras pessoas, percebi algo muito interessante As pessoas foram direto para as fórmulas, mas as pessoas que se saíram muito bem sempre começaram escrevendo o que estavam tentando alcançar Em uma caixa de texto, em uma guia comum ou em uma guia separada Você não precisa fazer isso no seu dia a dia de trabalho, mas se estiver fazendo uma avaliação de entrevista, recomendo adicionar outra guia chamada estrutura lógica, e você pode usá-la para descrever seu processo de pensamento, suas suposições e suas etapas Veja por que isso é importante. Isso demonstra seu raciocínio lógico antes mesmo você apresentar seu trabalho aos entrevistadores, e é isso que eles estão procurando, mas quase nunca E a segunda é que, se você esquecer uma fórmula ou sintaxe, seu raciocínio ainda brilha Portanto, ter uma compreensão clara da descrição do problema e defini-la antes de começar a fazer qualquer trabalho será um divisor de águas Para este curso, temos três guias, pedidos, dados do produto e do cliente, e nossa descrição do problema é baseada no desempenho do produto e nos dados do cliente onde a empresa está tentando expandir a seguir E tentaremos responder a essa pergunta até o final do curso. Agora que sabemos o que estamos procurando, vamos começar a explorar os dados e ver quais histórias eles estão tentando nos contar. 7. Aula 7 - Análise de dados exploratórios: Há um ditado na análise de dados que diz que 80% do trabalho é limpar e entender os dados e apenas 20% está analisando. Portanto, antes de criarmos qualquer gráfico ou identificarmos tendências, vamos começar explorando o que está à nossa frente. Análise exploratória de dados ou EDA: você está conhecendo seu conjunto de Você se faz perguntas como: Quais colunas eu tenho? O que significa cada um? Os tipos de dados estão corretos? Há algum valor ausente ou padrões estranhos? Então, por exemplo, se olharmos para o conjunto de dados à nossa frente, podemos ver claramente que há alguns valores em branco em nosso conjunto de dados que podem nos causar problemas no futuro E isso é algo que abordaremos na parte de limpeza. Também quero ver se há outros espaços em branco no meu conjunto Então, usando filtros que aprendemos em nossa lição anterior, vou ver rapidamente se há algum erro ou células em branco. Vou escolher qualquer coluna aleatória para procurar isso, e tudo bem, não só há espaços em branco em nosso conjunto de dados, mas há um não aleatório na coluna de quantidade, o que é claramente um erro de entrada de dados porque essa coluna só deveria aceitar números, mas há um não nessa Portanto, há claramente alguns erros, mas também há espaços em branco. Então, abordaremos os dois em nossa peça de limpeza. Se seu conjunto de dados for maior e mais confuso, você também quer ter certeza de que os tipos de dados estão corretos porque o texto não pode ser adicionado Então, essas são coisas que eu observaria ao explorar meu conjunto Outra coisa que podemos observar talvez seja a idade em nossa guia de dados do cliente. O que posso fazer rapidamente é selecionar a coluna, posso entrar em Inserir e adicionar um gráfico rápido pois os gráficos são uma ferramenta muito boa para ajudar você a entender melhor seus dados. Então, aqui podemos ver que a idade é de 25 a 70 anos, em algum lugar de 25 a 70 Mas há um claro pico em que o valor é 460. Agora, isso é um caso atípico e, claramente, um erro. É muito normal ter valores discrepantes em seu conjunto de dados, mas você precisa decidir se faz sentido removê-los ou manter esse valor discrepante. Você não deseja remover valores reais do seu conjunto de dados para que ele discrepante. Você não deseja remover valores reais do seu conjunto de dados para tenha Você deseja remover os erros, como erros claros. Portanto, mesmo nas zonas azuis do mundo, ninguém viveu até os 460 anos Portanto, isso é claramente um erro de entrada de dados, e isso é algo que removeremos na peça de limpeza. Novamente, você quer ter certeza de não remover todos os valores discrepantes porque, por exemplo, se estivéssemos vendendo nossos produtos apenas nos EUA e você visse que há um produto vendido no Canadá Agora, isso pode significar duas coisas. Ou isso é um erro ou talvez nossa empresa esteja tentando se expandir no Canadá e tenha acabado de começar a vender produtos. Então, se você simplesmente remover aquela peça, isso seria incorreto. Nesse caso, entretanto, isso é claramente um erro. Então, em nossa peça de limpeza, novamente, vamos apenas remover isso. É um benefício da peça de análise exploratória de dados. Você se familiariza com seu conjunto e pode ter uma ideia do trabalho necessário nas próximas etapas A lógica por trás da análise sempre permanece a mesma Não importa qual ferramenta você usa Excel, SQL Python, você está sempre tentando converter números brutos em insights significativos Se você estiver usando o Excel para analisar qualquer tipo de dado, o conjunto de dados provavelmente é gerenciável porque, para conjuntos de dados maiores, o Python SQL é uma ferramenta melhor, mas Excel ainda é um software muito dominante em setores como finanças Por isso, é muito importante que você também entenda como pensar e aplicar esse pensamento no Excel. O arquivo do projeto do Excel está anexado abaixo e espero que você esteja acompanhando. Para este projeto, nosso foco será a expansão com base no desempenho do produto, e essa é a lente que manteremos nas próximas aulas. Em seguida, começaremos a limpar nosso conjunto de dados, lidar com linhas em branco, cortar espaços e remover duplicatas para garantir que nossa análise permaneça a mais precisa possível 8. Aula 8 — Limpeza de dados: Agora chegou a hora de uma das partes mais importantes, que é limpar nossos dados. Muitas vezes, você obtém exportações brutas com textos inconsistentes, entradas duplicadas, linhas em branco, e esse é um processo em que você pode começar a limpar essas coisas Como mencionei anteriormente, eu intencionalmente mantive esse conjunto muito pequeno para que você não ficasse sobrecarregado, mas ainda analisamos os diferentes aspectos que você deve Você está limpando seu conjunto de dados. E falarei sobre meu pensamento sempre que estiver analisando dados. Então, em primeiro lugar, queremos remover as linhas em branco do nosso conjunto de dados porque, como falamos anteriormente, linhas em branco podem causar muitos problemas, especialmente com filtros, especialmente com filtros, arrastar fórmulas e coisas Então, usando filtros, podemos simplesmente selecionar os espaços em branco em nosso conjunto Queremos excluir a linha inteira e, para fazer isso, mantenha pressionada a tecla Shift e o espaço, e ela seleciona a linha inteira E, em seguida, segurando Shift, use a tecla de seta para baixo para selecionar todas as células que você deseja remover, e Command minus permitirá que você exclua essas linhas Então, podemos remover o filtro aqui. E antes de fazer qualquer outra coisa, quero converter meu conjunto em uma tabela porque, como vimos antes, é muito fácil arrastar fórmulas e simplesmente brincar com coisas diferentes quando seu conjunto de dados está em formato de tabela, pois isso remove muitas das coisas manuais que talvez você precise fazer Vou selecionar meu conjunto de dados, Command shift e a tecla de seta para a direita e, em seguida, segurar Command Shift, a tecla de seta para baixo me permite selecionar todo o meu conjunto de dados, e tenho certeza de que desta vez ele desceu para a última linha só porque não há espaços No meu conjunto de dados. Então, o comando, T e Enter converterão isso em uma tabela. Durante o processo de EDA, notei que havia algumas inconsistências na Eu sei que há um valor de texto que é não e, obviamente, esse é um valor incorreto. Este é um exemplo muito bom para você entender que saber qual é o seu objetivo para qualquer tarefa que você esteja fazendo é muito importante. Agora, se eu não estivesse analisando meus dados para entender onde posso expandir minha empresa com base no desempenho do produto, eu teria adotado uma abordagem muito diferente. Se eu estivesse apenas coletando dados e me certificando de que meus dados estavam completos, eu voltaria e procuraria o ID do pedido em qualquer software em que estivesse coletando todas essas informações ou, se eu tivesse um banco de dados, eu iria até lá e me certificaria de saber a quantidade de itens que foram comprados nesse pedido no pedido 68. O motivo pelo qual isso é claramente um problema de entrada de dados é porque um não foi adicionado. Deveria ter havido um número. E parece claramente que foi uma exportação de outra plataforma na qual estamos coletando nossos dados de pedidos. Então, eu entraria nessa plataforma e substituiria o não pelo valor real, porque quero ter certeza de que não estou perdendo nenhum dos meus valores. No nosso caso, realmente não temos acesso à plataforma em que todos os pedidos foram coletados. E então, obviamente, não podemos substituir esse valor. O que vou fazer é excluir isso do meu conjunto de dados. Mas eu só queria deixar claro que é importante que você entenda o que está fazendo, mais do que apenas seguir as etapas porque suas decisões podem mudar com base na tarefa. Então, vou selecionar esse item de linha usando shift in space e Command minus Digite para remover isso. Agora vou verificar rapidamente se tudo está bem. Não há nada no preço, o item devolvido também é apenas sim e não, então todo o resto parece ótimo. Uma coisa que eu quero fazer é calcular a receita. Você pode fazer isso na próxima etapa ou posteriormente, mas prefiro fazer isso agora, enquanto estou limpando meu conjunto de dados, porque sei que tudo está limpo no momento e também sei qual é meu objetivo Portanto, a receita é uma métrica importante que eu gostaria. Então, o que eu posso fazer é adicionar uma coluna aqui. Logo antes da devolução dos itens e após o valor do preço, posso renomear isso como receita, e a receita é igual a iniciar É o valor vezes o preço para dar o valor total. Agora, se isso não fosse uma tabela, também teríamos que arrastar e soltar nossas fórmulas, mas isso ocorre porque nossos dados estão em formato de tabela, não precisamos fazer isso Agora, o valor, o preço e a receita, ao que parece, têm o mesmo tipo de dados, mas há dois itens muito diferentes. quantidade é apenas a quantidade de coisas que um cliente comprou, e o preço e a receita são valores em dólares. Então, quero mudar isso porque quero estar muito familiarizado com meu conjunto e realmente quero entender quais são os diferentes tipos de dados, porque os cálculos que farei serão baseados nos tipos de dados Às vezes, o que acontece, na verdade, na maioria das vezes, quando você está exportando seus dados, os tipos de dados podem estar incorretos Portanto, você pode obter valores numéricos em formatos de texto. O que isso faz é que não permite que você execute operações específicas como uma divisão média de multiplicação no texto, porque, obviamente, não é um valor numérico Então, você quer ter certeza de que seu tipo de dados está sempre correto para que você possa realizar todas as diferentes operações matemáticas que você gostaria de fazer com base no que você está calculando Nesse caso, selecionarei as duas colunas e depois as converterei em valores em dólares. Você pode ver as hashtags ou os sinais numéricos. Não sei como esse símbolo se chama. Eu chamo isso de hashtag, mas isso significa apenas que a largura da sua coluna não é suficiente para exibir totalmente o número aqui Assim, você pode passar o mouse sobre aquela coluna específica, onde você pode ver a linha e as duas setas, e você pode clicar duas vezes nessa coluna e ela se expandirá totalmente para mostrar o número inteiro E então o número ou o sinal de hash será removido. Outra coisa é que quero extrair o mês do campo de data e, usando a função mês, posso extrair o mês. Então, vou começar a digitar a função, clicar em tab e entrar no campo de data, fechar parênteses e inserir, e veremos que agora nossa coluna foi preenchida Ok, a seguir, estou na aba do produto. E o que eu quero fazer aqui é procurar duplicatas, porque talvez durante o processo de entrada de dados, tenhamos adicionado vários itens E essa também é uma verificação pontual para garantir que não foram adicionados itens incorretos ou preços incorretos , ou talvez tenhamos o mesmo ID do produto, coisas assim. É aqui que vou procurar essa parte. Então, selecionando a coluna inteira em home, entrarei na formatação condicional e selecionarei valores duplicados E então clique em OK. Então, posso ver que tenho duas duplicatas Um é um quadro branco e ambos são brancos, ambos custam $100. Então, essa foi apenas uma entrada que foi adicionada por engano. Então, vou apenas comandar menos remover ou excluir esta coluna O próximo item duplicado é A 112. Agora, isso é um erro porque o mesmo ID faz referência a dois produtos diferentes Um é verde para confiança e o outro é preto para confiança. Para o nosso caso, assumirei que essa foi uma entrada incorreta e também removerei esse valor duplicado Certo, agora que não temos duplicatas, vou para a última guia, que são os dados do nosso cliente Instantaneamente, posso ver que há alguns espaços extras nas colunas de nomes, o que não é bom. Espaços podem ser um grande problema quando se trata de pesquisar valores, porque quando você está pesquisando valores usando a correspondência ou pesquisa do índice V Lou, que abordaremos nas próximas partes do curso, as pesquisas são sensíveis aos caracteres Qualquer caractere extra, um espaço é um caractere, uma letra é um caractere, uma vírgula é um caractere Lou pode ficar confusa sobre o que você está tentando fazer e ela não fará a mesma coisa Então Liam Johnson em Cell two é diferente do SpaceSpace Liam Por esse motivo, precisamos garantir que não tenhamos espaços extras incorretos em nosso conjunto Outra coisa que eu quero fazer é combinar nome e sobrenome em um, então vou usar a função concat, e você provavelmente usará muito essa função, especialmente com algo assim Se você estiver coletando dados de clientes, é muito comum pedir o nome e o sobrenome separadamente. Mas para o conjunto de dados, é muito útil ter o nome completo do cliente, mesmo que você nunca use o nome do cliente como uma função de pesquisa Como valor de pesquisa, desculpe, você usará o e-mail do cliente ou ID do cliente, pois esse é sempre um valor verdadeiro e exclusivo. Então, primeiro, vou juntar esses dois usando a função de concatenação Antes de fazer isso, quero converter isso em uma tabela para facilitar minha vida. E agora posso adicionar outra coluna, chamá-la de nome do cliente e, usando a função concat, juntarei essas duas Eu posso iniciar uma função pressionando o sinal de igual. Concat, você pode ver que ele já aparece, aperte a aba. Agora, o Concat usa textos diferentes como parâmetros, como você pode ver na caixa de ajuda da função logo abaixo, e considera cada caractere Então, neste caso, usaremos Emma e seguida, precisamos um espaço entre o nome e o sobrenome, então temos que especificar o espaço também e o sobrenome. Feche os parênteses e podemos ver que a fórmula foi arrastada até o final, mas ainda temos espaços extras que não queremos Portanto, agora posso usar outra função para remover os espaços extras, que é a função de corte A função de corte pode ajudá-lo a remover todos os espaços extras e, em seguida, fechar os parênteses e pressionar Enter, para que possamos ver que todos os espaços extras foram Agora que não precisamos do nome do nosso cliente, posso simplesmente excluí-lo e você verá algo interessante acontecendo. Portanto, posso ver que, assim que removo a coluna do nome do cliente, recebo um erro de referência porque a célula aqui tem uma fórmula anexada à coluna que acabamos de excluir. E, portanto, ele não sabe mais onde se referir. E isso é muito importante quando você está trabalhando com pastas de trabalho diferentes, especialmente se você estiver criando uma planilha do Excel para enviar a outra pessoa, se ela não tiver acesso a todas as planilhas ou a todas as pastas de trabalho que você referenciou. Em sua planilha final, Em sua planilha final, eles verão Portanto, é extremamente importante que você os converta em valores e não os mantenha como fórmulas Em nossa versão anterior do pedido Stab, não preciso fazer isso com a receita porque sei que o valor e o preço sempre estarão lá, e eles estão na mesma Mas, nesse caso, na guia de dados do cliente, estamos usando uma coluna da qual não precisaremos. Então, se eu voltar pressionando o Comando Z, agora temos a fórmula do nome do cliente lá E o que vou fazer é pressionar as teclas shift e down para selecionar a coluna inteira Vou copiar e clicar com o botão direito do mouse em Colar valores. Agora eu posso ver que não é mais uma fórmula aqui. Ele colou todos os valores. Agora, se eu excluir essa coluna, você verá que nada acontece e nosso conjunto de dados está bom Não fica louco porque não está mais fazendo referência a outra coluna Então, eu posso mudar isso para o nome do cliente. E, honestamente, eu nem preciso do nome e sobrenome, então posso simplesmente deletar isso A próxima coisa que quero fazer é abordar a discrepância em nossa coluna de idade Decidimos na fase de EDA que isso é algo que removeremos do nosso conjunto de Novamente, você não remove todos os valores discrepantes. Você precisa realmente entender o que é esse outlier. No nosso caso, esse é um valor incorreto certeza, porque ninguém pode viver até os 460 anos Talvez haja algum avanço tecnológico que aconteça no futuro. Mas agora, até hoje, ninguém pode viver até 460 Mesmo nas zonas azuis, o máximo que alguém viveu é, eu acho, 120 anos ou algo assim. Portanto, esse é um erro claro e, por esse motivo, vou removê-lo do nosso conjunto de Se eu achasse que isso era algo realmente atípico com base nos dados que inserimos ou nos dados que coletamos , você não o removerá Todos os valores discrepantes não são removidos do seu conjunto de dados. Somente aqueles que são erros claros são removidos. Então mude o espaço para selecionar e depois comande menos, ok? E agora eu posso limpar os filtros. Esse processo não foi muito difícil porque nosso conjunto de dados é muito pequeno e bastante limpo, na verdade, para os padrões de big data Isso nem é considerado próximo ao big data, mas os dados podem ser muito, muito confusos. Mas a abordagem geral permanece a mesma. Você está sempre procurando valores duplicados para tipos de dados incorretos, para linhas em branco Essas são as principais coisas que você procurará. Você abordará valores atípicos, coisas assim. Por isso, abordamos a maioria das coisas que você precisa saber ao limpar seu conjunto Mas, obviamente, quanto maiores os dados, mais confusos eles podem ser Inclinar-se leva tempo, e você pode ficar impaciente porque quer fazer coisas sofisticadas Mas lembre-se do que eu disse antes : análise de dados é 80% compreender, limpar, explorar os dados e apenas 20% analisar. E se você gastar tempo limpando os dados e entendendo os dados, ficará muito feliz consigo mesmo quando precisar fazer outras coisas. Agora que nossos dados estão limpos, podemos começar a conectar planilhas diferentes e reunir tudo usando pesquisas E a primeira pesquisa que usaremos é V Lookup. 9. Aula 9 VLookup: Agora que nossos dados estão limpos e prontos para serem usados, é hora de falar sobre uma das funções mais úteis e poderosas do Excel, que são as funções de pesquisa. As funções de pesquisa permitem que você conecte informações em várias planilhas e pastas de trabalho, por exemplo, conectando IDs a informações reais do produto, IDs de clientes a informações reais do cliente, e ajudam você a organizar e reunir seus dados de forma que sua análise seja perfeita e mais Neste curso, falaremos sobre três funções de pesquisa. O primeiro é V Lou, que é o clássico e mais fácil de entender Segunda correspondência de índice, que é mais flexível e poderosa do que Vu E a terceira é a pesquisa, que é a função de pesquisa moderna, completa e flexível, disponível nas versões mais recentes do Excel Começando com V lookup, é a abreviação de vertical lookup, o que significa que ele procura um valor verticalmente até encontrar uma correspondência Digitando a função aqui, você verá que a pesquisa V requer três parâmetros obrigatórios O primeiro é o valor de pesquisa, que é o valor que você está usando para pesquisar outro valor, a matriz da tabela é onde está seu valor final e o número do índice da coluna é o número da coluna em que seus dados estão. Então, essas são as três coisas que você precisa inserir para que o Lookup funcione. Então, usando-o em nosso conjunto de dados, vamos examinar as diferentes guias para ver quais informações usaremos Aqui temos o ID do pedido, o ID do produto e o ID do cliente. Então, vamos primeiro acessar a guia do produto. Podemos ver que na guia do produto, temos o nome do produto cor do produto e o preço unitário. Acredito que já temos o preço, sim, já temos o preço, então não precisamos trazê-lo da guia do produto, mas não temos o nome do produto. Temos apenas o ID do produto e, para melhorar nossa análise, queremos que todas as informações das três guias sejam combinadas em uma guia Então, vamos trazer os dados das guias de dados do produto e do cliente para a guia Pedidos Então, a primeira coisa que queremos é o nome do produto. Começaremos digitando a função. O primeiro parâmetro é o valor de pesquisa. Esse é o valor que usaremos para pesquisar outro item. Vamos usar o ID do produto, que é o valor de pesquisa para procurar o nome do produto. Portanto, no nosso caso, nosso valor de pesquisa é o ID do produto a 108. E depois de adicionarmos o parâmetro, é hora de adicionar o próximo parâmetro, você pressiona vírgula e agora ele está solicitando a matriz da tabela Agora, essa é a tabela na qual estão nosso valor de pesquisa e nosso valor final, porque usaremos essa matriz de tabela específica para obter as informações. Portanto, nossa matriz de tabelas é obviamente a guia do produto, e essas são as quatro colunas que usaremos para nossa matriz de tabelas. Agora, o importante para pesquisa V é que nosso valor de pesquisa, que é a ID do produto, esteja na coluna mais à esquerda Se não estiver na coluna mais à esquerda ou pelo menos à esquerda da coluna em que estamos interessados, não funcionará. Então, como o nome do produto é o que queremos, queremos ter certeza de que o ID do produto, que é nosso valor de pesquisa, esteja à esquerda. Nesse caso, é. Então, vamos pressionar a vírgula novamente e agora ele está solicitando o número do índice da coluna Então, começando pela esquerda e incluindo a coluna de pesquisa, começaremos a contar. Então, A é o número um e B é o número dois. E como o nome do produto é o que estamos procurando, vamos colocar dois e fechar parênteses e E então você verá que automaticamente ele arrastou a fórmula até o fim porque é uma tabela Agora, a razão pela qual eu não gosto do V Lou e o motivo pelo qual não o uso menos que eu tenha um conjunto de dados muito pequeno, e eu só queira obter informações rapidamente, e sei que nunca mais vou examiná-las , é porque não é nada flexível Nesse caso, nosso conjunto de dados é tão pequeno que não importa o tipo de função de pesquisa que estamos usando Nosso ID do produto está à esquerda. É o cenário perfeito, mas nem sempre é o caso. Se o ID do nosso produto estivesse em algum lugar intermediário e tivéssemos muitas informações diferentes, seria preciso muito tempo para organizarmos primeiro nosso conjunto para garantir que nosso valor de pesquisa estivesse à esquerda, seria uma perda de tempo Outro problema é que se nosso conjunto de dados estiver mudando de alguma forma, ele poderá interromper a função Por exemplo, se eu adicionasse uma coluna extra antes do nome do produto, você verá que agora nossa função de pesquisa V não funciona mais porque está procurando a coluna dois. coluna dois está vazia agora e, portanto, não está puxando nada Portanto, não é flexível e não é dinâmico, é por isso que eu não sou muito fã do V, mas também vejo que pode ser muito útil se você tem um conjunto de dados muito pequeno e talvez esteja criando uma apresentação e queira rapidamente algumas respostas e não se importa se mais colunas são inseridas, excluídas, você não se importa com isso, então V Loup é perfeito Então, voltando se eu excluir esta coluna ou se eu apenas pressionar o Controle Z, você verá que agora a fórmula funciona bem. Loup é extremamente simples. Certifique-se de trabalhar comigo usando o arquivo do projeto , pois isso realmente ajuda a fortalecer seus conceitos. Então, falamos sobre os prós e os contras do Lookup, mas quando você tem conjuntos de dados maiores, você gostaria de mais flexibilidade e precisaria uma função mais poderosa para esse propósito, e é aí que entra o Index Match Então, na próxima lição, veremos como Index Match oferece mais controle sobre seu conjunto 10. Aula 10 Combinação de índice: Agora que falamos sobre Lou, vamos passar para a próxima função, que é Index Match Index Match costumava ser minha combinação favorita porque é flexível e poderosa, e falaremos sobre por que minhas preferências mudaram em alguns minutos. Ao contrário do V Lou, que só puxa os dados para a direita do valor de pesquisa, Index Match pode extrair dados de qualquer direção e não quebra se você fizer alguma alteração na coluna Em 2016, quando eu trabalhava na startup Construction, aprendi a correspondência de índices examinando um parágrafo inteiro de funções diferentes e dividindo-as uma por uma para ver o que cada uma significa, e foi assim que aprendi essa função. As funções aninhadas podem parecer um pouco complicadas, mas na verdade não são, e oferecem uma compreensão muito boa de como as funções gerais funcionam no Excel Então, vamos começar com a função match primeiro. Para essa função, extrairemos o nome do cliente em nossa guia de dados do cliente usando ID do cliente como valor de pesquisa Então, vamos escrever o nome do cliente aqui, e vamos usar primeiro a função match para que você saiba o que a função match retorna. É igual à partida e você pode pressionar a tecla tab para que ela seja preenchida. Ele está solicitando dois parâmetros obrigatórios, o valor de pesquisa e a matriz de pesquisa. Portanto, o valor da pesquisa será o ID que usaremos para extrair o nome do cliente, e a matriz de pesquisa será a coluna em que o ID está. Portanto, o cliente um é nosso valor de pesquisa. E se entrarmos na guia de dados do cliente, nossa coluna A é onde está o ID do cliente. E, em seguida, feche o parêntese e pressione Enter. Então, a função de correspondência retorna números, o que isso significa? O cliente 001 devolveu dois. Então, se entrarmos na guia de dados do cliente, podemos ver que o cliente 001 está na segunda linha. Vamos dar uma olhada em alguns outros. Volte para os pedidos e o cliente nove devolveu nove. Se analisarmos os dados do cliente, cliente nove estará na nona linha Então, isso significa que a função match está retornando os números das linhas onde está seu valor de pesquisa. Agora que sabemos o que a função de correspondência faz, vamos passar para índice igual a índice e, em seguida, você pode pressionar Tab, ela está solicitando dois parâmetros obrigatórios A primeira é a matriz, e é aí que está nosso valor final. Então, no nosso caso, é o nome de um cliente na guia de dados do cliente. Em seguida, está solicitando o número da linha, que, se você se lembra, obtivemos da função de correspondência. Então, em vez de adicionar um número de linha, vamos usar a função match para pegar o número da linha para nós. Então, vamos analisar os dados do cliente. Em seguida, selecione o nome do cliente como a coluna que queremos, agora ele está solicitando o número da linha. E se você se lembrar de que usamos a função de correspondência para obter o número da linha para nós, não faremos isso manualmente. Usaremos a função de correspondência aqui para que ela possa pegar o número da linha para nós. Se eu digitar match, ele solicitará um valor de pesquisa, entrarei nos pedidos e pego o ID do cliente porque queremos informações do cliente, e o ID do cliente é a chave primária na guia de dados do cliente. E está solicitando a matriz de pesquisa. Onde está a ID do cliente na coluna ID do cliente. Uma coisa que esqueci de mencionar quando estava mostrando a função de correspondência é que você precisa especificar se é uma correspondência exata ou não Então, podemos fazer isso pressionando e zero, o que mostra que é uma correspondência exata Você fecha os parênteses e agora isso fecha a função Então, quando você fecha os parênteses, agora nossa função de correspondência indica à função que a correspondência está fechada, e agora você fechará a função de índice adicionando outro parêntese Pressione Enter e você verá que o nome do cliente foi adicionado. Não sei por que isso é um NA, cliente 005, cliente 00o Veja, não há nenhum cliente 005, e é por isso que está nos dando um NA Não tenho certeza de onde veio essa informação. Talvez quando eu estava limpando o conjunto de dados, essa poderia ter sido a pessoa de 460 anos que removemos do nosso conjunto de Então, o que posso fazer é deslocar o espaço para selecionar e comandar menos remover isso do nosso conjunto de Novamente, se você estivesse fazendo isso na vida real, voltaria ao software ou a qualquer plataforma, coletaria seus dados, pesquisaria as informações e veria se consegue obter os dados originais para colocar em seu conjunto E às vezes é só um erro, então você pode removê-lo do seu conjunto de dados se for um erro Mas, por padrão, não presuma que, se algo estiver faltando, é um erro ou, se algo for atípico, é um erro Isso nem sempre é o caso. Então você viu como a função de correspondência de índice funciona porque é mais complicada do que a Vu Faremos outra e garantiremos que você acompanhe seu arquivo de projeto Então, agora o que eu quero é a cidade. Posso simplesmente digitar a cidade do cliente e começar digitando a guia de teclas da função de índice para que ela seja preenchida Está pedindo a matriz, a matriz final onde está seu destino. O valor que você está procurando, onde está? Está na guia de dados do cliente e na coluna da cidade porque estamos procurando a cidade. Pedindo o número da linha. É aqui que usaremos nossa função de correspondência. Precisamos do valor de pesquisa, que está na guia Pedidos. Obviamente, é o primeiro valor de pesquisa porque essa é uma função individual e queremos que ela se aplique a todos os valores. Não é uma função agregada. Ele está solicitando a matriz de pesquisa. A matriz de pesquisa é nosso valor de pesquisa, como onde está nosso valor de pesquisa, que está na coluna ID do cliente. Queremos uma combinação exata. Feche a função de correspondência com o primeiro parêntese e, em seguida, feche a segunda função com o segundo Pressione Enter e agora temos a cidade para todos os nossos clientes. Sempre que estiver fazendo pesquisas, você quer fazer verificações pontuais rápidas Então, o que vou fazer é garantir que as informações estejam corretas. O cliente 002 está em Londres, o que é correto. Agora, da forma como essa função está configurada, não importa onde esteja sua coluna de ID ou sua coluna de pesquisa, pois ela pode estar em qualquer lugar, pois você está apenas selecionando a coluna e especificando onde está seu valor de pesquisa A função não será interrompida se estiver à esquerda ou à direita. E o segundo problema que tivemos com Vlookup foi que se você inserisse ou excluísse alguma coluna, sua função seria interrompida Então, vamos testar isso também. Se eu entrar nos dados do cliente, o nome do cliente foi um dos valores que extraímos. Então, se eu inserir uma coluna aqui e voltar aos pedidos. Então, você verá que a coluna não quebrou, e essa é uma das flexibilidades que o Index Match oferece, o que pesquisamos que não Há duas desvantagens principais com a função de correspondência de índices. Uma é que é um pouco complexo e muito difícil de escrever. Mas se você praticar o suficiente, poderá resolver esse problema facilmente. E a segunda é que, se houver algum erro ou se houver algum ID ausente no seu conjunto de dados, ele não retornará nada Então, se você se lembra, quando tivemos o cliente 005, adicionando isso novamente, cliente CST 005, obtivemos um Obviamente, não há identificação, então isso está lhe dando um erro. Mas se você quiser especificar o que fazer se houver um erro, precisará usar a função if error e poderá dizer não encontrado. E feche os parênteses. Então, eu basicamente adicionei outra função chamada if error e, em seguida, especifiquei o que eu gostaria se houvesse um erro. Deixe-me fazer isso de novo. Então, eu basicamente entro aqui e escrevo se error, que é outra função, pressiono tab para que ela se preencha, ela adiciona automaticamente os primeiros parênteses e os dois valores necessários. Desculpe, os dois parâmetros necessários são o valor, que é o que estamos obtendo da função de correspondência de índice E então está perguntando o que acontece se o valor retornado for um erro? Então você pode pressionar a vírgula, então ele vai para o segundo parâmetro E então talvez você possa digitar não encontrado. Feche os parênteses e pressione Enter. Portanto, se não houver nenhum valor encontrado , você pode dizer não encontrado, mas precisará usar outra função para isso. Então essa é a segunda desvantagem da função de correspondência de índice Mas há outra função de pesquisa chamada X Lou, que resolve todos os problemas de V Lou e todos os problemas de correspondência de índices e os combina em uma função nova, mais flexível e mais poderosa Então, agora que entendemos o conceito e conhecemos a versão mais complicada das pesquisas, vamos passar para a pesquisa X. 11. Aula 11 XLookup: Vimos V Lou e Index Match. Agora vamos dar uma olhada na função de pesquisa mais nova e poderosa do Excel, que é X Lou X Lou foi projetado para corrigir todas as limitações da pesquisa V e da correspondência de índice, mantendo a fórmula curta e simples Antes de vermos o que o X Lookup faz, vou apenas ocultar algumas colunas para que você possa vê-las claramente e ficar mais fácil para mim editá-las posteriormente. Tudo bem, perfeito. Então, usaremos o Xo up para obter o país do cliente. Na guia de dados do cliente. Digitando a função de pesquisa X, podemos ver que ela requer quatro parâmetros obrigatórios O primeiro é o valor da pesquisa, que nesse caso é a ID do cliente, pois o uso da ID do cliente extrairá o país do cliente da guia de dados do cliente. A segunda é a matriz de pesquisa. Então, onde está esse valor de pesquisa no conjunto de dados? A terceira é a matriz de retorno, que são os dados nos quais estamos realmente interessados, neste caso, o país do cliente. E o quarto, na verdade, esse não é um parâmetro obrigatório porque podemos ver que está entre colchetes. É um parâmetro opcional, mas são os parâmetros, se não forem encontrados, semelhantes à função de erro I que usamos na função de correspondência de índice. Então, usando os quatro parâmetros, veremos como a pesquisa X funciona. Nosso valor de pesquisa é o ID do cliente, então cliente 001, para ir para o próximo parâmetro, nossa matriz de pesquisa está nos dados do cliente. A matriz de pesquisa é basicamente onde está o valor da pesquisa, e o valor da pesquisa está em algum lugar na coluna ID do cliente. A matriz de retorno é o que nos interessa. Nesse caso, estamos interessados no país do cliente e também podemos adicionar um parâmetro opcional se não for encontrado, seja, se não encontrarmos o ID de cliente específico que estamos procurando, queremos que nossa função retorne não encontrada. Feche os parâmetros e pressionamos Enter. Você pode ver que obtemos todos os valores dos diferentes IDs de clientes em segundos, não havia funções aninhadas Não nos importamos se as colunas se movem aqui e ali. Também podemos testar isso. Portanto, se eu inserir outra coluna em qualquer lugar meu conjunto de dados para que as colunas se movam para a direita, veremos que nossa função não quebrou e ainda temos todos os valores nos quais estamos interessados Agora, não temos o cliente 005, que inicialmente nos deu o erro Então, deixe-me adicioná-lo para mostrar como isso funciona. Insira e cliente 005. Então, podemos ver que, em vez de NA, que podemos ver nas colunas L e M, essa função sem adicionar uma função extra está nos dando não encontrado. Portanto, se você tiver um conjunto de dados muito grande, poderá realmente usar essa função seu benefício porque pode, de forma muito simples, sem adicionar uma função extra, especificar o valor que a função deve retornar se a pesquisa não estiver em seu conjunto de dados e, em seguida, filtrá-la ou examiná-la em tabelas dinâmicas É muito fácil ver quais valores você não tem. Portanto, essa é uma das principais coisas que o X lookup fornece ; portanto, as outras funções não o fazem . Eu recomendo novamente que você experimente essa função no arquivo do projeto, porque não importa o quão fácil pareça, você ficará confuso. Se você não pratica isso sozinho. Você realmente precisa entender os valores de pesquisa, as chaves primárias e as matrizes de retorno para realmente obter algum benefício das funções de pesquisa É por isso que o X Loup é minha nova função de pesquisa favorita e, inicialmente, eu não sabia muito sobre ela porque estava acostumado a usar o Index Match desde 2016, e a pesquisa X não existia na época, o que é uma das desvantagens do X Loup: se você estiver usando uma versão mais antiga do Excel, essa função de pesquisa não funcionará E se você estiver trabalhando em um arquivo do Excel em seu computador e for a versão mais recente do Excel, mas enviá-lo para alguém que não tenha essa nova versão instalada , essa função de pesquisa não funcionará e eles verão apenas erros Então, se você está enviando para alguém, se você está enviando o arquivo e está enviando para alguém que você sabe que tem uma versão do Excel atualizada , usar essa função é incrível. Mas se não, eu o encorajaria a usar Index Match ou a converter sua fórmula em valores, como praticamos em uma das aulas anteriores, para que você tenha certeza de que sua função não será interrompida e quem estiver lendo seu arquivo do Excel terá todos os valores que você deseja que eles vejam Abordamos todas as três funções de pesquisa e usamos V Loup, Index Match e X Lookup para reunir nosso conjunto de dados em uma guia Agora é hora de pegar esses dados conectados e começar a analisá-los usando tabelas dinâmicas, que é um dos recursos mais fortes do Excel E na próxima lição, criaremos uma tabela dinâmica passo a passo para resumir dados, identificar tendências e reunir visualmente nosso projeto 12. Aula 12 Aplique lógica usando tabelas dinâmicas e gráficos: Agora que nossos dados estão limpos e conectados, finalmente chegou a hora de trabalhar com um dos recursos mais importantes do Excel, que são as tabelas dinâmicas. Se você já precisou resumir grandes quantidades de dados, é assim que você faz As tabelas dinâmicas podem ajudar você a encontrar insights, tendências e respostas sem precisar escrever fórmulas complexas E, honestamente, se você precisar fazer algum tipo de análise no Excel, deve saber como usar tabelas dinâmicas Tudo bem, então já convertemos nosso intervalo de células em uma tabela, e há dois motivos principais pelos quais você gostaria de fazer isso primeiro, eu acho, é mais um problema do Windows do que do MAC, mas o problema é que se você não tiver um cabeçalho, por exemplo, se não houvesse cabeçalho nos itens retornados, se fosse apenas um intervalo de células, você veria que o cabeçalho foi removido. Nesse caso, se você tentar adicionar uma tabela dinâmica com essa coluna incluída, ela não funcionará Eu lhe darei um, então você deve se certificar de que todas as suas colunas tenham cabeçalhos. E, honestamente, eu realmente não me importo muito com esse problema porque é bom, especialmente quando você tem grandes quantidades de dados para ter cabeçalhos em todas as suas colunas, caso contrário, você pode se perder Então, esse é um bom problema, na minha opinião. O segundo problema é que, se você começar a obter linhas adicionais em seu conjunto de dados, precisará alterar o intervalo de dados selecionado precisará alterar o intervalo de dados selecionado repetidamente para garantir que todas as células que deseja incluir na tabela dinâmica No entanto, se você usar uma tabela, esse não será o caso. Portanto, esse é um grande benefício de usar uma tabela ao criar tabelas dinâmicas em vez de apenas um simples intervalo de células Sabemos que nossos dados estão em formato de tabela porque podemos ver a opção de tabela na faixa de opções na parte superior, e você notará que, assim que inserirmos uma tabela dinâmica, essa opção também aparecerá Vamos mudar o nome da tabela aqui e chamá-la de pedidos, para que seja mais fácil navegar pelas diferentes tabelas. Quando você tiver várias tabelas, verá por que fazer isso é realmente importante. Vamos inserir uma tabela dinâmica. Você não precisa selecionar o intervalo de células porque já temos tudo em formato de tabela. Mas se fosse apenas um intervalo de células e não uma tabela, você teria que selecionar seu conjunto Em seguida, insira uma tabela dinâmica. Se você selecionar o conjunto de dados inteiro, verá que sua tabela dinâmica tem muitos espaços em branco, e isso não é muito divertido de Isso atrapalha a análise e os espaços em branco reais podem ser Como resultado. Você não quer fazer isso. Assim, você selecionará apenas o intervalo de células que deseja incluir em uma tabela dinâmica. No nosso caso, não precisamos nos preocupar com isso porque nossos dados estão em formato de tabela. Inserir tabela dinâmica Eu gosto de adicioná-la em uma nova planilha. Se eu estivesse criando várias tabelas dinâmicas para a mesma tabela , as manteria na mesma planilha Mas a primeira tabela dinâmica você pode criar em uma nova planilha e depois Tudo bem, agora temos uma nova planilha com as opções da tabela dinâmica à nossa frente Então, à direita, temos o painel de campos da tabela dinâmica, e é aqui que você tem todas as diferentes opções de linhas, filtros, colunas e valores Você pode simplesmente arrastar e soltar o campo selecionado em uma dessas caixas ou, em um MAC, basta clicar no nome do campo em que está interessado e ele se atribui automaticamente Talvez você queira alterar isso dependendo do que está procurando em uma tabela, e nem sempre é preciso. Então, eu mesmo gosto de adicioná-lo, e você pode simplesmente arrastar e soltar, é muito fácil. Então, a pergunta que queremos responder é: onde devemos expandir com base no desempenho do produto? E para isso, a primeira coisa que eu quero ver é a receita total por país. Então, vamos entrar e selecionar o país do cliente. Você percebe que eu simplesmente cliquei no país do cliente e ele apareceu na rosa É aí que a primeira opção sempre aparecerá. E se eu fosse selecionar qualquer outra opção, ela continuaria indo para o campo de rosas, certo? Então, para remover um campo de uma das caixas, basta clicar nesse campo e removê-lo e ele desaparecerá Portanto, essa é a receita total por país. Então, como podemos ver, há uma opção não encontrada em nossas tabelas dinâmicas, que está Essa é uma das coisas que eu realmente gosto nas tabelas dinâmicas. Se você perder alguma coisa nos filtros, você a verá instantaneamente assim que inserir uma tabela dinâmica. Então, claramente, há um problema no país do cliente. Imagino que tenha algo a ver com o cliente cinco ou talvez com cliente cinco ou talvez algo que removemos do nosso conjunto de Assim, podemos fazer pedidos e usar nossos filtros. Tudo bem Portanto, podemos ver que há uma opção não encontrada aqui. Então, sim, isso é para o cliente cinco e, como fizemos antes, podemos simplesmente remover esse campo do nosso conjunto de Como mencionamos anteriormente, se você estivesse fazendo isso na vida real e tivesse outra plataforma onde estivesse extraindo dados, gostaria de voltar lá e garantir que não tenha nenhuma informação conectada ao cliente cinco, porque, se tiver, prefiro que você extraia essas informações e conclua seu conjunto de dados em vez essas informações e conclua seu conjunto de excluindo para nossos propósitos, nosso conjunto de dados é pequeno e, obviamente, não temos nenhuma outra plataforma porque esse é um conjunto de dados gerado pelo GBT de bate-papo Então, estou simplesmente deletando a corda. Mas na vida real, você não quer apenas excluir dados, sejam eles discrepantes ou dados ausentes, você quer primeiro ver se você pode realmente coletar os dados e , em seguida, para valores discrepantes, seguida, para valores discrepantes, ver se consegue entender o raciocínio E então você quer decidir se um ponto de dados deve ser excluído ou não do seu conjunto Portanto, desloque o espaço para selecionar a coluna inteira e , em seguida, comande menos. Queremos excluir toda a linha da planilha, e então você pode limpar os filtros voltar usando o comando e a tecla de erro ascendente Tudo bem, então se você voltar para a nossa primeira folha. Portanto, a opção não encontrada ainda está lá, o que é um problema. Acabamos de removê-lo, certo? Mas nas tabelas dinâmicas, você precisa atualizar manualmente seu conjunto de dados sempre que fizer Portanto, se você entrar na Tabela Dinâmica, analise a opção aqui para atualizar pode ser selecionada E agora podemos ver que não encontrado não é mais um problema. Voltando à nossa pergunta, que é onde devemos expandir com base no desempenho do produto? Teríamos que analisar algumas camadas de dados para entender onde deveríamos expandir. Então, de uma olhada rápida, podemos ver que o Reino Unido tem sido nosso país com melhor desempenho em termos de receita. Nesta aula, analisamos apenas a receita total para decidir se queremos expandir para outro país ou outra cidade ou não. Mas na vida real, você nunca usará apenas um ponto de dados para tomar uma decisão tão importante, especialmente se estiver entrevistando para cargos de consultoria ou se for consultor, saberá que usar apenas um campo é uma receita para o desastre E venho enfatizando valores discrepantes e excluindo conjuntos de dados e não usando um campo porque é extremamente importante na análise de dados que você use seu pensamento e sua lógica para tomar decisões, qualquer decisão, seja excluir um conjunto de dados ou apenas decidir sobre um ponto de dados, que estou enfatizando isso repetidamente Então, se tivermos apenas a receita total, nunca tomaremos a decisão de expandir para um novo país porque existem muitos fatores diferentes, tarifas, taxas de desemprego, leis diferentes em um país, independentemente de ser realmente fácil expandirmos ou não Quais são as outras despesas do país um versus do país dois? Esses são muitos fatores que você deve considerar ao tomar uma decisão sobre qualquer problema que esteja tentando resolver usando dados. Os aspectos qualitativos são tão importantes, se não mais, quanto os aspectos quantitativos de qualquer problema que você esteja resolvendo. Então, isso é algo que você deve ter em mente sempre que estiver analisando números. Portanto, temos o valor, que é a receita total. Nesse caso, é muito óbvio que o Reino Unido é o número um. Mas se você tivesse várias entradas diferentes, não gostaria de ver o desempenho relativo de um país em relação a outro. Para fazer isso, eu obteria a receita novamente no campo de valores, clicaria com o botão direito do mouse na soma das opções de receita e entraria nas configurações do campo para mostrar os dados como uma porcentagem do total geral e, em seguida, pressionaria OK, porque a porcentagem fornece informações relativas. Os valores em dólares estão fornecendo informações estáticas. Então, quando você tem muitas opções diferentes, você realmente quer ver como uma opção funciona em relação a outra. E quando você tem várias opções, é muito difícil comparar uma opção com outra. Portanto, neste caso, sabemos que 65% dos nossos produtos estão sendo vendidos no Reino Unido e 20% no Canadá. Portanto, o Reino Unido pode parecer a opção óbvia, mas não vamos tomar essa decisão imediatamente. Também temos outras coisas para analisar. Então, agora que temos uma ideia do desempenho relativo, posso simplesmente remover a opção de soma da receita. No painel de campos da tabela dinâmica. E a próxima coisa que eu quero ver é o desempenho ano após ano. Vou colocar o campo do ano na seção de colunas. Portanto, agora podemos ver o desempenho 2022-2025. Então, de 22 a 23 anos, a receita no Canadá estava aumentando ligeiramente, mas no Reino Unido houve um grande aumento Mas, desde então , vem diminuindo. Mas nossa receita no Canadá em 2025 é muito maior do que no Reino Unido ou nos EUA. Portanto, posso dizer que tanto o Canadá quanto o Reino Unido podem ser opções válidas para comparação. E, novamente, temos que considerar que esses são dados aleatórios do HatchBT, portanto, podem não ser tão precisos quanto os dados que você realmente obteria para vendas em Em um conjunto de dados real, as tendências que você veria seriam muito diferentes das que você está vendo aqui agora. Portanto, a decisão que talvez tenhamos que tomar é entre o Reino Unido e o Canadá no momento. EUA não são concorrentes porque os números de receita são muito baixos e, embora não baseemos nossa decisão apenas na receita, é um fator muito importante Portanto, se esse fator nem mesmo for algo que consideraríamos para os EUA, podemos simplesmente eliminar essa opção imediatamente. Então, agora que temos uma ideia ano após ano, podemos simplesmente remover o ano de nossas colunas. E agora eu quero analisar o desempenho do produto. Posso simplesmente selecionar o nome do produto e colocá-lo no país do cliente na seção rosa. E agora temos a divisão por produto. Novamente, a receita total não é informação suficiente para entender quantos produtos realmente vendemos e qual é a popularidade do produto porque, por exemplo, se a estante custar $1.400, isso significa que vendemos apenas um desse Quer entender o volume dos produtos que estamos vendendo. E para isso, podemos simplesmente extrair um valor e eu realmente quero um valor além da receita na seção de valores. Tudo bem, então eu gostaria de classificar meus dados para que sejam visualmente agradáveis. Eu posso entrar no filtro aqui que você pode ver, e o campo que eu gostaria é, eu acho, país do cliente, e eu quero classificar pela receita total, em ordem decrescente Agora podemos ver que o Reino Unido está no topo, porque obviamente a receita total é a mais alta do Reino Unido. Também vou classificar pela soma do valor em ordem decrescente E agora posso ver que o apoio para os pés é o que mais vendemos. A quantidade total vendida é de 160 produtos de apoio para pés, o que é bastante em comparação com todos os outros produtos que temos Então, a partir desses dados, entendemos o que está funcionando em qual país. Portanto, a Futrust é a que mais trabalha no Reino Unido. Obviamente, nossa decisão de expansão será diferente com base no fato de termos uma loja on-line ou uma loja física, mas esses dados, pelo menos, estão nos dando uma compreensão de qual país é o Agora que sabemos qual país é o mais favorável, posso selecionar minhas opções para o E agora podemos verificar qual cidade é a mais favorável para sabermos onde queremos abrir uma nova loja. Então, o que posso fazer é remover o nome do produto, porque agora isso não é mais relevante, e posso procurar a cidade do cliente e colocá-la na opção de país do cliente. Como não estou realmente olhando para o Canadá e os EUA, posso simplesmente destruir esses campos. Então, estamos apenas olhando para o Reino Unido. E a partir de nossos dados, podemos ver que Birmingham é o lugar onde talvez queiramos abrir uma nova loja apenas com base no número de itens vendidos e na receita total Novamente, quero enfatizar que essa não é nossa decisão final para os dados que temos e, para o nosso caso, é nossa decisão final, mas na vida real, haveria muitas opções diferentes que você teria que considerar antes de tomar uma decisão. Outra coisa rápida que quero mostrar sobre tabelas dinâmicas é que você viu o quão importantes são as tabelas dinâmicas, certo Os números são ótimos e você tem uma compreensão muito rápida do que funciona e do que não funciona. Os números são sempre melhores quando você pode visualizá-los, e é aí que entram os gráficos dinâmicos. Os gráficos dinâmicos podem ajudá-lo em sua análise porque você pode inserir gráficos rapidamente, e eles continuam mudando à medida que você altera sua tabela dinâmica, se estiver apenas usando suas tabelas dinâmicas para entender Então, por exemplo, começamos com o país do cliente, depois analisamos o desempenho do produto e depois o reduzimos às cidades Para visualizar um gráfico dinâmico, basta verificar se está na seção da tabela dinâmica. E aqui à direita, há uma opção de gráfico dinâmico na qual você pode clicar e obter uma visualização rápida que o Reino Unido é seu país com melhor desempenho Novamente, isso é mais útil quando você tem opções diferentes e deseja ver respostas rápidas. Então, se eu arrastar a cidade do cliente, na coluna rosa, você verá que o gráfico dinâmico foi atualizado automaticamente com base nas seleções que temos em nossa tabela dinâmica atualizado automaticamente com base nas seleções que temos em nossa Então, se eu fosse expandir o Canadá, você teria as diferentes cidades no Canadá, e se eu fosse expandir os EUA , você teria todas as opções diferentes aqui também. Obviamente, isso pode ficar um pouco confuso quanto mais gráficos forem adicionados Mas a vantagem dos gráficos dinâmicos é que você pode visualizar seus dados enquanto tenta analisá-los Então, se eu estiver apenas olhando para o Reino Unido, posso simplesmente reduzir as opções do Canadá e dos EUA. Então, agora eu só tenho a visão de um país. Assim, você pode brincar com gráficos dinâmicos conforme desejar, mas isso realmente ajuda você a visualizar seus dados à medida Isso também pode ser muito útil quando você deseja adicionar gráficos a uma apresentação. E ao analisar seus dados, você pode continuar reunindo os diferentes gráficos para realmente visualizar a experiência de outras pessoas, que verão que é muito importante que você escolha as coisas que deseja mostrar com base no público Portanto, se você estiver apresentando para a gerência sênior, usará gráficos diferentes e gráficos temáticos abrangentes mais concisos Mas se você estiver apresentando para uma equipe específica que precisaria de mais dados, talvez queira apresentar a tabela dinâmica, que tem os detalhes de cada ponto de dados, em vez de cada ponto de dados, em vez um gráfico que mostra Portanto, com base no seu objetivo e no público para o qual você está se apresentando, seu método de apresentação também mudará. Portanto, lembre-se disso ao analisar seus conjuntos de Falando sobre lógica e compreensão, uma coisa que quero mencionar é que se fosse uma entrevista, você também poderia adicionar uma guia chamada estrutura lógica. E aqui você pode simplesmente descrever as diferentes etapas que você tomou para analisar seus dados ou seu pensamento e lógica por trás de tudo o que você fez Dessa forma, você está ajudando quem está analisando seu trabalho a analisar suas ideias, sem precisar manipular os dados sozinhos E, às vezes, há coisas que você pode estar pensando e pode estar considerando que outras pessoas não sabem que você está considerando, por exemplo, quando falamos sobre receita e não apenas considerando a receita e tomando uma decisão de expansão, isso é algo que você pode adicionar seu campo de estrutura lógica porque se esse fosse o seu dia a dia normal. No trabalho, você pode ter essa discussão com seu gerente ou com sua equipe. Mas em um ambiente de entrevista, talvez você não consiga fazer isso porque muitas vezes as empresas fornecem avaliações técnicas e, se gostarem de suas avaliações técnicas , passam para a próxima rodada, que é a apresentação do estudo de caso Portanto, ter uma guia de estrutura lógica sempre funcionará a seu favor, especialmente se você estiver entendendo bem. Agora você sabe como dar vida aos seus dados e usar números simples para entender um problema maior. 13. Aula 13 - Conclusão: E é isso. Você chegou ao final do curso. Começamos com o básico, aprendemos a pensar logicamente sobre os dados, limpamos e preparamos nosso conjunto de dados, aplicamos funções de pesquisa, criamos tabelas dinâmicas e, finalmente, visualizamos Se você seguiu passo a passo, agora sabe não apenas como usar o Excel, mas como pensar como um analista, e é isso que o diferenciará. Lembre-se disso, qualquer pessoa pode aplicar fórmulas. É realmente muito fácil fazer isso. O que os empregadores estão procurando e o que fará você se destacar no trabalho ou em seus negócios pessoais é como você pensa sobre os dados e como encontra o porquê por trás dos números e os comunica com clareza. Muito obrigado por fazer este curso. Continue praticando, continue experimentando. Sempre comece com a lógica antes das fórmulas e continue analisando. Tchau.