Собираем данные с разных листов в Excel и Google Таблицах (список листов - динамический)

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

Чтобы обработать несколько листов и с каждым проделывать какие-то манипуляции, будем забирать названия листов из массива (можно взять с запасом, например, A2:A20, а можно вообще открытый A2:A, если не знаете даже примерно, сколько листов может быть - но в последнем случае формулы могут подтормаживать) с помощью MAP и передавать в LAMBDA, где будет нужное нам вычисление.

В заметке разбираем по шагам + вспоминаем, что в Excel в любых версиях (а в старых, в отличие от Таблиц и новой версии, LAMBDA нет) можно ссылаться на пачку листов или использовать символ подстановки в названиях листов (правда, только один раз 🤷🏻‍♂️).

А ранее мы писали, как собирать данные с нескольких листов с помощью пользовательской функции (скриптами).

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

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

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 🙂

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

новая колонка "q" - общие остатки, её получаем их столбца "stocks", остатки на всех складах

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

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

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

А вы знали?😺

Извлекаем числа, едим пончики

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

"выпито 580 литров молока", вытаскиваем из строки число 580.

Берём функцию REGEXREPLACE, функция позволяет заменять то, что соответствует регулярному выражению на текст.

Заменим все символы, которые не являются цифрами 0-9 (\D+) на пустоту. И умножим результат на 1, чтобы привести его к числу:
=REGEXREPLACE(A1;"\D+"; ) * 1

"выпито 580 литров молока и съедено 10 пирожков", вытащим 580 и 10 в отдельные ячейки.

Сначала, с помощью REGEXREPLACE заменим все "не числа" на пончик, потом с помощью SPLIT (делит строку по указанному разделителю) разделим нашу строку по этому пончику:
=SPLIT(REGEXREPLACE(A2;"\D+";"🍩") ; "🍩")

🍩 уйдут, а числа останутся и встанут в отдельные ячейки, смотрите скриншот :)

Новый интерфейс на Диске

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

Обновился вид и у комментариев в Доках. Также история версий и последнее изменение теперь в виде иконки справа.
Еще меняется внешний вид самого Диска. Добавляются опции прямо в списке файлов - иконками в правой части (см. скриншот).

Новость в блоге Google Workspace:
https://workspaceupdates.googleblog.com/2023/03/refreshed-ui-google-drive-docs-sheets-slides.html

Выводим все даты текущего месяца формулой

Тут понадобится функция SEQUENCE, которая может вернуть последовательность чисел (ищите эту прелесть в Excel 2021, Microsoft 365, Google Таблицах). У нее задается число строк, столбцов, начальное значение и шаг.
Шаг - самое простое, у нас это единица, один день.
Число строк или столбцов будет равно единице - в зависимости от ориентации списка. Если вы хотите список в один столбец (как в примере на картинке), то столбец будет один, а число строк будет определяться числом дней в месяце.
Начало - первое число месяца.

Единицы добыть легко - их есть на клавиатуре, а вот как получить остальное?

Первое число месяца - это дата, у которой день = единица, месяц текущий (функция МЕСЯЦ / MONTH вернет нам месяц СЕГОДНЯшней даты), год текущий (функция ГОД / YEAR тоже от текущей даты, которую мы получим с помощью функции СЕГОДНЯ / TODAY).
Собрать дату из трех кусочков можно функцией ДАТА / DATE. Ее аргументы - номера года, месяца, дня:
ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());1)

Последнее число месяца - это функция КОНМЕСЯЦА / EOMONTH. Первый аргумент - дата (мы зададим текущую). Второй - на сколько месяцев вперед (положительное число) или назад от нее отступить. То есть EOMONTH(TODAY();-1) - это последнее число предыдущего месяца. Нам нужен текущий, так что без отступов - второй аргумент равен нулю.
EOMONTH(TODAY();0)

Все вместе (для списка в один столбец):
=SEQUENCE(DAY(EOMONTH(TODAY();0)); 1;
DATE(YEAR(TODAY());MONTH(TODAY());1); 1)

PS Можно и начало месяца получить через EOMONTH - как последнюю дату предыдущего месяца плюс 1. Но мы решили заодно рассказать/напомнить и про функцию DATE.
EOMONTH(TODAY();-1) + 1

Совместная работа в 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