Как выгрузить в Таблицу курс доллара с 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 t.me/google_sheets/616

2. СОБИРАТОР 4.0 t.me/google_sheets/661

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

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

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

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

7: OR / AND в функции FILTER t.me/google_sheets/434

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

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

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

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

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

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

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

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

16: Проблемы с IMPORTRANGE() t.me/google_sheets/822

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

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

19: VLOOKUP(ВПР) + SORT t.me/google_sheets/13

20: Как одной функцией ВПР (VLOOKUP) подтянуть данные сразу из трех столбцов t.me/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(число;точность)

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

Каналы, которые мы читаем (иногда ведем) и рекомендуем

Друзья, вот вам нерекламная подборка каналов, которыми мы хотим поделиться. Потому что читаем (в одном случае пишем) сами. Они нас даже не просили об этом (вроде бы).

Но прежде всего приглашаем в наш табличный чат. Это как бы и не канал, зато можно зайти, спросить и получить помощь. И, наоборот, помочь другим:
https://tttttt.me/google_spreadsheets_chat

От Жени
Орёл и кошка - этот канал ведут мои друзья, пара, которая релоцировалась в Турцию с котами. Про жилье, еду и местный колорит.

Например:
– Как улететь с котом в Армению: t.me/orel_i_koshka/20

– Влажный бургер! Семейный бизнес, превратившийся в туристический феномен Стамбула: t.me/orel_i_koshka/73

От Рената

CEO Readz - сооснователь, совладелец и в прошлом CEO издательства МИФ Артем Степанов про деловые книги.

Николай Павлов (Планета Excel) - главный маэстро Excel в России теперь есть в Телеграме. Ура! Николая, его статьи и книги категорически рекомендую.

Личный канал - это как моя личная страничка в соцсети. Ни слова про таблицы. Бег, книги, футбол и прочая жизнь. Заглядывайте в гости

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

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

Обычная ссылка на другой лист выглядит так:
='Москва'!A:B

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

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

Чтобы превратить полученную текстовую строку в ссылку, используем функцию INDIRECT - она ровно для этого и используется.
=INDIRECT("'" & ячейка с названием листа & "'!диапазон")

И отправляем это внутрь ВПР'а как второй аргумент:
=VLOOKUP(значение для поиска; INDIRECT("'" & ячейка с названием листа & "'!диапазон"
) ; номер столбца ; 0)

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