Тема 4. Формулы с массивами
Массивы являются удобным средством обработки групп однотипных данных. Массиы могут быть аргументами в некоторых функциях или формулах, возвращающих в результате вычислений либо единственное значение, либо массив новых значений. Формулы, возвращающие массив результатов, называются табличными формулами или формулами массива. Блок ячеек, в котором используется общая формула, называется интервалом массива. Массив представляет собой определенным способом организованный список каких-либо данных.
Массивы констант.
В формулах Excel можно использовать массивы констант. Массив констант строится как совокупность значений, заключенных в фигурные скобки и разделенных точкой с запятой. Массивы могут иметь различную размерность. Различают одномерные и двумерные массивы. Например, одномерный массив, размерностью 1×3, состоящий из одной строки и трех элементов в этой строке и содержащий элементы:
5 7 9,
записывается как {5;7;9}, т.е. представляет собой совокупность значений массива, заключенных в фигурные скобки и разделенных точкой с запятой. Это горизонтальный одномерный массив (вектор-строка). Чтобы записать одномерный массив вертикально (вектор-столбец):
5
7
9,
следует записать {5:7:9}, т.е. записать совокупность значений массива в фигурных скобках и разделить их двоеточием.
Двумерный массив состоит из нескольких строк и столбцов. Например, двумерный массив, размерностью 2×3, (состоящий из двух строк и трех столбцов), содержащий элементы:
11 13 15
17 19 21,
записывается как {11;13;15:17;19;21}, т.е. представляет собой совокупность значений массива, заключенных в фигурные скобки, при этом значения, расположенные в одной строке отделяются друг от друга точкой с запятой, а строки двоеточием.
Массивы констант могут содержать числовые, текстовые или логические значения, причем текстовые константы должны быть заключены в кавычки. В один массив можно вводить значения разных типов, например, массив:
a b c
1 2 3
представляется как {«a»;«b»;«c»:1;2;3}.
Пусть имеются два двумерных массива:
1 2 10 20
3 4, 30 40.
Найти массив, каждый элемент которого представляет собой сумму соответствующих элементов исходных массивов:
11 22
33 44.
Выполним следующие действия:
Выделим блок ячеек, в котором должны расположиться элементы результирующего массива: В2:С3.
Не сбрасывая выделения, в текущую ячейку (или в строку формул) введем выражение:
={1;2:3;4}+{10;20:30;40}.
Одновременно нажмем комбинацию клавиш Ctrl+Shift+Enter. Использование такой комбинации клавиш сообщает Excel, что вводится формула с массивами. Excel автоматически добавляет фигурные скобки при вводе табличной формулы. Эти скобки видны в строке формул, но если эти скобки ввести «вручную», формула будет воспринята как текст. Операндами этой формулы являются массивы констант.
В выделенном диапазоне получим элементы результирующего массива. Формулы с массивами выдали более одного результата: в режиме решения видим массив чисел изображенный на рисунке 224, в режиме показа формул – массив формул на рисунке 225.
Рисунок 224
Рисунок 225
Как видно из рисунка 225, в каждой ячейке выделенного диапазона записана формула, введенная в текущую ячейку. Эта «единичная» формула как бы существует одновременно в четырех ячейках в виде различных формул выделенного диапазона. Но внести изменения в них по отдельности нельзя. Это можно сделать, только выделив весь интервал В2:С3. Интервал В2:С3 представляет собой интервал массива, и табличная формула хранится в каждой ячейке.
Если в формуле используется массив констант, все остальные компоненты должны иметь ту же размерность, что и первый массив. В случае необходимости Excel расширяет нужные компоненты до требуемых размеров. Все компоненты должны иметь такое же количество строк и столбцов, как компоненты с наибольшим числом строк и столбцов.
Например, пусть одна из компонент – двумерный массив, состоящий из двух строк и трех столбцов. Для представления результатов вычисления по формуле ={1;2;3:4;5;6}*2 требуется выделить блок ячеек 2×3.
Одна из компонент исходного массива – массив 2×3, а другая – одиночное значение. При вычислении по этой формуле автоматически будет расширена вторая компонента до массива размерности 2×3 и произойдет вычисление по этой формулы как ={1;2;3:4;5;6}*{2;2;2:2;2;2}. Результат представлен на рисунке 226.
Рисунок 226.
Формулы массива.
В формулах Excel в качестве операндов будут использоваться формулы массива (табличные формулы).
Пусть в блоке ячеек А2:D2 записаны числа 1, 3, 5, 7, а в блоке ячеек А3:D3 – 10, 30, 50, 70. Вычислим и запишем в блок ячеек А5:D5 суммы соответствующих элементов блоков ячеек А2:D2 и А3:D3.
Сделаем это уже традиционным способом: введем в ячейку А5 формулу =А2+А3 и скопируем содержимое этой ячейки в блок ячеек В5:D5. В блоке ячеек А5:D5 будут записаны числа 11, 33, 55, 77 (рисунок 227 – режим формул, рисунок 228 – режим решения).
Теперь используем для вычисления элементов результирующего массива табличную формулу (формулу массива). Для этого:
Выделим для получения результата блок ячеек А7:D7.
Не сбрасывая выделения, в текущую ячейку (или в строку формул) введем выражение:
=A2:D2+A3:D3.
Одновременно нажмем комбинацию клавиш Ctrl+Shift+Enter.
Формула будет автоматически заключена в фигурные скобки. Результатом введенного выражения будут суммы в выделенных ячейках как показано на рисунке 227 – в режим показа формул. В результате в блоке ячеек получим результирующий массив показанный на рисунке 228 – в режиме решения.
Рисунок 227
Рисунок 228
Интервал А7:D7 представляет собой интервал массива, где в каждой ячейке содержится формула массива. Внести изменения по отдельности в ячейку интервала нельзя. Интервал же А5:D5 не представляет собой интервал массива, и можно отдельно внести изменения в любую формулу этого блока.
Рассмотрим пример. Пусть в блок ячеек А2:В3 записаны числа 1, 3, 5, 7, а в блок ячеек D2:Е3 – 10, 30, 50, 70, т.е. даны два двумерных массива. Вычислим и запишем в блок ячеек В5:С6 сумму соответствующих элементов блоков ячеек А2:В3 и D2:Е3.
Выполним действия, аналогичные действиям, рассмотренным в предыдущем примере:
Введем в блоки ячеек А2:В3 и D2:Е3 исходные данные.
Выделим для получения результата блок ячеек В5:С6.
Не сбрасывая выделения, в текущую ячейку (или в строку формул) введем выражение:
=А2:В3+D2:Е3.
Одновременно нажмем комбинацию клавиш Ctrl+Shift+Enter.
Рисунок 229
Формула будет автоматически заключена в фигурные скобки. Результатом введенного выражения будут суммы в выделенных ячейках как показано на рисунке 229 – в режиме показа формул). В результате в блоке ячеек получим результирующий массив как показано на рисунке 240 – в режим решения.
Рисунок 240
Аргументами формулы являются два однотипных массива: в первом примере – одномерные массивы, состоящие из четырех элементов, во втором - двумерные, состоящие из двух столбцов и двух строк. Такими же являются результирующие массивы. Попробуем ввести аналогично формулу в диапазон ячеек, отличный от диапазона исходных ячеек. Правильные суммы оказались записанными только в блок, соответствующий исходным блокам, в остальные же ячейки блока помещен код ошибки #Н/Д (нет данных) как показано на рисунке 241.
Рисунок 241
Выделим для получения результата блок ячеек, меньший, чем требуется. Тогда от результата останется только два значения, остальные будут потеряны как показано на рисунке 242.
Рисунок 242
Формулы массива можно использовать для ввода повторяющихся формул, что позволит сэкономить время при вводе.
Необходимо помнить, что нельзя удалить отдельные ячейки в интервале массива, так как ячейки совместно используют одну формулу.
Редактирование массивов.
Так как все ячейки интервала массива используют одну формулу, этот интервал редактируется иначе, чем обычные ячейки. При редактировании интервала массива одновременно меняются все ячейки интервала. Ни одна операция не может быть выполнена для части массива. Нельзя ни редактировать, ни удалять, ни перемещать, ни очищать отдельные ячейки массива, нельзя изменять содержимое только некоторых ячеек массива, вставлять в него или удалять из него ячейки. Все ячейки в таком интервале являются единым целым и редактируются также целиком путем выделения всего интервала. При попытке это сделать выводится сообщение о том, что данную операцию нельзя выполнить для массива.
Редактировать формулу массива можно следующим образом:
выделить любую ячейку диапазона и поместить в строку формул курсор (либо дважды щелкнуть по одной из ячеек и курсор появится в самой ячейке). Фигурные скобки в строке формул исчезнут.
Ввести другую формулу в ячейку или изменить старую, можно также удалить ячейку.
Нажать комбинацию клавиш Ctrl+Shift+Еnter. Будет выделен интервал массива и внесены изменения во все ячейки интервала.
Например, поменяем знак плюс в формуле =А2:В3+D2:Е3 на минус. Для этого активизируем текущую ячейку, поменяем знак «+» на «–» и нажмем Ctrl+Shift+Enter получим результат показанный на рисунке 243:
Рисунок 243
Для перемещения содержимого массива-интервала надо:
выделить его целиком.
выполнить команду Правка – Вырезать или нажать кнопку Вырезать (вокруг выделенной области побежит пунктирная рамка).
Выделить ячейку в начале новой области.
Выполнить команду Правка – Вставить.
Выделенный массив можно перетащить в другое место мышью.
Чтобы определить диапазон, занимаемый формулой массива, следует выделить какую-нибудь ячейку массива и выбрать команду Правка – Перейти – Выделить, в окне выделение группы ячеек установить переключатель на группе текущий массив.
|
