Автоматизація розрахунків на робочому листі

Мова Visual Basic for Application дає змогу для автоматизації розрахунків на листах робочих книг використовувати елементи управління – командні кнопки, перемикачі, прапорці та ін. Для створення цих об’єктів слід вивести панель елементів за допомогою інструмента Элементы управления на панелі інструментів Visual Basic або виконавши команду меню Вид – Панели инструментов - Элементы управления. У результаті у вікні програми Microsoft Excel з’явиться панель елементів, яка схожа з панеллю елементів середовища Visual Basic 6.0:

 

За допомогою кнопок на панелі елементів можна створювати об’єкти різних класів. При роботі з елементами управління використовується режим конструктору для визначення властивостей об’єктів, створення процедур обробки подій, що пов’язані з певними об’єктами. Якщо режим конструктору виключено, елементи управління використовуються за призначенням (наприклад, натискання на командній кнопці призводить до виконання певних дій). Для переходу до режиму конструктору використовується інструмент Режим конструктора на панелі елементів управління, наступне натискання на інструменті виключає режим конструктору.

Для визначення властивостей елементів управління потрібно перейти до режиму конструктору, активізувати відповідний об’єкт і вивести вікно властивостей Properties за допомогою інструменту Свойства на панелі елементів або команди контекстного меню Свойства.

Подвійне клацання лівою кнопкою миші на об’єкті дає змогу створити процедуру для обробки події за замовчуванням для об’єктів даного класу. Наприклад, після подвійного клацання на командній кнопці можна почати створення процедури, яка буде обробляти подію Click - клацання лівою кнопкою миші на командній кнопці. За допомогою списку, що розчиняється, Procedure можна обрати іншу подію. Процедура обробки події буде створюватися у вікні редактору VBA, буде пов’язана з тим листом робочої книги, на якому розташовано елемент управління.

При створенні процедур можна змінювати, використовувати різні властивості різних об’єктів. При чому слід пам’ятати, що у VBA властивості об’єктів можуть повертати інші об’єкти. Наприклад, властивість Selection об’єкту Application повертає виділений діапазон чарунок.

Можна працювати з об’єктом Range, якому відповідає конкретна чарунка або діапазон чарунок. Об’єкт Range характеризується властивістю Value. Властивість Value – це вміст чарунки. Наприклад, для запису у чарунку D3 числа 4 можна подати команду:

Range("D3").Value = 4

За допомогою наступної команди в усі чарунки діапазону D3:E5 записується значення 0:

Range("D3:E5").Value = 0

До об’єкту Range можна застосувати метод Select, який призводить до виділення чарунок певного діапазону. Для виділення чарунок діапазону А2:А5 можна виконати команду:

Range("a3:a5").Select

Під час створення програм часто використовується властивість об’єктів Application, Worksheet, Range, Selection Cells(i, j), яка повертає об’єкт – певну чарунку листа робочої книги. У круглих дужках визначається адреса чарунки, при чому і – номер рядка, j – номер стовпця. Об’єкт Cells(i, j) характеризується властивостями:

- Value – уміст чарунки;

- NumberFormat – числовий формат;

- Formula – уміст чарунки, формула у звичайному вигляді;

- Font – шрифт символів;

- FormulaR1C1 – формула у форматі R1C1.

Можна навести приклади зміни властивостей об’єкту Cells(i, j):

Cells(1, 1).Value = 23	У чарунку А1 записується число 23.
Cells(20, 3).Formula = "Разом:"	У чарунку С20 записується текст “Разом:”.
Cells(4, 2).NumberFormat = "0.00"	Для чарунки В4 визначається числовий формат з двома знаками після десяткової крапки.
Cells(5, 6).NumberFormat = "0.00%"	Для чарунки F5 визначається процентний формат.
Cells(3, 1).Formula = "=A1 + A2"	У чарунку А3 записується формула =A1 + A2.
Cells(3, 1).Font.Bold = True	Для чарунки А3 визначається напівжирний шрифт.
Cells(3, 1).Font.Size = 16	Для чарунки А3 визначається розмір шрифту 16 пунктів.
Cells(3, 1).Font.Color = QBColor(9)	Для чарунки А3 визначається синій колір символів.

Введення формули у форматі FormulaR1C1 дає можливість визначити як абсолютні так і відносні стосовно поточної чарунки адреси чарунок у формулі. При застосуванні абсолютних адрес чарунок можна визначати номери рядків (R – row) і номери стовпців (C – column). Так, наприклад, у рядку коду

Cells(5, 1).FormulaR1C1 = "=R1C1+R2C1"

у чарунку А5 вводиться формула =$A$1+$A$2.

Можна також формувати формулу з адресами чарунок, які задаються відносно поточної, активної чарунки. Відносні значення визначають зсув на певну кількість рядків, стовпчиків, значення зсуву задається завжди у квадратних дужках. Так у рідку коду

Cells(3, 2).FormulaR1C1 = "=R[-2]C+R[-1]C"

у чарунку В3 вводиться формула =В1+В2. Адреса чарунки В1 визначається так: чарунка на 2 рядка вище поточної чарунки В3 і того ж рядка.

Використання елементів управління на листах робочої можна розглянути на прикладі кнопки “Розрахунок сум”, за допомогою якої можна розрахувати суми по рядках і колонках для всіх чарунок виділеного діапазону. Тобто якщо виділити, наприклад, блок чарунок B2:C3, то у чарунку D2 буде записано суму значень, які знаходяться в чарунках B2:C2, у чарунку D3 – суму значень чарунок B3:C3, у чарунку В4 - суму значень чарунок B2:В3 і т.д.

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

Вивести панель елементів за допомогою інструмента Элементы управления на панелі інструментів Visual Basic або виконавши команду меню Вид – Панели инструментов - Элементы управления.

Обрати елемент Кнопка і при натиснутій лівій кнопці миші визначити розмір і місце розташування об’єкту на листі Microsoft Excel. Коли користувач знаходиться у режимі конструктору (натиснута кнопка на панелі елементів) можна переміщувати кнопку по листу робочої книги, змінювати її розмір, властивості.

Перейти до режиму конструктору, активізувати кнопку та вивести вікно властивостей Properties за допомогою інструменту Свойства на панелі елементів або команди контекстного меню Свойства.

У вікні властивостей визначити властивості кнопки Name – cmdSum і Caption – Розрахунок сум.

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

Дані в рядку коду	Коментар
Sub cmdSum _Click()	Заголовок процедури, яка виконуватиметься після натискання на кнопці “Розрахунок сум”.
n = Selection.Rows.Count	Змінній n привласнюється значення кількості рядків у виділеному діапазоні чарунок.
m = Selection.Columns.Count	Змінній m привласнюється значення кількості стовпців у виділеному діапазоні чарунок.
' Підсумки по рядках	Коментар.
For i = 1 To n	Для i що змінюється від 1 до n виконуються дії (для всіх чарунок рядка i).
s = 0	Змінній s привласнюється значення 0.
For j = 1 To m	Для j що змінюється від 1 до m виконуються дії (для всіх чарунок стовпця j).
s = s + Selection.Cells(i, j).Value	У змінній s накопичується сума значень чарунок певного рядка.
Next j	Кінець циклу обчислення підсумкового значення по рядку.
Selection.Cells(i, m + 1).Value = s	У чарунці з адресою – певний рядок і колонка m + 1 (чарунка ліворуч від останньої чарунки виділеного діапазону) виводиться значення суми, що обчислено.
Next i	Кінець циклу обчислення підсумкових значень по всіх рядках.
' Підсумки по стовпцях	Коментар.
For j = 1 To m + 1	Для j що змінюється від 1 до m+1 виконуються дії (для всіх чарунок стовпця j). У циклі обробляються значення для m+1 колонок тому, що суми слід розрахувати і для чарунок, які є підсумками по рядках.
s = 0	Змінній s привласнюється значення 0.
For i = 1 To n	Для i що змінюється від 1 до n виконуються дії (для всіх чарунок рядка i).
s = s + Selection.Cells(i, j).Value	У змінній s накопичується сума значень чарунок певного стовпчика.
Next i	Кінець циклу обчислення підсумкового значення по стовпчику.
Selection.Cells(n + 1, j).Value = s	У чарунці з адресою – рядок n + 1 і колонка j (чарунка нижче останньої чарунки виділеного діапазону) виводиться значення суми, що обчислено.
Next j	Кінець циклу обчислення підсумкових значень по всіх стовпчиках.
End Sub	Кінець процедури.

Після створення процедури можна активізувати робочий лист, на якому створено кнопку “Розрахунок сум”, вийти з режиму конструктору, виділити будь-який діапазон чарунок і натиснути на кнопці “Розрахунок сум” для перевірки правильності виконання розрахунків.

Можна також створити кнопку для розрахунку підсумкових значень по таблиці, в яку введено дані для аналізу реалізації товарів. Таблиця має таку структуру:

Після натиснення на кнопці “Розрахунок” буде здійснено обчислення суми і відсотка відхилень від плану реалізації по кожній товарній групі, незалежно від кількості рядків у таблиці нижче заповнених даних буде додано підсумковий рядок для розрахунку загальних значень прогнозу щодо реалізації, фактичної реалізації, а також підсумкові значення відхилень. Далі здійснюватиметься розрахунок питомої ваги фактичної реалізації по кожній товарній групі в загальному обсязі реалізації. У результаті виконання процедури у чарунки електронної таблиці будуть записані не результати розрахунків, а формули обчислення показників.

З кнопкою “Розрахунок” слід пов’язати таку процедуру:

Дані в рядку коду	Коментар
Private Sub cmdR_Click()	Заголовок процедури, яка виконуватиметься після натискання на кнопці “Розрахунок”.
k = 0	Змінній k, яка використовується для підрахунку заповнених рядків таблиці, привласнюється значення 0.
For i = 4 To 60	Для і від 4 до 60 виконуються дії в циклі. 4 це номер першого заповненого рядка таблиці, 60 номер максимального можливого заповненого рядка.
If Cells(i, 3).Value <> 0 Then	Якщо для рядка і фактичне значення реалізації не дорівнює 0, то...
k = k + 1	Змінна k збільшується на 1.
Cells(i, 4).FormulaR1C1 = "=RC[-1]-RC[-2]"	У чарунку Cells(i, 4) – D4, D5... для виведення суми відхилення фактичної реалізації від прогнозного значення записується формула =Cі-Bi (=C4-B4, =C5-B5...). Операнди формули визначаються так: чарунка того ж рядка, що і визначена (D4, D5...), і стовпця з номером –1 – стовпчик ліворуч мінус чарунка того ж рядка і стовпця з номером –2 – на 2 стовпчика ліворуч.
Cells(i, 5).FormulaR1C1 = "=(RC[-2]-RC[-3])/RC[-3]*100"	у чарунку Cells(i, 5) для виведення відсотку відхилення фактичної реалізації від прогнозного значення записується формула записується формула =(Cі-Bі)/Bі*100 (=(C4-B4)/B4*100...).
Cells(i, 5).NumberFormat = "0.00"	Для чарунки, у якій виводиться відсоток відхилення, визначається числовий формат з двома знаками після десяткової крапки.
End If	Кінець оператору If.
Next i	Кінець циклу для розрахунку відхилень і визначення кількості заповнених рядків таблиці.
Cells(k + 4, 1).Formula = "Разом:"	У чарунці з адресою рядок - k + 4, стовпчик 1 виводиться текст "Разом:".
Cells(k + 4, 2).FormulaR1C1 = "=Sum(R4C2:R[-1]C2)"	У чарунку для розрахунку підсумкового значення по прогнозних сумах реалізації вводиться формула =СУММ(B4:B(k+4-1)). Адреси чарунок діапазону: R4C2 - $B$4, R[-1]C2 – чарунка попереднього рядка відносно чарунки (k + 4, 2) і другого стовпчика.
Cells(k + 4, 3).FormulaR1C1 = "=Sum(R4C3:R[-1]C3)"	У чарунку для розрахунку підсумкового значення по сумах фактичної реалізації вводиться формула =СУММ(С4:С(k+4-1)).
Cells(k + 4, 4).FormulaR1C1 = "=Sum(R4C4:R[-1]C4)"	У чарунку для розрахунку підсумкового значення по сумі відхилень вводиться формула ==СУММ(D4:D(k+4-1)).
Cells(k + 4, 5).FormulaR1C1 = "=(RC[-2]-RC[-3])/RC[-3]*100"	У чарунку для розрахунку підсумкового значення по відсотку відхилення вводиться формула =(C(k+4)-B(k+4))/B(k+4)*100.
Cells(k + 4, 5).NumberFormat = "0.00"	Для чарунки, у якій виводиться підсумок по відсотку відхилення, визначається числовий формат з двома знаками після десяткової крапки.
n = Trim(k + 4)	Змінній n привласнюється значення k + 4 у символьному форматі.
For i = 4 To k + 3	Для і від 4 до k+3 (для всіх інформаційних рядків таблиці) виконуються дії в циклі.
Cells(i, 6).FormulaR1C1 = "=RC[-3]/R" & n & "C3"	У чарунку чергового рядка поміщається формула =Ci/C(k+4).
Cells(i, 6).NumberFormat = "0.00%"	Для чарунок, в яких виводиться питома вага реалізації в загальному обсязі реалізації, встановлюється процентний формат.
Next i	Кінець циклу.
End Sub	Кінець процедури.

Після створення процедури можна скористатися кнопкою “Розрахунок”. Таблиця – приклад після застосування командної кнопки буде виглядати так: