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

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

В прошлых уроках мы анализировали продажи канцелярских товаров.

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

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

Начинаем работу с большой таблицей

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

В таблице есть четыре колонки:

Product — продукт
Quantity — количество проданных товаров
Price — цена
Product_id — уникальный идентификатор товара

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

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

Как анализировать данные с помощью сортировки

Рассмотрим сортировку — первый способ анализа. Пройдем этот процесс по шагам.

Сначала выясним количество строк в таблице. Для этого выделим любой столбец таблицы — например, столбец A:

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

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

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

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

Отсортируем таблицу по значениям одного столбца:

Выделяем всю таблицу, не включая строки с названиями столбцов
Открываем вкладку «Данные»
На вкладке «Данные» раскрываем пункт «Сортировать диапазон»
Выбираем нужный тип сортировки диапазона — нам подходит «Сортировать диапазон по столбцу А»

Теперь таблица стала удобнее для восприятия:

Как найти товары с максимальной выручкой

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

Для этого на вкладке «Данные» мы пройдем в пункт «Расширенные настройки сортировки диапазонов». В ответ Google Sheets выведет такое окно:

В нашей таблице цена записана в столбец С. По нему и отсортируем весь диапазон.

Если вы хотите отсортировать таблицу сразу по нескольким столбцам, алгоритм сортировки можно настроить тут же, добавив последовательность сортировки, нажав на кнопку «Добавить еще один столбец для сортировки».

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

Также в меню расширенной сортировки вы можете выбрать, будете ли вы сортировать:

По возрастанию (от А до Я)
По убыванию (от Я до А)

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

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

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

Как анализировать данные с помощью функции

Кроме сортировки в Google Sheets есть еще и функция МАКС. Также мы умеем фиксировать ссылку на столбец и номер ячейки с помощью символа $.

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

ИНДЕКС
ПОИСКПОЗ
МАКС

В прошлом уроке мы уже применяли эти формулы в таком виде:

=ИНДЕКС(C2:C4;ПОИСКПОЗ(МАКС($G$2:$G$4);$G$2:$G$4;))

Перед применением формул нужно убрать символ $ с помощью метода «Найти и заменить»:

Скопируем символ $
Нажмем сочетание горячих клавиш Ctrl+F
В появившемся окне мы увидим поле «Заменить на». Ничего не пишем в нем и нажимаем кнопку «Заменить все»:
Нажимаем кнопку «Готово»:
По умолчанию в Google Sheets целая и десятичная часть чисел разделены запятой. Нам нужно найти и заменить все запятые в столбце C на точки. Это можно сделать по аналогии с заменой знака $ на пустоту

Теперь наша таблица готова. Начинаем анализ:

Выбираем ячейку E3 и находим в ней название товара с максимальной выручкой от продаж. Формулу вы видите на картинке ниже:
Выбираем ячейку Е4. Находим в ней название товара со второй по размеру выручкой от продаж для диапазона, стартующего уже в третьей строке:

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

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

Как анализировать данные с помощью сводной таблицы

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

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

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

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

Как анализировать данные с помощью графика

Бегло оценить самые продаваемые товары можно с помощью графика или диаграммы:

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

Выводы

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