Мельников П.П. Кафедра Информационные технологии актуализировано 20.10.2012П. Мельников1. - презентация

1 Мельников П.П. Кафедра Информационные технологии актуализировано П. Мельников1

2 Рекомендуемая учебная литература и электронные ресурсы П.П. Мельников. Компьютерные технологии в экономической науке и практике. Уч.пос. М.: ФА П.П. Мельников. Компьютерные технологии в экономике. Уч.пос. М.: Кнорус, Электронное пособие на диске О: Электронные файлы. Веб – ресурс 2 П. Мельниковактуализировано

3 Объём дисциплины и виды учебной работы Виды учебной работы Объем дисциплины Общая трудоемкость дисциплины в зачётных единицах2 в часах72 Аудиторная работа (в часах):24 Лекции (Л)8 Семинары (С) Практические занятия (ПЗ) 16 Самостоятельная работа (в часах)48 Аудиторная -- Внеаудиторная – Подготовка к лекциям и практическим занятиям Выполнение домашних заданий 24 Форма промежуточного контроля по дисциплине зачет 3 П. Мельниковактуализировано

4 Содержание программы 1. Место и роль информационных технологий в экономической науке и практике.4 2. Технологии применения табличного процессора для решения экономических задач с использованием аналитических, табличных и графических моделей Компьютерные технологии решения задач оптимизации. 9 4 Технологии экономического анализа с использованием вероятностных моделей 8 5. Информационные технологии решения задач статистического анализа Технологии анализа данных, представленных в табличной форме Модели финансовых вычислений и их реализация в табличном процессоре 8 ИТОГО:72 4 П. Мельниковактуализировано

5 Формат зачета Зачет проводится по формуле: ОЗ=БЗ+БСР, где БЗ – балл, полученный на зачете (max 60); БСР – оценка самостоятельной работы (max 40). Для получения оценки Зачтено нужно, чтобы выполнялось условие ОЗ>= 51 Самостоятельная работа оценивается по итогам выполнения 4-х заданий в дистанционной тестирующей системе, размещенной на Веб – ресурсе Зачет проводится в среде автоматизированной системы объективного контроля- тесты в закрытой и открытой формах. 5 П. Мельниковактуализировано

6 Тематика и цель лекций 1. Ознакомить с программой 2. Дать основные установки 3. С целью подготовки к практическим занятиям осветить наиболее сложные вопросы П. Мельников 6 актуализировано

7 Тема 1. Место и роль информационных технологий в экономической науке и практике 1. Области применения компьютерных технологии в экономической науке и практике. 2. Формы применения информационных технологий в процессе обучения. 3. Роль и формы применения компьютерных технологий в научных исследованиях и профессиональной деятельности. 4. Компьютерное моделирование как метод научного исследования. 5. Понятие модели, формы представления и реализации моделей, технология моделирования. П. Мельников 7 актуализировано

8 П. Мельников 8 Технология Технология – применяемые средства, последовательность, приемы, используемые для преобразования исходного материала (сырья) в конечный продукт. Компьютерные технологии – это технологии, применяемые для преобразования исходных данных (информации) в полезную информацию, т.е. это технология обработки исходной информации и получения новой информации. Технической базой компьютерных технологий являются программные системы, современные вычислительные средства и средства коммуникаций. актуализировано

9 П. Мельников 9 1. Области применения компьютерных технологии в экономической науке и практике Взаимодействие с мировым информационным пространством Решение задач учета Экономическая наука Компьютерная поддержка других наук Образование Развитие навыков информационной культуры Использование информационных технологий в процессе обучения Подготовка к профессиональной деятельности Решение задач вычислительного характера Исследование экономических процессов на моделях (моделирование) Практика Решение задач планирования Решение аналитических задач Компьютерная подготовка документов Справочные системы Автоматизированные Информационные системы актуализировано

10 П. Мельников 10 актуализировано Использование информационных технологий в процессе обучения Применение электронных учебных пособий и учебников Автоматизированные системы обучения Автоматизированные системы контроля знаний Использование дистанционных технологий в образовании и обучении Справочные информационные системы

11 П. Мельников 11 Технологии и системы для разработки информационных моделей экономических объектов (CASE-технологии) актуализировано Компьютерная поддержка экономических наук Компьютерные технологии для решения задач экономической математики (MATLAB, MATCAD) Компьютерные технологии решения задач статистики (Пакет STATISTICA) Технологии и системы для моделирования и исследования экономических процессов. (Технологии, использующие нейронные сети, нечеткие множества) Справочные правовые системы для дисциплинам цикла правоведения, юриспруденция и т.п. (СПС КОНСУЛЬТАНТ+, ГАРАНТ)

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

13 П. Мельников 13 Тема 2. Технологии применения MS Excel для решения экономических задач с использованием аналитических, табличных и графических моделей Изучаемые вопросы 1. Понятие модели, формы представления 2. Технологии выполнения операций с массивами и матрицами. Модель Леонтьева. 3. Моделирование числовых последовательностей и рядов 4. Технологии исследования функций 5. Аппроксимация экспериментальных данных, уравнение линии тренда 6. Численное решение нелинейных уравнений и систем нелинейных уравнений. Баланс спроса и предложения 7. Численное дифференцирование и интегрирование. Предельные показатели экономики 8. Технология разработки моделей для решения задач нахождения условного экстремума. Транспортная задача линейного программирования 9. Оптимизация портфеля ценных бумаг актуализировано

14 Модель Модель – это прототип реального объекта, либо процесса, который адекватно отражает те свойства реального процесса или объекта, которые существенны для решения задачи. Таким образом, изучение одних сторон моделируемого объекта осуществляется ценой отказа от отражения в модели других его сторон. Иными словами, модель – это такой объект, который в процессе исследования замещает объект – оригинал так, что его изучение дает новые знания об объекте – оригинале. П. Мельников 14 актуализировано

15 Модели, формы представления Модели могут представляться различными способами. В экономической области чаще всего используются модели представленные: o в виде таблиц; o в виде графиков; o математическими зависимостями (аналитически в виде формул); o Статистически П. Мельников 15 актуализировано

16 П. Мельников 16 1.Технологии выполнения операций с массивами и матрицами Массив - это набор данных одного типа. Массив в MS Excel может храниться в диапазоне ячеек. Диапазон – адресуемая совокупность смежных ячеек в области рабочего листа. В одной ячейке диапазона может храниться один элемент данных массива. MS Excel позволяет оперировать одномерными, двумерными и трехмерными массивами, которые хранятся, соответственно в одномерных, двумерных и трехмерных диапазонах. Одномерный и двумерный диапазоны создаются на одном рабочем листе. Адресная ссылка на такой диапазон имеет формат: Имя_РЛ!Адрес_первой_ячейки : Адрес_последней_ячейки. например, адресная ссылка на трехмерный диапазон = Лист1:Лист2!$A$1:$B$4 актуализировано

17 П. Мельников 17 Если массив содержит данные арифметического типа, то с таким массивом можно выполнять арифметические операции такие, как: -операции, в которых в качестве операндов участвуют массив и единственная переменная, например умножение элементов массива на число; -операции, в которых в качестве операндов участвует двумерный массив и одномерный массив, например, почленно - построчное умножение; -операции, в которых участвуют массивы одинаковой размерности. Например, массивами в электронной таблице задаются значения векторов и матриц. Операции над массивами указываются комбинацией клавиш + + актуализировано

18 П. Мельников 18 Пример скалярного произведения векторов Пример суммирования (вычитания матриц) Скалярное произведение выполняется с векторами одинаковой размерности. Для вычисления произведения нужно перемножить значения одноименных координат и полученные результаты сложить. актуализировано

19 П. Мельников 19 Встроенные функции для работы с матрицами Русифицированное имя функции Англоязычное имя функции Выполняемое действие МОБР (параметр)MINVERSE (parametr)обращение матрицы МОПР (параметр)MDETERM (parametr) вычисление определителя матрицы МУМНОЖ (список параметров) MMULT (parametrlist)умножение матриц Параметрами приведенных функций могут быть адресные ссылки на массивы, содержащие элементы матриц, или имена диапазонов, например МОБР (А1: B2), или МОПР (матрица_1). актуализировано

20 П. Мельников 20 Технология вычисления произведения матриц Произведение матриц может быть вычислено, если количество столбцов умножаемой матрицы равно количеству строк матрицы множителя. Если А=(а ij ) m x n, и B=(b ij ) n x p, то матрица С, полученная умножением матрицы А на матрицу В будет иметь размер m x p, а каждый ее элемент будет равен сумме произведений i-й строки матрицы А на соответствующие элементы j-го столбца матрицы В: c ij =a i1 b 1j +a i2 b 2j + …a ip b pj =, i=1, 2, …, m; j= 1, 2, …, n. Пример умножения матриц актуализировано

21 П. Мельников 21 Решение систем линейных уравнений методом обратной матрицы Система линейных уравнений в матричном виде может быть представлена в виде: А х Х = В. В частном случае, когда число уравнений (m) в системе равно числу неизвестных (n) - m=n, то решение такой системы можно найти методом обратной матрицы в виде X=A -1 х B, где A -1 -матрица, обратная по отношению к А. Пример актуализировано

22 П. Мельников 22 Решение систем линейных уравнений методом наименьших квадратов В общем случае m может быть не всегда равно n. Возможны три случая: m n. При решении задачи в электронной таблице удобнее применить более общий подход - метод наименьших квадратов. Для этого обе части уравнения нужно умножить на транспонированную матрицу системы : А т АХ=А т В. Затем обе части уравнения нужно умножить на (А т А) -1. Если матрица (А т А) -1 существует, то система определена. С учетом того, что (А т А) -1 А т А=Е, получаем решение системы в виде Х=(А т А) -1 А т В. актуализировано

23 П. Мельников 23 Требуется решить систему Пример применения метода наименьших квадратов Модель решения актуализировано

24 П. Мельников 24 Решение систем линейных уравнений с использованием инструмента Поиск решения Концепция решения системы с использованием этого инструмента заключается в поиске таких значений аргументов целевой функции, при которых функция принимает нужное значение при заданных ограничениях. Т.е. решается задача математического программирования. В качестве целевой функции при этом выступает одно из уравнений системы. Оставшиеся уравнения выполняют роль ограничений. Инструмент Поиск решения использует итерационный алгоритм по методу сопряженных градиентов. Точность решения определяется задаваемой относительной погрешностью. актуализировано

25 П. Мельников 25 Пример для случая m=n актуализировано

26 П. Мельников 26 Пример для случая, когда mn актуализировано

27 П. Мельников 27 Известно, что рациональное функционирование многоотраслевого хозяйства предполагает соблюдение баланса между отраслями. Каждая отрасль многоотраслевого хозяйства является, с одной стороны, производителем определенной продукции, а с другой потребителем продукции, выпускаемой другими отраслями. Макроэкономика функционирования многоотраслевого хозяйства требует, чтобы соблюдался баланс по производству и потреблению между отдельными отраслями. Балансовый принцип связи различных отраслей состоит в том, что валовой выпуск i-й отрасли должен быть равен сумме объемов потребления. В простейшей форме балансовые соотношения имеют вид x i = x i1 + x i2 + … + x in + y i, i = 1, 2, …, n, где x i общий объем выпускаемой продукции i-й отрасли; x ij объем продукции i-й отрасли, потребляемый j-й отраслью при производстве объема продукции x j ; y i объем продукции i-й отрасли конечного потребления (для реализации в непроизводственной сфере). Приложение технологии использования матриц в макроэкономике Технология исследования линейной модели многоотраслевой экономики Леонтьева актуализировано

28 П. Мельников 28 Для производства продукции j-й отрасли объемом x i нужно использовать продукцию i-й отрасли объемом a ij xi, где а ij постоянное число, характеризующее прямые затраты. Это допущение позволяет представить модель многоотраслевой экономики (модель Леонтьева) в виде системы линейных уравнений, которая в матричной форме имеет вид x = Ax + y, (А= П/x т, П- матрица потребления), где x вектор валового выпуска; y вектор объема продукции конечного потребления; A матрица коэффициентов прямых затрат. Приведенная система уравнений может быть представлена в виде (E – A)x= y, где E единичная матрица. Если существует обратная матрица (E – A) –1 (матрица полных затрат), то существует единственное решение системы x = (E – A) –1 y. Из экономической теории известно несколько критериев продуктивности матрицы А: матрица А продуктивна тогда и только тогда, когда матрица (E – A)–1 существует и ее элементы неотрицательны; матрица А с неотрицательными элементами продуктивна, если сумма элементов по любому ее столбцу (строке) не больше единицы, причем хотя бы для одного столбца (строки) строго меньше единицы. актуализировано

29 П. Мельников 29 Пример ОтрасльПотреблениеКонечный продукт Валовой выпуск (ден.ед) Станкостроение Энергетика Машиностроение Автомобильная промышленность Добыча и переработка углеводородов В таблице приведены данные по балансу за некоторый период времени между пятью отраслями Требуется найти векторы конечного потребления и валового выпуска, а также матрицу коэффициентов прямых затрат и определить ее продуктивность актуализировано

30 П. Мельников 30 Решение актуализировано

31 П. Мельников Моделирование числовых последовательностей и рядов Числовые последовательности представляют собой множества чисел. Если каждому числу n из натурального ряда чисел 1,2,3, …, n…поставлено в соответствие вещественное число xn, то множество чисел x 1, x 2, x 3, …,x n … называют числовой последовательностью. Числа x 1, x 2, x 3, …, xn называют, членами последовательности, элемент x n - общим элементом, а число n –его номером. Таким образом, последовательность представляет собой множество пронумерованных элементов. Последовательность задана, если известен способ получения любого ее элемента. Последовательность обозначается символом {x n }. Например, символ {1/n}обозначает последовательность чисел 1, 1/2, 1/3, 1/4, …, 1/n. В общем случае для создания массива элементов последовательности нужно выполнить следующие действия: 1. Создать массив, содержащий множество чисел натурального ряда; 2. Ввести в ячейку формулу последовательности, делая в ней адресные ссылки на ячейки, содержащие номера элементов последовательности 3. Скопировать введенную формулу во все другие ячейки массива. актуализировано

32 П. Мельников 32 Пример создания последовательности {1/n}, и последовательности {n/(n+1)}. Для создания наиболее часто встречающихся последовательностей, таких как арифметическая или геометрическая прогрессия, табличный процессор имеет специальный инструмент Прогрессия, который включается командой меню Правка - Заполнить - Прогрессия. актуализировано

33 П. Мельников 33 Технология вычисления пределов числовых последовательностей Технологию приближенного вычисления предела числовой последовательности рассмотрим на примере. Пусть требуется найти предел числовой последовательности Решение Технология моделирования числовых рядов Числовым рядом называется бесконечная последовательность чисел u 1, u 2, …, u n …, соединенных знаком сложения: Ряд считается заданным, если известен его общий член u n =f(n). Сумма n первых членов ряда называется частичной суммой ряда. Для вычисления частичной суммы ряда в электронной таблице нужно выполнить следующие шаги: 1.Вычислить n первых членов числовой последовательности 2.Вычислить сумму членов числовой последовательности актуализировано

34 П. Мельников 34 Моделирование функциональных рядов В отличие от числовых рядов членами функционального ряда являются функции. Ряд, составленный из функций одной и той же переменной х: называется функциональным. Функциональные ряды находят практическое применение в финансовых вычислениях. Например, в задаче о сложных процентах при вкладе в банк U 0 денежных единиц с ежегодной выплатой х процентов годовых, функциональный ряд годовых приростов будет иметь вид: U 0 +U 0 x + U 0 (1+x)x +…+U 0 (1+x) n-1 x +…=U 0 (1+x) n Для вычисления частичных сумм этого ряда в библиотеке Excel есть специальная функция с именем БЗ. Кроме того, есть несколько функций, предназначенных для вычисления различных параметров такого ряда. Так функция КПЕР позволяет вычислить число членов ряда n по его частичной сумме, Функция ПЗ вычисляет начальное значение U 0 при заданном числе членов ряда n, частичной сумме ряда и величине процентной ставки. актуализировано

35 П. Мельников Исследование функций Способы задания функций Функция может быть задана таблично, в виде графика или аналитически. Табличный способ задания функций имеет широкое распространение в различных областях знаний и приложениях: экспериментальных измерениях, таблицах бухгалтерской отчетности и банковской деятельности, статистических данных и т.п. В табличном представлении одна из переменных представляется как независимая, другие величины будут являться функциями этого аргумента. На рисунке приведен пример табличного задания функции. Каждому значению независимой переменной Х соответствует значение функции Y, записанное в той же строке таблицы. Графическое представление функции, позволяет наглядно представить характер функции. Аналитический способ задания функции заключается в задании связи между аргументом в виде формулы или системы формул, например Y= x2. актуализировано

36 П. Мельников 36 Графики функций в табличном процессоре Если функциональная зависимость задана таблично или аналитически, то в ряде случаев бывает целесообразно для исследования функциональной зависимости представить ее графически. График - это графическое отображение характера зависимости значения функции от значения ее аргумента. Графики функций и диаграммы в Excel создаются с помощью мастера диаграмм, который включается командой меню Вставка - Диаграмма или щелчком на соответствующей кнопке панели инструментов. График (диаграмма) представляет собой составной объект, который может включать несколько объектов В их число входят: область диаграммы - объект, в котором могут размещаться все другие объекты диаграммы; область построения диаграммы - объект, в котором размещаются ряды и линии сетки; ось категорий (аргумента); ось значений; область названия оси категорий; область названия оси значений; область заголовка диаграммы; область легенды. актуализировано

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

38 П. Мельников 38 Нахождение корней функции одной переменной Корнями функции Y=f(x) называют такие значения х, при которых функция принимает значение ноль. Используя возможности MS Excel можно находить корни функции в ограниченной области определения переменной х. Последовательность операций нахождения корней следующая: 1. Производится табулирование функции в диапазоне вероятного существования корней. 2. По таблице фиксируются ближайшие приближения к значениям корней. 3. Используя средство MS Excel Подбор параметра, вычисляются корни уравнения с заданной точностью. Например, требуется найти все корни функции Y=X3-0,01X2- 0,7044X+0,139104=0 на отрезке [-1 ; 1]. Функция представлена полиномом третьей степени, следовательно, она может иметь не более трех корней. Для локализации начальных приближений необходимо определить интервалы значений Х, внутри которых значение функции пересекает ось абсцисс, т.е. функция меняет знак. С этой целью табулируем функцию на отрезке [–1;+1] с шагом 0,2, получим табличные значения функции актуализировано

39 П. Мельников 39 Выполним команду меню Сервис - Подбор параметра. В диалоговом окне заполните следующие поля: Установить в ячейке: в поле указывается адрес ячейки, в которой записана формула правой части функции. Значение: в поле указывается значение, которому должно удовлетворять значение функции, т.е. правая часть уравнения (в нашем случае 0). Изменяя значение: в поле указывается адрес ячейки (где записано начальное прибли- жение), в которой будет вычисляться корень уравнения и на которую ссылается формула. После щелчка на ОК получим значение первого корня: -0,92. Выполняя последовательно операции аналогичные предыдущим, вычислим значения остальных корней: -0, и 0, актуализировано

40 П. Мельников 40 Нахождение локальных экстремумов функции Если функция F(x) непрерывна на отрезке [a, b] и имеет внутри этого отрезка локальный экстремум, то его можно найти, используя надстройку Excel Поиск решения. Рассмотрим последовательность нахождения экстремума функции на примере. Задана неразрывная функция Y= X 2 +X +2. Требуется найти ее экстремум (минимальное значение) на отрезке [-2, 2]. Решение актуализировано

41 П. Мельников 41 Графическое решение систем уравнений Системы уравнений с двумя неизвестными могут быть приближенно решены графически. Решением такой системы является точка пересечения кривых на графике. Для решения системы необходимо выполнить следующие действия: Представить уравнения системы в виде функций. Табулировать полученные функции в области вероятного существования решения Построить график. Найти точку пересечения, навести указатель мыши на точку пересечения и щелкнуть левой кнопкой, после чего появится надпись с указанием искомых координат. Пример. Найти графически приближенное решение системы в диапазоне значений х [0,2;3] с шагом 0,2. актуализировано

42 П. Мельников 42 Решение актуализировано

43 П. Мельников 43 Интерполяция и аппроксимация экспериментальных данных в электронной таблице На практике часто бывает необходимым получить аналитическую формулу для функциональной зависимости, полученной экспериментально и представленной в виде таблицы. С этой целью полученные экспериментальные данные интерполируют. Интерполяцией называется нахождение значения таблично заданной функции в тех точках внутри данного интервала, где она таблично не задана. Иначе говоря, это процесс подбора приближенной эмпирической формулы Q(х) для полученной на основе экспериментальных данных функциональной зависимости f(x), приближенно заменяющей исходную и проходящую через все заданные точки. С помощью полученной функции можно рассчитать искомое значение исходной функции в любой точке, в том числе при таких значениях аргумента, при которых она не задана таблично. актуализировано

44 П. Мельников 44 Задачей аппроксимации является построение приближенной (аппроксимирующей) функции наиболее близко проходящей около данных точек или около заданной непрерывной функции. Подбор аналитической формулы сводится к вычислению входящих в нее параметров таким образом, чтобы из всех функций такого вида выбрать ту, которая наилучшим образом описывает зависимость между изучаемыми величинами. Подбираемая эмпирическая функция в зависимости от характера экспериментальных данных может быть следующих видов: 1. Линейная (Y=ax + b ) обычно применяется в тех случаях, когда экспериментальные данные изменяются относительно постоянно Полиноминальная ( y= a 0 + a 1 x +a 1 x 2 + …+ a n x n ) – используется для описания экспериментальных данных, попеременно возрастающих и убывающих Логарифмическая (Y= a lnx + b ), где а и b – константы, применяется для опи-сания экспериментальных данных, которые первоначально быстро возрастают или убывают, а затем постепенно стабилизируются Степенная ( y = bx a ), где a и b – константы – используется для аппроксимации экспериментальных данных, скорость изменения которых постоянно увеличивается или уменьшается Экспоненциальная ( y = be ax ), где a и b константы, применяется для описания экспериментальных данных, которые быстро возрастают или убывают, а затем стабилизируются. актуализировано

45 П. Мельников 45 Пример Имеются сведения о величинах страховых выплат по годам, представленные в таблице. Требуется исследовать характер изменения величины страховых выплат и подобрать интерполяционную функцию. Решение ГодСумма страховых выплат Интерполяционная функция y = -9259,3x 3 + 6E+07x 2 - 1E+11x + 7E+13, Достоверность R2 = 0,9818. актуализировано

46 П. Мельников 46 Приложения в экономике. Кривые спроса и предложения, точка равновесия Известно, что чем ниже цена (p), тем больше спрос (D) при постоянной покупательной способности населения. Обычно зависимость спроса от цены имеет вид ниспадающей линии, чаще всего приближающейся к прямой: D= -ap + c. В свою очередь, предложение растет с увеличением цены на товар и выражается зависимостью S=bp+d. Для экономики представляет интерес условие равновесия спроса и предложения. Если зависимость спроса от цены определяется функцией D=f(p), а зависимость предложения от цены – S =Q(p), то условие равновесия определяется уравнением: f(p)= Q(p) и соответствует точке пересечения кривых D и S. Цена Р 0, при которой выполняется это условие, называется равновесной. Таким образом, задача нахождения равновесной цены сводится к решению системы двух уравнений. Решение может быть получено графически. актуализировано

47 П. Мельников 47 Технология решения систем нелинейных уравнений Приближенно с заданной относительной погрешностью систему нелинейных уравнений можно решить, используя инструмент Поиск решения. Для решения системы уравнений нужно выполнить операции: 1. Выделить рабочие ячейки для неизвестных переменных и ввести в них какие - либо числовые значения переменных из предполагаемой области их определения. 2. В рабочих ячейках записать формулы каждой из функций, составляющих систему в терминах табличного процессора. 3. Включить инструмент Поиск решения. В поле Установить целевую ячейку указать адресную ссылку на ячейку, в которой записана формула одной из функций системы. Установить переключатель Значению и в соответствующем поле ввести числовое значение ноль. В поле Изменяя ячейки указать адресную ссылку на диапазон ячеек, которые определены для вычисления неизвестных переменных. На вычисляемые значения в других ячейках, содержащих формулы оставшихся функций, наложить ограничения на равенство нулю. Выполнить решение. актуализировано

48 П. Мельников 48 Пример решения системы нелинейных уравнений Требуется решить систему уравнений: 2/x - y +2 = 0 x 2 - y + 1 = 0 Решение. 1. Полагаем, что результат решения будет вычисляться в ячейках В4 и С4. Введем в эти ячейки какие-либо числовые значения из области определения, например значение В ячейки B6 и B7 введем формулы уравнений системы. 3. Включим инструмент Поиск решения и настроим параметры модели, как показано на рисунке. 4. Выполним решение. актуализировано

49 П. Мельников 49 Решение актуализировано

50 П. Мельников Численное дифференцирование и интегрирование. Технологии решения экономических задач Вычисление производной функции одной переменной Численными приближенными методами производная функции в заданной точке может быть вычислена с использованием формулы конечных разностей. Выражение для вычисления производной функции одного переменного, записанное в конечных разностях, имеет вид: При достаточно малых приращениях х, можно с приемлемой точностью получить величину производной Для вычисления производной в MS Excel будем использовать приведенную зависимость. Рассмотрим методику вычисления производной на примере. Пусть требуется найти производную функции Y= 2x 3 + x 2 в точке x= 3. Решение актуализировано

51 П. Мельников 51 Применение в экономике Вычисление предельных показателей Известно, что себестоимость продукции зависит от производимого объема. C=f(Q). Предельная себестоимость характеризует себестоимость (дельта С) прироста продукции (дельта Q): Пример: Зависимость издержек производства от объема выпускаемой продукции в денежных единицах выражается формулой C=20Q – 0,05Q 3. Требуется определить предельные издержки производства при объеме выпускаемой продукции 10 ден.ед Решение. актуализировано

52 П. Мельников 52 Вычисление эластичности экономических показателей В анализе и прогнозах ценовой политики применяется понятие эластичности спроса. Под эластичностью спроса понимается процентное изменение спроса при изменении цены товара на один процент. Пример. Спрос на товар определяется формулой D(P)=100-3P Требуется определить эластичность спроса при цене на товар Р= 20 ден. ед. Решение актуализировано

53 П. Мельников 53 Численное вычисление определенных интегралов Для численного вычисления определенного интеграла существует несколько методов. Наиболее простым является метод трапеций. Для вычисления определенного интеграла по методу трапеций используется формула: Технология вычисления определенного интеграла в электронной таблице основана на построении табличных значений подинтегрального выражения для каждого шага интегрирования. Используя его можно получить лишь приближенное значение интеграла актуализировано

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

55 П. Мельников 55 Точные методы вычисления определенного интеграла Можно предложить две технологии вычисления определенного интеграла с малой относительной погрешностью. Первая основана на использовании аппарата циклических ссылок и итераций табличного процессора, вторая – на основе использования созданного приложения Visual Basic For Application. Применение этих технологий позволяет задавать достаточно малый шаг интегрирования, что увеличивает точность вычислений. Для решения задачи с помощью циклических ссылок нужно выполнить операции: определить, на сколько интервалов нужно разбить диапазон интегрирования, чтобы получить требуемую точность, и задать их количество в виде числа итераций. Положим, для решения нашей задачи достаточно интервалов; выполнить команду меню Сервис/Параметры, в закладке Вычисления в диалоговом окне Параметры и в поле Предельное число итераций ввести число Если установлен флажок Итерации, то выключить его. Закрыть диалоговое окно Параметры; в ячейки рабочего листа ввести исходные данные и формулы для вычислений. актуализировано

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

57 П. Мельников 57 Пример приложения Исходными данными для вычисления определенного интеграла являются: формула подынтегральной функции; адрес ячейки, на которую ссылается формула (аргумент функции); значения нижнего и верхнего пределов интегрирования. Создадим форму пользователя, выполняющую роль диалогового окна, подобную приведенной на рисунке. актуализировано

58 П. Мельников 58 Элементы управления, размещенные в форме, имеют следующие свойства: актуализировано

59 П. Мельников 59 Листинг кода программы Private Sub ОК_Click() Процедура вычисления Dim delta As Double Переменная, хранящая шаг интегрирования Dim f1 As Double Переменная для хранения значения функции на предыдущем шаге Dim s As Double Переменная для накопления сумм площадей элементарных фигур Dim c As Integer Переменная для хранения количества шагов delta = Константа величины приращения Вычисляем количество шагов интегрирования c = CInt((CDbl(VP.Text) – CDbl(NP.Text)) / delta) – 1 В ячейку, содержащую аргумент функции, вводим величину нижнего предела Range(arg.Value).Value = NP.Value Выделяем ячейку с формулой функции Range(fun.Value).Select s = 0 актуализировано

60 П. Мельников 60 Выполняем цикл For i = 1 To c Запоминаем значение функции f1 = ActiveCell.Value Вычисляем значение функции на следующем шаге, вычисляем «площадь элементарной фигуры и накапливаем сумму Range(arg.Value).Value = Range(arg.Value).Value + delta s = s + f1 * delta + (Range(fun.Value).Value – f1) * delta / 2 Next В ячейку рабочего листа выводим результат Range(rez.Value).Value = s End End Sub Private Sub CommandButton2_Click() Процедура завершения End End Sub Sub Интеграл() Макрос для запуска приложения Интеграл макрос Макрос записан (Мельников) Вызываем диалоговое окно Integral.Show End Sub актуализировано

61 П. Мельников 61 7.Технология разработки моделей для решения задач оптимизации Постановка задачи оптимизации Основной целью экономики является рациональное функционирование хозяйствующих субъектов или, иначе говоря, оптимальная деятельность при ограниченных ресурсах. Поэтому в экономической области существует широкий класс задач оптимизации, или, как их еще называют, экстремальных задач. В задачах оптимизации вычисляются значения параметров некоторой функции y=f(x 1,x 2,…,x n ), при которых она принимает экстремальное значение (максимальное или минимальное) и при условии, что на эти параметры наложены ограничения. Эту функцию называют целевой функцией, а набор количественных значений между переменными, выражающих определенные требования к параметрам экономической задачи в виде уравнений или неравенств называют системой ограничений. Совокупность соотношений, содержащих целевую функцию и ограничения на ее аргументы, называют математической моделью экономической задачи оптимизации. Если целевая функция линейна и на ее аргументы наложены линейные ограничения, то такую задачу оптимизации называют задачей линейного программирования. актуализировано

62 П. Мельников 62 Последовательность разработки и решения 1. На основе постановки задачи и уяснения ее экономической сути, разрабатывается математическая модель, аналитически представляющая целевую функцию и функции ограничений. 2. Ввод исходных данных и формул, реализующих математическую модель в электронную таблицу. 3. Настройка параметров инструмента Поиск решения и его применение для решения задачи 4. Анализ результатов, оценка устойчивости. Пример: Фирма производит два вида мороженого: сливочное и шоколадное. Для изготовления мороженого используются два исходных продукта: молоко и наполнители, расходы которых на 1 кг готового продукта и их суточные запасы приведены в таблице. Исходный продуктРасход исходных продуктов на 1 кг мороженого Запас, кг СливочноеШоколадное Молоко0,80,5400 Наполнители0,40,8365 Суточный спрос на сливочное мороженое превышает спрос на шоколадное не более чем на 100 кг. Кроме того известно, что спрос на шоколадное мороженое не превышает 350 кг в сутки. Отпускная цена 1 кг сливочного мороженого 16 ден. ед., шоколадного – 14 ден. ед. Требуется определить в каком количестве мороженого каждого вида должна производить фирма, чтобы доход от реализации продукции был максимальным. актуализировано

63 П. Мельников 63 Математическая модель Введем обозначения: x 1 – суточный объем производства сливочного мороженого, х 2 - суточный объем производства шоколадного мороженого. Исходя из условия задачи целевая функция будет иметь вид Ограничения Формализация математической модели в электронной таблице актуализировано

64 П. Мельников 64 Настройка параметров инструмента Поиск решения Ввод данных в окне Поиск решения актуализировано

65 П. Мельников 65 Результат решения Настройка окна Результаты поиска и решения для получения подробного отчета с оптимальным решением прямой и двойственной задачи актуализировано

66 П. Мельников 66 Отчет по устойчивости1 для оптимальных решений прямой и двойственной задачи и оценка предельной эффективности выпускаемой продукции Анализ результатов Устойчивость оптимального решения прямой задачи Устойчивость оптимального решения двойственной задачи актуализировано

67 П. Мельников 67 Прямая задача: Нормированная стоимость - рассчитаны при текущих двойственных оценках ресурсов ожидаемые потери по эффективности при выпуске соответствующей единицы продукции. По обоим видам продукции потери по эффективности нулевые; Допустимое увеличение (уменьшение) -Максимально возможные увеличения (уменьшения) цен соответствующих продуктов при сохранении цен на другие продукты, которые не приведут к изменению вычисленной производственной программы Например цена сливочного мороженого может быть увеличена на 6,4 руб. (до 22,4) или уменьшена на 9 руб. (до 7 рублей) Диапазон изменения цены сливочного мороженого (7 - 22,4) является диапазоном устойчивости найденных оптимальных выпусков продукции; Двойственная задача: Теневая цена - оптимальные двойственные оценки соответствующих ресурсов сырья. (предельная эффективность каждого килограмма); Допустимое увеличение (уменьшение) - максимально возможные увеличения (уменьшения) запасов соответствующих ресурсов при сохранении неизменными запасов других ресурсов, которые не приведут к изменению текущих оптимальных двойственных оценок (диапазон устойчивости). актуализировано

68 П. Мельников 68 Оценка предельной эффективности сырья ( на примере молока) в диапазоне 0 - Диапазон устойчивости по запасу молока (395, ,08). Для оценки предельной эффективности нужно получить решения при значениях за границами диапазона устойчивости. Оценим эффективность 394- го литра молока. Изменим соответствующее ограничение в модели задачи, решим ее вновь. При предельной эффективности 23,07 диапазон постоянства предельной эффективности запаса молока составит 0, , который содержит 395 кг. молока. актуализировано

69 П. Мельников 69 Оценим предельную эффективность го литра молока. Изменим соответствующее ограничение в модели задачи, решим ее вновь. Диапазоном постоянства предельной эффективности 0 будет диапазон изменения запаса молока который содержит 434 литр молока. актуализировано

70 П. Мельников 70 Технология решения транспортной задачи линейного программирования Целью транспортной задачи является планирование наиболее рациональных путей и способов транспортировки товаров. В сущности технология решения транспортной задачи линейного программирования в электронной таблице практически ничем не отличается от технологии решения других оптимизационных задач Пример. На складах А1, А2, А3 имеются запасы товаров в количествах 90, 400 и 110 т соответственно. Грузополучатели В1, В2, В3 должны получить эти товары в количествах 130, 300, 160 т соответственно. Требуется найти такой вариант перевозки грузов, при котором сумма затрат на перевозки будет минимальной. Расходы по перевозке 1 т грузов в у.е. приведены в таблице ГрузополучателиСклад А1Склад А2Склад 3 В1252 В2415 В3368 актуализировано

71 П. Мельников 71 Математическая модель Введем обозначения x 11 - количество товара перевозимое грузополучателю В1 со склада А1; x 12 - количество товара перевозимое грузополучателю В1 со склада А2; x 13 - количество товара перевозимое грузополучателю В1 со склада А3; x 21 - количество товара перевозимое грузополучателю В2 со склада А1; x 22 - количество товара перевозимое грузополучателю В2 со склада А2; x 23 - количество товара перевозимое грузополучателю В2 со склада А3; x 31 - количество товара перевозимое грузополучателю В3 со склада А1; x 32 - количество товара перевозимое грузополучателю В3 со склада А2; x 33 - количество товара перевозимое грузополучателю В3 со склада А3. Тогда целевая функция будет иметь вид: L = 2 x 11 +5x x x 21 + x 22 +5x x x x 33 - min При ограничениях актуализировано

72 П. Мельников 72 Представление математической модели в табличном процессоре актуализировано

73 П. Мельников 73 Результат актуализировано

74 П. Мельников 74 Оптимизация портфеля ценных бумаг в среде MS Excel Известно, что цена продажи акций A, B и C до начала предстоящего месяца составляет 34,30; 74,87; 107,00 руб. В распоряжении инвестора имеется капитал 73 тыс. руб. Инвестора интересует вопрос, акции какого эмитента и в каком количестве следует приобрести по сегодняшнему курсу продажи, чтобы с минимальным риском получить в предстоящем месяце доход от портфеля не менее 55,41% на вложенный капитал. Ретроспектива динамики курсов: Месяц Курс А ,535,4231,4434,7433,440,9635,0135,89 Див. от А1,22,8648,223,944,960,0411,320,821,330,26 Курс В6664,19 61,3751,8865,8588,977,5756,6865,14 Див от В0,054,717,5356,9520,9634,50,0373,1212,714,59 Курс С4860,95 58,2350,3851,8772,1105,9573,1489,94 Див. от С19,4216,717,2447,112,2430,3250,8114,8425,225,59 актуализировано

75 П. Мельников 75 Технологическая последовательность компьютерного решения задачи 1.Экономико– статистический анализ данных a)Ввод данных на рабочий лист b)Расчет рядов эффективности ценных бумаг c)Расчет оценок средней эффективности по каждой бумаге d)Расчет отклонений эффективности каждой ЦБ от своего среднего e)Расчет ковариации 2.Составление математической модели оптимизации портфеля ценных бумаг 3.Формализация математической модели на рабочем листе 4.Составление компьютерного аналога математической модели с помощью инструмента Поиск решения и выполнение расчетов 5.Экономическая интерпретация результатов актуализировано

76 П. Мельников 76 актуализировано

77 П. Мельников 77 Математическая модель инвестора Найти Х= (Х1, Х2, Х3); Z=0,1244X1X2+2*0,02X1X2+2*0,0123X1X3+0,1311X2X2+2*0,0056X2X3+0,1312X3X3 min При ограничениях: 0,1632X1+0,3734X2+0,5742X3=>0,5541; X1+X2+X3=0; X2>=0; X3>=0; актуализировано

78 П. Мельников 78 Составление компьютерного аналога математической модели с помощью инструмента Поиск решения и выполнение расчетов Вывод: инвестору следует вложить 10% капитала в акции эмитента В и 90% капитала – в акции эмитента С актуализировано