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

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

Разобраться

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

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

Дмитрий Якушев

Тренер и разработчик обучающих курсов по Excel

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

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

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

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

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

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

Решения:

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

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

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

Решения:

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

Графики

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

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

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

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

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

Возможности Excel для новичков, о которых не знают профи
График-образец и построенный по нему новый график

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

Excel

Узнать больше

  • Научитесь работать с любыми отчётами
  • Узнаете, как сортировать и анализировать данные
  • Сможете работать с формулами

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

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

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

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

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

Задание Возможности Excel для новичков, о которых не знают профи: добавлять графики на общий график в один клик

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

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

Решение:

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

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

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

Задачи:

  • Подсветить в таблице столбец с данными об одном менеджере.
  • Подсветить не только ячейку с ФИО, а всю строку.
Возможности Excel для новичков, о которых не знают профи
Таблица с данными о продажах менеджеров

Решения:

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

Вывод

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


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

Оцените статью

Средняя оценка 4 / 5. Всего проголосовало 28