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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Подробнее про фильтра можно прочитать тут:
https://www.tgoop.com/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-функции), но на момент написания, автору решения казались неплохими

Что мы вам посоветуем из подборки (ну кроме просмотра фильма "основной инстинкт")

Новые функции Таблиц: Новые функции в Google Sheets (2023-02-02)

Про курсы валют (кстати, cbr опять можно достать с помощью IMPORTXML): Получение курсов валют в Google Sheets

Как дать расшарить доступ к IMPORTRANGE с помощью скрипта: Программно даём доступ для IMPORTRANGE к другой таблице

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

---
⭐️ Оглавление канала: ты-дыц

Сортировка списка в случайном порядке

Чтобы решить такую задачу, будем использовать SORTN и сортировать этой функцией наш список по другому столбцу — виртуальному, состоящему из случайных чисел. Сгенерируем его функцией RANDARRAY. Единственным аргументом будет число элементов в нашем списке — мы его считаем функцией СЧЁТЗ / COUNTA.

Чтобы все работало для любого числа элементов в списке, диапазон задаем не явно (A2:A30, допустим), а через СМЕЩ / OFFSET по тому же самому числу элементов в списке.
OFFSET(первая ячейка;нулевой отступ по строкам; и по столбцам; число элементов, найденное через COUNTA) — выдаст ссылку на диапазон от первой ячейки до последней заполненной.
Далее сортируем его по столбцу, сформированному RANDARRAY.
Так как у нас в формуле трижды используется число элементов (COUNTA(диапазон)) — то мы его заменим на N с помощью функции LET, дабы не повторяться.

=let(N; COUNTA(диапазон); SORTN(OFFSET(первая ячейка;;;n);n;;RANDARRAY(n);1))

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

Сделать случайную сортировку диапазона можно и из интерфейса. Гифка от Михаила Смирнова.

В нашем чате задали вопрос, а зачем вообще сортировать массив данных в случайном порядке?

Представьте: у вас есть Таблица с тысячей ссылок на звонки колл центра. Вы хотите взять случайные 25 звонков для проверки. Перемешиваем массив и берём 25 строк сверху (ну или снизу) 😎

@zadavai_vopros_bot для анализа текста

А нашего GPT-бота можно использовать и для анализа текста.

Отправляем текст
— просим сделать выжимку
— или вывести ключевые слова
— или даже перевести текст на другой язык
... попробуйте свой вариант, у вас есть 10 запросов бесплатно (а кто хочет обнулить счетчик, пишите @namokonov)

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

Как получить первые или последние 3 (или сколько угодно) строк из диапазона с данными?

Выводим 3 первые строки :
=ARRAY_CONSTRAIN(A1:E9;3;COLUMNS(A1:E9))

Выводим 3 последние строки:
=CHOOSEROWS(A1:E9; SEQUENCE(1;3;ROWS(A1:E9);-1))

А создать диапазон диких, но симпатичных привидений нам помогла протянутая формула =REPT("👻";ROW())

Задача из нашего чатика: @google_spreadsheets_chat, скоро 5000 участников, залетайте!

PS Вариантов этих формул может быть множество - напишите в комментариях свои, а мы добавим в Таблицу

Таблица

⭐️ Оглавление канала: ты-дыц

Переносим форматирование с одного листа на множество

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

Что делать? Можно обойти каждый лист и вставить форматирование туда.

А можно всё сделать на одном листе (у нас он называется "шаблон") и воспользоваться нашим скриптом.

Скрипт в комментариях ↓