Чат для обмена опытом и взаимопомощи по Google Таблицам

Нам уже не раз приходила в голову эта идея, и читатели тоже об этом просили - и мы решили попробовать.
Сделали чат, в котором можно будет:
— попросить помощи и задать вопрос;
— помочь другим или поделиться своим опытом;
— подбросить идею для новых публикаций;

Присоединиться: https://tttttt.me/joinchat/ExSZIBK_acOSX8GURNbqFQ

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

Чтобы извлечь из столбца с датами (А) номер месяца и год - используйте month(A), year(A) в Select. Для группировки - добавьте эти поля в group by.

Все целиком (группировка по месяцу, году и одной категории) будет выглядеть так:

=QUERY(A2:C6;"select year(A), month(A) +1, B, sum(C) group by year(A), month(A), B")

p.s. в SELECT прибавляем к месяцу 1, QUERY считает месяца с 0

добавив к тексту запроса label - мы можем переименовать столбцы

=QUERY(A2:C6;"select year(A), month(A) + 1, B, sum(C) group by year(A), month(A), B label year(A) 'год', month(A) + 1 'месяц', B 'фрукт', sum(C) 'продажи'")

Всем привет.
Сегодня вашему вниманию большая статья про форматы чисел и дат в Google Таблицах. Числовые, дата и время, пользовательские, валюта и финансы - чем отличаются и как выглядят.
http://telegra.ph/Formaty-chisel-i-dat-v-Google-Tablicah-03-26

Хорошей недели!

​​Привет, сегодняшний пост в телеграфе:

1) задаем массив данных формулой,
2) используем его в ВПР,
3) в ВПР с интервальным поиском = 1,
4) вопрос из нашего чата: формула с ВПР и инт. поиском + формула массива + ЕСЛИОШИБКА(IFERROR)

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

1) Вопрос из нашего чата. Группировка по двум осям в QUERY и скрипт для этого же от автора вопроса.

2) Формат "продолжительность"

3) Sumif и Subtotal?

p.s. библиотека AlaSQL для GAS, спасибо Александру Иванову.

Дамы и господа, вашему вниманию еще одно видео из нового курса по Таблицам.

Про функцию ПОВТОР/REPT. Она повторяет любой символ заданное число раз.
И это позволяет с помощью нее создавать нечто вроде гистограмм. А уж какие символы для этого использовать - решать вам. В примере будем количеством знаков доллара иллюстрировать продажи товара.

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

​​Привет, коллеги.

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

Во втором - расмотрим, как сгруппировать таблицу с помощью QUERY и с помощью UNIQUE/SUMIF.

​​Как посчитать:
- Количество ячеек, начинающихся не на букву "Т"?
- Сумму всех товарных позиций из 7 и более символов?
- Количество записей не из 4 символов?

Подготовили для вас шпаргалку по символьным шаблонам.

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

Вариант 1
function sheets1() {
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var i = sheets.length
var n = []
while (i--){return n.push(sheets[i].getName());}
return n
}

Вариант 2, в одну строку:
function sheets2 (){
return SpreadsheetApp.getActiveSpreadsheet().getSheets().map(function(sheet){return sheet.getName()})
}

Как это работает? Вам нужно:
1) открыть Инструменты > Редактор скриптов
2) вставить код функции, сохранить
3) ввести название функции в любую ячейку

Сегодня у нас правило условного форматирования, которое выделит все выходные дни (сб, вс). Четвертая строка наших данных - это настоящие даты, с месяцем и годом, просто отображаются только дни.

Создаем правило:
1) выделяем диапазон ячеек для применения правила (у нас b4:af15)
2) выбираем "новое правило", "задать формулой" и вводим: =WEEKDAY(B$4,2)>5
-WEEKDAY с аргументом 2 возвращает день недели, где ПН - 1
-закрепление строки (B$4) нужно для того, чтобы выделить весь столбец диапазона данных, если 4-ая строка отвечает условию.
3) чтобы исключить выделение пустых столбцов, добавим проверку на даты и объединим два условия: =AND(WEEKDAY(B$4,2)>5,ISDATE(B$4))

-
Названия функций на русском (вводить, кстати можно на любом языке):
=WEEKDAY (=ДЕНЬНЕД)
=AND (=И)
=ISDATE (аналога нет)

​​Друзья, сегодня для вас материал о формулах массива с парочкой примеров.

В частности, создаем ВПР с автоматическим поиском номеров всех необходимых столбцов.

​​Друзья, доброе утро. Сегодня у нас отличный пост про GAS скрипты от Александра Иванова.

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

​​Друзья, доброе утро! Сегодня рассматриваем функцию QUERY вложенную в QUERY.

Разберем таблицу с кол-вом часов, которые наши сотрудники потратили на проекты и стоимостью часа каждого сотрудника.

С помощью первого QUERY мы умножим стоимость часа сотрудника на кол-во часов (по каждой строке). А с помощью второго полученный массив сгруппируем по проекту и сотруднику. Все действия уместим в одну формулу.

Статья со скриншотами в Medium.

P.S. В ближайшее время мы перенесем в Medium все наши статьи из Telegraph, который сейчас не у всех доступен.