Google Таблицы

Заставляем IMPORTXML работать

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

Победить это можно с помощью скрипта из этого поста.

Работает скрипт так:

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

2) Запускаете скрипт из меню, скрипт собирает функции IMPORTXML из выделенного диапазона и вставляет функции в соседний столбец

3) После вставки результат сразу заменяется на значения

4) Вставка происходит партиями по 20 функций, за это отвечает параметр step в коде (можете с ним поиграть и выставить другое значение).

Код
Таблица с кодом

Чего нам не хватало в Excel

Ну некоторых чудо-функций Google Таблиц. Например, FLATTEN или SPLIT.
И вот - 14 новых функций Excel. Огненных 🔥
А именно:

TEXTSPLIT / ТЕКСТРАЗД - это как SPLIT, только умеет и вертикально, и горизонтально выкатывать полученные текстовые значения. А еще до кучи TEXTBEFORE / ТЕКСТДО и TEXTAFTER / ТЕКСТПОСЛЕ, извлекающие текст до/после разделителя.

TOROW / ВСТРОКУ и TOCOL (ВСТОЛБЕЦ, вероятно? Пока даже в русском Excel - TOCOL), как табличный FLATTEN, делают массив плоским - в одну строку/столбец соответственно.

VSTACK и HSTACK склеивают несколько массивов в один вертикально или горизонтально.

WRAPROWS и WRAPCOLS делают плоский массив двумерным.

TAKE / ВЗЯТЬ и DROP / СБРОСИТЬ извлекают первые/последние номера строк из массива.

CHOOSEROWS и CHOOSECOLS извлекают заданные (по номерам) строки или столбцы из массива. То есть можно вытащить, например, 1 и 5 столбец. Или вытащить весь массив, поменяв порядок столбцов.

EXPAND / РАЗВЕРНУТЬ увеличивает размерность массива, добавляя заданные значения к исходному массиву (например, нули или пустые строки).

Функции уже выкатили на подписчиков бета-канала обновлений. Заходите в параметры Excel, если у вас подписка Microsoft 365, и включайте в программе предварительной оценки Office бета-канал.

Чего нам не хватало в Таблицах

Ну ячеек иногда. И их наконец подвезли! Хотя 10 миллионов ячеек на одну книгу (таблицу) в качестве ограничения мелькали в некоторых аккаунтах уже давно, это было тестированием, вероятно. А теперь официально.

Разбираем ваши вопросы, пришедшие на форму.

Много вопросов про потенциальную блокировку сервисов. Друзья, у нас с вами, увы, хрустального шара нет. Потенциальные сценарии и возможные альтернативы (например, р7) уже несколько недель обсуждаются в чате – заглядывайте! Про бэкап мы уже написали (здесь и здесь). Перестраховаться никогда не лишне.

Как настроить изменение правила проверки данных (значение из диапазона) на основе вводимой информации в соседней строке.
Используйте скрипт "связанные списки из кэша" - вот ссылка.

Какую функцию использовать для изменении в колонке «Продажа».
При продаже цена должна подтягиваться с таблицы «прихода», а если была переоценка, то тогда цена берётся с таблицы «переоценка»
Раз в вопросе (если мы его верно поняли) есть слово ЕСЛИ (IF), то и в формуле оно может пригодиться. Можно ВПР-ить (VLOOKUP'ить) переоценку, и если ВПР выдаст пустоту, то ВПР-ить уже таблицу с приходом.
Можно функцией МАКС брать максимальное значение из двух ВПР, если переоценка всегда в большую сторону (но давайте не будем о грустном, о росте цен).
Так или иначе, варианты по ссылке.

Имеется таблица в которой строки повторяются но отличаются только по одному значению. Необходимо удалить все повторяющиеся строки, при это оставить уникальные строки в которых одна ячейка больше всех среди повторяющихся. Как это сделать? Заранее спасибо!

Если правильно поняли вас и одна ячейка больше = та ячейка, где больше текстовых пунктов, начинающихся на "-" (дефис), то можно подсчитать количество пунктов через COLUMNS(SPLIT("-";...)). Можно и по количеству переносов строки SPLIT(CHAR(10);...)

А потом отсортировать по этому столбцу SORT и оставить уникальные UNIQUE.
Пример тут.

Как правильно импортировать с Листа1 в ячейку "успешные" по сервису? буду очень благодарен!
Ничего не поняли наверняка, но похоже, что нужна функция FILTER - по названию продукта. Если имен будет несколько, в одну текстовую строку результат FILTER можно склеить с помощью TEXTJOIN.

На iOS ссылки на определенные листы в книге работают не так, как ожидалось. С ПК при нажатии на гиперссылку открывается соответствующий лист. С телефона - открывается первая страница в книге.
Эх 😞

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

Друзья, недавно сделали решение для нашего клиента и с его разрешения делимся с вами. Спасибо, Равиль!

Умная Таблица работает так:

1) вставляете ссылки на Таблицы, бэкап которых вы хотите сделать, в столбец A:A
2) далее открываете редактор скриптов и заполняете:
— chatId – чат, в который будут отправляться бэкапы Таблицы, чат может быть как личным, так и общественным, чтобы узнать chatId - используйте @myidbot в телеграм
— botToken - токен бота, который будет отправлять таблицы, чтобы зарегистрировать бота и получить его токен - используйте @botfather
— hours - часы отправки через запятую
— далее запускаете из меню 🐞 скрипт "создать триггер на каждые 15 минут"

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

Таблица и скрипт

Как сделать бекап Google Диска

1. Самый простой и правильный способ забекапить данные
https://takeout.google.com/

2. А также – нажатие на кнопку Download / Скачать в контекстном меню по щелчку на папке Google Диска создаёт ZIP-архив с файлами (при этом файлы редакторов Google будут преобразованы в форматы Microsoft Office, например, Google Таблицы в XLSX) и скачивает его на ваш компьютер.

3. А еще – вот здесь мы писали о скрипте, с помощью которого можно сохранить лист Таблицы в выбранном формате (PDF / CSV / XLSX).

Задавайте вопросы!

Если у вас есть вопросы по Таблицам – вы их можете задать через форму.

Формулируйте вопрос, прикладывайте ссылку на Таблицу, открытую на просмотр и напишите свой ник в Телеграм.

На самые интересные вопросы будем отвечать в чате и на канале.

Задать вопрос: docs.google.com/forms/d/1QLVp4CM8mMsT3ZwkDlpdjbr5B20tCMWU2l7DjA5BlcQ/edit

На заметку: нажатие на кнопку Download / Скачать в контекстном меню по щелчку на папке Google Диска создаёт ZIP-архив с файлами (при этом файлы редакторов Google будут преобразованы в форматы Microsoft Office, например, Google Таблицы в XLSX) и скачивает его на ваш компьютер.

А вот здесь мы писали о скрипте, с помощью которого можно сохранить лист Таблицы в выбранном формате (PDF / CSV / XLSX).

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

Функции:
​​— Учимся извлекать из текста нужное. База знаний регулярных выражений для REGEXEXTRACT →→
— Памятка по синтаксису QUERY / FILTER / SUMIFS →→
— Функция FILTER. Список условий выбираем диапазоном прямо с листа →→
— Обоюдоострый FILTER. Убираем с помощью одной формулой из таблицы пустые строки и столбцы →→
— SUMIF для нескольких условий в формуле массива →→
— Формулой достаем изображения из поисковой выдачи Яндекса →→
​​— ЛЕВЫЙ ВПР (когда искомое значение не в первом столбце) →→
— ВПР в массиве вместо тысячи CУММЕСЛИМН (заполняем весь лист одной формулой) →→
— Автоматически создаем фразы по определенным шаблонам в Google Таблицах →→
— Пользовательские числовые форматы, добавляем к числам 🔥🔥🔥 →→

Скрипты:
— Скрипт. Распознаем текст на изображениях. OCR в Google Docs →→
— Простой скрипт копирования / фильтрации (поможет, когда IMPORTRANGE перестает работать) →→
— Скрипт onEdit(), реагирующий на изменения и отправляющий письма (или триггер в триггере) →→
— Скрипт, загружаем письма вашего GMAIL-аккаунта в Таблицу →→
— Скрипт, отправляем письма на электропочты из диапазона, который вы выделяете →→
— Простой скрипт для ежедневной рассылки из Google Таблицы →→
— Важный скрипт. Связанные выпадающие списки из кэша →→

Проекты:
— Telegram bot + Google Sheets (используя вебхуки) →→
— Эмоджи форматирование выполнения плана (IMAGE, IFS) →→
— Создаем красивое расписание групповых занятий →→
— Создание оглавления в телеграм-канале: как автоматически загружать и сортировать публикации из вашего рабочего файла →→
— Google Форма + Google Таблица для проведения тестирования →→
— Применение IFTTT для отправки сообщений с данными из Таблиц →→
— Googlefinance, Парето и графики (#готовое решение) →→
— Создаем инфографику с помощью IMAGE. Размер картинок пропорционален значениям →→
— Импортируем таблицу из веб-страницы и оставляем только нужные нам столбцы (IMPORTHTML+QUERY) →→

Для новичков:
— Мы на Хабре: "Функции Таблиц, которых нет в Excel": https://habrahabr.ru/post/331360/
— Советы по оптимизации Таблиц →→
— Видеоурок по функции IMPORTRANGE (для связывания нескольких таблиц) →→
— Видеоурок по фильтрам и режиму фильтрации →→
— Про виды доступа к документам →→
— Совместная работа с фильтрами →→

Полное оглавление нашего канала: https://goo-gl.ru/5Kc3
Наш чат: @google_spreadsheets_chat

Друзья, повторяем нашу подборку полезных постов:

Рубрика “Чего нам не хватало в Excel”

Теперь в Excel — увы, только у подписчиков Microsoft 365 — появился аналог GOOGLEFINANCE.
Функция называется STOCKHISTORY / ИСТОРИЯАКЦИЙ.

Аргументы:
stock / акции — это тикер (например, "GOOG") или коды валютной пары (например, "USDRUB" или "BTCRUB"). В кавычках или в виде ссылки на ячейку с текстом.
start_date и end_date / нач_дата и кон_дата — период, за который загружаем данные.
interval / интервал — периодичность. 0 = ежедневно(по умолчанию), 1 = еженедельно, 2 = ежемесячно.
headers / заголовки — по умолчанию 1 (1 строка с заголовками, дата и название параметра), еще есть варианты 0 — без заголовков и 2 — две строки, где первой будет название тикера / валютной пары.
свойства1; свойства2;…; свойства5 — с помощью этих аргументов мы набираем те параметры, которые будут возвращаться, в желаемом порядке. Вот набор параметров:
0 — Дата
1 — Закрытие (Close)
2 — Открытие (Open)
3 — Высокая (High)
4 — Низкая (Low)
5 — Объем (Volume)

Например, в следующем случае будeт выводиться дата (0), объем (5) и цена закрытия (1):

=ИСТОРИЯАКЦИЙ("GOOG";...;0;5;1)

По умолчанию будут дата и цена закрытия.

Функция будет возвращать динамический массив. Если ей не хватит места для возвращения результата — будет ошибка #SPILL! / #ПЕРЕНОС!

Подробнее про источники данных для функции

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

ЗАЩИЩАЕМ СКРИПТЫ ОТ РЕДАКТИРОВАНИЯ

Рубрика "нам пишут" Александр Трифонов пришел в комментарии к последнему посту и написал, что любой редактор может зайти в код скрипта и поменять скрипт так, как захочет.

Рассказываем Александру и другим как защитить свои скрипты от редактирования.

Способ первый – библиотека

Уносим скрипты в библиотеку, открываем библиотеку для всех на просмотр, подключаем библиотеку к Таблице и оставляем в редакторе скриптов Таблицы только функцию для вызова функции из библиотеки:

function something(){
lib.something();
}

Что такое библиотеки и как с ними работать отлично написал Миша Смирнов: t.me/google_sheets/828

Способ второй - "inject", подходит для скриптов, которые можно положить на триггер

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

Способ третий - развернутое приложение

1) переносим скрипты в другую таблицу / другой отдельный скрипт;
2) пишем там doGet функцию, которая будет принимать на вход название скрипта, разные аргументы, сами скрипты убираем в объект (смотрите в примере кода);
3) разворачиваем всё это как приложение, получаем ссылку приложения;
4) пишем функции в таблице, в которой вы хотите запускать скрипты, функции должны будут отправлять на адрес вашего приложения (пункт 3) названия скрипта, который надо запустить, а также аргументы, которые в этот скрипт надо передать (смотрите в примере кода, мы отправляем ключ и id таблицы);
5) всё! пользователь в Таблицы выбирает в меню своей Таблицы пункт, маленькая функция в Таблице отравляет на адрес развернутого приложения имя функции, которую нужно запустить и параметры. Далее приложение от имени того, кто его развернул запускает требуемый скрипт с переданными в него параметрами;

Примерный код: pastebin.com/TnBazuut

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

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

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

Мы написали для вас скрипт и прокомментировали в нем каждую строку с кодом: pastebin.com/fACHx817

Внутри три функции:

from_edit_to_view — меняем права всех редакторов документа (кроме себя) с редактирования на просмотр;

from_view_to_edit — меняем права всех пользователей с просмотром на редактирование;

main — главная функция, определяет текущее время и запускает или первую или вторую функции;

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

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

Флаг вам в руки — суммируйте что хотите. SUMIFS с флажком

Задача: вставить в таблицу флажок, который будет включать или отключать один из критериев функции СУММЕСЛИМН / SUMIFS (ну или ее аналогов для подсчета/усреднения COUNTIFS, AVERAGEIFS, COUNTUNIQUEIFS).

Вставляем флажок и добавляем функцию IF / ЕСЛИ, которая в соответствующем аргументе будет возвращать условие, если флажок включен, и “*”, если флажок выключен. Так как звездочка — символ подстановки (wildcard), соответствующий любому тексту, то одна звездочка = любое значение = отсутствие всякого условия для соответствующего столбца.

=SUMIFS(F:F;B:B;IF(I4;J4;"*"))

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

=SUMIFS(F:F;B:B;IF(I4;"*"&J4&"*";"*"))

Понятное дело, таким образом и несколько флажков можно сделать для разных критериев — как в примере по ссылке (создать копию).

P.S. Благодарим функцию RANDBETWEEN / СЛУЧМЕЖДУ за предоставленные (сгенерированные случайно) данные

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

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

Статья: telegra.ph/Programmno-dayom-dostup-dlya-IMPORTRANGE-k-drugoj-tablice-07-25

Скрипт из статьи (даём доступ к трём таблицам): pastebin.com/WfgqTEKD

Рубрика "Это не помешало бы нам в Таблицах"

В Excel есть два приема для обработки нескольких листов в формулах. Надеемся, что когда-нибудь они доберутся и до Таблиц :)

Первый аспект известен многим - возможность ссылаться на несколько листов через двоеточие. Следующая формула суммирует числа из ячеек A1 на листах от Москвы и до Саратова включительно:

=СУММ('Москва:Саратов'!A1)

"От и до" значит, что эти листы (их ярлыки) идут слева направо в книге. Если какой-то из них переместится за пределы этого набора листов - ячейка с него перестанет вычисляться. И наоборот, если после Москвы или до Саратова появится новый лист - он попадет в вычисление.

А второй аспект - возможность применять символ подстановки * в ссылках на листы.
Допустим, у вас в книге много листов со словом "Расходы" в названии ("Расходы январь", "Расходы февраль", ... ). Следующая формула позволит просуммировать ячейки A1 со всех этих листов:

=СУММ('Расходы*'!A1)

Правда, в отличие от ссылки с двоеточием, звездочка в формуле не сохранится - после ввода такой формулы ссылка на лист со звездочкой превратится в формулу с отдельными ссылками:

=СУММ('Расходы январь'!A1;'Расходы февраль'!A1;'Расходы март'!A1;...)

Но все же!

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

Случайный подбор и перестановка
– памятка от Михаила Смирнова

1) Выбор случайного значения из списка
2) Выбор k-случайных значений из списка с повторениями
3) Выбор k-случайных значений из списка без повторений
4) Случайная перестановка списка

Таблица c примерами от Михаила

Функции для генерации случайных чисел волатильные, то есть пересчитываются при любых изменениях. Поэтому результаты вычисления формул с ними пересчитываются. Если вам нужно их зафиксировать — скопируйте и вставьте как значения (Ctrl+Shift+V).

⭐️ Стать патроном нашего канала
⭐️ Оглавление нашего канала: goo.gl/HdS2qn

Видео: Пользовательские числовые форматы в Google Таблицах

Для тех, кто предпочитает формат видео - разбор основных правил создания пользовательских числовых форматов (Custom number formats) в Google Таблицах (в Excel все похоже, есть небольшие нюансы - о них в нашей таблице со сравнением Excel и Таблиц).

Таблица из видео (создать копию)

Еще по этой теме:
Статья
Памятка по применяемым символам