Файл: Контрольная работа по теме Базы данных в Excel 72 IV. Макросы в ms excel 78 Макросы для автоматизации работ 78.doc

ПРИЛОЖЕНИЯ

Приложение 1

Технология генерации модельных данных
При выполнении лабораторных работ, связанных с базами данных, используются уже готовые и довольно значительные массивы информации.

Все эти данные являются модельными, т.е. не соответствуют никаким реальным данным и получены искусственным образом.

Поэтому настоящее приложение предназначено, прежде всего, для преподавателей ставящих соответствующие курсы информатики.

Но, изложенные здесь сведения, могут быть полезны и для студентов, выполняющих курсовые работы, связанные с использованием баз данных. При этом, если база данных формируется в Access, то заполнять ее конкретными данными проще всего в два этапа:

– сначала сгенерировать данные в Excel,

– затем (буквально в несколько щелчков мыши) импортировать их в Access.

Для получения данных возможно использование двух, практически равнозначных способов.

1. С помощью специально написанных макросов, которые после генерации данных удаляются из рабочей книги.

2. С использованием встроенных средств Excel.

Выбор метода генерации зависит от специфики создаваемой базы данных и вкуса пользователя.
Генерация данных с помощью макросов

Данный способ предполагает наличие у пользователя некоторых навыков программирования и знания VBA (Visual Basic for Application).

Для начала записи макроса выполняются команды:

Вид > Панели инструментов > Выбирается панель VisualBasic >

На панели выбирается «Редактор VisualBasic».

В редакторе выполняются команды:

Insert > Module

и затем

Insert > Procedure

В окне параметров процедуры необходимо задать только имя процедуры (например, Generate) и затем Ok.

В появившейся заготовке пишется необходимый набор команд.

В качестве примера приведен текст макроса для генерации учебной базы данных «Кадры»
PrivateSubGenerate ()

‘ Объявление массивов

DimFamilyM(10) AsString ‘Массив мужских фамилий

Dim NameM(10) As String ‘Массив

---

мужскихимен

Dim FamilyW(10) As String ‘Массивженскихфамилий

Dim NameW(10) As String ‘Массивженскихимен

DimOtdel(4) AsString ‘Массив наименований отделов

Dim Adress(9) As String ‘Массивадресов



‘ Присвоение элементам массивов конкретных значений

FamilyM(1) = "Иванов": FamilyM(2) = "Петров"

FamilyM(3) = "Сидоров": FamilyM(4) = "Кузнецов"

FamilyM(5) = "Андреев": FamilyM(6) = "Васильев"

FamilyM(7) = "Алексеев": FamilyM(8) = "Кузьмин"

FamilyM(9) = "Романов": FamilyM(10) = "Степанов"

FamilyW(1) = "Иванова": FamilyW(2) = "Петрова"

FamilyW(3) = "Сидорова": FamilyW(4) = "Кузнецова"

FamilyW(5) = "Андреева": FamilyW(6) = "Васильева"

FamilyW(7) = "Алексеева": FamilyW(8) = "Кузьмина"

FamilyW(9) = "Романова": FamilyW(10) = "Степанова"

NameM(1) = "Андрей": NameM(2) = "Петр"

NameM(3) = "Михаил": NameM(4) = "Алексей"

NameM(5) = "Денис": NameM(6) = "Владимир"

NameM(7) = "Александр": NameM(8) = "Дмитрий"

NameM(9) = "Вячеслав": NameM(10) = "Иван"

NameW(1) = "Мария": NameW(2) = "Светлана"

NameW(3) = "Любовь":NameW(4) = "Наталья":

NameW(5) = "Вероника":NameW(6) = "Евгения"

NameW(7) = "Елена": NameW(8) = "Людмила"

NameW(9) = "Надежда": NameW(10) = "Екатерина"

Otdel(1) = "Сбыта":Otdel(2) = "Снабжения"

Otdel(3) = "Плановый":Otdel(4) = "Производственный"
Adress(1)="ул. Лебедева":Adress(2)="ул. Заовражная"

Adress(3)="ул. Мира": Adress(4)="ул. Павлова"

Adress(5)="ул. Горького":Adress(6)="ул. Хевешская"

Adress(7)="ул. Ленина":Adress(8)="ул. Водопроводная"

Adress(9)="ул. Яковлева"
‘Заполнение шапки таблицы. Это можно было и просто напечатать во второй строке рабочего листа

Cells(2,2) = "Фамилия":Cells(2, 3) = "Имя":

Cells(2,4) = "Таб. №": Cells(2, 5) = "Пол"

Cells(2,6) = "Отдел": Cells(2, 7) = "Оклад"

Cells(2,8) = "Дата рождения":Cells(2,9) = "Дети"

Cells(2,10)= "Адрес": Cells(2, 11) = "Телефон"
‘ Цикл генерации

Randomize Timer

For i = 1 To 100

k = Int(2 * Rnd(Timer))

If k = 0 Then Cells(i + 2, 5) = "м" _

Else Cells(i + 2, 5) = "ж"

k1 = Int(1 + 10 * Rnd(Timer))

k2 = Int(1 + 10 * Rnd(Timer))

If k = 0 Then

Cells(i + 2, 2) = FamilyM(k1)

Cells(i + 2, 3) = NameM(k2)

Else

Cells(i + 2, 2) = FamilyW(k1)

Cells(i + 2, 3) = NameW(k2)

End If

Cells(i + 2, 4) = i * 100

k = Int(1 + 4 * Rnd(Timer))

Cells(i + 2, 6) = Otdel(k)

Cells(i+2,7)=Int(35 + 100 * Rnd(Timer)) * 100

Cells(i + 2, 8) = Int(1940 + 45 * Rnd(Timer))

Cells(i + 2, 9) = Int(3 * Rnd(Timer))

k = Int(1 + 9 * Rnd(Timer))

Cells(i + 2, 10) = Adress(k)

Cells(i+2,11)=Int(200000+700000*Rnd(Timer))

---

Next

End Sub
Готовый макрос можно запустить из редактора Visual Basic с помощью кнопки Run или нажав клавишу F5.
Генерация данных с помощью встроенных функций

Для пользователей, не обладающих навыками программирования, использование встроенных функций является наиболее простым методом получения больших объемов модельных данных.

Основой метода является функции генерации случайных чисел – СЛЧИС().

Она генерирует случайные числа из диапазона 0..1. Для генерации целых чисел из произвольного диапазона используется формула:
=А + ЦЕЛОЕ((В – А+1)*СЛЧИС()),
где А – нижняя граница необходимого диапазона;

В – верхняя граница диапазона;

ЦЕЛОЕ – имеющаяся в Excel функция округления дробных чисел.

В качестве примера рассмотрим поэтапное создание базы данных «Кадры».

1. Создаем шапку таблицы

	B	C	D	E	F	G	H	I	J	K
1
2	Таб. №	Фамилия	Имя	Пол	Отдел	Оклад	Дата рождения	Дети	Адрес	Телефон
3

2. В ячейки B3 и B4 вводятся значения 100 и 200, которые затем путем автозаполнения копируются на сто последующих строк.

3. В E3 вводится формула: =ЕСЛИ(ЦЕЛОЕ(2*СЛЧИС())=0;"м";"ж")

Смысл формулы заключается в следующем:

– генерируется случайное целое число (0 или 1);

---

– если это число равно 0, то пол мужской;

– иначе (т.е. это число равно 1), то пол – женский.

4. В стороне от формируемой таблицы печатаются пронумерованные списки наиболее распространенных фамилий и имен (мужских и женских).

		M	N	O	P	Q
1
2		1	Кузнецов	Андрей	Кузьмина	Екатерина
3		2	Степанов	Иван	Петрова	Светлана
4		3	Кузьмин	Дмитрий	Романова	Людмила
5		4	Сидоров	Михаил	Степанова	Надежда
6		5	Иванов	Денис	Сидорова	Любовь
7		6	Андреев	Владимир	Кузнецова	Мария
8		7	Петров	Петр	Иванова	Вероника
9		8	Романов	Александр	Алексеева	Елена
10		9	Алексеев	Вячеслав	Андреева	Наталья
11		10	Васильев	Алексей	Васильева	Евгения

---

5. В ячейку С3 вводится формула:
=ЕСЛИ(E3="м";

ВПР(ЦЕЛОЕ(1+10*СЛЧИС());$M$2:$Q$11;2);

ВПР(ЦЕЛОЕ(1+10*СЛЧИС());$M$2:$Q$11;4))
Смысл формулы заключается в следующем:

– если пол мужской, из списка фамилий с помощью функции ВПР берется случайная мужская фамилия;

– иначе берется женская фамилия.
6. Для формирования имен в ячейку D3 вводится аналогичная формула:

=ЕСЛИ(E3="м";

ВПР(ЦЕЛОЕ(1+10*СЛЧИС());$M$2:$Q$11;3);

ВПР(ЦЕЛОЕ(1+10*СЛЧИС());$M$2:$Q$11;5))
7. Заполнение колонок F и J производится практически аналогично.

В стороне от базы создается список отделов:

		M	N
14
15		1	Снабжения
16		2	Плановый
17		3	Сбыта
18		4	Производственный
19

В ячейку F3 вводится формула:
=ВПР(ЦЕЛОЕ(1+4*СЛЧИС());$M$15:$N$18;2)
В стороне от базы создается список адресов:

		P	Q
14
15		1	ул. Павлова
16		2	ул. Яковлева
17		3	ул. Ленина
18		4	ул. Горького
19		5	ул. Заовражная
20		6	ул. Хевешская
21		7	ул. Мира
22		8	ул. Водопроводная
23		9	ул. Лебедева
24

---