Анализ данных
Промежуточные итоги
• В диалоговом окне «Промежуточные итоги» следует указать: • в поле «При каждом изменении в» заголовок столбца, для которого необходимо вычислять итоги после каждого изменения данных; • в поле «Операция» - выбрать из списка нужную функцию для подведения итогов; • в поле «Добавить итоги по» - заголовок столбца, для вычисления итогов.
Пример
Для удобства дальнейшей работы с данными можно задать следующие режимы: • Заменить текущие итоги (вычисление новых промежуточных итогов для замены текущих. ); • Конец страницы между группами (вставка автоматических разрывов страниц после каждой группы промежуточных итогов); • Итоги под данными (вставка строк промежуточных итогов и общих итогов под позициями данных). Для удаления строк с итоговыми значениями предназначена кнопка Убрать все, расположенная в окне Промежуточные итоги.
Консолидация данных • Консолидация выполняется в том случае, если необходимо подытожить данные, расположенные в разных областях таблицы.
• В диалоговом окне следует: • в поле «Функция» - указать функцию, используемую при консолидации; • в поле «Ссылка» - задать первый исходный диапазон. Для этого выделить исходный диапазон с помощью мыши; • нажать кнопку Добавить. Ссылка будет отображена в поле «Список диапазонов» ; аналогичным способом указать другие исходные диапазоны. Можно указывать до 255 исходных диапазонов. Для удаления из списка диапазона следует выделить его и нажать кнопку «Удалить» ;
• в области Использовать в качестве имени определяется расположение заголовков итоговой таблицы – «подписи верхней строки» или «значения левого столбца» ; • чтобы установить связь между исходными и консолидированными данными следует включить опцию Создавать связи с исходными данными • Обработка данных представленных в консолидированной таблице с параметром «Создавать связи с исходными данными» дает возможность просмотреть и исходные данные. Для этого достаточно раскрыть необходимый раздел, щелкнув на знак «+» или перейти на вкладку под цифрой « 2» , расположенной в левом углу листа.
Подбор параметра • Процедура Подбор параметра осуществляет поиск определенного результата для целевой ячейки с помощью подбора значения другой ячейки (влияющей).
• В поле Установить в ячейке введите ссылку на ячейку, содержащую необходимую формулу. • Введите искомый результат в поле Значение. • В поле Изменяя значение ячейки введите ссылку на ячейку, содержащую подбираемое значение. В ходе выполнения команды появится окно Результат подбора параметра, в котором будет отображен результат работы команды. Изменения произойдут и на самом рабочем листе. Следует определить нужное действие Сохранять или отменять предлагаемый результат
Поиск решения Процедура поиска решения позволяет найти оптимальное значение, содержащейся в ячейке, которая называется целевой. Эта процедура работает с группой ячеек, прямо или косвенно связанных с формулой в целевой ячейке. Чтобы получить по формуле, содержащейся в целевой ячейке, заданный результат, процедура изменяет значения во влияющих ячейках. Чтобы сузить множество значений, используемых в модели, применяются ограничения.
• Если команда Поиск решения отсутствует в меню Сервис, следует установить надстройку Поиск решения (надстройка – это вспомогательная программа, служащая для добавления в Microsoft Office специальных команд или возможностей)
В поле Установить целевую ячейку ввести адрес или имя целевой ячейки, значение которой должно быть максимизировано, минимизировано, или приравнено какому-либо значению. Выполнить одно из следующих действий: • чтобы максимизировать значение целевой ячейки путем изменения значений влияющих ячеек, установить переключатель в положение максимальному значению; • чтобы минимизировать значение целевой ячейки путем изменения значений влияющих ячеек, установить переключатель в положение минимальному значению; • чтобы установить значение в целевой ячейке равным некоторому числу, установить переключатель в положение значению и ввести в соответствующее поле требуемое число.
• В поле Изменяя ячейки ввести имена или ссылки на изменяемые ячейки, разделяя их запятыми. Изменяемые ячейки должны быть прямо или косвенно связаны с целевой ячейкой. Допускается задание до 200 изменяемых ячеек. • Чтобы автоматически найти все ячейки, влияющие на формулу модели, нажать кнопку Предположить.
В поле Ограничения ввести все ограничения, накладываемые на поиск решения. В разделе Ограничения диалогового окна Поиск решения нажать кнопку Добавить. • В поле Ссылка на ячейку ввести адрес или имя ячейки, на значение которой накладываются ограничения. • Выбрать из раскрывающегося списка условный оператор ( <=, =, >=, цел или двоич), который должен располагаться между ссылкой и ограничением. • В поле Ограничение ввести число, ссылку на ячейку или ее имя либо формулу. • Выполнить одно из следующих действий.
Чтобы принять ограничение и вернуться в диалоговое окно Поиск решения, нажать кнопку OK. Нажать кнопку Выполнить и произвести одно из следующих действий: • чтобы сохранить найденное решение на листе, выбрать в диалоговом окне Результаты поиска решения вариант Сохранить найденное решение; • чтобы восстановить исходные данные, выбрать вариант Восстановить исходные значения.
Пример транспортной задачи
Заполнение окна команды