Анализ данных. Подбор параметра, поиск решения в табличном процессоре

Подбор параметра

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

Математическая суть задачи состоит в решении уравнения, где описывается заданной формулой искомый параметр, требуемый результат формулы.

Для решения этой задачи необходимо выполнить следующие действия:

1) В меню Сервис найти Надстройки.

2) Установить флажок напротив Пакета анализа.

3) Выделить ячейку, содержащую формулу, для которой нужно найти определенное значение.

4) В меню Сервис выбрать команду Подбор параметра.

5) В поле Установить в ячейке ввести ссылку на ячейку, содержащую формулу (по умолчанию в это поле вводится адрес текущей ячейки).

6) В поле Значение ввести значение, которое нужно получить по заданной формуле.

7) В поле Изменяя ячейку ввести ссылку на ячейку, содержащую значение изменяемого параметра (эта ячейка называется изменяемой).

Если процесс подбора заданного значения успешно завершится, то Excel выведет окно Результат подбора параметра, в котором будет указано Подбираемое значение и Текущее значение (заданное и подобранное значение параметра). При нажатии кнопки ОК значения будут сохранены. При нажатии кнопки Отмена исходные значения будут восстановлены.

Процесс подбора параметра является итерационным: в изменяющуюся ячейку один за другим подставляются значения параметра, пока не будет достигнут заданный результат. При этом результат подбора параметра не всегда может устраивать. Чтобы изменить параметры выполнения команды на вкладке Вычисления диалогового окна Параметры (Сервис - Параметры …) надо установить новые значения в поле Относительная погрешность и Предельное число итераций.

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

Команда Сервис - Поиск решения... позволяет решать более сложные задачи: находить значения нескольких параметров или комбинации параметров, определяющих оптимальное (наибольшее или наименьшее) или фиксированное значение исследуемой функции. При этом для изменяемых параметров можно задавать ограничения, в пределах которых будет осуществляться поиск их значений.

Если команды Поиск решения нет в меню Сервис, то надо ее установить, т.к. она является надстройкой: выберите команду Сервис -> Надстройки … и в списке Доступные надстройки выполните установку команды Поиск решения (флажок).

Задачи, решаемые с помощью инструмента Поиск решения, можно сформулировать следующим образом:

Дана целевая функция. Найти такие x, что целевая функция достигает минимального значения, максимального значения или некоторого фиксированного значения. На искомые переменные x могут накладываться ограничения (в виде равенств или неравенств).

При выборе команды Поиск решения открывается диалоговое окно, которое содержит следующие элементы:

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

· переключатель Равной служит для выбора варианта оптимизации значения целевой ячейки;

· поле Изменяя ячейки используется для указания ячеек, значения которых изменяются в процессе поиска решения (имена ячеек разделяются запятыми);

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

· поле Ограничения служит для отображения списка граничных условий поставленной задачи (задаются ограничения с помощью кнопок Добавить, Изменить, Удалить);

· кнопка Параметры используется для вывода диалогового окна Параметры поиска решения, позволяющего сохранить параметры поиска (Сохранить модель…) или использовать уже сохраненные параметры (Загрузить модель…);

· кнопка Выполнить используется для запуска поиска решения поставленной задачи;

· кнопка Закрыть позволяет выйти из диалогового окна без запуска поиска решения поставленной задачи;

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

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

27. Организация модели данных в виде списков. Основные понятия: список, запись, поле.

Список – определенным образом сформированный на рабочем листе Excel массив данных со столбцами строками. Список может использоваться как БД, в которых строки выступают в качестве записей, а столбцы являются полями. Первая строка списка при этом содержит названия столбцов. Каждая запись должна содержать полное описание конкретного элемента. Количество полей в каждой записи – одинаково. Каждое поле в записи может являться объектом поиска и сортировки.

Для создания списка с помощью формы:

1) Сформулировать заглавную строку списка

2) В каждом столбце ввести название соответствующего поля записи

3) Задать команду Данные – Форма

4) Добавить для помещения еще одной записи

· Записи подобны карточкам библиотечного каталога. Каждая запись должна содержать полное описание конкретного элемента.

· Поля - это отдельные категории, по которым упорядочена информации в списке (отдельный столбец - отдельная категория).

· Заглавная строка состоит из заголовков столбцов и располагается в самом начале списка. Заголовки - это метки соответствующих полей.

· В списке Excel каждый столбец - это поле, а каждая строка - это запись.

28. Применение формы для работы со списками: создание списка, поиск записей, редактирование записей, удаление записей, добавление записей.

Создание списка:

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

Для создания списка с помощью формы (маски ввода):

· Сформируйте заглавную строку списка. В каждом столбце этой строки введите название соответствующего поля записи.

· Щелкните на любой из ячеек заглавной строки и выберете команду Данные - Форма

· В открывшемся диалоговом окне, содержащем поля, название и количество которых соответствует созданным заголовкам столбцов введите данные в каждое поле. Ширина полей соответствует самому широкому столбцу заголовка.

· Нажмите кнопку Добавить для помещения значений данной записи в список и введите следующую запись

Для того, чтобы в большой таблице найти записи, удовлетворяющие некоторому условию:

· Установите курсор в любую из ячеек списка и выберете команду Данные - Форма.

· Нажмите кнопку Критерии

· Введите критерии поиска в одно или несколько полей записи

· Нажимайте кнопки Назад или Далее, либо кнопки полосы прокрутки для поиска записей, отвечающих заданному критерию

Для задания нового критерия поиска нажмите кнопку Очистить

Для исправления значения (но не формул) в любом поле записи:

· Установите курсор в любую ячейку списка и выберете команду Данные - Форма

· Найдите требуемую запись с помощью кнопок Назад и Далее

· Отредактируйте запись

Для удаления записи:

· Установите курсор в любую ячейку списка и выберете команду Данные - Форма

· Найдите требуемую запись и нажмите кнопку Удалить

· Подтвердите удаление, нажав ОК

При использовании диалогового окна команды Форма вновь создаваемые записи заносятся в конец списка

29. Анализ данных на основе их сортировки: сортировка строк списка; сортировка строк диапазона; сортировка столбцов списка; пользовательский порядок сортировки

Сортировка строк списка:

· Указать ячейку, которую требуется отсортировать

· Данные - Сортировка

· В окне Сортировка диапазона нажмите Параметры

· Установить переключатель в положение Столбцы диапазона и нажмите ОК

· В окне Сортировка диапазона выберете строки, по которым требуется отсортировать столбцы в списках Сортировать по…, Затем по…, В последнюю очередь по…

Сортировка столбцов списка:

· Выделить ячейку

· Данные - Сортировка

· Сортировка по установить переключатель в положение По возрастающей или По убывающей

· Затем по в списке установить сортировку для второго столбца

· В списке В последнюю очередь по выбрать заголовок 3-го столбца и указать сортировку после нажать ОК

Пользовательская сортировка

· Указать ячейку

· Данные - Сортировка

· В списке Сортировка по выбрать заголовок

· Установить переключатель в положение По возрастающей или По убывающей

· Параметры

· В списке Порядок сортировки по первому ключу выберете порядок сортировки и нажмите ОК

30. Фильтрация списков: с помощью инструмента Автофильтр, Пользовательский автофильтр, Расширенный автофильтр.

Фильтрация данных – проводится с помощью Автофильтра и Расширенного фильтра.

Применение Автофильтра:

· Выделение необходимого диапазона ячеек

· Данные – Фильтр – Автофильтр

Автофильтр включает в себя:

· Сортировку по возрастанию

· Сортировку по убыванию

· «Первые 10»

· «Условие»

o Пользовательский автофильтр – установка одного и (или) двух значений как критерия фильтрации.

· Выведение повторяющихся значений

Расширенный фильтр включает в себя:

· Обработку диапазона – выбор места исходного диапазона после фильтрации

· Исходный диапазон

· Диапазон условий

· Поместить результат в диапазон (при копировании результата фильтрации на другое место)

31. Функции для работы со списками: общий синтаксис, примеры использования функций БДСУММ, БИЗВЛЕЧЬ,БСЧЕТ, ДМАКС, ДМИН, ДСРЗНАЧ, ДСТАНДОТКЛ.

Некоторые функции соответствуют уже известным excel-функциям таблицы (например, БДСУММ(список;поле;критерий поиска), БСЧЕТ(список,поле,критерий поиска), ДМАКС(список,поле,критерий поиска) и др.), но являются более гибкими, т.к. в них можно указать определенные критерии

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

Excel вычисляет результат функции для значений из обозначенного столбца списка, перебирая при этом только те записи, которые отвечают критерию поиска. Если область критериев поиска состоит из заглавной строки и пустой строки критериев поиска, т.е. не имеет элементов и поэтому не представляет никаких ограничений, то обрабатываются все записи списка.

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

БСЧЁТ - подсчитывает количество ячеек, содержащих числа;

ДМАКС - ищет максимальное значение;

ДМИН - ищет минимальное значение;

БДСУММ - вычисляет сумму числовых значений;

ДСРЗНАЧ - считает среднее значение;

ДСТАНДОТКЛ - оценивает стандартное отклонение;

БИЗВЛЕЧЬ - извлекает из базы данных одну запись, удовлетворяющую заданному условию.

32. Анализ данных на основе сводных таблиц: понятие сводной таблицы, технология создания сводной таблицы, изменение сводной таблицы.

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

Создание сводных таблиц осуществляется с помощью Мастера сводных таблиц

Перед построением сводной таблицы необходимо убрать все ранее созданные промежуточные итоги и наложенные фильтры

· Установите курсор в любую ячейку списка и выберете команду Данные - Сводная таблица

· В открывшемся диалоговом окне Мастер сводных таблиц отметьте опцию в списке или базе данных MS Excel

· Далее определить диапазон, с которым будет работать Мастер сводных таблиц. Щелкните на кнопке Далее.

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

· Далее определяется место, в которое будет помещена разработанная таблица (например, ячейка нового листа). Там же определяются параметры сводной таблицы: следует ли выводить общие итоги по строкам и по столбцам.

· После нажатия на кнопку Готово в указанном месте появляется таблица со сводными данными. В левом верхнем углу таблицы располагается кнопка с полем, помещенным в зону Страница. По умолчания в таблице отображается вся информация по этому полю. Используя выпадающий список значений данного поля (в соседней справа ячейке), можно указать значение для фильтрации.

Используя панель инструментов Сводные таблицы, можно изменять вид сводной таблицы.