8.3.Представление данных и вычисления в таблицах Excel

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

Excel позволяет вводить данные в ячейки таблицы как непосредственно, так и через строку формул (в ячейке таблицы данные отображаются в соответствии с форматом, заданным для этой ячейки пользователем, а в строке формул – в том виде, в каком данные были введены). Для автоматизации ввода данных в Excel используются команды заполнения: ячейки можно заполнить путем копирования ранее введенных в некоторый диапазон значений, числовыми последовательностями (арифметической и геометрической прогрессиями), списками (в Excel определены списки, представляющие названия дней недели, месяцев, кроме того, пользователь может задать свои списки), последовательностями дат. Заполнение может выполняться как с помощью мыши, так и с помощью команды Заполнить меню Правка. Более подробную информацию о способах заполнения можно получить в разделе «Ввод данных» справочной системы.

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

Между данными, расположенными в различных таблицах, могут быть установлены связи. Это позволяет автоматически обновлять связанные данные во всех таблицах при изменении исходных данных, что позволяет осуществлять ввод данных однократно, избежать дублирования, поддерживать целостность (согласованность) данных в нескольких таблицах.

Таблица Excel может быть получена путем консолидации данных из нескольких таблиц (исходных диапазонов).

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

8.3.1. Ссылки на ячейки рабочих листов Excel

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

Координаты текущей (активной) ячейки или ее имя (если имя определено) отображаются в поле со списком «Имя» в окне Excel. Введя координаты в это поле или выбрав имя из списка определенных имен, можно быстро перейти к нужной ячейке.

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

По умолчанию в Microsoft Excel используются ссылки вида “A1”, в которых столбцы обозначаются буквами от A до IV (256 столбцов максимально), а строки числами – от 1 до 65536). Чтобы указать ссылку на ячейку, вводится буква заголовка столбца, а затем номер строки. Например, D50 является ссылкой на ячейку, расположенную в пересечении столбца D с 50-й строкой.

Можно воспользоваться стилем ссылок, в котором и столбцы, и строки листа пронумерованы. Этот стиль, называемый “R1C1”, наиболее полезен при вычислении позиции строки и столбца в макросах, а также при отображении относительных ссылок. В стиле “R1C1”, после буквы ‘R’ указывается номер строки ячейки, после буквы ‘C’ – номер столбца.

Стиль ссылок можно изменить с помощью команды Параметры меню Сервис.

Excel может использовать относительные и абсолютные координаты ячеек.

Абсолютные координаты не изменяются, когда ячейка, содержащая формулу (в частности, функцию), копируется в другое место. Для указания абсолютных координат ячеек перед координатой (номером строки или названием столбца) следует поместить знак доллара (символ $), например: $B$5 (абсолютные координаты ячейки B5). В стиле “R1C1” указывается место размещения ячейки, где после буквы ‘R’ ставится номер строки ячейки, а после буквы ‘С’ – номер столбца. Например, абсолютная ссылка R1C1 эквивалентна абсолютной ссылке $A$1 для формата “A1”.

Однако в большинстве случаев удобно, чтобы при копировании содержимого ячейки, содержащей формулу со ссылками на другие ячейки, их координаты изменялись бы (например, значение ячейки – сумма значений предшествующих ей ячеек той же строки, если значения всех ячеек этого столбца вычисляются по той же формуле, то при ее копировании координаты ячеек, содержащих слагаемые, должны изменяться, настраиваясь на новый номер строки). При копировании формулы в другое место относительные координаты изменяются. При определении относительных координат знак доллара добавлять не нужно. Если ячейка A1 является текущей, то относительная ссылка R[1]C[1] в стиле “R1C1” указывает на ячейку, расположенную на одну строку ниже и на один столбец правее, то есть на ячейку B2.

Если при создании формулы используется диапазон ячеек, то он вводится в формулу в такой последовательности: координаты первой ячейки диапазона, двоеточие и координаты последней ячейки диапазона (например, B1:B5).

Если в формуле используются значения ячеек других рабочих листов, то соответствующие операнды должны иметь следующий формат: имя рабочего листа, восклицательный знак, координаты ячейки (например, Лист1!B5).

Для ссылки на ячейки другой рабочей книги используются «трехмерные» ссылки вида '[Книга.xls]Лист'!Диапазон.

Чтобы избежать использования координат, сделать ссылки на ячейки более наглядными, ячейке или диапазону ячеек можно присвоить имя, которое затем можно будет использовать вместо указания координат ячеек.

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

Координаты ячеек (номера строки и столбца) являются свойствами объекта Range.

Свойством Range обладают объекты Application, Range и Worksheet. Значением этого свойства является объект Range. Для обращения к этому свойству необходимо записать выражение вида

Выражение.Range(Cell1, Cell2)

или

Выражение.Range(Cell)

где Cell – имя диапазона или ссылка, которая должна быть задана в A1‑стиле (задается как текстовая строка), или объект Range, который может содержать единственную ячейку, целый столбец или строку; Cell1 и Cell2 определяют ячнйки в левом верхнем и правом нижнем углах диапазона. Например, операторы

Worksheets(1).Range("A1:B3").Select

и

Worksheets(1).Range("A1", "B3").Select

позволяют выделить один и тот же диапазон рабочего листа. Если диапазону “A1:B3” присвоено имя (например, “Range_name”), оператор выделения диапазона можно записать так:

Worksheets(1).Range("Range_name")

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

Свойство Range зависит от объекта Range. Например, если выделена ячейка C3, то результатом вычисления выражения

Selection.Range("B1")

станет ячейка D3 (используется относительная ссылка, результат вычисляется по отношению к текущему выделению), а выражение

ActiveSheet.Range("B1")

всегда вернет значение B1.

При реализации вычислений в Excel в различных контекстах бывает удобнее пользоваться различными способами ссылок на ячейки.

Свойство Cells (Cells(RowNum, ColumnNum)) используется, когда нужно указать единственную ячейку в строке и столбце с заданными номерами (индексами RowNum и ColumnNum). Например, следующий оператор устанавливает для ячейки A1 первого рабочего листа значение 100:

Worksheets(1).Cells(1, 1).Value = 100

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

Ссылка, возвращаемая как результат при вычислении

Выражение.Cells(Row, Column)

где Выражение возвращает объект Range, определяет ячейку по отношению к левому верхнему углу полученного диапазона. Например оператор

Range("C5:C10").Cells(1, 1).Formula = "=СУММ(C6:C10)"

заносит в первую ячейку указанного диапазона (C5) активного рабочего листа формулу для вычисления суммы значений, записанных в диапазоне “C6:C10”.

Свойство Offset (Offset(RowOffs, ColumnOffs)) используется, когда строка и столбец нужной ячейки определяются с помощью смещений (RowOffs и ColumnOffs)) по отношению к другой ячейке (диапазону). Например, после выполнения операторов

Application.Goto Reference:="R1C1"

Selection.Offset(3, 1).Range("A1").Select

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

Метод Union (Union(Range1, Range2, ...)) используется для получения диапазонов, которые могут состоять из двух и более прямоугольных блоков ячеек (прямоугольных диапазонов Range1, Range2, ...). В приведенном ниже примере создается объект, являющийся объединением двух диапазонов “A1:B2” и “C3:D4”, после чего эти диапазоны выделяются.

Dim R1 As Range, R2 As Range, myMultiAreaRange As Range

Worksheets("sheet1").Activate

Set R1 = Range("A1:B2")

Set R2 = Range("C3:D4")

Set myMultiAreaRange = Union(R1, R2)

myMultiAreaRange.Select

Для выделения, которое состоит из более чем одной области, можно воспользоваться свойством Areas, которое позволяет разбить выделение на отдельные диапазоны и возвращает соответствующие обхекты как семейство. Количество объектов в семействе можно получить с помощью свойства семейства Count:

numberOfSelectedAreas = Selection.Areas.Count

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