Робота з іменованих діапазоном в Microsoft Excel

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

Маніпуляції з іменованими областями

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

Існують обов'язкові вимоги до найменування групи осередків:

• У ньому не повинно бути пробілів;
• Воно обов'язково повинно починатися з літери;
• Його довжина не повинна бути більше 255 символів;
• Воно не повинно бути представлено координатами виду A1 або R1C1;
• У книзі не повинно бути однакових імен.

Найменування області осередків можна побачити при її виділенні в полі імен, яке розміщене зліва від рядка формул.

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

Створення іменованого діапазону

Перш за все, дізнаємося, як створити іменований діапазон в Ексель.

1. Найшвидший і простий варіант присвоєння назви масиву - це записати його в поле імен після виділення відповідної області. Отже, виділяємо масив і вводимо в поле стару назву вважаємо за потрібне. Бажано, щоб воно легко запам'ятовувалося і відповідало вмісту осередків. І, безумовно, необхідно, щоб воно відповідало обов'язковим вимогам, які були викладені вище.
2. Для того, щоб програма внесла ця назва в власний реєстр і запам'ятала його, тиснемо по клавіші Enter. Назва буде присвоєно виділеної області осередків.

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

1. Виділяємо масив, над яким потрібно виконати операцію. Клацають по виділенню правою кнопкою миші. У списку, зупиняємо вибір на варіанті «Присвоїти ім'я ...».
2. Відкривається віконце створення назви. В область «Ім'я» слід вбити найменування відповідно до озвученими вище умовами. В області «Діапазон» відображається адреса виділеного масиву. Якщо ви провели виділення вірно, то вносити зміни в цю область не потрібно. Тиснемо на кнопку «OK».
3. Як можна бачити в поле імен, назва області присвоєно успішно.

Ще один варіант виконання зазначеного завдання передбачає використання інструментів на стрічці.

1. Виділяємо область осередків, яку Ви бажаєте перевести в іменовану. Пересуваємося у вкладку «Формули». У групі «Певні імена» виробляємо клік по значку «Присвоїти ім'я».
2. Відкривається точно таке ж вікно присвоєння назви, як і при використанні попереднього варіанту. Всі подальші операції виконуються абсолютно аналогічно.

Останній варіант присвоєння назви області осередків, який ми розглянемо, це використання Диспетчера імен.

1. Виділяємо масив. На вкладці «Формули», клацають по великому значку «Диспетчер імен», розташованому все в тій же групі «Певні імена». Або ж можна замість цього застосувати натискання клавіш Ctrl + F3.
2. Активується вікно Диспетчера імен. У ньому слід натиснути на кнопку «Створити ...» у верхньому лівому кутку.
3. Потім запускається вже знайоме вікно створення файлів, де потрібно провести ті маніпуляції, про які йшла розмова вище. Те ім'я, яке буде присвоєно масиву, відобразиться в диспетчері. Його можна буде закрити, натиснувши на стандартну кнопку закриття в правому верхньому куті.

урок: Як присвоїти назву осередку в Ексель

Операції з іменованими діапазонами

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

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

1. Перш за все, на аркуші зі списком присвоюємо діапазону найменування будь-яким з тих способів, про які йшла мова вище. У підсумку, при виділенні переліку в полі імен у нас повинно відображатися найменування даного масиву. Нехай це буде найменування «Моделі».
2. Після цього переміщаємося на лист, де знаходиться таблиця, в якій нам належить створити список, що випадає. Виділяємо область в таблиці, в яку плануємо впровадити, що випадає. Переміщаємося у вкладку «Дані» і клацаємо по кнопці «Перевірка даних» в блоці інструментів «Робота з даними» на стрічці.
3. У запустити вікні перевірки даних переходимо у вкладку «Параметри». В поле «Тип даних» вибираємо значення «Список». В поле «Джерело» в звичайному випадку потрібно або вручну вписати всі елементи майбутнього списку, або дати посилання на їх перелік, якщо він розташований в документі. Це не дуже зручно, особливо, якщо перелік розташовується на іншому аркуші. Але в нашому випадку все набагато простіше, так як ми відповідному масиву присвоїли найменування. Тому просто ставимо знак «дорівнює» і записуємо це назва в поле. Виходить такий вираз:

   =Модели

   Тиснемо по «OK».

4. Тепер при наведенні курсору на будь-яку клітинку діапазону, до якої ми застосували перевірку даних, праворуч від неї з'являється трикутник. При натисканні на цей трикутник відкривається список даних, що вводяться, який підтягується з переліку на іншому аркуші.
5. Нам просто залишається вибрати потрібний варіант, щоб значення зі списку відобразилося в вибраній комірці таблиці.

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

Отже, ми маємо таблицю, в якій помісячно розписана виручка п'яти філій підприємства. Нам потрібно дізнатися загальну виручку за Філії 1, Філії 3 і Філії 5 за весь період, зазначений у таблиці.

1. Перш за все, кожному рядку відповідної філії в таблиці дамо назву. Для Філії 1 виділяємо область з осередками, в яких містяться дані про виручку за нього за 3 місяці. Після виділення в поле імен пишемо найменування «Філіал_1» (не забуваємо, що назва не може містити пробіл) і клацаємо по клавіші Enter. Найменування відповідної області буде присвоєно. При бажанні можна використовувати будь-який інший варіант присвоєння найменування, про який йшла мова вище.
2. Таким же чином, виділяючи відповідні області, даємо назви рядків і інших філій: «Філіал_2», «Філіал_3», «Філіал_4», «Філіал_5».
3. Виділяємо елемент листа, в який буде виводитися підсумок підсумовування. Клацають по іконці «Вставити функцію».
4. Ініціюється запуск Майстра функцій. Виробляємо переміщення в блок «Математичні». Зупиняємо вибір з переліку доступних операторів на найменуванні «СУМ».
5. Відбувається активація віконця аргументів оператора СУМ. Ця функція, що входить в групу математичних операторів, спеціально призначена для підсумовування числових значень. Синтаксис представлений наступною формулою:

   =СУММ(число1;число2;…)

   Як неважко зрозуміти, оператор підсумовує всі аргументи групи «Число». У вигляді аргументів можуть застосовуватися, як безпосередньо самі числові значення, так і посилання на комірки або діапазони, де вони розташовані. У разі застосування масивів в якості аргументів використовується сума значень, яка міститься в їх елементах, підрахована в фоновому режимі. Можна сказати, що ми «перескакуємо», через дію. Саме для вирішення нашої задачі і буде використовуватися підсумовування діапазонів.

   Всього оператор СУМ може налічувати від одного до 255 аргументів. Але в нашому випадку знадобиться всього три аргументи, так як ми буде виробляти складання трьох діапазонів: «Філіал_1», «Філіал_3» і «Філіал_5».

   Отже, встановлюємо курсор в поле «Число1». Так як ми дали назви діапазонів, які потрібно скласти, то не потрібно ні вписувати координати в поле, ні виділяти відповідні області на аркуші. Досить просто вказати назву масиву, який підлягає додаванню: «Філіал_1». У поля «Число2» і «Чісло3» відповідно вносимо запис «Філіал_3» і «Філіал_5». Після того, як вищевказані маніпуляції були зроблені, клацають по «OK».

6. Результат обчислення виведений в клітинку, яка була виділена перед переходом в Майстер функцій.

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

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

урок: Як порахувати суму в Майкрософт Ексель

Управління іменованими діапазонами

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

1. Щоб перейти в Диспетчер, переміщаємося у вкладку «Формули». Там слід клікнути по іконці, яка так і називається «Диспетчер імен». Зазначена іконка розташовується в групі «Певні імена».
2. Після переходу в Диспетчер для того, щоб зробити необхідну маніпуляцію з діапазоном, потрібно знайти його назву в списку. Якщо перелік елементів не дуже великий, то зробити це досить просто. Але якщо в поточній книзі розташовується кілька десятків іменованих масивів або більше, то для полегшення завдання є сенс скористатися фільтром. Клацають по кнопці «Фільтр», розміщеної в правому верхньому куті вікна. Фільтрацію можна виконувати за наступними напрямками, вибравши відповідний пункт меню,:
   • Імена на аркуші;
   • У книзі;
   • з помилками;
   • без помилок;
   • Певні імена;
   • Імена таблиць.

   Для того, щоб повернуться до повного переліку найменувань, досить вибрати варіант «Очистити фільтр».

3. Для зміни кордонів, назви або інших властивостей іменованого діапазону слід перейти до елементу в диспетчері і натиснути на кнопку «Змінити ...».
4. Відкривається вікно зміна назви. Воно містить у собі точно такі ж поля, що і вікно створення іменованого діапазону, про який ми говорили раніше. Тільки на цей раз поля будуть заповнені даними.

   В поле «Ім'я» можна змінити найменування області. В поле «Примітка» можна додати або відредагувати існуюче примітка. В поле «Діапазон» можна поміняти адресу іменованого масиву. Існує можливість зробити, як застосувавши ручне введення необхідних координат, так і встановивши курсор в поле і виділивши відповідний масив осередків на аркуші. Його адреса тут же відобразиться в поле. Єдине поле, значення в якому неможливо відредагувати - «Область».

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

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

Це дуже важливо, тому що якщо ви вже застосовували видаляється найменування в якийсь формулою, то після видалення назви дана формула стане помилковою.

1. Щоб провести процедуру видалення, виділяємо потрібний елемент зі списку і тиснемо на кнопку «Видалити».
2. Після цього запускається діалогове вікно, яке просить підтвердити свою рішучість вилучити вибрані елементи. Це зроблено, щоб уникнути того, щоб користувач помилково не виконав дану процедуру. Отже, якщо ви впевнені в необхідності видалення, то потрібно клацнути по кнопці «OK» у віконці підтвердження. У зворотному випадку тисніть на кнопку «Скасування».
3. Як бачимо, обраний елемент був вилучений з переліку Диспетчера. Це означає, що масив, до якого він був прикріплений, втратив найменування. Тепер він буде ідентифікуватися тільки за координатами. Після того, як всі маніпуляції в диспетчері завершені, клацають по кнопці «Закрити», щоб завершити роботу в вікні.

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

• ← Створення посилань в Microsoft Excel
• Обчислення різниці в Microsoft Excel →