Бесплатная аналитика на маркетплейсах. Статья поможет вам быстро и бесплатно провести анализ своей работы на маркетплейсах, имея под рукой только Excel или Google Таблицы.
Основное определение, которое дальше будет использоваться во всех формулах – это “синтаксис”, поэтому для начала разберем значение этого термина.
Синтаксис – это формула функции, которая состоит из двух частей, как правило, это имя функции и один или несколько аргументов.
Имя функции – это описание операции, которую выполняет данная функция.
Аргумент задает значения или ячейки, которые использует данная функция. Аргументы имеют определенную последовательность и заключаются в круглые скобки и разделяются между собой точкой с запятой, это могут быть текстовые, числовые, логические значения или ссылки на ячейки/диапазон ячеек.
Возьмем, к примеру, формулу =СУММ(С1:С3), здесь СУММ – это имя функции, а С1:С3 – аргумент. Формула считает сумму чисел из ячеек С1, С2, С3.
Теперь рассмотрим основные формулы, которые понадобятся вам для анализа данных на маркетплейсах.
ВПР и ГПР (vlookup и hlookup)
Функция ВПР
Данная функция расшифровывается как «вертикальный просмотр».
ВПР занимается поиском данных в текстовой строке или в диапазоне ячеек таблицы и переносит их в другую таблицу.
Синтаксис
Формула представляет собой функцию ВПР плюс 4 аргумента и представлена следующими значениями:
=ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])
Искомое значение – указывается в первом столбце диапазона ячеек и может представлять собой значение или ссылку на ячейку.
Таблица – это группа ячеек, в которой будет происходить поиск искомого и возвращаемого значения. Диапазон ячеек должен включать в себя “искомое значение” в первом столбце и возвращаемое значение в любом месте.
Номер столбца – столбец, который содержит в себе возвращаемое значение.
Интервальный просмотр – это необязательный аргумент. Представляет собой логическое выражение, определяющее, насколько точное совпадение должна обнаружить функция.
Интервальный просмотр подразделяется на две функции:
- Истина – применяется по умолчанию, и выполняет поиск ближайшего к искомому значения. Данные первого столбца должны быть упорядочены по возрастанию или в алфавитном порядке.
- Ложь – ищет точное значение в первом столбце.
Пример
Разберем несколько вариантов использования функции ВПР:
- Функция ВПР понадобится, чтобы найти в табличке нужное значение и перенести его в другую таблицу
Предположим, функции нужно найти “Название товара”, соответствующее определенному артикулу и перенести это название в таблицу с другими данными, где артикулы расположены в ином порядке.
Используем формулу ВПР
В нужном нам поле ставим =ВПР и заполняем значения аргументов.
В первую очередь выбираем запрос или искомое значение, которое нам нужно, в данном случае это будет артикул товара, название которого нам нужно найти.
Далее определяем диапазон таблицы в котором будет происходить поиск. Искомое значение всегда должно находиться в крайнем левом столбце, поэтому если в вашей таблице слева есть еще столбцы, выделяйте диапазон начиная с того столбца, в котором находится искомое значение. В нашем случае диапазоном будут являться ячейки А2:В9.
Далее переходим к аргументу номер_столбца, здесь мы обозначаем порядковый номер столбца в котором будет происходить поиск, нам нужно найти название, поэтому выбираем лоя поиска столбец 2.
Далее идет аргумент отсортировано, он же интервальный просмотр, здесь может быть два варианта функции ИСТИНА и ЛОЖЬ.
ИСТИНА – выполняет поиск ближайшего к искомому значения и применяется по умолчанию, но нам нужно найти значение, которое соответствует искомому в точности, поэтому используем функцию ЛОЖЬ.
Таким образом наша формула будет выглядеть следующим образом: =ВПР(E2;A2:B9;2;ЛОЖЬ()). А в заполняемом поле появится наименование “Кастрюля”, которое соответствует искомому значению.
Чтобы заполнить таблицу полностью, нужно растянуть формулу на все оставшиеся ячейки. При растягивании, чтобы наши значения были абсолютными ссылками, таблица предложит изменить формулу на такую же, но со значком $, кликаем на предложенный вариант и значения изменятся автоматически.
Таким образом у нас получится таблица, в которой поля с названием товара в новой таблице будут соответствовать артикулу.
Таким же способом можно перенести столбец с ценами, чтобы посчитать сумму заказов по каждой единице. Главное не забывать про знак $, для правильного отображения абсолютных ссылок.
Теперь растягиваем это значение на все поля в столбце.
Таким образом, с помощью функции ВПР мы смогли автоматизировать перенос значений из одной таблицы в другую, что сильно сократило нам время на работу с данной таблицей.
В примере было всего 9 строк со значениями, но в работе функция будет также успешно переносить информацию и при 100 строках и при 1000.
Функция ГПР
Функция ГПР схожа с функцией ВПР по принципу работы, а также своей синтаксической записью, и отличается только направлением поиска в диапазоне (построчный, то есть горизонтальный поиск).
Пример
Допустим, у нас есть та же таблица, только названия и артикулы расположены в строке и нужно подтянуть названия к артикулам, расположенным в столбцах.
Для этого используем функцию ГПР, которая поможет быстро перенести значения. В качестве искомого значения используем все тот же артикул.
В качестве диапазона будут использоваться не столбцы, а строки 2 и 3, которые отобразятся ввиде обозначений $1:$2. Обязательно добавляем значок $, чтобы при копировании формулы ссылки на диапазон отображались корректно.
Далее выбираем номер строки в которой будет происходить поиск. У нас это будет строка номер 2. И добавляем ЛОЖЬ, чтобы найти значение, которое соответствует искомому в точности.
Таким образом у нас получается формула =ГПР(A8;$1:$2;2;ЛОЖЬ()), которую теперь можно размножить на весь столбец, чтобы заполнить всю таблицу.
Итого мы разобрали две функции ВПР и ГПР, которые помогают найти значения и перенести их в другую таблицу. Идем дальше.
ИНДЕКС + ПОИСКПОЗ (index + match)
Функцию ИНДЕКС можно использовать вместе с функцией ПОИСКПОЗ с целью замены функции ВПР. Главное отличие от ВПР здесь в том, что искомое значение в ВПР должно всегда находиться в крайнем левом столбце, а ИНДЕКС + ПОИСКПОЗ – это решение, не зависящее от расположения столбцов.
Формула работает следующим образом. ИНДЕКС отсчитывает необходимое количество ячеек вниз в диапазоне искомых значений. Количество отсчитываемых ячеек определяется по столбцу критериев функцией ПОИСКПОЗ.
Пример
Берем все ту же таблицу с артикулами и ценами в которой нам нужно перетянуть количество заказанных товаров в таблицу с наименованиями. При этом столбец по которому мы будем производить поиск находится в правой части таблицы.
Таким образом, чтобы подтянуть количество заказов, соответствующее первому артикулу (Кружка стеклянная), нужно прописать такую формулу:
Берем функцию ИНДЕКС и прописываем в ней диапазон в котором находится необходимое нам значение для переноса, в нашем случае это F2:F9.
Далее добавляем функцию ПОИСКПОЗ, которая будет выполнять поиск конкретного значения в указанном диапазоне. Выбираем значение, которое будем искать и сам диапазон, в нашем случае это значение ячейки А2 и диапазон поиска во второй таблице Н2:Н9.
В конце заполняем аргумент «тип сопоставления». Он указывает, каким образом сопоставляется найденное значение с искомым. Существует 3 типа сопоставления:
1 – значение меньше или равно искомому (при указании данного типа нужно учитывать, что просматриваемый массив должен быть упорядочен по возрастанию);
0 – точное совпадение;
-1 – наименьшее значение, которое больше или равно искомому.
Нам нужно точное совпадение, поэтому ставим 0.
У нас получилась формула =ИНДЕКС(F2:F9;ПОИСКПОЗ(A2;H2:H9;0)), не забудьте добавить к ней $ и привести к виду =ИНДЕКС($F$2:$F$9;ПОИСКПОЗ(A2;$H$2:$H$9;0)), чтобы корректно растянуть на остальные ячейки.
Таким образом, комбинация функций ИНДЕКС и ПОИСКПОЗ является полной заменой ВПР и обладает дополнительным преимуществом: умеет находить данные слева от столбца с критерием. Кроме того, сами столбцы можно двигать как угодно, лишь бы ссылка не съехала, чего нельзя проделать с ВПР, т.к. количество столбцов там указывается конкретным числом. Посему комбинация ИНДЕКС и ПОИСКПОЗ более универсальна, чем ВПР.
ЕСЛИ (if)
Функция ЕСЛИ выполняет проверку заданных условий, выбирая один из двух возможных результатов: если сравнение истинно и если сравнение ложно.
Синтаксис
Формула функции состоит из трех аргументов и выглядит следующим образом:
=ЕСЛИ(логическое_выражение;«значение_если_истина»;«значение_если_ложь»)
где:
- «логическое выражение» – формула;
- «значение если истина» – значение, при котором логическое выражение выполняется;
- «значение если ложь» – значение, при котором логическое выражение не выполняется.
Примеры
Рассмотрим пример использования обычной функции ЕСЛИ на примере нашей таблицы с заказами. Допустим у нас есть определенный план по заказам, который нужно выполнить. С помощью функции ЕСЛИ можно посчитать выполнен ли план исходя из количества полученных заказов по каждому товару.
Выполнение плана здесь обусловлено количеством заказов больше 30. Чтобы узнать по какому товару план выполнен, а по какому нет, нужно ввести следующую формулу:
=ЕСЛИ(D2>30;”не выполнен”;”выполнен”)
- Логическое выражение здесь – “D2>30”.
- «Значение если истина» – “не выполнен”.
- «Значение если ложь» – “выполнен”.
Функция ЕСЛИ является основой других формул: СУММЕСЛИ, СЧеТЕСЛИ, ЕСЛИОШИБКА, СРЕСЛИ. Мы рассмотрим три из них – СУММЕСЛИ, СЧеТЕСЛИ и ЕСЛИОШИБКА.
СУММЕСЛИ и СЧЕТЕСЛИ (sumif и countif)
СУММЕСЛИ и СУММЕСЛИМН
Функция СУММЕСЛИ позволяет суммировать данные, соответствующие условию и находящиеся в указанном диапазоне.
Синтаксис
Формула состоит из функции СУММЕСЛИ и трех аргументов и имеет вид:
=СУММЕСЛИ(диапазон;условие;[диапазон_суммирования])
- «Условие» – аргумент, определяющий какие именно ячейки нужно суммировать. Это может быть текст, число, ссылка на ячейку или функция. Обратите внимание на то, что условия с текстом и математическими знаками необходимо заключать в кавычки.
- «Диапазон суммирования» – необязательный аргумент. Позволяет указать на ячейки, данные которых нужно суммировать, если они отличаются от ячеек, входящих в диапазон.
Пример
В приведенном примере функция суммировала нашу выручку с товаров, которые стоят дороже 800 рублей. Получается формула: =СУММЕСЛИ(C2:C9;”>800″)
Подробнее по синтаксису. В примере функция вычислила сумму (7260), которая является сложением ячеек (С2:С9), удовлетворяющих условию (>800).
Если нужно суммировать ячейки в соответствии с несколькими условиями, можно воспользоваться функцией СУММЕСЛИМН.
Синтаксис
Формула функции имеет следующий вид:
=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)
«Диапазон условия 1» и «условие 1» – обязательные аргументы, остальные – необязательные.
Таким образом мы получаем формулу, которая может посчитать нам сумму, которую мы заработаем с товаров стоимостью больше 800 рублей и меньше 3000.
СЧЕТЕСЛИ и СЧЕТЕСЛИМН
Функция СЧЕТЕСЛИ считает количество непустых ячеек, соответствующих заданному условию внутри указанного диапазона.
Синтаксис
Формула функции:
=СЧЕТЕСЛИ(диапазон;критерий)
- «Диапазон» – группа ячеек, которые нужно подсчитать.
- «Критерий» – условие, согласно которому выбираются ячейки для подсчета.
Пример
Допустим мы хотим посмотреть количество наших товаров, которые стоят дороже 800 рублей.
Здесь мы через формулу =СЧЁТЕСЛИ(C2:C9;”>800″), где C2:C9 это диапазон ячеек, а >800 – выполняемое условие, вычислили количество товаров, которые приносят на больше прибыли.
Функция подсчитывает количество элементов, не указывая ссылок на них
В функции СЧЕТЕСЛИ можно использовать только один критерий. Если нужно сделать подсчет по нескольким условиям, примените функцию СЧЕТЕСЛИМН.
Аналогично СУММЕСЛИМН функция позволяет подсчитать данные, соответствующие нескольким заданным условиям. Каждому условию соответствует один вариант диапазона ячеек.
Формула функции:
=СЧЕТЕСЛИМН(диапазон_условия1;условие1;[диапазон_условия2;условие2];…)
«Диапазон условия 1» и «условие 1» – обязательные аргументы, остальные необязательны. Можно использовать до 127 пар диапазонов и условий.
Выводы
В данной статье мы рассмотрели основные формулы, которые пригодятся вам для анализа данных на маркетплейсах Ozon и Wildberries. Подробнее тему аналитики с данными для анализа и основными метриками эффективности мы разбираем на курсе “Аналитика на маркетплейсах Wildberries и Ozon”, где вы найдете всю информацию, необходимую для работы.
