3. Порядок выполнения работы
Задание. Реализовать балансовую модель в электронной таблице (ЭТ) Excel.
3.1. Выполнение задания
Компьютерная реализация балансовой модели в ЭТ показана в табл. 8 (режим показа формул) и в табл. 9 (режим вычислений).
Для реализации задачи в электронной таблице выполним следующие действия:
3.1.1. Создать блок исходных данных. В ячейки А2:D5 ввести исходные данные из таблицы задания.
3.1.2. В ячейках B6:D8 разместить формулы для вычисления технологических коэффициентов:
в ячейку В6 ввести формулу для вычисления первого коэффициента =B3/$A$3 и скопировать ее в ячейки В7:B8;
Таблица 8
|
|
A |
B |
C |
D | |
|
1 |
БАЛАНСОВАЯ МОДЕЛЬ | ||||
|
2 |
Объём про- изводства |
Потребление отраслей | |||
|
3 |
600 |
250 |
100 |
160 | |
|
4 |
1000 |
150 |
500 |
0 | |
|
5 |
800 |
0 |
300 |
400 | |
|
6 |
Вычисление технологиче-ских коэф-фициентов |
=В3/А$3 |
=С3/А$4 |
=D3/А$5 | |
|
7 |
= В4/А$3 |
=С4/А$4 |
=D4/А$5 | ||
|
8 |
= В5/А$3 |
=С5/А$4 |
=D5/А$5 | ||
|
9 |
Проверка продуктивности матрицы А | ||||
|
10 |
|
=СУММ(B6:B8) |
=СУММ(C6:C8) |
=СУММ(D6:D8) | |
|
11 |
=ИЛИ(B10>=1;C10>=1;D10>=1) |
=ЕСЛИ(A11=ИСТИНА;"Решения нет";"Матрица продуктивна") | |||
|
12 |
Единичная матрица |
1 |
0 |
0 | |
|
13 |
0 |
1 |
0 | ||
|
14 |
0 |
0 |
1 | ||
|
15 |
Вычисление Е-А |
=B12-B6 |
=C12-C6 |
=D12-D6 | |
|
16 |
=B13-B7 |
=C13-C7 |
=D13-D7 | ||
|
17 |
=B14-B8 |
=C14-C8 |
=D14-D8 | ||
|
18 |
Вычисление обратной матрицы |
=МОБР(B15:D17) |
=МОБР(B15:D17) |
=МОБР(B15:D17) | |
|
19 |
=МОБР(B15:D17) |
=МОБР(B15:D17) |
=МОБР(B15:D17) | ||
|
20 |
=МОБР(B15:D17) |
=МОБР(B15:D17) |
=МОБР(B15:D17) | ||
|
21 |
Спрос на будущий период |
2000 |
План выпуска продукции |
=МУМНОЖ(B18:D20;B21:B23) | |
|
22 |
2000 |
=МУМНОЖ(B18:D20;B21:B23) | |||
|
23 |
3000 |
=МУМНОЖ(B18:D20;B21:B23) | |||
аналогично в ячейку С6 ввести формулу =C3/$A$4 и скопировать ее в ячейки С7:С8;
в ячейку D6 ввести формулу =D3/$A$5 и скопировать ее в ячейки D7:D8.
3.1.3. В ячейках В10:D10 разместить формулы для подсчета суммы значений элементов по столбцам:
в ячейку В10 ввести формулу =СУММ(В7:В9);
скопировать формулу в ячейки С10:D10.
3.1.4. В строке 11 размещаем формулы для проверки продуктивности матрицы технологических коэффициентов:
в ячейку А11 ввести формулу =ИЛИ(В10>=1;C10>=1;D10>=1).
Эта формула проверяет содержимое ячеек В10:D10. Если хотя бы в одной из этих ячеек значение больше единицы (т.е. сумма значений элементов хотя бы в одном столбце превышает единицу), то в ячейке А11 будет записано значение ИСТИНА». В противном случае – значение «ЛОЖЬ»;
в ячейку C13 ввести формулу
=ЕСЛИ(А11=”ИСТИНА”;”Нет решения”;”Матрица продуктивна”).
Эта формула проверяет содержимое ячейки А11 и если сумма элементов хотя бы одного столбца превысила единицу, выводит сообщение “Нет решения”, а в противном случае – “Матрица продуктивна”.
3.1.5. В строках 12 –14 разместить единичную матрицу Е.
3.1.6. В строках 15 – 17 произвести вычисление матрицы Е-А:
в ячейку В15 поместить формулу =В12-В6;
скопировать формулу в ячейки В16:D17.
3.1.7. В строках 18 – 20 разместим формулы для вычисления матрицы, обратной матрице Е-А:
активизировать ячейку В18;
зажав левую клавишу мыши, выделить диапазон ячеек В18:D20, где будет размещена обратная матрица;
щелкнуть по пиктограмме Мастер функций fx;
в первом окне Мастера функций в поле Категория выбрать Математические;
в поле Функция среди расположенных по алфавиту функций найти функцию МОБР;
щелкнуть по кнопке Ок и перейти во второе окно Мастера функций;
в поле Массив ввести адрес матрицы Е-А: диапазон ячеек В15:D17;
чтобы формула была введена во все ячейки выделенного диапазона, следует одновременно нажать клавиши Ctrl+Shift+Enter; после этого в ячейки В18:D20 будет введена формула =МОБР(В15:D17).
3.1.8. В строках 21 – 23 поместим формулы для вычисления плана выпуска продукции:
в ячейках В21:В23 разместить значения спроса на будущий период согласно заданию (табл. 8);
выполнить команды Вставка – Функция;
выделить ячейки D21:D23, в которых будет размещена формула перемножения элементов матрицы, обратной Е-А, и вектора-столбца спроса;
в категории Математические Мастера функций выбрать функцию МУМНОЖ;
во втором окне Мастера в поле Массив1 ввести адрес обратной матрицы: диапазон ячеек В18:D20;
в поле Массив2 ввести адрес вектора-столбца спроса: диапазон ячеек В21:D23;
чтобы формула была введена во все ячейки выделенного диапазона, следует одновременно нажать клавиши Ctrl+Shift+Enter. После этого в ячейки D21:D23 будет введена формула =МУМНОЖ(В18:D20;B21:B23).
Таблица 9
|
|
A |
B |
C |
D |
|
1 |
БАЛАНСОВАЯ МОДЕЛЬ | |||
|
2 |
Объём производства |
Потребление отраслей | ||
|
3 |
600 |
250 |
100 |
160 |
|
4 |
1000 |
150 |
500 |
0 |
|
5 |
800 |
0 |
300 |
400 |
|
6 |
Вычисление технологических коэффициентов |
0,417 |
0,1 |
0,2 |
|
7 |
0,25 |
0,5 |
0 | |
|
8 |
0 |
0,3 |
0,5 | |
|
9 |
Проверка продуктивности матрицы А | |||
|
10 |
|
0,667 |
0,900 |
0,700 |
|
11 |
ЛОЖЬ |
Матрица продуктивна | ||
|
12 |
Единичная матрица |
1 |
0 |
0 |
|
13 |
0 |
1 |
0 | |
|
14 |
0 |
0 |
1 | |
|
15 |
Вычисление Е-А |
0,583 |
-0,1 |
-0,2 |
|
16 |
-0,25 |
0,5 |
0 | |
|
17 |
0 |
-0,3 |
0,5 | |
|
18 |
Вычисление обратной матрицы |
2,113 |
0,930 |
0,845 |
|
19 |
1,056 |
2,465 |
0,423 | |
|
20 |
0,634 |
1,479 |
2,254 | |
|
21 |
Спрос на будущий период |
2000 |
План выпуска продукции |
8619,72 |
|
22 |
2000 |
8309,86 | ||
|
23 |
3000 |
10985,92 | ||