Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Разработка Windows-приложения по формированию типовых форм документов.
Цель работы: овладеть навыками технологией автоматизировать процесс преобразования первичной информации в выходные формы документов; проведения статистического анализа, средствами табличного процессора MS Excel.
Задание
Порядок выполнения работы:
При выполнении Задания №1 необходимо определить объект исследования. При этом нужно учесть специфику функционирования выбранного объекта и определить цель автоматизации. На этом этапе необходимо руководствоваться основополагающими и вспомогательными принципами проектирования информационных систем. В ходе разработки необходимо выделить:
На основании выделенной предметной области, производится формирование макета информационных таблиц исходных данных.
При расчете основных экономических показателей применяют определенную экономико-математическую модель расчета основных показателей при учете выделенной цели автоатизации.
Для более удобного пользования данной автоматизированной системой необходимо использовать имена в рабочих книгах MS Excel.
При расчете основных экономических показателей применяют определенную экономико-математическую модель расчета основных показателей при учете выделенной цели автоматизации.
Для более удобного пользования данной автоматизированной системой необходимо использовать имена в рабочих книгах MS Excel.
Имя может быть присвоено любому объекту MS Excel. Присвоение имен ячейкам позволяет:
Пример выполнения:
Задача: Выполнить построение вычислений с использованием имен ячеек.
Решение:
Рис.1. Исходные данные
в ячейку Е5 вводим =С5*D5 и «протаскиваем» формулу вниз для всех наименований услуг;
в ячейку F4 вводим =D4+E4 и «протаскиваем» формулу. (Вспомним рациональную технологию ввода формулы: вводим знак = , щелчком мыши выбираем ячейку D4 , вводим знак + , щелчком мыши выбираем ячейку E4 , нажимаем «ENTER»)
Рис.2. Поле ввода имен
Любому выделенному объекту можно дать имя непосредственным вводом в поле имен (по окончании ввода нажать «Enter»).
Рис.3. Применение имен
При расчете основных показателей, которые рассчитываются с помощью данных не только с данного листа, но и с других рабочих листов, необходимо использовать ссылки на эти ячейки, а не вводить данные вручную. Это позволит легко вносить новые и изменять старые данные в ячейках.
Пример выполнения:
Задача: Выполнить построение вычислений с использованием ссылок на необходимые ячейки.
Решение:
Рис. 4. Ввод начальных данных.
Рис. 5. Расчетная таблица.
Установим курсор в ячейку А4, введем знак «=», активизируем Лист1, ячейку А4 и нажмем клавишу Enter. В ячейке А4 появиться формула =Лист1!A4, «протаскиваем» формулу вниз до ячейки А8 и вправо на одну ячейку.
Установим курсор в ячейку С5, введем знак «=», активизируем Лист1, ячейку С5, введем знак «*», активизируем ячейку D5 и нажмем клавишу Enter. В ячейке С4 появиться формула =Лист1!C5*Лист1!D5 , «протаскиваем» формулу вниз до окончания таблицы.
Автоматизированное решение задач статистического анализа
Microsoft Exсel предполагает широкий диапазон средств для анализа статистических данных. В Microsoft Exсel используются как встроенные статистические функции для проведения несложного анализа данных, так и функции из «Пакета анализа», являющегося надстройкой в Excel.
«Пакет анализа» используется для:
При полной установки Microsoft Exсel для получения доступа к требуемой функции «пакета анализа» следует выполнить:
В случае отсутствия команды «Анализ данных» в пункте меню «Сервис» необходимо выполнить настройку:
Статистический анализ включает рассмотрение следующих лабораторных работ:
«Корреляционный анализ»
Краткая теория:
Корреляция представляет собой меру зависимости переменных. Простая линейная корреляция (Пирсона R) определяет степень, с которой значения двух переменных «пропорциональны» друг другу. Значение коэффициента корреляции не зависит от масштаба измерения. Коэффициенты корреляции изменяются в пределах о -1.00 до +1.00.
Корреляционный анализ дает возможность установить, ассоциированы ли наборы данных по величине:
Значимость определенного коэффициента корреляции зависит от объема выборок.
Пример выполнения:
Задача: Установит зависимость между плановым и фактическим выпуском продукции у фирмы производителя за смену.
Решение:
Рис. 6 Ввод начальных данных
Рис.7 Ввод данных для корреляционного анализа
|
А |
В |
C |
D |
|
|
1 |
выпуск продукции, руб. |
|||
|
2 |
№ смены |
план |
фактически |
|
|
3 |
1 смена |
5000 |
5100 |
|
|
4 |
2 смена |
4900 |
4800 |
|
|
5 |
3 смена |
4950 |
5000 |
|
|
6 |
Столбец 1 |
Столбец 2 |
||
|
7 |
Столбец 1 |
1 |
||
|
8 |
Столбец 2 |
0,981980506 |
1 |
Рис. 8 Результат корреляционного анализа с применением пакета «Анализ данных».
Коэффициент корреляции Пирсона (R) представляет собой меру линейной зависимости двух переменных. Если возвести его в квадрат, то полученное значение коэффициента детерминации (R2) представляет долю вариации, общую для двух переменных, т.е. «степень» зависимости или связанности двух переменных.
Из представленной задачи, очевидно, что эффективная деятельность предприятия напрямую зависит от степени выполнения плана выпуска продукции, коэффициент детерминации равен 0,9641.
«Трендовый анализ»
Краткая теория:
Трендовый анализ представляет дополнительную характеристику для рядов данных в диаграмме с областями, линейчатой диаграмме, гистограмме, графике или точечной диаграмме. Трендовый анализ представляет линию регрессии, которая аппроксимирует точки данных, или линии скользящего среднего.
Пример выполнения:
Задача: Выполнить трендовый анализ для производства, выпускающего молочную продукцию с 1999-2004 гг., используя следующие параметры:
Решение:
1. Построим таблицу, показывающую выпуск продукции за 1999-2004 гг. в ячейках А1:Н2 рис. 9:
|
|
А |
В |
C |
D |
E |
F |
G |
H |
|
1 |
Продукция |
Ед.изм. |
1999 |
2000 |
2001 |
2002 |
2003 |
2004 |
|
2 |
Молочные издлия |
л |
400 |
450 |
455 |
445 |
470 |
500 |
Рис.9 Ввод начальных данных
2. Построим гистограмму (рис. 10):
Рис. 10. Гистограмма выпуска продукции за 1999-2004 год
3. Выделить ряд данных на диаграмме и выбрать команду «Добавить линию тренда» в локальном меню (вызов правой кнопкой мыши).
4. Задаем тип линии тренда и входные параметры в появившемся диалоговом окне «Линия тренда», показанном на рис.11
Рис. 11. Диалоговое окно «Линия тренда»
5. Полученный трендовый анализ показан на рис 12.
Рис. 12. Трендовый анализ гистограммы.
«Структурирование листов. Подбор параметра. Поиск оптимального решения»
Краткая теория:
Подбор параметра
Команда «Подбор параметра» пункта меню «Сервис» позволяет определить неизвестное значение, которое будет выдавать желаемый результат.
Поиск оптимального решения
Необходимость использования метода моделирования коммерческой деятельности определяется тем, что многие объекты или проблемы (поиск поставщика, получение максимальной прибыли при минимальных затратах и т.д.) непосредственно исследовать невозможно, или это исследование требует много времени и средств.
При решении этих проблем необходимо знать ответы на дополнительные вопросы, которые можно сгруппировать в две группы:
Без ответа на поставленные вопросы невозможно принять обоснованного решения, которое требует:
Большинство задач коммерческой деятельности поиска оптимального решения относятся к классу задач линейного программирования.
Задачи линейного программирования записываются следующим образом:
, , ,
Множество всех допустимых решений задачи линейного программирования называется областью допустимых решений (ОДР).
Допустимое решение задачи линейного программирования, максимизирующее или минимизирующее целевую функцию называется оптимальным.
Задачу линейного программирования можно решать аналитическими и графическими методами. Аналитические методы представляют собой последовательность вычислений по некоторым правилам, являются основой для решения задачи на компьютере. Их единственный недостаток заключается в том, что в отличие от графических методов, они совершенно не наглядны. Графические методы достаточно наглядны, но они пригодны лишь для решения таких задач, в которых число неизвестных переменных n=2, что дает возможность представлять задачу на плоскости.
В MS Excel существует возможность поиска оптимального решения с использованием команды «Поиск решения» пункта меню «Сервис». Подключение пакета «Поиск решения» выполняется аналогично пакету «Анализ данных».
Пример выполнения:
Задача 1. Торговая фирма для организации новой компании не имеет достаточно средств. Ей необходимо привлечь заемный капитал на 15 лет с месячной выплатой, не превышающей 100000 руб. и процентной годовой ставкой равной 5%.
Решение:
|
|
A |
B |
C |
D |
|
1 |
Сумма заемного капитала, руб. |
Процентная ставка, % |
Срок заема, лет |
Ежемесячная выплата, руб. |
|
2 |
10000000 |
0,05 |
15 |
-79 079,36р. |
Рис. 13. Таблица данных
2. Предполагается, что на начальном этапе сумма заемного капитала задана в размере 10000000 руб.
3. Ввести в ячейку D2 формулу: =ПЛТ(В2/12;С2*12;А2);
4. Выбрать команду «Подбор параметра» пункта меню «Сервис».
5. Ввести входные параметры в окне диалога «Подбор параметра» (Рис. 14)
Рис. 14. Окно диалога «Подбор параметра»
После нажатия «Ок» появится диалоговое окно «Результат подбора параметра» и изменения в исходной таблице.
При подборе параметра MS Excel использует итерационный процесс: проверяется одно значение за другим для изменяемой ячейки, адрес которой указан в поле «Изменения значения ячейки», пока не получится нужное решение. Для прерывания вычисления при решении сложных задач подбора параметра используется кнопка «Пауза» в окне диалога «Результат подбора параметра», а для просмотра результата каждой последовательной итерации – кнопка «Шаг». Если задача решается в пошаговом режиме, то в окне диалога появится кнопка «Продолжить», которая предназначена для возврата в обычный режим подбора параметра.
По умолчанию команда «Подбор параметра» прекращает вычисления, когда выполняется 100 итераций, или при получении результата, который находится в пределах 0,001 от заданного целевого значения. Если нужна большая точность, то можно изменить используемые параметры. Для этого следует выбрать команду «Параметры» пункта меню «Сервис». В поле «Предельное число итераций» выбрать вкладку «Вычисления» и ввести значение больше 100, а в поле «Относительная погрешность» - значение меньше 0,001.
Команда «Подбор параметра» находит только одно решение, даже если задача имеет несколько решений. Команда «Подбор параметра» является удобной для решения задач, которые имеют точное целевое значение, зависящее от одного неизвестного параметра.
Задача 2. Фирма, выпускающая некоторую продукцию, осуществляет ее рекламу двумя способами: через радио и телевидение.
Постановка задачи:
Стоимость рекламы на радио обходится фирме в 450 руб., о стоимость телерекламы – 2800 руб. за минуту.
Фирма готова тратить на рекламу по 56000 в месяц. Так же известно, что фирма готова рекламировать свою продукцию по радио по крайней мере в 2 раза чаще, чем по телевидению. Опыт предыдущих лет показал, что телереклама приносит в 30 раз больший сбыт продукции, нежели радиореклама. Задача заключается в правильном распределении финансовых средств фирмы.
Формирование математической модели:
Обозначим через х1 (х2) – время потраченное на радиорекламу (телерекламу). Z – искомая целевая функция, отражающая максимальный сбыт от 2-ух видов рекламы:
Z (x1,x2) = x1+30* x2.
Суммарные затраты V составят:
V (x1,x2) = 450 x1 + 2800 x2
Частота использования рекламы Р можно записать в виде функции х1 и х2 следующим образом:
Р(х1,х2) = х1 – 2*х2.
Таким образом, математическая модель ситуации записывается следующим образом:
x1+30* x2max;
450 x1 + 2800 x2 56000;
х1 – 2*х 2 0 или 2*х 2 – х1 0,
х1 0, х2 0.
Тогда математическая модель задачи будет выглядеть следующим образом:
Z = x1+30* x2max;
450 x1 + 2800 x2 56000;
2*х 2 – х1 0,
х1 0, х2 0.
Ввод условий задачи
Ввод условий задачи состоит из следующих шагов:
Реализация в табличном процессоре MS Excel:
|
|
А |
B |
C |
D |
E |
F |
|
1 |
Реклама |
|||||
|
2 |
радиореклама |
телереклама |
||||
|
3 |
значение |
|||||
|
4 |
нижняя граница |
|||||
|
5 |
верхняя граница |
ЦФ |
Направление |
|||
|
6 |
коэффициент в ЦФ |
1 |
30 |
0 |
макс |
|
|
7 |
Ограничения |
Левая часть |
Знак |
Правая часть |
||
|
8 |
вид |
|||||
|
9 |
в1 |
450 |
2800 |
0 |
<= |
56000 |
|
10 |
в2 |
-1 |
2 |
0 |
<= |
0 |
Рис. 15. Таблица для ввода условий задачи линейного программирования.
D6 = СУММПРОИЗВ (В$3:С$3;В6:С6);
D9 = СУММПРОИЗВ (В$3:С$3;В9:С9);
D10= СУММПРОИЗВ (В$3:С$3;В10:С10).
В результате диалоговое окно «Поиск решения» должно быть заполнено так же как на рисунке 16:
Рис. 16. Диалоговое окно «Поиск решения»
|
Реклама |
|||||
|
радиореклама |
телереклама |
||||
|
значение |
30,27027027 |
15,13513514 |
|||
|
нижняя граница |
|||||
|
верхняя граница |
ЦФ |
Направление |
|||
|
коэффициенты в ЦФ |
1 |
30 |
484,3243243 |
макс |
|
|
Ограничения |
Левая часть |
Знак |
Правая часть |
||
|
вид |
|||||
|
в1 |
450 |
2800 |
56000 |
<= |
56000 |
|
в2 |
-1 |
2 |
0 |
<= |
0 |
Рис. 17. Выходные значения
В начале работы были выделены потоки выходной информации, которые должны быть представлены в виде типовых форм документа, и процесс их составления должен быть автоматизирован.
В комплексе описания выходной информации осуществляются операции по определению состава реквизитов выходной информации, расположению реквизитов выходной информации, описание полей выходной информации.
Последовательность расположения реквизитов определяется правилами распределения по частям документа.
Любой документ содержит следующие элементы:
Заголовочная часть располагается верхней части документа и содержит следующие объекты:
- логотип организации (эмблема);
- реквизиты организации (ИНН, расчетный счет, юридический адрес, телефон и др.);
- наименование организации полное.
Содержательная часть может содержать как текстовую информацию, так и графическую. текстовая информацию отражается в формате текста в виде аналитики или в таблицах, а графическая – в виде диаграмм.
Оформительная часть отражает фамилии ответственных исполнителей руководящих подразделений, выделяется место для печати.
Пример выполнения:
Задача. Имеются данные по моделям имеющихся у фирмы автомобилей. Создать типовую форму документа, в которой будет отражаться информация по выбранной из списка модели.
Решение:
1. Необходимо создать макет типовой формы документов, где будет отражена только заголовочная и оформительная части (рис.18)
Рис.18. Макет типовой форма документа
2. Далее автоматизируем процесс заполнения содержательной части в зависимости от поставленной перед нами цели. Исходные данные представлены на рис. 19.
Рис. 19 Исходные данные.
3. Составим список моделей на листе, где находятся исходные данные.
1) Вызвать панель инструментов «Формы», найти инструмент «Список» (выбрать щелчком по кнопке: при правильном выборе мышь при перемещении в область построения документа превратится в тонкий крест).
2) Нарисовать (растягиванием при нажатой левой клавиши мыши креста-указателя) поле списка (оно появится на листе пустым в выделенном формате).
3) Поместить мышь внутрь выделенного объекта и правой кнопкой вызвать контекстное меню, выбрать пункт «Формат объекта».
4) В открывшемся диалоговом окне заполнить два поля ввода (см. рис.20) следующим образом:
• установить курсор в первом поле ввода, протащить мышью по столбцу с моделями, которые составляют список (без заголовка столбца);
• установить курсор во втором поле, щелкнуть по любой ячейке (например, N6) в которую будет возвращен порядковый номер выбранного в списке элемента.
Рис.20 Формирование списка (фрагмент)
5) Выше списка поместить текст «Список выбора» (рис.21).
Рис.21. Список выбора.
4. Далее разработаем содержательную часть конкретного типового документа.
1) Вводим всю необходимую «стандартную» (не изменяемую в зависимости от работника) текстовую информацию (Модель, Тип кузова, Комплектация, Цвет, Год выпуска, Цена, Наличие).
2) Вводим формулы, определяющие выбор данных для конкретного работника в соответствии со списком выбора на листе с исходными данными. Для этого используем функцию ИНДЕКС, а в качестве значения порядкового номера значение ячейки N6 (рис.22).
Рис.22 Аргументы функции ИНДЕКС.
Аналогично заполняем все остальные ячейки, а также используя функцию ИНДЕКС строим название таблицы, а именно: ="Прайс лист по "&ИНДЕКС('прайс-лист'!C5:C44;'прайс-лист'!N6).
Получившаяся типовая форма документа представлена на рисунке 23.
Рис.23 Типовая форма документа.
Для удобного пользования данной автоматизированной информационной системой необходимо разработать пользовательский интерфейс по управлению созданной системой. Для этого следует создать макросы, построить кнопки для управления макросами, объединение которых в группы позволяет строить «меню» для управления обработкой информации.
Слово «макро» обозначает «большой образ». Макрос - это сохраненная последовательность действий (программа), которую можно воспроизвести при необходимости. Макросы можно создавать для самых различных целей: для ввода названия фирмы в заглавии рабочих таблиц, форматирования ячеек, переходов на листы, содержащие нужную информацию, построения графиков, формирования определенных документов и многого другого.
Воспроизведение макросов (запуск на исполнение) осуществляется:
• через меню «Сервис – макрос …»;
• назначенной комбинацией быстрых клавиш;
• нажатием на предварительно построенную кнопку, которой назначен определенный макрос.
Пример выполнения:
Задача. Построим макросы, позволяющие осуществлять переход между листами рабочей книга.
Решение:
Пусть Лист 1 содержит кнопки переходов к документам рабочей книги в рамках построения обработки информации по конкретной задаче (Рис. 24). Переименуем Лист1 в «Главная».
Рис. 24. Главное «меню» задачи «Анализ хозяйственной деятельности предприятия»
Для построения такого меню целесообразно предварительно продумать распределение информации на рабочих листах и, если есть намерение переименовать листы, то это надо сделать перед построением макросов. На каждом из листов логично построить кнопку для возврата в главное «меню», а также к любому другому элементу рабочей книги или воспроизведению любой другой функции, для которой решено создать свой макрос.
Построим первую связку переходов: между листом «Главная» и листом «Прайс-лист».
1. Переименуем листы в соответствии с будущими построениями.
2. Вернемся в лист «Главная» (сделаем его активным).
3. Выберите последовательность команд через меню «Сервис- макрос - начать запись...».
4. В диалоговом окне (Рис.25) имя макроса можно оставить предлагаемое (например, Макрос1) или заменить его на «Прайс-лист», а в поле «Описание» добавить краткое описание назначении макроса. Это описание будет «всплывать» при выборе макроса в списке макросов. Нажать «Ок».
Рис. 25. Задание имени макроса, описания и комбинации клавиш для вызова
5. С этого момента любое наше действие будет записано в программный модуль, который и составит макрос. Выполнив все действия, включаемые в макрос не забудьте «выключить» режим записи, нажав на кнопку
Примечание. Если кнопка «Останов» не появилась на экране, ориентиром включения процесса создания макроса является индикатор «Запись» в строке статуса окна Ms Excel (Рис.26)
Для нашего макроса необходимо выполнение одного действия: переход на лист «Прайс-лист». Выполните этот переход и остановите запись нажатием на кнопку «Останов». Если кнопка на экране отсутствует, остановить запись через меню «Сервис - запись макроса - остановить запись...».
Рис.26 Строка статуса окна Ms Excel
6. Перейдите на лист «Главная» и создайте кнопку для управления макросом. Вызовите панель инструментов «Формы», выберите инструмент «Кнопка», растяните на рабочем листе прямоугольник кнопки до нужного размера (Рис. 27, 28).
7. Отпустив кнопку мыши, увидим диалоговое окно, предлагающее выбрать из списка макрос, который следует выполнить при нажатии на построенную кнопку. У нас пока один макрос «Макрос1», выберем его щелчком мыши и щелкнем по кнопке «Ок» (Рис.28).
Рис 28. Назначение макроса кнопки
8. Щелчком мыши внутри кнопки (пока она выделена рамкой) войдите в режим редактирования названия кнопки, удалите текст «Кнопка 1» и наберите «Прайс-лист». Снять выделение с кнопки щелчком мыши за ее пределами.
Теперь при наведении курсора мыши на кнопку указатель меняется на образ «руки», свидетельствуя о воспроизведении макроса на нажатие левой кнопки мыши. Режим редактирования кнопки можно вызвать щелчком правой кнопки мыши.
Проверьте действие кнопки.