19100

Некоторые специальные возможност и Excel

Лабораторная работа

Информатика, кибернетика и программирование

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

Русский

2014-03-24

467.55 KB

8 чел.

Лабораторная работа № 2

Некоторые специальные возможност и Excel

Цель работы:

  1. Изучить возможности надстроек: поиска решения и подбор параметра.
  2. Решить уравнение с помощью подбора параметра и поиска решения.
  3. Решить систему у равнений с помощью поиска решения.

2.1  Автоформат

Команда Автоформат позволяет быстро оформить готовую таблицу воспользовавшись готовым Списком форматов.

Для того что бы пользоваться данной командой, необходимо добавить кнопку "Автоформат" на панель быстрого доступа:

1)  Щелкните  стрелку на панели быстрого доступа и выберите элемент Другие

команды.

2)  В открывшемся окне Параметры Excel в поле "Выбрать команды из" выберете из

списка "Все команды".

3)  В списке выберите кнопку "Автоформат" и щелкните элемент "Добавить".

2.2 Решение уравнений

Решение уравнений с помощью функции «Поиск решения»

Добавить надстройку Поиск решения можно следующим образом: Кнопка «Office» / Параметры  Excel. В появившемся диалоговом окне выбрать пунк Надстройки/ Поиск решения/Перейти, затем установить флажек напротив надстройки поиск решения и подтвердить ОК.

Одной из интереснейших особенностей Excel является возможность нахождения корней у равнений типа f(x)=a. Для этого необходимо в какой-либо из ячеек ввести предполагаемое (начальное) значение корня уравнения, в другой ячейке записать само уравнение f(x), выполнить команду Анализ | Поиск решения….В окне Поиск решения, в поле Установить целевую  ячейку записать адрес ячейки где записано уравнение f(x), в поле Равной: | Значению ввести значение a, в поле Изменяя ячейки ввести адрес ячейки где находится предполагаемое (начальное) значение корня у равнения и нажать кнопку Выполнить. 

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

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

Решим уравнение метод «Поиск решений»

 

Ответ: х=0.

Решим систему уравнение метод «Поиск решений»

При нажатии на кнопку Найти решение происходит решение системы уравнений и в ячейках высвечивается результат.

Ответ х=1, у=3.

2.3 Решение  уравнений с  помощью  функции  «Подбор параметра»

При  моделировании  экономических  ситуаций  часто  приходится  решать  уравнение вида:

f(x, p1, p2,…, pn)=0,                                                                                              (1)

где f-заданная функция;

х – неизвестная переменная;

p1, p2,…, pn – параметры модели.

Решение  таких  уравнений  может  быть  как  самостоятельной,  так  и  частью  более сложных  задач.  Как  правило,  исследователя  интересует  поведение  решения в зависимости от параметров pk, k= 1,n

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

у равнения).

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

Решим уравнение метод «Подбор параметра»

 

Сформиру ем  лист  электронной  таблицы,  как  показано  на  рису нке.  Уравнение  (2) запишем  в  клетку  B3,  начиная  со  знака  равенства,  а  вместо  переменной  x  у кажем адрес клетки A3, которая содержит значение начального приближения решения.

Метод,  применяемый в  EXCEL  для решения таких  у равнений  –модифицированный  конечными  разностями  метод  Ньютона,  который  позволяет  несильно  заботится  о  начальном  приближении,  как  этого  требуют  дру гие  численныеметоды решения  у равнений  (метод  хорд,  дихотомии  и  др.)  Единственно,  что  следует учесть  –  это  то,  что  будет  найдено  решение  ближайшее  к  выбранному  начальному приближению. Для  полу чения решения  у равнения  (2)  надо  выполнить следующую последовательность действий:

1.  Выполнить  команду  Данные/Работа с данными/Анализ «что-если»/Подбор параметра;

Заполним диалоговое окно Подбор параметра...:

Щелкнем левой  клавишей  мыши  в  поле  «Установим в  ячейке»,  после появления  в  нем  ку рсора,  переместим  у казатель  мыши  и  щелкнем  на  клетке  с форму лой, в нашем слу чае это  клетка B2, абсолютный адрес которой $B$3.

Этот  адрес можно было бы набрать на клавиатуре, после появления курсора в Поле.

- В поле «Значение» у становить 0.

- В  поле  «Изменяя  значение  ячейки»  ввести  адрес  клетки,  где  задано начальное  приближение  решения,  в  нашем  слу чае  это  клетка  A3  (абсолютный  адрес которой  $A$3 появится в поле после щелчка левой клавиши мыши на клеткеA3).

После  нажатия  на  кнопке  ОК  появится  окно  «Результат  подбора  параметра»

Ответ: х=-0.0356.

2.4 Создание сценариев

Часто  встречается  необходимость  одним  и  тем  же  ячейкам  присвоить  разные значения,  причем  так,  чтобы  не  потерять  предыдущие  значения.  В  Excel  это выполняется  за  счет  механизма  сценариев,  вызов  которого  производится  через  меню Данные/Работа с данными/Анализ «что-если»/Диспетчер сценариев.  Откроется  окно в  котором  будет  список всех имеющихся  сценариев (вначале этот  список пу ст).  Нажмите кнопку  "Добавить", появится окно.

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

Отчет  по сценариям.

После  того,  как сценарии  созданы,  можно  создать  отчет  по  этим  сценариям.  Для этого используется кнопка Отчет. В  окне  Отчет  по  сценарию  у кажите:  Тип  отчета  |  Структура,  а  в  поле  Ячейки результата  -  у кажите  адреса  ячеек  (разделенные  знаком  "точка  с  запятой")  в  которых содержатся итоги (например: A2;B8;B9).

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

данным сценариев и ячейкам результатов.

Отчет по методу поиск решений

Отчет по методу Подбор параметров

Вывод: в данной работе

  1. Изучили возможности надстроек: поиска решения и подбор параметра.
  2. Решили у равнение с помощью подбора параметра и поиска решения.
  3. Решили систему у равнений с помощью поиска решения.

 

А также другие работы, которые могут Вас заинтересовать

43375. Вибір потужності двигуна і дослідження процесу пуску електроприводу двигуна постійного струму 309 KB
  Кафедра автоматизації та енергоменеджменту ЗАВДАННЯ на виконання курсової роботи Маліновської Марії Володимирівни Тема курсової роботи: Вибір потужності двигуна і дослідження процесу пуску електроприводу двигуна постійного струму. Відповідно до заданого варіанту розрахувати потужність двигуна. Розрахувати можливе перевантаження обраного двигуна при роботі в короткочасному режимі із часом роботи хв.
43378. Використання табличного процесора MS Excel для рішення задач механіки та інженерії 1.57 MB
  Її аргумент Диапазон містить значення діапазону комірок з назвами деталей серед яких відшукуються ті що задовольняють умову поставлену в аргументі Критерий. Аргумент Диапазон_суммирования містить діапазон тих клітинок в якому відбувається підсумовування; при цьому обробляються тільки ті записи значення яких задовольняють поставлену умову. Функція ЕСЛИ використовується для перевірки умови стосовно значень та формул і повертає одне розраховане значення якщо задана умова після розрахунку дає значення ІСТИНА й інші розраховані значення...
43379. Криві в параметричному представленні 667.5 KB
  3 Визначити площу фігури обмеженої лініями 1будуємо графічне зображення фігури 2визначаю точки перетину кривих з віссю ОХ 3одна з одною 4обчислюємо площу Завдання 7.Авизначити вузлові точки xi у j та їх кількістьnкількість значень xim кількість значень у j відповідно до заданих для цих змінних проміжків та кроків hx i hy. Така крива епіциклоїда – могла б виникнути як траєкторія точки маленької окружності яка котиться по внутрішній фіксованій окружності. Обчислення каустики як траєкторії точки на окружностіщо котиться було...
43381. Формирование местного бюджета на примере муниципального образования полюстрово 755 KB
  Бюджетная система представляет собой регулируемую нормами права совокупность федерального бюджета, бюджетов субъектов Федерации и бюджетов органов местного самоуправления.
43382. Организация работы электротехнического цеха АТП г. Москва 1.25 MB
  Коэффициент механизации снижающий трудоемкость ЕО рассчитывается по формуле: где снижения трудоемкости за счёт применения моечной установки принимается 55 снижения трудоемкости путем замены обтирочных работ обдувом воздуха принимается 15 Трудоёмкость ТО1 tто1 = tн то1 K2 K5 = 311115 = 356 чел. ч tн то1 = 31 чел.
43383. Завдання та система органів державного управління безпекою України 187 KB
  Метою даного дослідження є проведення комплексного аналізу завдань, функцій системи органів державного управління безпекою України, а також розгляд існуючих проблем та перспектив розвитку, які повязані з їх практичним виконанням.