КАТЕГОРИИ: Архитектура-(3434)Астрономия-(809)Биология-(7483)Биотехнологии-(1457)Военное дело-(14632)Высокие технологии-(1363)География-(913)Геология-(1438)Государство-(451)Демография-(1065)Дом-(47672)Журналистика и СМИ-(912)Изобретательство-(14524)Иностранные языки-(4268)Информатика-(17799)Искусство-(1338)История-(13644)Компьютеры-(11121)Косметика-(55)Кулинария-(373)Культура-(8427)Лингвистика-(374)Литература-(1642)Маркетинг-(23702)Математика-(16968)Машиностроение-(1700)Медицина-(12668)Менеджмент-(24684)Механика-(15423)Науковедение-(506)Образование-(11852)Охрана труда-(3308)Педагогика-(5571)Полиграфия-(1312)Политика-(7869)Право-(5454)Приборостроение-(1369)Программирование-(2801)Производство-(97182)Промышленность-(8706)Психология-(18388)Религия-(3217)Связь-(10668)Сельское хозяйство-(299)Социология-(6455)Спорт-(42831)Строительство-(4793)Торговля-(5050)Транспорт-(2929)Туризм-(1568)Физика-(3942)Философия-(17015)Финансы-(26596)Химия-(22929)Экология-(12095)Экономика-(9961)Электроника-(8441)Электротехника-(4623)Энергетика-(12629)Юриспруденция-(1492)Ядерная техника-(1748) |
ХОД РАБОТЫ. 1.1. Сделайте заголовок и заполните шапку таблицы
ЗАДАНИЕ 1. Заполните таблицу.
1.1. Сделайте заголовок и заполните шапку таблицы. 1.2. Столбцы А и В отведите под коэффициенты а и b соответственно. 1.3. В столбец С занесите значения t с 1960 г. 1.4. В столбец D занесите взятые из справочника значения численности населения России с 1960 г.
ЗАДАНИЕ 2. Подберите значения коэффициентов а и b. Следующий шаг в решении задачи - это вычисление теоретической численности по формуле (1), в которой не известны значения коэффициентов а и b. Подбор а и b можно произвести в два этапа. Сначала определим их значения приближенно, для чего построим график роста статистической численности и аппроксимируем его. Затем уточним полученные коэффициенты а и b с использованием функции Excel Поиск решения. 2.1. Постройте график типа X-Y по данным таблицы. (Х-годы; Y- статистическая численность). 2.2. Перемасштабируйте оси Х и Y. В версиях Excel до 2010 необходимо перемасштабировать оси:
2.3. Аппроксимируйте полученную кривую.
Необходимо статистические данные по численности населения представить на графике плавной кривой (аппроксимировать). Эта кривая называется линией тренда. Для построения линии тренда:
· Выполните команду Макет – Линии тренда – экспоненциальное приближение
В результате на графике появится линия тренда и уравнение с подобранными коэффициентами а и b. 2.4. Занесите полученные значения коэффициентов а и b в ячейки A3 ВЗ и присвойте им имена: A3 имя а ВЗ имя b
Коэффициенты а и b не изменяются с течением времени, это константы, следовательно, при вычислении теоретической численности они должны быть адресованы абсолютно.
ЗАДАНИЕ 3. Вычислите теоретическую численность по формуле(1). 3.1. В ячейку ЕЗ занесите формулу =а *ЕХР(b * СЗ) 3.2. Скопируйте формулу в ячейки Е4:Е11
ЗАДАНИЕ 4. Вычислите отклонение.
Отклонение - это модуль разности теоретических и фактических значений функции f(t).. 4.1. В ячейку F3 занесите формулу =ABS(E3-D3) 4.2. Скопируйте формулу в ячейки F4:F11
ЗАДАНИЕ 5. Вычислите погрешность.
Погрешность – это максимальное отклонение. В ячейку F13 введите функцию определения максимального из чисел этого столбца.
ЗАДАНИЕ 6. Подберите значения коэффициентов а и b более точно.
При полученных в результате аппроксимации коэффициентах а и b погрешность уже неплохая (по условию она должна быть в пределах нескольких миллионов). Но коэффициенты а и b можно подобрать более тонко, используя функцию Excel Поиск решения. В отличие от Подбора параметра Поиск решения может для достижения нужного результата изменять или подбирать подходящие значения во многих ячейках. 6.1. Выполните команду: Данные – Поиск решения Если этого пункта в меню нет, то его следует загрузить, выполнив команду меню Разработчик – Надстройки. В открывшемся диалоговом окне следует поставить флажок около дополнения Поиск решения. 6.2. Сделайте необходимые настройки в окне диалога Поиск решения
В поле Установить целевую ячейку укажите адрес ячейки $F$13 (в ней погрешность).
В этом поле задаются адреса ячеек, значения которые, будут варьироваться в процессе поиска решения. В нашем случае - это адреса ячеек со значениями a и b. · Нажните на кнопку Найти решение. Начнется поиск решения. Так как у нас довольно точные коэффициенты а и b, то поиск займет немного времени. Когда Excel найдет решение, то откроется диалог, представленный на рисунке.
Поиск свелся к текущему решению. Все ограничения выполнены.
Произойдет изменение значений ячеек в соответствии с найденным решением. Обратите внимание, что коэффициенты а и b изменились, а погрешность уменьшилась.
ЗАДАНИЕ 7. Определите численность населения России в 2000 г. 7.1. Подставьте в ячейку С12 число 100, что соответствует 2000 г. 7.2. В Е12 скопируйте формулу из Е11. В ячейке Е12 появится искомое число.
ЗАДАНИЕ 8. Построите на одной диаграмме совмещенные графики роста численности населения на основе статистических и теоретических данных. 8.1. Выделите на построенном графике линию тренда и удалите ее, выполнив команду Очистить контекстно-зависимого меню линии тренда. 8.2. Добавьте в уже построенную диаграмму теоретические данные.
8.3. Оформите диаграмму в соответствии с рисунком, где показан примерный вид графиков.
ЗАДАНИЕ 9. Оформите таблицу на свой вкус (обрамление, заполнение, шрифты). ЗАДАНИЕ 10. Воспользуйтесь предварительным просмотром печати. 10.1. Разместите диаграмму на одном листе с таблицей. 10.2. Добейтесь хорошего расположения таблицы и диаграммы на листе. 10.3. Снимите сетку. 10 4. Установите верхний колонтитул: Численное моделирование. Работу выполнил (Фамилия и имя). В нижнем колонтитуле укажите дату и время.
ЗАДАНИЕ 11. Сохраните файл в личном каталоге под именем work8_1.xls
ЗАДАНИЕ 12. Распечатайте результаты работы на принтере.
ЗАДАНИЕ 13. Проанализировав данные таблицы и графика, сделайте вывод об адекватности предложенной математической модели реальному процессу (т.е. вывод о правильности описания роста населения формулой (1)).
ЗАДАНИЕ 14 (дополнительное). Самостоятельно попробуйте выбрать для построения линии тренда другие типы, а соответственно и другие формулы для описания математической модели.
Предъявите преподавателю:
ЗАДАЧА № 2 Несколько человек решили организовать видеокафе на 6 столиков по 4 места за каждым. С каждого посетителя будет взиматься плата за сеанс видеофильма и ужин (всем посетителям будет предлагаться один и тот же набор блюд). Администрация города постановила, что плата за вход не должна превышать $5. Требуется определить такую входную плату, при которой будет получена наибольшая выручка. Казалось бы, здесь и решать нечего. Разве не ясно, что чем больше входная плата, тем больше выручка. Вот и ответ: входная плата должна быть $5. Очень часто планирующие органы подобным образом и поступают. В нашем случае если сильно увеличить входную плату, то люди перестанут посещать кафе. Начать надо, как всегда, с построения математической модели. В чем были причины нашей неудачи? Мы предположили, что посещаемость не зависит от входной платы, и получили модель задачи, не соответствующую действительности. Значит, надо предполагать, что посещаемость зависит от входной платы. Обозначим входную плату через X. Тогда среднее число посетителей видеосалона является функцией от Х. Обозначим эту функцию через Р(Х). В задаче требуется найти такое значение А, при котором выручка, равная произведению входной платы на количество посетителей X* Р(Х), достигает максимума. Если бы функция Р(Х) была известна, то найти требуемый максимум не составило бы особого труда. Но эта функция не известна, поэтому попробуем найти хотя бы общий вид функции. Его можно указать, обобщив опыт работы подобных кафе:
Р(Х) = ах2-bх + с. (2)
Коэффициенты a, b и с для каждого кафе свои. Как же их определить? Проще всего найти значение с. Представьте себе невообразимое - в видеокафе пускают бесплатно (т. е. Х=0). Ясно, что свободных мест не будет. Следовательно, P(0) равно числу мест в кафе. С другой стороны, подставив 0 вместо X, получим Р(0)=с. Значит, с равно количеству мест. В нашем случае с=24 (6 столиков по 4 места за каждым). Определить а и b так же просто не удается. Справочников по посещаемости видеокафе еще нет. Поэтому здесь требуется эксперимент. Достаточно открыть кафе и установить на некоторый срок (дней на десять) определенную плату за вход. Среднее число посетителей и даст нам (приближенное!) значение функции. Установив другую плату за вход, найдем приближенное значение Р(Х) при новом X, и так несколько раз. Зависимость посещаемости от входной платы (на основе экспериментальных данных для конкретного кафе):
Пользуясь электронной таблицей, можно подобрать значения а и b способом, аналогичным описанному при решении задачи № 1,т.е. минимизацией погрешности между экспериментальной и теоретической выручкой. Затем можно определить, при какой входной плате выручка будет наибольшей. ХОД РАБОТЫ: ЗАДАНИЕ 1. Внимательно ознакомьтесь с постановкой задачи. На каких предположениях строится математическая модель? Что является исходными данными? Что должно явиться результатом?
ЗАДАНИЕ 2. Заполните таблицу эксперимента.
2.1. Сделайте заголовок и заполните шапку таблицы. 2.2. Отведите столбцы А и В таблицы соответственно лад коэффициенты а и b. 2.3. В столбец С занесите данные по входной плате. 2.4. В столбец D занесите экспериментальные данные по среднему числу посетителей. 2.5. В столбце Е подсчитайте выручку на основе экспериментальных данных как произведение входной платы на количество посетителей.
ЗАДАНИЕ 3. Подберите приближенное значение коэффициентов а и b.
Подбор коэффициентов а и b выполняется аппроксимацией экспериментальных данных по аналогии с задачей 1. 3.1. Постройте диаграмму типа X-Y по экспериментальным данным. (X - входная плата, Y- экспериментальные данные по количеству посетителей).= 3.2. Аппроксимируйте полученную кривую. При построении линии тренда следует выбрать полиномиальный тип (см. формулу (2)) и указать Y-пересечение = 24.
3.3. Занесите полученные значения коэффициентов а и b в таблицу.
ЗАДАНИЕ 4. Вычислите теоретическое количество посетителей и теоретическую выручку. 4.1. В столбце F вычислите по формуле (2) теоретическое количество посетителей, причем, как объяснялось выше, С=24. 4.2. В столбце G вычислите теоретическую выручку.
ЗАДАНИЕ 5. Вычислите отклонение между экспериментальной и теоретической выручкой и погрешность. 5.1. В столбце Н вычислите отклонение между экспериментальной и теоретической выручкой (аналогично заданию 4 в задаче 1). 5.2. В свободной ячейке столбца Н определите погрешность (аналогично заданию 5 в задаче 1).
ЗАДАНИЕ 6. Подберите коэффициенты а и b, стараясь минимизировать погрешность (аналогично заданию 5 в задаче 1).
ЗАДАНИЕ 7. Постройте графики. 7.1. Постройте на одной диаграмме два графика типа Х-Y (экспериментальный и теоретический) зависимости количества посетителей от входной платы P(Х) (аналогично заданию 8 в задаче 1). 7.2. Разместите диаграмму на одном листе с таблицей и оформите, как показано на рисунке. 7.3. Постройте на одной диаграмме два графика типа X-Y (экспериментальный и теоретический) зависимости выручки от входной платы X. 7.4. Разместите диаграмму на том же листе и оформите, как показано на рисунке. ЗАДАНИЕ 8. Определите, при какой входной плате выручка будет максимальна. Каково среднее число посетителей сеанса при найденной оптимальной входной плате?
ЗАДАНИЕ 9. Оформите таблицу на свой вкус (обрамление, заполнение, шрифты).
ЗАДАНИЕ 10. Воспользуйтесь предварительным просмотром печати. 10.1. Добейтесь хорошего расположения таблицы и двух диаграмм на листе. 10.2. Снимите сетку. 10.3. Установите верхний колонтитул: Численное моделирование. Работу выполнил <Фамилия и имя>. В нижнем колонтитуле укажите дату и время.
ЗАДАНИЕ 11. Сохраните файл в личном каталоге под именем work8_2.xls
ЗАДАНИЕ12. Распечатайте результат работы на принтере.
ЗАДАНИЕ 13. Проанализировав данные таблицы эксперимента и график, сделайте выводы об адекватности предложенной математической модели.
ЗАДАНИЕ 14.( дополнительное) Самостоятельно попробуйте выбрать для построения линии тренда другие типы, а соответственно и другие формулы для описания математической модели.
Предъявите преподавателю:
Дата добавления: 2014-12-25; Просмотров: 732; Нарушение авторских прав?; Мы поможем в написании вашей работы! Нам важно ваше мнение! Был ли полезен опубликованный материал? Да | Нет |