Формулы в Google Таблицах на примере заявок
Таблица заявок растёт, а ответы на простые вопросы — сколько заявок из Москвы, кто их ведёт, нет ли дублей — ищут глазами. Разбираем 14 формул Google Таблиц на одном примере: как написать каждую по-русски и по-английски и где чаще всего ошибаются.
Как устроена формула
Формула начинается со знака «=», дальше — функция и аргументы в скобках: =СУММ(E2:E9). Функции можно писать по-русски или по-английски: по справке Google, «работать с функциями можно на любом из поддерживаемых языков (сейчас их 22, включая английский)». У части функций русского имени нет — UNIQUE, FILTER, QUERY, IMPORTRANGE, ARRAYFORMULA пишутся только латиницей.
В русских таблицах аргументы обычно разделяют точкой с запятой — так написано и в русской справке: IMPORTRANGE(ключ_таблицы; диапазон). Если таблица не принимает «;», проверьте «Файл → Настройки таблицы → Региональные настройки»: от региона зависит формат чисел, а с ним и разделители. Знак $ закрепляет ссылку: $D$2:$D$9 не съедет, когда формулу протянут вниз.
Считать: СУММ, СЧЁТЕСЛИ, СУММЕСЛИ
Пример — восемь заявок: дата, имя, телефон, город, сумма и статус (лист «Заявки» в шаблоне).
СЧЁТЕСЛИ «подсчитывает количество ячеек, соответствующих заданному условию», СЧЁТЕСЛИМН — то же «на основании нескольких критериев» (справка Google).
Чистить: СЖПРОБЕЛЫ, ПОДСТАВИТЬ, ПРАВСИМВ
После этого «8 912…» и «+7 (912)…» — одинаковые строки, и дубли по телефону находятся простым сравнением.
Искать: ВПР и ЕСЛИОШИБКА
=ВПР("Пермь";Справочник!A2:B4;2;ЛОЖЬ) ищет город в первом столбце справочника и возвращает менеджера из второго — «Ольга». ЛОЖЬ в конце — точный поиск: без него функция ищет приблизительно и на несортированном справочнике может вернуть чужую строку.
Если города в справочнике нет, ВПР вернёт ошибку. Обёртка =ЕСЛИОШИБКА(ВПР(…);"не назначен") покажет понятный текст вместо #N/A.
Находить дубли
=ЕСЛИ(СЧЁТЕСЛИ(B2:B9;"*"&СЖПРОБЕЛЫ(B5)&"*")>1;"дубль";"") — считает, сколько раз имя встречается в колонке, даже с пробелами по краям. Для Ирины Котовой — «дубль»: она оставила заявку дважды, один раз с пробелами в начале имени.
Надёжнее искать дубли по телефону, приведённому к одному виду, — формулами из раздела выше. Подробно, с цветовой подсветкой, — в статье «Как найти дубли в Excel по телефону и e-mail»: те же формулы работают и в Google Таблицах.
Только в Google Таблицах: UNIQUE, FILTER, QUERY, IMPORTRANGE
Цитаты — из списка функций в справке Google Таблиц. Эти формулы «разливаются»: результат занимает столько строк, сколько нужно, поэтому ниже и правее должно быть пусто.
Шаблон: 14 формул на примере заявок
В шаблоне четыре листа: «Как пользоваться», «Формулы» — десять задач с формулой по-русски, по-английски и живым результатом и ещё четыре формулы только для Google Таблиц, «Заявки» — пример базы с лишними пробелами, телефонами в разном виде и дублем, «Справочник» — города и менеджеры. Откройте файл в Google Таблицах (Файл → Импортировать) или в Excel.
Скачать шаблон с формулами · xlsx, 13 КБ
Из таблицы — в amoCRM без формул
Если заявки собираются в таблицу, а работают с ними в amoCRM, чистить и сводить их формулами не обязательно. «Импорт из Google Таблиц» переносит строки в сделки, контакты и компании: вы выбираете, какая колонка в какое поле попадает, а значения читаются в том виде, как их пишут люди, — телефоны «8 (999) 123-45-67», «+7 999 123 45 67» и «9991234567» становятся +7 999 123-45-67, «15 000 ₽» и 15000 — одно и то же.
Клиент ищется по телефону, email и ИНН: есть открытая сделка — обновится она, все закрыты — создастся новая у того же контакта. Сначала проверка на 5 строках, потом запуск; по каждой строке видно, что с ней сделано, и загрузку можно откатить. 999 ₽ в месяц за аккаунт (оплата от 6 месяцев: 5 994 ₽ за 6 + 1 в подарок); пробный период — первые 70 загруженных строк.
Как настроить перенос заявок — в статье «Заявки из Google Таблицы в amoCRM».
Для таблицы заявок хватает 14 формул: СУММ, СЧЁТЕСЛИ, СУММЕСЛИ и СЧЁТЕСЛИМН считают, СЖПРОБЕЛЫ, ПОДСТАВИТЬ и ПРАВСИМВ чистят имена и телефоны, ВПР с ЕСЛИОШИБКА подставляет менеджера, СЧЁТЕСЛИ находит дубли, а UNIQUE, FILTER, QUERY и IMPORTRANGE работают только в Google Таблицах. Функции пишутся по-русски или по-английски.
Частые вопросы
Converssion CRM — официальный партнёр amoCRM с 2017 года. Внедряем и сопровождаем amoCRM, делаем собственные виджеты для амоМаркета, чат-ботов, ИИ-агентов, сайты и рекламу. Услуги · Кейсы клиентов · Виджеты
