Книжно-табличные новости

В издательстве ДМК Пресс вышли две хорошие книги про Excel:
Визуализация данных при помощи дашбордов и отчетов в Excel (мы писали про нее ранее)

Приручи данные с помощью Power Query в Excel и Power BI
Бодрая книга по Power Query: от основ (зачем это нужно, интерфейс, создание запросов) до языка M (на котором "записываются" шаги запросов в Power Query и который можно использовать для расширения возможностей). Нескучно и доступно написана, много иллюстраций и скриншотов, примеров - все на уровне.

И несколько о слов о совсем новой и совсем старой книгах, увы, недоступных (в первом случае - вероятно, пока) на русском.

Microsoft Excel Inside Out (Office 2021 and Microsoft 365)
Лучшая книга по новому Excel и одна из лучших вообще. Будет полезна и тем, кто хочет освоить новые функции, и вообще всем, кто хочет системно изучить Excel или иметь качественный справочник по всем вопросам.

Максимально глубокое погружение, есть такие нюансы, про которые вообще нигде больше не прочтешь или которые придется очень долго искать.
Есть обзор нововведений в 365 и 2021 и практически все ключевые темы.

Ctrl+Shift+Enter Mastering Excel Array Formulas
Книга по формулам массива, вышедшая задолго до появления динамических массивов в Excel, но по-прежнему актуальная, тем более что у большинства еще версии до 2019 включительно, где этих самых чудо-массивов нет.

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

Эти и другие книги в обзоре литературы для всех героев ячейки и формулы:
https://teletype.in/@renat_shagabutdinov/excellent_books

ТРИ КОРОТКИХ СКРИПТА ИЗ НАШЕГО ЧАТА

Переход к последнему листу в Таблице. Если положить внутрь onOpen(){} – пользователь при открытии Таблицы будет автоматически перемещаться на последний лист.
Спасибо Даниилу Осипову!

function go_last_sheet() {
const ss = SpreadsheetApp.getActiveSpreadsheet().getSheets();
ss[ss.length - 1].activate();
}

—————————————

Определяем индекс последней строки с данными в выбранном столбце.
Спасибо создателю циклов!

function getLastRow(data, column) {
var max = 0;

for (x in data) {
if (data[x][column - 1]) {
max = x * 1 + 1;
};
}

return max;
}

—————————————

Превращаем текст, написанный болдом в текст вида *текст*. Лайфхак – если в v сослаться на само значение (смотрите скриншот), то функция будет обновляться при его изменении.
Спасибо Роману Игнатову!

function getBold(r,v) {
return SpreadsheetApp.getActive().getActiveSheet().getRange(r).getRichTextValue().getRuns().map(run=>
run.getTextStyle().isBold()?`*${run.getText()}*`:run.getText()
).join(' ');
}

Полное оглавление нашего канала: тыц
Наш чат: @google_spreadsheets_chat

Наш чат: @google_spreadsheets_chat

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

Работает чат так – отправляете вопрос и добавляете к нему Таблицу с примером. Если у кого-то есть время – вам помогают.

В чате есть правила:
1. Таблица с примером - обязательна. Проверяйте, чтобы в Таблице не было личных данных (телефоны, фамилии).

2. Кратко формулируйте вопрос.

3. Вам ответили в чате - там и продолжайте общаться, не пишите человеку в лс.

4. Донаты приветствуются, но не обязательны. Донаты делятся между модераторами чата (они же – активные помощники). Реквизиты появятся, если отправить в чат "донат".

5. Не хотите разбираться в своем вопросе вообще - пообещайте какую-то сумму за решение.

6. Не просите написать за вас с нуля скрипт или телеграм бота – это требует и навыка и времени, такие вещи нужно заказывать.

Теперь вы готовы, наш чат: @google_spreadsheets_chat

Логично. Функции SWITCH и CHOOSE

Две полезные функции для проверки условий, с которыми можно избавиться от многоэтажных вложенных ЕСЛИ / IF.

ВЫБОР / CHOOSE
Первый аргумент ВЫБОРа - число, а все следующие - что нужно вернуться для значений этого числа, последовательно для 1, 2, 3 и так далее.
Например, такая функция будет переводить оценку по пятибальной шкале в текстовую:

=ВЫБОР (ячейка с оценкой; "один"; "два"; "три"; "четыре"; "пять")

ПЕРЕКЛЮЧ / SWITCH
В Excel появилась в версии 2016, в Таблицах называется SWITCH даже при русском языке формул.
В ПЕРЕКЛЮЧ первый аргумент - выражение (значение из ячейки), а все последующие - это пары "значение - результат". Последний аргумент - результат для всех остальных случаев.
Например, можно задать разную скидку для трех каналов продаж, а для остальных - нулевую:
=SWITCH(ячейка с каналом продаж;"Сайт";15%;"Магазин";12%;"Ярмарка";10%;0)

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

Кстати, в Excel ВЫБОР до появления динамических массивов и функций для склеивания массивов (VSTACK, HSTACK) использовался для объединения нескольких диапазонов. Например, чтобы поменять порядок столбцов для ВПР.

=ВЫБОР({1;2};первый диапазон; второй диапазон)

То есть в качестве номера задаем сразу массив в фигурных скобках - и ВЫБОР возвращает оба диапазона, указанные в аргументах, вместе.

Скрипт – добавляем к значению в ячейке новое значение, которое ввел пользователь

Сегодня отвечаем на вопрос Никиты из нашего чата.

Скрипт (его код - ниже) работает супер просто - запускаем, вводим значение в "Inputbox" (форму для ввода) и скрипт добавляет это значение к тому, которое есть в ячейке.

Обратите внимание - в гифке я кликаю просто на картинку и это запускает скрипт. Как это сделать – вставляем любую картинку в Таблицу, кликаем на неё правой кнопкой, кликаем на три точки справа, "назначить скрипт" и вводим название скрипта, который будем запускать при клике на картинку.

А в следующем посте рассмотрим, как выводить html-окно с фиксированным вариантами для выбора. Оставайтесь с нами и заходите в наш чат, задавайте вопросы.

function pasteValue() {
const value = Browser.inputBox('Введите значение').toString();
const cell = SpreadsheetApp.getActiveRange();
const old_value = cell.getValue().toString();
cell.setValue((old_value.length ? old_value + ';' : '') + value);
};

Биты в байты, пинты в литры, столовые ложки в чайные. Функция ПРЕОБР / 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=t.me/google_sheets"

Доступно 5 параметров:
cht=qr //тип графика, для QR-кода – всегда qr
chs=70*70 //размер изображения
chl=t.me/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 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, спасибо в очередной раз!