No Excel, preciso encontrar os dados da planilha3 a partir dos dados da planilha1

Question

Eu tenho uma pasta de trabalho do Excel com várias planilhas. A coluna 'SheetA'!W:Wlista o texto que pode ser encontrado no cabeçalho de uma coluna na linha 'Sheet3'!1:1. Por exemplo, SheetA'!W42contém "B16". A célula Sheet3'!CB1possui o texto de cabeçalho "B16: Amostra 40", portanto seria a coluna de destino.

Exemplo: Amostra de'SheetA'!W42

Existe 'SheetA'!CD42uma fórmula que calcula o número da amostra que precisa ser localizado 'Sheet3'!A:A. No exemplo abaixo, CD42exibe o valor calculado “30”, que pode ser encontrado em 'Sheet3'!A32.

Exemplo: Dados de amostra da Planilha3 exibindo os cabeçalhos e interseção do cabeçalho da coluna (B16) e linha contendo o número da amostra de interesse:

O objetivo é recuperar o conteúdo da célula da Planilha3 onde a coluna e a linha pesquisadas se cruzam e exibi-lo em uma célula da PlanilhaA. No exemplo fornecido, na 'Sheet3'!CB:CBinterseção da linha 32 e na planilhaA, o valor "0,1950581843" seria exibido.

Alguém sabe como isso pode ser feito?

Answer 1

Dados os dados acima, farei algumas suposições. Se tudo isso for verdade, então um HLOOKUPpode resolver facilmente o seu problema. Caso contrário, poderá ser necessária uma cadeia de funções mais complexa.

Premissas:

1. Todos os textos de cabeçalho relevantes 'Sheet3'!1:1sãoabsolutamente únicodentro dessa linha.
2. Todos os textos de cabeçalho relevantes 'Sheet3'!1:1têm o formato "[X]: [Y]", onde:
   • [X] é um valor que pode ser encontrado em 'SheetA'!W:W.
   • [Y] é o valor 'SheetA'!X:Xque está na mesma linha que [X].
3. Todos os valores possíveis em 'SheetA'!CD:CDsão pesquisáveis ​​em 'Sheet3'!A:A, de modo que estarão na mesma linha que os dados que devem ser recuperados.
   • Só estou colocando isso porque percebi que parece haver alguma repetição desses valores na Planilha3 e queria ter certeza de que não havia condições sob as quais você pudesse estar procurando por um valor 'SheetA'!CD:CDque deveria ser comparado com outra coluna em vez disso (e, portanto, pode produzir resultados imprecisos se correspondidos em 'Sheet3'!A:A).
   • Se essa repetição for estritamente para facilitar a leitura, posso sugerir o uso do recurso "Congelar painéis"?
4. Todos os valores relevantes 'Sheet3!A:A'são perfeitamente sequenciais, começando com "1" em 'Sheet3'!A3, nunca se repetindo dentro dessa coluna, esempreordenado em ordem crescente.

Dadas as suposições acima, construí minhas próprias planilhas com dados de amostra que representam aproximadamente o seu cenário.

Aqui está uma captura de tela parcial da minha "Planilha3".

E uma foto parcial da minha "SheetA".

Lista de análogos:

• Folha3
  • Meu A:A = Seu A:A, BY:BY:, CA:CA:, ...
  • Meu B:B, C:C, D:D, ... = Seu B:B, BZ:BZ, CB:CB, ...
• FolhaA
  • Meu A:A = Seu W:W
  • Meu B:B = Seu X:X
  • Meu C:C = Seu CD:CD
  • Meu D:D = Qualquer coluna em que você deseja colocar os dados encontrados.

Como você pode ver na segunda imagem, a fórmula para D2 é:

=HLOOKUP(CONCATENATE(A2,": ",B2),Sheet3!A:Y,C2+2,FALSE)

Passo a passo da fórmula:

PROCHpermite que você procure um valor horizontalmente em um intervalo de células e, em seguida, retorne o valor de uma célula na mesma coluna com base em uma posição relativa da linha. São necessários quatro argumentos, três dos quais são obrigatórios:valor_procurado,matriz_tabela,número_índice_linha,[Pesquisa de alcance]. Esta é a fórmula que fará o trabalho braçal de encontrar os dados desejados na Planilha3 e colocá-los em uma célula na PlanilhaA.

• valor_procuradoé o valor que você deseja HLOOKUPencontrar primeiro. Este valordeveestar presente na primeira linha dematriz_tabela, porque essa é a única linha que será pesquisada. HLOOKUPtambém retornará apenas a primeira correspondência, portanto esses valores também devem ser únicos. Aqui, estamos usando CONCATENATEpara construir nossa string de pesquisa.
  
  • CONCATENARpermite reunir várias strings e valores em uma string. Aceita uma série de argumentos, ordenados de acordo com a sequência em que devem ser colocados na string resultante.
    
    • A2é o nosso primeiro argumento para CONCATENATE. A primeira parte da nossa string será o “Nome da Amostra” em A2.
    • ":"é o nosso segundo argumento para CONCATENATE. Isso coloca dois pontos e espaço na string, para corresponder ao formato dos valores em 'Sheet3'!1:1.
    • B2é nosso último argumento para CONCATENATE. Ele extrai o "ID de amostra" de B2, para completar a sintaxe usada para cabeçalhos em 'Sheet3'!1:1.
• matriz_tabelaé uma referência a um intervalo de células HLOOKUPcom o qual você deseja trabalhar. Lembre-se que a primeira linha deve ser aquela que conterávalor_procurado. Este intervalo também deve abranger todos os valores possíveis paranúmero_índice_linha.
  • Folha3!A:Yé uma referência a todas as células nas colunas A a Y (as únicas preenchidas na minha Planilha3), da Planilha3. Isso garante que quaisquer dados adicionados posteriormente em novas linhas também estarão no escopo da pesquisa. Se os dados fossem adicionados em novas colunas em vez de linhas, eu gostaria de usar a referência Sheet3!1:32(atualmente, 32 é a última linha preenchida em minha Planilha3). Se os dados puderem ser adicionados por novas colunasenovas linhas, eu referenciaria a planilha inteira com Sheet3!1:1048576ou Sheet3!A:XFD. (Observação: a referência "planilha inteira" é válida para Excel 2013. Versões anteriores podem ter limitações menores de linhas/colunas - ajuste 1048576ou XFDconforme apropriado.)
• número_índice_linhaé um número inteiro positivo que representa uma posição de linha em relação à linha superior emmatriz_tabela. Isso informa HLOOKUPqual célula você deseja retornar da coluna correspondente. Observe que, como HLOOKUPapenas procura porvalor_procuradona linha superior dematriz_tabela, enúmero_índice_linhanão pode ser negativo, você não pode usar HLOOKUP(pelo menos não por si só) para retornar informações de células que estão acimavalor_procurado.
  • C2+2- Como todos os valores em 'Sheet3'!A:Asão perfeitamente sequenciais, sem números inteiros ignorados, e sempre serão ordenados de forma crescente, podemos usar esses valores (também representados em 'SheetA'!C:C) como indicadores dos números das linhas dos dados que queremos encontrar. Isso +2explica o fato de que a numeração começa com “1” na linha 3 da Planilha3.
• [Pesquisa de alcance]é um argumento opcional para HLOOKUP. As opções são TRUE ou FALSE, para indicar se você deseja permitir que correspondências aproximadas sejam válidas ou se apenas correspondências exatas devem ser permitidas. O padrão do Excel é TRUE (correspondência aproximada) se esse argumento for omitido, o que muitas vezes pode resultar em comportamento indesejado - especialmente se sua planilha não estiver classificada de determinadas maneiras. Então, especificamosFALSOaqui para ter certeza de HLOOKUPque obterá apenas uma correspondência exata.

Adaptando o texto acima ao layout da sua planilha, acredito que esta seja a fórmula que você precisará para a célula 'SheetA'!CE42(assumindo que é onde você deseja que os dados sejam inseridos):

=HLOOKUP(CONCATENATE(W42,": ",X42),Sheet3!A:CB,CD42+2,FALSE)

Observe que, se seus dados na Planilha3 forem mais à direita do que a coluna CB e/ou os dados puderem ser adicionados a outras colunas, você desejará ajustarmatriz_tabelade acordo.