=IF(вы_любите_наш_канал ; поддержите_нас ; )

Друзья, мы с 2017 года помогаем вам с таблицами и скриптами.

Если хотите поддержать нас в ответ - будем очень признательны. Мы тут прикрутили кнопку для донатов к нашему каналу - будем рады, если вы опробуете эту штукенцию в деле!

Вычисляем период в днях/месяцах/годах: функция РАЗНДАТ / DATEDIF

Если вам нужно вычислить разницу между двумя датами не в днях (для чего достаточно вычесть из одной даты другую или воспользоваться функцией ДНИ / DAYS), а в месяцах или годах (например, возраст) — пользуйтесь функцией РАЗНДАТ / DATEDIF. Она не является документированной в Excel (то есть при ее вводе не будут отображаться всплывающая подсказка с аргументами, Excel не предложит ее дописать, ее нет в справке), но не обращайте на это внимание — она работает во всех версиях. И в Google Таблицах тоже!

 =РАЗНДАТ(дата_начала; дата_окончания; единица измерения)

Первые два аргумента — даты начала и окончания периода. Они могут быть указаны прямо в формуле в кавычках либо в виде ссылок на ячейки с датами, а также быть заданными функцией СЕГОДНЯ / TODAY.

Единица измерения задается в кавычках. Есть следующие возможные варианты:

"d" — число дней (такой параметр не имеет особого смысла, так как для этой задачи подойдет и функция ДНИ / DAYS, и просто вычитание);
"m" — число полных месяцев в периоде;
"y" — число полных лет в периоде;
"md" — разница в днях без учета месяца и года (например, между 01.01.2021 и 15.06.2022 — 14 дней);
"ym" — разница в месяцах без учета дня и года (например, между 01.01.2021 и 15.06.2022 — 5 месяцев);
"yd" — разница в днях без учета года (например, между 01.01.2021 и 15.06.2022 —165 дней).

Сумма по строке в новых реалиях, 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 построчно

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

(Был вопрос: напишите формулу массива, которая посчитает сумму построчно для 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);

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

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

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

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

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

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

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

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

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

Во-первых, рекомендуем мини-курс про новые функции от Бена Коллинса, если знаете английский, то пройдите эти бесплатные уроки и сможете использовать новые функции в офисном бою: 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, есть же еще диапазон проверки) и нам достаточно указать только начало диапазона начало и окончание.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Простой 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.

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

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

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

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

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

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

Приводим данные к правильному виду или три 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")

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

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

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

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

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

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

Простейший 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);
}
};

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Дано: кривая и некрасивая выгрузка, где добавляются по 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 / СУММ.

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