Логические функции Excel
Автор работы: Пользователь скрыл имя, 21 Февраля 2013 в 21:07, лабораторная работа
Описание
Логические функции предназначены для построения сложных логических выражений. Например, проверка условия, что значение ячейки A1 больше 0 и меньше 1, записывается с помощью логической функции: =И(A1>0;A1<1).
Работа состоит из 1 файл
1 ЛАБОРАТОРНАЯ РАБОТА «ИСПОЛЬЗОВАНИЕ ЛОГИЧЕСКИХ
ФУНКЦИЙ В MICROSOFT EXCEL»
1.1 Теоретические сведения
Логические выражения предназначены для проверки выполнения условия
или для проверки нескольких условий. Если проверяемое условие верно,
логическое выражение принимает значение ИСТИНА, если проверяемое
условие неверно, логическое выражение принимает значение ЛОЖЬ.
Например, логическое выражение =2>1 всегда истинно, а логическое
выражение =(7=3) всегда ложно. Истинность логического выражения =A1<100
определяется значением ячейки A1.
Логические функции предназначены для построения сложных логических
выражений. Например, проверка условия, что значение ячейки A1 больше 0 и
меньше 1, записывается с помощью логической функции: =И(A1>0;A1<1).
Логические функции, используемые в записи логических выражений
перечислены в таблице 1.
Таблица 1 – Логические функции Excel
Функция
Описание
ЕСЛИ()
Функция ЕСЛИ() имеет три аргумента. Первый аргумент
функции должен быть логическим выражением. Функция
ЕСЛИ() проверяет это логическое выражение, и если он
истинно, возвращает значение, указанное в качестве второго
аргумента. Если логическое выражение ложно, функция
возвращает значение, указанное в качестве третьего аргумента.
И()
Функция И() возвращает значение ИСТИНА, если все ее
аргументы имеют значение ИСТИНА
ИЛИ()
Функция ИЛИ() возвращает значение ИСТИНА, если хотя бы
один ее аргумент имеет значение ИСТИНА
НЕ()
Функция НЕ() возвращает противоположное логическое
значение своего аргумента.
ИСТИНА()
Функция ИСТИНА() не имеет аргументов и всегда возвращает
логическое значение ИСТИНА.
ЛОЖЬ()
Функция ЛОЖЬ() не имеет аргументов и всегда возвращает
логическое значение ЛОЖЬ.
1.2 Построение графика функций
Нужно построить график функции
для t [0,3],
а=-0,5, шаг изменения значений t - 0,15.
На рабочем листе в ячейку С1 занесите значение параметра а, т.е. -0,5.
Значения аргумента t занесите с помощью арифметической прогрессии. В
ячейку А1 занесите значение 0 (нуля) – начальное значение t. Выберите
команду Правка – Заполнить – Прогрессия и в открывшемся диалоговом окне
Прогрессия в группе Расположение установите переключатель в положение По
столбцам, а в группе Тип – в положение Арифметическая. В поле Шаг введите
значение шага - 0,15, а в поле Предельное значение введите конечное значение
параметра t - 3.
Для вычисления значения данной функции y используется логическая
функция ЕСЛИ(условие;действие1; действие2), в которой в зависимости от
условия выполняется действие1 или действие2. В ячейку В1 занесите
следующую
расчетную
формулу:
=ЕСЛИ(А1<2;$С$1*А1^2;$С$1*А1^2*cos(2*А1)), а затем скопируйте ее для
всех значений t.
Для построения графика функции, выделите диапазон ячеек А1:В21 и
щелкните по кнопке мастера диаграмм
Выберите точечный тип диаграммы и щелкните на кнопку «Далее»
Убедитесь, что в окне задан нужный диапазон данных и щелкните по
кнопке «Далее»
Задайте параметры диаграммы такие, как заголовок и подписи данных и
щелкните по кнопке Готово. Диаграмма будет выведена на том же листе, что и
данные.
Чтобы переместить диаграмму, выделите ее и перетащите в новое место.
Чтобы изменить размеры диаграммы, выделите ее и перетащите
соответствующие маркеры изменения размера, расположенные по углам
диаграммы. Изменить вид диаграммы можно с помощью команд контекстного
меню
1.3 Варианты индивидуальных заданий
Протабулировать функцию в пределах [-10,10] с шагом 1 и построить
график функции.
2
,
2
cos
2
,
2
2
t
при
t
at
t
при
at
y
Таблица 2 – Варианты заданий
Вариант
Условие
1
2
3
4
5
1
x
3
)x
ln(
1
x
0
x
cos
0
x
3
3
x
)x
(y
1
x
x
1
x
2
x
2
x
5
x
1
)x
(y
3 2
2
2
1
x
x
1
x
3
e
3
x
4
x
)x
(y
3
x
1
1
x
x
1
x
3
x
1
e
3
x
3
x
)x
(y
3
x
2
x
4
0
x
3
5
2
x
3
x
2
2
x
)x
(y
1
x
x
1
x
3
x
1
e
3
x
3
x
)x
(y
Продолжение таблицы 3
6
7
8
9
10
1
x
)x
ln(
1
x
2
x
2
x
3
x
2
1
)x
(y
1
x
)x
ln(
1
x
0
x
cos
0
x
2
5
x
)x
(y
1
x
4
2
x
1
x
0
x
sin
3
0
x
8
x
)x
(y
1
x
x
1
x
2
x
2
x
5
x
1
)x
(y
3 2
2
2
2 ЛАБОРАТОРНАЯ РАБОТА «ИСПОЛЬЗОВАНИЕ
МАТЕМАТИЧЕСКИХ ФОРМУЛ И ФУНКЦИЙ В MICROSOFT EXCEL»
2.1 Теоретические сведения
Для вычисления значений математических выражений в задании формулы
можно использовать встроенные математические функции. Основные
математические функции
Excel, которые используются в
записи
математического выражения перечислены в таблице 4.
Таблица 3 – Математические функции Excel
Функция
Результат
EXP(аргумент)
Степень числа е
ABS(аргумент)
Абсолютная величина
LN(аргумент)
Натуральный логарифм
LOG(аргумент, основание логарифма)
Логарифм
SIN(аргумент)
Синус числа
COS(аргумент)
Косинус числа
TAN(аргумент)
Тангенс
ФАКТ(аргумент)
Факториал
ЦЕЛОЕ(аргумент)
Целая часть
ОСТАТ(арг.1, арг.2)
Остаток от деления
ОКРУГЛ(арг.1,арг.2)
Округление
КОРЕНЬ(аргумент)
Квадратный корень
Примеры записи математических выражений с применением функций
SIN
2
A+COSA
2
=SIN(A)^2+COS(A^2)
5
3
=5^(1/3)
ctg6
=1/TAN(6) либо =COS(6)/SIN(6)
[1,5*3]
=ЦЕЛОЕ(1,5*3)
В таблице 5 перечислены функции для работы с массивами данных.
Таблица 4 – Функции для работы с массивами
Функция
Описание
МОПРЕД()
Возвращает определитель матрицы
МОБР()
Возвращает обратную матрицу
МУМНОЖ()
Возвращает перемноженные матрицы
СУММКВ()
Возвращает сумму квадратов аргументов
СУММКВРАЗН()
Возвращает сумму квадратов разностей соответствующих
значений в двух массивах
СУММПРОИЗВ()
Возвращает сумму произведений соответствующих
значений в двух массивах
СУММРАЗНКВ()
Возвращает сумму разностей квадратов соответствующих
значений в двух массивах
СУММСУММКВ()
ТРАНСП()
Возвращает транспонированную матрицу.
Аргументом функции выступает адрес диапазона массива.
Перечисленные функции относятся к математической категории, кроме
функции ТРАНСП(), которая относится к категории Ссылки и массивы.
Нельзя редактировать, очищать или перемещать отдельные ячейки в
диапазоне массива, а также вставлять и удалять ячейки. Для изменения или
очистки массива нужно выделить весь массив и активизировать строку формул.
Во всех случаях при работе с матрицами перед вводом формулы надо
выделить область на рабочем листе, куда будет выведен результат вычислений.
Для ввода функции работы с матрицами нажимаются клавиши
CTRL+Shift+Enter.
2.2 Построение графиков
Создайте лист как показано на рисунке 3.
Выделив ячейки A4:A5, установите указатель мышки на маркер
заполнения и потяните вниз до ячейки A24 включительно. Ячейки A4:A24
будут заполнены числами от -5 до 5 с шагом 0,5.
Выделив ячейку B4, на панели инструментов нажмите кнопку "Вставка
функции" .
Рисунок 3 – Пример заполнения рабочего листа
На первом шаге Мастера функций при выборе функции выберите
математическую функцию TAN.
На втором шаге Мастера при выборе аргумента функции щелкните кнопку
"Свернуть" . Выделите на листе ячейку A4 в качестве аргумента функции и
нажмите кнопку "Развернуть" . Нажмите "ОК". В ячейке B4 отобразится
значениефункции,австрокеформулы –формула =TAN(A4).
Выделите ячейку B4 и щелкните мышкой в строке формулы, чтобы
продолжить ввод формулы.
Допечатайте в формуле знак деления / и на панели инструментов нажмите
кнопку"Вставкафункции"
.
На первом шаге Мастера функций выберите математическую функцию
LN.
На втором шаге Мастера введите в поле аргумента функции A4^2+1 и
нажмите "ОК". В ячейке B4 отобразится значение функции, а в строке формулы
– формула =TAN(A4)/LN(A4^2+1) (рис.4).
Рисунок 4 – Пример ввода функции
Выделив ячейку B4, установите указатель мышки на маркер заполнения и
потяните вниз до ячейки B24 включительно. Ячейки B5:B24 будут заполнены
формулами, в которых ссылка на ячейку A4 будет соответственно заменена на
ссылки A5, .., A24.
В ячейке B14 будет выведено сообщение об ошибке #ДЕЛ/0!, которое
означает, что в формуле, содержащейся в ячейке, произошло деление на 0.
Удалите содержимое ячейки B14.
Выделив ячейки A4:B24, на панели инструментов щелкните кнопку
"Мастер диаграмм" .
В окне Мастера диаграмм выберите точечную диаграмму со значениями,
соединенными сглаживающими линиями без точек. Нажмите "Готово" (рис.5).
Рисунок 5 – Пример создания диаграммы
2.3 Решение уравнений
Найти все корни уравнения х
3
-0,01х
2
-0,7044х+0,139104=0.
У полинома третьей степени имеется не более трех вещественных корней.
создайте новый рабочий лист и присвойте ему имя Уравнение.
Протабулируйте полином на отрезке [-1;1] с шагом 0,2. Результат
табуляции приведен в таблице 6, где в ячейку А3 введена формула: =A2+0,2
В ячейку В2 введена формула: =A2^3-0,01*A2^2-0,7044*A2+0,139104
Таблица 6 – Результат табуляции
х
у
-1
-0,1665
-0,8
0,184224
-0,6
0,342144
-0,4
0,355264
-0,2
0,271584
0
0,139104
0,2
0,005824
0,4
-0,08026
0,6
-0,07114
0,8
0,081184
1
0,424704
Из рисунка видно, что полином меняет знак на интервалах [-1, -0,8], [0,2,
0,4] и [0,6, 0,8]. Это означает, что на каждом из них имеется корень данного
полинома.
Задайте относительную погрешность и предельное число итераций,
равными 0,00001 и 1000, соответственно, в диалоговом окне Параметры
(Сервис - Параметры)
В качестве начальных значений приближений к корням можно взять
любые точки из отрезков локализации корней. Например: -0.9, 0.3, 0.7. Введите
эти значения в диапазон ячеек C2:C4
В ячейку D2 введите формулу для вычисления уравнения, в качестве
значения – адрес ячейки С2. С помощью маркера автозаполнения протащите
введенную формулу на диапазон D2:D4
Выделив ячейку D2, дайте команду Сервис – Подбор параметра. С
помощью подбора параметра можно вести поиск такого значения параметра,
при котором формула дает нужный результат.
В поле Установить в ячейке укажите ссылку на ячейку, содержащую
формулу, т.е. адрес D2
В поле Значение задайте значение, которое должно быть достигнуто путем
подбора. В данном случае это значение 0.
В поле Изменяя значение введите ссылку на ячейку, которая содержит
изменяемый параметр, т.е. адрес ячейки С2.
Щелкните на кнопке ОК и посмотрите на результат подбора, отражаемый
в диалоговом окне Результат подбора параметра. В данном случае оно равно –
0.919999. Щелкните на кнопке ОК, чтобы сохранить полученные результаты.
Аналогично в ячейках С3 и С4 найдите два оставшихся корня. Они равны
0.20999 и 0.71999. Сохраните изменения.
2.4 Решение системы уравнений
Запустите Microsoft Excel и введите данные, как показано на рисунке 6.
Рисунок 6 - Пример заполнения таблицы
3.
1
24
.9
15
.4
3.
5
28
.1
9.
2
31
.1
1.
7
27
.4
14
.0
45
.3
56
.2
1.
2
4.
3
23
.1
89
.5
56
.0
5.
1
2
4
3
2
1
4
3
2
1
4
3
2
1
4
3
2
1
x
x
x
x
x
x
x
x
x
x
x
x
x
x
x
x
y
Чтобы найти определитель матрицы, необходимо выделить ячейку D7 и
выбрать функцию МОПРЕД(). В диалоговом окне укажите адрес исходной
матрицы (A2:D5) и нажмите Ok (рис.7).
Рисунок 7 –Пример ввода функции
Чтобы найти обратную матрицу, необходимо выделить диапазон ячеек
(A10:D13), а затем вызвать функцию МОБР(). В диалоговом окне ввести адрес
исходной матрицы (A1:D4). Так как результат вычисления функции
помещается в диапазон ячеек, то ввод закончить нажатием сочетания клавиш
Ctrl+Shift+Enter. В строке формул выражение заключено в фигурные скобки,
указывая тем самым, что выражение является функцией массива (рис.8).
Рисунок 8 – Пример ввода функции
Видно, что для вычисления корней системы уравнений необходимо
перемножить матрицу обратную матрице коэффициентов на вектор В.
Чтобы найти корни системы, выделим диапазон ячеек (G2:G5) и вызовем
функцию МУМНОЖ(). В диалоговом окне функции в поле Массив1 введем
адрес обратной матрицы (A10:D13), а в поле Массив2 – адрес вектора В (F2:F5)
(рис.9) . Ввод данных закончим комбинацией клавиш Ctrl+Shift+Enter.
Рисунок 9 – Пример ввода функции
2.5 Варианты индивидуальных заданий
1. Постройте таблицу значений и график функции y(x) на интервале [-2, 2]
с шагом 0,1. Математическую запись формулы y(x) создайте в редакторе
формул Microsoft Equation.
Таблица 7 – Варианты заданий
Вариант
Функция y(x)
1
1
cos
)
(
2
x
x
x
y
2
x
arctg
x
y
2
1
)
(
3
x
x
y
cos
ln
)
(
4
)
(
)
(
2
x
tg
e
x
y
x
Продолжение таблицы 7
5
1
)
(
cos
x
e
x
y
6
1
sin
)
(
x
x
x
y
7
1
)
(
3
tgx
x
x
y
8
x
x
x
y
3
cos
)
(
9
x
x
x
y
sin
*
1
cos
)
(
10
3
2
)
(
tgx
x
y
2. Вычислите и проверьте правильность математических выражений
Таблица 8 – Варианты заданий
Вариант
Выражение
Результат
1
-0,52
2
0,86
3
-0,40
4
1370,63
5
-10
6
80942,81
7
2,39
8
-1
9
4,13
10
24,07
3. Найти все корни уравнения
Таблица 9 – Варианты заданий
Вариант
Условие
1
0
791136
,0
4355
,1
92
,2
2
3
x
x
x
2
0
395006
,4
3251
,1
56
,2
2
3
x
x
x
3
0
766336
,
14
6064
,5
84
,2
2
3
x
x
x
4
0
380384
,7
4724
,5
41
,1
2
3
x
x
x
5
0
043911
,0
4317
,0
85
,0
2
3
x
x
x
6
0
191906
,0
4775
,1
12
,0
2
3
x
x
x
7
0
016995
,0
2513
,0
77
,0
2
3
x
x
x
8
0
069951
,0
2143
,0
71
,0
2
3
x
x
x
9
0
037638
,0
3999
,0
88
,0
2
3
x
x
x
10
0
146718
,0
8269
,0
78
,0
2
3
x
x
x
4. Решить систему уравнений
Таблица 10 – Варианты заданий
1
-0,45Х1+7,11Х2-9,94Х3+1,18Х4=-36
8,45Х1+0,23Х2+4,68Х3+0,91Х4=-64,3
2,
29
521
51
3
107
57
,0
sin
87
,0
2,
5
7
5,
2
7
25
*
25
,2
4
5
5
2,
3
cos
7,
2
sin
4,
1
12
sin
*
7,
0
cos
55
5
ln
*
16
65
*
51
3
8
*
1
37
sin
*
25
cos
25
cos
25
sin
079
,1
*
35
5,
3
*
6
6,
0
*
3
5,
3
*
2
3
2
1,
7
3
3
3
3
1
3
e
ctg
tg
ctg
tg
-4,41Х1+6,51Х2-7,89Х3+28,63Х4=0,2
-1,26Х1+9,37Х2-1,89Х3+4,49Х4=35,6
2
8,54Х1+4,37Х2+0,92Х3-1,71Х4=96,1
2,21Х1-3,49Х2+1,72Х3+9,24Х4=91
2,96Х1+6,21Х2+3,18Х3-0,11Х4=87,2
-2,43Х1+1,96Х2+6,53Х3-1,51Х4=78,2
3
-5,38Х1-9,31Х2-1,48Х3-22,39Х4=-89,8
1,33Х1+7,35Х2-1,31Х3-3,96Х4=24,8
14,3Х1-9,22Х2+1,26Х3-0,48Х4=15,64
45,89Х1-23,48Х2-5,16Х3+3,48Х4=58,78
4
96,15Х1-78,56Х2+1,28Х3-0,48Х4=2,36
-23,78Х1+12,85Х2-36,7Х3+8,45Х4=56
45,56Х1-59,4Х2+12,96Х3-23,61Х4=-6,48
37,7Х1+0,485Х2+1,59Х3-7,45Х4=32,75
5
31,5Х1-7,26Х2+3,45Х3-0,46Х4=25,7
26,4Х1+33,4Х2-56,71Х3-2,45Х4=34
2,48Х1-5,16Х2+0,67Х3+6,78Х4=38,4
31,8Х1-31,7Х2+5,16Х3+2,47Х4=23,4
6
98,5Х1-45,1Х2-2,49Х3+1,78Х4=66
13,4Х1-4,16Х2+7,19Х3+1,56Х4=46,7
49,2Х1+13,8Х2-49,1Х3+0,45Х4=91
-33,4Х1+12,8Х2-19,3Х3-86,4Х4=-56,4
7
7,49Х1+48,6Х2-33,78Х3-0,48Х4=-12,6
78,5Х1+0,78Х2-12,79Х3+7,29Х4=26,48
4,26Х1-0,26Х2-5,49Х3+16,48Х4=5,49
7,16Х1+8,67Х2-15,46Х3-4,16Х4=25,46
8
5,26Х1-42,7Х2+16,4Х3+3,48Х4=6,78
-4,26Х1+56,78Х2-46,7Х3-12,8Х4=-5,5
26,8Х1+8,49Х2-5,49Х3-3,78Х4=6,4
6,15Х1-5,26Х2+1,89Х3+8,4Х4=12,7
9
45,49Х1-26,78Х2-5,29Х3+0,458Х4=3,49
-23,7Х1+5,48Х2-3,78Х3+21,4Х4=9,45
-0,48Х1+45,29Х2+6,17Х3-54,36Х4=-23,78
26,4Х1+54,48Х2-7,59Х3-42,23Х4=13,46
10
31,5Х1-46,8Х2+13,7Х3-76Х4=-28,4
-42,7Х1+26,7Х2+34,1Х3-6,1Х4=7,22
9,46Х1+5,16Х2-4,11Х3-7,24Х4=3,48
-5,16Х1+6,18Х2+2,76Х3-26Х4=-1,45
3 ЛАБОРАТОРНАЯ РАБОТА «ИСПОЛЬЗОВАНИЕ ФУНКЦИЙ ДАТЫ И
ССЫЛОК»
3.1 Теоретические сведения
В Microsoft Excel даты и время суток интерпретируются как числа,
поэтому их можно складывать и вычитать. Отсчет дат ведется с 1 января 1900
года, этой дате соответствует число 1. Разность двух дат представляет собой
количество дней между этими датами.
Встроенные функции, перечисленные в таблице 11, для работы с датами и
временем позволяют использовать значения даты и времени в формулах. Так,
например, с помощью этих функций можно получить текущую дату, вычислить
число рабочих дней между двумя датами, определить день недели,
приходящийся на конкретную дату.
Таблица 11-Функции Excel для работы с датами
Функция
Описание
ГОД()
Преобразует дату в числовом формате в год
ДАТА()
Возвращает заданную дату в числовом формате Microsoft
Excel
ДАТАЗНАЧ()
Преобразует дату из текстового формата в числовой
ДАТАМЕС()
Возвращает дату в числовом формате, отстоящую на
заданное число месяцев вперед или назад от начальной
даты
ДЕНЬ()
Преобразует дату в числовом формате в день месяца
ДЕНЬНЕД()
Преобразует дату в числовом формате в день недели
ДОЛЯГОДА()
Возвращает долю года, которую составляет количество
дней между двумя датами
КОНМЕСЯЦА() Возвращает дату в числовом формате для последнего дня
месяца, отстоящего вперед или назад на заданное число
месяцев
МЕСЯЦ()
Преобразует дату в числовом формате в месяц
РАБДЕНЬ()
Возвращает дату в числовом формате, отстоящую вперед
или назад на заданное количество рабочих дней
РАЗНДАТ()
Вычисляет число дней, месяцев и лет между двумя датами
СЕГОДНЯ()
Возвращает числовой формат текущей даты
ЧИСТРАБДНИ() Возвращает количество рабочих дней между двумя датами
Функции для работы со ссылками и массивами, перечисленные в таблице
12, применяются, если необходимо осуществить поиск значения в заданном
массиве значений или если необходимо найти ссылку к заданной ячейке.
Таблица 12 – Функции для работы с массивами данных
АДРЕС()
Возвращает ссылку на отдельную ячейку в рабочем листе в
виде текста
ВПР()
Ищет значение в первом столбце массива и возвращает
значение из ячейки в найденной строке и указанном столбце
ВЫБОР()
Выбирает значение из списка значений по индексу
ГПР()
Ищет значение в первой строке массива и возвращает
значение из ячейки в найденном столбце и указанной строке
ИНДЕКС()
Использует индекс для выбора значения из ссылки или
массива
ПОИСКПОЗ()
Ищет значения в ссылке или массиве
ПРОСМОТР()
Ищет значения в векторе или массиве
СТОЛБЕЦ()
Возвращает номер столбца, на который указывает ссылка
СТРОКА()
Возвращает номер строки, определяемой ссылкой
ЧИСЛСТОЛБ() Возвращает количество столбцов в массиве или ссылке
ЧСТРОК()
Возвращает количество строк в ссылке
3.2 Отчетная ведомость по расчету просроченных платежей
На листе создайте таблицу, как показано на рисунке 16.
Рисунок 16 – Пример заполнения листа
Введите формулу для расчета просрочки: =ЕСЛИ(D2=0;$F$2-C2;””),
которая протаскивается на диапазон Е3:Е11.
В ячейки G8, G9, G10 введите следующие формулы:
=СУММ((Е2:Е11>0)*(Е2:Е11<=29)*(В2:В11))
=СУММ((Е2:Е11>30)*(Е2:Е11<=39)*(В2:В11))
=СУММ((Е2:Е11>40)*(В2:В11))
вычисляющие суммарные стоимости просроченных оплат сроком до 29
дней, от 30 до 39 дней и свыше 40 дней
В ячейки G2, G3, G4 введите формулы:
=СУММ((Е2:Е11>0)*(Е2:Е11<=29))
=СУММ((Е2:Е11>30)*(Е2:Е11<=40))
=СЧЕТЕСЛИ(Е2:Е11; “>=40”)
вычисляющие количество просроченных оплат сроком до 20 дней, от 30 до
39 дней и свыше 40 дней
3.3 Использование функций для работы с датами и массивами
Запустите Microsoft Excel. Убедитесь, что установлена надстройка "Пакет
анализа".
Введите данные в ячейки и отформатируйте их, как показано на рисунке
17.
Рисунок 17 – Пример заполнения рабочего листа
В ячейке C16 установите процентный формат с 2-мя десятичными знаками
после запятой.
В ячейке C18 установите формат даты по шаблону ДД.ММ.ГГ.
Выделите ячейку С15 и на панели инструментов щелкните кнопку
"Вставка функции" .
На первом шаге Мастера функций выберите функцию ВПР() из категории
"Ссылки и массивы".
На втором шаге Мастера в поле "Искомое_значение" выберите ячейку C9.
В
поле
"Табл_массив"
выберите
диапазон
A4:B6.
В
поле
"Номер_индекса_столбца" введите 2. Нажмите "ОК".
В ячейке C15 отобразится название депозита, а в строке формулы –
формула =ВПР(C9;A4:B6;2) (рис.18).
Рисунок 18 – Пример использования функции ВПР()
Выделите ячейку С16 и на панели инструментов щелкните кнопку
"Вставка функции" .
На первом шаге Мастера функций выберите функцию ГПР() из категории
"Ссылки и массивы".
На втором шаге Мастера в поле "Искомое_значение" выберите ячейку C11.
В
поле
"Табл_массив"
выберите
диапазон
D3:G6.
В
поле
"Номер_индекса_строки" введите C9+1 . Нажмите "ОК".
В ячейке C16 отобразится ставка депозита, а в строке формулы – формула
=ГПР(C11;D3:G6;C9+1) (рис. 19).
Рисунок 19 – Пример использования функции ГПР()
В ячейку С17 введите формулу = C10*C16*C11/12 для расчета суммы
начисленных процентов.
Выделите ячейку С18 и на панели инструментов щелкните кнопку
"Вставка функции" .
На первом шаге Мастера функций выберите функцию ДАТАМЕС() из
категории "Дата и время".
На втором шаге Мастера в поле "Нач_дата" выберите ячейку C12. В поле
"Мес" выберите ячейку C11. Нажмите "ОК".
В ячейке C18 отобразится дата закрытия, а в строке формулы – формула
=ДАТАМЕС(C12;C11) (рис.20).
Рисунок 20 – Пример использования функций Дата
3.4 Варианты индивидуальных заданий
Вариант 1. Сформируйте реестр товаров вручную, как показано на рисунке
21. Журнал продаж сформируйте, придерживаясь следующих правил: код
товара вводится вручную; наименование и цена товара вычисляются
автоматически при вводе кода; гарантийный срок и дата продажи вводятся
вручную; дата истечения гарантии вычисляется автоматически при вводе срока
гарантии и даты продажи; скидка 1% предоставляется, если продажа состоялась
с 1-го по 5-е число месяца, иначе скидка равняется 0 (указание: использовать
функцию ДЕНЬ()); итого к оплате вычисляется как цена минус скидка.
Рисунок 21 – Пример заполнения таблицы
Вариант 2. Сформируйте виды заявок в КСК вручную, как показано на
рисунке 22. Журнал заявок сформируйте, придерживаясь следующих правил:
код заявки вводится вручную; наименование вычисляется автоматически при
вводе кода; адрес, дата заявки, дата и время исполнения вводятся вручную;
бонус начисляется (равен 1), если между датой заявки и датой исполнения
прошло не более 1 дня, иначе бонус не начисляется (равен 0); дата повторной
проверки вычисляется автоматически (проверка через 7 дней после
исполнения).
Рисунок 22 – Пример заполнения таблицы
Вариант 3. Сформируйте реестр услуг вручную, как показано на рисунке
23. Журнал учета заказов сформируйте, придерживаясь следующих правил: код
услуги вводится вручную; наименование вычисляется автоматически при вводе
кода; количество вводится вручную; сумма вычисляется автоматически
(количество умножается на цену услуги); дата заказа вводится вручную; дата
исполнения вычисляется, исходя из нормы 10 страниц в день (указание:
использовать функцию целое() при делении количества страниц на 10); скидка
10% предоставляется, если количество страниц более 50, иначе скидка
равняется 0; итого вычисляется как сумма заказа минус скидка.
Рисунок 23 - Пример заполнения таблицы
Вариант 4. Сформируйте расценки за прокат вручную, как показано на
рисунке 24. Журнал проката спортинвентаря сформируйте, придерживаясь
следующих правил: код спортинвентаря вводится вручную; наименование и
цена вычисляются автоматически при вводе кода; дата выдачи, количество
дней и дата возврата вводятся вручную; пеня вычисляется как 10% от цены
проката в день, если разница между датой возврата и датой выдачи превышает
срок проката, иначе пеня равняется 0; итого к оплате вычисляется как цена,
умноженная на срок проката, плюс пеня.
Рисунок 24 - Пример заполнения таблицы
Вариант 5. Сформируйте темы курсовых работ вручную, как показано на
рисунке 25. Журнал регистрации курсовых работ сформируйте, придерживаясь
следующих правил: код работы вводится вручную; наименование и срок
работы вычисляются автоматически при вводе кода; фио студента и дата
выдачи работы вводятся вручную; планируемая дата сдачи вычисляется
автоматически при вводе даты выдачи и срока работы; фактическая дата сдачи
водится вручную; если работа сдана раньше срока, ставится отметка
"досрочно", если не позднее, чем через 2 дня по плану, ставится отметка "в
срок", иначе ставится отметка "с опозданием".
Рисунок 25 - Пример заполнения таблицы
Вариант 6. Сформируйте реестр расценок за пошив вручную, как показано
на рисунке 26. Журнал заказов сформируйте, придерживаясь следующих
правил: код заказа вводится вручную; наименование заказа и цена вычисляются
автоматически при вводе кода; дата заказа и срок выполнения вводятся
вручную; дата выполнения вычисляется автоматически при вводе срока и даты
заказа; наценка за срочность 10% начисляется, если срок исполнения не
превышает 3 дней, иначе наценка равняется 0; итого вычисляется как цена
плюс наценка.
Рисунок 26 - Пример заполнения таблицы
Вариант 7. Сформируйте реестр товаров вручную, как показано на рисунке
27. Журнал продаж сформируйте, придерживаясь следующих правил: код
товара вводится вручную; наименование, цена товара и гарантийный срок
вычисляются автоматически при вводе кода; дата продажи вводится вручную;
дата истечения гарантии вычисляется автоматически при вводе даты продажи;
новогодняя скидка 5% предоставляется, если продажа состоялась в декабре
месяце, иначе скидка равняется 0 (указание: использовать функцию месяц());
итого к оплате вычисляется как цена минус скидка.
Рисунок 27 - Пример заполнения таблицы
Вариант 8. Сформируйте список туров вручную, как показано на рисунке
28. Журнал заказов сформируйте, придерживаясь следующих правил: код тура
вводится вручную; наименование, цена и срок тура вычисляются
автоматически при вводе кода; дата отправки вводится вручную; дата возврата
вычисляется автоматически при вводе даты отправки; количество людей
вводится вручную; стоимость тура вычисляется путем умножения цены на
количество людей. если число людей 5 и менее, стоимость тура оплачивается
на 100 %, иначе только на 80 %.
Рисунок 28 - Пример заполнения таблицы
Вариант 9. Сформируйте наименования рейсов вручную, как показано на
рисунке 29. Расписание сформируйте, придерживаясь следующих правил: код
рейса вводится вручную; наименование, время в пути вычисляются
автоматически при вводе кода; дата и время отправления вводятся вручную в
формате дд:мм:гг чч:мм; дата и время прибытия вычисляются автоматически
при вводе даты и времени отправления (указание: к дате отправления
прибавить число часов/24 и число минут/(24*60)); если число часов в пути
более 12, поезд имеет купейные вагоны, иначе нет.
Рисунок 29 - Пример заполнения таблицы
Вариант 10. Создайте журнал занятий, как показано на рисунке 30, и
заполните, придерживаясь следующих правил: код курса вводится вручную;
наименование выбирается автоматически из перечня курсов при вводе кода;
фио, дата начала обучения и число занятий в неделю (1, 2, 3 или 6) вводится
вручную; число недель обучения вычисляется автоматически (общее число
занятий по данному курсу делится на число занятий в неделю); примерная дата
окончания занятий вычисляется путем прибавления к дате начала занятий
числа недель, умноженного на 7; бонус (3 занятия дополнительно)
предоставляется, если число занятий в неделю равно 6, иначе бонус не
предоставляется.
Рисунок 30 - Пример заполнения таблицы
4 ЛАБОРАТОРНАЯ РАБОТА «СРЕДСТВА ФИНАНСОВОГО АНАЛИЗА
В EXCEL»
4.1 Теоретические сведения
Excel предоставляет большой спектр функций и средств для финансового
анализа. К этим средствам относятся:
Финансовые функции предназначенные для вычисления базовых величин,
необходимых при проведении сложных финансовых расчетов. Полный
перечень доступных финансовых функций можно просмотреть в Мастере
функций, который вызывается командой Вставка-Функция или нажатием
одноименной кнопки на панели инструментов Стандартная.
Подбор параметра предназначенный для решения «обратных» задач, т.е.
выполнять исследования области допустимых значений аргументов, а также
подбирать значения аргументов под заданное значение функции. Вызывается
командой Сервис-Подбор параметра.
Диспетчер сценариев предназначен для вариантных финансовых расчетов,
основанных на задании различных значений аргументов функции. Вызывается
командой Сервис – Сценарии
Таблица подстановки предназначена для многовариантности финансовых
расчетов с целью выбора наиболее оптимального
Поиск
решения
предназначен
для решения задач
линейного
программирования. Эта надстройка полезна в тех случаях, когда для
оптимального решения проблемы приходится иметь дело со многими
переменными.
Диаграммы используется для обозначения всех видов графического
представления числовых данных.
4.2 Финансовые функции
Рассчитать схему погашения займа в 70000 у.е., выданного сроком на 3
года под 17% годовых.
На рабочем листе1 книги “Анализ ” создайте таблицу, представленную на
рисунке 31.
Рисунок 31 – Пример заполнения листа 1
Для вычисления общей суммы платежа используется финансовая функция
ПЛТ. Синтаксис: ПЛТ(ставка; кпер; пс; бс; тип)
Ставка – процентная ставка за период (норма). В задаче она равна 17%
Кпер – общее число периодических выплат (срок). В задаче он равен 3
пс – общая сумма, которую составят будущие платежи. В задаче она равна
70000
Бс – будущая стоимость, если опущен, то он полагается равным 0. В задаче
она равна 0
Тип – число 0 или 1, обозначающее, когда должна производиться выплата:
в конце периода или в начале. В задаче он равен 0.
Для вычисления общей суммы платежа заноситься формула ПЛТ(17%;3;-
70000)
Для вычисления платежей по процентам используется финансовая
функция ПРПЛТ
Синтаксис: ПРПЛТ(ставка;период; кпер; пс; бс; тип)
Ставка – процентная ставка за период (норма). В задаче она равна 17%
Период – период, за который требуется найти прибыль. В задаче для
первого года - 1, для второго года – 2, для третьего - 3
Кпер – общее число периодических выплат (срок). В задаче он равен 3
Пс – общая сумма, которую составят будущие платежи. В задаче она равна
70000
Бс – будущая стоимость, если опущен, то он полагается равным 0. В задаче
она равна 0
Тип – число 0 или 1, обозначающее, когда должна производиться выплата:
в конце периода или в начале. В задаче он равен 0.
Для вычисления платежа по процентам за первый год заноситься формула
ПРПЛТ (17%,1, 3,-70000), платежа по процентам за второй год заноситься
формула ПРПЛТ (17%;2; 3;-70000), платежа по процентам за третий год
заноситься формула ПРПЛТ (17%;3; 3;-70000).
Для вычисления суммы основного платежа используется финансовая
функция ОСПЛТ. Синтаксис: ОСПЛТ (ставка;период; кпер;пс; бз; тип)
Ставка – процентная ставка за период (норма). В задаче она равна 17%
Период – период, за который требуется найти прибыль. В задаче для
первого года - 1, для второго года – 2, для третьего - 3
Кпер – общее число периодических выплат (срок). В задаче он равен 3
Пс– общая сумма, которую составят будущие платежи. В задаче она равна
70000
Бз – будущая стоимость, если опущен, то он полагается равным 0. В задаче
она равна 0
Тип – число 0 или 1, обозначающее, когда должна производиться выплата:
в конце периода или в начале. В задаче он равен 0.
Для вычисления основного платежа за первый год заноситься формула
ОСПЛТ (17%;1; 3;-70000), основного платежа за второй год заноситься
формула ОСПЛТ (17%;2; 3;-70000), основного платежа за третий год
заноситься формула ОСПЛТ (17%;3; 3;-70000).
Столбец СУММА ЗАЙМА НА КОНЕЦ ГОДА вычисляется по формуле:
СУММА ЗАЙМА НА НАЧАЛО ГОДА - СУММА ОСНОВНОГО ПЛАТЕЖА
ПО ЗАЙМУ.
Значения столбца СУММА ЗАЙМА НА НАЧАЛО (следующего) ГОДА
равняются значениям столбца СУММА ЗАЙМА НА КОНЕЦ (предыдущего)
ГОДА.
Для вычисления строки Итого используется кнопка Автосумма
стандартной панели инструментов.
Результат вычислений показан на рисунке 32.
Рисунок 32 – Результат вычислений
4.3 Таблица подстановки
Требуется определить, какие ежемесячные выплаты необходимо вносить
по ссуде размером 200000 у.е., выданной на 3 года, при разных процентных
ставках.
На листе2 рабочей книги «Анализ» создайте таблиц, показанную на
рисунке 33.
.
Рисунок 33 – Пример заполнения листа 2
В ячейку С3 введите формулу для расчета общей суммы платежа.
Аргумент ставка занесите в виде адреса ячейки.
Выделите диапазон ячеек, содержащий исходные значения процентных
ставок и формулу для расчета (В3:С9).
В меню Данные выберите команду Таблица подстановки, в появившемся
диалоговом окне (используется для задания рабочей ячейки, на которую будет
ссылаться формула расчета) в поле Подставлять значения по строкам в введите
адрес ячейки ставки равной 8.5%
При нажатии кнопки ОК заполнится столбец результатов.
4.4 Диспетчер сценариев
Просят дать в долг 10000 руб. и обещают возвращать по 2000 руб. в
течение 6 лет. Будет ли выгодна эта сделка при годовой ставке 7%?
На листе3 книги «Анализ» создайте таблицу, показанную на рисунке 34.
Рисунок 34 – Пример заполнения листа 3
Для вычисления чистого текущего объема вклада вводится формула:
ПЗ/ПС(В4;В2;-В3).
В ячейку В6 введите формулу для вывода одного из значений «Выгодно
дать деньги в долг», «Варианты равносильны», «Выгоднее деньги положить
под проценты» в зависимости от полученного значения в ячейке В5.
Часто бывает удобно проанализировать ситуацию для нескольких
возможных вариантов параметров. Команда Сервис – Сценарии предоставляет
такую возможность с одновременным автоматизированным составлением
отчета. Рассмотрим способ применения команды для следующих трех
комбинаций срока и суммы ежегодно возвращаемых денег: 6, 2000; 12, 1500 и
7, 1500. В диалоговом окне Диспетчер сценариев для создания первого
сценария нажмите кнопку Добавить. В поле Название сценария введите,
например пз1, а в поле Изменяемые ячейки – ссылку на ячейки В2 и В3, в
которые вводятся значения параметров задачи.
После нажатия кнопки ОК в поля диалогового окна введите значения
параметров для первого сценария :6, 2000
С помощью кнопки Добавить последовательно создайте нужное
количество сценариев. С помощью кнопки Вывести выводятся результаты.
Нажатие кнопки Отчет открывает диалоговое окно в котором выбирается
нужный тип и дают ссылки на ячейки, где вычисляются значения
результирующих функций
4.5 Поиск решения
Специалист по маркетингу разрабатывает план рекламной компании,
призванной ознакомить с продукцией компании как можно большее количество
людей и в то же время не выйти за рамки бюджета. Размещать рекламу
предполагается на телевидении и в журнале. В ходе разработки нужно решить,
в какой пропорции следует распределить рекламу между этими средствами
массовой информации для достижения максимальной выгоды от ее
использования.
Создайте лист4, показанный на рисунке 35.
Рисунок 35 – Пример заполнения
Выделите ячейки с А2 по В16. Выбрав Вставка – Имя – Создать,
откройте диалоговое окно Создать имя. Здесь уже должен быть установлен
переключатель В столбце слева. Щелкнув на ОК, создайте имена по названию
противоположенных ячеек.
В
ячейку
В12
напечатайте
=ТВАудитория*ТВРекл+ЖурналАудитория*ЖурналРекл и нажмите ввод
В ячейку В14 напечатайте =ТВРекл*ТВЦена
В ячейку В15 напечатайте =ЖурналРекл*ЖурналЦена
В ячейку В16 напечатайте =ТВРеклБюджет+ЖурналРеклБюджет
Выделите ячейку В9 и выберите команду Сервис – Поиск решения (если
данной команды в меню Сервис нет, то загрузите надстройку выполнив
команду Сервис – Надстройки). Появится диалоговое окно Поиск решения.
В текстовом поле Установить целевую функцию напечатайте
ОбщаяАудитория. Установите селектор Равной максимальному значению. В
текстовом поле Изменяя ячейки напечатайте ТВРекл, ЖурналРекл.
Щелкните на кнопке Добавить. Появится диалоговое окно Добавление
ограничения.
В текстовом поле Ссылка на ячейку напечатайте ЖурналРекл. Если в этом
текстовом поле уже окажется какой-то текст, то выделите этот текст и
напечатайте поверх его.
Откройте расположенный между двумя текстовыми полями выпадающий
список и выберите в нем знак операций Больше или равно (>=)
В текстовом поле Ограничение напечатайте МинЖурналРекл
Щелкнув на кнопку ОК, вернитесь в диалоговое окно Поиск решения и
добавьте ограничения: ТВРекл >= МинТВРекл, ОбщийБюджет <= РеклБюджет
Вернитесь в диалоговое окно Поиск решения. Щелкните на кнопке
Выполнить. Появится диалоговое окно Результаты поиска решения, в котором
можно либо Сохранить найденное решение, либо Восстановить исходные
значения.
Примите значения установленные по умолчанию, и щелкните на кнопке
ОК. найденное решение появится на рабочем листе.
Значения в ячейках В10 и В11 показывают, что наилучший результат
достигается при размещении 46 телевизионных и 10 журнальных рекламных
объявлений.
4.6 Задания к лабораторной работе
1. Используя аппарат подбора параметра решите следующие задачи:
Под какие проценты нужно разместить сумму размером в 5 млн. руб.,
чтобы через три года на счете лежало 7 млн. руб. Используется функция БЗ
(БС). Ответ 12%
По вкладу размером 2 млн. руб. начисляются 10% годовых. Через сколько
лет на сберегательном счете будет 3,5 млн. руб., если проценты начисляются
ежемесячно. Используется функция БЗ (БС). Ответ 5,5 лет
Ожидается, что будущая стоимость инвестиции размером 1500 тыс. руб. к
концу четвертого года составит 3 млн. руб. При этом за первый год доходность
составит 15%, за второй – 17%, за четвертый – 23%. Рассчитайте доходность
инвестиции
за
третий
год.
Используется
формула
Буд.стоим.инвест=инвест*(1+ставка1)*(1+ставка2)*…*(1+ставка n). Ответ 21%
По облигации, выпущенной на 6 лет, предусмотрен следующий порядок
начисления процентов: в первый год – 10%, на последующие два – 20%, в
оставшиеся три года – 25%. Рассчитайте номинал облигации, если известно, что
ее будущая стоимость составила 1546880 руб. Используется формула
Буд.стоим.инвест=инвест*(1+ставка1)*(1+ставка2)*…*(1+ставка n).Ответ 500
тыс.
2. Вычислите
В конце года капиталовложения по проекту составят около 1280 млн. руб.
Ожидается, что за последние три года проект принесет следующие доходы 420,
490, 550, 590 млн. руб. Рассчитайте чистую текущую стоимость проекта для
различных норм дисконтирования и объемов капиталовложений. Построить
диаграмму. Используется функция НПЗ
Рассчитайте таблицу погашения займа размером 50 млн. руб., выданного
на один год под 15% годовых, если проценты начисляются ежемесячно.
Вывести диаграмму. В таблице должны быть выведены: номер месяца, сумма
займа на нач. месяца, общая сумма платежа, платежи по процентам,
основные платежи, сумма займа на конец месяца
Информация о работе Логические функции Excel