Использование электронных таблиц MS Excel в экономических расчетах и алгоритмического языка Pascal при реализации численных методов

АРЗАМАССКИЙ ПОЛИТЕХНИЧЕСКИЙ ИНСТИТУТ (ФИЛИАЛ) государственного образовательного учреждения высшего профессионального образования НИЖЕГОРОДСКИЙ ГОСУДАРСТВЕННЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ ПОЯСНИТЕЛЬНАЯ ЗАПИСКА к курсовой работе Верховцев Евгений Александрович Факультет Технология машиностроения Кафедра Экономика и управление в машиностроении Группа АСЭ 10−1

Дата защиты «10 «июня 2011

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ ФЕДЕРАЛЬНОЕ АГЕНТСТВО ПО ОБРАЗОВАНИЮ АРЗАМАССКИЙ ПОЛИТЕХНИЧЕСКИЙ ИНСТИТУТ (филиал) государственного образовательного учреждения высшего профессионального образования

«Нижегородский государственный технический университет им. Р.Е.Алексеева»

Кафедра «Экономика и управление на предприятии»

Утверждаю:

Зав. Кафедрой Балакин М. Ф ЗАДАНИЕ на курсовую работу по дисциплине информатика

_____________________________________________________________

Студенту Верховцеву Е. А. группы АСЭ 10 — 1

Тема работы: Использование электронных таблиц MS EXCEL в экономических расчетах и алгоритмического языка PASCAL при реализации численных методов Начало проектирования «_____"_______________________2011г.

Конец проектирования «_____"_______________________2011г.

ИСХОДНЫЕ ДАННЫЕ К РАБОТЕ

1. С целью исследования динамических рядов даны абсолютные уровни ряда: март-504,23, апрель-907,24, май-651,83 842, июнь-631,33, июль-598,22, август- 521,44._

2. В базе данных (БД), содержащей сведения о хранящихся на складе материалах, требуется отыскать все наименования материалов, поступивших в последний месяц.

Структура БД: Регистрационный номер Наименование оборудования Описание материала (включить стоимость единицы измерения) Номер поставщика Месторасположение поставщика Дата поставки Объём поставки Номер потребителя Наименование потребителя Объём потребления Дата потребления Сформируйте форму накладной на отпуск товара со склада.

3.Составить программу для решения задачи Метод численного решения -_метод половинного деления. Отрезок, содержащий корень: [1;2]. Приближенное значение корня -1,8756. Уравнение: cos (2x) — 2sin (1/x) + 1/x = 0

Содержание графического материала:

1.____________________________________________________________

_____________________________________________________________

2.____________________________________________________________

_____________________________________________________________

3.____________________________________________________________

____________________________________________________________

4.____________________________________________________________

Содержание пояснительной записки Введение Глава 1. Исследование динамических рядов с использованием электронных таблиц MS Excel

1. Алгоритм решения задачи Глава 2. Экономические расчеты в электронных таблицах MS Excel

1. Постановка задачи

2. Алгоритм решения задачи Глава 3. Реализация численных методов при использовании алгоритмического языка Pascal

1. Постановка задач

2. Блок-схема программы Заключение Библиографический список ГОСТы Приложение 1

Приложение 2

Приложение 3

Приложение 4

Приложение 5

Приложение 6

Основная рекомендуемая литература

1. Информатика: Практикум по технологии работы на компьютере /Под. ред. Н. В. Макаровой. — М.: Финансы и статистика, 2004___________

2. Использование электронных таблиц MS EXCEL в экономических расчетах и________ ___ алгоритмического языка PASCAL при реализации численных методов: методические __ _ указания к курсовым работам по курсу «Информатика» / АПИ НГТУ; Сост. О. В. Глебова, __ Н. В. Жидкова, Арзамас, 2006_______ __________________________________

3. Информатика. Базовый курс. Под редакцией С. В. Симоновича. — 2-е издание. СПб.: Питер, 2004__________________________________________

4. Островский В. А. Информатика: учеб. для вузов. М.: Высшая школа, 2000

5. Turbo Pascal / С. А. Немюгин. — СПб.: Издательство «Питер», 2000___

6. Практикум по информатике. Под редакцией Курносова А. П. Воронеж: ВГАУ, 2001

Фамилия, имя, отчество, ученая степень, должность и подпись консультанта______________________________________________________

Подпись студента_____________________________________________

ОТЗЫВ КОНСУЛЬТАНТА

_____________________________________________________________

_____________________________________________________________

«_____"_____________________________2011 г.

Подпись______________________________

Руководитель_________________________________________________

«_____"_____________________________20 011г.

Подпись____________________________

Содержание Введение Глава 1. Исследование динамических рядов с использованием электронных таблиц MS Excel

1.1 Определение абсолютных и относительных показателей изменения уровней ряда динамики

1.2 Определение абсолютного размера одного процента прироста

1.3 Определение средних показателей ряда динамики

1.4 Аналитическое выравнивание ряда (выявления тренда)

1.5 Расчет показателей случайной колеблемости уровней ряда

1.6 Графическое изображение ряда динамики Глава 2. Экономические расчеты в электронных таблицах MS Excel

Глава 3. Реализация численных методов при использовании алгоритмического языка Pascal

1. Постановка задачи

2.Основная Блок-схема программы Заключение Библиографический список ГОСТы Приложение 1

Приложение 2

Приложение 3

Приложение 4

Приложение 5

Приложение 6

Excel — это составная часть Microsoft Office, предназначенная для создания электронных таблиц. Данные в Excel хранятся в книге на рабочих листах. Рабочий лист состоит из ячеек. В ячейки рабочего листа можно ввести два типа данных: константы и формулы. Все формулы, которые создаются в Excel, должны начинаться со знака равенства (=), который говорит, что следующие символы образуют формулу. Значения, которые появляются в ячейке, являются отображаемыми значениями (результаты), а в строке формул выводятся хранимые значения (как получается результат). Формула может содержать различные функции. Функция — это заранее определенная формула, которая оперирует с одним или несколькими значениями и возвращает одно значение или несколькими.

Так же с помощью MS Excel можно проводить исследование динамических рядов.

Динамический ряд представляет собой ряд значений отдельного технико-экономического показателя в последовательные периоды времени (месяцы). Такие ряды абсолютных уровней отдельных технико-экономических представлены в таблице 1 (исходные данные).

Таблица 1.

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

1) абсолютные приросты уровней ряда динамики;

2) базисные и цепные темпы роста;

3) базисные и цепные темпы прироста;

4) абсолютные размеры одного процента прироста.

Кроме того, должны быть рассчитаны обобщающие показатели динамики в виде средних величин:

1) средний годовой абсолютный прирост;

2) средние годовые темпы роста и прироста;

3) средние показатели колеблемости ряда динамики.

Важной задачей анализа динамических рядов также является определение основной закономерности, тенденции изменения анализируемого показателя за изучаемый период времени, т. е. выявление тренда, с помощью одной из прикладных надстроек электронных таблиц Excel — Поиск решения.

Затем строится график анализируемого ряда динамики при помощи Мастера диаграмм, анализом и выводом по проведенному исследованию. Расчеты всех обобщающих статистических показателей ряда динамики и их результаты производятся в электронных таблицах MS Excel.

Глава 1. Исследование динамических рядов с использованием электронных таблиц MS Excel

1.1 Определение абсолютных и относительных показателей изменения уровней ряда динамики Абсолютный прирост уровня ряда я определила как разность между сравниваемыми уровнями, потому что он показывает, на сколько единиц изменился уровень за тот или иной промежуток времени.

Базисный прирост равен разности между сравниваемым и базисным уровнями и выражается в тех же единицах, в которых измерены уровни:

где — базисный абсолютный прирост уровня ряда;

— сравниваемый уровень ряда в i-ом периоде;

— базисный уровень ряда.

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

Цепной абсолютный прирост равен разности между сравниваемым и предыдущим уровнями:

где — цепной абсолютный прирост уровня ряда;

— сравниваемый уровень ряда в i-ом периоде;

— уровень ряда в предыдущем периоде.

Если сравниваемый уровень уменьшается относительно уровня, с которым он сравнивается, то имеет отрицательный знак и характеризует размер абсолютной убыли (снижения) уровня. Абсолютный прирост (убыль) за единицу времени измеряет абсолютную скорость роста (снижения) уровня ряда динамики.

Относительная скорость изменения уровня динамики, т. е. интенсивность процесса роста, измеряемая базисными и цепными темпами роста.

Базисный темп роста уровня ряда для i-го периода равен отношению этого уровня к базисному уровню:

где — базисный темп роста уровня ряда для i-го периода;

— сравниваемый уровень ряда в i-ом периоде;

— базисный уровень ряда.

Цепной темп роста уровня ряда для i-го периода определяется как отношение этого уровня к уровню ряда в предыдущем периоде:

где — цепной темп роста уровня ряда для i-го периода;

— сравниваемый уровень ряда в i-ом периоде;

— уровень ряда в предыдущем периоде.

Темпы роста выражаются в долях единицы или процентах и показывают, во сколько раз увеличился сравниваемый уровень по сравнению с предыдущим или базисным уровнем, или сколько процентов сравниваемый уровень составляет от предыдущего или базисного уровня, принятых за 100%. Величина темпа роста, меньшая 1 (или 100%) показывает уменьшение уровня ряда.

Для характеристики относительной величины прироста я вычислил базисные и цепные темпы прироста.

Цепной темп прироста равен отношению цепного абсолютного прироста уровня ряда в i-ом периоде к уровню в предыдущем периоде:

где — цепной темп прироста уровня ряда i-го периода;

— цепной абсолютный прирост уровня ряда;

— уровень ряда в предыдущем периоде.

Базисный темп прироста равен отношению базисного абсолютного прироста уровня ряда к базисному уровню:

где — базисный темп прироста уровня ряда;

— базисный абсолютный прирост уровня ряда;

— базисный уровень ряда.

Базисные и цепные темпы прироста можно определить и иначе: когда темпы роста и прироста выражаются в виде коэффициентов:

или когда темпы роста и прироста выражаются в процентах:

.

При <1 или 100% (снижение уровня ряда) получаются отрицательные темпы прироста.

1.2 Определение абсолютного размера одного процента прироста Важной характеристикой ряда динамики является абсолютный размер одного процента прироста, который показывает содержание 1% прироста, его весомость. Абсолютный размер 1% прироста представляет собой отношение абсолютного прироста к темпу роста:

где — абсолютный размер 1% прироста;

— абсолютный прирост уровня ряда;

— темп прироста уровня ряда в i-ом периоде;

— уровень ряда в i-ом периоде;

— уровень ряда в предыдущем периоде.

При снижении Тр и Тпр абсолютное содержание 1% прироста может расти.

1.3 Определение средних показателей ряда динамики Статистические показатели динамики уровней ряда (абсолютные приросты и темпы роста и прироста) изменяются с течением времени, поэтому для их обобщения и выявления типичных особенностей и закономерностей развития явления я использовала средние показатели динамики: средние уровни, средние абсолютные приросты, средние темпы роста и прироста.

В соответствии с теорией средних величин расчет всех указанных динамических средних можно производить лишь для такого периода времени, который характеризуется одинаковыми условиями развития явления. Кроме того, общая средняя может быть выполнена средними показателями за отдельные промежутки этого периода.

Средний абсолютный уровень или хронологическая средняя интервального ряда динамики рассчитывается по формуле простой средней арифметической:

где — средний абсолютный уровень ряда;

— сумма уровней ряда;

— число уровней ряда (число периодов).

Если отдельные периоды ряда имеют неодинаковую длину, то средний уровень ряда определяется по формуле средней арифметической, взвешенной числом равных периодов.

Поскольку равна разности между последним и первым уровнями, средний абсолютный прирост уровня ряда можно определить по формуле:

где — последний уровень ряда;

— первый уровень ряда.

Число абсолютных приростов меньше числа уровней на единицу.

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

Средний темп роста вычисляется по формуле простой средней геометрической из темпов роста за составляющие промежутки времени изучаемого периода:

где — цепные темпы роста;

— последний уровень ряда;

— первый уровень ряда;

— число темпов роста.

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

Для определения среднего темпа прироста пользуются соотношением:

или в процентах:

.

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

1.4 Аналитическое выравнивание ряда (выявление тренда) Фактические уровни любого динамического ряда отражают влияние как постоянных общих причин, определяющих основную тенденцию (тренд), так и влияние случайных факторов, вызывающих случайную колеблемость уровней ряда.

где — детерминированная составляющая (тренд);

— случайная составляющая (компонента) временного ряда;

— уровень динамического ряда в i-ом периоде.

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

Для выравнивания динамических рядов экономических показателей наиболее часто применяют следующие функции:

1) линейная функция (полином первой степени):

где — координаты прямой;

— параметры прямой;

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

2) параболическая функция (полином второй степени):

Применяется при постоянном темпе (скорости) изменения абсолютного прироста, равному, т. е. при стабильном ускорении.

3) показательная функция:

Применяемая при постоянных цепных темпах роста уровней, когда уровни изменяются в геометрической прогрессии, — темп роста за единицу времени.

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

Параметры функций я находила путем решения системы нормальных уравнений, полученных по способу наименьших квадратов. При этом требование наименьших квадратов имеет вид:

где — эмпирические (фактические) уровни ряда;

— соответствующие им выровненные уровни.

Для выбора функции, описывающей тренд, я воспользовался одной из прикладных надстроек электронных таблиц Excel — Поиск решения, которая находится в меню Сервис > Поиск решения. С помощью данной надстройки рассчитываются параметры функции .

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

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

Результаты отображены на графике, на котором построена теоретическая линия (тренд), характеризующая динамику анализируемого показателя.

1.5 Расчет показателей случайной колеблемости уровней ряда Абсолютная мера случайной колеблемости уровня динамического ряда — среднее квадратическое отклонение фактических абсолютных уровней ряда от соответствующих теоретических уровней, рассчитанных по уравнению тренда:

где — абсолютный показатель случайной колеблемости уровней ряда;

— фактические уровни ряда;

— соответствующие им выровненные уровни;

— число отклонений (число периодов).

Чем больше абсолютная величина, тем сильнее колеблемость уровней ряда.

Относительный размер случайной колеблемости определяется как отношение абсолютного показателя колеблемости к среднему уровню ряда, выраженное в процентах:

где — коэффициент вариации ряда динамики;

— среднее квадратическое отклонение уровней ряда

— средний уровень ряда.

Коэффициент вариации ряда динамики характеризует устойчивость ряда динамики во времени.

1.6 Графическое изображение ряда динамики Для большей эффективности и наглядности анализа динамики ряда я дал его изображение в виде линейного графика, построенного с использованием Мастера диаграмм электронных таблиц Excel. На оси абсцисс — я отложил шкалу времени, а на оси ординат — шкалу уровней динамического ряда.

Сводная таблица, выравнивание ряда методом наименьших квадратов и выбор вида аппроксимирующей функции с помощью надстройки «Поиск решения», а также график динамики анализируемых уровней ряда находятся в приложениях 1,2 и 3 соответственно.

Глава 2. Экономические расчеты в электронных таблицах MS Excel

ряд динамика показатель прирост

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

Структура БД:

Регистрационный номер Наименование оборудования Описание материала Номер поставщика Месторасположение поставщика Дата поставки Объем поставки Номер потребителя Наименование потребителя Объем потребления Дата потребления Сформируйте форму накладной на отпуск товара со склада.

2.Алгоритм решения задачи.

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

ЕСЛИ (лог_выражение;значение_если_истина;значение_если_ложь) МЕСЯЦ (дата) Для отбора записей только последнего месяца использовался инструмент Фильтр (Данные-Фильтр). Для этого необходимо выбрать соответствующий пункт меню и задать параметры Автофильтра.

Для формирования накладной использовал функцию ВПР:

Ищет значение в первом столбце массива таблицы и возвращает значение в той же строке из другого столбца массива таблицы.

Буква В в названии функции ВПР означает «вертикально ВПР (искомое_значение;таблица;номер_столбца;интервальный_просмотр) Искомое значение — значение, которое должно быть найдено в первом столбце табличного массива (Массив. Объект, используемый для получения нескольких значений в результате вычисления одной формулы или для работы с набором аргументов, расположенных в различных ячейках и сгруппированных по строкам или столбцам. Диапазон массива использует общую формулу; константа массива представляет собой группу констант, используемых в качестве аргументов.). Этот аргумент может быть значением или ссылкой. Если искомое_значение меньше, чем наименьшее значение в первом столбце табличного массива, функция ВПР возвращает значение ошибки #Н/Д.

Таблица — два или более столбцов данных. Можно использовать ссылку на диапазон или имя диапазона. Значения в первом столбце аргумента «таблица» — это значения, в которых выполняется поиск аргумента «искомое_значение». Эти значения могут быть текстовыми, числовыми или логическими. Текстовые значения в нижнем и верхнем регистре считаются эквивалентными.

Номер столбца — номер столбца в аргументе «таблица», из которого возвращается соответствующее значение. Если номер_столбца = 1, то возвращается значение из первого столбца таблицы; если номер_столбца = 2 — значение из второго столбца таблицы и т. д. Если значение аргумента «номер_столбца»:

меньше 1, функция ВПР возвращает значение ошибки #ЗНАЧ!;

больше, чем число столбцов в таблице, функция ВПР возвращает значение ошибки #ССЫЛ!

Интервальный просмотр — логическое значение, определяющее, какое соответствие должна найти функция ВПР — точное или приблизительное.

Если этот аргумент имеет значение ИСТИНА или опущен, то возвращается точное или приблизительное значение. Если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше, чем искомое_значение Результат работы находится в приложении 5

Глава 3. Реализация численных методов при использовании алгоритмического языка Pascal

1. Постановка задачи Решить уравнение методом половинного деления отрезка:

.

Отрезок, содержащий корень: [1;2]

Приближенное значение корня: 1.8756

Решение уравнения методом половинного деления

При решении нелинейного уравнения методом половинного деления задаются интервал [a, b], на котором существует только одно решение, и желаемая точность е. Затем определяется середина интервала с=(а+b)/2 и проверяется условие F (a)•F (c)<0. Если указанное условие выполняется, то правую границу интервала b переносим в среднюю точку с (b=c). Если условие не выполняется, то в среднюю точку переносим левую границу (a=c). Деление отрезка пополам продолжается пока |b-a|>е. Структограмма решения нелинейных уравнений методом половинного деления приведена на рисунке.

Рисунок 1 — Метод половинного деления

пока |b-a|>е

c=(a+b)/2

F (a)•F (c)<0

2. Основная Блок-схема программы

Заключение

В данной курсовой работе при помощи MS Excel я исследовал динамические ряды, произвел экономические расчеты в электронных таблицах, а так же решил уравнение методом половинного деления при помощи алгоритмического языка Pascal.

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

Библиографический список

1. Информатика: Практикум по технологии работы на компьютере /Под ред. Н. В. Макаровой. -М.: Финансы и статистика, 2004. -384 с.

2. Использование электронных таблиц MS Excel в экономических расчетах и алгоритмического языка Pascal при реализации численных методов: методические указания к курсовым работам по курсу «Информатика» / АПИ НГТУ; Сост.: О. В. Глебова, Н. В. Жидкова, Арзамас, 2006 г. -35с.

3. Практикум по информатике. Под ред. Курносова А. П. Воронеж: ВГАУ, 2001. 173 с.

4. Информатика /под ред. Проф. Н. В. Макаровой. — М.: Финансы и статистика, 2004. — 768 с.: ил.

5. Информатика. Базовый курс/Симонович С.В. и др. — Спб.: издательство «Питер», 2000. — 640 с.: ил.

6. Островский В. А. Информатика: учеб. для вузов. М.: Высшая школа, 2000. —511 с.: ил.

7. Turbo Pascal / С. А. Немнюгин.? СПб: Издательство «Питер», 2000.? 496 с.:ил

ГОСТы

2.104−68 ЕСКД Основные надписи

2.105−95 ЕСКД Общие требования к текстовым документам.

2.106−96 ЕСКД Текстовые документы

2.301−68 ЕСКД Форматы

2.302−68 ЕСКД Масштабы

2.303−68 ЕСКД Линии

2.304−68 ЕСКД Шрифты чертежные

2.316−68 ЕСКД Правила нанесения на чертеж надписей, технических требований таблиц

Приложение 1

Сводная таблица к задаче 1:

Приложение 2

Выравнивание ряда методом наименьших квадратов и выбор вида аппроксимирующей функции с помощью надстройки «Поиск решения»:

Приложение 3

График динамики анализируемых уровней ряда:

Приложение 4

Приложение 5

PROGRAM GECKA;

USES WINCRT;

FUNCTION F (X:REAL):REAL;

BEGIN

F:=COS (2/X)-2*SIN (1/X)+(1/X);

END;

VAR A, B, C, X, E:REAL; i: integer;

BEGIN

CLRSCR;

WRITELN ('НАЙТИ КОРЕНЬ УРАВНЕНИЯ С ТОЧНОСТЬЮ ДО 0.0001');

A: =1;

B: =2;

E: =0.0001;

I: =0;

REPEAT

I: =I+1;

C:=(A+B)/2;

IF F (A)*F (C)<=0 THEN B:=C ELSE A:=C;

UNTIL B-A

X:=(A+B)/2;

WRITELN ('X=', X:5:4);

WRITELN (' КОЛИЧЕСТВО ПОДСЧЁТОВ ', i);

READLN;

END.

Приложение 6

Блок — схема функции