Флажки в Excel: очередной повод сказать "а в Таблицах это есть давно!"

Вы вообще помните, когда появились флажки в Таблицах? Кажется, что были всегда :)
А в Excel — пару недель назад и пока только для бета-канала обновлений у подписчиков Microsoft 365.

Работают аналогично: пробелом можно включать и выключать, в ячейке хранится ИСТИНА / TRUE или ЛОЖЬ / FALSE.

Увы, при скачивании Таблицы на локальный диск в формате xlsx "табличные" флажки в Excel превратятся просто в логические значения, придется вставлять флажки поверх них. Аналогично при импорте Excel в Таблицы.

P.S. Речь именно про флажки в ячейках, так как в Excel всегда были доступны другие флажки, которые не размещаются в ячейке, а вставляются на отдельный слой поверх ячеек. Вставляются они на вкладке "Разработчик" (Developer), и в контекстном меню каждого флажка можно настроить связь с ячейкой (в которой будет отображаться ИСТИНА / ЛОЖЬ в зависимости от флажка)

@zadavai_vopros_bot

Сначала коротко новости - мы перевели нашего chatGPT бота на другой стек, а еще мы прикрутили оплату, месяц по прежнему стоит 500 рублей *

* Но можно пользоваться и бесплатно, каждый месяц у вас по 10 запросов, пробуйте

Про бота / Пишем ботом простые скрипты / Отвечаем ботом на отзывы / Работа с текстом

~~~
Немного базы
Что самое важное при работе с языковыми моделями? Промпт. То, что вы отправляете в нашего бота (или в чат open ai).

Представьте, что у вас есть текст и вы хотите достать из него названия городов.

Промпт будет примерно таким: После "текст: " будет текст, из него нужно извлечь названия городов США, их нужно написать по-английски и каждый город нужно взять в кавычки. В тексте города могут быть написаны с ошибками, ошибки нужно исправить. Текст: ".

Советы:
1) можете использовать капслог - по непровенным данным модель это воспринимает как приказ, который нужно выполнить
2) меняйте промпт пока не придете к нужному результату
3) пишите на английском, можете использовать google translate

Новые чипы в Таблицах: звездочки с оценками от 0 до 5

Вводим @Оценка (@ Rating)
или идем в меню Вставка (Insert) — Умные чипы (Smart chips) — Оценка (Rating)
или нажимаем Alt+I + A + 8

И получаем возможность выбирать звездочки в числе от 0 до 5 штук.
Гибкости, увы, немного: всегда будет серый фон, всегда от 0 до 5, всегда звездочки.

Бен Коллинз в своей рассылке предлагает менять фон всей ячейки на #e8eaed — это цвет чипа — чтобы хотя бы вся ячейка была залита.
еще можно поменять цвет шрифта на любой — это будет цвет звездочек.

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

Выделяем сегодняшнюю дату и соседние с ней.

Если хотим как на картинке — сегодня с заливкой, вчера и завтра потемнее, а все остальное побледнее серым, то применяем два правила условного форматирования к столбцу с датами:

1 С первым все просто — форматировать ячейки, если "Дата" -> "Сегодня"

2 А второе правило будет с формулой. Формула, например, такая:

=ABS((TODAY()-первая ячейка диапазона))>1


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

"Скроем формулу с глаз"

Друзья, иногда нам нужно не показывать формулу в строке формул, для этого можно воспользоваться простым лайфхаком:

1) скопируйте формулу;

2) откройте любой текстовый редактор, к пример, блокнот, вставьте ее туда;

3) добавьте несколько переносов строк между "=" и телом формулы;

4) вставьте, то, что получилось в таблицу;

Формула останется на месте, конечно, но чтобы до нее добраться нужно будет немного постараться :)

Парсим Ютуб ⚡️

И Ютуб – Google и Таблицы – Google. Что нам это даёт?

Например то, что в скриптах Таблиц есть обёртка для использования Ютуб API. Если вы хотите достать просмотры роликов, то вам необязательно парсить страницу с роликом влоб, просто откройте скрипты, в сервисах подключите YouTube и используйте код, который будет ниже. Кроме просмотров (stat.viewCount) функция выведет и название видео и лайки.

function get_youtube() {
let video_url = "https://www.youtube.com/watch?v=ObthaxGP7gg";
console.log(platformId)
}

function youtube(url) {
let id = url.match(/[-\w]{11,}/)[0];
let item = YouTube.Videos.list('snippet,statistics', { 'id': id }).items[0]
let stat = item.statistics
let snippet = item.snippet
return ({ views: stat.viewCount, name: snippet.title, likes: stat.likeCount, dislikes: stat.dislikeCount })
};

PS Расскажите в комментариях, интересна вам эта тема? Если да, то продолжим с парсингами других площадок (телеграм, инстаграм, твич, вк, дзен).

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

Для этого:
1. вычислим число рабочих дней в периоде (функция ЧИСТРАБДНИ / NETWORKDAYS)

2. Засунем это число в функцию SEQUENCE и получим последовательность чисел от 1 до числа рабочих дней в периоде

3. отправим эту последовательность в функцию РАБДЕНЬ / WORKDAY — она возвращает дату, которая наступит по прошествии N рабочих дней от заданной. В нашем случае она выдаст много дат, по одной для каждого числа полученной на прошлом шаге последовательности. Формулу вводим сочетанием Ctrl+Shift+Enter, чтобы засунуть ее в функцию ArrayFormula.

Формула такая:

=ArrayFormula(WORKDAY(начальная дата-1; SEQUENCE(NETWORKDAYS(начальная дата ;конечная дата))))

На скриншоте конечная дата задается функцией СЕГОДНЯ / TODAY — так что список будет обновляться каждый день.

Как выгрузить в Таблицу все официальные выходные / рабочие дни

Функция ТЕКСТ / TEXT: превращаем число в текстовое значение в заданном числовом формате

Эта чудо-функция возвращает текстовую строку со значением (первый аргумент), оформленным в заданном числовом формате (второй аргумент).

Для чего нужна?
Допустим, вы хотите "склеить" в одну текстовую строку текст и число.
Чтобы получить в таблице надпись вида "По состоянию на: 20.11.23" или "Сумма продаж: 20 500". То есть текст из фиксированной части и какого-то вычисления/функции, как-то суммы чисел или текущей даты.

Проблема в том, что если сделать это "в лоб" без функции ТЕКСТ / TEXT, форматирование потеряется. Число будет без разделителей разрядов, со всеми знаками после запятой; дата будет в виде числа ("По состоянию на: 45250") — потому что вот так даты хранятся в Таблицах (и Excel).

И функция ТЕКСТ позволяет это исправить — укажите нужный формат во втором аргументе, как если бы вводили его в пользовательском числовом формате.

Итак, для даты в нашем примере нужна будет такая формула:

="По состоянию на: " & ТЕКСТ (дата; "DD.MM.YY DDD")

Это формат вида 20.11.2023 Пн (с коротким днем недели — за него отвечает DDD). Подробнее про пользовательские числовые форматы можно посмотреть в видео.

#таблицы

Реакции (эмодзи) в Google Документах

Если вам нужно проголосовать за идеи, проекты, продукты или что угодное еще в Google Документе — можно использовать эмодзи!

Еще они могут пригодиться для оценки текста, домашней работы или чего угодно еще.

Это просто: выделяем текст, нажимаем на смайл справа и выбираем эмодзи по вкусу.

Чат для патронов

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

Что даёт подписка

1. Взяли скрипт у нас на канале и не работает? – Поможем внедрить
2. Есть вопросы, за что отвечает та или иная строчка в скрипте? – Расскажем
3. Обсудим в спокойной обстановке формулы, как решить задачку, зачем нужны веб-приложения в Таблицах и кто такие телеграм боты
4. Ну и конечно подпиской вы поддержите нас и наш канал ❤️

Если вам стало интересно – напишите @namokonov

Парсим картинки из Рамблера

Друзья, недавно перестали работать формулы с загрузкой картинок из Гугл и Яндекс (ссылки давать не будем, они же не работают).

В нашем чате предложили альтернативу - рамблер (а он всё еще работает, мы тоже удивились).

Формула:
=IMAGE(INDEX(IMPORTXML(HYPERLINK("https://images.rambler.ru/search?query="&B2);"//img/@src");3))

PS Большое спасибо пользователю "Недвижимость Воронежа" из нашего чата!

Считаем количество ответов на форму... формулой

Вы проводите опрос и хотите быстро посмотреть статистику: сколько раз пользователи выбирали тот или иной вариант при ответе на каждый вопрос (конечно, такое не прокатит с открытыми вопросами, а только при выборе из списка вариантов)

Можно поступить так:
1. выведем список уникальных ответов (функция UNIQUE)

2. для каждого ответа посчитаем, сколько раз он встречается в столбце (COUNTIF / СЧЁТЕСЛИ)

3. Объединим в одну текстовую строку через дефис или другой разделитель:
COUNTIF(диапазон;ответ)&" - "&ответ

4. Чтобы все было одной формулой, проделаем это через MAP — массивом значений для этой функции и будет список уникальных ответов. Для каждого из них мы будем считать, сколько раз он встречается, и склеивать число с текстом ответа.
Можно добавить проверку на пустоту через ISBLANK / ЕПУСТО, чтобы не выводить пустые ответы и ссылаться на открытый диапазон (если ждете новых ответов на форму).

=MAP(UNIQUE(диапазон с ответами);LAMBDA(ответ;IF(ISBLANK(ответ);"";COUNTIF(диапазон;ответ)&" - "&ответ)))

Именованные функции (Named functions)

Если в Excel можно создавать функции (без макросов) с помощью LAMBDA и диспетчера имен, то в Таблицах есть интерфейс для этого — Named functions (именованные функции) в меню "Данные".

Вашему вниманию очень короткое видео (3 минуты, ибо там все просто) про создание своих функций таким образом. С примером функции, обрабатывающей отдельные ячейки и функции, обрабатывающей диапазоны (для такого придется засунуть в именованную функцию LAMBDA и одну из вспомогательных функций, например, MAP).
https://www.youtube.com/watch?v=ASPf1LdDoDQ

Это один из 95 уроков курса "Драйв. Гугл Драйв" в МИФе. В курсе не только Таблицы, но на них упор, конечно❤️

Уникальные пары значений

Нам нужны уникальные пары значений. Например, пары "Клиент" — "Продукт" (полный список клиентов и продуктов — кто что покупал)

Если столбцы рядом — то можно просто сослаться функцией UNIQUE на эти два столбца.

Если они не рядом — то предварительно выбрать их функцией CHOOSECOLS. Например, если нам нужны первый и третий столбцы:
=UNIQUE(CHOOSECOLS(диапазон;1;3))

Отбираем по ошибке #N/A строки в QUERY и FILTER

Друзья, иногда в наших датасетах может быть ошибка #N/A (not available или значение не доступно).

Показываем, как отобрать строки или значения с ошибкой и без:

1. Для QUERY, отбираем строки с N/A:
=QUERY(A1:A12;"where Col1 = '#N/A'";0)

2. Для QUERY, отбираем строки без N/A:
=QUERY(A1:A12;"where Col1 != '#N/A'";0)

3. Чтобы отобрать N/A в FILTER:
=FILTER(A1:A12;ISNA(A1:A12))

4. Ну и отбираем всё, кроме N/A в FILTER:
=FILTER(A1:A12;NOT(ISNA(A1:A12)))

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

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

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

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

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

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

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

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

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

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

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

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

Данные с разных листов, на которых разная структура

Что делать в более тяжелом случае, когда данные на разных листах и еще разбросаны как попало (все в разных столбцах; нужный столбец то левее, то правее столбца с ключом для поиска)?

Тут сложнее. Можно решить эту задачу так: через ИНДЕКС+ПОИСКПОЗ (INDEX + MATCH) вместо ВПР (про эту комбинацию мы уже писали), чтобы все работало при любом порядке столбцов.

Через СМЕЩ / OFFSET будем формировать ссылку на столбец для поиска и на столбец с нужными данными. Находить их будем по заголовкам (заголовок будет находиться через ПОИСКПОЗ / MATCH, и это будет использоваться в функции СМЕЩ / OFFSET для смещения по столбцам, чтобы попасть на нужный).

Вот ингредиенты нашего коктейля:
INDIRECT("'"&название листа&"'!диапазон") - ссылка на ячейку или диапазон на нужном листе, с которого тянем данные

MATCH(заголовок;INDIRECT("'"&название листа&"'!диапазон");0) - поиск нужного нам заголовка (столбца, из которого нужно тянуть данные)

ROWS(INDIRECT("'"&название листа&"'!A:A") - число строк на листе, с которого нужно тащить данные)

OFFSET(INDIRECT("'"&название листа&"'!A1"); 0;MATCH (ищем заголовок, как выше) - 1; число строк, как выше;1) - ссылка на диапазон на нужном листе шириной 1 столбец со всеми строками, с отступом от A1 до нужного нам заголовка.

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

=ИНДЕКС(СМЕЩ(который дает ссылку на диапазон на нужном листе в нужном столбце, с которого нужно тащить данные;
ПОИСКПОЗ(ключ для поиска;СМЕЩ(который дает ссылку на диапазон на нужном листе в столбце, в котором находятся ключи для поиска, например, названия товаров);0))

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

Что мы делаем в нашем патреон чате?

К примеру, взяли ссылку на крипто-API https://apilist.tronscanapi.com/api/account/wallet?address=TSTVYwFDp7SBfZk7Hrz3tucwQVASyJdwC7&asset_type=1

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

🌊 (здесь про наш патреон-чат, условия и какие темы мы в нём обсуждаем)

📌 В комментариях будет код из скриншота.