Excel 2007: іменовані діапазони
 | Сьогодні ми продовжуємо тему роботи з формулами — головним інтелектуальним наповненням електронної таблиці. Від формул залежить практично все: і корисність документа, і зручність його використання. Одна неправильно складена формула здатна перекреслити всі зусилля щодо розроблення бухгалтерського звіту, перекрутити результати довгої послідовності обчислень. Створювати і редагувати формули ми вже навчилися. Тепер час зосередити зусилля на роботі зі складними формулами. Перш за все ми мусимо з’ясувати ключові проблеми при їх створенні, окреслити коло інструментів Excel 2007 для вирішення цих проблем та послідовно ознайомитися з кожним із цих інструментів на практичному прикладі. А почнемо з найважливішого питання — способів адресації комірок на базі іменованих діапазонів. Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства |
Помилково вважати, що в бухгалтерських розрахунках складні формули не потрібні, що достатньо навчитися підсумовувати комірки, перемножувати колонки — і всі завдання бухобліку буде вирішено. Формули не лише виконують конкретні арифметичні дії. Формули — це насамперед засіб для реалізації алгоритму вирішення завдання. А цей алгоритм може бути будь-яким: від примітивного перемножування таблиць до розрахунків з багатьма перевірками, з аналізом декількох варіантів обчислень тощо. З мого досвіду стверджуватиму: практично в будь-якій таблиці можна (і потрібно!) використовувати формули для контролю значень, що вводяться, для перевірки проміжних результатів, для аналізу обчислень та виведення попереджень про можливі помилки. Для складання таких формул можуть знадобитися і вбудовані функції, і логічні операції, і робота з масивами комірок. Боятися складних формул не варто. Скласти будь-яку формулу в Excel 2007 не викличе труднощів. Потрібно лише розібратися в деяких моментах.
О
сновні проблеми при роботі з формуламиЯ б окреслив три проблеми, що виникають при створенні формул. Особливо якщо йдеться про будь-який, хоч трохи просунутий вираз.
1. Робота з адресами. Більшість помилок в електронних таблицях пов’язана з неправильним використанням адрес. Це може бути помилкове посилання, діапазон комірок або некоректне застосування абсолютної та відносної адресації. У питанні роботи з адресами є й інша проблема. Довгу формулу з багатьма посиланнями важко читати й аналізувати. Особливо якщо ці посилання вказують на різні листи робочої книги. Кожного разу, зустрівши вираз «
=Лист1!A1*1,2-Лист2!A1*Лист2!B1+Лист3!A1», вам доведеться згадувати, які дані записано до комірки «A1» на листі «Лист1», а також що знаходиться в комірці «B1» листа «Лист2». Способом вирішення більшості проблем з адресами є використання іменованих діапазонів.2. Використання вбудованих функцій. Вважаю, тут доречніше говорити про недостатнє їх застосування. Excel 2007 пропонує своїм користувачам кілька сотень вбудованих функцій. Багато які з них дуже корисні в роботі бухгалтера. На жаль, у багатьох випадках далі функції підсумовування справа не йде. І це — дійсно проблема! Є безліч бухгалтерських завдань, які у принципі не можна вирішити без вбудованих функцій.
Як вихід із ситуації Excel пропонує чудовий інструмент — будівник формульних виразів, або Майстер функцій. Його застосування максимально спрощує роботу з функціями та мінімізує можливі помилки при їх написанні.3. Контроль за проміжними обчисленнями. Як тільки формула готова, відразу виникає запитання: а чи правильно вона працює? Як тільки в довгій формулі виникла помилка, відразу постає проблема: де саме, в якій частині виразу це сталося?
Упоратися з цією ситуацією можна двома способами. Перший — застосувати прийом формування формули частинами. Другий — використовувати вікно контрольного значення.Основні проблеми при роботі з формулами ми окреслили. Вважаю, план дій на найближчий час зрозумілий. І першим у нашому списку стоїть проблема
адресації. Щоб ознайомитися з одним зі способів її вирішення, ми візьмемо за приклад документ прайс-лист із попередньої статті.Нагадаю, що цей документ складається з двох таблиць. Основну таблицю розташовано на листі «
Прайс», її форму показано на рис. 1. У цій таблиці вихідними даними є гривневі ціни (колонка «С»). Ціни у валюті перераховано з урахуванням поточних курсів. Їх значення записано в допоміжній таблиці на листі «Спр» (рис. 2, комірки «B4», «B5»). Дрібнооптова, оптова та спеціальна ціни визначаються з урахуванням знижок, які також розміщено на листі «Спр» (рис. 2, комірки «B7», «B8», «B9»). Крім того, у робочій таблиці «Спр» є дані про поточну дату (комірка «А1») і дату коригування курсів валют (комірка «А2»). Минулого разу ми побудували прайс, комбінуючи в розрахункових формулах абсолютну та відносну адресацію. Наприклад, вираз для розрахунку спеціальної ціни товару «Блокнот» (комірка «H2» на листі «Прайс») мала такий вигляд: «=C2*(1-Спр!$B$9)». Аналізувати навіть таку просту формулу вже стає незручно. Набагато простіше було б прочитати запис «=C2*(1-СкидкаОпт)». 
Або такий приклад. На листі «
Доход» у комірці «D50» записано суму доходу. На листі «Расход» у комірці «D175» знаходиться значення витрати. Тоді формула для розрахунку прибутку має такий вигляд: «=Доход!D50-Расход!D175». Набагато приємніше було б перетворити формулу до вигляду «=Доход-Расход». Читати й розуміти її буде набагато простіше. А досягти цього дуже просто: потрібно присвоїти елементам таблиці імена.
Робота з іменованими діапазонами
Людині властиво пам’ятати не знеособлені координати, а їх зрозумілі
назви. Як ви поставитеся, наприклад, до покупця, який запитає: «Яка оптова ціна на 27-му позицію у вашому прайсі?». Так само і в електронній таблиці. Присвоївши коміркам та блокам мнемонічні* імена, можна суттєво спростити сприйняття формул. Використання іменованих комірок та діапазонів має багато переваг.* Мнемоніка, мнемотехніка (грец. mneme — пам’ять) — сукупність спеціальних прийомів та способів, що полегшують запам’ятовування потрібної інформації та збільшують обсяг пам’яті шляхом утворення асоціацій (зв’язків).
1. Імена дозволяють легше зрозуміти значення формули. Це особливо важливо для тих, хто не брав участі у створенні таблиці, але змушений коригувати її.
2. Імена комірок та діапазонів запам’ятовуються легше, ніж їх фізичні адреси на робочому листі.
3. Імовірність друкарської помилки при написанні імен менша, ніж при введенні адреси комірки чи діапазону.
4. Використовуючи імена, можна швидко переміщатися по робочому листу. Для цього потрібно звернутися до інструменту «
Перейти…» з групи «Найти и выделить». Наприклад, натискуємо на комбінацію клавіш «Ctrl+G» (або на клавішу «F5 ») — з’явиться список імен, клацаємо по потрібному імені — і Excel миттєво встановить поточну комірку на зазначену адресу.5. При виділенні іменованої комірки чи діапазону її ім’я з’являється в полі «
Имя ». Так можна ще раз проконтролювати правильність адресації, а отже, помилок буде менше.6. Одного разу визначивши ім’я, його легко впровадити у формулу, натиснувши на «F3». Це дуже зручний спосіб зазначати адреси посилань на комірки незалежно від того, на якому листі вони розташовані.
Для роботи з іменами в Excel 2007 є спеціальний розділ основного меню — «
Определенные имена». У цьому розділі будь-якій комірці чи блоку комірок можна присвоїти ім’я, яке згодом використовувати як посилання.Імена діапазонів мають починатися з літери чи символу «
_» (підкреслення). Ім’я комірки не може збігатися за написанням з адресою посилання на комірку (чи на блок комірок). Наприклад, ім’я «A1» неприпустиме, оскільки воно збігається з адресою комірки «A1».В іменах не можна використовувати пробіли
. Якщо ім’я складається з декількох слів, я замість пробілу ставлю символ «_» (підкреслення). Ось кілька прикладів правильних імен: «Ставка_За_Кредит», «Сумма_Налога», «База_НДС».За умовчанням імена є абсолютними посиланнями.
Порада
Створюючи імена, намагайтеся робити їх максимально зрозумілими. В імені «абв» не більше змісту, ніж у звичайному посиланні на комірку. Ім’я має чітко відображати значення даних у відповідній комірці. Водночас ім’я має бути коротким. Ці суперечливі вимоги іноді важко сумістити. Тут може виручити такий прийом: у довгих назвах використовуйте скорочення. Створюючи скорочення, пам’ятайте, що приголосні літери несуть основну інформацію, їх потрібно залишати. Голосні можна опустити. Одне з можливих правил скорочення — скомбінувати основу з чотирьох перших і трьох останніх приголосних довгої назви і лише тоді доповнити скорочення іншими символами. Наприклад, словосполучення «Ставка Налога» за цим правилом можна написати так: «СтвкНлг». Відштовхуючись від цієї основи, можна отримати: «СтвкНал», «СтНал» тощо. По-моєму, цілком прийнятно.
СТВОРЕННЯ ІМЕНОВАНИХ ДІАПАЗОНІВ
Роботу з іменованими діапазонами почнемо з листа «
Спр» (рис. 2). Наше завдання — присвоїти назви коміркам з курсами валют і значеннями знижок. Викликаємо Excel 2007, завантажуємо файл із прайсом, переходимо на лист «Спр». Клацаємо по розділу «Формулы» головного меню. З’явиться стрічка з п’яти груп іконок: «Библиотека функций», «Определенные имена», «Зависимости формул», «Вычисление», «Решения». Нам потрібна група «Определенные имена» (рис. 3). 
Порада
Швидко потрапити до розділу меню «Формулы» можна за допомогою комбінації клавіш «Alt+E». Натискувати на клавіші потрібно на англійському регістрі.Роботу почнемо з Диспетчера імен.
1. Робимо активною комірку «B4» — у ній знаходиться значення курсу долара.
2. Клацаємо по іконці «
Диспетчер имен». З’явиться вікно, показане на рис. 4. 
У верхній частині вікна розташовано три кнопки: «
Создать…», «Изменить…», «Удалить». Основну частину вікна займає таблиця з переліком наявних іменованих діапазонів. Для кожного діапазону можна побачити його ім’я, значення у відповідній комірці, адресу в робочій книзі, примітку. Діапазонів у робочій книзі може бути багато. Усіх їх буде показано у вигляді таблиці в центральній частині вікна. Переміщатися таблицею можна звичайним способом: навігаційними клавішами або за допомогою миші. У будь-який момент один рядок таблиці (одне ім’я діапазону) є поточним. Його виділено інверсним кольором. У нижній частині вікна знаходиться поле «Диапазон:». У ньому Excel показує адресу в робочій книзі для поточного імені. Кнопка «Фильтр» у правому верхньому куті потрібна для відбору імен за умовою. Кнопка «Закрыть» завершує роботу з Диспетчером імен.3. Натискуємо на кнопку «
Создать». Excel відкриє вікно «Создание имени», як показано на рис. 5. У ньому — чотири поля, частину з них уже заповнено. В області «Имя: » Excel підставив значення «USD». Це буде назвою діапазону. За бажання це ім’я можна змінити, увівши до області «Имя:» нове значення. У полі «Область» Excel установив значення «Книга». Це означає, що ім’я діє в межах будь-якого листа поточної робочої книги, тобто в межах документа «Прайс». Область із приміткою поки що порожня. Тут ми можемо надрукувати будь-який коментар. У нижній частині вікна розташоване поле «Диапазон:», у ньому стоїть значення «=Спр!$B$4». Це — адреса поточної комірки на робочому листі. У нашому випадку — це адреса комірки зі значенням курсу долара на листі «Спр». 
4. Натискуємо на «
ОК» — ім’я присвоєно.Подивимося, що ми отримали. Переходимо на лист «
Прайс», робимо активною комірку «D2». У ній записано формулу перерахунку вартості товару «Блокнот» у долари. Формула залишилася без змін «=C2/Спр!$B$4».Важливо!
Адреси у формулах мають пріоритет перед іменами. Якщо ви створили нове ім’я, наявні адреси автоматично не змінюватимуться.Спробуємо наново побудувати формулу розрахунку доларової ціни в комірці «
D2» на листі «Прайс». Для цього виконуємо такі дії:1) натискуємо на «
=»;2) клацаємо по «
С2», натискуємо на «/»;3) клацаємо по ярличку листа «
Спр», потім — по комірці «B4»;4) натискуємо на «
Enter».Дивимося на нову формулу, що має вигляд «
=C2/USD».Важливо!
Excel автоматично підставляє до формули наявні іменовані діапазони.Продовжимо експеримент:
1) переходимо на лист «
Спр». Стаємо на комірку «B4»;2) клацаємо по іконці «
Диспетчер имен» (рис. 3);3) у вікні Диспетчера (рис. 4) переходимо всередину таблиці з іменами та клацаємо по рядку «
USD». Стане доступною кнопка «Изменить»;4) клацаємо по кнопці «
Изменить». З’явиться вікно, як на рис. 5, але з єдиною відмінністю: воно називається «Изменение имени»;5) змінюємо значення в полі «
Имя: », удруковуємо сюди рядок «КурсДоллара»;6) натискуємо на «
ОК».Ми змінили назву діапазону. Переходимо на лист «
Прайс». Формула в комірці «D2» змінилася. Тепер вона має вигляд «=C2/КурсДоллара».Важливо!
При зміні назв діапазонів Excel автоматично коригує відповідні формули.Повертаємо комірці «
Спр!$B$4» назву «USD». Тепер прокоментуємо кілька важливих моментів.Запитання перше: звідки Excel узяв назву діапазону «
USD»? Він її знайшов у лівому сусідньому стовпці відносно поточної комірки. Стосовно «B4» — це комірка «A4» (колонка «A»). Заголовки можуть розміщатися і над поточною коміркою. Тоді спрацьовує пріоритет їх присвоєння: спочатку Excel намагається взяти імена в комірці ліворуч від поточної. Якщо імені там знайти не вдалося, Excel бере його з комірки зверху (над поточною). Інакше він залишає поле імені порожнім та пропонує заповнити його з клавіатури.Запитання друге стосується параметрів вікна «Создание имени
» (рис. 5). Таких параметрів три: «Область», «Примечание» і «Диапазон». Із приміткою все зрозуміло, тут може знаходитися будь-який пояснюючий текст. З іншими параметрами потрібно розібратися.Поле «
Область» являє собою список. У нашому прикладі він складається з таких значень: «Книга», «Прайс», «Спр». Тобто в цьому списку знаходиться один глобальний параметр «Книга», а потім перелічено всі наявні в ній листи. Параметр «Область» визначає сферу дії створеного імені. Якщо ми вкажемо «Книга», ім’я діятиме в межах будь-якого листа робочої книги. Якщо вибрати конкретний лист, ім’я діє лише в межах цього листа.
Приклад
Ми створили ім’я «
USD» і зазначили зону дії цього імені — «Книга». Стаємо на комірку «B5» (курс євро) і за допомогою Диспетчера сформуємо для цієї комірки ім’я «EUR». Але для цього імені обмежимо зону дії листом «Прайс». Стаємо на лист «Спр». У будь-якій комірці цього листа вводимо формулу «=USD». У комірці з’явиться значення курсу долара (у прикладі воно дорівнює 8,3 грн. за один долар). Уводимо формулу «=EUR». Результатом буде «#ИМЯ?». Це означає, що Excel не розпізнав уведеного імені. Усе правильно, на листі «Спр» воно недоступне.Переходимо до поля «
Диапазон». При створенні імені за умовчанням Excel ставить до цього поля адресу поточної комірки. Здебільшого це значення змінювати не потрібно. Але бувають винятки.Важливо!
«Диспетчер имен» — єдиний інструмент, в якому можна побудувати ім’я для тривимірного посилання. Подивимося, як це реалізувати на практиці.
Приклад
Відкриваємо нову робочу книгу, стаємо на комірку «
A1».Викликаємо «
Диспетчер имен».У полі «
Имя: » вводимо назву «Ссылка3D».Переходимо до поля «
Диапазон:». Зараз у ньому знаходиться адреса поточної комірки. У нашому випадку — це «=Лист1!$A$1».Акуратно клацаємо мишкою по рядку з адресою наприкінці тексту «
=Лист1» і додруковуємо її, щоб вийшло таке посилання: «=Лист1:Лист3!$A$1».Стаємо на комірку «
А2» та вводимо формулу для підсумовування значень у діапазоні «Ссылка3D». Формула буде такою: «=СУММ(Cсылка3D)».Тепер на листі «
Лист1» до комірки «A1» записуємо будь-яке число, наприклад «10». На листі «Лист2» до комірки «A1» записуємо «20». На листі «Лист3» в комірці «A1» записуємо «30».Переходимо на лист «
Лист1». Результат підсумовування в «А2» дорівнює «60». Це означає, що функція підсумовування виконала оброблення тривимірного посилання між різними листами.Напишемо в будь-якій комірці листа посилання у вигляді «
=Ссылка3D». Результатом буде «#ЗНАЧ!». Цього і слід було чекати: Excel 2007 не може виконати операцію над тривимірним посиланням без спеціального оброблення (наприклад, у вигляді функції підсумовування).Щоб видалити ім’я, викликаємо Диспетчер імен. У центральній частині вікна знаходимо рядок із потрібним ім’ям, виділяємо його і натискуємо на кнопку «
Удалить» (рис. 4).Важливо!
Перш ніж рухатися далі, видаліть ім’я «EUR» на листі «Спр».Крім Диспетчера імен, у групі
«Определенные имена» є ще три інструменти. Подивимося, як вони працюють. Переходимо на лист «Спр», ставимо покажчик поточної комірки на «B5» (курс євро). Клацаємо лівою кнопкою по іконці «Присвоить имя» (рис. 3). Розкриється вікно «Создание имени», показане на рис. 5. Усі параметри цього вікна ми вже знаємо, їх можна залишити за умовчанням чи підкоригувати просто на місці. Щоб створити ім’я, клацаємо по «ОК». У лівій частині іконки «Присвоить имя» знаходиться значок випадаючого списку. Клацаємо по ньому — з’явиться меню з двох пунктів: «Присвоить имя» і «Применить имена…». З першим ми знайомі, а от на другому слід зупинитися докладніше.Можливість застосування імен працює з певним діапазоном комірок. За командою «
Применить имена…» Excel проглядає формули у виділеному діапазоні. Якщо посилання у формулі збігається з адресою наявного імені, Excel автоматично замінить посилання на іменований діапазон. Цей спосіб дуже економить час на коригування формул. Наприклад, замість того, щоб вручну змінювати посилання у формулах прайс-листа і потім копіювати їх, можна було виділити весь діапазон комірок та виконати команду «Применить имена…».Іконка «
Использовать в формуле» (рис. 3) потрібна для підстановки імен діапазонів до тексту формули. Іконка являє собою список, що розкривається. У ньому наведено всі доступні назви діапазонів. Крім того, у списку є спеціальний пункт «Вставить имена». Як же користуватися цими інструментами? Наприклад, я хочу побудувати формулу в комірці «А10» листа «Прайс» (рис. 1). Формула має показати інформацію про поточний курс долара щодо гривні. Виконуємо такі дії:1) переходимо до листа «
Прайс». Ставимо покажчик поточної комірки на «А10»;2) друкуємо текст «
="Текущий курс USD: "&». Тепер у цьому місці потрібно вставити посилання на комірку «B4» листа «Спр». Я пам’ятаю, що ця комірка називається «USD». Клацаємо по іконці «Использовать в формуле», зі списку, що розкрився, вибираємо «USD»;3) завершуємо формулу, надрукувавши текст
"&" грн. за 1$"&"."»;4) натискуємо на «
ОК».У результаті ми отримали такий вираз: «
="Текущий курс USD: "&USD&" грн. за 1$"&"."».Імена не завжди можна показати у вигляді випадаючого списку. Тому серед пунктів меню «
Использовать в формуле» є рядок «Вставить имена». Клацаємо по ньому лівою кнопкою миші. Розкриється вікно, показане на рис. 6. У ньому знаходимо ім’я, клацаємо по ньому лівою кнопкою миші. Назва імені потрапить до формули. 
Останній інструмент у групі «
Определенные имена» має назву «Создать из выделенного фрагмента» (рис. 3). Він зручний для групового присвоєння імен відразу декільком коміркам. Дія цієї іконки схожа на роботу Диспетчера імен, але з дещо іншим інтерфейсом. От як нею скористатися.
Приклад
Мені потрібно створити імена для трьох видів знижок. Дані записано на листі «
Спр» у комірках «B7», «B8», «B9». Ліворуч у комірках розташовано назви знижок. Причому ці назви є сенс використовувати як імена для створюваних діапазонів. Виконуємо такі дії:1) виділяємо блок «
A7: B9». Назви діапазонів до виділеної області потрібно включити обов’язково!2) клацаємо по іконці «
Создать из выделенного фрагмента». З’явиться вікно, як на рис. 7; 
3) для нашого прикладу включаємо прапорець «
в столбце слева» (діє за умовчанням). Це означає, що імена Excel візьме з комірок, розташованих у лівій частині виділеного діапазону (у нас це колонка «А», комірки «A7», «A8», «A9»);4) натискуємо на «
ОК». Проглядаємо адреси комірок. Тепер «B7» називається «МО», «B8» — «Опт», «B9» — «Спец».Завершуючи тему роботи з іменованими діапазонами, хочу відзначити невелику відмінність між Excel 2007 та Excel 2003. При написанні формул в Excel 2003 як імена можна було використовувати заголовки рядків чи стовпців. Наприклад, у табличному документі є колонка з назвою «
Сумма». У комірках цієї колонки стоять числові значення. Для визначеності вважатимемо, що це блок «B2:B4». Тоді формула «=СУММ(B2:B4)» зможе підрахувати суму за цим діапазоном. Цю саму формулу в Excel 2003 можна було написати так: «=СУММ(Сума)», результат отримаємо однаковий. В Excel 2007 такий спосіб адресації застосовувати не можна. На мій погляд, це не є недоліком програми. Знаючи про таку можливість, я, наприклад, застосував її на практиці лише кілька разів за багато років роботи з Excel, та й то скоріше зі спортивного інтересу. До того ж спосіб адресації через заголовки рядків та колонок загрожує проблемами. Хто може гарантувати правильний результат, якщо в таблиці з’явиться ще одна колонка з назвою «Сумм»?... Відмовившись від такого екзотичного способу адресації, розробники Excel фактично усунули одне із джерел потенційних помилок.Отже, ми розібралися з можливостями застосування іменованих діапазонів в Excel 2007. Така адресація комірок має багато переваг. Запис формул спрощується. Абсолютна адресація посилань мінімізує помилки у створенні формул. В Excel 2007 для роботи з діапазонами з’явилися зручні засоби, з яких найфункціональнішим є Диспетчер імен. За необхідності створити велику кількість імен можна скористатися інструментом «
Создать из выделенного фрагмента». Тож я не бачу причин, щоб не застосовувати на практиці іменовані діапазони якомога частіше. Зі свого досвіду зауважу: коли я почав користуватися іменами, мої таблиці стали набагато зрозумілішими. Одного цього аргументу для мене було досить, щоб іменовані діапазони стали постійним супутником кожної створюваної мною таблиці.А нам для завершення теми роботи з формулами залишилося ознайомитися з роботою вбудованих функцій, методами контролю обчислень та опанувати прийоми швидкого створення складних формул. Ці три питання і стануть темою наступної статті.
Чекаю на ваші листи, пропозиції та зауваження на
bk@id.factor.ua, nictomkar@rambler.ru або на форумі редакції www.bk.factor.ua/forum . Успішної роботи!
