8. Работа с массивами

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

Для операций с массивами в библиотеке Microsoft Excel достаточно большой набор функций. Рассмотреть все функции этого класса в рамках данного пособия вряд ли возможно, поэтому рассмотрим несколько наиболее распространённых, разбив их на две группы.

Функции первой группы возвращают ответ в виде числа, например:

• СУММ(число1;число2;...)

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

• МИН (число1;число2; ...) ...) или МАКС(число1;число2; ...).

Функции второй группы возвращают ответ в виде массива чисел. Для того чтобы в ответе получился массив, а не одно число, нужно выполнить следующие действия:

1. выделить область такого размера, какого должен быть массив, выдаваемый в качестве ответа, например, А5:С7, если ответом будет матрица из трёх строк и трёх столбцов;

2. задать аргументы, необходимые для правильной работы выбранной функции;

3. при завершении создания функции вместо клавиши «Enter» или кнопки «ОК» одновременно нажать на три клавиши: «Ctrl» + «Shift» + «Enter» . Лучше всего сделать это так: пальцами одной руки нажать на две клавиши «Ctrl» + «Shift» и, не отпуская их, пальцем другой руки нажать на клавишу «Enter».

Наиболее распространёнными функциями этой группы являются функции, позволяющие работать с матрицами, например:

• МОБР(массив).

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

Приведём описание функций первой группы.

СУММ(число1;число2; ...).

Число1, число2, .. - это от 1 до 30 аргументов, для которых требуется определить итог или сумму. В качестве аргумента могут использоваться: числа, логические значения и текстовые представления чисел, непосредственно введенные в список аргументов, массивы, ссылки на одну или блок ячеек. В последнем случае суммируются только числа, а пустые ячейки, логические значения, тексты и значения ошибок в массиве или ссылке игнорируются. Причём, аргументы, которые являются значениями ошибки или текстами, не преобразуемыми в числа, вызывают ошибки. Например:СУММ(3; 2)равняется 5 ,аСУММ("3"; 2; ИСТИНА)равняется 6, так как текстовые значения преобразуются в числа, а логическое значение ИСТИНА - в число 1. Если ячейки A2:E2 содержат числа 5, 15, 30, 40 и 50, тоСУММ(A2:C2)равняется 50, аСУММ(B2:E2; 15)равняется 150

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

Массив1, массив2, массив3, ... - это от 2 до 30 массивов, чьи компоненты нужно перемножить, а затем сложить. Массивы, используемые в качестве аргументов, должны иметь одинаковые размерности. Если это не так, то функция СУММПРОИЗВ возвращает ошибку #ЗНАЧ!. Нечисловые элементы массивов трактует как нулевые. Например, перемножить компоненты двух массивов(Рисунок 26), можно, используя следующие варианты:

СУММПРОИЗВ(A1:B3;D1:E3) илиСУММПРОИЗВ({3;4:8;6:1;9};{2;7:6;7:5;3}).

В обоих случаях вычисления производятся по формуле: 3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3, а результат равняется 156

МИН (число1;число2; ...) или МАКС(число1;число2; ...).

Ч

	A	B	C	D	E
1	3	4		2	7
2	8	6		6	7
3	1	9		5	3

Рисунок 26 - Массивы

исло1, число2, ... - это от 1 до 30 аргументов, среди которых ищется минимальное или максимальное значение. Можно задавать аргументы, которые являются числами, пустыми ячейками, логическими значениями или текстовыми представлениями чисел. Аргументы, которые являются значениями ошибки или текстами, не преобразуемыми в числа, вызывают появление ошибок. Если аргумент является массивом или ссылкой, то в нем учитываются только числа. Пустые ячейки, логические значения или текст в массиве или ссылке игнорируются. Если аргументы не содержат чисел, то функция возвращает 0 (ноль). Например, если используются данные)двух массивов(Рисунок 26),то МАКС(A1:В3) равняется 9, МАКС(A1:В3;30) равняется 30, а Мин(A1:В3;D1:E3) равняется 1.

СУММЕСЛИ(диапазон; критерий; диапазон_суммирования) - вычисляет сумму значений ячеек, удовлетворяющих заданному критерию, в приведенном диапазоне или диапазоне суммирования.Диапазон— диапазон ячеек, где проверяется критерий.Критерий— критерий в форме числа, выражения или текста, определяющего суммируемые ячейки. Например, критерий может быть выражен как 32, "32", ">32", "яблоки".Диапазон_суммирования— фактические ячейки для суммирования. Ячейки в «диапазон_суммирования» суммируются, только если соответствующие им ячейки в аргументе «диапазон» удовлетворяют критерию. Если «диапазон_суммирования» опущен, то суммируются ячейки в аргументе «диапазон».

Например, функция =СУММЕСЛИ(A2:A5;">160000";B2:B5)- вычисляет количество ячеек, удовлетворяющих заданному критерию, в приведенном диапазоне.

СЧЁТЕСЛИ(диапазон; критерий) - вычисляет количество ячеек удовлетворяют критерию

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

Например, критерий может быть выражен следующим образом: 32, "32", ">32", "яблоки", а функция =СЧЕТЕСЛИ(A2:A5;"яблоки")вычисляет количество ячеек с текстом «яблоки» в приведенном диапазоне

ГПР(искомое_значение; инфо_таблица; номер_строки; интервальный_просмотр).

О

	A	B	C
1	Оси	Подшипники	Болты
2	4	4	9
3	5	7	11
4	6	8	10

Рисунок 27 - Перечень автомобильных деталей

существляет поиск «искомого значения» в верхней строке таблицы (массива) и возвращает значение элемента в том же столбце из строки, номер которой задан. Функция ГПР используется, когда сравниваемые значения расположены в верхней строке таблицы данных, а возвращаемые значения расположены на несколько срок ниже. Искомое_значение - это значение, которое требуется найти в первой строке таблицы. Искомое_значение может быть значением, ссылкой или текстовой строкой. Инфо_таблица - это таблица с информацией, в которой ищутся данные. Можно использовать ссылку на интервал или имя интервала. Значения в первой строке аргумента инфо_таблицы могут быть текстами, числами или логическими значениями. Если интервальный_просмотр имеет значение ИСТИНА, то значения в первой строке аргумента инфо_таблица должны быть расположены в возрастающем порядке: ...-2, -1, 0, 1, 2,... , A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ГПР может выдать неправильный результат. Если интервальный_просмотр имеет значение ЛОЖЬ, то инфо_таблица не обязана быть сортированной. Регистр при поиске не учитывается (т.е. строчные и заглавные буквы не различаются). Интервальный_просмотр - это логическое значение, которое определяет, нужно ли, чтобы функция ГПР искала точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА или опущен, то возвращается приблизительно соответствующее значение; другими словами, если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше, чем искомое_значение. Если искомое_значение меньше, чем наименьшее значение в первой строке аргумента инфо_таблица, то функция ГПР возвращает значение ошибки #Н/Д. Если этот аргумент имеет значение ЛОЖЬ, то функция ГПР ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д. Номер_строки - это номер строки в массиве инфо_таблица, из которой будет возвращено искомое значение. Если номер_строки равен 1, то возвращается значение из первой строки аргумента инфо_таблица, если номер_строки равен 2, то возвращается значение из второй строки аргумента инфо_таблица, и так далее. Если номер_строки меньше 1, то функция ГПР возвращает значение ошибки #ЗНАЧ!; если номер_строки больше, чем количество строк в аргументе инфо_таблица, то функция ГПР возвращает ошибку #ССЫЛ.

Например, использование функции ГПР для таблицы «Перечень автомобильных деталей»расположенной в ячейках A1A4 ( Рисунок 27) приведёт к следующим результатам:

ГПР("Оси"; A1:C4;2;ИСТИНА) равняется 4,

ГПР("Подшипники";A1:C4;3;ЛОЖЬ) равняется 7,

ГПР("Подшипники";A1:C4;3;ИСТИНА) равняется 7,

ГПР("Болты";A1:C4;4;) равняется 11.

Инфо_таблица может быть также массивом констант: ГПР(3;{1;2;3:"а";"б";"в":"г";"д";"е"};2;ИСТИНА) равняется "в"

• ВПР(искомое_значение; инфо_таблица; номер_столбца; интервальный_просмотр). Функция аналогична ГПР, но ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы. Функция ВПР используется вместо функции ГПР, когда сравниваемые значения расположены в столбце слева от искомых данных. Например, таблице «Зависимость между плотностью и вязкостью и температурой» расположенной в ячейках А2:С5 (Рисунок 28), присвоено имя «Диапазон». Различные варианты поиска с

  	A	B	C
  1	Плотность	Вязкость	Температура
  2	0,45	3,55	500
  3	0,52	3,25	400
  4	0,61	2,93	300
  5	0,74	2,75	250

  Рисунок 28 - Зависимость между плотностью и вязкостью и температурой

  помощью функции ВПР в этой таблице дают следующие результаты:

2,93 - ВПР(0,61;А2:С5;2;ИСТИНА())

или

ВПР(0,61;Диапазон;2;ИСТИНА()) ,

300 - ВПР(0,61;Диапазон;3;ЛОЖЬ()),

3,25 - ВПР(0,561;Диапазон;2;ИСТИНА()).

Приведём описание функций второй группы.

МОБР(массив). Возвращает обратную матрицу для матрицы, хранящейся в массиве. Массив - это числовой массив с равным количеством строк и столбцов. Массив может быть задан: как диапазон ячеек, например, A1:C3; как массив констант, например, {1;2;3: 4;5;6: 7;8;9} или как имя диапазона или массива. При использовании массива констант для разделения чисел в строке используется «;» а для разделения строк «:». Если какая-либо из ячеек в массиве пуста или содержит текст, а также если массив имеет неравное число строк и столбцов, то функция МОБР возвращает ошибку #ЗНАЧ!.

МУМНОЖ(массив1;массив2). Возвращает произведение матриц (матрицы хранятся в массивах). Результатом является массив с таким же числом строк, как массив1, и с таким же числом столбцов, как массив2. Массив1, массив2 - это перемножаемые массивы. Количество столбцов аргумента массив1 должно быть таким же, как количество сток аргумента массив2, и оба массива должны содержать только числа. Массив1 и массив2 могут быть заданы как интервалы, массивы констант или ссылки. Если хотя бы одна ячейка в аргументах пуста или содержит текст, или если число столбцов в аргументе массив1 отличается от числа строк в аргументе массив2, то функция МУМНОЖ возвращает ошибку #ЗНАЧ!.