Окно «Найти и заменить» (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

Извлекаем из таблицы строки с самой большой и маленькой сделкой (наименьшим и наибольшим числом)

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

=СОРТ(таблица; номер столбца, по которому сортируем)

Если нужно по убыванию, то задаем третий аргумент, равный -1

Ну а далее, не выводя на лист отсортированный результат, сразу отправляем его внутрь функции CHOOSEROWS / ВЫБОРСТРОК — и берем первую (1) и последнюю (-1) строки.
=ВЫБОРСТРОК(СОРТ(таблица; номер столбца для сортировки);1;-1)

В общем виде ВЫБОРСТРОК имеет такой синтаксис:
=ВЫБОРСТРОК(диапазон / массив; номер строки, которую извлекаем ; [еще номер строки]; ...)

То есть можем извлечь и одну строку, и несколько — перечисляем столько номеров, сколько нужно.
А если нужны все нечетные строки, например, с 1 по 100, можно использовать функцию ПОСЛЕД / SEQUENCE, чтобы не вводить столько чисел вручную:
=ВЫБОРСТРОК(диапазон; ПОСЛЕД(50;;1;2))