Авторизация в excel как сделать

Отслеживание входа пользователей в книгу Excel

Представьте себе книгу Excel с очень важными для вас данными, сохраненную на общем сетевом диске компании, куда имеет доступ куча народу. Одним совсем не прекрасным утром вы открываете этот файл и обнаруживаете, что внутри кто-то поиграл в аль-каиду: формулы поломаны, данные стерты, дизайн нарушен. Поскольку вы опытный пользователь, то у вас, конечно же, была резервная копия этого важного документа (была же, правда?) и данные вы восстановите, но чисто из спортивного интереса хотелось бы все же узнать — КТО ЭТО СДЕЛАЛ?!

Давайте попробуем решить эту задачу. Итак, нам нужно:

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

Этап 1. Создаем «Лог»

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

Этап 2. Макросы фиксации входа-выхода

Теперь добавим макросы для записи на лист Лог даты-времени и имен пользователей при открытии и закрытии книги. Для этого нужно открыть редактор Visual Basic с помощью сочетания Alt+F11 или с помощью кнопки Visual Basic на вкладке Разработчик (Developer) и найти в левом верхнем углу панель Project (если она не отображается, то включить ее можно сочетанием клавиш Ctrl+R):

Двойным щелчком откройте модуль ЭтаКнига (ThisWorkbook) и вставьте туда пару наших макросов для обработки событий открытия и закрытия книги:

В первом приближении все уже должно работать. Попробуйте открыть-закрыть этот файл пару раз и убедитесь, что на лист Лог попадает ваше имя пользователя (логин входа в Windows) и дата-время:

Этап 3. Улучшаем надежность

Можно было бы скрыть лист Лог и на этом остановиться, но есть одно «но»: если у пользователя, который открывает нашу книгу, макросы разрешены по умолчанию либо он сам их разрешает, нажав в окне предупреждения на кнопку Включить содержимое, то все в порядке:

Но что если пользователь не разрешит выполнение макросов или они отключены у него по умолчанию? Тогда наши макросы отслеживания выполняться не будут и фиксации имени и даты не произойдет 🙁 Как же заставить пользователя разрешить использование макросов?

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

Суть в том, чтобы по умолчанию скрыть в книге все листы кроме этого, а рабочие листы с данными отображать с помощью специального макроса. Если пользователь не разрешил выполнение макросов, то он увидит в книге только один лист с предупреждением. Если же макросы разрешены, то наш макрос обработки события открытия книги скроет лист с предупреждением и отобразит листы с данными. Чтобы пользователь сам не отобразил их — используем суперскрытие вместо обычного скрытия листов (параметр xlSheetVeryHidden вместо обычного False).

Чтобы реализовать все описанное, слегка изменим наши процедуры в модуле ЭтаКнига (ThisWorkbook):

Чтобы просмотреть скрытый Лог откройте редактор VisualBasic (Alt+F11), выделите лист на панели Project и измените его видимость на панели Properties, используя свойство Visible:

Если пользователи настолько продвинутые, что знают про суперскрытые листы и могут их отобразить через редактор Visual Basic или нарушить работу наших макросов, то можно дополнительно поставить пароль на просмотр и изменение макросов. Для этого щелкните правой кнопкой мыши по имени файла в панели Project (строка VBAProject (blackbox.xls)), выберите команду VBA Project Properties и включите флажок Lock project for viewing и задайте пароль на вкладке Protection:

Теперь точно никто не уйдет безнаказанным. Большой Брат следит за тобой! 😉

Мгновенное заполнение в Excel

Одной из самых гениальных и полезных, на мой взгляд, функцией Excel является «Мгновенное заполнение» (доступно с Microsoft Office 2013 года). Оно творит такие чудеса, что за пару движений пальцами вы сэкономите время и нервы от выполнения огромной рутинной работы. Вместе с тем функция проста как дважды два и подойдёт для пользователей любого уровня. Почувствуйте себя всемогущим, мгновенно решив сложные задачи, это как щелчок Таноса, только лучше)) Заинтриговал?)) Теперь поподробней:

1. У нас имеется таблица с фамилиями, именем и отчеством. Из неё нам нужно сделать «Фамилию и инициалы». Для этого мы в соседнюю строку вводим как это должно быть, затем при вводе на следующей строке у нас появляется предлагаемый вариант, если он нас устраивает, то нажимаем «Enter», если список не появился жмём Ctrl+ E и вуаля, у вас готов весь список по заданной форме.

Также можно вписать только Фамилию и имя и получить желаемый результат, можно вывести инициалы вперёд, можно оставить только фамилию, в общем вариантов много и Excel сделает как вам нужно

Читать еще:  Как сделать судоку в excel?

Здесь есть одно правило, вводимый текст должен быть непосредственно рядом с исходным текстом, не важно слева или справа, но, чтобы рядом, иначе выйдет ошибка

Также значения из одного столбца с текстом можно разбить на три разных. Заполняем каждый столбец как нам нужно, нажимаем Ctrl+E на нижней пустой ячейке и получаем готовый результат.

Также он работает и в обратную сторону, больше не нужны формулы СЦЕПИТЬ, ЛЕВСИМВ, ПРАВСИМВ, ПСТР и т.д.

Можно задать регистр и сделать фамилии, написанные верхним регистром или полностью капсом. Можно исправлять слова с НеПрАвИлЬнЫм регистром. Если между словами нет пробела, это тоже не проблема, главное, чтобы Ф.И.О. начиналось с заглавной буквы, для определения начала слова

Можно наоборот из заглавных букв сделать печатные, но в этом случае может неправильно отобразиться результат, в этом случае нажимаем на значок с молнией, где отменям мгновенное заполнение, затем вводим ещё раз в следующую строку и нажимаем Ctrl+ E и в этот раз отобразится как нужно.

2. Вы восхищены его супер возможностями? Тогда возрадуйтесь, потому что это ещё не всё)) Есть возможность склейки текста. Итак, у нас имеются значения, разбитые по ячейкам, но нам нужно, чтобы они были в одной ячейке и ещё с дополненным текстом. Вводим в первую строку так, как нам нужно, повторяем то же действие на второй строке и на третьей нажимаем Ctrl+E и радуемся результатом

3. Вы уже кричите от того почему вы не знали об этом раньше? Тогда продолжаем удивляться «Мгновенным заполнением»))

У нас есть текст с цифрами в ячейках и нам нужно вывести в другой столбец только цифры, делать это вручную означает почувствовать себя Золушкой, перебирающей горох, но волшебным нажатием Ctrl+E, получаем нужный результат, причём Excel не важно, есть ли там лишние пробелы, знаки или прочее, он как настоящий мужик – берёт и делает.

То же самое он делает и с извлечением только текста

Если попадается разношёрстный текст, где первым стоит слово затем цифра, а в нижних строках имеется сначала цифра потом текст, лучше сначала в первых двух строках дописать цифры перед текстом, затем нажать Ctrl+E, тогда Excel правильно определит алгоритм и выведет текст без ошибок

4. Ты до сих пор сомневаешься в возможностях мгновенного заполнения? Говоришь он не справится с датами? Забери свои слова обратно, даты он изменяет на изи, причём определяет не только цифровые, но и текстовые, главное, чтобы они были в именительном падеже, либо на внятном сокращении месяца

5. Ты тайный поклонник нестандартных дат? Не волнуйся, мгновенное заполнение удовлетворит твои желания. Он может изменить обычную дату в Unix-формат ГГММДД. Для этого меняем формат соседнего столбца на текстовый, затем вводим форму значений в пару ячеек, чтобы правильно задать алгоритм и нажав Ctrl+E получаем результат

Вы ещё не устали удивляться великолепным «Мгновенным заполнением»? Тогда получайте очередную порцию волшебства))

6. У нас имеется столбец с телефонными номерами, но они выглядят как простые цифры, мы же придадим им красоты, добавив + и заключив код оператора в скобки. Преобразуем цифры и соседний столбец в текстовый формат, затем вводим нужный параметр в первые две ячейки соседнего столбца и нажав Ctrl+E, гордимся проделанной работой

7. Для тех, кто выгружает даты из различных ERP и CRM-систем, дата представляется 8-значным числом в формате ГГГГММДД. Исправлять в нормальный вид можно было функцией ДАТАЗНАЧ, либо вручную, но теперь ловите лайфхак от героя «Мгновенного заполнения», главное покажите ему правильный алгоритм, дальше он всё сам сделает за вас))

При использовании мгновенного заполнения помните следующие моменты:

— первое правило напомню, ячейки должны находиться рядом с определяемым столбцом;

— при выявлении шаблона учитываются все значения в строке — слева и справа от вводимого столбца, поэтому лишние колонки, способные запутать алгоритм или внести помехи, лучше заранее отделить от рабочих данных пустыми столбцами или удалить;

— мгновенное заполнение также работает и в умных таблицах;

— ошибка или опечатка при наборе ячеек-образцов может привести к тому, что мгновенное заполнение не сможет найти нужное значение и не сработает;

— бывает, что шаблон определяется некорректно, поэтому всегда проверяйте полученные результаты (хотя бы выборочно).

Как вам «Мгновенное заполнение», поделитесь впечатлениями в комментариях))

Авторизация в Excel на VBA

Авторизация в Excel на VBA: создание формы, написание программного кода, особенности реализации.

В этой статье я расскажу вам о создании формы авторизации в Excel с помощью VBA.

Что же представляет из себя авторизация в Excel? Это форма, то есть окно, с запросом ввода логина и пароля, при успешном вводе которых, пользователю будут открываться различные листы документа, в зависимости от группы доступа пользователя. Точно также, помимо открытия листов, можно будет выставить ограничения на действия в Excel: запрет форматирования ячеек, удаление строк, столбцов, использование фильтров, объектов, сценариев и так далее. Но обо всем по порядку.

Читать еще:  Как в excel сделать нумерацию страниц не с первой страницы?
Авторизация: основной алгоритм работы

Для начала, необходимо разработать алгоритм, по которому мы будем работать, и выглядеть он будет примерно так:

Макет документа

Наш рабочий документ будет состоять из четырех листов:

  1. Лист с приветствием — единственный лист, который будет отображаться всем пользователям до авторизации
  2. Лист с дашбордом (визуализированным отчетом) — графики, диаграммы/гистограммы — изначально со свойством VeryHidden*
  3. Лист с данными — источник расчетов для дашборда — изначально со свойством VeryHidden
  4. Служебный/технический лист — для хранения логинов, паролей и служебной и вспомогательной информации — также, со свойством VeryHidden

*VeryHidden— свойство листа, при котором сам лист скрыт и включить его отображение можно только через использование режима разработчика. Для выбора данного свойства, необходимо в Excel зайти в меню «Разработчик» — «Visual Basic». Далее, необходимо выбрать нужный нам лист и в его свойствах («Properties») найти свойство Visible и установить в «2 — xlSheetVeryHidden».

После этого лист станет «очень скрытым». Отменить свойство VeryHidden можно точно так же через режим разработчика, либо используя необходимый нам макрос, о котором я напишу чуть дальше.

Группы доступа

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

  1. Администраторы (Admin): доступны все листы , в том числе служебный, а также отсутствуют какие-либо ограничения.
  2. Руководители и ответственные за данные (Head): доступны 2 рабочих листа + стоит пароль на изменение структуры книги.
  3. Рядовые сотрудники компании (Worker): доступен только 1 рабочий лист с дашбордом + стоит пароль на изменение структуры книги.
Разработка макета формы

С этим пунктом не должно возникнуть никаких проблем. Элементов на форме авторизации должно быть не так уж и много:

  • Поле ввода для логина
  • Поле ввода для пароля
  • Кнопка «Авторизация» (проверка логина и пароля)
  • Различные подписи на форме
  • По желанию: кнопка закрытия формы, кнопка восстановления пароля, логотипы, справка и все, что душе угодно

Для тех, кто не знает, как создавать формы в VBA: нужно зайти в меню «Разработчик» — «Visual Basic», нажать правой кнопкой по нашему проекту (VBAProject Название_Файла.xlsx), затем Insert — UserForm. Создается форма с названием UserForm1, на которой мы и будем размещать все наши объекты с помощью меню «ToolBox».

Если быстро набросать элементы, которые мы перечислили, должно получиться что-то вроде этого:

После добавления элементов, поменял их стандартные названия:

«UserForm1» переименовал в «Authorization».
«TextBox1» переименовал в «TextBox_Login».
«TextBox2» переименовал в «TextBox_Pass».

Также, в свойствах «TextBox_Pass», мы находим свойство «PasswordChar» и вводим любой символ, который мы хотим видеть вместо вводимых символов пароля — чаще всего используется символ «*».

Ничего лишнего, пока что все просто. Переходим далее.

Подготовка служебного листа

В моем примере, служебный лист будет содержать список логинов, паролей, а также соответствующую логину группу доступа. Чтобы немного «усложнить» потенциальный взлом (хотя надо понимать, что взломать Excel продвинутому пользователю не составит никакого труда), пароли на листе мы будем хранить в захешированном виде. Подробнее о хешировании можно прочитать на Википедии, но в вкратце — это преобразование данных в строку фиксированной длины, состоящей из цифр и букв с помощью определенных математических алгоритмов. О том, как мы будем хешировать пароли в Excel, я расскажу далее.

Итак, для начала, содержимое служебного листа будет выглядеть вот так:

Авторизация: особенности и написание программного кода на VBA

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

Для начала, мы напишем код, который будет отображать различные листы в зависимости от роли пользователя после авторизации. Макрос user_group мы делаем приватным и вписывать его будем не в отдельный модуль, а в нашу готовую форму Authorization. Аргументом для макроса является переменная X, которая будет содержать название группы доступа в виде текстовой строки String:

Мгновенное заполнение в Excel

Одной из самых гениальных и полезных, на мой взгляд, функцией Excel является «Мгновенное заполнение» (доступно с Microsoft Office 2013 года). Оно творит такие чудеса, что за пару движений пальцами вы сэкономите время и нервы от выполнения огромной рутинной работы. Вместе с тем функция проста как дважды два и подойдёт для пользователей любого уровня. Почувствуйте себя всемогущим, мгновенно решив сложные задачи, это как щелчок Таноса, только лучше)) Заинтриговал?)) Теперь поподробней:

1. У нас имеется таблица с фамилиями, именем и отчеством. Из неё нам нужно сделать «Фамилию и инициалы». Для этого мы в соседнюю строку вводим как это должно быть, затем при вводе на следующей строке у нас появляется предлагаемый вариант, если он нас устраивает, то нажимаем «Enter», если список не появился жмём Ctrl+ E и вуаля, у вас готов весь список по заданной форме.

Читать еще:  Как сделать таблицу список в excel?

Также можно вписать только Фамилию и имя и получить желаемый результат, можно вывести инициалы вперёд, можно оставить только фамилию, в общем вариантов много и Excel сделает как вам нужно

Здесь есть одно правило, вводимый текст должен быть непосредственно рядом с исходным текстом, не важно слева или справа, но, чтобы рядом, иначе выйдет ошибка

Также значения из одного столбца с текстом можно разбить на три разных. Заполняем каждый столбец как нам нужно, нажимаем Ctrl+E на нижней пустой ячейке и получаем готовый результат.

Также он работает и в обратную сторону, больше не нужны формулы СЦЕПИТЬ, ЛЕВСИМВ, ПРАВСИМВ, ПСТР и т.д.

Можно задать регистр и сделать фамилии, написанные верхним регистром или полностью капсом. Можно исправлять слова с НеПрАвИлЬнЫм регистром. Если между словами нет пробела, это тоже не проблема, главное, чтобы Ф.И.О. начиналось с заглавной буквы, для определения начала слова

Можно наоборот из заглавных букв сделать печатные, но в этом случае может неправильно отобразиться результат, в этом случае нажимаем на значок с молнией, где отменям мгновенное заполнение, затем вводим ещё раз в следующую строку и нажимаем Ctrl+ E и в этот раз отобразится как нужно.

2. Вы восхищены его супер возможностями? Тогда возрадуйтесь, потому что это ещё не всё)) Есть возможность склейки текста. Итак, у нас имеются значения, разбитые по ячейкам, но нам нужно, чтобы они были в одной ячейке и ещё с дополненным текстом. Вводим в первую строку так, как нам нужно, повторяем то же действие на второй строке и на третьей нажимаем Ctrl+E и радуемся результатом

3. Вы уже кричите от того почему вы не знали об этом раньше? Тогда продолжаем удивляться «Мгновенным заполнением»))

У нас есть текст с цифрами в ячейках и нам нужно вывести в другой столбец только цифры, делать это вручную означает почувствовать себя Золушкой, перебирающей горох, но волшебным нажатием Ctrl+E, получаем нужный результат, причём Excel не важно, есть ли там лишние пробелы, знаки или прочее, он как настоящий мужик – берёт и делает.

То же самое он делает и с извлечением только текста

Если попадается разношёрстный текст, где первым стоит слово затем цифра, а в нижних строках имеется сначала цифра потом текст, лучше сначала в первых двух строках дописать цифры перед текстом, затем нажать Ctrl+E, тогда Excel правильно определит алгоритм и выведет текст без ошибок

4. Ты до сих пор сомневаешься в возможностях мгновенного заполнения? Говоришь он не справится с датами? Забери свои слова обратно, даты он изменяет на изи, причём определяет не только цифровые, но и текстовые, главное, чтобы они были в именительном падеже, либо на внятном сокращении месяца

5. Ты тайный поклонник нестандартных дат? Не волнуйся, мгновенное заполнение удовлетворит твои желания. Он может изменить обычную дату в Unix-формат ГГММДД. Для этого меняем формат соседнего столбца на текстовый, затем вводим форму значений в пару ячеек, чтобы правильно задать алгоритм и нажав Ctrl+E получаем результат

Вы ещё не устали удивляться великолепным «Мгновенным заполнением»? Тогда получайте очередную порцию волшебства))

6. У нас имеется столбец с телефонными номерами, но они выглядят как простые цифры, мы же придадим им красоты, добавив + и заключив код оператора в скобки. Преобразуем цифры и соседний столбец в текстовый формат, затем вводим нужный параметр в первые две ячейки соседнего столбца и нажав Ctrl+E, гордимся проделанной работой

7. Для тех, кто выгружает даты из различных ERP и CRM-систем, дата представляется 8-значным числом в формате ГГГГММДД. Исправлять в нормальный вид можно было функцией ДАТАЗНАЧ, либо вручную, но теперь ловите лайфхак от героя «Мгновенного заполнения», главное покажите ему правильный алгоритм, дальше он всё сам сделает за вас))

При использовании мгновенного заполнения помните следующие моменты:

— первое правило напомню, ячейки должны находиться рядом с определяемым столбцом;

— при выявлении шаблона учитываются все значения в строке — слева и справа от вводимого столбца, поэтому лишние колонки, способные запутать алгоритм или внести помехи, лучше заранее отделить от рабочих данных пустыми столбцами или удалить;

— мгновенное заполнение также работает и в умных таблицах;

— ошибка или опечатка при наборе ячеек-образцов может привести к тому, что мгновенное заполнение не сможет найти нужное значение и не сработает;

— бывает, что шаблон определяется некорректно, поэтому всегда проверяйте полученные результаты (хотя бы выборочно).

Как вам «Мгновенное заполнение», поделитесь впечатлениями в комментариях))

Ссылка на основную публикацию
Adblock
detector