Выделил в Таблице – отправилось в Телеграм
Привет, друзья! Ну что, очередной вариант отправки из Таблиц в Телеграм чаты или каналы. Точнее, очередной скрипт.
Скрипт работает так: выделяете диапазон, в котором в первом столбце chatId (уникальный Id пользователя или чата в телеграм, узнать можно с помощью @myidbot), а во втором столбце текст, который мы хотим отправить.
Далее запускаем скрипт и он проходит по каждой строке, пытаясь отправить сообщение, если получается – пишет получилось, если нет – то выводит ошибку.
ПС не забудьте внести токен своего бота в редактор скриптов.
Таблица
Привет, друзья! Ну что, очередной вариант отправки из Таблиц в Телеграм чаты или каналы. Точнее, очередной скрипт.
Скрипт работает так: выделяете диапазон, в котором в первом столбце chatId (уникальный Id пользователя или чата в телеграм, узнать можно с помощью @myidbot), а во втором столбце текст, который мы хотим отправить.
Далее запускаем скрипт и он проходит по каждой строке, пытаясь отправить сообщение, если получается – пишет получилось, если нет – то выводит ошибку.
ПС не забудьте внести токен своего бота в редактор скриптов.
Таблица
Добавляем таблицам огонька: пользовательские числовые форматы
Друзья, хотим напомнить вам про нашу таблицу — нарядную, бесплатную и с огоньками — с примерами пользовательских форматов.
Там всякое:
— прячем нули или вообще все значения
— добавляем к числам смайлы, стрелочки или текст
— форматируем даты или телефонные номера
— и другое — примеры на все возможности форматов.
Таблица
Другие наши посты по теме:
Статья про пользовательские форматы
Видео
⭐️ Оглавление канала: ты-дыц
Друзья, хотим напомнить вам про нашу таблицу — нарядную, бесплатную и с огоньками — с примерами пользовательских форматов.
Там всякое:
— прячем нули или вообще все значения
— добавляем к числам смайлы, стрелочки или текст
— форматируем даты или телефонные номера
— и другое — примеры на все возможности форматов.
Таблица
Другие наши посты по теме:
Статья про пользовательские форматы
Видео
⭐️ Оглавление канала: ты-дыц
Превращаем строку с числами в спарклайн одной формулой
Друзья, сегодня простая формула с превращением ряда чисел из одной текстовой строки в спарклайн одной формулой — спасибо нашему чату за идею.
В A1 на скриншоте — значения, разделенные запятой. Превратим их в спарклайн (диаграмму в одной ячейке). Используем формулу:
С помощью SPLIT с запятой в качестве разделителя (второй аргумент функции) делим нашу строку на отдельные числа, далее превращаем всё это в спарклайн типа "гистограмма" (столбики)
Другие наше про спарклайны:
Спарклайн с условием \ Видеоурок: Синтаксис и примеры \ Что такое спарклайны? \ Динамический спарклайн
Друзья, сегодня простая формула с превращением ряда чисел из одной текстовой строки в спарклайн одной формулой — спасибо нашему чату за идею.
В A1 на скриншоте — значения, разделенные запятой. Превратим их в спарклайн (диаграмму в одной ячейке). Используем формулу:
=SPARKLINE(SPLIT(A1;",");{"charttype"\"column";"color"\"green"})С помощью SPLIT с запятой в качестве разделителя (второй аргумент функции) делим нашу строку на отдельные числа, далее превращаем всё это в спарклайн типа "гистограмма" (столбики)
"charttype"\"column", а "color"\"green" отвечает за цвет столбцов, green сможете поменять на нужный вам цвет. Можно использовать и шестнадцатеричные коды цветов.Другие наше про спарклайны:
Спарклайн с условием \ Видеоурок: Синтаксис и примеры \ Что такое спарклайны? \ Динамический спарклайн
Курсы по Excel и Google Драйву
Друзья, несколько новостей по табличным курсам в МИФе
Во-первых, добавил новые видео и модули в эти курсы:
- В оба курса — модуль про функцию LAMBDA и вспомогательные функции BYROW/ BYCOL, SCAN и REDUCE, MAP и ISOMITTED (последняя в Excel). В случае с Гугл Драйвом — отдельное видео про именованные функции в Google Таблицах.
- Видео про ПРОСМОТРX / XLOOKUP в Google Таблицах — добавлено некоторое время назад, благо функция там появилась вскоре после Excel. В курсе про Excel эта функция и так, разумеется, была.
- Большое видео про Power Pivot (модель данных) в Магии Excel будет скоро.
Во-вторых, с 1 ноября цена на оба курса вырастет. Так что если думали про то, чтобы изучить основательно ту или иную тему или обучить свою команду/компанию (а на этих курсах у нас учатся сотрудники самых разных компаний, включая компанию из топ-10 крупнейших в России и национальный исследовательский университет) — самое время брать курс по старой цене с новыми материалами. Тем более несколько дней будет скидка — см ниже.
Немного фактов.
В каждом курсе есть исходные и заполненные файлы с примерами к каждому уроку, а в отдельных тарифах и подробные конспекты (суммарно как книга).
В Магии Excel 60+ уроков, в Гугл Драйве 90+.
Урокам можно ставить оценки. За все время у Гугл Драйва 600 оценок (из них 7 четверок и 2 тройки), у Магии Excel 245 оценок (тоже почти все пятерки: только 4 четверки, ни одной тройки).
Коллеги подкинули промокод на скидку, ловите:
GOOGLE
35% до 5 октября.
https://www.mann-ivanov-ferber.ru/courses/magicexcel/
https://www.mann-ivanov-ferber.ru/courses/gdrive/
Друзья, несколько новостей по табличным курсам в МИФе
Во-первых, добавил новые видео и модули в эти курсы:
- В оба курса — модуль про функцию LAMBDA и вспомогательные функции BYROW/ BYCOL, SCAN и REDUCE, MAP и ISOMITTED (последняя в Excel). В случае с Гугл Драйвом — отдельное видео про именованные функции в Google Таблицах.
- Видео про ПРОСМОТРX / XLOOKUP в Google Таблицах — добавлено некоторое время назад, благо функция там появилась вскоре после Excel. В курсе про Excel эта функция и так, разумеется, была.
- Большое видео про Power Pivot (модель данных) в Магии Excel будет скоро.
Во-вторых, с 1 ноября цена на оба курса вырастет. Так что если думали про то, чтобы изучить основательно ту или иную тему или обучить свою команду/компанию (а на этих курсах у нас учатся сотрудники самых разных компаний, включая компанию из топ-10 крупнейших в России и национальный исследовательский университет) — самое время брать курс по старой цене с новыми материалами. Тем более несколько дней будет скидка — см ниже.
Немного фактов.
В каждом курсе есть исходные и заполненные файлы с примерами к каждому уроку, а в отдельных тарифах и подробные конспекты (суммарно как книга).
В Магии Excel 60+ уроков, в Гугл Драйве 90+.
Урокам можно ставить оценки. За все время у Гугл Драйва 600 оценок (из них 7 четверок и 2 тройки), у Магии Excel 245 оценок (тоже почти все пятерки: только 4 четверки, ни одной тройки).
Коллеги подкинули промокод на скидку, ловите:
35% до 5 октября.
https://www.mann-ivanov-ferber.ru/courses/magicexcel/
https://www.mann-ivanov-ferber.ru/courses/gdrive/
Издательство «МИФ»
Магия Экселя
50+ полезных функций и инструментов. Лайфхаки для ускорения работы. Обновления 2022
Простое. Вопрос из чата: "кто подскажет, как из строки вывести четвертое по счету слово в другой столбец?"
Подсказываем. Используем функцию
Либо тоже самое, но с новой функцией выбора столбца, который хотим оставить. Про новые функции.
Двигаем время формулами
💥 заказать работу | наш "пакет поддержки компаний"
Подсказываем. Используем функцию
SPLIT и делим строку по пробелу на отдельные слова. Далее используем INDEX и берём четвертое слово.=INDEX(SPLIT(W3;" ");4)
Либо тоже самое, но с новой функцией выбора столбца, который хотим оставить. Про новые функции.
=CHOOSECOLS(split(W3;" ");4)Двигаем время формулами
💥 заказать работу | наш "пакет поддержки компаний"
Задание для подписчиков:
Галатасарайзавтра выиграет в Лондоне Манчестере...
Задача: распределяем команды по группам с учетом дополнительного условия
Друзья, сегодня хотим предложить вам не решение, а задачу.
Мы недавно писали про случайное распределение команд по группам с помощью SORT, WRAPCOLS и RANDARRAY. Из столбца A (слева на скриншоте) получали одной формулой (она в оранжевой ячейке) то, что справа.
Усложняем задачу!
Сможете одной формулой формировать случайные группы по 4 команды так, чтобы (и в этом отличие от первоначальной формулы) в одной группе не было команд из одной страны? Все как в настоящей Лиге чемпионов (тут любители футбола загрустят, вспомнив, что это последний год, когда этот турнир проходит в текущем формате с группами😿)
Страны указаны в столбце B. Делайте копию таблицы и присылайте варианты в комменты! Потом опубликуем подборку решений :)
https://docs.google.com/spreadsheets/d/15QY8X7kfse7HoYbYqo_Th2-qtjMx8BZjlJNftbCfNG8
Галатасарай
Задача: распределяем команды по группам с учетом дополнительного условия
Друзья, сегодня хотим предложить вам не решение, а задачу.
Мы недавно писали про случайное распределение команд по группам с помощью SORT, WRAPCOLS и RANDARRAY. Из столбца A (слева на скриншоте) получали одной формулой (она в оранжевой ячейке) то, что справа.
Усложняем задачу!
Сможете одной формулой формировать случайные группы по 4 команды так, чтобы (и в этом отличие от первоначальной формулы) в одной группе не было команд из одной страны? Все как в настоящей Лиге чемпионов (тут любители футбола загрустят, вспомнив, что это последний год, когда этот турнир проходит в текущем формате с группами😿)
Страны указаны в столбце B. Делайте копию таблицы и присылайте варианты в комменты! Потом опубликуем подборку решений :)
https://docs.google.com/spreadsheets/d/15QY8X7kfse7HoYbYqo_Th2-qtjMx8BZjlJNftbCfNG8
Фокусы над временем
Друзья, есть столбец с датой и временем, A3:A20.
⚡️ Чтобы оставить только время, используем формулу:
⚡️ Чтобы округлить до получаса:
Откуда 48? 1 – это 24 часа, чтобы округлить до часа: время * 24, округляем результат до целых и делим на 24. Для округление до получаса используем 48, как аргумент, до двадцати минут – 72 и так далее.
⚡️ Впрочем, до часа можно округлить проще, просто возьмем час от каждой строки:
А зачем нам все это мы вам покажем в следующих постах. Ждите :)
дата и время в Google Таблицах
Друзья, есть столбец с датой и временем, A3:A20.
⚡️ Чтобы оставить только время, используем формулу:
=ARRAYFORMULA(A3:A20 - INT(A3:A20))
⚡️ Чтобы округлить до получаса:
=ARRAYFORMULA(ROUND(C3:C20*48)/48)
Откуда 48? 1 – это 24 часа, чтобы округлить до часа: время * 24, округляем результат до целых и делим на 24. Для округление до получаса используем 48, как аргумент, до двадцати минут – 72 и так далее.
⚡️ Впрочем, до часа можно округлить проще, просто возьмем час от каждой строки:
=ARRAYFORMULA(HOUR(A3:A20))
А зачем нам все это мы вам покажем в следующих постах. Ждите :)
дата и время в Google Таблицах
Не зря они используют шары с названиями команд для жеребьевки... а не Google Таблицы
Да, задача со случайным распределением команд по группам так, чтобы в группе не было команд из одной страны, оказалась непростой. Но некоторые из вас нашли решение и поделились под постом с задачей — выносим их в отдельную таблицу! Спасибо всем за участие!
Плюс одно решение от нас.
Вот таблица со всеми работающими решениями:
https://docs.google.com/spreadsheets/d/15CYM9j0U2RpPciQBK62Nzd5cqxlcfQ1QAGv1F9_Aksk
Мы протестировали их на существующих данных (реальному составу участников ЛЧ этого года) — все отработали корректно.
А также на измененных данных, где из одной страны аж 8 команд. С такой структурой сработали решения 1,2 и 4. Но в любом случае столько команд из одной страны в ЛЧ не участвуют, это уже за рамками задачи :)
Обратите внимание, что везде используются LET и LAMBDA. Новые функции уверенно вошли в нашу формульную жизнь :) Ну и разброс по длине формул (в два с лишним раза) и числу используемых функций внутри тоже радует, показывая, что у большинства задач, как правило, есть много решений.
P.S. И красивая таблица со сравнением решений, их авторами, формулами для проверки каждого решения и оценкой длины формул / числа функций в каждой — от Леонида (одно из решений — самое короткое — тоже его) 🔥
https://docs.google.com/spreadsheets/d/1VoxfzY6LmL01KTmAXh2G2OSqUXBsS-94oXkPnwxXpwk/edit#gid=0
Да, задача со случайным распределением команд по группам так, чтобы в группе не было команд из одной страны, оказалась непростой. Но некоторые из вас нашли решение и поделились под постом с задачей — выносим их в отдельную таблицу! Спасибо всем за участие!
Плюс одно решение от нас.
Вот таблица со всеми работающими решениями:
https://docs.google.com/spreadsheets/d/15CYM9j0U2RpPciQBK62Nzd5cqxlcfQ1QAGv1F9_Aksk
Мы протестировали их на существующих данных (реальному составу участников ЛЧ этого года) — все отработали корректно.
А также на измененных данных, где из одной страны аж 8 команд. С такой структурой сработали решения 1,2 и 4. Но в любом случае столько команд из одной страны в ЛЧ не участвуют, это уже за рамками задачи :)
Обратите внимание, что везде используются LET и LAMBDA. Новые функции уверенно вошли в нашу формульную жизнь :) Ну и разброс по длине формул (в два с лишним раза) и числу используемых функций внутри тоже радует, показывая, что у большинства задач, как правило, есть много решений.
P.S. И красивая таблица со сравнением решений, их авторами, формулами для проверки каждого решения и оценкой длины формул / числа функций в каждой — от Леонида (одно из решений — самое короткое — тоже его) 🔥
https://docs.google.com/spreadsheets/d/1VoxfzY6LmL01KTmAXh2G2OSqUXBsS-94oXkPnwxXpwk/edit#gid=0
Выделяем на диаграмме текущий месяц
Допустим, вам нужно выделить на диаграмме отдельную точку или период, и чтобы это происходило автоматически (например, максимальное значение или два наибольших, текущий месяц или текущий и предыдущий).
Можно добавить дополнительный столбец с формулой, которая будет возвращать значение показателя из столбца с данными, если соответствующая строка соответствует условию (допустим, это текущий месяц), и будет возвращать ничего, если строка условию не соответствует.
Этот столбец будет вторым рядом данных - который будет "поверх" основного, и это будет выглядеть как выделение отдельных точек/периода. Этот ряд можно сделать с большим контуром и более ярким цветом.
Формула в общем виде
Допустим, вам нужно выделить на диаграмме отдельную точку или период, и чтобы это происходило автоматически (например, максимальное значение или два наибольших, текущий месяц или текущий и предыдущий).
Можно добавить дополнительный столбец с формулой, которая будет возвращать значение показателя из столбца с данными, если соответствующая строка соответствует условию (допустим, это текущий месяц), и будет возвращать ничего, если строка условию не соответствует.
Этот столбец будет вторым рядом данных - который будет "поверх" основного, и это будет выглядеть как выделение отдельных точек/периода. Этот ряд можно сделать с большим контуром и более ярким цветом.
Формула в общем виде
=ЕСЛИ(условие, по которому выбираются точки для выделения;значение из столбца с данными;"")
Например, если мы выделяем текущий месяц на диаграмме: =ЕСЛИ(МЕСЯЦ(СЕГОДНЯ())=месяц в этой строке;значение в этой строке;"")
Таблица с примеромНесколько лайфхаков для работы в Google Workspace
— Клавиша S на Диске: делаем файл или папку помеченными (Starred) — после этого всегда будет под рукой, собственно в "Помеченных" (по сути это избранное)
— Горячие клавиши и ссылки для создания новых таблиц/документов в интерфейсе Google Диска: Shift+F — папка; Shift+S — таблица; Shift+T — документ; Shift+P — презентация
— Голосовой ввод в Документах (Ctrl + Shift + S)
— Ссылка на слайд/заголовок/закладку/фрагмент таблицы. Добавлять в Избранное / на панель закладок в браузере документ или презентацию сразу в нужном месте / на нужном слайде
— Отвечать на комментарии можно прямо в почте, не открывая документ/таблицу — в письме с уведомлением о комментарии
— Клавиша S на Диске: делаем файл или папку помеченными (Starred) — после этого всегда будет под рукой, собственно в "Помеченных" (по сути это избранное)
— Горячие клавиши и ссылки для создания новых таблиц/документов в интерфейсе Google Диска: Shift+F — папка; Shift+S — таблица; Shift+T — документ; Shift+P — презентация
— Голосовой ввод в Документах (Ctrl + Shift + S)
— Ссылка на слайд/заголовок/закладку/фрагмент таблицы. Добавлять в Избранное / на панель закладок в браузере документ или презентацию сразу в нужном месте / на нужном слайде
— Отвечать на комментарии можно прямо в почте, не открывая документ/таблицу — в письме с уведомлением о комментарии
Простой скрипт поиска на двух листах
Друзья, недавно встала задача искать данные на двух листах. Конечно, в Таблицах есть встроенный инструмент "найти и заменить" и в нём можно искать на всех листах, но его не стали использовать из-за того, что сотрудники могут промахнуться и нажать "заменить всё" и после найденное заменится на пустоту :)
Поэтому мы написали простой скрипт и делимся им с вами. В начале – вызываем
Если находим, то добавляем к сообщению, которое будем выводить в конце (сообщение на скриншоте): название листа, то, что мы нашли и номер строки. Номер строки тут условный, он будет правильным только если данные на наших листах начинаются с первой строки. Но для нашей задачи этого вполне хватило.
И сам скрипт:
ПС В комментарии пришел Кирилл и показал свой вариант с textFinder
Друзья, недавно встала задача искать данные на двух листах. Конечно, в Таблицах есть встроенный инструмент "найти и заменить" и в нём можно искать на всех листах, но его не стали использовать из-за того, что сотрудники могут промахнуться и нажать "заменить всё" и после найденное заменится на пустоту :)
Поэтому мы написали простой скрипт и делимся им с вами. В начале – вызываем
inputBox, чтобы пользователь ввёл то, что мы ищем, далее обращаемся к двум листам и далее проходимся по каждой ячейке каждого листа в цикле и ищем в ней с помощью регулярного выражения наше значение.Если находим, то добавляем к сообщению, которое будем выводить в конце (сообщение на скриншоте): название листа, то, что мы нашли и номер строки. Номер строки тут условный, он будет правильным только если данные на наших листах начинаются с первой строки. Но для нашей задачи этого вполне хватило.
И сам скрипт:
function search() {
const search = Browser.inputBox('Search');
if (search) {
const ss = SpreadsheetApp.getActive();
const sh0 = ss.getSheetByName('LOADS');
const sh1 = ss.getSheetByName('CANCELED');
const d0 = sh0.getDataRange().getValues();
const d1 = sh1.getDataRange().getValues();
var msg = '';
d0.forEach((row, y) => {
row.map((cell, x) => msg = new RegExp(search, 'i').test(cell) ? msg + '\\n' + 'LOADS' + ', ROW: ' + y + 1 + ' ' + cell : msg);
});
d1.forEach((row, y) => {
row.map((cell, x) => msg = new RegExp(search, 'i').test(cell) ? msg + '\\n' + 'CANCELED' + ', ROW: ' + y + 1 + ' ' + cell : msg);
});
Browser.msgBox(msg)
};
};ПС В комментарии пришел Кирилл и показал свой вариант с textFinder
Меняем регистр у предложения
Жаль, нет функции для того, чтобы только первая буква всего текста была заглавной. Это можно исправить формулой из нескольких функций. Нужно соединить первую букву, сделав ее заглавной, и все остальные, сделав их строчными:
Жаль, нет функции для того, чтобы только первая буква всего текста была заглавной. Это можно исправить формулой из нескольких функций. Нужно соединить первую букву, сделав ее заглавной, и все остальные, сделав их строчными:
=ПРОПИСН(ЛЕВСИМВ(текст))&СТРОЧН(ПРАВСИМВ(текст;ДЛСТР(текст)-1))
=UPPER(LEFT(текст))&LOWER(RIGHT(текст;LEN(текст)-1))
Извлекаем первую букву (ЛЕВСИМВ / LEFT) делаем ее заглавной (ПРОПИСН / UPPER), прикрепляем (&) к этому все буквы справа (ПРАВСИМВ / RIGHT), кроме первой (длина всего текста — ДЛСТР / LEN — за вычетом единицы) и делаем строчными (СТРОЧН / LOWER).Forwarded from Google Таблицы
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ / SUBTOTAL: подсчет только отфильтрованных строк
Эта функция позволяет производить вычисление только с видимыми строками.
У нее такой синтаксис:
Вот базовые функции (кроме них есть еще стандартное отклонение и дисперсия):
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) в ней, то в строке итогов функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ будет формироваться автоматически, вам достаточно выбрать тип вычисления, например, "среднее".
Эта функция позволяет производить вычисление только с видимыми строками.
У нее такой синтаксис:
=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_sheets
Друзья, привет! Наша новая услуга – внедрение нашей команды (автор канала Евгений и его спецы) в ваш бизнес для помощи по Таблицам и GAS-скриптам.
💥Как это выглядит
Мы создаём чатик, вы добавляете своих коллег, мы добавляем наших асов.
💥 Условия
Стоимость 225 000 р. за три месяца, далее по 75 000 р. в месяц, если захотите продолжить.
💥 Что может входить в пакет поддержки
(чем сможем помочь вашему бизнесу)
Друзья, привет! Наша новая услуга – внедрение нашей команды (автор канала Евгений и его спецы) в ваш бизнес для помощи по Таблицам и GAS-скриптам.
💥Как это выглядит
Мы создаём чатик, вы добавляете своих коллег, мы добавляем наших асов.
💥 Условия
Стоимость 225 000 р. за три месяца, далее по 75 000 р. в месяц, если захотите продолжить.
💥 Что может входить в пакет поддержки
(чем сможем помочь вашему бизнесу)
Нужно построить простой линейный прогноз?
Можно использовать функцию TREND / ТЕНДЕНЦИЯ.
В нашем примере — исторические данные по выручке, старые даты, новые даты.
Функция выдает прогноз по методу наименьших квадратов (линейное уравнение с минимальной суммой квадратов отклонений — речь про отклонения линии тренда от фактических значений).
С ним можно оценить тренд, но не более того. Если хочется добавить еще и сезонность, то придется пошаманить дополнительно (расскажем об этом отдельно).
Делитесь в комментах, кто как прогнозирует в Google Таблицах!
💥 Другие наши посты можно найти в оглавлении канала.
Можно использовать функцию TREND / ТЕНДЕНЦИЯ.
=ТЕНДЕНЦИЯ (фактические значения прогнозируемого показателя y; фактические значения x — ими могут быть номера периодов; прогнозируемые значения x)В нашем примере — исторические данные по выручке, старые даты, новые даты.
Функция выдает прогноз по методу наименьших квадратов (линейное уравнение с минимальной суммой квадратов отклонений — речь про отклонения линии тренда от фактических значений).
С ним можно оценить тренд, но не более того. Если хочется добавить еще и сезонность, то придется пошаманить дополнительно (расскажем об этом отдельно).
Делитесь в комментах, кто как прогнозирует в Google Таблицах!
💥 Другие наши посты можно найти в оглавлении канала.
Google Таблицы
Нужно построить простой линейный прогноз? Можно использовать функцию TREND / ТЕНДЕНЦИЯ. =ТЕНДЕНЦИЯ (фактические значения прогнозируемого показателя y; фактические значения x — ими могут быть номера периодов; прогнозируемые значения x) В нашем примере —…
А если нужно прогнозировать с учетом сезонности?
Самый простой подход — рассчитать коэффициенты сезонности. Допустим, у нас сезонность по месяцам. Для каждого месяца рассчитаем его коэффициент:
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. Полученные коэффициенты используем в прогнозе. Умножаем прогноз, полученный функцией
Самый простой подход — рассчитать коэффициенты сезонности. Допустим, у нас сезонность по месяцам. Для каждого месяца рассчитаем его коэффициент:
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).Парсим ключевую ставку ЦБ 🗣 не по-человечески*
Друзья, на сайте ЦБ, в разделе с ключевой ставкой есть график, если нам нужна ключевая ставка за доступные даты, то мы можем обратиться к данным в коде страницы из которых построен этот график, обработать их и вставить эти данные в Таблицу.
Скрипт как всегда простой – обращаемся к странице, далее достаём регулярным выражением даты и значения, как строки, делим по запятой и собираем из этого массив, который вставляем в нашу Таблицу.
В Таблице у вас появятся два столбца – даты и значения.
Таблица, если нужна
* а по-человечески данные можно получать так, может попробуем в другой раз
Друзья, на сайте ЦБ, в разделе с ключевой ставкой есть график, если нам нужна ключевая ставка за доступные даты, то мы можем обратиться к данным в коде страницы из которых построен этот график, обработать их и вставить эти данные в Таблицу.
Скрипт как всегда простой – обращаемся к странице, далее достаём регулярным выражением даты и значения, как строки, делим по запятой и собираем из этого массив, который вставляем в нашу Таблицу.
В Таблице у вас появятся два столбца – даты и значения.
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);
};Таблица, если нужна
* а по-человечески данные можно получать так, может попробуем в другой раз
Please open Telegram to view this post
VIEW IN TELEGRAM
Несколько советов для работы в браузере Google Chrome
Сегодня не совсем о таблицах... Хотя таблицы у нас существуют не сами по себе, а во вкладках браузера, так что эти приемы могут пригодиться и для работы с ними тоже.
Пишите в комментариях свои лайфхаки - добавим в пост!
Сегодня не совсем о таблицах... Хотя таблицы у нас существуют не сами по себе, а во вкладках браузера, так что эти приемы могут пригодиться и для работы с ними тоже.
Пишите в комментариях свои лайфхаки - добавим в пост!
Teletype
Несколько советов для работы в браузере Google Chrome
Выделяем текст на странице, щелчок правой кнопкой мыши и в контекстном меню выбираем “Копировать ссылку на выделенный текст” (Copy link...
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ / SUBTOTAL: подсчет только отфильтрованных строк
Эта функция позволяет производить вычисление только с видимыми строками.
У нее такой синтаксис:
Вот базовые функции (кроме них есть еще стандартное отклонение и дисперсия):
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) в ней, то в строке итогов функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ будет формироваться автоматически, вам достаточно выбрать тип вычисления, например, "среднее".
Эта функция позволяет производить вычисление только с видимыми строками.
У нее такой синтаксис:
=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) в ней, то в строке итогов функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ будет формироваться автоматически, вам достаточно выбрать тип вычисления, например, "среднее".