Спасибо Ренату 🙂

Друзья, мой соавтор по каналу Ренат Шагабутдинов решил сосредоточиться на своем проекте @lemur_excel, поэтому этот канал дальше буду вести только я, Евгений Намоконов.

Мы с Ренатом вместе написали книгу "Google Таблицы. Это просто. Функции и приемы"

Также мы вместе делали курс на Скиллбоксе и много лет вели этот канал бок о бок. Ренат больше писал про формулы, а я про скрипты.

У Рената в его канале @lemur_excel есть рубрики, например "хоткеи по понедельникам" и разные полезности для тех, кто работает с Excel.

Пожелаем Ренату удачи 👏

Горячие клавиши по понедельникам 🔥

Сегодня два сочетания для выделения столбца/строки.

Обратите внимание, что они работают и в официальных ("умных") таблицах, и просто на листе.

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

И эти сочетания работают при вводе формул!

Отличия по строкам
Вы хотите быстро выделить цветом ячейки, в которых план отличается от факта (один столбец от другого — в общем случае)?

1 Выделяем столбцы (можно быстро быстро выделить их сочетанием Ctrl + Shift + стрелка вниз)
2 Ctrl + G —> Выделить (Special)
3 Отличия по строкам (Row differences)
4 Красим выделенные ячейки нужным цветом. Готово!

Смотрим на GIF (без звука)

Функция ПОСЛЕД / SEQUENCE — видео
Продолжительность: 15:40

— Синтаксис функции
— Нумерация списка + новая функция TRIMRANGE и новый тип ссылок A.:.A
— ПОСЛЕД + ВЫБОРСТРОК для извлечения части строк из таблицы
— Разделение текста на символы
— Последовательность дат с выбором “Неделя / день”

Видео (это и другие) на сайте — доступно в России
Ютуб
Ссылка на файл с примерами

Нумеруем только видимые строки

Если мы хотим, чтобы при скрытии строк номера пунктов обновлялись автоматически (то есть нумеровались видимые в моменте строки, а не все, что есть на листе) — нужно использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ / SUBTOTAL.

Ибо она умеет обрабатывать только видимые (не скрытые) ячейки.

В нашем случае мы считаем значения, а не суммируем или что-то еще, то есть нужна функция СЧЁТЗ / COUNTA. Внутри SUBTOTAL ее код — 103 (у SUBTOTAL тип вычисления указывается в первом аргументе).

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

Чтобы сформировать ссылку на диапазон от начала до текущей строки, сделаем так:
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;$B$2:B2)

$B$2:B2 — начало всегда в B2, а конец диапазона в строке с формулой. Такую ссылку не задать через F4, придется попотеть и ввести доллары вручную.

_ _ _
Ну а заодно напоминание про 🔥 клавиши:
Ctrl + 9 — скрыть строку
Ctrl + Shift + 9 — отобразить скрытые строки

Нарастающий итог: на старых и новых формулах

Старые формулы:
Вводим формулу, в которой суммируем диапазон, начинающийся и заканчивающийся в одной и той же ячейке — первой ячейке с данными.
Но закрепляем первую из них (начало):

=СУММ($C$2:C2)   

И при протягивании диапазон будет меняться на $C$2:C3, $C$2:C4 и так далее — то есть будем суммировать вплоть до той строки, в которой формула.

Новые формулы: вводим одну формулу, которая работает для всего столбца

=MAP(C2:.C1048576; LAMBDA(x; СУММ(C2:x)))

Что тут происходит? Обрабатываем все ячейки в столбце C, начиная со второй строки. Но исключаем пустые ячейки — за это отвечает точка после двоеточия в ссылке C2:.C1048576.
И для каждого значения x в этом массиве вычисляем сумму от C2 до этого значения.

Спасибо Николаю за комментарий — на больших объемах данных гораздо быстрее будет работать вариант со SCAN:

=SCAN(0; C2:.C1048576; LAMBDA(acc;val; acc + val))

Мой пример — для демонстрации того, что переменная в LAMBDA может быть ссылкой.
Это может пригодиться в том числе и в SCAN — например, для нарастающих итогов с условием. Пример можно посмотреть в этой статье:
https://shagabutdinov.ru/scanexcel

Хороший новогодний подарок для ваших знакомых экселье

Напоминаем — в продаже третье издание "Магии таблиц". Это 500 с лишним страниц, только свежак и все актуальное, файлы со всеми примерами, от интерфейса до введения в PQ и PP, все новые функции, включая GROUPBY и PIVOTBY, твердый переплет.

Сейчас бумажная версия в издательстве стоит всего 850 рублей. Сравните с книгами про Excel по 30 долларов (+доставка) с ужасным качеством бумаги и скриншотов и мягкой обложкой (но великолепным содержанием, тут без вопросов) и хватайте подарок для всех, кто живет и работает в таблицах!

Вот где можно купить: Озон, ВБ, Литрес (электро), МИФ (бумага и электро)

Отзыв Николая Павлова, автора проекта "Планета Excel" и замечательных книг:

Уникальность этой книги в том, что впервые под одной обложкой собрана коллекция наиболее эффективных и полезных приемов и функций сразу из Microsoft Excel и Google Sheets – самых мощных, на сегодняшний день, инструментов для работы с электронными таблицами. Это сравнение двух программ, показ их различий и общих возможностей позволит пользователям гибко переключаться на нужный инструмент при необходимости. А это дорогого стоит.

Функция LAMBDA в Excel и Google Таблицах: подробное руководство + видео + файлы с примерами

Содержание статьи:
— Где LAMBDA доступна
— Видео: Основы, LAMBDA и MAP, LAMBDA в Google Таблицах, сравнение формул массива и LAMBDA + MAP
— LAMBDA: первый подход. Создаем свою функцию
— LAMBDA и необязательный аргумент пользовательской функции с ISOMITTED / ПРОПУЩЕНО
— LAMBDA и MAP
— MAP внутри нашей пользовательской функции
— LAMBDA внутри функции GROUPBY. Краткая форма записи eta lambda
— Строим "сводную таблицу" на формулах в Google Таблицах
— Обработка столбцов и строк — функции BYROW и BYCOL
— Функции SCAN и REDUCE: работаем с промежуточными итогами и каждым значением
— Переменная как ссылка на ячейку
— Функции для формирования массивов — EXPAND и MAKEARRAY
— Рекурсия в LAMBDA. Лимиты вызова
— Заключение: не формулами едиными
— Ссылки на дополнительные примеры
— Ссылки на файлы с примерами

https://shagabutdinov.ru/tpost/lambda

Спасибо Михаилу Музыкину за помощь и обсуждение предварительного варианта статьи!

Читайте, изучайте, задавайте вопросы в комментариях и делитесь статьей с коллегами и товарищами, работающими в Excel 2024 / 365 / Google Spreadsheets.

Горячие клавиши по понедельникам 🔥

Сегодня про умные таблицы!

Создать их можно разными способами, включая два горячих:
— Ctrl + T
— Ctrl + L (раньше они назывались списками)
— Вставка — Таблица
— Главная — Форматировать как таблицу

Вставить строку итогов можно так:
— Ctrl + Shift + T

А вставить новую строку в таблицу можно как с ленты, так и из контекстного меню (правая кнопка мыши), но самые быстрые способы:
— просто ввести данные в одну из ячеек под таблицей (но это сработает только без строки итогов, а если таковая есть, то просто дописать данные не получится)
— нажать Tab в правой нижней ячейке тела таблицы (последней ячейке с данными, а не в строке итогов)

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

И Excel такое дело помечает ошибкой с зеленым треугольником — "Число сохранено как текст".

Эти ошибки можно отключить. Отправляемся сюда:
Параметры Excel — Формулы — Правила проверки ошибок — отключаем "Числа, отформатированные как текст или с предшествующим апострофом".

Excel Options — Formulas — Error Checking Rules — Numbers Formatted As Text of Preceded By An Apostrophe