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

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

Нажмите 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 жду еще, конечно :)

Генератор QR-кодов в Excel 365

Если у вас есть подписка Microsoft 365 и хочется создавать QR-коды в книге Excel и/или пощупать руками Python, можно воспользоваться шаблоном "QR Code Generator".

В очень коротком видео смотрим:
1 Шаблон выглядит как магия — меняем параметры в ячейках, QR меняется. А где есть магия, хочется сразу искать макросы, но в данном случае их нет.
2 Но если выделить картинку с куаром — мы видим ссылку на ячейку A2. Можно нажать F5 и ввести адрес ячейки, чтобы активировать ее, потому что руками ее не нащупать — хитрецы-создатели спрятали заголовки строк и скрыли вторую строку. Также можно и в поле "Имя" слева от строки формул ввести адрес ячейки и нажать Enter.
3 Можно и вернуть строку, конечно — идем на вкладку "Вид" ленты, возвращаем "Заголовки", находим 1-3 строки, показываем скрытую вторую.
4 Там-то и находим код PY, генерирующий изображение!

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

Обратите внимание: В отличие от других языков, которые живут в Excel (VBA, M, DAX), код Python не отрабатывает локально, а требует интернет-соединения! Это помимо того, что требует наличия Microsoft 365 😸

Нужно выделить все формулы?

Нажимаем Ctrl+G или F5

В открывшемся окне "Переход" нажимаем "Выделить" (Special)

Далее — "Формулы" (Formulas)

Готово. Можно теперь покрасить ячейки с формулами, если хочется.

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

Лента в Excel. Ее можно свернуть, оставив только вкладки.
Это сочетание Ctrl + F1.
Но есть другой вариант: двойной клик по активной вкладке сворачивает/разворачивает. Вжух и все! У вас больше места на экране для лютых многоэтажных формул и красивых таблиц.
Еще вариант, но тянуться далеко — правый нижний угол ленты, там есть стрелка для этого.

Ну а нажатие Alt позволяет с клавиатуры вызывать команды с панели быстрого доступа и ленты.
Либо нажимайте отображаемые после нажатия Alt символы, либо используйте стрелки на клавиатуре (а еще можно Tab и Shift + Tab) для перемещения по ленте и выделения вкладок / команд.
Esc — чтобы отключить подсказки.

Интересно: в Excel для Windows это поведение (Alt и отображение подсказок для вызова с клавиатуры) отключить нельзя, на Mac его никогда не было, но вот добавили в новом Excel, а в отечественном Р7-Офисе тоже есть и его можно отключать :)

Двухуровневые заголовки в таблицах Excel / Google Spreadsheets

Не ради задачи как таковой, а для рассказа про всякие функции Excel и Google Таблиц — небольшая статья про то, как заполнить пустоты в строке заголовков (например, из-за объединенных ячеек, которые, напомню, все пустые, кроме первой) и склеить несколько строк заголовков в одну.

Какие функции в игре:
— CHOOSEROWS, VSTACK, LAMBDA и SCAN, DROP
— в Google Таблицах — плюс QUERY
— и еще в статье вариант решения в Power Query.

Гуглотаблица и книга Excel с примерами в наличии. Все по ссылке:
https://shagabutdinov.ru/tpost/mergeheaders

Подарим 5 книг — 3 книги "Магия таблиц" с автографом и 2 книги "Бег — моя терапия". Четные номера в списке победителей узнают о том, как бег положительно влияет на наше ментальное здоровье, а нечетные — смогут создать очень удобный автоматизированный дневник тренировок в Google Таблицах или Excel ;)

Условия участия
1. Быть подписанными на каналы:
Бегать просто
Магия Excel
2. Нажать кнопку «Участвовать» (под этим постом)

Итоги опубликуем 6 февраля в 12:00 (по Мск)
Победителей определит бот. Книги отправим по России до ближайшего к вам пункта Яндекса за наш счет.

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

Победители розыгрыша:
1. Олeg - 4ttoj5
2. Olga - 4sjmra
3. Aleksei - 4shkbe
4. Иван Мухин - 4tr6ju
5. S. Fender - 4uaqd8

Эмодзи в названиях листов

... Собственно, на этом предложении можно все и заканчивать :) Простой вариант, помимо цветов, как выделять ваши листы по важности / назначению.

В Google Таблицах будет даже в цвете.
Потом в формулах ссылки на эти листы будут забавными.

Другой вариант, более традиционный — цвет ярлыков. Например, все отчеты одним цветом, все листы, где вводим данные — другим, импорт (в Google Таблицах с помощью функции IMPORTRANGE) — третьим.

Где добыть эмодзи?
1 В Excel, а точнее в Windows — WIn + . (точка)
2 В Google Таблицах меню "Вставка" — "Эмодзи"
3 С поиском нужных смайлов в браузере вы бы и без нас разобрались :)

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

Ctrl + минус
Это сочетание для удаления ячеек. Если у вас выделены столбцы / строки, то они будут удаляться сразу. Если ячейки — то Excel спросит, что удалять (ячейки, строки, столбцы).

Для скрытия строк / столбцов используем Ctrl + 9 и Ctrl + 0. Выделять их не нужно, так как тут отдельные сочетания. Будут скрываться строки или столбцы активного диапазона (и даже для несмежных диапазонов, выделенных через Ctrl, будет работать!). То есть если выделить один любой столбец и нажать Ctrl + 9, то вы скроете все строки на листе вообще :)

С Shift'ом — показываем скрытое.
Со столбцами может не работать. Тогда вам сюда :)

Вы работаете в Google Таблицах? Не грустите. Нажимайте Ctrl + / и включайте флажок:
Включить совместимые быстрые клавиши для таблиц
Enable compatible spreadsheet shortcuts

После чего вам будет доступна и эта радость со скрытием-удалением, и еще горячие клавиши для группировки/разгруппировки (Ctrl + Alt + стрелки вправо-влево), и на десерт Ctrl+1 для форматирования.

У сводных теперь (в новом Excel, 365) есть ошибка #ПЕРЕНОС! (#SPILL!)

Как у формул с динамическими массивами.
Потому что теперь сводные могут обновляться автоматически, то есть приблизились по поведению к формулам в этом смысле.
И вот ваша сводная обновляется, а в исходнике добавились новые категории, которые уходят в строки / столбцы.

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

И также она будет, когда сводная просто вылезает за пределы листа (но кот Лемур очень надеется, что вы не строите сводных где-то там в правом углу и у вас нет сводных с миллионом строк и тысячами столбцов 🙀)

Дата на этой неделе?

Хотим вывести список или пометить в списке тех, у кого ДР на этой неделе.
В общем виде — что даты относятся к этой неделе.

Отдельные формулы для любых версий Excel
Получаем день рождения с текущим годом

ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ([@[Дата рождения]]);ДЕНЬ([@[Дата рождения]]))

То есть берем текущий год, а месяц и дату — из столбца с ДР. человек родился 05.02.1998, а мы получаем 05.02.2026
После чего вычисляем номер недели

=НОМНЕДЕЛИ.ISO (полученная дата)

Сравниваем с номером текущей недели НОМНЕДЕЛИ.ISO(СЕГОДНЯ()). Все вместе:

=НОМНЕДЕЛИ.ISO(ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ([@[Дата рождения]]);ДЕНЬ([@[Дата рождения]])))=НОМНЕДЕЛИ.ISO(СЕГОДНЯ())

Альтернатива
Можно вычислять понедельник этой недели:

=СЕГОДНЯ()-ДЕНЬНЕД(СЕГОДНЯ();2)+1

И воскресенье:

=СЕГОДНЯ()-ДЕНЬНЕД(СЕГОДНЯ();2)+7

А потом проверять, что ваша дата в этом интервале:

=И(дата>=понедельник; дата<=воскресенье)

Список одной формулой в новом Excel
На новых формулах будем сразу выводить список всех, у кого ДР на этой неделе, с помощью ФИЛЬТРа.

=LET(ДР;Сотрудники[Дата рождения];
ФИЛЬТР(Сотрудники;НОМНЕДЕЛИ.ISO(ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(ДР);ДЕНЬ(ДР)))=НОМНЕДЕЛИ.ISO(СЕГОДНЯ())))

Раз формулы новые, то попользуем и функцию LET, чтобы два раза не ссылаться на столбец с датами, а вместо этого заменить его переменной "ДР".

В Power Query
В M есть функция Date.IsInCurrentWeek — даем ей дату, она возвращает true / false в зависимости от того, относится ли дата к текущей неделе.
Вот такой вариант добавления столбца с проверкой, если сами проверяемые даты не нужно править, и они тоже относятся к текущему году:

= Table.AddColumn(ВашаТаблица, "На этой неделе", each Date.IsInCurrentWeek([Дата]))

(где "Дата" — столбец с датами, а ВашаТаблица — предыдущий шаг с таблицей, где есть такой столбец).

Файл с формулами в комментариях!