Google Spreadsheets | Аналитика, автоматизации, Telegram-боты

2023-03-03 13:41:38 Небольшое upd по каналу - добавили возможность оставлять комментарии
Если остаются вопросы или жгучее желание дать обратную связь - пишите, запросы не останутся без ответа Открыть/Комментировать

2023-03-03 08:44:23 Сегодня будет пятничный лайтовый контент
Начнем с полезностей - функция вертикального поиска или ВПР() VLOOKUP(). Невероятно полезная, используется почти в каждом клиентском кейсе. Что же в ней такого полезного?
ВПР находит нужное нам значение в массиве данных и выдает значение напротив. Это если просто. Приведу пример.
1 - Синий
2 - Белый
3 - Желтый
4 - Красный
5 - Оранжевый
Предположим что нам нужно узнать какой цвет стоит напротив цифры 3. Тогда формула будет выглядеть примерно так:
=ВПР(3; ДАННЫЕ; 2; 0)
3 - это искомое значение
ДАННЫЕ - это диапазон с данными
2 - это номер столбца откуда нужно забрать ответ
0 - о нолике позже, пока просто запомним что в 95% ВПРов пишем именно 0.
Результатом выполнения функции будет текст “Желтый”. Открыть/Комментировать

2023-03-02 19:01:00 Продолжаем говорить про массивы. Некоторые формулы после отработки "вписывают" в таблицу массив. Напр. этим любит заниматься функция FILTER(). Если в результате фильтрации будет больше одного значения - результатом будет массив. Массив будет стараться занять все необходимое ему место. Т.е. если в массиве 3 строки - 3 строки он и будет пытаться собой занять. Если ячейки будут заняты и массив не сможет найти для себя свободного пространства - он выдаст соответствующую ошибку.
Кстати про функцию фильтр. Если мы детально разберем из чего она состоит =FILTER (ВЫВОДИМЫЕ ЗНАЧЕНИЯ; УСЛОВИЕ ФИЛЬТРАЦИИ 1; УСЛОВИЕ ФИЛЬТРАЦИИ 2), то увидим, что каждый элемент формулы - это массив и нужно чтобы каждый массив имел одинаковую высоту или длину (иначе FILTER() выдаст ошибку).
Есть формулы, которые хорошо кушают массивы внутри фильтра. Например функция REGEXMATCH(), которая проверяет содержится ли кусок строки внутри ячейки вполне себе лояльно применяется поочередно к каждой строке, если использовать его внутри фильтра.
(напр. FILTER( ВЫВОДИМЫЕ ДАННЫЕ; REGEXMATCH(ДИАПАЗОН ФИЛЬТРАЦИИ1; ЧАСТЬ СТРОКИ ДЛЯ ПОИСКА)
Есть функции которым нужно помогать. Например функция МЕСЯЦ() не всегда корректно отрабатывает и ей нужно принудительно указать, что к данным внутри себя нужно относиться как к массивам. Для этого покрываем функцию МЕСЯЦ() функцией ARRAYFORMULA(). Важно! Результатом ARRRAYFORMULA() всегда является массив.
прим. В ГТ это делается куда интуитивнее чем в экселе.
Вернемся к заданию:
=UNIQUE({ C2:C9 , arrayformula(МЕСЯЦ(A2:A9))})
Т.о. в начале задания мы собираем массив, первый столбец которого это имя сейла (столбец С2:С9), а второй столбец - это столбец дата, к каждому значению которого была применена функция МЕСЯЦ(). Потом поверх всего этого была применена функция UNIQUE(), которая убирает все дубли строк.

Послесловие:
Когда мы собираем сложные и длинные формулы нам важно понимать что происходят в каждой из ее частей, где мы получаем значение, где мы получаем массив и что мы сможем с этим делать. Особенно это становится важно, когда мы начинаем перекидывать данные между таблицами с помощью функции импорта. Изначальный реестр может содержать в себе десятки тысяч строк и десятки столбцов данных, из которых нам может быть нужно только 2 столбца для написания функции ВПР (о ней в следующих постах). В таком случае нам лучше сформировать массив из двух импортируемых столбцов, чем импортировать весь гигантский реестр, для нагруженных таблиц это бывает (и довольно часто) критически важно. Открыть/Комментировать

2023-03-02 15:00:17 Разбираем по кирпичикам первое задание и начнем мы со значений и массивов.
Со значениями все просто - это либо статичные данные в ячейке, либо результат вычисления формулы.
С массивами все сложнее. Хоть оно и называется массивом, оно отличается от аналогичного понятия в программировании. По сути массив - это упорядоченный набор данных, но в ГТ к нему нельзя обратиться по номеру (для этого можно использовать формулу INDEX(), но это обращение через формулу). Массивы можно разделить на 3 типа - строка, столбец и прямоугольник (при этом прямоугольник это либо строка столбцов, либо столбец строк - это важно при сборе массивов вручную).
Массив в ГТ можно объявить с помощью фигурных скобок { }. При этом разделители внутри массива отличаются в зависимости от настроек языка в аккаунте ГТ. Для русского варианта это \ и ;, в английской - , и ;. Один разделить для того чтобы разделять строки, другой - для разделения столбцов. На скрине как раз результат ручной записи массива. Открыть/Комментировать

2023-03-02 09:30:00 Кто особо внимательно следил за данными в исходной таблице могли заметить, что в одной из строк допущена опечатка. Сделано это умышленно и здесь мы проверяем что будет делать потенциальный соискатель. Здесь три варианта действия:
- Не заметить/забить - для нас это такое себе
- Молча исправить - лучше прошлого варианта, но все еще не ок
- Подсветить в данных, что есть возможная опечатка - единственно правильный вариант.
Опечатка уровня Сейл 1 и Селй 1 - это явная опечатка, но в компаниях на 100 человек вполне могут быть люди с одинаковой фамилией и разными инициалами или очень похожими. В таких случаях нужно проявлять особую бдительность и перепроверять данные, в т.ч. синхронясь с заказчиком. Открыть/Комментировать

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

Итак нам дана таблица с продажами 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) скорее всего мы будем рады видеть тебя в нашей команде :) Открыть/Комментировать

2023-03-01 16:45:10 И давайте сразу к полезностям)
Ниже выкладываем тестовое задание, которые мы даем аналитикам на собеседовании. По нашему опыту с первыми тремя задачами справляются 10% кандидатов, а с 4 не справился еще никто. Поэтому это отличный способ попробовать свои силы если ты аналитик:
Тестовое задание

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

В следующих постах мы будем разбирать решения этих тестовых задачек, обязательно смотри их, т.к. каждая из этих тестовых задач - это упрощенные версии реальных кейсов с которыми мы работаем. Открыть/Комментировать

2023-03-01 16:37:37 Добро пожаловать :)

У нас ты узнаешь про:
- Гугл таблицы, их базовые, продвинутые и экспертные функции
- Гугл скрипт, как писать скрипты в гугл таблицах и как они помогают автоматизировать рутину
- Интеграцию гугл таблиц и телеграм-ботов - незаменимый инструмент для небольших автоматизаций, которые экономят сотни тысяч рублей каждый месяц
- Хорошие практики, крутые кейсы, задачки

Канал будет полезен тебе если:
- Ты хочешь связать свою жизнь с таблицами и быть крутым аналитиком и зарабатывать от 50 000 на старте
- Ты уже занимаешься таблицами, но хочешь прокачаться
- Ты занимаешься финансами и хочешь автоматизировать рутину
- Ты собственник бизнеса и хочешь получать актуальные данные в режиме онлайн для принятия решений
- Ты собственник бизнеса и хочешь сэкономить от 100 000 ежемесячно на повторяющейся рутине

Немного о нас:
Привет, меня зовут Иван и я занимаюсь автоматизацией в таблицах с 2015 года. За это время успел вдоль и поперек изучить Excel и Гугл таблицы, и мое сердце навсегда принадлежит именно детищу гугла. С помощью гугл таблиц и телеграм ботов можно автоматизировать почти все процессы в небольшой компании - тайм-трекинг сотрудников, учет финансов, СРМ, различные метрики отделов и связать все это в одну экосистему и вывести в единый красивый дашборд. При этом не нужно будет содержать собственный парк дорогих разработчиков или ежемесячно оплачивать хостинг сервера. Кейсы автоматизации которые мы делаем с моей командой C-tables стоят в 2-3 раза ниже рыночной, а поставка ценности (релиз продукта) происходит в 2 раза быстрее. Есть ли здесь подвох? Да - эти автоматизации плохо выдерживают большую нагрузку и подходят только для команд до 100 человек, поэтому у нас есть своя узкая ниша где мы создаем сверх-ценность для клиента.
За время работы с таблицами я автоматизировал:
- Аналитику и прогнозку в продажах
- Аналитику и прогнозку в найме
- Процессы передачи задач между отделами
- Тайм-трекинг
- Учет финансов
- Начисление зарплат всей компании по довольно сложным мот схемам
- Игры типа квизов, тайного Санты, бункера и т.д.

Поэтому, если чувствуешь что у тебя болит что-то из вышеперечисленного, пиши мне (@ottodice) и, мы проведем с тобой бесплатную консультацию и сходу включимся в работу) Открыть/Комментировать