Файл: Информационные технологии-Багиева.docx

21. Поиск решения

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

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

1. В меню Сервис выбрать команду Надстройки.

Рисунок 32. Окно команды Надстройки

2. Установить в окне Надстройки флажок той надстройки, которую необходимо загрузить (Поиск решения), а затем нажать кнопку OK (рис 32).

3. Следовать инструкциям программы установки, если они имеются.

Использование процедуры поиск решения.

1. В меню Сервис выбрать команду Поиск решения (рис. 33).

Рисунок 33. Диалоговое окно Поиск решения

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

3. Выполнить одно из следующих действий:

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

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

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

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

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

• В поле Ограничения ввести все ограничения, накладываемые на поиск решения (рис. 34).

• В разделе Ограничения диалогового окна Поиск решения нажать кнопку Добавить.

• В поле Ссылка на ячейку ввести адресилиимяячейки, на значение которой накладываются ограничения.

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

• В поле Ограничение ввести число, ссылку на ячейку или ее имя либо формулу.

• Выполнить одно из следующих действий.

• Чтобы принять ограничение и приступить к вводу нового, нажать кнопку Добавить.

Рисунок 34. Окно Добавление ограничения

• Чтобы принять ограничение и вернуться в диалоговое окно Поиск решения, нажать кнопку OK.

• Нажать кнопку Выполнить и произвести одно из следующих действий:

• чтобы сохранить найденное решение на листе, выбрать в диалоговом окне Результаты поиска решения вариант Сохранить найденное решение;

• чтобы восстановить исходные данные, выбрать вариант Восстановить исходные значения.

Чтобы прервать поиск решения, следует нажать клавишу ESC. Лист Microsoft Excel будет пересчитан с учетом найденных значений влияющих ячеек.

На рис. 35 приведен пример использования команды Поиск решения для транспортной задачи.

Рисунок 35. Пример использования команды Поиск решения

22. Сводная таблица

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

Сводные таблицы удобны при анализе данных по нескольким причинам:

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

• Легко преобразуются;

• Разрешают выполнять автоматический отбор информации;

• На основе сводных таблиц строятся диаграммы, которые динамически перестраиваются вместе с изменением сводной таблицы.

Процедура выполнения:

1. Вызвать команду Данные/Сводная таблица. Появится окно Мастера сводных таблиц и диаграмм (рис. 35).

Рисунок 36. Окно диалога Мастер сводных таблиц

В первом окне следует указать источник данных. Возможны четыре источника

• В списке или базе данных Excel – список или таблица, с помеченными столбцами, расположенная на рабочем листе;

• Во внешнем источнике данных – файл и таблицы, созданные другими программами

• В нескольких диапазонах консолидации – несколько списков или таблиц с помеченными столбцами, расположенными на рабочем листе Excel;

• В другой сводной таблице – другая существующая в активной рабочей книге сводная таблица.

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

2. Во втором окне (рис. 36) указать диапазон данных, по которым строиться сводная таблица.

Рисунок 37. Ввод диапазона данных

3. В третьем окне следует создать структуру сводной таблицы, нажав кнопку Макет. В окне макета (рис. 37) определяется, какая информация будет выводиться по строкам, по столбцам, в области данных и страниц. Эти значения задаются перетаскиванием кнопок, расположенных в правой части окна, в соответствующие области макета. Для изменения функции данных следует дважды щелкнуть на заданном в этой области поле. В результате откроется окно Вычисление поля сводной таблицы (рис. 38)

Рисунок 38. Макет создания сводных таблиц

В списке Операция перечислены допустимые операции. Кнопка Дополнительно позволяет расширить множество допустимых операций. Нажатие кнопки ОК приведет к возврату в окно макета.

Рисунок 39. Окно Вычисление поля сводной таблицы

4. В четвертом окне (рис. 39) определить размещение таблицы на новый лист или существующий лист.

Рисунок 40. Размещение сводной таблицы

5. Для задания параметров сводной таблицы следует нажать кнопку Параметры. В окне, под полем Имя, в которое вводиться имя сводной таблицы, находятся две группы параметров Формат и Данные, с помощью которых определяются настройки сводной таблицы (рис. 40). Те значения, которые установлены по умолчанию, как правило, являются оптимальными, и их изменять не надо.

Рисунок 41. Окно параметров сводной таблицы

6. Нажатие кнопки Готово приведет к созданию сводной таблицы. Одновременно на экране отображается панель инструментов Сводная таблица (рис 41), с помощью которой можно вносить изменения. Кнопка Мастер диаграмм добавляет новый лист со сводной диаграммой.

Рисунок 42. Панель инструментов «Сводная таблица»

23. Статистика вExcel

В Microsoft Excel представлен список методов статистической обработки данных, вызываемых командой Сервис/Анализ данных. Каждый метод реализован в виде отдельного режима работы. Пакет анализа включает следующие инструменты:

• Гистограмма;

• Выборка;

• Генерация случайных чисел;

• Методы проверки статистических гипотез;

• Дисперсионный анализ и др.

Наряду с надстройкой «Анализ данных» могут применяться статистические функции.

Гистограмма.

Результат сводки и группировки материалов статистического наблюдения оформляются в виде таблиц и статистических рядов распределения. В зависимости от признака, положенного в основу образования ряда, различают атрибутивные и вариационные ряды распределения. Последние, в свою очередь делятся от характера вариации на дискретные (прерывные) и интервальные (непрерывные). Удобнее всего ряды распределения анализировать с помощью их графического изображения. Наглядное представление о характере изменения частот вариационного ряда дают полигон и гистограмма.

Полигон используется для изображения дискретных вариационных рядов (рис.42). Для построения полигона применяется мастер построения диаграмм (см. тему Диаграмма).

Рисунок 43. Построение полигона

Для изображения интервальных вариационных рядов распределения применяют гистограммы. При необходимости гистограмма интегрального ряда распределения может быть преобразована в полигон. Для этого нужно середины верхних сторон прямоугольников соединить прямыми линиями (рис. 43).

Рисунок 44. Гистограмма и полигон интегрального ряда распределения

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