Практическое занятие 9

Практическое занятие №9.

Тема: Сводные таблицы. Промежуточные итоги. Макросы. Решение задач оптимизации

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

Время работы: 2 часа.

Оборудование: Компьютер с установленным программным обеспечением и подключенный к Internet

Ход работы

Задание 1.Сводные таблицы

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

1. В Microsoft Excel 2010 оформите таблицу

1. Перейдите на Лист 2.

1. Выполните команду: вкладка ленты Вставка ► панель инструментов

Таблицы ► кнопка .

1. Укажите диапазон ячеек Лист1!$A$3:$C$16 и нажмите кнопку ОК.

1. Выберите поля Исполнитель и Стоимость работ

1. Измените заголовки в сводной таблице

1. На основе сводной таблицы постройте сводную диаграмму

Задание 2. Решение задач оптимизации

Задачи оптимизации занимают очень важное место в бизнесе, производстве, прогнозировании. Условно эти задачи можно разделить на следующие категории:

• транспортная задача – минимизация расходов на транспортировку товаров;

• задача о назначениях – составление штатного расписания с минимизацией денежных затрат на заработную плату или времени выполнения работ;

• задачи оптимизации производства – максимизация выпуска товаров при ограничениях на сырье для производства этих товаров.

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

Задача оптимизации в общем виде формулируется следующим образом.

Найти значения переменных x₁, x₂, … , x_n, такие, что целевая функция f(x₁, x₂, … , x_n) примет максимальное, минимальное или заданное значения при ограничениях вида g(x₁, x₂, … , x_n).

Таким образом, задача оптимизации содержит три основных компонента:

• переменные x₁, x₂, … , x_n – определяемые величины;

• целевая функция – это цель, записанная математически в виде функции от переменных, принимающая максимальное, минимальное или заданное значения;

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

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

Продемонстрируем эту возможность на примере решения следующей транспортной задачи.

Пример 1. Компания «Атлант» хранит свою продукцию на трех складах (первом, втором и третьем), расположенных в разных частях города. На этих складах хранится продукция в количествах 1000, 3000 и 2500 штук соответственно. Продукцию необходимо доставить четырем оптовым покупателям «Урал», «Купец», «Гелиос» и «Меркурий» с минимальными затратами, заявки которых составляют 1300, 800, 2700 и 1700 штук соответственно. Склады оптовых покупателей также расположены в разных частях города. Стоимости (в сомах) доставки одной штуки продукции со складов компании на склады покупателей показаны в следующей таблице.

1. Построим математическую модель задачи: определим переменные, целевую функцию и ограничения.

Пусть:

• x₁₁, x₁₂, x₁₃, x₁₄, x₂₁, x₂₂, x₂₃, x₂₄, x₃₁, x₃₂, x₃₃, x₃₄ – количество продукции, перевозимой со складов компании на соответствующие склады покупателей;

• z=50 x₁₁ + 150 x₁₂ + 60 x₁₃ + 75 x₁₄ + 100 x₂₁ + 30 x₂₂ +100 x₂₃ +40 x₂₄+

+70x₃₁+180 x₃₂ + 210 x₃₃ + 120 x₃₄ – целевая функция, общая стоимость доставки грузов покупателям;

• x₁₁ + x₁₂ + x₁₃ + x₁₄=1000, x₂₁ + x₂₂ +x₂₃ +x₂₄=3000,

x₃₁+x₃₂ + x₃₃ + x₃₄=2500 – ограничения для складов компании;

• x₁₁+ x₂₁ + x₃₁=1300, x₁₂ + x₂₂ + x₃₂=800, x₁₃ + x₂₃ + x₃₃=2700,

x₁₄+ x₂₄+ x₃₄=1700 – ограничения для складов покупателей.

1. Имеем сбалансированную транспортную задачу, так как спрос покупателей (1300+800+2700+1700=6500) равен предложению производителей (1000+3000+2500=6500).

2. Запустите табличный процессор MS Excel. Переименуйте Лист 1 в

Сбалансированная модель.

1. Составьте табличную модель Excel

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

6. =СУММПРОИЗВ (В4:Е6;В13:Е15).

1. Выделите целевую ячейку и запустите надстройку Поиск решения (Данные  Анализ  Поиск решения).

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

1. В диалоговом окне параметры Поиска решения установите флажки Линейная модель, Неотрицательные значения и Автоматическое масштабирование.

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

1. Скопируйте полученную табличную модель на Лист 2 рабочей книги и переименуйте его в Несбалансированная задача.

2. Решим эту же задачу, немного изменив условие.

1. Пусть на складе №1 хранится не 1000 штук продукции, а 500. В таком случае на трех складах компании хранится 6000 штук продукции, покупатели по-прежнему заказывают 6500 штук. Перед нами транспортная задача с дефицитом.

2. Несбалансированная задача решается аналогично сбалансированной. Изменения коснуться только ограничений. Причем в ограничениях для складов покупателей знак «=» заменяется знаком « ≤ ».

3. После выполнения надстройки Поиск решения получаем, что покупатель «Гелиос» недополучит 500 ед. продукции, а минимальные транспортные расходы составят 479 000

Задание 3. Макросы

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

Использование макросов позволяет экономить время, так как избавляет от необходимости повторять одни и те же действия.

Создайте макрос, выполняющий следующие действия:

• рисует границы ячеек и выполняет заливку первой строки и первого столбца выделенного диапазона

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

Назначьте данный макрос кнопке на панели быстрого доступа.

1. Запустите табличный процессор MS Office Excel 2007.

2. Для разрешения выполнения всех макросов выполните команду: вкладка ленты Разработчик  группа Код  кнопка .

3. В группе Параметры макросов выберите переключатель

.

1. Для записи макроса выполните команду: вкладка ленты Разработчик 

группа Код  кнопка .

1. В диалоговом окне Запись макроса (рис. 57) задайте имя макроса и сочетание клавиш для его вызова. Нажмите кнопку ОК.

1. Выполните команду: вкладка ленты Разработчик  группа Код 

кнопка .

1. Выполните действия, которые должны быть записаны в макросе (обозначение границ ячеек, заливка, формат ячеек).

2. Завершив выполнение всех действий, щелкните по кнопке .

3. Проверьте работу макроса. Установите курсор в нужное место и

выполните команду: вкладка ленты Разработчик  группа Код 

кнопка .

1. В диалоговом окне Макрос выберите имя макроса и нажмите кнопку Выполнить.

2. Щелкните по кнопке Office . В диалоговом окне Параметры Excel

выберите категорию Настройка. В поле Выбрать команды из

установите Макросы.

1. Найдите Ваш макрос и нажмите кнопку Добавить.

2. Измените символ кнопки.

3. Нажмите кнопку ОК.

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

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

1. Какие имеются возможности Excel для анализа экономической информации?

2. Какие математические, статистические и финансовые функции вы использовали в расчетах?