Тренер и разработчик обучающих курсов «Академия Excel» Дмитрий Якушев провел для Нетологии открытое занятие по Excel и рассказал о возможностях программы для маркетологов, аналитиков, экономистов и других специалистов.
Дмитрий Якушев
Тренер и разработчик обучающих курсов по Excel
Формулы для больших таблиц
Дано. Таблица с заявками, которые обработали менеджеры. В столбце А номер заявки, В — фамилия менеджера, С — определенное количество заявок, D — цена. Цифры в каждой строке обозначают одну заявку.
Задания для новичков:
- Подсчитать общее количество заявок в столбце С.
- Подсчитать все заявки менеджера Бумажкина М.
- Узнать, кто оформил заявку 4321.
Выбор формулы: = в ячейке → выбрать формулу курсором из выпадающего списка →
подтвердить Tab
Решения:
- Формула СЧЕТ(COUNT) → выделить столбец С.
- Формула СЧЕТЕСЛИ (COUNTIF) → ⨏ открыть диалоговое окно мастера → диапазон — весь столбец с менеджерами → критерий — Бумажкин М. → переместить курсор в поле диапазон при первом подсчете.
- Формула ВПР(VLOOKUP) → ⨏ открыть диалоговое окно мастера → искомое значение — ссылка на ячейку с номером заявки → таблица — выделить все столбцы таблицы → номер столбца — указать номер столбца с менеджерами → интервальный просмотр — 0 или ложь.
Задания для профи:
- Подсчитать, на какую сумму оформили заявок, если в столбце с ценами присутствуют значения #Н/Д, #ЗНАЧ! или другие не числовые.
- Узнать, кто оформил заявку 7095.
Решения:
- Формула СУММЕСЛИ(SUMIF) → ⨏ открыть диалоговое окно мастера → диапазон — выделить столбец с ценами → критерий — >0 → диапазон суммирования — выделить столбец с ценами.
- Формула ВПР(VLOOKUP) → ⨏ открыть диалоговое окно мастера → искомое значение — номер ячейки с номером заявки и дописать &»» → таблица — выделить всю таблицу → номер столбца — номер столбца с менеджерами → интервальный просмотр — 0 или ложь.
Графики
Задание 
: скопировать формат графика
Дано. График образец и таблица с данными, по которым нужно построить новый график. График другого типа, который вы построили по данным из таблицы и который нужно заменить.
Задание. По данным из таблицы построить такой же график, как на образце.
Решение. Выделить график-образец → Cntl+C → выделить новый график, который нужно заменить → главная → вставить → специальная вставка → форматы.
- Научитесь работать с любыми отчётами
- Узнаете, как сортировать и анализировать данные
- Сможете работать с формулами
Задание 
: сохранить график как шаблон
Дано. График, который вы построили, понадобится в дальнейшем.
Задача. Сохранить график так, чтобы использовать в дальнейшем и не тратить время на поиск.
Решение. Выделить график → ПКМ → сохранить как шаблон в папке со всеми шаблонами.
Чтобы найти сохранённый шаблон, перейдите во вкладку Просмотр всех диаграмм.
Задание 
: добавлять графики на общий график в один клик
Дано. Таблица продаж четырёх менеджеров за месяц. Значения в таблице существенно отличаются, сравнивать их на обычном графике неудобно.
Задача. Для удобного сравнения сделать общий график, включить отображение графиков каждого менеджера на общем в один клик.
Решение:
- Сделать вспомогательную таблицу. Выделить столбцы с данными менеджеров в основной таблице → создать вспомогательную таблицу → вставить данные из основной во вспомогательную
- Настроить отображение вкладки Разработчик. ПКМ на любой вкладке → настройка ленты → включить отображение вкладки Разработчик.
- Добавить строку с именами менеджеров и флажками. Вставить — элементы управления — флажок → над вспомогательной таблицей прописать имя первого менеджера, флажок подтянется автоматически → ПКМ на имени менеджера → формат элемента управления → в окне Связь с ячейкой указать ячейку с именем менеджера. Сделать также для других менеджеров.
- Настроить отображение графика. Формула ЕСЛИ → ⨏ открыть диалоговое окно мастера → Лог выражения — указать номер ячейки, где будет отображаться Истина или Ложь → ⨏ открыть диалоговое окно мастера → Значение_если_истина — данные по первому менеджеру → Значение_если_ложь — 0. Сделать также для всех менеджеров.
Условное форматирование
Дано. Таблица с данными о продажах четырех менеджеров.
Задачи:
- Подсветить в таблице столбец с данными об одном менеджере.
- Подсветить не только ячейку с ФИО, а всю строку.
Решения:
- Написать в ячейке фамилию менеджера → выделить столбец с фамилиями менеджеров → перейти на вкладку Главная → выбрать инструмент Условное форматирование → Правила выделения ячеек — равно → форматировать ячейки, которые равны — номер ячейки с фамилией менеджера.
- Выделить всю таблицу → главная → условное форматирование → создать правило → использовать формулу для определения ячеек → Формула =$B1=$H$7.
ЧИТАЙТЕ ТАКЖЕ
Вывод
Используйте специальные функции Excel в работе с большими таблицами. Это позволит сделать отчеты более наглядными и сэкономит время. Прописать одну формулу быстрее, чем настраивать все вручную.
Мнение автора и редакции может не совпадать. Хотите написать колонку для Нетологии? Читайте наши условия публикации. Чтобы быть в курсе всех новостей и читать новые статьи, присоединяйтесь к Телеграм-каналу Нетологии.