A análise de variações hipotéticas do Excel oferece uma tabela de dados. Este é um nome ruim. Deve ser chamado de Análise de Sensibilidade. É legal. Leia sobre isso aqui.
Goal Seek permite encontrar o conjunto de entradas que levam a um resultado específico. Às vezes, você deseja ver muitos resultados diferentes de várias combinações de entradas. Contanto que você tenha apenas duas células de entrada para alterar, a tabela de dados fornece uma maneira rápida de comparar alternativas.
Usando o exemplo de pagamento de empréstimo, digamos que você deseja calcular o preço de vários saldos de principal e de vários termos.
Certifique-se de que a fórmula que você deseja modelar está no canto superior esquerdo de um intervalo. Coloque vários valores para uma variável na coluna esquerda e vários valores para outra variável na parte superior.
Na guia Dados, selecione Análise de variações hipotéticas, tabela de dados.
Você tem valores na linha superior da tabela de entrada. Você deseja que o Excel conecte esses valores a uma determinada célula de entrada. Especifique essa célula de entrada como a Célula de Entrada de Linha.
Você tem valores ao longo da coluna esquerda. Você quer esses plugados em outra célula de entrada. Especifique essa célula como a célula de entrada da coluna.
Quando você clicar em OK, o Excel repetirá a fórmula na coluna superior esquerda para todas as combinações da linha superior e coluna esquerda. Na imagem abaixo, você vê 60 pagamentos de empréstimos diferentes com base em vários resultados.
Observe que formatei os resultados da tabela para não ter decimais e usei Home, Formatação condicional, Escala de cores para adicionar o sombreamento vermelho / amarelo / verde.
Aqui está a grande parte: esta mesa é "ao vivo". Se você alterar as células de entrada ao longo da coluna esquerda ou linha superior, os valores na tabela serão recalculados. Abaixo, os valores à esquerda estão focados na faixa de $ 23K a $ 24K.
Obrigado a Owen W. Green por sugerir tabelas.
Assistir vídeo
- Três ferramentas hipotéticas no Excel
- Ontem - Atingir Meta
- Hoje - uma tabela de dados
- Ótimo para problemas de duas variáveis
- Curiosidades: a função de array TABLE não pode ser inserida manualmente - não funcionará
- Use uma escala de cores para colorir as respostas
- E se você tiver 3 variáveis para alterar? Cenários? Não! Copiar planilha
- As tabelas demoram para calcular: modo de cálculo para todas as tabelas, exceto
- Obrigado a Owen W. Green por sugerir esta dica
Transcrição de vídeo
Aprenda Excel com o podcast, episódio 2034 - What-ifs with a data table!
Estou fazendo um podcast deste livro inteiro, clique no “i” no canto superior direito para acessar a lista de reprodução!
Hoje vamos falar sobre a segunda ferramenta na análise de variações hipotéticas, ontem falamos sobre a busca de metas, hoje vamos cobrir uma tabela de dados. Portanto, temos este pequeno modelo agradável aqui, este é um modelo pequeno, 3 células de entrada, uma fórmula. Mas este modelo pode ter centenas de células de entrada, milhares de linhas, desde que se reduza a uma resposta final, e queremos modelar essa resposta para vários valores diferentes de células de entrada 2-3 (?). Por exemplo, talvez estejamos interessados em olhar para carros diferentes, então algo em torno de 20000 para cima, então colocarei 20 e 21000 lá, agarrei a alça de preenchimento e arraste, diminuindo para 28000. No topo nós ' estamos considerando termos diferentes, portanto, um empréstimo de 36 meses, um empréstimo de 42 meses, um empréstimo de 48 meses, 54, 60, 66 e até mesmo 72.
Tudo bem agora, este próximo passo é completamente opcional, mas realmente me ajuda a pensar sobre isso, eu sempre mudo as cores dos valores na parte superior e os valores na esquerda. E o que é realmente importante aqui é que aquela célula de canto, aquela célula de canto muito importante, tem que ser a resposta que estamos tentando modelar, certo. Portanto, você deve começar a selecionar a partir dessa célula de canto com a resposta e, em seguida, selecionar todas as linhas e todas as colunas. Então, entramos em Dados, Análise de variações hipotéticas e Tabela de dados, e ele está pedindo duas coisas aqui, e aqui está como você pensa sobre isso. Diz que há um monte de itens diferentes ao longo da linha superior da tabela. Quero pegar esses itens, um de cada vez, e conectá-los ao modelo, onde devemos inserir? Então esses itens, esses são os termos, eles devem ir para a célula B2. E depois,há um monte de itens ao longo da coluna da esquerda, queremos pegá-los, um de cada vez, e conectá-los ao B1, assim, tudo bem e clicarmos em OK, BAM, ele executa esse modelo indefinidamente .
Agora, só um pouco de limpeza aqui, eu sempre entro e faço Home, e provavelmente 0 casas decimais, assim. E talvez um pouco de Formatação Condicional, Escalas de Cores, e vamos usar números vermelhos para números grandes e números verdes para pequenos, apenas para me dar uma, você sabe, maneira de rastrear isso visualmente. Agora, parece que se estamos atirando em $ 425, estamos meio, você sabe, neste local ou neste local, ou você sabe, talvez aqui, todos nós conseguiremos perto de $ 425 Portanto, posso ver quais são as várias probabilidades, nossas várias combinações, para nos levar a esses valores.
Agora, algumas coisas, esta parte aqui dentro, é na verdade uma grande fórmula de matriz, então = TABLE (B2, B1), a entrada de linha e coluna. Isso é curioso, você não tem permissão para digitar isso, você só pode criar isso usando Dados, Análise de variações hipotéticas, você tem que usar essa caixa de diálogo. Se você tentar digitar essa fórmula, pressione Ctrl + Shift + Enter, ela não funcionará, certo? Portanto, é uma função no Excel, mas se você for inteligente o suficiente para digitá-la, que pena, não vai funcionar, mas é constantemente recalculada. Então, se determinarmos que estamos olhando apenas para os termos de 48, e queremos olhar em grupos de 3 ou algo assim, então, conforme eu mudo esses números, tudo isso é cálculo. Nesse caso, ele está fazendo apenas uma fórmula para cada, mas imagine se estivéssemos fazendo 100 fórmulas, isso fica dramaticamente mais lento. Então aqui em Fórmulas, lá 'Na verdade, é uma opção Opções de Cálculo, Automático ou Manual, há um terceiro que diz “Sim, recalcule tudo, exceto as Tabelas de Dados, não continue recalculando a tabela de dados”. Porque isso pode ser um grande entrave nos tempos de cálculo.
Tudo bem agora, as tabelas de dados são fantásticas quando você tem duas variáveis para alterar, mas temos três variáveis para alterar. E se houvesse taxas de juros diferentes, recomendo ir ao Gerenciador de cenários? NÃO, NUNCA recomendo ir ao Scenario Manager! Neste caso, temos 9x7, são 63 cenários diferentes que calculamos aqui, para criar 63 cenários diferentes do Scenario Manager levaria 2 horas, é horrível. Não abordo isso no livro “MrExcel XL”, porque são as 40 melhores dicas. Provavelmente está no meu livro “Power Excel” com 567 mistérios do Excel resolvidos, mas tenho certeza de que reclamei sobre como é péssimo de usar, vocês não me verão fazendo o Scenario Manager aqui. Se realmente tivéssemos que fazer isso para várias taxas diferentes, a melhor coisa a fazer é apenas Ctrl-arrastar, pegar esta folha, Ctrl-arrastar, Ctrl-arrastar,Arraste com a tecla Ctrl pressionada e altere as taxas em cada folha. Portanto, se pudéssemos obter 5% ou 4,75% ou algo assim e assim por diante, certo, não há uma maneira fácil de configurar isso para 3 variáveis no Gerenciador de cenários. Tudo bem, “40 melhores dicas de Excel de todos os tempos”, todas neste livro, você pode comprar o livro, clique no “i” no canto superior direito.
Recapitulação do episódio de hoje: Existem três ferramentas de variações hipotéticas no Excel, ontem falamos sobre Atingir Meta, hoje Tabela de Dados. É incrível para problemas de 2 variáveis, amanhã você verá um com um problema de 1 variável. A função de matriz de tabela não pode ser inserida manualmente, ela não funcionará, você deve usar Dados, Análise de variações hipotéticas, Tabela de dados. Usei uma escala de cores, Home, Formatação Condicional, Escalas de Cores, para colorir as respostas. Se você tem 3 variáveis para mudar, você faz cenários? Não, basta fazer cópias da planilha ou da tabela, eles demoram para calcular, principalmente com um modelo complexo. Há um modo de cálculo para Automático para todas as tabelas, exceto, e Owen W. Green sugeriu incluir esse recurso nos livros.
Então, obrigado a ele, e obrigado pela visita, nos vemos na próxima vez para outro netcast do!
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2034.xlsx
