Як призначити ім'я для діапазону осередків в Excel

Створюючи формули в Excel, ви можете посилатися на клітинки з іншої частини листа у формулах. Але якщо у вас є багато формул, всі ці посилання на клітини можуть заплутати. Є простий спосіб усунути плутанину.

Excel включає функцію, яка називається "Імена", яка може зробити ваші формули більш читабельними та менш заплутаними. Замість того, щоб посилатися на клітинку або діапазон осередків, ви можете присвоїти їй назву або діапазон і використовувати це ім'я у формулах. Це зробить ваші формули набагато легше зрозуміти та підтримувати.

У наведеній нижче формулі ми посилаємося на діапазон клітин (напівжирним шрифтом) з іншого аркуша, який називається «База даних продукту», у тій же робочій книзі. У цьому випадку назва робочого листа дає нам гарне уявлення про те, що міститься в діапазоні комірок, “A2: D7”. Однак, ми можемо використовувати назву для цього діапазону комірок, щоб зробити формулу коротшою і легше читати.

= IF (ISBLANK (A11), "", VLOOKUP (ВСІ, "Database Product '! A2: D7,2, FALSE))

ПРИМІТКА. Для отримання додаткової інформації про функцію VLOOKUP, що використовується у формулі вище, див. Нашу статтю про використання VLOOKUP в Excel. Ви також можете дізнатися, як використовувати функцію «IF» та інші корисні функції.

Створення імені для клітинки або діапазону клітин з використанням поля "Ім'я"

Щоб призначити назву діапазону осередків, виберіть клітинки, які потрібно назвати. Клітини не повинні бути суміжними. Для вибору не суміжних комірок використовуйте клавішу "Ctrl" під час їх вибору.

Клацніть мишею в полі "Name Box" над сіткою комірки.

Введіть ім'я діапазону комірок у полі та натисніть "Enter". Наприклад, ми назвали вибрані комірки на нашому робочому листі "Продукт". Існують правила синтаксису, яких потрібно дотримуватися при виборі імені. Ви можете почати ім'я лише з літерою, підкресленням (_) або зворотною рискою (\ t Інша назва може складатися з букв, цифр, періодів і підкреслення. Існують додаткові правила синтаксису про те, що є правильним, а не для визначення імен.

Пам'ятайте формулу з початку цієї статті? Вона містила посилання на робочу книгу «База даних продуктів» у робочій книзі та діапазон комірок на цьому аркуші. Тепер, коли ми створили назву "Продукти" для представлення діапазону комірок на нашому робочому аркуші "База даних продуктів", ми можемо використовувати цю назву у формулі, показаної напівжирним шрифтом.

= IF (ISBLANK (A11), "" ВИБІР (ВСІ,Продукти,2, FALSE))

ПРИМІТКА. Під час створення імені за допомогою поля "Name Box" область назви за замовчуванням є робочою книгою. Це означає, що ім'я доступне для використання на будь-якому аркуші поточної робочої книги без посилання на певний аркуш. Ви можете обмежити область застосування певним робочим листом, щоб назву робочого листа було використано при посиланні на ім'я, наприклад, у прикладі на початку цієї статті..

Як редагувати імена за допомогою менеджера імен

Excel надає інструмент, який називається «Менеджер імен», що дозволяє легко знаходити, редагувати та видаляти імена у вашій робочій книзі. Можна також використовувати Менеджер імен для створення імен, якщо потрібно вказати більш детальну інформацію про ім'я. Щоб отримати доступ до Менеджера імен, перейдіть на вкладку "Формули".

У розділі "Визначені імена" на вкладці "Формули" натисніть "Менеджер імен".

Відобразиться діалогове вікно Менеджер імен. Щоб відредагувати існуюче ім'я, виберіть ім'я зі списку та натисніть «Редагувати». Наприклад, ми будемо редагувати назву "Продукти".

Відобразиться діалогове вікно “Edit Name”. Ви можете змінити сам "Ім'я", а також додати "Коментар" до назви, надавши більш детальну інформацію про те, що представляє ім'я. Також можна змінити діапазон осередків, до яких присвоюється це ім'я, натиснувши кнопку "Розгорнути діалогове вікно" на правій стороні вікна редагування "Відноситься до".

ПРИМІТКА: Ви побачите, що випадаючий список "Сфера" буде позначено сірим кольором. Коли ви редагуєте існуюче ім'я, ви не можете змінити "Сфера" цієї назви. Ви повинні вибрати область, коли ви вперше створюєте ім'я. Якщо ви хочете, щоб сфера була певним робочим листом, а не цілою робочою книгою, ви можете створити ім'я таким чином, щоб спочатку вказати область. Ми покажемо вам, як це зробити в наступному розділі.

Як приклад, скажімо, ми додали ще один продукт до нашої «Продуктової бази даних», і ми хочемо включити його в діапазон осередків під назвою «Продукти». Коли ми натискаємо кнопку «Розкрити діалогове вікно», діалогове вікно «Редагувати ім'я» зменшується, щоб містити лише вікно редагування «Відноситься до». Ми вибираємо діапазон осередків безпосередньо на робочому аркуші «База даних продукту», включаючи рядок, що містить новий продукт. Ім'я робочого аркуша та діапазон комірок автоматично вводяться в поле редагування "Відноситься до". Щоб прийняти ваш вибір і повернутися до повного діалогового вікна «Редагувати ім'я», натисніть кнопку «Згорнути діалогове вікно». Натисніть кнопку "OK" у діалоговому вікні "Редагувати ім'я", щоб прийняти зміни до імені.

Як видалити ім'я за допомогою менеджера імен

Якщо ви вирішили, що вам більше не потрібне ім'я, його легко видалити. Просто відкрийте діалогове вікно «Менеджер імен», як ми обговорювали в попередньому розділі. Потім у списку імен виберіть ім'я, яке потрібно видалити, і натисніть "Видалити".

У діалоговому вікні підтвердження, яке відображається, натисніть кнопку "OK", якщо ви впевнені, що хочете видалити вибране ім'я. Ви повертаєтеся до діалогового вікна "Менеджер імен". Натисніть "Закрити", щоб закрити його.

Як створити ім'я за допомогою діалогового вікна «Нове ім'я»

Коли ви створюєте нову назву, вибираючи одну або більше комірок, а потім вводячи ім'я в поле "Ім'я", типовою областю назви є вся робоча книга. Отже, що робити, якщо ви хочете обмежити область назви лише конкретним листом?

Виберіть клітинки, до яких потрібно призначити ім'я. Перейдіть на вкладку "Формули" і натисніть "Визначити ім'я" в розділі "Визначені імена".

ПРИМІТКА. Не потрібно спочатку вибирати клітини. Ви також можете вибрати їх за допомогою кнопки «Розгорнути діалогове вікно» пізніше, якщо хочете.

Відобразиться діалогове вікно “New Name”. Зверніть увагу, що це дуже схоже на діалогове вікно “Редагувати ім'я”, згадане раніше. Головна відмінність полягає в тому, що тепер ви можете змінити область назви. Скажімо, ми хочемо обмежити область назви лише робочим листом «Рахунок-фактура». Ми робимо це, якщо хочемо мати змогу використовувати однакову назву для діапазону клітин на іншому аркуші.

По-перше, ми введемо назву, яку ми хочемо використовувати, що в нашому випадку є "Продукти". Запам'ятайте правила синтаксису при створенні свого імені. Потім, щоб обмежити область назви "Продукти" лише робочим листом "Рахунок-фактура", ми виберемо його зі спадного списку "Область"..

ПРИМІТКА: Доступ до діалогового вікна «Нове ім'я» також можна отримати, натиснувши кнопку «Створити» у діалоговому вікні «Менеджер імен».

Введіть більше подробиць про назву, якщо потрібно, у полі "Коментар". Якщо ви не вибрали клітини, до яких ви призначаєте ім'я, натисніть кнопку "Розгорнути діалогове вікно" праворуч від вікна редагування "Відноситься до", щоб вибрати клітини так само, як ми зробили, коли ми раніше редагували ім'я . Натисніть "OK", щоб завершити створення нового імені.

Ім'я автоматично вставляється в одне й те саме поле "Name", яке ми використовували для присвоєння імені діапазону осередків на початку цієї статті. Тепер ми можемо замінити посилання на діапазон осередків ("База даних продуктів"! $ A $ 2: $ D: 7) на назву (Продукти) у формулах на робочому аркуші "Рахунок-фактура", як це було зроблено раніше в цій статті..

Як використовувати ім'я для представлення постійної величини

При створенні імені не потрібно посилатися на клітинки. Ви можете використовувати ім'я для представлення константи або навіть формули. Наприклад, робочий лист нижче показує обмінний курс, який використовується для розрахунку ціни в євро для різних розмірів віджетів. Оскільки часто обмінний курс змінюється, було б корисно, якщо б він знаходився в місці, яке легко знайти та оновити. Оскільки імена легко редагувати, як обговорювалося раніше, ми можемо створити ім'я для представлення обмінного курсу і присвоїти йому ім'я значення.

Зверніть увагу, що формула містить абсолютну посилання на клітинку до комірки, що містить поточний обмінний курс. Ми б скористалися назвою, яка посилатиметься на поточний обмінний курс, щоб легше було змінити формулу, і легше зрозуміти формули, які використовують обмінний курс..

Щоб створити ім'я, яке буде присвоєно постійному значенню, відкрийте діалогове вікно «Нове ім'я», натиснувши вкладку «Формули», а потім клацніть «Визначити ім'я» у розділі «Визначені імена». Введіть ім'я, яке буде представляти константне значення, наприклад "ExchangeRate". Щоб присвоїти цьому імені значення, введіть знак рівності (=) у полі "Відноситься до", а потім значення. Між знаком рівності та значенням не повинно бути пробілу. Натисніть "OK", щоб завершити створення імені.

ПРИМІТКА. Якщо у вашій робочій книзі існує формула, яку ви використовуєте в багатьох місцях, ви можете ввести цю формулу в поле редагування "Відноситься до", щоб просто ввести назву в кожну клітинку, де потрібно використовувати формулу.

Тепер, ми можемо використовувати нове ім'я у формулах, де ми хочемо використовувати обмінний курс. Коли ми натискаємо на клітинку з формулою, яка містить абсолютну посилання на клітинку, зверніть увагу на результат "0.00". Це пояснюється тим, що ми видалили курс обміну із згаданої клітини. Цю посилання на клітинку буде замінено на нове ім'я, яке ми створили.

Виділіть посилання на клітинку (або іншу частину формули, яку потрібно замінити на ім'я) і почніть вводити створене ім'я. Під час введення будь-яких відповідних імен відображається у спливаючому вікні. Виберіть ім'я, яке потрібно вставити у формулу, натиснувши на нього у спливаючому вікні.

Ім'я вставляється у формулу. Натисніть клавішу "Enter", щоб прийняти зміну і оновити клітинку.

Зверніть увагу, що результат оновлюється за допомогою обмінного курсу, який називається ім'ям.

Імена дуже корисні, якщо ви створюєте складні робочі книги Excel з великою кількістю формул. Якщо вам потрібно розповсюджувати свої книги іншим користувачам, використання імен полегшує іншим, так само як і ви, зрозуміти ваші формули.