​​Друзья, сегодня рекомендуем вам мощный дружественный канал, на котором вы найдете
—полезные материалы по аналитике (как работает Google Analytics на самом деле?)
—ссылки на хорошие курсы по аналитическим инструментам и программированию

Школа бородатого веб-аналитика: @schoolWA

​​Звездочки и тильды ~ в СУММЕСЛИ(МН), СРЗНАЧЕСЛИ(МН)

Друзья, мы обратили внимание, что функции СУММЕСЛИ(МН), СРЗНАЧЕСЛИ(МН) не работают, если в текстовых значениях есть звездочки * и тильды ~
Вот такая незадача. Если что-то одно из этих символов - нет проблем. Если оба символа сразу - то такие строки не будут попадать в расчет.

Как быть? Можно обойти эту проблему многими вариантами, вот пара самых простых:

1. С помощью функции FILTER. По сути, вместо точки с запятой между диапазоном и условием ставите знак "равно" =, и добавляете снаружи функцию СУММ/SUM:

 =СУММ(FILTER(C:C;A:A=E2))

2. С помощью QUERY. Можно сразу рассчитать сумму по всем значениям с помощью кляузы GROUP BY.

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

 =СУММЕСЛИ(ARRAYFORMULA(ПОДСТАВИТЬ(A:A;"~";""));ПОДСТАВИТЬ(E2;"~";"");C:C)

Вот этот фрагмент будет возвращать исходный столбец А, но без знаков "тильда":

 ARRAYFORMULA(ПОДСТАВИТЬ(A:A;"~";""))

Во втором аргументе то же самое будем делать с критерием (искомым названием товара) из E2.
И далее все это обрабатывать СУММЕСЛИ.

P.S. Увы, все эти варианты работают медленнее, чем простая СУММЕСЛИ.

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

Telegram bot + Google Sheets (используя вебхуки)

Друзья, привет! Сегодня создаем Telegram бота, связанного с Google Таблицей.

Наш бот по команде от пользователя отправит ему в Telegram последний пост из оглавления нашего канала.

После того, как вы поймете, как работает код—вы сможете адаптировать его под свои нужды. Например, пишете боту в Telegram и он отправляет письмо с данными из Таблицы работнику, или начинает собирать несколько Таблиц в одну и конвертирует её в PDF, в общем - всё, что вы сможете придумать :)

План действий:
1. Создадим бота в Telegram и получим токен авторизации.
2. Напишем Google Apps Script, управляющий ботом. Вставим в него токен.
3. Развернем скрипт как веб-приложение.
4. Зарегистрируем вебхук.
5. Всё, будем пользоваться 😎

https://medium.com/@grclubpoker/bot-happens-telegram-bot-google-sheets-on-webhooks-e415509a6213

​​Создаем диаграмму Ганта с помощью формул (визуализируем график работ)

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

В каждой ячейке отчёта использована функция QUERY.

Детализация до месяца:

=if(isna(QUERY($A$1:$C;"select * where
(A='"&$E2&"' and month(B)+1<="&month(F$1)&" and month(C)+1>="&month(F$1)&")";0));0;1)

Детализация до дня:

=if(isna(QUERY($A$1:$C;"select * where
(A='"&$E2&"' and B <= date '"&text(F$1;"yyyy-mm-dd")&"' and C >= date '"&text(F$1;"yyyy-mm-dd")&"')";0));0;1)

В формуле проверяются два условия:
1. проект начинается или заканчивается в выбранную дату гистограммы
2. проект продолжается в выбранную дату гистограммы

Если любое из условий выполняется — формула возвращает 1 для этого проекта и месяца.

Условное форматирование:
Выводить 1 и 0 не обязательно, можно создать на основе нашей формулы правило условного форматирования. Для этого добавьте после формулы =1 (или измените 0;1 на false;true) Правило закрасит все ячейки, для которых формула будет истиной.

Таблица с решением

​​Считаем рабочие дни между двумя датами в 2018 правильно. Учтём все выходные, праздники и переносы.

Друзья, привет. Функция ЧИСТРАБДНИ.МЕЖД / NETWORKDAYS.INTL считает рабочие дни между двумя датами.

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

Cтуктура выходных задается строкой, начиная с понедельника. 0 - рабочий день, 1 - выходной. Строка "0000000" будет означать, что все дни на каждой неделе - рабочие. Такая структура нам и нужна, нет, мы не против выходных, совсем наоборот - мы хотим их учесть правильно.

В Таблице с примером в столбце E:E мы выписали все праздники и выходные дни (сб и вс), с учетом всех переносов по производственному календарю РФ за 2018 (получилось каких-то 118 дней).

Этот массив мы добавили в диапазон дней-исключений (последний аргумент ЧИСТРАБДНИ.МЕЖД) и теперь можем легко посчитать правильное количество рабочих дней между любыми датами в 2018.

 =NETWORKDAYS.INTL("01-01-18";"31-01-18";"0000000";$E$2:$E) 
//17

 =NETWORKDAYS.INTL("01-01-18";"31-12-18";"0000000";$E$2:$E) 
//247

Без дней-исключений и без выходных:

 =NETWORKDAYS.INTL("01-01-18";"31-01-18";"0000000")

//ожидаемые 31

Считаем рабочие дни между двумя датами в 2018 правильно. Учтём все выходные, праздники и переносы.

Видеурок. WHERE в функции QUERY.

Друзья, сегодня вашему вниманию видеоурок из курса по Таблицам в Skillfactory.
Он посвящен кляузе WHERE в функции QUERY.

Обсуждаем ее синтаксис и рассматриваем примеры:
- запрос с условием на число
- запрос с условием на текстовые значения, в том числе на несколько
- запрос, условие для которого берется из выпадающего списка (что позволяет строить простые интерактивные отчеты)

P.S. Увы, в нашей копии этого видео не очень громкий звук - и только в моно-режиме на одно ухо. Надеемся, это не помешает восприятию материала :)

Видео | Google Док (описание + скриншоты)

​​Выводим ближайшие к заданному числу (дате) значения из диапазона.

У нас есть массив элементов (смотрите скриншот), отберём из него три ближайших значения к числу три.

Поехали!
1. Вычитаем из каждого элемента массива наше число:

 =ArrayFormula({A3:A11-3})

2. Берем модуль результата и добавим к нему столбец исходного массива:

 =ArrayFormula({A3:A11\abs(A3:A11-3)})

3. Добавляем результат в формулу QUERY, сортируем по столбцу с модулем Col2, оставляем верхние три элемента limit 3 и выводим только столбец исходного массива Col1.

Итоговая формула:

 =query(ArrayFormula({A3:A11\abs(A3:A11-D1)});
 "Select Col1 order by Col2 limit 3")

Бинго, у нас все получилось правильно 🤘

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

​​Спарклайны: некоторые примеры применения

Друзья, сегодня публикуем Google Документ с примерами использования спарклайнов (функция, которая строит график в ячейке).

— Гистограмма
— Выше определенного уровня или нет?
— Позиционное сравнение
— Выигрыш/проигрыш: визуализация попыток спортсменов

​​Расписание групповых занятий в Google Таблицах.
Выделяем тренеров и сортируем по времени.

Друзья, доброе утро. Недавно для нашего клиента — крупной московской школы плавания мы создали расписание тренеров (или групповых занятий).

К расписанию было всего два требования :  простота в чтении и минимизация ошибок при заполнении.

Мы публикуем для вас сокращенный вариант расписания (в оригинале тренеров и локаций было больше).

Красочно выделяем тренеров
За простоту чтения у нас отвечает возможность выбрать (и тем самым выделить) тренера чекбоксом ✅ напротив его фамилии. Это—простое правило условного форматирования (формулой), его нужно создать для каждого тренера. Для Иванова оно будет =and($A$4=A3;$B$4=true), диапазон правила при это должен начинаться с А3, иначе все уедет и выделится другая ячейка.


Фильтр: достаем из расписания выбранную дату и сортируем по времени
Такую возможность мы сделали на отдельном листе. Сначала, с помощью =FILTER мы достаем день, выбранный в выпадающем списке:
=FILTER('Расписание'!F3:AJ; 'Расписание'!F1:AJ1=B1)

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

Итоговая формула:
=query({'Расписание'!D3:E\filter('Расписание'!F3:AJ; 'Расписание'!F1:AJ1=B1)};"SELECT Col2, Col1, Col3 WHERE Col3 is not null order by Col2";0)

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

​​Делим неправильную дату на элементы и собираем из них правильную

Привет, друзья! Частенько нам нужно разобрать дату, чтобы сделать из неё другую дату. Например, из даты mm-dd-yyyy получить dd-mm-yyyy.

Сегодня показываем, как это сделать с помощью функций: =SPLIT, =INDEX и =DATE.

Поехали:
1. Делим дату по разделителю на массив элементов:
=SPLIT(A2;"-")

2. Поместим полученный массив внутри =INDEX и выбирая нужный элемент массива (1, 2, 3) cформируем новую дату функцией =DATE (в аргументах функции нужно отдельно указать год, месяц и день)
=DATE(
index(SPLIT(A2;"-");3);
index(SPLIT(A2;"-");1);
index(SPLIT(A2;"-");2))

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

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

P.S. И прямо сейчас, в далёком Берлине, один из авторов нашего канала (Ренат) подбирается к финишу марафона. Давайте коллективно пожелаем ему бодрости духа на всех стадиях 💪🏃

​​Делим неправильную дату на элементы и собираем из них правильную

​​КВЕРИМ ПОНЕДЕЛЬНИКИ (достанем из диапазона все, кроме первого)

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

Всё уместилось в одну формулу:

1. Оставим только уникальные значения:
=UNIQUE(A:A)

2. Полученный массив используем в QUERY.
Так как массив модифицирован  — к нему нужно обращаться не по названию столбца, а по его номеру :  Col1, Col2 и т.д.

— отбираем: WHERE dayOfWeek(Col1)=2, отбираем по дню недели. В QUERY неделя начинается с воскресенья, то есть понедельник  —  это второй день недели.

— сортируем результат по убыванию: order by Col1

— убираем первое значение (первый понедельник): offset 1

Вся формула целиком: =QUERY(UNIQUE(A:A);"SELECT Col1 where dayOfWeek(Col1)=2 order by Col1 offset 1")

​​Арифметические операции в запросе функции QUERY

Если вам нужно “на лету” произвести арифметические операции с выборкой, которую выдает функция QUERY, смело делайте это прямо в запросе, не нужно создавать отдельных столбцов.

Умножаем на 0,13 каждое значение столбца A (дробным разделителем должна быть точка):
=QUERY(A1:A5;"select A*0.13")

Прибавляем к каждому значению столбца A число, взятое из ячейки E8, а также выводим столбец B:
=QUERY($A$1:$B$5;"select A+"&E8&" ,B")

Если вы хотите переименовать (LABEL) или отсортировать (ORDER BY) по измененному столбцу, то указывайте его также, как он записан в SELECT (в этом примере мы умножаем на два среднее по столбцу D):
=QUERY(Данные!A1:F;”SELECT A,C, avg(D) * 2 GROUP BY C ORDER BY avg(D) * 2”)

Конечно, можно в одном запросе указать и просто среднее, и среднее с множителем. Главное - чтобы столбец, по которому идет сортировка, уже был в выборке, был указан в SELECT.

​​​​Выбираем город — видим его изображение, которое выберет функция RANDBETWEEN

Друзья, привет. Недавно я делал дашборд с выпадающим списком, в котором можно было выбрать город. Всё просто  — выбираем город и получаем по нему данные.

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

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

Волшебная формула:
=IMAGE(INDEX(FILTER(data!B2:D20;data!A2:A20=A2);RANDBETWEEN(1; 3)))

Если писать функции по-русски (IMAGE и FILTER не переводятся):
=IMAGE(ИНДЕКС(FILTER(data!B2:D20;data!A2:A20=A2);СЛУЧМЕЖДУ(1; 3)))

Разбор — на скриншоте (или в Таблице с примером)

P.S. Что тебе снится, крейсер Аврора?

​​Росстат. Население городов России за 2013-2018 в Google Таблице.

Друзья, привет! По ссылке - таблица со сводным населением городов РФ (около 1100 городов) за последние 6 лет. Мы недавно её собрали для одной задачи и выкладываем для вас.

На листе "РОССТАТ" - отформатированные данные из XLS отчетов службы по годам. Оставлены только город, население, год отчета.

На листе «Свод» - красочная таблица, построенная с помощью функции QUERY из этих данных: по городам, годам и с процентами изменения от года к году. В ячейках B2:D2 - выпадающие списки, которые управляют сортировкой.

P.S. Если у кого-то есть идеи или предложения где взять более точное население, чем предоставляет Росстат - напишите, пожалуйста @namokonov.

​​QUERY c QUERY в QUERY

Привет, друзья. Сегодня - лайфхак с функцией QUERY.

У нас есть таблица из шести столбцов (на скриншоте диапазон A1:F7).

А нужно нам, допустим, объединить столбцы 1-2-3 и 1-5-6 в один массив, а потом отсортировать результат по столбцу 1.

Поехали:

1) Отбираем нужные столбцы в двух отдельных QUERY:
=QUERY(A2:F7;"select A, B, C") и =QUERY(A2:F7;"select A, E, F")

2) Объединяем результат двух функций в массив с помощью { и }:
={QUERY(A2:F7;"select A, B, C");QUERY(A2:F7;"select A, E, F")}

3) Полученный массив помещаем в еще одну функцию QUERY как диапазон данных и сортируем по первому столбцу:
=QUERY({QUERY(A2:F7;"select A, B, C");QUERY(A2:F7;"select A, E, F")};"select * order by Col1")

"Найти и заменить" в Таблицах: удаляем ненужные данные, заменяем слова и символы в ячейках и формулах.

Не только же про QUERY внутри QUERY нам писать - сегодня предлагаем небольшую инструкцию по простому инструменту "Найти и заменить".
Он полезен не только для поиска каких-то текстовых значений на листе. С помощью него можно удалить или заменить определенные символы или слова (хотя бы пробел, которым заменяется разделитель разрядов при выгрузке данных из других систем; или же ссылку на файл-источник во всех ваших функциях IMPORTRANGE).

​​Парсим погодное API в Таблице. Пользовательская функция.

Друзья, привет! Недавно мы решили научиться брать данные из API, предоставляющего прогноз погоды: https://openweathermap.org/api.

Мы написали пользовательскую функцию на Google Apps Script, она парсит JSON (вот такой), достает из него нужные нам поля и выводит результат на лист Таблицы.

Пользовательская функция с описанием - в посте.

QUERY в массиве.
Найдем максимальное значение по каждой строке.

Друзья, привет! Сегодня будем расширять границы вашего понимания функции QUERY.

Задача звучит так - есть ряд строк с разным количеством значений в каждой. Нужно - найти максимум по каждой строке (или минимум или среднее или сумму) и сделать это с помощью одной формулы.

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

Подробно и по шагам решение на скриншоте →

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

Итоговая формула:
=QUERY(TRANSPOSE(QUERY(TRANSPOSE(A2:C5);"select "&
ARRAYFORMULA(JOIN(",";"max(Col"&row(A2:A5)-1&")"))));"select Col2";0)

P.S. Неоценимую помощь в поиске решения оказал @Volond (он же @SigmaSpola). Спасибо ему🤘

UPDATE из будущего: Три другие примера решения этой задачи t.me/google_sheets/504