Именованные диапазоны в Excel
До сих пор на тренингах регулярно встречаю продвинутых пользователей, которые весьма поверхностно знакомы с именованными диапазонами в Microsoft Excel и не используют их в своей работе. А это печально, т.к. именованные диапазоны - это мощный механизм, позволяющий заметно упростить вычисления в Excel. Давайте быстро пробежимся по основам (что такое ИД, как их создать и какие они бывают) и затем рассмотрим 10 наиболее вкусных и полезных приёмов работы с ними в реальных задачах.
Основы
Именованный диапазон (ИД) - это, по сути, переменная в Excel, которая может хранить следующие данные:
- ссылку на отдельную ячейку
- ссылку на диапазон
- константу (число, текст, дату)
- формулу
Управлять ИД можно в специальном окне Диспетчера имён, открыв его на вкладке Формулы - Диспетчер имён (Formulas - Name manager) или с помощью сочетания клавиш Ctrl+F3.
При создании ИД нужно ввести:
- имя - любой текст без пробелов, начать с буквы, не должен быть похож на адрес ячейки;
- диапазон - ссылка на ячейку, диапазон или ввести константу или формулу
- область - область видимости создаваемого имени, т.е. где его можно будет использовать (лист или книга)
После этого можно использовать созданные имена:
- в формулах и функциях
- как источник данных для диаграмм и сводных таблиц;
- загружать их в Power Query
- в макросах
Использование имён в формулах
Это самое очевидное и основное назначение именованных диапазонов - их имена можно смело использовать в любых формулах и функциях:
Далее переходим к менее очевидным сценариям.
Навигация
Если вы на постоянной основе работаете с большой книгой, содержащей множество листов, то вам наверняка придётся часто прыгать между ними - от одной ключевой ячейки к другой, от исходных данных к итогам и т.д. Для ускорения можно использовать ИД: создать их для всех ключевых ячеек у вас в книге, а затем выбирать нужный ИД из выпадающего списка в строке формул - и тут же "телепортироваться" к требуемой ячейке на соответствующий лист:
Для пущего удобства можно добавлять нумерацию в имена этим якорным диапазонам так, чтобы они сортировались в выпадающем списке желаемым образом (а не по алфавиту как обычно).
Карта имён
Классический способ узнать где у вас на листе какой ИД, состоит в открытии Диспетчера имен с вкладки Формулы (Formulas - Name manager). Однако, есть и другой (весьма наглядный) способ - если уменьшить масштаб листа до 40% или менее, то Excel отобразит имена диапазонов прямо на экране:
Именованные константы
Именованный диапазон может ссылаться не только на ячейки листа, но и хранить любое фиксированное значение, т.е. быть именованной константой. Например, можно сделать что-то типа глобальной переменной - именованный диапазон с именем НДС, равный текущей ставке в 22%. После этого мы сможем использовать эту константу в любых формулах на любом листе текущей книги:
Если в будущем НДС поменяется, то нам достаточно будет изменить ставку в значении этой именованной константы в Диспетчере имен, чтобы скорректировать все наши расчёты.
Массовое создание именованных диапазонов
В некоторых случаях именованные диапазоны можно создавать не поштучно, а сразу оптом. Например, можно выделить вот такую таблицу целиком (вместе с заголовками строк и столбцов) и выбрать на вкладке Формулы команду Создать из выделенного (Formulas - Create from selection).
В открывшемся окне мы увидим два флажка:
- флажок В строке выше (Top row) означает, что мы даём имена каждому столбцу в таблице и берём их при этом из верхней строки;
- флажок В столбце слева (Left column) означает, что мы даём имена каждой строке и берём названия для каждой из них из первого столбца.
Таким образом, если оставить оба флажка включенными, мы получим сразу несколько именованных диапазонов - по одному для каждой строки и столбца в нашей таблице. Обратите также внимание, что Excel автоматически заменяет недопустимые символы и пробелы в именах диапазонов на подчёркивание, т.е. диапазон-строка B8:M8 для Нижнего Тагила получит имя Нижний_Тагил.
Забавным последствием такого сценария может стать использование таких диапазонов для поиска значения с пересечения заданной строки и столбца. Дело в том, что в Excel пробел в строке формул на самом деле играет роль оператора пересечения множеств. Так что найти продажи определенного товара в определенном городе можно с помощью очень простой формулы:
=ДВССЫЛ(ПОДСТАВИТЬ(P3;" ";"_")) ДВССЫЛ(R3)
или в английской версии:
=INDIRECT(SUBSTITUTE(P3," ","_")) INDIRECT(R3)
Здесь функция ПОДСТАВИТЬ (SUBSTITUTE) подменяет пробелы на подчёркивания, если они есть в исходных ячейках, а затем функция ДВССЫЛ (INDIRECT) делает так, чтобы Excel воспринимал эти значения как имя диапазона, а не как обычный текст.
Диапазоны-невидимки и их ошибки
Кроме явных именованных диапазонов, которые мы видим в Диспетчере имен, в книге Excel могут содержаться ещё и невидимые. Обычно это всевозможные технические и вспомогательные диапазоны, которые создают различные программы для своего внутреннего использования при выгрузке данных в Excel. Иногда этот невидимый "мусор" может доставлять вам массу проблем - например, при копировании листов или ячеек с такими невидимыми диапазонами могут возникать ошибки или миллион диалоговых окон с вопросом о конфликте имён, если они уже есть в вашей книге.
Чтобы проверить таких невидимок и, при необходимости, от них избавиться сначала их нужно сделать видимыми. Для этого потребуется простой макрос. Нажмите сочетание клавиш Alt+F11, чтобы открыть редактор Visual Basic или нажмите кнопку Visual Basic на вкладке Разработчик (Developer - Visual Basic). Вставьте новый пустой модуль через меню Insert - Module и скопируйте туда следующий код:
Sub MakeNamesVisible()
Dim n As Name
For Each n In ActiveWorkbook.Names
n.Visible = True
Next n
End Sub
Механика его проста - мы проходим по внутренней экселевской коллекции Names, хранящей все именованные диапазоны в книге и делаем каждое имя видимым. Запустить созданный макрос можно с помощью сочетания клавиш Alt+F8 или кнопкой Макросы на вкладке Разработчик (Developer - Macros). После этого все скрытые именованные диапазоны станут видимыми и мы сможем с ними разобраться в окне Диспетчера имен.
Плавающие диапазоны
Неочевидным моментом для многих пользователей является то, что при создании имени мы можем задать ссылку на исходный диапазон не в абсолютном (со знаками $), а в относительном виде. Тогда наше имя привяжется не к какой-то конкретной ячейке, а запомнит только сдвиг на X строк и Y столбцов для него относительно текущей ячейки. На практике это можно использовать во множестве случаев.
Например, можно выделить любую ячейку на листе (например, D5) и создать именованный диапазон ЯчейкаСверху со ссылкой на D4 (без знака "$" и без имени текущего листа до символа "!"):
Полученное имя будет полностью относительным и всегда будет ссылаться на ячейку выше текущей на любом листе, где мы это имя используем. Например, можно применить его в строке итога, чтобы посчитать сумму по всем ячейкам в столбце независимо от его размера - начиная с начала и до ЯчейкиСверху:
Или создать именованный диапазон с именем Рейтинг, где взять оценку из ячейки слева и вывести её в виде соответствующего количества звёздочек (для их вставки можно использовать сочетание Win+точка, открывающее библиотеку эмодзи и спецсимволов):
Для этого в имени достаточно будет прописать простую формулу с функцией ПОВТОР (REPT), повторяющей заданный символ N-раз.
Динамический именованный диапазон
Весьма полезным частным случаем именованного диапазона с формулами является динамический именованный диапазон, способный автоматически подстраиваться под размеры исходных данных и расширяться-сжиматься при изменении количества строк-столбцов в нашей таблице. Его можно реализовать как при помощи динамических "умных" таблиц, так и создав именованный диапазон с формулой, которая будет сама определять конец таблицы. Например, если наша таблица начинается с ячейки K1 и прирастает потом новыми столбцами и строками вправо и вниз, то формула для именованного диапазона (назовём его, допустим, Расходы) будет выглядеть следующим образом:
=$K$1:ИНДЕКС($K:$XFD;СЧЁТЗ($K:$K);СЧЁТЗ($K$1:$XFD$1))
В английской версии это будет соответственно:
=$K$1:INDEX($K:$XFD,COUNTA($K:$K),COUNTA($K$1:$XFD$1))
Здесь функции СЧЁТЗ (COUNTA) подсчитывают количество ячеек в подписях по строчкам и столбцам, что даёт нам высоту и ширину таблицы, а затем функция ИНДЕКС (INDEX) ссылается на ячейку по этим координатам. Другой (чуть более продвинутый) вариант - это использовать для нахождения последней заполненной ячейки функцию ПОИСКПОЗ (MATCH) в режиме приблизительного поиска. Про него подробно можно почитать в отдельной статье.
Лямбда-функции в именах
Высшим пилотажем в использовании именованных диапазонов является, конечно, их применение для создания пользовательских лямбда-функций. LAMBDA - это новая функция Excel, появившаяся, начиная с 2019 года. Она играет роль "обёртки", давая нам возможность сконструировать собственную функцию с любой логикой прямо внутри именованного диапазона, а затем пользоваться ей как обычной встроенной функцией Microsoft Excel. Подробно об этом можно почитать вот тут, а здесь давайте ограничимся простым примером.
Предположим, что нам хотелось бы создать функцию, которая извлекала бы из любого исходного текста нужное нам N-ое по счёту слово. Для этого откроем Диспетчер имён на вкладке Формулы, создадим имя и введём в его поле Диапазон (Reference) следующую конструкцию:
=LAMBDA(текст;N;ИНДЕКС(ТЕКСТРАЗД(текст;" ");N))
В английской версии это будет:
=LAMBDA(текст,N,INDEX(TEXTSPLIT(текст," "),N))
Здесь:
- первые два аргумента (текст и N) - это переменные, которые функция будет получать от пользователя;
- функция ТЕКСТРАЗД (TEXTSPLIT) делит исходный текст по пробелу и превращает в массив отдельных слов;
- функция ИНДЕКС (INDEX) извлекает из полученного массива N-й по счету элемент
После создания этой функцией можно пользоваться на любом листе текущей книги - как будто это встроенная функция Excel и была в нём всю жизнь:
Ссылки по теме
- Динамический именованный диапазон в Excel
- LAMBDA - новая суперфункция в Excel
- Разбор функции ДВССЫЛ на примерах
Вопрос не совсем по данной теме (вопрос про применение PQ): имеется множество файлов в папке, но не все файлы содержат нужный лист. Как сделать так, чтобы обрабатывались только содержащие нужный лист файлы (иначе возникает ошибка и обработка останавливается)?