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

Статьи по теме: