Примеры простейших функций с массивами

• =МАКС(A11:F11) возвращает максимум из диапазона A11 : F11

• =МИН (A11:F11) возвращает минимум из диапазона A11 : F11

(аргументы данных функций можно задать не только с помощью диапазонов:

=МАКС(A11:F11; D30; 125) возвращает максимум из A11, B11, … F11, D30, 125

Функции МАКС, МИН используют до 30 аргументов).

• Пример – умножение матриц С = A*B

1,0	0,0	0,0	0,0		3,0	2,0		3	2
0,0	1,0	0,0	0,0	*	5,0	4,0	=	5	4
0,0	0,0	1,0	0,0		7,0	6,0		7	6
					9,0	8,0

Формулу в этом примере необходимо ввести как формулу массива

=МУМНОЖ ( A30:D32 ; F30:G33 )

в ячейку I30 – левая верхняя ячейка результата. Чтобы распространить результат на весь диапазон для матрицы I (I30 – J32 ) следует нажать клавишу F2, а затем нажмите клавиши CTRL+SHIFT+ENTER.

Вложенные функции

В некоторых случаях может потребоваться использование функции как одного из аргументов другой функции. Такая вложенная функция должна иметь результат, тип которого соответствует типу аргумента . Например, если аргумент должен быть логическим, то есть ИСТИНА либо ЛОЖЬ, то вложенная функция тоже должна возвращать логическое значение, иначе появится сообщение об ошибке «#ЗНАЧ!».

В формулах можно использовать до семи уровней вложения функций. Когда функция Б является аргументом функции А(Б( …)) , то функция Б() находится на втором уровне вложенности.

Ссылки в формулах. Стиль ссылок A1

Ссылка указывает на ячейку или диапазон ячеек, которые требуется использовать в формуле. При помощи ссылок можно:

• использовать в одной формуле данные, находящиеся в разных частях листа,

• использовать в разных формулах значение одной ячейки.

• задавать ссылки на ячейки других листов той же книги и даже другие книги ( Ссылки на ячейки других книг называются связями).

По умолчанию Microsoft Excel использует стиль ссылок A1, определяющий столбцы буквами (от A до IV, всего не более 256 столбцов), а строки номерами (от 1 до 65536). Эти буквы и номера называются заголовками строк и столбцов. Для ссылки на ячейку введите букву столбца, а следом номер строки. Например, ссылка B2 указывает на ячейку, расположенную на пересечении столбца B и строки 2.

Примеры заданий ячеек или диапазонов

Ячейку в столбце A и строке 10 A10

Диапазон ячеек: столбец А, строки 10-20. A10:A20

Диапазон ячеек: строка 15, столбцы B-E. B15:E15

Все ячейки в строке 5. 5:5

Все ячейки в строках с 5 по 10. 5:10

Все ячейки в столбце H. H:H

Все ячейки в столбцах с H по J. H:J

Диапазон ячеек: столбцы А-E, строки 10-20. A10:E20

Ссылка на другой лист в той же книге

Синтаксис ссылки на другой лист следующий:

имя_листа!ссылка_на_ячейку_или_диапазон

Пример

= СРЗНАЧ ( Маркетинг!A10:E20 )

В приведенном ниже примере функция СРЗНАЧ используется для расчета среднего значения в диапазоне A10:E20 на листе «Маркетинг» в той же самой книге. Обратите внимание на то, что имя листа и восклицательный знак (!) предшествуют ссылке на диапазон ячеек.

Ссылка на другой лист в другой книге

[имя_книги.xsl]имя_листа!ссылка_на_ячейку_или_диапазон

Относительные и абсолютные ссылки

Абсолютная ссылка ячейки в формуле, например $A$1, всегда ссылается на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов абсолютная ссылка не корректируется. По умолчанию в новых формулах используются относительные ссылки, и для использования абсолютных ссылок надо выбрать соответствующий параметр. Например, при копировании абсолютной ссылки из ячейки B2 в ячейку B3, она остается прежней =$A$1.

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

№ строк	i1	. . .	I2		i2+k
n	Данные		Формула(n i1)		Формула(n i1 + k)
. . .
n+m			Формула(n+m i1)		Формула(n+m i1-k)

Копирование формул по вертикали

Копирование формул по горизонтали,

Смешанные ссылки. Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка столбцов приобретает вид $A1, $B1 и т. д. Абсолютная ссылка строки приобретает вид A$1, B$1 и т. д. При изменении позиции ячейки, содержащей формулу, относительная ссылка изменяется, а абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется.

Пример

Построить график функции Z = x^2 + y^2 для -5<= x <= 5, -5<= y <= 5. Для построения графика следует заполнить двумерный массив - таблицу, содержащую значения x, y. При выборе шага hx = hy =0.5 число точек по горизонтали и вертикали равно 26, следовательно, общее число точек 676. Как заполнить эту таблицу?

	A	B	C	D
10		-5,0	-4,6	-4.2	. . .	5,0
11	-5,0	=B$10^2+$A11^2	=C$10^2+$A11^2
12	-4,6	=B$10^2+$A12^2	=C$10^2+$A12^2
. . .	. . .
36	5.0

Ячейки В10, C10, D10 . . . - значения аргумента х.

Ячейки A11, A12, A13 . . . - значения аргумента y.

Значения функции Z находятся в рядах 11, 12, 13, . . . 36 и столбцах B, C, D, … .

Функция зависит только от аргументов X, Y, следовательно, в формулах должны находиться только значения В10, C10, D10 . . . и A11, A12, A13 . . . В ячейку B11 заносим формулу:

=B$10^2 +$A11^2

Если данная формула «протягивается» вертикально вниз или горизонтально вправо, то:

- абсолютная часть адреса B10, т.е. ряд 10 не изменяется

( при протягивании вниз всегда остается B10, при протягивании вправо получаем В10, C10, D10 )

- абсолютная часть адреса A11, т.е. столбец A не изменяется.

( при протягивании вправо всегда остается A11, при протягивании вниз получаем - A11, A12, A13 )