Считаем рабочие дни между двумя датами в 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

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

Обычный фильтр, кнопка "Фильтр" на панели = фильтрация, которая отображается у всех пользователей

Filter views (фильтры), кнопка "Фильтры" на панели = фильтрация, которая отображается только у того, кто создал фильтр или вошел в него (по ссылке или через меню "Фильтры")

Временные фильтры, кнопка "Фильтры" на панели = фильтрация, которая отображается только у того, кто ее создал. Такие фильтры удаляются при закрытии вкладки и не видны другим пользователям. Временные фильтры доступны пользователям с доступом на комментирование/просмотр.

— помните, что обычный фильтр и результаты фильтрации видны абсолютно всем пользователям с любым доступом. Если вы отфильтровали данные с помощью него, другие люди не увидят скрытые строки, пока не снимут фильтр (снять его они смогут, только имея доступ на редактирование);

— есть фильтры (filter views) которые видны только тому пользователю, который в этот фильтр зашел. Их можно сохранять под названиями, ссылки на них можно передавать другим (это очень удобно, когда нужно вместе посмотреть на какой-то срез данных) - просто копируйте ссылку с окончанием вида fvid из адресной строки.

— доступ ко всем созданным в файле filter views есть у любого пользователя, даже с доступом на просмотр (но менять и удалять их он не сможет)

— пользователь с доступом на просмотр/комментирование может создавать только временные фильтры или просматривать чужие фильтры. Временные фильтры не сохраняются и не видны другим пользователям.

— именно filter views - оптимальное решение для совместной работы. Обычный фильтр же лучше вообще не использовать, если пользователей больше одного. При этом не забывайте, что фильтры (filter views) сохраняются, по умолчанию - под названиями вида ФильтрN, где N - порядковый номер. Хорошее правило - удалять все фильтры без названий периодически, чтобы не засорять файл. Если у фильтра нет названия, значит, его создали "на один раз". Правильная же ситуация - когда у каждого отдела/сотрудника/ситуации есть свой фильтр, например "Иванов", "Маркетинг" или "Проекты в работе с приоритетом 2".

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

Ссылка на видеоурок про фильтры разных типов:
https://www.youtube.com/watch?v=kHN5sIFLIjw

​​В правиле условного форматирования можно использовать данные другого листа.

Друзья, привет! Речь о правиле условного форматирование формулой. Чтобы сослаться на другой лист - просто напишите диапазон и имя листа в кавычках и оберните в функцию INDIRECT (ДВССЫЛ). Такое правило будет работать.

Например: выделим города-миллионники.
Гифка: http://recordit.co/LBQufQSzIP

Формула из примера:
=COUNTIF(INDIRECT("'Города-миллионики'!A:A");A2)

=СЧЁТЕСЛИ(ДВССЫЛ("'Города-миллионики'!A:A");A2)

Таблица

Отправляем письма скриптом. Опять.

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

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

Рассказываем про решение

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

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

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

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

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

Все эти доступы можно давать конкретным пользователям или по ссылке.
Доступ по ссылке означает, что любой человек, имеющий эту ссылку, сможет документ просматривать/комментировать/редактировать.
Если у вас G Suite (корпоративный аккаунт), вы сможете открывать доступ по ссылке только пользователям из вашего домена (вашим коллегам, имеющим корпоративную почту Gmail).

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

О том, как менять и как передавать доступ со скриншотами.

​​Наш читатель Коля из Тернополя спрашивает: "Ребят, есть список картинок, с названиями 1.jpg, 2.jpg и т.д. (шаг - единица). Как мне узнать, картинок с какими номерами нет, если известно, что всего их, например, 10?"

Коля, есть способ:
1) Формируем список от 1 до 10 с помощью функции ROW() / СТРОКА()
2) Добавляем к каждому элементу списка - ".JPG"
3) Помещаем полученный список из всех возможных картинок в функцию FILTER в диапазон вывода и в диапазон условия, критерием условия станет список картинок, которые у нас есть. Формула выведет только то, что есть в первом списке и отсутствует во втором.

Итоговая формула: =FILTER(ROW(A1:A10)&".jpg";ISNA(MATCH(ROW(A1:A10)&".jpg";A3:A6;0)))

Если писать функции на русском языке:
=FILTER(СТРОКА(A1:A10)&".jpg";ЕНД(ПОИСКПОЗ(СТРОКА(A1:A10)&".jpg";A3:A6;0)))

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