Функции Excel

=ПИ()

Функция РАДИАНЫ и ГРАДУСЫ

Тригонометрические функции  используют углы, выраженные в радианах, а не в градусах. Измерение углов в радианах основывается на константе пи и при этом 180 градусов равны пи радиан. Excel предоставляет две функции, РАДИАНЫ (RADIANS) и ГРАДУСЫ (DEGREES), чтобы облегчить работу с тригонометрическими функциями.

Вы можете преобразовать  радианы в градусы, используя  функцию ГРАДУСЫ. Синтаксис:

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

Здесь - угол - это число, представляющее собой угол, измеренный в радианах. Для преобразования градусов в радианы используется функция РАДИАНЫ, которая имеет следующий синтаксис:

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

Здесь - угол - это число, представляющее собой угол, измеренный в градусах. Например, следующая формула возвращает значение 180:

=ГРАДУСЫ(3,14159)

В то же время следующая  формула возвращает значение 3,14159:

=РАДИАНЫ(180)

Функция SIN

Функция SIN возвращает синус  угла и имеет следующий синтаксис:

=SIN(число)

Здесь число - угол в радианах.

Функция COS

Функция COS возвращает косинус  угла и имеет следующий синтаксис:

=COS(число)

Здесь число - угол в радианах.

Функция TAN

Функция TAN возвращает тангенс угла и имеет следующий синтаксис:

=TAN(число)

Здесь число - угол в радианах.

1. СИСТЕМЫ ЛИНЕЙНЫХ  АЛГЕБРАИЧЕСКИХ УРАВНЕНИЙ

Некоторые задачи сводятся к решению систем линейных уравнений. Систему [1] вида

 

(1)

 

принято называть системой n линейных алгебраических уравнений (СЛАУ) с n неизвестными. При этом произвольные числа a_ij (i = 1, 2,…, n; j = 1, 2,…, n) называются коэффициентами системы (коэффициентами при неизвестных), а числа b_i (i = 1, 2,…, n) – свободными членами. Такая форма записи (1) алгебраической линейной системы называется нормальной. Решением СЛАУ (1) называется совокупность чисел x_i (i = 1, 2,…, n), при подстановке которых в систему каждое из ее уравнений обращается в тождество.

Систему (1) можно записать в матричной форме

 

A ´ X = B,

(2)

 

где A – матрица коэффициентов при неизвестных (матрица системы):

 

(3)

 

X – вектор-столбец неизвестных X = (x₁, x₂, …, x_n)^T:

 

(4)

 

B – вектор-столбец свободных членов:

 

(5)

 

или B = (b₁, b₂,..., b_n)^T. Целое число n называется размерностью системы.

Система (2) может быть записана в развернутом виде

 

(6)

 

Система уравнений (6) называется совместной, если она имеет хотя бы одно решение, и несовместной – в противном случае. Совместная система (6) называется определенной, если она имеет единственное решение, и неопределенной, если она имеет больше одного решения.

2. РЕШЕНИЕ ЛИНЕЙНЫХ  АЛГЕБРАИЧЕСКИХ СИСТЕМ

Вопросы исследования СЛАУ (6) на совместность оставим за пределами нашего рассмотрения. Все же методы решения линейных алгебраических систем принято делить на два класса: прямые и итерационные. Прямыми называются методы решения СЛАУ, которые приводят к решению за конечное число арифметических операций. В случае точной реализации операций решение будет точным, поэтому и прямые методы иногда называют точными. Итерационными методами называют такие методы, в которых точное решение может быть получено за счет реализации многократно повторяющихся действий (итераций). Эффективность способов решения системы (6) во многом определяется свойствами матрицы (3) A (размерностью, симметричностью, заполненностью и т.д.). MS Excel располагает рядом возможностей для работы с матрицами.

2.1. Табличные формулы и операции с матрицами

Табличные формулы или формулы массива – очень мощное вычислительное средство Excel, позволяющее работать с блоками рабочего листа как с отдельными ячейками. Табличные формулы в качестве результата возвращают массив значений. Поэтому перед вводом такой формулы необходимо выделить диапазон ячеек, куда будут помещены результаты. Потом набирается сама формула. Ввод ее в выделенный диапазон ячеек осуществляется нажатием комбинации клавиш Ctrl+Shift+Enter. Это принципиально. Формула вводится во все ячейки выделенного интервала. При активизации любой ячейки из интервала, содержащего формулу массива, в строке формул отображается введенная формула, заключенная в фигурные скобки. Именно фигурные скобки являются признаком табличной формулы. Для выделения всего блока, содержащего табличную формулу, необходимо выделить одну из его ячеек, после чего нажать комбинацию клавиш Ctrl+/. Невозможно редактировать содержимое только одной ячейки из интервала с табличной формулой. Изменить можно только весь блок целиком, для чего он и должен быть предварительно выделен.

Например, пусть необходимо сложить две матрицы размера 3´3. Элементы первой матрицы (9 элементов) разместим в интервале A1:C3, второй – в диапазоне E1:G3. Под результат выделим интервал A5:C7. После чего, не снимая выделения, введем формулу =A1:C3+E1:G3, нажав комбинацию клавиш Ctrl+Shift+Enter. В ячейках интервала A5:C7 отобразится результат – сумма соответствующих элементов матриц, а в строке формул мы увидим {=A1:C3+E1:G3}. Пусть вместо сложения нам надо умножить первую матрицу на число 2. Для этого перемещаемся внутрь интервала A5:C7, выделяем его, нажав комбинацию Ctrl+/, вносим в формулу исправления =A1:C3*2, вводим ее нажатием Ctrl+Shift+Enter. В интервале A5:C7 увидим результат умножения, а в строке формул – табличную формулу {=A1:C3*2}.

К простейшим операциям  с матрицами принято относить следующие [2]: сложение и вычитание матриц, умножение и деление матрицы на число, перемножение матриц, транспонирование, вычисление обратной матрицы. Умножение (деление) матрицы на число, сложение (вычитание) матриц в Excel реализуются достаточно просто: с помощью обычных формул (поэлементное сложение или вычитание, умножение или деление на число), либо с использованием табличных формул, как это было описано выше. Для остальных матричных операций в Excel предусмотрены функции рабочего листа из категории «Арифметические и тригонометрические функции» [3]:

1) МОПРЕД(матрица) – вычисление определителя матрицы,

2) МОБР(матрица) – вычисление обратной матрицы,

3) МУМНОЖ(матрица1;матрица2) – произведение матриц,

4) ТРАНСП(матрица) – транспонирование матрицы.

Первая из этих функций  в качестве результата возвращает число (определитель матрицы), поэтому вводится как обычная формула (Enter). Последние три возвращают блок ячеек, поэтому должны вводиться как табличные формулы (Ctrl+Shift+Enter).

2.2. Группировка рабочих  листов

Рассмотрим задачу решения  СЛАУ на следующем примере

 

(7)

 

Т.е. будем решать систему  из трех алгебраических уравнений относительно трех неизвестных. Размерность системы (7) n=3, матрица системы A (3) размерности 3´3 имеет вид

 

(8)

 

а вектор-столбец свободных  членов (5) B=(-24, -48, 18)^T.

Попытаемся решить СЛАУ (7) в среде MS Excel тремя различными способами. Для чего создадим рабочую книгу из трех листов и назовем ее Решение СЛАУ.xls. Поскольку исходные данные для трех различных способов решения (а значит и трех рабочих листов книги) одни и те же (матрица системы A (8) и вектор-столбец свободных членов B), то неплохо было бы их одновременно ввести в эти рабочие листы. Excel предоставляет такую возможность. Этот инструмент называется группировкой рабочих листов [4]. Для того, чтобы применить средство Группа, необходимо выделить группируемые рабочие листы, щелкнув первый рабочий лист (Лист1), на котором будут вводиться данные, а затем, удерживая клавишу Ctrl, щелкнуть ярлычки листов (Лист2 и Лист3), куда одновременно должны вводиться те же самые данные. Либо, если группируемые рабочие листы расположены подряд, как в нашем случае, при выделенном первом (Лист1) щелкнуть, удерживая нажатой клавишу Shift, на ярлычке последнего (Лист3). После этого можно вводить данные на текущем рабочем листе, они автоматически появятся в одноименных ячейках на всех остальных сгруппированных листах. Признаком группировки нескольких листов является появившееся в строке заголовка слово [Группа] ([Group]), заключенное в квадратные скобки (рис. 1). После ввода группировку необходимо отменить. Для отмены необходимо выбрать любой из листов, не входящих в группу, либо щелкнуть правой кнопкой мыши на любом ярлычке листа из группы и выполнить команду Разгруппировать листы.

Для решения рассматриваемой СЛАУ (7) сгруппируем листы (Лист1:Лист3), разместим в ячейках текущего листа (Лист1) A1, B2, D2, A6:A9 соответствующие поясняющие тексты (заголовки), в интервале A3:C5 – элементы матрицы A (8), а в интервале D3:D5 – элементы вектора B. Интервал B7:B9 зарезервируем под искомое решение – вектор X (4). После этих манипуляций все три рабочих листа примут одинаковый вид (рис. 1). Перед дальнейшей работой не забудьте разгруппировать рабочие листы.

2.3. Метод Крамера

Метод Крамера большинству  известен еще со школьной скамьи. Решение СЛАУ (6) находится по формулам Крамера

 

(9)

 

где det A = ïAï– определитель матрицы (3) системы (главный определитель),  
det A_i = ïA_iï (i = 1, 2, …, n)– определители матриц A_i (вспомогательные определители), которые получаются из A заменой i-го столбца на столбец свободных членов B (5). Линейная алгебраическая система несовместна (не имеет решений), если det A=0. Для рассматриваемой СЛАУ (7) вспомогательные матрицы имеют следующий вид

 

(10)

 

Разместим их на рабочем листе (рис. 2). Причем сделаем это не путем простого копирования соответствующих значений, а вводом формул с использованием абсолютных ссылок (рис. 3) на элементы матрицы A из интервала A3:C5 и элементы вектора B из интервала D3:D5 (рис. 1). Во-первых, это ускорит процесс ввода матриц A_i (i = 1, 2, 3) (формулы введем только в интервал A11:C13 матрицы A₁ и в интервал E11:E13 первого столбца матрицы A₂, далее же будем их блоками только копировать: A11:A13 в F11:F13 и в K11:K13, B11:B13 в J11:J13, C11:C13 в G11:G13, E11:E13 в I11:I13). Во-вторых, это сделает проектируемую таблицу универсальной в том смысле, что можно будет изменять только исходные данные (матрицу системы A в интервале A3:C5 и вектор-столбец свободных членов B в D3:D5), а все остальное (в том числе и решение СЛАУ) будет автоматически вычисляться.

Далее, воспользовавшись функцией МОПРЕД(матрица), вычислим определители всех матриц (рис. 4). Аналогичная формула (=МОПРЕД(A3:C5)) для вычисления определителя матрицы A записана в ячейку E8. Осталось по формулам Крамера (9) найти решение системы (7). Соответствующие формулы Excel запишем в интервал решения B7:B9 (рис. 5), в котором и увидим результат (рис. 6). Обратите внимание на то (рис. 5), что при вычислении x_i (i = 1, 2, 3) анализируется значение определителя матрицы системы A, вычисленное в ячейке E8, и, если оно равно нулю (система несовместна), то в B7 помещается текст «Решения нет», а в ячейки B8 и B9 – пустые строки.

2.4. Матричный способ  решения

Матричный способ решения  СЛАУ (6) достаточно прост. Обе части матричного равенства (2) умножим слева на обратную матрицу А^-1. Получим A^-1´A´X=A^-1´B. Т.к. A^-1´A=E, где E – единичная матрица (диагональная матрица, у которой по главной диагонали расположены единицы). Тогда решение системы (2) запишется в следующем виде

 

X = A-1 ´ B.

(11)

 

Т.е. для решения системы (2) (вычисления вектора-столбца X (4)) необходимо найти для матрицы A (3) обратную A^-1 и умножить ее справа на вектор-столбец B (5) свободных членов. Для чего, воспользовавшись функциями Excel МУМНОЖ(матрица1;матрица2) и МОБР(матрица), введем в интервал B7:B9 следующего рабочего листа (Лист2) табличную, т.е. используя для ввода комбинацию Ctrl+Shift+Enter, мегаформулу =МУМНОЖ(МОБР(A3:C5);D3:D5). После чего в строке формул увидим {=МУМНОЖ(МОБР(A3:C5);D3:D5)}, а в интервале B7:B9 – решение, точно такое же, как и в предыдущем случае (рис. 6).

2.5. Поиск решения

Широкий класс экономических задач составляют задачи оптимизации. Задачи оптимизации предполагают поиск значений аргументов, доставляющих функции, которую называют целевой, минимальное или максимальное значение при наличии каких-либо дополнительных ограничений. MS Excel располагает мощным средством для решения оптимизационных задач. Это инструмент-надстройка, который называется Поиск решения (Solver) [5]. Поиск решения доступен через меню Сервис/Поиск решения.

Задачу решения СЛАУ (1) можно свести к оптимизационной задаче. Для чего одно из уравнений (например, первое) взять в качестве целевой функции, а оставшиеся n-1 рассматривать в качестве ограничений. Запишем систему (1) в виде

 

(12)

 

Тогда задача оптимизации  для Поиска решения может звучать следующим образом. Найти значения X = (x₁, x₂, …, x_n)^T, доставляющие нуль функции, стоящей слева в первом уравнении системы (12), при n-1 ограничениях, представленных оставшимися уравнениями.

Для решения этой задачи необходимо записать выражения (формулы) для вычисления значений функций, стоящих слева  в уравнениях системы (12). Отведем под эти формулы интервал C7:C9 текущего рабочего листа (Лист3). В ячейку C7 введем формулу =A3*$B$7+B3*$B$8+C3*$B$9-D3 и скопируем ее в оставшиеся C8 и C9. В них появятся соответственно =A4*$B$7+B4*$B$8+C4*$B$9-D4 и =A5*$B$7+B5*$B$8+C5*$B$9-D5. Осталось, обратившись к пункту меню Сервис/Поиск решения, в окне диалога (рис. 7) задать параметры поиска (установить целевую ячейку C7 равной нулю, решение в изменяемых ячейках B7:B9, ограничения заданы формулами в ячейках C8 и С9). После щелчка по кнопке Выполнить в интервале B7:B9 получим результат (рис. 8) – решение СЛАУ (7).