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

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

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

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

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

Окно «Найти и заменить» (Find and Replace) во многих случаях помогает решить задачи по обработке текстовых значений (и не только) без применения сложных функций и формул. Это окно позволяет исправить большое количество формул, поменять форматирование всех однотипных ячеек, удалить определенные слова или символы из диапазона или из всей книги Excel.

Его можно вызвать сочетаниями клавиш Ctrl + F (⌘ + F) или Ctrl + H (⌃ + H) — в обоих случаях откроется одно и то же диалоговое окно, но в первом случае на вкладке «Найти» (Find), а во втором — «Заменить» (Replace).

Вот несколько нюансов:
— Если вы предварительно выделили диапазон ячеек, то поиск/замена будут производиться в пределах этого диапазона. Если же нет — то на листе или в книге (изменить этот параметр можно в поле «Искать» (Within) в окне «Найти и заменить»; по умолчанию будет лист).

— Если вы хотите что-то удалять, а не заменять, просто оставьте поле «Заменить на» пустым. Заменить на ничто = удалить, не так ли?

— Можно производить изменения сразу с большим количеством формул. Например, вам нужно поменять диапазон или функцию во многих формулах. Выделите диапазон с формулами, вызовите окно «Найти и заменить» и введите в поле «Найти» тот фрагмент формул, который вы хотите изменить, а в «Заменить на» — то, на что хотите его изменить. Убедитесь, что в списке «Область поиска» (Look in) заданы «Формулы» (Formulas).

Ctrl + левая кнопка мыши: быстрое копирование листов или объектов

Нужно создать копию листа? Зажимаем Ctrl и тянем ярлык существующего листа мышкой. Получаем копию.

Это чудо работает не только с листами, но и с фигурами, например (см видео). Или с диаграммами.

И не только в Excel, но и в других приложениях. Например, в Power Point или Google Презентациях 🔥

Быстрая фильтрация в сводной таблице

Если вам нужно быстро исключить некоторые значения из сводной: выделите то, что нужно убрать (в строках или столбцах отчета сводной таблицы) и нажмите Ctrl + - (минус).

Данные будут отфильтрованы, те значения, что вы выделяли, будут исключены в фильтре.

Функция SCAN: нарастающий итог — простой, по каждому году/месяцу или с условием

SCAN — одна из вспомогательных функций LAMBDA, которая позволяет пробегаться по массиву, обращаясь к каждому элементу и накопленному итогу. И творить всякую магию. Доступно это удовольствие в Google Таблицах и в Excel 365 / Excel Online.

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

Файлы с примерами из статьи:
Рабочая книга Excel
Google Таблица

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

Если дата в ячейках записана как текстовое значение вида ДДММГГГГ, без точек/дефисов/других разделителей, можно превратить такой текст в настоящую дату формулой:

=ДАТА( ПРАВСИМВ( ячейка с датой; 4) ; ПСТР (ячейка; 3; 2) ; ЛЕВСИМВ (ячейка; 2) )

Функция ДАТА / DATE возвращает дату, заданную тремя параметрами — годом, месяцем и днем.
Ее аргументы мы получаем текстовыми функциями:
Год — извлекая первые цифры цифры с помощью ПРАВСИМВ / RIGHT (она возвращает первые N символов из текстовой строки)
Месяц — извлекая два символа, начиная с третьего, с помощью ПСТР / MID.
День — последние две цифры с помощью функции ЛЕВСИМВ / RIGHT.

Вот так новости!
Наконец-то в Excel появились функции для работы с регулярными выражениями. Но, увы, как водится с новинками — только у подписчиков 365.

Напоминаем, что в Google Таблицах функции для работы с регулярками есть и доступны всем — там они называются REGEXEXTRACT (извлекаем), REGEXMATCH (проверяем соответствие), REGEXREPLACE (заменяем).
В Excel до появления этих функций можно было работать с регулярками через макросы — вот статья маэстро Николая Павлова на эту тему.

Официальная новость про функции

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

Приводим mm-dd к dd-mm не формулой

Меняем формат даты с ММ/ДД/ГГГГ на ДД.ММ.ГГГГ формулой

Таблица с примерами регулярок от участников сообщества

Извлекаем числа, едим пончики

Извлекаем актуальное число подписчиков телеграм-каналов из ссылки вида t.iss.one/канал

Получаем последнюю дату из таблицы

Если нам просто нужно последнее значение из столбца (по порядку, нижнее) - можно использовать функцию ПРОСМОТР / LOOKUP. Введем у нее в первом аргументе число, которое априори больше любой даты (можно просто 100 000). И тогда ПРОСМОТР вернет последнее значение.
(подробнее про ПРОСМОТР читайте здесь)

Если нужна самая поздняя дата, то можно вспомнить, что любая дата в Excel — это число, и просто взять максимальное число из столбца с помощью функции МАКС / MAX. Это будет последняя дата, в какой бы строке она ни находилась.

А если нужна не просто самая поздняя, а поздняя у определенного администратора? С условием, иначе говоря.
Если у вас Excel 2019, 2021, Google Таблицы — можно воспользоваться функцией МАКСЕСЛИ / MAXIFS (подробнее о ней тут; она работает как СУММЕСЛИМН / SUMIFS, только не суммирует, а ищет максимум).
А если другие версии Excel?
Сделаем МАКСЕСЛИ сами. Из... МАКС и ЕСЛИ :)

Функцией ЕСЛИ будем проверять столбец с именами на соответствие нужному (B2:B132="Лемур") и возвращать при выполнении условия даты из столбца A. При невыполнении условия функция ЕСЛИ вернет просто логическое значение ЛОЖЬ, так как мы ничего явно в третьем аргументе не указали. На выходе получим массив, где будут даты, когда работал Лемур, и значения ЛОЖЬ, когда работали другие.
Функция МАКС в этом массиве найдет самую большую дату.
Чтобы формула сработала, ее нужно ввести как формулу массива — заклинанием Ctrl+Shift+Enter.

Видеоурок: модель данных Power Pivot. Создание отношений между таблицами.

Друзья, делюсь с вами одним из уроков нового модуля курса "Магия Excel", посвященного модели данных Excel (Power Pivot).
https://www.youtube.com/watch?v=IR-rjAsC968

А весь курс можно найти тут — в нем 14 модулей и сотни минут таких видеоуроков, домашки и файлы со всеми примерами — в исходном и готовом виде:
https://www.mann-ivanov-ferber.ru/courses/magicexcel/

В строке формул можно переходить на следующую строку с помощью Alt+Enter. Это позволяет визуально разделить отдельные фрагменты/функции — тогда формулу будет проще воспринимать (вашим коллегам и вам самим в будущем, когда вы уже забудете ее логику).

Это может помочь, если у вас уже многоэтажная формула, а в ней возникает синтаксическая ошибка. Обратите внимание, что высоту строки формул можно менять — достаточно потянуть за нижнюю границу, удерживая нажатой левую кнопку мыши.

Также можно пробелами ставить отступы в формуле, если это поможет вам с восприятием формулы

На скриншоте формула с переносами строк, но без отступов. Каждая функция начинается с новой строки. На работу формулы это не влияет.

Закрепление верхней строки в Excel в один клик: добавляем команды на панель быстрого доступа

Панель быстрого доступа (Quick Access Toolbar, QAT) — простейший инструмент для настройки интерфейса "под себя". Туда можно добавить любую команду — как с ленты, так и из списка вообще всех команд и инструментов Excel.

Даже если какое-то действие нельзя добавить напрямую из ленты (потому что оно там находится в выпадающем списке, в коллекции — как, например, закрепление верхней строки находится в коллекции "Закрепление областей"; или потому что действия вообще нет на ленте) — его все равно можно добавить через параметры Excel, чтобы во всех книгах у вас всегда был доступ к нужной команде в один клик. В мини-статье разбираем, как это сделать — как раз на примере закрепления верхней строки.

В связи с недавним выходом новинки, посвященной языку M (он используется в Power Query в Excel и Power BI) время обновить обзор основных книг по каждой эксельной теме 😸

Итак, если вы хотите изучить конкретную тему в рамках Excel, вот по одной книге на каждую.

Excel в целом
Microsoft Excel Inside Out (Office 2021 and Microsoft 365)
На русском:
Excel 2019. Библия пользователя — Куслейка, Александер

Макросы
Microsoft Excel VBA and Macros — Bill Jelen
На русском: Excel 2016. Профессиональное программирование на VBA — Александер, Куслейка (не пугайтесь версии 2016 — макросы не меняются десятилетиями)

Сводные таблицы
Сводные таблицы в Microsoft Excel 2021 и Microsoft 365 — Джелен

Power Query
Скульптор данных в Excel с Power Query — Николай Павлов
или / и
Приручи данные с помощью Power Query в Excel и Power Bi — Пульс, Эскобар

Язык M
The Definitive Guide to Power Query (M): Mastering complex data transformation with Power Query
(книга для глубокого погружения именно в язык M, то есть ее лучше читать с опытом работы в интерфейсе Power Query и при желании решать там нестандартные задачи и писать код самостоятельно)

Power Pivot и язык формул DAX (который используется и в Power BI / других решениях Microsoft)
Анализ данных при помощи Microsoft Power BI и Power Pivot для Excel — Руссо, Феррари

Очень глубоко и основательно про DAX: Подробное руководство по DAX: бизнес-аналитика с Microsoft Power BI, SQL Server Analysis Services и Excel — Руссо, Феррари

Для первого ознакомления с Power Pivot можно начать с глав в книге Джелена про сводные

Формулы в целом
С новыми формулами (LAMBDA, новые массивы), от начального до продвинутого уровня: главы про формулы в Microsoft Excel Inside Out.
С новыми формулами посложнее: Advanced Excel Formulas: Unleashing Brilliance with Excel Formulas
На русском с новыми формулами: главы про формулы у меня в "Магии таблиц"
На русском до 2019 включительно от начального до продвинутого: главы про формулы в Excel 2019. Библия пользователя
На русском до 2019 включительно посложнее: Мастер формул — Николай Павлов

Старые формулы массива (до 2019 включительно)
Ctrl+Shift+Enter Mastering Excel Array Formulas: Do the Impossible with Excel Formulas Thanks to Array Formula Magic — Girvin
На русском: Мастер формул — Николай Павлов

Новые формулы массива (динамические массивы)
Up Up and Array!: Dynamic Array Formulas for Excel 365 and Beyond
На русском: немного есть у меня в "Магии таблиц"

Визуализация
Визуализация данных при помощи дашбордов и отчетов в Excel — Куслейка

Новая функция REGEXEXTRACT в Excel: извлекаем электронную почту, даты и другие фрагменты из текста

Весной 2024 года в Excel были анонсированы функции для работы с регулярными выражениями. Ранее они были доступны в Google Spreadsheets. Разбираем несколько примеров. Извлекаем:
- вес из длинного названия товара
- все адреса электропочты из ячейки с текстом
- даты в разных форматах
- текст в скобках.

https://www.youtube.com/watch?v=xylfF5lS3WY

Если у вас есть формула, возвращающая динамический массив (то есть результат может быть разных размеров; допустим, функция FILTER / ФИЛЬТР будет возвращать разное количество строк в разное время, если в исходном диапазоне будут добавляться строки или меняться текущие), результат работы такой формулы можно отправить в Power Query.

И в запрос будут попадать новые данные, которые вернет формула в будущем, то есть это не будет статичная таблица с результатом на момент загрузки данных в Power Query.

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

Выделяем любую ячейку с результатом работы формулы:
Данные — Из таблицы/диапазона
Data — From Table/Range

Или просто кликайте правой кнопкой на любую ячейку формулы и в контекстном меню выберите "Получить данные из таблицы/диапазона" (Get Data from Table/Range...)

Про динамические массивы можно подробнее узнать в следующем видео:
https://t.iss.one/lemur_excel/95

Статья для тех, кто работает в Р7-Офис. Но и пользователям Excel будет полезно пробежаться и вспомнить про принципы работы формул массива :)

В российском офисном пакете Р7-Офис в таблицах есть некоторые функции, появившиеся в Excel только в 2021 версии вместе с динамическими массивами. При этом формулы массива в Р7 работают как “старые” формулы массива (возможно, это когда-нибудь изменится). Так что новыми функциями пользоваться не так удобно, как в новом Excel, но зато они в принципе есть, в отличие от Excel 2019, допустим 🙂

В статье разбираем:
— Как вводятся и работают старые и новые формулы массива в Excel
— Какие новые функции появились в Excel 2021/365 благодаря динамическим массивам Excel
— И разбираем, как работать с новыми функциями в Р7, где принципы работы формул массива старые, несмотря на наличие новых функций :)

https://shagabutdinov.ru/r7array/

Парочка горячих клавиш для тех, кто работает с Power Query

Многие из вас знают, что сочетание клавиш Alt — F11 открывает редактор Visual Basic (макросы).

А есть еще одно, очень похожее — Alt — F12 — для открытия другого редактора, Power Query.
Внутри самого Power Query можно менять масштаб с помощью сочетаний:
Ctrl — Shift — + (плюс)
Ctrl — Shift — - (минус)

Это будет влиять на масштаб всего, кроме ленты инструментов.

Ищем данные в разных таблицах с помощью ВПР / VLOOKUP и ДВССЫЛ / INDIRECT

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

У разных подразделений разная шкала оценки — например, где-то третий разряд присваивается с 60 лет, а где-то с 50.
Как быть?
Если бы задача была с одной таблицей, то все просто решается функцией ВПР / VLOOKUP: ищем возраст сотрудника в таблице, получаем разряд из второго столбца. Последний (четвертый аргумент) ВПР не трогаем, т.к. по умолчанию у этой функции интервальный просмотр, то есть поиск ближайшего наименьшего числа, а именно это нам и нужно в данном случае.

=ВПР(возраст сотрудника; таблица с возрастами и разрядами; 2)

Но у нас таблица не одна! Во втором аргументе ВПР могут быть разные таблицы, в зависимости от должности.
Поступим так:
— превратим таблицы для каждого отдела в "умные" таблицы (Форматировать как таблицу / Format as Table или Ctrl + T или Ctrl + L)
— назовем каждую по имени отдела
— теперь можно ссылаться на таблицы по имени. Нам надо получить название отдела по сотруднику (найти должность в списке "должность-отдел" и подтянуть отдел) — это и будет название нужной таблицы. Чтобы название таблицы из текста стало ссылкой, мы засунем всю конструкцию в ДВССЫЛ / INDIRECT — функцию, превращающую текст в ссылку.

В общем виде будет так:

=ВПР(возраст сотрудника; ДВССЫЛ(формула для определения названия нужной таблицы); 2)

Разбор задачи — в видео, а в соседнем посте файл (книга Excel) с формулой. Эту идею можно использовать в любой подобной задаче, когда нужно искать значение в нескольких диапазонах, а не в одном.

Интерфейс Excel: приемы и горячие клавиши для ускорения работы

Настраиваем интерфейс Excel:
— закрепляем и скрываем ленту инструментов
— вызываем команды с помощью клавиш
— "создаем" собственные сочетания клавиш для команд
— добавляем на панель быстрого доступа любые инструменты и команды — даже те, которых нет на ленте.

Лемур уверен: хоть что-то из этого видео вы раньше не знали :) Например, что можно настраивать панель быстрого доступа для отдельных файлов или добавлять туда любые команды, даже те, что с ленты инструментов не добавляются или добавляются только вместе со всей своей коллекцией.
Пишите в комментариях, что оказалось наиболее полезным!

Кстати, эти знания пригодятся вам и для настройки других приложений MS Office.

https://www.youtube.com/watch?v=hRGpkN787Yw