Технология генерации модельных данных
При выполнении лабораторных работ, связанных с базами данных, используются уже готовые и довольно значительные массивы информации.
Все эти данные являются модельными, т.е. не соответствуют никаким реальным данным и получены искусственным образом.
Поэтому настоящее приложение предназначено, прежде всего, для преподавателей ставящих соответствующие курсы информатики.
Но, изложенные здесь сведения, могут быть полезны и для студентов, выполняющих курсовые работы, связанные с использованием баз данных. При этом, если база данных формируется в Access, то заполнять ее конкретными данными проще всего в два этапа:
– сначала сгенерировать данные в Excel,
– затем (буквально в несколько щелчков мыши) импортировать их в Access.
Для получения данных возможно использование двух, практически равнозначных способов.
1. С помощью специально написанных макросов, которые после генерации данных удаляются из рабочей книги.
2. С использованием встроенных средств Excel.
Выбор метода генерации зависит от специфики создаваемой базы данных и вкуса пользователя.
Генерация данных с помощью макросов
Данный способ предполагает наличие у пользователя некоторых навыков программирования и знания VBA (Visual Basic for Application).
Для начала записи макроса выполняются команды:
Вид > Панели инструментов > Выбирается панель Visual Basic >
На панели выбирается «Редактор Visual Basic».
В редакторе выполняются команды:
Insert > Module
и затем
Insert > Procedure
В окне параметров процедуры необходимо задать только имя процедуры (например, Generate) и затем Ok.
В появившейся заготовке пишется необходимый набор команд.
В качестве примера приведен текст макроса для генерации учебной базы данных «Кадры»
Private Sub Generate ()
‘ Объявление массивов
Dim FamilyM(10) As String ‘Массив мужских фамилий
Dim NameM(10) As String ‘Массив мужских имен
Dim FamilyW(10) As String ‘Массив женских фамилий
Dim NameW(10) As String ‘Массив женских имен
Dim Otdel(4) As String ‘Массив наименований отделов
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 |
|
|
|
В ячейку J3 вводится формула:
=ВПР(ЦЕЛОЕ(1+10*СЛЧИС());$P$15:$Q$23;2)
8. Для заполнения колонок «Оклад», «Дата рождения», «Дети» и «Телефон»:
– в ячейку G3 вводится формула: =5000+1000*ЦЕЛОЕ(16*СЛЧИС());
– в ячейку H3 вводится формула: =ЦЕЛОЕ(3*СЛЧИС());
– в ячейку I3 вводится формула: =1945+ЦЕЛОЕ(50*СЛЧИС());
– в ячейку K3 вводится формула:
=100000+100000*ЦЕЛОЕ(10*СЛЧИС()).
Обратите внимание на числа в формулах – они определяют диапазоны генерации.
Для окладов генерируются числа из диапазона 5000..20000;
Для детей – числа из диапазона 0..2;
Для дат рождения –числа из диапазона 1945..1994;
Для телефонов – числа из диапазона 100000..900000.
9. Выделить все введенные в третью строку формулы и скопировать их на нужное количество строк таблицы.
Вы должны были обратить внимание на то, что после каждой манипуляции с данными их значения меняются. Это свойство функции СЛЧИС.
Чтобы избавиться от этого эффекта:
– выделяем всю таблицу и копируем ее в буфер;
– не снимая выделения произведем перекопирование данных командой Правка > Специальная ставка > Значения.
- Содержание
- VII. Статистические методы 167
- Введение
- I. Общие методы работы
- 1.1. Работа с формулами
- 1.1.1. Общие сведения
- Вычисления сложных выражений
- 1.1.2. Задание
- 1.2. Математические функции
- 1.2.1. Общие сведения
- 1.2.2. Пример
- 1.2.3. Варианты заданий
- 1.3. Вычисления с условиями
- 1.3.1. Общие сведения
- 1.4. Работа со справочниками
- 1.4.1. Общие сведения
- 1.4.2. Варианты заданий
- «Разносортица»
- «Маршрутное такси»
- «Гостиница»
- «Автовокзал»
- «Книжное издательство»
- «Продукты»
- «Коттеджи»
- «Гастроли»
- «Туристическое агентство»
- «Комплектующие»
- «Авиаперевозки»
- «Винный погребок»
- «Сберкасса»
- «Мебельная фабрика»
- 16. «Сага о таре»
- 1.5. Работа с диаграммами
- 1.5.1. Общие сведения
- 1.5.2. Задание на построение диаграммы
- 1. Изменение настроек параметров диаграммы:
- 3. Форматирование рядов данных и их элементов:
- 4. Форматирование осей диаграммы:
- 5. Форматирование сетки, стен и основания:
- 6. Форматирование легенды:
- 1.5.3. Варианты заданий
- 1.6. Собственные функции
- 1.6.1. Общие сведения
- 1.6.2. Общие сведения о Visual Basic for Excel
- Математические операции
- Математические функции
- 1.6.3. Варианты заданий
- II. Численные методы
- 2.1. Решение алгебраических уравнений Средство «Подбор параметра»
- 2.1.1. Общие сведения
- 2.1.2. Пример
- 2.1.3. Варианты заданий
- 2.2. Решение систем уравнений
- 2.2.1. Общие сведения
- 2.2.2. Реализация расчетов в Excel
- 2.2.3. Варианты заданий
- 2.3. Задачи оптимизации
- 2.3.1. Общие сведения
- 2.3.2. Пример
- 2.3.3. Варианты заданий
- III. Базы данных в ms Excel
- Каждое из последующих заданий необходимо выполнять на отдельном листе!!!
- Сортировка
- 3.1.1. Общие сведения
- 3.1.2. Варианты заданий
- Фильтрация данных
- 3.2.1. Общие сведения
- Варианты заданий
- Средство «Итоги»
- 3.3.1. Общие сведения
- Сводные таблицы
- 3.5. Функции для работы с базами данных
- 3.6. Консолидация данных
- 3.6.2. Варианты заданий
- 3.7. Контрольная работа по теме «Базы данных в Excel»
- 3.7.1. Указания
- 2. Скопируйте указанный файл в свою рабочую папку и вся дальнейшая работа должна производиться только с этой копией.
- 3.7.2. Варианты заданий
- Вариант 12
- Вариант 13
- Вариант 14
- Вариант 15
- IV. Макросы в ms Excel
- 4.1. Макросы для автоматизации работ
- 4.1.1. Пример
- 4.2. Вычислительные макросы
- 4.2.1. Пример 1. Расчет точки безубыточности
- 4.2.2. Пример 2. Моделирование процесса налогообложения [8]
- 4.3. Использование макросов для создания интерфейса
- V. Технология создания информационной системы средствами ms Excel
- 5.1. Постановка задачи
- 5.2. Требования к системе
- 5.3. Общая архитектура ис
- 5.3.1. Проектирование общей архитектуры
- 5.3.2. Создание общей архитектуры
- 5.3.2.1. Создание объектов ис
- 5.3.2.2. Организация переходов между объектами
- 5.3.2.3. Этапы создания интерфейса
- 5.4. Организация работы с базой данных
- 5.4.1. Заполнение таблиц модельными данными
- 5.4.2. Работа с данными
- 5.4.3. Сортировка
- 5.4.4. Поиск данных
- 5.4.5. Отчеты
- 5.4.5.1. Использование функций
- 5.4.5.2. Использование сводных таблиц
- 5.4.5.3. Использование элементов управления
- 5.4.5.5. Использование встроенных функций
- Функция должна быть в англоязычном варианте.
- 5.4.5.6. Варианты заданий
- 5.4.6. Расчет заработной платы
- 5.4.6.1. Постановка задачи
- 5.4.6.2. Интерфейс расчета заработной платы
- 5.4.6.3. Реализация расчетов
- VI. Экономические расчеты
- 6.1. Задачи на проценты
- 6.1.1. Общие сведения
- 6.1.2. Пример.
- 6.1.3. Варианты заданий
- 6.2. Финансовые функции
- 6.2.1. Общие сведения
- Бс(Ставка, Кпер, Плт, Пс, Тип).
- 6.2.3. Варианты заданий
- 6.3. Анализ межотраслевого баланса (модель Леонтьева)
- Основные понятия
- Математическая модель межотраслевого баланса
- 6.3.4. Варианты заданий
- 6.4. Задача об эквивалентности ставок [1]
- 6.4.1. Основные формулы
- 6.4.2. Постановка задачи
- 6.4.3. Варианты заданий
- 6.5. Методы анализа проектов (использование средства «Подбор параметра»)
- 6.5.1. Термины и определения
- 6.5.2. Примеры
- Варианты заданий
- 6.6. Выбор оптимального портфеля инвестиций
- 6.6.1. Основные определения
- 6.6.2. Пример
- 6.6.3. Варианты заданий
- 6.7. Вычисление налогов
- 6.7.1. Предварительные замечания
- 6.7.2. Пример.
- 6.7.3. Варианты заданий
- 6.8. Моделирование динамических процессов
- 6.8.1. Общие сведения
- 6.8.2. Порядок выполнения работы
- 6.8.3. Пример
- Результаты должны отражать основные закономерности процесса
- 6.8.4. Варианты заданий
- Производство в условиях постоянного спроса
- Конкуренция
- Сезонное производство
- Рыночные отношения
- Взаимопоставки
- Цены в условиях ограниченного объема выпуска
- Северный завоз
- Два пароходства
- Последовательные перевозки
- Антимонопольная система
- Конъюнктура
- Количество информации в Интернет
- Валютная интервенция
- Реклама
- VII. Статистические методы
- 7.1. Определение характеристик случайных величин
- 7.1.1. Содержание работы
- 7.1.2. Варианты заданий
- 7.2. Дисперсионный анализ
- 7.2.1. Общие сведения
- 7.2.1. Пример
- 7.2.3. Методы, применяемые после дисперсионного анализа
- 7.2.4. Варианты заданий
- 7.3. Регрессионный анализ
- 7.3.1.Общие сведения
- 7.3.2. Порядок выполнения работы
- 7.3.3. Проверка уравнения регрессии на адекватность
- 7.3.4. Использование уравнения для прогноза
- 7.4. Кластерный анализ
- 7.4.1. Общие положения.
- 7.4.2. Примеры
- 7.4.3. Формализация процесса кластеризации
- 7.4.4. Порядок выполнения работы
- 7.4.5. Задания
- 7.5. Анализ временных рядов
- 7.5.1. Общие сведения
- 7.5.2. Пример
- Литература
- Приложения
- Технология генерации модельных данных
- Приложение 2 Районы и города Чувашии в цифрах [9]
- Тексты макросов Текст макроса для кластерного анализа
- Текст макроса для решения систем дифференциальных уравнений
- Текст макроса для генерации временного ряда
- Приложение 4 Транспорт и связь
- Статистические данные по регионам рф [5]