Информатика и компьютерная техника

Тема 18. Электронные таблицы MS Excel:

Подбор параметра. Работа со сценариями.

Подбор параметра

Средство Подбор параметра (Подбор параметра) представляет по сути средство для нахождения корня уравнения. Для того, чтобы найти корень уравнения, нужно задаться некоторым начальным значением. Обозначать начальное значение спустя. Исходя из этого первоначального значения, Excel пытается подобрать такое значение, чтобы удовлетворить уравнения. Обозначим его через. Если начальное значение задано неудачно, то корни не будет не найден, несмотря на то, что он существует. Рекомендации, в общем случае, по задания начального значения, дать невозможно, поскольку его выбор зависит от вида функции, которая находится в левой части уравнения. Единственное, что можно сказать [1], что начальное значение следует выбирать как можно ближе к значению искомого корня. Если уравнение имеет несколько корней (), то чтобы найти их все, нужно несколько раз применять средство Подбор параметра, выбирая начальные значения () близкими к корням, то есть ...,. Для нахождения таких значений можно протабулировать функцию и построить ее график. По графику можно приближенно определить корни, а значит и начальные приближения. Можно обойтись без построения графика, а использовать только результаты табулирования, но использование графика вносит определенную наглядность в решение задачи. Чтобы протабулировать функцию, необходимо задать промежуток табулирования шаг. Промежуток табулирования должен содержать все корни (или по крайней мере те, которые нас интересуют), а шаг должен быть меньше наименьшее расстояние между корнями. Определение промежутка табулирования и шага требует определенных математических исследований функции, чем мы заниматься не будем, а отошлем читателя к соответствующим математических курсов. В общем случае, при решении реальных практических задач, промежуток и шаг можно найти, исходя из содержания (экономического, физического) задачи.

 Рассмотрим на примере использования средства Подбор параметра для нахождения корней уравнения.

Пример 1

 Найти корни уравнения.

1) Сначала табулюемо функцию. За промежуток табулирования выберем [0; 4], а в шаге 0,5.

Соображения, по выбору промежутка табулирования такие: поскольку, согласно теореме Виета, произведение корней равно свободному члену, а свободный член есть положительный (= 2,1), то оба корня является одной знака. Сумма корней равен коэффициенту при с противоположным знаком, (т.е. = 3,5), поэтому оба корня положительны.Поскольку их сумма равна 3,5, то каждый из них не может быть больше суммы. Итак, на промежутке [0; 4] находятся все два корня.

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

Для табулирования функции формируем рабочий лист. Для этого, в A1 заносим нижнюю границу промежутка табулирования - 0; в A2 заносим сумму  нижня_границя + шаг , то есть 0 + 0,5 = 0,5. Выделяем ячейки A1 и A2. Используя маркер заполнения, заполняем ячейки A3: A9. Заносим в ячейку B1 формулу для вычисления при равной значению, которое находится в ячейке A1, то есть формулу = A1 * A1-3,5 * A1 + 2.1. Копируем эту формулу в ячейки B2: B9. Копирование можно осуществить перетаскиванием маркера заполнения. Сформированный рабочий лист в режиме отображения формул приведены ниже:

 В режиме отображения результатов, мы увидим

 Строим график функции, используя результаты табулирования. Напомним, что для этого нужно выделить ячейки A1: B9, после чего щелкнуть мышью на кнопке Добавить диаграмму. Как тип диаграммы, указываем - Точечная.

С построенного графика видно, что корни уравнения находятся на промежутках [0, 1] и [2, 3].

 Запускаем средство Подбор параметра для нахождения первого корня. Для этого выбираем из главного меню Сервис Þ Подбор параметра. На экран выводится диалоговое окно:

 В поле Установить в ячейке указываем ячейку B1. Для этого достаточно щелкнуть на ней мышью (если ячейка не видима - закрывается диалоговым окном, то можно воспользоваться кнопкой сворачивания окна). В поле Значение записываем 0 в поле Изменяя значение ячейки указываем ячейку A1. Именно это значение будет использовано средством Подбор параметра в качестве начального значения при поиске первого корня. Заполнено диалоговое окно имеет вид:

 После нажатия на кнопку ОК, на экран выводится диалоговое окно с результатом подбора параметра:

При этом, в рабочем листе в ячейке A1 устанавливается подобранное значение (= 0,77), то есть приближенное значение 1-го корня, а в ячейке B1 - значение функции (левой части уравнения) при. Эта величина называется невязкой. Итак, невязка равна 1,5 × 10 -5 .

 Если щелкнуть на кнопке ОК, то эти изменения в рабочем листе зафиксируются, то есть в ячейках A1 и B1 останутся найденные значения. Если щелкнуть на кнопке Отмена, то в этих ячейках восстановятся значения, что там были к применению поиска корня. Аналогично ищем второго корня. По начальное значение возьмем значение в ячейке A7, т.е. 3. подбирать его так, чтобы в ячейке B7 установившееся значение 0. диалоговое окно с введенными для подбора данным показано ниже: После нажатия кнопки ОК, на экран выводится диалоговое окно

а в ячейках A7, B7 на рабочем листе выводятся приближенное значение второго корня (2,73) и невязка (7,16 × 10 -5 ) соответственно: Работа со сценариями

 Мы рассмотрели с средства автоматизации вычислений такие как таблицы данных (или по другому, таблицы подстановок) и подбор параметра. Еще одним из таких средств является сценарии.

Сценарий - это именованный набор значений, используемых для вычислений в рабочем листе. Одновременно можно задать значение 32 переменным (ячейкам). Для создания сценариев используется так называемый диспетчер (менеджер) сценариев.

Рассмотрим работу со сценариями на следующем примере.

Пример 2

 Создадим рабочий лист, позволяющий рассчитать высоту подъема и дальность полета тела, брошенного под углом к ​​горизонту со скоростью (Рис.1).

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

 Как известно из школьного курса физики, время подъема тела до высшей точки траектории вычисляется по формуле

,

где - вертикальная составляющая начальной скорости. Здесь - начальная скорость, - угол, под которым брошено тело, - ускорение свободного падения, равное.

Время подъема тела равна времени падения, если пренебрегать сопротивлением воздуха, поэтому время полета тела. Тогда расстояние s, которую пролетит тело, составляет

, Где - горизонтальная составляющая начальной скорости.

 Высота подъема тела:

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

.

Используя приведенные соотношения, сформируем рабочий лист. Заносим в ячейки данные и расчетные формулы, задав значение начальной скорости -, значение угла, под которым бросается тело - 33º, и ускорение свободного падения - (слева указаны имена ячеек, а справа - данные, которые в них заносятся):

A1 Движение тела, брошенного под углом к ​​горизонту

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

A4 Начальная скорость v0 =

A5 Угол в градусах alpha =

A6 Ускорение свободного падения g =

A7 Промежуточные вычисления

A8 Угол в радианах rad =

A9 Горизонтальная составляющая скорости vg =

A10 Вертикальная составляющая скорости vv =

A11 Время подъема тела t =

A12 Время полета тела tpol =

A13 Результаты

A14 Высота подъема тела H =

A15 Дальность полета тела s =

B4 12

B5 33

B6 9,8

 В8 = В5 * ПИ () / 180 или = РАДИАНЫ (В5) [2]

 В9 = В4 * COS (B8)

 B10 = В4 * SIN (B8)

 B11 = B10 / B6

 B12 = 2 * B11

 B14 = B10 ^ 2 / (2 * B6)

 B15 = B9 * B12

В результате получим следующий рабочий лист (Рис.2):

 Оценим теперь, с помощью созданного рабочего листа, дальность и высоту полета каменного ядра баллисты (метательного оружия древних греков и римлян), когда известно, что угол, под которым выбрасывалось ядро, составлял 40º, а начальная скорость зависела от массы ядра:

 - Для ядра массой 10 кг она составляла;

 - Для ядра массой 20 кг она составляла.

 Для вычислений достаточно подставить входные данные (по очереди) в рабочий лист. В результате вычислений заходим, что в первом случае дальность полета составляет 726,04 м, а во втором - 492,40 м. Высоты подъема ядер соответственно составляют 152,31м и 103,29 м.

 Аналогично, с помощью созданного рабочего листа, можно вычислить дальность и высоту полета ядер другой древнего оружия - катапульты. Для начальной скорости   и угла 70º иметь: дальность полета составляет 321,39м, а высота подъема ядра - 220,76 м.

 Баллиста напоминает большой лук, а катапульта - ложку, что бросает камни по крутой траектории (под большим углом). Дальность метания камней баллисты составляла 400-800м, а легких стрел - 1000м. Катапульта бросала камни на расстояние 250-850м.

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

Создание сценария

 1) Для создания сценария, во-первых, необходимо присвоить имена ячейкам, которые содержат входные данные (В4: В6), которые будут меняться, а во-вторых, ячейкам, в которых будут находиться результаты вычислений (В14: В15). Присвоение имен можно осуществлять записывая имена непосредственно в поле имени Имя (Рис. 3):

или воспользовавшись командой Вставка Þ Имя Þ Присвоить  Этот, последний, вариант более удобным, поскольку Microsoft Excel автоматически предлагает как имена ячеек столбца В имена ячеек, находящихся в столбце А (то есть в столбце слева), следовательно имена не придется набирать на клавиатуре . Так, для ячейки В4 будет предложено имя Початкова_швидкисть_v0, а для ячейки В5 - имя Кут_в_градусах_alpha и т. Д. [3]

2) Запустить Диспетчер сценариев с помощью команды Сервис ÞСценариы  В результате на экран выводится диалоговое окно Диспетчера сценариев (рис. 4):

 Так будет выглядеть окно Диспетчера сценариев, если до этого не было создано никаких сценариев.

3) После того, как мы щелкнем мышью на клавиши Добавить  открывается диалоговое окно Добавление сценария  (рис. 5).

В поле Название сценария следует ввести имя сценария, который, в отличие от имен ячеек может начинаться с цифры, а не только буквы, содержать пробелы и иметь длину до 255 символов. Зададим как имя нашего первого сценария имя Баллиста - легкое ядро. В поле изменяемый ячейки следует указать адреса ячеек, где содержатся исходные данные.В данном случае, ячейки являются смежными, так отодвинув с помощью мыши диалоговое окно Добавление  сценария в сторону, выделяем с помощью мыши эти ячейки (рис. 6).Если ячейки с входными данными является несмежными, то чтобы их выделить, следует удерживать клавишу Ctrl при щелчке мышью на такой ячейке.

Зададим как изменяемые ячейки - ячейки В4: В5. Этим ячейкам были присвоены имена Початкова_швидкисть_v0 и Кут_в_градусах_alpha. После выделения ячеек с изменяемыми данными, окно автоматически переименуется из окна Добавление  сценария в окно Изменение  сценария.

В поле Примечание по умолчанию выводится имя автора сценария и дата его создания. Можно написать в это поле любую другую информацию, объясняет назначение и использование сценария.

 4) Щелкаем мышью на кнопке ОК, после чего на экран выводится диалоговое окно Значения ячеек сценария (рис. 7). В текстовых полях Початкова_шв и Кут_в_граду следует ввести данные, соответствующие начальным условиям легкого ядра, запущенного с помощью баллисты (и 40º).

 После ввода данных и нажатия кнопки ОК на экран будет выведено снова окно Диспетчера сценариев (рис. 8):

 5) Далее можно вывести на экран результаты расчета по созданным сценарием Баллиста - легкое ядро (для этого следует нажать кнопку Вывести  ) . А можно добавить к рабочему письмо другие сценарии. Повторяя те же действия, как и при создании сценария Баллиста - легкое ядро, создадим еще два сценария - Баллиста - тяжелое ядро и Катапульта, введя соответствующие данные (кн. Добавить). В результате, на экран будет выведено окно Диспетчера сценариев, иметь следующий вид (рис. 9):

 6) получим результаты расчетов сразу по всем сценариям выведя на экран отчет (кн. Отчет). После нажатия кн. Отчет на экран выводится диалоговое окно Отчет по сценарию (рис. 10):

 Нам предоставляется возможность получить один из двух типов отчетов: отчет в виде структуры или отчет в виде сводной таблицы. После выбора типа отчета, (выберем, например, отчет в виде структуры), следует указать ячейки, которые будут использоваться в отчете (если ячейки смежные, - выделить их с помощью мыши, а если есть несовместимые, то для их выделения воспользоваться клавишей Ctrl) . В данном примере мы выводим как результат содержимое ячеек, содержащих высоту подъема и дальность полета ядра (В14, В15). Можно и непосредственно написать адреса ячеек в поле Ячейки результата, отделяя адреса смежных ячеек двоеточием, а несмежных - точкой с запятой.Щелкнув мышью на кн. ОК, получим на отдельном листе отчет (Рис. 11).

 На рис. 11 таблица разделена на две части, поскольку не умещается на экране. Полученный отчет можно распечатать. Если он содержит ненужные столбцы, то их можно удалить из отчета перед печатью. Удаление столбцов осуществляется так же, как и в случае обычного рабочего листа.

 

 Если выводить отчет в виде сводной таблицы, то на экране получим (рис. 12):

Как видно из рисунка, в этом случае на экран не выводятся исходные данные (начальная скорость тела и угол, под которым тело брошено к горизонту), а только результаты расчетов. Выводы

Сделаем выводы из приведенного выше рассмотрения.

1. Имея рабочий лист (рис. 2), что позволяет рассчитывать параметры полета тела, мы можем, подставляя в ячейки исходные данные, рассчитать параметры полета. Если возникает потребность найти параметры полета для известной метательного оружия (баллиста, катапульта - как в примере), то не является удобным, всякое время подставлять ее исходные данные, - тем более, что их нужно помнить. Создав сценарии, как в приведенном выше примере, мы в случае необходимости, можем, вызвав соответствующий сценарий по имени, автоматически получить результат.

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

Дополним создан рабочий лист еще двум сценариям: Африка и Тундра. Как известно, ускорение свободного падения  g  не является величиной постоянной - для экватора его величина составляет, а для районов Земли, близких к полюсу -.

Присвоим ячейке B6 имя Прискорення_вильного_падиння_g (Вставка Þ Имя Þ Присвоить ) . Вызывая Диспетчер сценариев (Сервис Þ Сценарии  с помощью кнопки Добавить создаем сценарий Африка, взяв за изменяемую ячейку В6 и занося в нее значение 9,78. Аналогично создаем сценарий Тундра, с той разницей, что в ячейку В6 занесем значения 9,83.

Теперь достаточно легко комбинировать имеющиеся сценарии. Запустив Диспетчер сценариев, и выбрав сценарий Катапульта и дальше кн. Вывести, получим (Рис.13)

Рис. 13.

Выбрав теперь из списка сценариев сценарий Африка, мы получим параметры полета тела, выпущенного из катапульты, вычисленные с учетом африканских условий (рис. 14):

 

Рис. 14.

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

Рассмотренные примеры показывают, насколько легко могли бы планировать свои битвы древние римские полководцы, если бы они умели пользоваться Microsoft Excel, ну и должны его, конечно!

Совершенно аналогично применяются сценарии и в случае экономических задач. Если, например, рабочий лист содержит параметр, характеризующий влияние конкурентов, то создав сценарий, с наименьшим значением этого параметра, и назвав его, например "Слабые конкуренты", а также создав сценарии "Нормальные конкуренты" - со средним значением параметра и "Агрессивные конкуренты "с максимальным значением параметра, можно наблюдать, как будет меняться экономическая ситуация.

[1] в случае, если функция непрерывной

[2] Функция РАДИАНЫ (угол) является стандартной функцией Microsoft Excel, переводит величину угла, заданного в градусах, в величину угла в радианах.

[3] Можно обойтись и без присвоения имен ячейкам, но пользоваться таким сценарием будет неудобно.

Змiст

Тема 1. Основные понятия информатики.

Тема 2. Устройство компьютера и принципы его работы.

Тема 3. Периферийные устройства

Тема 4. Программное обеспечение

Тема 5. Операционная система Windows 95/98:

Тема 6. Операционная система Windows 95/98:

Тема 7. Операционная система Windows 95/98: Настройка

Тема 8. Стандартные программы Windows 95/98:

Тема 9. Стандартные программы Windows 95/98:

Тема 10. Редактор MS Word: Основы работы с редактором

Тема 11. Редактор MS Word: Форматирование документов

Тема 12. Редактор MS Word: Работа с таблицами.

Тема 13. Редактор MS Word: Дополнительные возможности

Тема 14. Электронные таблицы MS Excel: Основы работы

Тема 15. Электронные таблицы MS Excel: Работа с формулами Вот формул

Тема 16. Электронные таблицы MS Excel: Построение диаграмм. Географические карты

Тема 17. Электронные таблицы MS Excel: Сводные таблицы. Таблицы данных.

Тема 18. Электронные таблицы MS Excel:

Тема 19. Электронные таблицы MS Excel Средство Поиск развязку


Нові надходження

Всього підручників:

292