Применение средств табличного процессора MS Excel для моделирования экономических ситуаций

1.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
Применение средств
табличного процессора MS
Excel для моделирования
экономических ситуаций
Подготовила: к.п.н, доцент
Пьянкова Н.Г.
1

2.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
Цель занятия:
изучение функциональных
возможностей табличного процессора Excel 2007 и
приобретение навыков практической работы по
созданию и редактированию электронных таблиц в
целях моделирования экономических задач.
Задачи занятия:
выделить основные принципы работы с
табличным процессором;
научиться
создавать
и
редактировать
электронные таблицы, вводить и редактировать
формулы, использовать функции для решения
экономических задач.
2

3.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
План лабораторного занятия:
1. Основные понятия электронных таблиц
2. Относительные, абсолютные и смешанные
адреса ячеек
3. Ввод и редактирование формул
4. Математические функции
5. Задания для самостоятельной работы
6. Литература
3

4.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
Электронная таблица – это форма организации данных по
столбцам и строкам. В ячейках (клетках) электронной
таблицы могут содержаться текст, числа, даты, формулы,
функции, графические изображения и другая информация.
Несомненное достоинство электронных таблиц для
профессиональных
экономистов
заключается
в
практически мгновенном пересчете всех содержащихся в
таблице данных, связанных формульными зависимостями,
при изменении значения любого из них.
4

5.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
MS Excel в рамках моделирования позволяет:
производить
вычисления,
используя
не
только
создаваемые самим пользователем формулы, но и большой
набор готовых встроенных функций;
осуществлять
математическое
моделирование
и
численное экспериментирование с данными в таблицах
(типа что будет, если);
осуществлять статистический анализ, прогнозирование и
оптимизацию результатов вычислений (поддержку принятия
решений);
наглядно представлять зависимости между данными в
виде диаграмм.
5

6.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
Документ в программе Excel называется рабочей книгой, которая в
свою очередь состоит из рабочих листов, называемых
электронными таблицами. Таким образом, рабочая книга Excel –
это совокупность Рабочих листов, сохраненных в виде единого
файла на одном диске.
Объектом
обработки
программы
является
произвольным именем и расширением
97-2003) и .xlsx ( начиная с MS Excel 2007).
файл
с
.xls ( MS Excel
В
каждом
файле, обрабатываемом программой, может
размещаться одна книга, а в книге – от 1 до 255 рабочих листов
(электронных таблиц). По умолчанию в каждой книге содержится по
3 рабочих листа.
6

7.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
1. Формулы
2. Адреса ячеек
Относительные
7

8.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
Абсолютные адреса
F4
Относительный адрес -> Абсолютный адрес
8

9.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
Смешанные адреса
F4
Абсолютный адрес -> Смешанный адрес
9

10.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
3. Функции
10

11.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
3.1 Математические функции
МОПРЕД(массив)
11

12.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
Пример 1. Исследовать систему линейных уравнений и, если решение
существует найти по формулам Крамера решение системы
A*x=b.
2
1
A
2,3
3
7
3 16 7,5
8 4
4
2 6.7 5
6
5
1 .0
2,0
b
3 .5
4,0
Найдем определитель матрицы А
12

13.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
Как мы видим определитель
матрицы А Δ=823.76. Если
определитель матрицы не
равен нулю, значит
решение системы
существует.
Составим матрицу А1, заменив первый столбец матрицы А на значения
вектора В и найдем определитель матрицы А1, обозначив его Δ1.
13

14.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
Δ1 =
1378
14

15.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
По аналогии найдем определители матриц А2, А3, А4.
А2:
Δ2 =
114,715
15

16.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
А3:
Δ3 =
256,8
16

17.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
А4:
Δ4 =
-557,79
17

18.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
Затем найдём x1, x2, x3 и x4
i
по формулам Крамера: хi
x1
x2
x3
x4
1,672817 0,139258 0,311741 -0,67713
18

19.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
МУМНОЖ(Массив1; Массив2)
Количество столбцов Массива1 должно совпадать с количеством с
количеством строк Массива2. В результате получается массив с
количеством строк Массва1 и количеством столбцов Массива2..
Каждый элемент нового массива находится по формуле:
Пример 2.
1 4 8 8 1 * 8 4 *1 8 * 2
3 7 2 1 3 * 8 7 *1 2 * 2
6 9 5 2 6 * 8 9 *1 5 * 2
19

20.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
Рассмотрим решение примера средствами MS Excel.
1.
20

21.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
2.
21

22.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
3. Нажмите Shift+Ctrl+Enter
22

23.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
Функция СУММ(число1;число2;…)
Функция СУММ(адрес1;адрес2;…)
Функция СУММ(диазан1; диапазон2; …)
Пример 3: Рассчитать общую сумму покупки в магазине
23

24.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
24

25.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
Также для нахождения итоговой суммы
можно использовать значок Σ – автосумма
25

26.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
СУММЕСЛИ(Диапазон1;
Критерий; Диапазон
суммирования)
Пример 4. Посчитать итоговую
сумму товара по каждой кассе,
если известны следующие
данные.
Для решения использовать
функцию СУММЕСЛИ
26

27.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
27

28.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
В результате
получаем
следующие итоги
28

29.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
3.2. Логические функции
ЕСЛИ (логическое выражение; значения_если_истина;
значение_если_ложь)
И (логическое выражение1; логическое выражение2;
логическое выражение3; …)
ИЛИ
(логическое
выражение1;
логическое
выражение2; логическое выражение3; …)
29

30.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
Пример 5. Вычислить сумму комиссионных с учетом заданных
данных
Для расчета комиссионных используем формулу:
ЕСЛИ объем продаж ≤нормированного объема продаж,
ТО комиссионные = объем продаж ставка комиссионных;
ИНАЧЕ, комиссионные = объем продаж ставка премиальных
30

31.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
Решение:
31

32.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
Получили следующие результаты:
32

33.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
Самостоятельная работа
1. Исследовать систему линейных уравнений и, если решение
существует найти решение по формулам Крамера решение
системы
A*x=b.
2
1
A
2,3
3
1,2
6,5
b
5,3
8,6
7
3 16 7,5
8 4
4
2 6.7 5
6
5
2. Выполнить умножение матриц
3
2.1
6 3 7
6
8
9 5
1
0
6 2
6
1 5 0
3 5
0 1
3 2
8 9
33

34.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
Рассчитать стоимость товара, имеющегося на складе
При расчете суммы пользуемся формулой:
ЕСЛИ ед. измерения тонны, то сумма = цена*кол-во*1000,
иначе, сумма=цена*кол-во
34

35.

Программное обеспечение и автоматизация профессиональной деятельности
Программное обеспечение
Табличный процессор MS Excel
Выполнить задания из файла Задания
35