Cálculo do Excel com base na entrada do usuário dos campos suspensos

Question

Estamos usando o Excel 2003, mas podemos atualizar, se necessário. Precisamos calcular (ou pesquisar) um valor baseado em duas entradas. Ambas as entradas serão caixas suspensas do Excel.

O primeiro terá valores 6 , 9 , 12 , 16 , 20 . O segundo menu suspenso tem 6x6 , 10x10 , 20x20 .

Como podemos procurar o valor correspondente e colocar o resultado em uma célula separada?

    

Answer 1

Não precisa ser tão complexo assim. Tomemos por exemplo o seguinte.

1. Selecione seus cabeçalhos de coluna e nomeie-os de Entrada1. (Intervalo E1: I1 no exemplo.)
2. Selecione seus cabeçalhos de linha e nomeie-os como Entrada2. (Intervalo D2: D4)
3. Selecione seus dados e nomeie-os como Dados. (Intervalo E2: I4)
4. Use a validação de dados para obter os menus suspensos.
   • Selecione a célula B2 e clique em Dados - Validação de dados.
   • Definir critérios de validação Permitir para List e Origem para =Input1
   • Faça o que quiser para Mensagem de entrada e Alerta de erro. (Eu os desliguei)
   • Repita para sua segunda entrada. (cell B3 obtém a fonte =Input2 )
5. Na sua célula de resultados, digite esta fórmula =INDEX(Data,MATCH(B3,Input2,0),MATCH(B2,Input1,0))

A fórmula funciona da seguinte forma:

MATCH(lookup_value, lookup_array, [match_type])

MATCH é semelhante ao VLOOKUP , exceto que, em vez de retornar um valor de célula / célula, ele retorna a posição relativa de lookup_value dentro do lookup_array . Usá-lo no intervalo Input1 retorna a posição relativa da coluna ao usá-la em Input2 retorna a linha relativa. A configuração de match_type para 0 diz ao Excel para retornar apenas a correspondência exata.

INDEX(reference, row_num, [column_num], [area_num])

INDEX está retornando a célula em Data encontrada na linha referenciada de Input2 e na coluna referenciada em Input1 .

Nota: No Excel 2010 (não tenho certeza sobre versões anteriores), a tabela de dados nem precisa estar na mesma planilha que as células de entrada e de resultados.

    

Answer 2

Existe uma maneira de fazer isso, mas não é tão intuitivo quanto você imagina. Para conseguir o que você quer, você pode usar o mesmo tipo de layout de tabela que você tem, mas você precisará adicionar um pouco mais:

• Tabela de dados do usuário : pode ter o mesmo formato que você já tem, com as dimensões à esquerda e tipo / tamanho na parte superior e os dados relevantes para onde vai.

• Dadosdeformulárioeíndices:Comovocêtemcaixasdecombinação,ouseja,controlesdeformulário,naplanilhadoExcel,énecessáriovincularadequadamenteessasinformações.Parafazerisso,verifiqueaseçãodeligaçãodestarespostanaparteinferior;masésuficientedizerquevocêprecisainformaraoformulárioquaisdimensõesetiposvocêpossui,paraqueelesapareçamnascaixasdecombinação.Vocêtambémprecisainformaràplanilhadoexcelquaisíndicessãoparaqualvalor,poisascaixasdecombinaçãonãoretornamnenhumdado,anãoseroíndicereal.

• Resultados e fórmula : o usuário pode selecionar as dimensões e os tipos desejados e você pode ver quais são os dados; o resultado também é calculado com a fórmula listada lá. Você pode ver, a partir dos dados, como a fórmula é montada. Você precisaria personalizar os intervalos e os locais com base em sua planilha e onde você colocou tudo, mas é por isso que eu forneci a seção detalhamento .

AdicionandoComboBoxesaoExcel:

• CliqueemDesenvolvedornapartesuperiorouuseomenuDesenvolvedornasversõesanteriores:

If you do not have a Developer tab, or menu at the top, edit your preferences, usually in the advanced section, and check, or enable the Developer option, so you have the menu. You can check this article here, for more detail: http://office.microsoft.com/en-us/excel-help/show-the-developer-tab-or-run-in-developer-mode-HA010173052.aspx

• Clique em Inserir e, em seguida, escolha a Caixa de combinação :

Issoadicionaráacaixadecombinaçãorealàsuaplanilhadoexcel,normalmente,amenosquevocêdesenheotamanhodesejado,vocêreceberáumacaixagrande:

You can re-size the combo box, by right clicking on it, and dragging the edges as necessary.

• Clique com o botão direito do mouse na nova caixa de combinação e escolha Controle de formato :

• Configureasopçõesdamaneiraquevocêprecisa:
  • Intervalodeentrada:deveserumalistaverticaldetodasasopçõesnacaixadecombinação
  • CellLink:Aquiéondevocêdesejaqueoresultadodoíndicesejaretornado.

• Os resultados retornarão enquanto você trabalha com a caixa de combinação:

• Paravincularumíndiceaumvalor:bastaespecificarascontagensdeíndiceapósasopçõesdacaixadecombinaçãoefazerumapesquisasimples: