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

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, который сейчас не у всех доступен.

​​В сегодняшних ответах мы расскажем,
- как найти кнопку поиска в таблицах
- как искать значение в интервалах в ПРОСМОТР(LOOKUP), не описывая каждое возможное положение
- а еще про связанные выпадающие списки и про Google Формы

Недавно в Таблицах прошло большое обновление. Наш сегодняшний пост - про него.

В статье в Medium детально разберем:
- группировку строк и столбцов (Ура, она появилась!)
- группировку в сводных таблицах
- флажки и как их использовать в функциях
- и покажем гифку с созданием макроса

​​ВПР по нескольким условиям.

Друзья, сегодня рассказываем про ВПР с мультиусловиями. По сути - это привычная вам функция ВПР, в которой нужно принудительно объединить и условия и диапазоны для поиска условий. В статье Medium - подробно и со скриншотами.

P.S. в конце статьи покажем более простую альтернативу

​​Друзья, сегодня у нас подробная статья в Medium, а в ней:

1. Импортируем турнирную таблицу РФПЛ из веб-страницы (=IMPORTHTML)
2. При импорте "на лету" оставим только нужные строки и столбцы (=QUERY)
3. Опубликуем результат как веб-страницу

​​Новая функция AVERAGE.WEIGHTED - средневзвешенное значение

Мы не отследили наверняка, но, кажется, функция AVERAGE.WEIGHTED добавилась совсем недавно, в рамках апрельских обновлений (о самых существенных мы писали здесь)

Теперь можно рассчитать средневзвешенное значение без промежуточных вычислений.
Например, чтобы получить средневзвешенную цену, раньше нужно было сначала получить стоимость (перемножить массивы цен и объема продаж с помощью СУММПРОИЗВ/SUMPRODUCT) и разделить на количество товаров (обычная СУММ/SUM):
=СУММПРОИЗВ(B2:B13;C2:C13)/СУММ(B2:B13)

А теперь достаточно воспользоваться одной функцией:
=AVERAGE.WEIGHTED(C2:C13;B2:B13)

​​Друзья, сегодня у нас два вопроса - один нам пришлось уточнить, а в другом случае получилось решить несложную, но любопытную задачу по аккуратному сцеплению текста в одной ячейке с переходами на новые строки . В качестве орудия пользовались замечательной функцией СМЕЩ, формирующей ссылку на динамический диапазон. Наш вестник.

​​Друзья, привет. Привет, друзья.

Тема, навеянная нашим чатом. Как запретить вводить формулы в ячейки?

Отвечаем:
Выделяете ячейки > "проверка данных" > "ваша формула" > =not(isformula(верхняя-левая ячейка выбранного диапазона) и "запрещать ввод данных". Все, теперь формулу не ввести. Чтобы разрешить ввод только формул - убираем not.

Хозяйке на заметку: используя эти же формулы вы можете создать правило условного форматирование, которое выделит все формулы (или наоборот).

Друзья, сегодня делимся с вами еще одним роликом из онлайн-курса по Таблицам в SkillFactory.

"Условное форматирование и логические функции. Как выделять строки по одному или нескольким условиям"
https://www.youtube.com/watch?v=1O6SWDDsVZk