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

Мастер функций

Программа Excel включает большую библиотеку встроенных функций, доступ к которой открывается после нажатия кнопки Вставка функций. Появляется окно Мастер функций. Встроенные функции собраны в разделах: финансовые, математические, статистические и др. Например, раздел математических функций включает прямые и обратные тригонометрические функции, логарифмические и показательные функции и т.д.

После выбора нужной функции и нажатия на кнопку <Enter> открывается второе окно мастера функций, в котором следует указать адреса ячеек, содержащих значения аргументов выбранной функции. Окно содержит поля для ввода аргументов, а также краткое описание функции. Адреса ячеек можно либо непосредственно вписать в поля ввода, либо ввести их щелчком мыши в нужных ячейках. Чтобы открылся доступ ко всем ячейкам таблицы окно Мастера функций можно уменьшить либо передвинуть с помощью мыши. Для уменьшения окна нужно щелкнуть на кнопке, расположенной справа в поле ввода аргумента, - окно уменьшится до размеров поля ввода. Теперь можно выбрать нужные ячейки. Перечень выделенных ячеек отображается в поле ввода первого аргумента и строке формул. После выбора ячеек диалоговое окно можно снова увеличить, используя кнопку, расположенную справа в поле аргумента уменьшенного диалогового окна. Для завершения ввода функции следует щелкнуть на кнопке ОК.

В случае функции нескольких аргументов процедура ввода адресов ячеек повторяется для каждого аргумента.

Созданные таким образом формулы, включающие в себя функции, появляются в строке формул.

Генерация случайных чисел

Для получения случайных чисел, равномерно распределенных на отрезке [0,1], служит функция СЛЧИС.

Функция СЛУЧМЕЖДУ( , ) позволяет получать целые числа, принимающие случайные значения в диапазоне от нижней границы до верхней. Например, если в качестве границ задать числа 0 и 1, то функция СЛУЧМЕЖДУ с равной вероятностью выдаст либо 0, либо 1. Отметим, что функция СЛУЧМЕЖДУ доступна, если в Excel установлен Пакет анализа; при этом Пакет анализа должен быть подключен в диалоговом окне Надстройки, вызываемым из раздела меню Сервис.

Последовательность из равновероятных нулей и единиц можно получить также с помощью функции СЛЧИС, если выбрать для ячеек числовой формат без дробной части. Например, впишем функцию СЛЧИС в ячейки блока размером 4x4. Выберем числовой формат без дробной части. Блок заполнится числами 0 и 1. Нажимая на клавишу F9 – подавая тем самым команду на проведение пересчета, сможем видеть, как меняется в блоке расположение нулей и единиц.

Создавать массивы случайных чисел с разными распределениями можно, используя команды диалогового окна Генерация случайных чисел. Это окно открывается из раздела меню Сервис – Анализ данных.

Табличные формулы

Табличные формулы - это формулы для обработки массивов.

Вернемся вновь к примеру 2: Расчет расходов на содержание парка автомобилей. Обозначим номера строк буквой i; строки с числовыми данными имеют номера 3, 4 и 5. В последнем столбце таблицы - в ячейках Fi - записаны результаты вычислений по формулам: Bi+Ci+Di*Ei. Фактически в столбце F3:F5 записан вектор - результат операций с векторами: B3:B5, C3:C5, D3:D5 и E3:E5. Можно ли описать эти операции одной общей формулой?

Для решения этой задачи выделим блок F3:F5. Наберем знак равенства. Выделим блок (вектор) B3:B5. Прибавим вектор C3:C5, т.е. напишем знак "+" и выделим блок C3:C5. Аналогично прибавим затем вектор D3:D5, поэлементно умноженный на вектор E3:E5. (С клавиатуры вводим только знаки "+" и "*", а векторы просто выделяем мышкой.

Завершаем ввод формулы одновременно нажав клавиши: <Ctrl>+<Shift>+<Enter>. В ячейках F3:F5 появляются искомые результаты, а в строке формул - формула в фигурных скобках: {=B3:B5+C3:C5+D3:D5*E3:E5}. Если поочередно выделить ячейки F3:F5, то увидим, что эта формула содержится в каждой из ячеек. Фигурные скобки означают, что это - табличная формула. Эти скобки нельзя набирать вручную (формула будет воспринята как текст).

Можно сделать формулу более наглядной, если для векторов ввести имена. Например, обозначим:

VB - блок (вектор) B3:B5;

VC - блок C3:C5;

VD - блок D3:D5;

VE - блок E3:E5.

Для назначения имени выделяем блок, в меню Вставка выбираем команды Имя - Присвоить, вписываем в поле ввода нужное имя и нажимаем на кнопку OK.

С

Таблица квадратов, кубов и логарифмов по основанию 2
n	n^2	n^3	Log2(n)
1	1	1	0
2	4	8	1
3	9	27	1.585
4	16	64	2
5	25	125	2.322

отрем предыдущую формулу: выделим блок F3:F5 и нажмем на клавишу <Del>. Введем знак равенства. Нажмем на функциональную клавишуF3. Появится диалоговое окно Вставка имени. Выбираем имя VB и щелкаем на OK. Пишем знак "+", снова нажимаем на F3, вводим имя VC и т.д. Заканчиваем ввод формулы, одновременно нажав на клавиши: <Ctrl>+<Shift>+<Enter>. Получаем более компактную формулу:

{=VB+VC+VD*VE}.

Пример 3. В качестве следующего примера построим таблицу квадратов, кубов и двоичных логарифмов натуральных чисел. Пусть таблица строится в блоке A1:D7. В двух верхних строчках пишем заголовки всей таблицы и отдельных столбцов. Используя описанные выше приемы, заполняем первый столбец натуральными числами от 1 до 5 и вписываем в последующие столбцы формулы: {=A3:A7^2}, {=A3:A7*B3:B7}, {=LOG(A3:A7,2)}. В последней формуле число 2 - это основание логарифмов. Эту формулу вводим с помощью мастера функций, заканчивая ввод, как обычно, одновременным нажатием клавиш <Ctrl>+<Shift>+<Enter>. Получаем в итоге требуемую таблицу.

Оказывается, что, если выделить построенную таблицу и потянуть мышкой за правый нижний угол, таблицу можно расширить на последующие значения ряда натуральных чисел: 6, 7, 8 и т.д.

Матричные функции

П

MB
1	0
1	2

MB+MB1
2	0
0.5	2.5

MB1
1	0
-0,5	0.5

МУМНОЖ (MB,MB1)
1	0
0	1

рограмма Excel предоставляет возможности работы с двумерными массивами - матрицами. В качестве примера создадим матрицу 2 на 2 и произведем с ней некоторые операции. Заполним числами квадратный блок и присвоим блоку - матрице - имя MB. (Последовательность действий: заполняем блок числами, выделяем его, в менюВставка выбираем команды Имя - Присвоить, вписываем имя и нажимаем на OK.)

Найдем матрицу, обратную к матрице MB.. Для вычисления обратной матрицы выделяем новый блок размером 2 на 2 и обращаемся к мастеру функций. Вписываем в строку ввода функцию {=МОБР(MB)}; заканчиваем ввод одновременным нажатием клавиш <Ctrl>+<Shift>+<Enter>. Введем для обратной матрицы имя MB1

Теперь мы имеем две матрицы. Найдем сумму и произведение матриц. Для вычисления суммы выделим новый блок размером 2 на 2 и введем в него табличную формулу: {=MB+MB1}.

По определению произведение прямой матрицы на обратную равно единичной матрице. Для вычисления матричного произведения вновь обратимся к мастеру функций; нужная формула имеет вид: {=МУМНОЖ(MB, MB1)}. По окончании ввода получаем ожидаемый результат.

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

Кроме матричного произведения по правилу "строка на столбец" программа Excel позволяет вычислять поэлементное произведение. Для двух матриц одинаковых размеров MA1 и MA2, не обязательно прямоугольных, поэлементное произведение описывается формулой: {=MA1*MA2}.