VBA-Урок 13.3. Використання масивів (Arrays) (Вправи)

Щоби попрактикуватись у використанні масивів, ми створимо нашу власну версію макроса, яку ми використовували для демонстрації переваги масивів у швидкості, крок за кроком...

Далі є початкова точка цієї вправи (ви побачите, що набір даних був зменшений до 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