Кликаем – строки выделяются

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

Скрипт из ГИФки проверяет, какой диапазон был сохранён в ScriptProperties в предыдущий раз, убирает с этих строк заливку, далее закрашивает строки, которые были выделены сейчас и сохраняет этот диапазон в ScriptProperties (чтобы при следующем запуске убрать заливку с него).

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

🤓 Если найдёте триггеру применение в своем Табличном хозяйстве – напишите нам в комментариях.

const pr_key = 'lr';
const colour = '#800080'; //en.wikipedia.org/wiki/Web_colors

function onSelectionChange(e) {
const range = e.range;
const sh = e.source.getActiveSheet();
const range_a1 = ${range.rowStart}:${range.rowEnd};
const last_range_a1 = ScriptProperties.getProperty('lr');
if (last_range_a1) {
sh.getRange(last_range_a1)
.setBackground(null);
};
sh.getRange(range_a1)
.setBackground(colour);
ScriptProperties.setProperty('lr', range_a1);
};

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

Нарастающий итог: закрепляем только первую ячейку в диапазоне

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

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

2. Вводим двоеточие и ту же самую ячейку, но уже оставляем относительной. Получается диапазон с началом и концов в одной ячейке, но конец не закреплен - так что при протягивании/копировании формулы будет меняться.
=СУММ($B$2:B2)

3. Протягиваем и получаем диапазон с началом в одной и той же ячейке и концом в текущей строке.

Альтернативное решение, одна формула массива:
=ARRAYFORMULA(SUMIF(row(B1:B16),"<="&row(B1:B16),B1:B16))

Про такую формулу мы писали вот здесь

TOO MUCH IMPORTRANGE

Друзья, представьте ситуацию – у вас 100 Таблиц, в них точно есть функции IMPORTRANGE и вы хотите узнать, какие Таблицы указаны внутри этих функций. То есть, откуда тянутся данные.

Мы подготовили скрипт, который решит эту проблему (скрипт и короткое описание: pastebin.com/EcRZwhg0).

А зачем это может понадобиться?
Расскажу про проблему наших клиентов – одна Таблица была использована внутри IMPORTRANGE слишком много раз (>500) и после очередного раза всё перестало работать, данные из Таблицы перестали передаваться, пользователи видели ошибку как на картинке, а еще в эту несчастную Таблицу стало нельзя добавлять новых пользователей с правами чтение или редактирование.

Наше решение:
1) Мы выяснили, какие Таблицы у нас лежат внутри рабочей папки нашим скриптом Drive Columbus;

2) Прошлись по этому списку Таблиц скриптом и узнали, какие Таблицы указаны внутри IMPORTRANGE в этих Таблицах;

3) Сделали копию Таблицы-донора и написали скрипт, который вставил формулу IMPORTRANGE с этой Таблицей в ряд Таблиц по списку;

4) Использовали скрипт от Михаила Смирнова, который прошелся по списку Таблиц с обновленной формулой IMPORTRANGE и раскрыл доступ к Таблицам автоматически, кликать на "расшарить доступ" не пришлось;

В итоге достаточно неприятная проблема была решена и сейчас сотни Таблиц по-прежнему работают.

Чего нам не хватало в Excel

Ну, например, функции IMAGE.
В русскоязычном Excel она будет называться ИЗОБРАЖЕНИЕ.

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

Также обязательным является один аргумент - ссылка на изображение.

Попробовали закинуть Excel с этой функцией в Таблицы - все работает.

В обратную сторону - в формуле будет @IMAGE (которая будет вызывать ошибку ИМЯ / NAME - дескать, не знает Excel такого), то есть если у вас Excel на русском, то придется вызвать окно "Найти и заменить" и поменять на русскоязычное название. В Excel на английском - убрать собачку из формул. Разумеется, все это при наличии актуальных обновлений и Microsoft 365.

Чего нам не хватало в Таблицах

Ну, например, функций ПРОСМОТРX / XLOOKUP, ПОИСКПОЗX / XMATCH и LAMBDA.
И вот они здесь (точнее, на подходе - ждем, когда все они будут доступны у всех).

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

Про функцию LAMBDA мы писали здесь (она и в Excel появилась недавно). Новые пользовательские функции (Named functions) - по сути и есть LAMBDA, с возможностью задать имя и делать это все в специальном интерфейсе (это будет боковая панель, вызываемая в меню Data / Данные - пока можно посмотреть на гифку в новости от Google).
Про ПРОСМОТРX здесь.
ПОИСКПОЗX / XMATCH - это тот же MATCH, но в новой функции по умолчанию ведется точный поиск, а еще можно искать снизу вверх, а не сверху вниз.

В ближайшее время расскажем подробнее про новинки.

Обновили табличку со сравнением двух редакторов:
Схватка двух ёкодзун. Сравнение Google Таблиц и Excel

Перемещение после ввода данных в ячейку

Друзья, думаем, многие из вас знают, что после ввода данных можно нажимать не только Enter. Можно завершить ввод и попасть в любую соседнюю ячейку:
Enter - вниз
Shift-Enter - вверх
Tab - вправо
Shift-Tab - влево

Если вы заполнили несколько ячеек в одной строке через Tab и потом нажмете Enter, то переместитесь в начало следующей строки (под первый заголовок).

Ну а еще в Таблицах можно заранее выделить диапазон или строку, куда вы будете вводить данные. И тогда при нажатии Enter вы будете перемещаться к следующей ячейке в пределах этого диапазона! Если выделена только строка (например, заголовки), то к следующей ячейке в столбце справа. Если диапазон - то сначала в пределах первого выделенного столбца, а потом к первой ячейке во втором выделенном столбце.

Ну а в Excel еще есть Ctrl+Enter - после ввода остаемся в той же ячейке. А если выделено несколько ячеек, то ввод будет осуществляться в каждую из них! Даже если это несмежные диапазоны/ячейки.

И в Excel можно изменить то направление, куда мы перемещаемся после нажатия Enter - в параметрах (в разделе "Дополнительно" / Advanced).

Накопительный итог в новых реалиях

Господа, после того, как в таблицах появились функции LAMBDA, SCAN, MAP, BYROW, BYCOL, REDUCE, MAKEARRAY считать накопительный итог по каждой строке стало гораздо проще.

Рассказываем. Функция SCAN умеет возвращать промежуточные значения (читайте - по каждой строке), путем применения вложенной функции LAMBDA.

Синтаксис такой:
=SCAN(начальное значение [аккумулятор]; диапазон; LAMBDA)

Формула для накопительного итога по каждой строке:
=SCAN(0; A1:A5; LAMBDA(acc;x; acc+x))

Значит,
1) задаём аккумулятор и диапазон значений 0; A1:A5
2) обращаемся к ним в лямбде, как к acc и x, добавляя каждое значение, построчно, к аккумулятору, acc + x
3) SCAN выводит аккумулятор, он же - накопительный итог, по каждой строке в Таблицу

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

Символы подстановки в функциях Google Таблиц

Друзья, хотим напомнить про символы подстановки (wildcard), тем более что в новых функциях они работают несколько иначе.

Итак, символов подстановки есть три:
* (звездочка) - любое количество любых символов, в том числе нулевое, то есть на месте звездочки может не быть ничего.
? (знак вопроса) - один любой символ. В отличие от звездочки, на месте знака вопроса точно должен быть символ: пробел, цифра, буква, символ
~ (тильда) - используется ,чтобы искать именно звездочку (~*), знак вопроса (~?) или тильду (~~).

Символы подстановки работают по умолчанию в следующих функциях:
- ВПР / VLOOKUP и ПОИСКПОЗ / MATCH
- СУММЕСЛИ (МН) / SUMIF(S), СЧЁТЕСЛИ(МН) / COUNTIF(S), СРЗНАЧЕСЛИ(МН) / AVERAGEIF(S)
- COUNTUNIQUEIFS
- Функциях баз данных. БДСУММ / DSUM, ДСРЗНАЧ / DAVERAGE, БСЧЁТ / DCOUNT, БСЧЁТА / DCOUNTA и других
- ПОИСК / SEARCH

В новых функциях ПОИСКПОЗX / XMATCH и ПРОСМОТРX / XLOOKUP символы подстановки по умолчанию не работают! Но у них есть аргумент "режим_сопоставления" (match_mode), в котором есть следующие варианты:
0 - точный поиск (по умолчанию). Символы подстановки не работают.
1 ближайшее большее значение (или точное совпадение)
-1 ближайшее меньшее значение (или точное совпадение)
2 - точный поиск с символами подстановки.

То есть чтобы все работало как в ВПР с последним аргументом, равным нулю, нужен режим 2. Иначе магия звездочек и знаков вопросов не будет работать.

P.S. В Excel символы подстановки работают еще и в окне "Найти и заменить" и в условиях расширенного фильтра.

Всякое по теме:
Примеры условий с символами подстановки в функциях СУММЕСЛИ / SUMIF, СЧЁТЕСЛИ / COUNTIF, СРЗНАЧЕСЛИ / AVERAGEIF
СУММЕСЛИМН / SUMIFS с флажком (включаем и выключаем условие)
Функции баз данных
ВПР со звездочкой

onEdit скрипт, который предлагает вернуть старое значение

Друзья, привет!

Очередная задачка наших клиентов - есть цветные строки, редактирование которых производить нежелательно.

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

Сам код
function onEdit(e) {
var range = e.range
//проверяем фон ячейки, которая редактируется
if (range.getBackground() != '#ffffff') {
var old_value = e.oldValue;
var ui = SpreadsheetApp.getUi();
//выводим диалоговое окно
var response = ui.alert(
Сохранить изменения - OK\n\nВернуть старое значение [${old_value}] - CANCEL,
ui.ButtonSet.OK_CANCEL);

//обрабатываем результат выбора пользователем, CANCEL - возвращаем старое значение, ОК - ничего не делаем
response == ui.Button.CANCEL ? range.setValue(old_value) : '';
}
}

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

PS Недавно в нашем чате был вопрос о том, как переносить строки по чекбоксу с помощью onEdit, вот пост и про это

Задача: посчитать количество значений (или что-то еще, не столь важно - мы рассмотрим на примере счета) в каждой строке одной формулой.

Здесь можно воспользоваться одной из новых функций, предназначенных для использования вместе с LAMBDA, а именно BYROW.
Она позволяет применить вычисление к каждой строке массива.
Синтаксис:

BYROW(массив данных ; LAMBDA(строка; вычисление (строка)))

Первый аргумент - весь диапазон, в котором надо обработать каждую строку.
Второй - функция LAMBDA. В ней первый аргумент - переменная (называйте как хочется), обозначающая каждую строку в массиве (первом аргументе BYROW). Второй - то, что мы делаем с каждой строкой. То есть вводим формулу, в которой ссылаемся по тому же самому имени к строке.

Если нужно считать количество значений в каждой строке, применяем СЧЁТЗ / COUNTA:

=BYROW(D2:Z;LAMBDA(массив;СЧЁТЗ(массив)))

P.S. Если нужно обрабатывать столбцы - то, соответственно, пользуем BYCOL.

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

Смотрите также:
Накопительный итог с помощью функций SCAN и LAMBDA

А как в Excel?

Ну если у вас Microsoft 365, то наслаждайтесь LAMBDA и вспомогательными функциями - все будет работать аналогично.
А если версия до 2021?

Всегда есть простор для формульно-массивного безумия!
Вот один из вариантов - предлагайте в комментариях свои, как бы вы решили такую задачу😉

=СЧЁТЗ(СМЕЩ(F3;СТРОКА(3:11)-3;0;1;100))

В более общем виде:

=СЧЁТЗ(СМЕЩ(первая ячейка диапазона;СТРОКА(строки диапазона)-корректировка ;0;1;число столбцов в диапазоне))

Что тут происходит?
Функция СМЕЩ / OFFSET выдает диапазон - шириной в сто столбцов (с запасом), высотой в одну строку, с началом в столбце F. Каждый раз смещаемся исходя из номера строки - на 0, 1, 2 и так далее строк вниз, получая тем самым ссылки на диапазоны, начинающиеся в F2, F3 и т.д. И с помощью СЧЁТЗ / COUNTA считаем количество значений.

На всякий напомним - в старых формулах массива Excel нужно заранее выделить диапазон (и это, конечно, минус старых массивов - потому что в случае с LAMBDA и вообще динамическими массивами можно ввести формулу в одну ячейку), где будет результат, и нажать Ctrl+Shift+Enter для ввода формулы. Фигурные скобки, показывающие, что это формула массива (но не нового типа), появляются автоматически.

Google Apps Script — Публикация листов

- показываем только данные
- публикация вручную
- публикация скриптом

Друзья, полезное от Михаила Смирнова: telegra.ph/Google-Apps-Script--Publikaciya-listov-09-17

Видеоурок по Excel для новичков: разделение и объединение текста

Друзья, привет! Делимся с вами уроком из курса «Магия Excel» — про то, как разделять текст на столбцы и как, наоборот, объединять несколько ячеек в одну строку.

https://www.mann-ivanov-ferber.ru/courses/magicexcel/#rec493432600

В курсе 55 таких видеоуроков — про сводные таблицы, функции поиска (ВПР, ИНДЕКС) и формулы массива, Power Query, визуализацию данных и даже обзор свежих функций 2022 года. Всего обучающего материала — на 700+ минут.

Курс «Магия Excel» прошли больше 1000 учеников, средняя оценка — 4,9 из 5.

Коллеги из МИФа сделали промокод на скидку 40% — по промокоду Magia40. Действует до полуночи 10 октября.

https://www.mann-ivanov-ferber.ru/courses/magicexcel/

Выделяем на диаграмме текущий месяц

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

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

Этот столбец будет вторым рядом данных - который будет "поверх" основного, и это будет выглядеть как выделение отдельных точек/периода. Этот ряд можно сделать с большим контуром и более ярким цветом.

Формула в общем виде
=ЕСЛИ(условие, по которому выбираются точки для выделения;значение из столбца с данными;"")

Например, если мы выделяем текущий месяц на диаграмме:
=ЕСЛИ(МЕСЯЦ(СЕГОДНЯ())=месяц в этой строке;значение в этой строке;"")

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

JOIN / TEXTJOIN по каждой строке в новых реалиях

Друзья, функции JOIN и TEXTJOIN соединяют значения из нескольких ячеек в одной.

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

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

=BYROW(A1:D5;LAMBDA(row;TEXTJOIN(" ";1;row)))

Как это работает
— В BYROW передаем диапазон, далее функция передает каждую строку диапазона в LAMBDA;
— В LAMBDA каждая строка диапазона используется как аргумент для функции TEXTJOIN;
— TEXTJOIN, в свою очередь, объединяет значения с разделителем пробел, отбрасывая пустые ячейки и выводит результат в каждую строку;

А замените BYROW на BYCOL - сможете соединять значения по столбцам 🤩

Еще про новые функции:
Накопительный итог построчно
Подсчёт значений построчно

Находим последнее значение с помощью XLOOKUP

Вы ищете значение в таблице, и вам нужно получить данные из последней, а не первой строки с искомым значением. Например, с последней продажей или курсом (последним в данном случае = по расположению строк, по сортировке).

ВПР / VLOOKUP ищет "сверху вниз", то есть если искомое значение встречается несколько раз, будет возвращаться первое (верхнее) значение.

Но теперь у нас есть новая функция XLOOKUP!
А в ней - аргумент "search_mode" (режим поиска). Задаем его равным -1 (минус единице), чтобы искать "снизу вверх".
По умолчанию он равен 1 (единице, это стандартный вариант "сверху вниз").

Функция в общем виде будет выглядеть так:
=XLOOKUP(искомое значение; просматриваемый диапазон ; возвращаемый диапазон ;;;-1)

Аргумент с режимом поиска последний, обратите внимание, что мы пропускаем два других: [missing_value] и [match_mode]. Это аргументы для возвращения значения в случае ошибки (когда ничего не найдено) и для использования символов подстановки/примерного поиска. В данном случае мы оставляем их по умолчанию - то есть в случае отсутствия искомого значения будет ошибка N/A, и будет вестись точный поиск без использования символов подстановки.

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

P.S. Конечно, с ВПР тоже можно пошаманить, добавив другую функцию - об этом мы писали аж 4 года назад - вот тут.

Немного Excel-экзотики: проговаривание ячеек (текст в речь)

Есть в Excel и такая опция. Можно воспроизвести содержимое ячеек - это касается и текста на русском/английском, и дат, и чисел.
Как и многие команды, эта недоступна на ленте инструментов. Ее можно добавить на панель быстрого доступа (Quick Access Toolbar).

Заходим в параметры Excel - Панель быстрого доступа (либо на самой панели в выпадающем списке выбираем "Настроить панель быстрого доступа").
Выбираем в выпадающем списке "Выбрать команды из" - "Все команды" (Choose commands from - All Commands). Вот она, магия - тут действительно все команды Excel, ряд из которых нигде больше не найдешь в принципе (например, то же проговаривание ячеек или мастер сводных таблиц и диаграмм из старых версий приложения).

Список длинный - вводите первую букву команды, чтобы быстрее найти необходимое.

Нас с вами интересуют команды "Проговорить ячейки" (Speak Cells) и "Прекратить проговаривание ячеек" (Stop Speaking).
Последняя выключит проговаривание принудительно. Иначе же будут проговорены все выделенные ячейки.
Если выделена одна ячейка, будут проговариваться и смежные.

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

Сколько в Таблице
листов?
ячеек?
ячеек заполнено?
можно еще создать ячеек?

Привет, друзья, такой вопрос задали недавно в нашем чате.

Покажем простой скрипт, который ответит на все эти вопросы. Чтобы получилось "образовательно" – расскажем про каждую строчку этого скрипта.

Код и комментарии: pastebin.com/e2vva9Rr

💡 Заходите в наш чатик и задавайте вопросы, но только сначала вам нужно будет пройти капчу, а для этого придется зайти в оглавление канала :)

Пара фокусов с "найти и заменить" из нашего чата

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

Добавим в конце каждой строки <br/>, убирая перенос строки

1) Найти и заменить
2) Найти: \n|$
3) Заменить на: <br/>
4) Галочку на "использование регулярных выражений"
5) Заменить всё!

Другие примеры "найти и заменить"

Наш чат