Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Формула выдающая последовательность только из чисел, соответствующих контрольному номеру
 
#1
15.01.2019 01:45:09
Добрый вечер. такая задача стоит, нужно путем растягивания формулы на весь лист excel получить последовательность из чисел, проходящих проверку контрольным номером.

Вот алгоритм вычисления контрольного числа
Берем число 12039119:
  Код           1    2     0    3     9    1    1    9
  Вес разрядов  1    2     3    4     5    6    7
Сумма произведений, полученных путём умножения каждой цифры кода на вес разряда, равна:

1 х 1 + 2 х 2 + 0 х 3 + 3 х 4 + 9 х 5 + 1 х 6 + 1 х 7 = 75.
75 = 11 * 6 + 9;

Остаток от деления полученной суммы на 11 равен 9. Контрольная цифра кода — 9 (последняя цифра). Контрольное число (остаток от деления) = 9 — совпадает с контрольной цифрой = 9, номер правильный.
И путем растягивания этой формулы в следующей ячейке должно появится первое последующие число, отвечающее критериям этой проверки, то есть 12039125.

Помогите пожалуйста.
 
 
 
#2
15.01.2019 02:29:40
исходное 12039119 в А1
в А2
Код
=A1+10+ОСТАТ(СУММПРОИЗВ(ЗНАЧЕН(ПСТР(A1+10;СТОЛБЕЦ($A:$G);1));СТОЛБЕЦ($A:$G));11)-ЗНАЧЕН(ПРАВСИМВ(A1;1))
получите 12039125
тянете А2 за правый нижний уголок....и дальше видите:
12039131
12039148
12039154
12039160
12039177
12039183
12039200
12039214
12039220
...
Изменено: Ігор Гончаренко - 15.01.2019 02:40:56
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#3
15.01.2019 02:55:35
Огромное человеческое спасибо!!!!
 
 
 
#4
15.01.2019 02:59:33
Цитата
Ігор Гончаренко написал:
Изменено: Ігор Гончаренко  - 15 Янв 2019 02:40:56
Игорь, Вы что-то поменяли в формуле?
 
 
 
#5
15.01.2019 03:13:32
в формуле ничего не менял. просто смиренно поднял глаза к образам и чуть правее Николая Чудотворца мне явилась формула
Изменено: Ігор Гончаренко - 15.01.2019 03:22:22
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#6
15.01.2019 03:25:28
Игорь, если растягивать эту формулу на длинные расстояния, она дает сбои. И чем длиннее расстояние, тем больше "неправильных" номеров в промежутках между "правильными". Может такое быть? или я что-то не так делаю?
 
 
 
#7
15.01.2019 03:32:31
Вы делаете все правильно. формула кривовата
есть там один скользкий момент - остаток от деления на 11 может быть 10 и в этом случае возможны траблы...
в какой строке 1-я ошибка?
Изменено: Ігор Гончаренко - 15.01.2019 03:36:20
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#8
15.01.2019 03:42:53
вообще у меня задача дойти от 10000000 до 15000000
При этом раскладе первая ошибка уже в 6ой строке
10000018
10000024
10000030
10000047
10000053
10000070
 
 
 
#9
15.01.2019 04:07:13
Без файла-примера помогать нет желания.
 
 
 
#10
15.01.2019 04:14:27
концепция меняется в А2 это:
Код
=ЕСЛИ(ОСТАТ(СУММПРОИЗВ(ЗНАЧЕН(ПСТР(A1+10;СТОЛБЕЦ($A:$G);1));СТОЛБЕЦ($A:$G));11)=10; ЦЕЛОЕ(A1/10)*10+20+ОСТАТ(СУММПРОИЗВ(ЗНАЧЕН(ПСТР(A1+20;СТОЛБЕЦ($A:$G);1));СТОЛБЕЦ($A:$G));11);A1+10+ОСТАТ(СУММПРОИЗВ(ЗНАЧЕН(ПСТР(A1+10;СТОЛБЕЦ($A:$G);1));СТОЛБЕЦ($A:$G));11)-ЗНАЧЕН(ПРАВСИМВ(A1;1))*ЕСЛИ(ЗНАЧЕН(ПРАВСИМВ(A1;1))=3;-1;1))
а потом тянем А2 за правый нижний уголок до упора
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#11
15.01.2019 04:51:35
vikttur,
тут не нужен файл пример, нужно одно "правильное" 8-значное число
первое "правильное" 8-значное число:
10000001 - правильное число
1234567   - тут приняты такие порядковые номера разрядов в 8-м разряде - контрольная цифра.
правильные в даной задаче это числа, у которых остаток от деления сумма цифр умноженных на порядковый номер разряда на 11 равен последей цифре в числе
почему 10000001 правильное? потому что 1*1+ 0*2+0*3 .. +0*7 = 1, 1 деленное на 11 в остатке дает 1 (последнюю цифру числа)
задача: написать формулу,. которая вычислит след. ближайшее "правильное" число больше начального
след. ближайшее правильное число за 10000001 это число 10000018 (сумма цифр * на разряды = 1*1+0*2+0*3... +1*7 = 8, 8 деленное на 11 в остатке = 8

задача начиналась с числа 12039119
оно тоже правильное 1*1+2*2+0*3+3*4+9*5+1*6+1*7 = 1+4+0+12+45+6+7 = 75, остаток от 75 разделить на 11 = 9, равен последней цифре числа
ближайшее правильное за 12039119 - это число 12039125
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#12
15.01.2019 08:04:24
=999999+ROW()&RIGHT(MOD(SUM(MID(999999+ROW();{1;2;3;4;5;6;7};1)*{1;2;3;4;5;6;7});11);1)

Только тема
Цитата
Формула выдающая последовательность только из чисел, соответствующих контрольному номеру
не соответствует вопросу. Вот если б в результате нужна была последовательность которая имеет конкретное контрольное число, то другое дело.
По вопросам из тем форума, личку не читаю.
 
 
 
#13
15.01.2019 08:38:13
Вариант:
Код
=ОТБР(A1+10;-1)+ОСТАТ(ОСТАТ(МУМНОЖ(--ПСТР(A1+10;{1;2;3;4;5;6;7};1);{1:2:3:4:5:6:7});11);10)
 
 
 
#14
15.01.2019 09:26:48
Цитата
Ігор Гончаренко написал: тут не нужен файл пример,
С примером проще вникнуть в суть.
 
 
 
#15
15.01.2019 10:41:28
Ігор Гончаренко, спасибо за формулу.
Похоже что теперь все работает! Большое спасибо за вникание в суть вопроса!

MCH, Ваша формула у меня почему-то выдает ошибку
Цитата
Error
Argument must be a range.

БМВ, по Вашей формуле у меня получается вот такая последовательность:
10000011
10000021
10000031
10000041
10000051
10000061
https://docs.google.com/spreadsheets/d/1F4Px5Y4u6peJKNhYlMe0nqZVYCWTbY1DahdemuDQ­z28/edit?usp=sharing
Изменено: pavelk - 15.01.2019 11:13:11
 
 
 
#16
15.01.2019 10:46:12
pavelk, посмотрите на исправленное сообщение  и в следующий раз не создавайте очереди, да еще и с бессмысленным цитированием

О файле я Вам уже писал, а Вы партизаните, не показываете. Ответ для МСН и БМВ нужно иллюстрировать примером  Так проще понять ошибку и дать совет.
Надумаете прикрепить файл - вот туда и прикрепите, к сообщению №15
 
 
 
#17
15.01.2019 11:02:24
Свел решения в один файл, на всякий случай сделал второй вариант своей формулы (через СУММ, без МУМНОЖ), где ошибка?
не увидел ответа что делать с остатком деления на 11, когда остается число 10, оставляем "0" или пропускаем такое число?
 
 
 
#18
15.01.2019 11:04:42
я пропускал числа с остатком 10 (10 - не помещается для записи в 1 разряд)
Изменено: Ігор Гончаренко - 15.01.2019 11:06:30
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#19
15.01.2019 11:59:37
Ігор Гончаренко, все-таки формула дает сбой. Я пошел от числа 12038835 по этой формуле и сбился на 8ой ячейке

В колонке А растянуто по формуле, в колонке H - руками подбирал последовательность.
https://docs.google.com/spreadsheets/d/1vyLEZuIsea-QQS8cwBrUhn97TNZKQeiUG470fohaOb4/edit?usp=sharing

У меня на 36 руками подобранных значений последовательности получается одно неправильное значение(8я ячейка) и два пропущенных значения.
 
 
 
#20
15.01.2019 12:07:20
pavelk,
1. Как правильно отметил Игорь остаток от деления на 11 может быть 10.  Нужно четко описать правило формирования контрольной цифры. Цифры а не числа. 10  - это число, и что с ним делать - не описано.
2. #17 в котором все формулы, включая мою , рабочие, разве что Игорь внес свое видение основанное на недосказанности по п.1
По вопросам из тем форума, личку не читаю.
 
 
 
#21
15.01.2019 12:29:29
БМВ, Видно Мак Ос делает свое дело. я это вижу на компе вот так:
https://drive.google.com/open?id=1vaUyTqjxJHu-xhdxjb27XoVitbS2N25g
А в Гугле вот так:
https://drive.google.com/open?id=1eaNsuYGm_3-iWQ3UQGAbS-hFwwziVAXb

А алгоритм сложения вроде довольно корректно описал в топике. В каждом последующем значении проверяется сумма умножений первых семи цифр на порядковый номер разряда, дальше делится на 11 и остаток от этого деления сравнивается с восьмой цифрой, если они совпадают - значит значение верное и выводится, если не совпадают - проверяется следующее.

Вы уж извините, когда человек не программист, ему нужно "ручками" дойти до такой ситуации и уткнуться в нее носом :)

Вот я дошел до 10000120 и понял о чем вы... вот он алгоритм:
Если получается остаток, равный 10, то для обеспечения одноразрядного контрольного числа необходимо провести повторный расчет, применяя вторую последовательность весов, сдвинутую на два разряда влево (3, 4, 5,…). Если в случае повторного расчета остаток от деления вновь сохраняется равным 10, то значение контрольного числа проставляется равным «0».

Простите меня великодушно.
Изменено: pavelk - 15.01.2019 13:31:02
 
 
 
#22
15.01.2019 13:29:29
=999999+ROW(D1)&IFERROR(1/(1/(MOD(SUM(MID(999999+ROW(D1);{1;2;3;4;5;6;7};1)*{1;2;3;4;5;6;7});11)-10))+10;RIGHT(MOD(SUM(MID(999999+ROW(D1);{1;2;3;4;5;6;7};1)*{3;4;5;6;7;8;9});11);1))

и в гугле работает
По вопросам из тем форума, личку не читаю.
 
 
 
#23
15.01.2019 20:52:02
Код
=(МИН(ЕСЛИ(ОСТАТ(МУМНОЖ(--ПСТР(ЦЕЛОЕ(R[-1]C/10)+СТРОКА(R1:R3);СТОЛБЕЦ(C[-4]:C[2]);1);ТРАНСП(СТОЛБЕЦ(C[-4]:C[2])));11)<>10;СТРОКА(R1:R3)))+ЦЕЛОЕ(R[-1]C/10))*10+ОСТАТ(МУМНОЖ(--ПСТР(ЦЕЛОЕ(R[-1]C/10)+МИН(ЕСЛИ(ОСТАТ(МУМНОЖ(--ПСТР(ЦЕЛОЕ(R[-1]C/10)+СТРОКА(R1:R3);СТОЛБЕЦ(C[-4]:C[2]);1);ТРАНСП(СТОЛБЕЦ(C[-4]:C[2])));11)<>10;СТРОКА(R1:R3)));СТОЛБЕЦ(C[-4]:C[2]);1);ТРАНСП(СТОЛБЕЦ(C[-4]:C[2])));11)
в файле подсвечены значения, где цифра в последнем разряде не равна остатку от деления суммы цифр, умноженных на величины разрядов,  
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
Страницы: 1
Наверх