Страницы: 1
RSS
Выборка для формулы конкретных цифр из числа, находящего в одной ячейке
 
Добрый день уважаемые форумчане. Сразу отмечу, что в Excel я нуб.
Требуется создать формулу для для автоматического подсчёта контрольного знака, перерыл весь интернет и нахожу только уже готовые формулы для подсчёта контрольного знака штрихкодов, ИНН и прочего, но конкретного алгоритма арифметических действий с конкретными цифрами из числа, находящего в одной ячейке найти не могу.
Суть задачи: автоматизировать проверку контрольного знака вагона с выводом в соседнюю ячейку сообщения типа Истина/Ложь (или раскрашивания ячейки в зелёный/красный цвет).
В ячейке A1 написан восьмизначный код вагона, к примеру 74854688. В ячейку A2 необходимо вывести значение Истина/Ложь при выполнении следующего алгоритма (копипаста):
Контрольная цифра - это число, дополняющее под разрядную сумму до ближайшего целого десятка. Если, под разрядная сумма кратна 10, то контрольный знак равен 0. В примере контрольным знаком будет 8.
Номер вагона    7    4    8    5    4    6    8
Множитель.        2    1    2    1    2    1    2
Произведение  14    4    16    5    8    6    16
Сумма    1 + 4 + 4 + 1 + 6 + 5 + 8 + 6 + 1 + 6 = 42
то есть из восьмизначного числа, находящегося в одной ячейке, нужно с первой по седьмую цифру поочерёдно умножить 2 и 1, а затем из полученных чисел сложить цифры и разницу между получившемся числом и ближайшим десятком сравнить с восьмым знаком.
Конечно можно использовать множество ячеек для нескольких формул, которые я скрою уменьшив их размер до одного пикселя. Арифметика не хитрая, но мне в принципе не понятен сам процесс выбора для формулы и для сравнения конкретной цифры из числа.
Заранее спасибо.  
 
Цитата
FaTaL Плиско написал:
Сразу отмечу, что в Excel я нуб.
У Вас даже файла нет? ))
 
74854468 и а расчет на базе  7    4    8    5    4    6    8
По вопросам из тем форума, личку не читаю.
 
Цитата
Юрий М написал:
У Вас даже файла нет? ))
Я работаю дежурным по станции, вагоны, как понимаете, прибывают и убывают. Номерной учёт вагона ведём в "на коленке" состряпанной табличке Excel, так как наша система учёта вагонов в купе с бюрократичностью и виртуальными перемещениями вагонов не позволяет вести достоверный учёт. Я конечно могу ее прикрепить, но что это даст? Ну минут через 15 отправлю таблицу, как мы наличие  вагонов ведём.
Цитата
БМВ написал:
74854468 и а расчет на базе  7    4    8    5    4    6    8
"расчет на базе" - правильный номер, это я опечатался, когда указывал этот вагон для примера. Сейчас исправлю.
 
=RIGHT(MOD(SUM(--MID(SUM(MID(A1;{7;6;5;4;3;2;1};1)*{2;1;2;1;2;1;2}*100^{0;1;2;3;4;5;6});{1\2\3\4\5\6\7\8\9\10\11\12\13\14};1));10)-10)=RIGHT(A1)

=RIGHT(RIGHT(SUM(--MID(SUM(MID(A1;{7;6;5;4;3;2;1};1)*{2;1;2;1;2;1;2}*100^{0;1;2;3;4;5;6});{1\2\3\4\5\6\7\8\9\10\11\12\13\14};1)))-10)=RIGHT(A1)
Изменено: БМВ - 11.08.2020 19:00:48
По вопросам из тем форума, личку не читаю.
 
Прикреплен файл "Парк"
На первом листе то, как мы в текущий момент ведём номерной учёт наличия вагонов. На втором листе в столбцы C, G, K, O хотелось бы вставить контроль восьмого контрольного знака с выводом значения Истина/Ложь (или цветовые обозначения) с контролем правильности указанного номера в столбцах B, F, J, N.
 
Цитата
БМВ написал:
=RIGHT(MOD(SUM(--MID(SUM(MID(A1;{7;6;5;4;3;2;1};1)*{2;1;2;1;2;1;2}*100^{0;1;2;3;4;5;6});{1\2\3\4\5\6\7\8\9\10\11\12\13\14};1));10)-10)=RIGHT(A1)=RIGHT(RIGHT(SUM(--MID(SUM(MID(A1;{7;6;5;4;3;2;1};1)*{2;1;2;1;2;1;2}*100^{0;1;2;3;4;5;6});{1\2\3\4\5\6\7\8\9\10\11\12\13\14};1)))-10)=RIGHT(A1)

Что-то не берутся формулы, "Ошибка в формуле".
Может гляните в моем файле "Парк" на второй страничке? В столбец C проставить формулу к любому номеру вагона из столбца B, чтобы формула указала, верен ли контрольный знак этого вагона.
Или ткните носом в гайд, как проводить арифметические операции с конкретными цифрами из числа из одной ячейки. Может я как-то не правильно у Гугла спрашиваю, но на все мои запросы он выдает статейки о банальных операциях с ячейками. Как провести арифметические расчеты с конкретными ячейками я знаю, но это тогда надо и номера вагонов по цифре в каждую ячейку записывать, это будет жутко не удобно.
 
 Можно такой формулой (для B3):
=ОСТАТ(СУММПРОИЗВ(--ПСТР(B3;{1;2;3;4;5;6;7;8};1);{2;1;2;1;2;1;2;1})-9*СУММПРОИЗВ(--(ПСТР(B3;{1;3;5;7};1)>="5"));10)=0
Изменено: sokol92 - 11.08.2020 20:15:05
Владимир
 
Чуть скорректировал
=--(RIGHT(RIGHT(SUMPRODUCT(IFERROR(-MID(SUM(MID(B3;{7;6;5;4;3;2;1};1)*{2;1;2;1;2;1;2}*100^{0;1;2;3;4;5;6});{1\2\3\4\5\6\7\8\9\10\11\12\13\14};1);)))-10)=RIGHT(B3))
или
=--(RIGHT(RIGHT(SUM(-MID(SUM(MID(B3;{7;6;5;4;3;2;1};1)*{2;1;2;1;2;1;2}*100^{0;1;2;3;4;5;6})&"00000000";{1\2\3\4\5\6\7\8\9\10\11\12\13\14};1)))-10)=RIGHT(B3))
или
=--(RIGHT(RIGHT(SUM(-MID(SUM(MID(B3;{7;6;5;4;3;2;1};1)*{2;1;2;1;2;1;2}*100^{0;1;2;3;4;5;6})&REPT(0;8);{1\2\3\4\5\6\7\8\9\10\11\12\13\14};1)))-10)=RIGHT(B3))
или
=--(RIGHT(RIGHT(SUMPRODUCT(-MID(SUM(MID(B3;{7;6;5;4;3;2;1};1)*{2;1;2;1;2;1;2}/1%^{0;1;2;3;4;5;6})&REPT(0;8);COLUMN(A:N);1)))-10)=RIGHT(B3))

sokol92,  Владимир, метод иной, но результат тот же :-)
Изменено: БМВ - 11.08.2020 20:27:46
По вопросам из тем форума, личку не читаю.
 

=ПРАВСИМВ(ТЕКСТ(ОСТАТ(СУММ(ЕСЛИ(--ПСТР(B3;СТРОКА($A$1:$A$7);1)*{2:1:2:1:2:1:2}>9;--ПСТР(B3;СТРОКА($A$1:$A$7);1)*{2:1:2:1:2:1:2}-9;--ПСТР(B3;СТРОКА($A$1:$A$7);1)*{2:1:2:1:2:1:2}));10)-10;";#");1)=ПРАВСИМВ(B3;1)

ну так, не додумал еще
F1 творит чудеса
 
Можно, конечно, написать UDF, но ведь задача - это ж асса какая-то (чтобы не писать "бред полный") :)
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
Проверил - действительно такой алгоритм, как описан в #1. Воспоминания об избушке...
Здравствуйте, Михаил, арифметику не обманешь :)  
Изменено: sokol92 - 11.08.2020 20:24:16
Владимир
 
Блин, ребят, поражен вашей отзывчивостью.
Крайне благодарен за вашу помощь. У вас тут отличное сообщество. Я бы сам такую задачу не решил.
Будем тестировать, внедрять. Если зайдет, то и на соседние станции распространим решение. Это реально облегчит нашу работу. А то мы с бумаги в ручную вагоны забиваем себе в эту таблицу, порой ошибёшься одной цифрой по прибытию, а по отправлению давай себе голову ломать и по другим источникам искать правильный номер вагона, при отправлении обратно из таблички вагоны переносим в автоматизированные системы, а в ответ прилетают ошибки, что вагона с таким номером не существует.
 
Цитата
FaTaL Плиско написал:
а в ответ прилетают ошибки, что вагона с таким номером не существует.
Девятый вагон  :D
По вопросам из тем форума, личку не читаю.
 
Код
=ОСТАТ(СУММ(ПСТР(K3;СТРОКА(A$1:A$7);1)*{2:1:2:1:2:1:2}-ЕСЛИ((ПСТР(K3;СТРОКА(A$1:A$7);1)>"4")
*{1:0:1:0:1:0:1};9;))+ПРАВСИМВ(K3;1);10)=0
где К3 - ссылка на ячейку с № вагона
см. колонки В и С как это можно оргпнизовать
Изменено: Ігор Гончаренко - 12.08.2020 11:42:42
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Еще вариант:
Код
=ОСТАТ(B3+СУММ((-ПСТР(0&B3;{1:3:5:7}+{0;1};1)<0)*(ОСТАТ(ПСТР(0&B3;{1:3:5:7}+{0;1};1)*{1;2}-1;9)+1));10)=0

или даже так:
Код
=ОСТАТ(СУММ((-ПСТР(B3;{1:3:5:7}+{0;1};1)<0)*(ОСТАТ(ПСТР(B3;{1:3:5:7}+{0;1};1)*{2;1}-1;9)+1));10)=0
 
есть формула на 76 знаков:
Код
=ОСТАТ(СУММ(-ПСТР(B3;{1:3:5:7}+{0;0;1};1);-(--ПСТР(B3;{1:3:5:7};1)>4));10)=0
 
MCH, лишнее убрать
=MOD(SUM(-MID(B3;{1;3;5;7}+{0\0\1};1)-MID(B3;{1;3;5;7};1)>4);10)=0
По вопросам из тем форума, личку не читаю.
 
Цитата
MCH написал:
{1:3:5:7}+{0;0;1}
это очень красиво!
для такого озарения где-то рядом, буквально в двух шагах, должна была молния ударить!
Изменено: Ігор Гончаренко - 12.08.2020 17:29:04
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Да, красиво, напоминает тензоры. :)  
Владимир
 
Цитата
БМВ написал:
лишнее убрать
Нет, так не должно сработать,
А вот за счет ПРАВБ можно сократить на пару символов:
Код
=-ПРАВБ(СУММ(-ПСТР(B3;{1:3:5:7}+{0;0;1};1);-(ПСТР(B3;{1:3:5:7};1)-4>0)))=0

можно и так, но короче не стало:
Код
=-ПРАВБ(СУММ(ПСТР(B3;{1:3:5:7}+{0;0;1};1)+(ПСТР(B3;{1:3:5:7};1)-4>0)/3))=0
Страницы: 1
Наверх