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

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

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

У цьому розділі

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

Правила синтаксису для імен

Визначення імені для клітинки або діапазону клітинок на аркуші

Визначення імені для виділених клітинок на аркуші

Визначення імені за допомогою діалогового вікна "Нове ім’я"

Керування іменами за допомогою діалогового вікна "Диспетчер імен"

Змінення імені

Видалення одного або кількох імен

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

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

Тип прикладу

Приклад без імені

Приклад з іменем

Посилання

=SUM(C20:C30)

=SUM(ПродажПершийКвартал)

Константа

=PRODUCT(A5;8,3)

=PRODUCT(Ціна;ПодатокІзПродажуКиїв)

Формула

=SUM(VLOOKUP(A1;B1:F20;5;ХИБНІСТЬ); -G5)

=SUM(Рівень_резерву;-Сума_замовл)

Таблиця

C4:G36

=НайбільшийПродаж06

Типи імен

Існує кілька типів імен, які можна створити й використовувати.

Визначене ім’я.    Ім’я, яке позначає клітинку або їх діапазон, формулу чи константу. Ви можете самостійно створити визначене ім’я, або це може зробити Excel (наприклад, коли задається область друку).

Ім’я таблиці.    Ім’я для таблиці Excel, що являє собою сукупність даних про певний предмет, які зберігаються в записах (рядках) і полях (стовпцях). Щоразу, коли ви додаєте таблицю, Excel створює стандартне ім’я (Таблиця1, Таблиця2 тощо), яке можна замінити на зрозуміліший варіант. Докладні відомості про таблиці Excel див. в статті Використання структурованих посилань із таблицями Excel.

Область імені

Усі імена мають область застосування на вказаному аркуші (на локальному рівні) або в усій книзі (на глобальному рівні). Область імені – це розташування, де воно розпізнається без уточнення. Розглянемо кілька прикладів.

  • Якщо ви визначили ім’я, наприклад "Бюджет_08ФР", область якого – "Аркуш1", воно без уточнення розпізнаватиметься лише на першому аркуші.

    Щоб скористатися локальним іменем з одного аркуша на іншому, його можна уточнити, указавши перед ним ім’я аркуша. Наприклад:

    Аркуш1!Бюжет_08ФР

  • Якщо ви визначили ім’я, наприклад "Цілі_відділу_збуту", область якого – робоча книга, воно розпізнаватиметься на всіх аркушах, але тільки в цій книзі.

В Excel не можна визначати імена, які вже існують у межах області, але одне й те саме ім’я можна використовувати в різних областях. Наприклад, ім’я "Валовий_прибуток" можна визначити в областях "Аркуш1", "Аркуш2" та "Аркуш3" в тій самій книзі, де воно буде унікальним. Це дає змогу зробити так, щоб формула, яка використовує ім’я "Валовий_прибуток", завжди посилалася на одну й ту ж клітинку на локальному рівні аркуша.

Те саме ім’я ("Валовий_прибуток") можна визначити навіть на глобальному рівні робочої книги, але знов-таки за умови, що воно залишатиметься унікальним у межах області. У такому разі може статися конфлікт імен. Щоб його вирішити, Excel за замовчуванням використовує ім’я, визначене для аркуша, оскільки локальний рівень аркуша має пріоритет над глобальним рівнем робочої книги. Якщо потрібно змінити цю послідовність і скористатись іменем на рівні книги, проблему двозначності можна вирішити, додавши ім’я книги як префікс. Наприклад:

Файл_книги!Валовий_прибуток

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

Визначення та введення імен

Визначати імена можна такими способами:

  • Скористатися полем "Ім’я" в рядку формул.    Це найзручніший спосіб створити ім’я на рівні робочої книги для вибраного діапазону.

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

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

Примітка. За замовчуванням в іменах використовуються абсолютні посилання на клітинки.

Вводити імена можна такими способами:

  • Вводити на клавіатурі.    Вводити ім’я на клавіатурі, наприклад як аргумент у формулі.

  • Використати автозаповнення формул.    Скористатися розкривним списком автозаповнення формул, у якому автоматично наводяться припустимі імена.

  • Вибір із команди "Використовувати у формулі"   . Вибір визначеного імені в списку, доступному з команди Використовувати у формулі в групі Визначені імена на вкладці Формули.

Аудит імен

Ви також можете створити список визначених імен у книзі. Знайдіть область із двома пустими стовпцями на аркуші (список міститиме два стовпці – один для імені, а другий для його опису). Виберіть клітинку, розташовану у верхньому лівому куті списку. На вкладці Формули в групі Визначені імена натисніть кнопку Використовувати у формулі, клацніть Вставити імена, а потім у діалоговому вікні Вставлення імені виберіть Усі імена.

На початок сторінки

Правила синтаксису для імен

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

  • Припустимі символи   . Перший символ імені має бути буквою, символом підкреслення (_) або зворотною скісною рискою (\). Усі інші символи імені можуть бути буквами, цифрами, крапками та символами підкреслення.

    Порада. Не вказуйте великі й малі букви C, c, R або r як визначене ім’я, оскільки вони застосовуються як короткі умовні позначення для вибору рядка або стовпця поточної клітинки, коли ви вводите їх у текстові поля Ім’я або Перейти до.

  • Заборонено посилання на клітинки   . Імена не можуть бути ідентичні посиланням на клітинки, таким як "Z$100" або "R1C1".

  • Пробіли неприпустимі.    В іменах не можна використовувати пробіли. Натомість, щоб відокремити слово, потрібно використовувати символ підкреслення (_) і крапку (.), як у прикладі Податок_на_додану_вартість або Перший.квартал.

  • Довжина імені   . Ім’я може містити до 255 символів.

  • Регістр.    Імена можуть містити як великі, так і малі букви. Excel не розрізняє регістр в іменах. Наприклад, якщо в одній книзі спочатку створити ім’я "Збут", а потім – інше ім’я "ЗБУТ" у тій самій книзі, Excel запропонує вибрати унікальне ім’я.

На початок сторінки

Визначення імені для клітинки або діапазону клітинок на аркуші

  1. Виділіть клітинку, діапазон клітинок або несуміжні елементи, яким потрібно призначити ім’я.

  2. У лівій частині рядка формул клацніть поле Ім’я.

    Поле імені

    Поле імені

  3. Введіть ім’я, за яким посилатиметеся на вибраний фрагмент. Воно може містити до 255 символів.

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

Примітка. Клітинці не можна призначати ім’я, коли змінюється її вміст.

На початок сторінки

Визначення імені для виділених клітинок на аркуші

Наявні підписи рядків і стовпців можна перетворити на імена.

  1. Виберіть діапазон, якому потрібно присвоїти ім’я, разом із підписами рядків або стовпців.

  2. На вкладці Формули в групі Визначені імена натисніть кнопку Створити з виділеного.

    Зображення стрічки у програмі Excel

  3. У діалоговому вікні Створення імен з виділеного фрагмента призначте розташування, яке містить підписи, установивши прапорець у рядку вище, у стовпці зліва, у рядку нижче або у стовпці справа. Ім’я, створене таким чином, посилається лише на клітинки, які містять значення, і не включає наявні підписи рядків і стовпців.

На початок сторінки

Визначення імені за допомогою діалогового вікна "Нове ім’я"

  1. На вкладці Формули у групі Визначені імена натисніть кнопку Визначити ім’я.

    Зображення стрічки у програмі Excel

  2. У діалоговому вікні Нове ім’я в поле Ім’я введіть ім’я, яке використовуватиметься для посилань.

    Примітка. Ім’я може містити до 255 символів.

  3. Щоб указати область імені, у розкривному списку Область виберіть пункт Робоча книга або ім’я аркуша у книзі.

  4. За потреби введіть у полі Примітка описову примітку довжиною до 255 символів.

  5. У полі Посилання виконайте одну з наведених нижче дій.

    • Щоб указати посилання на клітинку, введіть це посилання.

      Порада. Поточне виділення введено за замовчуванням. Щоб ввести інші посилання на клітинки як аргумент, натисніть кнопку Згорнути діалогове вікно Зображення кнопки (діалогове вікно буде тимчасово згорнуто), виберіть клітинки на аркуші й натисніть кнопку Розгорнути діалогове вікно Зображення кнопки .

    • Щоб ввести константу, введіть = (знак рівності), а потім значення константи.

    • Щоб ввести формулу, введіть =, а потім формулу.

  6. Щоб завершити й повернутися до аркуша, натисніть кнопку ОК.

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

На початок сторінки

Керування іменами за допомогою діалогового вікна "Диспетчер імен"

За допомогою діалогового вікна Диспетчер імен можна працювати з усіма визначеними іменами та іменами таблиць у книзі. Наприклад, може знадобитися знайти імена з помилками, підтвердити значення та посилання імені, переглянути або змінити описові примітки чи визначити область. Крім того, у цьому вікні ви можете сортувати й фільтрувати список імен, а також легко додавати, змінювати й видаляти імена.

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

Зображення стрічки у програмі Excel

Перегляд імен

У діалоговому вікні Диспетчер імен відображено наведені нижче відомості про кожне ім’я у списку.

Стовпець

Вміст

Піктограма та ім’я

Можливі такі варіанти:

  • Визначене ім’я, позначене піктограмою визначеного імені. Піктограма визначеного імені

  • Ім’я таблиці, позначене піктограмою імені таблиці. піктограма імені таблиці

Значення

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

  • "це моя рядкова константа"

  • 3,1459

  • {2003;12,2002;23,;2001,18}

  • #REF!

  • {...}

Посилання

Поточне посилання для імені. Нижче наведено приклади.

  • =Аркуш1!$A$3

  • =8,3

  • =НR!$A$1:$Z$345

  • =SUM(Аркуш1!A1;Аркуш2!B2)

Область

  • Ім’я аркуша, якщо областю є локальний рівень аркуша.

  • "Робоча книга", якщо область – глобальний рівень книги.

Примітка

Додаткові відомості про ім’я можуть містити до 255 символів. Нижче наведено приклади.

  • Це значення дійсне до 2 травня 2007 року.

  • Не видаляти! Критичне ім’я!

  • Базується на показниках сертифікаційного іспиту ISO.

  • Діалогове вікно Диспетчер імен не можна використовувати, коли ви змінюєте вміст клітинки.

  • У діалоговому вікні Диспетчер імен не відображаються імена, визначені у Visual Basic for Applications, так само, як і приховані імена (для властивості імені Visible встановлено значення False).

Змінення розміру стовпців

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

Сортування імен

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

Фільтрування імен

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

Щоб відфільтрувати список імен, виконайте одну або кілька з наведених нижче дій.

Виберіть:

Щоб:

Імена на аркуші

відображати тільки імена, локальні для цього аркуша.

Імена в книзі

відображати імена, глобальні для цієї книги.

Імена з помилками

відображати тільки імена, значення яких містять помилки (наприклад #REF, #VALUE або #NAME).

Імена без помилок

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

Визначені імена

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

Імена таблиць

відображати тільки імена таблиць.

На початок сторінки

Змінення імені

Якщо змінити визначене ім’я або ім’я таблиці, зміняться всі його примірники в книзі.

  1. На вкладці Формули у групі Визначені імена натисніть кнопку Диспетчер імен.

    Зображення стрічки у програмі Excel

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

    Порада. Можна також просто двічі клацнути ім’я.

  3. У діалоговому вікні Редагування імені введіть нове ім’я в полі Ім’я.

  4. Змініть посилання в полі Посилання і натисніть кнопку ОК.

  5. У діалоговому вікні Диспетчер імен у полі Посилання змініть клітинку, формулу або константу, які представлено іменем.

    • Щоб скасувати небажані або випадкові зміни, натисніть кнопку Скасувати  Кнопка ''Скасувати'' чи клавішу Esc.

    • Щоб зберегти зміни, натисніть кнопку Зберегти  кнопка Enter або клавішу Enter.

Кнопка Закрити лише закриває діалогове вікно Диспетчер імен. Натискати її, щоб затвердити вже внесені зміни, не обов’язково.

На початок сторінки

Видалення одного або кількох імен

  1. На вкладці Формули у групі Визначені імена натисніть кнопку Диспетчер імен.

    Зображення стрічки у програмі Excel

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

  3. Виберіть одне або кілька імен, виконавши одну з наведених нижче дій.

    • Щоб вибрати ім’я, клацніть його.

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

    • Щоб вибрати кілька довільно розташованих імен, слід клацнути кожне з них, утримуючи клавішу Ctrl.

  4. Натисніть кнопку Видалити або клавішу Delete.

  5. Натисніть кнопку ОК для підтвердження видалення.

Кнопка Закрити лише закриває діалогове вікно Диспетчер імен. Натискати її, щоб затвердити вже внесені зміни, не обов’язково.

На початок сторінки

Удосконалення навичок роботи з Office
Ознайомтеся з навчальними матеріалами
Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×