Варианты защиты листа в Excel

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

Переходите по вкладке ленты Рецензирование – Защитить лист, перед вами появятся вариант защиты. По-умолчанию включено 2 флажка:

▫️Выделение заблокированных ячеек - разрешает на защищенном листе выделять заблокированные ячейки.

▫️Выделение незаблокированных ячеек - разрешает на защищенном листе выделять незаблокированные ячейки.


Однако это не мешает вам использовать другие опции для расширения возможностей:

▫️Форматирование ячеек - разрешает форматировать заблокированные ячейки.

▫️Форматирование столбцов - разрешает изменять ширину столбцов и скрывать их.

▫️Форматирование строк - разрешает изменять высоту строк и скрывать их.

▫️Вставка столбцов - разрешает вставку новых столбцов.

▫️Вставка строк - разрешает вставку новых строк.

▫️Вставка гиперссылок - разрешает вставлять гиперссылки в ячейки, в том числе и на заблокированные.

▫️Удаление столбцов - разрешает удалять столбцы.

▫️Удаление строк - разрешает удалять строки.

▫️Сортировка - разрешает выполнять сортировку данных, но только если это не заблокированные ячейки.

▫️Использование автофильтра - разрешает использовать уже установленный вами автофильтр. Новый не добавить.

▫️Использование сводной таблицы и сводной диаграммы - разрешает изменять сводную таблицу/диаграмму и создавать новые.

▫️Изменение объектов - разрешает изменять объекты (фигуры), диаграммы, примечания.

▫️Изменение сценариев - разрешает пользоваться сценариями. Если забыли про них, то вот начало: https://t.me/exstudy/194

​​Защита структуры и книги

Рабочие листы защитили (https://t.me/exstudy/204), теперь перейдем на уровень повыше.

Шаг 1 – защита структуры.
Это позволит ограничить возможность пользователя создавать, перемещать, удалять, показывать скрытые рабочие листы в книге.

1. Перейдите на вкладку ленты Рецензирование – Защитить книгу.

2. В открытом окне проверьте, чтобы стоял флажок Структура.

3. Задайте пароль для блокировки. Возможно и без него, но тогда защиту будет легко отключить. Повторно введите пароль.

✅ Готово
Снять защиту можно на той же вкладке по одноименной команде Защитить книгу.

Шаг 2 – защита книги.
Пользователь не откроет и не увидит содержимое книги, если не знает пароль.

1. Перейдите на вкладку Файл – Сведения – Защитить книгу.

2. В выпадающем списке выбираете Зашифровать книгу с использованием пароля.

3. Вводите пароль и повторно подтверждаете его.

✅ Готово
Теперь нежелательные пользователи даже не увидит содержимое файла.
Не забудьте сообщить пароль «своим».

​​Строим дашборд. Введение

Многие слышали о таком понятии как дашборд (dashboard). Это итоговая визуальная панель, которая наглядно отражает ваши данные и позволяет принять управленческое решение.
Цикл материалов с тегом #строимдашборд будет посвящен построению вашего собственного дашборда. Поехали!

В чем же особенности дашборда?
— Дашборд - это группа графиков/таблиц с визуальной составляющей, объединенные одной целью.
— Дашборд предназначен для донесения конкретной информации.
— Информация с дашборда позволяет принять управленческое решение.
— Каждый дашборд доноситинформацию до конкретного лица (директор по продажам, финансовый менеджер, менеджер проектов).

Самое главное - выбрать правильное направление. Сегодняшний материал как раз об этом.
Первоначально советую:

1. Определите конечного пользователя. Если получили задачу сделать дашборд (сами решили), то уточните (определите) конечного пользователя дашборда. Отсюда станет понятно какие данные необходимы... ведь вряд ли директору по продажам будет интересно KPI малярного цеха.

2. Определите назначение дашборда. Какие управленческие решения возможно принять глядя на вашу панель? Определив цели, вы определите наборы KPI/метрик, которые будете использовать.

3. Определите источники данных. Вы должны точно знать откуда получите те или иные данные. Это могут такие же таблицы excel, кубы данных OLAP, хранилище данных ERP (1С, Axapta и т.д.). Определите точный список, какие данные откуда вы возьмете.

4. Определите детализацию дашборда. Будет детализация продаж по городам или укрупните до регионов? Остановитесь на отделах или копнете до конкретных сотрудников? Выбирайте подходящую детализацию.

Для введения достаточно, оставайтесь на связи😉

📌 В перерыве цикла советую подписаться и изучить канал Клуб анонимных аналитиков.

📌 Автор канала выпускает посты о правильной аналитике, своих дашбордах, а главное - советы о том, как сделать свой дашборд понятнее.

Присоединяйтесь!

Дополнительная защита книги

Hi!
Есть еще несколько способов остановить пользователя в корректировке информации в книге Excel.

✅ Пометить версию книги как окончательную
В Excel есть возможность показать пользователям, что перед ними финальная версия книги.

1. Перейдите на вкладку Файл – Сведения – Пометить как окончательный.

2. Подтвердить действие.

После этого книга будет работать в режиме чтения. Под лентой будет уведомление об окончательной версии.
Однако, пользователь может нажать на кнопку Все равно редактировать и сможет изменить таблицу.
Это не мера защиты, а лишь уведомление о завершенности работы над файлом.

✅ Сохраняем в PDF
Конечно, любое изменение файла можно предотвратить, сохранив его в PDF.
Excel поддерживает этот формат.

1. Достаточно перейти на вкладку Файл – Сохранить как.

2. Выбираете папку для файла.

3. Выбираете Тип файла – PDF.

Описанные методы не являются полноценной защитой данных, поэтому пользуйтесь защитой листа/книги, описанной здесь:

— https://t.me/exstudy/204
— https://t.me/exstudy/209

Предотвращение потери данных

Работа в Excel может затягивать на многие часы, но иногда забываем сделать самое главное – сохранить результат своей работы.
Забыли сохранить? Часы трудов пошли напрасно)

Рекомендация №1
Всегда сохраняйтесь, используя короткую комбинацию Ctrl + S.
Просто приучите себя периодически нажимать это комбинацию на самых важных рабочих моментах.

Рекомендация №2
Настройте автосохранение файлов. В системе есть функционал автоматического сохранения с заданной периодичностью.

1. Перейдите на вкладку Файл – Параметры – Сохранение

2. Выставите время для Автосохранение каждые. По-умолчанию стоит 10 мин., однако это длинный период. За 10 минут можно существенно изменить таблицу. Поэтому советую поставить 5 мин.

3. Советую также поменять каталог для автосохранения. По-умолчанию стоит Диск:\Users\Пользователь\AppData\Roaming\Microsoft\Excel\. Поставьте папку удобную для вас и назовите ее соответствующе, например «Автосохранения Excel» или «Backup Excel».

Если вдруг отключится компьютер или забудете сохраниться, то у вас хотя бы останется версия-пятиминутка😉

Работа с датами

Друзья, начинаем работу с временем и датами в Excel.
Первые и самые простейшие функции: СЕГОДНЯ и ТДАТА.

Чтобы получить сегодняшнюю дату через формулу, то укажите:

= СЕГОДНЯ()

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

= СЕГОДНЯ() + 3

Если требуется узнать зафиксировать не только дату, но и текущее время, то используется функцию ТДАТА:

= ТДАТА()

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

​​Функция ДАТА

Следующая немаловажная функция для работы с датами, так собственно и называется ДАТА.

Синтаксис:

= ДАТА (год; месяц; день)

— Год – указываете год в виде числа или ссылкой на ячейку. Excel ведет отсчет от 1900, поэтому если укажете 5, то система поймет это как 1905 год. Советую использовать четырехзначное значение года.
— Месяц – указываете месяц в виде числа или ссылкой на ячейку.
— День – указываете день в виде числа или ссылкой на ячейку.

Выражение =ДАТА(2005;10;15) передаст дату 15.10.2005.

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

Функция ДАТА как раз справится с этой задаче.
Не забывайте также, что все функции дат позволяют прибавлять/вычитать дни, месяцы, годы.

Выражение = ДАТА (А1; В1+5; С1+10) будет прибавлять +5 месяцев от значения В1 и +10 дней от значения С1.

​​Сколько рабочих дней?

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

Для этого в Excel есть прекрасная функция ЧИСТРАБДНИ

Синтаксис:

= ЧИСТРАБДНИ (нач дата; кон дата; [праздники])

— нач дата – начальная дата, от которой необходимо посчитать рабочие дни.
— кон дата – конечная дата, до которой считаете рабочие дни.
— [праздники] – необязательный аргумент.

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

​​Получаем год из даты

Есть ряд прекрасных функций в Excel, которые выбирают из даты год, месяц или день. Рассмотрим функцию ГОД.

Синтаксис:

= ГОД (дата в числовом формате)

— дата в числовом формате – ссылка на ячейку с датой или текстовое значение даты в двойных кавычках (“14.01.2020”)

В дальнейшем значение года можно использовать в других формулах/расчетах.

​​Получаем номер месяца и дня из даты

Салют, друзья!
Давно с вами не виделись. Меня затянуло в подготовку материала для курса, посты здесь могут запаздывать, за что извиняюсь.

Аналогично году (https://t.me/exstudy/223), из даты вы можете получить числовое значение месяца и дня. Давайте по порядку.

Функция МЕСЯЦ

= МЕСЯЦ (дата в числовом формате)

— дата в числовом формате – ссылка на ячейку с датой или текстовое значение даты в двойных кавычках (“14.01.2020”)


Функция ДЕНЬ

= ДЕНЬ (дата в числовом формате)

— дата в числовом формате – ссылка на ячейку с датой или текстовое значение даты в двойных кавычках (“14.01.2020”)

Ничего сложного. Дополнительно в видео ⬇️

​​Получаем номер недели

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

Синтаксис:

= НОМНЕДЕЛИ (дата; тип)

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

▫️1 (или если аргумент не указан) – система считает, что неделя начинается с воскресенья.
▫️2 – система считает, что неделя начинается с понедельника. Это классическая система отсчета для России, поэтому ставьте 2.
▫️ 21 – отсчет недель по стандарту ISO 8601 (как говорят оф. источники), который используются в западных странах. Суть в том, что первая неделя года считается первой в случае если содержит четверг. Если же 1 января пришлось на пятницу, то это вообще неделей не считается.

По итогу система посчитаем вам порядковый номер недели.

​​Считаем разницу между датами

Салют, друзья!
Всем прекрасного лета в его лучшем проявлении: походы, отдых и отличное солнечное настроение. Ну а я продолжаю выпускать материалы для обучения Excel.
Show must go on.

Сегодня выпускаю лонгрид, казалось бы, на банальную тему - как посчитать разницу между датами.
В это деле есть 2 пути. Первый прост как палка. Второй же подойдет для анализа крупного массива данных с датами.

Приступим же.

Способ №1. Вычитание или функция ДНИ

Мне нужно посчитать кол-во дней между датами. Что самое логичное можно сделать в этом случае? Конечно же применить арифметику, точнее Вычитание.
Из более поздней даты вычесть раннюю дату.

Пример: 13.02.2021 - 10.02.2021. По итогу Excel отдаст вам количество дней - 3.
Обратите внимание, что результат включает в себя кол-во дней между этими датами + 1 день даты "До".

Также работает вычитание ячеек со значениями дат.
▫️В ячейке А2 значение 13.02.2021.
▫️В ячейке В2 значение 10.02.2021.
▫️Выражение = А2- В2 выдаст аналогичный результат - 3.

Если ошиблись и поменяли начало с концом - не страшно, система посчитает то же самое значение, но только с минусом.

Способ №2. Функция РАЗНДАТ

Получить кол-во дней между датами здорово, однако если наша задача на анализ данных куда интереснее: получить кол-во месяцев между датами или же узнать кол-во дней проходит между покупками внутри месяца, неважно какого года?

Здесь уже можно взять функцию РАЗНДАТ.
Функции нет в справочнике Excel, поэтому не пробуйте вызвать ее через мастера "Вставить функцию". Придется вводить по памяти или по моей инструкции😉

Синтаксис функции:
= РАЗНДАТ (ранняя дата; поздняя дата; аргумент)

1) По датам предельно понятно. Особенность в том, что если вы перепутали начало с концом, то формула выдаст ошибку ЧИСЛО. Будьте внимательны.

2) Аргумент. Указываем латиницей обязательно в кавычках.
▫️"d" - считаем кол-во дней между датами. Аналог вычитания дат.
▫️"m" - считаем кол-во целых месяцев между датами.
▫️"y" - считаем кол-во целых лет между датами.
▫️"yd" - считаем кол-во дней между датами без учета лет. Простой пример: 14.12.2021 - 13.12.2020 = 366 дней. А если применим аргумент "yd" с функцией РАЗНДАТ, то система посчитает 1 день. То есть Excel не смотрит на года, только даты с учетом месяцев.
▫️"md" - считаем кол-во дней между датами без учета лет и месяцев. Системе неважно какой год и месяц, вычитает только дни.
▫️"ym" - считаем кол-во месяцев между датами без учета лет. Тоже самое как и "yd" только месяцы.

Для наглядности прикладываю скриншот с применением различных аргументов для РАЗНДАТ.

Немного юмора для фанов Игры престолов😅
Всем отличной недели!