Координатне підсвічування в MS Excel
 | Робота з великими таблицями для бухгалтера — річ звична. Значними за обсягом звітами тут нікого не здивуєш. Читати документи, що перекривають площу сучасного монітора, бухгалтеру доводиться постійно. І не просто читати, а звіряти, аналізувати, шукати помилки, виконувати складну й відповідальну роботу. Що б не говорили, ця справа не з приємних. Переглядати великі документи на екрані важко. Пробігаючи поглядом таблицю, легко «перестрибнути» на сусідній рядок і припуститися помилки. У цьому розумінні з паперовим документом усе набагато простіше — можна прикласти лінійку або аркуш паперу — і тоді працювати з таблицею буде зручніше, багатьох помилок удасться уникнути. У зв’язку з цим виникає запитання: як створити таку лінійку в MS Excel, щоб, переміщаючись по листу, підсвічувати рядок і колонку для активної комірки. На жаль, серед стандартних інструментів такої можливості немає. Про те, як виправити цю ситуацію (причому простими й доступними способами!), я розповім у цій статті. Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства |
Отже, наша мета — побудувати інструмент, що вміє відстежувати на екрані місцерозташування активної комірки, виділяючи кольором поточний рядок і стовпець. У результаті на листі з’явиться своєрідна «координатна лінійка», переглядати таблицю стане набагато зручніше. Вирішити таке завдання можна по-різному. Наприклад, написати мовою VBA (Visual Basic for Application) спеціальну програму. Але я — не прихильник цього підходу і тому пропоную розпочати зі стандартних можливостей MS Excel.
Спосіб 1. Використовуємо умовний формат
і функцію «ЯЧЕЙКА()»
Для організації координатного підсвічування нам потрібно вирішити дві проблеми:
1. Якимось чином визначити номер рядка та колонки, де знаходиться активна комірка.
2. Змінити формат цих рядків та колонок, щоб позначити їх на робочому листі.
Для вирішення першого завдання ми звернемося до функції «ЯЧЕЙКА()» зі стандартної бібліотеки MS Excel. Після цього, знаючи номер конкретного рядка та колонки, ми застосуємо до них умовне форматування. Як бачите, схема проста. Залишається реалізувати її на практиці. Почнемо з функції «ЯЧЕЙКА()», яка дозволяє отримати багато корисної інформації про будь-яку комірку робочого листа. Наприклад, дізнатися про її висоту, ширину, номер рядка чи колонки, числовий формат тощо. Функція має два аргументи:
— ключове слово, наприклад «"строка"» або «"столбец"»;
— адреса комірки, для якої потрібно з’ясувати її параметри.
Хитрість полягає в тому, що другий аргумент зазначати не обов’язково. І тоді функція поверне параметри для поточної комірки. Тобто через функцію «ЯЧЕЙКА()» ми зможемо визначити номер рядка та колонки для активної комірки робочого листа — саме те, що нам потрібно для вирішення першого завдання.
Переходимо до умовного форматування. Цей корисний інструмент Excel дозволить нам змінити формат комірок, якщо вони задовольняють певну умову. Причому (і це дуже важливо!) умову в цьому випадку можна задати за допомогою формули. Отже, усі інструменти для вирішення завдання, здається, у нас є. Переходимо до деталей. Подивимося, який вигляд усе це має у програмі Excel 2010. Виконуємо такі дії:
1. Відкриваємо (або створюємо новий) документ Excel. Нехай це буде база даних, як на рис. 1.
2. Клацаємо мишею по комірці «A1».
3. Переходимо до меню «Главная». У групі «Стили» клацаємо по іконці «Условное форматирование» (рис. 2). Відкриється меню, як на рис. 3.
4. Із цього меню вибираємо пункт «Создать правило…». Відкриється вікно «Создание правила форматирования», зображене на рис. 4.
5. У розділі «Выберите тип правила:» знаходимо варіант «Использовать формулу для определения форматируемых ячеек».
6. У полі «Измените описание правила:» вводимо формулу «=ИЛИ(ЯЧЕЙКА("строка")=СТРОКА(A1);ЯЧЕЙКА("столбец")=СТОЛБЕЦ(A1))».
7. Клацаємо по кнопці «Формат…» (рис. 4). Відкриється вікно «Формат ячеек», зображене на рис. 5.
8. У цьому вікні переходимо на закладку «Заливка». У запропонованій палітрі вибираємо колір фону, наприклад світло-сірий варіант.
9. У вікні «Формат ячеек» натискуємо на «ОК».
10. У вікні «Создание правила форматирования» натискуємо на «ОК». Умовний формат для комірки «A1» готовий. Залишається скопіювати його на весь робочий лист.
11. Залишаючись на комірці «A1», клацаємо по кнопці «Формат по образцу» (рис. 6).
12. Обводимо за допомогою миші (не клавіатури!) блок комірок, де потрібно створити координатне виділення.
Порада У Excel 2003 (або більш ранніх версіях цієї програми) викликати інструмент умовного форматування можна через меню «Формат → Условное форматирование…», після чого у вікні настройок установити параметр «Условие 1» у положення «формула».
Скажемо кілька слів про роботу формули і про те, як вона пов’язана з умовним форматуванням. Нагадаю, що наразі ми знаходимося в комірці «A1», причому формулу ми записали саме для цієї комірки. Тепер по черзі. Розпочнемо з виразу «ЯЧЕЙКА("строка")». Оскільки у функції адресу явно не зазначено, номер рядка вона визначить для активної комірки робочого листа. Це значення ми порівняємо з номером рядка для адреси «A1», який нам поверне функція «СТРОКА(A1)». І тоді, якщо покажчик активної комірки знаходиться в першому рядку робочого листа, то вираз «ЯЧЕЙКА("строка")=СТРОКА(A1)» буде істинним. Інакше ця формула поверне значення «ЛОЖЬ». Друга частина формули, по суті, така сама, тільки перевіряє вона номер колонки, тобто вираз «ЯЧЕЙКА("столбец") =СТОЛБЕЦ(A1))» порівнює номер колонки для активної комірки з номером стовпця для адреси «A1».
Усе, що залишається зробити, — це об’єднати обидва вирази через операцію «ИЛИ()». І тоді отримана формула означатиме: «Якщо активна комірка знаходиться в першому рядку або в першій колонці робочого листа, поверни значення «ИСТИНА» і включи інструмент умовного форматування». Для комірки «A1» все правильно. Залишається поширити це правило на всю область робочого листа. Для цього ми скористалися іконкою копіювання формату комірок. У цьому випадку адреси у формулі умовного форматування буде перетворено за звичайними правилами Excel, тобто при копіюванні по вертикалі зміняться номери рядків. При копіюванні по горизонталі зміняться номери колонок. Наприклад, формула для умовного форматування комірки «B1» стане такою: «=ИЛИ (ЯЧЕЙКА("строка")=СТРОКА(B1);ЯЧЕЙКА ("столбец")= СТОЛБЕЦ(B1))». А в результаті рядок і колонка поточної комірки змінять свій колір. Це й є координатне виділення, яке ми хотіли отримати.
Але це ще не все. Справа в тому, що правило умовного форматування спрацьовує лише при оновленні робочого листа, наприклад у момент перерахунку формул. Просто Excel не вважає переміщення активної комірки таким оновленням. І тому координатне підсвічування не працюватиме. Для вирішення проблеми нам доведеться штучно відновити лист. Найпростіше це зробити за допомогою невеликого макросу, додавши його до властивостей робочого листа. Для цього виконуємо такі дії:
1. Клацаємо правою кнопкою миші на листі з параметрами умовного форматування. Відкриється контекстне меню, як на рис. 7.
2. Із цього меню вибираємо пункт «Исходный текст». Відкриється вікно редактора Visual Basic.
3. У цьому вікні вставляємо такий текст:
«Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveCell.Calculate
End Sub».
4. Закриваємо вікно Visual Basic. На пропозицію зберегти зміни відповідаємо ствердно.
Важливо! Текст модуля на Visual Basic потрібно вводити без лапок обрамлення.
Тепер при будь-якому переміщенні активної комірки буде активовано умовне форматування, а на листі з’явиться координатне виділення рядків та колонок, як показано на рис. 8. Завдання вирішено.
Подивимося, що ми отримали насправді, якими є переваги та недоліки нашого підходу. Спочатку про переваги, серед яких я б зазначив такі:
— інструмент умовного форматування не порушує функціонал таблиці, працюють усі прийоми копіювання, переміщення комірок;
— робота з умовними форматами не порушує форматування таблиці;
— наш варіант реалізації координатного підсвічування правильно працює з таблицями, де є об’єднані комірки, а це для бухгалтера дуже важливо;
— макроси на VBA потрібні в мінімальному обсязі, прикріплення їх до робочого листа не викликає труднощів.
Що стосується недоліків, то вони теж є, зокрема:
— формулу для умовного форматування потрібно створювати вручну;
— немає способу швидко включити або відключити координатне підсвічування на робочому листі — для цього потрібно видалити правило умовного форматування, а це вимагатиме часу;
— при копіюванні комірок з інших робочих книг або листів для них доведеться відновлювати правила умовного форматування.
На мій погляд, зазначені проблеми не належать до розряду істотних. З особистому досвіду скажу, що робота з координатним обчисленням на базі умовного форматування не викликає жодних проблем. Проте є сенс розглянути й інші способи вирішення завдання, чим ми зараз і займемося.
Спосіб 2. Застосовуємо надбудову
«FollowCellPointer»
У результаті п’ятнадцятихвилинного спілкування з Google я виявив щонайменше десяток варіантів для реалізації координатного підсвічування з використанням макросів на VBA. Усі вони відрізнялися в деталях, але принцип був один: для позначення рядка та колонки ці макроси використовували виділення незв’язних діапазонів. У ручному режимі це можна зробити, натиснувши на клавішу «Ctrl», а потім клацанням миші виділити рядок і колонку. Такі варіанти я відкинув без жалю — коли на листі виділено незв’язний діапазон, не працюють операції копіювання та переміщення комірок. А це — величезний недолік при роботі з таблицею. Та й заглиблюватися в нюанси роботи з VBA, відверто кажучи, не хотілося б. Але одне посилання мене зацікавило. Ішлося про готову надбудову «FollowCellPointer», яку роздає на своєму сайті Jan Karel Pieterse (Нідерланди). Цей продукт є безкоштовним, побудований він на макросах Excel та дозволяє малювати на листі графічні лінії для позначення рядка і колонки активної комірки.
Перш ніж ознайомиться з цією програмою, її потрібно скачати. Для цього виконуємо такі дії:
1. Заходимо на сайт «http://www.jkp-ads.com» і клацаємо по гіперпосиланню «free utilities» (рис. 9). Відкриється розділ завантажень, оформлений у вигляді таблиці.
2. У запропонованому переліку знаходимо програму «FollowCellPointer» (рис. 9) та клацаємо по посиланню лівою кнопкою миші. Через деякий час у папці завантажень вашого браузера з’явиться файл «followcellpointer.zip».
3. Копіюємо цей файл до будь-якої папки та розпаковуємо його. Усе — надбудова готова для інсталяції.
Важливо! Для роботи з файлом «followcellpointer.zip» на вашому комп’ютері має бути встановлено програму-архіватор (WinRar, WinZip чи аналогічна).
У переважній більшості випадків така програма є. І тоді для розпаковування достатньо клацнути по імені файлу лівою кнопкою миші, потім вибрати з контекстного меню «Извлечь все…» або «Распаковать в текущую папку». У результаті в цій папці з’явиться файл «FollowCellPointer.xla». Клацанням миші завантажуємо його до MS Excel. Надбудова готова до роботи.
Важливо! Для роботи надбудови в Excel 2010 скопіюйте файл «FollowCellPointer.xla» до системної папки прикладних рішень MS Office. За умовчанням шлях до цієї папки є таким: «C:\Users\…\AppData\Roaming\Microsoft\AddIns». У цьому рядку замість символу «…» потрібно поставити ім’я користувача для вашого облікового запису Windows.
Далі виконуємо такі дії:
1. Відкриваємо або створюємо новий документ.
2. Викликаємо меню «Файл», потім клацаємо по пункту «Параметры».
3. У вікні настройок Excel переходимо до розділу «Надстройки» і натискуємо на кнопку «Перейти…» (рис. 10). Відкриється однойменне вікно «Надстройки», як на рис. 10.
4. У цьому вікні ставимо галочку біля рядка «FollowCellPointer».
5. У вікні «Надстройки» натискуємо на «ОК».
6. Переходимо до меню «Надстройки». Стрічку цього меню показано на рис. 11. На ній з’явиться додаткова група з іконками: «Enable», «Disable», «Anchor», «ClearAnchor», «Format».
7. Клацаємо по іконці «Enable». Біля активної комірки з’являться тонкі лінії, як показано на рис. 12. На мій погляд, дуже витончене рішення, яке цілком замінює координатне підсвічування рядків та колонок.
Щоб відключити цей режим, клацаємо по кнопці «Disable».
Як і більшість програм, «FollowCellPointer», містить низку корисних режимів та настройок. Ось основні з них.
Інструмент «Anchor» (рис. 11) дозволяє зафіксувати позначення поточної комірки, після чого продовжити роботу у звичайному режимі. Така можливість є корисною, наприклад, при аналізі зв’язків між елементами таблиці. Приклад використання режиму «Anchor» показано на рис. 13.
Кнопка «ClearAnchor» видаляє позначку, зроблену в режимі «Anchor».
Останньою іконкою, що належить до «FollowCellPointer», є кнопка «Format». Клацання по цій кнопці відкриває вікно «Format Arrows», зображене на рис. 14. Незважаючи на англійський інтерфейс, тут усе просто. Більшість параметрів у цьому вікні дозволяють змінити форму стрілок та ліній для позначення поточної комірки. Опишу стисло основні настройки, наведені у вікні «Format Arrows»:
— «Arrow head style» — змінює форму покажчиків на лініях, що ведуть до активної комірки. Можливі варіанти: «Diamond» — покажчики мають вигляд як ромбів; «Open» — тонкі стрілки; «Oval» — круглі покажчики, «None» — лінії буде показано без покажчиків; «Triangle» — покажчики мають вигляд заповнених стрілок (у вигляді трикутників);
— «Arrow head length» — дозволяє відрегулювати довжину покажчиків на кінці ліній. Передбачено два варіанти: «Short» — короткий покажчик і «Long» — довгастий покажчик;
— «Arrow head width», тут можна задати ширину покажчика: «Narrow» — тонкий покажчик, «Wide» — широкий покажчик;
— «Arrow line width» — цей параметр впливає на товщину ліній: «1» — тонка лінія, «2» — лінії з напівжирним зображенням;
— «Transparency» — змінює спосіб малювання ліній. Параметр може набувати два значення: «Opaque» — малювати звичайні лінії, «Transparent» — малювати напівпрозорі лінії.
Кнопка «Line Color» (рис. 14) дозволяє вибрати колір лінії зі стандартної палітри MS Excel.
Зрозуміло, що настроювання параметрів — справа суто індивідуальна. Мені сподобався варіант із напівпрозорими лініями та округлими закінченнями. А детальний склад моїх настройок має такий вигляд: «Arrow head length» = «Short», «Arrow head width» = «Narrow», «Arrow line width» = «1», «Transparency» = «Transparent».
На сьогодні все. Сподіваюся, що матеріал цієї статті зробить вашу роботу з великими таблицями простішою та зручнішою. До нових зустрічей!
Чекаю ваших питань, зауважень та пропозицій на bk@id.factor.ua, nictomkar@rambler.ru або на форумі редакції www.bk.factor.ua/forum.
