Добавляем к QUERY итоги

Функция QUERY не умеет добавлять итоги по строкам / столбцам к данным, которые выводятся.

Зато, вы можете написать несколько QUERY: одну для вывода запроса, другие для вывода итогов и объединить все эти функции в один массив.

Подробно про объединение массивов мы писали здесь.

В результате вы получите одну формулу и эта формула будет выводить всё.

Примеры формул, QUERY с итогами по столбцам:
={ QUERY(B3:D11;"SELECT B, SUM(D) GROUP BY B PIVOT C");
TRANSPOSE( QUERY(B3:D11;"SELECT SUM(D) GROUP BY C")) }

QUERY с итогами по столбцам, строкам и общей суммой:
={
{ QUERY(B3:D11;"SELECT B, SUM(D) GROUP BY B PIVOT C");TRANSPOSE( QUERY(B3:D11;"SELECT SUM(D) GROUP BY C")) }\
{ QUERY(B3:D11;"SELECT SUM(D) GROUP BY B");QUERY(B3:D11;"SELECT SUM(D) label SUM(D) ''") }
}

Подробный разбор - на скриншоте.
Таблица с примером

Сегодня два кейса.

У нас есть ряд чисел от 0 до 1231, как посчитать одной формулой количество чётных элементов?

Разбор — на скриншоте и в Таблице.

Итоговая формула:
=COUNTIF( ARRAYFORMULA( ISEVEN(SEQUENCE(1232;1;0)));true)

Мы выбрали любой месяц, как посчитать в нем количество суббот?

Разбор — на скриншоте и в Таблице.

Итоговая формула:
=COUNTIF( ARRAYFORMULA( WEEKDAY(SEQUENCE(DAY( EOMONTH(I1241;0) );1;I1241);2))

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

Пользовательские форматы: округление до тысяч и миллионов

Продолжим исследовать бездонный мир пользовательских форматов и сегодня поговорим про округление.

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

Если хочется отображать в тысячах с десятыми/сотыми/прочими долями тысячи, можно добавить знаки после запятой:
0.00,

Сработает округление и для форматов с разделителями разрядов:
#,##0,
(То есть мы будем показывать число в тысячах, но если округленный результат будет иметь больше 10 тыс. - то в нем появятся разделители разрядов)

Можно добавить надпись после числа - чтобы не запутаться и точно понимать, что здесь числа в тысячах:
#,##0, тыс

Помня о том, что мы можем задавать отдельные форматы (через точку с запятой) для положительных и отрицательных чисел, можно задать формат с округлением для положительных чисел, а отрицательные показывать в обычном формате:
#,##0,;#,##0

И, конечно, можно пойти дальше и отображать число в миллионах или миллиардах:
#,##0,, - миллионы с разделителями разрядов
0,,, - миллиарды без разделителей разрядов

Файл с примерами

Скрипт: создаем каталог из файлов выбранной папки в Google Таблице. Google Drive Api.

Как работает:
1) вам нужно сделать копию таблицы
2) ввести в ячейку «B1» ID папки
3) запустить из меню [ с к р и п т ы ] единственный скрипт

Скрипт рекурсивный, он будет искать в папке, во вложенных папках, а также покажет все случаи, когда файл, находящийся в папке одновременно лежит еще где-то, на Google Drive это возможно.

Превращаем Таблицы в полноценное приложение:

Планировщик для занятий в тренажерном зале от нашего подписчика

Друзья, сегодня отличный пример того, что на базе бесплатных Google Таблиц можно собрать что угодно. Ниже — пост от нашего подписчика, он сделал планировщик для занятий в тренажёрном зале. Занятия заносятся из связанного выпадающего списка по группам, а на листе «как выполнять» можно посмотреть фото и видео выбранного занятия, они будут загружаться с помощью прекрасной функции =IMPORTXML.

Передаем слово @akunilovskiy:
Те, кто хотят добиться результатов в тренажерном зале знают, что тренировки нужно планировать, а результаты записывать. Когда я решил делать это с помощью смартфона я попробовал несколько приложений для Android, но ни одно не показалось удобным.
Мне нужны были: легко доступная история тренировок, быстрое составление плана тренировки (в т.ч. на основе предыдущих) и, желательно, не только посредством экрана мобильного устройства, широкий выбор упражнений или возможность добавлять свои.
В итоге я сделал журнал/планировщик тренировок с нужным мне функционалом на базе Google Таблиц.
Поскольку результат меня устраивает, то я решил им поделиться с подписчиками канала и чата Google Таблицы.
Если при использовании возникнут вопросы, предложения - можете задать их в группе: https://t.me/workoutplanner
1. Текущая версия планировщика/журнала тренировок.
2. Инструкция.

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

И отдельное спасибо тем, кто присылал нам свои решения, мы начислили вам +10 кармы в чате за каждый опубликованный пост:

@mike59 (+30 кармы), целых три поста:
— Изменяем наш Google Calendar с помощью скриптов
— Скрипт для работы с Google Календарем. Напоминалка про дни рождения
— Таблица для учета своих активов

@mildly_disastrous (+20 кармы):
— Прочитаем, что написано на стикерах. OCR в Google Docs
— Важный скрипт. Связанные выпадающие списки из кэша
— Перемещение мебели

@IT_sAdmin (+10 кармы):
— Вывод названия месяца из даты в именительном падеже

@IT_sAdmin, @mrykin, @avazbek88, Макс Махров (всем по +10)
— База знаний регулярных выражений для REGEXEXTRACT

@akunilovskiy (+10)
— Планировщик для занятий в тренажерном зале

Друзья, будут новые идеи — пишите @namokonov, @renat_shagabutdinov

Пользовательские форматы чисел

Друзья, привет! С наступившим Новым годом!

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

https://shagabutdinov.ru/custom_format/

Таблица с примерами к статье с пользовательскими форматами чисел ⬆️

Видеоурок: спарклайны в Google Таблицах. Синтаксис и примеры

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

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

https://www.youtube.com/watch?v=ijkGAXCWaKU

Объединяем любое количество диапазонов Таблицы в одной функции.

Друзья, ДВССЫЛ / INDIRECT в массиве не работает, но очень часто вам нужно сослаться на ряд ячеек с диапазонами и собрать их в один массив. Например, у вас в Таблице двенадцать листов с расходами за разные месяца и вы хотите собрать все строки этих листов с помощью одной формулы, просто выделив диапазоны мышкой.

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

Чтобы функция работала в вашей Таблице - скопируйте код и вставьте его в редактор скриптов этой Таблицы.

Функция отдельно:
function indirects(ranges){
var ss = SpreadsheetApp
.getActiveSpreadsheet();

if(ranges.map){
return [].concat.apply([], ranges.map(function(i){return ss.getRange(i).getValues()}));}
else{return ss.getRange(ranges).getValues();}
}

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

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

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

Просто скопируйте Таблицу себе и укажите в диапазоне «A2:C7» ссылки на Таблицы, листы и номера столбцов, которые нужно скопировать.

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

Самое главное — вы можете поставить скрипт на регулярное выполнение и собирать данные каждую ночь, раз в два часа, etc. Для этого создайте триггер: нужно открыть редактор скриптов, меню изменить > триггеры текущего проекта, добавить триггер и поставить функцию get_google_sheets на нужную периодичность выполнения.

После этого скрипт сможет собирать тысяч строк из указанных Таблиц, даже если ваш компьютер в это время будет выключен. 🚀😈

>> Таблица со скриптом

Будут вопросы, пишите в наш чат: @google_spreadsheets_chat

Сапер и морской бой в Таблицах
Уже пошли игры

Друзья, вокруг нашего канала собирается сообщество людей, которые по-настоящему увлечены Таблицами и всем, что в них можно сделать. Поэтому сегодня мы выкладываем целых две игры — Сапёр и Морской Бой, их написал наш подписчик Лео. Он прошёл путь от «ночью заснуть не мог, думал-думал, но не придумал» до «ловите парни, я сделал».

Сапёр
Морской бой

Достаём просмотры и лайки статей прямо из веб-страниц. Волшебство =IMPORTXML.

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

В функции =IMPORTXML два аргумента: адрес веб-страницы и запрос Xpath для поиска данных. Чтобы понять, что писать в Xpath, чтобы он вернул то, что вам нужно — можете воспользоваться бесплатным дополнением для браузера ChroPath.

А на гифке — пример, подтягиваем просмотры нашей статьи про ключевые отличия Ms Excel от Google Таблиц.

Статья Михаила на habr
Таблица LynxReport: учёт публикаций

Друзья, вашему вниманию - еще один видеоурок для начинающих!

Рассматриваем функции для расчета сумм, средних и количества в Google Таблицах:
- по всему диапазону (СЧЁТ, СЧЁТЗ, СУММ, СРЗНАЧ)
- с одним условием на число, дату или текст (СЧЁТЕСЛИМН, СУММЕСЛИМН, СРЗНАЧЕСЛИМН)
- с двумя и более условиями (СУММЕСЛИМН, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИМН)

https://www.youtube.com/watch?v=sqBhegW-JYU

Отображаем функцией =IMAGE() картинки из Google Диска в ячейке

Друзья, привет! Поменялась ссылка, которую нужно добавлять к ID файла на Google Диске, чтобы вывести изображение в ячейку. Старый вариант не работает, публикуем работающий.

Ссылка теперь такая: "https://docs.google.com/uc?export=download&id="

Целиком формула для ячейки A1, с регулярным выражением, достающим из ссылки ID файла будет выглядеть так:
=IMAGE("https://docs.google.com/uc?export=download&id="& REGEXEXTRACT(A1;"[-\w]{25,}"))

Чтобы все работало и изображение должно быть расшарено для всех по ссылке.

Таблица, в которой все работает

P.S. Регулярное выражение [-\w]{25,} матчит ровно 25 символов, каждым из которых может быть либо буква-цифра-подчёрк (\w, word character), либо дефис.

Дата и время в Google Таблицах

Сегодня хороший день - 43 873.
Во всяком случае, так говорят Таблицы. Мы, люди, называем это 12 февраля 2020 года.

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

В нем:
- Обсуждаем, как отображаются дата и время в Google Таблицах
- Знакомимся с функциями для отображения текущих даты и времени:
Для расчета разницы между двумя датами в разных единицах измерения, включая рабочие дни
Для определения номера года, месяца, дня и недели по ячейке с датой
Для расчета того, какой день наступит по истечению X рабочих дней

https://www.youtube.com/watch?v=Lbj5kh0q5KQ

Скрипт для работы с Google Контактами
Сохраняем контакты из Таблицы

Друзья, привет! Сегодня — гостевой скрипт от нашего подписчика Дмитрия @Mityayka1. Скрипт синхронизирует Таблицу и Контакты, добавляя в Контакты только те контакты, которых там не было, проверяя по номеру телефона. Принцип работы скрипта - на гифке.

Дима снабдил скрипт очень подробными комментариями, поэтому, если вы хотите понять логику работы с классом Google Контактов, а также посмотреть на то, как в скрипте обходятся с IF / циклами / массивами - открывайте редактор скриптов и читайте.

Дима — спасибо за полезный материал 👍👌🔥

>> Таблица со скриптом по ссылке

Приходите в наш чат: https://t.me/google_spreadsheets_chat

Как работать с Таблицами из R

Друзья, привет. Алексей Селезнев записал небольшой урок о том, как работать с API Google Таблиц с помощью языка программирования R.

Из урока можно научиться:
1. Проходить авторизацию для работы с API.
2. Считывать данные с листов Google Таблиц.
3. Создавать Google Таблицы.
4. Создавать новые листы в Google Таблицах.
5. Записывать данные на листы Google Таблиц.
6. Дописывать данные в созданные ранее листы Google Таблиц.

https://youtu.be/no75MyT87l0

Канал Алексея про R, его библиотеки и маркетинг: @R4marketing