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

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

Тож почнемо!

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

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

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

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

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

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

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

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

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

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

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

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

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

=СУМ(SALES)

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

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

• Сума продажів продавця Tom: = Сумісність (SalesRep, «Tom», Sales);

• Чистий прибуток продавця Joe:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Готово!

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

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

• А тепер просто впишіть ім'я у полі «Ім'я»;

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

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

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

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

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

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

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

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

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

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

• Першим символом імені може бути лише літера, “_” або “”. У решті випадків буде помилка;

• Ім'ям може бути діапазон осередків;

• А також в іменах не може бути пробілів, ви можете використовувати _;

• Реєстр немає значення;

• Максимальна довжина імені – 255 символів.

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

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

Що ж робити?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Список усіх діапазонів – F3;

• Відкрити «Диспетчер імен» – CTRL + F3;

• Створити діапазон із виділеного – CTRL + SHIFT + F3.

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

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

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

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

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

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

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

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

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

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

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

Готово!

Як це працює?

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

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

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

=$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.

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

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

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

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