С помощью программы Поиск решения можно создать три типа отчетов по результатам, полученным при успешном завершении процедуры решения.
Каждый отчет создается на отдельном листе текущей рабочей книги.
Для создания отчета надо в диалоговом окне Результаты поиска решения выбрать нужный тип отчета в поле Тип отчета. Можно выбрать сразу несколько типов (при выделении нескольких строк используется клавиша <Сtгl>).
Типы отчетов:
• Результаты — отчет содержит целевую ячейку, список изменяемых ячеек, их исходные и конечные значения, ограничения и сведения о них.
• Устойчивость — отчет содержит сведения о степени зависимости модели от изменений величин, входящих в формулы, применяемые в задаче (формулы модели и формулы ограничений).
• Пределы — выводится целевая ячейка и ее значение, а также список изменяемых ячеек, их значений, нижних и верхних пределов и целевых результатов.
Упражнение
1. Создать таблицу, отображающую результаты хозяйственной деятельности предприятия.
В строках таблицы разместить следующие показатели: сезонный фактор, объем сбыта, доход от оборота, себестоимость реализованной продукции, валовая прибыль, затраты на зарплату, затраты на рекламу, накладные расходы, валовые издержки, прибыль, коэффициент прибыльности, цена, себестоимость.
В столбцах таблицы разместить значения этих показателей для каждого квартала, а также суммарные значения за год.
Формулы и константы для расчетов:
Объем сбыта = 35 х Сезонный фактор х Затраты на рекламу + 3000.
Доход от оборота = Объем сбыта х Цена.
Себестоимость реализованной продукции = Объем сбыта х Себестоимость.
Валовая прибыль = Доход от оборота — Себестоимость реализованной продукции.
Накладные расходы =15% дохода от оборота.
Валовые издержки = Затраты на зарплату + Затраты на рекламу + Накладные расходы.
Прибыль = Валовая прибыль — Валовые издержки.
Коэффициент прибыльности = Прибыль / Доход от оборота.
Сезонный фактор: для I квартала — 0,9; для II — 1,1; для III -0,8; для IV квартала — 1,2.
Затраты на зарплату: для I квартала — 8000 р.; для II — 8000 р.; для III — 9000 р.; для IV квартала — 9000 р.
Затраты на рекламу для каждого квартала — по 10000 р.
Цена — 40 р.; себестоимость — 25 р.
2. Отформатировать таблицу: ячейкам, содержащим денежные величины, назначить денежный формат; ячейкам строки Коэффициент прибыльности назначить процентный формат; расчертить таблицу линиями.
3. С помощью программы Поиск решения определить величину затрат на рекламу, обеспечивающую максимальную прибыль в I квартале. Сохранить установки задачи в виде модели. После просмотра результата восстановить исходные значения.
4. Определить величину затрат на рекламу для каждого квартала, обеспечивающую максимальную прибыль за год. Сохранить установки задачи в виде модели. После просмотра результата восстановить исходные значения.
5. Определить величину затрат на рекламу для каждого квартала, обеспечивающую максимальную прибыль за год при ограничении суммарной величины расходов на рекламу за год 40000 р. Сохранить установки задачи в виде модели. После просмотра результата восстановить исходные значения.
6. Изменить ограничение, установив верхний предел расходов на рекламу за год 50000 р. Сохранить установки задачи в виде модели. После просмотра результата восстановить исходные значения.
7. Сохранить в качестве сценария первоначальные значения величин затрат на рекламу в каждом квартале.
8. Загрузить каждую модель и сохранить результаты в качестве сценариев. Просмотреть все созданные сценарии.
9. Загрузить каждую модель и создать отчеты по результатам поиска решения.
10. Восстановить первоначальные значения с помощью первого сценария.
6.9. РАБОТА СО СПИСКАМИ (БАЗАМИ ДАННЫХ)
Список — определенным образом сформированный на рабочем листе Excel массив данных со столбцами и строками. Список может использоваться как база данных, в которой строки выступают в качестве записей, а столбцы являются полями. Первая строка списка при этом содержит названия столбцов. Каждая запись должна содержать полное описание конкретного элемента. Количество полей в каждой записи — одинаково. Каждое поле в записи может являться объектом поиска или сортировки.
Для ведения больших, постоянно пополняющихся списков, для удобства их заполнения, а также для организации поиска данных по какому-либо критерию в Excel используются формы (маски данных), в которых отображаются значения ТОЛЬКО ОДНОЙ записи.
6.9.1. Создание списка (базы данных)
На листе не следует помешать более одного списка. Некоторые функции обработки списков, например фильтры, не позволяют обрабатывать несколько списков одновременно.
Между списком и другими данными листа необходимо оставить, по меньшей мере, одну пустую строку и один пустой столбец. Это позволяет Microsoft Excel быстрее обнаружить и выделить список при выполнении сортировки, наложении фильтра или вставке вычисляемых автоматически итоговых значений.
В списке не должно быть пустых строк и столбцов. Это упрощает идентификацию и выделение списка.
Список должен быть организован так, чтобы во всех строках в одинаковых столбцах находились однотипные данные.
Перед данными в ячейке не следует вводить лишние пробелы, так как они влияют на сортировку.
Для создания списка с помощью формы (маски ввода):
1. Сформируйте заглавную строку списка. В каждом столбце этой строки введите название соответствующего поля записи.
2. Щелкните на любой из ячеек заглавной строки и выберите команду Данные ® Форма.
3. В открывшемся диалоговом окне, содержащем поля, название и количество которых соответствует созданным заголовкам столбцов введите данные в каждое поле. Ширина полей соответствует самому широкому столбцу заголовка. Для перехода между полями можно пользоваться указателем мыши, либо клавишами
<Tab> — для перехода вниз и <Shift><Tab> — для перехода вверх.
4. Нажмите кнопку Добавить для помещения значений данной записи в список и введите следующую запись.
Для завершения процесса ввода данных нажмите кнопку Закрыть.
6.9.2. Поиск записей в списке
Для того чтобы в большой таблице найти записи, удовлетворяющие некоторому условию:
1. Установите курсор в любую ячейку списка и выберите команду Данные ® Форма.
2. Нажмите кнопку Критерии.
3. Введите критерии поиска в одно или несколько полей записи. Так, для поиска всех фамилий, начинающихся на букву А, достаточно в поле фамилии набрать А. Для поиска записей с величиной оклада, большей 500000, в поле оклада следует ввести «>500000».
4. Нажимайте кнопки Назад и Далее, либо кнопки полосы прокрутки для поиска записей, отвечающих установленному критерию.
Для задания нового критерия поиска нажмите кнопку Очистить.
6.9.3. Редактирование записей с помощью формы
Для исправления значений (но не формул) в любом поле записи:
1. Установите курсор в любую ячейку списка и выберите команду Данные ® Форма.
2. Найдите требуемую запись с помощью кнопок Назад и Далее.
3. Отредактируйте запись.
4. Нажмите кнопку Закрыть.
6.9.4. Удаление записей с помощью формы
Для удаления записи:
1. Установите курсор в любую ячейку списка и выберите команду Данные ® Форма.
2. Найдите требуемую запись и нажмите кнопку Удалить.
3. Подтвердите удаление, нажав ОК.
4. Нажмите кнопку Закрыть.
6.9.5. Добавление записей
При использовании диалогового окна команды Форма вновь создаваемые записи заносятся в конец списка.
Для добавления записи внутрь списка:
1. Установите курсор в строку, перед которой будет вставлена новая строка.
2. Выберите команду Вставка ® Строка и введите в нее соответствующие значения.
6.9.6. Фильтрация списка
Фильтрация данных позволяет выбрать из списка только те записи, которые удовлетворяют некоторому условию и в случае необходимости проанализировать их отдельно от всего списка. В отфильтрованном списке на экран выводятся только те записи, которые содержат определенное значение или отвечают определенным критериям, при этом остальные записи оказываются скрыты. В Microsoft Excel можно использовать для фильтрации данных как команду Автофильтр, так и команду Расширенный фильтр. В большинстве случаев достаточно команды Автофильтр, однако если нужно использовать сложные критерии для выборки данных или поместить результат фильтрации в отдельную область рабочего листа, следует воспользоваться командой Расширенный фильтр. Созданная для Расширенного фильтра область критериев может быть использована для вычисления значений списка, удовлетворяющих нескольким критериям.
6.9.7. Выбор элементов списка с помощью Автофильтра
Для отображения только тех данных списка, которые удовлетворяют некоторому критерию с использованием автофильтра следует воспользоваться следующей последовательностью действий:
• Установить курсор в любую ячейку списка, задать команду Данные ® Фильтр, а затем выбрать пункт Автофильтр.
• Нажать на кнопку со стрелкой в том столбце, по которому надо фильтровать данные.
• Выбрать любой элемент из списка.
• При использовании пункта Условие можно задавать до двух критериев фильтрации одного столбца, выбирая из списка операторов сравнения и списка значений данного поля те значения, которые необходимы для задаваемого критерия. В случае неточного совпадения значений можно пользоваться подстановочными символами. Завершив установки, нажмите ОК.
Список можно сжать еще больше, щелкая на стрелках в других столбцах и выбирая другие элементы.
Для восстановления всех записей списка необходимо задать команду Данные ® Фильтр ® Показать все или же в раскрывающемся списке автофильтра выбрать пункт Все. Для отмены фильтрации необходимо повторно выбрать команду Данные ® Фильтр ® Автофильтр.