Filter - универсальная связка

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

• У вас есть список сотрудников и напротив каждого нужно выводить, сколько он продал за текущий месяц/день
• У вас есть реестр всех заказчиков и по каждому нужно показывать последний статус и содержание переписки одного из ваших менеджеров
• Вам необходимо посчитать сумму покупок, сделанных по безналу/наличке/в долг или еще как-то

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

Разбор

Рассмотрим пример работы функции:

Мы суммируем список значений с листа операции в столбце G (это у нас сумма покупки, как видно на рис.2), при этом список этих значений должен удовлетворять условию, что название заказчика на листе операции равно значению ячейки B3 на листе "Клиенты", то есть "Сырная губерния". Если перевести этот запрос на человеческий язык, то будет как-то так: "Суммируй мне все суммы покупок, где название клиента "Сырная губерния"".

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

Важно знать

• Функция Filter() может выдавать несколько значений, если они все удовлетворяют условиям. Поэтому нужно убедиться, что ячейки, где будет выводиться этот список пусты, иначе будет отображаться ошибка

• Если необходимо, чтобы в итоге выводилось одно значение, то этого можно добиться агрегирующими функциями (sum, count, average и др.), либо быть точно уверенным, что выведется только одно значение, как я это сделал на листе "Клиенты" с выводом последнего комментария (пример разберу отдельно, если проголосуете, что это интересно)
• Если фильтр не нашел ни одной записи, то он выдает ошибку #N/A. Это довольно неприятная особенность, поскольку если выборка по фильтру является только частью вычислительной формулы, то результат всех вычислений становится #N/A.
• Чтобы избежать ситуации с #N/A, каждый фильтр, который может выдать такую ошибку нужно окружать проверкой if(isna(тут формула с фильтром);ноль_или_пустые_кавычки;(тут снова повторяем формулу с фильтром)). ISNA() - выдает True, если значение в скобках выдало #N/A. А If()- позволяет вывести ноль или пустое место " " вместо ошибки. Такое дублирование значительно усложняет чтение формул, но другого способа защититься от ошибки #N/A я не нашел

• Вместо функции Filter() можно использовать перемножение массивов, как я делал в самом начале, но работа с массивами сложнее в понимании потребляет в разы больше ресурсов, чем Filter(), и вызывает тормоза даже не сравнительно несложных объемах.

• Здесь и далее я буду использовать формулы на английском языке, потому что так привык и считаю кармически правильным программировать на английском. Но вы можете использовать формулы на русском. Работают они точно так же :-)

Заключение

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

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

===========================================

Если вам нравится канал и мои статьи, то подписывайтесь на него и подключайте своих друзей, кому это может быть полезно. Чем больше заинтересованных людей, тем больше у меня мотивации писать чаще и детальнее.
Ссылка на канал: https://t.me/googlesheetmanagement

Ссылка на таблицу, рассматриваемую в статье: https://docs.google.com/spreadsheets/d/1CNtdYEVGmbGI-EeQ0fMscZhgJlgBFGSpqDwwUCFR9sE/edit#gid=488354144