sigizmind,

Или я чего-то не понял, или тут дело гиблое. Вы просто прикиньте сколько времени будет уходить на полный пересчет если 80.000 (40.000 мин. + 40.000 макс.) раз сканировать диапазон в 40.000 строк (я так понимаю, бинарный метод исключен. т.к. нельзя отсортировать калькулируемые столбцы по возрастанию одновременно). Даже если использовать ПОИСКПОЗ(), которая прекращает поиск при первом нахождении соответствия, это может быть очень долго в зависимости от расположения искомого значения.

На всякий случай, вот самое быстродействующее решение пришедшее мне в голову :) Для 10.000 строк полный пересчет на нехилом компе в XL2010 занял 27 сек (а у вас там еще свои формулы)

Есс-но первой проверкой в формуле
ЕСЛИ(СТРОКИ($A1:$A$1)<2;--($A$1<B1);...;)
можно пожертвовать и сделать свою формулу в первой строке, что должно слегка ускорить пересчет, но, думаю, не значительно.

Пояснение: в данном решении мне-таки удалось использовать бинарный поиск (поиск последнего числового значения), но вся тяжесть пересчета легла на сравнение всех значений с искомым и трансформирование ненужных в значение ошибки деления на 0. Также значительная экономия достигнута с помощью нарастающей ссылки на диапазон.

И еще: 27 сек может показаться относительно приемлимым временем, но до 40.000 оно будет расти в прогрессии. Правда полный пересчет ( http://www.decisionmodels.com/calcsecretsg.htm) - дело необязательно постоянное если нет летучих формул ( http://www.decisionmodels.com/calcsecretsi.htm) и не превышены лимиты зависимостей на листе ( http://www.decisionmodels.com/calcsecretsf.htm)

Ну давайте попробуем :)

Основная формула: ПОИСКПОЗ(1;0/($A1:$A$1<B1))

1. все остальное адаптация для первой строки т.к. $A1:$A$1 в отличие от последующих $A1:$A$1, $A1:$A$1 и т.д. возвратит не массив (вектор) чисел а число, что непригодно для второго параметра функции ПОИСКПОЗ()

2. $A1:$A$1 нарастающий при копировании вниз диапазон, т.к. ссылка содержит начальную, хоть и на второй позиции, полностью фиксированную ячейку $A$1 и частично фиксированную (только столбец) ячейку $A1. Таким образом при копировании формулы вниз начало диапазона будет оставаться в $A$1 а конец будет переходить на уровень искомой ячейки например в столбце [B:B]:

1 | $A1:$A$1
2 | $A1:$A$1
2 | $A1:$A$1
2 | $A1:$A$1
2 | $A1:$A$1

Ну давайте попробуем :)

Основная формула: ПОИСКПОЗ(1;0/($A1:$A$1<B1))

1. все остальное адаптация для первой строки т.к. $A1:$A$1 в отличие от последующих $A1:$A$1, $A1:$A$1 и т.д. возвратит не массив (вектор) чисел а число, что непригодно для второго параметра функции ПОИСКПОЗ()

2. $A1:$A$1 нарастающий при копировании вниз диапазон, т.к. ссылка содержит начальную, хоть и на второй позиции, полностью фиксированную ячейку $A$1 и частично фиксированную (только столбец) ячейку $A1. Таким образом при копировании формулы вниз начало диапазона будет оставаться в $A$1 а конец будет переходить на уровень искомой ячейки например в столбце [B:B]:

1 | $A1:$A$1
2 | $A$1:$A2
3 | $A$1:$A3
4 | $A$1:$A4
5 | $A$1:$A5

Для наглядности в дальнейшем будем использовать формулу из строки 5: ПОИСКПОЗ(1;0/($A$1:$A5<B5))

3. $A$1:$A5 возвращает массив значений {1:2:3:2:6}

4. Выражение ($A$1:$A5<B5) сравнивает каждый из элементов предыдущего массива со значением ячейкм B5: {1:2:3:2:6}<2 и возвращает массив {ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}

5. Далее делим 0 на полученный массив: 0/{ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}. Данный трюк основан на двух принцыпах:
а) логические значения ИСТИНА и ЛОЖЬ имеют имплицитные значения 1 и 0 соответственно. В эти значения их можно конвертировать например с помощью любой арифметиской операции, в т.ч. деления. Т.е. по сути мы делаем вот что: 0/{1:0:0:0:0}
б) деление на 0 дает ошибку, т.е. не число: 0/{1:0:0:0:0}={1:#ДЕЛ/0!:#ДЕЛ/0!:#ДЕЛ/0!:#ДЕЛ/0!}. Таким образом в результирующем массиве все значения меньшие, чем искомое будут представлены номером строки в диапазоне (не обязательно на листе!), а все остальные - значением ошибки #ДЕЛ/0!

6. Осталось найти последнее числовое значение в массиве {1:#ДЕЛ/0!:#ДЕЛ/0!:#ДЕЛ/0!:#ДЕЛ/0!}, чего можно достичь с помощью функции ПОИСКПОЗ() с приблизительным поиском (третий параметр опущен, 1 или -1) невозможно большого числа (в данном случае 1 вполне достаточно, т.к. при делении 0 на что угодно возможно только два значения: 0 и :#ДЕЛ/0!)

В данном случае последнее число является одновременно первым, т.ч. ПОИСКПОЗ(1;{1:#ДЕЛ/0!:#ДЕЛ/0!:#ДЕЛ/0!:#ДЕЛ/0!}) вернет 1

Надеюсь, так понятнее :)

Пардон, в спешке налепил описок. Вот отредактированный вариант:

Ну давайте попробуем :)

Основная формула: ПОИСКПОЗ(1;0/($A1:$A$1<B1))

1. все остальное адаптация для первой строки т.к. $A1:$A$1 в отличие от последующих $A1:$A$2, $A1:$A$3 и т.д. возвратит не массив (вектор) чисел а число, что непригодно для второго параметра функции ПОИСКПОЗ()

2. $A1:$A$1 нарастающий при копировании вниз диапазон, т.к. ссылка содержит начальную, хоть и на второй позиции, полностью фиксированную ячейку $A$1 и частично фиксированную (только столбец) ячейку $A1. Таким образом при копировании формулы вниз начало диапазона будет оставаться в $A$1 а конец будет переходить на уровень искомой ячейки например в столбце [B:B]:

1 | $A1:$A$1
2 | $A$1:$A2
3 | $A$1:$A3
4 | $A$1:$A4
5 | $A$1:$A5

Для наглядности в дальнейшем будем использовать формулу из строки 5: ПОИСКПОЗ(1;0/($A$1:$A5<B5))

3. $A$1:$A5 возвращает массив значений {1:2:3:2:6}

4. Выражение ($A$1:$A5<B5) сравнивает каждый из элементов предыдущего массива со значением ячейкм B5: {1:2:3:2:6}<2 и возвращает массив {ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}

5. Далее делим 0 на полученный массив: 0/{ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}. Данный трюк основан на двух принцыпах:
а) логические значения ИСТИНА и ЛОЖЬ имеют имплицитные значения 1 и 0 соответственно. В эти значения их можно конвертировать например с помощью любой арифметиской операции, в т.ч. деления. Т.е. по сути мы делаем вот что: 0/{1:0:0:0:0}
б) деление на 0 дает ошибку, т.е. не число: 0/{1:0:0:0:0}={0:#ДЕЛ/0!:#ДЕЛ/0!:#ДЕЛ/0!:#ДЕЛ/0!}. Таким образом в результирующем массиве все значения меньшие, чем искомое будут представлены числом 0, а все остальные - значением ошибки #ДЕЛ/0!

6. Осталось найти последнее числовое значение в массиве {0:#ДЕЛ/0!:#ДЕЛ/0!:#ДЕЛ/0!:#ДЕЛ/0!}, чего можно достичь с помощью функции ПОИСКПОЗ() с приблизительным поиском (третий параметр опущен, 1 или -1) невозможно большого числа (в данном случае 1 вполне достаточно, т.к. при делении 0 на что угодно возможно только два значения: 0 и :#ДЕЛ/0!)

В данном случае последнее число является одновременно первым, т.ч. ПОИСКПОЗ(1;{0:#ДЕЛ/0!:#ДЕЛ/0!:#ДЕЛ/0!:#ДЕЛ/0!}) вернет 1, т.е. номер строки в диапазоне (не обязательно на листе!)

Надеюсь, так понятнее :)

sigizmindНе подскажите еще, с увеличением строк с 10000 до 40000 время на ДЕСЯТИЧНЫЙ порядок увеличится? т.е. у Вас 27сек и станет 270сек примерно?
Без учета того, какие у вас там формулы уже, думаю время вырастет раз в 40, но это полный пересчет. При изменении единичных значений в столбцах [B:B] и [C:C] пересчет будет мгновенным (если конечно лист не летуч, см. мой пост от сегодня, 20:36)

Вот не поленился - сделал симулятор. У меня результаты такие:

10.000 - 24,19531 сек

40.000 - 548,2109 сек

х22.65773 раз

Думаю, решение с расчетами в памяти (на массивах или со словарем) будет на порядок быстрее.

Вот такой неоптимизированный вариант у меня заполняет оба столбца для 40.000 строк за 32 сек:

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

sigizmindВозникла еще такая задача, не знаю как решить :-(
Есть большой столбец (30000 строк) с текстовыми фразами. Справа от него столбец с числами. Надо создать два столбца, в первом будут указаны все встречающиеся текстовые фразы из первого столбца , а во втором произведение всех чисел для этой фразы.
Если непонятно - прилагаю пример.
Пожалуй соглашусь с AndreTM, делать список уникальных фраз формулами (если вам нужно это) будет намного медленнее, чем даже предыдущая задача :) VBA через словарь будет очень быстро. Сводная таблица - оптимальный вариант.

sigizmindKL (XL)Во всех случаях, помимо алгоритма и размера ячеек, на время перерасчета будут влиять сами данные, в особенности удаленность искомого значения от начала поиска.
Тогда еще такой вопрос. Данные искомые (ищущиеся наименьшие и наибольшие) находятся недалеко от сравнивомого (в пределах от 1 до 200-500 строк примерно), есть ли смысл как-то переделать алгоритм сравнения, можно таблицу перевернуть (она отсортирована по датам, можно чтобы последние даты были сверху).
Сегодня проверил - с Вашим алгоритмом стало считать примерно в 2 раза быстрее, чем с моим топорным, еще раз спасибо за помощь!
Думаю, что алгоритм сравнения предельно быстрый. Проблема в необходимости создания промежуточного массива результатов сравнений для последующего поиска с пом. ПОИСКПОЗ() и, если честно, я пока не вижу возможности ускорить эту операцию :(