Создание списка уникальных значений по условиям

[Gobaith]

Добрый день!

Очень нужно написать формулу, которая бы генерировала список уникальных значений с соблюдением следующих условий:

- пропускать пустые ячейки
- пропускать ячейки, начинающиеся с дефиса
- сравнивать только первые 5 знаков в каждой ячейке.

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

{=INDEX(kor!$C$2:$C$999, MATCH(0, IF(ISBLANK(kor!$C$2:$C$999), 1, COUNTIF(kor!$Q$1:Q1, kor!$C$2:$C$999)), 0))}

Дальше что-то завяз. Как сюда вклинить LEFT(kor!C$2:C$999,1)="–", и что нужно дописать, чтобы сравнивались только первые 5 знаков представляю довольно смутно.

Буду очень благодарен за помощь.

[DiemonStar]

А расширенный фильтр использовать не пробовали?

[ZORRO2005]

Сообщение от Gobaith

Добрый день!
- сравнивать только первые 5 знаков в каждой ячейке.

Список:
aaaaa11
aaaaa22
b
b

-c
d
11

Что получаем на выходе?
aaaaa11
aaaaa22
b
d
11

[Gobaith]

@DiemonStar,

Нет, к сожалению, фильтр не вариант. Нужна формула.

@ZORRO2005,

Сообщение от ZORRO2005

Что получаем на выходе?
aaaaa11
aaaaa22
b
d
11

Нет, нужны уникальные значения в границах первых пяти знаков. На выходе получаем:

aaaaa
b
d
11

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

Уверен, все это можно сделать намного эффективнее. Вот только пока не знаю как...

[ZORRO2005]

Сообщение от Gobaith

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

Попробуйте вариант во вложении.
Тоже с 2-мя доп. столбцами.

[Gobaith]

Сообщение от ZORRO2005

Попробуйте вариант во вложении.
Тоже с 2-мя доп. столбцами.

Большое спасибо за вариант. По сравнению с моим, Ваш гораздо лаконичнее и эффективнее.

Вот только лишние столбцы добавляют головной боли: файл и так очень немаленький, а тут - дополнительные 40 тыс. значений.

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

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

[Serge 007]

Сообщение от Gobaith

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

Одной формулой сделать не проблема:

=ИНДЕКС(B$2:B$14;НАИМЕНЬШИЙ(ЕСЛИ((B$2:B$14="")+(ПСТР(B$2:B$14;1;1)="-")=0;ЕСЛИ(ПОИСКПОЗ(ПСТР(B$2:B$14;1;5);ПСТР(B$2:B$14;1;5);)=СТРОКА($1:$13);СТРОКА($1:$13)));СТРОКА(A1)))

Только вот на 40 000 строк думаю тормозить будет безбожно...

[Gobaith]

Сообщение от Serge 007

Одной формулой сделать не проблема...
Только вот на 40 000 строк думаю тормозить будет безбожно...

Действительно, тормозит так мощно, что даже при 8Гб памяти можно не торопясь сходить выпить чаю, пока машинка шуршит.

Даже мои еретические эксперименты с двумя дополнительными столбцами не вешали машину так основательно.

Но всё-равно, огромное Вам спасибо. Такая формула всегда пригодится для файлов меньшего объема. Да и как образец эффективного программирования - вещь незаменимая...

[Serge 007]

Сообщение от Gobaith

...тормозит так мощно, что даже при 8Гб памяти можно не торопясь сходить выпить чаю, пока машинка шуршит.

Я об этом и писал
На 2Гб RAM ноуте Asus (Win 7, Exc 2010) 10 000 строк обрабатывал 2:40

Сообщение от Gobaith

Такая формула всегда пригодится для файлов меньшего объема.

Тут согласен. Если строк полтыщи - работает быстро

ЗЫ Возьмите решение ZORRO2005, доп столбцы вынесите на отдельный лист, если надо - скройте или очень скройте их. Решение Серёги оптимально по скорости, а уж ограничения по пересчёту - это естественные ограничения Экса, их обойти не получится...

[Gobaith]

Сообщение от Serge 007

Возьмите решение ZORRO2005, доп столбцы вынесите на отдельный лист, если надо - скройте или очень скройте их. Решение Серёги оптимально по скорости, а уж ограничения по пересчёту - это естественные ограничения Экса, их обойти не получится...

Думаю, так и придётся сделать, а то эти апдейты значений в реальном времени так и будут держать машину в вечном клинче.

А вообще, временами становится муторно смотреть на то, как Офисный пакет жуёт данные: полтора-два часа на сортировку в Эксе, столько же на полнотекстовую замену в Ворде, когда обыкновенный копеешный текстовичок делает всё на лету за несколько минут.

Одно время думал перейти на OpenOffice, но там, по отзывам, скорости ещё ниже для документов со сложным форматированием...