Щоби попрактикуватись у використанні масивів, ми створимо нашу власну версію макроса, яку ми використовували для демонстрації переваги масивів у швидкості, крок за кроком...
Далі є початкова точка цієї вправи (ви побачите, що набір даних був зменшений до 1000 рядків):
Тут ви можете завантажити Excel файл з прикладом: arrays_exercise.xls
Ціль вправи: процедура повинна обробляти дані в наборі даних за допомогою циклу і порахувати кількість відповідей "ТАК" або "НІ" для кожного року і для кожного номера клієнта (або "ТАК" або "НІ", залежно від вибору користувача) і ввести цю кількість у вказану комірку робочому аркуші.
Завершіть наступний макрос, щоби зберегти дані з робочого аркуша «DS» в масив:
Sub actualize()
Dim last_row As Integer
'Останній рядок набору даних
'...
'Збереження набору даних в динамічному масиві
Dim array_db()
'...
End Sub
Ось один з прикладів вирішення:
Sub actualize()
Dim last_row As Integer
'Останній рядок бази даних
last_row = Sheets("DS").Range("A1").End(xlDown).Row
'Збереження набору даних в динамічному масиві
Dim array_db()
ReDim array_db(last_row - 2, 2)
For row_number = 2 To last_row
array_db(row_number - 2, 0) = Sheets("DS").Range("A" & row_number)
array_db(row_number - 2, 1) = Sheets("DS").Range("B" & row_number)
array_db(row_number - 2, 2) = Sheets("DS").Range("C" & row_number)
Next
End Sub
Це в основному повторює те, що ми робили на попередньому уроці...
Але зараз нам потрібно змінити наш макрос додаванням наступних дій:
Ось один з прикладів вирішення:
Sub actualize()
Dim last_row As Integer, search_value As String, insert_row As Integer, value_yes_no As String, rows_number As Integer
'Останній рядок бази даних
last_row = Sheets("DS").Range("A1").End(xlDown).Row
'Пошук значення (YES чи NO)
If Sheets("RES").OptionButton_yes.Value = True Then
search_value = "YES"
Else
search_value = "NO"
End If
'Кількість відповідей YES чи NO
rows_number = WorksheetFunction.CountIf(Sheets("DS").Range("C2:C" & last_row), search_value)
'Збереження набору даних в масив
Dim array_db()
ReDim array_db(rows_number - 1, 1)
insert_row = 0
For row_number = 2 To last_row
value_yes_no = Sheets("DS").Range("C" & row_number)
If value_yes_no = search_value Then
array_db(insert_row, 0) = Sheets("DS").Range("A" & row_number)
array_db(insert_row, 1) = Sheets("DS").Range("B" & row_number)
insert_row = insert_row + 1
End If
Next
End Sub
Пошук вибором користувача визначається на початку процедури наступним кодом:
'Пошук значення (YES чи NO)
If Sheets("RES").OptionButton_yes.Value = True Then
search_value = "YES"
Else
search_value = "NO"
End If
Ми використаємо функцію CountIF, щоби визначити кількість відповідей "ТАК" чи "НІ":
'Кількість відповідей YES чи NO
rows_number = WorksheetFunction.CountIf(Sheets("DS").Range("C2:C" & last_row), search_value)
Розмір масиву був змінений, щоби відповідати кількості відповідей "ТАК" чи "НІ" і зменшити до двох стовпців:
ReDim array_db(rows_number - 1, 1)
Тепер ці дані будуть збережені в масиві, коли його третій стовпець відповідає вибору користувача:
'Вставлення номеру в масив
insert_row = 0
'Обробка набору даних
For row_number = 2 To last_row
'Значення стовпця C (YES чи NO)
value_yes_no = Sheets("DS").Range("C" & row_number)
'Якщо значення відповідає вибору користувача, рядок зберігається в масиві
If value_yes_no = search_value Then
'Збереження значення в стовпець A
array_db(insert_row, 0) = Sheets("DS").Range("A" & row_number)
'Збереження значення в стовпець B
array_db(insert_row, 1) = Sheets("DS").Range("B" & row_number)
'Один рядок був збережений => номер вставлення в масиві збільшується на 1
insert_row = insert_row + 1
End If
Next
Наш масив містить лише ті дані, які нас цікавлять.
Все, що ам залишилось зробити, це:
Ось один з прикладів вирішення:
'Кількість відповідей "YES"/"NO"
For no_years = 2011 To 2026
For no_client = 1 To 30
counter = 0
For i = 0 To UBound(array_db)
If Year(array_db(i, 0)) = no_years And array_db(i, 1) = no_client Then
counter = counter + 1
End If
Next
Cells(no_years - 2009, no_client + 1) = counter
Next
Next
Вирішенн задачі з коментарями, що пояснюють це в деталях:
'Цикл для кожного рядка
For no_years = 2011 To 2026
'Цикл для кожного стовпця
For no_client = 1 To 30
'Скидання лічильника
counter = 0
'Обробка масиву
For i = 0 To UBound(array_db)
'Перевірка, що рядок в таблиці відповідає року та номеру клієнта
If Year(array_db(i, 0)) = no_years And array_db(i, 1) = no_client Then
'Якщо рік та номер клієнта відповідає, лічильник збільшується на 1
counter = counter + 1
End If
Next
'Після обробки масиву, підсумок вводиться у відповідну комірку
Cells(no_years - 2009, no_client + 1) = counter
Next
Next
І накінець, код усього нашого макроса:
Sub actualize()
Dim last_row As Integer, search_value As String, insert_row As Integer, value_yes_no As String, rows_number As Integer, counter As Integer
'Видалення вмісту
Range("B2:AE17").ClearContents
'Останній рядок в наборі даних
last_row = Sheets("DS").Range("A1").End(xlDown).Row
'Пошук значення (YES чи NO)
If Sheets("RES").OptionButton_yes.Value = True Then
search_value = "YES"
Else
search_value = "NO"
End If
'Кількість відповідей YES чи NO
rows_number = WorksheetFunction.CountIf(Sheets("DS").Range("C2:C" & last_row), search_value)
'Збереження значень в масив
Dim array_db()
ReDim array_db(rows_number - 1, 1)
insert_row = 0
For row_number = 2 To last_row
value_yes_no = Sheets("DS").Range("C" & row_number)
If value_yes_no = search_value Then
array_db(insert_row, 0) = Sheets("DS").Range("A" & row_number)
array_db(insert_row, 1) = Sheets("DS").Range("B" & row_number)
insert_row = insert_row + 1
End If
Next
'Підрахунок відповідей YES чи NO
For no_years = 2011 To 2026
For no_client = 1 To 30
counter = 0
For i = 0 To UBound(array_db)
If Year(array_db(i, 0)) = no_years And array_db(i, 1) = no_client Then
counter = counter + 1
End If
Next
Cells(no_years - 2009, no_client + 1) = counter
Next
Next
End Sub
Тут ви можете завантажити Excel файл з прикладом: arrays_exercise_completed.xls
