Студопедия

КАТЕГОРИИ:


Архитектура-(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 необходимо перемасштабировать оси:

  • Выделите ось X.
  • Вызовите контекстно-зависимое меню и выполните коман­ду: Формат оси - Шкала
  • Установите минимальное значение X, основную единицу измерения и пересечение с осью У.
  • Аналогично Перемасштабируйте ось Y.

2.3. Аппроксимируйте полученную кривую.

 

Необходимо статистические данные по численности населе­ния представить на графике плавной кривой (аппроксимировать). Эта кривая называется линией тренда. Для построения линии тренда:

  • Выделите линию графика.

· Выполните команду Макет ­– Линии тренда­ – экспоненциальное приближение

  • Выберите экспоненциальный тип (см. формулу (1)).
  • Выберите Линию тренда.
  • В контекстном меню выберите Формат линии тренда
  • Установите флажок Показывать уравнение на диаграмме и нажмите кнопку Закрыть.

 

В результате на графике появится линия тренда и уравнение с подобранными коэффициентами а и 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 (в ней погрешность).

  • Установите переключатель Минимум
  • В поле Изменяя ячейки переменных укажите $А$3:$В$3

В этом поле задаются адреса ячеек, значения которые, будут варьироваться в процессе поиска решения. В нашем случае - это адреса ячеек со значениями a и b.

· Нажните на кнопку Найти решение.

Начнется поиск решения. Так как у нас довольно точные коэффициенты а и b, то поиск займет немного времени. Когда Excel найдет решение, то откроется диалог, представленный на рисунке.

 

Поиск свелся к текущему решению. Все ограничения выполнены.

  • Нажмите на кнопку <ОК>.

Произойдет изменение значений ячеек в соответствии с най­денным решением. Обратите внимание, что коэффициенты а и b изменились, а погрешность уменьшилась.

 

ЗАДАНИЕ 7. Определите численность населения России в 2000 г.

7.1. Подставьте в ячейку С12 число 100, что соответствует 2000 г.

7.2. В Е12 скопируйте формулу из Е11. В ячейке Е12 появится искомое число.

 

ЗАДАНИЕ 8. Построите на одной диаграмме совмещенные графи­ки роста численности населения на основе статистических и теоре­тических данных.

8.1. Выделите на построенном графике линию тренда и уда­лите ее, выполнив команду Очистить контекстно-зависимого меню линии тренда.

8.2. Добавьте в уже построенную диаграмму теоретические данные.

  • В таблице эксперимента выделите теоретические данные Е2.Е12.
  • Установите указатель мыши на правой границе выделен­ного блока.
  • Нажмите левую кнопку мыши и прибуксируйте данные на диаграмму.
  • В появившемся окне сделайте настройку (если это необхо­димо).

8.3. Оформите диаграмму в соответствии с рисунком, где показан примерный вид графиков.

 

ЗАДАНИЕ 9. Оформите таблицу на свой вкус (обрамление, запол­нение, шрифты).

ЗАДАНИЕ 10. Воспользуйтесь предварительным просмотром печати.

10.1. Разместите диаграмму на одном листе с таблицей.

10.2. Добейтесь хорошего расположения таблицы и диаг­раммы на листе.

10.3. Снимите сетку.

10 4. Установите верхний колонтитул: Численное моделиро­вание. Работу выполнил (Фамилия и имя). В нижнем ко­лонтитуле укажите дату и время.

 

ЗАДАНИЕ 11. Сохраните файл в личном каталоге под именем work8_1.xls

 

ЗАДАНИЕ 12. Распечатайте результаты работы на принтере.

 

ЗАДАНИЕ 13. Проанализировав данные таблицы и графика, сде­лайте вывод об адекватности предложенной математической мо­дели реальному процессу (т.е. вывод о правильности описания рос­та населения формулой (1)).

 

ЗАДАНИЕ 14 (дополнительное).

Самостоятельно попробуйте выбрать для построения линии тренда другие типы, а соответственно и другие формулы для опи­сания математической модели.

 

Предъявите преподавателю:

  • файл work8_l.xls;
  • распечатку результатов работы.

 

ЗАДАЧА № 2

Несколько человек решили организовать видеокафе на 6 столиков по 4 места за каждым. С каждого посетителя будет взиматься плата за сеанс видеофильма и ужин (всем посетителям будет предлагаться один и тот же набор блюд). Администрация города постановила, что плата за вход не должна превышать $5. Требуется определить такую входную плату, при которой будет получена наибольшая выручка.

Казалось бы, здесь и решать нечего. Разве не ясно, что чем больше входная плата, тем больше выручка. Вот и ответ: входная плата должна быть $5. Очень часто планирующие органы подоб­ным образом и поступают. В нашем случае если сильно увели­чить входную плату, то люди перестанут посещать кафе.

Начать надо, как всегда, с построения математической мо­дели. В чем были причины нашей неудачи? Мы предположи­ли, что посещаемость не зависит от входной платы, и получи­ли модель задачи, не соответствующую действительности. Зна­чит, надо предполагать, что посещаемость зависит от входной платы.

Обозначим входную плату через X. Тогда среднее число посе­тителей видеосалона является функцией от Х. Обозначим эту функцию через Р(Х). В задаче требуется найти такое значение А, при котором выручка, равная произведению входной платы на количе­ство посетителей X* Р(Х), достигает максимума. Если бы функ­ция Р(Х) была известна, то найти требуемый максимум не соста­вило бы особого труда. Но эта функция не известна, поэтому попробуем найти хотя бы общий вид функции. Его можно ука­зать, обобщив опыт работы подобных кафе:

 

Р(Х) = ах2-bх + с. (2)

 

Коэффициенты a, b и с для каждого кафе свои. Как же их оп­ределить? Проще всего найти значение с. Представьте себе невообразимое - в видеокафе пускают бесплатно (т. е. Х=0). Ясно, что свободных мест не будет. Следовательно, P(0) равно числу мест в кафе. С другой стороны, подставив 0 вместо X, получим Р(0)=с. Значит, с равно количеству мест. В нашем случае с=24 (6 столи­ков по 4 места за каждым).

Определить а и b так же просто не удается. Справочников по посещаемости видеокафе еще нет. Поэтому здесь требуется экс­перимент.

Достаточно открыть кафе и установить на некоторый срок (дней на десять) определенную плату за вход. Среднее число посетителей и даст нам (приближенное!) значение функции. Установив другую плату за вход, найдем приближенное зна­чение Р(Х) при новом X, и так несколько раз.

Зависимость посещаемости от входной платы (на основе экспериментальных данных для конкретного кафе):

 

Входная плата X (в $) Среднее число посетителей сеанса Р(Х)
   
1,5 17,5
   
2,5  
  12,4
3,5  
  9,2
   

Пользуясь электронной таблицей, можно подобрать значения а и 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.( дополнительное) Самостоятельно попробуйте выбрать для построения линии тренда другие типы, а соответственно и другие формулы для описания математической модели.

 

Предъявите преподавателю:

  • файл work8_2.xls
  • распечатку результатов работы.

 

 




Поделиться с друзьями:


Дата добавления: 2014-12-25; Просмотров: 732; Нарушение авторских прав?; Мы поможем в написании вашей работы!


Нам важно ваше мнение! Был ли полезен опубликованный материал? Да | Нет



studopedia.su - Студопедия (2013 - 2024) год. Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав! Последнее добавление




Генерация страницы за: 0.013 сек.