Как собрать 1000 гостей с помощью магии таблиц и формул и не сойти с ума

Привет! Меня зовут Даша, и я занимаюсь организацией ивентов в группе компаний Kokoc Group, которая объединяет более 30 маркетинговых агентств. На днях мы отпраздновали 20-летие нашей компании, и это событие стало настоящим грандиозным мероприятием с 1000 приглашенных гостей. Я отвечала за сбор и модерацию всех гостей, и хочу поделиться своим опытом — как справиться с этой задачей и не потерять при этом разум. Кроме того, мне кажется, что многие не до конца понимают, что именно было сделано и в чем заключается «магия» этого процесса. Давайте рассмотрим это подробнее!

Каждое агентство в составе Kokoc Group подготовило свои списки тех, кого они планируют пригласить. Эти списки включали имена, фамилии, названия компаний, должности и контактные данные. В итоге у нас получилось чуть больше 20 разных таблиц, которые заполняли 150 человек. Пользоваться системой в таком формате, конечно, было не очень удобно. Мы понимали, что нужна общая таблица для хранения всех агрегированных списков, но сразу возникли сложности.

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

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

Я обожаю Google Таблицы, различные формулы и процесс автоматизации. Поэтому, когда я увидела, как моя коллега просто скопировала данные из 20 таблиц и вставила их в одну, я немного ужаснулась. У меня возникли вопросы: как мы будем отслеживать изменения, если кто-то вносит правки? Как вести контроль над этими данными, чтобы не потеряться в хаосе? Решив не полагаться на ручную работу, я решила автоматизировать процесс с помощью формул.

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

Первым делом я настроила перенос данных из командных таблиц на отдельные листы нового общего документа с помощью формулы IMPORTRANGE. Формула выглядит так: `=IMPORTRANGE(“ссылка на исходный документ”;”диапазон”)`. Я была удивлена, узнав, что эта формула может забирать целый диапазон, например, `Лист1!A5:Z1000`. Это значит, что все данные в этом диапазоне будут автоматически передаваться в мою новую таблицу.

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

Тут я обратилась за советом к чату GPT, который подсказал использовать формулу FILTER. Она позволяла отфильтровать непустые значения с каждого листа. С помощью фигурных скобок я объединила отфильтрованные значения в один общий список.

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

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

Есть простое решение: ставить прочерк, если поле пустое. Но периодически мне приходили сообщения, что списки «съехали»: у Алины, например, фамилия не Иванова, а Катя работала не в компании «Рога и Копыта». Если у вас есть идеи, как можно было бы решить эту проблему, с удовольствием выслушаю ваши предложения!

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

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

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

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

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

Как я решила эту проблему? Я настроила еще одно условное форматирование, которое подсвечивало фамилии и компании. Формула проверяла, есть ли данная фамилия в списке приглашенных, и если находила совпадение, то подсвечивала её салатовым. Я сама проверяла, совпадают ли остальные данные и, если да, то одобряла регистрацию.

А что делать с теми, кто зарегистрировался, но в списке приглашенных не нашелся ни по контактам, ни по фамилии и компании? Мне было важно разобраться с этими «потеряшками». Я решила создать для них отдельную таблицу, чтобы мои коллеги могли с легкостью их находить и добавлять в свои списки, а я — одобрять их регистрацию.

Первоначально я собирала данные вручную, и это оказалось настоящим испытанием: постоянное копирование из двух таблиц было утомительным, а иногда забывала удалить тех, кого уже нашли. Конечно, это было совсем неэффективно. Я поняла, что здесь тоже нужна автоматизация!

На помощь снова пришла формула FILTER. Я добавила в списки заполнивших форму регистрации столбец «Статус» с раскрывающимся списком, где отмечалось значение «не найден в списке». Затем я организовала формулу FILTER, которая отбирала все записи со статусом «не найден в списке», а с помощью IMPORTRANGE передала данные из регистраций с лендинга и бота. В итоге, на отдельном листе с использованием той же формулы FILTER я объединила непустые значения в один аккуратный список. Таким образом, у меня получился автоматически обновляемый список потеряшек, где единственным моим заданием оставалось периодически напоминать коллегам заглянуть в него, и вся система работала в своем круговороте данных, как хорошо настроенный механизм!

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

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

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

Однажды ко мне подошла коллега с вопросом, который стал для меня отправной точкой. Она рассказала, что клиент, которого они не приглашали, поблагодарил за приглашение и сказал, что будет рад увидеться на мероприятии. «Можно как-то увидеть, кто его пригласил?» — спросила она. Эта мысль осенила меня: почему бы не сделать полный список приглашающих и приглашенных, чтобы избежать подобных ситуаций?

Я быстро создала новую таблицу с помощью формулы IMPORTRANGE, куда передала лишь приглашающего, имя, фамилию и название компании каждого гостя. Так появился автоматизированный «Полный список приглашенных без контактов». Но дальше мне пришла в голову ещё одна идея: если у меня уже есть такой список, почему бы не отображать в нем успешно промодерированных гостей? Это было бы невероятно удобно — можно было бы легко находить свою фамилию и видеть, кто из приглашенных зарегистрировался, а кто еще нет.

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

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

Но я знала: людям нравится, когда всё максимально понятно. К тому же я боялась предоставлять прямой доступ к выданным куаркодам. Поэтому я добавила отдельный столбец «Статус», в котором с помощью формулы ЕСЛИ отмечалось, зарегистрирован гость или нет. Если поле с куаркодом пустовало, ставился прочерк; если нет — добавлялось значение «зарегистрирован».

В дополнение я настроила условное форматирование с формулой ЕСЛИ: если в поле стояло значение «зарегистрирован», вся строка выделялась зеленым. Затем, используя IMPORTRANGE, я передала только значения из столбца «Статус» в мой «Полный список приглашенных без контактов» и также настроила для него выделение зарегистрированных зеленым цветом.

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

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

Потратив пару часов на настройку автоматического подставления статуса «зарегистрирован» я подтянула эти данные во все изначальные 20 таблиц. По моему замыслу, условие выглядело так: если в списке приглашенных совпадали емейл и телефон с данными из регистраций с лендинга и бота, то подставлялся выданный куаркод. Если поле с куаркодом было заполнено, это означало, что контакт зарегистрирован.

В дополнение к этому я настроила условное форматирование: зарегистрированные гости выделялись зеленым цветом, а те, кто не сможет прийти — красным. Тем самым все могли видеть, кто подтвердил свое участие, а кто нет. Я скрыла ненужные данные от глаз коллег, чтобы упрощать работу с таблицами и делать её более интуитивно понятной.

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

Возможно, для многих из вас формулы и автоматизация не кажутся особенно интересными, но именно они делают нашу работу значительно легче. За время подготовки к мероприятию было создано и активно использовалось моими коллегами 32 разных таблицы. Управлять таким количеством данных вручную было бы просто невозможно, особенно с учетом того, что их заполнением занимались около 150 человек (а, возможно, и больше — точно сказать не могу). Признаюсь, мне доставляет удовольствие «наряжать» эти таблицы формулами и заставлять их работать вместо меня!

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