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