Выгружаете базу из CRM или аналитики, открываете файл, а там — бардак. Имена капсом, телефоны с адресами слиплись в одну ячейку, артикулы разъехались. Всё это придется править вручную: столбец за столбцом, ячейка за ячейкой. Или нет?
Таблицы умеют делать большую часть такой работы сами — с помощью формул. Эти фишки есть не только в Excel и Google Sheets, но и в отечественных онлайн-редакторах — например, в Документах VK WorkSpace.
Разбираемся на конкретных задачах, как работать с таблицами без ручных правок: оформлять разнокалиберные списки, разделять слипшиеся данные и подтягивать цены из одной таблицы в другую.
Как вводить формулу
Поставьте курсор в пустую ячейку, введите знак «=», напишите имя функции и в скобках, что обработать — ссылку на ячейку с данными. Нажмите Enter, чтобы увидеть результат. Если не помните, как называется формула, откройте вкладку «Формула» → «Функция» и выберите из списка: редактор подскажет, какие аргументы нужны.
Пишите формулу рядом с данными, а не поверх них. Функция не меняет исходную ячейку, а выдает результат туда, где стоит сама. Например, чтобы исправить имена в столбце A, нужно ввести формулу в пустой столбец B.
В скобках указывайте одну ячейку, а не диапазон. Некоторые функции обрабатывают одно значение за раз: даете им ячейку — получаете результат по ней. Если такой функции дать сразу диапазон вроде A1:A20, получится ошибка.
Чтобы применить формулу ко всему столбцу, протяните ее вниз. Напишите формулу в верхней ячейке, наведите курсор на правый нижний угол — появится крестик — и потяните до конца списка. Формула скопируется на каждую строку и пересчитает ее по своим данным.
Копируйте значения. Получившийся столбец — это формулы: он считается из соседнего и без него работать не будет. Чтобы оставить только результат, скопируйте столбец с формулами и вставьте обратно. Затем нажмите кнопку вставки и в выпадающем меню выберите «Вставить только значение». После этого формулы превратятся в обычный текст, и исходный столбец можно будет удалить.
Приводим базу контактов к единому виду
Бывает, что перед рассылкой базу нужно привести в порядок — иначе велик риск, что имена в письмах будут со строчной буквы, а часть адресов не пройдет проверку из-за лишних пробелов или разнобоя в регистре.
Какие формулы помогут:
- СТРОЧН приводит почту к нижнему регистру: =СТРОЧН(A1) превратит «Ivan@Company.RU» в «ivan@company.ru».
- ПРОПНАЧ ставит заглавную букву в начале каждого слова: =ПРОПНАЧ(A1) исправит «иван петров» на «Иван Петров».
- СЦЕПИТЬ собирает строку из нескольких ячеек: =СЦЕПИТЬ(A1;" ";B1) склеит имя из A1 и фамилию из B1 через пробел.
- СОВПАД сравнивает две ячейки с учетом регистра: =СОВПАД(A1;B1) вернет ИСТИНА, если значения совпадают точь-в-точь, — так находят дубли.
- ПОВТОР повторяет символ нужное число раз: =ПОВТОР("-";10) поставит десять дефисов подряд, удобно для разделителей.
Вытаскиваем из строки нужный кусок
Бывает, что нужно не собрать данные, а достать часть из строки. Например, домен из почты, имя из полного ФИО, кусок до или после разделителя. Раньше под это писали макросы — специальные мини-программы, — а теперь хватает готовых функций.
Эти формулы работают с одной ячейкой и протягиваются вниз по столбцу:
- ТЕКСТДО берет всё, что идет до знака: =ТЕКСТДО(A1;" ") вытащит из «Иван Петров» имя — всё до первого пробела.
- ТЕКСТПОСЛЕ забирает хвост после знака: =ТЕКСТПОСЛЕ(A1;"@") достанет из «ivan@company.ru» домен company.ru.
Для более сложных задач есть еще три функции. Они работают с массивами данных и устроены сложнее — если такая задача встает часто, их стоит освоить отдельно:
- ТЕКСТРАЗД режет строку сразу на несколько ячеек по разделителю.
- ВСТОЛБИК ставит несколько диапазонов друг под друга в один столбец — удобно, когда списки с разных листов нужно слить воедино.
- СВЕРНСТРОК раскладывает один длинный ряд значений по строкам, разбивая его на нужное число столбцов.
Связываем две таблицы между собой
Допустим, у нас есть прайс в одной таблице и список заказов в другой — и нужно подтянуть цены к артикулам. Или нужно подставить остатки со склада к позициям в отчете. Ускорить процесс помогут две формулы:
- ВПР ищет значение в первом столбце таблицы и возвращает то, что стоит с ним в одной строке: =ВПР(A2;E:F;2;0) найдет артикул из A2 в прайсе и подтянет цену из второго столбца, а 0 на конце означает «точное совпадение».
- ИНДЕКС + ПОИСКПОЗ — поможет, если над файлом работают несколько человек и колонки могут двигаться. Работает в два шага: ПОИСКПОЗ находит, в какой строке стоит нужный артикул, а ИНДЕКС достает из этой строки значение нужного столбца. Такой поиск не сбивается от вставки колонок и ищет в обе стороны — и влево, и вправо.
Считаем партии, остатки и распределения
Логистам и закупщикам нужно разбивать заказы по диапазонам, делить товар на партии, рассчитывать фасовку. В этом помогут математические функции:
- ЧАСТНОЕ делит и возвращает только целую часть, без остатка: =ЧАСТНОЕ(100;12) покажет 8 — столько полных коробок по 12 штук выйдет из сотни.
- КОРЕНЬ извлекает квадратный корень: =КОРЕНЬ(144) вернет 12. Пригодится в расчетах площадей и в статистике.
- НОД находит наибольший общий делитель нескольких чисел: =НОД(24;36) вернет 12 — например, по столько единиц можно ровно разложить обе партии.
- НОК считает наименьшее общее кратное: =НОК(4;6) вернет 12 — через столько дней совпадут отгрузки с циклами в 4 и 6 дней.
Формулами в таблицах можно пользоваться не только в офлайн-программах, но и в облачных редакторах. Плюс таких решений в том, что с файлом может работать сразу вся команда: редактировать удаленно, просматривать версии и историю изменений. При этом не множатся копии и итоговый вариант не потеряется у кого-нибудь в ноутбуке.
На платформе VK WorkSpace есть всё для совместной работы: облачное хранилище, редактор текстовых документов, таблиц и презентаций, корпоративная почта, мессенджер, видеозвонки, онлайн-доски, инструменты для работы с проектами и многое другое. Сервисы доступны в одном приложении на ПК и телефоне.
А вы пользуетесь формулами в таблицах? Помогают ли они вам не ошибаться и экономить время? Делитесь в комментариях!
Подписывайтесь на канал: разбираемся, как российские сервисы работают с реальными задачами бизнеса.