Como a tabela de dados no Microsoft Excel permite comparar resultados

gurinho

Sempre quis experimentar diferentes valores para uma fórmula para ver qual saída você obtém para cada um? A Tabela de Dados no Excel é uma ótima ferramenta para dar uma olhada nas possibilidades.

Análise de variações hipotéticas no Excel: a tabela de dados

As ferramentas de análise de variações hipotéticas do Excel são um excelente conjunto de funções que permitem prever a mudança nos dados de saída em relação à mudança nos valores. O Excel tem três ferramentas de análise de variações hipotéticas: Gerenciador de cenários, Atingir metas e Tabela de dados.

A ferramenta Tabela de dados permite que você visualize como diferentes entradas afetarão o resultado de sua fórmula. Você pode usar a Tabela de dados para obter uma perspectiva de sua fórmula e ver qual saída você pode obter de diferentes entradas.

Como funciona a tabela de dados

A Tabela de dados no Excel pega um conjunto de entradas, coloca-as em sua fórmula e, finalmente, cria uma tabela das saídas para cada entrada.

Para utilizar a Tabela de Dados no Excel, você precisa primeiro ter uma fórmula pronta. Então, você pode consultar a fórmula em outra célula e trabalhar a Tabela de Dados nela. Finalmente, você pode alimentar a Tabela de dados com dois conjuntos de dados: célula de entrada de linha e célula de entrada de coluna .

A Tabela de dados tomará então os valores na linha adjacente como entrada para a célula de entrada Linha e os valores na coluna adjacente como entrada para a célula de entrada Coluna e criará uma tabela de resultados da fórmula.

Relacionado: Como criar uma tabela dinâmica no Excel

Exemplo 1: Tabela de dados de duas variáveis

Para este exemplo, suponha que você tenha seis carrinhos de brinquedo com preços diferentes e deseja saber quanta receita obterá com a venda de determinadas quantidades deles.

Para fazer isso, você precisa multiplicar o número de carros de brinquedo vendidos de um tipo pelo preço e, finalmente, subtrair a taxa de imposto para obter a receita.

Portanto, em suma, para esta Tabela de Dados, você terá duas entradas variáveis: a quantidade e o preço. Primeiro, vamos criar a fórmula:

  1. Nas células A1 , B1 , C1 e D1 , digite Preço , Quantidade , Imposto e Receita , respectivamente.
  2. Selecione as células A1 e D1 .
  3. Na guia Página Inicial , na seção Números , clique no símbolo $ para alterar a Formatação de Número dessas células para Contabilidade (isso ocorre porque essas células irão hospedar os valores contábeis).
  4. Selecione a célula C2 .
  5. Na guia Página Inicial , na seção Números , clique no símbolo % para alterar a Formatação do Número desta célula para Porcentagem .
  6. Selecione a célula D2 em Receita e insira a seguinte fórmula na barra de fórmulas e pressione Enter:
     =(A2*B2)-(A2*B2*C2)

    Esta fórmula multiplicará o preço das unidades vendidas (A2) por sua quantidade (B2) e, em seguida, subtrairá o valor do imposto (A2 * B2 * C2).

Você pode ir em frente e fornecer valores de amostra para as células e observar como o Excel calcula a receita obtida com as vendas.

Relacionado: Como ocultar ou reexibir colunas e linhas no Excel

Tabela de dados de diferentes entradas do Excel

Para criar uma tabela de dados para diferentes entradas, você precisa ter uma tabela vazia contendo as duas entradas.

  1. Selecione a célula G2 e insira a fórmula abaixo na barra de fórmulas:
     =D2

    Isso definirá a célula G2 igual à fórmula criada anteriormente.

  2. Nas células abaixo de G2 (coluna G), insira as quantidades possíveis de peças vendidas. Para este exemplo, os números são 5, 10, 15, 20, 25 e 30.
  3. Nas células próximas a G2 (linha 2), insira os preços de cada peça. Para este exemplo, os preços são 10, 20, 30, 40, 50 e 60.
  4. Selecione as células onde você inseriu os preços e as células abaixo, que exibirão as receitas possíveis, e altere sua Formatação de Número para Contabilidade .

Finalmente, agora que você tem a linha e a coluna configuradas, é hora de transformar esta tabela em uma Tabela de Dados.

  1. Selecione a tabela clicando em G1 e arrastando-a até M7 .
  2. Vá para a guia Dados e, na seção Previsão, clique em Análise de variações hipotéticas . Uma lista de três itens aparecerá.
  3. Na lista, selecione Tabela de dados . Isso abrirá a caixa de diálogo Tabela de dados.
  4. Na célula de entrada Linha, insira A2 . A linha da tabela contém os preços e a entrada de preços na fórmula original é a célula A2.
  5. Na célula de entrada Coluna, insira B2 . A coluna da tabela contém as quantidades de peças vendidas.
  6. Depois de definir as duas entradas, clique em OK . O Excel agora irá gerar uma Tabela de Dados.

Agora você tem uma tabela de dados para sua fórmula! A Tabela de dados fornece informações valiosas sobre suas vendas potenciais.

Por exemplo, agora você pode ver como obter uma receita de $ 1000 ou mais olhando para as seis células no canto inferior direito. Ou você pode comparar e saber que vender 25 de carros de brinquedo de $ 20 vai gerar mais receita do que vender 15 de carros de brinquedo de $ 30.

Exemplo 2: Tabela de dados de uma variável

Visto que a Tabela de dados é, afinal, uma tabela, ela só pode abrigar entradas em uma linha e uma coluna. Isso significa que você não pode ter mais de duas entradas de variáveis ​​em uma tabela de dados. No entanto, você certamente pode ter menos de dois: Uma Tabela de Dados com entrada de variável única.

Para este exemplo, lembre-se do exemplo anterior. No entanto, desta vez, suponha que você deseja obter uma tabela de receita potencial exclusivamente para os carrinhos de brinquedo de $ 50.

O método ainda é o mesmo da tabela de dados de duas variáveis, embora o posicionamento seja ligeiramente diferente.

Relacionado: Como copiar fórmulas no Microsoft Excel

Para começar a criar a Tabela de dados, você precisa criar uma fórmula. A fórmula para este exemplo é igual à anterior. Depois de ter a fórmula pronta, é hora de definir os dados.

  1. Selecione a célula H1 e na barra de fórmulas, insira a fórmula abaixo e pressione Enter :
     =D2
  2. Insira os números nas células G2 e abaixo. Para este exemplo, insira 5, 10, 15, 20, 25 e 30 nas células G2 a G7 .

Agora é hora de criar a Tabela de dados.

  1. Selecione a tabela clicando em G1 e arrastando-a para H7 .
  2. Vá para a guia Dados e, na seção Previsão, clique em Análise de variações hipotéticas .
  3. Na lista Análise de variações hipotéticas, selecione Tabela de dados .
  4. Na caixa de diálogo Tabela de dados, clique na célula de entrada da coluna e digite B2 .
  5. Deixe a célula de entrada Linha vazia.
  6. Como o objetivo é obter uma Tabela de dados de receita por um preço fixo, você só precisa alimentar a Tabela de dados com o número de carrinhos de brinquedo vendidos e não seus preços. A quantidade é definida na linha da tabela de dados e a entrada para ela na fórmula é a célula B2.
  7. Clique OK . O Excel criará uma tabela de dados de uma variável.

Portanto, agora você pode descobrir quanta receita pode gerar com a venda de carros de brinquedo de US $ 50, sem os impostos, é claro.

Defina as possibilidades em uma mesa

A Tabela de dados oferece uma boa visão do resultado de sua fórmula com diferentes entradas, e agora você sabe como criar uma.

A Tabela de dados é apenas uma das ferramentas de análise de variações hipotéticas do Excel. Há mais informações se você quiser responder a todas as suas perguntas de variações hipotéticas com o Excel.