Можно ли использовать вход массива в имени листа диапазона vlookup?

630
Stephen

У меня есть формула, которая работает, но абсурдно долго, но повторяет основные данные снова и снова.

Моя электронная таблица содержит 70 страниц с описанием наборов карточек. Страница, над которой я работаю, дает мне конкретную сводку, используя идентификатор карты (в A3) в качестве индекса. Точнее, я хочу минимальную цену за карту по этому идентификатору, которая может присутствовать в любом из наборов. Все наборы имеют менее 500 карт (поэтому диапазон поиска B3: I500)

Имена не следуют шаблону, несмотря на упрощение, которое я здесь сделал, но перечислены в строке G2: Z2 (на данный момент явно неполной)

=MIN( IFERROR(VLOOKUP($A3,Set1!$B$3:$I$500,6,FALSE),99999), IFERROR(VLOOKUP($A3,Set2!$B$3:$I$500,6,FALSE),99999), IFERROR(VLOOKUP($A3,Set3!$B$3:$I$500,6,FALSE),99999), ...

[шаблон повторяется для других наборов ...]

Я использую INDIRECT в другой части таблицы и обозначения массива еще раз в другом месте.

VLOOKUP($A3,INDIRECT("'"&M$2&"'!$B$3:$I$516"),6,FALSE)

Я не хочу объединять страницы (которые, я признаю, в некоторых отношениях сильно упростили бы вещи).

Есть ли способ упростить это большое выражение в более короткое, которое достигает той же цели (и, надеюсь, позволит избежать дальнейшего редактирования при добавлении остальных данных)?

Я пытался заставить что-то работать с косвенными, vlookup и массивом, но я всегда получаю ошибки #VALUE, вероятно, из-за искажения синтаксиса.

Я не ищу VBA-решения и не имею прямого ответа «преобразовать его в базу данных», но любая помощь приветствуется.

0
просто для ясности, если в идентификаторе карты было 10 совпадений, я бы хотел, чтобы все 10 результатов из столбца 6 были возвращены, чтобы min мог выбрать наименьшее. Stephen 13 лет назад 0
Одна быстрая вещь: если вы используете Excel 2007 или новее, вы можете использовать IFERROR, чтобы избежать повторения. Например, `= IFERROR (VLOOKUP ($ A3, Set1! $ B $ 3: $ I $ 500,6, FALSE), 99999)` Excellll 13 лет назад 1
Этот совет помогает упростить множество злоупотреблений isna :) Stephen 13 лет назад 0
Чтобы ответить на вопрос в заголовке: К сожалению, вы не можете использовать 3D-ссылки с VLOOKUP, либо как исходную ссылку, либо как значение поиска. Более подробная информация здесь: http://office.microsoft.com/en-us/excel-help/create-a-3-d-reference-to-the-same-cell-range-on-multiple-worksheets-HP010102346. ASPX Ellesa 13 лет назад 0

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

1
chris neilsen

Вы можете поместить поиски в Set#листах.
Если ваша формула на листе называется Summary:
Поместите формулу в ячейку, скажем, I1на каждом Setлисте

=IFERROR(VLOOKUP(Summary!A3,$B$3:$I$500,6,FALSE),99999)

а потом в Summaryлисте положи 

=MIN('Set1:Set70'!I1:I1)

Предполагается, что Setлисты сгруппированы вместе (т.е. нет других листов между ними)

К сожалению, в сводном листе есть 100 интересующих статей, которые меняются довольно часто, поэтому я вынужден был бы поместить 100 таких записей в (скажем) столбец I, что кажется более трудоемким, чем расширение существующей формулы. Спасибо за идею повернуть поиск вокруг однако. Stephen 13 лет назад 0
0
Ellesa

Чтобы ответить на вопрос в заголовке: К сожалению, вы не можете использовать 3D-ссылки с VLOOKUP, либо как исходную ссылку, либо как значение поиска. Более подробная информация здесь: http://office.microsoft.com/en-us/excel-help/create-a-3-d-reference-to-the-same-cell-range-on-multiple-worksheets-HP010102346. ASPX

Ваша формула кажется достаточно упрощенной, хотя и довольно длинной. Хотя, если ваша рабочая книга станет больше, я бы посоветовал использовать ее INDEX/MATCHвместо VLOOKUPболее быстрых вычислений.

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