Примечание. Формулы во вторую строку можно ввести протяжкой из первой строки, но при этом надо предварительно закрепить клавишей F4 адреса
Формулы во вторую строку можно ввести протяжкой из первой строки, но при этом надо предварительно закрепить клавишей F4 адреса, которые не должны меняться при копировании.
Пояснение к задачам 7.2.3–7.2.7
В этих задачах требуется решить предложенную систему нелинейных уравнений. Для выбора начального приближения следует составить таблицу вспомогательной целевой функции (см. подразд. 6.7).
7.2.3. х2 + у2 = 1; у = –х2.
7.2.4. 3х2 + 5у2 = 24; ху = –0,4.
7.2.5. ху = –32; х = –6у2.
7.2.6. ху = 31; у = –6х2.
7.2.7.2х2 + 5у2 = 17; ху =0,3.
Поиск безусловных экстремумов и корней функций
Пояснение к задачам 7.3.13–7.3.5
В этих задачах требуется найти графически и по командам Сервис ® Подбор параметра…, Сервис ® Поиск решения… минимумы и корни предложенных функции (см. подразд. 2.1 и 6.5).
7.3.1. х4 – х3 – х2 – 2х + 1.
7.3.2. х4 –2х3 – 3х2 – 3х – 32.
7.3.3. х4 – 3х3 + 2х2 – 20.
7.3.4. х4 – х3 – 3х2 – 5х – 16.
7.3.5. х4 – 2х3 + х2 – 3х – 22.
Задачи планирования
7.4.1.Предприятие электронной промышленности выпускает две модели радиоприемников, причем каждая модель производится на отдельной технологической линии. Суточный объем производства первой линии – 60 изделий, второй – 75 изделий. В радиоприемниках первой модели расходуется 10 однотипных электронных схем, во второй – 8 таких же схем. Максимальный суточный запас этих схем – 800 штук. Прибыль от реализации одного радиоприемника первой модели $30, второй – $20. Определить оптимальный план производства.
7.4.2.Фирма имеет возможность рекламировать свою продукцию через теле- и радиосеть. Каждая минута радиорекламы обходится в $5, телерекламы – в $100. Затраты на рекламу в бюджете фирмы ограничены суммой в $1000. Эффективность телерекламы, как показал опыт предыдущих лет, в 25 раз выше, чем радиорекламы. Определить оптимальное распределение средств между радио- и телерекламой.
7.4.3.Фирма производит два вида продукции – А и В. Объем сбыта продукции А составляет не менее 60 % общего объема реализации продукции обоих видов. Для изготовления обоих видов продукции используется одно и то же сырье, суточный запас которого может быть не более 100 кг. Расход сырья на единицу продукции А – 2 кг, на единицу продукции В – 4 кг. Прибыль от реализации продукции А и В соответственно $20 и $40. Определить оптимальное распределение сырья между видами продукции.
7.4.4.Фирма выпускает ковбойские шляпы фасонов А и В. Трудоемкость изготовления шляпы фасона А вдвое выше, чем для фасона В. Если бы фирма выпускала шляпы только фасона А, то суточный объем производства мог бы составить 160 шляп. Совокупный объем сбыта за сутки составляет 150–200 шляп. Прибыль от продажи одной шляпы А составляет $8, шляпы В – $5. Определить оптимальный план производства каждого вида шляп.
7.4.5.Изделия четырех типов (А, В, С и D) проходят последовательную обработку на двух станках. Время обработки одного изделия каждого типа на каждом станке приведено в табл. 7.4.1. Затраты на производство каждого типа изделий пропорциональны времени использования станков в машино-часах. Стоимость 1 часа для станка 1 – $5, для станка 2 – $7. Совокупное время использования станков 1 и 2, которое можно использовать для выпуска любых изделий, соответственно 500 и 380 машино-часов. Цена реализации одного изделия A, B, C или D соответственно $65, $70, $55, $45. Определить оптимальный план производства.
Таблица 7.4.1
| Станок | Время обработки одного изделия, ч | |||
| Тип А | Тип В | Тип С | Тип D | |
7.4.6.Фирма выпускает аквариумы трех моделей (А, В и С). Для их изготовления используются стекло и уголки, запасы которых на складе составляют соответственно 400 м2 и 600 м. Расход материала на каждую модель приведен в табл. 7.4.2. Минимальный спрос на модели А, В и С составляет соответственно 200, 200 и 150 штук в месяц. Прибыль от реализации каждой модели составляет соответственно $30, $20 и $50. Определить план выпуска разных моделей, максимизирующий прибыль.
Таблица 7.4.2
| Ресурс | Запас | Расход на 1 изделие | ||
| Модель А | Модель В | Модель С | ||
| Стекло, м2 | 0,6 | 1,1 | 0,4 | |
| Уголки, м | 1,2 | 0,9 | 0,7 | |
| Трудоемкость, час | 0,5 | 1,1 | 1,6 |
7.4.7.Для производства двух продуктов (А и В) необходимы два химических процесса. На производство единицы продукта А требуется процесс 1 в течение 2 ч и процесс 2 в течение 4 ч. Для производства единицы продукта В требуется процесс 1 в течение 1,5 ч, процесс 2 – в течение 5 ч. Процесс 1 можно использовать 16 ч в сутки, процесс 2 – 24 ч. При производстве единицы продукта В в качестве побочного продукта получается 2 единицы продукта С. Некоторую его часть можно продать, остальное уничтожается. Прибыль по продукту А – $4 за единицу продукта, по продуктам В и С – соответственно $10 и $3. Затраты на ликвидацию излишков единицы продукта С – $2. По прогнозу возможность сбыта продукта С составляет не более 10 единиц. Определить оптимальный план производства.
7.4.8. Продукция типа А выпускается в течение 4-х недель. Стоимость производства единицы продукции равна $10 в течение первых двух недель и $15 в третью и четвертую недели. Потребности этой продукции равны по неделям соответственно 300, 700, 900 и 800 единиц. Производственные мощности позволяют выпускать до 700 единиц продукции в неделю. Во вторую и третью неделю возможно применение сверхурочных работ, при которых можно дополнительно произвести еще до 200 единиц продукции. Оплата сверхурочных на $5 выше тарифа для основных работ. Стоимость хранения единицы продукции $3. Найти оптимальный план производства продукции.
7.4.9. Фирма выпускает два варианта растворителя (А и В), отличающихся по чистоте. Растворитель обоих видов выпускается в упаковке емкостью 1 л. Прибыль по А составляет 0,4 р./л, по В – 0,3 р./л. Время производства растворителя А в 2 раза дольше, чем для В. Производя только растворитель В, фирма может выпустить не более 500 л в день. Контракт предусматривает, что каждый день должно выпускаться не менее 200 л растворителя В. Объем выпуска растворителя А не оговаривается. Определить оптимальный план производства.
7.4.10.Фирма выпускает три продукта (А, В, С). На производство единицы продукта А требуется затратить 10 ч физического труда, 1 ч – квалифицированного и 3 кг сырья. На единицу продукта В расходуется соответственно 4 ч, 2 ч и 2 кг. На единицу продукта С – 5 ч, 1 ч и 1 кг. Ресурсы фирмы: 700 ч физического труда, 100 ч – квалифицированного и 400 кг сырья. При оптовых закупках покупателю предоставляются скидки. Удельная прибыль от продажи единицы каждого продукта с учетом этого приведена в табл. 7.4.3. Определить оптимальный план производства в каждом оптовом диапазоне и выбрать оптимальный набор диапазонов.
Таблица 7.4.3
| Продукт А | Продукт В | Продукт С | |||
| Продажа, ед. | Удельная прибыль, $ | Продажа, ед. | Удельная прибыль, $ | Продажа, ед. | Удельная прибыль, $ |
| 0–40 | 0–50 | 0–100 | |||
| 40–100 | 50–100 | Более 100 | |||
| Более 100 | Более 100 |
7.5. Задачи на подбор функций по таблице опытных
данных
7.5.1. В табл. 7.5.1 представлен объем реализации фирмы, торгующей автомобилями, за 12 недель ее работы. Необходимо подобрать функцию, которая хорошо воспроизводит эти данные, и сделать прогноз сбыта на следующие две недели.
Таблица 7.5.1
| Неделя | ||||||||||||
| Сбыт |
Подсказка
1. Изобразите опытные данные на точечной диаграмме (см. подразд. 4.1–4.5). "На глаз" видно, что можно использовать следующие математические модели:
· очень грубо – линейную y = a + bx (линейный тренд);
· с учетом замедления роста сбыта в конце периода – квадратичную y = a + bx + cх2 (полиномиальный тренд второй степени);
· если предполагается, что сбыт выходит на стационарный уровень, то y = a + b / (x – c) или y = a + barctg(cx) (нестандартные тренды).
2. Добавьте к диаграмме стандартные тренды и их уравнения (см. подразд. 4.6). Изобразите каждый тренд и его уравнение новым цветом.
3. Рассчитайте нестандартные тренды по методике, изложенной в подразд. 6.8 (в расчетном шаблоне надо предусмотреть две строки с коэффициентами разных трендов и дополнительные графы с расчетами по ним). Добавьте их изображения на диаграмму (команда Диаграмма ® Добавить данные… или Диаграмма ® Исходные данные…). Изобразите их сплошными линиями без маркеров разных цветов.
4. Выберите "на глаз" наиболее удачные линии тренда и сосчитайте средние квадраты отклонений исходных данных от расчетных по ним.
5. Сделайте вывод, какой тренд лучше всего использовать для прогноза сбыта на следующие недели.
7.5.2.Подберите наилучшие коэффициенты для описания данных, представленных в табл. 7.5.2, функцией y = a + bx + cх2.
Таблица 7.5.2
| х | –3 | –2,5 | –2,0 | –1,5 | –1,0 | –0,5 | 0,5 | 1,0 | 1,5 | |
| у | 0,36 | –1,83 | –3,32 | –4,18 | –4,43 | –4,06 | –2,90 | –1,22 | 1,20 | 4,21 |
7.5.3.Подберите наилучшие коэффициенты для описания данных, представленных в табл. 7.5.3, функцией y = a + bx + cх2 + dx3.
Таблица 7.5.3
| х | 4,7 | 4,4 | 4,1 | 3,8 | 3,5 | 3,2 | 2,9 | 2,6 | 2,3 | |
| у |
Пояснение к задачам 7.5.4–7.5.13
В этих задачах надо подобрать численные значения коэффициентов предложенных функций так, чтобы они наилучшим образом воспроизводили данные, представленные в задаче. Для того чтобы воспользоваться методикой, предложенной в подразд. 6.8, надо все комбинации аргументов и соответствующие им значения функций расположить в виде линейной, а не прямоугольной таблицы (первая строка – х, вторая – у, третья – z).
| 7.5.4. z = a + bxexp(cy) | 7.5.5. z = a / (bx + cy) | |||||||||||||||||||||||||||
| у х | 2,0 | 2,2 | 2,4 | у х | 1,0 | 1,8 | 2,4 | |||||||||||||||||||||
| 1,0 | 7,67 | 7,90 | 8,16 | 1,0 | 3,11 | 2,08 | 1,51 | |||||||||||||||||||||
| 1,5 | 8,76 | 9,11 | 9,48 | 1,5 | 2,24 | 1,65 | 1,27 | |||||||||||||||||||||
| 2,0 | 9,85 | 10,31 | 10,81 | 2,0 | 1,75 | 1,37 | 1,10 | |||||||||||||||||||||
| 2,5 | 10,94 | 11,51 | 12,14 | 2,5 | 1,44 | 1,17 | 0,96 | |||||||||||||||||||||
| 7.5.6. z = a / (bx + exp(cy)) | 7.5.7. z = ax + bexp(cy) | |||||||||||||||||||||||||||
| у х | 0,5 | 0,7 | 0,9 | у х | 0,1 | 0,4 | 0,8 | |||||||||||||||||||||
| 0,1 | 26,13 | 21,37 | 17,41 | 0,2 | 0,57 | 0,59 | 0,63 | |||||||||||||||||||||
| 0,2 | 23,89 | 19,84 | 16,39 | 0,5 | 0,81 | 0,83 | 0,87 | |||||||||||||||||||||
| 0,3 | 21,99 | 18,52 | 15,47 | 1,1 | 1,29 | 1,31 | 1,35 | |||||||||||||||||||||
| 0,4 | 20,38 | 17,36 | 14,66 | 1,7 | 1,77 | 1,79 | 1,83 | |||||||||||||||||||||
| 7.5.8. z = (a + x) / (b + cy2) | 7.5.9. z = a (xb + exp(cy)) | |||||||||||||||||||||||||||
| у х | 0,5 | 1,5 | 2,5 | у х | 2,0 | 5,0 | 10,0 | |||||||||||||||||||||
| 1,0 | 0,74 | 0,37 | 0,18 | 1,0 | 44,43 | 9,31 | 6,21 | |||||||||||||||||||||
| 4,0 | 1,75 | 0,87 | 0,43 | 4,0 | 6,21 | 5,48 | 4,24 | |||||||||||||||||||||
| 9,0 | 3,43 | 1,71 | 0,85 | 7,0 | 4,24 | 3,88 | 3,22 | |||||||||||||||||||||
| 16,0 | 5,78 | 2,88 | 1,44 | 10,0 | 3,21 | 3,01 | 2,59 | |||||||||||||||||||||
| 7.5.10. z = a + bxyc | 7.5.11. z = (a + bx)yc | |||||||||||||||||||||||||||
| у х | 0,4 | 0,9 | 1,5 | у х | 2,0 | 3,0 | 5,0 | |||||||||||||||||||||
| 0,4 | -0,07 | 0,03 | 0,22 | 2,0 | 28,50 | 75,42 | 256,99 | |||||||||||||||||||||
| 0,9 | -0,02 | 0,20 | 0,62 | 4,0 | 45,39 | 120,11 | 409,29 | |||||||||||||||||||||
| 1,1 | -0,01 | 0,27 | 0,78 | 8,0 | 79,17 | 209,50 | 713,87 | |||||||||||||||||||||
| 1,8 | 0,05 | 0,50 | 1,33 | 16,0 | 146,73 | 388,27 | 1323,04 | |||||||||||||||||||||
| 7.5.12. z = ayc + bx | 7.5.13. z = (a + bxc)y | |||||||||||||||||||||||||||
| у х | 0,4 | 0,9 | 1,5 | у х | 2,0 | 3,0 | 5,0 | |||||||||||||||||||||
| 0,4 | -0,50 | 6,50 | 19,40 | 2,0 | 6,2 | 8,4 | 14,1 | |||||||||||||||||||||
| 0,9 | -3,20 | 3,20 | 17,10 | 4,0 | 0,3 | -0,5 | -0,9 | |||||||||||||||||||||
| 1,1 | -4,30 | 3,10 | 17,90 | 8,0 | -8,1 | -13,4 | -20,2 | |||||||||||||||||||||
| 1,8 | -8,20 | -0,75 | 12,90 | 16,0 | -18,8 | -28,9 | -52,6 | |||||||||||||||||||||
СПИСОК ЛИТЕРАТУРЫ
1. Гарнаев. А. Excel, VBA, Internet в экономике и финансах. – СПб.: БХВ-Петербург, 2002. – 816 с.
2. Дадлей К., Кокс Дж., Урбан Microsoft Оffice 97: Краткий курс. – СПб: ЗАО Изд-во "Питер", 1999. – 384 с.
3. Долженков В.А., Колесников Ю.В. Самоучитель Micro-
soft® Excel 2002. – СПб.: БХВ-Петербург, 2002. – 432 с.
4. Попов А. Excel: Практическое руководство. – М.: DECCOM, 2000. – 302 с.
Дата добавления: 2015-01-02; просмотров: 971;