Эффективная формула Excel для возврата нескольких совпадений из большого количества строк

Я в шоке от серьезной проблемы. У меня есть набор данных, состоящий из примерно 16000 строк (может быть больше в будущем). Этот список в основном представляет собой прейскурант, содержащий продукты и соответствующие им платы за установку. Теперь продукты классифицируются по следующей иерархии: Город -> Категория -> Рейтинг / Тип. Прежде чем я использовал именованные диапазоны для обозначения каждого набора, объединив City & Category & Rating (_XYZ_SPC_9.5). Это привело к тому, что около 1500 названных диапазонов увеличили размер файла Excel. Поэтому я решил рассчитать продукты «на лету», используя входные данные от пользователя. Я пробовал формулы массива и простые формулы, но они занимают некоторое время, чтобы вычислить (16000 строк !!), что неприемлемо с точки зрения юзабилити; наши продавцы очень подробно рассказывают о том, сколько времени им приходится тратить на инструмент.

Я загрузил образец файла: Пример прейскуранта

Формулы, которые я использовал до сих пор:

=IFERROR(INDEX($H$6:$H$15000, SMALL(INDEX(($AE$9=$R$6:$R$15000)*(MATCH(ROW($R$6:$R$15000), ROW($R$6:$R$15000)))+($AE$9<>$R$6:$R$15000)*15000, 0, 0), AC3)),"Not Available") {=IFERROR(INDEX(ref_PRICE_LIST!$H$6:$H$16074,MATCH(INDEX(ref_PRICE_LIST!$H$6:$H$16074,(SMALL(IF(IF(RIGHT($AE$3,3)="All",ref_PRICE_LIST!$Z$6:$Z$16074,ref_PRICE_LIST!$R$6:$R$16074)=$AE$3,ROW(ref_PRICE_LIST!$H$6:$H$16074)-ROW(ref_PRICE_LIST!$H$6)+1),$AC3))),ref_PRICE_LIST!$H$6:$H$16074,0),1),"Not Available")} 

Я был бы очень признателен, если кто-то сможет мне помочь.

Спасибо огромное!

Я думаю, что лучший способ ускорить это – разбить формулу на вспомогательную колонку K и столбец повтора L

Столбец помощника (скопируйте все 16 000 строк данных)

 =IF($D:$D=$O$2,ROW(),"") 

Столбец результата (начиная с L2 , скопируйте столько, сколько вам нужно)

 =IFERROR(INDEX($F:$F,SMALL($K:$K,ROW()-1)),"Not available") 

Я проверил это примерно с 150 000 строк и обновил его в <1s

  • Как добавить многомерный массив (AoA) в Excel с помощью Perl?
  • Рельсы не доступны для доступа к объекту В контроллере с использованием Roo для импорта Excel / CSV и PGSQL DB
  • Возможно ли иметь кнопку «сбросить форму» в форме Excel?
  • Excel несколько условий для каждой строки в нескольких строках (приветствуются решения массивов или скриптов) (Пороги производительности предприятия)
  • Средние строки Excel для формулы массива
  • Корреляционная функция с добавлением и вычитанием в массивах
  • Interesting Posts

    Импортируйте файл excel, измените порядок расположения столбцов и экспортируйте новый макет обратно в excel

    Подключение Excel 2013 к SSRS 2012 для использования в качестве источника данных

    Как получить последнюю строку из Excel в Microsoft Flow?

    Как заставить Excel 2007 остановить форматирование ячеек?

    OleDB и знак доллара

    Очистить содержимое диапазона, если ячейка содержит определенный текст

    макрос ошибка времени выполнения «9»: индекс вне диапазона

    Может ли VLOOKUP взять массив в качестве критериев?

    Установите флажок «Формы» в обработчике событий VBA

    Доступ к данным из Excel в одном столбце с использованием Apache POI

    Копировать строки в столбцы с помощью VBA

    VBA: объединить ячейки с одинаковым идентификационным номером

    Извлечение точных слов в таблицу

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

    Сравнение прогнозируемых значений с фактическими данными

    Давайте будем гением компьютера.