Первая бизнес-задача — Введение в дата-аналитику

Видео может быть заблокировано из-за расширений браузера. В статье вы найдете решение этой проблемы.

В прошлом уроке мы узнали, что такое электронная таблица, и познакомились с их базовыми функциями. Сегодня мы решим нашу первую бизнес-задачу в Google Sheets.

Опишем бизнес-задачу

Представим, что нам нужно проанализировать таблицу продаж магазина канцелярских товаров:

На основе этих данных мы хотим сделать выводы, которые помогут бизнесу развиваться.

Чтобы сделать выводы, мы должны ответить на несколько вопросов:

Какова общая выручка от продажи всех товаров?
Сколько единиц товаров куплено за весь период?
Как изменилась выручка после применения скидки?
Сколько уникальных клиентов было за прошедший месяц?
Какой товар покупали больше всего?
Каков средний чек клиентов по карандашам?

Ответить на все эти вопросы помогут функции Google Sheets.

Какова общая выручка от продажи всех товаров?

Для ответа на этот вопрос нужно сложить все значения в столбце «Сумма». Но при этом возникает вопрос: «Как искать название нужной функции?».

Можно написать знак = и начать писать название функции. Например, мы хотим найти функцию суммы:

Нажимаем на знак суммы на панели инструментов:

Идем в раздел «Справка» и поищем нужную функцию там:

Еще поискать нужную функцию можно так:

Прочитать официальную справку о функциях в Google Sheets
Поискать ответ в поисковике или на Youtube — скорее всего, там найдется обзор самых полезных функций

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

Затем мы выбираем диапазон ячеек. Для этого нужно кликнуть на первую ячейку диапазона, зажать левую кнопку мыши и тянуть выделение до последней ячейки диапазона:

В строке формул fx видно, какая формула используется в ячейке F5.

Ответ: всего было продано товаров на 46 рублей.

Сколько единиц товаров куплено за весь период?

Наша задача постепенно усложняется. Теперь у нас в распоряжении таблица проданной канцелярии с января по март 2023 года. Каждый месяц продаж вынесен на отдельный лист Google Sheets:

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

Например, вот февраль:

А вот март:

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

Чтобы найти общее количество проданных товаров, нужно сложить все значения столбца «Количество» для нескольких листов сразу.

Пройдем этот процесс по шагам:

Сложим все проданные единицы товара за январь:
Добавим сюда проданные единицы с февраля и марта. Чтобы сложить ячейки на разных листах, нужно сначала выделить их — тогда мы получим диапазон, например, D2:D4. Затем мы ставим точку с запятой, кликаем на нужный лист Google Sheets и выделяем ячейки в столбце «Количество» уже там:

Ответ: за три месяца было продано 57 единиц канцтоваров:

Как изменилась выручка после применения скидки?

В марте владелец магазин решил сделать скидку в 5% на все позиции канцелярии.

Для расчета новой выручки нужно:

Сложить сумму продаж за март
Умножить ее на 0,95 (так мы узнаем сумму с учетом скидки)
Вычесть сумму со скидкой из полной суммы

Так выглядит формула на этом этапе:

Ответ: из-за скидки в 5% выручка за март снизится на 7,05 рублей.

Сколько уникальных клиентов было за прошедший месяц?

Узнаем, сколько уникальных клиентов купило канцелярские товары в феврале.

Здесь нам подойдет функция COUNTUNIQUE, чье название переводится как «подсчитай уникальные значения»:

Ответ: В феврале у нас было 3 уникальных клиента.

Какой товар покупали больше всего?

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

Снова опишем процесс по шагам:

Найдем общее количество проданных товаров за январь. Для этого создадим колонку «Количество за квартал» на листе «Март 23». Сложим количество проданных товаров за все три месяца в каждой товарной категории.
Подтянуть ячейки из других таблиц можно с помощью функции ВПР:
Разберем формулу на скриншоте подробнее:
- C2 — название продукта, которое мы будем искать в соседних таблицах
- 'Январь 23'!C1:D4 — название листа и диапазон ячеек, в которых будем искать количество по названию товара
- 2 — порядковый номер столбца выбранного диапазона, где лежит количество проданных единиц
- ; — аналог записи ;0. Так мы обозначаем, что мы ищем точное совпадение названия товара
Теперь к количеству проданных за январь единиц товара прибавим аналогичные за февраль и март:
Растягиваем созданную формулу на все товарные категории. Чтобы было быстрее, можно два раза кликнуть на правый нижний угол выделенной ячейки:
Ячейки со значением #Н/Д можно найти, поставив фильтр на столбец.
Выясняем максимальное количество проданных единиц товара с помощью функции МАКС:
Ищем название товара по количеству его продаж в квартал. Сделать это можно с помощью аналога ВПР.
Обсудим подробнее, почему нам нужен именно аналог. Дело в том, что ВПР всегда ищет значения в левом столбце, подбирая соответствующую им ячейку справа. А мы хотим наоборот: по значению столбца G найти ячейку в столбце С.
Аналог ВПР — комбинация двух функций:
- ИНДЕКС ищет ячейку по ее порядковому номеру в столбце
- ПОИСКПОЗ возвращает порядковый номер ячейки в столбце
С помощью значка $ мы зафиксировали название столбца и номера ячеек в диапазоне поиска:
Пока что мы научились искать название товара по количеству его продаж в квартал. Теперь нужно выбрать название одного товара с максимальным числом продаж. Вместо G2 подставляем функцию МАКС от диапазона ячеек поиска:

Ответ: больше всего покупали карандаши:

Каков средний чек клиентов по карандашам?

Мы хотим узнать, сколько денег люди в среднем потратили на покупку одного карандаша за три месяца. Начнем:

В марте была скидка 5%, поэтому исправляем цену карандаша в марте
Считаем сумму продаж карандашей в отдельности за три месяца
Делим эту сумму на количество проданных за квартал карандашей:

Ответ: средний чек за карандаш составил 4,927 рубля.

Выводы

Найти ответы на очень многие бизнес-вопросы можно еще на уровне Google Sheets. Для качественного анализа данных необязательно каждый раз пользоваться сложными инструментами.

Сегодня мы узнали, что в магазине канцелярии:

Общая выручка от продажи всех товаров составила 46 рублей
За квартал мы продали 57 единиц канцелярских товаров
Выручка после применения скидки в 5% снизилась на 7 рублей
За февраль было 3 уникальных клиента
Карандаши — это самый продаваемый товар
Средний чек клиентов по карандашам составил 4,927 рубля

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