Оглавление |

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

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

Наш курс на 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

Волшебство "найти и заменить"
Приводим mm-dd к dd-mm не формулой.

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

— Вместо пустых ячеек вставляем наш текст
Найти: ^$
Заменить: Наш текст
К ячейкам должен быть применён текстовый формат
данных. Пустая или пробельная ячейка: ^\s*$

— Заменяем перенос строки на наш текст
Найти: \n (или \r, \r\n)
Заменить: Наш текст

Встречались с тем, что в диапазоне американский формат дат, а его нужно быстро привести к российскому?

Группы захвата:
— Сделаем из mm-dd-yyyy на dd-mm-yyyy
Найти: (\d{1,2})-(\d{1,2})-(\d{4}|\d{2})
Заменить: $2-$1-$3

— Сделаем из dd-mm-yyyy на День: dd, месяц: mm
Найти: (\d{1,2})-(\d{1,2})-(\d{4}|\d{2})
Заменить: День: $1, месяц: $2

Спасибо нашу чату, @vitalich и @mildly_disastrous за идею поста.

📣 Наш чат | Оглавление нашего канала