Encontre a entidade em uma lista de entidades e devolva outro valor de célula com base na categoria à qual pertence no excel

Question

Eu quero encontrar uma boa maneira de obter um valor específico com base em uma categoria e outra característica. Vou tentar exemplificá-lo abaixo;

Problemas:

1) Eu quero verificar a qual categoria uma determinada entidade pertence.

2) Com base na resposta em 1) E outro fator, vamos chamá-lo pontos de experiência, eu quero ser capaz de obter o valor da interceptação entre os dois.

Dados:

Entidade .......................... XP ............. A ......................... B ...................... XP. ........... A ............. B Gandalf ......................... 80 .......... Gimli ............ ........ Frodo ................. 0-50 .......... 2.5 ......... 3.1
Frodo ............................ 21 ........ Gandalf ..... Rei dos Bruxos de Angmar. .... 51-100 ...... 3.3 ......... 4.2
Gimli ............................. 39 ................... ............................................ 101-150 ... ..4.6 .......... 7,4
Rei dos Bruxos de Angmar..140

Ou seja. o que eu realmente quero descobrir é que Gandalf teria um valor de 3,3 e o Qual-rei de Angmar teria 7,4 sem ler as malditas tabelas. Eu apreciaria muito uma maneira inteligente e enxuta de fazer isso. Eu tentei correspondência, indexação, Vlookuping e construção de macros simples sem encontrar essa solução que fará seu estômago agradar de uma maneira especial. Por favor me ajude!

    

Answer 1

Você já deu a resposta. vlookup e index são as funções certas a serem usadas.

Minha sugestão não seria tentar colocar tudo em uma fórmula / célula, mas dividir as fórmulas. Bem ao lado do campo no qual o nome da Entidade seria inserido, faça um VLOOKUP para o XP. Além disso, use MATCH para encontrar a categoria. Por fim, use INDEX para encontrar o valor com base nas outras duas colunas. Você pode usar ROUND para arredondar o XP para o valor correto ( ROUND(XP/50) em seu exemplo).

Espero que isso faça seu estômago cócegas; -)

    

Answer 2

Se eu entendi isso corretamente, você quer

Index coluna 1, Match coluna 2 a coluna 5, e Return qualquer o valor da coluna 6 ou 7, dependendo se a entidade está na coluna 3 ou 4

Parece que você quer usar Index (Match) para fazer a maior parte do trabalho, como sugere o agtoever. Além disso, ele está certo sobre o dinheiro com você terá problemas para combinar a entidade específica xp com a banda xp oin que corresponde à segunda tabela (à direita).

A primeira coisa a fazer é arredondar o XP, conforme indicado, e também alterar os valores na coluna mais correta do XP para valores únicos correspondentes.

A próxima coisa que você precisa fazer é deixar o excel saber se a entidade está na coluna a ou na coluna b. Parece que todo o seu layout é uma tabela atm. Vou sugerir que você divida em três.

Tabela 1

Entidade | Entidade Específica XP | Entitiy XP Band | A ou B | Retorno Final

Tabela 2

A | B

Tabela 3

Banda XP | Coluna A | Coluna B

Isso significa que, se você mapear a ou b para cada entidade na tabela 1, poderá verificar a tabela 2 para retornar o valor correto de A ou B

Etapa 1: XP

1.1: altere a banda do XP para um valor único (provavelmente o máximo), de modo que pareça

50 100 150

1.2: calcule em qual banda o direito está em ROUND como acima.

Etapa 2: A ou B

Escreva uma fórmula na quarta coluna que indexa o nome da entidade e, em seguida, verifica se ela está na coluna a da tabela 2. Se ela retornar "A", se não, retorne "B"

algo como

=IF([entitity in this row] = [range A], "A", "B")

Etapa 3: Valor de retorno

Escreva uma fórmula na quinta coluna que verifica a quarta coluna e, em seguida, verifique a terceira tabela para o número final, isso pode estar usando as instruções IF aninhadas, AND ou

algo como

=IF([entitiy XP band] = [XP band], IF([entity A or B] = "A",[return A in table 3]),[Return B in table 3])

Espero que esta explicação do código do psuedo funcione. Se você precisar de mais esclarecimentos, escreverei um exemplo específico quando chegar em casa esta noite.

    

Answer 3

Por algum motivo, esta questão não foi adicionada como minha, por isso infelizmente perdi estas respostas. De qualquer forma, muito obrigado, você entendeu o problema corretamente e encontrou uma boa solução.

Os dados reais são muito grandes, as Bandas XP são divididas em muito mais categorias, a tabela 2 consiste em AG em vez de AB e existem 3 "tabelas 3" diferentes dependendo de outra categoria (similarmente construída como tabela 2 usando XYZ). Devido a tudo isso, escolhi uma solução semelhante, mas ligeiramente diferente, que achei que poderia compartilhar para qualquer finalidade acadêmica.

Passo um: Obter a banda XP para combinar foi resolvido por:

=IF(AND(0<=[XP in this row], [XP in this row]<51), "0-50", IF(AND(50<[XP in this row], [XP in this row]<101), "51-100", "101-150"))

Etapa 2_ Obtendo categoria 1, A-G:

=IF(ISERROR(MATCH([entity in this row],[range A],0)),IF(ISERROR(MATCH([entity in this row],[range B],0)), "C", "B"), "A")

Passo 3: Encontrando qual tabela usar (categoria 2) X-Z:

Resolvido da mesma forma que o passo 2.

Passo 4: Encontrando a solução para cada entidade e para todos os tipos X-Z

=INDEX([table X], IF([XP]=[XP band1], 2, IF([XP]=[XP band2], 3, 4)), IF([category1 for entity]="A",2,IF(([category1 for entity]="B", 3, 4)))

Passo 5: O passo final onde escolho qual valor de X, Y e Z usar:

=IF([category 2]="X", [table X], IF([category 2]="Y", [table Y], [table Z]))

Talvez não seja a solução mais bonita, mas certamente funcionou e vai me poupar muito trabalho.

Mais uma vez, obrigado pelas soluções propostas, é provável que você tenha resolvido isso de forma mais enxuta.