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

Це в основному повторює те, що ми робили на попередньому уроці...

Але зараз нам потрібно змінити наш макрос додаванням наступних дій:

Визначити вибір користувача ("ТАК" чи "НІ");

Порахувати кількість відповідей "ТАК" чи "НІ" в наборі даних, для того щоби визначити розмір масиву (Redim);

Зберігаємо тільки ті рядки з набору даних, що містять відповіді "ТАК" чи "НІ" в масиві (це означає, що не потрібно зберігати дані з третього стовпця).

Ось один з прикладів вирішення:

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

Наш масив містить лише ті дані, які нас цікавлять.

Все, що ам залишилось зробити, це:

Обробити кожен елемент таблиці на робочому аркуші "RES", використовючи 2 цикла (така сама ідея, як в праві з шахматною дошкою);

Вставити загальну кількість входжень на цьому аркуші для кожного року в розрізі кожного номера клієнта для кожної комірки.

Ось один з прикладів вирішення:

'Кількість відповідей "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

Статті по темі:

  • VBA-Урок 13.2. Використання масивів (Arrays) (Продовження)
  • VBA-Урок 14.1. Використання Excel функцій