Биты в байты, пинты в литры, столовые ложки в чайные. Функция ПРЕОБР / CONVERT

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

=ПРЕОБР (значение; исходная единица измерения; конечная единица измерения)

Единицы измерения указываются сокращенно и на английском. Можно брать из ячеек, можно указывать в кавычках - все как с любыми другими текстовыми аргументами функций.
Например, "km" (километр), "byte" (байт), "uk_pt" (английская пинта), "ha" (гектар).

Можно использовать префиксы "Т" (тера), "G" (гига), "M" (мега) и подобные. Например, "Tbyte" - терабайт.

Единиц измерения много. Расстояние, время, вес и масса, давление, сила, энергия, мощность, температура, объем, скорость, площадь, биты и байты.
Полный список можно посмотреть в справке - и в Google Таблицах, и в Excel.

Создаём в QR-код прямо в ячейке

Друзья, в Google Таблице просто из своей текстовой строки создать QR-код.

1) Обращаемся к https://chart.googleapis.com/chart? и собираем ссылку вида:
"https://chart.googleapis.com/chart?cht=qr&chs=100x100&chl=www.tg-me.com/google_sheets"

Доступно 5 параметров:
cht=qr //тип графика, для QR-кода – всегда qr
chs=70*70 //размер изображения
chl=www.tg-me.com/google_sheets //данные, которые превращаем в QR-код
choe= //необязательный параметр, как кодировать данные, по умолчанию utf-8
chld= //необязательный параметр, уровень исправления ошибок, по умолчанию 7%

2) Получившуюся оборачиваем в функцию IMAGE(ссылка; 3) и получаем изображение прямо в ячейке.

Документация

Оглавление канала. Внутри очень много постов и материала про Таблицы и скрипты =)

Как выгрузить в Таблицу курс доллара с 01/04/22 по сегодняшний день?

Используйте формулу: =GOOGLEFINANCE("USDRUB";"price";"01.04.2022";TODAY())

Большая статья про курсы от Михаила Смирнова

Имеем: сводную таблицу в Google.

Хотим: добавить к ней срез для фильтрации данных.

1. Добавляем к исходным данным срез (Данные - Добавить срез). Выбираем в боковой панели нужный столбец, по которому будем фильтровать данные. Оставляем флажок "Применить к сводным таблицам".

2. После этого копируем срез (правая кнопка мыши - "Копировать срез"). И вставляем его на лист со сводной таблицей.

3. Срез на листе с данными можно удалить, если там он не нужен.

Ссылка на пример сводной со срезом.

P.S. в Excel срезы (Slicers) в сводных таблицах есть с версии 2010 и добавляются через вкладку "Анализ сводной таблицы" на ленте.

Диалоговое html-окно, первые шаги

Друзья, в Google Таблицах при помощи HTML и JS можно собрать модальное окно и вывести его.

Хоть простой сайт собирайте (если он вам нужен в Таблице).

Начнём с простой задачи, которая позволит разобраться, как использовать в HTML данные из Таблицы и как запускать из HTML скрипты.
— создадим HTML-окно с текстом, который возьмём из ячейки
— с двумя кнопками, при нажатии на которые будет запускаться скрипт, в который будет передаваться текст, который написан на кнопке, сообщение с этим текстом будет выводиться пользователю

Весь код, который нужен

1) Часть Google Apps Script (вставляем на лист gs)

function showDialog() {
var html = HtmlService.createTemplateFromFile('Page').evaluate()
.setWidth(200) //размеры в пикселях
.setHeight(200);
SpreadsheetApp.getUi()
.showModalDialog(html, "@google_sheets"); //второй аргумент - заголовок окна
}

function script1(v) {
SpreadsheetApp.getActive().toast(v);
}

2) Часть html (вставляем на html-лист "page" в редакторе скриптов)

К этому тексту добавим текст из ячейки
<?=SpreadsheetApp
.getActiveSpreadsheet()
.getRange('Лист1!A1')
.getValues();
?>
<br>
<br>
<input type="button" value="первый скрипт" onclick="google.script.run.script1(value)" />
<br>
<br>
<input type="button" value="второй скрипт" onclick="google.script.run.script1(value)" />

Таблица с примером
Документация

Встраиваем видео в Таблицу

Диалоговое html-окно 2. Сделаем переключатель (radio button).

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

Выведем в Таблице HTML-окно с Яблоко / Банан / Арбуз и кнопкой "отправить". Нажатие на кнопку отправит выбор во внешний скрипт, а он, в свою очередь, выведет этот выбор в Таблицу. Также нажатие на кнопку закроет окно (за это отвечает google.script.host.close())

Весь код, который нужен

1) Часть Google Apps Script (вставляем на лист gs)
function showDialog() {
var html = HtmlService.createTemplateFromFile('Page2').evaluate()
.setWidth(200) //размеры в пикселях
.setHeight(200);
SpreadsheetApp.getUi()
.showModalDialog(html, "@google_sheets"); //второй аргумент - заголовок окна
}

function script1(v) {
SpreadsheetApp.getActive().toast(v);
}

2) Часть html (вставляем на html-лист "page" в редакторе скриптов)
<p>Выбирайте</p>
<input type="radio" name="gender" value="Яблоко">Яблоко
<br>
<input type="radio" name="gender" value="Банан">Банан
<br>
<input type="radio" name="gender" value="Арбуз">Арбуз
<br>
<button type="button" onclick="displayRadioValue(), google.script.host.close()">Отправить!</button>

<script>
function displayRadioValue() {
var ele = document.getElementsByName('gender');
for(i = 0; i < ele.length; i++) {
if(ele[i].checked) {
break;
}
}
google.script.run.script1(ele[i].value);
}
</script>

Таблица с примером
HTML в GAS
Radio Button

Встраиваем видео в Таблицу

Оглавление нашего канала: тыц
Наш чат: тыц-тыц

Возвращаем ссылку на диапазон с помощью функций

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

Например, мы можем находить значение с помощью сочетания INDEX / ИНДЕКС и MATCH / ПОИСКПОЗ. Если после поставить двоеточие, то вместо значения будет возвращаться ссылка на ячейку.

В следующем варианте ищем два значения - превращаем каждое из значения в ссылку за счет двоеточия, получаем на выходе диапазон и его суммируем

=SUM(INDEX(диапазон суммирования;MATCH(что ищем-1;диапазон поиска;0)):INDEX(диапазон суммирования;MATCH(что ищем-2;диапазон поиска;0)))

Магия работает в Excel и Google Таблицах со следующими функциями:
CHOOSE / ВЫБОР
IF / ЕСЛИ
IFS / ЕСЛИМН
INDEX / ИНДЕКС
INDIRECT / ДВССЫЛ
OFFSET / СМЕЩ
SWITCH / ПЕРЕКЛЮЧ
VLOOKUP, HLOOKUP, LOOKUP / ВПР, ГПР, ПРОСМОТР
XLOOKUP / ПРОСМОТРX (удовольствие, доступное в Microsoft 365 / Excel 2021)

INDEX будет работать в качестве ссылки и без двоеточия - тогда это будет ссылка на одну ячейку. Если он будет указан в аргументе, тип которого - ссылка. Пример в таблице.

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

Условное форматирование + ВПР: выделяем цветом имена сотрудников из определенного отдела

Дано: на одном листе список сотрудников и их отделы/департаменты/что-то еще. На другом - календарный/временной план, в котором мы расставляем сотрудников на определенные даты, смены, временные слоты.
Нужно: красить одним цветом всех сотрудников одного отдела, другим цветом - сотрудников другого, и тэ дэ.

Решение: создаем правило условного форматирования, ВПР-им каждого сотрудника (первую ячейку форматируемого диапазона, в нашем примере это B2), находим его отдел и проверяем, в зависимости от значения отдела применяем ту или иную заливку.

Чтобы условное форматирование работало с диапазоном с другого листа, на диапазон ссылаемся через функцию INDIRECT / ДВССЫЛ:
=ВПР(B2;ДВССЫЛ("'Список сотрудников'!A:B");2;0)="Оплот"

Можно ссылаться на именованный диапазон:
=ВПР(B2;ДВССЫЛ("Список");2;0)="Замок"

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

Топ-20 упоминаний ссылок нашего канала за полгода (1.11.21 - 30.04.22)

★ Чат: @google_spreadsheets_chat
★ Больше постов в оглавлении нашего канала: goo.gl/HdS2qn

Друзья, @vitalich выгрузил все сообщения из нашего чата и составил топ самых упоминаемых постов нашего канала. Присмотритесь – вполне возможно, найдете полезное для себя.

1. Полный справочник по QUERY www.tg-me.com/google_sheets/616

2. СОБИРАТОР 4.0 www.tg-me.com/google_sheets/661

3. Считаем сумму по каждой строке / столбцу в формуле массиве www.tg-me.com/google_sheets/502

4. Народный Telegram бот❗️ Отправляем сообщения прямо из Таблицы www.tg-me.com/google_sheets/556

5: DRIVE COLUMBUS, смотрим на свой Google Диск и управляем им прямо из Таблицы www.tg-me.com/google_sheets/843

6: 🚜 Собиратыр-тыр-тыр 🚜 www.tg-me.com/google_sheets/626

7: OR / AND в функции FILTER www.tg-me.com/google_sheets/434

8: 8 советов о том, как ускорить работу вашего документа в Google Таблицах www.tg-me.com/google_sheets/143

9: Сравнение списков в Таблицах. Способ 3 — сравниваем функциями FILTER / MATCH www.tg-me.com/google_sheets/515

10: ​​В правиле условного форматирования можно использовать данные другого листа www.tg-me.com/google_sheets/338

11: Telegram бот, который записывает всё, что видит в Google Таблицу www.tg-me.com/google_sheets/541

12: ЗАПУСКАТОР – реагируем на изменения формул / Таблицы и запускаем скрипты www.tg-me.com/google_sheets/719

13: отправлятор таблиц в телеграм чаты. бесплатно и по расписанию www.tg-me.com/google_sheets/604

14: ОТПРАВЛЯТОР 2.0. Создаём из Таблиц PDF и JPG и рассылаем в Телеграм по заданному расписанию www.tg-me.com/google_sheets/643

15: Скрипт: создаём в Таблице триггер и скрипт, который не увидят редакторы и владелец www.tg-me.com/google_sheets/753

16: Проблемы с IMPORTRANGE() www.tg-me.com/google_sheets/822

17: Отображаем функцией =IMAGE() картинки из Google Диска в ячейке www.tg-me.com/google_sheets/467

18: Важный скрипт. Связанные выпадающие списки из кэша www.tg-me.com/google_sheets/408

19: VLOOKUP(ВПР) + SORT www.tg-me.com/google_sheets/13

20: Как одной функцией ВПР (VLOOKUP) подтянуть данные сразу из трех столбцов www.tg-me.com/google_sheets/57

P.S. @vitalich, спасибо в очередной раз!

Сумма прописью по-английски

Друзья, сегодняшний скрипт позволяет получить сумму прописью по-английски в формате "ONE THOUSAND ONE HUNDRED USD DOLLARS AND 50 CENTS" для 1100,50.

Спасибо за скрипт Людмиле из нашего чата, за основу она взяла вопрос на stackoverflow с примером подходящего скрипта и немного доработала его.

Скрипт будет работать и как пользовательская функция – смотрите скриншот. Просто вставьте код в редактор скриптов, далее введите в таблице =words() и сошлитесь на ячейку с числом.

Таблица с кодом
Код отдельно

PS Также по этой теме есть дополнение для Google Таблиц "numbertext" от Александра Иванова

Проектная диаграмма - Условное форматирование

Как сделать нечто вроде проектной диаграммы за счет заливки ячеек условным форматированием:
1. Сначала с помощью SEQUENCE формируем последовательность дат - от начала первого этапа до окончания последнего. Здесь в столбце B - даты начала этапов проекта, в C - окончания.
=SEQUENCE(1;МАКС(C:C)-МИН(B:B)+1;МИН(B:B);1)

2. В ячейках с датами в первой строке меняем формат: вращение текста на 90 градусов, числовой формат в духе "dd mmm" (чтобы было короче, без года), за счет этого можем сделать минимальную ширину столбцов.

3. Создаем правило условного форматирования. Наша задача красить ячейку, если дата в ее столбце входит в период из ее строки. Можно сделать у ячеек границы какого-нибудь цвета, это будет более четко выделять отдельные дни (в примере желтые границы, и синяя заливка в условном форматировании).

Формула будет выглядеть так:
=AND(первая дата>=начало первого этапа;первая дата<=окончание первого этапа)

=AND(D$1>=$B2;D$1<=$C2)

Первая строка закреплена $, так как мы всегда смотрим на дату в этой строке (это заголовки). А у начала и окончания этапов закреплены столбцы, чтобы при смещении вправо мы в любом случае сравнивали каждую дату с началом и окончания этапов. Строки 2 не закреплены, так как в следующих строках мы уже смотрим на даты следующих этапов.

P.S. Если хочется исключить выходные дни, можно добавить условие (номер недели < 6, то есть не СБ или ВС) в условное форматировании:
=И(D$1>=$B2;D$1<=$C2;ДЕНЬНЕД(D$1;2)<6)
=AND(D$1>=$B2;D$1<=$C2;WEEKDAY(D$1;2)<6)

Дальше - только полет вашей фантазии. Можно отдельным правилом красить выходные другим цветом. Каким-нибудь грустным, если все-таки предполагается там работать 😥

Ссылка на таблицу с примером (Сделать копию)

Еще про диаграмму Ганта: Диаграмма Ганта с помощью одной формулы

Два скрипта от нашего подписчика
– создаем список листов
– вставляем выпадающие списки по этому списку

Друзья, недавно в нашем чате Александр Кулешов предложил два скрипта для решения двух проблем:

1. первый скрипт создаёт список листов Таблицы и вставляет его на лист, помимо списка листов вставятся чекбоксы (выключенные);

2. второй скрипт проходится по этому списку листов и если пользователь напротив названия листа активировал чекбокс – скрипт вставляет в выбранный столбец этого листа выпадающие списки из листа "значения";

Таблица с примером (делайте копию, чтобы попробовать): тут

Код скриптов отдельно: pastebin.com/XZ70VzmU

Спасибо Александру, заходите в наш чат и изучайте скрипты и Таблицы, на нашем канале множество примеров – оглавление 😎

Отправлятор / Удалятор постов и сообщений Телеграм
(на гифке - отправляем сообщения, в т. ч. делаем реплай)

Отправлятор / Удалятор постов и сообщений Телеграм
(на гифке - удаление сообщений)

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

С помощью нашей Таблицы вы сможете и отправлять и удалять ботом сообщения как в каналах (для этого бот должен быть админом канала с правами), так и в личных чатах с пользователями (для этого бот должен уже контактировать с пользователем в личном чате, по крайней мере кликнуть /start)

❗️ Полная инструкция
🚜 Таблица
⚙️ Код отдельно

Наш чат

Функция ОКРУГЛТ / MROUND - округление с заданной точностью

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

Допустим, вы хотите округлить цены, чтобы они всегда заканчивались на 0, тогда вторым аргументом функции будет 10.
А если надо, чтобы цены заканчивались на 0 или 5 - то 5 (число будет округляться до ближайшего кратного 5).

=MROUND(число;точность)

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