Створюємо іменовані діапазони в Excel

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

Тож почнемо!

Що таке іменовані діапазони в Excel?

Отже, якщо вам необхідно часто обробляти будь-які конкретні елементи таблиці – ви можете просто назвати їх і виділяти вже за назвою (ім'я).

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

Допустимо, у нас є така табличка:

Створюємо іменовані діапазони в Excel

Наприклад, тут ми можемо створити діапазон Дат, імен продавців та продажів.

Вам може здатися, що це марно, але я запевняю вас – ви помиляєтеся. Коли йдеться про великі обсяги даних – іменовані діапазони одна з кращих функцій!

Як я вже сказав, ми можемо створити діапазон осередків з кожного стовпчика:

Створюємо іменовані діапазони в Excel

А також надати їй конкретне ім'я.

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

Давайте розглянемо переваги використання цієї функції.

Використовуємо імена у функціях

Отже, припустимо, ви створили іменований діапазон. Тепер ви можете використовувати її навіть у функціях!

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

=СУМ(SALES)

Для нашої таблички ми можемо використовувати такі функції, якщо створимо діапазон кожного стовпчика:

  • Сума продажів тих продавців, які продали більше 500 позицій: = РАХУНКИ(Sales,»>500″);
  • Сума продажів продавця Tom: = Сумісність (SalesRep, «Tom», Sales);
  • Чистий прибуток продавця Joe:

=СУМІСЛІ(SalesRep, “Joe”,Sales)*Комісія

Загалом нічого складного немає, ми просто замінюємо посилання на комірки у формулах ім'ям діапазону.

Прописати ім'я набагато швидше, ніж діапазон осередків

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

Ви починаєте вводити його ім'я і Excel сам розуміє, що ви хочете написати, таким чином помилки виключені:

Створюємо іменовані діапазони в Excel

Функції стають динамічними

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

А якщо ж ви використовуєте іменований діапазон – все перераховуватиметься щоразу!

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

Є 3 методи, давайте розглянемо кожен.

Функція “Присвоїти ім'я”

Покрокова інструкція:

  • Виділіть комірку (або діапазон);

Створюємо іменовані діапазони в Excel

  • Клацніть на “Формули” -> “Присвоїти ім'я”;

Створюємо іменовані діапазони в Excel

  • Отже, вкажіть ім'я та перевірте діапазон;

Створюємо іменовані діапазони в Excel

  • Підтвердіть.

Готово!

За допомогою поля “Ім'я”

  • Виділіть комірку (або діапазон);
  • А тепер просто впишіть ім'я у полі «Ім'я»;

Створюємо іменовані діапазони в Excel

  • Діапазон буде створено для файлу Excel (а не для окремого аркуша). Якщо вам потрібно створити діапазон лише одного аркуша, вам потрібен розглянутий варіант.

Функція «Створити із виділеного»

Цим варіантом вигідно користуватися, коли вам потрібно створити відразу кілька іменованих діапазонів. Наприклад, як у нашому випадку, ми хочемо створити три діапазони з кожного стовпчика нашої таблички.

Створюємо іменовані діапазони в Excel

Покрокова інструкція:

  • Виділіть комірку (або діапазон);
  • Клацніть на “Формули” -> “Створити з виділеного”;

Створюємо іменовані діапазони в Excel

  • У вікні, виберіть ту опцію, яка відповідає положенню заголовків у вашій табличці. Грубо кажучи, вкажіть де Excel повинен шукати заголовки таблички (у нашому випадку – “у рядку вище”);

Створюємо іменовані діапазони в Excel

Таким чином, він створив три діапазони з назвами наших стовпчиків, змінюючи прогалину на _;

Важлива інформація

Є кілька моментів, які вам потрібно знати про іменовані діапазони:

  • Першим символом імені може бути лише літера, “_” або “”. У решті випадків буде помилка;
  • Ім'ям може бути діапазон осередків;
  • А також в іменах не може бути пробілів, ви можете використовувати _;
  • Реєстр немає значення;
  • Максимальна довжина імені – 255 символів.

Пошук необхідного названого спектра

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

Що ж робити?

Давайте покажу.

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

Покрокова інструкція:

  • Клацніть на “Формули” -> “Використовувати у формулі”;

Створюємо іменовані діапазони в Excel

  • Далі – “Вставити імена …”;

Створюємо іменовані діапазони в Excel

Отже, відкрився перелік усіх діапазонів.

Створюємо іменовані діапазони в Excel

Автопідстановка імені діапазону

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

Створюємо іменовані діапазони в Excel

Редагуємо вже створений іменований діапазон

Покрокова інструкція:

  • Клацніть на “Формули” -> “Диспетчер імен”;

Створюємо іменовані діапазони в Excel

  • У вікні будуть всі створені діапазони та їх описи'

Створюємо іменовані діапазони в Excel

  • Натисніть «Змінити…»;

Створюємо іменовані діапазони в Excel

  • Поміняйте те, що потрібно;
  • Підтвердіть.

Гарячі клавіші

Які є гарячі клавіші:

  • Список усіх діапазонів – F3;
  • Відкрити «Диспетчер імен» – CTRL + F3;
  • Створити діапазон із виділеного – CTRL + SHIFT + F3.

Динамічний іменований діапазон

До цього часу ми розглянули лише ті іменовані діапазони, які змінюватимуть свою область.

Якщо ми створили діапазон B1:B20, то він завжди буде посилатися на цю область.

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

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

Створюємо іменовані діапазони в Excel

Для цього ми викликатимемо функцію ІНДЕКС.

Допустимо у вас є така табличка:

Створюємо іменовані діапазони в Excel

Покрокова інструкція:

  • Клацніть на “Формули” -> “Присвоїти ім'я”;

Створюємо іменовані діапазони в Excel

  • У вікні вкажіть дані на ваш вибір, а в полі «Діапазон» введіть — =$A$2:ІНДЕКС($A$2:$A$100,ПОЛІЧИЛИ($A$2:$A$100,»<>»&»))

Створюємо іменовані діапазони в Excel

  • Підтвердіть.

Готово!

Як це працює?

Щоб зрозуміти як ця «фішка» працює, потрібно знати, як працює функція ІНДЕКС.

Найчастіше ІНДЕКС використовують для отримання значення з будь-якої комірки.

Формула, яку ми використовували при створенні діапазону:

=$A$2:ІНДЕКС($A$2:$A$100,ПОЛІЧНІ($A$2:$A$100,”<>“&””))

ІНДЕКС($A$2:$A$100,РАХУНКИ($A$2:$A$100,»<>»&») -> видасть нам значення останнього елемента стовпчика.

Але якщо функція буде виглядати так: (=$A$2:INDEX($A$2:$A$100,COUNTIF($A$2:$A$100,»<>»&»))) вона поверне порядковий номер останнього осередку , а чи не значення.

У нашому випадку, це $A$2:$A$11.

І відповідно, якщо ми створимо нові осередки — все буде перераховано.

Зверніть увагу:

Ця конструкція спрацює тільки в тому випадку, якщо в осередках, що йдуть один за одним, не буде порожніх. Якщо вам потрібний більший діапазон у функції ІНДЕКС — вкажіть його.

Також можна використовувати функцію ЗМІЩ, але вона розраховуватиметься кожного разу, коли дані змінюються і може уповільнювати роботу з програмою.

nBook
Додати коментар