Слово вам!

Друзья, привет! Расскажите в комментариях

– как используете Таблицы вы?

– если бы вы могли заказать Таблицу со скриптами (или без) для решения своих задач, чтобы это была за Таблица?

Месяц из дат в именительном падеже

Друзья, формула =text(ваша дата;"mmmm"), c русскими региональными настройками вернёт вам месяц в родительном падеже.

То есть: января, февраля, марта, июня ...

Что сделать, если мы хотим получить именительный падеж: январь, февраль, март?

Попробуем быть оригинальными, напишем три регулярных выражения:

1) меняем мая на май
2) меняем я, если она заканчивает слово на ь
3) меняем а, если она заканчивает слово на пустоту (или удаляем)

=REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(B3;"мая";"май");"я$";"ь");"а$";)

И более классические варианты :)

---
⭐️ ЗАКАЗАТЬ РАБОТУ
⭐️ Наш курс по Excel, Таблицам и скриптам: тыц

Расходусы

Телеграм бот для подсчёта расходов связанный с Таблицей

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

Статья про интеграцию века

Подсчитываем число флажков

Итак, у вас есть флажки, которыми вы отмечаете (напоминаем: можно пробелом включать/выключать флажки) что-то выполненное/сданное/имеющееся, и вам нужно подсчитывать все отмеченные (или все отключенные).

Вспоминаем, что флажки — это логические значения TRUE / FALSE (ИСТИНА / ЛОЖЬ), так что можно считать таковые функцией СЧЁТЕСЛИ / COUNTIF с единственным условием — ИСТИНА или ЛОЖЬ в столбце с флажками.
=СЧЁТЕСЛИ(B2:B;ИСТИНА)

Еще один вариант — использовать функцию СУММПРОИЗВ / SUMPRODUCT. Вообще она перемножает значения в нескольких диапазонах, а потом складывает произведения (то есть можно одной функцией подсчитать стоимость всех товаров, если у вас столбцы с ценой и количеством, например). А здесь будет только один диапазон, в котором в результате умножения ИСТИНЫ превратятся в единицы и просуммируются.

=СУММПРОИЗВ(B2:B21)

А чтобы посчитать отключенные флажки, просто применим к диапазону функцию НЕ / NOT (логическое отрицание).

=СУММПРОИЗВ(НЕ(B2:B21))

Но вот последний вариант не сработает с открытым диапазон или ссылкой на весь столбец (потому что пустые ячейки тоже будут считаться) — в таком случае лучше использовать СЧЁТЕСЛИ / COUNTIF.

Ну а если вам нужно считать не все подряд флажки, а с условием, то используйте СЧЁТЕСЛИМН / COUNTIFS:
=СЧЁТЕСЛИ(диапазон с флажками; ИСТИНА; диапазон условия; условие)

Ссылка на таблицу с примерами

P.S. А в этом посте писали про то, как работать с флажками через скрипты.
P.P.S. Те, кто писал формулы в Excel давно — до 2007 версии, например — может помнить, что СУММПРОИЗВ активно использовалась не только по назначению, как в примере с флажками. Эта труженица заменяла СУММЕСЛИМН / SUMIFS, которой еще не было:
=СУММПРОИЗВ((диапазон условия = условие) * (диапазон суммирования))

и позволяла создавать формулы массива без Ctrl+Shift+Enter, например, подсчитать длину текста (ДЛСТР / LEN) в целом диапазоне сразу:
=СУММПРОИЗВ(ДЛСТР(диапазон))

Бот сохраняет всё 2

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

Бот сохраняет каждое сообщение новой строкой в Таблицу, если в сообщении будет файл, то в строке будет ссылка на этот файл. Гифка с примером работы.

Таблица с кодом бота здесь, копируйте.

Как поднять у себя:
1) зарегистрировать бота в @botfather и взять его токен и вставить в редактор скриптов скопированной таблицы
2) далее правильно развернуть веб-приложение в таблице (от лица своего аккаунта и для всех), кто не умеет, вот видео
3) далее скопировать ссылку развернутого приложения, вставить в редактор скриптов и запустить скрипт setWebHook()

Всё, после этого всё должно заработать, если не работает – посмотрите в Таблице лист "ошибки".

Что изменилось в коде - для доступа к объекту, который приходит от телеграм мы используем библиотеку jsonPath, с её помощью удобнее добираться до нужных переменных.

---
⭐️ ЗАКАЗАТЬ РАБОТУ
⭐️ Наш курс по Excel, Таблицам и скриптам: тыц

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

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

Рассчитываемое поле в сводных таблицах

Срезы в сводных таблицах

​​Сводная по “бесконечному” количеству строк

Группировка текстовых элементов в сводной

У вас есть данные по отделам, а вам нужно сгруппировать несколько отделов в департаменты, причем только в сводной, а не в исходных данных?

Выбираем несколько значений (с зажатой клавишей Ctrl) — щелкаем правой кнопкой и нажимаем на загадочную надпись "Сгруппировать сводные таблицы" (Create pivot group)

И теперь в строках сводной будет два уровня! Выбранные элементы сгруппируются в один на верхнем уровне. Можно и другие элементы тоже далее сгруппировать.

Рассчитываемое поле в сводных таблицах

Срезы в сводных таблицах

​​Сводная по “бесконечному” количеству строк

Ура! Теперь можно уничтожить 170 ненужных фильтров одним кликом без скрипта!

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

Это пригодится, если вы (и ваши коллеги) забываете удалять ненужные фильтры. Ведь если создать фильтр и просто его закрыть, он сохранится под названием ФильтрN, и в файлах с большим числом пользователей таких фильтров мы наблюдали и по 150 штук.

Теперь без скрипта: режимы фильтрации на панели инструментов — Удалить все фильтры.

А для профилактики этого безобразия: если фильтр нужен, меняем имя на что-то содержательное ("Лютые Продажи" или "Маша НЕ УДАЛЯТЬ" или что-то подобное), а если не нужен, то нажимаем на шестеренку, а не крестик, и удаляем.

Подробнее про фильтра можно прочитать тут:
https://tttttt.me/google_sheets/337

А посмотреть тут:
https://www.youtube.com/watch?v=kHN5sIFLIjw

Средневзвешенное по условию

Чтобы посчитать среднее по каким-то условиям, подойдет СРЗНАЧЕСЛИМН / AVERAGEIFS.
Но это среднее арифметическое. Оно не всегда подходит. Например, если вам нужно посчитать среднюю цену, правильнее будет учесть "веса" — число проданных по каждой цене товаров или количество отработанных по каждой цене часов.

Для средневзвешенного используем функцию AVERAGE.WEIGHTED. В ней сначала указывается ссылка на показатель, а затем — на веса.

Ну а если нужно считать не все подряд, а только значения по какому-то условию, то аргументы для функции можно получить с помощью функции FILTER.

Ссылка на таблицу с примером

Отделяем с помощью условного форматирования блоки друг от друга

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

Допустим, для работы с Таблицей вы хотите отделить группы друг от друга визуально:

1) Выделяем наш диапазон
2) Условное форматирование > создать правило > правило форматирования формулой
3) Вводим формулу =ISODD( MATCH($A2;UNIQUE($A$2:$A$18);0))
4) Выбираем цвет и вуаля, всё работает

Формула простая, мы делаем уникальный список с названиями групп UNIQUE($A$2:$A$18) и ищем номер вхождения каждой группы в этот список, номер будет 1,2,3,4,... Далее мы проверяем номер на нечетность (можете проверять на четность с помощью ISEVEN) и формула выводит true для каждой нечетной группы и условное форматирование окрашивает эти строки.

Таблица, в которой всё работает (можно сделать копию и посмотреть)

---
⭐️ ЗАКАЗАТЬ РАБОТУ
⭐️ Оглавление канала: ты-дыц

Превращаем лист Таблицы или в xlsx / pdf / csv

Друзья, напоминаем вам про наше абсолютно бесплатное решение.

Несколько скриптов, с их помощью вы сможете конкретный лист Таблицы превратить в XLSX (файл сразу скачается локально) и далее уже руками скопировать полученный файл и отправить поставщику / заказчику / коллегам по работе.

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

PS И только вчера обсуждали в нашем чатике решение

Импорт только выбранных столбцов из другой таблицы

Если вы хотите выбирать в выпадающем списке заголовки тех столбцов из другой таблицы, которые хотите импортировать, а не грузить все подряд, то можно сделать следующее:
1. Загрузить заголовки из источника (ТРАНСП / TRANSPOSE — чтобы в столбик) на отдельный лист:
=ТРАНСП(IMPORTRANGE("ссылка на источник";"Название_листа!1:1"))

2. Настроить проверку данных на основе этого списка — чтобы выбирать в выпадающем списке заголовки

3. Далее нужно сделать формулу, которая в общем виде будет делать следующее: импортировать (IMPORTRANGE) только выбранные столбцы (QUERY). Запрос для QUERY будет вида Select ColN, ColM, ... , где N,M и так далее — порядковые номера нужных нам столбцов, определяемые функцией ПОИСКПОЗ / MATCH

Ловите таблицу с двумя вариантами формул и пользуйтесь на здоровье 🤠

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

Ии-шечка, @zadavai_vopros_bot

Друзья, мы сделали ChatGPT телеграм бота, на вопросы бот отвечает с помощью GPT 3.5, картинки рисует с помощью DALL·E 2, контекст поддерживает.

Можно попробовать, первые десять запросов - бесплатны.

Если бот будет отвечать долго - потерпите, мы поправим это в первую очередь.

Чтобы использовать бота после 10 бесплатных запросов -> 500 рублей / месяц по любым нашим донатным реквизитам и напишите в бота "я оплатил".

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

Пишем в @zadavai_vopros_bot простой скрипт на Google Apps Script, запускаем и следующим сообщением его редактируем.

Друзья, видео со звуком.

PS! С помощью GPT действительно можно писать скрипты на GAS, а также задавать про программирование, но имейте ввиду, что не всегда то, что вернёт ИИ будет правдой. Поэтому не слепо верим, а пробуем, читаем документацию и пытаемся разобраться.

150 уникальных пользователей @zadavai_vopros_bot за 4 дня – это здорово, спасибо за то, что участвуете в тестировании!

Помимо общения и создания кода наш GPT-бот может делать и необычные вещи, к примеру, отвечать на отзывы на товары (смотрите на скриншот).

А в режиме "диалог" вы можете отредактировать ответ, который получите, например, попросить ИИ сделать отзыв короче, убрать мат или добавить предложение о возмещении ущерба 😎

Col1, Col2, ColN в QUERY

Друзья! А теперь в функции QUERY можно сразу обращаться к колонкам как Col1, ColN, где N - номер колонки в диапазоне, который вы в QUERY передаёте.

Напомним, ранее можно было обращаться к столбцам только как A, B, C и так далее, когда источником (первым аргументом) был обычный диапазон.
А ColN — только когда источником выступал массив (например, несколько диапазонов, объединенных через фигурные скобки, VSTACK, или функция IMPORTRANGE, и т.д.)

Как это использовать? Вот так, например.

Статьи про Таблицы и скрипты от Михаила Смирнова за четыре года

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

Google Data Studio на примере небольшой задачи

🚜 Собиратыр-тыр-тыр 🚜

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

FLATTEN 2: Other Undocumented Google Sheets Functions

Получение курсов валют в Google Sheets

Исторические данные индекса Московской Биржи (IMOEX) в Google Sheets

Программно даём доступ для IMPORTRANGE к другой таблице

Массово удаляем временные фильтры через Google Sheets API

Как проредить колонки и столбцы диапазонов в Google Sheets

Google Apps Script Library — Библиотеки в Гугл Скриптах

Google Apps Script — Сам себе триггер в библиотеке

Google Apps Script Library — Сам себе триггер в библиотеке (короткая версия)

Google Apps Script — Custom Functions — Полезные Мелочи

Google Sheets — Немного про IMPORTRANGE()

Google Apps Script — Публикация листов

Google Sheets — Время расчёта формулы

Новые функции в Google Sheets (2023-02-02)

PS от автора: имейте в виду, что некоторые из решений теперь делаются проще (например, через lambda-функции), но на момент написания, автору решения казались неплохими