Excel для будущего аналитика: от нуля до уверенного пользователя

Основы интерфейса и базовые вычисления: от навигации до первых формул (SUM, AVERAGE)

Добро пожаловать в курс «Excel для будущего аналитика». Если вы читаете это, значит, вы решили превратить Excel из «той сложной зеленой программы» в свой главный рабочий инструмент. Мы не будем учить сухую теорию. Мы будем готовиться к реальной работе джуниор-аналитика.

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

Диагностика: Ваш стартовый уровень

Прежде чем мы начнем, честно ответьте на 5 вопросов (для себя), чтобы понять точку старта:

Знаете ли вы, чем отличается ячейка A1 от 1A?

Какой знак нужно поставить в ячейку первым, чтобы Excel начал считать?

Использовали ли вы когда-нибудь сочетание клавиш Ctrl + Стрелки?

Знаете ли вы, как называется функция суммы в русской версии Excel?

Можете ли вы мгновенно выделить таблицу из 10 000 строк без мышки?

Если вы ответили «Нет» хотя бы на один вопрос — этот урок для вас.

Часть 1. Интерфейс: Ваша кабина пилота

Excel — это не просто таблица, это пульт управления данными. Чтобы управлять эффективно, нужно знать, где лежат рычаги.

!Схема основных элементов интерфейса Excel: Лента, Строка формул и Сетка ячеек.

Ключевые элементы:

* Лента (Ribbon): Верхняя панель с кнопками. Это ваш ящик с инструментами. * Строка формул (Formula Bar): Длинная белая полоса над буквами столбцов. Это «рентген» ячейки — там видно истинное содержимое (формулу), а не только результат. * Адрес ячейки: Пересечение столбца (буквы) и строки (цифры). Например, B2.

> Представьте, что Excel — это огромный комод. Ячейка — это один ящик. Лист — это одна комната с комодами. Книга (файл) — это всё здание. Чтобы найти носки, вы не говорите «вон там», вы говорите: «Комната 1, Комод B, Ящик 2».

Микро-практика №1 (3 минуты)

Откройте пустой файл Excel.

Кликните мышкой в любую ячейку.

Посмотрите в левый верхний угол (слева от строки формул) — там написан адрес (например, D5).

Попробуйте найти ячейку XFD1048576. Сложно? Сейчас научимся делать это мгновенно.

Часть 2. Навигация ниндзя: Забудьте про колесико мыши

Аналитики редко пользуются мышкой для навигации. Скроллить таблицу из 50 000 строк колесиком — это как идти пешком из Москвы во Владивосток. Долго и больно.

Главные горячие клавиши:

* Ctrl + Стрелка вниз: Телепортация к последней заполненной ячейке. * Ctrl + Стрелка вправо: Телепортация к правому краю таблицы. * Ctrl + Home: Возврат в начало (ячейка A1).

> Аналогия: Использование мыши — это подъем по лестнице ступенька за ступенькой. Использование Ctrl + Стрелки — это скоростной лифт, который везет вас сразу на нужный этаж.

Микро-практика №2 (Музыкальный плейлист)

Давайте создадим наш учебный датасет. Мы не будем анализировать скучные продажи болтов. Мы будем анализировать музыкальный плейлист.

В ячейку A1 напишите: Трек

В B1: Исполнитель

В C1: Прослушивания

В D1: Длительность (мин)

Заполните 3-4 строчки любимыми песнями. В колонке «Прослушивания» пишите числа без пробелов (например, 1500000, а не 1 500 000).

Теперь встаньте на ячейку A1, зажмите Ctrl и нажмите Стрелку вниз. Курсор прыгнул на последнюю песню? Отлично.

Часть 3. Базовая арифметика: Excel как калькулятор

Любая формула в Excel начинается со знака равенства =. Если вы напишете 2 + 2, Excel покажет текст «2 + 2». Если напишете =2+2, Excel покажет 4.

Операторы: * + (плюс) * - (минус) (умножение — звездочка) * / (деление — слэш)

> Аналогия: Формула — это рецепт. Вы говорите повару (Excel): «Возьми содержимое ячейки A (яйца), добавь содержимое ячейки B (мука) и смешай». Если ингредиенты в ячейках изменятся, результат (пирог) тоже изменится автоматически.

Микро-практика №3

Добавим аналитики в наш плейлист. Узнаем, сколько времени мы потратим, если послушаем одну песню 100 раз.

В ячейке E1 напишите заголовок: Время (100 раз).

В ячейке E2 введите формулу: =D2*100 (где D2 — это длительность вашей первой песни).

Нажмите Enter.

Вы увидите результат. Теперь измените длительность в ячейке D2. Результат в E2 пересчитался сам? Поздравляю, вы создали динамическую модель.

Часть 4. Первые функции: SUM и AVERAGE

Складывать ячейки вручную (=A1+A2+A3...) долго. Для этого есть функции — заранее заготовленные программы внутри Excel.

Функция СУММ (SUM)

Складывает все числа в выбранном диапазоне.

* Синтаксис: =SUM(число1, [число2], ...) или =SUM(диапазон) * На русском: =СУММ()

Функция СРЗНАЧ (AVERAGE)

Считает среднее арифметическое. Давайте вспомним математику, чтобы понимать, что происходит «под капотом».

Математическая формула среднего арифметического выглядит так:

Где: * — среднее значение (то, что мы ищем). * — количество элементов (сколько всего песен). * — знак суммы (сложение всех чисел). * — значение каждого отдельного элемента (длительность каждой песни).

В Excel вам не нужно считать или сумму вручную. Функция делает это за вас.

* Синтаксис: =AVERAGE(диапазон) * На русском: =СРЗНАЧ()

> Аналогия: =SUM — это кнопка «Итого» на кассе в супермаркете. Она пробивает всё сразу. =AVERAGE — это попытка понять «средний чек» покупателя.

Микро-практика №4 (Финал урока)

Вернемся к плейлисту.

Под колонкой «Прослушивания» (допустим, в ячейке C10) введите: =SUM(.

Выделите мышкой все ячейки с прослушиваниями выше.

Закройте скобку ) и нажмите Enter.

Рядом посчитайте среднюю длительность трека, используя =AVERAGE() (или =СРЗНАЧ()).

Теперь вы знаете общее количество стримов и среднюю длину хита.

Чек-лист прогресса (Модуль 1)

Отметьте, что вы теперь умеете: * [ ] Различать Ленту, Строку формул и Ячейки. * [ ] Использовать Ctrl + Стрелки для навигации. * [ ] Начинать любую формулу со знака =. [ ] Использовать базовые операторы (+, -, , /). * [ ] Применять функции =SUM() и =AVERAGE().

Ресурсы для самостоятельного изучения

* ExcelJet (англ.) — лучшая энциклопедия формул с примерами. * Планета Excel (рус.) — форум и статьи для поиска решений. * Открытые датасеты — Kaggle (раздел Datasets) для будущих проектов.

В следующем уроке мы разберем логические функции (IF) и узнаем, как закрепить ячейку, чтобы формула не «уползала» при копировании.

Логика и адресация: функции IF (ЕСЛИ) и работа с абсолютными ссылками

Приветствую, будущий аналитик! В прошлом модуле мы научились считать сумму и среднее значение. Это круто, но жизнь редко бывает линейной. Обычно в работе (и в жизни) всё зависит от условий: «Если пойдет дождь, я возьму зонт, иначе пойду в футболке».

Сегодня мы научим Excel принимать решения за нас и разберемся с одной из самых частых ошибок новичков — «уползающими» формулами.

Часть 1. Функция IF (ЕСЛИ): Искусственный интеллект на минималках

Функция =IF() (ЕСЛИ) — это первый шаг к программированию. Она позволяет ячейке менять свое содержимое в зависимости от ситуации.

Как это работает?

Представьте фейс-контроль в клубе. Охранник смотрит на возраст гостя. У него есть простая инструкция:

> Если возраст >= 18, то «Проходи». Иначе — «Иди домой».

В языке Excel это записывается так:

Где: * Результат — то, что появится в ячейке. * Значение 1 — то, что мы увидим, если условие выполнено. * Значение 2 — то, что мы увидим, если условие НЕ выполнено.

Синтаксис: =IF(логическое_выражение, значение_если_истина, значение_если_ложь) На русском: =ЕСЛИ(логическое_выражение; значение_если_истина; значение_если_ложь)

Микро-практика №1: Анализ плейлиста

Вернемся к нашему музыкальному датасету из прошлого урока. Допустим, у нас есть колонка C с количеством прослушиваний.

Создайте новую колонку F с заголовком Статус.

Мы решили: если у трека больше 1 000 000 прослушиваний — это «Хит». Если меньше — «Ниша».

В ячейке F2 пишем формулу:

=IF(C2>1000000, "Хит", "Ниша") (Обратите внимание: текст в формулах всегда пишется в кавычках!)

Нажмите Enter и протяните формулу вниз (двойной клик по правому нижнему углу ячейки).

Теперь Excel сам промаркировал все ваши треки. Если вы измените число прослушиваний, статус обновится автоматически.

Часть 2. Абсолютные ссылки: Как прибить ячейку гвоздями

Это тема, на которой «сыпятся» 80% новичков. Внимательно следите за руками.

Проблема «уползания»

Представьте, что вы хотите перевести цены из рублей в доллары. У вас есть список цен в столбце A, а курс доллара записан в одной конкретной ячейке — скажем, D1.

Вы пишете формулу: =A2/D1. Тянете её вниз... И видите ошибки #DIV/0! или странные числа.

Почему? Excel мыслит относительно. Когда вы тянете формулу вниз, он думает: * В первой строке: «Взять ячейку слева и поделить на ячейку справа-вверху» (A2/D1). * Во второй строке: «Взять ячейку слева и поделить на ячейку справа-вверху» (A3/D2).

Но в D2 у вас пусто! Excel «спустился» вниз и потерял курс доллара.

!Схема, показывающая проблему относительных ссылок при копировании формулы.

Решение: Знак доллара . Это «якорь» или «гвоздь», который фиксирует адрес.

* A1 — Относительная ссылка. «Ячейка слева от меня». При копировании меняется. * 1 — Абсолютная ссылка. «Конкретно ячейка A1, где бы я ни находился». При копировании НЕ меняется.

> Аналогия: Относительная ссылка — это навигатор, который говорит «поверните направо через 100 метров» (зависит от того, где вы едете). Абсолютная ссылка — это GPS-координаты точки на карте. Они неизменны.

Горячая клавиша F4

Вам не нужно печатать доллары вручную. Поставьте курсор на адрес ячейки в формуле (например, на D1) и нажмите клавишу F4 (на ноутбуках иногда Fn + F4). Excel сам расставит знаки: 1.

Микро-практика №2: Конвертер валют

В нашем плейлисте предположим, что за одно прослушивание платят роялти.

В ячейку H1 напишите: Ставка за стрим.

В ячейку H2 введите: 0.003 (это ).

В ячейке G2 введите формулу умножения прослушиваний на ставку: =C2*H2.

СТОП! Прежде чем нажать Enter, поставьте курсор на H2 и нажмите F4. Формула должна стать: =C2*2.

Теперь жмите Enter и копируйте вниз.

Проверьте любую нижнюю ячейку. Ссылка на прослушивания (C10, C11...) меняется, а ссылка на ставку (2) стоит как вкопанная. Вы великолепны.

Часть 3. Смешанные ссылки (Для продвинутых)

Иногда нужно закрепить только строку или только столбец. Знак A1 — Закреплен столбец А, но строка может меняться (ездим по лифту вверх-вниз, но только в первом подъезде). * A$1 — Закреплена строка 1, но столбец может меняться (бегаем по первому этажу влево-вправо).

Это часто нужно для условного форматирования, которое мы разберем в Модуле 6.

Мини-проект: Трекер привычек (Начало)

Давайте начнем создавать полезный инструмент, который будем развивать весь курс.

Создайте новый лист, назовите его Tracker.

В столбце A (начиная с A2) напишите привычки: «Спорт», «Чтение», «Вода».

В строке 1 (начиная с B1) напишите даты: 1, 2, 3... (растяните до 7).

Представим, что если вы выполнили привычку, вы ставите в ячейку букву «x».

В столбце I (после дат) сделаем подсчет прогресса.

Нам нужно посчитать, сколько раз мы выполнили привычку, но пока мы умеем только IF. Давайте сделаем индикатор «Молодец».

В ячейке I2 напишите: =IF(B2=

Поиск данных: мастерство функций VLOOKUP (ВПР), XLOOKUP и INDEX/MATCH

Добро пожаловать в четвертый модуль! Мы уже умеем считать (SUM), усреднять (AVERAGE) и принимать решения (IF). Но настоящая сила аналитика — это умение связывать данные из разных источников.

Представьте ситуацию: у вас есть таблица с продажами, где указаны только ID товаров (например, «A-101»). А названия товаров и их цены лежат в совершенно другой таблице. Копировать вручную? Ни за что. Сегодня мы научим Excel делать это за нас.

Часть 1. VLOOKUP (ВПР): Старый добрый друг

Функция VLOOKUP (Vertical Lookup) или ВПР (Вертикальный ПРосмотр) — это, пожалуй, самая известная функция Excel. Ее спрашивают на 9 из 10 собеседований.

Как это работает?

> Аналогия: Представьте, что вы пришли в кофейню. Вы смотрите в меню (таблица). Вы ищете название «Капучино» (искомое значение) в левом столбце. Находите его, а затем ведете пальцем вправо, чтобы узнать цену (результат).

Синтаксис: =VLOOKUP(что_ищем, где_ищем, номер_столбца, тип_совпадения) На русском: =ВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр)

Важные правила:

Что ищем: Значение должно быть в первом (самом левом) столбце таблицы, в которой ищем.

Тип совпадения: Почти всегда ставьте 0 или FALSE (ЛОЖЬ). Это означает «точное совпадение». Если поставить 1 или забыть этот аргумент, Excel может найти «что-то похожее», и это приведет к ошибкам.

!Схема движения VLOOKUP: поиск вниз по первому столбцу, затем движение вправо до нужной ячейки.

Микро-практика №1 (3 минуты)

Давайте создадим базу данных фильмов.

На листе Excel в ячейках A1:B4 создайте справочник:

* A1: ID, B1: Фильм * A2: 1, B2: Матрица * A3: 2, B3: Начало * A4: 3, B4: Интерстеллар

В ячейке D1 напишите ID, который хотим найти: 2.

В ячейке E1 введите формулу:

=VLOOKUP(D1, A1:B4, 2, 0)

Нажмите Enter. Excel должен вернуть «Начало».

Попробуйте поменять число в D1 на 3. Название фильма изменилось? Поздравляю, вы связали данные!

Часть 2. XLOOKUP (ПРОСМОТРX): Спорткар в мире формул

Если у вас Excel 2021 или Office 365, забудьте про VLOOKUP. Функция XLOOKUP (ПРОСМОТРX) — это её современная, улучшенная версия.

Почему она круче? * Не нужно считать номер столбца (1, 2, 3...). Умеет искать влево* (VLOOKUP умеет только вправо). * По умолчанию ищет точное совпадение (не нужно писать 0).

Синтаксис: =XLOOKUP(что_ищем, столбец_где_ищем, столбец_откуда_берем_результат) На русском: =ПРОСМОТРX(искомое_значение; просматриваемый_массив; возвращаемый_массив)

> Аналогия: Если VLOOKUP — это поиск книги в библиотеке по каталожному номеру (строго по порядку), то XLOOKUP — это спросить библиотекаря: «Где лежит эта книга?», и он сразу укажет на полку, неважно, в каком углу комнаты она находится.

Микро-практика №2

Используем ту же таблицу с фильмами.

В ячейке D2 напишите ID: 3.

В ячейке E2 введите:

=XLOOKUP(D2, A:A, B:B) (Мы ищем D2 в столбце A и возвращаем значение из столбца B).

Это намного быстрее и интуитивно понятнее.

Часть 3. INDEX/MATCH (ИНДЕКС/ПОИСКПОЗ): Снайперская винтовка

Если у вас старый Excel и нет XLOOKUP, но VLOOKUP не справляется (например, нужно искать влево), используют связку двух функций: INDEX и MATCH.

Это работает как система координат в «Морском бое».

Математически это можно представить так:

Где: * — значение, которое мы хотим получить. * — наша таблица (массив данных). * — номер строки (находит функция MATCH). * — номер столбца (обычно задаем вручную или тоже через MATCH).

Разберем по винтикам

MATCH (ПОИСКПОЗ): Возвращает номер позиции элемента. Она не дает само значение, она говорит: «То, что ты ищешь, находится в 5-й строке».

* =MATCH(что_ищем, где_ищем, 0)

INDEX (ИНДЕКС): Возвращает значение по координатам.

* =INDEX(диапазон_результатов, номер_строки)

Собираем вместе: =INDEX(столбец_с_ценами, MATCH(товар, столбец_с_товарами, 0))

> Аналогия: MATCH — это разведчик, который находит координаты цели. INDEX — это артиллерия, которая бьет точно по этим координатам.

Мини-проект: Анализ кассовых сборов (Open Dataset)

Давайте поработаем с данными, похожими на реальные. Представьте, что вы аналитик в киностудии.

Задача: У вас есть список «Топ-5 любимых фильмов шефа» (только названия), и вам нужно быстро подтянуть к ним год выпуска и рейтинг из общей базы данных.

Подготовка:

Создайте лист Database. Заполните его данными (5-10 строк): Название, Год, Рейтинг, Сборы.

Создайте лист Report. В столбце A напишите 3 любых названия фильмов из вашей базы.

Выполнение:

На листе Report в ячейке B2 (Год) используем VLOOKUP:

=VLOOKUP(A2, Database!A:D, 2, 0) (Ищем название из A2 в таблице Database, берем 2-й столбец, точное совпадение).

В ячейке C2 (Рейтинг) попробуем XLOOKUP (или INDEX/MATCH, если старый Excel):

=XLOOKUP(A2, Database!A:A, Database!C:C)

Протяните формулы вниз.

Теперь, если вы измените данные в Database (например, исправите ошибку в рейтинге), ваш Report обновится автоматически. Это и есть автоматизация отчетности!

Частые ошибки (Troubleshooting)

* #N/A (#Н/Д): Самая частая ошибка. Означает «Not Available». Excel не нашел то, что вы искали. Проверьте, нет ли лишних пробелов в названии (например, «Матрица » вместо «Матрица»). * #REF! (#ССЫЛКА!): Вы попросили VLOOKUP вернуть данные из 5-го столбца, а в таблице их всего 4.

Чек-лист прогресса (Модуль 4)

Отметьте, что вы теперь умеете: * [ ] Понимать принцип работы VLOOKUP (поиск вниз и вправо). * [ ] Использовать 4-й аргумент 0 для точного поиска. * [ ] Применять XLOOKUP для быстрого поиска без подсчета столбцов. * [ ] Понимать логику связки INDEX/MATCH (координаты). * [ ] Объединять данные из двух разных таблицу в одну.

В следующем модуле мы перейдем к анализу данных: научимся крутить Сводные таблицы (Pivot Tables), которые делают 80% работы аналитика за секунды.

Анализ данных: сводные таблицы, фильтры и срезы для обработки массивов

Добро пожаловать в пятый модуль курса «Excel для будущего аналитика». Мы уже прошли большой путь: научились ориентироваться в интерфейсе, писать формулы и связывать таблицы через VLOOKUP. Вы уже знаете Excel лучше, чем 70% офисных сотрудников.

Но есть проблема. Формулы прекрасны, когда у вас 100 строк. А что, если строк 500 000? Если начальник просит: «Покажи продажи по регионам, потом по менеджерам, а потом только за март»? Писать SUMIF для каждого случая — это путь к выгоранию.

Сегодня мы освоим Сводные таблицы (Pivot Tables). Это инструмент, который делает аналитика аналитиком. Он позволяет превратить миллион строк хаоса в понятный отчет за 10 секунд.

Часть 1. Что такое Сводная таблица?

Представьте, что у вас есть огромная копилка, в которую свалили тысячи монет разных стран и номиналов. Чтобы посчитать общую сумму, вам нужно сначала разобрать их на кучки: «Евро», «Доллары», «Рубли», а внутри каждой кучки — по номиналу.

Сводная таблица делает именно это. Она берет «кучу» данных и автоматически раскладывает их по «стопочкам», которые вы задаете.

> Аналогия: Исходная таблица — это ингредиенты в холодильнике (яйца, мука, молоко, мясо). Сводная таблица — это готовое блюдо. Вы можете собрать из тех же ингредиентов пиццу, а через секунду пересобрать их в лазанью, не меняя сами продукты.

Подготовка данных: Правило гигиены

Сводная таблица — инструмент капризный. Перед созданием убедитесь, что ваш датасет (исходная таблица) соответствует трем правилам:

Заголовки: У каждого столбца должно быть уникальное название в первой строке.

Без пустот: В таблице не должно быть полностью пустых строк или столбцов, разрывающих данные.

Плоская структура: Никаких объединенных ячеек в шапке.

Часть 2. Создание первой сводной: Магия в 4 клика

Давайте сразу к делу. Забудьте про сложные меню. Путь аналитика прост:

Кликните в любую ячейку вашей таблицы с данными.

Перейдите на вкладку Вставка (Insert).

Нажмите кнопку Сводная таблица (PivotTable) — самая первая слева.

Нажмите ОК.

Excel создаст новый лист. Слева будет пустое место для отчета, а справа — панель управления Поля сводной таблицы.

!Схема четырех областей сводной таблицы: Фильтры, Колонки, Строки и Значения.

Анатомия конструктора

Вся магия происходит в четырех окошках справа внизу:

Строки (Rows): То, что будет написано слева (названия категорий). Например, Имена менеджеров*. Столбцы (Columns): То, что будет в шапке (разбивка по горизонтали). Например, Месяцы*. Значения (Values): Самое главное. То, что мы считаем (деньги, штуки). Сюда кидаем Сумму продаж*. Фильтры (Filters): Глобальный отбор. Например, Год*.

Микро-практика №1: Кофейный барон

Представим, что мы анализируем продажи сети кофеен. Создайте в Excel небольшую табличку (или представьте её):

| Дата | Напиток | Размер | Цена | | :--- | :--- | :--- | :--- | | 01.01 | Латте | Большой | 350 | | 01.01 | Эспрессо | Мал | 150 | | 02.01 | Латте | Средний | 250 | | 02.01 | Капучино | Большой | 300 |

Задание:

Создайте сводную таблицу по этой базе.

Перетащите поле Напиток в область Строки.

Перетащите поле Цена в область Значения.

Что произошло? Excel автоматически нашел все уникальные напитки (Латте, Эспрессо, Капучино) и просуммировал их продажи. Вы только что сделали отчет, на который вручную ушло бы 10 минут, за 3 секунды.

Часть 3. Не только сумма: Настройка вычислений

По умолчанию, если вы кидаете числа в Значения, Excel их складывает (SUM). Но аналитику часто нужно другое.

Кейс: Нам нужно узнать не сколько денег принес Латте, а сколько чеков (транзакций) было пробито.

Решение:

В области Значения кликните правой кнопкой мыши (или стрелочкой) по полю «Сумма по полю Цена».

Выберите Параметры полей значений (Value Field Settings).

Выберите Количество (Count).

Теперь вы видите популярность напитков в штуках.

> Совет: Если вы перетащите текстовое поле (например, «Имя клиента») в Значения, Excel автоматически применит Количество, так как текст нельзя сложить.

Часть 4. Срезы (Slicers): Ваш пульт управления

Обычные фильтры (галочки в заголовках) — это прошлый век. Они скрыты и неудобны. Профессионалы используют Срезы.

Срез — это визуальные кнопки, которые плавают поверх таблицы. Нажал кнопку «Латте» — таблица перестроилась. Нажал «Эспрессо» — перестроилась снова.

Как добавить?

Кликните по любой ячейке готовой сводной таблицы.

Вкладка Анализ сводной таблицы (PivotTable Analyze) -> Вставить срез (Insert Slicer).

Выберите поле, по которому хотите фильтровать (например, Размер).

Появится красивое меню с кнопками.

Микро-практика №2: Интерактивность

Вернемся к нашему кофейному отчету.

Добавьте срез по полю Размер.

Понажимайте кнопки «Большой», «Средний».

Заметьте, как цифры в таблице меняются мгновенно.

Если вы добавите диаграмму (об этом в следующем модуле), срез будет управлять и ей тоже. Так создаются Дашборды.

Мини-проект: Анализ киноиндустрии (Open Dataset)

Помните наш датасет с фильмами из прошлого урока? Давайте выжмем из него инсайты. Представьте, что у вас таблица из 100 фильмов с колонками: Жанр, Год, Рейтинг, Сборы ($).

Задача: Узнать, какой жанр самый прибыльный, и как менялись сборы по годам.

Алгоритм:

Загрузка: Выделите таблицу -> Вставка -> Сводная таблица.

Анализ жанров:

* Жанр -> в Строки. * Сборы -> в Значения. Результат:* Видим, что Боевики приносят больше всего денег.

Детализация:

* Перетащите Год в Столбцы. * Теперь у вас огромная матрица: Жанры по вертикали, Годы по горизонтали.

Фокусировка:

* Добавьте Срез по полю Рейтинг (или создайте группу рейтингов). * Оставьте только фильмы с высоким рейтингом.

Вы только что провели многомерный анализ данных, не написав ни одной формулы.

Часть 5. Главная ловушка новичка: Кнопка «Обновить»

Это критически важно. Сводные таблицы НЕ обновляются автоматически.

Если вы исправили цену Латте в исходной таблице с 350 на 400, в сводной таблице останется старая сумма.

Что делать? * Кликните правой кнопкой мыши по сводной таблице -> Обновить (Refresh). * Или сочетание клавиш Alt + F5.

> Аналогия: Сводная таблица — это фотография ваших данных. Если вы переоделись (изменили данные), на старой фотографии это не появится, пока вы не сделаете новый снимок (не нажмете Обновить).

Чек-лист прогресса (Модуль 5)

Отметьте, что вы теперь умеете: * [ ] Понимать отличие исходной таблицы от сводной. * [ ] Создавать сводную таблицу (Вставка -> Сводная таблица). * [ ] Распределять поля по 4 областям (Строки, Столбцы, Значения, Фильтры). * [ ] Менять функцию агрегации (Сумма -> Количество -> Среднее). * [ ] Использовать Срезы для фильтрации. * [ ] Обновлять таблицу после изменения данных.

Ресурсы для самостоятельного изучения

* ExcelJet Pivot Tables (англ.) — отличные примеры настроек. * YouTube: Николай Павлов (Планета Excel) — видео про трюки со сводными. * Kaggle Datasets — скачайте датасет "Supermarket Sales" и потренируйтесь строить отчеты.

В следующем, финальном модуле мы займемся визуализацией: превратим наши скучные таблицы в красивые графики и соберем полноценный дашборд, который не стыдно показать директору.

Визуализация, автоматизация через Power Query и итоговый аналитический проект

Поздравляю! Вы добрались до финала. Вы уже умеете собирать данные, писать сложные формулы и крутить сводные таблицы. Вы уже аналитик. Но есть один нюанс: ваши таблицы, какими бы умными они ни были, скучны для директора.

Директору не нужна таблица на 10 000 строк. Ему нужна картинка, на которой видно: «Мы растем» или «У нас проблемы».

В этом финальном модуле мы научимся «продавать» свои данные через красивые дашборды, автоматизируем рутину с помощью мощнейшего инструмента Power Query и соберем итоговый проект, который можно смело класть в портфолио.

Часть 1. Визуализация: От таблицы к истории

Диаграмма — это не просто украшение. Это способ мгновенной передачи информации. Человеческий мозг обрабатывает визуальные образы в 60 000 раз быстрее текста.

> Аналогия: Таблица с данными — это сценарий фильма, лежащий в папке. Диаграмма — это трейлер фильма. За 2 минуты (или один взгляд) вы понимаете суть, жанр и стоит ли смотреть дальше.

Три кита визуализации

В Excel десятки типов графиков, но в 95% случаев вам понадобятся только три:

Гистограмма (Column Chart): Столбики. Идеально для сравнения категорий (кто продал больше: Иванов или Петров?).

График (Line Chart): Линия. Идеально для динамики во времени (как росли продажи с января по декабрь?).

Круговая (Pie Chart): Пирог. Показывает долю от целого. Осторожно: используйте её, только если у вас меньше 5 категорий. Если больше — это превращается в нечитаемую кашу.

!Основные типы диаграмм в аналитике: сравнение, динамика, структура.

Микро-практика №1: Визуализация киносборов

Вспомним наш датасет с фильмами из прошлых уроков (Жанр, Сборы).

Постройте сводную таблицу: Жанр в строках, Сборы в значениях.

Выделите полученную таблицу.

Вкладка Вставка (Insert) -> Рекомендуемые диаграммы.

Выберите Гистограмму с группировкой.

Теперь одним взглядом видно, какой жанр доминирует. Уберите легенду (она дублирует подписи) и добавьте название диаграммы «Сборы по жанрам».

Часть 2. Условное форматирование: Светофор для данных

Иногда строить график долго, а подсветить важное нужно прямо в ячейках. Здесь помогает Условное форматирование.

> Аналогия: Представьте, что вы учитель проверяете диктант. Вы не переписываете текст, вы просто подчеркиваете ошибки красной ручкой. Условное форматирование — это ваша автоматическая красная ручка.

Тепловая карта (Color Scales)

Это самый быстрый способ найти аномалии.

Выделите столбец с числами (например, длительность песен в плейлисте).

Главная -> Условное форматирование -> Цветовые шкалы.

Выберите «Зеленый-Желтый-Красный».

Теперь самые большие числа стали зелеными, а маленькие — красными. Вы мгновенно видите «лидеров» и «аутсайдеров».

Часть 3. Power Query: Убийца рутины

Если вы запомните из этого курса только одну вещь, пусть это будет Power Query. Это инструмент ETL (Extract, Transform, Load), встроенный в Excel.

Проблема: Каждую неделю вам присылают выгрузку продаж в ужасном виде: объединенные ячейки, лишние заголовки, даты в формате текста. Вы тратите час на чистку. И так каждую неделю.

Решение: Вы один раз показываете Power Query, как почистить файл. В следующий раз вы просто нажимаете кнопку «Обновить», и Excel делает всё сам за 1 секунду.

> Аналогия: Обычная работа в Excel — это стирка белья руками в реке. Power Query — это стиральная машина. Вы один раз настраиваете программу (хлопок, 40 градусов), а потом просто закидываете грязное белье (данные) и нажимаете «Старт».

Как это работает?

Данные -> Получить данные (Get Data) -> Из файла.

Открывается отдельное окно — редактор Power Query.

Здесь вы нажимаете кнопки: «Удалить верхние 3 строки», «Разделить столбец по запятой», «Заменить значения».

Справа записываются Примененные шаги (Applied Steps).

Нажимаете Закрыть и загрузить.

Данные выгружаются на чистый лист. В следующий раз, когда исходный файл изменится, просто нажмите Alt + F5.

!Редактор Power Query, где записывается сценарий обработки данных.

Часть 4. Итоговый проект: Дашборд аналитика

Пришло время собрать всё воедино. Мы создадим отчет с нуля.

Легенда: Вы работаете в музыкальном стриминге. Вам прислали «грязный» файл raw_music_data.csv с топ-50 треками за год. Ваша задача: подготовить интерактивный дашборд для руководства.

Этап 1: ETL (Загрузка и очистка)

Представим, что у нас есть данные, где в одной ячейке написано "Artist - Song Name", а длительность указана как текст "3 min 45 sec".

Откройте пустой Excel. Данные -> Из текстового/CSV файла.

В Power Query:

* Разделите столбец «Трек» по разделителю « - », чтобы получить отдельно Артиста и Песню. * Удалите лишние столбцы (например, ID записи). * Преобразуйте длительность в секунды (или просто десятичное число).

Закрыть и загрузить.

Этап 2: Анализ (Сводные таблицы)

На основе полученной «зеленой» таблицы создайте 2 сводные таблицы на новом листе Calculation:

Топ-5 Артистов: Строки = Артист, Значения = Количество треков (сортировка по убыванию).

Жанровое распределение: Строки = Жанр, Значения = Средняя длительность.

Этап 3: Визуализация и Дашборд

Создайте новый лист Dashboard. Уберите сетку (Вид -> Сетка — снять галочку). Это ваш холст.

По первой сводной таблице постройте Линейчатую диаграмму (Bar Chart — полоски лежат горизонтально). Это удобно для длинных имен артистов.

По второй таблице постройте Гистограмму.

Вырежьте диаграммы (Ctrl+X) и вставьте их на лист Dashboard (Ctrl+V).

Добавьте Срез (Slicer) по Жанру. Разместите его сбоку.

Подключите срез ко всем диаграммам (ПКМ по срезу -> Подключения к отчетам).

Теперь, кликая по жанру «Pop» на срезе, все графики перестраиваются, показывая данные только для поп-музыки.

Этап 4: Выводы

Аналитик не просто строит графики, он делает выводы. Добавьте текстовое поле с инсайтами: «Артист X доминирует в топе».* «Средняя длина хита сократилась до 2.5 минут».*

Для расчета доли рынка (Share) часто используется формула:

Где: * — доля конкретного сегмента (например, жанра) в процентах. * — значение сегмента (сборы жанра). * — общая сумма всех значений (общие сборы).

В сводной таблице это делается автоматически: ПКМ по значению -> Дополнительные вычисления -> % от общей суммы.

Чек-лист прогресса (Финал курса)

Отметьте, что вы теперь умеете: * [ ] Выбирать правильный тип диаграммы под задачу. * [ ] Использовать условное форматирование для поиска аномалий. * [ ] Понимать принцип работы Power Query (ETL). * [ ] Строить интерактивный дашборд со срезами. * [ ] Проходить полный цикл: Сырые данные -> Очистка -> Сводная -> График -> Вывод.

Куда двигаться дальше?

Вы освоили базу и уверенный средний уровень. Excel — это океан, но вы уже умеете плавать. Что учить дальше?

Углубленный Power Query: Язык M (для сложных трансформаций).

Power Pivot & DAX: Если данных больше 1 млн строк и нужны сложные модели.

SQL: Язык запросов к базам данных (стандарт для аналитика).

Python (Pandas): Для автоматизации того, с чем не справился Excel.

Ресурсы для развития

* YouTube: Каналы «Планета Excel» (Николай Павлов) и «Excel с нуля». * Практика: Kaggle Datasets — берите любые данные о продажах, играх, спорте и стройте дашборды.

Спасибо за этот путь! Теперь вы не просто пользователь Excel, вы — аналитик. Удачи в проектах!

---