Google Таблицы

Ошибки в формулах Google Таблиц

А-а-а! Формула не работает! Что делать (кроме как сходить к коллеге, у которого больший опыт в Таблицах)?

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

Благодарим Михаила Смирнова за помощь в подготовке статьи

Для тех, кто в веб-версии и не видит ссылку: https://teletype.in/@renat_shagabutdinov/fgeGs-67J

Работаем со вшитыми (богатыми 😊) в ячейки ссылками
Примеры простых скриптов

Друзья, встречалась вам ситуация, когда приходилось обработать столбец, в котором были вшитые в текст ссылки?

Такие ссылки нельзя отделить от текста с помощью формул, но можно используя скрипты, а именно метод RichTextValue.

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

function fromUrl() {
//определяем активный диапазон, активный лист и забираем значения
const range = SpreadsheetApp.getActiveRange();
const sheet = SpreadsheetApp.getActiveSheet();
const richText = range.getRichTextValues();

//наполняем в цикле массив: текст, ссылка, формула
const values = richText.map(g => [g[0].getText(), g[0].getLinkUrl(), `=HYPERLINK("${g[0].getText()}";"${g[0].getLinkUrl()}")`]);

//вставляем, ориентируясь на границы выделенного диапазона
sheet.getRange(range.getRow(), range.getColumn() + 1, values.length, values[0].length)
.setValues(values)
}

И обратное превращение: выделяем диапазон [текст, ссылка] → получаем вшитые в текст ссылки

function toUrl() {
//определяем активный диапазон, активный лист и забираем значения, создаём пустой массив
const range = SpreadsheetApp.getActiveRange();
const sheet = SpreadsheetApp.getActiveSheet();
const values = range.getValues();
let richText = [];

//наполняем массив в цикле, добавляя вшитые ссылки (RichTextValueBuilder)
values.forEach(g => {
richText.push([SpreadsheetApp
.newRichTextValue()
.setText(g[0])
.setLinkUrl(g[1])
.build()]);
});

//вставляем
sheet.getRange(range.getRow(), range.getColumn() + 3, values.length, 1)
.setRichTextValues(richText)
}

Таблица с кодом, в которой все работает

Код с комментариями отдельно (если сложно читать в посте)

=
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
☺️ ДОНАТЫ: реквизиты

Отправлятор СМС 2 (+ добавили автоматическую отправку)

Друзья, привет! Когда-то давно мы написали пост про отправку СМС из Таблицы: в нём про сервис, который можно использовать и как формировать правильные ссылки для отправки.

В первой версии на каждую ссылку нужно было кликнуть, перейти и после этого сообщение отправлялось, сейчас апдейт – больше вам кликать никуда не надо, мы добавили для этого скрипт.

Инструкция
Таблица со скриптом

P. S. Спасибо нашему чату за идею.

Предлагайте в комментарии темы для будущих постов, про самое интересное напишем на канале.

=
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
☺️ ДОНАТЫ: реквизиты

Пользовательские форматы в Google Таблицах

Друзья, посмотрите на эту красоту на скриншоте. В столбце D "Прирост" нет условного форматирования или картинок. Это обычный числовой формат! Но не простой, конечно, а пользовательский. Пользовательские форматы - мощная штука, позволяющая создавать числовой формат специально под ваши задачи с помощью специальных кодов. Например, у формата ячеек на скриншоте код такой:
[Color10]+0% * ▲;[Color53]-0% * ▼

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

https://shagabutdinov.ru/custom_format/

Файл со всеми примерами из статьи

=
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
☺️ ДОНАТЫ: реквизиты

СОБИРАТОР 4.0

В прошлом году мы выпустили свой СОБИРАТОР – интерфейс для сбора других Таблиц через sheets api (самый быстрый скриптовый способ).

Задаёте правила сбора Таблиц в другие Таблицы и собираете, к примеру, 50 Таблиц ваших продавцов в одну сводную Таблицу и 30 Таблиц в другую сводную Таблицу, на лету фильтруя и оставляя только нужные столбцы.

Сегодня выпускаем обновление:
— теперь можно указать частоту загрузки каждой таблицы в минутах;
— можно выбрать тип копирования данных (formula / formatted_value / unformatted_value);
— решена проблема с добавлением новых данных: раньше они (иногда) вставлялись со сдвигом;

Главное:
Решение стало полностью автоматическим – теперь один раз заполняете конфиг сбора, выставляете частоту обновления в минутах, ставите скрипт в расписание и вуаля – данные собираются регулярно и уже без вашего участия.

СОБИРАТОР

ИНСТРУКЦИЯ

=
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
☺️ ДОНАТЫ: реквизиты

Запускаем скрипт по чекбоксу (работает и из мобильной версии Таблиц).

Недавно в нашем чате спросили – а как запускать скрипты, если у меня ведройд.

Автор вопроса имеет ввиду, что у него телефон на базе платформы Android c приложением Таблицы и там в Таблицах не работают скрипты, привязанные к кнопкам и не видны меню скриптов.

Но способ запускать скрипты есть:
1) создаём функцию starter() с параметром e (объектом события), эта функция сможет определить ячейку / лист, которые редактируются сейчас.

В функции – ЕСЛИ, проверка – редактируется ли ячейка на Листе1 ячейка А1. Если истина – запускаем нашу целевую функцию hellogirls().

function starter(e) {
if (e.source.getActiveSheet().getName() == 'Лист1' && e.range.getA1Notation() == 'A1') {hellogirls();}
}

2) Ставим функцию starter() на триггер (редактор скриптов > триггеры > добавление триггера > starter > при редактировании Таблицы);

3) Профит! Теперь при изменение чекбокса на Лист1 запускается функция hellogirls(). В том числе с мобильного устройства!

✔️☑️ ПРОСТЫЕ СОВЕТЫ ПРО ФЛАЖКИ (ОНИ ЖЕ ЧЕКБОКСЫ)

1) включаем / выключаем флажки массово:
выделяем мышкой диапазон и пробел

2) выключаем флажки через скрипты, clearContent():
const sheet = SpreadsheetApp
.getActive()
.getSheetByName('Лист1');

sheet.getRange("a1:a10").clearContent();

или вставляем в диапазон false или 'false':
sheet.getRange("a1:a10").setValue(false);

3) включаем флажки, вставляем в диапазон true или 'true':
sheet.getRange("a1:a10").setValue(true);

4) и классические варианты включения / отключения: check() / uncheck():
sheet.getRangeList(["c3:c9","c11:c20"]).uncheck();

P.S. пасхалка про чекбоксы от почётного модератора нашего чата @IT_sAdmin :)

=
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
☺️ ДОНАТЫ: реквизиты

Функция ВЫБОР и номер квартала
А еще про то, как организовать данные правильно

Допустим, в таблице есть данные за 12 месяцев, и на каждый из них приходится несколько столбцов (e.g. факт, прогноз, отклонение и т.д.)

Наша задача — суммировать данные за текущий квартал.
Сначала определим номер квартала по текущему месяцу:
ОКРУГЛВВЕРХ(МЕСЯЦ(СЕГОДНЯ())/3

Эта история станет первым аргументом функции ВЫБОР / CHOOSE.
А все последующие аргументы — что возвращать, если первый аргумент равен 1 (первый квартал), 2 и так далее:
=ВЫБОР(ОКРУГЛВВЕРХ(МЕСЯЦ(СЕГОДНЯ())/3; суммируем данные по 1 кварталу; суммируем по 2; ...; по 4)

В качестве аргументов функции ВЫБОР могут быть другие функции — то есть мы выбираем их списка функций в зависимости от значения первого аргумента:
=ВЫБОР(ОКРУГЛВВЕРХ(МЕСЯЦ(СЕГОДНЯ())/3;0);СУММ(D3;G3;J3);СУММ(M3;P3;S3);СУММ(V3;Y3;AB3);СУММ(AE3;AH3;AK3))

Можно было поступить и иначе — СУММ оставить снаружи, а внутри функции ВЫБОР собирать нужные ячейки в массивы.
=СУММ(ВЫБОР(ОКРУГЛВВЕРХ(МЕСЯЦ(СЕГОДНЯ())/3;0);{D3;G3;J3};{M3;P3;S3};...;...))

Эту задачу наверняка можно решить и другими способами (например, горизонтальный FILTER для отбора столбцов с фактом и потом нужных месяцев?) — предлагайте варианты в комментариях!

Таблица с формулой

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

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

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

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

=
📗ОГЛАВЛЕНИЕ КАНАЛА, всё оглавление / категории
☺️ ДОНАТЫ: реквизиты

Извлекаем из массива фразы, даже если они написаны с ошибками

Привет! У нас есть чат, в него можно приходить с вопросами. Недавно туда пришла Марина и спросила:
Например, у меня есть 200 фраз. Среди них есть фразы со словами крем, глаза, ретинол. Мне их нужно найти в этом списке. При этом часть этих слов может быть написана с ошибками, типа крэм, ритинол и пр. И мне нужно вытащить все фразы с этими словами, в том числе и с ошибками.

Марине помог @vitalich, показав, как с помощью =FILTER и простых регулярных выражений можно вытащить всё, что нужно.

На скриншоте: формула в ячейке C2 берет регулярное выражение из ячейки C1 и выводит в C3 и ниже все, что ему соответствует (cre одна любая буква m).

Таблица

=
📗ОГЛАВЛЕНИЕ КАНАЛА, всё оглавление / категории
☺️ ДОНАТЫ: реквизиты

🧞‍♂️Превращатор Листа Таблицы в xlsx / pdf / csv
— файл мгновенно скачивается
— либо сохраняется на Google Диск

Друзья, подготовили для вас комбайн превращений – скрипт, который при запуске определяет лист, на котором вы находитесь и превращает его в файл выбранного формата.

Если конвертируете в xlsx – скрипт вставит все формулы на листе как значения, чтобы ячейки, которые ссылаются на другие листы не потеряли своих значений.

Как установить:
Открываете вашу Таблицу, Инструменты > Редактор скриптов, вставляете код: pastebin.com/iuKKSFrM, сохраняете и перезагружаете Таблицу. После перезагрузки в Таблице появится меню "🧞‍♂️ – скрипты".

=
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
☺️ ДОНАТЫ: реквизиты

SEQUENCE — выводим заголовки с датами текущего месяца

И снова о прекрасной функции SEQUENCE, которая, кстати, появилась и в Excel — пока только для подписчиков Office 365 (на русском — ПОСЛЕД).

Эта функция генерирует массив чисел по заданным параметрам - высоте, ширине, первому значению и шагу.

Аргументы функции:
число строк, число столбцов, первое значение и шаг.

Например, такая функция выведет столбец с числами от 1 до 10000:
=ПОСЛЕД(10000;1;1;1)

Так как даты — это числа, то можно выводить и их.
Допустим, нам нужны все даты текущего месяца в строку. Понадобится такая конструкция:
=ПОСЛЕД(1 строка; число дней в месяце; дата начала месяца; шаг=1)

Первый день текущего месяца можно получить так (текущий год + текущий месяц + первое число):
=ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());1)

А количество дней в месяце — так:
=КОНМЕСЯЦА(СЕГОДНЯ();0)

Остается все собрать в одну конструкцию. И как тут не напомнить про то, что в строке формул можно использовать пробелы и переход на следующую строку (Alt+Enter), чтобы делать длинные формулы проще для восприятия.

Конечно, внутри функции ДАТА можно подставить любой месяц вместо текущего, и тогда будут выведены даты этого месяца (см ГИФ)

=
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
☺️ ДОНАТЫ: реквизиты

Встраиваем видео в Google Таблицы

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

Чтобы видео заиграло – создаём скриптами модальное окно с emded-ссылкой на видео.

Код очень простой:
Код скрипта в pastebin
Таблица со скриптом

=
Как заставить решение работать:
1) копируете Таблицу
2) открываете редактор скриптов в ней > триггеры > создаёте триггер на редактирование Таблицы, выбираете функцию onEditTrig и сохраняете
3) готово – скрипт будет запускаться из Таблицы, после того, как вы активируете один из чекбоксов

За идею и реализацию спасибо @Lev_Andreevich 🦁

=
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление

Отбираем три последних строки по имени и считаем среднее (ниже будет сложно!)

Представьте – вашим сотрудникам ставят оценки и вам нужно посчитать среднее по каждому сотруднику из его последних трёх оценок.

На скриншоте – разбор такой формулы.

В её основе (пункт 1 на скриншоте) – функция COUNTIFS / СЧЁТЕСЛИМН в массиве, она добавляет к диапазону виртуальный столбец с нумерацией по ключу, начиная от последнего элемента (у нас ключ – только имя, но при необходимости вы сможете использовать несколько ключей, просто добавив по аналогии условия в COUNTIFS). Подробнее про принцип работы формулы нумерации здесь.

Вторая часть (пункты 2-3 на скриншоте) – обработка получившего массива с новым столбцом внутри QUERY – оставляем последние три вхождения, группируем и считаем среднюю оценку.

Таблица поближе

Знаете, как решить по другому - пишите в комментариях :)

=
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
☺️ ДОНАТЫ: реквизиты

FLATTEN2, что говорит лисичка и другие неописанные в справке функции Google Таблиц

Друзья, привет! Сегодня у нас прекрасная статья от Михаила Смирнова – он рассказывает, как нашел ряд неописанных в справке функций.

FLATTEN 2: другие недокументированные функции Google Sheets

Не про все функции понятно, что они делают, в справке же их нет :) поэтому пробуйте и предлагайте свои варианты в чат: @google_spreadsheets_chat

==
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление

ОТПРАВЛЯТОР 2.0
Создаём из Таблиц PDF и JPG и рассылаем в Телеграм по заданному расписанию

Друзья, привет! Выпускаем вторую версию решения для отправки ваших данных в Телеграм.

В первой версии скрипт создавал простое PNG-изображение из диапазона данных, теперь же мы создаём полноценный PDF или JPG файл.

Таблица полностью готова к работе, просто заполните правила отправки и введите токен своего Телеграм бота.

Таблица со скриптом (инструкция – внутри!)

=
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
☺️ ДОНАТЫ: реквизиты

Сравнение текстовых строк по символам и их кодам

Зачастую одни и те же текстовые данные воспринимаются Excel или Таблицами как разные, если они из разных источников (например: одно и то же название товара в выгрузке из 1С и в ваших таблицах будет разным из-за того, что там отличаются... пробелы! Пробелы бывают разными, увы).

Внешне это понять сложно - нужно смотреть на код символа. Его можно определить с помощью функции КОДСИМВ / CODE. Если у внешне одинаковых символов отличаются коды, значит, для Таблиц это разные символы и текстовые ячейки с ними будут тоже считаться разными.

Ну а чтобы достать определенный символ из текста, нужна ПСТР / MID.
Если мы хотим сразу разбить текст по символам одной формулой, сделаем формулу массива, и в качестве второго аргумента ПСТР (какой по порядку символ извлекать) укажем функцию SEQUENCE, которая создаст массив от единицы до числа, соответствующего количеству символов в ячейке (его находим с помощью ДЛСТР / LEN):
=ArrayFormula(ПСТР(A1;SEQUENCE(ДЛСТР(A1);1;1;1);1))

Чтобы получить массив кодов, а не сами символы, добавим сверху КОДСИМВ:
=ArrayFormula(КОДСИМВ(ПСТР(A1;SEQUENCE(ДЛСТР(A1);1;1;1);1)))

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

=
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление