Google Таблицы

Не забываем про новые формулы – возьмём два столбца, которые выводит пользовательская функция (столбец с ссылками на листы и столбец с названиями листов) и превратим их с помощью BYROW в один массив с гиперссылками:

=BYROW(sheets();LAMBDA(ROW;HYPERLINK(INDEX(ROW;0;1);INDEX(ROW;0;2))))

Спасибо Александру Иванову за идею.

🔥 Что-то непонятно – приходите в наш чат и задавайте вопросы.

Оглавление Таблицы скриптами

Друзья, привет!

В первом посте 2023 года решим классическую проблему – выведем список всех листов Таблицы вместе с ссылками на каждый лист.

Базовый скрипт.
function sheets() {
//текущая таблица
const ss = SpreadsheetApp.getActive();

//определяем ссылку на текущую таблицу
const ssUrl = ss.getUrl();

//проходим по всем листам Таблицы в цикле и формируем массив [ссылка на лист, название листа]
return ss.getSheets().map(sheet =>
[`${ssUrl}#gid=${sheet.getSheetId()}`, sheet.getName()]);
}

Скрипт будет работать как пользовательская функция, просто вставьте =sheets() на лист в любую ячейку, как на скриншоте.

Хак, чтобы оглавление, которое выводится обновлялось почаще – передаём в функцию любой аргумент из листа, =sheets(k1), например.

Вставляем оглавление как значения при открытии Таблицы.
Добавляем код ниже.
function onOpen() {
const arr = sheets();
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName('оглавление') || ss.insertSheet('оглавление');
sh.clearContents();
sh.getRange(1, 1, arr.length, arr[0].length).setValues(arr);
}

Вставляем оглавление гиперссылками (будет один столбец) при открытии Таблицы
function onOpen() {
const arr = sheets().map(f => [SpreadsheetApp.newRichTextValue()
.setText(f[1])
.setLinkUrl(f[0])
.build()]);

const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName('оглавление') || ss.insertSheet('оглавление');
sh.clearContents();
sh.getRange(1, 1, arr.length, arr[0].length).setRichTextValues(arr);
}

Друзья, с наступающим вас!

Посты, которые вспоминали в нашем чате (@google_spreadsheets_chat) чаще всего:

>> Крутейшая таблица с подсказками про query

>> Памятка по работе с условиями в формулах FILTER, QUERY, SUMIF(и аналогах)

>> Условное форматирование. Выделяем дубликаты и не только

>> Избранные посты нашего канала

>> Как посчитать

>> Памятка с советами, позволяющими ускорить работу документа

>> Памятка по условиям, с помощью которых можно отобрать (для функции SUMIFs/СУММЕСЛИМН, например) пустые и непустые ячейки

>> СОБИРАТОР 4.0

>> Важный скрипт. Связанные выпадающие списки из кэша

>> Считаем сумму по каждой строке / столбцу в формуле массиве

>> Народный Telegram бот❗️ Отправляем сообщения прямо из Таблицы

>> OR / AND в функции FILTER

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

>> Объединяем любое количество диапазонов Таблицы в одной функции

>> ​​ARRAYFORMULA для автоматической нумерации списков (счетчик)

>> ЗАПУСКАТОР – реагируем на изменения формул / Таблицы и запускаем скрипты

>> SUMIF для нескольких условий в формуле массива. Объединяем диапазоны с помощью амперсанда (&)

>> ​​В правиле условного форматирования можно использовать данные другого листа.

Лучшие посты - 2022

Всего мы опубликовали чуть более 100 постов за этот год. Их всегда можно найти в нашем оглавлении, ну а сегодня решили выбрать для вас пачку хороших постов. Это не топ по просмотрам или по перепостам. А то, о чем мы - по нашей субъективной оценке полезности - решили напомнить. Вдруг кто-то пропустил что-нибудь полезное или присоединился к нам по ходу года.

Схватка двух ёкодзун. Сравнение Google Таблиц и Excel (ну ладно, это было перед НГ-2022, но все остальные посты точно из этого года)

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

ВСТАВЛЯТОР: вставляем формулу / скрипт сразу же вставляет вместо неё значения

Условное форматирование + ВПР: выделяем цветом имена сотрудников из определенного отдела

Проектная диаграмма - Условное форматирование

ВПР-им с разных листов

Sheets Api, берём данные

Sheets Api 2, вставляем данные

Достаём ссылки на фотографии товара из карточки WB

Задача: посчитать количество значений (или что-то еще, не столь важно - мы рассмотрим на примере счета) в каждой строке одной формулой.

JOIN / TEXTJOIN по каждой строке в новых реалиях

Находим последнее значение с помощью XLOOKUP

Сумма по строке в новых реалиях, c условием

Задача: считаем нарастающий итог до заданного месяца и за заданный год формулой.

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

Как бы вы решали такую задачу?

Вот вариант решения:
=SUM(QUERY(TRANSPOSE(FILTER($F3:$CW3;REGEXMATCH($F$2:$CW$2;"Деньги.*"&$B$1)));"limit "&MATCH($B$2;{"Январь" ; "Февраль"; "Март"; "Апрель"; "Май";"Июнь";"Июль";"Август";"Сентябрь";"Октябрь";"Ноябрь";"Декабрь"};0)))

Схематично:
=SUM(QUERY(TRANSPOSE(FILTER(строка с данными;REGEXMATCH(Заголовки;"Фиксированная часть заголовка.*"Номер года из ячейки)));"limit "&MATCH(Выбранный месяц в ячейке;{Массив с месяцами, чтобы получить номер выбранного};0)))

С помощью REGEXMATCH выбираем только столбцы с заголовком "Деньги" (или "Штуки") и выбранным годом, между годом и штуками добавляем любой текст (.*), чтобы все месяцы попали в выборку.

Выборка формируется по этому условию с помощью FILTER.

Дальше транспонируем (делаем массив вертикальным), и с помощью QUERY и кляузы limit в ней получаем первые N значений (N определяется по порядковому номеру месяца - просто через MATCH / ПОИСКПОЗ определяем, каким по порядку в массиве названий месяцев идет выбранный пользователем месяц).

Ну и дальше суммируем это безобразие с помощью SUM / СУММ.

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

Проверка данных стала наряднее!

Теперь:
— Правила настраиваются в боковой панели (как, например, условное форматирование). Боковая панель вызывается, как раньше, меню "Данные" (Data) — "Настроить проверку данных" (Data Validation).
Или Alt-D + V.

— Нажимаем Add Rule (Добавить правило), чтобы добавить новое правило, существующие правила видим в списке, можно наводить курсор на каждое и будет выделяться соответствующий диапазон. Удалить правило можно в его настройках, нажав Remove Rule, или по иконке с корзинкой в списке всех правил проверки.

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

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

Накопление с помощью onEdit(), из комментариев

Скрипт накопления для любой ячейки одного столбца (на гифке)

Скрипт накопления для выборочных ячеек (их указываете в коде)

Всем спасибо за комментарии и вопросы, друзья!

Минус на минус дает число: превращаем текст в число для дальнейших вычислений

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

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

Как превратить текст в число в Google Таблицах (и в Excel тоже)?

1 С помощью двух минусов. Два раза умножив текст на минус единицу, мы меняем тип данных, не меняя значения
=--REGEXEXTRACT(...)

2 С помощью функции VALUE/ЗНАЧЕН.

=ЗНАЧЕН(REGEXEXTRACT(...))

3 С помощью еще какой-нибудь математической операции, не меняющей значение, например, умножения на единицу
=REGEXEXTRACT(...)*1

Простейший onEdit скрипт накопления с комментариями

Сегодня персонально ответим на вопрос Евгения из нашего чата и покажем простой скрипт для накопления суммы.

Работает так: вводим что-то в ячейку, скрипт проверяет, число ли это, проверяет в какую ячейку и на какой лист ввели и если все правильно, то добавляет это число к аккумулятору.

Код с комментариями:
function onEdit(e) {
//определяем лист, который редактируется
const sheet = e.source;

//умножаем значение, которое ввёл пользователь на 1. чтобы преобразовать из текста в число
const value = e.value * 1;

//ЕСЛИ полученное значение число, ЕСЛИ пользователь ввёл его на "Лист1" и ЕСЛИ в ячейке "B1"
if (!isNaN(value) &&
sheet.getSheetName() == 'Лист1' &&
e.range.getA1Notation() == 'B1') {
//ТО определяем ячейку накопленного итога
const range = sheet.getRange('B2');
//берём из нее значение
const old_value = range.getValue();
//и добавляем наше число к нему
range.setValue(value + old_value);
}
};

Таблица
И наш чат, в котором можно задавать вопросы, на которые мы иногда отвечаем даже на канале 😎

Если без авокадо с круассаном (или иероглифов, или огонька, или чего угодно еще) в оформлении таблиц никуда, можно использовать функцию CHAR / СИМВОЛ, которая возвращает символ по коду.

Как узнать, какой код у символа?
Можно просто ввести формулу, которая будет возвращать символы для всех номеров, например, до 150000:

=ArrayFormula(CHAR(SEQUENCE(150000;1)))

Номер строки, в котором будет символ, и будет его номером. Запоминаем и используем в будущем.

Ловите таблицу с этой формулой - ссылка. Листайте и смотрите, вдруг что-то пригодится!

Либо можно использовать сайт graphemica.com. Ищите там нужный символ и копируйте номер "html entity (decimal)".
Вот пример.

Приводим данные к правильному виду или три REGEXREPLACE

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

Поехали:
1) сначала поборем пробелы, табуляцию и перенос строк: REGEXREPLACE(A2:A12;"\s";"") Про \s

2) берём результат и меняем все варианты х|\*|X|Х на х (строчную латиницу). Не забываем, что звездочка - метасимвол в регулярных выражениях и для точного поиска её нужно экранировать \*
REGEXREPLACE(REGEXREPLACE(A2:A12;"\s";"");"х|\*|X|Х";"x")

3) и напоследок меняем все точки (опять спецсимвол и нужно экранировать, \.) на ","
=ARRAYFORMULA( REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(A2:A12;"\s";"");"х|\*|X|Х";"x");"\.";","))

С помощью формулы массива заставляем формулу работать сразу на всём диапазоне данных.

Если будут идеи как это решить иначе - напишите в комментарии :)

Материалы:
Компактная памятка про регулярки от Vitalich
"Народная" база с примерами регулярных выражений (внутри не всё идеально, но некоторое понимание получите)

PS короткий вариант от @vitalich:
REGEXREPLACE(...;"(?i)[хx*]";"x")

Как выделить / найти все формулы на листе?

Можно просто нажать Ctrl + `
Или в меню: Вид - Показать - Формулы (View - Show - Formulae), или Alt+V + S + A.

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

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

Смотрите также:
Скрипт для вывода всех формул в таблице

Простой post-запрос, простое обращение к API

Привет! У нас часто спрашивают — а как написать обращение из Таблиц к API? Давайте мы вам покажем. Есть ресурс, который позволяет сокращать ссылки: https://bitly.com/, там можно зарегистрироваться и получить API-ключ.

Ключ получили, далее смотрим документацию и видим, что для сокращения ссылки нам нужно отправить post-запрос на адрес https://api-ssl.bitly.com/v4/shorten с нашим API-ключом и с длинной ссылкой в payload.

Минимальный необходимый код:
function easy_post(){
const url = 'https://...' //ссылка, которую сокращаем
const token = '4d280..' //наш токен

let params = {
method: 'post',
contentType: 'application/json',
payload: JSON.stringify({"long_url": url}),
'headers': {
'Authorization': 'Bearer ' + token,
}
};

var r = UrlFetchApp.fetch('https://api-ssl.bitly.com/v4/shorten', params);
r = JSON.parse(r);
console.log(r);
}

В r получаем такой объект:
{ created_at: '2022-12-04T06:34:47+0000', id: 'bit.ly/3umWEM9', link: 'https://bit.ly/3umWEM9', custom_bitlinks: [], long_url: 'https://docs.', archived: false, tags: [], deeplinks: [], references: { group: 'https://api-ssl.bitly.com/v4/groups/Bmbui906n47' }}

Чтобы достать короткую ссылку, достаточно обратиться к этому объекту как r['link'].

Хорошо, ребят, вы мне показали, а что с этим делать дальше?
Например, пишем такой скрипт — кликаем в таблице на ячейку с ссылкой, запускаем скрипт, а он сокращает ссылку и вставляет в соседнюю ячейку результат. Таблица с примером / код в pastebin.

Либо, как мы сделали недавно для нашего клиента - обращаемся к ответам формы, в ответах - отдельные части длинной ссылки, внутри скрипта собираем ссылку, далее сокращаем её и все это вставляем в Таблицу. Кладём скрипт на событие "приход нового ответа формы".

Новые ответы на форму сбивают формулы

Друзья, в наш уютный чатик очень часто приходят с вопросом как на скриншоте.

Если выделить главное:
Мы принимаем ответы Google Формы в Таблицу. Каждый новый ответ добавляется новой строкой и в этой строке слетают формулы, которые мы ранее настроили.

Какое тут решение:
Ответы на Форму действительно приходят новой строкой, строка физически добавляется на лист с ответами. Добавляется она со стандартным форматированием и без каких либо формул.

Два варианта, как это решить:
1) Пишите свои формулы формулами массивами в строке заголовка, например
=arrayformula({"сумма" ; if(A:A="";; B:B + C:C)})

Такая формула распространится на все добавленные строки автоматически.

2) Оставьте в покое лист, на который приходят ответы на форму, ничего на нем не делайте вообще и обрабатывайте ответы на другом листе: там вы сможете и форматирование задать и протянуть нужные вам формулы.

Уютный чатик
Целая библиотека постов про Таблицы

У вас есть дата, а вы хотите номера/названия месяцев в отдельном столбце (допустим, для сводной, для отчетов, для фильтрации) - 7 вариантов

Самый простой вариант - функция MONTH / МЕСЯЦ. Это число, порядковый номер (3 для марта, 11 для ноября).

С помощью функции TEXT / ТЕКСТ можно получить также вариант с нулем для коротких номеров (03 для марта, но 11 для ноября). С помощью нее же - текстовые варианты (мар. и марта для российских региональных настроек).

Если "марта" вам не нравится и вы хотите использовать абсолютно любые варианты, то можно брать их из диапазона с помощью ИНДЕКСа. Или из виртуального массива внутри формулы с помощью того же ИНДЕКСа или ВПР или ВЫБОРа.

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

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

Продолжаем λямбдовое!

Во-первых, рекомендуем мини-курс про новые функции от Бена Коллинса, если знаете английский, то пройдите эти бесплатные уроки и сможете использовать новые функции в офисном бою: https://courses.benlcollins.com/p/lambdafunctions

Ну а не знаете английский – вам придётся терпеть нас дальше 🙂

Во-вторых, самое удачное, на мой взгляд, решение домашки из предыдущего поста (нужно было написать функцию, которая, если столбец A заполнен - суммирует столбцы D, E, F)

Функция:
=MAP(A1:A6; D1:D6; G1:G6; LAMBDA(a; sumStart; sumEnd;if(a="";;sum(sumStart:sumEnd))))

Работает так: в map передаем три массива: столбец проверки, столбец начала суммирования и окончания суммирования.

Далее проверяем столбец проверки, если он не заполнен, то не возвращаем ничего if(a="";;, а если заполнен, то собираем с использованием двоеточия диапазон суммирования: столбец начала:столбец окончания sumStart:sumEnd и считаем его сумму.

С помощью фокуса с двоеточием мы не указываем диапазон суммирования целиком (это невозможно в функции map, есть же еще диапазон проверки) и нам достаточно указать только начало диапазона начало и окончание.

Про силу двоеточия писали здесь.

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

https://teletype.in/@renat_shagabutdinov/sheets_slicer

Про срезы в Excel читайте здесь

ИЗМЕРЯЕМ СКОРОСТЬ ФОРМУЛ В GOOGLE ТАБЛИЦАХ

Друзья, хотели узнать, какая формула работает быстрее, а какая медленнее?

Ловите статью от Михаила Смирнова, а в комментариях пишите свои наблюдения и свои цифры https://telegra.ph/Google-Sheets--Vremya-raschyota-formuly-06-06-2

Всем быстрых Таблиц и большой зарплаты 😎

💥 Возвращаемся с ответом на домашку!

(Был вопрос: напишите формулу массива, которая посчитает сумму построчно для C:E, для непустого столбца A)

Решение от @vitalich,
=BYROW(C2:E9; LAMBDA(lr; IF(ISBLANK(INDEX(A1:A9; ROW(lr);));;SUM(lr))))

Объясняем логику:
1) передаем в BYROW диапазон, который будем суммировать;

2) мы не можем в LAMBDA написать функцию вроде IF(A1:A9<>""; SUM(lr);) из-за особенности функции, но за то мы можем получать номера строк переданного массива C2:E9 и передавать их в INDEX(A1:A9; ROW(lr)), чтобы получить построчно A1, A2, A3 и далее проверить эти ячейки на наличие значения;

3) Если ячейки A1, A2, A3 пустые не выводим ничего, если заполнены - выводим сумму по строке SUM(lr);

💥 Если найдете другой вариант решения - напишите его в комментарии.

Сумма по строке в новых реалиях, c условием!
(💥 пост с домашним заданием)

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

Сегодня будем считать сумму по столбцам D, E, F в формуле массива при условии, что столбец A непустой.

Итак, формула целиком:
=BYROW(A1:F10;LAMBDA(ROW;if(INDEX(ROW;1)<>"";SUM(OFFSET(ROW;0;3;1;3));)))

Что мы в ней делаем:
— В функцию BYROW передаем весь диапазон
— В функции LAMBDA обращаемся к каждой строке диапазона как к ROW
— В привычной функции IF / ЕСЛИ обращаемся к первому элементу строки с помощью INDEX, проверяя есть ли значение
— Если IF / ЕСЛИ возвращает истину, то с помощью функции OFFSET / СМЕЩ отступаем от первой ячейки строки три столбца и суммируем этот отрезок
— Если IF / ЕСЛИ возвращает ложь, то не возвращаем ничего

💥 Домашнее задание: попробуйте написать формулу массива для столбца B с использованием новых функций, формула должна суммировать столбцы D, E, F для непустого столбца A. Отправьте свой вариант в комментарии.

Про новые функции:
Накопительный итог построчно
Подсчёт значений построчно
JOIN построчно