Программисты, проектировщики, видеомейкеры… все, чья работа требует долго высматривать мелкие детали на мониторе, любят темные интерфейсы. Не знаю почему для электронных таблиц все еще нет официальной темной темы, но мне иногда помогает расширение для браузера 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-окно, которое выводят Таблицы можно дейстительно поместить что угодно. Виджет карт — не предел.

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

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

Спарклайны. Меняем цвет у отрицательных, максимальных и минимальных, первых и последних значений.

Продолжим рассматривать примеры спарклайнов и сегодня посмотрим на несколько параметров:
— ymin и ymax - позволяют провести виртуальную ось, отсечь часть значений (например, все отрицательные) на спарклайне-графике типа line;
— negcolor позволяет задавать цвет всех отрицательных значений в столбчатом спарклайне типа column
— highcolor и lowcolor - выбрать цвета для точек с макс и мин значениями;
— firstcolor и lastcolor - задать цвет для крайних точек

Напомним, что цвета можно задавать не только с помощью их обозначений на английском (red = красный), но и с помощью hex-кодов, что обеспечивает доступ к большему разнообразию.
Вот вам код для цвета "Королевский пурпурный Крайола": 7851a9

А вообще любой код можно узнать, вбив название цвета в Яндексе.

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

Другие посты про спарклайны:
Спарклайн с условием
Видеоурок: Синтаксис и примеры
Что такое спарклайны?

Тест на знание Google Таблиц

Друзья, мы подготовили для вас тест из 31 одного вопроса.

Вот тест: t.me/QuizBot?start=Qbs6aoqy

Наш чат про Таблицы и скрипты, если у вас будут вопросы или вы просто захотите поделится результатом 🎰

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

Друзья, привет. Астрологи провозгласили месяц спарклайнов – продолжим развлекаться с маленькими графиками.

Сегодня рассмотрим такой пример: данные по выручке за несколько лет по месяцам.

Создадим формулу, которая будет строить спарклайн по выбранному количеству месяцев и начиная с выбранного месяца.
Для этого нужна будет функция СМЕЩ, формирующая ссылку на динамический диапазон. Зададим точку отсчета - первый месяц с данными; высотой диапазона будет выбранная пользователем в ячейке длительность периода, точкой отсчета (отступом по строкам) – позиция выбранного месяца.

=СМЕЩ(первая ячейка с данными; ПОИСКПОЗ(выбранный в выпадающем списке месяц - точка отсчета; диапазон с месяцами в наших данных;0)-1;0; выбранная длительность периода; 1)

Вычитаем единицу из позиции выбранного месяца, т.к. для января , который первый в диапазоне, нам отступать никуда не нужно.
Последний аргумент- это ширина, она равна единице.
0 в функции СМЕЩ - это отступ по столбцам, он здесь не нужен. Берем данные из одного и того же столбца с показателем.

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

Помня о том, что в случае со спарклайнами нельзя добавить ось и подписи данных, выведем отдельно минимальное и максимальное значение за выбранный период - просто будем использовать СМЕЩ как аргумент функций МИН и МАКС. Так мы будем понимать масштаб, понимать, какому значению соответствует минимальный и максимальный столбцы.

Ну и добавим еще немного интерактивности - сделаем возможность вводить цвет спарклайна в целом и максимальной точки руками и на русском языке.
Для этого будем в аргументе функции SPARKLINE переводить текст с названием цвета на английский с помощью GOOGLETRANSLATE.

Напомним параметры такого спарклайна:
charrtype (тип) – column (столбчатый)
color – цвет спарклайна
highcolor – цвет максимального значения

Ссылка на файл с примером

Другие посты про спарклайны:
– Меняем цвет у отрицательных, максимальных и минимальных, первых и последних значений.
– Спарклайн с условием
– Видеоурок: Синтаксис и примеры
– Что такое спарклайны?

Выведем все пары
Декартово произведение строк

Привет! Декартово произведение – все возможные пары элементов двух массивов. Если всё равно непонятно – просто посмотрите на скриншот )

Я подготовил для вас пользовательскую функцию, она будет работать с любым количеством строк. Чтобы воспользоваться – добавьте код функции в редактор скриптов Таблицы.

function RENE(range1, range2){
return []
.concat(...range1
.map(y => []
.concat(...[range2
.map(h => [y[0], h[0]]
)]))).filter(t => t[0]);
}

Второй вариант – решение вопроса с помощью стандартных формул Таблиц. Минус в том, что с большим количеством строк такое решение работать не будет из-за ограничения функции JOIN на количество символов.

=ARRAYFORMULA(TRANSPOSE(SPLIT(
{REPT(JOIN("^";A3:A5)&"^";4);REPT( JOIN("^";B3:B6)&"^";3)};
"^")))

Таблица с примером
Наш чат, там препарируем и не такие формулы 🔪

​​Динамическая диаграмма в Google Таблицах

В прошлый раз мы с вами развлекались с динамическим спарклайном, а сегодня сделаем по аналогии уже график.
У обычной диаграммы, конечно, есть плюсы. Больше возможностей для оформления и форматирования. И есть подписи оси! Можно видеть, за какой месяц данные, а не просто динамику без подписей, как на спарклайне.

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

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

Реализуется все не так элегантно, как в случае со спарклайном.
Для диаграммы данные придется вывести в ячейки рабочего листа. Сделаем это с помощью функции СМЕЩ, как и в предыдущем примере.
(Напомним, в Excel для создания таких диаграмм СМЕЩ можно вводить прямо в функцию РЯД в диаграмме)

На самом деле, мы воспользуемся двумя функциями СМЕЩ, соединенными в массив: одна будет выводить даты из первого столбца по выбранному диапазону, а вторая — данные из второго или третьего столбца в зависимости от выбранного пользователем показателя.
(Будем в зависимости от выбора менять значение смещения по столбцам в функции СМЕЩ)

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

И еще нашей диаграмме не хватает заголовка. Если в Excel прямо из заголовка диаграммы можно было сослаться на ячейку, как в обычной формуле, например =A1 и в A1 ввести формулу, которая будет формировать текст заголовка, то в Таблицах возможен только фиксированный текст. Так что остается сформировать динамический заголовок с помощью формулы в ячейке над диаграммой.

Будем брать название показателя и добавлять к нему фразу "за период с янв 17 по дек 19". Периоды будем оформлять с помощью функции ТЕКСТ / TEXT, которая меняет форматирование числовых данных по заданному шаблону.

Файл с примером: копируйте себе на диск, изучайте, разбирайте формулы.

Простой onEdit() скрипт переноса строки

Недавно в нашем чате спросили – как пользователь Таблицы может переносить строки из одного листа на другой?

Отвечаем. Вот здесь Таблица со скриптом.

Скрипт работает так – активируете на листе "отсюда" флажок в третьем столбце – эта строка удаляется из листа "отсюда" и значения вставляются на лист "сюда".

Код с комментариями:
https://pastebin.com/yhbU0FDf 🤗

Избранные посты нашего канала:
https://t.me/google_sheets/418

Оглавление канала в Google Таблице

СОБИРАТОР 3.0 (копируем и вставляем данные через sheets api)

Как-то к нам пришел Дима @IT_sAdmin и сказал – ребята, помогите! Мне очень нужно регулярно брать данные из больших Таблиц, фильтровать, а потом копировать результат в другие Таблицы, других Таблиц будет много (сотни их). Нужно, чтобы был интерфейс и всем этим волшебством можно было управлять из него.

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

Что добавили нового:

1) Фильтрация данных источника (столбец F листа настройки)
Примеры:
row[0] == 'Москва' || row[0] =='Спб' – Москва или Питер в первом столбце
row[2] > 10 && row[4] != 'Вася' – третий столбец больше 10 и в пятом столбце не имя Вася
row[4] – в пятом столбце есть данные

2) Дополнять / удалять / ❌ (пропускать) – вы выбираете, что делать с листом, на который вставляете данные, вы можете либо дополнять его новыми строками, либо очищать перед вставкой данных. А выберете ❌ – скрипт пропустит эту строку.

3) Маркер загрузки строк (✅ и количество строк) – мы добавили новое поле (столбец K), если загрузка выполнена успешно – появляется ✅ + количество загруженных строк. При повторном запуске скрипт продолжает с первой необработанной строки. Если хотите загрузить все Таблицы – очистите столбец K.

4) Стало быстрее – теперь к Таблицам обращаемся напрямую через SHEETS API, а не через обёртку.

5) Три попытки – теперь если сервер недоступен, то скрипт попытается еще.

>> Таблица со скриптом (делайте копию)

Будут вопросы по работе скрипта – пишите, поможем
📣💬 @google_spreadsheets_chat