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

Зміст

• Маніпуляції з названими областями
  • Створення іменованого діапазону
  • Операції з іменованими діапазонами
  • Управління іменованими діапазонами
• Питання та відповіді

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

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

Іменований діапазон &8212; це область комірок, якій користувач має певну назву. При цьому дане найменування розцінюється 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. Відбувається активація віконця аргументів оператора сума . Ця функція, що входить до групи математичних операторів, спеціально призначена для підсумовування числових значень. Синтаксис представлений наступною формулою:

   =sum (число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. Самими іменованими елементами можна управляти (змінювати і видаляти) за допомогою спеціального вбудованого Диспетчера .