O tutorial mostra como usar tabelas de dados para análise no Excel. Aprenda como criar uma tabela de uma variável e duas variáveis para ver os efeitos de um ou dois valores de entrada em sua fórmula, e como configurar uma tabela de dados para avaliar múltiplas fórmulas de uma vez.

Você construiu uma fórmula complexa dependente de várias variáveis e quer saber como mudar essas entradas altera os resultados., Em vez de testar cada variável individualmente, faça uma tabela de dados do What-if e observe todos os resultados possíveis com um olhar rápido!

  • tabela de Dados no Excel
  • Criar uma variável de tabela de dados
  • Criar duas variáveis de uma tabela de dados
  • tabela de Dados para avaliar várias fórmulas
  • Excel tabelas de dados – 3 coisas que você deve saber
  • Excluir uma tabela de dados no Excel
  • Editar dados de tabela de resultados
  • Recalcular a tabela de dados manualmente

o Que é uma tabela de dados no Excel?,

no Microsoft Excel, Uma tabela de dados é uma das ferramentas de Análise o que-If que lhe permite experimentar diferentes valores de entrada para fórmulas e ver como as mudanças nesses valores afetam a saída de fórmulas.as tabelas de dados

são especialmente úteis quando uma fórmula depende de vários valores, e você gostaria de experimentar com diferentes combinações de Entradas e comparar os resultados.actualmente, existe uma tabela de dados variável e duas tabelas de dados variáveis. Embora limitado a um máximo de duas células de entrada diferentes, uma tabela de dados permite testar quantos valores variáveis você quiser.,Nota: Uma tabela de dados não é a mesma coisa que uma tabela Excel, que é propositada para gerenciar um grupo de dados relacionados. Se você está procurando aprender sobre muitas maneiras possíveis de criar, limpar e formatar uma tabela Excel regular, não tabela de dados, por favor confira este tutorial: Como fazer e usar uma tabela no Excel.

Como criar uma variável de dados da tabela no Excel

Uma variável de tabela de dados no Excel permite testar uma série de valores para uma única célula de entrada e mostra como esses valores influenciam o resultado de uma fórmula.,

para ajudá-lo a entender melhor este recurso, Vamos seguir um exemplo específico em vez de descrever passos genéricos.suponha que está a considerar depositar as suas poupanças num banco, que paga 5% de juros que compõe mensalmente. Para verificar as diferentes opções, você construiu a seguinte calculadora de juros compostos onde:

  • B8 contém a fórmula FV que calcula o saldo final.
  • B2 é a variável que pretende testar (investimento inicial).,

E agora, vamos fazer uma análise simples do que-se para ver qual será a sua poupança em 5 anos, dependendo do montante do seu investimento inicial, variando de $1.000 a $6.000.

Aqui estão os passos para fazer uma tabela de dados de uma variável:

  1. indique os valores da variável numa coluna ou numa linha. Neste exemplo, nós vamos criar uma tabela de dados orientada a colunas, então nós digitar nossos valores variáveis em uma coluna (D3:D8) e deixar pelo menos uma coluna em branco à direita para os resultados.,
  2. escreva a sua fórmula na célula uma linha acima e uma célula à direita dos valores variáveis (E2 no nosso caso). Ou, ligue esta célula à fórmula do seu conjunto de dados original (se decidir alterar a fórmula no futuro, terá de actualizar apenas uma célula). Escolhemos esta última opção, e introduzimos esta fórmula simples em E2: =B8
    Tip., Se você quiser examinar o impacto dos valores da variável em outras fórmulas que se referem à mesma célula de entrada, introduza a fórmula adicional(s) à direita da primeira fórmula, como mostrado neste exemplo.
  3. selecione o intervalo da tabela de dados, incluindo a sua fórmula, células de valores variáveis e células vazias para os resultados (D2:E8).,
  4. Vá para a guia Dados > de Dados, grupo Ferramentas, clique na Análise de variações hipotéticas botão e, em seguida, clique em Tabela de Dados…
  5. Na caixa de diálogo Tabela de Dados da janela, clique na caixa célula de Entrada da Coluna (porque o nosso Investimento valores em uma coluna), e selecione a variável de célula referenciada na fórmula. Neste exemplo, selecionamos B3 que contém o valor de investimento inicial.
  6. Click OK, and Excel will immediately populate the empty cells with outcomes corresponding to the variable value in the same row.,
  7. aplique o formato de número desejado para os resultados (moeda no nosso caso), e você está pronto para ir!

Agora, você pode dar uma olhada rápida em sua variável de tabela de dados, analisar a possível saldos e escolher a melhor depósito tamanho:

Linha orientada a dados da tabela

O exemplo acima mostra como configurar uma vertical, ou coluna-orientado, a tabela de dados no Excel., Se preferir uma disposição horizontal, eis o que precisa de fazer:

  1. escreva os valores das variáveis numa linha, deixando pelo menos uma coluna vazia à esquerda (para a fórmula) e uma linha vazia em baixo (para os resultados). Para este exemplo, introduzimos os valores variáveis nas células F3: J3.
  2. indique a fórmula na célula que é uma coluna à esquerda do seu primeiro valor variável e uma célula abaixo (E4 no nosso caso).,
  3. criar uma tabela de dados, como discutido acima, mas, digite o valor de entrada (B3) na célula de entrada de Linha de caixa de:
  4. Clique em OK, e você terá o seguinte resultado:

Como fazer uma de duas variáveis de uma tabela de dados no Excel

Uma tabela de duas variáveis dados mostram várias combinações de 2 conjuntos de valores das variáveis afetam o resultado da fórmula. Em outras palavras, ele mostra como a mudança de dois valores de entrada da mesma fórmula muda a saída.,

os passos para criar uma tabela de dados de duas variáveis no Excel são basicamente os mesmos que no exemplo acima, exceto que você digita dois intervalos de possíveis valores de entrada, um em linha e outro em uma coluna.

para ver como funciona, vamos usar a mesma calculadora de juros composta e examinar os efeitos do tamanho do investimento inicial e o número de anos no saldo. Para o fazer, configure a sua tabela de dados desta forma:

  1. introduza a sua fórmula numa célula em branco ou ligue essa célula à sua fórmula original., Certifique-se de que tem colunas vazias suficientes para a direita e linhas vazias abaixo para acomodar os seus valores variáveis. Como antes, ligamos a célula E2 à fórmula FV original que calcula o saldo: =B8
  2. tipo um conjunto de valores de entrada abaixo da fórmula, na mesma coluna (valores de investimento em E3:E8).
  3. introduza o outro conjunto de valores variáveis à direita da fórmula, na mesma linha (número de anos em F2:H2).,

    neste ponto, a sua tabela de dados variável deve ser semelhante a esta:

  4. seleccione toda a gama de dados, incluindo a fórmula, a linha e coluna dos valores variáveis, e as células em que os valores calculados irão aparecer. Nós selecionamos o alcance E2: H8.,
  5. Criar uma tabela de dados no já familiar: guia de Dados > Análise de variações hipotéticas botão > Tabela de Dados…
  6. Na caixa célula de entrada de Linha de caixa, insira a referência para a célula de entrada para os valores da variável na linha (neste exemplo, é B6 contendo os Anos de valor).
  7. na coluna célula de entrada, indique a referência à célula de entrada para os valores variáveis na coluna (B3 contendo o valor de investimento inicial).clique em OK.,n o nosso caso), e analisar os resultados:

Dados da tabela para comparar vários resultados

Se você deseja avaliar mais de uma fórmula ao mesmo tempo, construir a sua tabela de dados, como mostrado nos exemplos anteriores, e introduza a fórmula adicional(s):

  • Para a direita da primeira fórmula, no caso de uma vertical tabela de dados organizados em colunas
  • Abaixo a primeira fórmula no caso de um horizontais da tabela de dados organizados em linhas

Para o “multi-fórmula” tabela de dados para funcionar corretamente, todas as fórmulas devem se referir à mesma célula de entrada.,

Como exemplo, vamos adicionar mais uma fórmula à nossa tabela de dados de uma variável para calcular o interesse e ver como ele é afetado pelo tamanho do investimento inicial. Aqui está o que fazer:

  1. Na célula B10, calcular o interesse com esta fórmula: =B8-B3
  2. Organizar os dados da tabela de origem de dados, como fizemos anteriormente: os valores da variável D3:D8 e E2 vinculada ao B8 (fórmula de Equilíbrio).,
  3. adicione mais uma coluna ao intervalo da tabela de dados (coluna F) e ligue F2 a B10 (fórmula de juros):
  4. Seleccione o intervalo da tabela de dados alargada (D2:F8).
  5. abre a janela da tabela de dados clicando na página de dados > What-If Analysis> Data Table…
  7. na coluna da célula de entrada, forneça a célula de entrada (B3) e carregue em OK.,

Pronto, agora você pode observar os efeitos de seus valores de variáveis em ambas as fórmulas:

Dados da tabela no Excel – 3 coisas que você deve saber

Para efetivamente utilizar tabelas de dados no Excel, por favor, mantenha em mente estas 3 simples fatos:

  1. Para uma tabela de dados a ser criado com êxito, a célula de entrada(s) deve ser na mesma folha que a tabela de dados.,
  2. Microsoft Excel usa a função TABLE (row_input_cell, colum_input_cell) para calcular os resultados da tabela de dados:
    • na tabela de dados de uma variável, um dos argumentos é omitido, dependendo da disposição (orientada em coluna ou orientada em linha). Por exemplo, em nossa tabela de dados horizontal de uma variável, a fórmula é =TABLE(, B3) onde B3 é a célula de entrada da coluna.
    • na tabela de dados de duas variáveis, ambos os argumentos estão no lugar. Por exemplo, =TABLE(B6, B3) where B6 is the row input cell and B3 is the column input cell.,

    a função da tabela é introduzida como uma fórmula de matriz. Para ter certeza disso, selecione qualquer célula com o valor calculado, olhe para a barra de fórmulas, e anote os parêntesis {curly} em torno da fórmula. No entanto, não é uma fórmula de array normal – você não pode digitá-lo na barra de fórmulas nem pode editar uma existente. É apenas “para mostrar”.

  3. Porque os resultados da tabela de dados são calculados com uma fórmula de matriz, as células resultantes não podem ser editadas individualmente. Você só pode editar ou excluir toda a lista de células como explicado abaixo.,

como apagar uma tabela de dados no Excel

como mencionado acima, o Excel não permite excluir valores em células individuais contendo os resultados. Sempre que você tentar fazer isso, uma mensagem de erro “Não pode mudar parte de uma tabela de dados” irá aparecer.

no entanto, você pode facilmente limpar toda a matriz dos valores resultantes. Aqui está como:

  1. dependendo das suas necessidades, Selecione Todas as células da tabela de dados ou apenas as células com os resultados.carregue na tecla Delete.feito!, 🙂

    como editar os resultados da tabela de dados

    Uma vez que não é possível alterar parte de uma matriz no Excel, você não pode editar células individuais com valores calculados. Você só pode substituir todos esses valores com o seu próprio, realizando estes passos:

    1. Selecione Todas as células resultantes.
    2. apaga a fórmula da tabela na barra de fórmulas.
    3. escreva o valor desejado e carregue em Ctrl + Enter.,

    Isto irá inserir o mesmo valor em todas as células selecionadas:

    uma Vez que a fórmula de TABELA é ido, o ex-tabela de dados torna-se um intervalo normal, e você está livre para editar qualquer célula individual normalmente.

    Como recalcular a tabela de dados manualmente

    Se uma grande tabela de dados com vários valores de variáveis e fórmulas retarda o Excel, você pode desativar recálculos automáticos e todas as outras tabelas de dados.,

    para isso, vá para a página fórmulas > grupo de cálculo, clique no botão Opções de cálculo e, em seguida, clique em automático exceto tabelas de dados.

    Isto irá desligar os cálculos automáticos da tabela de dados e acelerar recalculações de todo o livro de trabalho.

    para recalcular manualmente a sua tabela de dados, seleccione as células resultantes, ou seja, as células com fórmulas da tabela (), e carregue em F9.

    Esta é a forma como você cria e usa uma tabela de dados no Excel., Para ter uma olhada mais de perto nos exemplos discutidos neste tutorial, você é bem-vindo para baixar nosso workbook de tabelas de dados do Excel de amostra. Agradeço-lhe por ler e ficaria feliz em vê-lo novamente na próxima semana!

    • What-If analysis with Goal Seek in Excel
    • How to use Solver in Excel: step-by-step with examples
    • How to group rows in Excel