Начнем мы с разбора первого задания и сразу предупредим - не п | Google Spreadsheets | Аналитика, автоматизации, Telegram-боты

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

Итак нам дана таблица с продажами 3 сейлов за 2 месяца и нужно посчитать их зарплату по интересной мот схеме.

Как по нашей практике решают эту задачу?
Переносят список сейлов и месяцев руками, % КПИ и компенсацию считают через if. Имеет место быть, но нужно постоянно будет пополнять список руками и сама мот схема будет сложно масштабироваться (напр. если добавить еще одну ступеньку в грейды - формулы будут прям слишком громоздкими). Плюсом ко всему в условии необходимо использовать только три столбца - Сейл, Месяц и Сумма ЗП и другие столбцы мы использовать не можем (это вымышленное ограничение, но к таким хакам иногда приходится прибегать в итоговых отчетах для их лучшей читаемости).

Итого у нас 4 задачи:
- Вывести список сейлов и месяцев (их уникальные комбинации)
- Посчитать сумму продаж за месяц у конкретного сейла
- Посчитать % КПИ
- Посчитать компенсации

Первая задача решается с помощью следующей формулы:
=UNIQUE({C2:C9,arrayformula(МЕСЯЦ(A2:A9))})

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

Для расчета суммы используем следующую формулу:
=СУММ(ЕСЛИОШИБКА(filter($D$2:$D$9,$C$2:$C$9=$A19,ARRAYFORMULA(МЕСЯЦ($A$2:$A$9))=$B19)))

Тут используется комбинация =СУММ() и =filter(). Внутри все так же забираем месяц из даты.


3 и 4 задачи делаются супер похожим образом. Для этого нам нужно будет составить справочник для этой мот схемы (он может находиться на скрытом листе, поэтому начальные и итоговые данные он не затрагивает) и по нему с помощью ВПР с 1 в конце (работа с сортированным диапазоном).

В сокращенном виде это будет выглядеть так:
=ВПР(G19,$A$13:$C$15,2,1) - g19 - это сумма продаж

А в полном виде (где нам запрещено пользоваться вспомогательными столбцами) - так:
ВПР(СУММ(ЕСЛИОШИБКА(filter($D$2:$D$9,$C$2:$C$9=$A19,ARRAYFORMULA(МЕСЯЦ($A$2:$A$9))=$B19))),$A$13:$C$15,2,1)

И итоговая формула:
=$E$13 + ВПР(СУММ(ЕСЛИОШИБКА(filter($D$2:$D$9,$C$2:$C$9=$A19,ARRAYFORMULA(МЕСЯЦ($A$2:$A$9))=$B19))),$A$13:$C$15,2,1)+ВПР(СУММ(ЕСЛИОШИБКА(filter($D$2:$D$9,$C$2:$C$9=$A19,ARRAYFORMULA(МЕСЯЦ($A$2:$A$9))=$B19))),$A$13:$C$15,3,1)

Ниже прикрепляю ссылку на таблицу где выписывались все эти формулы:
https://docs.google.com/spreadsheets/d/12fOQX4EgoM8NV0dqaytqaKVtGd9vGd9BqDouC8C6WY4/edit?usp=sharing

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

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

Если же ты смог решить этот кейс также или лучше - обязательно напиши нам (@ottodice) скорее всего мы будем рады видеть тебя в нашей команде :)