А так?


KL
[MVP - Microsoft Excel]

Проблема не в макросах, а в одном из 3х:

1) спрашивающий не может точно сформулировать задачу
2) спрашивающий не знает как их правильно использовать
3) данные на самом деле не одинаковые и таковыми лишь кажутся

время выкладывать файл, а не куски данных

KL
[MVP - Microsoft Excel]

Пример прилагается

KL
[MVP - Microsoft Excel]

SvIvaВ примерах Уокенбаха нашла подходящие формулы массивов для решения этих задач (без макросов), но не могу справиться с ошибкой #ЧИСЛО!. Функцию ЕОШ() я знаю, но формулы такие длинно-вложенные... Посмотрите файл, может посоветуете что.

данная формула на мой взгляд далека от идеала и скорее интересна в чисто дидактическом плане.

1) Этот метод может и хорош, когда речь о сотнях и менее ячеек, т.к. формула чрезвычайно медленная

2) Добавить замену ошибок текстом нулевой длины без существенных потерь в скорости можно например так:
- в ячейку[H2] вносим формулу:
=СУММПРОИЗВ(1/СЧЁТЕСЛИ(data;data))
- главную формулу преобразуем в:
{=ЕСЛИ(СТРОКА()-СТРОКА($A$2)>=$H$2;"";ИНДЕКС(data;НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(data;data;0)=СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(data)));ПОИСКПОЗ(data;data;0);"");СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(data))))))}

3) Формула Джона не переваривает пустые ячейки, возвращая ошибку. Следующая формула глотает пустые ячейки (внося значение 0 в список уникальных значений):
{=ЕСЛИ(СТРОКА()-СТРОКА($A$2)>=$H$2;"";ИНДЕКС(data;НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(""&data;""&data;0)=СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(data)));ПОИСКПОЗ(""&data;""&data;0);"");СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(data))))))}

4) К тому же, используемая Джоном, конструкция ДВССЫЛ("1:"&ЧСТРОК(data)) является короткой, но крайне неэффективной фомой получения массива ввиду своей летучести. Формулы использующии функции типа ДВССЫЛ пересчитываются при любом изменении на листе, независимо от того, влияет это изменение на результат или нет. А это тем более нежелательно в столь тяжеловесных формулах. Летучесть формулы можно устранить более быстрой (в 500 ячейках скорость формулы возрасла на 20%) и нелетучей конструкцией:
СТРОКА(ИНДЕКС(A:A;1):ИНДЕКС(A:A;ЧСТРОК(data)))
т.е.:
{=ЕСЛИ(СТРОКА()-СТРОКА($A$2)>=$H$2;"";ИНДЕКС(data;НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(""&data;""&data;0)=СТРОКА(ИНДЕКС(A:A;1):ИНДЕКС(A:A;ЧСТРОК(data)));ПОИСКПОЗ(""&data;""&data;0);"");СТРОКА(ИНДЕКС(A:A;1):ИНДЕКС(A:A;ЧСТРОК(data))))))}

При всем при этом, все-таки для данной задачи несравненно лучше использовать Расширенный Фильтр как вручную так и через VBA.

KL
[MVP - Microsoft Excel]

KL (XL)3) Формула Джона не переваривает пустые ячейки, возвращая ошибку. Следующая формула глотает пустые ячейки (внося значение 0 в список уникальных значений):
{=ЕСЛИ(СТРОКА()-СТРОКА($A$2)>=$H$2;"";ИНДЕКС(data;НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(""&data;""&data;0)=СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(data)));ПОИСКПОЗ(""&data;""&data;0);"");СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(data))))))}

Если 0 не нужен, тогда можно так:

{=ЕСЛИ(СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(data)))>=$H$2;"";ИНДЕКС(data;НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(""&data;""&data;0)=(data<>"")*СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(data)));ПОИСКПОЗ(""&data;""&data;0);"");СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(data))))))}

KL
[MVP - Microsoft Excel]

2 SvIva

SvIvaПравильно ли я поняла, что формулы массивов всегда медленнее single формул ?Да и нет, сложно сравнивать, так как в известном смысле формула массивов это n-ое кол-во "single" формул в одной ячейке. Например:

={СУММ(A1:A5*2)}

эквивалентна:

=A1*2
=A2*2
=A3*2
=A4*2
=A5*2
=СУММ(B1:B5)

Другое дело это то, что в сложных формулах массивов очень часто неэффективность возникает из-за необходимости повтора операций, которые в обычных формулах не повторялись бы. Простой пример:

в каждой ячейке с нашей мега-формулой существет выражение СТРОКА(ИНДЕКС(A:A;1):ИНДЕКС(A:A;ЧСТРОК(data))) (да еще дважды) генерируещее некий стандартный массив из которого берется лишь один заранее известный элемент.

SvIvaИз твоих предыдущих ответов на разные темы я поняла, что любые формулы листа быстрее кода. Так ли это? Давай таки будем сравнивать яблоки с яблоками. Формула массива в некотором роде подобна циклу в VBA - повтор однотипных операций с переменными. Каждая операция в отдельности намного быстрее в формулах так как они компиллируются в С. Вопрос в том, использует ли соответствующий код VBA тот же алгоритм или нет. Ответ - нет. Расширенный фильтр использует намного более эффективный алгоритм (сходный с бинарным поиском + SmartCalc) и как таковой, если не ошибаюсь, скомпиллирован в С.

SvIvaФормулы Уокенбаха надо разбирать для понимания использования функций, но не применять их ?Ну это совсем абсурдное утверждение. По-моему, я ясно дал понять, что речь о конкретной формуле. В любом случае, книги Джона дают лишь поверхностное понятие о формулах массива, и для более глубокого их изучения, я всегда рекомендую след. имена:

Bob Umlas: http://www.emailoffice.com/excel/arrays-bobumlas.html
Laurent Longre: http://xcell05.free.fr/
Harlan Grove: поиск в microsoft.public.excel.worksheetfunctions
Aladin Akyurek: поиск в http://www.mrexcel.com/
Bob Philips: http://www.xldynamic.com/source/xld.SUMPRODUCT.html

KL
[MVP - Microsoft Excel]