Безопасное отражение в MS EXCEL номеров счетов с помощью шаблона

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

Решение задачи зависит от количества цифр в номере.

Число цифр в номере больше 15

Так как EXCEL хранит и выполняет вычисления с точностью 15 значащих цифр, то номера кредитных карт (16 цифр), банковских счетов (20 цифр) сохранить в числовом формате не удастся, последние цифры будут заменены 0. Поэтому номера с количеством цифр больше 15 могут храниться в EXCEL только в текстовом формате.

Настроим отображение номеров кредитных карт в безопасном формате. Вместо отображения полного номера 4887 1467 1084 1647, мы отобразим только 4 последние цифры **** **** **** 1647. Номер сохранен в текстовом формате, между группами из 4-х цифр - пробелы.

Решением является использование формулы: ="**** **** **** "&ПРАВСИМВ(A1;4)

В ячейке A1 должен быть номер кредитной карты. Столбец А можно скрыть, выделив любую его ячейку и нажав CTRL+0. В меню Рецензирование / Изменения выбрать Защитить лист. Имеет смысл также спрятать саму формулу, чтобы неавторизованный пользователь не написал формулу =A1 и не вывел полный номер карты.

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

В файле примера приведен вариант со скрытым столбцом и скрытой формулой. В столбцах А и В размещена таблица с полными номерами карт и фамилиями.

 

В ячейке D2 размещен Выпадающий список с фамилиями (ячейка не должна быть защищена). В ячейке Е2 размещена формула
="**** **** **** "&ПРАВСИМВ(ВПР(D2;A2:B4;2;0);4)

Скроем столбцы А и В. В формате ячейки Е2 поставим галочку "Скрыть формулу". Через меню Рецензирование / Изменения выберем Защитить лист. Получим следующий результат (формулы не видно, скрытые столбцы нельзя отобразить).

 

Выбрав в ячейке D2 другую фамилию, получим в ячейке Е2 соответствующий номер карты в безопасном формате.

Число цифр в номере меньше 16

Пусть необходимо скрыть номер телефона (10 цифр), который хранится в числовом формате.

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

Пользовательский формат можно ввести через диалоговое окно Формат ячеек (нажав CTRL+1), вкладка Число, (все форматы). Сам формат вводите в поле Тип, предварительно все из него удалив.

Скроем последние 3 цифры в номере телефона (916) 734-66-58: (916)-734-6*-**.

Запишем формат в виде: [<=9999999]0-0 "*-**";(00)000-0 "*-**"

В формате между 0 и " содержится символ пробела (в записи, которая приведена далее, символ пробела заменен словом Пробел: [<=9999999]0-0Пробел"*-**";(00)000-0Пробел"*-**"). Формат работает при записи номера в 7 значном и 10 значном формате.

 

Столбец А и формулу в ячейках B1 и B2 можно скрыть, как показано в предыдущем разделе.

Обратите внимание, что вместо (916)-734-6*-** мы получили (916)-734-7*-**, т.к. на самом деле мы отобразили с помощью нашего пользовательского формата число с точностью до миллиона, и при этом произошло округление. Если бы исходный номер был (916) 734-61-58, то мы получили бы верный результат. Именно по этому использование пользовательского формата в данном случае может быть источником ошибки.

Кроме того, открыв любой незащищенный лист с общим форматом ячейки, можно сослаться на ячейки B1 или B2, в результате чего неавторизованному пользователю может быть доступен полный номер телефона.  Чтобы избежать этого, указанный формат можно ввести через формулу =ТЕКСТ(A1;"[<=9999999]0-0 "&"\*-\*\*;(00)000-0 "&"\*-\*\*"), предварительно закомментировав обратным слешом специальный символ *. В этом случае мы получим результат в текстовом формате.

Чтобы скрыть последние 6 цифр можно использовать формулу =ТЕКСТ(A2;"[<=9999999]0 "&"\*\*-\*\*-\*\*;(00)-0 "&"\*\*-\*\*-\*\*")

В формате между 0 и " содержится 2 символа пробела (скрываются 2 группы разрядов, т.е. 6 цифр).

Для контроля правильности ввода номеров (должно быть строго определенное число знаков) можно использовать Условное форматирование или Проверку данных.

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

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 4.5 (2 оценок)