Excel - VLoolup с Match - это оптимальное решение?
Я пытаюсь избежать избыточности при вычислении одного и того же индекса столько раз, сколько существует записей, поэтому я должен предварительно рассчитать индекс с помощью MATCH в скрытой ячейке над моей таблицей?
Стоит также отметить, что хотя мои таблицы не будут большими, они тоже не будут маленькими. Вероятно, будет около 6 таблиц с одной или двумя VLOOKUPS (полный столбец) с общим количеством записей в сотнях.
Точнее, я собираюсь создать набор таблиц в Excel и буду эмулировать вкус правильной реляционной базы данных, используя функцию VLOOKUP для получения значения связанной таблицы, но я буду использовать MATCH функция, чтобы получить индекс столбца, который я хочу, по его заголовку. Ниже приводится фактическая формула, которую я буду использовать:
= ВПР ([@ForeignKey],RelatedTable,MATCH(RelatedTable[[# Заголовки],[ItemName]],RelatedTable[#headers],0), значение FALSE)
'Разбит по параметрам VLOOKUP(
Lookup_value:= [@ForeignKey]
Table_array:= RelatedTable
Col_index_num:= ' Возврат MATCH
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 ответ
Я бы написал в D2, =index(Table_Products'$B:$B,(match($B2,Table_Products'$A:$A,0))Вы также можете выполнить двухстороннее сопоставление, если хотите изменить столбец для возврата вместо жесткого кодирования столбца имени элемента (столбец B). Я могу изменить ответ, если вам это нужно.
Я использую это ежедневно на листах с тысячами поисков на многослойных таблицах и не замечаю проблем с производительностью. В общем случае Index/Match лучше, чем Vlookup, поскольку vlookup необходимо обрабатывать весь диапазон.