Pesquisas direcionadas a células mescladas - somente valor de retorno para a primeira linha

Question

Eu tenho uma planilha mestra que contém dados que desejo vincular a outra planilha de 'resumo' usando uma pesquisa.

No entanto, algumas das células cujos dados eu desejo incluir na planilha de resumo são mescladas em duas ou mais linhas adjacentes. Para ser claro, a coluna 'primária' A que estou usando na minha fórmula para identificar a linha de destino não contém células mescladas, mas a coluna da qual desejo retornar um valor.

Eu tentei VLOOKUP e INDEX+MATCH . O problema é que os dados só são retornados para a chave da primeira linha, e os outros retornam zero (como se a célula na coluna de destino estivesse em branco, onde, na verdade, ela é mesclada).

Eu tentei formas deselegantes em torno disso, por exemplo usando instruções IF para tentar encontrar a linha superior da célula mesclada. No entanto, eles não funcionam bem se a ordem dos valores na planilha de resumo for diferente daquela na planilha mestre, além de ser confusa.

Isso pode ser feito?

    

Answer 1

Eu não acho que haja uma maneira fácil de fazer isso com as fórmulas existentes do Excel. O problema é duplo. Em primeiro lugar, AFAIK não há nenhuma função do Excel que irá dizer se uma célula faz parte de um intervalo mesclado. Em segundo lugar, o valor mostrado em uma combinação mesclada é, na verdade, apenas na primeira célula do intervalo mesclado.

Se você estiver disposto a usar o VBA para criar uma função personalizada, isso poderá ser feito facilmente com uma combinação da função Match e o fato de que os objetos Range saberão se eles fazem parte de um intervalo mesclado.

Public Function VLookupMerge(lookup_value As Variant, table_array As Range, col_index As Long) As Variant
   Dim sMatchFormula As String
   Dim row_index As Variant
   Dim r As Range

   sMatchFormula = "=MATCH(" & lookup_value _
                        & "," & table_array.Columns(1).Address(External:=True) _
                        & ",0)"
   row_index = Application.Evaluate(sMatchFormula)

   If TypeName(row_index) = "Error" Then
      VLookupMerge = row_index
   Else
      Set r = table_array.Cells(row_index, col_index)
      VLookupMerge = r.MergeArea.Range("A1")
      Set r = Nothing
   End If

End Function

    

Answer 2

Aqui está outra maneira (usando o VBA) para saber se uma célula faz parte de uma região mesclada. Eu às vezes uso essa função (e algumas outras derivadas dessa forma):

Public Function IsMerged(AnyRange As Range) As Boolean
   Dim MergedRegion As Range

   IsMerged = AnyRange.MergeCells
   If IsMerged Then Set MergedRegion = AnyRange.MergeArea Else Set MergedRegion = AnyRange
   Debug.Print MergedRegion.Rows.Count; " x "; MergedRegion.Columns.Count
   Debug.Print MergedRegion.Cells.Address

End Function

Espero que isso ajude.

    

Answer 3

As células mescladas estão apenas formatando. O problema está apontando corretamente para a célula que possui o valor. Uma abordagem seria uma coluna auxiliar onde o Excel pode encontrar o que precisa (ou o valor real ou um ponteiro para ele). Isso é muito mais prático se cada linha estiver associada a um valor real ou a um "valor mesclado". Se algumas linhas não puderem ter valor, será mais complicado criar um roteiro na coluna auxiliar.

Mas, para um caso em que cada linha é associada a um valor real ou mesclado, uma solução simples seria algo assim. Digamos que o alvo VLOOKUP seja a coluna A e a coluna B contenha valores mesclados. Crie uma coluna auxiliar à direita; Vou usar a coluna C para o exemplo. Vamos supor que a linha 1 é a primeira linha de dados, então B1 sempre terá um valor. Na coluna auxiliar, C1 seria apenas:

    =B1

A célula C2 seria:

    =if(isblank(B2),C1,B2)

Copie isso na coluna. A coluna auxiliar conteria, então, um valor para cada linha e o VLOOKUP poderia extrair dessa coluna.

Não estou ciente de uma função regular (pelo menos nativa) que pode retornar se uma célula faz parte de um intervalo mesclado. Portanto, se houver células sem um valor real ou mesclado, uma das soluções do VBA provavelmente seria necessária.