Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Выделение шести цифр - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena 
Мир MS Excel » Работа и общение » Мозговой штурм » Выделение шести цифр
Выделение шести цифр
MCH Дата: Воскресенье, 24.04.2016, 06:27 | Сообщение № 1
Группа: Админы
Ранг: Старожил
Сообщений: 1756
Репутация: 665 ±
Замечаний: ±

Есть текстовая фраза в ячейке A2, содержащая буквы, цифры, знаки.
Необходимо написать формулу, которая выделяет последовательность из шести подряд идущих цифр, при этом последовательность цифр может отделяться пробелами, буквами или другими знаками отличных от цифр, быть в начале фразы или в конце, фраза может состоять только из 6 цифр.
Если последовательность состоит из менее или более чем 6 цифр то ее выделять не нужно.
Если в фразе встречается несколько последовательностей из 6 цифр, то выделяется первая последовательность (или любая, не имеет значения)
Если в фразе не встречается ни одной последовательностей из 6 цифр, то возвращается пустая строка.
Фраза состоит не более чем из 900 знаков (можно использовать СТРОКА($1:$999)
Формула должна быть копируема вниз.

Кросс на Планете

Пример текстовых фраз во вложении.
Есть формула менее 140 знаков
К сообщению приложен файл: 012345a.xls(25Kb)
 
Ответить
СообщениеЕсть текстовая фраза в ячейке A2, содержащая буквы, цифры, знаки.
Необходимо написать формулу, которая выделяет последовательность из шести подряд идущих цифр, при этом последовательность цифр может отделяться пробелами, буквами или другими знаками отличных от цифр, быть в начале фразы или в конце, фраза может состоять только из 6 цифр.
Если последовательность состоит из менее или более чем 6 цифр то ее выделять не нужно.
Если в фразе встречается несколько последовательностей из 6 цифр, то выделяется первая последовательность (или любая, не имеет значения)
Если в фразе не встречается ни одной последовательностей из 6 цифр, то возвращается пустая строка.
Фраза состоит не более чем из 900 знаков (можно использовать СТРОКА($1:$999)
Формула должна быть копируема вниз.

Кросс на Планете

Пример текстовых фраз во вложении.
Есть формула менее 140 знаков

Автор - MCH
Дата добавления - 24.04.2016 в 06:27
Светлый Дата: Воскресенье, 24.04.2016, 20:47 | Сообщение № 2
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 226
Репутация: 56 ±
Замечаний: 0% ±

Excel 2007
У меня пока получилось 133 132 131 110 символа, но в 2003 офисе работать не будет.


Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Понедельник, 25.04.2016, 10:56
 
Ответить
СообщениеУ меня пока получилось 133 132 131 110 символа, но в 2003 офисе работать не будет.

Автор - Светлый
Дата добавления - 24.04.2016 в 20:47
MCH Дата: Воскресенье, 24.04.2016, 22:03 | Сообщение № 3
Группа: Админы
Ранг: Старожил
Сообщений: 1756
Репутация: 665 ±
Замечаний: ±

у меня 111 108 (должна работать в 2003)
 
Ответить
Сообщениеу меня 111 108 (должна работать в 2003)

Автор - MCH
Дата добавления - 24.04.2016 в 22:03
MCH Дата: Вторник, 26.04.2016, 18:30 | Сообщение № 4
Группа: Админы
Ранг: Старожил
Сообщений: 1756
Репутация: 665 ±
Замечаний: ±

Есть смысл ждать других участников?
 
Ответить
СообщениеЕсть смысл ждать других участников?

Автор - MCH
Дата добавления - 26.04.2016 в 18:30
Светлый Дата: Среда, 27.04.2016, 11:31 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 226
Репутация: 56 ±
Замечаний: 0% ±

Excel 2007
Пусть люди подтянутся. Мало ли какие причины. Подождём до понедельника.
Пока лучшее, что я придумал 95 символов и условное форматирование на Н/Д.


Программировать проще, чем писать стихи.
 
Ответить
СообщениеПусть люди подтянутся. Мало ли какие причины. Подождём до понедельника.
Пока лучшее, что я придумал 95 символов и условное форматирование на Н/Д.

Автор - Светлый
Дата добавления - 27.04.2016 в 11:31
MCH Дата: Четверг, 28.04.2016, 07:06 | Сообщение № 6
Группа: Админы
Ранг: Старожил
Сообщений: 1756
Репутация: 665 ±
Замечаний: ±

без подавления #Н/Д у меня 100 (с "="), но лучше #Н/Д подавить и не через ЕСЛИОШИБКА(), чтобы работала в 2003
 
Ответить
Сообщениебез подавления #Н/Д у меня 100 (с "="), но лучше #Н/Д подавить и не через ЕСЛИОШИБКА(), чтобы работала в 2003

Автор - MCH
Дата добавления - 28.04.2016 в 07:06
Светлый Дата: Четверг, 28.04.2016, 21:57 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 226
Репутация: 56 ±
Замечаний: 0% ±

Excel 2007
Ещё одну формулу придумал на совершенно другом принципе 97 символов, но тоже с #Н/Д.
А для 2003 никак не получается. Не могу из плоскости проблемы выйти.


Программировать проще, чем писать стихи.
 
Ответить
СообщениеЕщё одну формулу придумал на совершенно другом принципе 97 символов, но тоже с #Н/Д.
А для 2003 никак не получается. Не могу из плоскости проблемы выйти.

Автор - Светлый
Дата добавления - 28.04.2016 в 21:57
Светлый Дата: Понедельник, 02.05.2016, 00:28 | Сообщение № 8
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 226
Репутация: 56 ±
Замечаний: 0% ±

Excel 2007
Вскрываюсь. Полное решение 196 символов:
Код
=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(7;ЧАСТОТА(СТРОКА(1:999);ЕОШ(-ПСТР(A2;СТРОКА($1:$999);1))*СТРОКА(1:999));));"";ПСТР(A2;ПОИСКПОЗ(7;ЧАСТОТА(СТРОКА(1:999);ЕОШ(-ПСТР(A2;СТРОКА($1:$999);1))*СТРОКА(1:999));)-6;6))

Формулы короткие, но все условия выполнить не удалось. Есть следующие варианты 95 и 97 символов:
Код
=ПСТР(A2;ПОИСКПОЗ(7;ЧАСТОТА(СТРОКА(1:999);ЕОШ(-ПСТР(A2;СТРОКА($1:$999);1))*СТРОКА(1:999));)-6;6)

Код
=ПСТР(A2;ПОИСКПОЗ(252;МУМНОЖ(1-ЕОШ(-ПСТР(A2;СТРОКА($8:$999)-СТОЛБЕЦ(A:H);1));2^СТРОКА($1:$8)););6)

и под офис 2007 и выше 110 символов:
Код
=ЕСЛИОШИБКА(ПСТР(A2;ПОИСКПОЗ(7;ЧАСТОТА(СТРОКА(1:999);ЕОШ(-ПСТР(A2;СТРОКА($1:$999);1))*СТРОКА(1:999));)-6;6);"")


Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Понедельник, 02.05.2016, 00:33
 
Ответить
СообщениеВскрываюсь. Полное решение 196 символов:
Код
=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(7;ЧАСТОТА(СТРОКА(1:999);ЕОШ(-ПСТР(A2;СТРОКА($1:$999);1))*СТРОКА(1:999));));"";ПСТР(A2;ПОИСКПОЗ(7;ЧАСТОТА(СТРОКА(1:999);ЕОШ(-ПСТР(A2;СТРОКА($1:$999);1))*СТРОКА(1:999));)-6;6))

Формулы короткие, но все условия выполнить не удалось. Есть следующие варианты 95 и 97 символов:
Код
=ПСТР(A2;ПОИСКПОЗ(7;ЧАСТОТА(СТРОКА(1:999);ЕОШ(-ПСТР(A2;СТРОКА($1:$999);1))*СТРОКА(1:999));)-6;6)

Код
=ПСТР(A2;ПОИСКПОЗ(252;МУМНОЖ(1-ЕОШ(-ПСТР(A2;СТРОКА($8:$999)-СТОЛБЕЦ(A:H);1));2^СТРОКА($1:$8)););6)

и под офис 2007 и выше 110 символов:
Код
=ЕСЛИОШИБКА(ПСТР(A2;ПОИСКПОЗ(7;ЧАСТОТА(СТРОКА(1:999);ЕОШ(-ПСТР(A2;СТРОКА($1:$999);1))*СТРОКА(1:999));)-6;6);"")

Автор - Светлый
Дата добавления - 02.05.2016 в 00:28
MCH Дата: Понедельник, 02.05.2016, 07:32 | Сообщение № 9
Группа: Админы
Ранг: Старожил
Сообщений: 1756
Репутация: 665 ±
Замечаний: ±

У меня практически повторение 3й формулы:
Код
=ПСТР(A2;ПОИСКПОЗ(252;МУМНОЖ(1-ЕОШ(-ПСТР(A2&" "&4^9;СТРОКА($1:$999)+СТОЛБЕЦ(A:H)-2;1));2^СТРОКА($1:$8)););6)

Для подавления ошибки я просто добавляю шестизначное число с пробелом
за счет СТРОКА()-СТОЛБЕЦ() вместо СТРОКА()+СТОЛБЕЦ()-2, можно было бы сократить 2 символа, но до этого я не додумался

PS: Что то я до ЧАСТОТы не додумался, в формуле Светлого можно подавить ошибки удлинив формулу на 8 знаков (добавив пробел и шестизначное число), получится самая короткая формула
Код
=ПСТР(A2;ПОИСКПОЗ(7;ЧАСТОТА(СТРОКА(1:999);ЕОШ(-ПСТР(A2&" "&4^9;СТРОКА($1:$999);1))*СТРОКА(1:999));)-6;6)
 
Ответить
СообщениеУ меня практически повторение 3й формулы:
Код
=ПСТР(A2;ПОИСКПОЗ(252;МУМНОЖ(1-ЕОШ(-ПСТР(A2&" "&4^9;СТРОКА($1:$999)+СТОЛБЕЦ(A:H)-2;1));2^СТРОКА($1:$8)););6)

Для подавления ошибки я просто добавляю шестизначное число с пробелом
за счет СТРОКА()-СТОЛБЕЦ() вместо СТРОКА()+СТОЛБЕЦ()-2, можно было бы сократить 2 символа, но до этого я не додумался

PS: Что то я до ЧАСТОТы не додумался, в формуле Светлого можно подавить ошибки удлинив формулу на 8 знаков (добавив пробел и шестизначное число), получится самая короткая формула
Код
=ПСТР(A2;ПОИСКПОЗ(7;ЧАСТОТА(СТРОКА(1:999);ЕОШ(-ПСТР(A2&" "&4^9;СТРОКА($1:$999);1))*СТРОКА(1:999));)-6;6)

Автор - MCH
Дата добавления - 02.05.2016 в 07:32
Светлый Дата: Понедельник, 02.05.2016, 08:57 | Сообщение № 10
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 226
Репутация: 56 ±
Замечаний: 0% ±

Excel 2007
Всё гениальное - просто. Век живи - век учись!
MCH, Спасибо за науку! Добавить нечего. +

Поторопился, оказалось есть что добавить, точнее сократить. Укоротил ещё на три символа, 100:
Код
=ПСТР(A2;ПОИСКПОЗ(7;ЧАСТОТА(СТРОКА(1:999);ЕОШ(-ПСТР(A2&-7^7;СТРОКА($1:$999);1))*СТРОКА(1:999));)-6;6)


Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Понедельник, 02.05.2016, 09:50
 
Ответить
СообщениеВсё гениальное - просто. Век живи - век учись!
MCH, Спасибо за науку! Добавить нечего. +

Поторопился, оказалось есть что добавить, точнее сократить. Укоротил ещё на три символа, 100:
Код
=ПСТР(A2;ПОИСКПОЗ(7;ЧАСТОТА(СТРОКА(1:999);ЕОШ(-ПСТР(A2&-7^7;СТРОКА($1:$999);1))*СТРОКА(1:999));)-6;6)

Автор - Светлый
Дата добавления - 02.05.2016 в 08:57
MCH Дата: Понедельник, 02.05.2016, 09:12 | Сообщение № 11
Группа: Админы
Ранг: Старожил
Сообщений: 1756
Репутация: 665 ±
Замечаний: ±

Укоротил ещё на три символа,

Отличное решение
 
Ответить
Сообщение
Укоротил ещё на три символа,

Отличное решение

Автор - MCH
Дата добавления - 02.05.2016 в 09:12
antycapral Дата: Четверг, 12.05.2016, 12:16 | Сообщение № 12
Группа: Пользователи
Ранг: Участник
Сообщений: 52
Репутация: 5 ±
Замечаний: 0% ±

Excel 2016
Светлый, MCH можно вас попросить объяснить как именно работает кусок формулы, который отвечает за поиск начальной позиции в ПСТР:
Код
ПОИСКПОЗ(7;ЧАСТОТА(СТРОКА(1:999);ЕОШ(-ПСТР(A2&-7^7;СТРОКА($1:$999);1))*СТРОКА(1:999));)-6

У меня от любопытства уже мозг кипит ...
 
Ответить
СообщениеСветлый, MCH можно вас попросить объяснить как именно работает кусок формулы, который отвечает за поиск начальной позиции в ПСТР:
Код
ПОИСКПОЗ(7;ЧАСТОТА(СТРОКА(1:999);ЕОШ(-ПСТР(A2&-7^7;СТРОКА($1:$999);1))*СТРОКА(1:999));)-6

У меня от любопытства уже мозг кипит ...

Автор - antycapral
Дата добавления - 12.05.2016 в 12:16
Светлый Дата: Четверг, 12.05.2016, 19:06 | Сообщение № 13
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 226
Репутация: 56 ±
Замечаний: 0% ±

Excel 2007
ПОИСКПОЗ ищет в массиве значение 7, из его номера вычитает 6 - это начало искомой последовательности из 6 цифр.
ЧАСТОТА подсчитывает, сколько последовательных чисел СТРОКА(1:999) попадёт в интервалы массива возрастающих чисел, среди которых встречаются нулевые значения. Проще - подсчитывает, сколько подряд нулевых значений в списке +1.
Нулевые значения в массиве получаются на месте любой цифры в исходной строке за счёт работы функции ЕОШ. Ошибка получится, если любой вырезанный из строки символ взять со знаком минус. Цифры ошибку не дают.
А конструкция A2&-7^7 гарантирует присутствие в исследуемом тексте шести цифр, отделённых от исходного текста знаком минус. Функция ПОИСКПОЗ ошибку не выдаст, но извлекаемые из строки символы будут за пределами исходной строки, если там нет своих шести цифр.


Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Четверг, 12.05.2016, 19:14
 
Ответить
СообщениеПОИСКПОЗ ищет в массиве значение 7, из его номера вычитает 6 - это начало искомой последовательности из 6 цифр.
ЧАСТОТА подсчитывает, сколько последовательных чисел СТРОКА(1:999) попадёт в интервалы массива возрастающих чисел, среди которых встречаются нулевые значения. Проще - подсчитывает, сколько подряд нулевых значений в списке +1.
Нулевые значения в массиве получаются на месте любой цифры в исходной строке за счёт работы функции ЕОШ. Ошибка получится, если любой вырезанный из строки символ взять со знаком минус. Цифры ошибку не дают.
А конструкция A2&-7^7 гарантирует присутствие в исследуемом тексте шести цифр, отделённых от исходного текста знаком минус. Функция ПОИСКПОЗ ошибку не выдаст, но извлекаемые из строки символы будут за пределами исходной строки, если там нет своих шести цифр.

Автор - Светлый
Дата добавления - 12.05.2016 в 19:06
bedvit Дата: Пятница, 20.05.2016, 14:53 | Сообщение № 14
Группа: Пользователи
Ранг: Прохожий
Сообщений: 1
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010-2016х64
Кстати, на основе замечательной формулы от Светлый очень легко вывести формулу для выделения от 1 до 20 цифр, просто заменив "6" на нужное количество выделяемых цифр.
Код
=ПСТР(A2;ПОИСКПОЗ(6+1;ЧАСТОТА(СТРОКА($1:$999);ЕОШ(-ПСТР(A2&-(9^6);СТРОКА($1:$999);1))*СТРОКА($1:$999));)-6;6)


«Бритва Оккама» или «Принцип Калашникова»?

Сообщение отредактировал Serge_007 - Пятница, 20.05.2016, 22:20
 
Ответить
СообщениеКстати, на основе замечательной формулы от Светлый очень легко вывести формулу для выделения от 1 до 20 цифр, просто заменив "6" на нужное количество выделяемых цифр.
Код
=ПСТР(A2;ПОИСКПОЗ(6+1;ЧАСТОТА(СТРОКА($1:$999);ЕОШ(-ПСТР(A2&-(9^6);СТРОКА($1:$999);1))*СТРОКА($1:$999));)-6;6)

Автор - bedvit
Дата добавления - 20.05.2016 в 14:53
Мир MS Excel » Работа и общение » Мозговой штурм » Выделение шести цифр
Страница 1 из 11
Поиск:

Яндекс цитирования
© 2010-2016 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!