Google Таблицы
58.3K subscribers
425 photos
121 videos
4 files
772 links
Работа в Google Таблицах. Кейсы, решения и угар.

контакты:
@namokonov
@r_shagabutdinov
@IT_sAdmin

оглавление: goo.gl/HdS2qn
заказ работы: teletype.in/@google_sheets/sheet_happens
чат: @google_spreadsheets_chat
Download Telegram
Forwarded from МИФ.Курсы
#таблицы

Реакции (эмодзи) в 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))

Таблица с примером
Любимый ВБ, формируем ссылки на изображения товара

Друзья, недавно у нас была задача по номенклатуре товара на Вайлдберрис создать ссылки на изображение этого товара.

Вот вам функция прямо из кода страницы WB, которую мы немного адаптировали (спасибо Роману) и теперь она может работать как пользовательская функция (смотрите скриншот) и в формуле массива.

Это нужно сохранить в редакторе скриптов:


function getImageUrl(nmid){
const basketObj = {
143: '01',
287: '02',
431: '03',
719: '04',
1007: '05',
1061: '06',
1115: '07',
1169: '08',
1313: '09',
1601: '10',
1655: '11',
1919: '12',
2045: '13',
2189: '14',
2405: '15',
2621: '16',
2837: '17',
9999999: '18'
};
return `https://basket-${basket[Object.keys(basket)
.filter(v=>Math.floor(nmid/100000)<=v)[0]]}.wb.ru/vol${Math.floor(nmid/100000)}/part${Math.floor(nmid/1000)}/${nmid}/images/big/1.jpg`
}

function images(nmds) {
return nmds.map(n => getimageUrl(n));
}

И запускаем это чудо на листе Таблицы (номенклатуры у нас в B2:B):

={"IMG" ; ARRAYFORMULA( IMAGE( images(B2:B)))}

🧑‍💻 Кто желает – перепишите под обычную формулу в Таблице, авторам лучших вариантов +7 кармы в нашем чате :)

Таблица с примером
Please open Telegram to view this post
VIEW IN TELEGRAM
Google Таблицы
Чат для патронов Мы давно хотели попробовать и вот наконец решились. Мы создаём чатик, в котором можно будет задавать вопросы по нашим скриптам, по формулам и по прекрасным Google Таблицам в целом, подписка на чат будет стоить 1000 рублей в месяц. Что даёт…
Что мы делаем в нашем патреон чате?

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

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

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

📌 В комментариях будет код из скриншота.
Минус на минус дает число: превращаем текст в число для дальнейших вычислений

Если функция возвращает текст, то результат вычисления будет текстовым, даже если состоит только из цифр. Например, если мы извлекаем суммы из текстовой строки с помощью REGEXREPLACE (или последние/первые цифры с помощью функций RIGHT / LEFT), они не будут готовы к употреблению сразу — это будут текстовые значения. См. сумму в столбце B на скриншоте или в таблице по ссылке — там ноль, хотя внешне вроде бы числа извлеклись правильные.

Как превратить текст в число в Google Таблицах (и в Excel тоже)?

1. С помощью двух минусов. Два раза умножив текст на минус единицу, мы меняем тип данных, не меняя значения
=--REGEXEXTRACT(...)


2. С помощью функции VALUE/ЗНАЧЕН.
=ЗНАЧЕН(REGEXEXTRACT(...))

3. С помощью еще какой-нибудь математической операции, не меняющей значение, например, умножения на единицу
=REGEXEXTRACT(...)*1


P.S. Если вы сразу используете число в вычислении, допустим, вам нужно его прибавить к другому — не добавляйте +, а потом два минуса. Достаточно два минуса — и текст преобразуется в число, и сложение случится.
Например:
=A2--B2
Схватка двух... ВПР / 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

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

Поиск по двум критериям (в строках и столбцах) — обычно это решается через сочетание INDEX / ИНДЕКС и MATCH / ПОИСКПОЗ.

Но ПРОСМОТРОМ (да не простым, а икс) тоже можно.

В нашем примере ищем в матрице оценку на основе двух оценок — профессиональных и поведенческих компетенций.
Сначала одной функцией получаем массив значений для профессиональной оценки (первого критерия) — у нас это C3:C6 (оранжевое) для первого сотрудника в списке.

Потом другой уже в этом массиве ищем значение, соответствующее второму критерию — поведенческой оценке (красное).

В итоге:
=ПРОСМОТРX(критерий1;где ищем критерий1; ПРОСМОТРX(критерий2;где ищем критерий2;двумерный массив))
Таблица с примером
Убираем пустые ячейки из столбца / диапазона элегантно

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

Нужно убрать пустые ячейки из диапазона и превратить его в строку или в столбец?

На помощь придут новые функции:


=torow(диапазон; 1)
=tocol(диапазон; 1)

Поэтому теперь вам необязательно использовать конструкцию вида
=filter(диапазон; один столбец из диапазона <>""), всё можно сделать проще.

Привыкайте к новым функциям и используйте!

---

Друзья, если читали или смотрели хорошие материалы по Таблицам / скриптам – а поделитесь ссылками в комментариях.

Если случится хорошая подборка, то мы ей обязательно поделимся в следующих постах.
@zadavai_vopros_bot

Друзья, а мы обновили модель, которая рисует картинки в нашем ИИ-боте, теперь там dall-e-3.

Пользуйтесь, напоминаю, что в боте 10 бесплатных запросов каждый месяц, нужно больше – 500 рублей, их можно также оплатить через бота.

Про бота / Пишем ботом простые скрипты / Отвечаем ботом на отзывы / Работа с текстом / Как писать промпты!
Давайте продолжим тему ПРОСМОТРов. И поговорим о старой функции LOOKUP / ПРОСМОТР.

Функция по синтаксису похожа на новую XLOOKUP / ПРОСМОТРX, о которой мы писали выше. Но она как раз была давно — в Excel даже есть примечание, что функция LOOKUP / ПРОСМОТР остается для совместимости. И у нее есть минусы :она требует постоянной сортировки данных, не особо подходит для поиска текста.

Поэтому лучше использовать VLOOKUP, XLOOKUP или INDEX+MATCH. Во всяком случае, для объединения таблиц по текстовому ключу.
Но все же в некоторых экзотических случаях LOOKUP используют до сих пор. Например, для нечеткого текстового поиска, когда нужно находить в названиях какое-то слово и заменять все значения с этим слово на одно и то же типовое (исправлять разные наименования компаний или товаров, например) — такой пример есть в статье.

https://teletype.in/@renat_shagabutdinov/wFymDX7fAN4
Немного формульно-датового многоэтажного ада под конец года. Выдаем одной формулой все недели года в формате "30 янв-5 фев"

В деле почти все функции Google Таблиц 🤠LET, LAMBDA, QUERY, SEQUENCE, NETWORKDAY.INTL, MIN, DATE, MONTH, YEAR, DAYS, WEEKNUM, WEEKDAY, BYROW, TRANSPOSE, CHOOSECOLS, TEXT, LEFT, REGEXREPLACE, IF, ARRAYFORMULA в тех или иных лютых комбинациях

Друзья, наверняка у вас есть отчеты и планы, где в заголовках идут недели. Если для вас привычен формат с первым и последним днем недели, забирайте формулы от нас и маэстро нашего чата (Михаил и Игорь, спасибо!) — они выдадут все недели года одной строкой в таком формате.

Три варианта в таблице по ссылке.

— в первом случае недели идут с первого понедельника, последняя неделя будет с первыми днями следующего года ("30 дек - 5 янв"). Выдаются недели за текущий год (можете поменять YEAR(TODAY()) на фиксированный год, если нужно)
— во втором случае недели тоже с первого понедельника, последняя неделя до 31 декабря ("30-31 дек"), год выбирается в ячейке
— в третьем случае все недели (включая ту, что до первого понедельника), последняя до 31 декабря, год в ячейке

С наступающим Новым годом! Пусть в этих самых отчетах у вас все будет хорошо в следующем году 🤠Спасибо, что читаете нас!
This media is not supported in your browser
VIEW IN TELEGRAM
Автоматизация елки

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

В ветках у неё формула, обеспечивающая для каждой ячейки свой цвет:
=INDEX({"🟠";"🟡";"🟢";"🔵";"🟣";"🟤"};RANDBETWEEN(1;6
);1)

А зажжет ёлку скрипт, он при каждом открытии Таблицы, сто раз, в цикле, вставит в ячейку A1 число, тем самым запуская пересчёт формул, в частности RANDBETWEEN:




function onOpen() {
for (var i = 0; i < 100; i++) {
SpreadsheetApp.getActiveSheet().getRange(1, 1).setValue(i);
SpreadsheetApp.flush();
};
};


Таблица с ёлкой

Друзья, с наступающим новым годом, берегите себя, встречаемся здесь же в 2024! Счастья и мира!

PS А еще у старожила нашего сообщества Каната сегодня день рождения, поздравляем! 🌟

---
🌲 Оглавление канала: ты-дыц
🌲 Самый табличный чат на свете: бадабум
Please open Telegram to view this post
VIEW IN TELEGRAM