Обработка Массивов в ms excel

Цель работы: приобрести навыки по работе с формулами массивов; изучить особенности ввода формул массива.

Методические указания

Массив — это смежный прямоугольный диапазон формул, который MS Excel обрабатывает как единое целое. Результатом расчетов может быть как массив, так и одно число. Использование формул массива дает возможность получать компактные решения достаточно сложных задач, а в некоторых случаях без них вообще нельзя обойтись. Замена повторяющихся формул формулами массива позволяет сэкономить память, так как MS Excel хранит в памяти массив формул как единую формулу.

Пример 1. Необходимо вычислить стоимость каждого вида товара (рис. 5.1).

 

Рис. 5.1. Исходные данные к примеру 1

Решение. Чтобы произвести вычисления с использованием формул массива необходимо:

1. Выделить диапазон , который будет заключать в себе формулу массива.

2. Ввести формулу . Данная запись предполагает перемножение соответствующих элементов массивов. Следует отметить, что вместо указания диапазонов ячеек допустимо использование их собственных имен (например: ).

3. Завершить ввод формулы одновременным нажатием SHIFT–CTRL–ENTER. При этом автоматически появятся фигурные скобки, обрамляющие формулу. Формула массива будет записана во всех ячейках выделенного диапазона.

После того, как создан массив, содержащий формулу, нельзя вставлять ячейки в диапазон массива, удалять часть диапазона или редактировать отдельную ячейку внутри диапазона. Массив можно изменять только как единое целое. Так, например, чтобы изменить формулу массива, выделите диапазон массива, отредактируйте формулу и завершите изменения нажатием SHIFT–CTRL–ENTER.

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

1. Выделить диапазон с формулой массива и добавить в начало символ апострофа «’». Формула превратится в текст.

2. Ввести этот текст во все ячейки выделенного диапазона (CTRL–ENTER).

3. Выделить новый диапазон для формулы массива, откорректировать необходимые адреса, удалить символ апострофа и завершить изменения нажатием SHIFT–CTRL–ENTER.

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

Если вычислению подлежит не общая сумма расходов, а только затраты на определенном этапе закупок. Для этого в ячейку запишем номер этапа. Тогда необходимая расчетная формула в ячейке примет вид: .

В MS Excel существует ряд функций, возвращающих результат в виде массива значений. Естественно, что формулы, содержащие такие функции (например, и др.), следует вводить как формулы массивов и перед их созданием выделять диапазон ячеек нужного размера.

В MS Excel допустимо использование массивов констант. Так, например, запись обозначает вектор-строку; — вектор столбец, — матрицу. В данном случае ввод фигурных скобок обязателен.

 

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

 

Пример 3. Предприятие производит продукцию двух видов и использует сырье двух типов. Нормы затрат сырья на единицу продукции каждого вида заданы матрицей , у которой по строкам указано количество сырья, расходуемого на производство единицы продукции вида 1 и 2. Стоимость единицы сырья каждого типа задана матрицей . Каковы общие затраты предприятия на производство 100 единиц продукции первого вида и 150 единиц второго вида?

Решение. Для того, чтобы определить стоимость сырья для производства единицы продукции каждого вида, умножим матрицу строку стоимости единицы сырья B на матрицу норм затрат сырья (рис. 5.2):

 

 

Рис. 5.2. Образец нахождения стоимости сырья для производства

единицы продукции каждого вида

 

Объемы производства продукции зададим матрицей-столбцом , тогда суммарные затраты на производство продукции равны произведению матрицы строки С на матрицу-столбец Q (рис. 5.3):

 

 

Рис. 5.3. Образец подсчета общих затрат предприятия

 

Пример 4. Решить систему линейных уравнений , где , .

Решение. Решением этой системы является вектор . Для нахождения вектора , введем элементы матрицы в диапазон ячеек , а элементы вектора в диапазон ячеек . Затем необходимо выбрать диапазон , куда поместим элементы вектора решения. В этот диапазон введем формулу =МУМНОЖ(МОБР(МУМНОЖ(A2:B3;A2:B3));D2:D3) и завершим ввод формулы нажатием комбинации клавиш SHIFT–CTRL–ENTER. В результате проделанных действий в диапазоне ячеек появится решение системы уравнений (рис. 5.4).

 

Рис. 5.4. Образец решения примера 4

 

Пример 5. Вычислить квадратичную форму , при этом , .

Решение. Для нахождения значения этой квадратичной формы введем элементы матрицы в диапазон ячеек , а элементы вектора в диапазон ячеек . Затем необходимо выбрать ячейку , куда поместим значение квадратичной формы. В эту ячейку введем формулу =МУМНОЖ(МОБР(МУМНОЖ(A2:B3;A2:B3));D2:D3) и завершим ввод формулы нажатием комбинации клавиш SHIFT–CTRL–ENTER. В результате проделанных действий в диапазоне ячеек появится решение системы уравнений (рис. 5.5).

 

Рис. 5.5. Образец решения примера 5