Теория Excel_0. Решение задач оптимизации исследование влияния разных факторов на данные
 Скачать 474.43 Kb.
|
Структурирование таблицБольшие таблицы не очень удобно просматривать, поэтому для удобства работы MS Excel предоставляет возможность временно закрывать (открывать) отдельные области, создавать вложенные друг в друга части таблицы на определённых иерархических уровнях. Для этих целей применяется структурирование таблицы – автоматическое группирование строк и столбцов. Сначала выделяется область – смежные строки или столбцы соответствующей структурной части таблицы определённого иерархического уровня. Команда Данные Группироватьвыполняет группировку выделенных строк и столбцов. Если был выделен блок ячеек, то появляется диалоговое окно, в котором указывается вариант группировки (строки или столбцы). В результате создаётся структурный компонент таблицы первого иерархического уровня . Если внутри структурной части выделить группу и выполнить команду Данные Группировать (рис. 3.4), будет создан вложенный структурный элемент второго уровня и т.д.; максимальное число уровней – 8. Для отмены структурного компонента повторяется выделение области и выполняется команда Данные Разгруппировать.  Рис. 3.4. Группировка строк таблицы Анализ данныхПод анализом данных в Microsoft Excel понимаются методы, позволяющие лучше понять тенденции и закономерности, которым подчиняются табличные данные. Для анализа больших массивов данных в программе MS Excel предусмотрены следующие способы обработки таблиц: анализ «что – если»; сводные таблицы; процедура группировки (описана выше); консолидации; фильтрация; подведение промежуточных итогов. Анализ «что – если»С помощью средств анализа «что – если» в MS Excel можно экспериментировать с различными наборами значений в одной или нескольких формулах для изучения всех возможных результатов. Например, анализ «что – если» можно выполнить для составления двух бюджетов, каждый из которых предполагает определенный уровень дохода. Можно также указать результат, который должен быть получен при вычислении формулы, а затем определить, какие наборы значений обеспечат этот результат. В приложении Excel предусмотрены несколько разных средств, помогающих выполнять анализ «что – если». Подбор параметров Вычислительные возможности Excel позволяют решать как «прямые», так и «обратные» задачи; выполнять исследование области допустимых значений аргументов, подбирать значение аргументов под заданное значение функции. Для подбора параметров используется команда Данные Анализ «что –если» Подбор параметра. В диалоговом окне задается требуемое значение функции: в поле Изменяязначениеячейкиуказывается адрес ячейки, содержащей значение одного из аргументов функции. Excel решает обратную задачу: подбор значения аргумента для заданного значения функции. В случае успешного завершения подбора выводится окно, в котором указан результат – текущее значение функции для подобранного значения аргумента, новое значение аргумента функции содержится в соответствующей ячейке. При нажатии кнопки OK подобранное значение аргумента сохраняется в ячейке аргумента, при нажатии кнопки Отмена происходит восстановление значения аргумента. При неуспешном завершении подбора параметра выдаётся соответствующее сообщение о невозможности подбора аргументов. Диспетчер сценариев Для вариантных финансовых расчетов, основанных на задании раз- личных значений аргументов функции, целесообразно воспользоваться сценарным подходом, реализованным средствами Excel. Диспетчер сценариев используется для создания списка значений для подстановки в изменяемые ячейки листа. Каждый сценарий является набором предположений, который можно использовать для прогнозирования результатов пересчета листа. Используя диспетчер сценариев, можно: создавать несколько сценариев, в каждом из которых содержится до 32 значений подстановки в ячейки листа; присваивать имена, сохранять и выполнять сценарии листа; создавать итоговые отчеты по сценариям; объединять сценарии; защищать сценарии от изменений; скрывать сценарии; автоматически отслеживать изменения сценария. Сценарий – именованная совокупность значений изменяемых ячеек. Для ячеек, являющихся аргументами функций, можно задавать различные значения. Команда Данные Анализ «что – если» Диспетчер сценариеввызывает диалоговое окно для ячеек текущего рабочего листа. В окне Сценариипредставлен список сценариев текущего рабочего листа. Возможно объединение сценариев, находящихся в открытых книгах или на других листах текущей рабочей книги при нажатии кнопки Объединить. Для создания нового сценария следует нажать кнопку Добавить, при этом появляется новое диалоговое окно. В поле Название сценариявводится имя нового сценария – последо- вательность символов, максимальная длина имени не более 255 знаков. В окне Примечание можно записать поясняющий сценарий текст. По умолчанию сюда заносится имя пользователя и дата создания сценария. С помощью переключателя Запретить изменения реализуется защита значений изменяемых ячеек от редактирования. Переключатель Скрытьпозволяет не показывать имя сценария в списке. При нажатии на OKпоявляется диалоговое окно для ввода значений изменяемых ячеек. Для просмотра результатов подстановки значений изменяемых ячеек по определенному сценарию в диалоговом окне Диспетчера сценариев следует выбрать из списка имя сценария и нажать кнопку Вывести. Excel выполняет подстановку значений изменяемых ячеек сценария и производит расчет значения функции. Все изменения будут отражены на рабочем листе в ячейках, содержащих формулы и имеющих ссылки на изменяемые ячейки сценария; новые результативные значения также будут выведены. Кнопка Закрытьобеспечивает выход из окна Диспетчера сценариев, при этом в изменяемых ячейках сохраняются значения последнего участвовавшего в просмотре сценария. Кнопка Отчет предназначена для подготовки отчетов по сценариям, при ее нажатии появляется диалоговое окно для выбора типа итогового отчета. В поле Ячейки результата указывается адрес ячеек, значения которых зависят от изменяемых ячеек сценариев. Формируется два вида отчетов: итоги сценария – табличный отчет, содержащий для каждого сценария состав изменяемых ячеек и значение выбранных результатных ячеек; свободная таблица результатов подстановки значений в изменяемые ячейки и вычисления результатов подстановки. |