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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В Excel 365 появилась чудесная функция ПРОСМОТРX / XLOOKUP — хороший апгрейд стандартного ВПР, не страдающий зависимостью от расположения столбцов в исходном диапазоне данных. И хоть мы не про Excel, но уверены, что вам будет полезно про нее знать.

И нечего грустить по поводу ее отсутствия в Таблицах - благодаря магии работы с массивами мы можем сделать собственный апгрейд ВПР'а, который будет работать при любом расположении столбцов.

В посте смотрим на ПРОСМОТРX, ее синтаксис и возможности, а также вспоминаем, как заменить ее в Google Таблицах с помощью пересборки исходной таблицы прямо внутри функции.

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

Зачем Таблицы в школе?

Людмила работает школьным психологом в украинской школе. Мы попросили ее рассказать, зачем ей в школе Google Таблицы: https://telegra.ph/Prichem-tut-tablicy-Google-07-20

Помогаем маме: Google Таблица для отправки посылок Amazon и Ebay

Друзья, мама нашего подписчика Антона активно торгует на Amazon и Ebay. Ежедневно ей приходится отправлять много посылок.

Антон создал решение для отправки и по нашей просьбе рассказал о нём в статье на медиум

ЗАМЕНЯТОР: замена значений из словаря на другие в выбранных Таблицах

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

Краткая инструкция:
1) делайте копию Таблицы со скриптом, чтобы унести её к себе на диск
2) заполняйте словарь (значения, которые мы ищем и значения, на которые мы будет их заменять) и вносите ссылку на Таблицу со словарём и название листа в ячейки a3, b3
3) в диапазоне a5:c укажите таблицы, листы и диапазоны, в которых требуется произвести замену
4) в диапазоне d5:d отметьте чекбоксами те строки, которые требуется обработать сейчас
5) и запустите скрипт нажатием на кнопку

==
Скрипт создан при поддержке lesollp.ru, спасибо им,

а еще мы пишем любые скрипты, создаём телеграм ботов на заказ (тыц)

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

ЗАКРЫВАТОР: скрипт автоматического закрытия прошедших дней

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

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

Работает он так – с помощью функции findRow находим номер последней строки со вчерашней датой, далее с помощью delProtection удаляем защищенный диапазон, далее с помощью createProtection создаём диапазон начиная со второй строки листа и по последнюю строку со вчерашней датой.

Таблица со скриптом здесь, код отдельно здесь, чтобы все работало у вас – уносите код к себе в Таблицу и заполните 4, 5, 6, 11 строку в редакторе скриптов: это название рабочего листа, емейлы, которые добавляем в закрытый диапазон, название защищенного диапазона и номер колонки с датой на вашем листе.

==
Разработка Таблиц, скриптов и ботов (от 10к рублей)

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

ДОПУСКАТОР: изменяем права доступа к выбранным файлам и папкам на Google Диске

Хотите добавить к пятидесяти рабочим файлам нового сотрудника как редактора или оперативно убрать сотрудника, который не прошёл испытательный срок и вообще не подходит по духу и грозится все удалить?

Сегодняшнее решение (тут) поможет вам с этим разобраться.

Советы и правила:
– Скрипт работает и с файлами и с папками
– Можно указывать как URL, так и ID файла или папки
– Если даёте доступ к папке, то он распространится на вложенные в папку папки и файлы
– После обработки всей строки скрипт добавит дату и время в столбец E, при следующем запуске скрипт начнёт с первой строки БЕЗ даты и времени (это сделано для того, чтобы обрабатывать много файлов)

Поэтому, если нужно обработать строку – очистите
столбец E
– Чтобы добавлять / удалять сотрудников вы должны быть по крайней мере редактором

===
а еще уменьшаем время, которое вы тратите на рутину (скрипты, боты и отчёты на заказ)

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

QUERY по дате, представленной в виде текста

Допустим, у вас есть столбец с текстовыми строками, содержащими даты (в виде 05.06.2020, далее до 15.06.2025 или чего-то подобного)

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

Итак, вам нужно доставать все строки, в которых встречается дата за определенный месяц и год (числа любые).
Будем вводить в ячейке A1 дату, а извлекать из нее текстовую строку вида "MM.ГГГГ" будем следующей конструкцией:

ТЕКСТ(МЕСЯЦ(A1);"00" )&"." &ГОД(A1)

И далее полученный результат можно подставить, например, в QUERY - с помощью contains будем находить все строки, в которых этот месяц и год через точку встречаются в тексте.

=QUERY(диапазон_с_данными;"select * WHERE G contains '"&ТЕКСТ(МЕСЯЦ(A1);"00")&"."&ГОД(A1)&"'";1)

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

ДОСТАВАТОР: берём из кода веб-страниц регуляркой нужное

Представьте – вам нужно зайти на 200 однотипных веб-страниц и достать из них адрес объекта или как в нашем примере – ссылку на изображение.

Так себе перспектива, да?

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

Как всё работает:
0) копируйте Таблицу
1) заполните столбец A: ссылки, которые надо обработать
2) заполните столбец B: регулярное выражение, которое извлечет из кода веб-страницы нужное
3) запускайте!

Скрипт проходит строки по одной, вставляя данные и включая чекбокс, обрабатываются только строки с выключенным чекбоксом.

Про регулярку:
Ссылка, которую достаём, в коде страницы выглядит так:
data-large="/upload/d5/b4/19/37/d.jpeg" class="my-foto">

Нам нужно всё, что после data-large=" и до "

Регулярка будет такой:
data-large="(.+?)"


regex101.com/ – проверить выражение
rexegg.com/regex-tools.html – научиться

💊💡(скрипты, боты и отчёты на заказ)

ПОЛЬЗОВАТЕЛЬСКИЕ ФУНКЦИИ
Выводим все листы Таблицы и диапазоны с данными

/**
* Выводим листы и диапазоны
* @customfunction
*/
function листы_диапазоны() {

return
[['Лист', 'Диапазон с данными', 'Границы листа']].concat(SpreadsheetApp.getActive().getSheets()
.map(g => [g.getName(), g.getDataRange().getA1Notation(), g.getRange(1, 1, g.getMaxRows(), g.getMaxColumns()).getA1Notation()]))
}

Как работает? Положите код функции в редактор скриптов Таблицы, после введите в ячейку название функции листы_диапазоны.

Для принудительного обновления – оберните название функции в ЕСЛИ (IF) и добавьте ссылку на чекбокс. Активируете чекбокс – функция пересчитается.
=IF(A1; листы_диапазоны())

🔥Другие функции из гифки:
– выводим имя книги
– текущий лист и ссылка на него
– все листы книги и ссылки на них
https://pastebin.com/8Rr3fgZc

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

УДАЛЯТОР / ДОБАВЛЯТОР
Следим за количеством пустых строк в выбранных Таблицах

Тормозит Таблица, а вы боитесь лезть в формулы и что-то оптимизировать?

Есть способ проще - физически удаляем пустые ячейки. Это поможет, ведь даже пустые ячейки имеют вес и при пересчёте формул на них требуется время.

Мы придумали для вас решение (таблица со скриптом), с помощью него вы сможете поддерживать нужное количество строк и столбцов:

Работает так:
1. заносите ссылки на Таблицы
2. по каждой Таблице указываете, сколько пустых строк / столбцов должно остаться
3. запускаете скрипт, скрипт открывает каждый лист каждой таблицы
4. удаляет столбцы и строки, учитывая запас, который вы задали
5. если потребуется – то добавляет строки / столбцы до запаса

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

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

Пять мини-лайфхаков в Google Таблицах

Если вам нужно вернуть только первое значение из массива, возвращаемого FILTER, поставьте перед функцией +
=FILTER(A:C; C:C < 2000) — все значения меньше 2000
=+FILTER(A:C; C:C < 2000) — первое значение меньше 2000

Клавиша F4 — ей мы бы дали звание самой недооцененной. Это повтор последнего действия. Работает не для всего, но вставка строк/столбцов, форматирование и многое другое — очень ускоряет работу. Кстати, работает и в Excel. На Mac: ⌘ + Y или ⌘ + Shift + Z или Fn + F4.

Ctrl+Shift+V — вставка только значений. Еще один наш фаворит среди горячих клавиш. Как же это ускоряет замену формул на значения. Вот бы такое в Excel (да, окно "Специальная вставка" там можно вызвать Ctrl+Alt+V, но потом нужно еще выбрать "Значения" в нем). Кстати, сочетание работает во всех приложениях Google Диска — Презентациях, Документах. На Mac: ⌘ + Shift + V.

/copy — вставьте слово "copy" после ссылки на Таблицу (или другой док Google Диска) и отправляйте ссылку тем, кому нужно создавать копию, а не пользоваться исходной таблицей — сразу будет открываться страница с кнопкой "Создать копию".

Хотите наглядно оформить дэшборд с отклонениями "план-факт" — используйте диаграмму "Сводка" (хотя это не совсем диаграмма) — в качестве диапазона данных используйте два значения, факт и целевое/прошлый период/другой базис для сравнения.
Можно настроить отклонения в % или абсолюте (см скриншот).

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

КАК МЫ ПРОВЕЛИ ЛЕТО

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

Налетайте, не благодарите и делитесь с друзьями:

ЗАМЕНЯТОР: замена значений из словаря на другие в выбранных Таблицах t.me/google_sheets/563

ЗАКРЫВАТОР: скрипт автоматического закрытия прошедших дней t.me/google_sheets/564

ДОПУСКАТОР: изменяем права доступа к выбранным файлам и папкам на Google Диске t.me/google_sheets/565

ДОСТАВАТОР: берём из кода веб-страниц регуляркой нужное t.me/google_sheets/567

TELEGRAM BOT (+ полная инструкция): t.me/google_sheets/556

СОБИРАТОР (копируем и вставляем много данных через sheets api) t.me/google_sheets/536

===

📕 Оглавление канала
📘 Канал: @google_sheets
📗 Чат: @google_spreadsheets_chat

Массивный ВПР

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

Задача: одной формулой получить стоимость всего проекта.

Решение: будем перемножать весь диапазон с количеством часов специалистов на функцию ВПР, которая вернет массив с их ставками:

=СУММПРОИЗВ(B2:F2;ВПР($B$1:$F$1;'Специалисты и ставки'!$A:$B;2;0))

СУММПРОИЗВ / SUMPRODUCT перемножает элементы массивов и возвращает сумму этих произведений.
Первый аргумент - это часы специалистов, а второй - функция ВПР, которая по заголовкам столбцов подтянет ставки из другого листа.

Обратите внимание, что порядок специалистов на листах отличается. С функцией ВПР меньше вероятность ошибки, чем если бы мы просто перемножали два массива (такая схема возможна только если порядок и количество специалистов на 2 листах строго совпадают).

В случае с ВПР на листе со ставками может быть любое количество специалистов в любом порядке, а подтягивать их можно на любое количество листов с работами, где будет только часть специалистов — опять же, в любой последовательности.
Таблица с примером

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

===
📕 Оглавление канала
📘 Канал: @google_sheets
📗 Чат: @google_spreadsheets_chat