Заказать работу у @google_sheets

Мы уже более пяти лет создаём на заказ Google Таблицы, разные полезные скрипты и Telegram ботов.

Несколько примеров

Есть работа? Напишите нашему боту @vas_mnogo_a_ya_bot

Конвертатор (XLSX > Google Таблица)

Привет, задачка из нашего чата. У вас есть папка на Google Диске с XLSX-файлами и вы хотите каждый файл превратить в Google Таблицу.

Как это сделать? Ну, точно можно в каждый файл зайти руками и дальше выбрать "файл > сохранить как Google Таблицу".

Если десять файлов, то мы с этим справимся, а вдруг их будет сто?

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

1) Копируйте Таблицу, открывайте код и вводите адрес вашей папки с файлами в первую строку кода.

2) Запускайте скрипт и скрипт начинает искать первый файл без "_" в названии.

3) Находит, копирует и превращает копию в Таблицу. А после к исходному файлу в название добавляет "_".

4) И так или до окончания списка файлов или до того, как скрипт завершится по тайм-ауту (6 минут). В этом случае просто запускаем скрипт еще раз.

Таблица с кодом

---
⭐️ Заказ работы
⭐️ Наш курс по Excel, Таблицам и скриптам: тыц

А теперь превращаем Таблицы в XLSX, обходя всю заданную папку

Друзья, в последнем посте мы превращали XLSX-файлы в Таблицы в заданной папке скриптом, сейчас же делаем обратное действие, превращаем Таблицы в XLSX-файлы.

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

---
⭐️ Заказ работы
⭐️ Наш курс по Excel, Таблицам и скриптам: тыц

Сочетания клавиш при работе с формулами

Shift + F1 — отображает и скрывает список аргументов функции

F1 — раскрывает и скрывает подробное описание аргументов

F9 — отображает и скрывает подсказку (вычисление формулы или выделенного фрагмента)

---
⭐️ Заказ работы
⭐️ Наш курс по Excel, Таблицам и скриптам: тыц

Дополнение GPT Copilot для Google Таблиц

Друзья, привет! Короткий обзор дополнения от нашего старожила Каната, слово ему:
---

Недавно столкнулся с прикольным дополнением для Google таблиц, о котором расскажу в видео.

Продемонстрирую несколько кейсов применения предустановленных функций из аддона:

=GPTX() - генерирует текст по запросу;

=GPTX_LIST() - генерирует список по запросу;

=GPTX_TABLE() - генерирует таблицу;

=GPTX_EXTRACT() - извлекает значения по ключу;

@akanat, спасибо!

Оберни колонки: новая (относительно) функция WRAPCOLS

Итак, нам с вами нужно превратить одномерный массив — например, столбец, в котором данные цикличные (время начала мероприятия + N строк с выступающими в нашем примере) — в двумерный, разместив каждый повторяющийся "блок" в отдельный столбец.

Засунем диапазон в WRAPCOLS, вторым аргументом укажем, сколько ячеек отправлять в каждый столбец. Необязательный третий аргумент — как возвращать пустые ячейки из исходника, если они там будут. Иначе будет выводиться ошибка #N/A (#Н/Д).

=WRAPCOLS(A1:A;N; [чем заменить пустые])

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

=WRAPCOLS(FILTER(A1:A;A1:A<>"");N)

P.S. Раз есть функция WRAPCOLS — значит — это кому-нибудь нужно? есть и WRAPROWS.
P.P.S. В Excel (365) при русскоязычном интерфейсе — СВЕРНСТОЛБЦ и СВЕРНСТРОК.

Слово вам!

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

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

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

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

Друзья, формула =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

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

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