Поиски, нацеленные на объединенные ячейки - возвращают только значение для первой строки

Question

У меня есть основной рабочий лист, который содержит данные, которые я хочу связать с другим «сводным» листом, используя поиск.

Однако некоторые ячейки, данные которых я хочу включить в сводный лист, объединены в две или более смежные строки. Для ясности, «основной» столбец A, который я использую в своей формуле для определения целевой строки, не содержит объединенных ячеек, но столбец, из которого я хочу вернуть значение, содержит.

Я пробовал VLOOKUP и INDEX+MATCH . Проблема в том, что данные возвращаются только для ключа первой строки, а остальные возвращают ноль (как если бы ячейка в целевом столбце была пустой, где фактически она объединена).

Я пробовал обходные пути, например, используя операторы IF чтобы найти верхний ряд объединенной ячейки. Однако они не работают должным образом, если порядок значений в сводной таблице отличается от порядка в основной таблице, а также является грязным.

Можно ли это сделать?

Answer 1

Я не думаю, что есть какой-либо простой способ сделать это с помощью существующих формул Excel. Проблема двоякая. Во-первых, в AFAIK нет функции Excel, которая сообщала бы, является ли ячейка частью объединенного диапазона. Во-вторых, значение, отображаемое в объединенной области, на самом деле находится только в первой ячейке объединенного диапазона.

Если вы хотите использовать VBA для создания пользовательской функции, это можно сделать довольно легко с помощью комбинации функции Match и того факта, что объекты Range знают, являются ли они частью объединенного диапазона.

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

Вот еще один способ (с использованием VBA) узнать, является ли ячейка частью объединенной области. Я иногда использую эту функцию (и некоторые другие производные от этой):

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

Надеюсь, это поможет.

Answer 3

Объединенные ячейки просто форматируют. Проблема правильно указывает на ячейку, которая имеет значение. Одним из подходов будет вспомогательный столбец, в котором Excel сможет найти то, что ему нужно (либо фактическое значение, либо указатель на него). Это гораздо более практично, если каждая строка связана с фактическим значением или "объединенным значением". Если некоторые строки не могут иметь значения, создание дорожной карты в столбце помощника усложняется.

Но для случая, когда каждая строка связана с фактическим или объединенным значением, простое решение будет примерно таким. Скажем, целью VLOOKUP является столбец A, а столбец B содержит объединенные значения. Создайте вспомогательный столбец справа; Я буду использовать столбец C для примера. Предположим, строка 1 является первой строкой данных, поэтому B1 всегда будет иметь значение. В столбце помощника C1 будет просто:

    =B1

Ячейка C2 будет:

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

Скопируйте это вниз по столбцу. В этом случае вспомогательный столбец будет содержать значение для каждой строки, и VLOOKUP может извлечь из этого столбца.

Мне не известна обычная (по крайней мере нативная) функция, которая может возвращать, является ли ячейка частью объединенного диапазона. Таким образом, если могут существовать ячейки без действительного или объединенного значения, вероятно, потребуется одно из решений VBA.