Google Таблицы

Задача для вас

Привет! Звучит задачка так – у нас есть столбец и мы хотим вывести номер последней заполненной строки по этому столцбу

Решений несколько, давайте соберем их все в комментариях 👇

LET ME QUERY

Привет-привет!

Задумывались ли вы, что функции QUERY порой не хватает финальной строки с общей суммой?

В этом видео я показываю, как на основе массива с населением городов России мы:
– сгруппировали данные по субъектам РФ,
– посчитали сумму по каждому субъекту,
– и добавили внизу таблицы общую сумму по всем субъектам
- и всё одной формулой.

Вот что получилось:

=LET(
  a; 'Города'!A:F;
  {
    QUERY(a; "select C, sum(B) group by C"; 1);
    {"Сумма" \ SUM(CHOOSECOLS(a; 2))}
  }
)

Таблица

🛸 В июле стартует мой курс «Google Таблицы. Формулы». Вас ждёт масса полезных приёмов и лайфхаков. План уроков / детали.

🔥 Считаем сумму по каждой строке с помощью формул массива!

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

В видео я показываю два способа: "классический" через ARRAYFORMULA с SUMIF и более продвинутый — с добавлением функции LET.

={"Классика" ; ARRAYFORMULA(IF(A2:A=""; ; SUMIF(A2:A; A2:A; B2:B)))}

={"С LET"; LET(
names; A2:A;
sums; B2:B; 
  ARRAYFORMULA(IF(names = ""; ""; SUMIF(names; names; sums)))
)}

✅ В июле старт нашего курса «Google Таблицы. Формулы», будем показывать вам много интересного. План уроков и детали.

🎓 Программа нашего курса по формулам
Старт — 1 июля

Готовы освоить все эти ЛЯМБДЫ, СКАНЫ, КВЕРИ и МАПЫ и стать тем самым человеком, к которому все бегут с вопросами по Таблицам в офисе?

🚀 Мы собрали для вас подробную программу по каждому уроку нашего курса — переходите по ссылке: t.me/google_sheets1/3

Курс для тех, кто хочет не просто уметь, а понимать, как работают формулы и использовать их по максимуму. Ждём вас!

Расставляем точки на карте с помощью Maps JavaScript API

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

Переходите, смотрите :)

Код GAS и HTML, как получить API ключ и разврнуть приложении оставлю в комментариях.

И напомню: сегодня стартует мой интенсив по JavaScript и Google Apps Script — 3 недели обучения.

В программе — база программирования и работа с популярными API: WB, OZON, Telegram Bot API.

Группа небольшая, так что будет комфортно и все сможем уделить внимание. Детали @namokonov

🚀 С 2017 года рассказываю про Google Таблицы и формулы на нашем канале — теперь запускаю полноценный курс!

Хотите упростить и ускорить до 90% рутинных задач в своей компании?

📊 Записывайтесь на курс по формулам в Google Таблицах — от базы до продвинутых функций.
Всего 12–14 уроков за 3 недели, по 2–3 занятия в неделю.

Что вас ждёт:

🔹 Базовые формулы
🔹 Современные инструменты — MAP, REDUCE, LAMBDA и работа с массивами
🔹 Много практики — с домашкой, разбором ошибок и персональной обратной связью

💬 У каждого участника будет:
– личный чат для сдачи и обсуждения ДЗ
– общий чат курса с поддержкой и общением

💰 Стоимость — 50 000₽, всего 20 мест, из них 5 уже забронированы
🗓 Старт — начало июля

Программа — в следующем посте.
Вопросы — пишите в личку: @namokonov

Получаем новые файлы и папки

Выводим новые документы, которые появились в них за последние n дней.

Наша подписчица Татьяна задала отличный вопрос: у нас есть несколько папок — как обойти их все и вытащить только те файлы, которые были созданы недавно?

Мы подготовили для вас Таблицу со встроенным скриптом (сделайте себе копию!).

Код — в комментариях к таблице.
Татьяне — большое спасибо за полезный вопрос!

🔥 Кстати, завтра стартует мини-курс по скриптам на Google Apps Script!
Будет всего два участника, в программе база и реальные кейсы работы с API Ozon, Wildberries и Telegram Bot Api.

Курс рассчитан на 2 недели — без воды, всё по делу.

Если интересно — напишите @namokonov.

Вопрос в студию 🙂

Друзья, это Google Таблица, и у нас в ней есть формула.

Напишите в чате, кто понимает, как это сделано.

Тот, кто первым ответит правильно — получит 15 кармы в чате!

ВПР-им с разных листов

Если вам нужно "подтягивать" данные с помощью ВПР / VLOOKUP с разных листов (например, на каждый город/месяц/склад у вас отдельный лист с данными), можно собрать ссылку с помощью INDIRECT / ДВССЫЛ.

Обычная ссылка на другой лист выглядит так:

='Москва'!A:B

Нам нужно подставлять внутри апострофов названия разных листов.

Сначала берем апостроф (в кавычках), потом к нему добавляем название листа, справа еще один апостроф, восклицательный знак и диапазон:

="'" & ячейка с названием листа & "'!диапазон"

Чтобы превратить полученную текстовую строку в ссылку, используем функцию INDIRECT - она ровно для этого и используется.

=INDIRECT("'" & ячейка с названием листа & "'!диапазон")

И отправляем это внутрь ВПР'а как второй аргумент:

=VLOOKUP(значение для поиска; INDIRECT("
) ; номер столбца ; 0)

Ссылка на таблицу с примером


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

Хардкорный вариант №2: получаем алфавит с внешнего сайта

Я нашёл сайт со всеми буквами русского алфавита. С помощью fetch получаю код страницы, затем с помощью регулярных выражений достаю буквы – они заключены в кавычки «».

После этого уникализирую список и вывожу в таблицу как заглавные, так и прописные буквы.

Что интересно – функция будет работать как пользовательская, то есть её можно просто ввести в таблицу Google Sheets как формулу.

Скрипт

function KIRILL() {
  // URL страницы с русским алфавитом
  const url = 'https://znanierussia.ru/articles/%D0%A0%D1%83%D1%81%D1%81%D0%BA%D0%B8%D0%B9_%D0%B0%D0%BB%D1%84%D0%B0%D0%B2%D0%B8%D1%82#:~:text=%D0%A0%D1%83%D1%81%D1%81%D0%BA%D0%B8%D0%B9%20%D0%B0%D0%BB%D1%84%D0%B0%D0%B2%D0%B8%D1%82%20%D1%81%D0%BE%D1%81%D1%82%D0%BE%D0%B8%D1%82%20%D0%B8%D0%B7%2033,%D1%8E%C2%BB%2C%20%C2%AB%D1%8F%C2%BB.';

  // Выполняем HTTP-запрос, получаем содержимое страницы в виде строки
  const response = UrlFetchApp.fetch(url);
  let html = response.getContentText();

  // Используем регулярное выражение для поиска всех вхождений "один символ внутри кавычек"
  let letters = html.match(/«(.?)»/g);

  console.log(letters);

  // Удаляем кавычки « и » из каждого элемента массива, оставляя только буквы
  letters = letters.map(l => l.replace(/«|»/g, ''));

  // Преобразуем все буквы к верхнему регистру и удаляем дубликаты с помощью Set
  let upper = [...new Set(letters.map(l => l.toUpperCase()))];

  // Для каждой буквы в верхнем регистре создаём пару [верхний регистр, нижний регистр]
  let all = upper.map(l => [l, l.toLowerCase()]);

  // Возвращаем массив пар букв (верхний и нижний регистр)
  return all;
};

⚡️Заказ работы у нас

Панграмма

Спасибо за участие, друзья! У всех почти всё получилось, если не считать букву ё, которую так просто не достать.

Поэтому – по хардкорным вариантам:

Панграмма – это предложение, в котором есть все буквы, в данном случае русского алфавита, например:

«Любя, съешь щипцы, — вздохнёт мэр, — кайф жгуч. Шеф взъярён тчк щипцы с эхом гудбай Жюль. Эй, жлоб!»

Вставляем в Таблицу, регуляркой убираем всё, что не буквы

=REGEXREPLACE(A1; "[^А-Яа-яЁё]"; "")

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

=SORT( UNIQUE( MID(UPPER(A3);SEQUENCE(LEN(A3));1)))

Результат – на картинке.

Конкурсы? Конкурсы!

Друзья, кто выведет английский алфавит и кириллицу формулой (или пользовательской функцией) получит в нашем чате +10 кармы.

Давайте свои варианты в комментарии.

Наш чат: @google_spreadsheets_chat

Как использовать множественный выбор в формулах Google Таблиц

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

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

Мы добавили выпадающий список с буквами русского алфавита (от А до Я). При этом:

• если включён чекбокс, поиск идёт без учёта регистра (буквы в любом виде),
• если чекбокс выключен — ищем только заглавные буквы.

Таблица

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

=IFNA(
  SORT(
    FILTER(
      'Россия'!A1:AF;
      REGEXMATCH('Россия'!A1:A; IF(B2; "(?i)"; "") & JOIN("|"; SPLIT(B1; ", ")))
    );
    2;
    FALSE
  );
  "—"
)

📌 B1 — ячейка с множественным выбором букв,
📌 B2 — чекбокс для переключения чувствительности к регистру,
📌 'Россия'!A1:A — названия городов,
📌 'Россия'!A1:AF — весь ряд данных по городам, который фильтруем.

⚡️Заказ работы у нас

Собираем данные из нескольких листов сразу с помощью Sheets API Batch

бета-версия нового собиратора

Друзья, в этом видео я показываю новое решение для сбора и вставки данных с использованием Sheets API Batch.
Из одной таблицы сразу берем данные с трёх листов и вставляем их на один общий лист.
Из другой таблицы — берем два листа и также объединяем их.

В скрипте демонстрирую возможности Sheets API Batch: как можно удобно и эффективно получать данные сразу из нескольких листов одновременно.

Таблица со скриптом, а скрипт отдельно будет в комментарии

⚡️Заказ работы у нас

Книга "Магия таблиц" — третье издание в продаже!

— Более 100 функций с индексом (два языка, где работает — Excel/Google, на каких страницах встречается)
— 1 фотография кота
— Сотни скриншотов
— 50 файлов с примерами
— Издание №3
— Уже более 500 отзывов на разных площадках со средними оценками 4.8-5.0
— 528 страниц

Третье издание уже в продаже:
Озон
WB
Издательство

Загружаем в Таблицу все города России

Друзья, подписчики — нашли мы на просторах интернета очень полезную штуку:
🔗 Открытая ссылка без авторизации
А там — весь список городов России! И не просто список, а с координатами, населением, округом и субъектом. Всё как мы любим:

Пример объекта:

{
"coords": {
"lat": "68.96667",
"lon": "33.08333"
},
"district": "Северо-Западный",
"name": "Мурманск",
"population": 270384,
"subject": "Мурманская область"
}

В видео показываю, как этот JSON
– забрать из интернета
— превратить в строки
– вставить все в Таблицу

Код скрипта

🏄 Заказ работы у нас

6 из 13 уроков второго потока позади!

Друзья, прошли уже 6 из 13 уроков второго потока обучения скриптам на Google Apps Script. Спасибо каждому участнику - именно ваша вовлечённость и интерес делают курс живым и насыщенным!

Покажем лучшие работы наших студентов на канале.

Пока что открыт приём заявок на:
– разработку скриптов (под Ozon, Wildberries, другие API),
– генерацию ответов с помощью скриптов,
– персональное сопровождение (обучение) по интересующим вас темам.

Пишите @namokonov

Решаем список формул!

У нас есть 606 названий — а значит, 606 потенциальных формул.

Что с ними делать?

Конечно же:

1. Добавить = и ()
2. Вставить всё это в Google Таблицу скриптами
3. Посмотреть, что эти формулы будут выводить на самом деле

📽 В видео я подробно показал, как это работает и что с этим делать.

⚡️Заказ работы у нас

Ссылка на формулы из кода Таблицы, их 606

🎁 Если кто-то найдет, какая формула работает кроме известных и классических – начислим +10 кармы в нашем чате

На картинке формула =DUCKHUNT()