Для представления данных в удобном виде используют таблицы. Компьютер позволяет представлять их в электронной форме, а это дает возможность не только отображать, но и обрабатывать данные. Класс программ, используемых для этой цели, называется электронными таблицами.
Особенность электронных таблиц заключается в возможности применения формул для описания связи между значениями различных ячеек. Расчет по заданным формулам выполняется автоматически. Изменение содержимого какой-либо ячейки приводит к пересчету значений всех ячеек, которые с ней связаны формульными отношениями и, тем самым, к обновлению всей таблицы в соответствии с изменившимися данными.
Применение электронных таблиц упрощает работу с данными и позволяет получать результаты без проведения расчетов вручную или специального программирования. Наиболее широкое применение электронные таблицы нашли в экономических и бухгалтерских расчетах, но и в научно-технических задачах электронные таблицы можно использовать эффективно, например для:
-
Проведения однотипных расчетов над большими наборами данных; -
Автоматизации итоговых вычислений; -
Решения задач путем подбора значений параметров, табулирования формул; -
Обработки результатов экспериментов; -
Проведения поиска оптимальных значений параметров; -
Подготовки табличных документов; -
Построения диаграмм и графиков по имеющимся данным.
Одним из наиболее распространенных средств работы с документами, имеющими табличную структуру, является программа Microsoft Excel.
Пользовательский интерфейс Excel
Пользовательский интерфейс табличного процессора Microsoft Excel подобен интерфейсу других программных продуктов Microsoft Office 2000. Это обстоятельство значительно облегчает их изучение и использование.
К основным элементам интерфейса процессора Excel относят: строку заголовка, строку основного меню, пиктографическое меню, поле имен и строку формул, строку заголовка столбцов и столбец заголовка строк, полосы прокрутки, ярлыки рабочих листов, строку состояний. Назначение перечисленных элементов аналогично назначению элементов текстового редактора Word. Отметим отличия.
Поле имен расположено ниже пиктографического меню и отображает имя или адрес текущей, активной ячейки, то есть ячейки, в которой располагается курсор и в которую будет помещаться вводимая с клавиатуры информация.
Строка формул отображает содержимое этой ячейки. Как правило, с использованием этой строки выполняется просмотр или редактирование формул.
Строка заголовка столбцов и столбец заголовка строк содержат номера строк и столбцов электронной таблицы.
Ярлыки рабочих листов расположены в нижней части окна электронной таблицы и обеспечивают переключение между ее листами.
Основным документом для хранения и обработки данных в Excel является рабочая книга. Рабочая книга содержит несколько рабочих листов, каждый из которых представляет собой матрицу, состоящую из столбцов и строк. Для изменения текущего рабочего листа используются ярлыки рабочих листов, содержащие их имена. Пользователь имеет возможность изменять количество рабочих листов в книге и их имена. Помимо рабочих листов в книге могут присутствовать листы диалога и программные модули на языке Visual Basic.
Главным элементом структуры электронной таблицы является ячейка. Адрес ячейки таблицы есть пара из номера строки и номера столбца, на пересечении которых располагается эта ячейка. При работе с электронной таблицей используются два вида адресации ячеек – А1-формат и R1C1-формат. Различие между ними состоит в обозначении столбцов. Так, А1-формат предусматривает обозначение столбцов латинскими буквами, а R1C1-формат – цифрами. Так, ячейка, расположенная на пересечении второго столбца и третьей строки, имеет адрес В3 или R3C2. изменение формата адресации осуществляется командой Сервис-Параметры-Общие-Стиль ссылок.
Для того чтобы одновременно адресовать несколько ячеек, используют диапазон. Задание диапазона предполагает указание через двоеточие адресов левой верхней ячейки и правой нижней ячейки.
Для ввода данных с клавиатуры необходимо установить в нужную ячейку курсор и набрать данные. Редактирование данных текущей ячейки осуществляется редактированием текста в строке формул.
Управление Excel выполняется с помощью команд основного меню, пиктограмм панелей инструментов, а также нажатием комбинаций горячих клавиш.
Кратко охарактеризуем команды основного меню.
Группа команд Файл объединяет команды, предназначенные для работы с файлами электронных таблиц. Использование команд меню позволяет создать новую электронную таблицу, открыть существующую, закрыть, сохранить редактируемую таблицу под старым или новым именем в виде электронной таблицы или файла формата HTML. Команды обеспечивают подготовку и печать электронной таблицы, пересылку таблицы посредством электронной почты.
Команды меню Правка используются при корректировке таблицы. Они позволяют отменить или повторить выполненные действия; перенести, копировать и извлечь данные из буфера обмена; одновременно работать с диапазоном ячеек и листами рабочей книги; обнаруживать заданный фрагмент в таблице и при необходимости заменять его на указанный; работать со связанными таблицами.
Команды меню Вид формируют интерфейс пользователя: добавление панелей инструментов; вывод строки формул и статусной строки; управляют масштабом изображения таблицы.
Использование группы команд меню Вставка добавляет в таблицу ячейки, строки, столбцы, рабочие листы, диаграммы, функции, гиперссылку, примечания, рисунки, OLE-объекты.
Команды меню Формат обеспечивают задание форматов ячеек и стилей представления данных в ячейках, устанавливают атрибуты строк и столбцов рабочего листа электронной таблицы, выполняют автоформатирование таблицы.
Команды пункта меню Сервис выполняют проверку правильности написания текста, автозамену; осуществляют настройку (установку) дополнительных компонентов Excel; используются при подготовке макросов; регламентируют защиту и доступ к информации.
Применение команд меню Данные позволяет выполнить сортировку, отбор данных из таблицы, получить промежуточные и общие итоги и обеспечивает поддержку средств по работе со списками (базами данных).
Команды меню Окно осуществляют управление расположением окон электронной таблицы, фиксацию части информации.
Работа с таблицей как с файлом
Создание таблицы. Новая рабочая книга создается командой меню Файл – Создать. В появившемся на экране диалоговом окне Создать пользователь с помощью вкладок Общие и Решения выбирает необходимый шаблон таблицы. В результате табличный процессор создает рабочую книгу с несколькими листами.
Сохранение таблицы осуществляется выбором команды Файл – Сохранить (сохранить под старым именем) или Файл – Сохранить как (сохранить под именем). В последнем варианте на экране появляется стандартное диалоговое окно Сохранение документа. Это окно содержит поля, в которых задается новое имя файла и маршрут к нему, указывается тип сохраняемого файла (Книга Microsoft Excel, Web – страница).
Пользователь имеет возможность поместить сохраняемый документ на Рабочий стол, и Персональную папку в папку Избранное или иную Web – папку. Обычно в последние две папки помещаются создаваемые Web – страницы. Просмотр Журнала позволяет определить имена файлов, редактируемых пользователем в последнее время.
Вычисления в Excel
Основным достоинством электронной таблицы Excel является наличие мощного аппарата формул и функций. Любая обработка данных в Excel осуществляется при помощи этого аппарата. Мы можем складывать, умножать, делить числа, извлекать квадратные корни, вычислять синусы и косинусы, логарифмы и экспоненты. Помимо чисто вычислительных действий с отдельными числами, мы можем обрабатывать отдельные строки или столбцы таблицы, а также целые блоки ячеек. В частности, находить среднее арифметическое, максимальное и минимальное значение, среднеквадратичное отклонение, наиболее вероятное значение и многое другое.
Формулы
Формулой в Excel называется последовательность символов, начинающаяся со знака равенства “=“. В эту последовательность символов могут входить постоянные значения, ссылки на ячейки, имена, функции или операторы. Результатом работы формулы является новое значение, которое выводится как результат вычисления формулы по уже имеющимся данным.
Если значения в ячейках, на которые есть ссылки в формулах, меняются, то результат изменится автоматически.
В качестве примера приведем формулы, вычисляющие корни квадратного трехчлена: ax2+bx+c=0. Они введены в ячейки A2 и A3 и имеют следующий вид:
=(-B1+КОРЕНЬ(B1*B1-4*A1*C1))/2/A1
=(-B1-КОРЕНЬ(B1*B1-4*A1*C1))/2/A1
В ячейках A1, B1 и C1 находятся значения коэффициентов a, b и с, соответственно. Если мы ввели значения коэффициентов a=1, b=-5 и с=6 (это означает, что в ячейках A1, B1 и C1 записаны числа 1, 5 и -6), то в ячейках A2 и A3, где записаны формулы, мы получим числа 2 и 3. Если мы изменим, число в ячейке A1 на -1, то в ячейках с формулами мы получим
числа -6 и 1.
Использование ссылок и имен
Ссылка определяет ячейку или группу ячеек рабочего листа. Ссылки указывают, в каких ячейках находятся значения, которые нужно использовать в качестве аргументов формулы. С помощью ссылок можно использовать в формуле данные, находящиеся в различных местах рабочего листа, а также использовать значение одной и той же ячейки в нескольких формулах.
Можно также ссылаться на ячейки, находящиеся на других листах рабочей книги, в другой рабочей книге, или даже на данные другого приложения. Ссылки на ячейки других рабочих книг называются внешними. Ссылки на данные в других приложениях называются удаленными.
Имя - это легко запоминающийся идентификатор, который можно использовать для ссылки на ячейку, группу ячеек, значение или формулу. Создать имя для ячейки можно в поле имени, или через меню Вставка | Имя. | Присвоить... Использование имен обеспечивает следующие преимущества:
Формулы, использующие имена, легче воспринимаются и запоминаются, чем формулы, использующие ссылки на ячейки.
Например, формула “=Активы-Пассивы” гораздо понятнее, чем формула “=F6-D6”.
При изменении структуры рабочего листа достаточно обновить ссылки лишь в одном месте в определении имен, и все формулы, использующие эти имена, будут использовать корректные ссылки.
После того как имя определено, оно может использоваться в любом месте рабочей книги. Доступ ко всем именам из любого рабочего листа можно получить с помощью окна имени в левой части строки формул.
Можно также определить специальные имена, диапазон действия которых ограничивается текущим рабочим листом. Это означает, что эти имена можно использовать лишь на том рабочем листе, на котором они определены. Такие имена не отображаются в окне имени строки формул или окне диалога “Присвоить имя”, если активен другой рабочий лист книги.
Excel автоматически создает имена на основе заголовков строк и столбцов рабочего листа. .После того, как имя определено, мы можем:
Заменить все соответствующие ссылки этим именем во всех местах рабочего листа.
Например, определив имя “Прибыль” как “=$F$12”, можно заменить все ссылки на ячейку $F$12 именем “Прибыль”.
Быстро перейти на поименованную ссылку, заменить ссылки, вставить ссылку в формулу с помощью окна имени в строке формул.
Перемещение и копирование формул. Относительные и абсолютные ссылки.
После того как формула введена в ячейку, мы можем ее перенести, скопировать или распространить на блок ячеек.
При перемещении формулы в новое место таблицы ссылки в формуле не изменяются, а ячейка, где раньше была формула, становится свободной. При копировании формула перемещается в другое место таблицы, ссылки изменяются, но ячейка, где раньше находилась формула, остается без изменения. Формулу можно распространить на блок ячеек. При копировании формул возникает необходимость управлять изменением адресов ячеек или ссылок. Для этого перед символами адреса ячейки или ссылки устанавливаются символы “$”. Изменяться только те атрибуты адреса ячейки, перед которыми не стоит символ “$”. Если перед всеми атрибутами адреса ячейки поставить символ “$”, то при копировании формулы ссылка не изменится.
Например, если в записи формулы ссылку на ячейку D7 записать в виде $D7, то при перемещении формулы будет изменяться только номер строки “7”. Запись D$7 означает, что при перемещении будет изменяться только символ столбца “D”. Если же записать адрес в виде $D$7, то ссылка при перемещении формулы на этот адрес не изменится, и в расчетах будут участвовать данные из ячейки D7. Если в формуле указан интервал ячеек G3:L9, то управлять можно каждым из четырех символов: “G”, “3”, “L” и “9”, помещая перед ними символ “$”.
Если в ссылке используются символы $, то она называется абсолютной, если символов $ в ссылке нет - относительной. Адреса таких ссылок называются абсолютными и относительными, соответственно. Абсолютные адреса при перемещении формул не изменяются, а в относительных адресах происходит смещение на величину переноса.
Функции
Функции в Excel используются для выполнения стандартных вычислений в рабочих книгах. Значения, которые используются для вычисления функций, называются аргументами. Значения, возвращаемые функциями в качестве ответа, называются результатами. Помимо встроенных функций вы можете использовать в вычислениях пользовательские функции, которые создаются при помощи средств Excel.
Чтобы использовать функцию, нужно ввести ее как часть формулы в ячейку рабочего листа. Последовательность, в которой должны располагаться используемые в формуле символы, называется синтаксисом функции. Все функции используют одинаковые основные правила синтаксиса. Если вы нарушите правила синтаксиса, Excel выдаст сообщение о том, что в формуле имеется ошибка.
Если функция появляется в самом начале формулы, ей должен предшествовать знак равенства, как и во всякой другой формуле.
Аргументы функции записываются в круглых скобках сразу за названием функции и отделяются друг от друга символом точка с запятой “;”. Скобки позволяют Excel определить, где начинается и где заканчивается список аргументов. Внутри скобок должны располагаться аргументы. При записи функции должны присутствовать открывающая и закрывающая скобки, при этом не следует вставлять пробелы между названием функции и скобками.
В качестве аргументов можно использовать числа, текст, логические значения, массивы, значения ошибок или ссылки. Аргументы могут быть как константами, так и формулами. В свою очередь эти формулы могут содержать другие функции. Функции, являющиеся аргументом другой функции, называются вложенными. В формулах Excel можно использовать до семи уровней вложенности функций.
Задаваемые входные параметры должны иметь допустимые для данного аргумента значения. Некоторые функции могут иметь необязательные аргументы, которые могут отсутствовать при вычислении значения функции.
Типы функций
Для удобства работы функции и в Excel разбиты по категориям: функции управления базами данных и списками, функции даты и времени, DDE/Внешние функции, инженерные функции, финансовые, информационные, логические, функции просмотра и ссылок. Кроме того, присутствуют следующие категории функций: статистические, текстовые и математические.
При помощи текстовых функций имеется возможность обрабатывать текст: извлекать символы, находить нужные, записывать символы в строго определенное место текста и многое другое.
С помощью функций даты и времени можно решить практически любые задачи, связанные с учетом даты или времени (например, определить возраст, вычислить стаж работы, определить число рабочих дней на любом промежутке времени).
Логические функции помогают создавать сложные формулы, которые в зависимости от выполнения тех или иных условий будут совершать различные виды обработки данных.
ПРИМЕНЕНИЕ ТАБЛИЧНОГО ПРОЦЕССОРА MS EXCEL ДЛЯ ИЗУЧЕНИЯ МЕТОДА МОНТЕ-КАРЛО
Для моделирования различных физических, экономических и других процессов широко распространены методы, называемые методами Монте-Карло. В их основе лежит метод статистических испытаний. Суть его состоит в том, что результат испытания ставиться в зависимость от значения некоторой случайной величины, распределенной по заданному закону. Поэтому результат каждого отдельного испытания носит случайный характер.
Принципиальная особенность метода состоит в том, что он гарантирует высокое качество статистических оценок только при весьма большом числе испытаний, которое невозможно выполнить без помощи компьютера.
Табличные процессоры не очень удобны для проведения расчетов Монте-Карло, однако с их использованием можно достаточно просто проиллюстрировать основные особенности этого метода.
Применение метода Монте-Карло для вычисления площади круга
Рассмотрим применение этого метода для вычисления площади круга заданного радиуса. Данная задача хорошо иллюстрирует возможности метода. Пусть круг имеет радиус R = 1 (рис. 1). Уравнение соответствующей окружности имеет вид: ( x – 1 )+ ( y – 1 )= 1. (1)
Для решения задачи методом Монте-Карло впишем круг в квадрат. Вершины квадрата будут иметь координаты (0,0), (2,0), (0,2), (2,2). Любая
точка внутри квадрата или на его границе должна удовлетворять неравенствам 0 < x < 2 и 0 < y < 2. При случайном заполнении квадратами точками, координаты которых распределены равномерно в этих интервалах, часть точек будет попадать внутрь круга. Если выборка состоит из n наблюдений и m точек попали внутрь круга или на окружность, то оценку площади круга S можно получить из
соотношения S = S m
n (2)
где S – площадь квадрата, в который вписан круг.
В Excel с помощью функции СЛЧИС( ) можно получать равномерно распределенные случайные числа в диапазоне от 0 до 1. Для получения значений x и y в нужном диапазоне следует вводить формулы =2*СЛЧИС().
Число точек, попавших внутрь круга или на окружность, можно подсчитать, использовать функцию ЕСЛИ. Если координаты x и y таковы, что
( x – 1 ) + ( y – 1) < 1, тогда функция будет возвращать 1, иначе 0. Тогда число m в формуле (2) для площади круга определится как сумма всех значений, возвращаемых функцией ЕСЛИ, а число n равно числу испытаний, которое можно подсчитать с помощью функции СЧЕТ. Только при большом числе испытаний можно получить близкое к точному значение = 0, 7854.
Поэтому нужными формулами необходимо заполнить сразу большое число строк, например 100. Так будет выглядеть электронная таблица в режиме отображения формул:
|
|
А |
В |
С |
|
1 |
Х |
У |
=СУММ(С3:С102) |
|
2 |
|
|
=СЧЁТ(С3:С102) |
|
3 |
=2*СЛЧИС() |
=2*СЛЧИС() |
=ЕСЛИ(А3^2+B3^2<=1;1;0) |
|
… |
… |
… |
… |
|
102 |
=2*СЛЧИС() |
=2*СЛЧИС() |
=ЕСЛИ(А102^2+В102^2<=1;1;0) |
Скопируем формулы, находящиеся в столбцах А-С, в следующие свободные столбцы так, чтобы число испытаний достигло 1000. При этом формулами будут заняты столбцы А-АD. Введем в ячейки АЕ1 и АЕ2 формулы для подсчета общего числа испытаний n и числа попаданий внутрь круга m соответственно. В ячейку АF1 введем формулы для вычисления отношения m/n. Получаемое значение m/n в большинстве случаев отличается от точного не более чем на 5 %. В этом можно убедиться, нажимая клавишу F9 для пересчета значений случайных чисел.
Так будет выглядеть правая часть электронной таблицы в режиме отображения формул:
|
|
… |
АС |
AD |
AE |
AF |
|
1 |
… |
У |
=СУММ(AD3:AD102) |
=СУММ(C1:AD1) |
=AE1/AE2 |
|
2 |
… |
|
=СЧЁТ(AD3:AD102) |
=СУММ(C2:AD2) |
|
|
3 |
… |
=2*СЛЧИС() |
=ЕСЛИ(AB3^2+AC3^2<=1;1;0) |
|
|
|
… |
… |
… |
… |
… |
… |
|
102 |
… |
=2*СЛЧИС() |
=ЕСЛИ(AB102^2+AC102^2<=1;1;0) |
|
|
При числе испытаний, равном 100, отклонения m/n от точного значения значительно больше ( результаты получаемые в ячейках C1, F1, I1,…). Вычисляя отношение m/n при нарастающем числе испытаний, можно сделать выводы, справедливые для любого статистического эксперимента независимо от природы и типа моделируемой системы:
- с увеличением продолжительности наблюдения отклонение измеряемой
величины от ее точного значения уменьшается;
- существует предел, за которым увеличение продолжительности модели уже
не дает существенного повышения точности результата.
Вычисление одномерных интегралов методом Монте-Карло
Классические методы численного интегрирования основаны на геометрической интерпретации интеграла как площади криволинейной трапеции под графиком функции f (x) в пределах от x – a до x – b (рис. 2). Ось x делиться на n равных отрезков длиной x = ( b – a )/ n.
Простейшей оценкой площади под кривой f (x) служит сумма площадей прямоугольников. При этом значение f (x) вычисляется в начале каждого отрезка и оценка интеграла F дается выражением:
(3)
Другим приближением является формула трапеций, в которой интеграл оценивается вычислением площади трапеции со сторонами, равными значениям f (x) в начале и конце отрезка x. Обычно более высокую точность обеспечивает использование квадратичной или параболической интерполяции функции f (x) по трем соседним точкам.
Метод Монте-Карло вычисления интегралов аналогичен вычислению площади круга. Построим прямоугольник высотой H и длиной (b – a) такой, что функция f (x) лежит внутри его (рис.2). Генерируем n пар случайных чисел x и y с равномерным распределением, удовлетворяющих условиям
a <= x <= b и 0 <= y<= H. Доля точек (x, y) которые удовлетворяют условию y <= f (x), представляет собой оценку отношения интеграла от функции f (x) к площади прямоугольника. Отсюда оценка F в методе «проб и ошибок» определяется выражением:
(4)
где m – число точек, лежащих под кривой, n – общее количество точек, а S – площадь прямоугольника.
Другая разновидность метода Монте-Карло также основана на представлении интеграла как площади под графиком функции f (x). В соответствии с этим интеграл F = f (x)dx можно определить через среднее значение ( f ) подынтегральной функции f (x) на отрезке a<= x<= b. Для вычисления этого среднего возьмем x не с постоянным шагом, а случайным образом и произведем выборку значений f (x). Оценка F одномерного интеграла методом «выборочного среднего» выражается формулой:
(5)
где x – случайные числа, равномерно распределенные на отрезке a <=x<= b, а n – количество испытаний. Обратим внимание на то, что формулы (3) (5) различаются тем, что в первой формуле n точек выбираются с постоянным шагом, а во второй – случайным образом.
Для интегралов невысокой размерности формула (3) является более точной, но для многомерных интегралов, которые не вычисляются обычными численными методами, более предпочтительной является формула (5).
Вычислим интеграл от функции f (x)=cos x при интервале 0 <=x<= . График функции приведен на рис. 3.
Вычисление по формуле (3).
Прямоугольник, внутрь которого должны попадать точки со случайными координатами, определяется значениями f (x) от 0 до 1 и значениями x от 0 до (рис. 3). Именно в этих пределах должны попадать случайные числа x и y с равномерным распределением.
Заполним столбец А рабочего листа Excel массивом x. Для этого введем в 100 строк ( с 3 по 102 ) формулу =ПИ()*СЛЧИС(). Соответствующие строки столбца В заполним формулой =COS(A1)^2, а столбца C – случайными числами y – формула =СЛЧИС(). В строку 3 столбца D введем формулу =ЕСЛИ(С3<В3; 1; 0) и заполним этой формулой нижележащие строки. Эта формула возвращает значение 1, если y < cos x , и значение 0, если это условие не выполняется. Сумма ячеек с 3-й по 102-ю столбца D, вычисляемая в ячейке D1, дает число точек, лежащих ниже кривой cos x , то есть дает число m в формуле (5). В следующей строке введем =СЧЁТ(D3:D102). Функция СЧЁТ дает число испытаний n. Поскольку площадь выбранного прямоугольника для определения интеграла равна , а точное значение интеграла равно /2, вычисляемое отношение m / n должно быть близким к 0,5. Введем формулу для вычисления этого отношения в ячейку Е1. при небольшом числе испытаний полученное нами значение m / n будет отличаться от 0,5.
Так будет выглядеть электронная таблица в режиме отображения формул:
|
|
А |
В |
С |
D |
|
1 |
Х |
Y |
f (x) |
=СУММ(D3:D102) |
|
2 |
|
|
|
=СЧЕТ(D3:D102) |
|
3 |
=СЛЧИС()*ПИ() |
=СЛЧИС() |
=COS(A3)^2 |
=ЕСЛИ(В3 |
|
… |
… |
… |
… |
… |
|
102 |
=СЛЧИС()*ПИ() |
=СЛЧИС() |
=COS(A102)^2 |
=ЕСЛИ(В102 |
Увеличим число испытаний до 1000 так, как делали при вычислении площади круга. Убедимся, что даже при таком числе испытаний отношение m / n будет отличаться от точного примерно на 1 – 3 %. При нескольких нажатиях на клавишу F9 для пересчета значений увидим, что получаемое отношение может отклоняться от 0,5 как в большую, так и в меньшую сторону.
Вычисление по формуле (5).
Для вычисления интеграла методом выборочного среднего достаточно ввести один столбец случайных чисел x столбец В (строки 2 – 101) заполнить формулой cos x, в ячейке В1 вычислить среднее значение cos x. Получаемое в этой ячейке значение может значительно отличаться от точного среднего значения функции, равного 0,5. Увеличивая число испытаний, копируя формулы из столбцов А и В в свободные столбцы.
Так будет выглядеть электронная таблица в режиме отображения формул:
|
|
А |
В |
… |
S |
T |
U |
|
1 |
|
=СРЗНАЧ(В2:В101) |
… |
|
=СРЗНАЧ(Т2:101) |
=СРЗНАЧ(В1:Т1) |
|
2 |
=СЛЧИС()*ПИ() |
=COS(A2)^2 |
… |
=СЛЧИС()*ПИ() |
=COS(S2)^2 |
|
|
… |
… |
… |
… |
… |
… |
|
|
101 |
=СЛЧИС()*ПИ() |
=COS(A101)^2 |
… |
=СЛЧИС()*ПИ() |
=COS(S101)^2 |
|
В ячейку U1 вводим формулу для вычисления среднего значения функции при 1000 испытаний. Убедимся, что получаемый результат такой же как и в предыдущем случае. Небольшое отличие заключается в том, что в данном случае нужно вводить меньшее число формул.
Анализ погрешности метода Монте-Карло
Как метод Монте-Карло, так и классические методы численного интегрирования дают приближенные результаты. Точность определяется числом испытаний в методе Монте-Карло и количеством отрезков разбиения в классических методах.
Оценим погрешность в расчетах методом Монте-Карло на конкретном примере.
Вычислим методом Монте-Карло среднее значение функции f (x)= cos x на отрезке [ 0, ] при различном числе испытаний. Сначала нужно определить среднее значение cos x при 40 испытаниях, затем проведем 14 серий по 40 испытаний, для того чтобы определить стандартное отклонение среднего значения функции при 40 испытаниях. Затем увеличим число испытаний вдвое, найдем среднее по этому числу испытаний и проведем 14 серий таких измерений и так далее. Результаты испытаний запишем в таблицу.
З
ависимость вычисляемого
среднего значения функции
{ f(x)} и стандартного отклонения
Q от числа испытаний n
|
N |
{f (x) } |
Q |
|
40 |
0,481 |
0,029 |
|
80 |
0,520 |
0,024 |
|
160 |
0,410 |
0,019 |
|
320 |
0,494 |
0,010 |
|
640 |
0,503 |
0,006 |
|
12880 |
0,504 |
0,005 |
Отсюда следует вывод, с ростом размерности погрешность стандартных численных методов увеличивается, то для интегралов большой размерности главными являются методы Монте-Карло.
Заключение
Табличный процессор Excel предназначен для обработки числовой и текстовой информации. С помощью программы Excel можно решать экономические, математические, инженерно – расчетные задачи, проводить статистическую обработку данных, а также отображать числовую информацию в виде различных графиков.
Для моделирования различных физических, экономических и других процессов широко распространены методы Монте-Карло, метод Симпсона, метод хорд, метод касательных, комбинированный метод и многие другие методы.
Для интегралов небольшой размерности стандартные численные методы, такие, как метод Симпсона, обычно предпочтительнее методов Монте – Карло, если область интегрирования не очень сложна. Однако поскольку с ростом размерности погрешность стандартных численных методов увеличивается, то для интегралов большой размерности главными являются методы Монте – Карло.
Оглавление
-
Введение…………………………………………………………………...1 -
Пользовательский интерфейс Excel……………………………………...2 -
Работа с таблицей как с файлом………………………………………….3 -
Вычисления в Excel………………………………………………………..4 -
Формулы……………………………………………………………………4 -
Использование ссылок и имен…………………………………………….4 -
Перемещение и копирование формул…………………………………….5 -
Относительные и абсолютные ссылки……………………………………5 -
Функции…………………………………………………………………….6 -
Типы функций……………………………………………………………...7 -
Применение табличного процессора MS Excel для изучения метода Монте – Карло……………………………………………………………..8 -
Применение метода Монте – Карло для вычисления площади круга…9 -
Вычисление одномерных интегралов методом Монте – Карло……….10 -
Анализ погрешности метода Монте – Карло………………………….. 12
15. Заключение……………………………………………………………….13
Литература
-
Журнал « Информатика и образование », № 12 – 2009. -
Основы информатики: Учеб. пособие / А. Н. Морозевич, Н.Н. Говядинова, В. Г. Левашенко и др.; Под ред. А.Н. Морозевича. – 2-е изд., испр. – Мн.: Новое знание, 2003. – 544с. – (Экономическое образование). -
Информатика: Базовый курс / С. В. Симович и др. – СПб : Питер, 2001. – 640с.: ил.