Буби, крести, пики. Или своя CRM-cистема в Гугл Таблицах за пару часов

Так как моя статья немного коснется гемблинга, я сразу оговорюсь, что никакой рекламной цели я не преследую. Я хочу рассказать о том, как мы сделали удобную систему отчетности, используя Google таблички и коснуться некоторых моментов, возможно, кому-то это будет полезно.

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

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

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

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

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

image

Как это вообще будет работать? Cначала мы делаем клиентский файл, в котором пытаемся учесть все, что нужно. Называем его шаблон, прародитель, как угодно и далее, делаем копию для каждого нашего клиента и с помощью настроек доступа «расшариваем» эту копию только для его адреса gmail. У нас, как у создателя файла будут права владельца.

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

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

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

image

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

Как получить информацию из всех файлов? Ладно, мы раздали 20-50-100 файлов нашим клиентам, они их регулярно заполняют. Сейчас я покажу, как мы можем собрать из них информацию.

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

Рассмотрим importrange, с помощью этой формулы мы можем перенести диапазон одного файла в другой или сделать с этими диапазоном какие-то действия. Начнем со структуры, в нашей сводной таблице будут ссылки на все наши файлы в столбце А. Далее, допустим мы решили подсчитывать общую сумму по столбцу F каждого файла, формула будет выглядеть вот так:

image

А вот так можно вывести наибольшую дату в столбце В каждого клиентского файла:

image

Пример чуть посложнее, вот так из табличек можно вывести результаты за выбранный период (в ячейки b1 и c1 сводного файла вводим даты):

image

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

В результате — мы на коленках создали систему отчетности, в добавок — бесплатную. Если тема будет интересна, то я продолжу про Google Документы и расскажу интересные и сложные кейсы с формулами или про скрипты.

P.S. Если кто-то захочет посмотреть на формулы из сводной части вблизи, вот документ (Файл → Создать копию, чтобы открылась возможность его редактировать).
AdBlock похитил этот баннер, но баннеры не зубы — отрастут

Подробнее
Реклама

Комментарии 16

    0
    Точно так же используем систему отчётности для 50 магазинов.
    Всю финансовую аналитику ведём в Google Sheets. Информация подтягивается с кучи разных файлов в одно место. А дальше используются дэшборды с наиболее важными цифрами для руководства.

    За статью — спасибо. Не думал, что к importrage можно еще и формулы применять)
    А вот о втором способе про скрипт с циклом ничего не рассказали в статье.
      0
      наверное в следующих частях
        0
        Рад был рассказать что-то полезное! Да, раз есть интерес, то напишу в следующий про скрипт, с помощью которого можно ускорить работу табличек, если в них слишком уж много тормозящих формул.

        Вообще я веду канал в телеграмме про формулы в гугл табличках и разные кейсы с ними, не знаю, можно ли по правилам ресурса давать ссылки?
          0
          Я предполагаю, что t.me/google_sheets
            0
            Да, тема отчётности в G sheet очень интересна!
            Продолжайте делиться своим опытом. Быть может и я сподоблюсь когда нибудь написать статью и о своём опыте рассказать.

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

            Я в данный момент делаю табеля, систему премирования, систему расчёта з/п, отображение остатка наличных в кассе.

            И еще многое уже реализовано)
              0
              По поводу сбора с Pivot table — экселевской формулы (getpivotdata, или как-то так) в Google Табличках нет, но можно обойтись и без нее.

              Набросал пример, допустим, пивот в диапазоне a1:c7. Используем формулу СМЕЩ (она позволяет вывести диапазон или ячейку отступив от заданного диапазона введенное кол-во строк и столбцов).

              Например, мы хотим вывести ячейку на пересечение «центр А» и «доход 1», с помощью ПОИСКПОЗ находим в нашем пивоте строку-столбец (условия поиска в E1 и F1) и в итоге СМЕЩ смещается на правильное кол-во полей и мы значение той ячейки, которую искали.
              image
                0
                гугл-док, на всякий случай (файл-создать копию, чтобы видеть формулы и редактировать)
                  0
                  Очень интересный пример, спасибо!
                  Но он не совсем подходит в случае, если мы не знаем какого размера и какие данные в нём будут.

                  К примеру:
                  Есть файл с отчётностью, в который заполняют информацию о всех тратах наличности из кассы. Некий журнал учёта. Он состоит из даты, суммы и статьи затрат. На основе этих данных строится Pivot table.
                  Сложность заключается в том, что бы спарсить статьи затрат и их сумму из Pivot, потому что мы не знаем заранее в скольки днях из месяца будут затраты? А в один день может быть несколько затрат. Ну и т.д.
                  image
                  image
          +1
          Целая статья на хабре про то, что в гуглотаблицах можно писать формулы.
            0

            Спасибо, не знал, буду использовать.

              0
              Лет шесть назад делал самописную систему сбора отчетности в формате Excel для одной конторы. Принцип был точно такой же — шаблон на Excel, заполнение только обозначенных ячеек в шаблоне, поддержка проверки правильности введенных значений. Дополнительно была реализована система состояний отчетов, разные роли для пользователей (заполняющий, проверяющий, наблюдатель и прочее), связь между разными отчетными формами и разные типы сводных форм. Все это было разработано на DevExpress XAF WinForms так как я был единственным разработчиком и в то время редактор для таблиц Excel у них был только на WinForms.

              Сейчас все переписываю на Web с помощью все того же DevExpress XAF (все еще работаю один), пытаюсь сделать веб-сервис для сбора отчетности в формате Excel. Основные функции уже реализованы, сводная форма пока только одного типа — простое суммирование соответствующих ячеек, но на подходе Pivot и Dashboard от тех же DevExpress. Не знаю, можно ли дать ссылку.
                0
                Занятно, мне было бы интересно взглянуть
                  0
                  Краткое описание процесса работы

                  Основной функционал работает, но есть еще много чего что нужно доделать, начиная от дополнительных типов сводных форм и заканчивая оповещениями. Сайт примитивный, я знаю, пока сойдет и это.
                0
                Как у вас с производительностью? Я пытался с подобным образом собирать и обрабатывать данные по номенклатуре (прайсы, хар-ки, отчеты, прогнозы), но гугл-таблички заметно тормоозят примерно после ~20 тыс.строк при пару десятках столбцов.
                  0
                  Приветствую! У меня был похожий случай, было около 30 документов и из них нужно было взять по тысяче строк. Владелец таблички все собирал с помощью importrange и документ нечеловечески тормозил.

                  Что можно сделать:

                  1) скрипт в этом гугл-доке, который по временному триггеру либо по кнопке будет вставлять все importrange, далее формулы просчитываются и скрипт меняет их на значения.
                  2) другой скрипт, он будет открывать каждый файл по списку и брать оттуда нужный нам диапазон и копировать его в сводный файл.

                  Напишу чуть попозже про это, как раз думал следующий пост посвятить тому, что можно сделать для оптимизации табличек и ускорения их работы.
                    0
                    Здравствуйте! Вот здесь написал про простой скрипт, который позволяет ускорить работу с ресурсоемкими формулами: https://habrahabr.ru/post/331360/#comment_10277466

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

                  Самое читаемое