Fórmula do Microsoft Excel usando uma tabela para classificar dados por linha

Question

Eu tenho duas planilhas em um documento do Excel que estou tentando comparar. Eu tenho uma lista de códigos de área na Folha 1, coluna B. Na Folha 2, tenho uma lista de todos os códigos de área nas colunas A a D com os cabeçalhos "Leste, Central, Montanha, Pacífico" na primeira linha de cada coluna seguida por todos os códigos de área em cada zona.

O que eu quero fazer é classificar cada código de área na Folha 1 na coluna C pelo fuso horário correspondente na Folha 2.

Como faço isso?

Aqui estão as capturas de tela das tabelas básicas que tenho.

Folha 1:

Folha2:

    

Answer 1

Veja como fazer isso com seus dados definidos exatamente como você os tem agora. Esta fórmula vai em C2 em sua planilha1:

=IF(ISNA(MATCH(B2,Sheet2!$A$1:$A$40,0)),IF(ISNA(MATCH(B2,Sheet2!$B$1:$B$40,0)),IF(ISNA(MATCH(B2,Sheet2!$C$1:$C$40,0)),IF(ISNA(MATCH(B2,Sheet2!$D$1:$D$40,0)),"Not Found","Pacific"),"Mountain"),"Central"),"Eastern")

É feio mas funciona. Talvez seja necessário ajustar as quatro referências absolutas para a Planilha2 (por exemplo, Sheet2!$A$1:$A$40 ) se os dados nessa planilha passarem da linha 40.

A fórmula final consiste em quatro usos da função MATCH, cada um pesquisando apenas uma coluna de códigos de área.

Veja como eu construí a fórmula.

Eu usei a função MATCH. MATCH só pode pesquisar uma única coluna por um valor. Primeiro eu criei quatro fórmulas MATCH separadas. Cada um pesquisa apenas uma das colunas de fuso horário. Por exemplo, para pesquisar na coluna "Oriental" o valor (código de área) encontrado na célula B2 :

=MATCH(B2,Sheet2!$A$1:$A$40,0)

Supondo que B2 contenha 201 , isso retorna 2 porque 201 é o segundo item da coluna. Mas se B2 contiver 205 , isso retornará #N/A . Nós pegamos isso envolvendo a fórmula com a fórmula ISNA:

=IF(ISNA(MATCH(B2,Sheet2!$A$1:$A$40,0)),"no_next_formula","Eastern")

ISNA testa se a fórmula MATCH retorna #N/A . Se sim / verdadeiro, retorna "no_next_formula". Se não / falso, retorna "Oriental" porque MATCH encontrou nosso código de área na coluna de código de área leste.

Podemos criar quatro dessas fórmulas, alterando o intervalo referenciado na Planilha2 apropriadamente para pesquisar os outros três códigos de área. Uma planilha de exemplo pode ter esta aparência:

Agora,paracombiná-losnafórmulaúnicamostradaacima,copieitodaafórmulanaminhacolunaF(menososinal=)esubstituíotexto"no_next_formula" na fórmula da coluna E.

Fórmula em F2:

=IF(ISNA(MATCH(B2,Sheet2!$D$1:$D$40,0)),"Not Found","Pacific")

Fórmula em E2 (antes):

=IF(ISNA(MATCH(B2,Sheet2!$C$1:$C$40,0)),"no_next_formula","Mountain")

Fórmula em E2 (depois de inserir a fórmula de F2):

=IF(ISNA(MATCH(B2,Sheet2!$C$1:$C$40,0)),IF(ISNA(MATCH(B2,Sheet2!$D$1:$D$40,0)),"Not Found","Pacific"),"Mountain")

Depois, repeti essas etapas, copiando a fórmula agora em E2 para substituir a fórmula do texto "no_next_formula" in D2 . Quando isso for feito para a fórmula em C2 , temos a fórmula completa mostrada acima.

    

Answer 2

Se você tem certeza de que seus dados estão bem formados (especificamente, que nenhum código de área aparece mais de uma vez na Planilha2), então você pode usar

=SUM(COLUMN(Sheet2!A$2:D$99)*(Sheet2!A$2:D$99=B2))

para encontrar o número da coluna (na Folha2) onde o código de área (em B2 ) aparece. (Substitua 99 pelo número da última linha na Folha2 onde você tem códigos de área.) Esta é uma fórmula de matriz ; você deve digitar Ctrl + Deslocar + Enter depois de digitar (ou colar). Isso cria um array virtual, onde cada célula contém o número da coluna multiplicado pelo valor de verdade se o valor da célula é igual a B2 , assim:

1*(Sheet2!A2=Sheet1!B2)   2*(Sheet2!B2=Sheet1!B2)   3*(Sheet2!C2=Sheet1!B2)   4*(Sheet2!D2=Sheet1!B2)
1*(Sheet2!A3=Sheet1!B2)   2*(Sheet2!B3=Sheet1!B2)   3*(Sheet2!C3=Sheet1!B2)   4*(Sheet2!D3=Sheet1!B2)
1*(Sheet2!A4=Sheet1!B2)   2*(Sheet2!B4=Sheet1!B2)   3*(Sheet2!C4=Sheet1!B2)   4*(Sheet2!D4=Sheet1!B2)
            ︙                        ︙                        ︙                        ︙

Então, por exemplo, se Sheet1!B2 for 303, isso se torna

1*(201=303)               2*(205=303)               3*(208=303)               4*(206=303)
1*(202=303)               2*(210=303)               3*(303=303)               4*(209=303)
1*(203=303)               2*(214=303)               3*(307=303)               4*(213=303)
      ︙                        ︙                        ︙                        ︙

ou seja,

1*FALSE                   2*FALSE                   3*FALSE                   4*FALSE
1*FALSE                   2*FALSE                   3*TRUE                    4*FALSE
1*FALSE                   2*FALSE                   3*FALSE                   4*FALSE
   ︙                        ︙                        ︙                        ︙

Como TRUE é 1 e FALSE é 0, isso reduz para

   0                         0                         0                         0
   0                         0                     →   3   ←                     0
   0                         0                         0                         0
︙   ︙                        ︙                        ︙                        ︙

e, claro, o SUM disso é 3, qual é o número da coluna onde 303 é encontrado.

Se você tem certeza de que todos os códigos de área na Planilha1, coluna B está presente na Folha2, então você pode obter o nome do fuso horário simplesmente conectando o número da coluna em uma função INDEX :

=INDEX(Sheet2!A$1:D$1, 1, SUM(COLUMN(Sheet2!A$2:D$99)*(Sheet2!A$2:D$99=B2)))

(lembre-se de usar Ctrl + Deslocar + Entrar ) mas se você tiver um código de área na Planilha1 que não esteja presente na Planilha 2, então Sheet2!whatever=B2 é FALSE para cada célula, então o array virtual é todo zeros, o SUM é 0, e o INDEX pode retornar algo enganoso. Para lidar com isso, defina Z2 para

=SUM(COLUMN(Sheet2!A$2:D$99)*(Sheet2!A$2:D$99=B2))

(lembre-se de usar Ctrl + Deslocar + Entrar ) e defina C2 para

=IF(Z2=0, "Not Found", INDEX(Sheet2!A$1:D$1, 1, Z2))