Aby poćwiczyć korzystanie z tablic, utworzymy własną wersję makra, którego użyliśmy do zademonstrowania, krok po kroku, szybkości stosowania tablic...
Oto punkt wyjścia tego ćwiczenia (zobaczysz, że zbiór danych został zredukowany do 1000 wierszy):
Tutaj możesz pobrać przykładowy plik Excel: arrays_exercise.xls
Cel ćwiczenia: Procedura powinna przeglądać dane w zbiorze danych i policzyć liczbę odpowiedzi TAK lub NIE dla każdego roku i dla każdego numeru klienta (TAK lub NIE w zależności od wyboru użytkownika) i wpisać tę liczbę do określonej komórki arkusz.
Wykonaj poniższe makro, aby zapisać dane z arkusza „DS” do tablicy:
Sub actualize()
Dim last_row As Integer
'Ostatni wiersz zbioru danych
'...
'Zapisywanie zestawu danych w tablicy dynamicznej
Dim array_db()
'...
End Sub
Oto jedno przykładowe rozwiązanie:
Sub actualize()
Dim last_row As Integer
'Ostatnia linia bazy danych
last_row = Sheets("DS").Range("A1").End(xlDown).Row
'Zapisywanie zestawu danych w tablicy dynamicznej
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
Zasadniczo powtarza to, co zrobiliśmy na poprzedniej lekcji…
Ale teraz musimy zmodyfikować nasze makro, dodając następujące akcje:
Oto jedno przykładowe rozwiązanie:
Sub actualize()
Dim last_row As Integer, search_value As String, insert_row As Integer, value_yes_no As String, rows_number As Integer
'Ostatnia linia bazy danych
last_row = Sheets("DS").Range("A1").End(xlDown).Row
'Wartość wyszukiwania (YES lub NO)
If Sheets("RES").OptionButton_yes.Value = True Then
search_value = "YES"
Else
search_value = "NO"
End If
'Liczba odpowiedzi YES lub NO
rows_number = WorksheetFunction.CountIf(Sheets("DS").Range("C2:C" & last_row), search_value)
'Zapisywanie zestawu danych do tablicy
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
Wyszukiwanie według wyboru użytkownika określane jest na początku procedury następującym kodem:
'Wartość wyszukiwania (YES lub NO)
If Sheets("RES").OptionButton_yes.Value = True Then
search_value = "YES"
Else
search_value = "NO"
End If
Za pomocą funkcji CountIF określimy liczbę odpowiedzi „TAK” lub „NIE”:
'Liczba odpowiedzi YES lub NO
rows_number = WorksheetFunction.CountIf(Sheets("DS").Range("C2:C" & last_row), search_value)
Rozmiar tablicy został zmieniony tak, aby pasował do liczby odpowiedzi TAK lub NIE i zredukowany do dwóch kolumn:
ReDim array_db(rows_number - 1, 1)
Dane te będą teraz przechowywane w tablicy, gdy jej trzecia kolumna będzie odpowiadać wyborowi użytkownika:
'Wstawianie liczby do tablicy
insert_row = 0
'Przetwarzanie zbioru danych
For row_number = 2 To last_row
'Wartość kolumny C (YES lub NO)
value_yes_no = Sheets("DS").Range("C" & row_number)
'Jeśli wartość odpowiada wyborowi użytkownika, ciąg znaków jest przechowywany w tablicy
If value_yes_no = search_value Then
'Zapisanie wartości do kolumny A
array_db(insert_row, 0) = Sheets("DS").Range("A" & row_number)
'Zapisanie wartości do kolumny B
array_db(insert_row, 1) = Sheets("DS").Range("B" & row_number)
'Jeden wiersz został zapisany => numer wstawienia w tablicy jest zwiększany o 1
insert_row = insert_row + 1
End If
Next
Nasza tablica zawiera tylko te dane, które nas interesują.
Jedyne co pozostało do zrobienia to:
Oto jedno przykładowe rozwiązanie:
'Liczba odpowiedzi „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
Rozwiązany problem z komentarzami wyjaśniającymi go szczegółowo:
'Pętla dla każdego wiersza
For no_years = 2011 To 2026
'Pętla dla każdej kolumny
For no_client = 1 To 30
'Reset licznika
counter = 0
'Przetwarzanie tablic
For i = 0 To UBound(array_db)
'Sprawdzenie, czy wiersz w tabeli odpowiada rokowi i numerowi klienta
If Year(array_db(i, 0)) = no_years And array_db(i, 1) = no_client Then
'Jeśli rok i numer klienta są zgodne, licznik zwiększa się o 1
counter = counter + 1
End If
Next
'Po przetworzeniu tablicy wynik jest wprowadzany do odpowiedniej komórki
Cells(no_years - 2009, no_client + 1) = counter
Next
Next
I na koniec kod całego naszego makra:
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
'Usuwanie treści
Range("B2:AE17").ClearContents
'Ostatni wiersz w zestawie danych
last_row = Sheets("DS").Range("A1").End(xlDown).Row
'Wartość wyszukiwania (YES lub NO)
If Sheets("RES").OptionButton_yes.Value = True Then
search_value = "YES"
Else
search_value = "NO"
End If
'Liczba odpowiedzi YES lub NO
rows_number = WorksheetFunction.CountIf(Sheets("DS").Range("C2:C" & last_row), search_value)
'Zapisywanie wartości w tablicy
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
'Liczenie odpowiedzi YES lub 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
Tutaj możesz pobrać przykładowy plik Excel: arrays_exercise_completed.xls
