Як створити іменовані діапазони в Excel (Покроковий посібник)

Що в назві?

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

У цьому посібнику ви дізнаєтесь, як створювати іменовані діапазони в Excel та як використовувати їх для економії часу.

Іменовані діапазони в Excel - Вступ

Якщо комусь доведеться подзвонити мені або звернутися до мене, він буде використовувати моє ім'я (замість того, щоб сказати, що самець залишається в такому собі місці з таким і таким ростом і вагою).

Правильно?

Подібним чином у програмі Excel можна дати ім’я клітині або діапазону клітинок.

Тепер, замість використання посилання на комірку (наприклад, A1 або A1: A10), ви можете просто використати ім’я, яке ви йому призначили.

Наприклад, припустимо, що у вас є набір даних, як показано нижче:

У цьому наборі даних, якщо вам потрібно звертатися до діапазону, що містить дату, вам доведеться використовувати A2: A11 у формулах. Аналогічно для представників відділу продажів та продажів вам доведеться використовувати B2: B11 та C2: C11.

Хоча це нормально, коли у вас є лише кілька точок даних, але якщо у вас великі складні набори даних, використання посилань на клітинки для посилання на дані може зайняти багато часу.

Іменовані діапазони Excel полегшують посилання на набори даних у Excel.

Ви можете створити іменований діапазон в Excel для кожної категорії даних, а потім використовувати це ім’я замість посилань на клітинки. Наприклад, дати можна назвати "Дата", дані представника відділу продажів - "Продажі", а дані продажів - "Продажі".

Ви також можете створити ім'я для однієї клітинки. Наприклад, якщо у комірці є відсоток від продажів, ви можете назвати цю комірку "комісією".

Переваги створення іменованих діапазонів у Excel

Ось переваги використання іменованих діапазонів у Excel.

Використовуйте імена замість посилань на комірки

Коли ви створюєте іменовані діапазони в Excel, ви можете використовувати ці імена замість посилань на клітинки.

Наприклад, для наведеного вище набору даних можна використовувати = SUM (SALES) замість = SUM (C2: C11).

Подивіться на перелічені нижче формули. Замість використання посилань на клітинки я використав Іменовані діапазони.

• Кількість продажів вартістю більше 500: = COUNTIF (Продажі,>> 500 ″)
• Сума всіх продажів, здійснених Томом: = SUMIF (SalesRep, «Tom», Sales)
• Комісія, зароблена Джо (продажі Джо, помножені на відсоток комісії):
  = SUMIF (SalesRep, «Joe», Sales)*Комісія

Ви б погодилися, що ці формули легко створити та зрозуміти (особливо, якщо ви поділитесь ними з кимось іншим або повторно переглянете його самостійно.

Немає необхідності повертатися до набору даних, щоб вибрати клітинки

Ще однією важливою перевагою використання Іменованих діапазонів у Excel є те, що вам не потрібно повертатися назад і вибирати діапазони клітинок.

Ви можете просто ввести пару алфавітів цього іменованого діапазону, і Excel покаже відповідні іменовані діапазони (як показано нижче):

Іменовані діапазони роблять формули динамічними

Використовуючи Іменовані діапазони в Excel, ви можете зробити формули Excel динамічними.

Наприклад, у разі комісії від продажу замість використання значення 2,5%можна використовувати Іменований діапазон.

Тепер, якщо ваша компанія згодом вирішить збільшити комісію до 3%, ви можете просто оновити Іменований діапазон, і всі розрахунки автоматично оновляться, щоб відобразити нову комісію.

Як створити іменовані діапазони в Excel

Ось три способи створення іменованих діапазонів у Excel:

Спосіб №1 - Використання Визначення імені

Нижче наведено кроки для створення іменованих діапазонів у Excel за допомогою Визначення імені:

• Виберіть діапазон, для якого потрібно створити іменований діапазон у Excel.
• Перейдіть до Формули -> Визначити назву.
• У діалоговому вікні «Нова назва» введіть ім’я, яке потрібно призначити вибраному діапазону даних. Ви можете вказати область дії як цілу книгу або окремий аркуш. Якщо вибрати певний аркуш, ім’я буде недоступне на інших аркушах.
• Натисніть OK.

Це створить Іменований діапазон SALESREP.

Спосіб №2: Використання вікна імені

• Виберіть діапазон, для якого потрібно створити назву (не вибирайте заголовки).
• Перейдіть до вікна імені ліворуч від рядка формул і введіть назву, з якою потрібно створити іменований діапазон.
• Зауважте, що ім’я, створене тут, буде доступне для всієї книги. Якщо ви хочете обмежити його робочим аркушем, скористайтеся способом 1.

Спосіб №3: Використання опції «Створити з вибору»

Це рекомендований спосіб, коли у вас є дані у вигляді таблиці, і ви хочете створити іменований діапазон для кожного стовпця/рядка.

Наприклад, у наборі даних нижче, якщо ви хочете швидко створити три іменовані діапазони (Date, Sales_Rep та Sales), то ви можете скористатися наведеним нижче методом.

Ось кроки для швидкого створення іменованих діапазонів із набору даних:

• Виберіть весь набір даних (включаючи заголовки).
• Перейдіть до Формули -> Створити з виділення (Комбінація клавіш - Control + Shift + F3). Відкриється діалогове вікно "Створення імен із виділення".
• У діалоговому вікні Створення імен із виділення перевірте параметри, у яких у вас є заголовки. У цьому випадку ми вибираємо верхній рядок, лише коли заголовок знаходиться у верхньому рядку. Якщо у вас є заголовки як у верхньому рядку, так і в лівому стовпці, ви можете вибрати обидва. Подібним чином, якщо ваші дані упорядковані, коли заголовки знаходяться лише в лівому стовпці, тоді ви перевіряєте лише опцію Ліва колонка.

Це створить три іменовані діапазони - Date, Sales_Rep та Sales.

Зауважте, що він автоматично збирає імена з заголовків. Якщо між словами є пробіл, він вставляє підкреслення (оскільки у названих діапазонах не може бути пробілів).

Конвенція про найменування іменованих діапазонів у Excel

Під час створення іменованих діапазонів у Excel потрібно знати певні правила іменування:

• Першим символом іменованого діапазону має бути буква та символ підкреслення (_) або зворотна коса риса (\). Якщо це щось інше, він покаже помилку. Решта символів можуть бути буквами, цифрами, спеціальними символами, крапкою або підкресленням.
• Не можна використовувати імена, які також представляють посилання на клітинки в Excel. Наприклад, ви не можете використовувати AB1, оскільки це також посилання на клітинку.
• Ви не можете використовувати пробіли під час створення іменованих діапазонів. Наприклад, ви не можете мати представника відділу продажів як іменований діапазон. Якщо ви хочете об’єднати два слова та створити іменований діапазон, для його створення використовуйте символи підкреслення, крапки або великі літери. Наприклад, ви можете мати Sales_Rep, SalesRep або SalesRep.
  • Під час створення іменованих діапазонів Excel однаково обробляє великі та малі регістри. Наприклад, якщо ви створите іменований діапазон ПРОДАЖ, то ви не зможете створити інший іменований діапазон, такий як "продажі" або "Продажі".
• Іменований діапазон може містити до 255 символів.

Занадто багато іменованих діапазонів в Excel? Не турбуйтесь

Іноді у великих наборах даних і складних моделях ви можете створити багато іменованих діапазонів у Excel.

Що робити, якщо ви не пам’ятаєте назву створеного вами іменного діапазону?

Не хвилюйтесь - ось кілька корисних порад.

Отримання імен усіх названих діапазонів

Ось кроки, щоб отримати список усіх створених вами діапазонів імен:

• Перейдіть на вкладку Формули.
• У групі Визначені імена натисніть кнопку Використовувати у формулі.
• Натисніть «Вставити імена».

Це дасть вам список усіх іменованих діапазонів у цій книзі. Щоб використати іменований діапазон (у формулах або комірку), двічі клацніть на ньому.

Відображення відповідних іменованих діапазонів

• Якщо ви маєте уявлення про Ім'я, введіть кілька початкових символів, і Excel покаже спадне меню відповідних імен.

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

Якщо ви вже створили іменований діапазон, ви можете відредагувати його, виконавши такі дії:

• Перейдіть на вкладку Формули та натисніть Менеджер імен.
• У діалоговому вікні "Менеджер імен" будуть перераховані всі іменовані діапазони у цій книзі. Двічі клацніть на іменованому діапазоні, який потрібно відредагувати.
• У діалоговому вікні Редагувати ім'я внесіть зміни.
• Натисніть OK.
• Закрийте діалогове вікно Менеджер імен.

Корисні ярлики діапазонів імен (потужність F3)

Ось кілька корисних комбінацій клавіш, які стануть у нагоді під час роботи з Іменованими діапазонами в Excel:

• Щоб отримати список усіх іменованих діапазонів та вставити його у формулу: F3
• Щоб створити нову назву за допомогою діалогового вікна "Менеджер імен": Control + F3
• Щоб створити іменовані діапазони з виділення: Control + Shift + F3

Створення динамічних іменованих діапазонів у Excel

Поки що в цьому підручнику ми створили статичні іменовані діапазони.

Це означає, що ці іменовані діапазони завжди будуть посилатися на один і той же набір даних.

Наприклад, якщо A1: A10 було названо як "Продажі", це завжди буде посилатися на A1: A10.

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

У світі наборів даних, які постійно розширюються, це може зайняти багато вашого часу. Кожного разу, коли ви отримуєте нові дані, можливо, вам доведеться оновлювати Іменовані діапазони в Excel.

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

Наприклад, наприклад, якщо я додаю дві додаткові точки даних про продажі, динамічний іменований діапазон автоматично посилатиметься на A1: A12.

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

Давайте подивимося, як створити динамічні іменовані діапазони в Excel.

Припустимо, ми маємо дані про продажі у комірці A2: A11.

Ось кроки для створення динамічних іменованих діапазонів у Excel:

1. 1. Перейдіть на вкладку Формула та натисніть Визначити назву.
   2. У діалоговому вікні "Нова назва" введіть наступне:
      • Назва: Продажі
      • Область застосування: Робочий зошит
      • Посилається на: = $ A $ 2: INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” та ””))
   3. Натисніть OK.

Готово!

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

Як працюють динамічні іменовані діапазони?

Щоб пояснити, як це працює, вам потрібно знати трохи більше про функцію Excel INDEX.

Більшість людей використовують INDEX для повернення значення зі списку на основі номера рядка та стовпця.

Але у функції INDEX є й інша сторона.

До цього можна звикнути повернути посилання на клітинку коли він використовується як частина посилання на клітинку.

Наприклад, ось формула, яку ми використали для створення динамічного іменованого діапазону:

= $ A $ 2: INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, "" & ""))

ІНДЕКС ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””) -> Очікується, що ця частина формули поверне значення (яке буде 10 -м значенням зі списку, враховуючи, що є десять пунктів).

Однак при використанні перед посиланням (=$ A $ 2:ІНДЕКС ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””))) він повертає посилання на клітинку замість значення.

Отже, тут повертається = $ A $ 2: $ A $ 11

Якщо ми додамо два додаткових значення до стовпця продажів, воно поверне = $ A $ 2: $ A $ 13

Коли ви додаєте нові дані до списку, функція Excel COUNTIF повертає кількість непустих клітинок у даних. Цей номер використовується функцією INDEX для отримання посилання на комірку останнього елемента у списку.

Примітка:

• Це буде працювати, лише якщо в даних немає порожніх клітинок.
• У наведеному вище прикладі я призначив велику кількість клітинок (A2: A100) для формули іменованого діапазону. Ви можете налаштувати це на основі свого набору даних.

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

Вам також можуть сподобатися такі ресурси Excel:

• Безкоштовні шаблони Excel.
• Безкоштовне онлайн-навчання Excel (7-частинний онлайн-відеокурс).
• Приклади корисних макрокодів Excel.
• 10 прикладів розширеного Excel VLOOKUP.
• Створення розкривного списку в Excel.
• Створення іменованого діапазону в Таблицях Google.
• Як звернутись до іншого аркуша чи робочої книги в Excel