Анализ данных Вычисление итогов Для автоматического вычисления

Анализ данных

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

Пример Продавец Иванов Петров Иванов Модель Стол Стул Количество 23 45 32 12 54 66

Выполнение сортировки Продавец Иванов Петров Модель Стол Стул Количество 23 32 12 66 45 54

Команда

• В диалоговом окне «Промежуточные итоги» следует указать: • в поле «При каждом изменении в» заголовок столбца, для которого необходимо вычислять итоги после каждого изменения данных; • в поле «Операция» - выбрать из списка нужную функцию для подведения итогов; • в поле «Добавить итоги по» - заголовок столбца, для вычисления итогов.

Результат

Для удобства дальнейшей работы с данными можно задать следующие режимы: • Заменить текущие итоги (вычисление новых промежуточных итогов для замены текущих. ); • Конец страницы между группами (вставка автоматических разрывов страниц после каждой группы промежуточных итогов); • Итоги под данными (вставка строк промежуточных итогов и общих итогов под позициями данных). Для удаления строк с итоговыми значениями предназначена кнопка Убрать все, расположенная в окне Промежуточные итоги.

Консолидация данных • Консолидация выполняется в том случае, если необходимо подытожить данные, расположенные в разных областях таблицы.

• В диалоговом окне следует: • в поле «Функция» - указать функцию, используемую при консолидации; • в поле «Ссылка» - задать первый исходный диапазон. Для этого выделить исходный диапазон с помощью мыши; • нажать кнопку Добавить. Ссылка будет отображена в поле «Список диапазонов» ; аналогичным способом указать другие исходные диапазоны. Можно указывать до 255 исходных диапазонов. Для удаления из списка диапазона следует выделить его и нажать кнопку «Удалить» ;

• в области Использовать в качестве имени определяется расположение заголовков итоговой таблицы – «подписи верхней строки» или «значения левого столбца» ; • чтобы установить связь между исходными и консолидированными данными следует включить опцию Создавать связи с исходными данными • Обработка данных представленных в консолидированной таблице с параметром «Создавать связи с исходными данными» дает возможность просмотреть и исходные данные. Для этого достаточно раскрыть необходимый раздел, щелкнув на знак «+» или перейти на вкладку под цифрой « 2» , расположенной в левом углу листа.

Подбор параметра • Процедура Подбор параметра осуществляет поиск определенного результата для целевой ячейки с помощью подбора значения другой ячейки (влияющей).

• В поле Установить в ячейке введите ссылку на ячейку, содержащую необходимую формулу. • Введите искомый результат в поле Значение. • В поле Изменяя значение ячейки введите ссылку на ячейку, содержащую подбираемое значение. В ходе выполнения команды появится окно Результат подбора параметра, в котором будет отображен результат работы команды. Изменения произойдут и на самом рабочем листе. Следует определить нужное действие Сохранять или отменять предлагаемый результат

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

• Если команда Поиск решения отсутствует в меню Сервис, следует установить надстройку Поиск решения (надстройка – это вспомогательная программа, служащая для добавления в Microsoft Office специальных команд или возможностей)

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

• В поле Изменяя ячейки ввести имена или ссылки на изменяемые ячейки, разделяя их запятыми. Изменяемые ячейки должны быть прямо или косвенно связаны с целевой ячейкой. Допускается задание до 200 изменяемых ячеек. • Чтобы автоматически найти все ячейки, влияющие на формулу модели, нажать кнопку Предположить.

В поле Ограничения ввести все ограничения, накладываемые на поиск решения. В разделе Ограничения диалогового окна Поиск решения нажать кнопку Добавить. • В поле Ссылка на ячейку ввести адрес или имя ячейки, на значение которой накладываются ограничения. • Выбрать из раскрывающегося списка условный оператор ( <=, =, >=, цел или двоич), который должен располагаться между ссылкой и ограничением. • В поле Ограничение ввести число, ссылку на ячейку или ее имя либо формулу. • Выполнить одно из следующих действий.

Чтобы принять ограничение и вернуться в диалоговое окно Поиск решения, нажать кнопку OK. Нажать кнопку Выполнить и произвести одно из следующих действий: • чтобы сохранить найденное решение на листе, выбрать в диалоговом окне Результаты поиска решения вариант Сохранить найденное решение; • чтобы восстановить исходные данные, выбрать вариант Восстановить исходные значения.

Пример транспортной задачи

Заполнение окна команды