Самостоятельная работа по курсу

"Информационные системы и технологии"

Применение средств анализа «Что-если»

В предыдущих разделах мы изучили четыре средства анализа данных «Что-если», предоставляемые Excel: средствоПодбор параметра, таблицы подстановки, сце­нарии и средствоПоиск решения. Рассмотрим задачи использования этих средств в вымышленном клубе любителей бега, названном скромно «Общество любителей бега» (ОЛБ). В этих задачах средствоПодбор параметрабудет использоваться для расчета членских взносов, таблицы данных(подстановки)— для прогнозирования времени забегов спортсменов, сценарии — для прогнозирования суммы денежных средств, собранных при проведении соревнований, средствоПоиск решения — для про­гнозирования рекордов спортсменов и отбора участников эстафеты.

О клубе ОЛБ

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

Заседание правления клуба происходит в конце каждого кален­дарного года. Члены правления традиционно готовят для заинтересованных членов клуба такие документы.

Расчет членских взносов на следующий год.

Прогноз на следующий год поступления денежных средств от проведения ежегодных Больших соревнований.

Кроме того, многие члены клуба просят у правления предоставить им программные средства, которые могут помочь им в проведении различных соревнований. Эти средства должны делать следующее.

§ Предсказывать результаты соревнований.

§ Предсказывать результаты соревнований в отдельных видах спорта.

§ Подбирать участников эстафетных соревнований.

Председатель правления клуба записал необходимые программ­ные средства на компакт-диски и распространил их среди членов клуба на ежегодном итоговом собрании членов клуба. В качестве основных программных средств, предназначенных как для подготов­ки отчетов правления клуба, так и для выполнения расчетов другими членами клуба, были выбраны средства Excel для проведения анализа «Что если».

В следующих разделах на конкретных примерах покажем приме­нение этих средств для удовлетворения «вычислительных» потреб­ностей рядовых членов и членов правления клуба ОЛ Б.

Использование средства «Подбор параметра» для определения членских взносов.

Исходные данные.

Размер начального взноса для новых индивидуальных членов клуба — 900 руб. (ячейка В1, названная ВНЧ).

Размер ежегодного взноса для постоянных индивидуальных членов клуба 750 руб. (ячейка В2, названная ВПЧ).

Размер бессрочного (пожизненного) индивидуального або­немента для новых членов (одноразовый взнос) — 8 250 руб. (ячейка ВЗ, названная БИА).

Размер начального взноса для новых семейных членов клуба 1 650 руб. (ячейка В4, названная ВСН).

Размер ежегодного взноса для постоянных семейных членов клуба — 1 350 руб. (ячейка В5, названная ВСП).

Размер бессрочного (пожизненного) семейного абонемента для новых членов (одноразовый взнос) 12 000 руб. (ячей­ка В6, названная БСА).

Предполагаемое количество новых индивидуальных членов в следующем году 95 (ячейка В8, названная КНЧ).

Количество постоянных индивидуальных членов клуба135 (ячей­ка В9, названная КПЧ).

Количество бессрочных (пожизненных) индивидуальных або­нементов, которое предполагается продать в следующем году35 (ячейка В10, названная КБИА).

Предполагаемое количество новых семейных членов в следую­щем году 40(ячейка В11, названная КВСН).

Количество постоянных семейных членов клуба 65 (ячейка В12, названная КВСП).

Количество бессрочных (пожизненных) семейных абонемен­тов, которое предполагается продать в следующем году (ячей­ка В13, названная КБСА).

Общее количество членов подсчитывается в ячейке В15.

Общая сумма членских взносов подсчитывается в ячейке С15.

Задание 1. Количество бессрочных семейных абонементов

Применить средство Подбор параметра для определения количе­ства проданных бессрочных семейных абонементов, необходимого для того, чтобы общая сумма всех взносов в следующем году достигла суммы 900000 руб. При этом предполагается, что размеры всех видов взносов и количества остальных членов клуба остаются неизменны­ми (такими, как в исходных данных).

Задание 2. Желаемое количество новых семейных членов

Применить средство Подбор параметра для определения того, сколь­ко надо принять новых семейных членов, чтобы сумма взносов для этой категории членов в следующем году достигла суммы 90 000 руб. При этом предполагается, что размеры всех видов взносов и количества остальных членов клуба остаются неизменными (такими, как в исходных данных)

Использование таблиц подстановки для прогнозирования результатов забегов

В этом разделе вам нужно использовать таблицы подстановки с одним и двумя входами для прогноза результатов забегов. Для простоты пред­положим, что бегун всю дистанцию пробегает с постоянной средней скоростью. При этом предположении подсчитаем, за какое время бе­гун может преодолеть дистанции разной длины.

Задание 3.Расчет времени для одной средней скорости

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

Исходные данные.

Среднее время на 1 км 5.3     5.3 ←=В2 *В1

• В столбце А в диапазоне А4:А24 записаны значения расстояния в километрах (21 км — это половина марафонской дистанции).
• В столбце В в диапазоне В4:В24 будут подсчитаны значения времени, за которое можно преодолеть соответствующее рас­стояние.
• В ячейке В2 содержится значение, обратное средней скорости, т.е. значение времени (в минутах), за которое бегун пробегает 1 км. В ячейке ВЗ вычисляется время пробега, если расстояние равно значению, записанному в ячейке В1. Формула в ячейке ВЗ необходима для построения таблицы подстановки.

Задание 4.Расчет времени для нескольких средних скоростей.

В данном подразделе вам нужно использовать таблицу подстановки с двумя входами для расчета времени, за которое бегун может преодолеть дистанции разной длины при условии, что всю дистанцию бегун пробе­гает с постоянной скоростью, которая известна. Расчеты произво­дятся сразу для нескольких значений средней скорости. Рабочий лист, подготовленный для выполнения таких расчетов, показан на рис.

5.3 Среднее время на 1 км           5.3 3.5 4.5 5.5 6.5

На этом рабочем листе содержатся такие данные.

♦ В ячейке А2 — значение, обратное средней скорости, т.е. зна­чение времени (в минутах), за которое бегун пробегает 1 км. В ячейке A3 путем перемножения значений ячеек А1 и А2 вычисляется время пробега, если расстояние равно значению, записанному в ячейке А1. Формула в ячейке A3 необходима для построения таблицы подстановки.

В столбце А в диапазоне А4:А24 значения расстояния в ки­лометрах.

В третьей строке в диапазоне B3:J3 — значения, обратные сред­ней скорости.

В диапазоне B4J24 будут подсчитаны значения времени, за которое можно преодолеть соответствующее расстояние и при соответствующем значении средней скорости

Применение сценариев для прогнозирования сбора денежных сумм

В этом разделе нужно использовать сценарии для прогнозирования суммы взносов, поступивших от участников Больших соревнований. Рабочий лист, подготовленный для этих расчетов

На этом рабочем листе содержатся следующие данные. (Имена ячеек нужны для идентификации данных в отчетах, создаваемых на основе сценариев.)

• Размеры взносов, которые платят участники соревнования, зарегистрировавшиеся заранее, в зависимости от дистанции, в забеге на которую они участвуют, введены в ячейки B2:F2. Этим ячейкам присвоены имена соответственно 32К, 35К, 310К, 321К.31ИФ.(Первым символом стоит буква «З»)
• Размеры взносов, которые платят постоянные члены клуба, участвующие в соревнованиях, в зависимости от дистанции, в забеге на которую они участвуют, введены в ячейки B3:F3. Этим ячейкам присвоены имена соответственно Ч2К, Ч5К, Ч10К, Ч21К, ЧМФ.
• Размеры взносов, которые платят участники соревнования, за­регистрировавшиеся в день соревнований, в зависимости от дистанции, в забеге па которую они участвуют, введены в ячей­ки B4:F4. Этим ячейкам присвоены имена соответственно Д2К, Д5К, Д10К, Д21К, ДМФ.
• Число участников соревнования, зарегистрировавшихся за­ранее, в зависимости от дистанции, в забеге на которую они участвуют, введено в ячейки B6:F6. Этим ячейкам присвоены имена соответственно К32К, К35К, К310К, К321К, КЗМФ.
• Число членов клуба — участников соревнования, в зависимо­сти от дистанции, в забеге на которую они участвуют, введены в ячейки B7:F7. Этим ячейкам присвоены имена соответствен­но КЧ2К, КЧ5К. КЧ10К. КЧ21К. КЧМФ.
• Число участников соревнования, зарегистрировавшихся в день соревнования, в зависимости от дистанции, в забеге на которую они участвуют, введены в ячейки B8:F8. Этим ячейкам присвое­ны имена соответственно КД2К, КД5К, КД10К, КД21К, КДМФ.
• Суммы взносов, сделанных участниками соревнований, в зави­симости от дистанции, в забеге на которую они участвуют, вве­дены в ячейки B10:F10. Значение в ячейке В10 вычисляется по формуле =В2*В6+ВЗ*В7+В4*В8, в другие ячейки диапазона В 10:F10 введены аналогичные формулы. Этим ячейкам при­своены имена соответственно Итого_2К, Итого_5К, Итого_10К, Итого_21 К, Итого_МФ.
• Общая сумма взносов подчитывается в ячейке В11 как сумма значений ячеек диапазона B10:F10. Этой ячейке присвоено имя ВСЕГО.

Задание 5. Сумма взносов в дождливый день

§ Создайте сценарий вычисления суммы взносов участников соревнования, если соревнования проводятся в дождливый день

§ Данные для ввода значений:

§ К32К: 55

§ К35К: 125

§ К310К:110

§ К321К: 90

§ КЗМФ: 50

§ КЧ2К: 95

§ КЧ5К: 200

§ КЧ10К: 180

§ КЧ21К: 120

§ КЧМФ: 75

§ КД2К: 50

§ КД5К: 100

§ КД10К: 95

§ КД21К: 75

§ КДМФ: 40

Задание 6. Сумма взносов в обычный день

Создайте сценарий вычисления суммы взносов участников соревнования, если соревнования проводятся в обычный

день.

Данные для ввода значений:

: КД2К: 75 КД5К: 130 КД10К: 120 КД21К: 100 КДМФ: 55

Задание 7. Сумма взносов в солнечный день

§ Данные для ввода значений:

§ КД2К: 110 КД5К: 170 КД10К: 150 КД21К: 100 КДМФ:70

Задание 8.Создайте отчет по сценариям в виде структуры

Задание 9. Создайте отчет по сценариям в виде сводной таблицы. Представьте результаты в денежном формате.

Использование средства «Поиск решения» для прогнозирования результатов забега

В этом разделе вам нужно решить три задачи.

• Задача 1: задано время (в минутах), за которое бегун преодо­левает дистанцию, и среднее время, за которое бегун пробегает 1 км. Необходимо определить, сколько километров при таких условиях пробегает спортсмен.
• Задача 2: заданы дистанция (в километрах) и среднее время, за которое бегун преодолевает 1 км. Необходимо определить, за сколько минут спортсмен пробежит эту дистанцию.
• Задача 3 : Эта задача построена для прогнозирования результатов гонки за лидером. Заданы дистанция (в километрах) и время, за которое лидер преодолевает дистанцию (изменяемые значения). Необходимо определить, за какое среднее время бегун должен пробегать 1 км (изменяемая переменная), чтобы разность меж­ду временем лидера преодоления дистанции и аналогичным временем бегуна составляла определенную величину (значе­ние целевой функции).

В гонке за лидером имеется один бегун (лидер), кото­рый ведет забег с определенной заданной скоростью. Остальные участники забега стараются или не отстать от лидера, или перегнать его (кто на что способен). В забе­гах на длинные дистанции таких «лидеров» может быть несколько, они берут на себя роль лидера поочередно.

На этом рабочем листе содержатся такие данные, снабженные по­ясняющими заголовками.

• Данные для первой задачи ячейки В2:В4.
• Данные для второй задачи — ячейки В7:В9.
• Данные для второй задачи — ячейки В12:В 16

Задача 1: вычисление расстояния

Использовать средство Поиск решения для определения макси­мальной дистанции, которую может преодолеть бегун за 2 часа (120 минут), если один километр он пробегает в среднем за 5,9 мину­ты.

Сохранить эту задачу в виде модели.

Задача 2: вычисление времени

Использовать средство Поиск решения для определения времени, которое бегун преодолеет марафонскую дистанцию (42 км), если один километр он пробегает в среднем за 6,5 минуты.

Задача 3: гонка за лидером

С помощью средства Поиск решения определим, за какое в среднем время бегун должен пробегать I км, чтобы на дистанции 10 км это время было на 15 с (0,25 минуты) лучше аналогичного времени лиде­ра, который эту же дистанцию пробегает за 52 минуты.

Использование средства «Поиск решения» для подбора участников эстафеты

Задание 10

В этом разделе средство Поиск решения будет использовано для определения, подходит ли бегун А для участия в эстафете, если из­вестно среднее время, за которое он пробегает 1 км, длина трассы, количество участников эстафеты, желаемое время финиша и т.д. Рабочий лист, подготовленный для выполнения таких расчетов.

На этом рабочем листе содержатся все необходимые данные и формулы, снабженные поясняющими заголовками.

Решить следующую задачу: необходимо определить, подходит ли бегун А для участия в эстафете, если он пробегает 1 км в среднем за 5,4 минуты. Трасса эстафеты составляет 100 км, в эстафете 5 участни­ков, каждый участник должен пробежать свою часть трассы за время, не превышающее 1,5 часа (90 минут).

Задание по созданию гипертекстового отчета