Google Таблицы
64.3K subscribers
510 photos
181 videos
8 files
893 links
С 2017 года пишем про Google Таблицы и Google Apps Script — с юмором, реальными кейсами и эффективными решениями.

Обучение и заказ услуг: @namokonov 🍒
Реклама: @IT_sAdmin

Оглавление: goo.gl/HdS2qn

РКН: clck.ru/3F3u9M
Download Telegram
Выделяем формулы с ошибкой #REF!

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

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

Выделяем пачку формул и создаем правило условного форматирования с формулой. Формула будет такой:
=REGEXMATCH(FORMULATEXT(первая ячейка диапазона);"#REF!")


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

Ну а про ошибки в подробностях читайте в нашей большой статье — тыц
🔥189🍓1
Добавляем изображение с ссылкой в ячейку

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

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

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

Функция HYPERLINK возвращает ссылку на веб-страницу. У нее два аргумента — ссылка и текст ссылки. Второй аргумент - это то, что будет записано в ячейке.
например:
=HYPERLINK("www.yandex.ru";"Яндекс")


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

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

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


📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас
🔥133🍓3
Друзья, новая задачка для вас!

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

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

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


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

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

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

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

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


📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас
👍149
Июнь выдался урожайным на посты - вот подборка, для тех, кто что-то пропустил или хочет пересмотреть.

🔥 Считаем сумму по каждой строке с помощью формул массива! 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 Таблицы по винтикам.
1🔥85👍4🍓1
Media is too big
VIEW IN TELEGRAM
Собираем сводную по фруктам с помощью LET

Друзья, сводную таблицу можно собрать и без самой сводной таблицы :)

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

Итоговая формула
=LET(
_b; B1:B15; // Список фруктов
_c; C1:C15; // Наши продажи
_d; D1:D15; // Продажи конкурентов
_fruits; UNIQUE(_b); // Уникальные фрукты

_we; BYROW(_fruits; LAMBDA(_fruit; SUMIF(_b; _fruit; _c))); // Наши продажи по фруктам
_k; BYROW(_fruits; LAMBDA(_fruit; SUMIF(_b; _fruit; _d))); // Продажи конкурентов по фруктам

{_fruits \ _we \ _k} // Итоговая таблица: фрукт / наши / конкуренты
)

🔗 Таблица с примером

🚀 Наш курс про формулы
Please open Telegram to view this post
VIEW IN TELEGRAM
1👍246🔥5
Присоединяйся!

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

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

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

Любые вопросы: @namokonov
123🍓2🔥1
Схватка двух... ВПР / 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

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


📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас
🔥174👍1
Forwarded from Google Таблицы
Media is too big
VIEW IN TELEGRAM
Как найти лучших и худших сотрудников с помощью формул в Google Таблицах?

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

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

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

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

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

План, вопросы.
🔥106👍5
Media is too big
VIEW IN TELEGRAM
Отправляем сообщение в 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
21🔥41👍87
Курс набран, теперь только ждать следующего:)

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

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


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

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

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

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

📌 3. Классный курс по Google Apps Script с нуля! Очень интенсивно – прямо чувствуешь, как растут новые нейронные связи, иногда даже "больно". Огромное спасибо преподавателям – всегда придут на помощь и объяснят! Рекомендую!
2🍓1
This media is not supported in your browser
VIEW IN TELEGRAM
5🔥2🍓2
Браузер внутри Таблицы? Легко!

Друзья, немного отвлечёмся от формул.

В этом видео я покажу, как всего 19 строками кода в редакторе скриптов Google Таблиц превратить саму Таблицу в браузер.

Что это значит?
Вы сможете открывать нужную ссылку прямо в окне Таблицы.

Зачем это нужно? Например, так можно проверить, какие сайты можно спарсить через скрипты (в этом случае вернётся нормальная страница), а какие — нет (появится капча или другие защитные механизмы, которые поймут, что заходят не как обычный пользователь).

Таблица

Весь код

function f1() {

const url = SpreadsheetApp.getActive()
.getRange("#!A2")
.getValue();

const response = UrlFetchApp.fetch(url);
return response.getContentText();
};

function f2() { //выводим html-код в Таблицу окном
const html = HtmlService.createHtmlOutput(f1())
.setWidth(900)
.setHeight(700);

SpreadsheetApp
.getUi()
.showModalDialog(html, 'Ку-ку');

};
13👍9🔥1
Media is too big
VIEW IN TELEGRAM
CSV? Не проблема распарсим!

Показываю, как вставить CSV в одну ячейку Google Таблицы и превратить его в массив строк и столбцов.

Дальше
с помощью QUERY сгруппируем, отсортируем и оставим топ‑3 лидеров.

И всё это — внутри одной формулы.

Таблица / Формула
1🔥24👍5🍓21
Google Таблицы pinned Deleted message
Media is too big
VIEW IN TELEGRAM
Друзья, спасибо всем, кто пришёл ко мне и к Саше Иванову!

Было очень приятно пообщаться с вами вживую.

Мы обязательно будем повторять такие вебинары время от времени.

А если хотите запрыгнуть в последний вагон курса (там будет ещё интереснее) — пишите.


Программа / вопросы и запись @namokonov

Таблица с вебинара / Видео видео вебинара
👍148
Media is too big
VIEW IN TELEGRAM
Получаем всех Лиз! (из Википедии)

Друзья, у нашей прекрасной подписчицы и активной участницы сообщества Елизаветы сегодня день рождения!

Во-первых — поздравляем от всего сердца! ❤️

Во-вторых – демонстрируем всю мощь Таблиц:
1. обращаемся к странице Википедии с именем Лиза;
2. загружаем HTML-код страницы;
3. фильтруем нужные строки;
4. очищаем от HTML-тегов — и вуаля, получаем список всех известных Лиз нашего времени.

📊 Google Таблицы.Формулы. На нашем курсе осталось всего три места! Успевай!

📌 План курса и любые вопросы — @namokonov
7
Google Таблицы pinned Deleted message
🎉 Поздравляем мотивированного Артёма с окончанием курса по базе JS и работе с API: WB / Ozon / Telegram!

Всего за 3 недели Артем научился писать код, тянуть данные из внешних сервисов и собирать отчёты. 🚀

Продолжаем набирать небольшие группы:

👥 В группе до 5 человек

💰 Стоимость — 100 000₽ за 12 часов (четыре недели интенсива)

📌 Программа подстраивается под участников


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