Массив Excel – это объект, используемый для вычисления нескольких значений в результате вычисления одной формулы или для работы с набором элементов, расположенных в различных ячейках и сгруппированных по строкам или столбцам. Существует два типа массивов: диапазоны массива и диапазоны констант. иапазоном массива называется непрерывный диапазон ячеек, использующих общую формулу. Диапазон констант представляет собой набор констант, используемых в качестве аргументов функций.
Формула массива может выполнить несколько вычислений, а затем вернуть одно значение или группу значений. Формула массива воздействует на несколько наборов значений, называемых аргументами массива. Каждый аргумент массива должен иметь соответствующий номер строки и столбца.
Рассмотрим пример: требуется определить число полученных зачетов и средние оценки как среди всех студентов, сдавших экзамен, так и только среди тех, кто получил зачет. Рабочий лист, содержащий исходные данные и результаты вычислений, показан на рис. 14.
Общее количество зачетов определяется с помощью функции Excel (в ячейку B7 введена формула =СЧЁТЕСЛИ(B2:B5;"=зачет")). Средняя оценка вычисляется с помощью формулы =СРЗНАЧ(C2:C5), введенной в ячейку C8. А вот для вычисления средней оценки только для студентов, сдавших зечет, необходимо использовать формулу массива.
Формула массива создается так же, как и простая формула. Выделяется ячейка или группа ячеек, в которых необходимо создать формулу (в данном примере это C10), вводится формула, показанная на рис. 14 в строке формул (=СРЗНАЧ(ЕСЛИ(B2:B5="зачет";C2:C5))), а затем нажимаются клавиши Ctrl+Shift+Enter (формула вводится без фигурных скобок). После нажатия комбинации клавиш введенная формула в строке формул заключается в фигурные скобки, принимая вид
{=СРЗНАЧ(ЕСЛИ(B2:B5="зачет";C2:C5))}
что является признаком того, что формула интерпретируется Excel как формула массива. Если фигурные скобки ввести вручную, формула будет распознаваться Excel как символьная строка и вычисления не будут выполняться.
Введенная формула вычисляет среднее значение только тех ячеек, принадлежащих диапазону C2:C5, которым в соответствующих строках столбца B поставлено в соответствие значение "зачет". Функция ЕСЛИ находит ячейки в диапазоне B2:B5, содержащие строку "зачет", и возвращает значения, соответствующие этой строке в диапазоне C2:C5, передавая их функции СРЗНАЧ.
Для вычисления нескольких значений в формуле массива, необходимо ввести массив аргументов для вычисления по формуле в диапазон ячеек, имеющих соответствующее число строк или столбцов. Чтобы отобразить все вычисленные значения, необходимо ввести формулу в ячейки, в которые должны быть помещены результаты (рис. 15).
В приведенном на рис. 15 примере формула введена в ячейки диапазона “G2:J2”.
Для генерации формулы массива в некоторых случаях можно воспользоваться Мастером частичных сумм, который позволяет пользователю создать формулу для вычисления суммы значений, находящихся в заданном столбце, причем суммироваться должны только значения, находящиеся в строках, ячейки которых удовлетворяют определяемым пользователем условиям. Это средство применимо к спискам (базам данных) Excel, так как каждый столбец таблицы должен иметь заголовок. Активизировать Мастер частичных сумм можно с помощью команды Мастер8Частичная сумма…. Все параметры, определяющие условия суммирования задаются в диалоге. Это средство Excel рассматрвается ниже.
В формулу массива можно включать константы так же, как это делается в простой формуле, но массив констант должен вводиться в определенном формате. Более подробную информацию можно получить в справочной системе.
Определение связей между таблицами
Связи позволяют использовать на листе одной рабочей книги данные других листов и даже рабочих книг. Можно установить связь с одной ячейкой, диапазоном ячеек, диапазоном рабочих листов.
Рабочая книга, содержащая исходные данные, называется исходной книгой или книгой-источником. Книга, получающая данные, – это книга-получатель или целевая рабочая книга.
Благодаря возможности связывать данные друг с другом, можно создавать небольшие рабочие книги с небольшими листами, соответствующие отельным задачам. Затем эти книги можно связать друг с другом для создания больших информационных систем, в которых информация не будет дублироваться и всегда будет поддерживаться в соглавосанном состоянии, что позволяет повысить гибкость в организации работы (связанные данные могут располагаться на различных компьютерах в сети), облегчить внесение изменений (изменения нужно вносить только в исходные данные).
Чтобы связать ячейки или диапазоны ячеек нужно выделить исходный диапазон ячеек, открыв исходную книгу, и выполнить команду копирования. Затем следует переключиться на целевой рабочий лист и выделить ячейку, которая должна быть расположена в левом верхнем углу диапазона ячеек, для которых создается связь. Затем нужно выполнить команду Специальная вставка меню Правка. В открывшемся диалоговом окне следует установить значения нужных переключателей (например, установить переключатель «Вставить» в положение «Все», а переключатель «Операция» – в положение «Нет»). Для завершения операции нажимается кнопка Вставить связь. В результате в ячейках будут отображены значения ячеек исходного диапазона, а содержимым этих ячеек (его можно увидеть в строке формул) будут соответствующие ссылки (рис. 16).
Программным способом эти действия выполняются с помощью последовательности операторов (целевая книга к моменту, когда начинают выполняться операторы, должна быть открыта):
Workbooks.Open _
FileName:="D:\Примеры\Исходные данные для ссылок.xls"
Sheets("Данные для копирования").Range("Источник").Select
Selection.Copy
Windows("Создание ссылок.xls").Activate
Sheets("Лист1").Select
Range("A1").Select
ActiveSheet.Paste Link:=True
При изменении исходных данных автоматически будет происходить обновление информации в связанных диапазонах.
Установить связь можно с помощью непосредственного указания: следует выделить целевую ячейку, в которую будет помещена связь, и обычным образом ввести формулу, в которой будет задана нужная ссылка. Ввод завершается нажатием клавиши Enter. Таким образом, формулу, содержащую ссылку (связь) можно набрать вручную.
При сохранении рабочих книг, содержащих внешние ссылки, следует сначала сохранять исходные книги, а затем целевые (это обеспечит правильное запоминание пути к источнику). При изменении книги-источника лучше держать открытой книгу-получателя (при этом в целевой книге будут зафиксированы изменения).
Изменение информации в связанных книгах лучше выполнять с самых нижних уровней иерархии (от источников) и обновлять связи в порядке от книги-источника самого низшего уровня к целевой книге самого высокого уровня иерархии связей.
Если при открытии целевой рабочей книги с установленными связями исходная рабочая книга открыта, то связи обновляются автоматически. Если же исходная книга закрыта, то пользователь должен будет ответить на вопрос, хочет ли он работать с данными, оставшимися после последнего сохранения книги, или их следует обновить, выбрав новые значения из исходной рабочей книги.
Если связи при открытии всегда должны обновляться, то следует отключить сохранение копий внешних данных вместе с рабочей книгой. Для этого нужно выполнить команду Параметры меню Сервис и на вкладке «Вычисления» диалогового окна снять флажок «Сохранять значения внешних связей».
Если целевая рабочая книга уже открыта, то для открытия исходных книг можно выполнить команду Связи в разделе меню Правка. В открывшемся диалоговом окне приведен список всех источников, в нем можно выделить нужные файлы и щелкнуть кнопку Открыть.
С этом же диалоговом окне можно выделить источники, связь с которыми нужно обновить (заменить на другой файл, например). Обновление ссылок необходимо, если изменилось имя или расположение исходной рабочей книги. Операция обновления связей запускается щелчком по кнопке Изменить. В открывшемся диалоговом окне в его верхней части выделяется обновляемая связь, а в полях ввода в нижней части окна указывается новый, предназначенный для замены источник.
Внешние ссылки, связывающие ячейки или диапазоны ячеек, могут быть «заморожены» – внешняя ссылка удаляется, но сохраняются значения полей, ссылка заменяется на значение. Для выполнения этой операции следует указать нужную ячейку, нажить клавиши F2 и F9 (и кнопку Вычислить).
Существует еще один вид связывания – связывание изображений ячеек.
Более подробную информацию об используемых командах можно получить в справочной системе. Соответствующий код можно сгенерировать с помощью средст макрогенерации.
