Готовим антисептик по рекомендации ВОЗ

Друзья, всемирная организация здравоохранения выпустила памятку о том, как изготовить антисептик в домашних условиях:
Guide RUS
Guide ENG

Наш подписчик Евгений Михайлов создал в Таблице небольшой калькулятор, который позволяет по этой памятке рассчитать, в какой пропорции нужно смешивать все ингредиенты. Для заполнения используются ячейки B10 или B21 на листе "formulation".

На листе "preparation" - этапы изготовления антисептика по шагам, с картинками.

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

Таблица с калькулятором | Наш чат

Дано: есть список контактов с адресами электропочты в Google Таблицах (ваши партнеры, клиенты, сотрудники и т.д.) и каким-либо параметром (например, регионом).

Задача: одним щелчком формировать в почтовом клиенте письмо на выборку адресатов из этой таблицы. Например, всем клиентам из города N. Или всем сотрудникам отдела M.

Решение
1 С помощью FILTER отбираем только нужные нам адреса по условию - например, по одному региону, как в нашем примере.
FILTER(столбец с адресами; столбец с условиями=ячейка с выпадающим списком с выбором условия)

2 С помощью JOIN собираем все отобранные адреса в одну текстовую строку - через запятую.

JOIN(",";FILTER(...))


3 С помощью ГИПЕРССЫЛКА/HYPERLINK формируем ссылку на отправку письма. Чтобы все адреса были в скрытой копии и адресаты не видели ящиков друг друга, используем ссылку вида "mailto:?bcc=". Туда подставляем полученные на предыдущих шагах адреса. Второй аргумент ГИПЕРССЫЛКА - то, как ссылка будет отображаться в ячейке. Можно ввести любой текст, который вам хочется там видеть. Например, "Рассылка".

=ГИПЕРССЫЛКА("mailto:?bcc="&JOIN(",";FILTER(...));"Рассылка")


4 Можно пойти еще дальше и добавить тему письма - например, брать тот самый параметр туда или указать фиксированный текст. Для этого справа к полученной ссылке добавляем &"&subject=" и после знака "равно" вводим тему или ссылаемся на ячейку, откуда ее нужно брать (в примере берем регион в тему)
и добавить текст письма - для этого после темы добавим также &"&body=Добрый день!", чтобы в формирующемся письме сразу появлялся текст "Добрый день!".
Здесь мы видим по два амперсанда & - первый нужен, чтобы соединить текстовые фрагменты ссылки, это оператор внутри функции, а второй используется непосредственно в получающемся тексте, чтобы отделять параметры bcc, subject и body.

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

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

Про функцию FLATTEN
(объединяем диапазоны в один список)

Друзья, активный участник нашего чата @akanat прислал пост про функцию Таблиц FLATTEN. Функции официально нет, в документации она не описана, но тем не менее она работает.

Канат в своем посте показывает, как сделать объединение диапазонов с помощью классических формул Таблиц, а потом - как всё решить с помощью тайной функции FLATTEN.

Передаем слово Канату: https://teletype.in/@google_sheets/u2Q0MOZ1T

(А за наводку на функцию спасибо Саше Иванову)

Наш лучший скрипт для сбора данных V2.0

Привет, сейчас идёт карантин. Что можно сделать дома? Пить винишко. А еще можно оптимизировать свои Таблицы.

Наша команда подготовила для вас Таблицу со скриптом загрузки данных через sheets api. Это Таблица - апгрейд Таблицы, которую мы сделали для вас в январе.

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

Как применить скрипт? Представьте - у вас сорок менеджеров по продажам. Все менеджеры ведут свои отдельные Таблицы в одном формате. Используя наш скрипт вы сможете настроить регулярную выгрузку всех сорока Таблиц в одну.

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

Если будете копировать код к себе, то зайдите в редактор скриптов > ресурсы > доп. функции Google и включите Google Sheets Api

Оглавление нашего канала

Друзья, у нашего канала есть оглавление. Разумеется - в Google Таблице. Новые посты попадают наверх. Реализовано всё очень просто — есть другая Таблица с планом публикаций, мы переносим строки из неё с помощью функций IMPORTRANGE. Потом добавляем QUERY и функцией фильтруем от пустых строк и сортируем по дате поста (см скриншот).

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

Есть вопрос про Таблицы — начните с оглавления нашего канала 🤗

>> Оглавление

>> Предложить свою тему

Таблица / приложение для голосования от @google_sheets

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

Мы создали для вас специальную Таблицу для голосования. Внутри три листа — кандидаты, лог, результат.

За голосование отвечает скрипт, чтобы он заработал нужно выбрать одного или нескольких кандидатов и запустить скрипт нажатием на "vote". Скрипт проверит, голосовали ли вы раньше (сверив ваш адрес электропочты с адресами проголосовавших на листе лог) и если не голосовали — запишет ваш голос на лист.

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

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

Наш чат 👨‍💻

Функции баз данных

Функции БД - мощный инструмент. Они есть и в Excel и в Google Таблицах и хороши для работы с несколькими условиями, с наборами условий.

Подготовили для вас статью про эти функции и про то, как ими пользоваться.

Наглядно и с примерами: https://teletype.in/@renat_shagabutdinov/4lVaI_Pj7

Считаем сумму по каждой строке / столбцу в формуле массиве

Среди наших читателей есть любители всё сделать с помощью одной формулы. Сегодняшний пост — как раз для них.

Посчитаем сумму по каждому столбцу тремя способами — c помощью DSUM, SUMIF и QUERY.

Самый короткий способ. Функция баз данных DSUM / БДСУММ:
=ARRAYFORMULA(DSUM(A1:F6;{2\3\4\5\6};G2:G3))

- первый аргумент, "данные": указываем всю таблицу;
- второй аргумент, "столбец суммирования": задаём руками массив {2\3\4\5\6} либо вставляем формулу SEQUENCE(1;5;2) или SEQUENCE(1;COUNTA(B1:F1);2);
- третий аргумент, "критерии": условий фильтрации у нас не будет, поэтому мы можем указать либо всю исходную Таблицу еще раз, либо две пустые ячейки;

Добавляем ARRAYFORMULA и превращаем нашу конструкцию в формулу массива.

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

Вариант с помощью SCAN и LAMBDA (добавлено в 2022): t.me/google_sheets/1006

C помощью SUMIF

=ARRAYFORMULA(SUMIF(if(COLUMN(B2:F2);ROW(B2:F6));ROW(B2:B6);B2:F6))

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

И решение с помощью QUERY

=INDEX(QUERY({B2:F6};"select sum(Col"&join("), sum(Col";SEQUENCE(1;5))&")";0);2)

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

Программисты, проектировщики, видеомейкеры… все, чья работа требует долго высматривать мелкие детали на мониторе, любят темные интерфейсы. Не знаю почему для электронных таблиц все еще нет официальной темной темы, но мне иногда помогает расширение для браузера Care Your Eyes. Работает почти на всех сайтах, не только в Google Docs, но на недорогих мониторах качество картинки может получиться не очень. Единственное неудобство в том, что в темной теме меняются не только цвета интерфейса, но и форматирования (заливки, шрифта, границ), но временно разгрузить глаза часто помогает, особенно при работе на большом мониторе, с которого просторы гугл щитс безжалостно светят белым.

Ссылка на аддон

Текст прислал наш подписчик Ринат (https://www.facebook.com/planer484)

Оглавление |

ЗАКАЗАТЬ РАБОТУ |

Поддержать нас |

Наш курс на Skillbox |

Обучение, Ренат: @r_shagabutdinov

>>Оглавление нашего канала

Сравнение списков в Таблицах
Способ 1 — функция QUERY и JOIN

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

На каждом скриншоте по шагам будет разобран один случай — вывод имён из списка А, если они есть в списке Б.

Помимо этого в Таблице с примерами для каждого способа сравнения есть еще три формулы (ячейки H3:E3):
* для вывода имён, которые есть в списке А и которых нет в Б
* есть в Б и нет А
* для вывода пересекающихся имён из обоих списков

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

Формула, которая разбирается на скриншоте:
=QUERY({B3:B10};"where Col1='" & JOIN("' or Col1 = '";C3:C8)&"'";0)

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

Сравнение списков в Таблицах
Способ 2 — функции FILTER / REGEXMATCH

Формула, которая разбирается на скриншоте:
=FILTER(B3:B10;REGEXMATCH(B3:B10; join("|";C3:C8)))

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

Вдогонку, про функции с регулярными выражениями

Как говорят у нас в деревне, два поинта:

1) Точное совпадение. Регулярное выражение Вася|Петя найдет строку "Вася", строку "Петя", а еще строку "Вася и его частушки под гармонь", так как поиск будет производиться по подстроке.

Для того, чтобы искать только точное совпадение — добавляем перед каждым элементом ^ и $ после.

Например, соберём из С1:D1 регулярное выражение:
="^"&JOIN("$|^";C1:D1)&"$"

// ^Вася$|^Петя$

2) Числа в диапазоне данных. В функции REGEXETRACT / REGEXMATCH / REGEXREPLACE вы можете передавать только текстовую строки. Хотите поработать с числами — предварительно приведите их к тексту, для этого возьмите диапазон в =TEXT(диапазон/ячейка;"@")

Формула со скриншота
=FILTER(A1:A5; REGEXMATCH(TEXT(A1:A5;"@") ; "^"&JOIN("$|^";C1:E1)&"$"))

Таблица

^ $ не обязательно добавлять перед каждым элементом — достаточно добавить по границам перечислений:
^(Вася|Петя)$

@vitalich, спасибо!

Сравнение списков в Таблицах
Способ 3 — сравниваем функциями FILTER / MATCH

Формула, которая разбирается на скриншоте:
=FILTER(B3:B10;ISNA(MATCH(B3:B10;C3:C8;0)))
и
=FILTER(B3:B10;NOT(ISNA(MATCH(B3:B10;C3:C8;0))))

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

​​Спарклайн с условием

Благодаря тому, что спарклайны в Google Таблицах реализованы в виде одноименной функции - к ним легко добавить условия с помощью другой функции, например ЕСЛИ / IF или IFS.

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

1. Формируем горизонтальный спарклайн, тип и максимальное значение указываем в массиве прямо в функции (charttype = bar, max = функция МАКС по всему диапазону данных)

2. В качестве цвета (параметр color1) указываем функцию ЕСЛИ. Условие — то, что конкретное значение в строке с формулой больше среднего. Если это так, выбираем зеленый, иначе — красный.

Напомним, что цвета в спарклайнах можно указывать и в виде hex-кодов: например, dc143c для малинового.

3. Добавляем функцию ЕСЛИОШИБКА / IFERROR, чтобы в строках без данных не было ошибки.

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

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

Геотаргетинг, СДЭК, находим объекты на карте в Таблице Google

Сегодня публикуем статью Романа Игнатова, по совместительсту — модератора 🎖 нашего чата.

Рома написал скрипт, по выбранному адресу отображающий в Таблице виджет Яндекс Карт с наложенными точками пунктов выдачи заказов СДЭК. При выборе пункта его название сохраняется в Таблицу.

Этот скрипт — отличный пример работы с внешними API. Еще эта реализация показывает, что в html-окно, которое выводят Таблицы можно дейстительно поместить что угодно. Виджет карт — не предел.

Статья со всеми ссылками

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