Символы подстановки в функциях 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 — Публикация листов

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

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

Видеоурок по 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) Заменить всё!

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

Наш чат

Ссылка на другую Таблицу может быть с http или без, с указанием листа (/edit#gid=0) в конце или без, давайте заменим любые вхождения ссылки на на что-то другое.

1) Найти и заменить
2) Найти: (.+)ID Таблицы$|(.+)
3) Заменить на: наш текст
4) Галочку на "использование регулярных выражений"
5) Заменить всё!

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

Наш чат

Используем функцию QUERY из GAS-скриптов

Отсортировать, отфильтровать и сгруппировать данные с помощью функции QUERY можно из скриптов Google Таблиц.

Показываем пример от @vitalich.

Функция в Таблице:
=QUERY(A1:C8; "SELECT A, SUM(B) WHERE C>30 GROUP BY A";1)

И её реализация в скриптах:
function queryAPI() {
let sheetId = SpreadsheetApp.getActive().getId();
let sheet = 'Sheet1';
let req = 'SELECT A, Sum(B) WHERE C>30 GROUP BY A';
let outputFormat = 'out:csv';
let urlTemplate = 'https://docs.google.com/spreadsheets/d/%s/gviz/tq?tqx=%s&sheet=%s&tq=%s';
let url = Utilities.formatString(urlTemplate, sheetId, outputFormat, sheet, encodeURIComponent(req));
let params = {
method: 'get',
headers: {
'Authorization': "Bearer " + ScriptApp.getOAuthToken(),
muteHttpExceptions: true
}
};
let query = UrlFetchApp.fetch(url, params);
let text = query.getContentText();
let output = text.split('\n').map(f => f.split(',').map(g => JSON.parse(g)));
console.log(output);
let sheetPaste = SpreadsheetApp.getActive().getSheetByName('result');
sheetPaste.clearContents();
sheetPaste.getRange(1, 1, output.length, output[0].length).setValues(output);
};

Вводим 2,3,5 в ячейку и вторая, третья и пятая строки выделяются /УСЛОВНОЕ ФОРМАТИРОВАНИЕ

Друзья, показываем простой трюк.

Представьте, вы рассказываете про Таблицу коллегам в зуме и в процессе рассказа выделяете то одну, то другую строку через ввод номера, чтобы было нагляднее. Смотрите гифку.

Как это реализовать:
1) Строки, которые нужно выделить будем вводить в ячейку E2;
2) Выделяем диапазон данных, у нас это A:C;
3) Условное форматирование;
4) Добавить правило > форматирование формулой > вводим формулу:
=match(row($A1); split($E$2;",");0)

Что делает формула УФ: делит ячейку с номерами строк по разделителю запятая с помощью SPLIT, получает массив номеров, далее ищет каждый номер строки в этом массиве с помощью MATCH, если находит - возвращается ИСТИНА и условное форматирование закрашивает эту строку.

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

Чипируем таблицы и документы

В таблицы и документы можно вставлять чипы (smart chips) - нарядные ссылки на пользователей, документы (другие таблицы, например), события в календаре.
Можно через меню "Вставка". А можно просто ввести собачку @ и начать вводить название события из календаря, электропочту коллеги или название таблицы/документа.

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

Добавление чипа с пользователем не откроет ему доступ автоматически.

---
⭐️ Наш курс по Excel, Таблицам и скриптам: тыц
⭐️ Оглавление канала: ты-дыц
⭐️ Самый табличный чат на свете: бадабум

А в Google Документах можно еще и даты чипировать. Например, ввести завтрашнюю дату.

Помимо чипов, о которых мы писали чуть выше, в Google Документах есть раскрывающиеся списки. Хорошая штука, чтобы выбирать один из нескольких статусов.
Можно использовать один из стандартных шаблонов или создать свой.

Вставка - Раскрывающийся список

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

---
⭐️ Наш курс по Excel, Таблицам и скриптам: тыц
⭐️ Оглавление канала: ты-дыц
⭐️ Самый табличный чат на свете: бадабум