Надстройки Microsoft Excel

Часто пользователи персонального компьютера вынуждены решать достаточно сложные с математической точки зрения задачи по манипулированию цифровыми данными. Вычислительной мощность современных компьютеров хватает для решения таких задач, однако сам пользователь, не владеющий навыками программирования, часто не в состоянии формализовать такую задачу, т.е. подготовить ее в виде, пригодном для решения компьютером.

Microsoft Excel является достаточно удобным средством, позволяющим вводить и располагать многочисленные числовые данные в виде, удобном для обработки. Для решения сложных задач в нем имеется механизм подключения внешних программ – надстроек.

Надстройкой называется вспомогательная программа, добавляющая в Microsoft Excel специальные команды и возможности. Несколько надстроек включены в комплект поставки Microsoft Office в качестве компонентов Microsoft Excel, однако при установке программы на компьютер необходимо явно указать на необходимость их установки. Помимо встроенных, можно найти дополнительные надстройки на специализированных сайтах в сети Интернет. Файл надстройки имеет расширение. xla (формат Excel 2003) или. xlam.

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

Для включения и выключения надстроек следует воспользоваться меню Файл – Параметры, слева выбрать категорию «Надстройки», в нижней части окна в выпадающем списке «Управление:» выбрать «Надстройки Excel» и нажать кнопку «Перейти…». Откроется диалоговое окно, показанное на рис. 15.5. В этом окне перечислены найденные в системе надстройки, а снизу дается их краткое описание. Кнопка «Обзор» позволяет найти на носителях компьютера и загрузить в список надстройки, полученные из сети Интернет или от других пользователей.

Для включения или выключения надстройки нужно поставить или снять соответствующий ей флажок в диалоговом окне. После нажатия кнопки «ОК» на ленте Microsoft Excel появятся (или исчезнут) инструменты, принадлежащие надстройке.

15.4 Использование надстройки «Поиск решения»

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

Множество практических экономических и инженерных задач сводится к оптимизации какого-нибудь процесса. Решение таких задач (их еще называют задачами оптимизации или, в более широком смысле, анализом «Что, если») заключается в последовательном изменении данных во входных ячейках и наблюдении за значением в зависимой ячейке. Такие задачи в математике при отсутствии аналитического решения (т.е. возможности преобразовать формулу) решаются численно при помощи итераций (повторных вычислений). Итерационный метод в данном случае сводится к тому, что значение влияющей ячейки изменяется на некоторую небольшую величину (приращение) и проверяется, как изменится значение зависимой ячейки. Если значение изменилось в нужную сторону, делают новую итерацию – влияющей ячейке сообщают очередное приращение. Процесс повторяют снова и снова, пока не будет получено нужное значение в итоговой ячейке. При помощи только формул и размножения ячеек в Microsoft Excel реализовать метод итераций чаще всего не получается.

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

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

Диалоговое окно «Поиск решения»

Для запуска надстройки «Поиск решения» служит кнопка « Поиск решения» в группе Анализ вкладки Данные (кнопка доступна, если данная надстройка включена). Нажатие кнопки открывает одноименное диалоговое окно (рис. 15.6). Отметим, что в Microsoft Excel 2007 и более старых версиях программы окно выглядело несколько иначе.

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

На следующем этапе следует указать изменяемые (влияющие) ячейки. Для этого служит специальное поле.

Еще ниже приведен список ограничений. Изначально он пустой. Кнопка «Добавить» открывает диалоговое окно рис. 15.7, при помощи которого создаются ограничения.

Для создания ограничения следует указать ячейку или диапазон в левом поле, задать оператор (равно, больше или равно, меньше или равно) и ввести значение (либо указать ячейку или диапазон с ячейками, содержащими значения) в правом поле. Ограничение «цел» указывает, что ячейки в правом поле не могут принимать дробные значения; ограничение «бин» позволяет ячейкам принимать значение только «0» или «1»; ограничение «раз» указывает, что данные в каждой из ячеек выделенного массива должны различаться. Данные три вида ограничений не используют правое поле. Их можно применять только к влияющим ячейкам.

Кнопка «Добавить» в диалоговом окне добавляет созданное ограничение в список ограничений окна рис. 15.6. Окно рис. 15.7 при этом не закрывается, позволяя продолжить создание новых ограничений. Кнопка «OK» добавляет ограничение с закрытием окна.

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

Каждое вводимое ограничение занимает в списке диалогового окна «Поиск решения» (рис. 15.6) одну строчку. Если список большой, появляется полоса прокрутки. Ограничения в списке можно выделять, редактировать (кнопка «Изменить», откроется диалоговое окно, аналогичное рис. 15.7) или удалять из списка.

Кнопка «Сбросить» служит для быстрой очистки окна.

Рис. 15.6 Диалоговое окно средства «Поиск решения»

Рис. 15.7 Диалоговое окно создания ограничений для средства «Поиск решения»

Тонкие настройки средства «Поиск решения»

Если задача сложна и есть основания предполагать, что решение займет много времени, есть возможность изменить настройки средства «Поиск решения».

В первую очередь следует выбрать метод поиска решения. Симплекс-метод применим для решения линейных задач, т.е. задач, в которых целевая функция зависит от значений изменяемых ячеек линейно (т.е. в формуле целевой ячейки используются операторы сложения, вычитания и умножения; допускается использование функции «ЕСЛИ»). Для нелтинейных гладких задач (т.е. таких, у которых малое приращение значения во влияющей ячейке вызывает столь же малое приращение величины в целевой ячейке) используется метод обобщенного понижающего градиента (ОПГ). Ноаонец, эволюционный метод позволяет решать нелинейные жесткие задачи (малые приращения значений во влияющих ячейках могут вызвать как малое, так и значительное изменение значения в целевой ячейке).

Также ряд натроек содержится в диалоговом окне настроек средства «Поиск решения». Для его открытия в диалоговом окне рис. 15.6 следует щелкнуть на кнопке «Параметры». Значение параметров диалогового окна представлено в табл. 15.1.

Кнопки «Сохранить модель…» и «Загрузить модель…» позволяют сохранить настройки из диалоговых окон рис. 15.6 и рис. 15.8. При нажатии этой кнопки Microsoft Excel автоматически находит на рабочем листе вблизи целевой ячейки диапазон пустых ячеек и выделяет его, но пользователь может указать и другие ячейки. Microsoft Excel помещает в эти ячейки адрес и вид целевой функции, область изменяемых ячеек, все ограничения и константы из диалогового окна настроек (рис. 15.8). В дальнейшем Кнопка «Загрузить модель…» открывает такое же диалоговое окно, где предлагается выделить ячейки. Если в выделенных ячейках найдены нужные данные, они будут записаны в поля диалоговых окон, в противном случае Microsoft Excel предложит восстановить настройки средства «Поиск решения» по умолчанию либо отказаться от загрузки модели. На одном рабочем листе можно создать несколько диапазонов ячеек, таким образом, сохранив несколько моделей одного и того же расчета с различными настройками.