Проверить набор цифр по определенным параметрам. - Мир MS Excel

Регистрация

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

= Мир MS Excel/Проверить набор цифр по определенным параметрам. - Мир MS Excel

Старая форма входа

Страница 1 из 2 1 2 »
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Проверить набор цифр по определенным параметрам. (Формулы/Formulas)

Проверить набор цифр по определенным параметрам.

Дата: Воскресенье, 02.12.2018, 16:37 | Сообщение № 1

Группа: Проверенные

Ранг: Ветеран

Сообщений: 738

Репутация: 3 ±

Замечаний: 0% ±

Excel 2010, 2013

Всем здравствуйте. Прошу помочь в решении сложной задачи. Есть набор цифр вида: 023-125-185-96, необходимо проверить их на несколько параметров:
1. на наличие менее или более 11 цифр
2. на наличие идущих подряд 3 или более одинаковых цифр
3. проверка контрольного числа (две последние цифры) NNN-NNN-NNN- KK

Проверка контрольного числа: для того, чтобы определить, является ли указанное контрольное число верным, необходимо каждый элемент N умножить на порядковый, номер исчисляемый в противоположном порядке (для первого элемента N порядковый номер будет 9, для второго 8 и т.д. Полученное значение складываем и сравниваем относительно цифры 100, если оно:
-меньше 100, то контрольное число равно самой сумме;
-равно 100 или 101, то контрольное число 00;
-если меньше или больше 300, вычитаем из него цифры 101.

Если формулой это сделать сложно, то можно макросом. Заранее спасибо, если получится это реализовать.

К сообщению приложен файл: _1.xlsx (7.9 Kb)

Сообщение отредактировал Mark1976 - Воскресенье, 02.12.2018, 17:24

Ответить

Дата: Воскресенье, 02.12.2018, 17:12 | Сообщение № 2

Группа: Друзья

Ранг: Экселист

Сообщений: 8759

Репутация: 2272 ±

Замечаний: 0% ±

Excel 2010

Код

=ЕСЛИ((ДЛСТР(A1)=14)*(ДЛСТР(ПОДСТАВИТЬ(A1;"-";))=11)*ЕОШ(ПОИСК("000";ПОДСТАВИТЬ(A1;"-";)))*ЕОШ(ПОИСК("111";ПОДСТАВИТЬ(A1;"-";)))*ЕОШ(ПОИСК("222";ПОДСТАВИТЬ(A1;"-";)))*ЕОШ(ПОИСК("333";ПОДСТАВИТЬ(A1;"-";)))*ЕОШ(ПОИСК("444";ПОДСТАВИТЬ(A1;"-";)))*ЕОШ(ПОИСК("555";ПОДСТАВИТЬ(A1;"-";)))*ЕОШ(ПОИСК("666";ПОДСТАВИТЬ(A1;"-";)))*ЕОШ(ПОИСК("777";ПОДСТАВИТЬ(A1;"-";)))*ЕОШ(ПОИСК("888";ПОДСТАВИТЬ(A1;"-";)))*ЕОШ(ПОИСК("999";ПОДСТАВИТЬ(A1;"-";)))*(ПРАВБ(0&ОКРУГЛ(ЕСЛИ((ПСТР(A1;1;1)*9+ПСТР(A1;2;1)*8+ПСТР(A1;3;1)*7+ПСТР(A1;5;1)*6+ПСТР(A1;6;1)*5+ПСТР(A1;7;1)*4+ПСТР(A1;9;1)*3+ПСТР(A1;10;1)*2+ПСТР(A1;11;1))<100;ПСТР(A1;1;1)*9+ПСТР(A1;2;1)*8+ПСТР(A1;3;1)*7+ПСТР(A1;5;1)*6+ПСТР(A1;6;1)*5+ПСТР(A1;7;1)*4+ПСТР(A1;9;1)*3+ПСТР(A1;10;1)*2+ПСТР(A1;11;1);ЕСЛИ((ПСТР(A1;1;1)*9+ПСТР(A1;2;1)*8+ПСТР(A1;3;1)*7+ПСТР(A1;5;1)*6+ПСТР(A1;6;1)*5+ПСТР(A1;7;1)*4+ПСТР(A1;9;1)*3+ПСТР(A1;10;1)*2+ПСТР(A1;11;1))<102;"00";(ПСТР(A1;1;1)*9+ПСТР(A1;2;1)*8+ПСТР(A1;3;1)*7+ПСТР(A1;5;1)*6+ПСТР(A1;6;1)*5+ПСТР(A1;7;1)*4+ПСТР(A1;9;1)*3+ПСТР(A1;10;1)*2+ПСТР(A1;11;1)-101)));0);2)=ПРАВБ(A1;2));"";"ошибка")

ЮMoney 41001841029809

Ответить

Дата: Воскресенье, 02.12.2018, 17:24 | Сообщение № 3

Группа: Друзья

Ранг: Старожил

Сообщений: 1829

Репутация: 510 ±

Замечаний: 0% ±

Excel 2013, 2016

Добрый вечер!

Код

=ЕСЛИ(И(ДЛСТР(A1)=14;ОСТАТ(ПСТР(ПОДСТАВИТЬ(A1;"-";ПОВТОР(" ";99));{0;1;2}*99+1;99)/111;1)<>0;ОСТАТ(СУММПРОИЗВ(ПСТР(ПОДСТАВИТЬ(A1;"-";);СТРОКА($1:$9);1)*(10-СТРОКА($1:$9)));101)=--ПРАВБ(A1;2);ЕЧИСЛО(-ПОДСТАВИТЬ(A1;"-";)));"ОК";"Ошибка")

Одинаковые цифры проверяет только в тройках.

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

Сообщение отредактировал Светлый - Воскресенье, 02.12.2018, 17:27

Ответить

Дата: Воскресенье, 02.12.2018, 17:26 | Сообщение № 4

Группа: Проверенные

Ранг: Ветеран

Сообщений: 738

Репутация: 3 ±

Замечаний: 0% ±

Excel 2010, 2013

Nic70y, спасибо. Этот монстр

Код

=ЕСЛИ((ДЛСТР(A1)=14)*(ДЛСТР(ПОДСТАВИТЬ(A1;"-";))=11)*ЕОШ(ПОИСК("000";ПОДСТАВИТЬ(A1;"-";)))*ЕОШ(ПОИСК("111";ПОДСТАВИТЬ(A1;"-";)))*ЕОШ(ПОИСК("222";ПОДСТАВИТЬ(A1;"-";)))*ЕОШ(ПОИСК("333";ПОДСТАВИТЬ(A1;"-";)))*ЕОШ(ПОИСК("444";ПОДСТАВИТЬ(A1;"-";)))*ЕОШ(ПОИСК("555";ПОДСТАВИТЬ(A1;"-";)))*ЕОШ(ПОИСК("666";ПОДСТАВИТЬ(A1;"-";)))*ЕОШ(ПОИСК("777";ПОДСТАВИТЬ(A1;"-";)))*ЕОШ(ПОИСК("888";ПОДСТАВИТЬ(A1;"-";)))*ЕОШ(ПОИСК("999";ПОДСТАВИТЬ(A1;"-";)))*(ПРАВБ(0&ОКРУГЛ(ЕСЛИ((ПСТР(A1;1;1)*9+ПСТР(A1;2;1)*8+ПСТР(A1;3;1)*7+ПСТР(A1;5;1)*6+ПСТР(A1;6;1)*5+ПСТР(A1;7;1)*4+ПСТР(A1;9;1)*3+ПСТР(A1;10;1)*2+ПСТР(A1;11;1))<100;ПСТР(A1;1;1)*9+ПСТР(A1;2;1)*8+ПСТР(A1;3;1)*7+ПСТР(A1;5;1)*6+ПСТР(A1;6;1)*5+ПСТР(A1;7;1)*4+ПСТР(A1;9;1)*3+ПСТР(A1;10;1)*2+ПСТР(A1;11;1);ЕСЛИ((ПСТР(A1;1;1)*9+ПСТР(A1;2;1)*8+ПСТР(A1;3;1)*7+ПСТР(A1;5;1)*6+ПСТР(A1;6;1)*5+ПСТР(A1;7;1)*4+ПСТР(A1;9;1)*3+ПСТР(A1;10;1)*2+ПСТР(A1;11;1))<102;"00";(ПСТР(A1;1;1)*9+ПСТР(A1;2;1)*8+ПСТР(A1;3;1)*7+ПСТР(A1;5;1)*6+ПСТР(A1;6;1)*5+ПСТР(A1;7;1)*4+ПСТР(A1;9;1)*3+ПСТР(A1;10;1)*2+ПСТР(A1;11;1)-101)));0);2)=ПРАВБ(A1;2));"";"ошибка")

работает. На большом кол-ве строк сработает?

Ответить

Дата: Воскресенье, 02.12.2018, 17:30 | Сообщение № 5

Группа: Проверенные

Ранг: Ветеран

Сообщений: 738

Репутация: 3 ±

Замечаний: 0% ±

Excel 2010, 2013

Светлый, спасибо.

Ответить

Дата: Воскресенье, 02.12.2018, 17:36 | Сообщение № 6

Группа: Друзья

Ранг: Старожил

Сообщений: 1829

Репутация: 510 ±

Замечаний: 0% ±

Excel 2013, 2016

Делает все проверки:

Код

=ЕСЛИ(И(ДЛСТР(A1)=14;ОСТАТ(ПСТР(ПОДСТАВИТЬ(A1;"-";);СТРОКА($1:$7);3)/111;1)<>0;ОСТАТ(СУММПРОИЗВ(ПСТР(ПОДСТАВИТЬ(A1;"-";);СТРОКА($1:$9);1)*(10-СТРОКА($1:$9)));101)=--ПРАВБ(A1;2);ЕЧИСЛО(-ПОДСТАВИТЬ(A1;"-";)));"ОК";"Ошибка")

*Даже если между одинаковыми цифрами стоит "-".

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

Сообщение отредактировал Светлый - Воскресенье, 02.12.2018, 17:37

Ответить

Дата: Воскресенье, 02.12.2018, 18:34 | Сообщение № 7

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16674

Репутация: 6479 ±

Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS

Цитата Светлый, 02.12.2018 в 17:36, в сообщении № 6 ()

Делает все проверки:

121-282-121-00 - результат 100, не ошибка
121-223-418-88 - 3 одинаковые цифры подряд (в условии не указано, что это не последние цифры), легко исправляется заменой 7 на 9
075-19О-129-57 - даст не "ошибка", а #ЗНАЧ

Непонятно что делать, если сумма перемножений (ААА) больше 200. тогда ААА-101 - трезначное число, его нельзя сравнивать с двузначным КК. Допустим, что нужно взять две правые цифры
Мой вариант (проверку на повторы взял у Светлого (с добавкой 1-9 вместо 1-7), у меня было длиннее)

Код

=ЕСЛИОШИБКА(1/НЕ(ТЕКСТ(ПРАВБ(СУММПРОИЗВ(ПСТР(ПОДСТАВИТЬ(A1;"-";);СТОЛБЕЦ(A:I);1)*(10-СТОЛБЕЦ(A:I)));2);"[<2]1;0")-1-ПРАВБ(A1;2))/И(ОСТАТ(ПСТР(ПОДСТАВИТЬ(A1;"-";);СТОЛБЕЦ(A:I);3)/111;1)<>0)/(ДЛСТР(A1)=14);"ошибка")

Формула массива. Вводится одновременным нажатием Контрл Шифт Ентер

К сообщению приложен файл: _16689_1.xlsx (11.3 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

Дата: Воскресенье, 02.12.2018, 20:56 | Сообщение № 8

Группа: Друзья

Ранг: Старожил

Сообщений: 1829

Репутация: 510 ±

Замечаний: 0% ±

Excel 2013, 2016

Цитата _Boroda_, 02.12.2018 в 18:34, в сообщении № 7 ()

075-19О-129-57 - даст не "ошибка", а #ЗНАЧ

потому что там буква О, а не цифра 0.
_Boroda_, у Вас, как и у меня немного неправильно считается контрольная сумма.

Цитата Mark1976, 02.12.2018 в 16:37, в сообщении № 1 ()

-если меньше или больше 300, вычитаем из него цифры 101

Надо вычислять остаток от деления на 101, а потом две последние цифры или остаток от деления на 100, тогда 100 даст 00. Надо просто больше примеров для проверки.

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

Ответить

Дата: Воскресенье, 02.12.2018, 21:27 | Сообщение № 9

Группа: Проверенные

Ранг: Ветеран

Сообщений: 738

Репутация: 3 ±

Замечаний: 0% ±

Excel 2010, 2013

_Boroda_, а как заменить 1 на ок в формуле? или вообще сделать строку пустую?

Ответить

Дата: Воскресенье, 02.12.2018, 21:27 | Сообщение № 10

Группа: Проверенные

Ранг: Ветеран

Сообщений: 738

Репутация: 3 ±

Замечаний: 0% ±

Excel 2010, 2013

Всем хочу сказать большое спасибо за помощь.

Ответить

Дата: Воскресенье, 02.12.2018, 21:29 | Сообщение № 11

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16674

Репутация: 6479 ±

Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS

Цитата Светлый, 02.12.2018 в 20:56, в сообщении № 8 ()

потому что там буква О, а не цифра 0.

Я знаю. Я специально туда ее написал. Возможно, что зря, ведь в условии именно про цифры речь. Это типа антиблондинковая проверка

Цитата Светлый, 02.12.2018 в 20:56, в сообщении № 8 ()

немного неправильно считается контрольная сумма

А конкретнее?

Цитата Светлый, 02.12.2018 в 20:56, в сообщении № 8 ()

Надо вычислять остаток от деления на 101 ... или на 100

Не знаю, я вообще не по остаткам считаю. Единственно что, у меня наверно неверно для контрольной суммы 201. А может, и верно, в ТЗ не прописано что делать после отнимания 101 -

Цитата Mark1976, 02.12.2018 в 16:37, в сообщении № 1 ()

равно 100 или 101, то контрольное число 00

или

Цитата Mark1976, 02.12.2018 в 16:37, в сообщении № 1 ()

контрольное число равно самой сумме

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

Дата: Воскресенье, 02.12.2018, 21:30 | Сообщение № 12

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16674

Репутация: 6479 ±

Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS

Цитата Mark1976, 02.12.2018 в 21:27, в сообщении № 9 ()

а как заменить 1 на ок в формуле? или вообще сделать строку пустую?

Форматом ячейки
"ОК";;;@
;;;@

Цитата Mark1976, 02.12.2018 в 21:27, в сообщении № 10 ()

Всем хочу сказать большое спасибо за помощь.

Хотите - говорите, не нужно себя сдерживать

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

Дата: Воскресенье, 02.12.2018, 21:51 | Сообщение № 13

Группа: Проверенные

Ранг: Ветеран

Сообщений: 738

Репутация: 3 ±

Замечаний: 0% ±

Excel 2010, 2013

Я дико извиняюсь, но я не учел один момент.
Вот цифры 148-874-726-25, ошибки быть не должно. Формулы:

Код

=ЕСЛИОШИБКА(1/НЕ(ТЕКСТ(ПРАВБ(СУММПРОИЗВ(ПСТР(ПОДСТАВИТЬ(A25;"-";);СТОЛБЕЦ(A:I);1)*(10-СТОЛБЕЦ(A:I)));2);"[<2]1;0")-1-ПРАВБ(A25;2))/И(ОСТАТ(ПСТР(ПОДСТАВИТЬ(A25;"-";);СТОЛБЕЦ(A:I);3)/111;1)<>0)/(ДЛСТР(A25)=14);"ошибка")

и

Код

=ЕСЛИ((ДЛСТР(A13)=14)*(ДЛСТР(ПОДСТАВИТЬ(A13;"-";))=11)*ЕОШ(ПОИСК("000";ПОДСТАВИТЬ(A13;"-";)))*ЕОШ(ПОИСК("111";ПОДСТАВИТЬ(A13;"-";)))*ЕОШ(ПОИСК("222";ПОДСТАВИТЬ(A13;"-";)))*ЕОШ(ПОИСК("333";ПОДСТАВИТЬ(A13;"-";)))*ЕОШ(ПОИСК("444";ПОДСТАВИТЬ(A13;"-";)))*ЕОШ(ПОИСК("555";ПОДСТАВИТЬ(A13;"-";)))*ЕОШ(ПОИСК("666";ПОДСТАВИТЬ(A13;"-";)))*ЕОШ(ПОИСК("777";ПОДСТАВИТЬ(A13;"-";)))*ЕОШ(ПОИСК("888";ПОДСТАВИТЬ(A13;"-";)))*ЕОШ(ПОИСК("999";ПОДСТАВИТЬ(A13;"-";)))*(ПРАВБ(0&ОКРУГЛ(ЕСЛИ((ПСТР(A13;1;1)*9+ПСТР(A13;2;1)*8+ПСТР(A13;3;1)*7+ПСТР(A13;5;1)*6+ПСТР(A13;6;1)*5+ПСТР(A13;7;1)*4+ПСТР(A13;9;1)*3+ПСТР(A13;10;1)*2+ПСТР(A13;11;1))<100;ПСТР(A13;1;1)*9+ПСТР(A13;2;1)*8+ПСТР(A13;3;1)*7+ПСТР(A13;5;1)*6+ПСТР(A13;6;1)*5+ПСТР(A13;7;1)*4+ПСТР(A13;9;1)*3+ПСТР(A13;10;1)*2+ПСТР(A13;11;1);ЕСЛИ((ПСТР(A13;1;1)*9+ПСТР(A13;2;1)*8+ПСТР(A13;3;1)*7+ПСТР(A13;5;1)*6+ПСТР(A13;6;1)*5+ПСТР(A13;7;1)*4+ПСТР(A13;9;1)*3+ПСТР(A13;10;1)*2+ПСТР(A13;11;1))<102;"00";(ПСТР(A13;1;1)*9+ПСТР(A13;2;1)*8+ПСТР(A13;3;1)*7+ПСТР(A13;5;1)*6+ПСТР(A13;6;1)*5+ПСТР(A13;7;1)*4+ПСТР(A13;9;1)*3+ПСТР(A13;10;1)*2+ПСТР(A13;11;1)-101)));0);2)=ПРАВБ(A13;2));"";"ошибка")

выдают ошибку. А на самом деле там контрольная цифра верная. Скажу почему.
1* 9= 9
4* 8= 32
8* 7= 56
8* 6= 48
7* 5= 35
4* 4= 16
7* 3= 21
2* 2= 4
6* 1= 6
227 101 126 25
Сумма получилась 227, вычитаем 101 получилось 126, А ТАК КАК БОЛЬШЕ 100, отнимаем от 126, цифру 101. Т.е. отнимает 101 до тех пор пока полученная цифра не будет меньше 100.

227-101=126-101=25

Сообщение отредактировал Mark1976 - Воскресенье, 02.12.2018, 21:56

Ответить

Дата: Воскресенье, 02.12.2018, 22:08 | Сообщение № 14

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4106

Репутация: 768 ±

Замечаний: 0% ±

Excel 2013/2016

Осмелюсь предложить

Код

=IFERROR(LEN((MOD(SUM(MID(SUBSTITUTE(A1;"-";"");ROW($1:$9);1)*(10-ROW($1:$9)));101)=--RIGHT(A1;2))*(SUM(--ISNUMBER(FIND(RIGHT("000"&(ROW($1:$10)-1)*111;3);SUBSTITUTE(A1;"-";""))))=0)*(1&SUBSTITUTE(A1;"-";"")))=12;1=0)

Код

=IFERROR(LEN((MOD(SUM(MID(SUBSTITUTE(A5;"-";"");ROW($1:$9);1)*(10-ROW($1:$9)));101)=--RIGHT(A5;2))*(SUM(--ISERR(FIND(RIGHT("000"&(ROW($1:$10)-1)*111;3);SUBSTITUTE(A5;"-";""))))>0)*(1&SUBSTITUTE(A5;"-";"")))=12;1=0)

~~Замечательный~~ Временно просто медведь ~~, процентов на 20~~.

Сообщение отредактировал bmv98rus - Воскресенье, 02.12.2018, 23:23

Ответить

Дата: Воскресенье, 02.12.2018, 22:24 | Сообщение № 15

Группа: Проверенные

Ранг: Ветеран

Сообщений: 738

Репутация: 3 ±

Замечаний: 0% ±

Excel 2010, 2013

bmv98rus, спасибо.

Сообщение отредактировал Mark1976 - Воскресенье, 02.12.2018, 22:33

Ответить

Дата: Воскресенье, 02.12.2018, 22:27 | Сообщение № 16

Группа: Проверенные

Ранг: Ветеран

Сообщений: 738

Репутация: 3 ±

Замечаний: 0% ±

Excel 2010, 2013

bmv98rus, вторая формула считает не верно.

148-874-726-25 ИСТИНА ЛОЖЬ

Сообщение отредактировал Mark1976 - Воскресенье, 02.12.2018, 22:28

Ответить

Дата: Воскресенье, 02.12.2018, 22:39 | Сообщение № 17

Группа: Проверенные

Ранг: Ветеран

Сообщений: 738

Репутация: 3 ±

Замечаний: 0% ±

Excel 2010, 2013

bmv98rus, 118-129-478-58 первая формула выдает ЛОЖЬ. на самом деле набор цифр без ошибок.
1* 9= 9
1* 8= 8
8* 7= 56
1* 6= 6
2* 5= 10
9* 4= 36
4* 3= 12
7* 2= 14
8* 1= 8
сумма 159- 101= 58

Сообщение отредактировал Mark1976 - Воскресенье, 02.12.2018, 22:40

Ответить

Дата: Воскресенье, 02.12.2018, 22:46 | Сообщение № 18

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4106

Репутация: 768 ±

Замечаний: 0% ±

Excel 2013/2016

Mark1976, Это массивные формулы вводятся Ctrl+Shift+Enter Выше исправил опечатку и вложил пример.

~~Замечательный~~ Временно просто медведь ~~, процентов на 20~~.

Сообщение отредактировал bmv98rus - Воскресенье, 02.12.2018, 22:53

Ответить

Дата: Воскресенье, 02.12.2018, 22:53 | Сообщение № 19

Группа: Проверенные

Ранг: Ветеран

Сообщений: 738

Репутация: 3 ±

Замечаний: 0% ±

Excel 2010, 2013

bmv98rus, понял.

Сообщение отредактировал Mark1976 - Воскресенье, 02.12.2018, 22:54

Ответить

Дата: Воскресенье, 02.12.2018, 23:02 | Сообщение № 20

Группа: Проверенные

Ранг: Ветеран

Сообщений: 738

Репутация: 3 ±

Замечаний: 0% ±

Excel 2010, 2013

bmv98rus, если сумма равна 100 или 101, то контрольная сумма = 00, а это ИСТИНА.

009-897-510-00

Еще раз извиняюсь за внесение изменение в пример. Прошу сильно не пинать.

Ответить

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Проверить набор цифр по определенным параметрам. (Формулы/Formulas)

Страница 1 из 2
1
2
»

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