Работа с именами диапазонов

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

Net_Present_Value = NPV (Discount_Rate, Cash_Flows)

более значимым, чем

C10 = NPV (C2, C6: C8)

С помощью Excel вы можете создавать и использовать значимые имена для различных частей ваших данных. Преимущества использования имен диапазонов включают —

• Значимое имя диапазона (например, Cash_Flows) гораздо проще запомнить, чем адрес диапазона (например, C6: C8).

• Ввод имени менее подвержен ошибкам, чем ввод адреса ячейки или диапазона.

• Если вы неправильно введете имя в формуле, Excel отобразит #NAME? ошибка.

• Вы можете быстро перемещаться по областям вашего рабочего листа, используя определенные имена.

• С Именами ваши формулы станут более понятными и простыми в использовании. Например, формула Net_Income = Gross_Income — Отчисления является более интуитивно понятным, чем C40 = C20 — B18.

• Создание формул с именами диапазонов проще, чем с помощью адресов ячеек или диапазонов. Вы можете скопировать имя ячейки или диапазона в формулу, используя формулу Автозаполнение.

Значимое имя диапазона (например, Cash_Flows) гораздо проще запомнить, чем адрес диапазона (например, C6: C8).

Ввод имени менее подвержен ошибкам, чем ввод адреса ячейки или диапазона.

Если вы неправильно введете имя в формуле, Excel отобразит #NAME? ошибка.

Вы можете быстро перемещаться по областям вашего рабочего листа, используя определенные имена.

С Именами ваши формулы станут более понятными и простыми в использовании. Например, формула Net_Income = Gross_Income — Отчисления является более интуитивно понятным, чем C40 = C20 — B18.

Создание формул с именами диапазонов проще, чем с помощью адресов ячеек или диапазонов. Вы можете скопировать имя ячейки или диапазона в формулу, используя формулу Автозаполнение.

В этой главе вы узнаете —

• Синтаксические правила для имен.
• Создание имен для ссылок на ячейки.
• Создание имен для констант.
• Управление именами.
• Область ваших определенных имен.
• Редактирование имен.
• Фильтрация имен.
• Удаление имен.
• Применяя имена.
• Использование имен в формуле.
• Просмотр имен в рабочей книге.
• Использование имен вставок и списка вставок.
• Использование имен для пересечений диапазонов.
• Копирование формул с именами.

Копирование имени с помощью автозаполнения формулы

Введите первую букву имени в формуле. Появляется раскрывающийся список с именами функций и именами диапазонов. Выберите нужное имя. Это скопировано в вашу формулу.

Правила синтаксиса имени диапазона

Excel имеет следующие правила синтаксиса для имен —

• Вы можете использовать любую комбинацию букв, цифр и символов — подчеркивание, обратную косую черту и точки. Другие символы не допускаются.

• Имя может начинаться с символа, подчеркивания или обратной косой черты.

• Имя не может начинаться с цифры (пример — 1stQuarter) или напоминать адрес ячейки (пример — QTR1).

• Если вы предпочитаете использовать такие имена, перед именем ставьте подчеркивание или обратную косую черту (пример — \ 1stQuarter, _QTR1).

• Имена не могут содержать пробелы. Если вы хотите различить два слова в имени, вы можете использовать подчеркивание (например, Cash_Flows вместо денежных потоков)

• Определенные имена не должны конфликтовать с внутренними именами Excel, такими как Print_Area, Print_Titles, Consolidate_Area и Sheet_Title . Если вы определите те же имена, они переопределят внутренние имена Excel, и вы не получите никакого сообщения об ошибке. Тем не менее, рекомендуется не делать этого.

• Делайте имена короткими, но понятными, хотя вы можете использовать до 255 символов

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

Имя может начинаться с символа, подчеркивания или обратной косой черты.

Имя не может начинаться с цифры (пример — 1stQuarter) или напоминать адрес ячейки (пример — QTR1).

Если вы предпочитаете использовать такие имена, перед именем ставьте подчеркивание или обратную косую черту (пример — \ 1stQuarter, _QTR1).

Имена не могут содержать пробелы. Если вы хотите различить два слова в имени, вы можете использовать подчеркивание (например, Cash_Flows вместо денежных потоков)

Определенные имена не должны конфликтовать с внутренними именами Excel, такими как Print_Area, Print_Titles, Consolidate_Area и Sheet_Title . Если вы определите те же имена, они переопределят внутренние имена Excel, и вы не получите никакого сообщения об ошибке. Тем не менее, рекомендуется не делать этого.

Делайте имена короткими, но понятными, хотя вы можете использовать до 255 символов

Создание имен диапазонов

Вы можете создать имена диапазонов двумя способами —

• Используя поле Имя .

• Использование диалогового окна « Новое имя ».

• Использование диалогового окна « Выбор ».

Используя поле Имя .

Использование диалогового окна « Новое имя ».

Использование диалогового окна « Выбор ».

Создайте имя диапазона, используя поле имени

Чтобы создать имя диапазона, самый быстрый способ — использовать поле « Имя», которое находится слева от строки формул. Следуйте инструкциям ниже

Шаг 1 — Выберите диапазон, для которого вы хотите определить имя.

Шаг 2 — Нажмите на поле Имя.

Шаг 3 — Введите имя и нажмите Enter, чтобы создать имя.

Создать имя диапазона с помощью диалогового окна «Новое имя»

Вы также можете создавать имена диапазонов, используя диалоговое окно «Новое имя» на вкладке «Формулы».

Шаг 1 — Выберите диапазон, для которого вы хотите определить имя.

Шаг 2 — Перейдите на вкладку Формулы.

Шаг 3 — Нажмите «Определить имя» в группе «Определенные имена». Откроется диалоговое окно « Новое имя ».

Шаг 4 — Введите имя в поле рядом с именем

Шаг 5 — Убедитесь, что диапазон, выбранный и отображенный в поле «Ссылки», правильный. Нажмите ОК.

Создать имя диапазона с помощью диалогового окна «Создать имена из выделения»

Вы также можете создавать имена диапазонов с помощью диалогового окна « Создание имен» на вкладке «Формулы», если у вас есть текстовые значения, которые находятся рядом с вашим диапазоном.

Шаг 1 — Выберите диапазон, для которого вы хотите определить имя вместе со строкой / столбцом, который содержит имя.

Шаг 2 — Перейдите на вкладку Формулы.

Шаг 3 — Нажмите « Создать из выделения» в группе «Определенные имена». Откроется диалоговое окно « Создать имена из выделения ».

Шаг 4 — Выберите верхнюю строку, так как текст появится в верхней строке выделения.

Шаг 5 — Проверьте правильность диапазона, который был выбран и отображен в поле рядом со ссылкой. Нажмите ОК.

Теперь вы можете найти наибольшее значение в диапазоне с = Sum (имя студента), как показано ниже —

Вы можете создавать имена с множественным выбором также. В приведенном ниже примере вы можете назвать ряд оценок каждого ученика именем ученика.

Теперь вы можете найти итоговые оценки для каждого учащегося с = Sum (имя учащегося), как показано ниже.

Создание имен для констант

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

В приведенном ниже примере процентная ставка Сбербанка установлена ​​на 5%.

• Нажмите Определить имя.
• В диалоговом окне «Новое имя» введите Savings_Bank_Interest_Rate в поле «Имя».
• В области действия выберите «Рабочая книга».
• В поле «Относится к» очистите содержимое и введите 5%.
• Нажмите ОК.

Имя Savings_Bank_Interest_Rate установлено равным 5%. Вы можете проверить это в Name Manager. Вы можете видеть, что значение установлено равным 0,05, а в ссылке имеется значение = 0,05.

Управление именами

Книга Excel может иметь любое количество именованных ячеек и диапазонов. Вы можете управлять этими именами с помощью диспетчера имен.

• Нажмите вкладку Формулы.

• Нажмите « Диспетчер имен» в группе « Определенные имена ». Откроется диалоговое окно « Диспетчер имен ». Отображаются все имена, определенные в текущей книге.

Нажмите вкладку Формулы.

Нажмите « Диспетчер имен» в группе « Определенные имена ». Откроется диалоговое окно « Диспетчер имен ». Отображаются все имена, определенные в текущей книге.

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

Менеджер имен имеет следующие возможности:

• Определите новое имя с помощью новой кнопки.

• Изменить определенное имя.

• Удалить определенное имя.

• Отфильтруйте определенные имена по категориям.

• Изменить диапазон определенного имени, к которому оно относится .

Определите новое имя с помощью новой кнопки.

Изменить определенное имя.

Удалить определенное имя.

Отфильтруйте определенные имена по категориям.

Изменить диапазон определенного имени, к которому оно относится .

Объем имени

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

Вы можете определить область действия нового имени, когда определяете имя в диалоговом окне « Новое имя ». Например, вы определяете имя Interest_Rate. Затем вы можете увидеть, что область действия нового имени Interest_Rate — это рабочая книга .

Предположим, вы хотите ограничить сферу действия этой процентной ставки только данным листом .

Шаг 1 — Нажмите стрелку вниз в поле «Область». Доступные параметры Scope появятся в раскрывающемся списке.

Опции Scope включают Workbook и имена листов в книге.

Шаг 2 — Щелкните имя текущей рабочей таблицы, в данном случае NPV и нажмите OK. Вы можете определить / найти имя листа на вкладке листа.

Шаг 3 — Чтобы убедиться, что Scope является рабочим листом, нажмите Name Manager . В столбце Scope вы найдете NPV для Interest_Rate. Это означает, что вы можете использовать Имя Interest_Rate только в NPV Рабочего листа, но не в других Рабочих листах.

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

Удаление имен со значениями ошибок

Иногда бывает так, что определение имени может иметь ошибки по разным причинам. Вы можете удалить такие имена следующим образом —

Шаг 1 — Нажмите « Фильтр» в диалоговом окне « Диспетчер имен ».

Появятся следующие параметры фильтрации —

• Очистить фильтр
• Имена, ограниченные рабочим листом
• Имена, относящиеся к книге
• Имена с ошибками
• Имена без ошибок
• Определенные имена
• Имена таблиц

Вы можете применить фильтр к определенным именам , выбрав один или несколько из этих параметров.

Шаг 2 — Выберите имена с ошибками . Имена, содержащие значения ошибок, будут отображены.

Шаг 3 — Из полученного списка имен выберите те, которые вы хотите удалить, и нажмите « Удалить» .

Вы получите сообщение, подтверждающее удаление. Нажмите ОК.

Редактирование Имен

Вы можете использовать опцию Edit в диалоговом окне Name Manager, чтобы —

• Изменить имя

• Изменить ссылки для диапазона

• Редактировать комментарий в имени .

Изменить имя

Изменить ссылки для диапазона

Редактировать комментарий в имени .

Изменить имя

Шаг 1 — Нажмите на ячейку, содержащую функцию Large .

Как видите, в массив добавлено еще два значения, но они не включены в функцию, так как они не являются частью Array1.

Шаг 2 — Щелкните по имени, которое вы хотите изменить, в диалоговом окне « Диспетчер имен ». В этом случае Array1 .

Шаг 3 — Нажмите « Изменить» . Откроется диалоговое окно « Изменить имя ».

Шаг 4 — Измените имя , введя новое имя, которое вы хотите в поле для имени .

Шаг 5 — Нажмите кнопку Range справа от Refers to Box и добавьте новые ссылки на ячейки.

Шаг 6 — Добавить комментарий (необязательно)

Обратите внимание, что Scope неактивен и, следовательно, не может быть изменен.

Нажмите ОК. Вы будете наблюдать сделанные изменения.

Применение имен

Рассмотрим следующий пример —

Как вы заметили, имена не определены и не используются в функции PMT. Если вы поместите эту функцию где-то еще на рабочем листе, вам также необходимо запомнить, где именно находятся значения параметров. Вы знаете, что использование имен является лучшим вариантом.

В этом случае функция уже определена со ссылками на ячейки, которые не имеют имен. Вы все еще можете определить имена и применить их.

Шаг 1 — Используя Create from Selection , определите имена.

Шаг 2 — Выберите ячейку, содержащую формулу. Нажмите рядом с Определить имя в группе Определенные имена на вкладке Формулы . В раскрывающемся списке нажмите « Применить имена» .

Шаг 3 — Откроется диалоговое окно « Применить имена ». Выберите имена, которые вы хотите применить, и нажмите OK.

Выбранные имена будут применены к выбранным ячейкам.

Вы также можете применить имена ко всему рабочему листу, выбрав рабочий лист и повторив вышеуказанные действия.

Использование имен в формуле

Вы можете использовать Имя в Формуле следующими способами:

• Введите имя, если вы его помните, или

• Введите первые одну или две буквы и воспользуйтесь функцией автозаполнения формул Excel .

• Нажмите «Использовать в формуле» в группе «Определенные имена» на вкладке «Формулы».

  • Выберите нужное имя из выпадающего списка определенных имен.

  • Дважды щелкните по этому имени.

Введите имя, если вы его помните, или

Введите первые одну или две буквы и воспользуйтесь функцией автозаполнения формул Excel .

Нажмите «Использовать в формуле» в группе «Определенные имена» на вкладке «Формулы».

Выберите нужное имя из выпадающего списка определенных имен.

Дважды щелкните по этому имени.

• Использование диалогового окна « Вставить имя ».

  • Выберите опцию Вставить имена из раскрывающегося списка определенных имен. Откроется диалоговое окно «Вставить имя».

  • Выберите имя в диалоговом окне « Вставить имена » и дважды щелкните его.

Использование диалогового окна « Вставить имя ».

Выберите опцию Вставить имена из раскрывающегося списка определенных имен. Откроется диалоговое окно «Вставить имя».

Выберите имя в диалоговом окне « Вставить имена » и дважды щелкните его.

Просмотр имен в книге

Вы можете получить все имена в своей книге вместе с их ссылками и сохранить их или распечатать .

• Нажмите пустую ячейку, в которую вы хотите скопировать имена в своей книге.

• Нажмите « Использовать в формуле» в группе « Определенные имена ».

• Нажмите Вставить имена из выпадающего списка.

• Нажмите Paste List в открывшемся диалоговом окне Paste Name .

Нажмите пустую ячейку, в которую вы хотите скопировать имена в своей книге.

Нажмите « Использовать в формуле» в группе « Определенные имена ».

Нажмите Вставить имена из выпадающего списка.

Нажмите Paste List в открывшемся диалоговом окне Paste Name .

Список имен и соответствующие им ссылки копируются в указанное место на вашем рабочем листе, как показано на снимке экрана ниже —

Использование имен для пересечений диапазонов

Пересечения диапазонов — это те отдельные ячейки, которые имеют два общих диапазона.

Например, в данных, приведенных ниже, диапазон B6: F6 и диапазон C3: C8 имеют общую ячейку C6, которая фактически представляет оценки, набранные учеником Кодедой, Адамом в экзамене 1.

Вы можете сделать это более значимым с помощью Range Names .

• Создать имена с помощью Создать из выбора как для студентов, так и для экзаменов.

• Ваши имена будут выглядеть следующим образом —

Создать имена с помощью Создать из выбора как для студентов, так и для экзаменов.

Ваши имена будут выглядеть следующим образом —

• Тип = Кодеда_Адам Экзамен_1 в B11.

Тип = Кодеда_Адам Экзамен_1 в B11.

Здесь вы используете операцию пересечения диапазонов, расстояние между двумя диапазонами.

Это покажет знаки Кодеды, Адама в экзамене 1, которые даны в ячейке C6.

Копирование формул с именами

Вы можете скопировать формулу с именами путем копирования и вставки на одном листе.

Вы также можете скопировать формулу с именами на другой лист путем копирования и вставки при условии, что все имена в формуле имеют рабочую книгу в качестве области действия . В противном случае вы получите ошибку #VALUE .