8.6.Использование сценариев

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

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

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

При создании сценария в диалоговом окне «Добавление сценария» вводится его имя и ссылки на ячейки, набор значений которых и определяет сценарий (указываются диапазоны ячеек или адреса ячеек, элементы списка разделяются символом ‘;’). Щелчок по кнопке OK открывает новое диалоговое окно, в котором вводятся конкретные значения для указанных изменяемых в сценарии ячеек. Набор ячеек запоминается в сценарии щелчком по кнопке Добавить или OK (кнопка Добавить позволяет продолжить создание новых сценариев, а OK возвращает пользователя в диалоговое окно Диспетчера сценариев).

Диспетчер сценариев позволяет изменить существующие сценарии и удалить сценарии.

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

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

Для работы со сценариями в VBA используется объект Scenario. Каждый объект WorkSheet содержит семейство объектов Scenarios. Для добавления сценария в рабочий лист используется метод Add. В качестве аргументов ему передаются: текстовая строка Name, содержащая имя сценария; диапазон ChangingCells, представляющий ячейки, к которым применяется сценарий (изменяемые ячейки); одно значение или массив значений, применяемых к изменяемым ячейкам (Values); текстовая строка комментария Comment; флажок защиты сценария Locked (если равен True, изменения запрещены) и флажок Hidden, позволяющий скрыть защищенный сценарий (защита сценария работает, если установлен соответствующий режим защиты рабочего листа).

Для выполнения других операций над сценариями также существуют соответствующие методы (Show, Delete и т.п.). Получить справку по свойствам и методам объекта можно в окне просмотра объектов в редакторе VBA.

8.7.Поиск и отбор данных в таблицах Excel

Часто при работе с большими массивами данных, представленными в виде таблицы, возникает необходимость отбора данных, соответствующих различным критериям. Для решения этой задачи в Excel используются команды подменю Фильтр в меню Данные (команды Автофильтр (метод AutoFilter объекта Range) и Расширенный фильтр… (метод AdvancedFilter объекта Range)).

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