Страницы: 1
RSS
Вывод значений из строк, в которых значение ячеек столбца соответствуют заданному, как ускорить расчеты?
 
Добрый вечер

Помогите, пожалуйста, с задачей.

Сделал формулу, для вывода в столбце Е всех значений из строк столбца B, в которых значение ячеек A соответствует ячейке G5. Но получилось это сделать с помощью формулы массива:  
Код
=ЕСЛИОШИБКА(ИНДЕКС($B:$B;НАИМЕНЬШИЙ(ЕСЛИ($A:$A=$G$5;СТРОКА($B:$B));D5));"") 

Но на бОльшем кол-ве данных Excel начинает подвисать, а комп тормозить.

Как можно изменить формулу на "не формулу массива"?
В одном из уроков видел комментарий, с такой формулой
Код
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16;АГРЕГАТ(15;6;(СТРОКА($B$2:$B$16)-1)/($E$2=$A$2:$A$16);СТРОКА()-5));"") 
, но не знаю как применить на практике.
 
сделайте сводной с фильтром и не нужно формул для большого объема. Ну или нужно смотреть в сторону Powerquery. MSQuery или тестировать новый фунцнкции новых версий excel. А так - все варианты будут примерно равны по скорости разве что ограничте диапазон, а то по миллиону строк бегать каждой формуле не просто.  
=ЕСЛИОШИБКА(ИНДЕКС($B:$B;НАИМЕНЬШИЙ(ЕСЛИ($A$1:$A$10000=$G$5;СТРОКА($B$1:$B$10000));D5));"") .
По вопросам из тем форума, личку не читаю.
 
БМВ, спасибо за советы, попробовал формулу, но она не сработала..

 
Я не сказал что она не массивная, она просто рабочая :-)
По вопросам из тем форума, личку не читаю.
 
БМВ,  ааа.. не понял сразу, спасибо))

а не массива формула будет быстрее или точно также отрабатывать?    
 
Цитата
iAlex написал:
а не массива формула будет быстрее или точно также отрабатывать?  
Например у меня, на довольно старом компе Q9550 c 8 гб ОЗУ, что массивные что нет, уже на 500 строках дают ощутимую задержку. С новыми пеньками, наверное, будет получше, но ненадолго.
 
может даже медленнеею 10000 вычислений

=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$10000=$G$5,ROW($B$2:$B$10000)),D5)),"")       10,89844
=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($B$2:$B$10000)/($A$2:$A$10000=$G$5),D5)),"")              11,03125

второй проход дал тоже соотношение.
Изменено: БМВ - 05.04.2021 23:47:55
По вопросам из тем форума, личку не читаю.
 
БМВ, хах) получается ваш вариант массива чуть побыстрее даже чем не массив, спасибо за помощь и хорошее решение!

memo, у меня сейчас +/- такие же показатели у компа :-)
 
С функцией АГРЕГАТ формула будет такая
Код
=IFERROR(INDEX(B$2:B$10000;AGGREGATE(15;6;ROW($1:$10000)/(A$2:A$10000=D$1);ROWS($2:2)));"")
 
jakim, спасибо, крутое решение!
Страницы: 1
Наверх