Помогите: нужно вытащить из столбца натуральные числа и сделать анализ

Автор zoldato55, 11 апреля 2020, 17:49

0 Пользователи и 1 гость просматривают эту тему.

zoldato55

Доброй день!

Помогите кто может: Есть столбец с текстовыми значениями подобного типа

120069080000100001000
120069080000100002008
120069080000100003006
120069080000100004009
120069080000100005007
120069080000100007005
120069080000100008001
120069080000100011003
120069080000100012005

в каждой из ячеек нужно отбросить последние три символа, из оставшегося текста взяв последние 5 символов преобразовать их в натуральное число - получится столбец из чисел:
1
2
3
4
5
7
8
11
12

нужно в этом столбце найти пропущенные порядковые номера, например для данного примера пропущенные номера будут 6, 9, 10.
Это видимо можно сделать только макросом?

mikekaganski

Что значит "найти"? куда-то записать? показать диалог? подсветить идущие неподряд ячейки?
С уважением,
Михаил Каганский

zoldato55

Цитата: mikekaganski от 11 апреля 2020, 17:53
Что значит "найти"? куда-то записать? показать диалог? подсветить идущие неподряд ячейки?

Записать в отдельный столбец, или через запятую в одну строчку.

mikekaganski

С уважением,
Михаил Каганский


bigor

По данному примеру можно и формулой :)

=TEXTJOIN(",";1;IF(C1:C8+1<>C2:C9;(C1:C9)+1;"");IF(C2:C9-1<>C1:C8;(C2:C9)-1;""))
Поддержать разработчиков LibreOffice можно можно тут, а наш форум вот тут

mikekaganski

Так можно даже без повтора ...

=TEXTJOIN(",";1;IF(C2:C9-C1:C8>1;(C1:C9)+1;"");IF(C2:C9-C1:C8>2;(C2:C9)-1;""))

... но при разрыве в 3 и больше уже правильно не сработает.
С уважением,
Михаил Каганский

zoldato55

Что-то не пойму как обе формулы работают - показывают "#ЗНАЧ!".

kompilainenn

Поддержать разработчиков LibreOffice можно тут, а наш форум вот тут

zoldato55

Цитата: kompilainenn от 12 апреля 2020, 00:50
Наверное функции на русском включены?

вы про настройку в параметрах "Использовать английские имена функций"? переключал ее и так и эдак - все равно ошибка в формуле видимо...

zoldato55

Сорян всем - чет туплю - не увидел готовый файлик "for_zold1.ods"...
Работает частично, как Вы и говорили - не работает при больших разрывах в нумерации.

Во вложении пример - выделил красным как должно быть при больших разрывах.

bigor

Можно и дальше поизвращаться с формулами :)
Ищет до 10 пропусков, легко расширяется
=TEXTJOIN(",";1;IF(C2:C9-C1:C8>1;IF({1;2;3;4;5;6;7;8;9;10}<C2:C9-C1:C8;(C1:C8)+{1;2;3;4;5;6;7;8;9;10};"");""))


Цитата: zoldato55 от 12 апреля 2020, 00:41
Что-то не пойму как обе формулы работают - показывают "#ЗНАЧ!".

Формулы массивные, вводятся тремя кнопками Ctrl+Shift+Enter
Поддержать разработчиков LibreOffice можно можно тут, а наш форум вот тут

mikekaganski

Здорово!

Упрощение:
=TEXTJOIN(",";1;IF(C2:C9-C1:C8>{1;2;3;4;5;6;7;8;9;10};C1:C8+{1;2;3;4;5;6;7;8;9;10};""))

Жаль, нет сортировки.
С уважением,
Михаил Каганский

zoldato55

Да, точно последние формулы работают - большое Спасибо за них (особенно про комбинацию клавиш Ctrl+Shift+Enter - не знал, что так нужно).
Можно еще попросить - кто может добавить:
1) какое-то универсальное решение по расширению диапазона разрывов  - немного проблематично если там пропуск в 500 или 3000 штук например.
2) с сортировкой действительно было бы круто.

bigor

Выжимаем формулы дальше :)
В принципе любой пропуск, меняем диапазон а1:а10, на необходимый
=TEXTJOIN(",";1;IF(C2:C9-C1:C8>TRANSPOSE(ROW(A1:A10));C1:C8+TRANSPOSE(ROW(A1:A10)));"")
С сортировкой пока никак :(
Добил и сортировку, и теперь даже не массивная
=TEXTJOIN(",";1;TRANSPOSE(IF(C2:C9-C1:C8>TRANSPOSE(ROW(A1:A10));C1:C8+TRANSPOSE(ROW(A1:A10));"")))
Поддержать разработчиков LibreOffice можно можно тут, а наш форум вот тут