Pavlo Zhezhnych Personal Site and Blog

Електронні таблиці MS Excel призначені для відображення та аналізу інформації у табличному вигляді. При роботі з електронними таблицями необхідно оперувати наступними основними поняттями: робоча книга, робочий листок, комірка, діапазон.
Робоча книга – це файл, з яким необхідно працювати і в якому зберігаються дані. Робоча книга складається з декількох листків.

Рис. 1. Робоча книга MS Excel та її основні елементи.

Робочий листок – це набір табличних даних. Дані можуть редагуватися на декількох листках одночасно. На основі даних робочого листка можна створити діаграму, яка буде розміщуватися або на робочому листку з даними, або на окремому листку діаграми. Кожен робочий листок має назву, яка повинна бути унікальною в межах робочої книжки. Робочий листок складається з комірок.
Комірка – це елемент робочого листка, в якому зберігається цілісний набір інформації. У комірках можуть зберігатися текстові, числові та часові дані. Крім того значення комірки може бути визначене за допомогою формули. Усі комірки робочої книжки мають адресу (назву), яка визначається через номер стовпчика та номер рядка таблиці. Оскільки стовпчики робочого листка нумеруються буквами A, B, C,…, а рядки – числами 1, 2, 3,…, то комірки адресуються A1, B1, C2,…. Якщо відбувається одночасне оперування комірками з декількох листків, то адреси комірок записуються у розширеному вигляді:
<Робочий листок>!<Стовпчик><Рядок>.
Наприклад, якщо робоча книга містить листки з назвами Sheet1, Sheet2, Sheet3,…, то адреси комірок запишуться Sheet1!A1, Sheet2!B1, Sheet3!C2,….
Ввід інформації здійснюється в активну комірку (виділену прямокутником) активного робочого листка (з виділеною назвою). Якщо значення комірки обчислюється за допомогою формули, то її вміст повинен починатися зі символу ‘=’ (в інакшому випадку з будь-якого іншого символу). Наприклад, формула, записана для комірки D2
=B2*C2
означає, що значення комірки D2 обчислюється як добуток значень комірки B2 та C2.
Діапазон – це одна або декілька комірок. Будь-який діапазон визначається своїми лівою верхньою та правою нижньою комірками, записаних через символ ‘:’. Наприклад, діапазон, зображений на рис. 2, описується наступним чином:
D2:D6.
Діапазон може містити комірки з декількох рядків і декількох стовпчиків. Наприклад, у діапазон B2:D6 входять комірки від B2 до B6, від C2 до C6 та від D2 до D6.

Рис. 2. Діапазон робочого листка.

Діапазони найчастіше використовуються у формулах, написаних з використанням вбудованих функцій MS Excel (найбільш вживаними функціями можуть бути SUM – сума, AVERAGE – середнє, IF – умова та ін.). Приклади використання функцій та діапазонів наведено в наступній таблиці.

Комірки таблиці характеризуються не тільки значеннями та формулами, але й виглядом. Якісна електронна таблиця повинна поєднувати правильний набір формул та прийнятне оформлення (форматування). Для форматування комірок використовується пункт меню Format\Cells (Формат\Комірки), який відкриває відповідне діалогове вікно (див. рис. 3). Для комірок визначаються такі властивості як формат відображення (закладка Number), вирівнювання (Alignment), шрифт (Font), рамка (Border) та ін.

Рис. 3. Діалогове вікно форматування комірок.

Створення електронної таблиці часто супроводжується виконанням однотипних рутинних операцій, що приводить до надмірної витрати часу. Для автоматизації виконання різноманітних рутинних операцій (наборів операцій), як, наприклад, форматування чи обчислення даних, та для прискорення створення електронної таблиці в MS Excel існують так звані макроси.
Макрос – це набір інструкцій, який описує деяку послідовність дій над елементами електронної таблиці (робоча книга, робочий листок, окрема комірка, діапазон, виділена область робочого листка тощо), яку користувач виконує за допомогою миші та клавіатури. З іншого боку, макрос – це набір інструкцій, описаних за допомогою мови програмування VBA у відповідному редакторі.
Таким чином, існує в MS Excel існує два способи створення макросів.

• Заданням відповідних дій за допомогою миші та клавіатури (за допомогою “Записувача макросів”).
• Написанням відповідної процедури мовою VBA.

Для створення макросів першим способом необхідно виконати наступні дії.

• Розпочати запис макросу вибравши пункт меню Tools\Macro\Record New Macro….


Рис. 4. Діалогове вікно запису нового макросу.


• У діалоговому вікні що з’явилось, задати ім’я макросу (Macro name), також можна задати гарячі клавіші, за якими в подальшому викликатиметься цей макрос (Shortcut key).
• Після натиску кнопки ОК, виконати необхідні маніпуляції над елементами MS Excel за допомогою миші та клавіатури. При цьому з’явиться інструментальне меню Stop Recording (Зупинити запис).


Рис. 5. Інструментальне меню Stop Recording (Зупинити запис).


• Завершити запис макросу з допомогою пункту меню Tools\Macro\Stop Recording або натиском відповідної (першої зліва) кнопки інструментального меню Stop Recording (Зупинити запис).

Створений макрос буде мати вказане вище ім’я.
Для створення макросів другим способом необхідно виконати наступні дії.

• Завантажити редактор Visual Basic за допомогою пункту меню Tools\Macro\Visual Basic Editor або натиснувши комбінацію клавіш ALT+F11.


Рис. 6. Редактор Visual Basic.


• У вікні проекту вибрати необхідний елемент (що відповідає активній робочій книзі), до якого буде належати макрос.
• Створити новий модуль за допомогою пункту меню редактора Insert\Module.
• В правій частині редактора написати процедуру мовою VBA, яка описує необхідні маніпуляції над елементами MS Excel.
• Закрити редактор Visual Basic.

Створений макрос буде мати таке ж ім’я, як ім’я відповідної VBA-процедури, задане після ключового слова Sub (на рис. 6 зображено макрос з назвою FormatFont).
Переглянути створені макроси можна за допомогою пункту меню Tools\Macro\Macros…. На екрані з’являється діалогове вікно, зображене на рис. 7.

Рис. 7. Діалогове вікно перегляду списку макросів.

Вибравши відповідний макрос, його можна виконати (викликати) натисненням кнопки Run. Для зручності виклику макросів можна задавати гарячі клавіші або створити кнопку на інструментальному меню MS Excel. Гарячі клавіші присвоюються при створенні макросу або натиском кнопки Options… (Установки…) діалогового вікна перегляду списку макросів (див. рис. 7). Для створення кнопки інструментального меню MS Excel необхідно виконати наступні дії.

• Викликати діалогове вікно налагодження за допомогою пункту меню Tools\Customize….
• За допомогою закладки Toolbars (Інструментальні меню), як показано на рис. 8, створити нове інструментальне меню, натиснувши кнопку New… (Нове…). При цьому необхідно вказати назву нового інструментального меню. Після створення нового інструментального меню його назва відобразиться у відповідному списку діалогового вікна налагодження та воно з’явиться у середовищі MS Excel.


Рис. 8. Діалогове вікно налагодження: закладка Toolbars.


• На закладці Commands (Команди) у списку категорій (Categories) вибрати пункт Macros (Макрос), як показано на рис. 9.


Рис. 9. Діалогове вікно налагодження: закладка Commands.


• Зі списку команд (Commands) вибрати мишкою або Custom Menu Item (Користувацький елемент меню), або Custom Button (Користувацька кнопка), і, не відпускаючи лівої кнопки миші, перетягнути вибрану команду на створене інструментальне меню (див. рис. 10).


Рис. 10. Створення кнопки на інструментальному меню.


• Клацнути правою кнопкою миші на створеній кнопці та в контекстному меню вибрати пункт Assign Macro (Призначити макрос).


Рис. 11. Діалогове вікно призначення макросу.


• У діалоговому вікні, яке з’явилося, зі списку вибрати створений макрос та натиснути кнопку Ok.
• Закрити діалогове вікно налагодження.

В результаті в середовищі MS Excel окрім стандартних інструментальних меню буде присутнє створене користувацьке інструментальне меню з однією кнопкою. Натиск цієї кнопки мишею приведе до виконання призначеного макросу.

Рис. 12. Інструментальне меню з призначеним макросом у MS Excel.

Аналогічно створюються наступні потрібні макроси та відповідні кнопки на інструментальному меню.

ПРИКЛАД
Приклад створення макросу, який змінює параметри шрифту для виділеної ділянки робочого листка.
1) Створення з допомогою “Записувача макросів”.
Після виклику команди Tools\Macro\Record New Macro… і початку запису макросу здійснимо наступні дії:

• На листку Sheet1 клацнемо правою кнопкою миші в комірці, наприклад, А2 і меню, що з»явилось вибираємо пункт Format Cells.
• У вікні, що з’явилось, переходимо на вкладку Font.
• Встановлюємо Font type в курсив (Italic) і Color вибираємо червоний.
• Закриваємо вікно, натиснувши кнопку ОК.
• На панелі інструментів Formatting натискаємо на кнопку В;
• Зупиняємо запис макросу виконавши команду Tools\Macro\Stop Recording.

2) Створення з допомогою редактора VBA.
Після створення нового модуля в редакторі Visual Basic (пункт меню Insert\Module) набираємо текст наступної VBA-процедури.

Sub ChangeFont() ' Службове слово визначення процедури та її імені
With Selection.Font ' Оператор початку роботи з об’єктом Selection.Font
.Name = «Times New Roman» ' Встановлення імені шрифту об’єкта Selection.Font
.FontStyle = «Italic» ' Встановлення стилю шрифту об’єкта Selection.Font
.Size = 14 ' Встановлення розміру шрифту об’єкта Selection.Font
.ColorIndex = 3 ' Встановлення кольору шрифту об’єкта Selection.Font
.Bold = True ' Присвоєння значення "ІСТИНА" властивості Bold (Жирний) об’єкта Selection.Font
End With ' Оператор закінчення роботи з об’єктом Selection.Font
End Sub ' Службове слово закінчення процедури