No último episódio, Ctrl + T fez a fonte da tabela dinâmica expandir
Em quase todos os seminários, alguém pergunta por que suas tabelas dinâmicas contam com um campo numérico em vez de somar. Existem duas respostas possíveis: ou existem algumas células em branco na coluna numérica ou a pessoa está selecionando colunas inteiras no conjunto de dados (como A: C em vez de A1: C16).
Eu entendo a lógica por trás da segunda possibilidade. Se você escolher todas as colunas A: C e posteriormente quiser adicionar mais registros abaixo dos dados, basta uma simples atualização para adicionar os novos dados em vez de encontrar o ícone Alterar fonte de dados. No passado, isso fazia sentido. Mas hoje, Change Data Source está ao lado do botão Atualizar e não é difícil de encontrar. Além disso, há uma solução alternativa na Tabela Ctrl + T.
Quando você escolhe seu conjunto de dados e seleciona Formatar como Tabela usando Ctrl + T, a origem da tabela dinâmica crescerá conforme a tabela cresce. Você pode até fazer isso retroativamente, depois que a tabela dinâmica existir.
Esta figura mostra um conjunto de dados e uma tabela dinâmica. A origem da tabela dinâmica é A1: C16.
Você deseja adicionar novos dados facilmente abaixo da tabela dinâmica.
Selecione uma célula nos dados e pressione Ctrl + T. Certifique-se de que My Table Has Headers esteja marcada na caixa de diálogo Create Table e clique em OK.
Alguma boa formatação é aplicada ao conjunto de dados. Mas a formatação não é a parte importante.
Você tem alguns novos registros para adicionar à tabela. Copie os registros.
Vá para a linha em branco abaixo da tabela e cole. Os novos registros pegam a formatação da tabela. O marcador de Fim da Tabela em forma de colchete angular move para C19. Mas observe que a tabela dinâmica ainda não foi atualizada.
Clique no botão Atualizar na guia Análise das Ferramentas da Tabela Dinâmica. O Excel adiciona as novas linhas à sua tabela dinâmica.
Dica de bônus
Ctrl + T Ajuda a PROCV e gráficos
Nesta figura, a tabela VLOOKUP está em E5: F9. O item A106 está faltando na tabela e o VLOOKUP está retornando # N / A. A sabedoria convencional diz para adicionar A106 ao meio de sua tabela VLOOKUP para que você não precise reescrever a fórmula.
Em vez disso, use Ctrl + T para formatar a tabela de pesquisa. Observe que a fórmula ainda aponta para E5: F9; nada muda na fórmula.
Mas quando você digita uma nova linha abaixo da tabela, ela se torna parte da tabela e a fórmula VLOOKUP é atualizada automaticamente para refletir o novo intervalo.
A mesma coisa acontece com gráficos. O gráfico à esquerda é baseado em A1: B5, que não é uma tabela. Formate A1: B5 como uma tabela pressionando Ctrl + T. Adicione uma nova linha. A linha é adicionada automaticamente ao gráfico.
É muito legal que você possa usar Ctrl + T após configurar a tabela dinâmica, VLOOKUP ou gráfico, e o Excel ainda faz o intervalo expandir.
Assistir vídeo
- No último episódio, Ctrl + T fez a fonte da tabela dinâmica expandir
- Isso também ajuda VLOOKUP e gráficos e validação de dados
- Embora seja um pouco diferente em cada
- Crie sua VLOOKUP e torne a tabela uma tabela Ctrl + T
- Notavelmente, a fórmula VLOOKUP irá se reescrever
- Construa um gráfico. Faça dos dados de origem uma tabela Ctrl + T. Adicione novos meses.
- Para fonte de validação de dados: faça uma tabela e nomeie o intervalo sem o título
- Use o intervalo nomeado como a fonte de validação
- Também mencionado no episódio: Função FORMULATEXT para mostrar uma fórmula
Transcrição de vídeo
Aprenda Excel para Podcast, Episódio 2002 - CTRL T ajuda a VLOOKUP
Estou fazendo um podcast neste livro inteiro, vá em frente e assine a playlist, canto superior direito, há um I lá em cima e seja bem-vindo de volta ao netcast. Sou Bill Jelen.
Então, no podcast de ontem, falamos sobre como CTRL T faz seus dados de tabela dinâmica crescerem automaticamente. A outra coisa realmente incrível, aqui, é que eu tenho uma VLOOKUP. Então aí está o PROCV e você está vendo a FÓRMULA aqui, obrigado pela FUNÇÃO TEXTO DA FÓRMULA. Eu adoro FORMULA TEXT. Era novo no Excel 2013. Me permite mostrar a FÓRMULA e os resultados lado a lado. Ok e você pode ver que esta FÓRMULA está apontando para uma Tabela aqui que tem uma, duas, três, quatro ou cinco linhas, mas há algo faltando. Portanto, A106. Tudo bem, aqui está a coisa incrível que vou levar esta mesa. Esta pequena tabela VLOOKUP aqui. Vou fazer CTRL T, para torná-la uma tabela real. Minha tabela tem cabeçalhos e, em seguida, vou vir aqui e digitar A106, o item ausente, fora do intervalo, e isso 's $ 88 e você viu isso? A FÓRMULA se reescreveu automaticamente para descer até a linha F10. Ele não se reescreveu para se referir à Tabela, usando a nomenclatura Tabela, mas simplesmente funcionou.
Aqui está outro exemplo em que CTRL T torna as coisas melhores. Aqui está um gráfico, de janeiro a abril, aqui está os dados, vou CTRL T os dados e observo em todos esses casos o VLOOKUP, o gráfico, estava tudo lá, apenas de um intervalo regular e agora quando eu adiciono novos dados , então aqui está maio e daremos 15.000 para crescer automaticamente. Tudo bem, e quando eu olho para a série de gráficos, porque estou fascinado sobre como isso funciona, a série de gráficos não é reescrita na nomenclatura da Tabela, mas simplesmente diz, oh, ei, esta é uma Tabela que iremos estender da linha cinco à linha seis. E aqui está outro. Eu peguei este aqui, este não está no livro, isso é um bônus. Peguei isso em uma conferência incrível em Lucerna, Suíça, chamada Trainer Tage. Isso é alemão para Trainer Days. Esses são,o the Trainer Tage Team, tive a sorte de falar lá por dois anos, Tanya Kuhn nos colocou e viu esse truque incrível.
Portanto, queremos uma lista de validação de dados e podemos adicionar mais coisas ao final da lista de validação de dados. Então, aqui está minha lista. Vou pressionar CTRL T para transformá-la em uma Tabela e, em seguida, nomearei tudo com muito cuidado, exceto o título. Então, vou chamá-lo de MyList ENTER. Certo, então acabamos de criar um nome lá e aqui vamos para Dados e, em seguida, é o menu suspenso, escolha Validação de dados. Vamos permitir uma lista e a fonte será = MyList ENTER. Tudo bem, então agora, o que devemos esperar ver é que a Apple jogou Fig estaria lá. Bonito. Tudo bem, mas quando eu chegar e digitar um novo item, esse marcador de fim de tabela irá se mover para o final da linha 8 e, surpreendentemente, estará na lista. Certo, todos esses são benefícios colaterais incríveis de usar tabelas.
Tudo bem agora, é claro que vou pedir para você comprar meu livro, mas antes de fazer isso, devo dar crédito a Zach Barresse e Kevin Jones que escreveram O livro sobre tabelas do Excel. Certo, se você precisa aprender alguma coisa sobre o Tables ou apenas ver todas as coisas incríveis que acontecem quando você usa o Tables, dê uma olhada neste livro de Zach e Kevin. Tudo bem sim, e claro, desejo que você compre meu livro, tanto conhecimento na palma da sua mão. Todas as dicas de todos os podcasts de agosto e setembro. Ali. 10 dólares é um e-book, 25 dólares é um livro impresso. Clique no I no canto superior direito.
Tudo bem, então uma recapitulação aqui. No último episódio, usamos CTRL T para expandir a Fonte da Tabela Dinâmica. Também ajuda a VLOOKUP e gráficos e validação de dados. É um pouco diferente em cada um, mas você sabe, mesmo depois que VLOOKUP e os gráficos são configurados, você pode, após o fato, torná-lo uma tabela e o VLOOKUP e os gráficos irão expandir. Então crie sua VLOOKUP e depois faça a Tabela, a Tabela VLOOKUP da Tabela CTRL T e a FÓRMULA apenas se reescreve. É tão legal. Ou construa um gráfico e, em seguida, transforme-o em uma Tabela CTRL T e, conforme você adiciona novos dados, o gráfico se expande automaticamente para a validação de dados. No momento, isso é de Tanya na Suíça, torne-o uma Tabela e nomeie o intervalo sem o título e, em seguida, use o intervalo de nome como a Fonte de validação. Também mencionei a função Form Of The Text.
Tudo bem agora, quando peço às pessoas que enviem suas dicas favoritas, as tabelas são populares. Tudo bem, Peter Albert, Snorri Islândia, Nancy Federici, Colin Michael, James Mead, KR Patel, Paul Payden e um grupo de pessoas sugeriram o uso de OFFSET para criar faixas de expansão para gráficos dinâmicos. Charlie, Don, Francis e Cecilia. As tabelas agora fazem a mesma coisa na maioria dos casos, então você não precisa mais do OFFSET. Então, eu realmente peguei as ideias deles e as joguei fora e coloquei as tabelas, mas ainda agradeço o envio de suas ideias
Agradeço a sua visita. Nos vemos na próxima vez para outro netcast de.
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2002.xlsx
