Задача от прекрасной Лизы из нашего чата.

Возьмите текст ниже, вставьте в одну ячейку Google Таблиц. Сделайте ширину столбца — 250 px, шрифт — по умолчанию, 10 pt.

Придумайте формулу, которая разобьёт текст на строки так же, как его переносит ячейка.

Пишите ответы в комментариях — за лучшие варианты начислим +25 в нашем чате.

Учиться и развиваться можно в любом возрасте: важно лишь найти дело по душе, упорно двигаться вперёд и радоваться маленьким победам. Каждый шаг открывает новые возможности и ведёт к успеху.

А чтобы финдиру стало проще, мы покажем классные лайфхаки с функцией ВПР (VLOOKUP).

— Последний аргумент ВПР или ВПР с интервальным просмотром = 1: www.tg-me.com/google_sheets/13

— ВПР с ПОИСКПОЗ в массиве: www.tg-me.com/google_sheets/78

— ВПР по нескольким диапазонам: www.tg-me.com/google_sheets/145

— Видео про функцию ВПР в Google Таблицах: www.tg-me.com/google_sheets/182

— ВПР (VLOOKUP) по нескольким условиям: www.tg-me.com/google_sheets/265

— ВПР в массиве вместо тысячи CУММЕСЛИМН: www.tg-me.com/google_sheets/355

— ЛЕВЫЙ ВПР (когда искомое значение не в первом столбце): www.tg-me.com/google_sheets/397

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

— ВПР, достаём последнее значение по ключу: www.tg-me.com/google_sheets/558

— Массивный ВПР: www.tg-me.com/google_sheets/577

— ВПР / VLOOKUP со звездочкой: www.tg-me.com/google_sheets/640

— Условное форматирование + ВПР: выделяем цветом имена сотрудников из определенного отдела: www.tg-me.com/google_sheets/925

— ВПР-им с разных листов: www.tg-me.com/google_sheets/945

— Объединяем ВПР и СУММПРОИЗВ (SUMPRODUCT): www.tg-me.com/google_sheets/253

🔥 Посты про другие функции и скрипты в оглавлении нашего канала: перейти


📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас

Поговорили про ВПР (VLOOKUP), пришло время и про ПРОСМОТРX (XLOOKUP) поговорить!

— В Google Таблицах функция доступна всем, а вот в Excel 2019 и древнее будет ошибка ИМЯ / NAME, ибо она есть только в 2021 / 365 / 2024 / Online. В Р7-Офис функция тоже есть.

— В ПРОСМОТРX есть встроенная замена ошибок (когда ничего не найдено) на любое значение — это четвертый необязательный аргумент.

— По умолчанию ищет текст, а не ближайшее число. При этом число может искать и ближайшее наименьшее, и наибольшее, и без сортировки диапазона (ВПР при интервальном просмотре требует сортировки диапазона по возрастанию).

— Если ВПР по умолчанию работает с символами подстановки (то есть * в искомом значении будет означать текстовую строку любой длины из любых символов, а ? любой символ), то ПРОСМОТРX, наоборот, воспринимает *, ? и ~ как просто символы. Чтобы они стали подстановочными знаками, нужен пятый аргумент функции, равный 2.

— В Excel с недавнего времени ПРОСМОТРX работает и с регулярными выражениями, для этого пятый аргумент должен быть равен 3. В Google Таблицах (пока?) такого варианта нет. Но, как правило, при поиске с регулярками нас интересуют все значения, а не одно (а ПРОСМОТРX, ВПР, ГПР, ПОИСКПОЗ возвращают только одно), так что для поиска всех значений, соответствующих регулярке, более актуальна конструкция:

=FILTER(диапазон; REGEXMATCH(столбец; регулярка))

— ПРОСМОТРX работает и со строками, и со столбцами. ВПР — только вертикально ,на то он и Вертикальный ПРосмотр, а не Горизонтальный (ГПР).

— ПРОСМОТРX может возвращать сразу несколько столбцов / строк (внимание на скриншот)

— ПРОСМОТРX может возвращать и первое, и последнее по порядку значение (здесь про это подробнее).

— Можно отправить в ПРОСМОТРX массив, полученный другой такой же функцией, чтобы искать и по строкам, и по столбцам — пример тут.


📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас

Экспортируем XLSX в Google Таблицу «налету» 🚀

Друзья, у нас есть очень удобный приём:
мы можем подхватить XLSX-файл прямо с Google Диска и тут же вывести его содержимое в нашу Таблицу — без скачиваний, конвертаций и промежуточных файлов.

Файл должен быть открыт по ссылке хотя бы на просмотр.

Базовая формула выглядит так:

=IMPORTDATA(A2 & "export?format=csv"; ""; "en_US")

🚨 Где A2 — ссылка на XLSX-файл (все, что в ссылке идёт до /edit или /view), открытый хотя бы на просмотр.
Формула вернёт данные с первого листа файла.

Если нужен конкретный лист — добавляем параметр gid (его можно подсмотреть в ссылке, открыв лист):

=IMPORTDATA(A2 & "export?format=csv&gid=1789128788"; ""; "en_US")

А если хотим сразу ограничить диапазон:

=IMPORTDATA(A2 & "export?format=csv&gid=1789128788&range=A1:C10"; ""; "en_US")

Что тут происходит?
1. Мы собираем ссылку с параметрами и Google сам «на лету» конвертирует XLSX в CSV через открытое API.
2. IMPORTDATA сразу подхватывает CSV и выводит в нашу Таблицу.

Такие маленькие, но мощные лайфхаки мы разбираем на нашем курсе по Google Таблицам.

Скоро открываем набор в новый поток — следите за анонсом, будет жарко 🔥

✨ Спасибо за пример Алексею Одиссею.

Друзья!

Посты на какие темы вы ждёте на нашем канале?

Пишите в комментариях :)

🟣 Открытое API сайта Wildberries

Друзья!
По ссылке

👉 https://card.wb.ru/cards/v4/detail?nm=98892471&dest=123586302&locale=ru

(номенклатур может быть несколько — перечисляем через ;)

Через этот запрос можно получить информацию о номенклатурах WB: количество, рейтинг, названия и другие данные.

⚠️ Но в последнее время видим только общее количество по номеклатуре — пропало распределение по складам. Ищем, как достать остаток FBS.

Кто знает, что дописать в ссылке, чтобы снова появилось распределение?
Подскажите — начислим 25 кармы в нашем чате 🙌

💬 Также делитесь в комментариях своими вариантами ссылок к пока ещё открытому API, уверен - многим будет полезно

🔥 Два открытых API WB в одной Таблице

Друзья, современные сайты — это HTML и JavaScript. Когда вы открываете Wildberries, JS-скрипты в вашем браузере (их вы не видите) подгружают данные и «собирают» страницу, которую вы видите. Эти скрипты можно назвать внутренними API сайта.

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

🔹 Пример первого API — ссылка, результат которой мы парсим:
https://card.wb.ru/cards/v4/detail?nm=98892471&locale=ru&dest=-1216601,-115136,-421732,123585595

🔹 И второго API:
https://basket-20.wbbasket.ru/vol3305/part330535/330535596/info/ru/card.json

Если открыть эти ссылки в браузере, вы увидите JSON-объекты — именно их наша Таблица обрабатывает и превращает в аккуратные строки.

📊 Таблица с решением

Копируйте, на листе nm вводите интересующие вас номенклатуры и запускайте скрипт из меню с 🔥 — и вся нужная информация появится прямо в вашей Таблице.

---
📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас

💱 Reduce для сбора курсов валют

Друзья, мы хотим получить курсы ряда валют к доллару (EUR, CHF, AED, UAH, RUB, TRY, CNY, OMR) за определённый период — с разбивкой по датам.

📄 Таблица / Итоговая формула:

= let(
_c;A1:H1;
_d1;A2;
_d2;B2;
reduce(;_c;LAMBDA(acc;v;ifna(hstack(acc;ifna(hstack(v; GOOGLEFINANCE("USD" & v;"PRICE";_d1;_d2));v))))))

🔹 Что делает формула:

1) Берёт список валют из диапазона A1:H1;

2) Получает для каждой курс USD→валюта за указанный период A2:B2 с помощью GOOGLEFINANCE;

3) Накапливает результат в единую таблицу с помощью REDUCE;

4) Склеивает всё по столбцам через HSTACK.

5) Итог — аккуратная сводка курсов по всем валютам в одном массиве

📅 А как извлечь курс за нужную дату из этой таблицы — покажем в следующем посте 😉

Возвращаемся к нашему посту с курсами.

1) Мы вывели по три столбца с курсом каждой валюты к доллару:

- НАЗВАНИЕ ВАЛЮТЫ
- ДАТА ЗАКРЫТИЯ
- ЦЕНА

2) Как вывести, например, курс арабского дирхама за 02/09? Таблица с примером

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

={
 "Rate";
 LET(
  _rows;A2:B20; _cur;Currency!A4:Y; _head;INDEX(_cur;1;);
  BYROW(_rows;LAMBDA(r;
    LET(
      _d;INDEX(r;1;1); _c;INDEX(r;1;2);
      IF(_c="";;
        LET(
          _n;MATCH(_c;_head;0);
          IFNA(
            VLOOKUP(_d; CHOOSECOLS(_cur; _n+1; _n+2); 2; 1)
          )))))))
}

Рассказали в видео, как работает формула и коротко о логике:

_rows— ваши исходные пары (дата; валюта).

_cur— таблица на листе Currency (с заголовками кодов валют в первой строке диапазона).

_head— эта самая строка заголовков, по ней ищем смещение валюты.

Для каждой строки (BYROW): берём дату _d и код _c.
Если код пуст — возвращаем пусто. Иначе находим позицию валюты _n в заголовке и через CHOOSECOLS собираем нужную пару столбцов (дату и значение) именно для этой валюты, после чего VLOOKUP вытягивает курс по дате. IFNA скрывает #N/A, если дата не найдена.

В VLOOKUP(...; ...; 2; 1) последний аргумент 1 — точное совпадение или ближайшее меньшее

⚡️ Друзья, напоминаем про распродажу курсов от маэстро Рената Шагабудинова:

Повтор последнего действия: F4

Какие действия можно повторить?

— форматирование ячейки / диапазона (покрасили, поменяли шрифт, выделили другую ячейку или диапазон, нажали F4 — там тоже применилось последнее действие по форматированию)
— группировка строк / столбцов
— ввод значений в ячейки
— вставка и удаление строк / столбцов
— вставка и удаление строк / столбцов официальных ("умных") таблиц
— вставка сводных таблиц (надо много отдельных сводных по одним и тем же данным? Нажимаем F4 после вставки первой)

Как еще можно?
Ctrl + Y.
В Excel тоже работает?
Да. И даже в PowerPoint.

📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас

🧹 Удаляем сообщения в чате / канале Telegram скриптом

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

📽 Как всё работает — объяснил в видео.

Что нужно сделать:
1️⃣ Скопировать Таблицу 📄
2️⃣ Зарегистрировать бота в @BotFather, добавить его в чат / канал как модератора и выдать права на удаление 🛡
3️⃣ Вставить токен бота в редактор скриптов в скопированной Таблице 🔑
4️⃣ Дальше — всё по видео 🎬

---
📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас

Нумерация в рамках группы

Слабая аура (старые формулы, нужно протягивать на каждую строку):

=СЧЁТЕСЛИМН($A$3:A3;A3)

Сильная аура (новые функции — все одной формулой)

=MAP(A2:A; LAMBDA(_a; ЕСЛИ(ЕПУСТО(_a); ; СЧЁТЕСЛИ(A1:_a; _a))))

Ссылка на таблицу с примером.
Предлагайте ваши лютые формульные варианты!


---
📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас

🔥⌨️

1 Нажимаем Ctrl + / (или Справка — Быстрые клавиши, help— Keyboard shortcuts)
2 Активируем "Включить совместимые быстрые клавиши для таблиц" (Enable compatible spreadsheet shortcuts)
3 Наслаждаемся вот этими всеми прелестями:

Ctrl + минус
будет удалять выделенные строки / столбцы
(если выделены не строки/столбцы целиком, то будет контекстное меню с выбором — что удалять)

Ctrl + 9
будет скрывать все выделенные строки (целиком их выделять предварительно не нужны — скроются все строки, в которых выделены ячейки; даже если это несмежные ячейки)

Ctrl + 0
аналогично — скрытие столбцов

Ctrl + 1
для открытия меню "Формат", если привыкли к этому сочетанию в Excel (там оно позволяет не только формат ячеек открыть, но и формат выделенного объекта в диаграмме, например)

Alt + F1
быстрая вставка диаграммы


---
📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас

Друзья, посмотрите, вдруг вы пропустили что-нибудь полезное:

— СОБИРАТОР 4.0, интерфейс для сбора других Таблиц →

— 🧞‍♂️ПРЕВРАЩАТОР Листа Таблицы в xlsx / pdf / csv →

— ОТПРАВЛЯТОР, отправляем ваши отчеты в теле письма по расписанию →

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

— ДОПУСКАТОР 2, убираем из файлов всех пользователей кроме себя и закрываем доступ по ссылке. →

— ЗАМЕНЯТОР, заменяем значения по словарю в выбранных Таблицах →

— ЗАКРЫВАТОР, скрипт автоматического закрытия прошедших дней →

— ДОСТАВАТОР, берём из кода веб-страниц регуляркой нужное →

— Скриптами определяем, когда освободится домен →

— Запускаем скрипт по чекбоксу (работает и из мобильной версии Таблиц) →

— DRIVE COLUMBUS, смотрим на свой Google Диск и управляем им прямо из Таблицы →

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

— Защищаем скрипты от редактирования →

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

— Скрипт. Распознаем текст на изображениях. OCR в Google Docs →

— Скрипт onEdit(), реагирующий на изменения и отправляющий письма (или триггер в триггере) →
Скрипт, загружаем письма вашего GMAIL-аккаунта в Таблицу →

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

📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас

🧩 Как достать рейтинг товара с сайта через IMPORTXML

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

Задача
Из страницы:

https://okapteka.ru/moskva/adyenoprosin-susp-ryekt-29mg-10-549226/

нужно достать рейтинг товара (например, 4.82 из 5 звезд).

В коде страницы нужный элемент выглядит так:

<div class="product-reviews__rating">4.82 из 5 звезд</div>

🧠 XPath-запрос
Чтобы достать эти данные, используем XPath:

//div[@class='product-reviews__rating']

Разбираем:
1. // - ищем в любом месте документа
2. div - тип элемента, который ищем
3. [@class='product-reviews__rating'] - фильтруем: берём только те div, где атрибут class равен "product-reviews__rating"

Формула в Google Таблицах

=--SUBSTITUTE(INDEX(
  SPLIT(IMPORTXML(A2;"//div[@class='product-reviews__rating']");" ");
1);
".";",")

Формула пошагово:
1. IMPORTXML - тянет содержимое <div> (например, "4.82 из 5 звезд")
2. SPLIT(...;" ") - разбивает по пробелам - {"4.82" \ "из" \ "5" \ "звезд"}
3. INDEX(...;1) - берёт первое значение - "4.82"
4. SUBSTITUTE(...;".";",") - заменяет точку на запятую (для русской локали)
5. VALUE(...) или --(...) - превращает текст "4,82" в число 4,82

📌 В итоге в ячейке вы получите чистое число 4,82, готовое для расчётов, фильтрации и красивых дашбордов.

Задавайте вопросы в нашем чате: @google_spreadsheets_chat

🌤 Погода, часть 1. Получаем прогноз в Красной Поляне и записываем в Таблицу

Друзья, привет!
Мы сделали для вас Таблицу со скриптом, который обращается к бесплатному API api.open-meteo.com по введённым координатам и возвращает в Таблицу температуру и осадки на сегодня и завтра.

Делайте копию, ниже разберём, как всё работает.

1. Формируем ссылку запроса
Ссылка выглядит так:

https://api.open-meteo.com/v1/forecast?latitude=43.676932&longitude=40.251855&daily=temperature_2m_max,temperature_2m_min,precipitation_sum&timezone=auto

где
* latitude - широта,
* longitude - долгота места, для которого получаем прогноз.

В примере указаны координаты курорта Красная Поляна.

2. Смотрим на ответ API
Если перейти по ссылке (можете прямо в браузере), API вернёт JSON вроде этого:

{
    "latitude": 43.6875,
    "longitude": 40.1875,
    "generationtime_ms": 0.07867813110351562,
    "utc_offset_seconds": 10800,
    "timezone": "Europe/Moscow",
    "timezone_abbreviation": "GMT+3",
    "elevation": 722,
    "daily_units":
    {
        "time": "iso8601",
        "temperature_2m_max": "°C",
        "temperature_2m_min": "°C",
        "precipitation_sum": "mm"
    },
    "daily":
    {
        "time": [
            "2025-11-01",
            "2025-11-02",
            "2025-11-03",
            "2025-11-04",
            "2025-11-05",
            "2025-11-06",
            "2025-11-07"
        ],
        "temperature_2m_max": [
            13,
            12.7,
            17.1,
            18.7,
            17.7,
            22.6,
            23
        ],
        "temperature_2m_min": [
            6.6,
            5.1,
            6.5,
            10,
            10.5,
            11,
            17.1
        ],
        "precipitation_sum": [
            0,
            0,
            0,
            0,
            0,
            0,
            0
        ]
    }
}

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

3. Извлекаем нужные данные
* Температура на сегодня

data.daily.temperature_2m_min[0]

data.daily.temperature_2m_max[0]

*Осадки на сегодня

data.daily.precipitation_sum[0]

4. Чтобы извлечь следующие дни
Для завтра используем индекс [1], для послезавтра [2] и так далее.

📢 В следующем посте покажем, как автоматически отправлять прогноз погоды в Telegram-канал.