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

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

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

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

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

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

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

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

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

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

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

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

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

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

Тип прикладу

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

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

Посилання

=SUM(C20:C30)

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

Константа

=PRODUCT(A5;8,3)

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

Формула

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

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

Таблиця

C4:G36

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

Типи імен

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

Визначене ім’я.    Це ім’я, яке позначає клітинку, діапазон клітинок, формулу або константу. Одні визначені імена ви створюєте самостійно, а інші Microsoft Office Excel генерує автоматично (наприклад, коли ви визначаєте область друку).

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

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

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

  • Якщо, наприклад, визначено ім’я Бюджет_08ФР, а його область – Аркуш1, воно без уточнення розпізнаватиметься лише на аркуші Аркуш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 символів.

    Примітка. Якщо книга зберігатиметься на Microsoft Office SharePoint Server 2007 (Excel Services) і ви вказали один або кілька параметрів, примітка використовуватиметься як екранна підказка в області завдань Параметри.

  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.

Примітка. Якщо книга зберігатиметься на Microsoft Office SharePoint Server 2007 (Excel Services) і ви вказали один або кілька параметрів, примітка використовуватиметься як екранна підказка в області завдань Параметри.

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

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

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

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

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

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

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

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

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

Виберіть:

Щоб:

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

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

Імена в книзі

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

×