Google Таблицы

Парсим картинки из Рамблера

Друзья, недавно перестали работать формулы с загрузкой картинок из Гугл и Яндекс (ссылки давать не будем, они же не работают).

В нашем чате предложили альтернативу - рамблер (а он всё еще работает, мы тоже удивились).

Формула:
=IMAGE(INDEX(IMPORTXML(HYPERLINK("https://images.rambler.ru/search?query="&B2);"//img/@src");3))

PS Большое спасибо пользователю "Недвижимость Воронежа" из нашего чата!

Чат для патронов

Мы давно хотели попробовать и вот наконец решились. Мы создаём чатик, в котором можно будет задавать вопросы по нашим скриптам, по формулам и по прекрасным Google Таблицам в целом, подписка на чат будет стоить 1000 рублей в месяц.

Что даёт подписка

1. Взяли скрипт у нас на канале и не работает? – Поможем внедрить
2. Есть вопросы, за что отвечает та или иная строчка в скрипте? – Расскажем
3. Обсудим в спокойной обстановке формулы, как решить задачку, зачем нужны веб-приложения в Таблицах и кто такие телеграм боты
4. Ну и конечно подпиской вы поддержите нас и наш канал ❤️

Если вам стало интересно – напишите @namokonov

#таблицы

Реакции (эмодзи) в Google Документах

Если вам нужно проголосовать за идеи, проекты, продукты или что угодное еще в Google Документе — можно использовать эмодзи!

Еще они могут пригодиться для оценки текста, домашней работы или чего угодно еще.

Это просто: выделяем текст, нажимаем на смайл справа и выбираем эмодзи по вкусу.

Функция ТЕКСТ / TEXT: превращаем число в текстовое значение в заданном числовом формате

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

Для чего нужна?
Допустим, вы хотите "склеить" в одну текстовую строку текст и число.
Чтобы получить в таблице надпись вида "По состоянию на: 20.11.23" или "Сумма продаж: 20 500". То есть текст из фиксированной части и какого-то вычисления/функции, как-то суммы чисел или текущей даты.

Проблема в том, что если сделать это "в лоб" без функции ТЕКСТ / TEXT, форматирование потеряется. Число будет без разделителей разрядов, со всеми знаками после запятой; дата будет в виде числа ("По состоянию на: 45250") — потому что вот так даты хранятся в Таблицах (и Excel).

И функция ТЕКСТ позволяет это исправить — укажите нужный формат во втором аргументе, как если бы вводили его в пользовательском числовом формате.

Итак, для даты в нашем примере нужна будет такая формула:

="По состоянию на: " & ТЕКСТ (дата; "DD.MM.YY DDD")

Это формат вида 20.11.2023 Пн (с коротким днем недели — за него отвечает DDD). Подробнее про пользовательские числовые форматы можно посмотреть в видео.

Выводим формулой список всех рабочих дней — от заданной до сегодняшней (или не до сегодняшней, а до какой захотите)

Для этого:
1. вычислим число рабочих дней в периоде (функция ЧИСТРАБДНИ / NETWORKDAYS)

2. Засунем это число в функцию SEQUENCE и получим последовательность чисел от 1 до числа рабочих дней в периоде

3. отправим эту последовательность в функцию РАБДЕНЬ / WORKDAY — она возвращает дату, которая наступит по прошествии N рабочих дней от заданной. В нашем случае она выдаст много дат, по одной для каждого числа полученной на прошлом шаге последовательности. Формулу вводим сочетанием Ctrl+Shift+Enter, чтобы засунуть ее в функцию ArrayFormula.

Формула такая:

=ArrayFormula(WORKDAY(начальная дата-1; SEQUENCE(NETWORKDAYS(начальная дата ;конечная дата))))

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

Как выгрузить в Таблицу все официальные выходные / рабочие дни

Парсим Ютуб ⚡️

И Ютуб – Google и Таблицы – Google. Что нам это даёт?

Например то, что в скриптах Таблиц есть обёртка для использования Ютуб API. Если вы хотите достать просмотры роликов, то вам необязательно парсить страницу с роликом влоб, просто откройте скрипты, в сервисах подключите YouTube и используйте код, который будет ниже. Кроме просмотров (stat.viewCount) функция выведет и название видео и лайки.

function get_youtube() {
let video_url = "https://www.youtube.com/watch?v=ObthaxGP7gg";
console.log(platformId)
}

function youtube(url) {
let id = url.match(/[-\w]{11,}/)[0];
let item = YouTube.Videos.list('snippet,statistics', { 'id': id }).items[0]
let stat = item.statistics
let snippet = item.snippet
return ({ views: stat.viewCount, name: snippet.title, likes: stat.likeCount, dislikes: stat.dislikeCount })
};

PS Расскажите в комментариях, интересна вам эта тема? Если да, то продолжим с парсингами других площадок (телеграм, инстаграм, твич, вк, дзен).

"Скроем формулу с глаз"

Друзья, иногда нам нужно не показывать формулу в строке формул, для этого можно воспользоваться простым лайфхаком:

1) скопируйте формулу;

2) откройте любой текстовый редактор, к пример, блокнот, вставьте ее туда;

3) добавьте несколько переносов строк между "=" и телом формулы;

4) вставьте, то, что получилось в таблицу;

Формула останется на месте, конечно, но чтобы до нее добраться нужно будет немного постараться :)

Выделяем сегодняшнюю дату и соседние с ней.

Если хотим как на картинке — сегодня с заливкой, вчера и завтра потемнее, а все остальное побледнее серым, то применяем два правила условного форматирования к столбцу с датами:

1 С первым все просто — форматировать ячейки, если "Дата" -> "Сегодня"

2 А второе правило будет с формулой. Формула, например, такая:

=ABS((TODAY()-первая ячейка диапазона))>1


И ячейки, соответствующие второму правилу (а это даты, отстающие назад или вперед более чем на один день от ячейки), форматируем серым шрифтом (или как захотите по вкусу)

Новые чипы в Таблицах: звездочки с оценками от 0 до 5

Вводим @Оценка (@ Rating)
или идем в меню Вставка (Insert) — Умные чипы (Smart chips) — Оценка (Rating)
или нажимаем Alt+I + A + 8

И получаем возможность выбирать звездочки в числе от 0 до 5 штук.
Гибкости, увы, немного: всегда будет серый фон, всегда от 0 до 5, всегда звездочки.

Бен Коллинз в своей рассылке предлагает менять фон всей ячейки на #e8eaed — это цвет чипа — чтобы хотя бы вся ячейка была залита.
еще можно поменять цвет шрифта на любой — это будет цвет звездочек.

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

@zadavai_vopros_bot

Сначала коротко новости - мы перевели нашего chatGPT бота на другой стек, а еще мы прикрутили оплату, месяц по прежнему стоит 500 рублей *

* Но можно пользоваться и бесплатно, каждый месяц у вас по 10 запросов, пробуйте

Про бота / Пишем ботом простые скрипты / Отвечаем ботом на отзывы / Работа с текстом

~~~
Немного базы
Что самое важное при работе с языковыми моделями? Промпт. То, что вы отправляете в нашего бота (или в чат open ai).

Представьте, что у вас есть текст и вы хотите достать из него названия городов.

Промпт будет примерно таким: После "текст: " будет текст, из него нужно извлечь названия городов США, их нужно написать по-английски и каждый город нужно взять в кавычки. В тексте города могут быть написаны с ошибками, ошибки нужно исправить. Текст: ".

Советы:
1) можете использовать капслог - по непровенным данным модель это воспринимает как приказ, который нужно выполнить
2) меняйте промпт пока не придете к нужному результату
3) пишите на английском, можете использовать google translate

Флажки в Excel: очередной повод сказать "а в Таблицах это есть давно!"

Вы вообще помните, когда появились флажки в Таблицах? Кажется, что были всегда :)
А в Excel — пару недель назад и пока только для бета-канала обновлений у подписчиков Microsoft 365.

Работают аналогично: пробелом можно включать и выключать, в ячейке хранится ИСТИНА / TRUE или ЛОЖЬ / FALSE.

Увы, при скачивании Таблицы на локальный диск в формате xlsx "табличные" флажки в Excel превратятся просто в логические значения, придется вставлять флажки поверх них. Аналогично при импорте Excel в Таблицы.

P.S. Речь именно про флажки в ячейках, так как в Excel всегда были доступны другие флажки, которые не размещаются в ячейке, а вставляются на отдельный слой поверх ячеек. Вставляются они на вкладке "Разработчик" (Developer), и в контекстном меню каждого флажка можно настроить связь с ячейкой (в которой будет отображаться ИСТИНА / ЛОЖЬ в зависимости от флажка)

Ставку рефинансирования достали, теперь достанем и курсы валют ЦБРФ 🗣

У Мосбиржи есть отличное API, которое без ключа отдает много полезного.

Обращаясь к эндпоинту moex.com/iss/statistics/engines/currency/markets/selt/rates мы получаем XML, с курсами йены, доллара и евро к рублю.

Вариант скрипта, в котором мы парсим XML как нормальные люди:
function usdClose() {
const url = 'moex.com/iss/statistics/engines/currency/markets/selt/rates'
const r = UrlFetchApp.fetch(url);

var d = XmlService.parse(r);
var root = d.getRootElement();
var dataElement = root.getChild('data');
var rowsElement = dataElement.getChild('rows');
var rowElement = rowsElement.getChild('row');
var closePrice = rowElement.getAttribute('USDTOM_UTS_CLOSEPRICE').getValue();
Logger.log(closePrice); // 93.0351
return closePrice
};

И вариант, где мы просто выдергиваем регулярным выражением нужное поле:
function eurLast() {
...
const regExp = /"EURRUB_TOM" price="(.+?)"/;
const lastPrice = r.toString().match(regExp)?.[1];
Logger.log(lastPrice); //
return lastPrice; //93.0351

};

Обходим проблему недоступности серверов
Иногда сервер Мосбиржи недоступен и скрипт падает с ошибкой, давайте для этих случаев напишем украдем часть собиратора обёртку, пусть обращается к серверу несколько раз.

//так запускать, дадим скрипту три попытки
function go() {
retry(3, eurLast);
}

//сама обертка
function retry(n, func, ...args) {
for (let i = 0; i < Math.min(n, 9); i++) {
try {
return func(...args)
} catch (e) {
Logger.log("Retry " + (i + 1) + " failed, waiting")
Logger.log(e)
Utilities.sleep(i * 1000)
}
}
throw "Number of tries exceeded, aborting"
}

ИЗБРАННЫЕ СКРИПТЫ НАШЕГО КАНАЛА

Посмотрите, вдруг вы пропустили что-нибудь сочное:

— СОБИРАТОР 4.0, интерфейс для сбора других Таблиц →

— 🧞‍♂️ПРЕВРАЩАТОР Листа Таблицы в xlsx / pdf / csv →

— ОТПРАВЛЯТОР, отправляем ваши отчеты в теле письма по расписанию →

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

— ДОПУСКАТОР 2, убираем из файлов всех пользователей кроме себя и закрываем доступ по ссылке. →

— ЗАМЕНЯТОР, заменяем значения по словарю в выбранных Таблицах →

— ЗАКРЫВАТОР, скрипт автоматического закрытия прошедших дней →

— ДОСТАВАТОР, берём из кода веб-страниц регуляркой нужное →

— Скриптами определяем, когда освободится домен →

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

— DRIVE COLUMBUS, смотрим на свой Google Диск и управляем им прямо из Таблицы →

— С помощью скрипта даём доступ к другой Таблице, чтобы IMPORTRANGE заработал сразу →

— Защищаем скрипты от редактирования →

— Регулярный бэкап Таблиц в формате XLSX в телеграм! →

— Скрипт. Распознаем текст на изображениях. OCR в Google Docs →

— Скрипт onEdit(), реагирующий на изменения и отправляющий письма (или триггер в триггере) →
Скрипт, загружаем письма вашего GMAIL-аккаунта в Таблицу →

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

* а если вам нужен какой-то другой скрипт, вы можете заказать эту разработку у нас, напишите в бота

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ / SUBTOTAL: подсчет только отфильтрованных строк

Эта функция позволяет производить вычисление только с видимыми строками.
У нее такой синтаксис:
=SUBTOTAL(номер функции ; диапазон ; [еще диапазон]; ...)

Номер функции определяет, какая операция будет производиться. Функций всего 11 - стандартный набор, который, например, есть и в вычислениях сводных таблиц Excel (в Google к нему в сводных еще добавляется подсчет уникальных значений).

Вот базовые функции (кроме них есть еще стандартное отклонение и дисперсия):
1 и 101 - среднее
2 и 102 - количество чисел
3 и 103 - количество значений
4 и 104 - максимум
5 и 105 - минимум
6 и 106 - произведение
9 и 109 - сумма

Каждая функция бывает в двух вариантах - коротком (9 или 11, например) и длинном из трех цифр (109 или 111).

Короткий вариант - подсчет всех видимых строк (отфильтрованных) и скрытых вручную (через скрытие или группировку) строк.
Длинный вариант - подсчет только отфильтрованных строк, без скрытых вручную.

Если внутри диапазона уже есть другие функции SUBTOTAL, такие вложенные подытоги не будут учитываться. То есть задвоения в таком случае не будет.

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

Таблица с примером (Создать копию)

P.S. В Excel функция работает аналогично. Кроме того, если вы создаете "Таблицу" (Ctrl + T) и затем строку итогов (Total Row) в ней, то в строке итогов функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ будет формироваться автоматически, вам достаточно выбрать тип вычисления, например, "среднее".

Несколько советов для работы в браузере Google Chrome

Сегодня не совсем о таблицах... Хотя таблицы у нас существуют не сами по себе, а во вкладках браузера, так что эти приемы могут пригодиться и для работы с ними тоже.

Пишите в комментариях свои лайфхаки - добавим в пост!

Парсим ключевую ставку ЦБ 🗣 не по-человечески*

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

Скрипт как всегда простой – обращаемся к странице, далее достаём регулярным выражением даты и значения, как строки, делим по запятой и собираем из этого массив, который вставляем в нашу Таблицу.

В Таблице у вас появятся два столбца – даты и значения.

function myFunction() {
const url = 'https://www.cbr.ru/hd_base/keyrate/'
const response = UrlFetchApp.fetch(url).getContentText();
var dates = response.match(/"categories":\[(.+?)\]/)?.[1];
var values = response.match(/"data":\[(.+?)\]/)?.[1];

if (!dates || !values) { return; };

dates = dates.split(',');
values = values.split(',');
all = dates.map((f, i) => [f.replace(/"/g, ''), values[i].replace('.', ',')]);

const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName('Лист1');
sh.clearContents();
sh.getRange(1, 1, all.length, all[0].length).setValues(all);

};

Таблица, если нужна

* а по-человечески данные можно получать так, может попробуем в другой раз

А если нужно прогнозировать с учетом сезонности?

Самый простой подход — рассчитать коэффициенты сезонности. Допустим, у нас сезонность по месяцам. Для каждого месяца рассчитаем его коэффициент:

1. Поделим фактическое значение каждого месяца на среднее по тому же году. Например, январь 2022 делим на среднее за весь 2022 год. Например, у нас это 0,95 — то есть январь на 5% хуже, чем средний месяц 2022 года.

2. Найдем среднее значение для каждого месяца за весь период. Например, у нас данные за 4 года. Для января возьмем средний коэффициент — среднее из 4 январских коэффициентов.
(1,32 + 1,18 + 0,95 + 1,03) / 4 = 1,12
То есть, несмотря на не очень хороший результат в 2022 году, в остальные годы январь был лучше других месяцев. Среднее по больнице — 1,12.

3. Полученные коэффициенты используем в прогнозе. Умножаем прогноз, полученный функцией TREND, на коэффициент соответствующего месяца. В нашем примере мы увеличим линейный прогноз на 12% (умножим на коэффициент января 1,12).

Нужно построить простой линейный прогноз?

Можно использовать функцию TREND / ТЕНДЕНЦИЯ.
=ТЕНДЕНЦИЯ (фактические значения прогнозируемого показателя y; фактические значения x — ими могут быть номера периодов; прогнозируемые значения x)

В нашем примере — исторические данные по выручке, старые даты, новые даты.

Функция выдает прогноз по методу наименьших квадратов (линейное уравнение с минимальной суммой квадратов отклонений — речь про отклонения линии тренда от фактических значений).

С ним можно оценить тренд, но не более того. Если хочется добавить еще и сезонность, то придется пошаманить дополнительно (расскажем об этом отдельно).

Делитесь в комментах, кто как прогнозирует в Google Таблицах!

💥 Другие наши посты можно найти в оглавлении канала.

Персональная техподдержка компаний от @google_sheets

Друзья, привет! Наша новая услуга – внедрение нашей команды (автор канала Евгений и его спецы) в ваш бизнес для помощи по Таблицам и GAS-скриптам.

💥Как это выглядит

Мы создаём чатик, вы добавляете своих коллег, мы добавляем наших асов.

💥 Условия

Стоимость 225 000 р. за три месяца, далее по 75 000 р. в месяц, если захотите продолжить.

💥 Что может входить в пакет поддержки
(чем сможем помочь вашему бизнесу)

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ / SUBTOTAL: подсчет только отфильтрованных строк

Эта функция позволяет производить вычисление только с видимыми строками.
У нее такой синтаксис:
=SUBTOTAL(номер функции ; диапазон ; [еще диапазон]; ...)

Номер функции определяет, какая операция будет производиться. Функций всего 11 - стандартный набор, который, например, есть и в вычислениях сводных таблиц Excel (в Google к нему в сводных еще добавляется подсчет уникальных значений).

Вот базовые функции (кроме них есть еще стандартное отклонение и дисперсия):
1 и 101 - среднее
2 и 102 - количество чисел
3 и 103 - количество значений
4 и 104 - максимум
5 и 105 - минимум
6 и 106 - произведение
9 и 109 - сумма

Каждая функция бывает в двух вариантах - коротком (9 или 11, например) и длинном из трех цифр (109 или 111).

Короткий вариант - подсчет всех видимых строк (отфильтрованных) и скрытых вручную (через скрытие или группировку) строк.
Длинный вариант - подсчет только отфильтрованных строк, без скрытых вручную.

Если внутри диапазона уже есть другие функции SUBTOTAL, такие вложенные подытоги не будут учитываться. То есть задвоения в таком случае не будет.

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

Таблица с примером (Создать копию)

P.S. В Excel функция работает аналогично. Кроме того, если вы создаете "Таблицу" (Ctrl + T) и затем строку итогов (Total Row) в ней, то в строке итогов функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ будет формироваться автоматически, вам достаточно выбрать тип вычисления, например, "среднее".