Друзья, с наступающим вас!

Посты, которые вспоминали в нашем чате (@google_spreadsheets_chat) чаще всего:

>> Крутейшая таблица с подсказками про query

>> Памятка по работе с условиями в формулах FILTER, QUERY, SUMIF(и аналогах)

>> Условное форматирование. Выделяем дубликаты и не только

>> Избранные посты нашего канала

>> Как посчитать

>> Памятка с советами, позволяющими ускорить работу документа

>> Памятка по условиям, с помощью которых можно отобрать (для функции SUMIFs/СУММЕСЛИМН, например) пустые и непустые ячейки

>> СОБИРАТОР 4.0

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

>> Считаем сумму по каждой строке / столбцу в формуле массиве

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

>> OR / AND в функции FILTER

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

>> Объединяем любое количество диапазонов Таблицы в одной функции

>> ​​ARRAYFORMULA для автоматической нумерации списков (счетчик)

>> ЗАПУСКАТОР – реагируем на изменения формул / Таблицы и запускаем скрипты

>> SUMIF для нескольких условий в формуле массива. Объединяем диапазоны с помощью амперсанда (&)

>> ​​В правиле условного форматирования можно использовать данные другого листа.

Оглавление Таблицы скриптами

Друзья, привет!

В первом посте 2023 года решим классическую проблему – выведем список всех листов Таблицы вместе с ссылками на каждый лист.

Базовый скрипт.
function sheets() {
//текущая таблица
const ss = SpreadsheetApp.getActive();

//определяем ссылку на текущую таблицу
const ssUrl = ss.getUrl();

//проходим по всем листам Таблицы в цикле и формируем массив [ссылка на лист, название листа]
return ss.getSheets().map(sheet =>
[`${ssUrl}#gid=${sheet.getSheetId()}`, sheet.getName()]);
}

Скрипт будет работать как пользовательская функция, просто вставьте =sheets() на лист в любую ячейку, как на скриншоте.

Хак, чтобы оглавление, которое выводится обновлялось почаще – передаём в функцию любой аргумент из листа, =sheets(k1), например.

Вставляем оглавление как значения при открытии Таблицы.
Добавляем код ниже.
function onOpen() {
const arr = sheets();
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName('оглавление') || ss.insertSheet('оглавление');
sh.clearContents();
sh.getRange(1, 1, arr.length, arr[0].length).setValues(arr);
}

Вставляем оглавление гиперссылками (будет один столбец) при открытии Таблицы
function onOpen() {
const arr = sheets().map(f => [SpreadsheetApp.newRichTextValue()
.setText(f[1])
.setLinkUrl(f[0])
.build()]);

const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName('оглавление') || ss.insertSheet('оглавление');
sh.clearContents();
sh.getRange(1, 1, arr.length, arr[0].length).setRichTextValues(arr);
}

Средний чек

Друзья, привет! Сделаем упражнение по расчету средних чеков для диапазона C3:C100. Посмотрите на этот диапазон на скриншоте.

Средний чек по всему диапазону
Считается очень незамысловато:
=AVERAGE(C3:C100)

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

Накопительный средний чек
Посложнее, пишем формулу массива (за формулу спасибо Михаилу Cмирнову):
=ARRAYFORMULA(IF(C3:C="";"";SCAN(0; C3:C; LAMBDA(acc; cur; acc + cur)) / SEQUENCE(ROWS(C3:C))))

Разбираем:
1) =ARRAYFORMULA(IF(C3:C="";""; формула будет работать только для заполненных ячеек в C3:C;
2) SCAN(0; C3:C; LAMBDA(acc; cur; acc + cur)) считаем сумму чеков для каждой ячейки от начала диапазона (подробно эту формулу разбирали здесь);
3) / SEQUENCE(ROWS(C3:C)) и делим сумму чеков для каждой строки на количество чеков от начала диапазона до этой строки (смотрите в G:G на скриншоте, вывел для наглядности функцию туда).

Для каждой ячейки получаем средний чек, столбцец E:E на скриншоте, ура.

Придумаете другие варианты - напишите в комментарии.

---
⭐️ Наш курс по Excel, Таблицам и скриптам: тыц
⭐️ Оглавление канала: ты-дыц
⭐️ Самый табличный чат на свете: бадабум

F2: переход к ссылке в формуле

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

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

Обновляем книжно-табличный обзор (целиком он по ссылке).

Сегодня - две книги-сборника отдельных лайфхаков: одна по Excel, другая по Таблицам. Будут полезны всем - и новичкам, и продолжающим.
И одна книга по узкой теме, на которую, казалось бы, нечего написать на целую книгу - но на самом деле есть что: это книга про "таблицы" и списки Excel.

Spice Up Your Sheet Life

100 гугло-табличных лайфхаков на самые разные темы - от одного из главных экспертов. Форматирование, функции, сводные, диаграммы, скрипты - тут всего понемногу. И есть действительно нетривиальные вещи.

Бен дарит книгу за подписку на рассылку. Рассылку отдельно рекомендуем, кстати!
Ссылка на PDF.

MrExcel 2022: Boosting Excel
Книга на Амазоне
Новинка от одного из лучших авторов в теме Excel.

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

Есть новшества Excel последних лет: LET, LAMBDA, ПРОСМОТРX, опции для совместной работы (как представления или история версий). Есть и Power Query и модель данных Power Pivot,
Много неочевидных вещей, как у всегда у автора - то, что зачастую не встретишь в других книгах.

Excel Tables: A Complete Guide for Creating, Using and Automating Lists and Tables
Книга на Амазоне

Книга целиком посвящена "таблицам" Excel и их предшественнику - спискам. То есть тому инструменту, который в русскоязычной среде называют "умными таблицами", а в интерфейсе Excel - "таблицами" / tables.

Тут все детали про ссылки на таблицы, их взаимодействие, про изменение размеров таблиц, сортировку и фильтрацию, форматирование, использование как источник в простых сводных и Power Query и даже про работу с таблицами в VBA (макросах) и в Excel Online!

Перевод есть на сайте Сергея Багузина. Спасибо ему за эту работу! Сергей использовал в переводе свои скриншоты (!!) с русскоязычным интерфейсом.

Простой скрипт: убираем выпадающие списки (проверка данных) из выбранных ячеек

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

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

Скрипт нужно положить в скрипты Таблицы (Расширения > Apps Script).

function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Скрипты')
.addItem('Удалить проверку данных', 'del_dv').addToUi()
};

function del_dv() {
const range = SpreadsheetApp.getActiveRange();
range.clearDataValidations(); //добавиьте .clearContent(), чтобы очищать из ячеек значения
}

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

Update: коллеги заметили, что проверка данных удаляется просто через нажатие на DEL. Первое нажатие удаляет значения, второе проверку данных. Что же, будем знать, а скрипт пусть остаётся как пример.

Обращаемся к GPT3 из Таблицы

Друзья, взрыв популярности ИИ не прошёл мимо нас, мы сделали для вас Таблицу с интерфейсом для обращения к API GPT3. На GPT3 базируются модели искусственного интеллекта, на данный момент из API можно обращаться к четырем моделям (самой современной, которую выложили недавно - нет, но и текущие что-то могут, смотрите гифку).

Чтобы заработала Таблица для обращения к ИИ:
1) Копируем Таблицу себе

2) Регистрируемся и получаем API-ключ (для РФ регистрация закрыта, можно попробовать обойти через ВПН + виртуальную сим-карту другой страны), ключ вставьте в редактор скриптов

3) Во второй строке скопированной Таблицы выбираем модель и пишем вопрос в "prompt". Максимальное количество токенов, это что-то вроде смысловых единиц в вопросе и ответе, для text-davinci-003 это 4000, для остальных моделей - 2048. Если вместо ответа увидите ошибку про количество токенов - уменьшайте.

4) Запускаем: Скрипты > Отправляем запрос. Скрипт добавит в диапазон G:I ответ ИИ, либо ошибку, если что-то не так

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

Описание API GPT (сможете почитать про модели, токены, температуру, запросы)
Обращаемся к сократителю ссылок из Таблицы

👇 Делитесь интересными ответами в комментариях

---
⭐️ Наш курс по Excel, Таблицам и скриптам: тыц
⭐️ Оглавление канала: ты-дыц
⭐️ Самый табличный чат на свете: бадабум

Извлечение данных из чипов?

Вот какую красоту обещают добавить в Таблицы - извлечение данных из чипов. Например, владельца документа/таблицы, дату создания, автора последних изменений.

Как считаете, баловство или вам в работе такая возможность действительно поможет?

Уведомления: можно не только о комментариях, где вы отмечены, но и об изменениях.

Если вы хотите получать письма с уведомлениями об изменениях, вам сюда:
Инструменты - Настройки уведомлений - Отправлять, когда внесены изменения
Tools - Notification Settings - Edit notifications
Alt+T + N + E

Можно выбрать один из вариантов - ежедневное письмо-сводка или мгновенная отправка после изменений.

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

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

Ну а если выбрать "Уведомления о комментариях", то можно включить уведомления о всех комментариях (а не только тех, где вы отмечены). Или отключить все.

---
⭐️ Наш курс по Excel, Таблицам и скриптам: тыц
⭐️ Оглавление канала: ты-дыц
⭐️ Самый табличный чат на свете: бадабум

Несколько удачных ответов GPT в нашем чате

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

Помимо этого мы подключили к GPT нашего телеграм-бота и несколько дней спрашивали разное. Особенно неплохо у модели получается писать небольшие скрипты на Google Apps Script. Ответы не всегда абсолютно точные, но годятся для обучения.

Несколько примеров:
Код для google apps script: сделать первую БУКВУ строки заглавной t.me/google_spreadsheets_chat/287379

Скрипт, который вставит диаграмму в таблицу google apps script и объясни как он работает t.me/google_spreadsheets_chat/285572

Напиши apps script переноса данных с одного листа таблицы на другой по условию, по условию, что таких данных еще нет во втором листе t.me/google_spreadsheets_chat/285575

Напиши скрипт на google apps script, который удалит все красные строки через batch sheets api t.me/google_spreadsheets_chat/286854

🔩 Таблица с кодом, в которой вы можете попробовать сами

Опять парсим WILDBERRIES: внутреннее API

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

У нас в 21 в 22 годах были примеры по парсингу данных из веб-страниц карточек товара, сейчас они уже работают частично из-за того, что WB часто меняет вёрстку своих страниц.

Сегодня расскажем и покажем, как обратиться в внутреннему открытому API WB (используется при наполнении страниц данными).

Запоминайте адрес: https://card.wb.ru/cards/detail?nm=

К нему добавляем номеклатуры товара через точку с запятой, например https://card.wb.ru/cards/detail?nm=140428476;139447779

Если перейдете из браузера по ссылке – увидите объект, в нём будут все аргументы по товару, которые можно достать: цены, скидки, название, размеры, цвета.

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

Показываем простой код, доставать будем не всё, а только название товара, цену со скидкой, рейтинг и количество отзывов и вставлять результат в Таблицу:

function myFunction() {
const url = 'https://card.wb.ru/cards/detail?nm=140428476;139447779'
var response = UrlFetchApp.fetch(url);
response = JSON.parse(response).data.products;
var arr = [];
response.forEach(f => {
arr.push([f.name, f.salePriceU / 1000, f.rating, f.feedbacks])
});

const sheet = SpreadsheetApp.getActive().getSheetByName('Лист1');
sheet.getRange(1, 1, arr.length, arr[0].length).setValues(arr);
};

Опять парсим WB, готовое решение

Мы ведём канал уже шестой год, поэтому заранее предвидим ваш вопрос "а как спарсить все данные из объекта".

Мы подготовили готовую Таблицу со скриптом, на листе "что парсим" вводите номенклатуры, которые будем загружать и отмечайте их флажками, далее запускайте скрипт из меню с 🔥

Таблица
Отдельно код в Pastebin

PS Массив stocks с остатками по размерам сейчас пустой, если найдете способ его заполнить - поделитесь в комментариях :)

Update: из комментариев, пример ссылки, которая вернёт и остатки по складам. Объект остатков сейчас выводится в одну строчку, парсите его по аналогии, чтобы вывести в столбцы.

Бен Коллинс в одной из последних рассылок пишет про идею Front Sheet — своего рода титульного листа — реализацию которой он наблюдал в одной компании.

Ничего космического: первый лист, на котором указано, кто создал таблицу, для чего она, какие данные внутри, и прочая информация (например: из каких источников импортируются данные, если есть функции IMPORTRANGE, какие есть листы — с ссылками на них, как работают сложные формулы в таблице). У той консалтинговой компании были добавлены какие-то элементы бренда (видимо, логотип).

Что еще можно делать, чтобы упрощать работу с таблицами клиентам, коллегам (и себе в будущем)?
— выделять разными цветами заливки заголовки тех столбцов, данные в которых вносятся вручную и тех, в которые "не входить, работает формула". Можно добавить легенду с этими цветами на тот же титульный лист
— ссылаться на ячейку со ссылкой на исходник в функциях IMPORTRANGE, чтобы была возможность быстро перейти к исходнику
— примечания к ячейке с какой-нибудь зверской формулой — с объяснением, как она работает

Что вы практикуете?

Полезности:
Скрипт для создания списка листов в Google Таблицах

Макрос для создания оглавления в Excel (листа с названиями всех листов и ссылками на них)

Достаем изображения из Google и Яндекса формулой

А в последней рассылке Коллинс рассказывает о формуле для поиска изображения в Google Images.
В ней мы подставляем название товара или что угодно еще, что нужно найти, в поисковый запрос, потом получаем список всех изображений этой странице с поисковой выдачей (IMPORTXML), затем берем одно из значений из этого списка (можно второе — первое обычно пустое или там может быть логотип, а далее могут быть менее релевантные варианты) - ИНДЕКС / INDEX, и превращаем ссылку в изображение (IMAGE).

=IMAGE(INDEX(IMPORTXML("https://www.google.com/search?tbm=isch&q="&ENCODEURL(текст для поиска) ; "//img/@src");2))

А мы вам рассказывали про такое еще в 2019 - но про поиск в Яндекс.Картинках :)
Там все аналогично, только ссылка на поиск другая:
=IMAGE(INDEX(IMPORTXML
("https://yandex.ru/images/search?text="&ENCODEURL(текст для поиска);"//img/@src");2))

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

Пост про новые функции в Таблицах от нашего постоянного блестящего автора Михаила Смирнова!

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

А ведь не так давно всяческую магию вроде TOROW или VSTACK подвезли в Excel, а вот уже и в Таблицах эти и другие функции на подходе!

А вот ссылка на все статьи Михаила, там много пользы.

Помните, сколько раньше было проблем с объединением массивов с разным количеством строк / столбцов? (A2:C6; A9:B12 на скриншоте)

А с помощью новой функции VSTACK это очень просто.

Знакомьтесь со статьей Михаила про новые формулы, там есть и другое полезное.

И смотрите Таблицу с примерами новых формул.

Обращаемся к GPT, рисуем валентинки прямо из Таблицы

Друзья, привет! Продолжаем про GPT3.

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

Как все работает можете посмотреть на ГИФке. Вам нужно ввести запрос, количество картинок, которые нужно создать и разрешение.

Срок жизни изображения по ссылке – час. Поэтому если понравилось – сразу скачивайте себе.

Таблица здесь.
Отдельно скрипты по генерации изображений и текстовых ответов.

Отличное руководство по получению API-ключа, если вы в России здесь.

В одном из следующих постов расскажем вам, как запрашивать и текст и изображения прямо из телеграм-бота. Ждите :)


---
⭐️ Наш курс по Excel, Таблицам и скриптам: тыц
⭐️ Оглавление канала: ты-дыц
⭐️ Самый табличный чат на свете: бадабум

sumifs в формуле массива

Друзья, привет вам. Раньше, чтобы написать суммирование по нескольким условиям в формуле массива мы делали вот так (sumif в массиве с объединением столбцов условий или пользовались query / dsum).

Сейчас, когда в Таблицы (и в Excel) пришли новые lambda-функции мы можем заставить классический sumifs работать в массиве.

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

Формула:
=MAP(A2:A7;B2:B7;LAMBDA(имя;товар; SUMIFS(D2:D7;A2:A7;имя;B2:B7;товар)))

Разберём:
– Передаем в map два столбца
– Передаем в lambda эти ячейки как "имя" и "товар"
– Пишем sumifs с суммированием столбца "общие продажи", передавая по каждой строке аргументы, которые мы задали

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

А так мы можем зашить формулу в заголовок:
={"Общие продажи" ; MAP(A2:A7;B2:B7;LAMBDA(имя;товар; SUMIFS(D2:D7;A2:A7;имя;B2:B7;товар)))}

Про новые функции:
Накопительный итог построчно
Подсчёт значений построчно
JOIN построчно

---
⭐️ Наш курс по Excel, Таблицам и скриптам: тыц
⭐️ Оглавление канала: ты-дыц
⭐️ Самый табличный чат на свете: бадабум

Возможность зафиксировать время (на небольшой срок) с помощью формулы

Друзья, функция NOW() возвращает текущую дату и время. При любом изменении в Таблице функция будет обновляться и показывать актуальное время.

Недавно пытливые умы в нашем чате нашли способ, с помощью которого можно заставить функцию остановиться (но не навсегда):
=LAMBDA(z;IF(B2;z;))(NOW())

Проверяем, активирован ли чекбокс в B2 и если да, то выводим текущую дату и время. Весь секрет в LAMBDA, как вы понимаете.

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

💬 Будете пробовать – напишите в комментариях, когда функция обновилась у вас и что послужило причиной, если сможете отследить.

Фиксируем время навсегда
Ну а намертво зафиксировать время можно с помощью скрипта onEdit(), сделаем его по той же логике:
function onEdit(e) {
if (e.range.getColumn() == 2 && e.value == 'TRUE') {
e.range.offset(0, 2).setValue(new Date());
}
}