VBA-Lecția 13.3. Utilizarea matricelor (Arrays) (Exerciții)

Pentru a exersa utilizarea matricelor, vom crea propria noastră versiune a macrocomenzii pe care am folosit-o pentru a demonstra avantajul de viteză al matricelor, pas cu pas...

Iată punctul de pornire pentru acest exercițiu (veți vedea că setul de date a fost redus la 1000 de rânduri):

Aici puteți descărca fișierul exemplu Excel: arrays_exercise.xls

Obiectivul exercițiului: Procedura ar trebui să parcurgă datele din setul de date și să numere numărul de răspunsuri DA sau NU pentru fiecare an și pentru fiecare număr de client (fie DA sau NU în funcție de alegerea utilizatorului) și să introducă această cantitate în celula specificată a fisa de lucru.

Completați următoarea macrocomandă pentru a salva datele din foaia de lucru „DS” într-o matrice:

Sub actualize()
     Dim last_row As Integer
     
     'Ultimul rând al setului de date
     '...
        
     'Salvarea unui set de date într-o matrice dinamică
     Dim array_db()
     '...
        
End Sub

Iată un exemplu de soluție:

Sub actualize()
     Dim last_row As Integer
     
     'Ultima linie a bazei de date
     last_row = Sheets("DS").Range("A1").End(xlDown).Row

     'Salvarea unui set de date într-o matrice dinamică
     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

Acest lucru repetă practic ceea ce am făcut în lecția anterioară...

Dar acum trebuie să ne modificăm macrocomandă adăugând următoarele acțiuni:

Determinați alegerea utilizatorului („DA” sau „NU”);

Numărați numărul de răspunsuri „DA” sau „NU” din setul de date pentru a determina dimensiunea matricei (Redim);

Salvăm doar acele rânduri din setul de date care conțin răspunsurile „DA” sau „NU” în matrice (asta înseamnă că nu este nevoie să salvăm datele din a treia coloană).

Iată un exemplu de soluție:

Sub actualize()
     Dim last_row As Integer, search_value As String, insert_row As Integer, value_yes_no As String, rows_number As Integer
     
     'Ultima linie a bazei de date
     last_row = Sheets("DS").Range("A1").End(xlDown).Row

     'Valoarea de căutare (YES sau NO)
     If Sheets("RES").OptionButton_yes.Value = True Then
         search_value = "YES"
     Else
         search_value = "NO"
     End If
     
     'Numărul de răspunsuri YES sau NO
     rows_number = WorksheetFunction.CountIf(Sheets("DS").Range("C2:C" & last_row), search_value)
     
     'Salvarea unui set de date într-o matrice
     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

Căutarea după alegerea utilizatorului este determinată la începutul procedurii de următorul cod:

 'Valoarea de căutare (YES sau NO)
 If Sheets("RES").OptionButton_yes.Value = True Then
     search_value = "YES"
 Else
     search_value = "NO"
 End If

Vom folosi funcția CountIF pentru a determina numărul de răspunsuri „DA” sau „NU”:

 'Numărul de răspunsuri YES sau NO
 rows_number = WorksheetFunction.CountIf(Sheets("DS").Range("C2:C" & last_row), search_value)

Matricea a fost redimensionată pentru a se potrivi cu numărul de răspunsuri DA sau NU și a fost redusă la două coloane:

ReDim array_db(rows_number - 1, 1)

Aceste date vor fi acum stocate în matrice atunci când a treia sa coloană se potrivește cu selecția utilizatorului:

 'Inserarea unui număr într-o matrice
 insert_row = 0

 'Prelucrarea setului de date
 For row_number = 2 To last_row
     'Valoarea coloanei C (YES sau NO)
     value_yes_no = Sheets("DS").Range("C" & row_number)
     
     'Dacă valoarea se potrivește cu selecția utilizatorului, șirul este stocat în matrice
     If value_yes_no = search_value Then
         'Salvarea valorii în coloana A
         array_db(insert_row, 0) = Sheets("DS").Range("A" & row_number)
         'Salvarea valorii în coloana B
         array_db(insert_row, 1) = Sheets("DS").Range("B" & row_number)
         'Un rând a fost salvat => numărul de inserare în matrice este incrementat cu 1
         insert_row = insert_row + 1
     End If
 Next

Matricea noastră conține doar datele care ne interesează.

Tot ce mai rămâne de făcut este:

Procesați fiecare element al tabelului pe foaia de lucru „RES” folosind 2 bucle (aceeași idee ca și exercițiul de șah);

Introduceți numărul total de apariții pe această foaie pentru fiecare an după fiecare număr de client pentru fiecare celulă.

Iată un exemplu de soluție:

 'Numărul de răspunsuri "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

Problemă rezolvată cu comentariile care o explică în detaliu:

 'Bucla pentru fiecare rând
 For no_years = 2011 To 2026
 
     'Buclă pentru fiecare coloană
     For no_client = 1 To 30
         'Resetarea contorului
         counter = 0
         
         'Prelucrare matrice
         For i = 0 To UBound(array_db)
             'Verificarea ca rândul din tabel să corespundă cu anul și numărul de client
             If Year(array_db(i, 0)) = no_years And array_db(i, 1) = no_client Then
                 'Dacă anul și numărul clientului se potrivesc, contorul crește cu 1
                 counter = counter + 1
             End If
         Next
         
         'După procesarea matricei, rezultatul este introdus în celula corespunzătoare
         Cells(no_years - 2009, no_client + 1) = counter
     Next
 Next

Și, în sfârșit, codul pentru întreaga noastră macrocomandă:

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
     
     'Ștergerea conținutului
     Range("B2:AE17").ClearContents
     
     'Ultimul rând din setul de date
     last_row = Sheets("DS").Range("A1").End(xlDown).Row

     'Valoarea de căutare (YES sau NO)
     If Sheets("RES").OptionButton_yes.Value = True Then
         search_value = "YES"
     Else
         search_value = "NO"
     End If
     
     'Numărul de răspunsuri YES sau NO
     rows_number = WorksheetFunction.CountIf(Sheets("DS").Range("C2:C" & last_row), search_value)
     
     'Salvarea valorilor într-o matrice
     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
     
     'Numărarea răspunsurilor YES sau 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

Aici puteți descărca fișierul exemplu Excel: arrays_exercise_completed.xls