- отсортировать таблицу по столбцу, содержащему группы, по которым надо подвести итоги;
- установить курсор в любую ячейку этого столбца;
- задать команду Данные ® Итоги;
- в поле При каждом изменении в указать столбец с группами, по которым надо подводить итоги;
- в поле Использовать функцию указать СУММА;
- в перечне Добавить итоги по указать столбцы, значения в которых должны быть просуммированы;
- нажать кнопку ОК.
Для скрытия или высвечивания входящих в итоги промежуточных данных нажать кнопку с номером уровня (чем выше номер, тем больше детализирующей информации отображается на экране). Для скрытия детализирующих данных по определенной группе нажать кнопку «-» (минус) слева от данной группы. Нажатие кнопки «+» (плюс) приводит к высвету детализирующей информации по группе.
Для удаления полученных таким образом итогов установить курсор в любую ячейку столбца, содержащего группы, задать команду Данные ® Итоги и нажать кнопку Убрать все.
6.8. АНАЛИЗ ДАННЫХ
6.8.1. Подбор параметра
Пусть имеется формула, которая прямо или косвенно зависит от некоторого параметра. Задача состоит в определении такого значения этого параметра, которое позволяет получить нужный результат формулы.
Математическая суть задачи состоит в решении уравнения f(х) = а, где функция f(х) описывается заданной формулой, х -искомый параметр, а — требуемый результат формулы.
Для решения этой задачи необходимо выполнить следующие действия:
1. Выделить ячейку, содержащую формулу, для которой нужно найти определенное решение.
2. В меню Сервис выбрать команду Подбор параметра.
3. В поле Установить в ячейке ввести ссылку на ячейку, содержащую формулу (по умолчанию в это поле вводится адрес текущей ячейки).
4. В поле Значение ввести значение, которое нужно получить по заданной формуле.
5. В поле Изменяя ячейку ввести ссылку на ячейку, содержащую значение изменяемого параметра (эта ячейка называется изменяемой).
6. Щелкнуть по кнопке ОК. Пример.
Дано уравнение
Х^2 + ЗХ - 2 = А,
где А — требуемый результат формулы; X — искомый параметр. Определить такое значение параметра X, при котором А будет равно 20.
1. Ввести в ячейку А4 указанную формулу. В формуле сделать ссылку на ячейку, в которой условно находится параметр X.
2. Задать команду Сервис > Подбор параметра.
3. В поле Установить в ячейке указать А4 (по умолчанию в это поле вводится адрес текущей ячейки).
4. В поле Значение ввести — 20.
5. В поле Изменяя значение ячейки указать адрес ячейки, в которой должен находиться параметр X.
После выполнения команды в изменяемой ячейке появится значение параметра X, при котором результат формулы равняется заданной величине. При этом будет пересчитана вся таблица, т.е. изменятся значения, прямо или косвенно зависящие от изменяемого параметра.
Подбор параметра можно выполнять графически, перетаскивая точки данных на диаграмме.
6.8.2. Таблицы подстановки данных
Пусть имеется формула, которая зависит от некоторых переменных. Задача состоит в определении результатов формулы при различных значениях этих переменных. Математическая сущность задачи состоит в табулировании функции. Эта задача является обратной к задаче подбора параметров.
Анализ выполняется при помощи таблицы подстановки данных.
Таблица подстановки данных представляет собой блок ячеек, в котором выводятся результаты подстановки различных значений переменных в одну или несколько формул.
Анализ может проводиться для функций с одной переменной или для функций с двумя переменными. Причем в случае одной переменной можно табулировать сразу несколько функций, зависящих от этой переменной.
Анализ формулы начинается с подготовки таблицы подстановки:
1. Левую верхнюю ячейку блока, отведенного под таблицу, оставить пустой.
2. В левый столбец блока, начиная со второй ячейки, последовательно ввести значения варьируемой переменной.
3. В верхнюю строку блока, начиная со второй ячейки, ввести ссылки на ячейки с анализируемыми формулами.
Допускается и другая ориентация таблицы, когда значения варьируемой переменной вводятся в первую строку, а анализируемые формулы — в первый столбец блока.
4. Выделить таблицу подстановки (в ячейки, расположенные рядом с таблицей, можно ввести пояснительные надписи, но эти ячейки не входят в таблицу подстановки данных и, следовательно, не выделяются).
5. В меню Данные выбрать команду Таблица подстановки.
6. Если значения варьируемой переменной расположены в столбце, то надо щелкнуть по полю Подставлять значения по строкам в и ввести в это поле адрес изменяемой ячейки (т.е. ячейки, которая играет роль варьируемой переменной в формуле).
Если значения варьируемой переменной расположены в строке, то адрес изменяемой ячейки вводится в поле Подставлять значения по столбцам.
7. Щелкнуть по кнопке ОК. Таблица будет заполнена значениями.
В случае анализа зависимости формулы от двух переменных таблица подстановки подготавливается по-другому:
1. В левую верхнюю ячейку блока, отведенного под таблицу, ввести ссылку на ячейку с анализируемой формулой.
2. В левый столбец блока, начиная со второй ячейки, последовательно ввести значения одной из варьируемых переменных.
3. В верхнюю строку блока, начиная со второй ячейки, ввести значения другой варьируемой переменной.
4. Выделить таблицу подстановки.
5. В меню Данные выбрать команду Таблица подстановки.
6. В поле Подставлять значения по строкам в ввести ссылку на ячейку с переменной, зачения для которой расположены в левом столбце таблицы подстановки.
7. В поле Подставлять значения по столбцам в ввести ссылку на ячейку с переменной, значения лля которой расположены в мерной строке таблицы подстановки, и Щелкнуть по кнопке ОК. Таблица, будет заполнена значениями.
6.8.3. Поиск решения
Программа Поиск решения является инструментом оптимизации. С помощью этой программы можно найти оптимальное или заданное значение некоторой ячейки путем подбора значений нескольких ячеек, удовлетворив нескольким граничным условиям.
Целевая ячейка — это ячейка, для которой нужно найти максимальное, минимальное или заданное значения.
Изменяемые ячейки — это ячейки, от которых зависит значение целевой ячейки. Целевая ячейка должна содержать формулу, прямо или косвенно зависящую от изменяемых ячеек. Поиск решения подбирает значения изменяемых ячеек до тех пор, пока не будет найдено решение.
Ограничение - это условие, накладываемое на некоторую ячейку. Ограничения могут быть наложены на любые ячейки таблицы, включая целевую ячейку и изменяемые ячейки.
Чтобы запустить процедуру поиска решения, надо:
1. В меню Сервис выбрать команду Поиск решения. Откроется диалоговое окно Поиск решения.
2. В поле Установить целевую ячейку ввести ссылку на ячейку, в которой нужно получить максимальное, минимальное или заданное значения.
3. В поле Изменяя ячейки ввести ссылки на изменяемые ячейки. (Если щелкнуть по кнопке Предположить, то Поиск решения самостоятельно определит изменяемые ячейки).
4. Для задания ограничений щелкнуть по кнопке Добавить.
5. В открывшемся диалоговом окне следует:
• в поле Ссылка на ячейку ввести ссылку на ячейку, содержащую формулу, которая определяет ограничение; формула должна прямо или косвенно зависеть от одной или нескольких изменяемых ячеек;
• во втором поле выбрать оператор ограничения (>, <, = и т.д.);
• в поле Ограничение ввести значение ограничения.
6. Для задания следующего ограничения щелкнуть по кнопке Добавить и повторить операции пункта 5.
7. Когда все ограничения будут заданы, щелкнуть по кнопке ОК, чтобы вернуться в диалоговое окно Поиск решения.
8. Изменять и удалять ограничения можно с помощью кнопок Изменить и Удалить.
9. С помощью кнопки Параметры можно задать: максимальное время решения; предельное число итераций; относительную погрешность; допустимое отклонение; сходимость; метод поиска.
Если известно, что решаемая задача линейная (т.е. зависимости между переменными линейны), то следует включить режим Линейная модель: процесс решения значительно ускорится.
Для возврата в диалоговое окно Поиск решения щелкнуть по кнопке ОК.
10. Для инициализации процедуры поиска решения щелкнуть по кнопке Выполнить. Полученные результаты будут выведены на рабочий лист.
После завершения процедуры решения в диалоговом окне Результаты поиска решения можно выполнить один из следующих вариантов:
• сохранить найденное решение или восстановить исходные значения на рабочем листе;
• сохранить параметры поиска решения в виде модели;
• сохранить решение в виде сценария;
• просмотреть любой из встроенных отчетов.
Текущие установочные параметры для поиска решения можно сохранить в виде модели.
Для этого надо в диалоговом окне Параметры поиска решения щелкнуть по кнопке Сохранить модель и указать на рабочем листе область для сохранения модели (можно указать только верхнюю ячейку области).
При сохранении модели запоминаются целевая ячейка, изменяемые ячейки, ограничения и параметры поиска решения.
Чтобы впоследствии загрузить модель, надо щелкнуть по кнопке Загрузить модель в диалоговом окне Параметры поиска решения. (Диалоговое окно Параметры поиска решения открывается при щелчке по кнопке Параметры в диалоговом окне команды Сервис - Поиск решения).
Найденные решения (значения изменяемых ячеек) можно сохранить в качестве сценария. Для этого нужно:
1. В диалоговом окне Результаты поиска решения выбрать Сохранить сценарий.
2. В поле Название сценария ввести имя сценария. Просмотреть сценарии можно с помощью команды Сервис -Сценарии.