Обработка данных с помощью средств MS Excel

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

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

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

7. Щелкнуть по кнопке ОК. Таблица будет заполнена значениями.

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

1. В левую верхнюю ячейку блока, отведенного под таблицу, ввести ссылку на ячейку с анализируемой формулой.
2. В левый столбец блока, начиная со второй ячейки, последовательно ввести значения одной из варьируемых переменных.
3. В верхнюю строку блока, начиная со второй ячейки, ввести значения другой варьируемой переменной.
4. Выделить таблицу подстановки.
5. В меню Данные выбрать команду Таблица подстановки.
6. В поле Подставлять значения по строкам в ввести ссылку на ячейку с переменной, зачения для которой расположены в левом столбце таблицы подстановки.
7. В поле Подставлять значения по столбцам в ввести ссылку на ячейку с переменной, значения лля которой расположены в мерной строке таблицы подстановки, и Щелкнуть по кнопке ОК. Таблица, будет заполнена значениями.

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

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

    Целевая ячейка — это ячейка, для которой нужно найти максимальное, минимальное или заданное значения.

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

    Ограничение - это условие, накладываемое на некоторую ячейку. Ограничения могут быть наложены на любые ячейки таблицы, включая целевую ячейку и изменяемые ячейки.

    Чтобы запустить процедуру поиска решения, надо:

1. В меню Сервис выбрать команду Поиск решения. Откроется диалоговое окно Поиск решения.
2. В поле Установить целевую ячейку ввести ссылку на ячейку, в которой нужно получить максимальное, минимальное или заданное значения.
3. В поле Изменяя ячейки ввести ссылки на изменяемые ячейки. (Если щелкнуть по кнопке Предположить, то Поиск решения самостоятельно определит изменяемые ячейки).
4. Для задания ограничений щелкнуть по кнопке Добавить.
5. В открывшемся диалоговом окне следует:

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

• во втором поле выбрать оператор ограничения (>, <, = и т.д.);
• в поле Ограничение ввести значение ограничения.

6. Для задания следующего ограничения щелкнуть по кнопке Добавить и повторить операции пункта 5.

7. Когда все ограничения будут заданы, щелкнуть по кнопке ОК, чтобы вернуться в диалоговое окно Поиск решения.
8. Изменять и удалять ограничения можно с помощью кнопок Изменить и Удалить.
9. С помощью кнопки Параметры можно задать: максимальное время решения; предельное число итераций; относительную погрешность; допустимое отклонение; сходимость; метод поиска.

    Если  известно, что решаемая задача линейная (т.е. зависимости между переменными  линейны), то следует включить режим  Линейная модель: процесс решения  значительно ускорится.

    Для возврата в диалоговое окно Поиск  решения щелкнуть по кнопке ОК.

10. Для инициализации процедуры поиска решения щелкнуть по кнопке Выполнить. Полученные результаты будут выведены на рабочий лист.

    После завершения процедуры решения в  диалоговом окне Результаты поиска решения  можно выполнить один из следующих  вариантов:

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

    Текущие установочные параметры для поиска решения можно сохранить в виде модели.

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

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

    Чтобы впоследствии загрузить модель, надо щелкнуть по кнопке Загрузить модель в диалоговом окне Параметры поиска решения. (Диалоговое окно Параметры  поиска решения открывается при  щелчке по кнопке Параметры в диалоговом окне команды Сервис - Поиск решения).

    Найденные решения (значения изменяемых ячеек) можно  сохранить в качестве сценария. Для этого нужно:

1. В диалоговом окне Результаты поиска решения выбрать Сохранить сценарий.
2. В поле Название сценария ввести имя сценария. Просмотреть сценарии можно с помощью команды Сервис -Сценарии.

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

    Каждый  отчет создается на отдельном  листе текущей рабочей книги.

    Для создания отчета надо в диалоговом окне Результаты поиска решения выбрать  нужный тип отчета в поле Тип отчета. Можно выбрать сразу несколько  типов (при выделении нескольких строк используется клавиша <С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. В открывшемся диалоговом окне, содержащем поля, название и количество которых соответствует созданным заголовкам столбцов введите данные в каждое поле. Ширина полей соответствует самому широкому столбцу заголовка. Для перехода между полями можно пользоваться указателем мыши, либо клавишами