Методические указания по выполнению лабораторной работы

Решение задач с векторно-матричными операциями и преобразованиями

Функции Microsoft Excel для выполнения векторноматричных оперций

Математические модели (ММ), используемые при решении большого числа инженерных задач, как правило приводятся к формату систем линейных алгебраических уравнений (СЛАУ).

Массив - это основной вид информационной структуры, используемой для записи и хранения векторов и матриц, входящих в представления ММ исследуемых систем.

Основными методами решения задач с ММ, представленными в виде СЛАУ, являются различные векторно-матричные преобразования.

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

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

Так в категории математических функций находятся функции используемые для выполнения операций над матрицами:

1.МОБР(массив) - возвращающая массив обратной матрицы,

2.МОПРЕД(массив) - возвращает значение определителя матрицы,

3.МУМНОЖ(массив1;массив2) - возвращает матричное произведение двух массивов размерности (n*m) на (m*n). Число столбцов n первой из умножаемых матриц должно равняться числу строк второй матрицы. В результате выполнения этой функции формируется массив, число строк в котором равно числу строк в массиве1, а число столбцов равно числу столбцов в

массиве2.

4.СУММ(арг1[; арг2; ... ; арг30]) - Функция СУММ принимает от 1 до 30 аргументов (в Office 2010 — до 255) и возвращает их сумму. В качестве аргументов можно передавать адреса диапазонов (что чаще всего и делается), в этом случае суммируются все числа в диапазоне.

1	Любимов Е.Б.

Методические указания по выполнению лабораторной работы

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

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

•критерий: Условие для суммирования, на соответствие которому проверяется каждая ячейка из проверяемого диапазона. Если необходимо использовать операцию сравнения, то "логическое выражение" указывается без левого операнда и заключается в двойные кавычки (например, ">=100" — суммировать все числа, большие 100). Также можно использовать текстовые значения (например, "яблоки" — суммировать все значения, находящиеся напротив текста "яблоки") и числовые (например, 300 — суммировать значения

в ячейках, значения в которых 300).

•диапазон_суммирования: Необязательный аргумент, используется тогда, когда проверяемый диапазон и диапазон суммирования находятся в разных диапазонах. Если он не указан, то в качестве диапазона суммирования используется проверяемый диапазон (первый аргумент). Если он указан, то суммируются значения из ячеек этого диапазона, находящиеся "напротив" соответствующих ячеек проверяемого диапазона.

Рис. 1. Исходные данные для обработки

2	Любимов Е.Б.

Методические указания по выполнению лабораторной работы

Рис. 2. Результаты обработки

6. СУММЕСЛИМН(диапазон_суммирования;диапазон_условий1; условие1[;диапазон_условий2; условие2]…) -

•диапазон_суммирования - обязательный аргумент,

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

•диапазон_условия1 - обязательный аргумент. Первый диапазон,

вкотором проверяется условие1.

•условие1 - обязательный аргумент. Условие в виде числа, выражения, ссылки на ячейку илитекста, определяющее,

какие ячейки в диапазоне_суммирования1 будут просуммированы. Условие может быть представлено в следующем виде: 32, ">32", B4, "яблоки" или "32".

•диапазон_условия2,условие2,… - необязательные аргументы,

определяющие дополнительные условия для них.

Разрешается использовать до 127 пар диапазонов и условий.(1 )

7. СУММКВ(арг1[,арг2] [,...]) – возвращает сумму квадратов аргументов. Аргументами могут быть числа, массивы, имена или ссылки на ячейки, содержащие числа;

(1) Справка: Excel - СУММЕСЛИМН() ДомашняястраницаExcel 2010

3	Любимов Е.Б.

Методические указания по выполнению лабораторной работы

8.СУММКВРАЗН(массив1,массив2) – вычисляет сумму квадратов разностей соответствующих элементов двух массивов;

9.СУММПРОИЗВ(массив1,массив2[,...]) - перемножает соответствующие элементы заданных массивов и возвращает сумму произведений;

10.СУММРАЗНКВ(массив1,массив2) - Вычисляет сумму разностей квадратов соответствующих элементов двух массивов;

11.СУММСУММКВ(массив1,массив2) - возвращает сумму сумм квадратов значений, записанных в соответствующие ячейки двух массивов.

Статистические функции

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

12.СРЗНАЧ(арг1[,арг2] [,...]) - возвращает среднее (среднее арифметическое) значение аргументов.

13.СРГАРМ(арг1[,арг2] [,...]) - возвращает среднее гармоническое множества данных. Среднее гармоническое - это числовое значение, обратное среднему арифметическому обратных

величин:	1		=	1	∑	1	.
	H			n
		Y				Y
						i

Пример:

Рис. 3. Отображение формул

4	Любимов Е.Б.

Методические указания по выполнению лабораторной работы

Рис.4. Результаты вычислений по формуле СРГАРМ

14.СРГЕОМ(арг1[,арг2] [,...]) - средним геометрическим нескольких положительных вещественных чисел называется число, которым можно заменить каждое из этих чисел так, чтобы их произведение не изменилось. Более формально:

Среднее геометрическое двух чисел также называется их

средним пропорциональным.

15. СРОТКЛ(арг1[,арг2] [,...]) - возвращает среднее значение абсолютных отклонений точек таблицы исходных данных от среднего значения, вычисленного для этой таблицы. СРОТКЛ является оценкой разброса множества данных от среднего

значения. Вычисляетсяпоформуле:

1

∑

x −

,

=

1

n

x .

x

x

i

где

∑

i

n

n i=1

Рис. 5. Отображение формул для примера вычисления СРОТКЛ()

5	Любимов Е.Б.

Методические указания по выполнению лабораторной работы

Рис. 6. Результаты вычислений по формуле СРОТКЛ()

16.ДИСП(арг1[,арг2] [,...]) - оценивает дисперсию по выборке

17.ДИСПА - функция ДИСПА возвращает выборочную дисперсию для выборки, значения которой расположены на рабочем листе Excel, и для определения которой служат аргументы ДИСПА.

Функция ДИСПА отличается от ДИСП только способом обработки ячеек, которые содержат логические значения или текстовые строки.

ДИСПА интерпретирует логическое значение ИСТИНА как число 1, ЛОЖЬ как 0, а текстовую строку как число 0; пустые ячейки игнорируются. Такая интерпретация характерна также для функций СЧЁТЗ, СРЗНАЧА и ДИСПРА.

При использовании функции ДИСП ячейки, содержащие логические значения и тестовые строки, игнорируются. Пустые ячейки также игнорируются. Такая интерпретация характерна также для функций СЧЁТ, СРЗНАЧ и ДИСПР.

Корпорация Microsoft рекомендует использовать функцию ДИСП, кроме случаев, когда необходима интерпретация логических значений и текстовых строк в стиле функции ДИСПА. Выборочная дисперсия, как правило, вычисляется для цифровых данных с помощью функции ДИСП.

18.ДИСПР (VARP) - Функция ДИСПР (VARP) возвращает дисперсию генеральной совокупности для генеральной совокупности, значения которых находятся в электронной

6	Любимов Е.Б.