Excel - VLoolup с Match - это оптимальное решение?

337
David

Я пытаюсь избежать избыточности вычисления одного и того же индекса столько раз, сколько существует записей, поэтому я должен предварительно рассчитать индекс с помощью MATCH в скрытой ячейке над моей таблицей?

Стоит также отметить, что хотя мои столы не будут массивными, они также не будут маленькими. Вероятно, будет около 6 таблиц в каждой с одним или двумя VLOOKUPS (полный столбец) с общим количеством записей в сотнях.

Точнее, я собираюсь создать набор таблиц в Excel и буду эмулировать полезности правильной реляционной базы данных, используя функцию VLOOKUP для получения значения связанной таблицы, но я буду использовать MATCH функция, чтобы получить индекс столбца, который я хочу, по его заголовку. Ниже приводится фактическая формула, которую я буду использовать:

= ВПР ([@ ForeignKey], RelatedTable, MATCH (RelatedTable [[# Заголовки], [ItemName]], RelatedTable [#headers], 0), значение FALSE) 
Разбить по параметрам ВПР ( Lookup_value: = [@ForeignKey] Table_array: = RelatedTable Col_index_num: = 'Возвращение MATCH МАТЧ( Lookup_value: = RelatedTable [[# Headers], [ItemName]] Lookup_array: = RelatedTable [#Headers] Match_type: = 0 'Точное соответствие Range_lookup: = FALSE 'Точное совпадение

РЕДАКТИРОВАТЬ:

Первая таблица - это таблица с первичным ключом. Последние два являются одним из двух примеров, на которые я ссылаюсь.

Table_Products ╔════╦════════╦═══════════════╦═══════════╗ ║ ║ A ║ B ║ C ║ ╠════╬════════╬═══════════════╬═══════════╣ ║ 1 ║ ItemID ║ ItemName ║ ItemPrice ║ ╠════╬════════╬═══════════════╬═══════════╣ ║ 2 ║ 1 ║ Стилус ║ 25,00 ║ ╠════╬════════╬═══════════════╬═══════════╣ ║ 3 ║ 2 ║ Механическая клавиатура ║ $ 120.00 ║ ╠════╬════════╬═══════════════╬═══════════╣ ║ 4 ║ 3 ║ Монитор ║ $ 750,00 ║ ╚════╩════════╩═══════════════╩═══════════╝  Table_Transactions  ТАК КАК ЭТО ТАКОЕ ╔════╦════════╦════════╦═════╦════════════════════ ═══╗ ║ ║ A ║ B ║ C ║ D ║ ╠════╬════════╬════════╬═════╬════════════════════ ═══╣ ║ 1 ║ CustID ║ ItemID ║ Кол-во ║ ItemName ║ ╠════╬════════╬════════╬═════╬════════════════════ ═══╣ ║ 2 ║ 101 ║ 3 ║ 1 ║ VLOOKUP (,, Match (,,),) ║ ╠════╬════════╬════════╬═════╬════════════════════ ═══╣ ║ 3 ║ 102 ║ 3 ║ 2 ║ ... ║ ╠════╬════════╬════════╬═════╬════════════════════ ═══╣ ║ 4 ║ 103 ║ 3 ║ 1 ║ ... ║ ╠════╬════════╬════════╬═════╬════════════════════ ═══╣ ║ 5 ║ 104 ║ 2 ║ 3 ║ ... ║ ╠════╬════════╬════════╬═════╬════════════════════ ═══╣ ║ 6 ║ 105 ║ 1 ║ 8 ║ ... ║ ╠════╬════════╬════════╬═════╬════════════════════ ═══╣ ║ 7 ║ 106 ║ 2 ║ 2 ║ ... ║ ╠════╬════════╬════════╬═════╬════════════════════ ═══╣ ║ 8 ║ 107 ║ 2 ║ 1 ║ ... ║ ╠════╬════════╬════════╬═════╬════════════════════ ═══╣ ║ 9 ║ 108 ║ 2 ║ 2 ║ ... ║ ╠════╬════════╬════════╬═════╬════════════════════ ═══╣ ║ 10 ║ 109 ║ 1 ║ 4 ║ ... ║ ╠════╬════════╬════════╬═════╬════════════════════ ═══╣ ║ 11 ║ 110 ║ 2 ║ 16 ║ ... ║ ╚════╩════════╩════════╩═════╩════════════════════ ═══╝  ИЛИ КАК ЭТО (Ячейка D1 НЕ является частью таблицы)  ╔════╦════════╦════════╦═════╦═════════════════╗ ║ ║ A ║ B ║ C ║ D ║ ╠════╬════════╬════════╬═════╬═════════════════╣ ║ 1 ║ ║ ║ ║ Match (,,) ║ ╠════╬════════╬════════╬═════╬═════════════════╣ ║ 2 ║ CustID ║ ItemID ║ Кол-во ║ ItemName ║ ╠════╬════════╬════════╬═════╬═════════════════╣ ║ 3 ║ 101 ║ 3 ║ 1 ║ VLOOKUP (,, D $ 1,) ║ ╠════╬════════╬════════╬═════╬═════════════════╣ ║ 4 ║ 102 ║ 3 ║ 2 ║ ... ║ ╠════╬════════╬════════╬═════╬═════════════════╣ ║ 5 ║ 103 ║ 3 ║ 1 ║ ... ║ ╠════╬════════╬════════╬═════╬═════════════════╣ ║ 6 ║ 104 ║ 2 ║ 3 ║ ... ║ ╠════╬════════╬════════╬═════╬═════════════════╣ ║ 7 ║ 105 ║ 1 ║ 8 ║ ... ║ ╠════╬════════╬════════╬═════╬═════════════════╣ ║ 8 ║ 106 ║ 2 ║ 2 ║ ... ║ ╠════╬════════╬════════╬═════╬═════════════════╣ ║ 9 ║ 107 ║ 2 ║ 1 ║ ... ║ ╠════╬════════╬════════╬═════╬═════════════════╣ ║ 10 ║ 108 ║ 2 ║ 2 ║ ... ║ ╠════╬════════╬════════╬═════╬═════════════════╣ ║ 11 ║ 109 ║ 1 ║ 4 ║ ... ║ ╠════╬════════╬════════╬═════╬═════════════════╣ ║ 12 ║ 110 ║ 2 ║ 16 ║ ... ║ ╚════╩════════╩════════╩═════╩═════════════════╝
1
Не уверен, что вы пытаетесь достичь. Почему бы вам не сделать индекс / матч против vlookup / матч? user218076 5 лет назад 0
Потому что я не знаю, является ли плохой практикой иметь индекс, лежащий вне самой таблицы, по сравнению с тем, что таблица полностью автономна. David 5 лет назад 0
Не могли бы вы опубликовать пример данных вместе с выходом, поможет нам решить эту проблему. Кажется, вы пытаетесь Vlookup (,,, Match (,,,,)) написать? Rajesh S 5 лет назад 0
@Rajesh S Извините, мне потребовалось некоторое время, чтобы ответить. Теперь есть пример с двумя форматами, о которых я говорю. Кроме того, я спрашиваю с точки зрения пользователя Excel без профессионального или формального образования. Я в основном хочу знать, является ли последний пример формально неприемлемым. David 5 лет назад 0
Я обычно использую ячейку в скрытой строке, чтобы сохранить номер столбца, который будет использоваться с VLOOKUP. Я должен признать, я не знаю, если это необходимо. Возможно, что если Excel видит, что одна и та же функция MATCH выполняется много раз, он вызывает функцию один раз и сохраняет результат, который будет использоваться для всех остальных вызовов. Blackwood 5 лет назад 0
Да, можно получить имя элемента из таблицы продуктов, сопоставив идентификатор между таблицей продуктов и транзакций. Rajesh S 5 лет назад 0
Вы можете записать эту формулу в ячейку D2 таблицы транзакций ,, - = Iferror (Vlookup (B2, A2: C4,3, false), "") и заполнить ее. нет необходимости назначать имя таблицы и столбца, как показано выше. Если вы чувствуете, просто назовите диапазон, тоже подойдет. Если таблица Product находится на другом Листе, тогда добавьте префикс имени Листа к A2: C4. Rajesh S 5 лет назад 0

1 ответ на вопрос

1
user218076

Я бы написал в D2. =index(Table_Products'$B:$B,(match($B2,Table_Products'$A:$A,0)) Вы также можете выполнить двухстороннее сопоставление, если хотите изменить столбец для возврата вместо жесткого кодирования столбца имени элемента (столбец B). Я могу изменить ответ, если вам это нужно.

Я использую это ежедневно на листах с тысячами просмотров на многослойных таблицах и не замечаю проблем с производительностью. В общем случае Index / Match лучше, чем Vlookup, поскольку vlookup необходимо обрабатывать весь диапазон.

Похожие вопросы