Специальные математические функции
| Функция | Назначение | Аргумент | Пример |
| ФАКТР (FACT) | Вычисляет факториал целого положительного числа | Число или ячейка | = ФАКТР(5) равен 120 = ФАКТ(50) равен 3.04141 Е+64 |
| ЧИСЛКОМБ (COMBIN) | Вычисляет количество сочетаний из п различных элементов по т2 | Число, выбранное число | = ЧИСЛКОМБ(5;2) равно 10 С52 = 5!/[2!(5 - 2)!] = = (5∙4∙3∙2∙1) : : [2∙1(3∙2∙1)] = 20/2 = 10 |
| СУММКВРАЗН (SUMXMY2) | Вычисляет сумму квадратов разностей Σ(x-y)2 | Массив X, массив Y | Вычислим в ячейке С1 сумму квадратов разностей чисел, расположенных в диапазоне А1:В7 таблицы, представленной на рис. 8.8. В результате получим 79 |
| СУММРАЗНКВ (SUMX2MY2) | Вычисляет сумму разностей квадратов Σ(x2-y2) | То же | = СУММРАЗНКВ({2,3,9, 1,8,7,5}, {6,5,11,7,5,4,4}) равно –55 |
| СУММРАЗНКВ (SUMX2MY2) | Вычисляет сумму разностей квадратов Σ(x2-y2) | То же | = СУММРАЗНКВ({2,3,9, 1,8,7,5}, {6,5,11,7,5,4,4}) равно –55 |
| СУММПРОИЗВ (SUMPRODUCT) | Вычисляет сумму произведений соответствующих элементов массивов | Массив 1, массив 2... | Вычислим в ячейке А4 (рис. 8.9) сумму произведений соответствующих элементов массивов А1 :ВЗ и D1 :ЕЗ. В результате получится 156. В самом деле, 3∙2 + 4∙7 + 8∙6 + +6∙7 + + 1 ∙5 + 9∙3=156 |
| СУММСУММКВ (SUMX2PY2) | Вычисляет сумму сумм квадратов соответствующих элементов двух массивов Σ(x2+y2) | Массив X, массив Y | = СУММСУММКВ({2,3,9, 1,8,75}, {6,5,11,7,5,4,4}) равно 521 |
| А | В | С | D | |
| =СУММКВРАЗН(А1:А7;В1:В7) | ||||
Рис. 8.8. Пример вычисления СУММКВРАЗН
| А | В | С | D | Е | |
| = СУММПРОИЗВ(А1:В3;D1:Е3) |
Рис. 8.9. Пример вычисления СУММПРОИЗВ
Функции для операции с матрицами
Прямоугольная таблица чисел
,
состоящая из m-строк и n-столбцов, называется матрицей размера m х n. Если m = n, то матрица называется квадратной.
Умножение матриц.Умножение матрицы А на матрицу В определено только в том случае, если число столбцов матрицы А равно числу строк матрицы В. В результате умножения получится матрица АВ, у которой столько же строк, сколько их в матрице А, и столько же столбцов, сколько их в матрице В.
Пусть даны матрицы А и В (рис. 8.10). Элементы матрицы АВ вычисляются следующим образом:
аb11= (2,3,4,5)(3,4,l,2)=2∙3+3∙4+4∙l+5∙2=32;
ab12=(2,3,4,5)(2,-l,-3,5)=2∙2+3∙ (-l)+4∙ (-3)+5∙5=14;
ab21=(9,2,-3,4)(3,4,l,2)=40 и т. д.
| А | В | С | D | Е | |
| А= | -3 | ||||
| -1 | |||||
| В= | -1 | ||||
| АВ= | |||||
Рис. 8.10. Пример умножения матриц
Для умножения двух матриц в Excel имеется функция
МУМНОЖ(матрица1;матрица2) (MMULT(arrayl,array2)).
Для нахождения произведения двух матриц в Excel необходимо:
– выделить область, где будет размещена матрица произведений двух матриц;
– найти функцию МУМНОЖ;
– указать диапазон первой и второй матриц;
– нажать клавишу «Готово».
Затем следует нажать клавишу F2 и нажать клавиши «Ctrl»+«Shift»+«Enter».В выделенной области появится результат от умножения двух матриц. Формула при этом будет заключена в фигурные скобки.
Обращение матриц.Квадратная матрица вида
называется единичной и обозначается через Е.
Квадратная матрица А называется обратимой, если можно подобрать такую матрицу В, что А∙В = В∙А = Е. Матрица В называется обратной матрицей для матрицы А. Обратную матрицу обозначают через А-1.
Матрицу называют невырожденной, когда ее столбцы линейно независимы. Квадратная матрица обратима тогда и только тогда, когда она невырожденная.
Для обращения матриц в Ехсе1 имеется функция МОБР(массив) (MINVERSE{array)).
Пример. Пусть нам дана исходная матрица А (рис. 8.11). Для ее обращения проделаем следующее:
– выделим область B6:D8;
– вызовем функции МОБР и зададим аргумент B1:D3;
– щелкнем по клавише «Готово»;
– перейдем в режим редактирования, нажав F2;
– нажмем клавиши «Ctrl»+«Shift»+«Enter».
В результате получим матрицу, обратную А.
| А | В | С | D | |
| -1 | ||||
| А= | ||||
| -1 | -1 | |||
| 1,142857 | 0,142857 | 0,428571 | ||
| А-1= | -0,857143 | 0,142857 | -0,571429 | |
| 0,142857 | 0,142857 | 0,428571 |
Рис. 8.11. Пример обращения матрицы
В заданиях 1, 2 и 3 все ячейки с результатами должны быть подписаны.
Задание 1.
С помощью встроенных функций Excel:
1) перемножьте матрицы 1 и 2;
2) найдите сумму чисел второго и третьего столбцов полученной матрицы (Внимание! При нахождении суммы матрицу желательно ввести заново.);
3) найдите сумму квадратов всех чисел для первого столбца полученной матрицы;
4) найдите произведение всех чисел для второго столбца полученной матрицы;
5) найдите обращенную матрицу для 1 матрицы;
6) округлите числа первой строчки матрицы вверх по модулю, второй строчки – вниз по модулю.
| Вариант 1 | Матрица 1 | Матрица 2 | |||||||
| 6,40 | 4,17 | 2,31 | 2,30 | 7,12 | 6,28 | ||||
| 3,65 | 5,62 | 6,27 | 4,93 | 4,23 | 3,24 | ||||
| 9,45 | 4,36 | 8,12 | 6,34 | 1,89 | 2,89 | ||||
| Вариант 2 | Матрица 1 | Матрица 2 | |||||||
| 7,56 | 3,12 | 9,31 | 7,30 | 6,32 | 7,21 | ||||
| 2,65 | 7,12 | 2,27 | 2,34 | 5,23 | 6,24 | ||||
| 5,23 | 5,25 | 11,12 | 1,34 | 7,45 | 2,11 | ||||
| Вариант 3 | Матрица 1 | Матрица 2 | |||||||
| 2,22 | 8,17 | 9,11 | 6,40 | 2,62 | 5,28 | ||||
| 4,15 | 9,67 | 11,67 | 4,53 | 5,83 | 7,24 | ||||
| 1,49 | 3,56 | 3,12 | 6,34 | 1,39 | 2,89 | ||||
| Вариант 4 | Матрица 1 | Матрица 2 | |||||||
| 6,56 | 4,12 | 4,31 | 6,30 | 2,32 | 4,21 | ||||
| 8,65 | 3,12 | 8,27 | 3,38 | 8,46 | 6,27 | ||||
| 1,23 | 1,25 | 4,12 | 8,34 | 7,45 | 4,11 | ||||
| Вариант 5 | Матрица 1 | Матрица 2 | |||||||
| 2,40 | 8,17 | 8,31 | 7,30 | 9,12 | 3,28 | ||||
| 7,65 | 3,62 | 4,27 | 3,93 | 3,23 | 6,24 | ||||
| 5,45 | 2,36 | 8,12 | 6,34 | 6,89 | 4,89 |
Задание 2.
С помощью встроенных функций Excel:
1) найдите сумму всех чисел, больших 5, в массиве;
2) вычислите сумму квадратов разностей (первый столбец принять за Х, второй столбец принять за У);
3) найдите полусумму всех значений массива.
| Вариант 1 | Вариант 2 | Вариант 3 | Вариант 4 | |||||||
| Массив | Массив | Массив | Массив | |||||||
| Вариант 5 | ||||||||||
| Массив | ||||||||||
Задание 3.
С помощью встроенных функций Excel решите следующие примеры:
1) вычислите абсолютное значение для числа А;
2) вычислите натуральный логарифм от аргумента N;
3) возведите число В в степень С;
4) вычислите 
;
5) вычислите факториал К.
| № варианта | А | N | В | С | m | n | К |
| -5668 | |||||||
| -7634 | |||||||
| -9369 | |||||||
| -2581 |
Результаты выполнения работы
В папке «Лабораторная работа № 8» должны присутствовать файлы:
Ø Задание 1.xclx
Ø Задание 2.xclx
Ø Задание 3.xclx
Лабораторная работа № 9
ЛОГИЧЕСКИЕ ФОРМУЛЫ В ЭЛЕКТРОННЫХ ТАБЛИЦАХ
Цель работы – изучение возможностей применения в электронных таблицах логических данных и выражений.
К логическим функциям относятся такие функции, которые позволяют выбрать то или иное решение в зависимости от того, выполняется или нет одно или несколько условий.
С помощью этих функций в Excel можно предпринять одно действие, если условие выполняется, и другое – если условие не выполняется.
Под условием в Excel понимается следующая запись:
выражение # 1 условный оператор выражение # 2
К условным операторам относятся:
< – меньше, чем, например В1 < С4;
<= – меньше или равно, например В1<=С4;
> – больше, чем, например В1>С4;
>= – больше или равно, например В1>=С4;
= – равно, например В1=С4;
<> – неравно, например В 1<>С4.
Логические функции электронных таблиц (рис. 9.1) предназначены для проверки истинности логических высказываний или построения таблиц истинности логических операций.
Рис. 9.1. Окно вставки логических функций
Аргументами логических функций являются логические значения ИСТИНА и ЛОЖЬ. Логические значения могут быть получены как результат определения логических выражений. Например, для логического выражения 10>5 результатом будет логическое выражение ИСТИНА, а для логического выражении А1<А2 (где в ячейке А1 хранится число 10, а в ячейке А2 – число 5) – значение ЛОЖЬ.
ПРОСТАЯ ФУНКЦИЯ ЕСЛИ
Формат записи: =ЕСЛИ (условие; выражение В; выражение С)
Эта запись означает:
1) если условие выполняется, то происходит действие, определенное в выражении В;
2) если условие не выполняется, то происходит действие, определенное в выражении С.
Выражениями В и С могут быть числовое выражение, функция, ссылка на клетку таблицы или ее имя, заключенный в кавычки текст.
Пример. Пусть у ряда работников имеется задолженность по потребительскому кредиту, которая отражена в диапазоне СЗ:С7 (рис. 9.2). Нужно найти в списке таких работников и удержать с них в счет погашения кредита 10 % от начисленной им суммы.
| А | В | С | D | Е | |
| Ф.И.О. | Начислено, тыс. руб. | Задолженность по кредитам | Удержано, тыс. руб. | ||
| Потреби-тельский | Жилищ-ный | ||||
| Иванов | =ЕСЛИ(С3>0;В3*0,1;””) | ||||
| Петров | =ЕСЛИ(С4>0;В4*0,1;””) | ||||
| Кузьмин | =ЕСЛИ(С5>0;В5*0,1;””) | ||||
| Сухов | =ЕСЛИ(С6>0;В6*0,1;””) | ||||
| Николаев | =ЕСЛИ(С7>0;В7*0,1;””) |
Рис. 9.2.Пример простой логической функции ЕСЛИ
Для нашего примера логическая функция будет иметь следующий вид:
=ЕСЛИ (С3>0; ВЗ *0,1; " ").
Данная логическая функция означает следующее: если задолженность по потребительскому кредиту больше нуля, то необходимо удержать 10 % с начисленной суммы, в противном случае необходимо вывести пробелы.
Запишем ее в ячейку ЕЗ, а затем скопируем в ячейки Е4:Е7.
По диапазону СЗ:С7 отыскиваются работники, у которых есть задолженность по потребительскому кредиту, а в диапазоне ЕЗ:Е7 указывается размер удержанной суммы. В случаях, когда задолженности по потребительскому кредиту нет, напротив фамилий соответствующих работников в диапазоне ЕЗ:Е7 выводятся пробелы.
В результате в диапазоне ЕЗ:Е7 (рис. 9.3) получим размер удержанной суммы с работников, у которых имеются задолженности по потребительскому кредиту.
| А | В | С | D | Е | |
| Ф.И.О. | Начислено, тыс. руб. | Задолженность по кредитам | Удержано, тыс. руб. | ||
| Потребительский | Жилищный | ||||
| Иванов | |||||
| Петров | |||||
| Кузьмин | |||||
| Сухов | |||||
| Николаев |
Рис. 9 3. Результат выполнения простой логической функции ЕСЛИ
ЛОГИЧЕСКАЯ ФУНКЦИЯ И
Логическая функция И обеспечивает проверку одновременного выполнения связанных этой функцией условий. При выполнении всех условий функция принимает значение ИСТИНА, а если хотя бы одно из условий не выполняется, функция принимает значение ЛОЖЬ.
Формат записи: И (условие #1; условие #2)
Результат является истинным, если два условия истинны. В противном случае результат ложен:
| Условие #1 | Условие #2 | Результат |
| Истина | Истина | Истина |
| Истина | Ложь | Ложь |
| Ложь | Истина | Ложь |
| Ложь | Ложь | Ложь |
Функция И входит в состав функции ЕСЛИ, которая в этом случае имеет следующий формат:
=ЕСЛИ(И(усл.#1;усл.#2); выражение В; выражение С)
Пример. Найти работников, у которых одновременно имеются задолженности по потребительскому кредиту и кредиту на жилищное строительство, и удержать от начисленной им суммы 20 % (рис. 9.4).
| А | В | С | D | Е | |
| Ф.И.О. | Начислено, тыс. руб. | Задолженность по кредитам | Удержано, тыс. руб. | ||
| Потребит | Жилищн. | ||||
| Иванов | =ЕСЛИ(И(С3>0;D3>0); В3*0,2;””) | ||||
| Петров | =ЕСЛИ(И(С4>0;D4>0); В4*0,2;””) | ||||
| Кузьмин | =ЕСЛИ(И(С5>0;D5>0); В5*0,2;””) | ||||
| Сухов | =ЕСЛИ(И(С6>0;D6>0); В6*0,2;””) | ||||
| Николаев | =ЕСЛИ(И(С7>0;D7>0); В7*0,2;””) |
Рис. 9.4. Логическая функция И в сочетании с функцией ЕСЛИ
Для нашего примера логическая функция будет иметь следующий вид:
=ЕСЛИ (И (C3>0;D3>0); В3*0,2; " ")
Данная логическая функция означает следующее: если одновременно задолженности по потребительскому кредиту и кредиту на жилищное строительство больше нуля, то необходимо удержать 20 % с начисленной суммы, в противном случае необходимо вывести пробелы.
Запишем ее в ячейку ЕЗ, а затем скопируем в ячейки Е4:Е7.
В столбцах С и D (рис. 9.5) будет найден только один работник, у которого есть задолженность по двум видам кредита. В столбце Е у него будет удержано 20 % от начисленной суммы. Напротив остальных работников в столбце Е будут выведены пробелы.
| А | В | С | D | Е | |
| Ф.И.О. | Начислено, тыс. руб. | Задолженность по кредитам | Удержано, тыс. руб. | ||
| Потребит. | Жилищн. | ||||
| Иванов | |||||
| Петров | |||||
| Кузьмин | |||||
| Сухов | |||||
| Николаев |
Рис.9.5. Результат выполнения логической функции И в сочетании с функцией ЕСЛИ
ЛОГИЧЕСКАЯ ФУНКЦИЯ ИЛИ
Логическая функция ИЛИ обеспечивает проверку выполнения хотя бы одного условия, из всех условий, связанных этой функцией. В этой ситуации функция принимает значение ИСТИНА, а если ни одно условие не выполняется, функция принимает значение ЛОЖЬ.
Данная функция истинна, если истинно хотя бы одно из двух входящих в нее условий. Формат записи: = ИЛИ (условие #1; условие #2)
Лишь в случае, когда оба условия ложны, функция ИЛИ также ложна:
| Условие #1 | Условие #2 | Результат |
| Истина | Истина | Истина |
| Истина | Ложь | Истина |
| Ложь | Истина | Истина |
| Ложь | Ложь | Ложь |
Данная функция используется вместе с логической функцией ЕСЛИ, которая в этом случае имеет следующий формат:
=ЕСЛИ(ИЛИ(усл.#1;усл.#2); выражение В; выражение С)
Пример. Найти работников, у которых имеется задолженность либо по потребительскому кредиту, либо по кредиту на жилищное строительство, либо по обоим видам кредита сразу, и удержать с них в счет погашения кредита 10 % от начисленной им суммы (рис. 9.6).
| А | В | С | D | Е | |
| Ф.И.О. | Начислено, тыс. руб. | Задолженность по кредитам | Удержано, тыс. руб. | ||
| Потребит. | Жилищн. | ||||
| Иванов | =ЕСЛИ(ИЛИ(С3>0;D3>0); В3*0,1;””) | ||||
| Петров | =ЕСЛИ(ИЛИ(С4>0;D4>0); В4*0,1;””) | ||||
| Кузьмин | =ЕСЛИ(ИЛИ(С5>0;D5>0); В5*0,1;””) | ||||
| Сухов | =ЕСЛИ(ИЛИ(С6>0;D6>0); В6*0,1;””) | ||||
| Николаев | =ЕСЛИ(ИЛИ(С7>0;D7>0); В7*0,1;””) |
Рис. 9.6. Логическая функция ИЛИ в сочетании с функцией ЕСЛИ
Логическая функция в нашем примере будет иметь вид
=ЕСЛИ (ИЛИ (С3>0; D3>0); В3*0,1; " ")
Данная логическая функция означает следующее: если одновременно или по отдельности задолженности по потребительскому кредиту и кредиту на жилищное строительство больше нуля, то необходимо удержать 10 % с начисленной суммы, в противном случае необходимо вывести пробелы.
Запишем ее в ячейку ЕЗ, а затем скопируем в ячейки Е4:Е7.
В столбцах С и D (рис. 9.7) будут найдены работники, у которых есть задолженность хотя бы по одному виду кредита, и в столбце Е с них будет удержано 10 % от начисленной им суммы. Один работник не имеет задолженности по кредиту, тогда в столбце Е напротив его фамилии будут выведены пробелы.
| А | В | С | D | Е | |
| Ф.И.О. | Начислено, тыс. руб. | Задолженность по кредитам | Удержано, тыс. руб. | ||
| Потребит. | Жилищн. | ||||
| Иванов | |||||
| Петров | |||||
| Кузьмин | |||||
| Сухов | |||||
| Николаев |
Рис.9.7. Результат выполнения логической функции ИЛИ
в сочетании с функцией ЕСЛИ
ВЛОЖЕННЫЕ ЛОГИЧЕСКИЕ ФУНКЦИИ ЕСЛИ
Формат записи:
=ЕСЛИ(усл.#1; выражение В; ЕСЛИ(yсл.#2; выражение С; ЕСЛИ (...)))
Число вложенных функций в принципе может быть любое, но общая длина строки не должна быть слишком большой, это затрудняет ее чтение; кроме того, необходимо следить за тем, чтобы число открытых скобок в точности равнялось числу закрытых скобок.
Пример. Найти работников, у которых имеются одновременно задолженности по обоим видам кредита, и удержать от начисленной им суммы 20 % в счет погашения кредитов (рис. 9.8). С остальных работников, имеющих задолженность по какому-либо одному виду кредита, удержать 10 % от начисленной им суммы. Работникам, не имеющим задолженности по кредиту, проставить в графе «Удержано» – «б/к».
В нашем примере логическая функция будет иметь такой вид:
=ЕСЛИ (И (C3>0; D3 >0); В3*0,2; ЕСЛИ (И(C3=0; D3=0); "б/к"; В3*0,1))
Данная логическая функция означает следующее: если одновременно задолженности по потребительскому кредиту и кредиту на жилищное строительство больше нуля, то необходимо удержать 20 % с начисленной суммы, если обе задолженности одновременно равны нулю, то необходимо вывести «б/к», в противном случае необходимо удержать 10 % от начисленной суммы.
Запишем ее в ячейку ЕЗ, а затем скопируем в ячейки Е4:Е7 ( рис. 9.8).
В столбцах С и D (рис. 9.9) будут найдены работники, у которых есть задолженности по двум видам кредита. С них будет удержано 20 % от начисленных им сумм. Напротив фамилии работника, у которого нет задолженности по кредиту, в столбце Е будет выведено «б/к». Наконец, с остальных работников будет удержано 10 % от начисленных им сумм.
| А | В | С | D | Е | ||
| Ф.И.О. | Начислено, тыс. руб. | Задолженность по кредитам | Удержано, тыс. руб. | |||
| Потребит. | Жилищн. | |||||
| Иванов | =ЕСЛИ(И(С3>0;D3>0); В3*0,2; ЕСЛИ(И(С3=0;D3=0); “б/к”); В3*0,1 | |||||
| Петров | =ЕСЛИ(И(С4>0;D4>0); В4*0,2; ЕСЛИ(И(С4=0;D4=0); “б/к”); В4*0,1 | |||||
| Кузьмин | =ЕСЛИ(И(С5>0;D5>0); В5*0,2; ЕСЛИ(И(С5=0;D5=0); “б/к”); В5*0,1 | |||||
| Сухов | =ЕСЛИ(И(С6>0;D6>0); В6*0,2; ЕСЛИ(И(С6=0;D6=0); “б/к”); В6*0,1 | |||||
| Николаев | =ЕСЛИ(И(С7>0;D7>0); В7*0,2; ЕСЛИ(И(С7=0;D7=0); “б/к”); В7*0,1 | |||||
Рис.9.8. Вложенная логическая функция ЕСЛИ
| А | В | С | D | Е | |
| Ф.И.О. | Начислено, тыс. руб. | Задолженность по кредитам | Удержано, тыс. руб. | ||
| Потребит. | Жилищн. | ||||
| Иванов | |||||
| Петров | |||||
| Кузьмин | |||||
| Сухов | |||||
| Николаев |
Рис. 9.9. Результат выполнения вложенной логической функции ЕСЛИ
Логическая функция НЕ преобразует уже имеющееся логическое значение и принимает значение ИСТИНА, если аргумент имеет значение ЛОЖЬ, и наоборот.
Логическая функция ЕСЛИОШИБКА возвращает значение ЕСЛИ_ОШИБКА, если выражение ошибочно. В противном случае функция возвращает само выражение.
Задание 1.
Выполнить рассмотренное в Примерах задание (рис. 9.4, 9.6, 9.8)
Задание 2.
Составить логическую функцию для взимания подоходного налога с физических лиц, если дана шкала налогообложения:
– до 10 тыс. руб. – 12 %;
– от 10 до 24 тыс. руб. – 1440 тыс. руб. + 20 % с суммы;
– от 24 до 36 тыс. руб. – 3840 тыс. руб. + 25 % с суммы;
– от 36 до 48 тыс. руб. – 6840 тыс. руб. + 30 % с суммы;
– свыше 48 тыс. руб. – 10 440 тыс. руб. + 35 % с суммы.
Задание 3.
1. На рабочем листе «Результаты экзаменационной сессии» электронной книги «Стипендия» подготовить ведомость назначения студентов на стипендию по результатам экзаменационной сессии (число студентов не менее 12). Вычислить число полученных группой оценок и средний балл по каждой дисциплине.
Оценки могут принимать значения «5», «4», «3», «2» и пустая ячейка – «не явился».