Инн в экселе с нулями как исправить

Обновлено: 06.07.2024

Почему ЕСЛИОШИБКА лучше и я называю её более оптимизированной? Разберем первую формулу подробнее:
=ЕСЛИ(ЕОШ( A1 / B1 );0; A1 / B1 )
Если вычислить пошагово, то увидим, что сначала происходит вычисление выражения A1 / B1 (т.е. деление). И если его результат ошибка – то ЕОШ вернет ИСТИНА (TRUE) , которое будет передано в ЕСЛИ (IF) . И тогда функцией ЕСЛИ(IF) будет возвращено значение из второго аргумента 0.
Но если результат не является ошибочным и ЕОШ (ISERR) возвращает ЛОЖЬ (FALSE) – то функция заново будет вычислять уже вычисленное ранее выражение: A1 / B1
С приведенной формулой это особой роли не играет. Но если применяется формула вроде ВПР (VLOOKUP) с просмотром на несколько тысяч строк – то вычисление два раза может значительно увеличить время пересчета формул.
Функция же ЕСЛИОШИБКА (IFERROR) один раз вычисляет выражение, запоминает его результат и если он ошибочен возвращает записанное вторым аргументом. Если же ошибки нет, то возвращает запомненный результат вычисления выражения из первого аргумента. Т.е. вычисление по факту происходит один раз, что практически не будет влиять на скорость общего пересчета формул.
Поэтому если у вас Excel 2007 и выше и файл не будет использоваться в более ранних версиях – то имеет смысл использовать именно ЕСЛИОШИБКА (IFERROR) .

Для чего формулы с ошибками вообще исправлять? Обычно делается для более эстетичного отображения данных в отчетах, особенно если отчеты потом руководству отправляют.

Итак, есть на листе такие формулы, ошибки которых надо обработать. Если подобных формул для исправления одна-две(да даже 10-15) – то проблем почти нет заменить вручную. Но если таких формул несколько десятков, а то и сотен – проблема приобретает почти вселенские масштабы :-). Однако процесс можно упростить через написание относительно простого кода Visual Basic for Application.
Для всех версий Excel:

Для версий 2007 и выше

Как это работает
Если не знакомы с макросами, то для начала лучше прочитать как их создавать и вызывать: Что такое макрос и где его искать?, т.к. может случиться так, что все сделаете правильно, но забудете макросы разрешить и ничего не заработает.

Копируете приведенный код, переходите в редактор VBA(Alt+F11), создаете стандартный модуль(Insert -Module) и просто вставляете в него этот код. Переходите в нужную книгу Excel и выделяете все ячейки, формулы в которых необходимо преобразовать таким образом, чтобы в случае ошибки они возвращали ноль. Жмете Alt+F8, выбираете код IfIsErrNull(или IfErrorNull, в зависимости от того, какой именно скопировали) и жмете Выполнить.
Ко всем формулам в выделенных ячейках будет добавлена функция обработки ошибки. Приведенные коды учитывают так же:
-если в формуле уже применена функция ЕСЛИОШИБКА или ЕСЛИ(ЕОШ, то такая формула не обрабатывается;
-код корректно обработает так же функции массива;
-выделять можно несмежные ячейки(через Ctrl).
В чем недостаток: сложные и длинные формулы массива могут вызвать ошибку кода, в связи с особенностью данных формул и их обработкой из VBA. В таком случае код напишет о невозможности продолжить работу и выделит проблемную ячейку. Поэтому настоятельно рекомендую производить замены на копиях файлов.
Если значение ошибки надо заменить на пусто, а не на ноль, то надо строку

Const sToReturnVal As String = "0"

Удалить, а перед строкой

Удалить апостроф ( ' )

Так же можно данный код вызывать нажатием кнопки(Как создать кнопку для вызова макроса на листе) или поместить в надстройку(Как создать свою надстройку?), чтобы можно было вызывать из любого файла.

При введите номер, содержащий более 15 цифр в ячейке в Microsoft Excel, Excel любые цифры за пятнадцатым местом на нули. Например, введите номер удостоверения кредитной карты в следующем формате:

В этой ситуации Excel последнюю цифру на ноль.

Причина

Excel спецификации IEEE 754 для хранения и вычисления номеров с плавающей точкой. Excel сохраняет только 15 значимых цифр в номере и изменяет цифры после пятнадцатого места в ноль

Обходной путь

Добавление кавычка

Чтобы не изменить цифры на ноль, введите одну кавычка перед вводом номера.

Для этого выберите пустую ячейку, введите одну кавычка ('), а затем введите номер. Все цифры отображаются в ячейке.

Введите одну кавычка и введите номер.

Формат ячеек

Чтобы не вводить кавычка в каждой затронутой ячейке, перед вводом каких-либо данных можно форматирование ячеек в виде текста.

Выберите все затронутые ячейки и нажмите кнопку Ctrl+1, чтобы открыть диалоговое окно Format Cells.

На вкладке Номер выберите Текст из списка Категорий и выберите ОК.

Выберите Текст в вкладке Номер в диалоговом окне "Ячейки формата".

Дополнительные сведения

Такое поведение происходит только в том случае, если ячейка отформатирована как Номер, а число, в которое вошел, превышает 15 цифр. Для ячеек, форматированные в виде текста, можно ввести до 32 767 символов. Excel на таблице отображается до 1024 символов.

Так как настраиваемые форматы номеров предназначены для работы в основном с числами, невозможно создать настраиваемый формат номеров, который хранит более 15 цифр. Например, нельзя использовать следующий формат для хранения 16-символьного удостоверения кредитной карты в виде номера:

Однако, если ввести номер в ячейке, которая форматирована как текст, все символы остаются как вы введите их, так как Excel сохраняет номер в виде текста, а не как номер.

Office 365 ProPlus переименован в Майкрософт 365 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.

Симптомы

После ввода длинного номера (например, номера кредитной карты) в ячейке Excel номер отображается неправильно в Excel. Например,

Пример длинного числа, которое неправильно отображается в Excel.

Числовой формат по умолчанию в Excel является общим, поэтому вы можете отображать до 11 цифр в ячейке.

Обходной путь

Для решения проблемы используйте один из указанных ниже способов.

Способ 1. Форматирование ячейки как текста

Для этого выполните следующие действия:

Щелкните правой кнопкой мыши поле со списком и выберите пункт Формат ячеек.

Отформатируйте ячейку как текст, выбрав параметр «Формат ячеек».

На вкладке Число выберите Текст, а затем нажмите ОК.

Выберите «Текстовый» на вкладке «Числовые форматы» в диалоговом окне «Формат ячеек».

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

Введите длинное число.

Если вы не хотите видеть предупреждающие стрелки, нажмите на маленькую стрелку, а затем Пропустить ошибку.

Выберите «Пропустить ошибку», если вы не хотите видеть предупреждающие стрелки.

Результат, возвращаемый методом 1.

Способ 2. Использование одинарной кавычки

При вводе длинного числа сначала введите одинарную кавычку (') в ячейке, а затем длинное число.

Например, введите '1234567890123456789, и одинарная кавычка не будет отображаться после нажатия ENTER.

Перед вводом длинного числа добавьте одинарную кавычку.

Связанные статьи

Ваше мнение важно для нас! Не стесняйтесь сообщать, что вы думаете об этой статье, используя поле комментария, расположенное внизу документа. Это позволит нам улучшить содержимое. Заранее спасибо!

ИНН – идентификационный номер налогоплательщика – присваивается физическим и юридическим лицам. Такие обозначения есть у граждан России, Украины и Беларуси (УНП, ИН). Аналоги существуют у граждан многих других государств.

В данном коде «спрятана» информация о дате рождения, поле человека, коде налоговой (для российских номеров).

Алгоритм расшифровки ИНН в Excel

Идентификационный номер налогоплательщика – это 10 (для юридических) или 12 (для физических лиц) цифр, которые содержат следующую информацию (слева направо):

  • код налоговой инспекции, присвоившей ИНН (4 цифры);
  • порядковый номер записи в едином государственном реестре налогоплательщиков территориального налогового органа (5 цифр – для организаций, 6 – для физических лиц);
  • контрольное число, полученное в ходе применения специального алгоритма (для юридических лиц – 1 цифра, для физических – 2).

Алгоритм проверки ИНН предложен в сервисе «Консультант».

Для десятизначного цифрового кода:

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

Для двенадцатизначного кода:

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

Проверку правильности ИНН лучше автоматизировать. Неизвестно, реально существующий контрагент выставляет счет к оплате или мошенник. Данная проблема особенно актуальна на крупных предприятиях.

Есть готовые функции для проверки актуальности идентификационного номера: для 10 знаков и для 12. Проверить правильность указания ИНН можно с помощью данной функции.

Реализуем алгоритм проверки с помощью встроенных функций Excel:

  1. Список номеров введем в столбец А. Перед каждым номером ставим апостроф:
  2. В ячейку В1 вводим формулу Копируем ее на весь столбец.

Реальные ИНН – «истина», неправильные – «ложь». Данная формула работает только в отношении десятизначных номеров. Если организация работает с физическими и юридическими лицами, то в базу будут заноситься 10-тизначные и 12-тизначные коды.

Проверка кода.

Просто копируйте и вставляйте – формула рабочая.

Дата рождения по ИНН (Россия) в Excel

Для физических лиц ИНН включает 12 цифр:

где ХХХХ – данные о налоговой инспекции;

ZZZZZZ – порядковый номер налогоплательщика;

YY – контрольный код.

Налогоплательщик получает порядковый номер, когда становится на учет в ФНС. Привязки к дате рождения нет.

Дата рождения по ИНН (Украина) в Excel

Идентификационный номер налогоплательщиков Украины состоит из 10 цифр. Первые 5 – количество дней, которые прошли с 01.01.1900 до рождения.

Например, человек родился 20.09.1976. Первые пять цифр – 28022. Столько дней до его рождения с 31.12.1899 года.

Чтобы найти дату рождения по первым пяти цифрам 28022, нужно 01.01.1900 + 28022 (дней) = 20.09.1976.

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

Чтобы найти дату рождения, нужно ввести в соседнюю ячейку пять первых цифр плюс 1. И установить формат «Дата». Сделать это можно с помощью ряда функций. Подойдут формулы:

  1. =ЗНАЧЕН(ЛЕВСИМВ(ТЕКСТ(A2;0);5))+1
  2. =ЛЕВСИМВ(A2;5)+1
  3. =ПСТР(A2;1;5)+1

Все три варианта выдают одинаковые результаты.

Пример.

Данный алгоритм срабатывает не всегда. Четыре цифры за датой рождения в ИНН – порядковый номер плательщика в налоговой службе. Формируется он по мере поступления данных в базу.

Если в какой-то день рождается слишком много людей, то четырех цифр для образования номера не хватает. В данном случае номер начинается с 7 или 8. Цифра указывает на будущее время – алгоритм определения даты неприменим.

Дата рождения по ИНН (Беларусь) в Excel

Граждане Беларуси при постановке на учет в налоговых органах получают УНП (учетный номер плательщика). Это девятизначный код, где:

  • первая цифра обозначает регион (у физ. лиц – буква);
  • вторая-восьмая – порядковый номер плательщика;
  • девятая – контрольная.

С датой рождения УНП не связан.

Зато по идентификационному (личному) номеру, который есть у каждого в паспорте, можно определить дату рождения.

  • первая цифра – пол;
  • вторая-седьмая – день, месяц и год;
  • буква – код региона;
  • три цифры за буквой – порядковый номер лица, обратившегося за паспортом;
  • две буквы – признак гражданства;
  • последняя цифра – контрольная.

Чтобы быстро извлечь дату из личного номера, можно использовать функцию =ПСТР(A2;2;6).

Результат.

С идентификационными номерами приходится работать бухгалтерам, инженерам по кадрам и другим специалистам. В некоторых организациях такая информация представлена тысячами значений. Обрабатывать вручную и принимать в базу такие коды бесконтрольно – нехорошо. Ряд процессов можно автоматизировать простейшими средствами Microsoft Excel.

Читайте также: