Присвоєння імен осередкам і діапазонам

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

! A1: AJ51, крім того, на відміну від позначень, за посиланнями доступні елементи таблиці в будь-якому місці будь-якої книги

Імена, визначені на поточному аркуші книги, не втрачають область видимості і на інших її аркушах Наприклад, присвоєне імя діапазону комірок на Лісте6, в тій же мірі застосовно в формулах, розташованих на Лісте1, Лісте2 і т д Але ви також можете призначати імена на рівні листа в цьому випадку вони будуть доступні тільки на тому аркуші, на якому вони визначені

ДИВИСЬ ТАКОЖ

Про імена на рівні листа див далі розділ «Імена на рівні листа в порівнянні з име-

нами на рівні книги »

РАДА

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

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

Результат застосування імені осередку або діапазону у формулі буде тим же самим, що і при введенні посилання на клітинку або діапазон Припустимо, що в комірці A3 міститься формула = А1 + А2 Якщо комірці А1 присвоїти імя Вася, а комірці А2 – імя Маша, то формула = Вася + Маша поверне те ж значення, що і формула = А1 + А2

Для того щоб присвоїти імя комірці, виконайте такі дії:

1 Виділіть клітинку

2 Клацніть у полі Імя (Name) у рядку формул, як показано на рис 1213

3 Введіть імя для комірки і натисніть клавішу Enter

Рис 1213 Самий простий і швидкий спосіб призначити імя клітинці або діапазону – це ввести його в рядку формул, в полі Імя

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

Excel:

► У полі Імя зазвичай відображається адреса виділеної комірки (діапазону) Але якщо їй вже присвоєно будь-яке імя, виводиться саме воно, а не посилання на клітинку

► Якщо імя визначено для діапазону комірок, воно не зявиться в полі Імя, поки не буде повністю виділено весь діапазон

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

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

У визначення імені обовязково включається імя листа і абсолютні посилання на клітинки Наприклад, якщо привласнити комірці С5 листа Лист1 імя Вася, фактично визначення цього імені буде все одно виглядати як Лист1 $ С $ 5

ДИВИСЬ ТАКОЖ

Детальніше про абсолютні посиланнях див раніше розділ «Відносні, абсолютні і змішані посилання»

Визначення імен та правила іменування

Замість того щоб вигадувати різноманітні імена осередкам і діапазонам, простіше використовувати вже існуючі позначення Виберіть у меню Вставка команду Імя (Name), а потім – Присвоїти (Define), виділіть потрібну комірку або діапазон, і Excel автоматично запропонує вам свій варіант, відповідно до назв заголовків таблиці, як показано на рис 1214 Ви вправі як погодитися з пропозицією, так і відмовитися від нього і призначити будь-яке інше імя

Рис 1214 Діалогове вікно Присвоєння імені

При призначенні імен осередкам і діапазонам не забувайте про наступне:

► імя має починатися з літери, зворотної косої межі (\) або символу підкреслення

(_)

► в іменах немає обмежень на числові символи

► прогалини в іменах неприпустимі

► Excel автоматично замінює всі прогалини в позначеннях діапазонів символами підкреслення

► ніякі інші знаки, крім зворотного слеша (косою риси) та підкреслення,

використовувати не можна

► остерігайтеся імен, які можуть трактуватися як посилання на комірки, наприклад АВ $ 5

або R1C7

► як імена можуть виступати будь-які літери, крім R і С

► Довжина імені обмежена 255 символами Але якщо воно буде більше, ніж 253 символу, ви не зумієте виділити його в поле Імя Зверніть увагу, програма не розрізняє в іменах регістр букв, тому уникайте перекриття імен виду Податок і ПОДАТОК

РАДА

Щоб швидко вивести на екран вікно Присвоєння імені, натисніть клавіші Ctrl + F3

Якщо вас влаштовує запропоноване імя, натисніть Enter Наступного разу, коли ви відкриєте вікно Присвоєння імені, це імя вже зявиться в списку Імя (Names In Workbook), в якому відображаються всі призначені імена поточної книги

Імя можна визначити без попереднього виділення клітинки або діапазону Для того щоб, наприклад, осередок D20 «відгукувалася на імя» Коля, наберіть його в списку Імя (Name) діалогового вікна Присвоєння імені, а в полі Формула (Refers To) вкажіть посилання на цю клітинку – = D20 Клацніть на кнопці Додати (Add), і Коліно імя потрапить в загальну скарбничку

Діалогове вікно все ще залишається відкритим, а в полі Формула зявляється посилання

= Лист D20 Excel додає посилання на лист, але зверніть увагу, що посилання на клітинку залишається відносною, такою, якою ви її ввели Якщо перед посиланням чи не поставити знак рівності, програма сприйме це визначення як текст Так, якщо замість = D20 ввести просто D20, в полі Формула як визначення імені Коля буде відображатися текстова константа = D20.

Ви вправі виділяти комірки робочого листа і при відкритому вікні Присвоєння імені Тому, замість того щоб вводити в полі Формула посилання на діапазон вручну, легше просто клацнути на цьому полі, а потім обвести потрібний діапазон комірок Якщо ви призначаєте імена декільком діапазонам, не забувайте після введення кожного нового імені натискати кнопку Додати (При натисканні OK Excel закриє діалогове вікно)

Редагування імен

Щоб змінити вміст поля Формула, в діалоговому вікні Присвоєння імені слід вибрати в списку потрібне імя комірки або діапазону, а потім відредагувати посилання в полі Формула Ви можете ввести в це поле нову посилання або виділити новий діапазон таким чином, ви створите додаткове імя, не закриваючи діалогового вікна

Щоб видалити імя зі списку, виділіть його та натисніть кнопку Видалити (Delete) Майте на увазі, що надалі будь-яка формула на аркуші, яка посилається на це імя, буде повертати значення # ІМЯ, Яке свідчить про помилку

Імена на рівні листа в порівнянні з іменами на рівні книги

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

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

Наприклад, щоб привласнити якомусь діапазону аркуша Лист1 імя Вася, що має силу тільки на поточному листі, виділіть клітинку або діапазон, далі виберіть у меню Вставка команду Імя, а потім – Присвоїти Введіть текст Лист1 Вася в полі Імя діалогового вікна Присвоєння імені

При роботі з іменами, визначеними для листа або для всієї книги, зверніть увагу на наступне:

► імена на рівні аркушів не відображаються в списку Імя та у вікні Присвоєння імені

для всіх інших листів поточної книги

► при виділенні іменованої на рівні листа осередку або діапазону відповідне імя (у нашому прикладі Вася) зявиться в полі імені в рядку формул, але частина його з назвою листа при цьому (Лист1) буде прихована Щоб, наприклад, використовувати слово Лист в якості частини тексту імені, з цієї точки зору розумно мати характерні імена листів

► щоб відредагувати імя на рівні листа, активізуйте аркуш, на якому це імя визначено, і змініть його, відкривши вікно Присвоєння імені

► коли лист містить імя на рівні листа, що збігається з імям на рівні книги,

перший має більш високий пріоритет

► відповідно, Excel не дозволяє створити імя рівня книги на аркуші, на якому ідентичне імя визначено вже на рівні листа

► все вищесказане не обмежує звернення до імені рівня листа в формулах на інших аркушах за тієї умови, що ви вкажете повну посилання на це імя Наприклад, в будь-яку клітинку аркуша ЛістЗ ви вправі ввести формулу = Лист1 Вася

Напівавтоматичне створення імен

Якщо в меню Вставка вибрати команду Имя, а потім – Створити (Create), програма запропонує спертися на текстові значення в сусідніх комірках або діапазонах Свій вибір вам належить зробити в діалоговому вікні Створити імена (Create Names), представленому на рис 1215

Рис 1215 Вікно Створити імена дозволяє привласнити імена відразу декільком осередкам або діапазонами на підставі позначень діапазонів

Наприклад, ліворуч на малюнку виділено діапазон АЗ: Е7, а в діалоговому вікні Створити імена встановлені прапорці У рядку вище (Top Row) і У стовпці зліва (Left Column) Таким чином відразу створюється два набори імен: по позначенням Кв і Регіон Зауважте, що для роботи з цим вікном вам потрібно попереднє виділення заголовків діапазонів Якщо клацнути на стрілці праворуч від поля імені в рядку формул, то в розкрився списку можна побачити всі захоплені виділенням імена (наприклад, імя Регіон_1 визначає діапазон $ В $ 4: $ Е $ 4), як показано на рис 1215, справа

Іменовані константи та формули

У Excel передбачена можливість присвоєння імен констант і формулами, навіть якщо ці константи та формули не відображаються в осередках робочого аркуша В якості імен можуть виступати абсолютні і відносні посилання, числа, текст, формули і функції Так, якщо у своїх розрахунках ви часто використовуєте значення 25% для розрахунку торгової націнки, то і назвіть його націнкою Щоб це зробити, відкрийте діалогове вікно Присвоєння імені та в полі Імя введіть слово Нацепка, а в полі Формула наберіть 25% або просто 025 Тепер ваші формули стануть легше читатися, як, наприклад, така:

= Ціна + (Ціна * Націнка) Неважко зрозуміти, що в кінцеву вартість товару ви заклали 25%-у добавку за посередницькі послуги Зверніть увагу, що іменовані константи та формули не відображаються в полі Імя в рядку формул, але виводяться в діалоговому вікні Присвоєння імені

Як імя в полі Формула можна ввести і формулу Припустимо, імя Ціна поставлено у відповідність Формулою = Лист1 А1 * 190% Тоді, якщо виділена комірка В1, ви вправі набрати запит = Ціна в цьому осередку, а програма виконає необхідні обчислення Оскільки тут використовується відносних посилання, результат виводиться за значенням у клітинці або діапазоні, розташованих безпосередньо зліва від вічка з формулою Excel автоматично оновлює і іменовану формулу, коли значення в осередках змінюються

Відносні посилання в іменованих формулах

При створенні іменованої формули, яка містить відносні посилання, такий як Лист1 В22 +12%, програма інтерпретує посилання на клітинку в полі Формула щодо позиції активної комірки Пізніше, якщо ви використовуєте це імя у формулі, посилання буде розглядатися щодо осередку, в якій знаходиться сама формула Таким чином, якщо осередок В21 була активною, коли ви визначали імя Націнка як = Лист1 В22 +12%, це імя завжди буде посилатися на клітинку, розташовану одним рядком нижче клітинки з формулою

«Тривимірні» імена

Excel дозволяє визначати імена за участю раніше вже розглянутих нами (у розділі

«Створення тривимірних формул »)« тривимірних »посилань Щоб створити таке імя,

виконайте наступні дії:

1 Виділіть комірку В5 на Листі 1

2 Виберіть у меню Вставка команду Імя, а потім – Присвоїти

3 Введіть значення Сквозное_імя в поле Імя та значення = Аркуш2: Ліст13 В5 в полі Формула

4 Натисніть клавішу Enter

Тепер присвоєне посиланням імя готове до застосування у формулі, яка містить будь-яку з перерахованих у згаданому вище розділі функцій Наприклад, формула

= МАКС (Сквозное_імя) повертає найбільше значення з діапазону з імям Сквозное_імя Так як у визначенні імені ми оперували відносними посиланнями, при виділенні на аркуші інших осередків змінюється і визначення діапазону Сквозное_імя Відповідно, якщо виділити клітинку СЗ, в полі Формула вікна Присвоєння імені ви побачите значення = Аркуш2: Ліст13 СЗ

Вставка імен у формули

Після того як на робочому листі визначено одне або кілька імен, ви можете вставляти ці імена у формули з діалогового вікна Вставка імені (Paste Name), зображеного на рис 1216

Рис 1216 Діалогове вікно Вставка імені

Наприклад, щоб додати імя Регіон_1 в формулу, потрібно виконати наступні дії:

1 Почніть введення зі знака рівності, потім введіть оператори, функції або константи створюваної формули

2 Встановіть точку вводу в тому місці формули, куди ви збираєтеся вставити імя, і ​​виберіть у меню Вставка команду Імя, а потім Вставити (або натисніть клавішу F3)

3 У списку імен виберіть Регіон_1 і натисніть кнопку ОК

4 Введіть у формулу всі інші операнди та оператори і натисніть Enter

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

Створення списку імен

Великий проект – це, як правило, і велика безліч імен, розкиданих по всій таблиці Щоб мати їх перед очима одночасно, вставте в робочий лист весь

список використовуваних імен Складнощі тут немає – просто натисніть кнопку Всі імена (Paste List) в діалоговому вікні Вставка імені Список заповнить собою деякий діапазон в напрямку вниз, починаючи з активного осередку, як показано на рис 1217

Рис 1217 Використовуйте кнопку Всі імена діалогового вікна Вставка імені для контролю своїх дій

ЗАУВАЖЕННЯ

Якщо ви ненавмисно перекрили в результаті даної операції вміст інших осередків, скасуйте команду вставки, натиснувши клавіші Ctrl + Z

Заміщення посилань іменами

Щоб замінити посилання у формулі на відповідні імена, виберіть у меню Вставка команду Імя, а потім – Застосувати (Apply) Якщо перед виконанням команди Застосувати була виділена лише одна клітинка, програма заместит всі знайдені на аркуші імена, а при виділенні діапазону комірок команда буде застосована тільки до виділених клітинок

На рис 1218 зображено діалогове вікно Застосування імен (Apply Names), в якому міститься список всіх іменованих клітинок і діапазонів За замовчуванням список виділений цілком якщо ви не хочете виробляти глобальну заміну, просто клацніть на потрібному імені, щоб зняти з нього виділення, або клацніть ще раз, щоб знову включити його в загальний список

При застосуванні імен до формул Excel зазвичай опускає імя стовпчика або рядка, якщо вони є зайвими, тобто коли осередок з формулою знаходиться в цьому ж стовпці або рядку Наприклад, таку картину ми спостерігаємо на рис 1218 Осередок J14 виділена, і в рядку формул можна прочитати формулу, що міститься в цьому осередку, = Регіон_2

Кв_3 До застосування імен в комірці зберігалася формула = D5 Так як осередок J14 не належить ні до одного з визначених діапазонів, в нову формулу включені імена і рядка і стовпчика Тепер помістимо ту ж формулу = D5 в клітинку D14 Оскільки тут обидві комірки, з формулою і впливає, знаходяться в одному стовпці, для визначення формули достатньо тільки імені рядка (так зване неявне перетин) У результаті формула для комірки D14 буде виглядати як = Регіон_2

Рис 1218 Діалогове вікно Застосування імен

Якщо ви віддаєте перевагу бачити в формулах і імена стовпців і імена рядків, навіть коли в цьому немає необхідності, зніміть прапорці Опускати імя стовпця, якщо той же стовпець (Omit Column Name If Same Column Option) і Опускати імя рядка, якщо та ж рядок (Omit Row Name If Same Row)

ДИВИСЬ ТАКОЖ

Детальніше про неявному перетині див раніше розділ «Зауваження про пересічних діа-

пазонов »

Перемикач Порядок імен (Name Order) призначений для завдання послідовності відображення імен рядка і стовпчика Наприклад, при установці перемикача в положення Стовпець, рядок (Column Row) формула в комірці J14 на рис 1218 читалася б як = Кв_3 Регіон_2

Якщо встановлено прапорець Ігнорувати тип посилання (Ignore Relative / Absolute), при заміні посилань іменами тип посилань не враховується В принципі краще, щоб цей прапорець завжди був активний Більшість визначень імен містять абсолютні посилання (саме вони використовуються за замовчуванням при створенні імен) У той же час більшість формул включають в себе відносні посилання (даний тип посилань застосовується неявно при вставці в рядок формул посилань на клітинку або діапазон) Скиньте цей прапорець, якщо хочете підставити замість абсолютних, відносних і змішаних посилань тільки ті імена, у визначенні яких задіюється відповідний тип посилань

Прапорець Використовувати імена рядків і стовпців (Use Row And Column Names) слід встановити, коли ви збираєтеся застосовувати імена у формулах, в яких присутня оператор перетину, як в розглянутому раніше прикладі Якщо ви призначили імена окремим осередкам листа, даний прапорець можна скинути, тоді у формулах

використовуватимуться не імена рядків і стовпців, а задані імена діапазонів

Перехід до заданого діапазону

Якщо в меню Правка вибрати команду Перейти (Go To) або натиснути клавішу F5, на екрані зявиться діалогове вікно Перехід (Go To), зображене на рис 1219 Щоб перейти до одного з цих діапазонів, просто виділіть у списку потрібне імя та натисніть кнопку ОК Зверніть увагу, що у вікні не відображаються іменовані константи та формули

Рис 1219 Діалогове вікно Перехід

РАДА

• Програма запамятовує чотири останні точки переходу Найнедавніше місце відображається в полі Посилання (Reference) при відкритті цього діалогового вікна Для швидкого переміщення між поточною позицією і останнім місцем відвідування використовуйте клавіші F5 і Enter

Джерело: Ефективна робота: Microsoft Office Excel 2003 / М Додж, К Стінсон – СПб: Питер, 2005 – 1088 с: ил

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*