абсолютная. Такие ссылки называются смешанными.
Пусть, например, в ячейке С1 записана формула =А$1+$В1, которая при копировании
в ячейку D2 приобретает вид =В$1+$В2.
Относительные ссылки при копировании изменились, а абсолютные — нет.
Сортировка и поиск данных.
Электронные таблицы позволяют осуществлять сортировку данных. Данные в
электронных таблицах сортируются по возрастанию или убыванию. При сортировке
данные выстраиваются в определенном порядке. Можно проводить вложенные
сортировки, т. е. сортировать данные по нескольким столбцам, при этом
назначается последовательность сортировки столбцов.
В электронных таблицах возможен поиск данных в соответствии с указанными
условиями — фильтрами. Фильтры определяются с помощью условий поиска (больше,
меньше, равно и т. д.) и значений (100, 10 и т. д.). Например, больше 100. В
результате поиска будут найдены те ячейки, в которых содержатся данные,
удовлетворяющие заданному фильтру.
Построение диаграмм и графиков.
Электронные таблицы позволяют представлять числовые данные в виде диаграмм или
графиков. Диаграммы бывают различных типов (столбчатые, круговые и т. д.); выбор
типа диаграммы зависит от характера данных.
Использование электронных таблиц для обработки числовых данных (на примере задач
из различных предметных областей):
- для расчета заработной платы, налогов в бухгалтерии
- для построения графиков функций, вычислений значений функций
- для исследования физических процессов
- для подведения итогов, суммирования различных величин
- для хранения больших объемов информации, выполнения сортировки и создания
простейших запросов в различных областях деятельности.
Билет 23
Математическая обработка статистических данных, результатов эксперимента.
Использование динамических (электронных) таблиц для обработки и представления
результатов естественно-научного и математического эксперимента, экономических и
экологических наблюдений, социальных опросов.
Многие объекты и процессы можно описать математическими формулами, связывающими
их параметры. Эти формулы составляют математическую модель оригинала. По
формулам можно сделать расчеты с различными значениями параметров и получить
количественные характеристики модели. Расчеты, в свою очередь, позволяют сделать
выводы и обобщить их. Среда электронных таблиц — это инструмент, который
виртуозно и быстро выполняет трудоемкую работу по расчету и пересчету
количественных характеристик исследуемого объекта или процесса.
Моделирование в электронных таблицах проводится по общей схеме, которая выделяет
четыре основных этапа: постановка задачи, разработка модели, компьютерный
эксперимент и анализ результата.
Рассмотрим особенности проведения моделирования в среде электронных таблиц по
каждому этапу:
I. Постановка задачи.
Все задачи можно разделить на 2 группы:
1) «Что будет, если…»
- Задачи, в которых требуется исследовать, как изменятся характеристики объекта
при некотором воздействии на него. Такую постановку задачи принято называть «что
будет, если...». Например, как изменится скорости автомобиля через 6 сек, если
он движется прямолинейно и равноускоренно с начальной скоростью 3 м/с и
ускорением 0,5 м/с2? Ответ, рассчитанный по формуле v=vo+at после подстановки
исходных значений 3+0,5 • 6=6 м/с, и есть результат расчета модели.
- Задачи, в которых требуется узнать, что будет, если изменять исходные данные в
заданном диапазоне с некоторым шагом? Такое исследование помогает проследить
зависимость параметров объекта от исходных данных.
2) «Как сделать, чтобы…»
Вторая группа задач имеет следующую обобщенную формулировку: какое надо
произвести воздействие на объект, чтобы его параметры удовлетворяли некоторому
заданному условию.
II. Разработка модели.
При составлении расчетных таблиц надо четко выделить три основные области
данных: исходные данные, промежуточные расчеты, результаты. Исходные данные
вводятся «вручную». Промежуточные расчеты и результаты проводятся по формулам,
составленным на основе математической модели и записанным по правилам
электронных таблиц. В формулах, как правило, используются абсолютные ссылки на
исходные данные и относительные ссылки на промежуточные расчеты.
III. Компьютерный эксперимент
После составления компьютерной модели проводятся тестирование и серя
экспериментов согласно намеченному плану.
План эксперимента должен четко отражать последовательность работы с моделью.
Первым пунктом такого плана всегда является тестирование модели. Тестирование в
ЭТ начинается с проверки правильности введения данных и формул.
Для проверки правильности алгоритма построения модели используется тестовый
набор исходных данных, для которых известен или заранее определен другими
способами конечный результат.
IV. Анализ результата моделирования.
По полученным расчетным данным проверяется, насколько расчеты отвечают нашему
представлению и целям моделирования. Важным качеством исследователя является
умение увидеть в числах реальный объект или процесс.
Решение различных задач постоянно требует учета зависимостей одних факторов от
других. Примеры зависимостей:
1) время падения тела на землю зависит от первоначальной высоты
2) давление зависит от температуры газа в баллоне
3) частота заболевания жителей бронхиальной астмой зависит от
качества городского воздуха.
Всякое исследование нужно начинать с выделения количественных характеристик
исследуемого объекта (процесса, явления). Такие характеристики называют
величинами. Если зависимость между величинами удается представить в
математической форме, то мы имеем математическую модель:
- это зависимость в функциональной форме (время пропорционально квадратному
корню от высоты).
Если зависимость можно установить только экспериментальным путем: путем сбора
многочисленных данных, их анализа и обобщения, то говорят об обработке
статистических данных. Статистические данные носят приближенный, усредненный
характер, получаются путем многократных измерений. Вид функции, определяющий
данную зависимость неизвестен, поэтому ее следует искать методом подбора по
экспериментальным данным.
Основные требования к искомой функции:
- она должна быть достаточно простой для использования ее в дальнейших
вычислениях
- график этой функции должен проходить вблизи экспериментальных точек так, чтобы
отклонения этих точек от графика были минимальны и равномерны.
Полученную функцию называют регрессионной моделью. График регрессионной модели
называют трендом. Линию тренда легко построить в Excel.
Метод наименьших квадратов используется для вычисления параметров регрессионной
модели. Этот метод содержится в математическом арсенале электронных таблиц (в
том числе и в Excel).
Выбор типа регрессионной модели пользователь производит сам, ЭТ позволяет
построить функцию такого типа, наиболее близкую к экспериментальным данным.
Характеристикой построения модели является параметр R2 – коэффициент
детерминированности. Чем его значение ближе к 1, тем модель лучше.
Может оказаться, что несколько моделей имею близкий параметр R2. В этом случае
пользователь выбирает из них наиболее подходящую, исходя из эмпирических
соображений.
Задачи на оптимизацию
В сфере управления сложными системами (например, в экономике) применяется
оптимизационное моделирование, в процессе которого осуществляется поиск наиболее
оптимального пути развития системы.
Критерием оптимальности могут быть различные параметры, например, в экономике
можно стремиться к максимальному количеству выпускаемой продукции, а можно и к
низкой себестоимости. Оптимальное развитие соответствует экстремальному
(максимальному или минимальному) значению выбранного целевого параметра.
Развитие сложных систем зависит от множества факторов (параметров),
следовательно, значение целевого параметра зависит от множества параметров.
Выражением такой зависимости является целевая функция
K=F(X1,X2,…,Xn)?
Где K – значение целевого параметра;
X1, X2,…,Xn – параметры, влияющие на развитие системы.
Цель исследования состоит в нахождении экстремума этой функции и определении
значений параметров, при которых этот экстремум достигается. Если целевая
функция нелинейная, то она имеет экстремумы, которые находятся определенными
методами.
Однако часто целевая функция линейна и, соответственно, экстремумов не имеет.
Задача поиска оптимального режима при линейной зависимости приобретает смысл
только при наличии определенных ограничений на параметры. Условия ограниченности
ресурсов математически представляются в виде системы неравенств.
Для поиска оптимального набора значений параметров, который соответствует
минимальному значению целевой функции, необходимо воспользоваться надстройкой ЭТ
Поиск Решений:
- Сервис/Надстройки, выбрать Поиск решений.
- На панели Поиск решения установить: адрес целевой ячейки; вариант оптимизации
(максимизация, минимизация, подбор значений); адреса ячеек, которые изменяются в
процессе поиска решения; ограничения.
Моделирование экологических систем.
Моделирование в экологической сфере позволяет прогнозировать развитие
биологических популяций, управлять численностью отдельных видов и предсказывать
влияние угрожающих их развитию факторов.
Цели моделирования:
- исследование изменения численности популяции при разных коэффициентах
рождаемости и смертности, с учетом природных факторов и биологического
взаимодействия видов. Чем больше внешних факторов учитывается при расчете, тем
более точной и реалистической получается модель.
- построение моделей с различной степенью огрубления природного процесса и
Страницы: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22
