ИМЭП_Пособие_лаб_Excel. Имитационное моделирование экономических
 Скачать 3.99 Mb.
|
|
Приложение 1. Основные приемы работы с пакетом Excel Электронная таблица (рис. 1) позволяет хранить в табличной форме исходные данные, результаты и связи (алгебраические или логические соотношения ) между ними. Для работы с таблицей используется курсор – выделенный прямоугольник, который можно поместить в ту или иную ячейку. Каждая ячейка имеет свой адрес, который определяется номерами столбца и строки , например, адрес самой верхней левой ячейки - А1. Также можно выделить диапазон ячеек (например, столбец – А1:А7, строка – А1:Е1; прямоугольная область – А1:Е7). Ячейки могут содержать числа, строковые, символьные, логические величины, формулы. Формулы служат для выполнения расчетов, и включает три части: знак равенства (=””); операнды (совокупность значений, ссылок, аргументов); операции (сложение, вычитание, деление, умножение). Возможно использование трех типов операций : • арифметические (сложение – «+»; вычитание – «-»; умножение –«*»; деление –«/»; возведение в степень –«^»); • текстовые (объединение – «&»); • сравнения (равно - «=»; меньше -«<»; меньше или равно -«<=»; больше - «>»; больше или равно - «>=»; не равно - «<>»). Пример формулы, вычисляющей произведение двух ячеек =А1*А2. Записав эту формулу в ячейке А3 получим результат умножения ячейки А1 на А2. Положение ячейки может быть указано с помощью относительной и абсолютной адресации При использовании относительной адресации копирование , перемещение формулы, вставка, удаление строки или столбца с изменением местоположения формулы приводит к перестраиванию формулы относительно ее нового местоположения. Если же необходимо, чтобы при изменении местоположения формулы адрес ячеек, используемых в формуле не изменялся , то используется абсолютная адресация Чтобы заменить относительную ссылку абсолютной необходимо поставить знак «$» перед именем столбца и номером строки, например, $А$1. Также ссылка может быть смешанной , когда ссылка на строку должна быть абсолютной, а на столбец – относительной (или наоборот), например, $А1, А$1. При проведении моделирования с помощью электронных таблиц ячейки также классифицируются по содержанию: 1. Исходные данные модели. Такие ячейки могут включать параметры модели , например, средний спрос или стоимость хранения единицы товара, а также примеры случайных чисел, которые представляют собой неопределенные величины модели: спрос, время обслуживания и др. 2. Промежуточные вычисления. Данные ячейки содержат формулы, которые выполняют определенные в модели расчеты , осуществляющие преобразование входов в выходы. Например, в модели управления запасами это может быть расчет уровня запаса в конце периода. 3. Выходные параметры модели. Такие ячейки представляют собой наблюдение за состоянием исследуемых характеристик модели, в которых заинтересован экспериментатор. Например, в модели управления запасами исследуемой характеристикой может быть общие издержки в конце периода Возможно два режима вычислений: ручной и автоматический (установка выполняется на вкладке «Вычисления», расположенной в окне «Параметры», вызов которого осуществляется в меню «Сервис»). При использовании автоматического режима пересчет значений формул будет произведен после каждого изменения ячейки. В случае применения ручного режима пересчет будет осуществляться только после нажатия кнопки «F9». Так, в некоторых задач необходимо провести несколько экспериментов и каким-либо образом обработать полученные значения. Каждый новый эксперимент выполняется путем нажатия кнопки «F9». Рис . 1 – Таблица Excel Табличный курсор Моделирование_случайных_чисел_и_событий__в_Excel'>Приложение 2. Моделирование случайных чисел и событий в Excel Моделирование случайных чисел в Excel может быть выполнено двумя способами : с помощью встроенных функций и путем использовании инструмента «Генератор случайных чисел» дополнения «Анализ данных». Ниже будут рассмотрены способы моделирования случайных чисел и событий с использованием встроенных функций. Моделирование простого события Рассмотрим механизм моделирования простого события. Пусть имеется событие A , вероятность наступления которого равна A P . Выберем с помощью датчика случайных чисел, равномерно распределенных в интервале (0,1) некоторое число z . Известно, что вероятность попадания в интервал (0, A P ) случайной величины z равна величине A P . Поэтому если при розыгрыше число z попало в этот интервал, то следует считать, что событие A произошло. Противоположное событие (не A ) произойдет с вероятностью (1 – A P ) в том случае , если A z P ≥ Процедура моделирования простого события в имитационной модели описывается алгоритмом, схема которого показана на рис. 1 [23]. Оператор 1 обращается к датчику случайных чисел, генерирующему случайную величину z Оператор 2 проверяет условие A z P < . Если оно выполняется, считается, что произошло событие A . В противном случае считается, что произошло противопо- ложное событие (не A ). Нет Да 4 3 2 1 ДСЧ(z) A z P < Событие “А” Событие “не А” Рис .1 – Моделирование простого события В Excel данную операцию можно реализовать с помощью функции ЕСЛИ. Пусть в ячейке А1 указана вероятность A P события, тогда моделирование его наступления будет выглядеть следующим образом ЕСЛИ (СЛЧИС() полной группы несовместных событий Пусть имеется полная группа несовместных событий 1 2 , ,..., k A A A с вероятностями 1 2 , ,..., k P P P . При этом выполняется условие 1 1 k i i P = = ∑ Процедура моделирования полной группы несовместных событий описывается алгоритмом, схема которого показана на рис. 2. Здесь i L - кумулятивная вероятность 1 2 i i L P P P = + + + Нет Нет Нет Да Да Да 8 7 6 5 4 3 2 1 ДСЧ(z) 1 Z L < 2 Z L < 1 k Z L − < 1 A 2 A 1 k A − k A Рис . 2 – Алгоритм моделирования полной группы несовместных событий Оператор 1 обращается к датчику случайных чисел с равномерным распределением в интервале (0,1). Условный оператор 1 проверяет условие попадания случайной величины z в интервал (0, 1 L ). Если это условие выполняется , то считается, что произошло событие 1 A . Если условие в операторе 2 не выполняется, то алгоритм осуществляет проверку условий попадания случайной величины в другие интервалы. Одно из событий 1 2 , ,..., k A A A обя- зательно произойдет. Рассмотрим выполнение данных операций в Excel. Запишем в ячейки С2:С4 значения вероятностей 1 2 3 , , P P P событий 1 2 3 , , A A A (рис.3). В ячейке С5 смоделируем случайную величину, распределенную равномерно на интервале (0,1). Тогда определение произошедшего события будет выглядеть следующим образом С 6=ЕСЛИ(C5 . 3 – Моделирование полной группы несовместных событий Моделирование дискретной случайной величины Дискретная случайная величина может быть задана табличной зависимостью : X 1 x 2 x n x P 1 p 2 p n p Здесь j p – вероятность того, что дискретная случайная величина X примет значение j x . При этом 1 2 1 n p p p + + + = . Разделим интервал (0,1) на n отрезков, длины которых равны заданным вероятностям. Если случайное число z , вырабатываемое датчиком случайных чисел, равномерно распределенных в интервале (0,1), попадет в интервал k p , то случайная величина X примет значение k x . Таким образом, при моделировании дискретных случайных величин фактически используется та же процедура, что и при моделировании полной группы несовместных событий. Моделирование непрерывной случайной величины Приведем способы моделирования непрерывных случайных чисел (на рис. 4 показаны формы распределения вероятностей) [23-24]. 1. Показательное распределение 1 ln( ) x z λ = − , где x - случайная величина, распределенная по показательному закону; λ - интенсивность потока (среднее значение 1 µ λ = ); z - случайная величина, равномерно распределенная на интервале (0,1). В Excel данное вычисление выглядит следующим образом (пусть в ячейке А 1 дано среднее значение, а в А2 - результат) А 2=-А1*LN(СЛЧИС()). 2. Равномерное распределение на интервале ( , a b ) ( ) x a z b a = + − , ( 0,5) cp x x x z = + ∆ − , где x - случайная величина, распределенная по равномерному закону; a и b - нижняя и верхняя границы интервала ( , a b ) соответственно; ср x - среднее значение интервала ( , a b ); x ∆ - величина интервала ( , a b ); z - случайная величина, равномерно распределенная на интервале (0,1). В Excel это реализуется посредством формулы (пусть в ячейке А1 дана нижняя граница; в ячейке А2 – верхняя граница, а в А3 - результат) А 3=А1+СЛЧИС()*(А2-А1) 3. Нормальное распределение Процедура розыгрыша нормально распределенной случайной величины заключается в следующем. • Сложим 12 случайных величин i z с равномерным распределением в интервале (0,1), т. е. составим сумму 12 1 i i v z = = ∑ • Нормируем и центрируем случайную величину v , т. е. перейдем к величине 6 v η = − • От нормированной и центрированной величины η перейдем к случайной величине y , распределенной по нормальному закону, с заданными параметрами ( ) M y и ( ) y σ по формуле ( ) ( ) y M y y σ η = + ⋅ , где ( ) M y – известное математическое ожидание случайной величины y ; ( ) y σ – известное среднее квадратическое отклонение случайной величины y Для реализации данного генератора в Excel нужно выполнить следующий расчет (в ячейке А1 дано среднее значение, А2 – среднее квадратическое отклонение , а в А3 - результат) А 3=А1+А2*((СЛЧИС()+СЛЧИС()+СЛЧИС()+СЛЧИС()+СЛЧИС()+СЛЧИС()+СЛЧИ С ()+СЛЧИС()+СЛЧИС()+СЛЧИС()+СЛЧИС()+СЛЧИС())-6)). Рис . 4 – Графики законов распределения Приложение 3. Основные используемые функции Excel Равномерно распределенные на интервале (0;1) случайные числа являются основой при получении выборок любого вероятностного распределения. В пакете Excel данная такой генератор представляет собой функция СЛЧИС(). Существует также функция СЛУЧМЕЖДУ(нижн_граница; верхн_граница), возвращающая случайное число, заключенное между заданными границами. При проверке различных условий используется функция ЕСЛИ (логическое_выражение; значение _истина; значение _ложь), которая возвращает значение «значение_истина», если «логическое_выражение» истинно и «значение_ложь» в противном случае. Рассмотрим функции, выполняющие различные арифметические операции и статистическую обработку данных. Функция СУММ(блок) вычисляет сумму значений, расположенных в блоке, в качестве которого может выступать массив данных: диапазон (например, А1:А10), отдельные ячейки (например, А1;А2) и т.д. Функция СУММЕСЛИ(блок; «условие») вычисляет сумму значений, расположенных в блоке и удовлетворяющих условию (например, СУММЕСЛИ (А1:А10; «>0») рассчитает сумму положительных значений в диапазоне А1:А10). Функция СЧЁТ(блок) подсчитывает число значений в блоке. Функция СЧЁТЕСЛИ(блок; условие) вычисляет количество ячеек в блоке, удовлетворяющих условию (например СЧЕТЕСЛИ(А1:А10; «>0») рассчитает число ячеек с положительными значениями в диапазоне А1:А10). Функция МАКС(блок) находит максимальное значение массива данных из блока ячеек, указанного в качестве аргумента. Функция МИН(блок) в отличие от функции МАКС() определяет минимальное значение блока. Функция НАИБОЛЬШИЙ(блок;k) вычисляет k-е максимальное значение блока (например, самое большое число будет найдено при k=1, второе по величине – при k=2; минимальное значение – при k равным количеству ячеек в диапазоне ). Функция НАИМЕНЬШИЙ(блок;k) в отличие от НАИБОЛЬШИЙ(блок;k) находит k-е минимальное значение блока. Функция LN(Х) вычисляет натуральный логарифм числа Х (используется при генерировании случайной величины с показательным законом распределения ). Функция СРЗНАЧ(блок) рассчитывает среднее арифметическое данных, находящихся в блоке по формуле 1 N i i x M N = = ∑ , где N - число ячеек в диапазоне; M - среднее значение; i x - значение i -той ячейки. Функция СТАНДОТКЛОНП(блок) вычисляет среднее квадратическое отклонение по данным, содержащимся в блоке 2 1 ( ) N i i x M N σ = − = ∑ , σ - среднее квадратическое отклонение. С помощью функций НОРМАЛИЗАЦИЯ() и НОРМСТРАСП() осуществляется нахождение вероятности того, что случайная величина E будет меньше или равна x : ( ) P E x ≤ Функция НОРМАЛИЗАЦИЯ (х; среднее ; станд _откл) возвращает нормализованное значение NE величины x (среднее – среднее значение E , станд _откл – среднее квадратическое отклонение E ). Функция НОРМСТРАСП( NE ) возвращает вероятность того, что случайная нормализованная величина NE будет меньше или равна x Поскольку для определения искомой величины необходимо использовать две эти функции, то допускается запись НОРМСТРАСП(НОРМАЛИЗАЦИЯ(х; среднее ; станд_откл)). Функция ЦЕЛОЕ(число) округляет число до ближайшего меньшего целого. Функция ОСТАТ(число; делитель) возвращает остаток отделения числа на делитель Рассмотрим также финансовую функцию ПС(). Функция ПС(ставка; число_период; выплата) - возвращает приведенную к начальному моменту стоимость инвестиции 1 (1 ) n j j j CIF ПС R = = + ∑ , где n (число_период) - срок реализации инвестиционного проекта; R (ставка) - процентная ставка за период; j CIF (выплата) - выплаты в момент j . В данной функции предполагается, что размер выплат не изменяется на протяжении всего периода. Если рассматриваются не выплаты, а поступления средств, то тогда параметр выплата используется со знаком «минус». Также в качестве вспомогательной при решении задач используется функция ЕТЕКСТ (значение), которая проверяет, является ли значение текстом и возвращает «ИСТИНА» или «ЛОЖЬ». Приложение 4. Построение диаграмм в Excel Процесс создания диаграммы в Excel включает следующие шаги: 1. Выделение в таблице некоторых данных (рис. 1). 2. Создание диаграммы путем выбора в меню «Вставка» подменю «Диаграмма». Выбор типа графика (рис.2). Нажав на кнопку «Далее» можно корректировать подписи по оси Х (рис.3), а затем и другие параметры диаграммы , например, название оси (рис.4). 3. Модификация диаграммы. Созданный автоматически график не всегда выглядит так, как это необходимо, и, нажав, на него правой кнопкой мыши можно вызвать те свойства, которые нужно изменить. Пункт «Формат рядов данных » позволяет изменять порядок рядов, вид линии и маркеров графика : цвет, толщина и т.д.; с помощью пункта «Тип диаграммы» может выбрать другой тип диаграммы; а выбор подменю «Исходные данные» позволяет выполнить корректировку диапазона значений исходных данных и подписей оси Х. Путем нажатия правой кнопкой на область построения графика , можно установить «Параметры диаграммы» (названия заголовков, наличие линии сетки и т.д.), а выбор пункта «Формат области построения» позволяет изменять фон графика: цвет заливки и рамки. Полученный в результате выполненных операций график представлен на рис .5. Рис .1 – Выделение исходных данных для построения таблицы Рис .2 – Определение типа графика Рис .3 – Корректировка характеристик исходных данных Рис . 4 – Определение параметров диаграммы |