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

Сегодня - две книги-сборника отдельных лайфхаков: одна по 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);
};

Бен Коллинс в одной из последних рассылок пишет про идею 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))

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

Помните, сколько раньше было проблем с объединением массивов с разным количеством строк / столбцов? (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());
}
}

Переносим диапазон внутри Таблицы

Как написать формулу, которая перенесет данные из одного листа Таблицы в другой?

1) Выбираем диапазон, который переносим
2) Оборачиваем его в фигурные скобки (символы массива)
={'Лист1'!A1:B}

Еще один вариант - сослаться на диапазон и нажать Ctrl+Shift+Enter, тогда будет введена функция ArrayFormula. Она будет работать вместо фигурных скобок.
Можно ввести ее и вручную.
=ArrayFormula('Лист1'!A1:B)

А вот излишние манипуляции с массивами в формулах, которые мы иногда наблюдаем в разных таблицах:
- Не нужно тянуть данные с другого листа с помощью IMPORTRANGE! Это функция для импорта из других таблиц.
=IMPORTRANGE("Ссылка на ту же таблицу, где формула"; "'Лист1'!A1:B") - такая конструкция вместо того, что выше - это лишнее.

- Не нужно добавлять фигурные скобки к диапазонам, если они внутри функций, которые и так предполагают диапазон. Например:
=FILTER({A1:E1000}; {A1:A1000} = "...")

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

Вафельная диаграмма

- Заполняем диапазон 10 на 10 значениями от 1 до 100%;
- Добавляем для него условное форматирование: если значение меньше либо равно, чем текущий статус (% выполнения чего-нибудь, например) - то заливаем цветом. И цвет шрифта надо сделать таким же цветом, чтобы у вас значения не "проглядывали" в этих самых квадратиках🤠

Заполнить диапазон можно через SEQUENCE. Правда, она отказывается работать с шагом, когда это не целое число, но можно сделать так - получить числа от 1 до 100 и поделить их на 100.
=ArrayFormula(SEQUENCE(10;10;1;1)/100)

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

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

Мы поздравляем прекрасных девушек с праздником! 👩🌸

PS А у кого получится все сделать только внутри условного форматирования? Пишите в комментариях.

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

(Таблица со скриптом, которая обращается к внутренней API WB и загружает информацию по товарам)

Привет, друзья. Ко мне в личку обратились с тем, что если выбирать слишком много артикулов (больше 100-200), то скрипт не работает.

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

В редактор скриптов добавлен шаг (строка 1, показатель step), это количество артикулов, которые могут быть в одном запросе.

Сейчас шаг 100 и это значит, что если у вас будет 550 артикулов, то скрипт сделает 6 запросов по 100, соединит результаты и вставит их на лист.

Помимо этого в колонку "q" мы добавили общее количество артикулов на всех складах (получаем его из столбца stocks).

Пользуйтесь, пока ВБ окончательно не забанил гугловские IP 🙂

Обновленная Таблица

Флажки можно включать и отключать пробелом

Вот что бы мы делали без Бена Коллинса и его статей/рассылок? Точно бы не узнали, что можно тру-фолсить флажки (переключать в выделенных ячейках состояние флажков, то есть менять значения в ячейках с ними с ИСТИНА / TRUE на ЛОЖЬ / FALSE и наоборот) нажатием пробела!

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

А вы знали?😺