Transcrições
1. Introdução: Meu nome é Al Chen, sou arquiteto de soluções da Coda, uma ferramenta que reúne o melhor de planilhas, documentos e aplicativos. Sou um usuário avançado do Excel há mais de 10 anos,
ensinei mais de 10 mil alunos de MBA. Atualmente, passo meu tempo criando tutoriais e vídeos sobre planilhas do Excel e outras ferramentas relacionadas. Hoje, eu vou ensinar a você como usar tabelas
do Excel um recurso relativamente desconhecido no Excel. Depois de aprender o poder das tabelas do Excel, você nunca olhará para trás. Estarei ensinando como criar tabelas do Excel, como criar referências de estrutura e também como formatar sua tabela, para que elas
fiquem muito bonitas e bonitas para sua equipe e para seus colegas. Estou muito entusiasmado com os alunos para assistir a esta aula porque uma vez que você vai aprender os recursos básicos e funções de tabelas do Excel, você verá o poder que ele desbloqueia para o resto de seus painéis, sua análise de dados, tarefas, e outras coisas relacionadas às planilhas do Excel. Apenas aprendendo esse recurso simples, esperançosamente
mudará a maneira como você aborda o Excel no futuro. Assim que terminar esta aula, espero que participe dos projetos da turma. Envolve analisar dados de atraso de voo do Departamento de Transporte de 2015, transformá-lo em uma tabela do Excel, e também fazer análises sobre esses dados criando um painel, criando um relatório para o usuário final, e também escrever alguns insights sobre o que você pode coletar analisando os dados depois
de usar tabelas do Excel. Eu realmente espero que você poste suas descobertas nos dados dentro da galeria do projeto. Vamos começar imediatamente a encontrar os benefícios das tabelas do Excel.
2. Os benefícios das Tabelas do Excel: Nesta lição, vou mostrar os benefícios das tabelas do Excel fazendo uma demonstração rápida de uma tabela que criei na pasta de trabalho da classe, onde mostrarei como filtrar e classificar, adicionar uma linha de resumo aos seus dados e também adicionar novos dados à tabela do Excel. Vou explorar esses tópicos mais detalhadamente mais tarde na aula. Estamos olhando para uma tabela do Excel agora. Na etapa 1, vamos apenas clicar na tabela abaixo e
rolar para baixo após a primeira linha de cabeçalho, que é a linha 11. À medida que eu percorrer, você notará que as colunas
ficarão grudentas na barra de ferramentas superior ou na faixa de opções. Este é um dos recursos agradáveis de tabelas do Excel onde você pode ver as colunas aqui em cima como você rolar passado. É como congelar sua primeira linha uma
forma se você já fez isso antes em seus arquivos do Excel. Eu também posso filtrar e classificar rapidamente meus dados apenas
clicando no “Dropdown” aqui em uma dessas colunas, e eu posso clicar em “Ascendente” ou “Decrescente” e filtrar coisas como você fez antes em seus arquivos do Excel. Este é um dos recursos padrão que vêm com sua tabela do Excel. Digamos que eu queira fazer uma linha de resumo na etapa 3. Posso clicar dentro da minha tabela, clicar na “guia Tabela” na faixa de opções
e, em seguida, você percebe que as caixas de seleção de linha total desmarcar por padrão. Se eu clicar nisso, você verá na linha 39, há uma linha de resumo que você pode basicamente resumir seus dados por médias,
somas, contagens, todas essas métricas típicas. Vou desmarcar isto por enquanto. Um dos recursos mais poderosos com tabelas está listado aqui na etapa 4. Quero que tente adicionar uma nova linha de dados à tabela. Se eu clicar aqui e pressionar “Enter” e começar a digitar algo como S510, e este é um filme, se eu quiser digitar em outra linha S200, você percebe que a tabela adiciona automaticamente a formatação para as novas linhas de dados, e essas linhas de dados são incluídos na tabela como um objeto. Agora, uma coisa que eu quero que você pense é imaginar ter criar esta tabela usando uma formatação condicional. Você teria que fazer um monte de fórmulas e
funções realmente interessantes para obter essas linhas com faixas para ficar colorido como este. A coisa boa sobre tabelas do Excel é que o Excel
cuida de toda a formatação condicional para você, modo que quando você adicionar novas linhas de dados eles automaticamente obter código de cor para os dados para se destacar. Finalmente, eu quero que você tente colocar uma fórmula, qualquer fórmula na coluna K, onde diz, Minha Fórmula. Eu vou escrever igual a bater em uma célula talvez na mesma linha aqui, e você percebe que ele lhe dá essas referências, a coluna, em vez de um tipo tradicional de referência de encouraçado, nós temos A1 e B5. É um pouco diferente do que você verá, mas vamos falar mais sobre o que é isso e por que ele é realmente poderoso em uma lição futura sobre referências estruturadas. Apenas para recapitular, passamos por alguns dos benefícios e recursos das tabelas do Excel, mostramos como tornar suas tabelas um pouco menos propensas a erros tendo essa formatação condicional que é facilmente aplicada, e também nós percorreu muito rapidamente como você pode filtrar e classificar tabelas. Uma das principais vantagens desta lição é começar a
brincar com a tabela na pasta de trabalho da classe. Na próxima lição, vamos falar sobre como criar
sua própria tabela do zero para que você possa começar a fazer algumas análises.
3. Como criar uma tabela no Excel: Vamos criar uma tabela do Excel a partir do zero usando uma lista de restaurantes na pasta de trabalho da classe. Vamos começar. Temos uma lista de restaurantes aqui com a localização, as vendas, o status de franquia, e você pode aprender mais sobre este conjunto de dados clicando neste URL que eu tenho aqui nesta célula. Mas vamos passar passo a passo como
criar uma tabela e usar alguns dos recursos desta tabela. Defina um, vamos clicar em “Inserir” e, em seguida, “Tabela”, e vamos nomear esta tabela, Restaurantes. Eu quero que você primeiro selecione todos os seus dados. São cerca de 50 filas. Em seguida, vá para Inserir tabela. Por padrão, ele vai dizer que minha tabela tem cabeçalhos. Normalmente, você deseja manter isso como marcado porque nossa tabela tem cabeçalhos, você pode ver aqui na linha 10. Vou clicar em “Ok”, e é assim que o Excel cria uma tabela para você por padrão. Você percebe que você obtém as linhas de cores com faixas, e você também obtém essas listas suspensas para filtragem e classificação rápidas. Você vê quais são os benefícios aqui da tabela quando você aperta Enter e você começa a digitar novamente um novo restaurante e dizer que este é Arby's, Excel expandirá automaticamente essa tabela para incluir essa nova linha de dados. Se eu adicionar novas linhas de dados, Taco Bell, você verá como a tabela do Excel irá
codificar automaticamente as linhas para você para que você não tenha que se preocupar com a formatação condicional como você faria normalmente com um lista de dados. Agora eu vou excluir alguns dados da tabela e tentar navegar em torno das células vazias na etapa 2. Vamos apenas excluir algumas dessas linhas. Você percebe que tudo isso se foi, mas o Excel ainda acha que essas linhas são provavelmente tabela. Agora, antes de excluirmos essas linhas, eu quero que você navegue pressionando “Command” ou “Control” com as teclas de seta. Você percebe que o comportamento é um pouco diferente do que você esperaria. Você começa a ir para as bordas da tabela, em vez de quando há uma nova célula com um valor. Vou apenas selecionar essas linhas, clique com o botão direito do mouse
e, em seguida, pressione “Excluir”, excluir linhas da tabela, e essas linhas serão excluídas da minha tabela para que elas não sejam contadas como linhas vazias. Nós excluímos as linhas vazias, e agora eu quero que você faça algumas outras opções para tabelas do Excel como podemos converter esta tabela em uma tabela dinâmica se quisermos clicando em “Tabela” na faixa de opções, e depois clicando em “Resumir com tabela dinâmica”. Eu vou usar worksheet existente neste caso e o intervalo vai ser a célula J14. Assim que eu clicar em “Ok” para a Tabela Dinâmica, você verá que isso é criado aqui, então temos a opção de criar uma Tabela Dinâmica se quisermos. Se em qualquer ponto no tempo você quiser converter sua tabela do Excel de volta para um intervalo normal de dados, tudo que você precisa fazer é clicar na faixa de opções “Tabela” novamente, e uma das opções na barra de ferramentas é converter para intervalo. Se eu clicar em “Converter em Intervalo”, Excel removerá automaticamente as suspensões para mim, mas a formatação condicional ainda permanecerá na mesa, que você
possa removê-la limpando a formatação se desejar. Vou desfazer essa conversão ao alcance. Uma última coisa que eu quero fazer, eu esqueci de mencionar é que no passo 1 é queremos
renomear esta tabela para restaurantes no canto superior esquerdo. Se eu clicar em uma célula dentro da tabela e clicar na guia “Tabela” na faixa de opções novamente,
você percebe por padrão,o você percebe por padrão, Excel me deu um nome de tabela de Tabela4. Eu vou renomear isso para Restaurantes e pressionar “Enter” para que no futuro, se eu quiser fazer referência a essa tabela, eu posso simplesmente chamá-la de Restaurantes em vez de Tabela4 ou o que quer que o
Excel me dê como padrão. Nesta lição, passamos por alguns
dos benefícios de usar uma tabela do Excel e como criar uma tabela do Excel, como podemos excluir linhas e aplicar alguma formatação rápida às tabelas. Na próxima lição, vamos analisar mais detalhadamente como filtrar, classificar e criar colunas calculadas em sua tabela do Excel.
4. Como filtrar e classificar uma tabela do Excel: Vamos aprofundar a filtragem e a classificação de sua tabela do Excel, e também como adicionar uma nova fórmula à sua tabela, que são chamadas colunas calculadas. Vamos mergulhar. Temos a mesma tabela de restaurantes também como uma tabela Excel. Se eu clicar em “Tabela” aqui você verá que esta tabela é chamada Rest2. Como você já viu antes, você pode classificar por qualquer uma dessas colunas clicando em “Ascendente” ou “Decrescente”, e eu posso simplesmente adicionar esse tipo muito rapidamente a qualquer coluna que eu quiser. Eu quero que você agora no segundo passo, é adicionar uma linha de resumo para esta tabela e encontrar a média de vendas, que está na coluna D em nossa lista de restaurantes. Se você for para Tabela, clique em “Linha Total” e você percebe que na parte inferior da linha 61 temos esta linha azul escuro para os nossos totais. Na coluna de vendas, quero que você clique no menu suspenso e clique em “Média”. Agora podemos apenas encontrar a média de vendas em todos esses restaurantes, que é de cerca de US $34 milhões. Essa é uma maneira muito agradável de obter rapidamente a contagem média de soma, assim como você faria em uma tabela dinâmica tradicional, mas em uma tabela Excel, ela é tratada através desta última linha na parte inferior da sua tabela. Na etapa três, vamos criar uma segmentação baseada na coluna Franchising. Agora slicer apenas como uma maneira agradável visualmente agradável para sua equipe, seus colegas, para interagir rapidamente com sua tabela do Excel, eu vou clicar na minha “Tabela” aqui, certifique-se de que você está na guia Tabela na faixa de opções e clique em “Inserir Slicer”. Você receberá este menu aqui, onde você pode selecionar qual coluna você deseja cortar. Eu só vou clicar em “Franchising”, apertar “Ok”, e você vai ter este pequeno menu onde você pode escolher e escolher Sim ou Não. Você percebe que quando você clica em Sim ou Não, esta coluna de Franchising é automaticamente filtrada para esse valor específico. É um recurso muito bom para ter com suas fatias. Se eu clicar nesta opção aqui, esta opção de marca de seleção na segmentação, agora
posso selecionar várias opções na minha caixa de segmentação. Eu só vou remover a segmentação por enquanto, apertar “Excluir” sobre isso, e voltar para a minha tabela principal vista aqui. Finalmente, vamos escrever uma fórmula na primeira coluna vazia à direita,
que é a coluna I, para calcular a média de vendas por loja. Deixe-me desfazer este filtro aqui. Se eu clicar na célula I10 e escrever vendas médias, você percebe que o Excel adiciona automaticamente essa coluna como uma nova coluna à minha tabela, e se eu começar a escrever igual na célula I11, é igual a vendas, que é a coluna D dividida pela coluna F, que é veja a fórmula que é criada, as vendas divididas por locais, vamos clicar em “Enter” e ver o que acontece. A fórmula é preenchida automaticamente até a última linha da minha tabela, então eu não preciso fazer nenhum clique duplo ou arrastar minha fórmula todo o caminho para baixo. Fórmulas em tabelas são automaticamente aplicadas a toda a coluna de uma só vez, para que você não precise se preocupar com todas essas etapas manuais extras para criar a fórmula. Você pode ter feito algo semelhante a isso, que em uma tabela do Excel é chamado de coluna calculada. Você pode ter feito algo semelhante em uma tabela dinâmica, que é chamado de campo calculado. Estamos apenas fazendo o [inaudível] em uma tabela do Excel. Recuando um pouco, se você tivesse que construir esta fórmula e construir esta tabela a partir do zero, e também obter os dados resumidos aqui nesta linha 61, você teria que fazer uma lista complicada de fórmulas. Você pode estar usando uma fórmula COUNT A para selecionar todas as células aqui que têm um valor vazio. A coisa boa sobre esta linha total é que ele automaticamente é adicionado ao fundo da tabela, você não precisa se preocupar em adicionar todas essas fórmulas adicionais que apenas tornam sua vida um pouco mais difícil de fazer. Falamos sobre como fazer um pouco mais de filtragem, classificação e criação de colunas calculadas em uma tabela do Excel. Agora, a fórmula da tabela do Excel que você viu na coluna para vendas médias parece complicada, então vamos aprofundar isso na próxima lição sobre referências estruturadas.
5. Fórmulas dentro de referências estruturadas: Vamos mergulhar mais em referências estruturadas. Estarei percorrendo diferentes maneiras de criar fórmulas com referências estruturadas dentro de suas tabelas do Excel. Uma nota rápida sobre essas referências estruturadas é que quando você está dentro da tabela em si, como temos com vendas médias, você faz referência à coluna colocando este e comercial mais o nome da coluna entre colchetes. Isso é diferente de quando você faz igual, célula K5 ou algo assim, ou K9. Nas tabelas do Excel, você faz referência à coluna inteira para que é por isso que quando você escreve uma fórmula, a fórmula preenche toda a coluna por conta própria. Se você estiver fora da tabela, como temos aqui no passo 1, vamos escrever iguais. Vou fazer referência à coluna de vendas novamente. Você percebe que eu tenho que primeiro colocar o nome da tabela Rest3, seguido da referência estruturada, que é @Sales. Isso me dará apenas o valor para essa linha, mesmo que esteja fazendo referência à coluna inteira. Essa é uma coisa para se acostumar ao fazer referência a células e linhas em sua tabela. Só para confirmar, vou clicar em “Tabela” aqui, de fato chamado Rest3. Finalmente, na etapa 2, Eu quero que você faça mais um exercício com referência a esta tabela é escrever igual e tentar selecionar a tabela inteira com os cabeçalhos. Você percebe que ele diz Rest3 #All. Isso está dizendo ao Excel que você está fazendo referência à tabela inteira de cada vez. Se você quiser apenas referenciar os dados, você pode apenas escrever iguais e selecionar tudo sem o cabeçalho e você só vai ver, ele diz Rest3, mas você também pode escrever [#Data e você terá o mesmo resultado. Isso vai me dar um erro porque eu estou representando a tabela em uma célula então isso não vai funcionar. Eu quero que você escreva uma fórmula SUM na coluna de locais fora da tabela usando
o que acabamos de aprender com fórmulas e preenchimentos automáticos com referências de tabela. Vamos escrever a fórmula aqui, igual a soma. O nome da nossa tabela é Rest3. Eu posso clicar em qualquer uma dessas referências estruturadas aqui, Rest3 e vamos tentar referenciar apenas a coluna locais. Eu posso escrever colchete esquerdo. No minuto em que você escreve um colchete esquerdo, ele lhe dá a opção de selecionar qualquer uma das colunas em sua tabela. Agora eu posso clicar em “Localização” clique no parêntese direito. O que eu estou fazendo aqui é que eu preciso escrever locais com um S para referenciar esta coluna,
coluna F. O que eu estou dizendo aqui é somar tudo na coluna locais dentro da minha tabela Rest3. Aperte “Enter” e isto é 1.735. Vou formatar isto para ser um número. Você pode estar se perguntando por que isso é mais fácil do que apenas escrever soma
igual e, em seguida, selecionar todas as células aqui? Tradicionalmente você faz isso com uma lista normal de dados, mas a coisa boa sobre essas referências estruturadas é se eu
descer aqui até a última linha da minha tabela e adicionar uma nova linha,
51, Taco Bell, e adicionar um valor à minha coluna Locais, digamos que em 100 ou 1.000, esse número será atualizado automaticamente na minha soma porque essa referência estruturada se expande e diminui com base no que está na sua tabela. Esse é um dos recursos mais poderosos das tabelas do Excel é como novos dados são adicionados à sua tabela, as referências estruturadas serão atualizadas. Imagine ter que fazer isso com uma fórmula COUNT que você pode estar usando hoje para contar todos os valores em sua coluna. As referências de estrutura o torna uma maneira muito mais limpa e
menos propensa a erros de referência a todos os valores em sua tabela. Finalmente, vamos fazer um pequeno exercício na criação de algumas estatísticas resumidas aqui na coluna M e N. Eu quero que você escreva uma fórmula SUMIFS para resumir todas as vendas desses restaurantes com base em locais com 75 graus ou mais, 50 ou 75 para restaurantes que têm franquias e sem franquias. Vamos começar a escrever. Posso escrever sumifs iguais. Eu só vou ampliar aqui para que você possa ver todos os meus dados, igual a sumifs. Vou usar meu cursor para sugar meu alcance. Meu intervalo de soma vai ser apenas meus dados de vendas, bater vírgula,
e, em seguida, meu primeiro critério será locais. Eu quero todos os locais que são [inaudíveis]. Vou apagar esta linha aqui só para ter certeza de que temos os dados de localização corretos. Vou clicar em “Excluir linhas”. Você deve estar se perguntando como eu vou colocar os critérios? Normalmente no meu arquivo do Excel eu coloco essas pequenas colunas de ajuda. Eu sou alguns para escrever o número 1.000, 75, 50, 25 e 0. Desta forma, posso dizer à fórmula SUMIFS o que fazer referência como meus critérios. Vamos em frente e escrever a fórmula é igual a sumifs. Meu intervalo de soma vai ser vendas novamente, vírgula, meu intervalo de critérios vai ser locais que são menos de 1.000. Neste caso, eu tive que colocar um comercial menos de 1.000, vírgula, mas maior ou igual a 75 para esta linha, eu vou apenas colocar em aspas duplas lá, comercial e. Então eu também quero ver apenas lojas que têm uma franquia neste caso. Posso colocar franchising vírgula, sim. Só para recapitular, estou pegando as vendas, somando vendas se os locais forem menores que 1.000 mas maiores ou iguais a 75, aperte “Enter”. Agora construímos minha fórmula, apertamos “Enter”. Se eu apenas preencher esta fórmula para baixo, ele automaticamente vai me dar o valor porque eu tenho meus critérios e coluna L referência dinamicamente. Vou copiar a mesma fórmula e colocá-la nestas células aqui. Mas o principal é que vou mudar a franquia aqui para não, porque estas são células. Quero que o meu relatório não tenha franchising. Agora vou somar esses valores muito rápido apenas para ter certeza que o total de vendas é o que eu espero que seja, que é 1,689. Mais uma vez, se eu descer para minha coluna de vendas aqui e escrever soma, isso é realmente 1.689. É assim que você usaria a fórmula SUMIFS com referências
dinâmicas de célula para nossos locais, mas usando referências estruturadas para realmente extrair os dados da tabela do Excel. Vimos como você pode referenciar as colunas em sua tabela usando referências estruturadas e também mostramos como você pode usar referências estruturadas em algumas
das fórmulas mais populares que você usa hoje como SUM e SUMIFS. Também vimos como ao escrever suas fórmulas com referências estruturadas, suas fórmulas são um pouco mais fáceis de ler e muito mais fáceis de depurar, especialmente se seus colegas estão pegando as fórmulas ou seu arquivo Excel de você. Agora vamos discutir como você pode usar referências estruturadas de suas tabelas do Excel com uma
das fórmulas mais populares que você está usando hoje, que é VLOOKUP.
6. PROCV e referências estruturadas: Vamos usar a fórmula VLOOKUP agora para procurar uma tabela do Excel para trazer os dados para a nossa tabela principal de dados. Trabalhando com nossas mesmas listas de restaurantes agora, você está vendo as colunas L e M, eu tenho o nome da cidade mais o número da população dessa cidade específica. Na etapa 1, quero que você transforme essa lista de cidades e população em uma tabela do Excel chamada Pop. Vamos selecionar essas células. Você pode clicar em “Inserir tabela” ou um atalho que eu gosto de usar é este pressionando Control T, que apenas traz o menu criar tabela para você. Minha tabela tem cabeçalhos apertado “ok” e agora temos isso como uma tabela. Só para ter certeza de clicar em “Tabela” e lembre-se, queremos renomear este nome de tabela para ser Pop para nossa população. Temos duas tabelas agora nesta planilha, temos Rest312, e então temos nossa tabela Pop, e você pode ver isso no canto superior esquerdo da nossa worksheet. Eu quero que você agora no passo 2 é adicionar uma nova coluna à nossa tabela principal e escrever um VOOKUP para procurar a população de nossa cidade com base na coluna C, que é nossos dados de localização. Vamos chamar essa população e, como já vimos antes, quando você adiciona uma nova coluna, a tabela do Excel automaticamente pega essa coluna como parte da tabela. Vamos começar a escrever a fórmula VLOOKUP que você está muito acostumado a VLOOKUP. Queremos pesquisar a coluna de localização da mesa do nosso restaurante e observar como essa
referência estruturada se transforma em um local de símbolo de chapéu. Nossa matriz de tabela é que você pode apenas literalmente apenas escrever Pop e notar como quando eu escrever Pop, ele destaca as células na minha tabela Pop. Eu também poderia escrever hashtag colchete esquerdo todos e isso faz referência a tabela inteira mais os cabeçalhos, que eu realmente não preciso, mas não vai realmente impactar minha fórmula. Vamos deixar como Pop,
2, 0 porque eu quero retornar a segunda coluna,
que é a população e quando eu aperto “Enter”, ele automaticamente preenche a fórmula para toda a minha lista de restaurantes. Temos um monte de não aplicável porque nem todas as cidades em minha mesa de restaurantes está em nossa tabela de localização. Mas digamos que eu queira adicionar uma nova linha de dados à minha tabela Pop, que é a etapa 4. Digamos que eu queira adicionar a população de Louisville, Kentucky. Eu só vou copiar isso por agora através de uma pasta aqui e vamos dizer que Louisville é 50.000. Observe como essa linha na minha tabela
de restaurantes preenchida automaticamente porque minha fórmula VLOOKUP está sempre fazendo uma pesquisa na minha tabela Pop e no minuto em que eu adicionar uma cidade à minha tabela pop, ela expande automaticamente a referência estruturada dentro da minha pesquisa, que está nesta coluna aqui. Podemos fazer a mesma coisa com a correspondência de índice, que você pode estar usando antes. Eu posso escrever índice e eu quero selecionar toda a minha população aqui. Eu quero combinar esta coluna de localização dentro da minha localização da cidade Pop hit “Enter”, e eu realmente quero fazer uma correspondência exata, é por isso
que ele está me dando valores em cada célula. Vamos fazer vírgula zero. Deveria estar funcionando assim, mas, oh, sinto muito, está dentro deste jogo. Isso lhe dará exatamente os mesmos resultados que o VOOKUP, mas observe como estamos usando a referência estruturada em várias tabelas agora. Estamos olhando para a população de uma mesa Pop porque é um valor que você quer retirar. Estamos combinando a coluna de localização, que é esta coluna, e então estamos comparando com a coluna da cidade, que é esta tabela Pop. Uma última coisa a observar sobre referências sociais é que quando você vê o símbolo @, que também é uma maneira de dizer ao Excel que isso está fazendo referência a esta linha na minha tabela, é por isso
que você só vê Seattle, Washington, mas não toda a coluna que seria apenas localização por si só. Isso ainda funcionaria, mas para ser mais específico, você deseja colocar esse símbolo @ para se certificar de que está fazendo referência apenas a essa linha de dados. Analisamos como você pode usar a fórmula VLOOKUP, uma fórmula realmente comum usada hoje, com referências de estrutura de suas tabelas do Excel para pesquisar dados
dinamicamente para que, quando você adicionar novos dados à sua tabela de pesquisa, uma fórmula realmente comum usada hoje,
com referências de estrutura de suas tabelas do Excel parapesquisar dados
dinamicamente para que, quando você adicionar novos dados à sua tabela de pesquisa,
sua tabela de estatísticas principais, que eles não são automaticamente atualizados e puxados corretamente. Vimos como você pode usar referências estruturadas e fórmulas, agora vamos entrar em um caso de uso mais avançado de usar referências estruturadas com uma lista suspensa e validação de dados na próxima lição.
7. Listas suspensas e validação de dados: Vamos criar uma Lista suspensa usando tabelas do Excel e mostraremos como essas opções flexíveis podem se expandir automaticamente usando a tabela. Com nossas mesmas listas de restaurantes, temos essa lista separada de dados aqui chamada KnownFor. Temos apenas alguns vários atributos com os quais queremos marcar os nossos restaurantes. Mas eu quero transformar essa lista de atributos em uma lista suspensa que podemos usar em nossa tabela principal de restaurantes. Na etapa 1, vamos transformar a lista Conhecido por em uma tabela chamada Conhecido por. Bem aqui em cima, nós vamos inserir tabela e minha tabela tem cabeçalhos. Então tudo bem. No nome da tabela, queremos chamar isso KnownFor. Temos isso como nossa tabela principal para selecionar atributos. Eu quero que você agora vá para Fórmulas e, em seguida, vá para Definir nome e dar a esta tabela um nome definido de tipos. Não queremos incluir os cabeçalhos. Agora o nome da tabela e definir nomes são dois conceitos separados. Isso é algo que fica um pouco complicado ao criar lista suspensa com Validação de Dados. Nós vamos para “Fórmulas”, “Definir Nome” e você percebe aqui eu já tenho um monte de nomes definidos, que inclui tabelas. Vou clicar em “Plus”, e vamos chamar esse tipo. Qual é o alcance das células aqui? Você pode apenas escrever KnownFor porque essa será a nossa lista de nossa mesa. Você percebe como quando eu clicar na tabela aqui, ele ainda diz KnownFor, se eu voltar para fórmulas definir nome, você verá que se eu clicar em tipos, ele faz referência ao meu intervalo de células, que é KnownFor. Agora, vamos para o passo 3. Eu quero criar uma nova coluna na minha tabela principal chamada KnownFor e, em seguida, ir para Validação de Dados que você já fez antes provavelmente e criar uma lista suspensa, onde vamos usar os tipos definir nome. Apenas para recapitular, temos um Definir nome chamado tipos, que faz referência a nossa tabela KnownFor. Vamos clicar em “Coluna J” Conhecido por e agora vamos para “Dados” “Validação de Dados”. Nós vamos mudar isso permitido para listar e qual é a nossa fonte vai ser? Você pode ser tentado a escrever iguais, KnownFor como nossa lista, mas nós temos que realmente usar nosso nome definido chamados tipos, que faz referência a nossa tabela KnownFor. Vamos escrever tipos iguais, clicar em “OK”, e vamos ver o que acontece neste menu suspenso. Você percebe que nós obtemos todos os valores da nossa tabela KnownFor. Eu também posso apenas arrastar esta fórmula para baixo e você pode clicar no menu suspenso, você pode ver todos esses valores. Uma coisa a ter em mente é se você clicar em Fórmulas, Definir Nome e na minha referência de tabela KnownFor aqui, se você acidentalmente selecionou a tabela inteira aqui assim, você percebe como a referência muda de KnownFor para KnownFor Todos conhecidos. Eu sei que isso parece complicado, mas a razão pela qual isso é problemático é que se você acertar ok, isso agora incluirá o cabeçalho da coluna KnownFor dentro do menu suspenso, que você não quer que aconteça. É por isso que vou redefinir isto só para ser conhecido. Dessa forma, você só tem as opções selecionáveis na tabela KnownFor. Se eu arrastar isso até o resto da minha tabela, agora
tenho esse atributo que eu posso selecionar na minha tabela KnownFor. Outra advertência sobre lista suspensa, que você pode ter visto antes é se eu mudar isso de smoothies para Smoothies-YM, você percebe como todos os meus sanduíches ainda permanecem sanduíches. Eu tenho que realmente clicar no menu suspenso e renomear isso para Sandwiches-ym. Apenas para ter certeza se você está corrigindo quaisquer valores aqui em sua lista suspensa KnownFor, você tem que alterá-lo manualmente aqui na sua tabela principal. Agora, é claro, o benefício de usar tabelas do Excel em nossa lista suspensa aqui, é se eu adicionar um novo valor à minha tabela KnownFor, digamos que eu chamo isso de panquecas, eu agora recebo panquecas como uma opção selecionável na minha lista suspensa, o que é muito bom de ver porque eu não tenho que me preocupar com a fórmula referenciando as referências de células adequadas nesta coluna. Eu posso apenas referenciar a tabela através do nome definido. Agora, mais um passo aqui é, podemos simplificar isso um pouco usando a função indireta e nosso campo de origem para a validação de dados. Vamos excluir tudo isso por enquanto e digamos que eu clique em Validação de Dados. Neste campo de origem, vez de escrever tipos, que faz referência a este nome de definição, que faz referência a esta tabela, eu posso realmente escrever a função indireta e escrever o nome da tabela que é KnownFor. Em seguida, coloque o nome da coluna, que é (“KnownFor [KnownFor]”) O que eu estou dizendo ao Excel aqui é me dar a tabela KnownFor, que é este e o nome da coluna é Conhecido por, pressione “OK” e isso também me dará o menu suspenso para a minha validação de dados. Apenas outra maneira de não ter que usar um Nome Definido, que então faz referência à tabela, se você quiser apenas atalho e apenas referenciar a tabela em si usando esta solução alternativa indireta. Para recapitular, pense no que você teria que fazer para criar essa validação suspensa com uma lista de
expansão automática dinamicamente usando algo como a função count ou a função offset. Os benefícios deste Definir Nome Agora é que você pode usá-lo em
qualquer lugar no arquivo do Excel onde você deseja fazer referência a essa tabela específica. Novamente, voltando para a lição anterior, pense em como seus nomes de tabela e seus nomes de definição podem ser variáveis que fazem referência a todas as tabelas diferentes em seu arquivo do Excel. Criamos uma lista suspensa usando o recurso Validação de Dados no Excel. Usamos tabelas do Excel como as opções selecionáveis em nossa lista suspensa. Fizemos uma variedade de coisas diferentes em tabelas
do Excel com referências estruturadas e fórmulas. Agora vamos mergulhar na formatação de nossas tabelas para que elas
fiquem muito bonitas e bonitas para nossa equipe e para nossos colegas.
8. Formatação de tabelas no Excel: Falaremos sobre formas rápidas de formatar as suas tabelas para que elas fiquem muito bonitas e bonitas e bem formatadas para a sua equipa e para os seus colegas. Temos um novo conjunto de dados nesta lição. É uma lista de preços de Bitcoin por data em 2013. Como você pode ver, o preço era obviamente muito mais baixo naquela época. Temos o aberto, alto, baixo, fechar e o spread para esses vários preços diários de Bitcoin. Na etapa 1, quero que você clique no menu suspenso na seção de
formatação de tabela para criar seu próprio formato de tabela personalizado. Se eu clicar na tabela, guia
Tabela na faixa de opções, e se eu clicar nesta lista suspensa aqui você pode ver uma variedade de opções de formatação diferentes que você pode aplicar à sua tabela com base no que você precisa para seus relatórios, seus painéis e também o que seus colegas e colegas de equipe podem achar útil para eles. Agora, uma coisa que é realmente benéfica sobre essas formatações é que você pode personalizar isso ainda mais criando um formato de tabela personalizado clicando com o botão direito do mouse no formato de tabela nesta faixa de opções, clique em Duplicar, e então você pode clicar em vamos chamar isso de AlBitCoinTable. A razão pela qual eu posso querer personalizar minha tabela
neste cenário específico é que talvez eu queira destacar assim como os preços do fim de semana Bitcoin, que é o dia da semana seis e sete,
e, em seguida, destacar o dia da semana preços que são dia da semana um a cinco. Minha faixa de primeira linha, é como a primeira linha codificada por cores. Eu posso mudar isso em vez de ser um tamanho de um, muda para um tamanho de cinco. Em seguida, na minha faixa de segunda linha, eu posso mudar isso de um tamanho de um para um tamanho de dois. O que isso faz é em vez de alternar cores a cada linha, ele alterna cores a cada cinco linhas, e então duas linhas, cinco linhas, duas linhas correspondem a dias de semana e fins de semana. Além disso, você também pode clicar no formato aqui para realmente ir e formatar a cor da banda específica, se você quiser. Eu só vou apertar “Ok” por enquanto. Se eu clicar no menu suspenso novamente aqui em meus formatos de tabela, eu posso clicar neste formato personalizado. Você percebe como a formatação agora muda de cada outra linha para ser codificada por cores, para ser as primeiras cinco linhas, em
seguida, duas linhas para realmente destacar os preços de fim de semana e dia da semana do Bitcoin de 2013. Finalmente, mais uma coisa sobre a formatação de tabelas é que às vezes você pode querer colorir partes específicas de suas colunas. Vamos voltar para a nossa formatação regular de tabela aqui, que é algo parecido com isso. Se você clicar na primeira coluna, você percebe como ela automaticamente destaca a primeira coluna de dados a ser chamada. Última coluna apenas destaca a última coluna e, em seguida, colunas com faixas. Se você tiver um tipo específico de formatação onde você tiver uma codificação de cores em banda. Vamos clicar neste de novo. Observe como, por padrão, ele tem linhas em faixas. Se eu clicar em colunas com faixas e linhas com faixas desmarcadas, realça todas as outras colunas em vez de linhas. Apenas mais alguma formatação adicional que você pode aplicar à sua tabela é baseada nas necessidades da sua equipe. Nós explicamos como você pode personalizar a
formatação da tabela com algumas das formatações integradas do Excel para tabelas do Excel, mas também como você pode personalizar o número alternado de cores de linha para sua tabela do Excel. Aprendemos muito sobre como você pode usar tabelas do Excel para fórmulas, referências estruturadas, formatação. Agora, vamos embrulhar tudo no seu projeto para esta aula.
9. Considerações finais: Parabéns, você fez essa aula em tabelas do Excel, e aprendemos tudo sobre como criar tabelas do Excel, como usar referências estruturadas em suas fórmulas diárias do Excel, como VLookup, alguns SUMIFS. Como você pode aplicá-los a listas suspensas e
validação de dados para que suas tabelas no Excel, seus painéis e relatórios sejam mais fáceis de ler, suas fórmulas são mais fáceis de depurar. Seus colegas ficarão muito mais felizes quando estiverem olhando seus arquivos do Excel. Espero que você pegue todos os ensinamentos desta classe e os aplique ao projeto da classe, onde você estará analisando um conjunto de dados do Departamento de Transporte sobre atrasos de voo. Espero que você compartilhe suas descobertas e capturas de tela de seus relatórios na galeria do projeto para que todos possam ver. Muito obrigado por fazer esta aula, e estou ansioso para ver seus projetos.
Al Chen, Excel Trainer & Coda Evangelist
