Лабораторная работа 5. Консолидация данных. Анализ решений

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

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

Задание 1. Выполнение консолидации данных.

Методические указания. Команда Данные|Консолидация предназначена для объединения данных из различных областей-источников и вывода результата в область назначения. Можно объединять до 255 областей-источников, размещенных на разных р/л и в разных р/к. Различают консолидацию по расположению и консолидацию по категории.

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

1. В новой р/к четырем р/л присвоить имена Семестр1 – Семестр4. Пятому р/л присвоить имя СреднЭкзам.

2. На всех пяти листах создать одинаково расположенные одинаковые таблицы, подобные показанной ниже.

3. Заполнить таблицы листов Семестр1 – Семестр4 оценками. Поле Среднее и лист Средн_Экзам не заполнять.

4. На листах Семестр1 – Семестр4 вычислить и заполнить столбец Среднее.

5. Активизировать итоговый лист СреднЭкзам и выделить область назначения (в приведенном образце В3:F7).

6. Выполнить команду Данные|Консолидация.

7. В диалоговом окне Консолидация в списке Функция выбрать Среднее.

8. Флажок Создавать связи с исходными данными и флажки в поле Использовать в качестве имен – сбросить.

9. В поле Ссылка внести поочередно интервалы исходных данных из р/л Семестр1 – Семестр4 (без шапки и левого столбца). После ввода каждой ссылки нажимать кнопку Добавить.

10. После ввода последней ссылки щелкнуть ОК.

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

[Имя файла]Имя листа!Ссылка, например, [5_Consol.xls]Семестр1!$B$3:$F$7.

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

1. На листах Семестр1 – Семестр4 и СреднЭкзам создать новые таблицы, ниже использованных при консолидации по расположению. На листе Семестр1 создать таблицу

На листе Семестр2 – таблицу

На листе Семестр3 – таблицу

На листе Семестр4 – таблицу

На листе Средн_Экзам создать шапку таблицы

2. Заполнить таблицы листов Семестр1 – Семестр4 оценками. Поле Среднее и лист СреднЭкзам не заполнять.

3. На листах Семестр1 – Семестр4 в таблицах вычислить и заполнить столбец Среднее.

4. Активизировать итоговый лист СреднЭкзам и выделить в нем левый верхний угол области назначения (отмечен на рисунке).

5. Выполнить команду Данные|Консолидация.

6. В диалоговом окне Консолидация в списке Функция выбрать Среднее.

7. Флажок Создавать связи - сбросить. В поле Использовать в качестве имен - установить флажок Значения левого столбца.

8. В поле Ссылка ввести поочередно интервалы исходных данных. из р/л Семестр1 – Семестр4 (без шапки, но с левым столбцом). После ввода каждой ссылки нажимать кнопку Добавить.

9. После ввода последней ссылки щелкнуть ОК.

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

Задание 2. Анализ решений.

Методические указания. Анализ решений с помощью модели "что–если" проведем на следующем примере: требуется взять кредит в размере 10 000 000р. на 30 лет. Рассчитать ежемесячные выплаты в счет погашения долга и рассмотреть разные варианты процентных ставок. Действуем по следующему алгоритму.

1. Создать р/л Что_если.

2. Ввести на р/л исходные данные (например, C2:D5)

и входной интервал(например, B10:B14) – годовые процентные ставки: 9; 9, 25; 9, 5; 9, 75; 10.

3. В ячейки C9 и D9 ввести формулы ПЛТ(D3/12;D4;D5) и C9*D4 + D5 соответственно, использующие входные параметры. Вспомогательная ячейка D3 служит для перебора значений из входного интервала (вместо D3 можно использовать любую другую ячейку).

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

5. Выполнить команду Данные|Таблица подстановки. В диалоговом окне Таблица подстановки выбрать Подставлять значения по столбцам в: или Подставлять значения по строкам в: и щелкнуть ячейку D3.

ОК.

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

Рассмотрим задачу, в которой переменными являются два параметра. Создать таблицу данных для расчета ежемесячных выплат за кредит в размере 10 000 000 р. при одновременном изменении как процентной ставки, так и срока выплаты.

Процентные ставки - 9; 9,25; 9,5; 9,75; 10.

Сроки выплаты - 15, 20, 25, 30, 35 лет (180, 240, 300, 360, 420 месяцев).

1. Ввести в столбец процентные ставки (A25:A29).

2. Ввести в строку правее процентных ставок и на одну ячейку выше сроки выплат (B24:F24).

3. Создать табличную формулу в ячейке A24 на пересечении строки и столбца с исходными данными – =ПЛТ(D33/12;D34;D35).

4. Выделить интервал для таблицы данных – наименьший прямоугольный блок, включающий все исходные данные и табличную формулу – (A24:F29).

5. Выполнить команду Данные|Таблица подстановки. Задать две ячейки ввода для массивов исходных данных. Щелкнуть ячейку D3 в поле Подставлять значения по строкам в: и D4 в поле Подставлять значения по столбцам в:.

ОК.

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

Создание сценария осуществляется по команде Сервис|Сценарии.

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

Рассмотрим работу Диспетчера сценариев на примере торгового предприятия. Первый сценарий представлен в таблице.

Ячейки Выручка от покупателя, Затраты на покупателя, Среднее число покупателей, Зарплата, Оборудование, Амортизация, Реклама, Расходные материалы, Коммунальные расходы являются изменяемыми. Им следует присвоить соответствующие имена. Также следует присвоить имена ячейкам Доход от покупателя, Общий доход (за неделю и год), Накл. расх. всего, Чистый доход, содержащим формулы.

Далее действуем по алгоритму.

1. Копировать таблицу первого сценария на р/л Сценарий р/к UchebnFile.

2. Выбрать команду Сервис|Сценарии.

3. В диалоговом окне Диспетчер сценариев нажать кнопку Добавить.

4. Ввести имя сценария.

5. В поле Изменяемые ячейки ввести ссылки или имена ячеек, значения которых будут изменяться. Если ссылок несколько, то следует отделять их друг от друга точкой с запятой ;. Ячейки можно выделять непосредственно на р/л. Если необходимо – ввести комментарий.

6. ОК.

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

8. Для создания другого сценария служит кнопка Добавить, для возврата в окно Диспетчер сценариев – кнопка ОК. Для возврата на рабочий лист – кнопка Закрыть.

Примечание. После присвоения имени первому сценарию следует установить флажок на опции запретить изменения. В остальных случаях после присвоения имени сценарию этот флажок нужно убрать.

Подбор параметра. Рассмотрим задачу, обратную той, которая была решена выше. Какую максимальную ссуду на 30 лет можно взять, если процентная ставка равна 10%, а месячные выплаты не должны превышать 2 000 руб.

Задача решается по следующему алгоритму.

1. Создать р/л ПодборПараметра.

2. Решить задачу с помощью функции ПЛТ для суммы в 500 000 руб. Для этого используется, например, диапазон A1:B4 со значениями:

3. Активизировать ячейку Ежемесячный взнос B1 и набрать формулу =ПЛТ(B2/12;B3;B4).

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

5. В окне Подбор параметра в поле Значение ввести величину -2000.

6. В поле Изменяя значение ячейки ввести ссылку на ячейку со значением величины займа или ее имя – B4.

ОК.

8. Чтобы сохранить полученное значение нажать кнопку ОК в окне Результат подбора параметра, а для восстановления исходного значения - кнопку Отмена.

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

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

1. На р/л ПодборПараметра ввести в Е1 формулу Е2^2.

2. Использовать команду Сервис|Подбор параметра для нахождения значения Е2, которое сделает Е1 равным 4.

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

Графический подбор параметра. Excel предоставляет способ подбора параметра с помощью манипулирования графиками. Таблица отображает прогнозируемый объем

продаж фирмы. Известно, что объем продаж за 2000 год составил 250000 $. Его хотят довести до 10000000. Коэффициентом роста был 1,40. Как видно из таблицы при таком коэффициенте объем продаж составит немногим более 7000000 $.

Для нахождения желаемого коэффициента необходимо построить диаграмму по приведенной таблице, выделить последний маркер диаграммы и увеличить его значение. В появившемся диалоговом окне Подбор параметра ввести в поле Значение величину 10 000 000, а в поле Изменяя значение ячейки – адрес ячейки, где находится коэффициент 1,40. Программа вычислит значение нового коэффициента, а диаграмма изменится автоматически.

Поиск решения. Рассмотрим задачу линейного программирования в следующей постановке. Составить план рекламной кампаниинового товара. Общий бюджет на рекламу составляет 120 000 р. Общее число публикаций рекламных объявлений желательно довестидо 800 млн. экз. Рекламу следует разместитьв шести изданиях- Изд1, Изд2,..., Изд6. Каждое издание имеет свое количество читателей и разную стоимость печатного текста.

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

1. Общая стоимость изготовления и распространения рекламы не должна превышать 120 000 р.

2. Общее число публикаций должно быть не менее 800 млн. экз.

3. В каждом издании должно появиться, по крайней мере, 6 объявлений.

4. Нельзя тратитьбольше одной трети средств на одно издание.

5. Общая стоимость размещения рекламы в Изд3 и Изд4 не должна превышать 75 000 р.

Структура задачи представлена в виде таблицы.

Решение задачи выполняется на р/л ПоискРешен по следующему алгоритму.

1. Копировать таблицу на р/л ПоискРешен.

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

3. Выполнить команду Сервис|Поиск решения.

4. В диалоговом окне Поиск решения заполнить поле Изменяя ячейки (диапазон Количество размещенных объявлений).

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

6. После ввода последнего ограничения в диалоговом окне Добавление ограничения вместо кнопки Добавить нажать кнопку OK.

7. В диалоговом окне Поиск решения нажать кнопку Выполнить.

Задание 3. Решение транспортной задачи.

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

Постановка задачи. Некоторый продукт, сосредоточенный у m поставщиков A_i в количестве a_i (i=1,2,…, m), необходимо доставить n потребителям B_j в количестве b_j, (j=1,2,…, n). Модель, в которой суммарные запасы равны суммарным потребностям, то есть называется закрытой. В противном случае модель называется открытой.

Известна стоимость c_ij перевозки груза от i-го поставщика к j-му потребителю. Количество груза, перевозимого от i-го поставщика к j-му потребителю –x_ij. Стоимость перевозки c_ij*x_ij. Нужно минимизировать стоимость всего плана перевозок . Система ограничений:

1. Все грузы должны быть перевезены (i = 1, …, m).

2. Все потребности должны быть удовлетворены (j = 1, …, n).

Таким образом, математическая модель имеет вид: найти

при ограничениях

, (i = 1, …, m);

, (j = 1, …, n);

, i = 1, …, m, j = 1, …, n.

Рассмотрим задачу, условие которой представлено в таблице.

Алгоритм решения задачи.

1. В р/к вставить новый р/л под именем ТранспортнЗадача.

2. В диапазон B10:E13 ввести стоимости перевозок из пп. a_i в пп. b_j.

Примечание. Диапазонизменяется в зависимости от размерности задачи.

3. Диапазон решений B3:E6, соответствующий по размерам диапазонустоимостей перевозок, не заполнять!

4. В диапазоны A10:A13 и B9:E9 ввести соответственно цифровые значения возможностей поставщиков и потребностей потребителей.

Примечание. Указанные диапазоны зависят от размерности задачи.

5. В диапазоны A3:A6 и B2:E2 ввести соответственно формулы суммированиядиапазона решений по столбцам и по строкам, например, =СУММ (B3:E3) или = СУММ (B3:B6).

Примечание. Диапазоны суммирования зависят от размерности задачи.

6. В ячейку B15 ввести формулу =СУММПРОИЗВ(B3:E6; B10:E13).

7. Выполнить команду Сервис|Поиск решения.

8. В диалоговом окне Поиск решения Установить целевую ячейку B15 Равной минимальному значению, Изменяя ячейки – B3:E6, Ограничения:

A10:A13 = A3:A6,

B9:E9 = B2:E2,

B3:E6 ³ 0.

9. В диалоговом окне Поиск решения нажать кнопку Параметры. В появившемся диалоговом окне Параметры поиска решения установить флажки на опциях Линейная модель и Неотрицательные значения. Щелкнуть OK.

10. В диалоговом окне Поиск решения нажать кнопку Выполнить.

11. В диалоговом окне Результаты поиска решения выбрать всетипыотчетови просмотреть их на соответствующих р/л.

Контрольные вопросы

1. Что нужно предпринять приконсолидации, есликнигас исходными даннымизакрыта?

2. Что нужно предпринять, чтобы последующиеизмененияисходных данныхотражалисьна итоговых значениях?

3. На основекаких рассужденийв диалоговом окне Таблица подстановки следует выбрать строку Подставлять значения по столбцам в: или Подставлять значения по строкам в:?

4. Для чего используютсясценарии?

5. Что нужносделатьперед использованием Диспетчера сценариев?

6. Что нужнопредпринять, для достижения большей точности при выполнении команды Подбор параметра?

7. Для чегоиспользуетсяграфический подбор параметра?

8. Для чегоиспользуютсятранспортные задачи и задачи линейного программирования?

9. Какформулируетсяматематическая модель транспортной задачи?

10. Какая модель транспортной задачиназываетсязакрытой?

Поиск по сайту: