Друзья, на нашем канале уже более 200 постов.
Иногда мы и сами не можем сразу вспомнить, о чем писали 🙂

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

Если вы только начинаете работать в Таблицах, вам будет полезно:
— Мы на Хабре: "Функции Таблиц, которых нет в Excel": https://habrahabr.ru/post/331360/

— Советы по оптимизации Таблиц: https://tttttt.me/google_sheets/143

— Видеоурок по функции IMPORTRANGE (для связывания нескольких таблиц): https://www.youtube.com/watch?v=HOTpjAqdalc

— Видеоурок по фильтрам и режиму фильтрации: https://www.youtube.com/watch?v=kHN5sIFLIjw

— Про виды доступа к документам: t.me/google_sheets/341

— Совместная работа с фильтрами: https://tttttt.me/google_sheets/337

Про функции:
— Функция FILTER. Список условий выбираем диапазоном прямо с листа: https://tttttt.me/google_sheets/102

— Памятка по синтаксису QUERY / FILTER / SUMIFS: https://tttttt.me/google_sheets/283

— Создаем красивое расписание групповых занятий: https://tttttt.me/google_sheets/325

​— Google Форма + Google Таблица для проведения тестирования: https://tttttt.me/google_sheets/292

— Формула, которая достает изображения из поисковой выдачи Яндекса и добавляет их в прайс-лист: https://tttttt.me/google_sheets/367

— ВПР в массиве вместо тысячи CУММЕСЛИМН (заполняем весь лист одной формулой): https://tttttt.me/google_sheets/355

Про скрипты:
— Простой скрипт копирования / фильтрации (поможет, когда IMPORTRANGE перестает работать): https://tttttt.me/google_sheets/384

— Скрипт, загружаем письма вашего GMAIL-аккаунта в Таблицу: https://tttttt.me/google_sheets/374

— Скрипт, отправляем письма на электропочты из диапазона, который вы выделяете: https://tttttt.me/google_sheets/212

— И еще один скрипт для отправки писем (отправляем письма только на те адреса, на которые еще не отправляли): https://tttttt.me/google_sheets/339

Важный скрипт. Связанные выпадающие списки из кэша.

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

Все будет работать быстро, потому что мы используем внутренний кэш Таблицы — теперь словарь списков не считывается при каждом действии из Таблицы заново (что дорого), а загружается в кэш один раз, при открытии Таблицы или по таймеру перестройки кэша. И списки строятся уже из этого кэша.

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

Чтобы перенести код списков в свою Таблицу — скопируйте все скрипты и заполните лист скрипта parameters.

Будут вопросы — пишите в наш чат.

有難う

Нас попросили инструкцию попроще, попробуем:
1) скопировать надо все листы скрипта кроме mock_catalogue, вставлять можно на один лист;
2) скопировали - идем на лист parameters и заполняем на каком листе будет работать скрипт, на каких строках и из какого листа возьмёт справочник;
3) всё! перезагружаем таблицу через F5, скрипт должен заработать;

Новый тип диаграммы в Google Таблицах - "Сводка"

Хотя скорее это похоже на некую микроскопическую панель показателей, вернее - показателя.
Она показывает значение показателя и его отклонение от исходного/целевого/планового etc. значения

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

Может пригодиться для создания аккуратных дэшбордов (панелей показателей).

Ленивый скрипт отправки напоминаний.

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

⏩⏩ Смотрите скриншот и открывайте Таблицу с примером.

Скрипт можно поставить в расписание регулярного запуска, например, ежедневно в 10 утра, через меню Изменить > Триггеры текущего проекта.

Кроме дубликатов вы можете добавить в ячейку что угодно: сделки, по которым нужно сделать оплату сегодня (если у вас в Таблице есть дата следующей оплаты), сделки, в которых не заполнены все столбцы, etc.

Вам достаточно написать для этого формулу и объединить результат до одной ячейки. А скрипт уже эту ячейку будет регулярно отправлять.

Еще раз - Таблица с примером

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

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

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

1) идем в наши скрипты: https://script.google.com/home (кстати, там вы можете увидеть все скрипты вашего аккаунта)
2) создаем новый скрипт:

function clear(){
//определяем таблицу и лист в ней
var ss = SpreadsheetApp.openByUrl('url');
var sheet = ss.getSheetByName('имя листа');

//удаляем строки после 100
var rows = sheet.getMaxRows();
if(rows-100>0){sheet.deleteRows(100, rows)}

//или удаляем лист целиком
ss.deleteSheet(sheet);
}

3) всё, проблемная область удалена, ваша таблица снова доступна и вы можете в ней работать

4) а еще в нашем чате уже больше 1000 человек, присоединяйтесь и вы

IF внутри условия FILTER, отбираем ВСЕ значения или конкретное

Друзья, мы нашли полезный хинт в функции FILTER: если в условии написать диапазон условия = диапазон условия, то функция выведет все строки из диапазона вывода.

Можно использовать для выпадающих списков в интерактивных дашбордах, которые вы строите с помощью FILTER.

=FILTER(A2:B25;A2:A25=IF(E1="ВСЁ";A2:A25;E1))

P. S. А вот так в FILTER можно написать ИЛИ:
=FILTER(A2:B25;(A2:A25<>"Майер")+(B2:B25<500000))

Ищу работу аналитиком / продактом в Москве.

Последнее место работы — Яндекс.Такси 🚕, аналитик по ценообразованию: репрайсил тарифы в городах МО, определял города в балансе и придумывал процедуры для них, а еще сделал тысячу разных калькуляторов.

— Перед этим работал аналитиком в компаниях Marriott, Lotte, Автомир

— Совместно с Ренатом уже три года ведем этот канал про Google Таблицы и написали про них книгу: https://www.mann-ivanov-ferber.ru/books/google-tabliczyi-eto-prosto/

— Частенько бываю продакт оунером, мы разрабатываем для внешних заказчиков софт на C++/Python/GAS и строим ERP и CRM-системы в Таблицах (отзыв нашего клиента Григория)

Пишите, пообщаемся: @namokonov

CASE внутри функции QUERY

Коллеги, внутри QUERY на данный момент нельзя использовать доступную в большинстве SQL-диалектов функцию CASE.

Но — вы можете с помощью IF и массива создать виртуальный столбец, присоединить его к своим данным и уже эту конструкцию сделать диапазоном QUERY. И, например, группировать по новому столбцу.

1) Определяем, начинаются имена на «B» или нет (скриншот наверху):
=ARRAYFORMULA(IF(A:A<>"";IF(LEFT(A:A;1)="В";"Имена на В";"Другие имена");""))

2. Добавляем диапазон данных в QUERY и группируем по новому столбцу, считаем сумму столбца B

=QUERY({ARRAYFORMULA(IF(A:A<>"";IF(LEFT(A:A;1)="В";"Имена на В";"Другие имена");""))\A1:B};
"Select Col1, sum(Col3) group by Col1")

Некоторые поинты:
1. ARRAYFORMULA (функция массива) нужна, чтобы обработать в IF больше одной ячейки внутри одной формулы
2. AND в функции массива не работает (можете проверить) и приходится использовать вложенный IF (см. формулу в пункте 1)
3. Чтобы обращаться внутри QUERY к колонкам как Col1, Col2 - достаточно формулой изменить диапазон или просто взять его в фигурные скобки {}

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

Функции:
​​— Учимся извлекать из текста нужное. База знаний регулярных выражений для REGEXEXTRACT →→
— Памятка по синтаксису QUERY / FILTER / SUMIFS →→
— Функция FILTER. Список условий выбираем диапазоном прямо с листа →→
— Обоюдоострый FILTER. Убираем с помощью одной формулой из таблицы пустые строки и столбцы →→
— SUMIF для нескольких условий в формуле массива →→
— Формулой достаем изображения из поисковой выдачи Яндекса →→
​​— ЛЕВЫЙ ВПР (когда искомое значение не в первом столбце) →→
— ВПР в массиве вместо тысячи CУММЕСЛИМН (заполняем весь лист одной формулой) →→
— Автоматически создаем фразы по определенным шаблонам в Google Таблицах →→
— Пользовательские числовые форматы, добавляем к числам 🔥🔥🔥 →→

Скрипты:
— Скрипт. Распознаем текст на изображениях. OCR в Google Docs →→
— Простой скрипт копирования / фильтрации (поможет, когда IMPORTRANGE перестает работать) →→
— Скрипт onEdit(), реагирующий на изменения и отправляющий письма (или триггер в триггере) →→
— Скрипт, загружаем письма вашего GMAIL-аккаунта в Таблицу →→
— Скрипт, отправляем письма на электропочты из диапазона, который вы выделяете →→
— Простой скрипт для ежедневной рассылки из Google Таблицы →→
— Важный скрипт. Связанные выпадающие списки из кэша →→

Проекты:
— Telegram bot + Google Sheets (используя вебхуки) →→
— Эмоджи форматирование выполнения плана (IMAGE, IFS) →→
— Создаем красивое расписание групповых занятий →→
— Создание оглавления в телеграм-канале: как автоматически загружать и сортировать публикации из вашего рабочего файла →→
— Google Форма + Google Таблица для проведения тестирования →→
— Применение IFTTT для отправки сообщений с данными из Таблиц →→
— Googlefinance, Парето и графики (#готовое решение) →→
— Создаем инфографику с помощью IMAGE. Размер картинок пропорционален значениям →→
— Импортируем таблицу из веб-страницы и оставляем только нужные нам столбцы (IMPORTHTML+QUERY) →→

Для новичков:
— Мы на Хабре: "Функции Таблиц, которых нет в Excel": https://habrahabr.ru/post/331360/
— Советы по оптимизации Таблиц →→
— Видеоурок по функции IMPORTRANGE (для связывания нескольких таблиц) →→
— Видеоурок по фильтрам и режиму фильтрации →→
— Про виды доступа к документам →→
— Совместная работа с фильтрами →→

Полное оглавление нашего канала: тыц
Наш чат: @google_spreadsheets_chat

Почти три года и сотни публикаций, кейсов, формул — вот такой путь к 10 000 подписчиков.
Друзья, благодарим вас за поддержку! И двигаемся дальше.

А сегодня - про простую функцию, которая может быстро сформировать массив хоть из 10 000 чисел.
Это SEQUENCE.
Она простая — у нее следующие аргументы:
число строк, число столбцов, первое значение и шаг.

=SEQUENCE(10000;1;1;1) - это один столбец с числами от 1 до 10000.
=SEQUENCE(3;3;0;100) - матрица 3 на 3 от 0 до 800.

P.S. Дата в Таблицах — это тоже число, поэтому с помощью функции вы сможете формировать массивы и из дат

Вытащим из предложения все товары и посчитаем их стоимость в рублях

Привет, друзья! Представьте, у нас есть текстовая строка:
купили: товар 1, товар 2, товар 6

Рассказываем, как в этой строке оставить только товары, вытащить каждый отдельно, сопоставить с прайс-листом и посчитать общую сумму.

Всё на скриншоте.

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

Копируем данные из сотен Таблиц с помощью Advanced Google Services

Максимальное время работы одного GAS скрипта — 6 минут. Это значит, что если вы с помощью Spreadsheet Service пытаетесь обойти сотни Таблиц и скопировать из них тысячи строк — времени может не хватить и ваш скрипт завершится по таймауту.

Есть альтернатива, есть более быстрый способ манипулировать файлами, обращаться к ним, копировать данные, удалять данные и наводить всяческий хаос, как мы любим — это Advanced Script Services. У вас будут те же 6 минут для работы скрипта, но за это время вы сможете успеть больше.

Мы подготовили для вас пример: скрипт 100 раз открывает оглавление нашего канала, объединяет данные в один массив и вставляет результат в Таблицу. Еще Advanced API грузит только заполненные ячейки, чтобы это побороть и не нарушить итоговую схему данных — скрипт дописывает пустые ячейки в те строки массива, где они нужны.

Официальная документация

Перед использованием службу нужно активировать

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

Всё, что касается Google Таблиц можно обсудить в нашем чате: @google_spreadsheets_chat

Присоединяйтесь!

Срезы в Google Таблицах

Итак, друзья, Google Таблицы продолжают обновляться - и некоторое время назад в них появились срезы.

Они похожи на срезы в Excel. Там их можно применять к сводным таблицам и "умным таблицам" (которые вызываются опцией "Форматировать как таблицу").

Срез - это отдельно вынесенный фильтр, примененный к одному столбцу. Удобно и наглядно.

В Google Таблицах Срезы можно применять к обычным диапазонам, сводным таблицам и диаграммам.
Вызываются срезы через меню Данные -> Срезы.

После создания среза выберите столбец, по которому будут фильтроваться данные. Срезов может быть несколько на одну таблицу - если вам нужно фильтровать по нескольким столбцам.

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

Если хотите применить срез к диаграмме - создайте его на листе с диаграммой. При этом фильтровать можно и данные на другом листе. Главное, чтобы это были те данные, на основе которых диаграмма построена.

Библиотека небольших скриптов onEdit()

Друзья, привет, в этом проекте мы собираем небольшие скрипты onEdit(), про которые когда-то писали на канале. А ниже — два новых скрипта, спасибо нашему чату за идеи.

1) Изменяем название листа в зависимости от введенного в ячейку A1:
function onEdit(e) {
var ss = e.source;
var sheet = ss.getActiveSheet();
var r = e.range;
var v = e.value;

if(r.getA1Notation() === 'A1' && sheet.getName() !== v){sheet.setName(v)}
}

2) Пользователь написал «лазер» в третьем столбце — отправим эту строку на другой лист
function onEdit(e) {
var ss = e.source;
var sheet1 = ss.getActiveSheet();
var sheet2 = ss.getSheetByName("сюда");
var lr = sheet2.getLastRow() + 1;
var range = e.range;
var row = range.getRow();
var column = range.getColumn();
var value = e.value;

if(column == '3' && value.match(/лазер/gi)){
sheet2.getRange("A" + lr + ":" + "C" + lr).setValues(
sheet1.getRange("A" + row + ":" + "C" + row).getValues()
)}
}

Функция QUERY. Формируем запрос в WHERE ко всем колонкам формулой

Привет! Запрос в QUERY — текстовая строка и поэтому его всегда можно сформировать формулой.

Представьте — в ваших данных сто колонок и вы хотите написать запрос и вывести строки, в которых каждая ячейка будет больше 0.

Можно перечислить все сто колонок руками: WHERE Col1>0 and Col2>0 and ColN>0 and Col100>0, а можно написать формулу, которая создаст эту текстовую строку.

На скриншоте показываем, как это сделать.

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