Информатика. Учебно-методический комплекс (информационные ресурсы дисциплины: лабораторные работы с электронными таблицами в Microsoft Office и OpenOffice.org)

92 Pages·1.433 MB·Russian

by  Афанасьева С.В., Боброва Л.В. и др.

1
Федеральное агентство по образованию
Государственное образовательное учреждение высшего профессионального образования

Северо-Западный государственный заочный технический университет

Кафедра информатики

И Н Ф О Р М А Т И К А

УЧЕБНО-МЕТОДИЧЕСКИЙ КОМПЛЕКС

ИНФОРМАЦИОННЫЕ РЕСУРСЫ ДИСЦИПЛИНЫ
Методические указания к выполнению лабораторных работ

Применение электронных таблиц Excel и OpenOffice.org Calc
в финансовых и экономических расчетах

Институты:
Системного анализа, автоматики и управления.
Управления производственными и инновационными программами

Специальности:
080105.65 – финансы и кредит
080507.65 – менеджмент организации
220201.65 – управление и информатика в технических системах
220301.65 – автоматизация технологических процессов и производства в
машиностроении
Направления подготовки бакалавра:
080100.62 – экономика
080500.62 – менеджмент
220200.62 – автоматизация и управление

Санкт-Петербург
Издательство СЗТУ
2008
2
Утверждено редакционно-издательским советом университета

УДК 881.3

Информатика: учебно-методический комплекс (информационные ресурсы
дисциплины: методические указания к выполнению лабораторных работ
«Применение электронных таблиц Excel и OpenOffice.org Calc в финансовых и
экономических расчетах») /сост.: С. В. Афанасьева, Л.В. Боброва, Е.А. Рыбакова,
Н. А. Смирнова. – СПб.: Изд-во СЗТУ, 2008. – 90 с.

Данная методическая разработка соответствует требованиям
государственных образовательных стандартов высшего профессионального
образования.

В методических указаниях приводятся тринадцать лабораторных работ.
Каждая работа построена таким образом, что ее могут выполнять как студенты,
работающие с пакетом Ms Office, так и те студенты, на компьютерах которых
установлен OpenOffice.org (в последнем случае команды, отличающиеся от
команд Excel, выделены серым цветом).

Рассмотрено на заседании кафедры информатики 15.05.08 г, одобрено
методической комиссией факультета общеобразовательных наук 26.05.08.

РЕЦЕНЗЕНТЫ: кафедра информатики и вычислительной математики СЗТУ
(зав. каф. Г.Г. Ткаченко, канд. физ.-мат. наук, доц.);
М.И. Барабанова, канд. экон. наук, доц. кафедры
информатики СПбГУЭФ

СОСТАВИТЕЛИ: С.В. Афанасьева, ст. преп.,
Л.В. Боброва, канд. техн. наук, проф.;
Е.А. Рыбакова, ст. преп.,
Н.А. Смирнова, канд. техн. наук, доц.

© Северо-Западный государственный заочный технический университет, 2008
3
Введение

Информационные технологии широко используются при решении многих
управленческих и экономических задач. Целью данного комплекса
лабораторных работ является ознакомление студентов с возможностями
табличного процессора Excel и программы OpenOffice.org Calc при проведении
финансовых и экономических расчетов.
Каждая работа построена таким образом, что ее могут выполнять как
студенты, работающие с пакетом Ms Office, так и те студенты, на компьютерах
которых установлен OpenOffice.org (в последнем случае команды,
отличающиеся от команд Excel, выделены серым цветом).

Библиографический список
Основной:
1. Информатика. Базовый курс: учебник для вузов / под ред. С.В. Симоновича.
– СПб.: Питер, 2004.
2. Гарнаев, А.Н. Excel, VBA, Internet в экономике и финансах/ А.Н. Гарнаев
– СПб.: BHV, 2003.
3. Боброва, Л.В. Информатика: уч. пособие / Л.В. Боброва. – СПб.: Изд-во
СЗТУ, 2007.

Дополнительный:
4. Пикуза, В.Н. Экономические и финансовые расчеты в Excel / В.Н. Пикуза,
А.С. Гаращенко – СПб.: BHV, 2004.
5. Долженов, В.А. Microsoft Excel 2000/ В.А. Долженов, Ю.В. Колесников –
СПб.: BHV, 1998.
6. Лавренов, С.М. Excel: Сборник примеров и задач/ С.М. Лавренов – М.:
Финансы и статистика, 2000.
4
Работа 1. МАТРИЧНЫЕ ОПЕРАЦИИ В ЭЛЕКТРОННЫХ ТАБЛИЦАХ

1. Цель работы

Освоение операций с матрицами.

2. Основные теоретические положения

Табличный процессоры содержат формулы, позволяющие проводить
основные операции с матрицами: сложение, вычитание, умножение,
вычисление обратной матрицы (в Excel в категории Математические, в Calc –
в категории Массив).

3. Порядок выполнения работы

Задание 1. Выполнить сложение матриц.
Задание 2. Выполнить умножение матрицы на число и вычитание матрицы.
Задание 3. Вычислить определитель матрицы.
Задание 4. Вычислить обратную матрицу.
Задание 5. Перемножить матрицы.

3.1. Выполнение задания 1
Найдем сумму матриц С=А+В, где
1 4 −3 −3 0 4
А = − 2 6 5 и В = 5 − 7 2 .
7 −8 9 − 4 6 −8
3.1.1. Ввод матриц
Откройте новую книгу Excel (Пуск – Программы – Excel). Для работы в
программе OpenOffice.org Calc следует выполнить команды Пуск –
Программы – OpenOffice.org2.4 - OpenOffice.org Calc.Введите матрицу А в
блок ячеек А3:С5, а матрицу В в блок ячеек Е3:G5 (табл. 1 – показ вычислений,
табл. 2 – показ формул в Excel, табл. 3 – показ формул в OpenOffice.org Calc).
3.1.2. Присвоение имени блокам
Выполните следующие действия:
• выделите блок А3:С5;
• выполните команды меню Вставка – Имя – Присвоить – А – ОК (В
программе OpenOffice.org Calc выполнить команды Вставка – Названия –
Определить – А – ОК);
• выделите блок Е3:G5;
• выполните команды меню Вставка – Имя – Присвоить – В – ОК (В
программе OpenOffice.org Calc выполнить команды Вставка – Названия –
Определить – B – ОК).
3.1.3. Сложение матриц
• выделите диапазон ячеек А7:С9, где будет размещена сумма (матрица С);
• введите с клавиатуры знак равенства =;
• выполните команды меню Вставка – Имя – Вставить – А – ОК (В
программе OpenOffice.org Calc выполнить команды Вставка – Названия –
5
Вставить – А – ОК);
• введите с клавиатуры знак +;
• выполните команды Вставка – Имя – Вставить – В – ОК (В программе
OpenOffice.org Calc выполнить команды Вставка – Названия – Вставить – В
– ОК);
• для того чтобы формула = А + В была введена во все выделенные ячейки
диапазона, нажмите одновременно клавиши Ctrl+Shift+Enter.

3.2. Выполнение задания 2
3.2.1. Вычисление матрицы D = 3 * A - B
Используем матрицы А и В, которые были введены ранее (пп. 3.1.1).
Поскольку матрица D также будет иметь размерность 3х3, выделите диапазон
ячеек А11:С13.
Таблица 1. Показ вычислений
3.2.2. Ввод формул
A B C D E F G
• Введите с клавиатуры =3*;
1 Задание 1. Вычисление суммы матриц
• выполните команды Встав-
C=A+B
ка – Имя – Вставить – А –
2 Матрица А Матрица В
ОК (В программе
3 1 4 -3 -3 0 4
OpenOffice.org Calc выпол-
4 -2 6 5 5 -7 2
нить команды Вставка –
5 7 -8 9 -4 6 -8
Названия – Вставить – А –
6 Матрица С
ОК);
7 -2 4 7
• введите с клавиатуры знак
8 3 -1 7
– (минус). Формула приобре-
9 3 -2 1
тет вид =3*А–;
10 Задание 2. Вычисление матрицы D=3*A-B
• выполните команды
11 6 12 5
Вставка – Имя – Вставить –
12 -11 25 13
В – ОК (В программе
13 25 -30 35
OpenOffice.org Calc выпол-
14 Задание 3. Вычисление определителя
нить команды Вставка –
матрицы А
Названия – Вставить – В –
15 228
ОК);
16 Задание 4. Вычисление обратной
• нажмите одновременно
матрицы А
клавиши Ctrl+Shift+Enter.
17 0,412 -0,263 0,009

18 0,232 -0,053 -0,05 3.3. Выполнение задания 3
19 -0,11 0,158 0,061 3.3.1. Работа с Мастером
20 Задание 5. Умножение матриц функций
21 5 -10 -12 Выполните следующие
действия:
22 16 -12 -36

23 -97 110 -60

• активизируйте ячейку А15, затем щелкните мышью по пиктограмме Мастер
функций;
• в окне Категория выберите Математические, а в окне Функция –
6
МОПРЕД, затем ОК. (В программе OpenOffice.org Calc в окне Категория
выберите Массив, а в окне Функция – MDETERM, затем Далее);
3.3.2. Ввод формулы
• В открывшемся окне Мастера функций в поле Массив введите диапазон
ячеек исходной матрицы А3:С5, затем ОК (В программе OpenOffice.org Calc в
открывшемся окне в поле Матрица введите диапазон ячеек исходной матрицы
А3:С5, затем ОК).
Обратите внимание! При вводе диапазона А3:С5 в окне Мастера
функций появилась формула =МОПРЕД(А), так как выделенному диапазону
ранее было присвоено имя А (В программе OpenOffice.org Calc формула имеет
вид = MDETERM(А3:С5).

3.4. Выполнение задания 4
3.4.1. Выбор диапазона
Выполните следующие действия:
• выделите диапазон ячеек А17:С19, в нем будем размещать обратную матрицу;

Таблица 2 . Показ формул в табличном процессоре Excel

A B C D E F G
1 Задание 1. Вычисление суммы матриц C=A+B
2 Матрица А Матрица
В
3 1 4 -3 -3 0 4
4 -2 6 5 5 -7 2
5 7 -8 9 -4 6 -8
6 Матрица С
7 =А+В = А+В = А+В
8 =А+В = А+В = А+В
9 =А+В = А+В = А+В
10 Задание 2. Вычисление матрицы D=3*A-B
11 =3*А-В =3*А-В =3*А-В
12 =3*А-В =3*А-В =3*А-В
13 =3*А-В =3*А-В =3*А-В
14 Задание 3. Вычисление определителя матрицы А
15 =МОПРЕД(А)
16 Задание 4 . Вычисление обратной матрицы А
17 =МОБР(А3:С5) =МОБР(А3:С5) =МОБР(..
18 =МОБР(А3:С5) =МОБР(А3:С5) =МОБР(..
19 =МОБР(А3:С5) =МОБР(А3:С5) =МОБР(..
20 Задание 5. Умножение матриц
21 =МУМНОЖ(А3:С5;Е3:G5)=МУМНОЖ(А3:С5;Е3:G5)=МУМН..
22 =МУМНОЖ(А3:С5;Е3:G5)=МУМНОЖ(А3:С5;Е3:G5)=МУМН..
23 =МУМНОЖ(А3:С5;Е3:G5)=МУМНОЖ(А3:С5;Е3:G5)=МУМН..
7
Таблица 3 . Показ формул в табличном процессоре Calc

A B C D E F G
1 Задание 1. Вычисление суммы матриц C=A+B
2 Матрица А Матрица В
3 1 4 -3 -3 0 4
4 -2 6 5 5 -7 2
5 7 -8 9 -4 6 -8
6 Матрица С
7 =А+В = А+В = А+В
8 =А+В = А+В = А+В
9 =А+В = А+В = А+В
10 Задание 2. Вычисление матрицы D=3*A-B
11 =3*А-В =3*А-В =3*А-В
12 =3*А-В =3*А-В =3*А-В
13 =3*А-В =3*А-В =3*А-В
14 Задание 3. Вычисление определителя матрицы А
15 =МDETERM(А)
16 Задание 4 . Вычисление обратной матрицы А
17 =МINVERSE(А3:С5) = МINVERSE (А3:С5) = МINV..
18 = МINVERSE (А3:С5) = МINVERSE (А3:С5) =МINV(..
19 = МINVERSE (А3:С5) = МINVERSE (А3:С5) =МINV(..
20 Задание 5. Умножение матриц
21 =МMULT(А3:С5;Е3:G5) = МMULT (А3:С5;Е3:G5)= МMULT
22 = МMULT (А3:С5;Е3:G5)= МMULT (А3:С5;Е3:G5)= МMULT
23 = МMULT (А3:С5;Е3:G5)= МMULT (А3:С5;Е3:G5)= МMULT

3.4.2. Ввод формулы
• щелкните мышью по пиктограмме Мастер функций;
• в окне Категория выберите Математические, а в окне Функция – МОБР,
затем ОК (В программе OpenOffice.org Calc в окне Категория выберите
Массив, а в окне Функция – MINVERSE , затем Далее);
• в открывшемся окне Мастера функций в поле Массив (Матрица) введите
диапазон ячеек исходной матрицы А3:С5;
• нажмите одновременно клавиши Ctrl+Shift+Enter.

3.5. Выполнение задания 5
3.5.1. Работа с Мастером функций
Перемножим матрицы А и В. В результате получим матрицу М = А * В.
Для этого:
• выделите диапазон ячеек А21:С23, в нем будем размещать результат
вычислений;
• щелкните мышью по пиктограмме Мастер функций;
• в окне Категория выберите Математические, а в окне Функция –
8
МУМНОЖ, затем ОК (В программе OpenOffice.org Calc в окне Категория
выберите Массив, а в окне Функция – MMULT, затем Далее);
• в открывшемся окне Мастера функций в поле Массив 1 (верхнее поле
Матрица) введите адрес исходной матрицы А (А3:С5), а в поле Массив 2
(нижнее поле Матрица) введите адрес исходной матрицы В (Е3:G5), затем
Ctrl+Shift+Enter.
Обратите внимание! Формула записалась в виде
=МУМНОЖ(А3:С5;Е3:G5).
В программе OpenOffice.org Calc = MMULT(А3:С5; Е3:G5)

3.6. Самостоятельная работа
Вычислите матрицу, обратную матрице В. Результат вычислений
продемонстрируйте преподавателю.

4. Отчет по работе
Распечатка таблицы (показ формул и показ вычислений).

Литература: [6], с. 166-171.

Работа 2. РЕШЕНИЕ СИСТЕМ УРАВНЕНИЙ

1. Цель работы

Научиться приемам решения систем уравнений.

2. Основные теоретические положения

Можно отметить два основных способа решения систем уравнений в
электронных таблицах. Первый способ – использовать процедуру Поиск
решения. Предпишем этой процедуре перебрать все возможные значения
переменных и выбрать в качестве целевой функции суммарное отклонение
левых частей уравнений для каждого набора переменных от правых частей
уравнений. Тогда при требовании обеспечить нулевые значения такого
отклонения Поиск решения найдет корни.
Второй способ использует метод обратных матриц для решения систем
уравнений и основан на использовании матричных операций в электронных таблицах.

3. Порядок выполнения работы

Задание 1. Решить систему уравнений
⎧3x + 4y + 2z = 8,
⎪
⎨2x − y −3z = −4,

⎪
⎩x +5y + z = 0.

с использованием процедуры Поиск решения.
Задание 2. Решить систему уравнений из задания 1 матричным методом.
9
Задание 3. Решить систему уравнений согласно индивидуальному заданию.

3.1. Выполнение задания 1

3.1.1. Ввести исходные данные в строки 1-9 электронной таблицы (табл. 4 в
режиме показа вычислений и табл. 5 в режиме показа формул).
3.1.2. В качестве начального приближения возьмем все значения
переменных равными единице. Заполнить этими значениями ячейки А10:С10.
3.1.3. Ввести формулы для вычисления левых частей уравнений в ячейки
А12:А14.
В программе OpenOffice.org Calc функция, аналогичная функции
СУММПРОИЗВ в программе Excel – SUMPRODUCT (категория Массив).
3.1.4. В ячейки Е12:Е14 ввести формулы для вычисления отклонений
значений левых частей уравнения при данном наборе переменных от правых
частей исходных уравнений.
3.1.5. В ячейке F13 вычислить суммарные отклонения.
В программе OpenOffice.org Calc функция, аналогичная функции СУММ в
программе Excel – SUM (категория Математические)

Таблица 4. Решение системы уравнений. Показ вычислений
A B C D E F
Решение системы уравнений с использованием процедуры
1 Поиск Решения
2 Имена переменных
3 X Y Z

Правая часть
Матрица коэффициентов системы
системы
уравнений
4 уравнений

5 3 4 2 8

6 2 -1 -3 -4

7 1 5 1 0

8

Приближенные значения неизвестных
9 (начальное приближение)

10 1 1 1

Отклонение
Значения левой
приближенного
части системы Суммарное
значения правых
уравнений для отклонение
частей
приближенных (целевая
уравнений от
значений функция)
истинного
неизвестных
11 значения

12 9 1

13 -2 2 10

14 7 7
10
Обратимся к процедуре Поиск решения: Сервис – Поиск решения.
3.1.6. В окне Поиск решения ввести:
Установить целевую ячейку F13
Равной (cid:127) Значению 0
(В программе OpenOffice.org Calc: Равной (cid:127) Значению Минимум)
Изменяя ячейки (Параметры функции) А10:С10
щелкнуть по кнопке Добавление ограничений
3.1.8. В окне Добавление ограничений ввести:
А12:А14=Е5:Е7, Ок.
3.1.9. Щелкнуть по кнопке Выполнить (Решить).
В результате получим следующие значения переменных (табл. 6).
x = 2; у = -1; z = 3.

3.2. Выполнение задания 2
Для решения системы уравнений матричным способом следует:
а) найти матрицу коэффициентов, обратную исходной матрице;
б) умножить полученную обратную матрицу на столбец свободных членов.
3.2.1. Вычисление обратной матрицы:
- выделить ячейки А16:С18;
- выполнить команды Вставка – Функция – Математические –
МОБР (В программе OpenOffice.org Calc в окне Категория выберите
Массив, а в окне Функция – MINVERSE , затем Далее);
- указать диапазон исходной матрицы А5:С7;
- одновременно нажать клавиши Ctrl+Shift+Enter.

3.2.2. Умножение матриц:
- выделить ячейки Е16:Е18;
- Вставка – Функция – Математические – МУМНОЖ (В программе
OpenOffice.org Calc в окне Категория выберите Массив, а в окне
Функция – MMULT, затем Далее);
- ввести: Массив 1 (Матрица) А16:С18
Массив 2 (Матрица) Е5:E7 ;
- одновременно нажать клавиши Ctrl+Shift+Enter.

В результате в ячейках Е16:Е18 получим те же самые значения корней, что в
пп. 3.1.

3.3. Выполнение задания 3
3.3.1. Выбрать из табл. 7 индивидуальное задание по последней цифре
шифра.
3.3.2. Решить систему уравнений с использованием процедуры Поиск
решения и матричным методом.