Создание электронной таблицы для автоматизированного решения задачи

1. Запустите Microsoft Excel.

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

3. В ячейке А1 введите текст Расчет среднегодовой стоимости ОПФ. Выделите диапазон ячеек А1:D1 и примените к ним команду Объединить и поместить в центре .

4. В ячейке А2 введите Стоимость ОПФ активная, в ячейке В2 - значение 4.

5. В ячейке А3 введите Доля активной части, в ячейке В3 - значение 0,4.

6. В ячейке А4 введите Стоимость ОПФ на начало года, ячейке В4 - формулу для расчета ОПФ нг = ОПФ акт / α акт.

7. В ячейке А6 введите Месяцы года, в ячейке В6 - Январь. Остальные месяцы введите, используя функцию автозаполнения.

8. В ячейке А7 введите Прибытие ОПФ, в ячейке А8 - Выбытие ОПФ. В ячейках под названиями месяцев введите данные о прибытии выбытии ОПФ по месяцам согласно условию задачи (для ввода величин выбытия используйте отрицательные значения).

9. В ячейке А9 введите Изменение по месяцам. В ячейке В9 рассчитайте величину изменения прибытия и выбытия, используя функцию Автосумма. Остальные ячейки в строке 9 рассчитайте, используя автозаполнение.

10. В ячейке В10 введите значение стоимости ОПФ на начало года согласно расчета в п.6.

11. В ячейке А10 введите Стоимость ОПФ по месяцам. В ячейке С10 рассчитайте стоимость ОПФ в феврале месяце. Остальные ячейки в этой строке заполните, используя функцию автозаполнения. (В10=В4, С10=В10+С9 далее автозаполнение по строке)

12. В ячейке А11 введите Среднегодовая стоимость ОПФ (1-й метод). В ячейке В11 сделайте расчет этой величины по формуле, данной в решении задачи. Для электронных таблиц эта формула выглядит следующим образом.

13. В ячейке А12 введите Среднегодовая стоимость ОПФ (2-й метод). В ячейке В12 сделайте расчет этой величины по формуле, данной в решении задачи. Для электронных таблиц эта формула выглядит следующим образом.

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

15. Переименуйте Лист1 в Расчет ОПФ.

16. Переключитесь на Лист2 и переименуйте его в Диаграмма1. На данном листе постройте диаграмму Прибытие и выбытие ОПФ. Для этого используйте функцию Мастер диаграмм . На графике отобразите динамику изменения прибытия ОПФ (строка 7 на листе Расчет ОПФ) и динамику изменения выбытия ОПФ (строка 8 на листе Расчет ОПФ). График должен выглядеть примерно следующим образом.

17. Переключитесь на Лист3 и переименуйте его в Диаграмма2. На данном листе постройте диаграмму Стоимость ОПФ. На графике отобразите динамику изменения стоимости ОПФ по месяцам (строка 10 на листе Расчет ОПФ). График должен выглядеть примерно следующим образом.

18. Сохраните файл под именем основные_фонды.xls

 

Пример решения задачи из раздела II «Оптимизация»

Задача. Фирма производит две модели A и B сборных книжных полок. Их производство ограничено наличием сырья (высококачественных досок) и временем машинной обработки. Для каждого изделия модели A требуется 3 м² досок, а для изделия модели B - 4 м². Фирма может получать от своих поставщиков до 1700 м² досок в неделю. Для каждого изделия модели A требуется 12 мин машинного времени, а для изделия модели B – 30 мин. В неделю можно использовать 160 ч машинного времени. Сколько изделий каждой модели следует выпускать фирме в неделю, если каждое изделие модели A приносит 2 долл. прибыли, а каждое изделие модели B – 4 долл. прибыли?

Ход решения

Указанная задача является задачей линейной оптимизации. В табличном процессоре Microsoft Excel имеется надстройка «Поиск решения», которая позволяет решать задачи отыскания наибольших и наименьших значений.