No Excel, preciso encontrar correspondências de dados entre dois arrays - um horizontal e um vertical

Question

No Excel 2010 (ou 2007 - eu tenho os dois, embora meu sistema operacional seja apenas o Win7 de 32 bits como uma limitação para alguns aplicativos legados que executamos), preciso descobrir como posso encontrar e retornar o valor correspondente de duas matrizes de dados .

Eu tenho duas planilhas. Um deles é um arquivo plano gigante de uma dimensão hierárquica OLAP Cube (37.000 linhas do SAP BPC). O outro é uma tabela de valores com os quais preciso corresponder. Preciso retornar o valor correspondente da segunda planilha para a ColumnA na primeira planilha - The flatfile.

O desafio é que, por ser uma estrutura hierárquica, não posso selecionar uma única coluna da Folha1 para corresponder - a correspondência poderia estar em qualquer uma das colunas de cada linha. Então, basicamente, eu estou olhando para a necessidade de tomar o que é que coincide entre a única linha da Folha 1 como uma matriz e a coluna da Folha 2 como uma matriz (eu acho).

Em inglês, quero que o Excel: Para cada linha da Planilha1 onde há dados, observe tudo em toda a linha (digamos, intervalo B2: R2 - deixei a Col A em branco para o valor da fórmula / correspondência). Se houver alguma coisa que corresponda a alguma coisa na lista Categoria de Relatório (que é a coluna da Folha 2, Intervalo A1: A42), retorne o valor de Folha2 para Folha1! A2 (a coluna em branco que fiz para a correspondência).

Aqui está uma amostra de dados com uma alegoria alimentar. Note que eu fiz uma ColumnA em branco, e que os dados em cada linha progridem em uma hierarquia de classificações onde ColB é o nível base e é repetido se necessário para que o pai terminal seja em ColF.:

Agora,nestapróximaimagemestáoformatodorelatórioquedesejousar.Veja,àsvezesqueremosdadosdealgunsdosníveishierárquicoseàsvezesdeoutros.

No final, minha planilha será preenchida com as categorias de relatório personalizadas que eu quero (então eu posso girar sobre essas categorias para dados agregados).

Eu venho realizando isso via fórmula vlookup de monstros, mas queria saber se há outra maneira, mais fácil ou pelo menos menos intensiva em recursos, já que 37.000 linhas com uma instrução vlookup aninhada em 8 profundo faz o Excel ficar muito travado. Então, usando minhas categorias reais de relatórios (sheet2 é chamado All_Budget_Units), aqui está o que eu uso atualmente:

= IFERROR (VLOOKUP (IFERROR (VLOOKUP) (IFERROR (VLOOKUP) (IFERROR (VLOOKUP) (IFERROR (VLOOKUP) (IFERROR (VLOOKUP (C2, All_Budget_Units! $ A $ 1: $ A $ 39,1, FALSE), D2), All_Budget_Units! $ A $ 1: $ A $ 39,1, FALSE), E2), All_Budget_Units! $ A $ 1: $ A $ 39,1, FALSE), F2), All_Budget_Units! $ A $ 1: $ A $ 39,1, FALSE), G2 ), All_Budget_Units! $ A $ 1: $ A $ 39,1, FALSE), H2), All_Budget_Units! $ A $ 1: $ A $ 39,1, FALSE), I2)

    

Answer 1

YMMV, mas Visualizar - > Macros, adicione uma macro. Tente isso (altere as referências da célula conforme necessário):

Dim data, reference As Range

Set reference = Worksheets("Sheet2").Range("A1", "A42")
Set data = Worksheets("Sheet1").Range("B2", "F6")

For Each dataCell In data
    For Each referenceCell In reference
        If dataCell.Value = referenceCell.Value Then
                Worksheets("Sheet1").Cells(dataCell.Row, 1).Value = dataCell.Value
        End If
    Next
Next

[Editar: se isso funcionar, você poderá acelerar um pouco parando de pesquisar uma linha quando encontrar uma correspondência. (Assumindo apenas uma correspondência possível por linha). por exemplo:

Sub newtest()
    Dim data, reference As Range
    Dim skipsome As Boolean
    skipsome = False

    Set reference = Worksheets("Sheet2").Range("A1", "A7")

    Set data = Worksheets("Sheet1").Range("B2", "F6")
    For Each dataCell In data
        For Each referenceCell In reference
            If dataCell.Value = referenceCell.Value Then
                    Worksheets("Sheet1").Cells(dataCell.Row, 1).Value = dataCell.Value
                    skipsome = True
                    Exit For
            End If

            If skipsome = True Then
                skipsome = False
                Exit For
            End If
        Next
    Next
End Sub

Apenas nos seus dados de teste de 5 linhas, isso reduz os testes de comparação de células de 175 para 132.] [Edit2: faça o código funcionar]

    

Answer 2

Você não precisa de uma solução de macro para isso; você pode usar uma fórmula de matriz.

{=INDEX(All_Budget_Units!$A$1:$A$39, MAX(IFERROR(MATCH(C2:I2, All_Budget_Units!$A$1:$A$39, 0), 0)))}

Isto assume que o valor em I2 também está na lista principal, o que a sua fórmula original não assume. Se não estiver ou não estiver presente, use isso:

{=IFERROR(INDEX(All_Budget_Units!$A$1:$A$39, MAX(IFERROR(MATCH(C2:H2, All_Budget_Units!$A$1:$A$39, 0), -1))), I2)}

Se você não usou fórmulas de matriz antes, não digite as chaves {}: coloque o restante da fórmula e pressione CTRL + SHIFT + ENTER para inseri-la como uma fórmula de matriz. Se você tiver feito isso corretamente, as chaves serão exibidas na caixa de fórmulas.

A maneira como isso funciona é que usamos a função MATCH para criar uma matriz de resultados de correspondência. Todos, exceto um, serão #N/A , então, nós o empacotamos com IFERROR para convertê-los em 0 (ou -1 na segunda versão). Qualquer correspondência real será um número positivo, portanto, levar o MAX da matriz encontra a única correspondência. Em seguida, usamos a função INDEX para converter isso no valor. Na segunda versão, se não houver nenhuma correspondência, tentaremos usar INDEX com uma posição de -1, o que gerará um erro, então usamos IFERROR para retornar o valor padrão.