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

08.02.2019
4867
Подпишитесь, чтобы получать новые статьи на почту

Тренер и разработчик обучающих курсов «Академия 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»


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

Дмитрий Якушев
Тренер и разработчик обучающих курсов по Excel
Университет интернет-профессий