Учебное_пособие_по_Excel1. В инженерных и экономических расчетах

• Название: В инженерных и экономических расчетах
• Дата: 21.05.2022
• Размер: 4.49 Mb.
• Имя файла: Учебное_пособие_по_Excel1.doc
• Тип: Учебное пособие; #541241
• страница: 14 из 29

КОНТРОЛЬНЫЕ ВОПРОСЫ К РАЗДЕЛУ 6

1. 
   Какие основные задачи выполняются при работе с базами данных?
   
2. 
   Как создать Форму в Excel?
   
3. 
   Какие операции можно выполнять в Форме?
   
4. 
   Как выполнить поиск по заданному критерию?
   
5. 
   Как выполнить сортировку записей?
   
6. 
   Какие фильтры существуют в Excel?
   
7. 
   Как выполнить промежуточные итоги?
   
8. 
   Каково назначение сводных таблиц?
   
9. 
   Как создать сводную таблицу?
   

7. НАДСТРОЙКИ ПОДБОР ПАРАМЕТРА И ПОИСК РЕШЕНИЯ

В электронных таблицах Excel существует возможность с помощью надстроек Подбор параметра и Поиск решения решать экономические и инженерные задачи. [3], [4]

0. Решение задач подбора параметра

Надстройка Подбор параметра запускается командой Сервис – Подбор параметра. Если в меню отсутствует команда Подбор параметра, следует воспользоваться командой Сервис – Надстройки и установить флажок Подбор параметра.

Задачи с использованием надстройки Подбор параметра сводятся к получению требуемое значение в одной ячейке путем изменения значения другой ячейки. Первая ячейка называется целевой, а значение второй ячейки – параметром. Целевая ячейка должна содержать формулу, прямо или косвенно ссылающуюся на ячейку с изменяемым параметром.

Пример 7.1. Известен размер вклада, который будет помещен в банк на некоторый срок под определенный процент (табл. 7.1).

Таблица 7.1

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




Подзадача 1. Требуется рассчитать сумму возврата вклада в конце периода.

Решение

Для расчета коэффициента увеличения вклада в ячейку B4 вводится формула =(1+B3)^B2, а для расчета суммы возврата вклада в ячейку B5 вводится формула =B1*B4.

Подзадача 2. Определить условия помещения вклада, наиболее подходящие для его владельца. Например, рассчитать процентную ставку вклада, при которой сумма возврата вклада будет составлять 8 000 рублей.

Решение

Значение в ячейке B5 зависит от значения ячейки B3. Необходимо подобрать параметр в ячейке B3 таким образом, чтобы значение в целевой ячейке стало равным заданному значению, в данном случае 8 000 рублей.

Решается задача методом последовательных приближений по следующей схеме:

1) указатель устанавливается в целевую ячейку B5;

2) вызываем процедура Подбор параметра из меню Сервис (рис.7.1);

3) в диалоговом окне Подбор параметра задается значение в целевой ячейке, равное 8 000;

4) в поле Изменяя значение ячейки вводится адрес варьируемой ячейки B3.

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



Рис.7.1. Диалоговое окно Подбор параметра

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

Надстройка Поиск решения запускается командой Сервис – Поиск решения. Если в меню отсутствует команда Поиск решения, следует воспользоваться командой Сервис – Надстройки и установить флажок Поиск решения.

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

Большую часть задач представляют собой задачи линейного программирования, т.е. такие, у которых критерий оптимизации и ограничения – линейные функции. В этом случае для решения задачи следует установить флажок Линейная модель в окне Параметры поиска решения. Это обеспечит применение симплекс-метода. В противном случае, даже для решения линейной задачи, будут использоваться более общие (т.е. медленные) методы.

Решая задачи с нелинейными зависимостями, следует:

• 
  ввести предварительно предположительные значения искомых переменных (иногда легко получить графическое представление решения и сделать приблизительные выводы о решении);
  
• 
  в окне Параметры поиска решения снять (если установлен) флажок Линейная модель.
  

При необходимости проводится анализ решения. Часто добавляют также представление решения в виде графиков или диаграмм. Можно получить и отчет о поиске решения. Отчеты бывают трех типов: Результаты, Устойчивость, Пределы. Тип отчета выбирается по окончании поиска решения в окне Результаты поиска решения в списке Тип отчета (можно выбрать сразу два или три типа).

• 
  Отчет типа Результаты содержит окончательные значения параметров задачи целевой функции и ограничений.
  
• 
  Отчет типа Устойчивость показывает результаты малых изменений параметров поиска решений.
  
• 
  Отчет типа Пределы показывает изменения решения при поочередной максимизации и минимизации каждой переменной при неизменных других переменных.