гифка к посту (для тех, кто не очень понял, как работает штука выше):

- вводим ссылки на таблицы и названия листов в интерфейс

- дожидаемся, чтобы прогрузилась формула, определяющая последнюю строку на каждом листе

- копируем собранную формулу и вставляем её на новый лист

ИТОГИ 2020

оглавление с категориями для простого поиска

Друзья! За 2020 год мы написали на наш канал 90 постов, сумма их просмотров ~ 1 100 000.

Чтобы вам было проще с этим разобраться – мы разбили посты на категории и вывели их на лист "2020" в оглавлении нашего канала.

Категории такие:
– готовые решения
– телеграм боты
– формулы
– гостевые статьи
– видео
– форматирование

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

Мы поздравляем вас с наступающими праздниками, берегите себя.

Прокачивайтесь в Таблицах, с помощью них вы сможете строить отличные системы отчётности и существенно упростите рутину в деле, которым занимаетесь 🎄

ОГЛАВЛЕНИЕ 2020

Достаём данные из ЮТУБ / ТЕЛЕГРАМ / VC / Т—Ж / ХАБР в Таблицу

Друзья, у нашего подписчика Михаила Шардина много материалов на ресурсах, которые перечислены в заголовке.

Перед ним встала задача доставать просмотры, лайки и комментарии всех этих материалов.

Для этого он создал Таблицу "LynxReport: yчёт публикаций" и для каждого ресурса написал скрипт. Ютуб парсится с помощью API, все остальные сайты - fetch страницы и последующий match строки по регулярному выражению.

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

==
📗 ОГЛАВЛЕНИЕ КАНАЛА категории | всё оглавление

Функция ВЫБОР/CHOOSE в Excel и Google Таблицах: выбираем случайный элемент из набора

Функция ВЫБОР возвращает одно из значений, перечисленных в ее аргументах, по порядковому номеру. И этими аргументами могут быть как значения, так и ссылки на диапазоны. Так что с помощью нее можно и сгенерировать случайное значение из набора, и, например, "выбирать", из какой таблицы ВПР-ить данные. Пример здесь.

=
🔥 НАШ КУРС НА SKILLBOX, Таблицы и скрипты, Excel и VBA
📗 ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление

Функция DETECTLANGUAGE в Google Таблицах: определяем язык текста в ячейке.

Кейс: автоматом формируем ссылку на нужный интернет-магазин

=
🔥 НАШ КУРС НА SKILLBOX, Таблицы и скрипты, Excel и VBA
📗 ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление

ВЫГРУЖАТОР: СОЗДАЁМ РЕЕСТР ФАЙЛОВ ИЗ ПАПКИ, ВЛОЖЕННОСТЬ – УЧИТЫВАЕТСЯ

Выпускаем вторую версию скрипта, с помощью которого вы сможете выгрузить реестр файлов в Таблицу. Теперь поиск идёт и по вложенным папкам.

КАК РАБОТАЕТ:
– копируйте Таблицу с кодом;
– вводите ID или URL папки начала поиска в ячейку B1;
– запускайте скрипт из меню [СКРИПТЫ];

Если в B1 напишете root – то выгрузится весь диск, это сработает только для небольших дисков, < 10 000 файлов, из-за максимальной продолжительности выполнения скрипта в 6 минут.

Помимо остальных столбцов – в столбце E – вся информация по файлу. Из этой длинной строки вы сможете вытащить, к примеру, владельца файла или дату его создания.

ТАБЛИЦА С КОДОМ

=
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление

Наше приложение: Эвотор + Google Таблицы

Друзья, привет! С середины прошлого года мы занимаемся созданием своего приложения для синхронизации онлайн-касс «Эвотор» и таблиц.

Судьба у приложения получилась непростой: сменилось 3 разработчика, но сейчас всё отлично — приложение готово и выложено в магазин Google.

Полезные функции:
– чеки вашего бизнеса загружаются в таблицу в «плоском формате» (одна позиция в чеке — одна строка)
– загрузка происходит автоматически каждый час
– чеки подставляются в отчёты, сейчас их три (общий отчёт по продажам / топ товаров / топ товаров по магазинам)
– есть модуль отправки данных в «Телеграм» — сможете настроить регулярную отправку диапазонов Таблиц по расписанию

Приложение: https://workspace.google.com/marketplace/app/%D0%BE%D0%BD%D0%BB%D0%B0%D0%B9%D0%BD_%D0%BA%D0%B0%D1%81%D1%81%D1%8B/978990150260

Для вопросов и покупки: @namokonov

ВПР / VLOOKUP со звездочкой

Друзья, хотим рассказать/напомнить про возможность применения символьных шаблонов в функции ВПР.

Мы неоднократно рассказывали про применение * (любое количество любых символов, в том числе и нулевое, то есть ничего) и ? (любой один обязательный символ) в функциях семейства СУММЕСЛИ(МН), СРЗНАЧЕСЛИ(МН), СЧЁТЕСЛИ(МН).

Но их можно использовать и в ВПР. Если мы хотим найти не ячейку с точным совпадением с искомым значением, а ячейку, соответствующую определенной маске, и тут пригодятся звездочка и знак вопроса.

Например, такая функция ВПР будет искать ячейку, которая начинается со слова "Google" и вернет данные из второго столбца таблицы

=ВПР("Google*";Таблица;2;0)

А такая — ячейку, в которых будет встречаться слово iPad с любыми тремя знаками после этого слова через пробел и отделенных пробелом от дальнейшего текста (то есть iPad Pro и IPad Air подойдут, а iPad 10.2 не подойдет)

=ВПР("*ipad ??? *";Таблица;2;0)

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

P.S. А заодно напомним про РЕГИСТР — он в функции ВПР не учитывается. Даже если в функции будет ipad или iPad, а в таблице для поиска IPAD — все найдется.

Про символьные шаблоны:
Символьные шаблоны в функциях СЧЕТЕСЛИ, СУММЕСЛИ
Шпаргалка по символьным шаблонам

Еще посты про ВПР:
– ЛЕВЫЙ ВПР (когда искомое значение не в первом столбце)
– ВПР (VLOOKUP) по нескольким условиям
– ВПР в массиве вместо тысячи CУММЕСЛИМН. Статья в Medium.
– ВПР по нескольким диапазонам
– ВПР с интервальным просмотром = 1
– Перекрестный ВПР (ищем по строке и заголовку)
– Видео про функцию ВПР в Google Таблицах

Сравнение текстовых строк по символам и их кодам

Зачастую одни и те же текстовые данные воспринимаются Excel или Таблицами как разные, если они из разных источников (например: одно и то же название товара в выгрузке из 1С и в ваших таблицах будет разным из-за того, что там отличаются... пробелы! Пробелы бывают разными, увы).

Внешне это понять сложно - нужно смотреть на код символа. Его можно определить с помощью функции КОДСИМВ / CODE. Если у внешне одинаковых символов отличаются коды, значит, для Таблиц это разные символы и текстовые ячейки с ними будут тоже считаться разными.

Ну а чтобы достать определенный символ из текста, нужна ПСТР / MID.
Если мы хотим сразу разбить текст по символам одной формулой, сделаем формулу массива, и в качестве второго аргумента ПСТР (какой по порядку символ извлекать) укажем функцию SEQUENCE, которая создаст массив от единицы до числа, соответствующего количеству символов в ячейке (его находим с помощью ДЛСТР / LEN):
=ArrayFormula(ПСТР(A1;SEQUENCE(ДЛСТР(A1);1;1;1);1))

Чтобы получить массив кодов, а не сами символы, добавим сверху КОДСИМВ:
=ArrayFormula(КОДСИМВ(ПСТР(A1;SEQUENCE(ДЛСТР(A1);1;1;1);1)))

Делюсь таблицей с этими формулами, которую делал для одного из слушателей курса на Skillbox: подставляйте текстовые строки в зеленые ячейки и сразу увидите разбивку по символам и их кодам, а условное форматирование "подсветит" ячейки в строках с разными символами.
Ячейки с формулами в таблице выделены оранжевым.

=
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление

ОТПРАВЛЯТОР 2.0
Создаём из Таблиц PDF и JPG и рассылаем в Телеграм по заданному расписанию

Друзья, привет! Выпускаем вторую версию решения для отправки ваших данных в Телеграм.

В первой версии скрипт создавал простое PNG-изображение из диапазона данных, теперь же мы создаём полноценный PDF или JPG файл.

Таблица полностью готова к работе, просто заполните правила отправки и введите токен своего Телеграм бота.

Таблица со скриптом (инструкция – внутри!)

=
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
☺️ ДОНАТЫ: реквизиты

FLATTEN2, что говорит лисичка и другие неописанные в справке функции Google Таблиц

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

FLATTEN 2: другие недокументированные функции Google Sheets

Не про все функции понятно, что они делают, в справке же их нет :) поэтому пробуйте и предлагайте свои варианты в чат: @google_spreadsheets_chat

==
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление

Отбираем три последних строки по имени и считаем среднее (ниже будет сложно!)

Представьте – вашим сотрудникам ставят оценки и вам нужно посчитать среднее по каждому сотруднику из его последних трёх оценок.

На скриншоте – разбор такой формулы.

В её основе (пункт 1 на скриншоте) – функция COUNTIFS / СЧЁТЕСЛИМН в массиве, она добавляет к диапазону виртуальный столбец с нумерацией по ключу, начиная от последнего элемента (у нас ключ – только имя, но при необходимости вы сможете использовать несколько ключей, просто добавив по аналогии условия в COUNTIFS). Подробнее про принцип работы формулы нумерации здесь.

Вторая часть (пункты 2-3 на скриншоте) – обработка получившего массива с новым столбцом внутри QUERY – оставляем последние три вхождения, группируем и считаем среднюю оценку.

Таблица поближе

Знаете, как решить по другому - пишите в комментариях :)

=
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
☺️ ДОНАТЫ: реквизиты

Встраиваем видео в Google Таблицы

В Таблицах можно воспроизводить видео со звуком. Как это можно использовать? К примеру, вы сможете положить несколько видео-инструкций для своих коллег в Таблицу.

Чтобы видео заиграло – создаём скриптами модальное окно с emded-ссылкой на видео.

Код очень простой:
Код скрипта в pastebin
Таблица со скриптом

=
Как заставить решение работать:
1) копируете Таблицу
2) открываете редактор скриптов в ней > триггеры > создаёте триггер на редактирование Таблицы, выбираете функцию onEditTrig и сохраняете
3) готово – скрипт будет запускаться из Таблицы, после того, как вы активируете один из чекбоксов

За идею и реализацию спасибо @Lev_Andreevich 🦁

=
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление

SEQUENCE — выводим заголовки с датами текущего месяца

И снова о прекрасной функции SEQUENCE, которая, кстати, появилась и в Excel — пока только для подписчиков Office 365 (на русском — ПОСЛЕД).

Эта функция генерирует массив чисел по заданным параметрам - высоте, ширине, первому значению и шагу.

Аргументы функции:
число строк, число столбцов, первое значение и шаг.

Например, такая функция выведет столбец с числами от 1 до 10000:
=ПОСЛЕД(10000;1;1;1)

Так как даты — это числа, то можно выводить и их.
Допустим, нам нужны все даты текущего месяца в строку. Понадобится такая конструкция:
=ПОСЛЕД(1 строка; число дней в месяце; дата начала месяца; шаг=1)

Первый день текущего месяца можно получить так (текущий год + текущий месяц + первое число):
=ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());1)

А количество дней в месяце — так:
=КОНМЕСЯЦА(СЕГОДНЯ();0)

Остается все собрать в одну конструкцию. И как тут не напомнить про то, что в строке формул можно использовать пробелы и переход на следующую строку (Alt+Enter), чтобы делать длинные формулы проще для восприятия.

Конечно, внутри функции ДАТА можно подставить любой месяц вместо текущего, и тогда будут выведены даты этого месяца (см ГИФ)

=
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
☺️ ДОНАТЫ: реквизиты

🧞‍♂️Превращатор Листа Таблицы в xlsx / pdf / csv
— файл мгновенно скачивается
— либо сохраняется на Google Диск

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

Если конвертируете в xlsx – скрипт вставит все формулы на листе как значения, чтобы ячейки, которые ссылаются на другие листы не потеряли своих значений.

Как установить:
Открываете вашу Таблицу, Инструменты > Редактор скриптов, вставляете код: pastebin.com/iuKKSFrM, сохраняете и перезагружаете Таблицу. После перезагрузки в Таблице появится меню "🧞‍♂️ – скрипты".

=
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
☺️ ДОНАТЫ: реквизиты

Извлекаем из массива фразы, даже если они написаны с ошибками

Привет! У нас есть чат, в него можно приходить с вопросами. Недавно туда пришла Марина и спросила:
Например, у меня есть 200 фраз. Среди них есть фразы со словами крем, глаза, ретинол. Мне их нужно найти в этом списке. При этом часть этих слов может быть написана с ошибками, типа крэм, ритинол и пр. И мне нужно вытащить все фразы с этими словами, в том числе и с ошибками.

Марине помог @vitalich, показав, как с помощью =FILTER и простых регулярных выражений можно вытащить всё, что нужно.

На скриншоте: формула в ячейке C2 берет регулярное выражение из ячейки C1 и выводит в C3 и ниже все, что ему соответствует (cre одна любая буква m).

Таблица

=
📗ОГЛАВЛЕНИЕ КАНАЛА, всё оглавление / категории
☺️ ДОНАТЫ: реквизиты