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

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

🚀 Мы собрали для вас подробную программу по каждому уроку нашего курса — переходите по ссылке: t.me/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://tttttt.me/google_sheets/1538

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


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

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

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

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

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

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


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

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

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

LET ME QUERY https://tttttt.me/google_sheets/1761

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

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

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

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

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

Присоединяйся!

Курс Google Таблицы. Формулы от канала @google_sheeets стартует на этой неделе, в пятницу.

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

Все подробности и план уроков: t.me/google_sheets1/3

Любые вопросы: @namokonov

Схватка двух... ВПР / VLOOKUP vs ПРОСМОТРX / XLOOKUP. Старая и новая функции для объединения таблиц (поиска текста и чисел)

=VLOOKUP(что ищем; таблица, в которой поиск идет в первом столбце; номер столбца, из которого забираем данные; [режим поиска])

=XLOOKUP (что ищем; в каком столбце ищем; из какого столбца забираем; [на что заменяем ошибку]; [ищем текст/число/текст с подстановочными символами] ; [ищем сверху или снизу])

— VLOOKUP ищет только в первом столбце таблицы, а XLOOKUP ссылается на отдельные столбцы (где ищем и откуда возвращаем данные) — ей все равно, какая структура данных. Соответственно, VLOOKUP слетит, если вставить столбец между первым и тем, откуда возвращаем данные (потому что номер возвращаемого столбца зашит как константа). А XLOOKUP, ссылающаяся на столбцы, будет работать при вставке новых столбцов. И может возвращать данные, которые левее, чем столбец для поиска.

— XLOOKUP по умолчанию ищет текст (точное совпадение), а VLOOKUP — ближайшее наименьшее число.

— В режиме поиска числа (пятый аргумент, равный единице или минус единице) XLOOKUP не требует сортировки данных и умеет искать и ближайшее наибольшее тоже;

— У XLOOKUP есть отдельный необязательный (четвертый) аргумент для замены ошибок (когда ничего не найдено) на другое значение. А в случае VLOOKUP для этого дела надо добавлять отдельную функцию IFNA.

— VLOOKUP умеет работать с символами подстановки (* и ?) по умолчанию, а XLOOKUP — нет. Чтобы использовать символы подстановки в XLOOKUP, нужно задать пятый аргумент match_mode равным 2 (по умолчанию 0 - точный поиск).

— VLOOKUP умеет только вертикально (столбцы), для горизонтального поиска используется HLOOKUP / ГПР. XLOOKUP может работать и со строками, и со столбцами.

— VLOOKUP всегда ищет сверху вниз (то есть при 2 и более совпадениях найдет первое), а XLOOKUP умеет и снизу вверх (то есть найдет последнее) - для этого задаем последний аргумент search_mode равным -1.

— В Excel XLOOKUP есть только в 2021 / 365, то есть при скачивании таблицы в формате XLSX функция не будет работать в 2019 и более ранних версиях Excel.

___
Подборка постов про VLOOKUP
Поиск последнего значения с помощью XLOOKUP

💥 Другие наши посты можно найти в оглавлении канала.


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

Как найти лучших и худших сотрудников с помощью формул в Google Таблицах?

На примере простой таблицы с именами сотрудников и их продажами, я покажу:

* как отсортировать данные по убыванию и возрастанию,
* как добавить порядковый номер к каждой строке,
* как с помощью формул LET, SORTN и других вывести ТОП-3 лучших и 3-х с наименьшими продажами.

📊 Всё делается формулами — никаких скриптов.

Таким практическим приёмам мы обучаем на курсе "Google Таблицы для офиса".
🔹 Небольшая группа
🔹 Старт уже скоро
🔹 Упор на реальные задачи

Хочешь стать лучшим спецом по Таблицам в офисе?
Присоединяйся!

План, вопросы.

Отправляем сообщение в Telegram прямо из Google Таблицы — без единой строчки кода!

Даже не заходим в редактор Apps Script.

Всё работает благодаря функции =IMPORTDATA, которая делает GET-запрос по введенной ссылке. Мы собираем специальную ссылку с параметрами (токен бота, текст сообщения, chat_id или username) и добавляем к строке чекбокс.

Нажимаем на чекбокс — и сообщение мгновенно улетает в Telegram.

Таблица

https://api.telegram.org/bot<ТОКЕН>/sendMessage?chat_id=<ID_ИЛИ_USERNAME>&text=<ТЕКСТ>&parse_mode=html

🚀 Присоединяйся, наш курс Google.Таблицы Формулы стартует уже на этой неделе.

Любые вопросы: @namokonov

Курс набран, теперь только ждать следующего:)

⚡️ На Google Таблицы. Формулы еще можно записаться, @google_sheets1 (программа), вопросы: @namokonov

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

📌 1. Огромная благодарность команде @google_sheets за обучение автоматизации в Google Таблицах с помощью Apps Script!
Этот курс дал мне именно ту базу, которая нужна для уверенного старта.
Когда знания дают профессионалы, гораздо проще продолжать обучение самостоятельно.

Что понравилось?
✔️ Домашние задания — интересные и приближенные к реальным задачам.
✔️ Поддержка преподавателей — разбор домашних работ, обратная связь и советы по оптимизации кода.
✔️ Даже после курса команда остаётся на связи (в чате) и помогает с вопросами по пройденному материалу.
✔️ Разбор рабочих задач — помощь преподавателей в автоматизации рабочих задач учеников.

Можно учиться самостоятельно, но это займёт больше времени, а результат не всегда будет оптимальным.
Курс понравился 🔥

📌 2. Мне посчастливилось обучаться у Евгения и команды работе с API через GAS. Объясняет максимально доступным языком, без воды. Постоянная обратная связь от команды, много практики. Однозначно рекомендую всем кто хочет понимать что происходит в коде и как его писать.

📌 3. Классный курс по Google Apps Script с нуля! Очень интенсивно – прямо чувствуешь, как растут новые нейронные связи, иногда даже "больно". Огромное спасибо преподавателям – всегда придут на помощь и объяснят! Рекомендую!