ВЫБРАТЬ функцию

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

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

Пользователи могут создавать эти имена с помощью поля Имя ( обведено на скриншоте выше) и/или просто используйте сочетание клавиш Ctrl + F3 во всех версиях Excel, а затем нажмите Создать в диалоговом окне Диспетчер имен , как показано на снимке экрана ниже.

При нажатии на Создать (показано на скриншоте выше) вызывает появление следующего диалогового окна:

Область действия

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

Например, если вы определили имя диапазона как «Прибыль» с его областью действия как Лист1, а не Рабочая книга, то оно будет распознано как «Прибыль» только на Листе 1 (т. е. без уточнения).

Чтобы использовать это локальное имя в другом листе, вы должны указать его, поставив перед ним локализованное имя листа:

= Лист1! Прибыль

Если вы определили имя, например «Денежный поток», и его областью действия является рабочая книга, это имя распознается для всех рабочих листов в этой книге (но не для любая другая рабочая тетрадь). Если бы областью был рабочий лист (скажем, Sheet1), тогда имя диапазона было бы «Sheet1! Cashflow». Таким образом, область действия книги обеспечивает более четкие имена диапазонов и позволяет избежать путаницы.

Имя всегда должно быть уникальным в пределах своей области. Excel не позволяет вам определять имя, которое не является уникальным в пределах его области действия. Однако вы можете использовать одно и то же имя с разными областями действия. Например, вы можете определить имя, такое как «Прибыль», с областью действия Sheet1, Sheet2 и Sheet3 в той же книге. Хотя каждое имя одинаково, каждое имя уникально в пределах своей области. Вы можете сделать это, чтобы гарантировать, что формула, которая использует, например, имя «GrossProfit», всегда ссылается на одни и те же ячейки на уровне локального рабочего листа.

Вы даже можете определить одно и то же имя, например как «Прибыль» для глобального уровня книги, но опять же эта область уникальна. В этом случае может возникнуть конфликт имен. Чтобы разрешить этот конфликт, Excel использует имя, заданное для рабочего листа по умолчанию. Уровень локального рабочего листа имеет приоритет над уровнем глобальной книги. Этого можно избежать, добавив к имени префикс, например, вместо этого переименовав его в «WorkbookFile_Profit».

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

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

Забота об именах

Строка имени должна начинаться с буквой или символом подчеркивания. Остальные символы в имени могут быть буквами, цифрами, точками и символами подчеркивания. Пробелы не допускаются, но два слова можно соединить знаком подчеркивания (_) или точкой (.). Например, чтобы ввести имя «Денежный поток», вы должны ввести «Cash_Flow» или «Cash.Flow».

Вы не можете использовать имя, которое иначе можно было бы спутать с ссылкой на ячейку; например, «День 1», поскольку это уже ссылка на ячейку (многие люди пытались!).

Кажется, нет ограничений на количество имен, которые вы можете определить, но имя может содержат не более 255 символов. Имена могут содержать прописные и строчные буквы, но Excel не различает прописные и строчные буквы в именах. Например, если вы создали глобальное имя «Прибыль», а затем создаете другое глобальное имя «ПРОФИТ» в той же книге, второе имя будет отклонено, поскольку имена должны быть уникальными, независимо от использования заглавных букв.

Это не проблема синтаксиса, но я настоятельно рекомендую подумать о добавлении префиксов к именам диапазонов. Постоянные читатели заметят, что имена диапазонов моих списков всегда начинаются с «LU_», где «LU» означает «Искать вверх». Точно так же я использую «BC_» для «базовой ячейки» при работе с функцией OFFSET .

Используя эти префиксы, я понимаю назначение имени диапазона , а имена с общим назначением сгруппированы в список. Это не означает, что все имена диапазонов должны содержать префикс. Например, «Tax_Rate» имеет смысл сам по себе, и добавление префикса только отвлечет от имени, которое может быть задано, что может запутать конечного пользователя.

Быстрое создание имен диапазонов

Существует отличный ярлык для создания имен диапазонов с использованием существующих имен. Рассмотрим следующий список:

Представьте, что вы должны выделить ячейки N12: N18 в приведенном выше примере, а затем используйте сочетание клавиш Ctrl + Shift + F3 (или выберите Создать из выделенного в группе Определенные имена в Формулы на ленте):

С первым полем ( Верхний ряд ) установлен, нажав OK диапазон N 13 : N18 (не N12 : N18 ) будет называться «Phonetic_Alphabet» (т. Е. Подчеркивание будет добавлено автоматически). Диапазоны в строках могут быть названы в секундах аналогичным образом, используя Левый столбец ..

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

Выделение N31: R34 и повторное использование сочетания клавиш Ctrl + Shift + F3 должно сгенерируйте диалоговое окно Создать имена из выделенного , как показано выше, с отмеченными флажками Верхняя строка и Левый столбец . Это означает, что O32: O34 будет называться «Jan», O33: R33 будет называться «COGS» и так далее. Это займет значительно больше времени для выполнения вручную.

Этот пример также показывает, почему пробелы являются недопустимыми символами в именах диапазонов (и их также не следует добавлять в формулы). Пробел — это оператор пересечения в Excel. Если бы вы набрали следующую формулу:

= Валовая_ маржа, фев ,

Excel вернет значение в ячейке P34 (пересечение двух диапазонов выше), то есть 4 183 доллара. Это может быть мощный, но быстрый и простой аналитический инструмент для ключевых результатов — быстрее, чем VLOOKUP или INDEX MATCH .

Быстрое использование имен диапазонов

Одна из причин, по которой мне нравится использовать сочетание клавиш Ctrl + F3, заключается в том, что он является частью семейства сочетаний клавиш F3 Names. Мы только что увидели, как могут быть полезны Ctrl + Shift + F3 — и F3 может быть полезна сама по себе.

Возможно, заменено тем, что в Excel 2007 и в более поздних версиях Excel теперь будет запрашивать при вводе формул, F3 был очень полезен в прошлом как ярлык Вставить имя . Например, при вводе формулы вы можете ссылаться на имя диапазона, просто набрав F3 , чтобы открыть диалоговое окно Вставить имя , как показано в снимок экрана ниже.

Если вы выберете одно из имен и нажмете OK , будет вставлено имя диапазона .

Однако присмотритесь к диалоговому окну. Кнопка Вставить список в нижнем левом углу, если она нажата, вставит список и их определения в предварительно выбранный диапазон ячеек (т. Е. Перед использованием сочетания клавиш F3) в Excel. рабочий лист, который может быть бесценным для целей аудита модели.

Иногда формулы были написаны до того, как было создано имя диапазона. В некоторых случаях эти имена можно применить ретроспективно, используя Применить имена в группе Определить имена в Формулах . вкладка (см. снимок экрана ниже).

Обратите внимание, что сочетание клавиш Alt + I + N + A будет работать во всех версиях Excel. При выборе требуемых имен диапазонов в появившемся диалоговом окне формулы на активных листах будут обновлены соответствующим образом..

Удаление имен диапазонов

Если бы мне платили всего 1 доллар за каждый раз меня спросили, как удалить имена диапазонов, я бы, наверное, уже удалился. В основном это было связано с нелогичным меню в Excel 2003 и более ранних версиях:

Затем в появившемся диалоговом окне вы должны выбрать имя диапазона ( к сожалению, за раз можно было выбрать только один) и нажмите Удалить , как показано на скриншоте ниже.

Excel 2007 и более поздние версии значительно упрощают эту задачу. В этом случае пользователи с большей вероятностью перейдут в Диспетчер имен , а не в запутанное раскрывающееся меню Вставить :

Другое заметное улучшение состоит в том, что несколько имен можно удалить одновременно с помощью клавиш Ctrl или Shift, чтобы сделать несколько выделений перед нажатием кнопки Удалить . .

Относительные ссылки

По умолчанию на имена диапазонов ссылаются абсолютно (т.е. содержат знак $, чтобы ссылки оставались статическими ). Однако представьте сценарий, в котором вы моделируете доход и хотите увеличить значение предыдущего периода за счет инфляции (уже задано имя диапазона, например, ячейка C3 на Листе 1). Просто щелкните любую ячейку (например, я буду использовать D17 произвольно), затем определите новое имя диапазона следующим образом:

Обратите внимание на запись Относится к: . Ячейка C17 (ячейка слева от D17 ) была выбрана без знаков доллара. Это относительная ссылка. После того, как мы нажмем OK , имя диапазона «Prior_Period» будет определено как ячейка, находящаяся непосредственно слева от активной ячейки. Затем мы можем легко увеличить значения, скопировав формулу

= Prior_Period * (1 + Inflation)

в строке.

Другие типы имен

Как я сказал ранее, имена могут также относиться к функциям, датам и константам — последние могут быть полезным (например, «Месяцы_ в_ году» определяется как 12), чтобы избежать вставки жесткого кода в формулу.

Слово мудрым

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

Кроме того, будьте осторожны, если вы называете диапазоны которые затем удаляются. Имена диапазонов не будут удалены (даже если они больше не будут отображаться в поле имени ).. Их нужно будет удалить, как описано выше, чтобы избежать возможных ошибок в формулах и т. Д. Важно помнить об этом, поскольку это часто было причиной повреждения файла или сбоя в Excel.

— Лиам Бастик , FCMA, CGMA, FCA, является директором SumProduct, глобальной консалтинговой компании, специализирующейся на обучении работе с Excel. Он также является MVP по Excel (по назначению Microsoft) и автором Введение в финансовое моделирование . Присылайте ему идеи для будущих статей, связанных с Excel, по адресу liam.bastick@sumproduct.com . Чтобы прокомментировать эту статью или предложить идею для другой статьи, свяжитесь с Джеффом Дрю, старшим редактором журнала FM , по адресу Jeff.Drew@aicpa-cima .com .