Google Таблицы

ВПР, достаём последнее значение по ключу

Функция =VLOOKUP("Вася";$A$3:$B$7;2;0) вернёт 100 – первое найденное значение из диапазона поиска по ключу "Вася".

Что делать, если мы хотим последнее значение по нашему ключу – 300?

1) Отсортируйте таблицу поиска по строкам по убыванию
2) Вставьте результат в диапазон ВПР

После этого последние строки станут первыми и функция начнет возвращать их.

Магия в том, что всё можно сделать внутри одной формулы:
=VLOOKUP("Вася"; sort(A3:B7; row(B3:B7); false) ;2;0)

P. S. а тут про левосторонний ВПР, с помощью него можно вернуть столбцы, которые расположены ДО ключа

Народный Telegram бот
❗️ Отправляем сообщения прямо из Таблицы

Друзья, продолжаем серию про нашего Telegram бота, которого вы можете поднять с нуля за 10 минут.

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

Итого, сейчас бот умеет:
– сохранять в Таблицу все сообщения, которые пишут ему и которые пишут в чаты, в которых он есть
– сохранять все файлы / фото на Google Диск
– отправлять сообщения из Таблицы

Как вы можете использовать новый функционал? К примеру – берём показатель из ячейки, добавляем к нему текст и рассылаем каждое утро всем заинтересованным людям, поставив скрипт в расписание.

Статья с инструкцией по установке

Таблица с ботом

Видео с полной установкой

---
📕📗📘 Оглавление канала (в Google Таблице)

Функция CHAR (СИМВОЛ) и генератор случайных кодов

Друзья, в статье рассказываем про функцию CHAR, выводим 20 000 разных символов с помощью SEQUENCE и создаем генератор случайных кодов.

Статья про генератор
(с Таблицей с формулами и скриптом)

---
📕📗📘 Оглавление канала

Тормозит таблица?

Узнаём, какие в Таблице формулы и на каких они листах.

Простой скрипт для вас:
1) вводите в ячейку B2 ссылку на Таблицу,
2) нажимаете на выходца из убежища
=) скрипт соберет все формулы на всех листах и вставит их на лист

Так вы сможете увидеть лишние и тяжелые формулы и их оптимизировать. Что такое тяжелые формулы? Например, не советуем использовать QUERY вместо SUMIFS для каждой ячейки. Не стоит делать запасы из десятков тысяч протянутых впрок формул.

Таблица со скриптом (делайте копию, чтобы сохранить её себе и запускать скрипты)

Код отдельно: https://pastebin.com/pgjCpRiF

---
📕📗📘 Оглавление канала

Превратим 10.62 руб. в правильное число

У нас есть текстовые строки:

10.62 руб.
8.85 руб.
0руб.

Превратим их в числа:

1) с помощью SUBSTITUTE(B3 ; "руб." ; "") заменяем все "руб" на ничего
2) с помощью SUBSTITUTE(... ; "." ; ",") заменяем точку на запятую
3) используя TRIM убираем пробелы, если они есть
4) и с помощью VALUE превращаем то, что получилось в число

Формула целиком:
=VALUE( TRIM( SUBSTITUTE( SUBSTITUTE(B3 ; "руб." ; "") ; "." ; ",")))

Другой вариант (регулярка):
=VALUE(
REGEXREPLACE(C3;"(\d+)\.?(\d+)?.*";"$1,$2"))

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

---
📕📗📘 Оглавление канала

Превратим 1 час, 2 мин, 22 сек в 1:02:22

Друзья, знаем, что вам нравятся такие формулы.

На входе у нас есть текстовые строки:

2 мин, 54 сек
11 мин, 37 сек
4 мин, 18 сек

Превратим эти строки в правильное время Google Таблиц:

1) С помощью трёх REGEXEXTRACT извлекаем из строк часы, минуты и секунды отдельно;
2) Добавляем IFNA, функция вернет 0, если какого-то показателя не будет;
3) Добавляем то, что получилось в функцию TIME(часы; минуты; секунды);

Формула целиком:
=TIME(
IFNA(REGEXEXTRACT(B4;"(\d+) ч.*");0);
IFNA(REGEXEXTRACT(B4;"(\d+) мин");0);
IFNA(REGEXEXTRACT(B4;"(\d+) сек");0))

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

---
📕📗📘 Оглавление канала

Табличные диалоговые окна. Подсказки ввода dadata.ru

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

Статья с примерами кода

---
📕📗📘 Оглавление канала

Очень простой летний скрипт

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

Работает он так:
1. заполняете лист "настройки": вносите название листа шаблона, а также названия листов, которые нужно создать
2. запускаете скрипт :)

Код скрипта:
function myFunction() {
const ss = SpreadsheetApp.getActive()
sheet1 = ss.getSheetByName('настройки'),
data = sheet1.getDataRange().getValues(),
sheet0 = ss.getSheetByName(data[0][0]);

for(let x = 1; x < data.length; x++){
const sheet_name = data[x][0];

if(!ss.getSheetByName(data[sheet_name])){
sheet0.copyTo(ss).setName(sheet_name);

}
}
}

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

---
📕📗📘 Оглавление канала

Переключение дэшборда между днями и неделями — с помощью функции SEQUENCE

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

И при этом хотите легко переключать режим “недели / дни" (или изменение любого другого параметра), не залезая в формулы.

В статье разбираем, как построить такой отчёт

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

---
📕📗📘 Оглавление канала

UPDATE к Telegram боту

— теперь бот умеет сохранять файлы, которые отправлены в чат на ваш Google Диск (quick view - тоже)
— ссылка на сохраненный файл попадёт в Таблицу
— подпись к файлам сохранится тоже

Статья (разворачиваем бота с 0 за 15 минут)
Таблица с кодом бота (кто развернул предыдущую версию – просто обновите страницы скрипта doPost и download и опубликуйте приложение под новой версией, регистрировать вебхук заново не нужно)

---
📕📗📘 Оглавление канала (в Google Таблице)

Telegram бот, который записывает всё, что видит в Google Таблицу

Вам потребуется 15 минут времени, чтобы создать такого бота самостоятельно.

В статье на медиуме – полная инструкция, справятся даже новички.

📣 Наш чат: @google_spreadsheets_chat

Несколько ссылок в одной ячейке

Небольшая новость: теперь добавлять гиперссылки можно к отдельным фрагментам текста, а не ко всей ячейке целиком.
Если раньше при создании гиперссылки (быстро сделать это можно с помощью Ctrl/Cmd + K) в ячейке автоматически формировалась функция ГИПЕРССЫЛКА / HYPERLINK, то теперь ссылки работают без функции и их может быть несколько в одной ячейке.

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

При этом объединить несколько функций ГИПЕРССЫЛКА (например, если формируете ссылку формулой, как в нашем примере с ссылкой на отправку письма контрагенту из таблицы) в одной ячейке не получится. Только вставленные руками ссылки.

📲 наш чат

Волшебство "найти и заменить"
Приводим 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 за идею поста.

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

СОБИРАТОР 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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)};
"^")))

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

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

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

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

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

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

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

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

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

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

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

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

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