Ćwiczenie 7
Zagadnienia:
-
Generowanie tabeli danych na podstawie tabeli 2-wymiarowej.
-
Wyszukiwanie i wypisywanie elementów określonych warunkiem za pomocą funkcji tablicowej VBA.
Ćwiczenie to dotyczy arkusza sprzedaz herbaty.xlsm przedstawionego poniżej.
rys.1
Zadanie 1
Celem zadania jest zaprojektowanie makra, które w nowym arkuszu o nazwie Arkusz2 wygeneruje tabelę danych o strukturze przedstawionej na rys.2 na bazie danych zgromadzonych w Arkusz1 (rys.1). Dla każdej wartości kilogramów zamówionej herbaty makro powinno odczytać z tego samego wiersza nazwisko pracownika i z tej samej kolumny nazwę herbaty i zapisać te trzy dane w poszczególnych wierszach nowej tabeli. W wygenerowanej tabeli powinno być zatem tyle wierszy ile jest niezerowych liczb określających ilość sprzedanej herbaty.

n
rys.2 rys.3
Aby zrealizować powyższe zadanie wykonaj następujące polecenia:
-
W arkuszy kalkulacyjnym wprowadź nazwy herbata, pracownik, ilość zgodnie ze schematem przedstawionym na rys.3.
-
W arkuszu Arkusz2 w komórkach A1,B1,C1 wpisz odpowiednio: Pracownik, nazwa herbaty i ilość.
-
W edytorze VBA napisz makro wypisz_tabele , które powinno zawierać:
-
deklarację zmiennych określających zakresy: herbata, pracownik, ilość oraz tabela,
-
powiązanie tych zmiennych z zakresami arkusza za pomocą instrukcji Set , przy czym powiązanie zmiennej tabela można zrobić albo poprzez odwołanie się do odpowiedniej, wcześniej zdefiniowanej nazwy zakresu komórek w arkusz2 albo też wprost:
Set tabela = Worksheets("Arkusz2").Range("a2")
-
deklarację zmiennych i,j,n typu integer, indeksujących odpowiednio wiersze i kolumny tabeli wyjściowej i wiersze tabeli docelowej,
-
blok pętli przebiegających po wszystkich komórkach zakresu ilość. W bloku tym dla n-tego wiersza w zakresie tabela należy określić wartości: tabela(n,1), tabela(n,2) oraz tabela(n,3) na podstawie danych pracownik(…), herbata(…),ilość(…,…).
Sposób zapisu danych przedstawiony w tabeli danych w Arkusz2 jest jednym z najbardziej rozpowszechnionych formatów gromadzenia danych charakterytycznym m.in. dla tzw. relacyjnych systemów baz danych. W samym Excelu jest wiele narzędzi operujących na tego typu tabelach do których m.in. należy raport tabeli przestawnej.
|
Zadanie 2
W arkuszu kalkulacyjnym wybierz na wstążce Wstawianie przycisk Tabela przestawna i postępując wg instrukcji kreatora w nowym arkuszu przekształć dane zebrane w Arkuszu2 do postaci z Arkusz1.
Zadanie 3
Należy zaprojektować funkcję tablicową, która wypisuje wszystkie dane dotyczące sprzedaży w formie analogicznej jak w zadaniu 1 (rys.2) ale tylko dla określonej przez parametr ilości herbaty. Na rys.4 pokazano projekt nagłówka takiej funkcji, a także schematycznie przedstawiono sposób przekazywania danych poprzez argumenty funkcji.
Uwaga: po argumencie ile_kg występuje spacja + podkreślenie co oznacza kontynuację instrukcji w następnej linii.
W projekcie tej funkcji zadeklaruj dynamiczną tablicę o nazwie tabela o ilość.Count wierszach i 3 kolumnach. Wypełnianie tej tablicy zaprogramuj podobnie jak w zadaniu 1. Użyj tej funkcji tablicowej w arkuszu w pustym zakresie komórek o szerokości 3 kolumn i ok. 20 wierszy. Dopisz odpowiednie nagłówki i opisy nad wygenerowaną tabelą wzorując się na rys.4.
Zadanie 4
Napisz funkcję podobną do tej z poprzedniego zadania, która oprócz nazwiska, nazwy herbaty i ilości wypisuje w czwartej kolumnie cenę sprzedanej herbaty.
Zadanie 5
Zaprojektuj funkcję tablicową, która w pięciu kolejnych komórkach wypisuje 5 maksymalnych i wzajemnie różnych wartości sprzedanej herbaty. Uwaga: można wykorzystać dostępną w VBA funkcję Excela WorksheetFunction.Large. Pomoc na temat tej funkcji można uzyskać wypisując jej nazwę + F1.
Zadanie 6
Zaprojektuj funkcję tablicową, która w podobny sposób jak w zadaniu 3 wypisuje dane dotyczące sprzedaży, przy czym należy wypisać te dane, dla których ilość sprzedanej herbaty osiąga trzy maksymalne wartości.
Zadanie 7
Treść jak zadanie 6 tylko zamiast maksymalne wartości wstawiamy minimalne i niezerowe wartości.
Zadanie domowe
Napisz funkcję tablicową, która dla wskazanego w argumencie funkcji pracownika podaje listę sprzedaży herbat z podaniem ich ilości.
Dąbrowski
|
|
|
Górecki
|
|
|
Grabowski
|
|
Assam
|
55 kg
|
|
Black Lychee
|
10 kg
|
|
Assam
|
70 kg
|
Black Lychee
|
20 kg
|
|
Ceylon
|
35 kg
|
|
Ceylon
|
15 kg
|
Ceylon
|
30 kg
|
|
Chunmee
|
15 kg
|
|
Chunmee
|
50 kg
|
Chunmee
|
50 kg
|
|
Darjeeling
|
110 kg
|
|
Darjeeling
|
65 kg
|
Darjeeling
|
115 kg
|
|
Dragonwell
|
20 kg
|
|
Dragonwell
|
35 kg
|
Dragonwell
|
60 kg
|
|
Gunpowder
|
45 kg
|
|
Gunpowder
|
15 kg
|
Gunpowder
|
35 kg
|
|
Gyokuru
|
15 kg
|
|
Gyokuru
|
40 kg
|
Gyokuru
|
45 kg
|
|
Jasmine
|
25 kg
|
|
Jasmine
|
15 kg
|
Jasmine
|
10 kg
|
|
Keemun
|
50 kg
|
|
Keemun
|
130 kg
|
Keemun
|
115 kg
|
|
Lapsang Souchong
|
65 kg
|
|
Oolong
|
50 kg
|
Lapsang Souchong
|
35 kg
|
|
Oolong
|
60 kg
|
|
Pi Lo Chun
|
45 kg
|
Oolong
|
60 kg
|
|
Pi Lo Chun
|
25 kg
|
|
Russian Blend
|
55 kg
|
Pi Lo Chun
|
15 kg
|
|
Russian Blend
|
90 kg
|
|
-
|
-
|
Russian Blend
|
55 kg
|
|
-
|
-
|
|
-
|
-
|
|