В Excel мне нужно найти совпадения данных между двумя массивами - один горизонтальный и один вертикальный

Question

В Excel 2010 (или 2007 - у меня есть оба, хотя моя ОС является только 32-битной Win7 как ограничение для некоторых устаревших приложений, которые мы запускаем), мне нужно найти, как я могу найти и вернуть совпадающее значение из двух массивов данных.

У меня есть две таблицы. Одним из них является гигантский плоский файл из иерархического измерения куба OLAP (37 000 строк из SAP BPC). Другой - это таблица значений, с которой мне нужно сопоставить. Мне нужно вернуть совпадающее значение из 2-й таблицы в ColumnA на первом листе - плоский файл.

Проблема в том, что, поскольку это иерархическая структура, я не могу выбрать один столбец из Sheet1 для сопоставления - совпадение может быть в любом из столбцов каждой строки. Итак, в основном, я смотрю на необходимость взять все, что совпадает между одной строкой Листа 1 в качестве массива и столбцом Листа 2 в качестве массива (я думаю).

В английском я хочу, чтобы Excel: Для каждой строки Sheet1, где есть данные, просматривал все по всей строке (скажем, диапазон B2: R2 - я оставил поле A пустым для значения формулы / соответствия). Если что-то там соответствует чему-либо в списке «Категория отчетности» (это столбец листа 2, диапазон A1: A42), то верните значение Sheet2 в Sheet1!A2 (пустой столбец, который я сделал для матча).

Вот пример данных с пищевой аллегорией. Обратите внимание, что я создал пустой столбец ColumnA, и что данные в каждой строке представляют собой иерархию классификаций, где ColB - это базовый уровень, и при необходимости он повторяется, чтобы родительский родительский терминал был в ColF .:

Теперь на следующем изображении представлен формат отчета, который я хочу использовать. Видите, иногда нам нужны данные с некоторых иерархических уровней, а иногда и с других.

В конце концов моя электронная таблица заполнится требуемыми категориями отчетов, которые я хочу (тогда я могу развернуть эти категории для сводных данных).

Я выполнял это с помощью формулы monster vlookup, но мне было интересно, есть ли другой, более простой или, по крайней мере, менее ресурсоемкий способ, так как 37 000 строк с вложенным 8 оператором vlookup заставляют Excel сильно падать. Итак, используя мои настоящие категории отчетности (sheet2 называется All_Budget_Units), вот что я сейчас использую:

= ЕСЛИОШИБКА (ВПР (ЕСЛИОШИБКА (ВПР (ЕСЛИОШИБКА (ВПР (ЕСЛИОШИБКА (ВПР (ЕСЛИОШИБКА (ВПР (ЕСЛИОШИБКА (ВПР (С2, 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

Вам не нужно макро-решение для этого; Вы можете использовать формулу массива.

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

Это предполагает, что значение в I2 также находится в главном списке, который не предполагает ваша исходная формула. Если это не так, или может не присутствовать, используйте это вместо:

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

Если вы ранее не использовали формулы массива, вы сами не вводите фигурные скобки {}: вставьте оставшуюся часть формулы и нажмите CTRL+SHIFT+ENTER, чтобы ввести ее как формулу массива. Если вы сделали это правильно, фигурные скобки появятся в окне формулы.

Это работает так, что мы используем функцию MATCH для создания массива результатов совпадений. Все, кроме одного, будут #N/A поэтому мы оборачиваем их с помощью IFERROR чтобы преобразовать их в 0 (или в -1 во второй версии). Любое фактическое совпадение будет положительным числом, поэтому взятие MAX массива находит единственное совпадение. Затем мы используем функцию INDEX для преобразования этого значения. Во второй версии, если совпадений нет вообще, мы попытаемся использовать INDEX с позицией -1, что приведет к ошибке, поэтому мы используем IFERROR для возврата значения по умолчанию.

Answer 2

YMMV, но View -> Macros, добавить макрос. Попробуйте это (измените ссылки на ячейки при необходимости):

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

[Редактировать: если это работает, вы можете немного ускорить его, прекратив поиск строки, когда нашли совпадение. (Предполагая только одно возможное совпадение на строку). например:

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

Только на ваших 5-рядных тестовых данных это сбивает тесты сравнения ячеек со 175 до 132.] [Edit2: заставить код работать]