Поваренная книга Экселиста #1 - Преобразовываем ФИО

Допустим, что вы часто используете фильтр мужчины/женщины. Выбираем в фильтре пол «М», затем на вкладе Вид нажимаем Представления «Добавить» и в открывшемся окне вписываем имя «Мужчины», затем проделываем то же самое с фильтром «Женщины».

Большим плюсом Представлений является то, что можно сделать связку из наиболее часто используемых фильтров сохранив их отдельным названием, в результате при выборе Представления будут отображаться нужные столбцы, что сэкономит время повторной настройки фильтров:

Пример действия Представлений:

Для удобной работы с отчётными таблицами можно сделать Представления с подробной статистикой и по кварталам:

Выделяем столбцы с месяцами C-E, G-I и скрываем их правой кнопкой мыши - Скрыть или нажав Ctrl+0. Затем создаём Представление тем же способом или вписав новое имя в выпадающий список на панели быстрого доступа и нажав Enter.

Теперь можно быстро переключаться между кратким и подробным вариантом с помощью выпадающего списка в левом верхнем углу окна Excel.

Ещё одним плюсом является то, что визуально данный способ выглядит аккуратней, чем группировка или макрос:

Примечание:

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

- Представления несовместимы с «умными таблицами». Если в вашей книге на любом листе создана хоть одна «умная таблица», то представления перестают работать.

Примеры файлов можно скачать здесь

Для просмотра сперва нужно включить строку с представлениями

2. Настраиваем формат оси, назначив предельное значение 6000

3. Удаляем сетку и легенду:

4. В значке «Фильтры диаграммы» выбираем «Выбрать данные», затем «Изменить», где в открывшемся поле выделяем диапазон «Года»:

5. Кликаем мышкой по центру диаграммы и в открывшемся меню вставляем рисунок:

6. Ставим боковой зазор 0%

7. Выделяем оранжевый фон и заменяем его на белый:

8. Затем выделяем столбцы диаграммы и выбираем «Нет заливки»:

9. Определяем границы диаграммы выбрав белый цвет и ширину 3 пт

10. Меняем название диаграммы нажав знак «равно» и указав ячейку «Доходы»

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

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

=ЕСЛИ(D2>СЧЁТ($H$2:$H$10);"";ИНДЕКС($E$2:$E$10;

НАИМЕНЬШИЙ($H$2:$H$10;СТРОКА(E2)-1)))

=IF(D2>COUNT($H$2:$H$10);"";INDEX($E$2:$E$10;

SMALL($H$2:$H$10;ROW(E2)-1)))

Эта формула выводит очередное по номеру имя сотрудника (используя функцию НАИМЕНЬШИЙ) из списка или пустую ячейку, если имена свободных сотрудников уже кончились.

4. Далее создаём именованный диапазон сотрудников нажав Ctrl+F3 или на вкладке «Формулы» - «Диспетчер имён», где из столбца списка E2 зададим название «Имена», а в строке «Диапазон» введём формулу:

=СМЕЩ(Лист1!$I$2;0;0;СЧЁТЗ(Лист1!$I$2:$I$10)-СЧИТАТЬПУСТОТЫ(Лист1!I$2:I$10))

=OFFSET(Лист1!$I$2;0;0;COUNTA(Лист1!$I$2:$I$10)-

COUNTBLANK(Лист1!I$2:I$10))

5. Создадим выпадающий список выделив ячейки B2:B8 и на вкладке «Данные» - «Проверка данных» - «Список» - «Источник» =Имена

Готово) Теперь при выборе сотрудников их имена будут автоматически удаляться из выпадающего списка, оставляя только тех, кто ещё свободен.

После нажатия на ОК первый выпадающий список готов:

Дальше создадим второй (зависимый) выпадающий список, в котором будет отображаться содержимое списков в зависимости от выбранной категории. Аналогично предыдущему способу, в окне «Проверка данных» в поле «Источник» вводим формулу =ДВССЫЛ(E2), где E2 – адрес ячейки с первым выпадающим списком.

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

Минусы данного способа:

- Имена вторичных диапазонов должны совпадать с элементами первичного выпадающего списка. Т.е. если в нём есть текст с пробелами, то придётся их заменять на подчёркивания с помощью функции ПОДСТАВИТЬ, =ДВССЫЛ(ПОДСТАВИТЬ(E2;" ";"_"))

=INDIRECT(SUBSTITUTE(E2;" ";"_"))

- Надо вручную создавать много именованных диапазонов (если у нас много значений).

2. Для следующего способа нам нужна таблица, разбитая по категориям и видам:

Как и в предыдущем способе через Ctrl+F3 создаём именованные диапазоны каждой категории (без заголовков). Т.е. сначала «Мебель», затем «Техника» и так каждую по отдельности.

Теперь создаём первый выпадающий список по категориям. Для этого на вкладке «Данные» - «Проверка данных» - «Тип данных» выбираем «Список» и в поле «Источник» вводим названия диапазонов (Мебель;Техника)

Далее для второго зависимого списка тем же способом для ячейки «Вид» в поле «Источник» вводим формулу =ДВССЫЛ($B$14), а для «Тип» =ДВССЫЛ($B$15). В результате у нас получится следующее:

3. С помощью функций СМЕЩ и ПОИСКПОЗ

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

Далее нажимаем Ctrl+F3, где задаём имя диапазону D1:D3 (Товар). Затем на вкладке «Данные» - «Проверка данных» - «Тип данных» - «Список» и в строке «Источник» указываем =Товар или просто выделить ячейки D1:D3 (если они на том же листе, где список).

Чтобы выпадающий список автоматически пополнялся новыми данными из категории «Товар», открыв «Диспетчер имён» в строке диапазон вписываем формулу =СМЕЩ($D$1;0;0;СЧЁТЗ($D$1:$D$400);1) где $D$400 количество ячеек необходимое для образца выпадающего списка.

=OFFSET($D$1;0;0;COUNTA($D$1:$D$400);1)

=СМЕЩ($A$1;ПОИСКПОЗ($G$7;$A:$A;0)-1;1;СЧЁТЕСЛИ($A:$A;$G$7);1)

=OFFSET($A$1;MATCH($G$7;$A:$A;0)-1;1;COUNTIF($A:$A;$G$7);1)

Ссылки должны быть абсолютными (со знаками $). После нажатия Enter к формуле будут автоматически добавлены имена листов.

Функция СМЕЩ выдаёт ссылку на диапазон нужного размера, сдвинутый относительно исходной ячейки на заданное количество строк и столбцов. Вот так: =СМЕЩ(начальная_ячейка; сдвиг_вниз; сдвиг_вправо; размер_диапазона_в_строках; размер_диапазона_в_столбцах)

Таким образом:

- начальная ячейка – берём первую ячейку нашего списка, т.е. А1

- сдвиг_вниз – считает функция ПОИСКПОЗ, которая выдаёт порядковый номер ячейки с выбранным товаром (G2) в заданном диапазоне (столбце А)

- сдвиг_вправо = 1, т.к. мы хотим сослаться на Вид в соседнем столбце (В)

- размер_диапазона_в_строках – вычисляем с помощью функции СЧЁТЕСЛИ, которая умеет подсчитать количество встретившихся в списке (столбце А) нужных нам значений – товара (G2)

- размер_диапазона_в_столбцах = 1, т.к. нам нужен один столбец с Видом товара

В итоге у вас должно быть так:

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

В результате будет выходить такое сообщение:

Удалить выпадающий список можно выбрав в «Тип данных» – «Любое значение».

С помощью макроса (с возможностью добавления новых элементов)

Допустим, что у нас есть список с именами и таблица, куда их нужно вносить.

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

Выделяем весь список (ячейки A1:A7) и нажимаем Ctrl+T или Главная - Форматировать как таблицу, чтобы создать "умную" таблицу:

Теперь создадим именованный диапазон, указывающий на заполненные именами ячейки в нашем списке. Для этого выделим в списке уже только имена без шапки (ячейки A2:A7) и в левой части строки формул (там будет имя таблицы) введём имя для нашего диапазона (например Легенды):

После переименования таблицы нажимаем Enter, в результате слово «Легенды» исчезнет, но диапазон будет создан.

Проверить диапазон можно на вкладке Формулы - Диспетчер имен:

Таким образом, при дописывании новых имен к списку будет расширяться наша "умная" Таблица3, а за ней и наш именованный диапазон Легенды.

Теперь создаём выпадающий список в ячейке

Выделяем зелёные ячейки и жмём на вкладке "Данные" кнопку "Проверка данных".

Далее выбираем из выпадающего списка "Тип данных" позицию "Список" и вводим в строку" Источник" ссылку на созданный именованный диапазон =Легенды:

Чтобы Excel позволил нам в будущем ввести в список новые имена, снимем галочки на вкладках "Сообщение для ввода" и "Сообщение об ошибке" и жмём ОК. Выпадающий список готов!

Причем, если, например, вручную дописать новое имя в список в столбце А, то оно автоматически появится в выпадающем списке в любой из зелёных ячеек, поскольку имена берутся из динамического диапазона Легенды:

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

Щёлкаем правой кнопкой мыши по ярлыку нашего листа и выбираем "Просмотреть код". В модуле листа в редакторе Visual Basic, скопируйте код (не забудьте заменить название диапазона и ячейки ввода, также можно изменить вопрос при добавлении:

Private Sub Worksheet_Change(ByVal Target As Range)

Set p = Range("Легенды")

If Target.Cells.Count > 1 Then Exit Sub

If IsEmpty(Target) Then Exit Sub

If Not Intersect(Target, Range("D2:D11")) Is Nothing Then

If WorksheetFunction.CountIf(p, Target) = 0 Then

r = MsgBox("Добавить новое имя в справочник?", vbYesNo)

If r = vbYes Then p.Cells(p.Rows.Count + 1) = Target

End If

End If

End Sub

Теперь при попытке ввести новое имя в любую из зелёных ячеек Excel будет спрашивать:

При утвердительном ответе, имя автоматически будет добавлено к списку и в выпадающий список.

Формулы

Убрать галочку с "использовать функции GetPivotData для ссылок в сводной таблице"

ВАЖНО:
в источнике, от куда вы берете данные для сводной таблицы, данные должны быть так же отформатированны как там, куда вы делаете ВПР, то есть либо везде числа, либо текст.

PS: не забудьте обновить данные:

По умолчанию этот флажок всегда включён для всех ячеек.

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

Этот приём необходим т.к. в Excel команда «защитить диапазон», отсутствует, а есть только «защитить лист» и «защитить книгу», поэтому таким образом мы решаем эту проблему.

Далее для включения защиты текущего листа нажимаем кнопку «Защитить лист» на вкладке «Рецензирование». В открывшемся окне можно установить пароль (он необходим, чтобы кто попало не мог снять защиту) и при помощи списка флажков настроить исключения:

Например, необходимо оставить пользователям:

- возможность помечать ячейки маркером (но не менять их содержимое) –включаем флажок «Форматирование».

- использование фильтрации и/или сортировки (через Автофильтр) – включаем флажки «Использование автофильтра» и/или «Сортировка». Только перед включением защиты сперва создайте сам фильтр на вкладке «Данные».

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

Выборочная защита диапазонов листа для разных пользователей

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

Чтобы это сделать, выбираем на вкладке «Рецензирование» кнопку «Разрешить изменение диапазонов».

В появившемся окне нажимаем кнопку «Создать» и вводим имя диапазона, адреса ячеек и пароль к нему:

Повторяем эти действия для каждого из диапазонов разных пользователей, пока все они не окажутся в списке. Далее нажимаем кнопку «Защитить лист» (предыдущий пункт) и включаем защиту всего листа.

Теперь при попытке доступа к любому из защищенных диапазонов из списка, Excel будет требовать пароль именно для этого диапазона.

Защита листов книги

С помощью кнопки «Защитить книгу» на вкладке «Рецензирование» защищаем документ от удаления, переименования, перемещения листов в книге, изменения закрепленных областей (зафиксированной «шапки» таблиц и т.п.), возможности сворачивать/перемещать/изменять размеры окна книги внутри окна Excel:

Флажок «Структуру» отмечен по умолчанию, т.к. он подразумевает стандартный набор функций защиты листов от удаления, переименования, копирования.

Флажок «Окна» не обязательный и необходим для запрета пользователю сворачивать и/или изменять размеры окна книги внутри окна Excel или изменять закрепление областей.

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

Шифрование книги

В Excel имеется возможность зашифровать весь файл книги, используя алгоритм шифрования AES 128-bit. Взломать его напрямую невозможно, а автоматический подбор пароля осложнён низкой скоростью перебора вариантов, поэтому не забывайте свои пароли.

Данную защиту можно задать при сохранении книги, выбрав «Файл» – «Сохранить как» или клавишу F12, в окне сохранения нажать «Сервис» – «Общие параметры».

В появившемся окне можно ввести два разных пароля – на открытие файла (только чтение) и на изменение:

Если задать первый пароль (для открытия), то пользователь не сможет даже открыть файл для просмотра.

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

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

Кстати, в Microsoft Word этот способ тоже работает.

Суперскрытый лист

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

Чтобы улучшить защиту у нас должно быть открыто минимум два листа (т.к. один лист мы скроем), затем открываем редактор Visual Basic, на вкладке «Разработчик» или нажатием Alt+F11, выбираем «Visual Basic».

Нажатием Project Explorer или Ctrl+R откроется меню

В верхней части выделяем наш Лист1, а в нижней части находим свойство Visible и делаем его xlSheetVeryHidden.

Можно переименовать Лист2 на какое-нибудь слово, чтобы не палиться о наличии Листа1 и теперь узнать о его существовании можно только в редакторе Visual Basic, в других местах он отображаться не будет.

Берегите свои данные, чаще сохраняйтесь Shift+F12 и вы никогда ничего не потеряете.