Использование функций в расчетах. Матричные операции.

Определение: Функция в Excel- это готовая к употреблению встроенная формула.

 

Функция состоит из имени функции и (как правило) аргументов. Примеры:

 

=СУММ(A1;A2;A3;A4) =СУММ(A1:A4)

 

=КОРЕНЬ(25) =КОРЕНЬ(A3) =КОРЕНЬ(F3*25)

 

Функции используются как самостоятельно, так и в составе более сложных формул

 

=КОРЕНЬ(СУММ(A1:A4)+ СУММ(В1:B4))

 

Список всех функций доступен по пути: Вставка-Функция. Далее действовать по предлагаемому диалогу.

Для удобства использования функций все они разделены на категории (математические, текстовые, финансовые, логические и.т.д.).

Математические функции

► ABS (число)

Возвращает модуль (абсолютную величину) числа.

=ABS(2) → 2;

=ABS(-3) → 3;

A2=-6 тогда =ABS(A2*3) →18;

 

► ПИ ()

Возвращает число «пи».

=ПИ() → 3,141592654…;

 

► РАДИАНЫ (угол)

Преобразует градусы в радианы. Угол — величина угла в градусах, которую требуется преобразовать.

=РАДИАНЫ(59) →1,03;

A2=150; тогда =РАДИАНЫ(А2) →2,62;

 

► SIN (угол)

Возвращает синус угла. Угол — величина угла в радианах.

=Sin(0,8) → 0,717356091;

Определить синус 30 град. =Sin(Радианы(30)) → 0,5;

► COS (угол)

Возвращает косинус угла. Угол — величина угла в радианах.

 

► TAN (угол)

Возвращает тангенс угла. Угол — величина угла в радианах.

 

► ГРАДУСЫ (угол)

Преобразует радианы в градусы. Угол — это угол в радианах, преобразуемый в градусы.

=Градусы(1,3) →74,48451337;

=Градусы(пи()) →???? (Каким будет результат?)

► ASIN (число)

Возвращает арксинус числа. Арксинус числа — это угол, синус которого равняется числу. Угол определяется в радианах в диапазоне от до

Число — это синус искомого угла, значение должно быть в диапазоне от -1 до 1.

 

=ASin(0,8) → 0,927295218;

=ГРАДУСЫ(ASin(0,8)) → 53,13010235;

► ACOS (число)

Возвращает арккосинус числа. Арккосинус числа — это угол, косинус которого равен числу. Угол определяется в радианах в интервале от 0 до

Число — это косинус угла, значение должно находиться в диапазоне от -1 до 1.

 

► ATAN (число)

Возвращает арктангенс числа. Арктангенс числа — это угол, тангенс которого равен числу. Угол определяется в радианах в диапазоне от до

Число — это тангенс искомого угла.

 

► EXP (степень)

Возвращает число «e», возведенное в указанную степень. Число «e» равно 2,71828182845904.

e³ → =EXP(3)

=EXP(3) → 20,08554;

=EXP(1) → 2,718282;

=EXP(2,4) → 11,02318;

► СТЕПЕНЬ (число; степень)

Возвращает результат возведения числа в степень.

Число — основание. Оно может быть любым вещественным числом.

Степень — показатель степени, в которую возводится основание.

=СТЕПЕНЬ(-5; 2) → 25;

=СТЕПЕНЬ(7,2; 2,8) → 251,4959;

=СТЕПЕНЬ(exp(1); пи()) → 23,14069

Вместо функции СТЕПЕНЬ для возведения в степень можно использовать оператор ^, например 5^2

► LN (число)

Возвращает натуральный логарифм числа. Натуральный логарифм — это логарифм по основанию e (2,71828182845904).

Число — положительное вещественное число, для которого вычисляется натуральный логарифм.

=LN(34) → 3,526361

 

► LOG (число; основание)

Возвращает логарифм числа по заданному основанию.

Число — положительное вещественное число, для которого вычисляется логарифм.

Основание — основание логарифма. Если основание опущено, то оно полагается равным 10.

 

=LOG(58;3) →3,695975

 

► ЧАСТНОЕ (числитель; знаменатель)

Возвращает целую часть результата деления с остатком. Эта функция используется, когда нужно отбросить остаток от деления.

=ЧАСТНОЕ(5; 2) → 2

=ЧАСТНОЕ(-10; 3) → -3

 

► ОСТАТ (число; делитель)

Возвращает остаток от деления аргумента число на делитель. Результат имеет такой же знак, как и делитель.

=ОСТАТ(3; 2) → 1

=ОСТАТ(-11; 3) → 2

=ОСТАТ(11; -3) → -2

 

► ОКРУГЛ (число; число_разрядов)

Округляет число до указанного количества десятичных разрядов.

Число — округляемое число.

Число_разрядов — количество десятичных разрядов, до которого нужно округлить число.

• Если число_разрядов больше 0, то число округляется до указанного количества десятичных разрядов справа от десятичной запятой.
• Если число_разрядов равно 0, то число округляется до ближайшего целого.
• Если число_разрядов меньше 0, то число округляется слева от десятичной запятой.

=ОКРУГЛ(20,3417; 1) → 20,3 =ОКРУГЛ(20,3417; 0) → 20

=ОКРУГЛ(20,3417; 2) → 20,34 =ОКРУГЛ(20,5417; 0) → 21

=ОКРУГЛ(20,3417; 3) → 20,342 =ОКРУГЛ(24; -1) → 20

=ОКРУГЛ(20,3417; 4) → 20,3427 =ОКРУГЛ(25; -1) → 30

 

► КОРЕНЬ (число)

Возвращает положительное значение квадратного корня.

Число — число, для которого вычисляется квадратный корень.

 

=КОРЕНЬ(16) → 4

=КОРЕНЬ(abs(A2)) → …

 

► ФАКТР (число)

Возвращает факториал числа.

 

=ФАКТР(5) → 120

=ФАКТР(3,2) → 6 { вычисляется факториал от целой части числа т.е. от 3 }

=ФАКТР(0) → 1

 

► ПРОИЗВЕД (число1; число2;...)

Перемножает числа, заданные в качестве аргументов и возвращает их произведение.

Число1, число2,... — от 1 до 30 перемножаемых чисел.

 

	A	B	C

=ПРОИЗВЕД(1; 2; 3; 4) → 24

=ПРОИЗВЕД(A1:B2) → 24

► СУММКВ (число1; число2;...) Возвращает сумму квадратов аргументов.

Число1, число2,... — от 1 до 30 аргументов, квадраты которых суммируются. Можно использовать отдельный массив.

СУММКВ((число 1; число 2; …число n) = где: x_i = число i

	A	B	C

=СУММКВ(1; 2; 3; 4) → 30

=СУММКВ(A1:B2) → 30

► СУММПРОИЗВ (массив1; массив2; массив3;...)

Перемножает соответствующие элементы заданных массивов и возвращает сумму произведений.

Массив1, массив2, массив3,... — от 2 до 30 массивов, чьи компоненты нужно перемножить, а затем сложить.

• Аргументы, которые являются массивами, должны иметь одинаковые размерности.
• СУММПРОИЗВ трактует нечисловые элементы массивов как нулевые.

A1:B2→ X; E1:F2→ Y тогда =СУММПРОИЗВ (X; Y) =

 

	A	B	C	D	E	F	G

=СУММПРОИЗВ (X; Y) → 40

Аналог: 1*5+2*1+3*7+4*3 =40

 

► СУММСУММКВ (массив_X; массив_Y)

Возвращает сумму сумм квадратов соответствующих элементов двух массивов.

Массив_X — первый массив или интервал значений.

Массив_Y — второй массив или интервал значений.

• Если аргумент, который является массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются; однако ячейки, которые содержат нулевые значения, учитываются.

· Если массив_X и массив_Y имеют различное количество значений, то функция СУММСУММКВ возвращает значение ошибки #Н/Д.

A1:B2→ X; E1:F2→ Y тогда =СУММСУММКВ (X; Y) =

 

	A	B	C	D	E	F	G

=СУММСУММКВ (X; Y) → 114

(1²+5²) +(2²+1²)+(3²+7²)+(4²+3²) =114

 

► СУММРАЗНКВ (массив_X; массив_Y)

Возвращает сумму разностей квадратов соответствующих элементов двух массивов.

Массив_X — первый массив или интервал значений.

Массив_Y — второй массив или интервал значений.

• Если аргумент, который является массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются; однако ячейки, которые содержат нулевые значения, учитываются.
• Если массив_X и массив_Y имеют различное количество значений, то функция СУММРАЗНКВ возвращает значение ошибки #Н/Д.

A1:B2→ X; E1:F2→ Y тогда =СуммРазнКВ (X; Y) =

 

	A	B	C	D	E	F	G

=СуммРазнКВ (X; Y) → -54

(1²-5²) +(2²-1²)+(3²-7²)+(4²-3²) = -54

 

► СУММКВРАЗН (массив_X; массив_Y)

Возвращает сумму квадратов разностей соответствующих элементов двух массивов.

Массив_X — первый массив или интервал значений.

Массив_Y — второй массив или интервал значений.

A1:B2→ X; E1:F2→ Y тогда =СуммКВРазн (X; Y) =

 

	A	B	C	D	E	F	G

=СуммРазнКВ (X; Y) → 34

(1-5)² +(2-1)²+(3-7)²+(4-3)² = 34

 

► СЧЁТЕСЛИ (диапазон; критерий)

Подсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному критерию.

Диапазон — массив, в котором нужно подсчитать ячейки.

Критерий — критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать. Например, критерий может быть выражен следующим образом: 32, "32", ">32", "яблоки".

	A	B	C	D	E
		яблоки
	Яблоки
			ЯБЛОКИ
	Яблоки

 

=СЧЕТЕСЛИ(A2:D8; "яблоки") → 4

=СЧЕТЕСЛИ(A2:D8; "груши") → 0

=СЧЕТЕСЛИ(A2:D8; ">30") → 3

 

 

► СУММЕСЛИ (диапазон; критерий; диапазон_суммирования)

Суммирует ячейки, удовлетворяющие заданному условию.

 

Диапазон — диапазон анализируемых ячеек.

Критерий — критерий в форме числа, выражения или текста, определяющего суммируемые ячейки. Например, критерий может быть выражен как 32, "32", ">32", "яблоки".

Диапазон_суммирования — фактические ячейки для суммирования.

· Ячейки в «диапазон_суммирования» суммируются, только если соответствующие им ячейки в аргументе «диапазон» удовлетворяют критерию.

· Если «диапазон_суммирования» опущен, то суммируются ячейки в аргументе «диапазон».

	A	B	C	D	E
		№	Товар	Вес	Стоим.
			Груши
			Яблоки
			Слива
			Яблоки
			Вишня
			Слива
			Черешня
			Яблоки

=СУММЕСЛИ(С3:C10; "яблоки"; E3:E10) → 3950

=СУММЕСЛИ(С3:C10; "СЛИВА";E3:E10) → 1300

=СУММЕСЛИ(С3:C10; "яблоки"; D3:D10) → 75

Объяснить формулы

► СЛУЧМЕЖДУ (нижн_граница; верхн_граница)

Возвращает случайное число в заданном интервале.При каждом вычислении рабочего листа возвращается новое случайное число.

 

Если данная функция недоступна или возвращает ошибку #ИМЯ?, установите и загрузите надстройку «Пакет анализа».

· В меню Сервис выберите команду Надстройки.

· В списке надстроек выберите Пакет анализа и нажмите кнопку OK.

· Выполните инструкции программы установки, если это необходимо.

 

Нижн_граница — наименьшее целое число, которое возвращает функция Верхн_граница — наибольшее целое число, которое возвращает функция

 

=СЛУЧМЕЖДУ(2;5) → вернет любое число из диапазона 2, 3, 4, 5

 

Матричные операции

 

► МОПРЕД (массив)

Возвращает определитель матрицы, хранящейся в массиве.

Массив — числовой массив с равным количеством строк и столбцов.

	A	B	C	D	E	F	G

 

 

Правило вычисления определителя матрицы

[(7*9*8)+(9*8*10)+(9*8*10)] – [(9*9*9)+(10*10*7)+(8*8*8)] = 3

 

 

► ТРАНСП (массив)

Транспонирование матрицы хранящейся в массиве.

Массив — это транспонируемый массив или диапазон ячеек на рабочем листе. Транспонирование массива заключается в том, что первая строка массива становится первым столбцом нового массива, вторая строка массива становится вторым столбцом нового массива и так далее.

	A	B	C	D	E	F	G	H	I

 

 

Порядок решения следующий: выделяется область для новой матрицы. Количество строк новой (транспонированной) матрицы равно количеству столбцов исходной, а количество столбцов равно количеству строк исходной. В верхний левый угол выделенной области вписывается функция = ТРАНСП(В3:D5) далее нажатие клавиш Ctrl-Sh-Ent.

► МОБР (массив) Возвращает обратную матрицу (матрица хранится в массиве).

Массив — числовой массив с равным количеством строк и столбцов.

 

	A	B	C	D	E	F	G	H	I
						-5,00	0,14	4,14
						3,00	-0,29	-2,29
						1,00	0,14	-0,86

 

 

 

Порядок решения следующий: выделяется область для новой матрицы такого же размера как и исходная. В верхний левый угол выделенной области вписывается функция = МОБР (В3:D5) далее нажатие клавиш Ctrl-Sh-Ent.

 

Примечание: обратная матрица может не существовать.

 

 

► МУМНОЖ (массив1; массив2)

Возвращает произведение матриц (матрицы хранятся в массивах). Результатом является массив с таким же числом строк, как массив1 и с таким же числом столбцов, как массив2.

Массив1, массив2 — перемножаемые массивы.

Количество столбцов аргумента массив1 должно быть таким же, как количество сток аргумента массив2, и оба массива должны содержать только числа.

 

 

	A	B	C	D	E	F	G	H	I
									Порядок решения следующий: выделяется область с таким же числом строк, как массив1 и с таким же числом столбцов, как массив2. В верхний левый угол выделенной области вписывается функция =МУМНОЖ(B2:D3; B7:С9)далее нажатие клавишCtrl-Sh-Ent.   Правило умножения матриц i — номер строки, j — номер столбца

 

 

 

 

Пример использования функций

Показать стремление функций к своим пределам.

Первый замечательный предел

Второй замечательный предел

 

	A	B	C	D	E	F	G	H
		Первый замеч. предел.		Второй замеч. предел.
		x(град)	Sin(x)	y		n	y
				0,63662
			0,999847695	0,643675			2,25
			0,087155743	0,998731			2,702644469
			0,069756474	0,999188			2,702822336
			0,052335956	0,999543			2,702996202
			0,034899497	0,999797			2,703166202
			0,017452406	0,999949			2,703332461
		0,5	0,008726535	0,999987			2,703495103
		0,005	8,72665E-05				2,708281999
		0,0001	1,74533E-06	1			2,708355035
						….	……………….
							2,718280469

 

 

Пример матричных расчетов

Матрица А (3 2); Матрица B (2 3); получить С=А*В → С^Т → (С^Т)^-1

	A	B	C	D	E
	А=
	B=
					=МУМНОЖ(B1:D2; B4:C6)Ctrl-Sh-Ent.
	C=
					=ТРАСП(B9:C10)Ctrl-Sh-Ent.
	C^T=
					=МОБР(B13:C14)Ctrl-Sh-Ent.
	(С^T)^-1=	0,394	-0,39
	-0,21	0,228

Функции даты и времени

Функции даты

► СЕГОДНЯ()

Возвращает текущую дату в числовом формате. Числовой формат даты — это код дата-время, используемый в Microsoft Excel для вычислений с датами и периодами времени. Если до ввода этой функции форматом ячейки был Общий, результат будет отформатирован как дата.

Заметки

Microsoft Excel хранит даты как ряд последовательных номеров, что позволяет выполнять над ними вычисления. По умолчанию день 1 января 1900 года имеет номер 1, а 1 января 2008 — номер 39448, так как интервал в днях между этими датами составляет 39448.

=СЕГОДНЯ() → день. месяц. год. (если формат ячейки "Общий" или "Дата")

=СЕГОДНЯ() → 38586 (если формат ячейки "Числовой")

► ДАТА (год; месяц; день)

Возвращает целое число, представляющее определенную дату. Если до ввода этой функции форматом ячейки был Общий, результат будет отформатирован как дата.

 

Год — аргумент, который может иметь от одной до четырех цифр. Microsoft Excel интерпретирует аргумент год в соответствии с используемой системой дат. По умолчанию Excel для Windows использует систему дат 1900.

· Если аргумент год находится в диапазоне от 0 (ноль) до 1899 (включительно), Excel прибавляет это значение к числу 1900 для вычисления года. Например, ДАТА(108;1;2) возвращает 2 января 2008 (1900+108) года.

· Если аргумент год находится в диапазоне от 1900 до 9999 (включительно), Excel использует это значение как год. Например, ДАТА(2008;1;2) возвращает 2 января 2008 года.

 

=ДАТА(1900;2;1) → 1.2.1900 (если формат ячейки "Общий" или "Дата")

=ДАТА(1900;2;1) → 32 (если формат ячейки "Числовой")

 

►ГОД (дата_в_числовом_формате)

Возвращает год, соответствующий аргументу дата_в_числовом_формате. Год определяется как целое в интервале 1900-9999.

Дата_в_числовом_формате — это дата, год которой необходимо найти. Даты должны вводиться с использованием функции ДАТА или как результат вычисления других формул и функций. Например, для 23-го мая 2008 года следует использовать ДАТА(2008;5;23).

 

=ГОД (дата(2008;5;23)) → 2008

=ГОД (23.5.2008) → 2007

 

►МЕСЯЦ( дата_в_числовом_формате ) Аналогично функции ГОД() только для месяца

►ДЕНЬ( дата_в_числовом_формате ) Аналогично функции ГОД() только для дня

 

=МЕСЯЦ (дата(2008;5;23)) → 5

=ДЕНЬ (23.5.2008) → 23

►ДЕНЬНЕД (дата_в_числовом_формате; Тип)

Возвращает день недели, соответствующий аргументу дата_в_числовом_формате. День недели определяется как целое в интервале от 1 (воскресенье) до 7 (суббота).

Дата_в_числовом_формате — это число, соответствующее дате, день недели которой необходимо найти. Даты должны вводиться с использованием функции ДАТА или как результат вычисления других формул и функций. Например, для 23-го мая 2008 года следует использовать ДАТА(2008,5,23).

Тип — это число, которое определяет тип возвращаемого значения.

22.8.2005 (понедельник)

=ДЕНЬНЕД(22.8.2005) → 2 День недели с числом от 1 (воскресенье) до 7 (суббота)

=ДЕНЬНЕД(22.8.2005; 2) → 1 День недели с числом от 1 (понедельник) до 7 (воскресенье)

=ДЕНЬНЕД(22.8.2005; 3) → 0 День недели с числом от 0 (понедельник) до 6 (воскресенье)

Операции с датами

Для сложения и вычитания применяются знаки (+) и (-)

 

	A	B	C	D	E
					=B3+C3
		21.08.2005		29.08.2005
					=B5+C5
		15.02.2005		10.04.2005
					=B7+C7
		01.01.1900		27.09.1902
					=B10-C10
		18.07.2005	23.03.2002	1213
					=B12-C12
		09.01.2004	17.08.1987	5989

 

Логические функции

 

Логические операторы

Логические операторы предназначены для операций сравнения:

=, >, <, >=, <=, <>

Логические выражения

Логические выражения это формулы, выполняющие операций сравнения:

=23>18 =23<=59 =2+45=13 =A1-C3<>0 и.т.д.

Логические выражения, в результате операции сравнения, выдают один из двух логических результатов: ИСТИНА или ЛОЖЬ. Например:

=23>18 → истина

=23<=59 → истина

=2+45=13 → ложь

=A1-C3<>0 → истина / ложь (в зависимости от значений по адресам A1 и С3)

 

Логические функции.

► И (логич.выраж1, логич.выраж2,….)

· Возвращает значение ИСТИНА, если все аргументы имеют значение истина;

· Возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ложь.

Логич.выраж1, логич.выраж2,….. — это от 1 до 30 проверяемых условий, которые могут иметь значение либо истина, либо ложь.

► ИЛИ (логич.выраж1, логич.выраж2,….)

· Возвращает значение ИСТИНА, если хотя бы один аргумент имеет значение истина;

· Возвращает значение ЛОЖЬ, если все аргументы имеет значение ложь.

Логич.выраж1, логич.выраж2,….. — это от 1 до 30 проверяемых условий, которые могут иметь значение либо истина, либо ложь.

 

 

	A	B	C	D	E	F	G	H	I
		Истина			Ложь		Ложь
		Ложь			Истина				Истина

 

 

►ЕСЛИ (ЛВ; Оператор1; Оператор2)

Возвращает одно значение (Оператор1), если ЛВ при вычислении дает значение ИСТИНА, и другое значение (Оператор2), если ЛОЖЬ.

· ЛВ –логическое выражение.

· Оператор1– оператор, выполняющийся, если ЛВ=ИСТИНА

· Оператор2– оператор, выполняющийся, если ЛВ=ЛОЖЬ

Функция ЕСЛИ используется при проверке условий для значений и формул.

 

	A	B	C	D	E
		Решения нет

 

 

 

Пример 1:

В ячейке C8 находится число. Это число может быть:

• равным нулю,
• больше нуля,
• меньше нуля.

Написать функцию ЕСЛИ, определяющую вид числа в ячейке С8.

=ЕСЛИ(С8=0; “ноль”; ЕСЛИ(С8>0; “больше нуля”; “ меньше нуля“))

Пример 2:

В оценочной ведомости выставлены оценки по двум предметам

Написать функцию ЕСЛИ, определяющую

отличников,

имеющих, хотя бы одну двойку.

 

 

 

	A	B	C	D	E	F
		Фамилия	Информ.	Иностр.	Примечание
		Захаров
		Никонов			Неуд.
		Савельев			Отличник
		Морозов			Неуд.
		Карпов
		Романов			Отличник

Самостоятельные.

ЗАДАЧА1: Решить уравнение ax²+bx+c=0 при а<>0

 

 

	A	B	C	D	E
	a =
	b =
	c =
	X1 =
	X2 =

		=ЕСЛИ (c1>=0; (-b2+c1^0,5)/(2*b1); “Нет решения”)
	=ЕСЛИ (c1>=0; (-b2-c1^0,5)/(2*b1); “Нет решения”)

 

 

ЗАДАЧА2: Для каждого отдела определить количество сотрудников и сумму денег для выплаты зарплаты. Данные приведены ниже.

 

	A	B	C	D	E	F	G	H	I
	Фамилия	Отдел	Оклад			Отдел	Количество	Сумма
	Алхазов
	Анисимов			<