Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Нужно добавить алгоритм проверки в Excel
 
#1
25.12.2012 11:23:31
Добрый день пишу сюда надеясь на помощь. Дело в том, что в организации где работаю есть необходимость проверки тысячи ИНН в очень короткий срок. Знаю что можно написать алгоритм (он существует) а Excel, который бы это делал быстро. У нас в этом никто ничего не понимает.

Здесь расположен алгоритм проверки ИНН 10 знаков:

1. Вычисляется контрольная сумма со следующими весовыми коэффициентами: (2,4,10,3,5,9,4,6,8,0)
2. Вычисляется контрольное число как остаток от деления контрольной суммы на 11
3. Если контрольное число больше 9, то контрольное число вычисляется как остаток от деления контрольного числа на 10
4. Контрольное число проверяется с десятым знаком ИНН. В случае их равенства ИНН считается правильным.

Помогите пожалуйста!!!
 
 
 
#2
25.12.2012 11:39:41
в каком виде существует ваш список ИНН?
приложите небольшой пример экселевскго (лучше в формате xls) или текстового файла.
Изменено: ikki - 25.12.2012 11:40:04
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#3
25.12.2012 11:47:48
Добрый день.
Контрольную сумму Вы уже можете вычислять?
Если контрольная сумма есть, то это можно исполнить даже двумя способами:
1. сделать макрос и в цикле перебрать все значения и с соседних ячейках выводить "правильно" или "неправильно"
2. с помощью стандартных функций. На мой взгляд достаточно трех: ЕСЛИ, ПРАВСИМВ и третью не помню - это преобразование символа в число.
 
 
 
#4
25.12.2012 11:52:03
Прилагаю небольшой фрагмент, все столбцы заполнены, но нас интересует ИНН
 
 
 
#5
25.12.2012 11:56:41
пока так.
в яч. A1 - ИНН
в яч. B1 формула
Код
=ОСТАТ(ОСТАТ(СУММПРОИЗВ(--ПСТР(A1;СТРОКА(A$1:A$9);1)*{2:4:10:3:5:9:4:6:8});11);10)=--ПРАВСИМВ(A1;1)
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#6
25.12.2012 11:56:52
Я понял примерно так для 10 знаков:

A1: значение ИНН
B1: сумма (массивная): =СУММ(ПСТР(A1;СТРОКА($1:$10);1)*{2:4:10:3:5:9:4:6:8:0})
C1: проверка (истина): =ЕСЛИ(ОСТАТ(B1;11)>9;ОСТАТ(B1;10);ОСТАТ(B1;11))=--ПСТР(A1;10;1)
 
 
 
#7
25.12.2012 12:01:40
Gustav,
если здесь
Цитата
Евгений Тарасов пишет:
3. Если контрольное число больше 9, то контрольное число вычисляется как остаток от деления контрольного числа на 10
нет опечаток, то моё понимание правильнее Вашего  :D
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#8
25.12.2012 12:03:35
Если по вашему алгоритму использовать формулу
Код
=ЕСЛИ(ЕСЛИ(СУММПРОИЗВ((ПСТР(A1;СТРОКА(1:10);1)*1)*{2:4:10:3:5:9:4:6:8:0})/11>9;СУММПРОИЗВ((ПСТР(A1;СТРОКА(1:10);1)*1)*{2:4:10:3:5:9:4:6:8:0})/10;СУММПРОИЗВ((ПСТР(A1;СТРОКА(1:10);1)*1)*{2:4:10:3:5:9:4:6:8:0})/11)=ПСТР(A1;10;1)*1;"ок";"обман")

в А1 ИНН - 7714617793, получим, что этот ИНН неверный. А он точно верный  :)
Или я не правильно считаю контрольную сумму или вы не полностью рассказали про алгоритм
 
 
 
#9
25.12.2012 12:06:39
Цитата
sva пишет:
7714617793
моя формула тоже говорит, что верный.
Роман, спи спокойно - налоговые органы идут лесом.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#10
25.12.2012 12:06:57
Цитата
ikki пишет: если здесь... нет опечаток, то моё понимание правильнее Вашего
да, наверное, эт я поспешил... "Болвааан... Согласен!" (с)  :D
 
 
 
#11
25.12.2012 12:18:35
Ребята вы гении спасибо, но есть еще проблема, в списке есть 10-ти значные и 12-ти значные ИНН, а алгоритм для 12-ти значного другой
алгоритм проверки ИНН 12 знаков.

1. Вычисляется контрольная сумма по 11-ти знакам со следующими весовыми коэффициентами: (7,2,4,10,3,5,9,4,6,8,0)
2. Вычисляется контрольное число(1) как остаток от деления контрольной суммы на 11
3. Если контрольное число(1) больше 9, то контрольное число(1) вычисляется как остаток от деления контрольного числа(1) на 10
4. Вычисляется контрольная сумма по 12-ти знакам со следующими весовыми коэффициентами: (3,7,2,4,10,3,5,9,4,6,8,0).
5. Вычисляется контрольное число(2) как остаток от деления контрольной суммы на 11
6. Если контрольное число(2) больше 9, то контрольное число(2) вычисляется как остаток от деления контрольного числа(2) на 10
7. Контрольное число(1) проверяется с одиннадцатым знаком ИНН и контрольное число(2) проверяется с двенадцатым знаком ИНН.
В случае их равенства ИНН считается правильным.

Возможно ли их распознавать и 12-ти значные проверять другим алгоритмом.
 
 
 
#12
25.12.2012 12:19:22
В общем формула должна быть такая в А1 ИНН
Код
=ЕСЛИ(ЕСЛИ(СУММПРОИЗВ((ПСТР(A1;СТРОКА(1:10);1)*1)*{2:4:10:3:5:9:4:6:8:0})-ЦЕЛОЕ((СУММПРОИЗВ((ПСТР(A1;СТРОКА(1:10);1)*1)*{2:4:10:3:5:9:4:6:8:0})/11))*11>10;0;СУММПРОИЗВ((ПСТР(A1;СТРОКА(1:10);1)*1)*{2:4:10:3:5:9:4:6:8:0})-ЦЕЛОЕ((СУММПРОИЗВ((ПСТР(A1;СТРОКА(1:10);1)*1)*{2:4:10:3:5:9:4:6:8:0})/11))*11)=ПСТР(A1;10;1)*1;"верно";"неверно")


Александр, налоговые органы идут лесом, но попадает все время к нам  :)
Изменено: sva - 25.12.2012 12:21:15
 
 
 
#13
25.12.2012 12:32:36
Цитата
Евгений Тарасов пишет:
в списке есть 10-ти значные и 12-ти значные ИНН, а алгоритм для 12-ти значного другой
правильно! не надо раскрывать все секреты сразу  :D
а 16-тизначных нету?  :oops:
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#14
25.12.2012 12:35:41
:) нет
 
 
 
#15
25.12.2012 12:58:23
Надеюсь у вас не 2003 Excel  :)
Формула во вложении.
 
 
 
#16
23.07.2013 18:16:44
Коллеги, мне нужно установить проверку на поля ИНН и КПП в Sharepoint 2010

Прошу помочь с приведением, сего произведения:
Код
=ЕСЛИ(ДЛСТР(A1)=10;ЕСЛИ(ЕСЛИ(СУММПРОИЗВ((ПСТР(A1;СТРОКА($1:$10);1)*1)*{2:4:10:3:5:9:4:6:8:0})-ЦЕЛОЕ((СУММПРОИЗВ((ПСТР(A1;СТРОКА($1:$10);1)*1)*{2:4:10:3:5:9:4:6:8:0})/11))*11>10;0;СУММПРОИЗВ((ПСТР(A1;СТРОКА($1:$10);1)*1)*{2:4:10:3:5:9:4:6:8:0})-ЦЕЛОЕ((СУММПРОИЗВ((ПСТР(A1;СТРОКА($1:$10);1)*1)*{2:4:10:3:5:9:4:6:8:0})/11))*11)=ПСТР(A1;10;1)*1;"верно";"неверно");ЕСЛИ(СУММПРОИЗВ(ЕСЛИ(ЕСЛИ(СУММПРОИЗВ(--(ПСТР(A1;СТРОКА($1:$12);1));{7:2:4:10:3:5:9:4:6:8:0:0})-ЦЕЛОЕ(СУММПРОИЗВ(--(ПСТР(A1;СТРОКА($1:$12);1));{7:2:4:10:3:5:9:4:6:8:0:0})/11)*11=10;0;СУММПРОИЗВ(--(ПСТР(A1;СТРОКА($1:$12);1));{7:2:4:10:3:5:9:4:6:8:0:0})-ЦЕЛОЕ(СУММПРОИЗВ(--(ПСТР(A1;СТРОКА($1:$12);1));{7:2:4:10:3:5:9:4:6:8:0:0})/11)*11)=ПСТР(A1;11;1)*1;1;0);ЕСЛИ(ЕСЛИ(СУММПРОИЗВ(--(ПСТР(A1;СТРОКА($1:$12);1));{3:7:2:4:10:3:5:9:4:6:8:0})-ЦЕЛОЕ(СУММПРОИЗВ(--(ПСТР(A1;СТРОКА($1:$12);1));{3:7:2:4:10:3:5:9:4:6:8:0})/11)*11=10;0;СУММПРОИЗВ(--(ПСТР(A1;СТРОКА($1:$12);1));{3:7:2:4:10:3:5:9:4:6:8:0})-ЦЕЛОЕ(СУММПРОИЗВ(--(ПСТР(A1;СТРОКА($1:$12);1));{3:7:2:4:10:3:5:9:4:6:8:0})/11)*11)=ПСТР(A1;12;1)*1;1;0))=1;"верно";"не верно"))
в порядок.

Как из этого убрать диапазоны и для чего они нужны в данном случае?
 
 
 
Страницы: 1
Читают тему
Наверх