Оптимизация vlookup с несколькими критериями (индекс + совпадение)

У меня есть диапазон Excel 12x18, который рисует данные с листа 823x20 (Results!$A:$T) в соответствии со страницей диапазона 12x18, заголовками строк и столбцов (критерий 1, критерий 2 и критерий 3 соответственно).

={INDEX(Results!$A:$T,
        MATCH(1, (criterion1 = Results!$A:$A) * (criterion2 = Results!$B:$B), 0),
        MATCH(criterion3, Results!$A$1:$T$1, 0))}

Как видите, данных не так много, но все же, когда я меняю заголовок страницы, моему компьютеру (2 ГГц Intel Xeon с 24 ГБ ОЗУ) требуется около минуты, чтобы обновить 216 (12x18) поисковых запросов по индексу, и я m не сделал и половины пути к созданию поисковых запросов.

Ранее в моем проекте у этих поисков был только один критерий, поэтому я использовал VLOOKUP(), и результаты приходили очень быстро. Однако теперь мне нужно найти значения по трем критериям, и приведенная выше функция — лучший способ, с помощью которого мне удалось это сделать. Тем не менее, похоже, это ответственно за долгое время вычислений, которое я получаю. Итак, мой вопрос: как я могу оптимизировать VLOOKUP() нескольких критериев? Должен ли я настроить INDEX(1, MATCH()*MATCH(), MATCH()) или есть более быстрый способ сделать это?

Вот пример поиска по индексу 12x18 (имя ветки — единственная переменная, которую конечный пользователь сможет изменить):

Индекс

И на листе 823x20 он ищет (на самом деле столбец A не имеет слияний):

введите здесь описание изображения


optimization excel indexing excel-match
person Waldir Leoncio    schedule 14.08.2013    source источник
comment
первый вариант — не использовать целые диапазоны столбцов; во-вторых, вы можете создать один ключевой столбец, объединяющий A и B, чтобы вы могли выполнять простое ПОИСКПОЗ, не прибегая к формуле массива.   -  person JosieP    schedule 14.08.2013
comment
@pnuts, судя по изображениям, 3 должно быть 0. На самом деле вторая строка первого изображения должна совпадать со второй строкой второго изображения. Этого не происходит, потому что имена ветвей несовместимы между изображениями, пожалуйста, не обращайте на это внимания.   -  person Waldir Leoncio    schedule 14.08.2013
comment
@JosieP, спасибо за совет. Я пытался избежать объединения ячеек и сокращения диапазонов, но если я не вижу другого пути, я мог бы попробовать.   -  person Waldir Leoncio    schedule 14.08.2013
comment
вы также можете попробовать ={INDEX(Results!$A:$T, MATCH(1, IF(criterion1 = Results!$A:$A,IF(criterion2 = Results!$B:$B,1, 0),0), MATCH(criterion3, Results!$A$1:$T$1, 0))}   -  person JosieP    schedule 14.08.2013

Ответы (2)


arrow_upward
1
arrow_downward

Для сводки и параметров Поиска по нескольким критериям вы можете проверить это.

Я часто использую метод 2 здесь (это формула без массива) и метод здесь.

Я думаю, вы должны попробовать их, чтобы проверить их скорость.

person sancho.s ReinstateMonicaCellio    schedule 21.12.2014

arrow_upward
0
arrow_downward

Во-первых, нельзя ли этого добиться, просто отфильтровав столбец А на листе «Результаты»? Я думаю, что это самое простое решение.

Однако я создал пример рабочей книги на основе описанных вами критериев. Его можно найти здесь

Сначала я создал список уникальных ветвей с помощью расширенного фильтра для уникальных значений в столбце A листа «Результаты» и поместил их на лист «Списки», где я создал именованный диапазон listUnqBranches. Я использовал этот именованный диапазон для создания раскрывающегося списка проверки данных в ячейке A1 листа «Лист1», чтобы пользователи могли выбирать, какую ветвь они хотели бы видеть. Я назвал эту ячейку Филиалом.

Затем я создал два именованных диапазона. rngDate определяется с помощью этой формулы динамического именованного диапазона:

=INDEX(Results!$B:$B,MATCH(Branch,Results!$A:$A,0)):INDEX(Results!$B:$B,MATCH(Branch,Results!$A:$A,0)+COUNTIF(Results!$A:$A,Branch)-1)

rngLookup определяется с помощью этой формулы динамического именованного диапазона:

=INDEX(Results!$C:$C,MATCH(Branch,Results!$A:$A,0)):INDEX(Results!$T:$T,MATCH(Branch,Results!$A:$A,0)+COUNTIF(Results!$A:$A,Branch)-1)

Наконец, в ячейке B2 листа «Лист1», скопированной снова и снова, находится эта формула:

=IF(Branch="","",INDEX(rngLookup,MATCH($A2,rngDate,0),MATCH(B$1,Results!$C$1:$T$1,0)))

Обратите внимание, что решение формулы с именованными диапазонами зависит от данных на листе «Результаты», отсортированных по ветвям. Это работает для вас?

person tigeravatar    schedule 14.08.2013