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. Решили систему у равнений с помощью поиска решения.

 

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

79345. Гуморальная регуляция физиологических функций. Физиология желез внутренней секреции 192.5 KB
  Изучить закономерности гуморальной регуляции механизмы действия гормонов структурно-функциональную организацию эндокринной системы виды и функции гормонов желез внутренней секреции: щитовидной паращитовидных поджелудочной половых вилочковой надпочечников и эпифиза.
79346. ПЕРВЫЙ ЗВОНОК 60 KB
  В первый день сентября всем радость даря Каждый раз повторяется это Каждый собрался солнышком согрет Только первого класса что-то нет Вед: Нет первый класс здесь и с нетерпением ждет когда его позовут на линейку ведь первоклассники сегодня самые главные действующие лица праздника.
79350. Відкритття Америки. Христофор Колумб 46.5 KB
  Every year the 12th of October Americans celebrate Columbus Day. And today at our lesson we shall speak about this famous sailor. We shall read the texts answer the questions and work with the map, and by the end of our lesson you will have known more about the great discoverer.
79351. Безпечна поведінка – запорука міцного здоров’я 8.65 MB
  Стан напруги який виникає в людини під впливом сильних чинників Що таке наркотики Яка інша назва наркотику ЛСД Назвіть пожежонебезпечні матеріали Номер служби газу Які місця в автомобілі є найбільш безпечними для пасажирів Обмеження або позбавлення прав громадян...
79352. Подорож до країни Математики 438 KB
  Спочатку ми вирушимо до круглого озера, біля якого в будиночку живе дідусь Ох. Потім нам потрібно зустрітися в лісовій школі з тітонькою Совою та її учнями. Далі ми відпочинемо на лісовій галявині, поласуємо медом на пасіці, що знаходиться біля квітучої лісової галявини...
79353. Відомий і невідомий український меценат Петро Яцик 27 KB
  Мета: ознайомити учнів школи з відомим меценатом Петром Яциком; практична формування вміння розуміти що українська мова -– це скарб народу який учні повинні здобувати із задоволенням і досягати успіхів у навчанні завдяки плідній творчій праці; виховна прищеплювати любов до української мови...