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

При этом уточнились как сами коэффициенты, так и рассчитанные по ним значения.

14. 
    По данным столбцов E и F построить совместную диаграмму следующего вида (рис. 7.11)
    

Рис. 7.11. Результат сглаживания сезонной составляющей с помощью формулы (7.18)Ее внешний вид позволяет визуально оценить качество аппроксимации остатков синусоидой.

15. 
    В столбце G рассчитать остатки второго уровня, т.е. разность между остатками и синусоидой. Для этого в ячейку G1 вводится формула =E1-F1, которая затем копируется на весь столбец.
    
16. 
    По данным столбца строится диаграмма остатков второго уровня (рис. 7.12).
    

Рис. 7.12. Диаграмма остатков второго уровняЕсли регулярная составляющая была подобрана и рассчитана правильно и, если также правильно была рассчитана периодическая составляющая, то диаграмма должна иметь вид случайного недетерминированного ряда. Признаком случайности является значение суммы этих остатков. Она должна быть много меньшей, чем сумма остатков первого уровня. В данном случае она оказалась равной 0,002332, что намного меньше суммы остатков первого уровня (-0.97).

17. 
    Используя уравнения отдельных составляющих, можно составить общее уравнение ряда. В данном случае оно будет выглядеть следующим образом:
    

y=235,515+193,87*x+510,89*Sin(0,116+0,49*x).Полученное уравнение можно использовать для целей прогноза. Для этого достаточно подставить в него то значение Х, для которого мы хотим узнать значение Y.Литература

1. 
   Бухвалов, А.В. Финансовые вычисления для менеджеров : учеб. пособие / А. В. Бухвалов, В. В. Бухвалова. – 2-е изд., испр. и доп. – СПб. : Высш. шк. менеджмента, 2009. - 367 с.
   
2. 
   ГОСТ 34.601-90. Информационная технология. Комплекс стандартов на автоматизированные системы. Автоматизированные системы. Стадии создания. – М.: Стандартинформ, 2009. – 6 с.
   
3. 
   Каплан, А. В. Решение экономических задач на компьютере / Каплан А. В., Каплан В. Е, Мащенко М. В., Овечкина Е. В. – М. : ДМК Пресс, 2008 . – 600 с. : ил.
   
4. 
   Карлсберг, Конрад. Бизнес-анализ с использованием Excel. Решение бизнес-задач : Пер. с англ.: учеб. Пособие / К. Карлсберг. – 4-е изд., – М.: Издательский дом «Вильямс», 2012. – 576 с.: ил.
   
5. 
   Россия в цифрах, 2002 : краткий статистический сборник / Гос. ком. Рос. Федерации по статистике (Госкомстат России) . – М. : Госкомстат России, 2002. – 399 с. – 5–89476–103–4.
   
6. 
   Титоренко, Г. А. Информационные технологии в экономике. / Г. А. Титоренко. – 2-е изд., перераб. и доп. – М. : Юнити-Дана, 2008. – 463 с.
   
7. 
   Уокенбах, Джон. Excel 2010: профессиональное программирование на VBA. : Пер с англ. / Дж. Уокенбах. – М. : ООО «И. Д. Вильямс», 2012. – 944 с.: ил.
   
8. 
   Федеральный государственный образовательный стандарт высшего профессионального образования по направлению подготовки 080200 «Менеджмент». – М., 2010. – 31 с.
   
9. 
   Филимонова, Е. В. Информационные технологии в экономике / Филимонова Е. В. , Черненко Н. А. , Шубин А. С. – М. : Феникс, 2008. – 443 с.
   
10. 
    Цисарь, И. Ф. Компьютерное моделирование экономики / И. Ф. Цисарь, В. Г. Нейман – М.: «Издательство ДИАЛОГ-МИФИ», 2008. – 384 с.
    
11. 
    Чувашская Республика и регионы Приволжского федерального округа, 2004 : статистический сборник / Ком. гос. статистики Чуваш. Республики. – Чебоксары : Госкомстат Чуваш. Респ. , 2004. – 181 с.
    

ПРИЛОЖЕНИЯПриложение 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 TimerFor i = 1 To 100k = 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 ThenCells(i + 2, 2) = FamilyM(k1)Cells(i + 2, 3) = NameM(k2)ElseCells(i + 2, 2) = FamilyW(k1)Cells(i + 2, 3) = NameW(k2)End IfCells(i + 2, 4) = i * 100k = Int(1 + 4 * Rnd(Timer))Cells(i + 2, 6) = Otdel(k)Cells(i+2,7)=Int(35 + 100 * Rnd(Timer)) * 100Cells(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))

NextEnd 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	Васильев	Алексей	Васильева	Евгения