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

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

Net_Present_Value = NPV (Discount_Rate, Cash_Flows)

имеет большее значение, чем

C10 = ЧПС (C2; C6: C8)

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

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

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

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

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

• С Именами ваши формулы станут более понятными и простыми в использовании. Например, формула Чистый доход = Валовой доход - Вычеты более интуитивно понятна, чем формула C40 = C20 - B18.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Используя Name box.

• Используя New Name диалоговое окно.

• Используя Selection диалоговое окно.

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

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

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

Step 2 - Щелкните поле "Имя".

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

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

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

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

Step 2 - Щелкните вкладку "Формулы".

Step 3- Нажмите «Определить имя» в группе «Определенные имена». ВNew Name появится диалоговое окно.

Step 4 - Введите имя в поле рядом с «Имя».

Step 5- Убедитесь, что диапазон, выбранный и отображаемый в поле Refers, правильный. Щелкните ОК.

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

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

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

Step 2 - Щелкните вкладку "Формулы".

Step 3 - Щелкните Create from Selectionв группе Определенные имена. ВCreate Names from Selection появится диалоговое окно.

Step 4 - Выберите верхнюю строку, поскольку текст отображается в верхней строке выделенного фрагмента.

Step 5- Убедитесь, что диапазон, который был выбран и отображен в поле рядом с полем «Ссылается», правильный. Щелкните ОК.

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

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

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

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

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

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

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

Название Savings_Bank_Interest_Rateустановлен на постоянное значение 5%. Вы можете проверить это в Диспетчере имен. Вы можете видеть, что значение установлено на 0,05 и вRefers to = 0,05.

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

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

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

• Нажмите Name Manager в Defined Namesгруппа. ВName Managerпоявится диалоговое окно. Отображаются все имена, определенные в текущей книге.

Список Names отображаются с определенным Values, Cell Reference (включая имя листа), Scope и Comment.

Менеджер имен имеет возможность:

• Определить New Имя с New Кнопка.

• Edit определенное имя.

• Delete определенное имя.

• Filter Определенные имена по категориям.

• Измените диапазон определенного имени, чтобы оно Refers to.

Объем имени

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

Вы можете определить Scope из New Name когда вы определяете имя с помощью New Nameдиалоговое окно. Например, вы определяете имя Interest_Rate. Тогда вы можете увидеть, чтоScope из New Name Interest_Rate - это Workbook.

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

Step 1- Щелкните стрелку вниз в поле «Область действия». Доступные параметры области отображаются в раскрывающемся списке.

Параметры области включают Workbookи имена листов в книге.

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

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

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

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

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

Step 1 - Щелкните Filter в Name Manager диалоговое окно.

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

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

Вы можете подать заявку Filter к defined Names выбрав один или несколько из этих вариантов.

Step 2 - Выбрать Names with Errors. Будут отображаться имена, содержащие значения ошибок.

Step 3 - Из полученного списка Names, выберите те, которые хотите удалить, и нажмите Delete.

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

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

Вы можете использовать Edit вариант в Name Manager диалоговое окно -

• Изменить Name.

• Измените Refers to спектр

• Отредактируйте Comment в Name.

Изменить имя

Step 1 - Щелкните ячейку, содержащую функцию Large.

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

Step 2 - Щелкните значок Name вы хотите отредактировать в Name Managerдиалоговое окно. В этом случае,Array1.

Step 3 - Щелкните Edit. ВEdit Name появится диалоговое окно.

Step 4 - Измените Name набрав новое имя, которое вы хотите, в Name Box.

Step 5 - Щелкните значок Range кнопка справа от Refers to Установите рамку и включите новые ссылки на ячейки.

Step 6 - Добавить Comment (Необязательный)

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

Щелкните ОК. Вы увидите внесенные изменения.

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

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

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

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

Step 1 - Использование Create from Selection, определите имена.

Step 2- Выберите ячейку, содержащую формулу. Нажмите

рядом сDefine Name в Defined Names группа по Formulasтаб. В раскрывающемся списке щелкнитеApply Names.

Step 3 - The Apply Namesпоявится диалоговое окно. ВыберитеNames что ты хочешь Apply и нажмите ОК.

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

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

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

Вы можете использовать Name в Formula следующими способами -

• Набрав Name если ты это помнишь, или

• Набрав первые одну или две буквы и используя Excel Formula Autocomplete характерная черта.

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

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

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

• Используя Paste Name диалоговое окно.

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

  • Выберите Name в Paste Names диалоговое окно и дважды щелкните его.

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

Вы можете получить все Names в вашей рабочей тетради вместе со своими References и Save их или Print их.

• Щелкните пустую ячейку, в которую вы хотите скопировать Names в вашей книге.

• Нажмите Use in Formula в Defined Names группа.

• Нажмите Paste Names из раскрывающегося списка.

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

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

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

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

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

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

• Создайте Names с участием Create from Selection как для студентов, так и для экзаменов.

• Твой Names будет выглядеть следующим образом -

• Тип =Kodeda_Adam Exam_1 в B11.

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

Это отобразит оценки Кодеды, Адама в Экзамене 1, которые указаны в ячейке C6.

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

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

Вы также можете скопировать формулу с именами на другой лист, copy и pasteпри условии, что все names в formula иметь workbook в виде Scope. В противном случае вы получите#VALUE ошибка.