Возможности Excel для новичков, о которых не знают профи08.02.2019 13:35

Тренер и разработчик обучающих курсов Академия Excel Дмитрий Якушев провел для Нетологии открытое занятие по Excel и рассказал о возможностях программы для маркетологов, аналитиков, экономистов и других специалистов.

Обучение в онлайн-университете: курс «Power BI: анаоиз и визуализация данных без программирования»

Формулы для больших таблиц

Дано. Таблица с заявками, которые обработали менеджеры. В столбце А номер заявки, В — фамилия менеджера, С — определенное количество заявок, D — цена. Цифры в каждой строке обозначают одну заявку.


Таблица с заявками менеджеров

Задания для новичков:

  1. Подсчитать общее количество заявок в столбце С.
  2. Подсчитать все заявки менеджера Бумажкина М.
  3. Узнать, кто оформил заявку 4321.

Выбор формулы: = в ячейке выбрать формулу курсором из выпадающего списка подтвердить Tab

Решения:

  1. Формула СЧЕТ (COUNT) выделить столбец С.
  2. Формула СЧЕТЕСЛИ (COUNTIF) ⨏ открыть диалоговое окно мастера диапазон — весь столбец с менеджерами критерий — Бумажкин М. переместить курсор в поле диапазон при первом подсчете.
  3. Формула ВПР (VLOOKUP) ⨏ открыть диалоговое окно мастера искомое значение — ссылка на ячейку с номером заявки таблица — выделить все столбцы таблицы номер столбца — указать номер столбца с менеджерами интервальный просмотр — 0 или ложь.

Задания для профи:

  1. Подсчитать, на какую сумму оформили заявок, если в столбце с ценами присутствуют значения #Н/Д, #ЗНАЧ! или другие не числовые.
  2. Узнать, кто оформил заявку 7095.

Решения:

  1. Формула СУММЕСЛИ (SUMIF) ⨏ открыть диалоговое окно мастера диапазон — выделить столбец с ценами критерий — 0 диапазон суммирования — выделить столбец с ценами.
  2. Формула ВПР (VLOOKUP) ⨏ открыть диалоговое окно мастера искомое значение — номер ячейки с номером заявки и дописать &» таблица — выделить всю таблицу номер столбца — номер столбца с менеджерами интервальный просмотр — 0 или ложь.


Результаты подсчетов в столбце Н

Графики

Задание 1: скопировать формат графика

Дано. График образец и таблица с данными, по которым нужно построить новый график. График другого типа, который вы построили по данным из таблицы и который нужно заменить.


Слева — график-образец, справа — неудачный график, который нужно заменить

Задание. По данным из таблицы построить такой же график, как на образце.

Решение. Выделить график-образец Cntl+C выделить новый график, который нужно заменить главная вставить специальная вставка форматы.


График-образец и построенный по нему новый график

Задание 2: сохранить график как шаблон

Дано. График, который вы построили, понадобится в дальнейшем.

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

Решение. Выделить график ПКМ сохранить как шаблон в папке со всеми шаблонами.

Чтобы найти сохраненный шаблон, перейдите во вкладку Просмотр всех диаграмм.

Задание 3: добавлять графики на общий график в один клик

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

Задача. Для удобного сравнения сделать общий график, включить отображение графиков каждого менеджера на общем в один клик.

Решение:

  1. Сделать вспомогательную таблицу. Выделить столбцы с данными менеджеров в основной таблице создать вспомогательную таблицу вставить данные из основной во вспомогательную
  2. Настроить отображение вкладки Разработчик. ПКМ на любой вкладке настройка ленты включить отображение вкладки Разработчик.
  3. Добавить строку с именами менеджеров и флажками. Вставить — элементы управления — флажок над вспомогательной таблицей прописать имя первого менеджера, флажок подтянется автоматически ПКМ на имени менеджера формат элемента управления в окне Связь с ячейкой указать ячейку с именем менеджера. Сделать так же для других менеджеров.
  4. Настроить отображение графика. Формула ЕСЛИ ⨏ открыть диалоговое окно мастера Лог выражения — указать номер ячейки, где будет отображаться Истина или Ложь ⨏ открыть диалоговое окно мастера Значение_если_истина — данные по первому менеджеру Значение_если_ложь — 0. Сделать так же для всех менеджеров.


Основная, вспомогательная таблица и графики менеджеров

Условное форматирование

Дано. Таблица с данными о продажах четырех менеджеров.

Задачи:

  1. Подсветить в таблице столбец с данными об одном менеджере.
  2. Подсветить не только ячейку с ФИО, а всю строку.


Таблица с данными о продажах менеджеров

Решения:

  1. Написать в ячейке фамилию менеджера выделить столбец с фамилиями менеджеров перейти на вкладку Главная выбрать инструмент Условное форматирование Правила выделения ячеек — равно форматировать ячейки, которые равны — номер ячейки с фамилией менеджера.
  2. Выделить всю таблицу главная условное форматирование создать правило использовать формулу для определения ячеек Формула =$B1=$H$7.

Вывод

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

Читать еще:»5 приемов, как улучшить диаграммы в Excel»

Мнение автора и редакции может не совпадать. Хотите написать колонку для «Нетологии»? Читайте наши условия публикации. Чтобы быть в курсе всех новостей и читать новые статьи, присоединяйтесь к Телеграм-каналу Нетологии.

Полный текст статьи читайте на Нетология