Введение

Аннотация.

Продвинутый уровень работы с Excel и Google Sheets — это навык, который превращает хаос таблиц в управляемый поток данных, готовый для принятия стратегических решений. Этот курс создан для тех, кто уже умеет вводить формулы, но хочет освоить мощные инструменты анализа, такие как сводные таблицы и сложные формулы. Мы разберем, как превратить сырые выгрузки с маркетплейсов в наглядные графики и отчеты, которые видят и понимают руководители. Главная боль, которую решает курс, — это бесконечная ручная обработка больших массивов данных, которая забирает часы и часто приводит к ошибкам. Вы научитесь автоматизировать повторяющиеся задачи и находить скрытые закономерности в цифрах, используя весь арсенал функций, доступных в офисных табличных процессорах.

Цель курса.

После прохождения курса вы сможете самостоятельно строить комплексные аналитические панели в Excel и Google Sheets, применяя сводные таблицы, сложные формулы и динамические графики для эффективного анализа данных с маркетплейсов, сократив время на подготовку отчетов не менее чем на 70%.

Для кого этот курс.

Этот курс разработан для специалистов по работе с данными, финансовых аналитиков, менеджеров по продажам, маркетологов и всех, кто ежедневно сталкивается с необходимостью обработки больших объемов табличной информации из Excel или Google Sheets. Он принесет пользу тем, кто хочет перейти от ручного копирования данных к профессиональному анализу и созданию автоматизированных систем отчетности.

Курс НЕ предназначен для абсолютных новичков, которые только открыли табличный редактор. Мы не рассматриваем базовые операции, такие как создание ячеек или простые арифметические действия. Предполагается, что вы уверенно пользуетесь интерфейсом программы, знаете основные формулы (например, SUM и IF) и умеете фильтровать данные. Если ваша работа требует принимать решения на основе цифр, и вы хотите делать это быстрее и качественнее — этот курс для вас.

Модуль 1. Погружение в сводные таблицы: от простого к сложному

Создание сводной таблицы в Excel или Google Sheets начинается с выбора диапазона данных. Ключевое преимущество этого инструмента — возможность мгновенно менять представление данных. Представьте, что у вас есть огромный список продаж с товарами, регионами и менеджерами. Вы просто перетаскиваете поле «Регион» в область «Строки», а поле «Сумма продаж» — в область «Значения». Вот и первый отчет!

Как применить: Для Excel: выделите таблицу, перейдите на вкладку «Вставка» и нажмите «Сводная таблица». Для Google Sheets: выберите меню «Данные» → «Сводная таблица». Настройка полей происходит в правой боковой панели.

Практический совет: Всегда проверяйте, чтобы исходный диапазон не содержал пустых столбцов или строк, иначе сводная таблица может не захватить все данные. Используйте сочетание клавиш Ctrl + A (или Cmd + A на Mac) для быстрого выделения всего заполненного диапазона.

Группировка данных в сводных таблицах — это функциональность, которая превращает детальные записи в категории для анализа. Например, у вас есть даты каждой продажи. Вы можете сгруппировать их по месяцам или кварталам, чтобы увидеть сезонность.

Как применить: В Excel: щелкните правой кнопкой мыши по любой дате внутри сводной таблицы и выберите «Группировать». В появившемся окне можно выбрать шаг: дни, месяцы, годы. В Google Sheets: вам нужно либо добавить новый столбец в исходные данные с формулой =MONTH(A2), либо использовать вычисляемое поле в самой сводной таблице.

Практический совет: Группировка дат — это стандартная задача для отчетов по маркетплейсам, чтобы отследить динамику продаж по неделям. Также можно группировать числовые значения, например, по ценовым диапазонам.

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

Как применить: Используйте «Срезы» (Excel: меню «Сводная таблица» → «Вставить срез»; Google Sheets: добавьте фильтр к столбцу в готовой таблице). Срезы — это визуальные кнопки-фильтры, которые упрощают использование сводной таблицы для других пользователей.

Альтернатива: Можно перетащить поле в область «Фильтры» на панели настроек. Это создаст выпадающий список над таблицей.

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

Как применить: Щелкните правой кнопкой по полю «Значения» (например, «Сумма по полю Продажи») и выберите «Параметры полей значений». В Excel перейдите на вкладку «Дополнительные вычисления» и выберите, например, «% от итога по столбцу». В Google Sheets функция называется «Показать как».

Практический совет: Используйте «% от итога по строке», чтобы понять, какой товар вносит наибольший вклад в продажи внутри своей категории. Это помогает в управлении ассортиментом на маркетплейсах.

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

Как применить: В том же окне «Параметры полей значений» (в Excel) или в выпадающем списке «Сводные значения» (в Google Sheets) выберите «Среднее», «Количество» или «Максимум».

Пример для маркетплейса: Вы выгрузили отзывы по товарам. С помощью сводной таблицы и агрегации «Количество» вы можете увидеть, сколько отзывов у каждого товара, а с помощью «Среднее» — средний рейтинг. Это выявляет проблемные позиции.

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

Как применить: В Excel: на вкладке «Сводная таблица» выберите «Поля, элементы и наборы» → «Вычисляемое поле». Введите имя и формулу. В Google Sheets добавьте вычисляемое поле прямо в редакторе сводной таблицы.

Важно: Вычисляемые поля используют агрегированные данные, а не построчные. Это мощный, но иногда неочевидный инструмент. Например, формула = Продажи / Количество даст средневзвешенную цену.

Модуль 2. Формулы для продвинутой обработки: до уровня профессионала

Функция VLOOKUP (ВПР) — это классика для вертикального поиска. Она находит значение в первом столбце таблицы и возвращает значение из другого столбца той же строки. Это незаменимо для обогащения данных, например, подстановки названий товаров по их артикулам.

Как применить: Синтаксис: =VLOOKUP(искомое_значение; таблица; номер_столбца; [интервальный_просмотр]). Интервальный просмотр (или FALSE/ЛОЖЬ) ищет точное совпадение, TRUE/ИСТИНА — приблизительное.

Критическое ограничение: VLOOKUP ищет только СПРАВА НАЛЕВО. Искомый столбец должен быть самым левым в таблице. Чтобы избежать ошибок #Н/Д, убедитесь, что форматы данных (текст/число) в искомом значении и таблице совпадают.

HLOOKUP (ГПР) — это «горизонтальный» брат VLOOKUP. Она ищет значение в первой строке таблицы и возвращает значение из другой строки того же столбца. Это полезно, когда данные структурированы горизонтально, например, месяцы расположены в столбцах.

Как применить: Синтаксис: =HLOOKUP(искомое_значение; таблица; номер_строки; [интервальный_просмотр]). Аргументы аналогичны VLOOKUP, но вместо номера столбца мы указываем номер строки.

Когда использовать: Если ваши данные, например, выгрузка из маркетплейса, где периоды (даты) расположены по горизонтали, а товары — по вертикали. Однако на практике VLOOKUP используется чаще, так как большинство данных организованы вертикально.

Связка INDEX и MATCH — это более гибкая альтернатива VLOOKUP. MATCH находит позицию искомого значения в строке или столбце, а INDEX возвращает значение из ячейки на пересечении нужной строки и столбца. Это позволяет искать в любом направлении, а не только справа налево.

Как применить: Синтаксис: =INDEX(диапазон; MATCH(искомое_значение; столбец_для_поиска; 0); номер_столбца). Ноль в функции MATCH означает точное совпадение.

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

Функция FILTER в Google Sheets (и в новых версиях Excel — ФИЛЬТР) возвращает массив значений, который соответствует одному или нескольким условиям. Это мощный инструмент для создания динамических отчетов, который обновляется автоматически при изменении исходных данных.

Как применить: =FILTER(диапазон; условие1; [условие2; ...]). Например, =FILTER(A2:E100; B2:B100 = 'Москва') покажет все строки из диапазона, где в столбце B указан город «Москва».

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

ARRAYFORMULA в Google Sheets (аналог массивных формул в Excel, вводимых Ctrl+Shift+Enter) позволяет обрабатывать целые диапазоны данных одной формулой. Это экономит время и делает таблицы более чистыми.

Как применить: =ARRAYFORMULA(формула_с_диапазоном). Например, если у вас в столбце A цены, а в B — количество, то =ARRAYFORMULA(A2:A10*B2:B10) сразу посчитает выручку для всех 10 строк, без необходимости копировать формулу вниз.

Пример для маркетплейса: Вы хотите применить комиссию 15% ко всем ценам товаров в столбце. =ARRAYFORMULA(C2:C*0.85) мгновенно пересчитает все цены. Это также работает с IF и другими сложными формулами.

Функция QUERY — это уникальный инструмент Google Sheets, который позволяет выполнять SQL-подобные запросы к вашим данным. С ее помощью можно фильтровать, группировать, сортировать и агрегировать данные в одном предложении.

Как применить: =QUERY(диапазон; 'SQL_запрос'; [заголовки]). Например, чтобы выбрать товары с продажами выше 1000 и отсортировать по убыванию, запрос будет выглядеть так: 'SELECT A, B, C WHERE C > 1000 ORDER BY C DESC'.

Практический совет: QUERY невероятно мощная, но требует знаний синтаксиса SQL. Она идеально подходит для разовой подготовки сложных отчетов из больших выгрузок. Это «швейцарский нож» аналитика в Google Sheets.

IMPORTXML в Google Sheets позволяет импортировать данные из XML, HTML и других структурированных форматов прямо из Интернета. Это открывает возможность автоматически собирать данные с сайтов и маркетплейсов.

Как применить: =IMPORTXML(URL; xpath_запрос). Например, чтобы получить текущий курс доллара с сайта ЦБ, нужно найти XPath элемента с курсом и подставить его в формулу.

Критическое ограничение: Функция зависит от структуры сайта. Если сайт изменит код, формула сломается. Кроме того, многие крупные сайты (например, Ozon) блокируют такие запросы. Используйте это для публичных и простых источников данных.

Power Query (встроенный в Excel начиная с 2016) — это мощнейший инструмент для импорта, очистки и трансформации данных. В отличие от обычных формул, он создает последовательность шагов, которые можно повторно выполнять одной кнопкой.

Как применить: На вкладке «Данные» выберите «Получить данные» → «Из файла» или «Из Интернета». В редакторе Power Query вы можете удалять столбцы, фильтровать строки, менять типы данных и объединять таблицы. Каждое действие записывается как шаг в правой панели «Примененные шаги».

Практический совет: Power Query идеально подходит для обработки ежемесячных выгрузок с маркетплейсов. Настроив трансформацию один раз, вы сможете обновлять отчет за секунды, просто подгружая новый файл.

Модуль 3. Визуализация данных: от цифр к решению

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

Как выбрать: Для сравнения величин используйте столбчатые или линейчатые диаграммы. Для отображения динамики во времени — линейные графики. Для долей и процентов — круговые диаграммы (но не более 5-7 сегментов).

Практический совет: В Excel и Google Sheets используйте «Рекомендуемые диаграммы», но не доверяйте им слепо. Всегда проверяйте, что у графика есть четкое название и подписи осей (даже если они не читаются как текст, они нужны для структуры). Избегайте 3D-эффектов — они искажают восприятие.

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

Как создать в Excel: Используйте «Срезы» для сводных таблиц и «Временную шкалу» для дат. В Google Sheets создайте несколько листов с данными и разместите на главном листе диаграммы, привязанные к ячейкам с фильтрами (используйте формулы FILTER или QUERY для подготовки данных).

Практический совет: Хороший дашборд отвечает на один главный вопрос: «Как идут дела?». Не перегружайте его информацией. 5-7 ключевых показателей (KPI), размещенных в верхней части, и 3-4 графика — это идеальный баланс.

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

Как применить: В Excel и Google Sheets выберите диапазон, перейдите в меню «Условное форматирование» и создайте правило. Например, выделить красным ячейки, где значение меньше 100.

Практический совет: Используйте «Цветовые шкалы» и «Наборы значков» (иконки) для быстрой визуализации распределения данных. Например, иконки стрелок вверх/вниз для сравнения продаж с предыдущим периодом — это отличный способ показать динамику в отчете.

Комбинированные диаграммы позволяют отображать два разных типа данных на одном графике, например, столбцы для объема продаж и линию для процента конверсии. Это показывает взаимосвязь между показателями.

Как создать: В Excel выберите «Гистограмма» → «Комбинированная» и укажите тип для каждого ряда данных. В Google Sheets выберите «Диаграмма» и на вкладке «Настройка» выберите «Комбинированная диаграмма».

Критический момент: Убедитесь, что у вас есть вспомогательная ось для второго ряда данных, иначе данные будут несопоставимы. Например, объем продаж в миллионах и конверсия в процентах не могут быть на одной шкале.

Гистограмма — это график для анализа распределения данных. Она группирует числа в диапазоны («корзины») и показывает, сколько значений попадает в каждую корзину. Это незаменимо для понимания структуры данных, например, распределения сумм заказов или рейтингов товаров.

Как создать: В Excel: на вкладке «Вставка» → «Гистограмма» (это отдельный тип диаграммы). В Google Sheets используйте функцию =HISTOGRAM или настройте столбчатую диаграмму, указав ширину интервала.

Пример для маркетплейса: Постройте гистограмму по суммам заказов. Вы увидите, что основная масса заказов — до 3000 рублей, а редкие заказы на 10 000+ рублей искажают средний чек. Это поможет в ценообразовании.

Модуль 4. Обработка данных маркетплейса: от загрузки до отчета

Работа с данными маркетплейса начинается с выгрузки. Wildberries и Ozon предоставляют отчеты в форматах CSV и XLSX. CSV — это текстовый файл, где значения разделены точкой с запятой или запятой. Это самый распространенный формат для обмена данными.

Как открыть в Excel/Google Sheets: Просто откройте файл через меню «Файл» → «Открыть». Для CSV важно правильно определить разделитель, чтобы данные не слиплись в один столбец. В Excel используйте «Текст по столбцам» для ручной настройки, если автоматическое открытие не сработало.

Первая проблема: кодировка. Часто CSV в Windows имеют кодировку ANSI, а Google Sheets работает с UTF-8. Это вызывает кракозябры (вместо букв — иероглифы). Решение: сохраните файл в кодировке UTF-8 в блокноте перед загрузкой.

Выгрузки с маркетплейсов часто содержат составные данные в одном столбце, например, «Бренд Модель Размер». Для анализа необходимо разделить их. Инструмент «Текст по столбцам» (Excel) или SPLIT (Google Sheets) решает эту задачу.

Как применить в Excel: Выделите столбец, перейдите «Данные» → «Текст по столбцам». Выберите «С разделителями» (если данные разделены запятыми или пробелами) или «Фиксированной ширины» (если каждый столбец имеет фиксированное количество символов).

Как применить в Google Sheets: Используйте формулу =SPLIT(A2; ' '), где второй аргумент — разделитель. Эта формула автоматически распределит слова по столбцам.

Данные с маркетплейсов часто содержат дубликаты: один и тот же заказ может быть записан дважды в разных статусах. Удаление дубликатов — обязательный шаг перед построением сводной таблицы или расчетом итогов.

Как применить: В Excel и Google Sheets выделите диапазон и выберите «Данные» → «Удалить дубликаты». В Excel можно указать, по каким столбцам искать дубликаты (если нужно считать уникальными только комбинации значений).

Альтернатива с формулой: В Google Sheets можно использовать =UNIQUE(диапазон), чтобы вывести список уникальных значений в новом месте. Это полезно, когда нужно сохранить исходные данные нетронутыми.

Очистка данных — это искусство и необходимость. В выгрузках с маркетплейсов могут быть лишние пробелы, невидимые символы и опечатки. Инструменты «Найти и заменить» и функция TRIM (СЖПРОБЕЛЫ) — ваши главные помощники.

Как применить: =TRIM(A2) удаляет все лишние пробелы в начале и конце строки, а также множественные пробелы между словами. Для замены опечаток используйте «Найти и заменить» (Ctrl+H).

Продвинутый прием: Используйте =SUBSTITUTE (ПОДСТАВИТЬ) для замены части текста. Например, =SUBSTITUTE(A2; 'шт.'; '') уберет из ячейки текст «шт.». Это необходимо, чтобы преобразовать текстовое значение в числовое для дальнейших расчетов.

Часто данные по товарам разрознены: одна таблица с продажами, другая с названиями и характеристиками. Обогащение данных — это процесс объединения этих источников в единую аналитическую таблицу с помощью функций поиска (VLOOKUP, INDEX/MATCH).

Пример: У вас есть выгрузка заказов с артикулами. В другой таблице есть прайс-лист. Вы используете =VLOOKUP(артикул; прайс-лист; 2; FALSE), чтобы подставить цену товара в каждый заказ. Теперь вы можете рассчитать выручку.

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

После очистки и обогащения данных, мы переходим к расчету ключевых метрик. Для маркетплейса это: Выручка, Средний чек, Конверсия (из просмотров в заказы) и Количество заказов. Эти метрики формируют основу дашборда.

Как рассчитать: Выручка = SUM (Сумма по полю «Цена * Количество»). Средний чек = AVERAGE (Сумма заказа). Конверсия = COUNT (Заказы) / COUNT (Визиты) * 100%. Визиты обычно берутся из отчета рекламного кабинета (например, Яндекс.Директ).

Практический совет: Создайте отдельный лист «Метрики» и выведите на него эти ключевые значения. Используйте SUMIFS, AVERAGEIFS и COUNTIFS, чтобы фильтровать метрики по датам, категориям или менеджерам. Это основа для управленческого отчета.

Модуль 5. Автоматизация и работа с шаблонами

Макросы в Excel (VBA) и Google Apps Script в Google Sheets позволяют записывать и воспроизводить последовательность действий. Это основа автоматизации для рутинных операций: очистка данных, применение форматов, создание отчетов.

Как применить: В Excel: вкладка «Разработчик» → «Записать макрос». Выполните действия, затем остановите запись. В Google Sheets: «Расширения» → «Apps Script». Напишите скрипт на JavaScript или запишите макрос через вкладку «Запись макросов».

Практический совет: Начните с записи простых макросов (например, применение одного и того же фильтра и построение графика). Постепенно добавляйте логику (условия, циклы) для создания умных скриптов. Это сэкономит вам часы работы каждый месяц.

Шаблоны (Templates) — это заготовки отчетов с настроенными сводными таблицами, графиками и формулами. Вместо того чтобы создавать отчет с нуля каждый месяц, вы открываете шаблон, вставляете новые данные и получаете готовый результат.

Как создать в Excel: Настройте отчет, удалите из него старые данные, оставив структуру. Сохраните файл как «Шаблон Excel» (.xltx) или просто создайте мастер-файл и используйте копии. В Google Sheets создайте «Мастер-лист» и используйте «Создать копию» для каждого нового периода.

Критическое требование: Ваш шаблон должен быть гибким. Используйте именованные диапазоны и формулы, которые ссылаются на всю таблицу (например, =SUM(Таблица[Продажи])), чтобы при добавлении новых строк сводная таблица и графики автоматически обновлялись.

В современном мире данные редко живут в одной книге. Связывание данных между файлами Excel и Google Sheets — это способ создания единой системы отчетности. Изменения в источнике автоматически отражаются в вашем отчете.

Как применить в Excel: В формуле перейдите в другой файл и выберите ячейку. Формула будет выглядеть так: ='[Данные.xlsx]Лист1'!$A$1. В Google Sheets используйте функцию =IMPORTRANGE('URL_таблицы'; 'Лист1!A1').

Критическое предупреждение: Ссылки на внешние файлы — это «хрупкая» конструкция. При перемещении или переименовании файла ссылка ломается. В Google Sheets IMPORTRANGE работает стабильнее, но требует доступа к источнику и может замедлять таблицу.

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

Как применить: В Excel: вкладка «Рецензирование» → «Защитить лист». Установите пароль и выберите разрешенные действия (например, только выделение ячеек). В Google Sheets: «Данные» → «Защищенные листы и диапазоны».

Практический совет: Защищайте листы даже от себя. Это предотвращает случайные правки. Всегда оставляйте несколько ячеек (например, для ввода новых данных) незащищенными. Это баланс между гибкостью и безопасностью.

Модуль 6. Оптимизация и работа с большими данными

Когда файл Excel или Google Sheets становится тяжелым (тысячи строк, сотни формул), он начинает тормозить. Оптимизация — это набор техник для ускорения работы.

Основные правила: 1. Избегайте формул-массивов там, где можно использовать сводную таблицу. 2. Ограничьте использование функций VLOOKUP на больших диапазонах, заменив их на INDEX/MATCH. 3. В Google Sheets избегайте IMPORTRANGE для больших объемов, если это не критично.

Практический совет: Включайте ручной расчет в Excel (меню «Формулы» → «Параметры вычислений» → «Вручную»). Тогда формулы будут пересчитываться только при нажатии F9. Это ускоряет ввод данных, но не забывайте пересчитывать перед сохранением.

Стандартный лист Excel ограничен 1 048 576 строками, а Google Sheets — около 10 миллионами ячеек. Но иногда данных больше. Для этого существуют Power Pivot в Excel и подключение к Google BigQuery или Google Sheets с помощью IMPORTDATA и QUERY.

Power Pivot — это движок базы данных внутри Excel. Вы можете загрузить в него миллионы строк и строить сводные таблицы на их основе, не открывая весь файл. Это меняет представление о возможностях Excel.

Как применить в Google Sheets: Используйте подключение к внешним базам данных через Google Apps Script или встроенные функции, такие как GOOGLEFINANCE (для финансовых данных) и IMPORTHTML для загрузки таблиц с веб-страниц.

Google Apps Script — это мощный инструмент для автоматизации в Google Sheets. Вы можете создавать скрипты, которые обновляют данные, отправляют отчеты по электронной почте и интегрируются с другими сервисами Google.

Как применить: Напишите скрипт, который использует SpreadsheetApp для чтения данных, MailApp для отправки письма и Gmail для взаимодействия с почтой. Установите триггер на выполнение скрипта по времени (каждый час, день, неделю).

Пример: Создайте скрипт, который каждое утро в 9:00 берет данные за вчерашний день из вашей Google Sheets, строит график и отправляет его в виде PDF-файла вашему руководителю. Это полностью автоматизированная отчетность.

Вопросы и ответы

Какая основная проблема, которую решает курс, по мнению авторов?

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

Это соответствует аннотации курса, которая прямо говорит о проблеме ручной обработки данных. Остальные варианты описывают смежные, но не основные проблемы.

Отсутствие базовых знаний о работе с таблицами.

Неумение пользоваться графическим интерфейсом.

Что такое «сводная таблица»?