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

 

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

67884. ПРОБЛЕМЫ И ПЕРСПЕКТИВЫ КОНСТИТУЦИОННОГО РАЗВИТИЯ ЯПОНИИ 131 KB
  Что касается экономики Японии то она испытывает финансовый кризис; назрела необходимость коренной перестройки а любая перестройка страны начинается с Конституции. По общественному опросу проведенному газетой Иомиури сегодня в Японии 65 сторонников изменения Конституции.
67885. АДМИНИСТРАТИВНО-ПРАВОВОЙ РЕЖИМ ПРЕДПРИНИМАТЕЛЬСКОЙ ДЕЯТЕЛЬНОСТИ В ОСОБЫХ ЭКОНОМИЧЕСКИХ ЗОНАХ 72 KB
  Свободные экономические зоны на территории Российской Федерации начали создаваться в конце 80х годов XX в. Они рассматривались как вспомогательный институт государственной экономической политики и как способ стимулирования межгосударственных экономических отношений.
67886. ОСНОВНЫЕ НАПРАВЛЕНИЯ СОВЕРШЕНСТВОВАНИЯ ЗАКОНОДАТЕЛЬСТВА, РЕГУЛИРУЮЩЕГО ПОРЯДОК РАЗРЕШЕНИЯ ЖАЛОБ НА ДЕЙСТВИЯ ТАМОЖЕННЫХ ОРГАНОВ 73 KB
  В настоящее время началась реализация новой Концепции развития таможенных органов Российской Федерации на 2006-2010 гг. Данная концепция является основой разработки программ и планов развития таможенных органов Российской Федерации на среднесрочную и долгосрочную перспективу.
67887. РАЗВИТИЕ ЛИЦЕНЗИОННОГО ЗАКОНОДАТЕЛЬСТВА НА ФЕДЕРАЛЬНОМ И РЕГИОНАЛЬНОМ УРОВНЯХ 91.5 KB
  О лицензировании отдельных видов деятельности в соответствии с которой к лицензируемым видам деятельности относятся те ведение которых может повлечь за собой нанесение ущерба правам законным интересам здоровью граждан обороне и безопасности государства культурному наследию народов...
67888. РОЛЬ КОНСТИТУЦИИ РОССИЙСКОЙ ФЕДЕРАЦИИ В ПРОЦЕССЕ ФОРМИРОВАНИЯ И РЕАЛИЗАЦИИ ЗАДАЧ И ПРИНЦИПОВ УГОЛОВНОГО ПРАВА 125.5 KB
  Акты действующие в сфере гражданского пенсионного уголовного и других отраслей законодательства подвергаются Конституционным Судом интенсивной ревизии с целью прекращения действия юридических норм нарушающих Конституцию РФ. № 113ФЗ О присяжных заседателях федеральных судов общей юрисдикции...
67889. РОЛЬ ГЕОПОЛИТИКИ В МЕЖДУНАРОДНЫХ ОТНОШЕНИЯХ 73.5 KB
  В научной или научно-популярной литературе по истории нередко можно встретить утверждение о том что выгодное географическое положение позволило такому-то государству добиться того-то и данный фактор весьма значимый для исторического развития государства. Привлекательность этих незначительных территорий...
67890. НЕКОТОРЫЕ ВОПРОСЫ ПРИНЯТИЯ ЗАЯВЛЕНИЙ О ПРЕСТУПЛЕНИЯХ, ПРЕСЛЕДУЕМЫХ В ЧАСТНОМ ПОРЯДКЕ 47.5 KB
  Согласно статье 20 УПК РФ уголовное преследование осуществляется в публичном частно-публичном и частном порядке. Порядок возбуждения уголовных дел публичного и частно-публичного обвинения закреплен в главе 20 УПК РФ а возбуждение уголовных дел частного обвинения регламентируется статьей 318 УПК РФ.
67891. ОСОБЕННОСТИ ЮРИДИЧЕСКОЙ ОТВЕТСТВЕННОСТИ КРЕДИТНЫХ ОРГАНИЗАЦИЙ ЗА НЕПРЕДСТАВЛЕНИЕ НАЛОГОВЫМ ОРГАНАМ СВЕДЕНИЙ О ФИНАНСОВО-ХОЗЯЙСТВЕННОЙ ДЕЯТЕЛЬНОСТИ КЛИЕНТОВ 51 KB
  В частности в своей жалобе один из московских банков оспаривал конституционность следующих законоположений: 1 абзаца первого пункта 3 статьи 7 Закона Российской Федерации от 21 марта 1991 г. О налоговых органах Российской Федерации согласно которому налоговые органы наделены правом получать от предприятий...
67892. НЕКОТОРЫЕ АСПЕКТЫ ИСТОРИИ ВОЗНИКНОВЕНИЯ ЧАСТНОГО ПРАВА В РОССИИ 59 KB
  Изменение политического курса государства переход от плановой экономики к рыночной отразились на правовой системе России. Ныне многие ученые начинают склоняться к мысли что в основу российской правовой системы может быть положено деление права на публичное и частное.