Панграмма

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

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

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

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

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

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

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

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

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

Хардкорный вариант №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;
};

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Что вас ждёт:

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

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

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

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

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

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

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

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

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

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

В видео я показываю два способа: "классический" через 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 Таблицы. Формулы», будем показывать вам много интересного. План уроков и детали.

LET ME QUERY

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

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

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

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

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

Таблица

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

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

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

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

ПУШКИН, Таблицы и Importdata

Друзья, в этом видео рассказываю про чудесную функцию IMPORTDATA. Она позволяет загружать файлы в формате TXT, CSV, TSV из интернета и вставлять их как значения в нашу Таблицу.

Импортом мы, конечно, не ограничились. Далее мы соединили каждую строчку Поэтма (некоторые расползлись на несколько столбцов) прямо внутри формулы — с использованием LET, BYROW и LAMBDA.

Таблица с тем, что получилось

Формула для Пушкина:

=LET(
  a, IMPORTDATA("https://lib.ru/LITRA/PUSHKIN/p2.txt"),
  BYROW(
    a;
    LAMBDA(row;
      TEXTJOIN(" "; TRUE; row)
    )
  )
)

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

Выделяем формулы с ошибкой #REF!

У вас гора формул. Надо выяснить, есть ли в ней формулы с ошибкой #REF! внутри (которая, например, возникает при удалении столбца или строки, на которые мы ссылались).

Их может быть и не видно. Допустим, в формуле есть ЕСЛИОШИБКА / IFERROR — и возвращается не ошибка, а пустое значение.

Выделяем пачку формул и создаем правило условного форматирования с формулой. Формула будет такой:

=REGEXMATCH(FORMULATEXT(первая ячейка диапазона);"#REF!")

Напоминаем, FORMULATEXT (для экселье с русским интерфейсом — Ф.ТЕКСТ) возвращает текст формулы.

Ну а про ошибки в подробностях читайте в нашей большой статье — тыц

Добавляем изображение с ссылкой в ячейку

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

Для этого понадобятся две функции — IMAGE и HYPERLINK (ГИПЕРССЫЛКА).

Функция IMAGE отображает в ячейке изображение.
У нее два аргумента — ссылка на изображение (заданное текстом в кавычках или в виде ссылки на ячейку таблицы, в которой хранится ссылка на изображение) и тип отображения (mode) — от которого зависит, как будет выглядеть картинка в ячейке.

Функция HYPERLINK возвращает ссылку на веб-страницу. У нее два аргумента — ссылка и текст ссылки. Второй аргумент - это то, что будет записано в ячейке.
например:

=HYPERLINK("www.yandex.ru";"Яндекс")

В ячейке будет слово "Яндекс", а при щелчке на нее пользователь перейдет по ссылке www.yandex.ru.

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

P.S. Ну а если вы хотите вставить в изображение ссылку в Google Презентациях, допустим, просто выделяйте его — и далее стандартное сочетание для вставки ссылки Ctrl+K.


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

Друзья, новая задачка для вас!

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

Про ссылки на таблицы писали тут: https://www.tgoop.com/google_sheets/1538

Еще примеры применения ссылок на умные таблицы (в IMPORTRANGE и VSTACK): https://www.tgoop.com/google_sheets/1546


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

Даты в таблицах

Друзья, ловите обновленную статью про табличные даты. Она живет по этому адресу:
https://shagabutdinov.ru/date_time

А вот что вы найдете внутри:

— значения и форматы дат
— ввод текущих дат и времени как значения (и почему не всегда работают горячие клавиши)
— функции СЕГОДНЯ / TODAY и ТДАТА / NOW
— функция РАНЗДАТ / DATEDIF
— вычисления с рабочими днями
— функции и формулы для получения отдельных параметров даты: день, месяц, номер недели, день недели цифрой и текстом, квартал (4 способами)

Про отдельные элементы даты есть и видео — оно лежит на Sponsr, но открыто и бесплатно для всех — по ссылке


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

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

🔥 Считаем сумму по каждой строке с помощью формул массива! https://www.tgoop.com/google_sheets/1759

LET ME QUERY https://www.tgoop.com/google_sheets/1761

ПУШКИН, Таблицы и IMPORTDATA https://www.tgoop.com/google_sheets/1763

Лабубу. IMPORTXML. Превращаем Таблицы в поисковую систему https://www.tgoop.com/google_sheets/1764

Выделяем формулы с ошибкой #REF! https://www.tgoop.com/google_sheets/1766

Добавляем изображение с ссылкой в ячейку https://www.tgoop.com/google_sheets/1768

🍒 А ещё со следующей недели стартует наш курс “Google Таблицы: Формулы" – разберём Google Таблицы по винтикам.