47230 (Использование Excel для решения статистических задач), страница 2
Описание файла
Документ из архива "Использование Excel для решения статистических задач", который расположен в категории "". Всё это находится в предмете "информатика" из 1 семестр, которые можно найти в файловом архиве . Не смотря на прямую связь этого архива с , его также можно найти и в других разделах. Архив можно найти в разделе "контрольные работы и аттестации", в предмете "информатика, программирование" в общих файлах.
Онлайн просмотр документа "47230"
Текст 2 страницы из документа "47230"
Расчетные формулы для решения данной задачи показаны в таблице 7.
Результат решения показан в таблице 6.
Таблица 6 – Расчетные формулы
Затраты | Выпуск (потребление) | Конечный | Валовый | ||||
(отрасли) | отрасль А | отрасль B | отрасль C | продукт | выпуск | ||
отрасль А | 0.05 | 0.1 | 0.4 | 47 | =МУМНОЖ (F12:H14; E3:E5) | ||
отрасль B | 0.1 | 0.1 | 0.3 | 58 | =МУМНОЖ (F12:H14; E3:E5) | ||
отрасль C | 0.3 | 0.15 | 0.2 | 81 | =МУМНОЖ (F12:H14; E3:E5) | ||
Решение | |||||||
Е = | 1 | 0 | 0 | ||||
0 | 1 | 0 | |||||
0 | 0 | 1 | |||||
Е-А = | =B8‑B3 | =C8‑C3 | =D8‑D3 | (Е-А)-1 = | =МОБР (B12:D14) | =МОБР (B12:D14) | =МОБР (B12:D14) |
=B9‑B4 | =C9‑C4 | =D9‑D4 | =МОБР (B12:D14) | =МОБР (B12:D14) | =МОБР (B12:D14) | ||
=B10‑B5 | =C10‑C5 | =D10‑D5 | =МОБР (B12:D14) | =МОБР (B12:D14) | =МОБР (B12:D14) | ||
Det (E-A)= | =МОПРЕД (B12:D14) |
Таблица 7 – Результат решения
Затраты | Выпуск (потребление) | Конечный | Валовый | ||||
(отрасли) | отрасль А | отрасль B | отрасль C | продукт | выпуск | ||
отрасль А | 0.1 | 0.1 | 0.4 | 47 | 140 | ||
отрасль B | 0.1 | 0.1 | 0.3 | 58 | 140 | ||
отрасль C | 0.3 | 0.15 | 0.2 | 81 | 180 | ||
Решение | |||||||
Е = | 1 | 0 | 0 | ||||
0 | 1 | 0 | |||||
0 | 0 | 1 | |||||
Е-А = | 1 | -0.1 | -0.4 | (Е-А)-1 = | 1.322880941 | 0.27438 | 0.76433 |
-0.1 | 0.9 | -0.3 | 0.333170015 | 1.25429 | 0.63694 | ||
-0.3 | -0.2 | 0.8 | 0.558549731 | 0.33807 | 1.65605 | ||
Det (E-A)= | 0.51025 |
Вывод: Для удовлетворения спроса на продукцию отрасли А величиной 47 д.е., отрасли В – 58 д.е. и отрасли С – 81 д.е. необходимо произвести продукции отрасли А на сумму 140 д.е., отрасли В на сумму 140 д.е., отрасли С – на сумму 180 д.е.
Задача №4
Вариант 15 Коммерческий магазин хочет закупить овощи А и В. Количество овощей, закупочные цены и цены, по которым магазин продает овощи, приведены в таблице 8.
Таблица 8
Овощи | Цены | Количество овощей | |
Закупка | Реализация | ||
А | 1,6 | 2,4 | 60 |
В | 1,7 | 2,2 | 70 |
Как выгоднее вложить деньги, если общая сумма, которой располагается магазин в данное время, составляет 180 д.е., причем овощей А нужно приобрести не менее 10 тонн.
Решение
Решение данной задачи состоит из трех основных этапов:
-
составление математической модели (формализация задачи);
Обозначим величину прибыли от овоща А как А, а величину прибыли от обоща В как В, тогда получим, что прибыль от продажи овоща А составляет (2,4–1,6) А, соответственно овоща В – (2,2–1,7) В. Суммарная прибыль магазина от продажи овощей составит (2,4–1,6) А+(2,2–1,7) В=0,8А+0,5В.
Тогда целевая функция имеет вид Z=0,8А – 0,5В
суммарная прибыль должна быть наибольшей (максимальной).
Данная задача содержит две неизвестных переменных, т.е. ее можно назвать плоской и она может быть решена графически.
Составим систему ограничений, исходя из условия задачи:
-
ограничение на покупку овощей по деньгам:
На покупку овоща А расходуется 1,6 д.е на 1 тонн. На все количество овоща А расходуется 1,6 А д.е. На овощ В расходуется 1,7 д.е. на 1 тонну на закупку овоща В тратят 1,7 В. Значит, исходя из условия задачи, суммарная сумма на которую закупаются овоща не должна превышать 180 д.е. Получим первое неравенство системы:
1,6 А + 1,7 В ≤ 180;
– дополнительные условия:
В условии задачи содержится дополнительное условие – закупка овоща А не менее 10 тонн и не более 60 тонн. т.е. имеем дополнительные неравенства для овоща А:
А ≥ 10;
А ≤ 60;
Для овоща В наложено верхнее ограничение не более 70 тонн, из условия задачи понятно что нижним ограничение является 0. Получаем дополнительные неравенства для овоща В:
В ≥ 0;
В ≤ 70;
Получили математическую модель задачи:
1 ,6А + 1,7В ≤ 180;
А ≥ 10; А ≤ 60;
В ≥ 0; В ≤ 70;
-
решение формализованной задачи;
Решив задачу графически и с использованием пакета Excel, получим одинаковое решение:
А = 60 тонн.
В = 49,412 тонн.
Ход решения – см. таблица 9 и рисунок 3
Вывод: Для получения максимальной прибыли в размере 72,7 ден. ед. необходимо следующим образом потратить существующие деньги:
-
овощ А закупить в количестве 60 тонн.
-
овощ В закупить в количестве 49,412 м.
При этом необходимо потратит все деньги: 180 д.е.
Графическое решение задачи 4
Необходимо найти значения (А, В), при которых функция Z=0,8 А – 0,5 В достигает максимума. При этом А и В должны удовлетворять системе ограничений, приведенной ранее:
1 ,6А + 1,7В ≤ 180;
А ≥ 10; А ≤ 60;
В ≥ 0; В ≤ 70;
Решение
-
Строим область, являющуюся пересечением всех полуплоскостей, уравнения которых приведены в системе ограничений. Например, полуплоскость 1,6А + 1,7В ≤ 180; представляет собой совокупность точек, лежащих ниже прямой, соединяющей точки с координатами (65; 44,705) и (32,813; 75). Аналогично – остальные. Построение – рисунок 3.
-
Находим градиент функции Z.
grad z = {0,8; 0,5}
Строим вектор с началом в точке (0; 0) и концом в точке (0,8; 0,5).
Построение – рисунок 3.
-
Строим прямую, перпендикулярную вектору градиента. Так как по условию мы ищем максимум функции Z, то передвигаем прямую в направлении указанном вектором. Точка максимума – последняя точка области, которую пересечет эта прямая. В нашем случае, искомая точка лежит на пересечении прямых А=60 и 1,6 А + 1,7 В = 180;
Построение – рисунок 3
-
Решаем систему уравнений
А =60;
1,6А + 1,7В = 180; В = 49,412;
Т.е графическое построение дало результат (60; 49,412).
Максимальное значение функции Z = 0,8*60+0,5*49,412=72,7.
Рисунок 3 – Графическое решение задачи 4
Решение задачи 4 с использованием пакета Excel
В пакете Excel решение задачи линейного программирования осуществляется с помощью пункта меню Сервис – Поиск решения.
Распечатка решения задачи в Excel приведена в таблице 9.
Формулы, по которым был произведен расчет, приведены в таб. 10.
Таблица 9 – Решение задачи в Excel
Переменные | |||||||
A | B | ||||||
Значения | 60 | 49.412 | |||||
Нижняя граница | 10 | 0 | |||||
Верхняя граница | 60 | 70 | |||||
Z=(2.4–1.6) A+(2.2–1.7) B | 0.8 | 0.5 | 72.706 | max | |||
Коэффициенты целевой функции | |||||||
Коэффициенты | Значение | Фактические ресурсы | Неиспользованные ресурсы | ||||
Система ограничений | 1.6 | 1.7 | 180 | <= | 180 | 0 |
Таблица 10 – Формулы для расчета в Excel
Переменные | ||||||||||||
A | B | |||||||||||
Значения | 60 | 49.412 | ||||||||||
Нижняя граница | 10 | 0 | ||||||||||
Верхняя граница | 60 | 70 | ||||||||||
Z=(2.4–1.6) A+(2.2–1.7) B | 0.8 | 0.5 | =СУММПРОИЗВ (B3:C3; B6:C6) | max | ||||||||
Коэффициенты целевой функции | ||||||||||||
Коэффициенты | Значение | Фактические ресурсы | Неиспользо- ванные ресурсы | |||||||||
Система ограничений | 1.6 | 1.7 | =СУММПРОИЗВ (B3:C3; B10:C10) | <= | 180 | =F10‑D10 |
Список используемой литературы
-
Финансово-экономические расчеты в Excel. – 2-е изд., доп. – М: Информационно-издательский дом «Филинъ», 2005. – 184 с.
-
Методический указания и контрольные задания по дисциплине «Информатика» для студентов заочного факультета экономического направления обучения. Ч. 3/ Сост. В.Н. Черномаз, Т.В. Шевцова, О.А. Медведева. – ДГМА, 2006 – 40 стр.