Находим последнее значение с помощью 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, Таблицам и скриптам: тыц
⭐️ Оглавление канала: ты-дыц
⭐️ Самый табличный чат на свете: бадабум

=IF(вы_любите_наш_канал ; поддержите_нас ; )

Друзья, мы с 2017 года помогаем вам с таблицами и скриптами.

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

Вычисляем период в днях/месяцах/годах: функция РАЗНДАТ / DATEDIF

Если вам нужно вычислить разницу между двумя датами не в днях (для чего достаточно вычесть из одной даты другую или воспользоваться функцией ДНИ / DAYS), а в месяцах или годах (например, возраст) — пользуйтесь функцией РАЗНДАТ / DATEDIF. Она не является документированной в Excel (то есть при ее вводе не будут отображаться всплывающая подсказка с аргументами, Excel не предложит ее дописать, ее нет в справке), но не обращайте на это внимание — она работает во всех версиях. И в Google Таблицах тоже!

 =РАЗНДАТ(дата_начала; дата_окончания; единица измерения)

Первые два аргумента — даты начала и окончания периода. Они могут быть указаны прямо в формуле в кавычках либо в виде ссылок на ячейки с датами, а также быть заданными функцией СЕГОДНЯ / TODAY.

Единица измерения задается в кавычках. Есть следующие возможные варианты:

"d" — число дней (такой параметр не имеет особого смысла, так как для этой задачи подойдет и функция ДНИ / DAYS, и просто вычитание);
"m" — число полных месяцев в периоде;
"y" — число полных лет в периоде;
"md" — разница в днях без учета месяца и года (например, между 01.01.2021 и 15.06.2022 — 14 дней);
"ym" — разница в месяцах без учета дня и года (например, между 01.01.2021 и 15.06.2022 — 5 месяцев);
"yd" — разница в днях без учета года (например, между 01.01.2021 и 15.06.2022 —165 дней).

Сумма по строке в новых реалиях, c условием!
(💥 пост с домашним заданием)

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

Сегодня будем считать сумму по столбцам D, E, F в формуле массива при условии, что столбец A непустой.

Итак, формула целиком:
=BYROW(A1:F10;LAMBDA(ROW;if(INDEX(ROW;1)<>"";SUM(OFFSET(ROW;0;3;1;3));)))

Что мы в ней делаем:
— В функцию BYROW передаем весь диапазон
— В функции LAMBDA обращаемся к каждой строке диапазона как к ROW
— В привычной функции IF / ЕСЛИ обращаемся к первому элементу строки с помощью INDEX, проверяя есть ли значение
— Если IF / ЕСЛИ возвращает истину, то с помощью функции OFFSET / СМЕЩ отступаем от первой ячейки строки три столбца и суммируем этот отрезок
— Если IF / ЕСЛИ возвращает ложь, то не возвращаем ничего

💥 Домашнее задание: попробуйте написать формулу массива для столбца B с использованием новых функций, формула должна суммировать столбцы D, E, F для непустого столбца A. Отправьте свой вариант в комментарии.

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

💥 Возвращаемся с ответом на домашку!

(Был вопрос: напишите формулу массива, которая посчитает сумму построчно для C:E, для непустого столбца A)

Решение от @vitalich,
=BYROW(C2:E9; LAMBDA(lr; IF(ISBLANK(INDEX(A1:A9; ROW(lr);));;SUM(lr))))

Объясняем логику:
1) передаем в BYROW диапазон, который будем суммировать;

2) мы не можем в LAMBDA написать функцию вроде IF(A1:A9<>""; SUM(lr);) из-за особенности функции, но за то мы можем получать номера строк переданного массива C2:E9 и передавать их в INDEX(A1:A9; ROW(lr)), чтобы получить построчно A1, A2, A3 и далее проверить эти ячейки на наличие значения;

3) Если ячейки A1, A2, A3 пустые не выводим ничего, если заполнены - выводим сумму по строке SUM(lr);

💥 Если найдете другой вариант решения - напишите его в комментарии.

ИЗМЕРЯЕМ СКОРОСТЬ ФОРМУЛ В GOOGLE ТАБЛИЦАХ

Друзья, хотели узнать, какая формула работает быстрее, а какая медленнее?

Ловите статью от Михаила Смирнова, а в комментариях пишите свои наблюдения и свои цифры https://telegra.ph/Google-Sheets--Vremya-raschyota-formuly-06-06-2

Всем быстрых Таблиц и большой зарплаты 😎

Срезы - удобные и наглядные фильтры, которые можно перемещать по листу "поверх" ячеек. В Google Таблицах применяются как к диапазонам, так и к сводным таблицам (в последнем случае есть небольшие нюансы, о которых этот пост и есть).

https://teletype.in/@renat_shagabutdinov/sheets_slicer

Про срезы в Excel читайте здесь

Продолжаем λямбдовое!

Во-первых, рекомендуем мини-курс про новые функции от Бена Коллинса, если знаете английский, то пройдите эти бесплатные уроки и сможете использовать новые функции в офисном бою: https://courses.benlcollins.com/p/lambdafunctions

Ну а не знаете английский – вам придётся терпеть нас дальше 🙂

Во-вторых, самое удачное, на мой взгляд, решение домашки из предыдущего поста (нужно было написать функцию, которая, если столбец A заполнен - суммирует столбцы D, E, F)

Функция:
=MAP(A1:A6; D1:D6; G1:G6; LAMBDA(a; sumStart; sumEnd;if(a="";;sum(sumStart:sumEnd))))

Работает так: в map передаем три массива: столбец проверки, столбец начала суммирования и окончания суммирования.

Далее проверяем столбец проверки, если он не заполнен, то не возвращаем ничего if(a="";;, а если заполнен, то собираем с использованием двоеточия диапазон суммирования: столбец начала:столбец окончания sumStart:sumEnd и считаем его сумму.

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

Про силу двоеточия писали здесь.