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

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

=СУММ($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

Курс "Магия табличных формул" на Sponsr. Статус и планы

Друзья, на Sponsr уже 43 видеоурока (по 10-25 минут) с исходными и готовыми файлами.

Последняя тема — промежуточные итоги, про их создание автоматически, функции SUBTOTAL и AGGREGATE и разную магию с ними.

Что дальше?
С этой недели начинается большая тема, формулы массива.
Мы уже по ходу дела их использовали, конечно. Здесь будет основательно — начнем с массивов в Excel вообще, старых формул массива (Ctrl + Shift + Enter, вжух!)
И далее про динамические массивы, их поведение со старыми и новыми функциями, и новые функции для манипуляций с массивами.
И потом про LET и LAMBDA во всей красе — отдельный модуль со всеми функциями и жизненными примерами.
А пока про LAMBDA читайте новую статью, бесплатно, без регистрации и СМС:
https://shagabutdinov.ru/tpost/lambda

А еще дальше?
— Разные темы и функции: преобразование данных, округление, статистические функции, случайные числа, даты и тексты, остаток от деления и модуль, информация и проверка, финансовые функции и ИСТОРИЯАКЦИЙ, ИЗОБРАЖЕНИЕ, ПЕРЕВОД и ОПРЕДЕЛИТЬ.ЯЗЫК, пустые и очень пустые ячейки, прогнозирование...
— Типы данных (акции/география и свои)
— Диаграммы + формулы
— Сводные таблицы и формулы. Вычисляемые поля.
— Продвинутые текстовые формулы
— Пользовательские функции
— А там будет видно! Python в Excel, DAX — всегда будет о чем поговорить :) Пожелания и обращения принимаются с 10 до 17 мск на почту renat@shagabutdinov.ru!

https://sponsr.ru/excel_magic

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

Сегодня фильтр!

Установить и снять его можно так:
Ctrl + Shift + L
(Cmd ⌘ + Shift + F)

Если у вас есть фильтр и какие-то строки скрыты (то есть применен фильтр в одном или нескольких столбцах), это сочетание отобразит все строки и уберет сам фильтр.

Следующее сочетание — это альтернатива клику по кнопке фильтра в заголовке столбца — откроется меню с опциями по фильтрации и сортировке. Должна быть активна ячейка заголовка.
Alt + ↓
(Opt ⌥ + ↓)

Наконец, повторное применение фильтра (подробнее про него можно прочитать здесь)
Ctrl + Alt + L
(Cmd ⌘ + Shift + R)

Новогодний розыгрыш: книга "Магия таблиц" с автографом + три деловые новинки МИФ в электронном формате!

Дорогие читатели, к Новому году приготовили для вас книжные подарки от каналов Магия Excel и CEO Readz!

Это книга "Магия таблиц" (третье издание в твердом переплете) с автографом автора, а также следующие деловые книги издательства МИФ:
1 Бизнес как система отношений. Как расти в карьере, бизнесе и жизни, инвестируя в людей и себя (Алексей Горячев, Алексей Гуреев)
2 Дофаномика. Инструкция по управлению вниманием, эмоциями и желаниями (Егор Апполонов)
3 Венчурное мышление. 9 принципов роста бизнеса в любых условиях (Илья Стребулаев, Алекс Данг)

Что нужно? Нажать кнопку под постом и подписаться на следующие каналы:
Магия Excel
CEO Readz
Канал Рената Шагабутдинова

Розыгрыш закончится 30 декабря в 12:00. Первое имя в списке — бумажная книга "Магия таблиц" с автографом (доставка по России до пунктов выдачи за наш счет). 2-4 места — по электронной книге издательства МИФ.
Всем удачи и с наступающим Новым годом!

Участников: 355
Призовых мест: 4
Дата розыгрыша: 12:00, 30.12.2025 MSK (завершён)

Победители розыгрыша:
1. Милена Скоарца | РТ МИС - 4jeilf
2. Татьяна - 4lnl95
3. Cloudy - 4lncu1
4. Cirius - 4lnqrp

Как спрятать формулы?

Итак, у вас секретнейший алгоритм расчета чего-то там.

Выделяем ячейки, заходим в окно формата (Ctrl + 1) и там включаем соответствующий флажок — "Скрыть формулы".

После чего нужно защитить лист (с паролем, если вы настроены очень серьезно, хотя пароль и не обязателен).

И формулы не будет видно. Но они будут вычисляться, разумеется.

И если мы отключим защиту ячеек, влияющих на формулу (как в видео столбец F "Сумма"), то можно будет менять их значения даже при защите листа и формулы будут пересчитываться. Но мы их все равно не увидим 😸

Как отключить защиту отдельных ячеек? По умолчанию все ячейки защищаемые. Можно снять защиту для отдельных ячеек — там же, где и скрытие формул, в окне формата на вкладке "Защита".

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

Сегодня клавиши для быстрой вставки диаграмм.

Если вам нужна внедренная (привычная — плавающая "поверх ячеек", на графическом слое) диаграмма:
Alt + F1

Если нужна диаграмма на отдельном листе — просто F11.

У вас активна сводная таблица? Тогда будет вставляться сводная диаграмма — тоже либо внедренная, либо на отдельном листе.

Существующую диаграмму можно на ленте на ее вкладке "Конструктор" всегда переместить с отдельного листа / на лист.

Хотите изменить тип и вид диаграммы, создаваемой по умолчанию? Вам по этой ссылке!

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

Сегодня про окно "Найти и заменить"

Как его открывать, почти все помнят — Ctrl + F или Ctrl + H.
В Excel оба сочетания откроют диалоговое окно, просто разные вкладки, в любом случае потом можно перейти с поиска на замену и наоборот (кстати, Ctrl + PgUp / PgDn подходит не только для перехода с листа на лист, но и с вкладки на вкладку в диалоговых окнах).
В Google Таблицах Ctrl + F откроет простое поле для поиска справа сверху, а если вам нужно окно со всеми параметрами, сразу нажимайте Ctrl + H, даже если вам нужен поиск, а не замена.

А еще есть сочетания для поиска совпадений в Excel уже после закрытия окна "Найти и заменить".

Вот искали вы слово "Лемур". Или заменяли его на что-то. Закрыли окно поиска / замены. Нажали Shift + F4 — и перешли к следующему совпадению. Ctrl + Shift + F4 — это обратно.

Как удалить пробелы из выгрузки?

Числа с пробелами (и поэтому это де-факто текст, который не обработать нормально), но "Найти и заменить" (Ctrl + H) не помогает. Проблема в том, что пробелы бывают разные 🤯 Короткий ответ: пробел лучше копировать, то есть брать именно тот пробел, что есть в выгрузке, который нужно удалить из чисел. А не вводить с клавиатуры.

В видео разбираемся более детально: как с помощью функции КОДСИМВ / CODE понять, что за символ вообще перед нами — в данном случае это не обычный пробел, а неразрывный, поэтому его не удалишь вводом обычного пробела с клавиатуры в окне "Найти и заменить". Его нужно либо копировать из данных и вставлять в окно "Найти и заменить", либо вводить с помощью кода — Alt+0160.

И делаем макрос, который позволит удалять ненужные символы одним нажатием кнопки или сочетанием клавиш Alt + цифра.

Код макроса можно сократить до такого:
Selection.Replace What:=" ", Replacement:=""

На месте пробела может быть другой символ, который вам нужно удалять в выделенном диапазоне.
Не забывайте: действие макросов отменить через Ctrl+Z нельзя!

Всю задачу разбираем в видео со звуком.

Дано: в ячейке есть символы, нам надо получить текстовую строку с ними же, но отсортированными.

Решение: создаем виртуальный массив из всех символов — для этого создаем последовательность (функция ПОСЛЕД / SEQUENCE) чисел от единицы до числа символов в ячейке (ДЛСТР/ LEN), извлекаем символы с помощью ПСТР / MID — сформированная последовательность выступает номерами символов, которые мы этой функцией извлекаем. То есть мы извлекаем 1, 2, ..., N символов из ячейки, где N — число символов в ней, определенное функцией ДЛСТР.

Далее сортируем полученный массив (СОРТ / SORT) и сразу же собираем его обратно в текстовую строку (СЦЕП / CONCAT).

Получится формула:
=СЦЕП(СОРТ(ПСТР(ячейка;ПОСЛЕД(ДЛСТР(ячейка));1)))

Выбираем клиента флажком и получаем "сводную" по нему — магия новых функций

Увидел похожий пример у зарубежных коллег и решил сделать похожее и поделиться с вами в формате видео.
Включаем флажок, получаем "сводную таблицу" — все на новых функциях. В деле ГРУПППО / GROUPBY, РАЗВЕРНУТЬ / EXPAND, ВСТОЛБИК / VSTACK, ну и старые добрые функции тоже делают свое дело.

Смотрим подробный разбор на Youtube:
https://www.youtube.com/watch?v=CErKi8r44oo

Или на сайте (доступно в России, это видео сверху, а ниже много других):
https://shagabutdinov.ru/video

Кнопки группировки можно скрывать!

У вас на листе группировка. Она нужна, но кнопки + / - занимают слишком много места.

Нажмите Ctrl + 8, и они исчезнут. Текущая группировка никуда не денется, она сохранится. Менять ее не получится, но достаточно нажать Ctrl + 8, чтобы вернуть кнопки и менять группировку опять.

А для быстрой группировки используйте горячие клавиши:
Alt + Shift + → (группировать)
Alt + Shift + ← (разгруппировать)

Ссылки на несколько листов в формулах Excel: три варианта
Продолжительность: 8 минут

Видео для новичков!
Как ссылаться на несколько листов в формулах Excel и как сформировать ссылку на другой лист, используя его имя из ячейки.

Можно посмотреть на Ютубе:
https://www.youtube.com/watch?v=FLEP871PMV8

Или на сайте (доступно в России):
https://shagabutdinov.ru/video

Истина (True) где-то рядом

Сегодня не совсем серьезный прием. Поделитесь, если вам пригодится в работе и придумаете, где это использовать.

Представим, что вам нужно проверять, какой язык у пользователя — русский или английский (любой другой).
Например, чтобы учесть региональные настройки (хотя они и язык в интерфейсе Excel независимы, это нужно иметь в виду), или чтобы поменять выводимые формулами тексты/обозначения, если в вашей компании говорят на нескольких языках. Ну или просто поздороваться с пользователем 😺

Возьмем функцию ЛЕВСИМВ / LEFT — она извлекает первый символ своего аргумента (или несколько, если явно задать количество во втором аргументе). И в качестве аргумента введем ИСТИНА. В Excel с англоязычным интерфейсом это будет TRUE.
Так что можно проверять — ЕСЛИ первая буква И (T), то язык интерфейса в моменте — русский (английский). Если нет — значит, другой.

=ЕСЛИ(ЛЕВСИМВ(ИСТИНА)="И";"Добро пожаловать в таблицу!";"Hello world")

Чтобы формула обновлялась автоматически при открытии книги и любом действии в ней, можно добавить вместо ИСТИНЫ что-нибудь не менее истинное, но волатильное (то есть обновляющееся при любом изменении в книге). Допустим, функцию СЕГОДНЯ. Можно просто сравнивать сегодняшнюю дату с ней же самой, это всегда будет возвращать ИСТИНА.

=ЕСЛИ(ЛЕВСИМВ(СЕГОДНЯ()=СЕГОДНЯ())="И";"Добро пожаловать в таблицу!";"Hello world")

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

Сегодня про интерфейс Power Query.

Чтобы увеличить масштаб в редакторе PQ, нажимаем Ctrl + Shift + плюс/минус. Это работает и в расширенном редакторе (где код всего запроса на языке M, Главная — Расширенный редактор)

Чтобы добавить комментарий — Ctrl + /. Автоматом строка (или несколько строк!) станет комментарием. Работает и в строке формул, и в редакторе. Обратите внимание, что даже если курсор у вас не в начале строки, она вся будет комментарием. Если хочется добавить комментарий после кода, то это сочетание клавиш не подойдет, тогда просто добавляем два слэша // в конце.

Ну а если нажать Alt, то далее вы получите доступ ко всем командам с панели быстрого доступа и ленты. Все как в Excel. Соответственно, если вам нужен короткий доступ к любой команде через Alt + одна цифра, добавьте эту команду в редакторе PQ на панель быстрого доступа (правая кнопка — Add to to Quick Access Toolbar).

Часто создаете "умные" таблицы?

Хорошая практика — их переименовывать (чтобы в формулах ссылаться не на "Таблица1", "Таблица2", а на "Прайс" или "Остатки")

Если приходится переименовывать их часто, поле "Имя таблицы" можно добавить на панель быстрого доступа! И оно всегда будет наверху во всех книгах Excel при любой активной вкладке ленты инструментов.

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

Чтобы добавить инструмент на панель, просто щелкните по нему правой кнопкой мыши и выберите соответствующую команду в контекстном меню.

Давно мы про книги не говорили!

Python in Excel Step-by-Step

Вот такая новинка вышла в январе и я не стал ждать бумажную, купил Kindle.
Хорошая книга, но за такой объем дороговато — 44 доллара.

Тут очень понятно и исчерпывающе про то, как Python работает в Excel, и основы языка.
Отдельным постом ниже выложу содержание книги из киндла.

Что еще жду в этом году из новинок?

Excel Power Tools: Unlocking the Power of AI, Python, and Power Automate - июль 2026

MrExcel 2026: The A to Z of Excel - больше по привычке, ибо Джелен, всегда будет что-то интересное, но качество печати его последней книги в этой серии дико разочаровало, писал об этом. Тоже июль

Automate Excel with Python: A Practical Guide for Nonprogrammers - май 2026

The Definitive Guide to DAX: Mastering the semantic model expression language for Microsoft Power BI, Fabric, and Excel - вышла в конце 2025. Третье издание мощной книги по DAX

Python in Excel: Unlocking powerful data analysis and automation solutions — вышла в январе, только в бумаге, придется ждать

И книгу Николая Павлова по Power BI жду еще, конечно :)