Совместная работа в Excel (в Microsoft 365)

Можно ли колдовать с данными вместе в режиме онлайн в Excel, как в Таблицах?

Можно, но нужна подписка Microsoft 365 🤯 И тогда доступны представления (sheet views, своего рода индивидуальные фильтры, как filter views в Таблицах), ветки комментариев, работа над книгой в браузере в Excel Online (но там функционал ограниченный, хоть и обновляется регулярно).

Чтобы работать над документом одновременно с другими пользователями, рабочую книгу Excel нужно хранить онлайн. Это предполагает облачное хранилище, а именно OneDrive, OneDrive for Business или SharePoint Online (в перспективе к списку может добавиться Dropbox).

Подробности - в этом фрагменте из книги "Магия таблиц" - ну а ее выход ожидается через несколько месяцев, а пока можно подписаться о уведомление о выходе тут:
https://www.mann-ivanov-ferber.ru/books/magiia-tablic/

PS А еще мы активно занимаемся обновлением нашей книги про Google Таблицы. Внутри обязательно будет материал и про новые формулы и про Google скрипты. Напишем вам, когда она будет доступна.

WB, достаём топ-100 товаров по запросу

Друзья, продолжаем тему про полезное для продавцов на сайте WILDBERRIES.

Недавно в нашем чате поделились ссылкой, с помощью которой можно достать топ-100 товаров по вашему поисковому запросу:
https://search.wb.ru/exactmatch/ru/common/v4/search?appType=1&curr=rub&dest=-1257786&lang=ru&locale=ru&query=запрос&resultset=catalog

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

1) В ячейку B1 вводите свой запрос (например, веселые костюмы).

2) Запускаете скрипт из меню 🔥 и в Таблицу вставятся результаты запроса.

Одна строка - один товар. Сортировки нет, все вставляется так, как было в полученном объекте.

Мы вставляем все поля, кроме "__sort", "ksort", "time1", "time2", "dist", непонятно, что они означают, если что-то полезное - расскажите и мы их добавим.

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

PS Друзья, если знаете другие полезные ссылки для маркетплейсов - напишите в комментариях 😎

Еще наше:
Обращаемся к внутреннему API WB, получаем остатки на складах и немного информации по артикулам

Непечатаемые символы в Документах

Анонс был давно, и вот наконец подвезли!

Вид - Показывать непечатаемые символы
View - Show non-printing characters
Ctrl + Shift + P

И наслаждайтесь - возможно, именно этого вам не хватало в Документах🤠

Вводите собачку (@) в ячейке (без знака "равно", это не формула) - и получите возможность быстро вставить дату - как значение, а не формулы с функциями ТДАТА / NOW или СЕГОДНЯ / TODAY. Выбор такой - любая дата (откроется календарь для выбора даты), сегодня-вчера-завтра.

Можно щелкнуть по варианту в списке - как на скриншоте.
Можно сразу вводить "@сего..." и потом нажимать Tab, как при вводе формул.

Для вставки сегодняшней даты как значение можно использовать и сочетание клавиш Ctrl + ;.

Еще один новый трюк со старой собачкой (@), пользу которого можно обсуждать. Но вдруг кому-то пригодится: валюта и акции. Вводите валютную пару или тикер, и будет вам чип, при наведении курсора на который появится плашка с информацией. Как GOOGLEFINANCE, но не функция. То есть такими значениями можно оформить таблицу, в которой разные валюты или акции, если хочется. Функция GOOGLEFINANCE на такой значение с собачкой может ссылаться.

Редактируем Google Форму прямо из Таблицы!

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

Как всё работает - на гифке, гифка сегодня со звуком.

Что происходит после запуска скрипта в Таблице:
1) в четвертой строке листа "🔥" Таблицы - вопросы, в пятой строке - тип каждого вопроса, под каждым вопросом и типом - варианты ответа, в ячейке "🔥!B2" - форма, которую будем редактировать
2) запускаем скрипт кликом на кнопку и код ищет каждый вопрос по названию в Google Форме
3) если вопрос найден - то скрипт обновляет варианты ответа
4) если вопрос не найден - скрипт добавляет этот вопрос в Google Форму с заданным типом и вариантами ответа

Типы вопросов, с которыми умеет работать скрипт:
CHECKBOX – выбираем ответ на вопрос чекбоксами, можно выбрать несколько вариантов
LIST – выбираем ответ из выпадающего списка
MULTIPLE_CHOICE – выбираем ответ в переключателе

Чтобы воспользоваться Таблицей - копируйте себе

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

Суммируем с условием только видимые строки

Просто суммировать (а также считать среднее и еще несколько базовых операций) скрытые строки - это функция SUBTOTAL / ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

А сумма с условием — это SUMIFS / СУММЕСЛИМН. Эта функция считает не скрытые, а все.

Так что ни одна из них "в чистом виде" тут не поможет: одна будет обрабатывать видимые строки (SUBTOTAL), другая суммировать по условию (SUMIFS). Нам надо совместить. Это можно сделать со вспомогательным столбцом и без, разными способами. Разбираем два варианта в этом посте!

Подробнее про SUBTOTAL писали здесь.

Статья от нашего подписчика Дмитрия @Mityayka1: про таблицы, про карты и про скрипты: dzen.ru/a/ZCDzfftbZQoEyekf

Самое часто встречающееся текстовое значение

Самое частое число — это мода. Функция МОДА / MODE.

А как найти самое часто встречающееся текстовое значение?
Бен Коллинс в своей книге предлагает решение через MATCH (теперь можно и XMATCH, которая просто по умолчанию ведет точный поиск без обязательного третьего аргумента = 0)

Ищем каждое значение в списке и получаем массив из чисел с порядковым номером каждого элемента (номером той строки, в которой впервые встречается значение). Если диапазон включает пустые ячейки, добавьте IFNA, чтобы заменить ошибки.
IFNA(XMATCH(B2:B;B2:B);)

Потом ищем самое частое число в этом диапазоне:
МОДА(IFNA(XMATCH(B2:B;B2:B);))

И извлекаем по этому порядковому номеру текст ИНДЕКСом / INDEX:
=ArrayFormula(ИНДЕКС(B2:B; МОДА(IFNA(XMATCH(B2:B;B2:B);))))

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

Импорт данных из всех Google Таблиц в списке с помощью формул

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

Решение: пробегаемся по массиву ссылок, и импортируем IMPORTRANGE данные из каждого, последовательно собирая в один массив с помощью REDUCE и LAMBDA. В статье — несколько вариантов формул.

https://teletype.in/@renat_shagabutdinov/IMPORT-LAMBDA

Подборка наших постов про onEdit

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

Ниже подборка скриптов onEdit, про которые мы писали на канале:
1. Выводим в ячейку A1 каждого листа дату и время последнего редактирования: https://t.me/google_sheets/157

2. Если редактируете второй столбец и вводите в нём слово "оплачено" - скрипт автоматически вставляет в соседний столбец дату и время https://t.me/google_sheets/171

3. Простой onEdit скрипт для связанных выпадающих списков: https://t.me/google_sheets/313

4. Скрипт onEdit(), реагирующий на изменения и отправляющий письма (или триггер в триггере) https://t.me/google_sheets/418

5. Небольшая подборка небольших скриптов onEdit: https://t.me/google_sheets/432

6. Храним данные в Properties и обращаемся к ним. Аналог функции ВПР: https://t.me/google_sheets/483

7. Простой onEdit() скрипт переноса строки: https://t.me/google_sheets/533

8. Пытаемся разобраться в механике onEdit: https://t.me/google_sheets/746

9. Создаём в Таблице триггер и скрипт, который не увидят редакторы и владелец: https://t.me/google_sheets/753

10. Логируем все изменения в Таблице: https://t.me/google_sheets/773

11. Простейший onEdit скрипт накопления с комментариями https://t.me/google_sheets/1061

Получаем из ссылки на Google Диске прямую ссылку на скачивание

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

Формула:
= "https://docs.google.com/uc?export=download&id=" & REGEXEXTRACT(A3;"[-\w]{25,}")

Если сделаете ссылку аргументом IMAGE, то получите изображение в ячейке, как на скриншоте.

Еще:
Формулой загружаем изображения из Яндекса / Google

IMPORTXML: загружаем изображения с веб-страницы

Формируем размеры изображений пропорционально их значениям

Аккаунт Google Диска по умолчанию

Друзья, наверняка у многих из вас несколько аккаунтов на Google Диске. Например, рабочий в Google Workspace и личный.

В ссылке на Google Диск есть номер аккаунта: ноль будет у аккаунта по умолчанию, а далее 1, 2 для последующих.

https://drive.google.com/drive/u/0/my-drive
А значит, можно добавить отдельные ссылки на разные аккаунты на панель закладок в браузере, например.

А еще аккаунт по умолчанию — это тот, в котором создаются новые документы, когда вы используете быстрые ссылки
Таблица: sheet.new / sheets.new
Документ: docs.new / doc.new
Форма: forms.new / form.new
Презентация: slide.new / slides.new
Новая встреча в Google Календаре: cal.new
Новая встреча в Meet: meet.new
Заметка: keep.new

Что если вы хотите изменить аккаунт по умолчанию?
Выходите из всех аккаунтов. Далее входите сначала в тот, который сходите сделать аккаунтом по умолчанию. А потом — в остальные.

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

1 мая выходной, а дальше?

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

Есть отличный сайт, на котором есть производственные календари за каждый год: http://xmlcalendar.ru/

Чтобы загрузить все выходные дни за 2023 год пишем формулу:
=IMPORTXML("http://xmlcalendar.ru/data/ru/2023/calendar.xml";"//day/@d")

Получилось так себе – смотрите столбец A на скриншоте, причина в том, что Таблицы неправильно преобразовали наш результат.

Добавим формул, чтобы получить правильные даты:
1) поменяем в том, что выводит IMPORTXML точки на запятые
2) загрузим результат в BYROW, LAMBDA и с помощью REGEXEXTRACT достанем месяц (до запятой), день (после запятой), добавим год и превратим функцией в дату.

Итоговая формула:
=ARRAYFORMULA( BYROW( SUBSTITUTE( IMPORTXML(A1;""//day/@d"");""."";"","");
LAMBDA(a; DATE(2023; REGEXEXTRACT(a;""(.+),""); REGEXEXTRACT(a;"",(.+)"")))))

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

API Wildberries – загружаем остатки FBS

Друзья, привет! Мы уже писали о том, как загрузить в Таблицу остатки и цены по любым артикулам, обращаясь к внутреннему API WB. Также писали про то, как выгрузить ТОП-100 товаров по вашему запросу.

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

С помощью сегодняшней Таблицы вы сможете получить свои остатки. Для работы вам нужно получить API-ключ в личном кабинете WB, скопировать нашу Таблицу и вставить API-ключ в редактор скриптов.

Для инфо, что происходит в коде:
1) Формируем ссылку вида https://statistics-api.wildberries.ru/api/v1/supplier/stocks?dateFrom=2023-04-30, где 2023-04-30 – сегодняшняя дата
2) Выполняем GET-запрос этой ссылки, в заголовках отправляя наш API-ключ (в коде за это отвечает функция Request)
3) Получаем результат в виде строки, превращаем в объект, далее в цикле проходим по этому объекту и делаем из него массив, годный для вставки в Таблицу, добавляем перевод заголовков
4) Очищаем лист и вставляем результат, это делаем с помощью sheets api (так получается быстрее)
5) Если на любом этапе проблемы, то скрипт завершится и на листе "ошибки" вы прочитаете ошибку

Таблица с кодом
Документация

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

PPS Дальше расскажем как загрузить заказы, продажи, отчёт по реализации из API, и что-нибудь еще, оставайтесь с нами :)

Друзья, собрали для вас всякую пользу про функции для вычислений с условиями: SUMIF(S), AVERAGEIF(S), COUNTIF(S), COUNTUNIQUEIFS, MAX/MINIF(S).

Про условия (прикрепляем также картинку с примерами):
— Регистр в условиях не учитывается.
— Важно, чтобы все диапазоны условий и диапазоны суммирования/усреднения были одинаковой размерности. Это могут быть и столбцы целиком (E:E), и диапазоны (E2:E40), и открытые диапазоны (E3:E). Например, если один аргумент — это столбец целиком (D:D), то и другой должен быть в таком же формате (такого же размера — E:E, а не E2:E120, например).
— Диапазоны могут быть со смещением (например, суммируем E2:E50, а условие ищем в D3:D51). Тогда при соответствии ячейки в столбце D условию суммироваться будет число из строки выше. Если вдруг применяли такое (или придумали, где применить, когда узнали пару секунд назад) — напишите в комментариях!
— Условия можно вводить в кавычках внутри функции — любые текстовые значения в формулах вводятся в кавычках. Либо ссылаться на ячейки, где хранится текст условия.
— В условиях можно использовать символы подстановки (* — любой текст любой длины, в том числе нулевой; ? — один любой символ). Например, "*сайт*" — это ячейка со словом "сайт" и любым другим текстом до и после, а не только ячейка со словом "сайт".
— В условиях можно использовать знаки сравнения (<, >, <=, >=, <> — "не равно"). Например, "<>Москва" — все, кроме ячеек, в которых текст "Москва".

Еще польза по теме:
Примеры условий с символами подстановки

Памятка по условиям в FILTER, QUERY и SUMIF

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

Телеграм + Google Таблицы, наши решения

Друзья, привет. Мы с 2017 года ведём наш канал в Телеграм и иногда публикуем свои скрипты, которые с этой платформой взаимодействуют. Все посты – готовые и бесплатные решения, которые можно скопировать, прочитать инструкцию и начать использовать.

Простой Телеграм-бот (гифка установки), логирует все, что ему пишут в Google Таблицу, также из Таблицы можно отправлять сообщения в Телеграм-чаты и каналы

Еще один Телеграм-бот, извлекает из Таблицы данные по запросу и отправляет в Телеграм

Отправлятор 2, отправляет по расписанию диапазоны из Таблицы в Телеграм (pdf / jpg)

Регулярный бэкап Таблиц в формате XLSX в Телеграм

ВЫГРУЖАТОР, достаёт скриптом количество подписчиков чатов и каналов в Телеграм

Скрипт внутри Google Формы,
— берёт последний ответ и отправляет его в Таблицу
— отправляет в Телеграм чат
— отправляет на электропочты

---
⭐️ Оглавление канала: ты-дыц
⭐️ Самый табличный чат на свете: бадабум

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

Ну а COUNTUNIQUEIFS позволяет считать уникальные значения с условиями — например, посчитать, сколько клиентов приобретали у нас консультации.

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

Обновление нашей Таблицы WB: загружаем отчет по реализации из API

Продолжаем разговор и продолжаем добавлять полезное в нашу Таблицу WB.

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

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

❗Обратите внимание, WB вернёт вам ошибку, если запросите слишком большой диапазон дат, да и в Таблицу вы не сможете вставить слишком много.

Наша Таблица WB (уже умеет загружать остатки FBO и отчёт по реализации)

Краткое описание работы скрипта

Документация метода

Если у вас есть интересные решения с WB, которыми можно поделиться - поделитесь в комментариях. Ждите обновлений и они будут :)