Ссылки MS-Excel

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

По умолчанию MS-Excel использует стиль ссылок A1, определяющий столбцы буквами (от A до IV, всего не более 256 столбцов), а строки номерами (от 1 до 65536).

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

1Нужно отметить, что при перемещении формул ссылки никогда не перенастраиваются.

Таблица 2.4 – Операторы ссылок

Оператор «:» используется для ссылки на данные, расположенные в смежных ячейках.

Таблица 2.5 – Определение областей

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

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

Ссылки подразделяются на относительные, абсолютные и смешанные.

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

Абсолютная ссылка ячейки в формуле всегда ссылается на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. Чтобы превратить относительную ссылку в абсолютную, необходимо перед индексами столбца и строки поставить знак «$». Например:

•      С1 — относительная ссылка;

•      $C — абсолютная ссылка.

Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка столбцов приобретает вид $A1, $B1 и т. д. Абсолютная ссылка строки приобретает вид A, B и т. д. При изменении позиции ячейки, содержащей формулу, относительная ссылка изменяется, а абсолютная ссылка не изменяется.

Для перехода от относительной ссылки к абсолютной, а затем к смешанной можно использовать клавишу — F4.

В формулах можно использовать данные, расположенные на разных листах.

Для обращения к этим данным используются трехмерные ссылки.

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

«= СУММ(Лист2 ∶ Лист13!B5)» суммирует все значения, содержащиеся в ячейке B5 на всех листах в диапазоне от Лист2 до Лист13 включительно. При использо-

вании трехмерных ссылок нужно пользоваться следующими правилами:

•      трехмерные ссылки могут быть использованы для создания ссылок на ячейки других листов, определения имен и создания формул с использованием следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЁТ, СЧЁТЗ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СТАНДОТКЛОН, СТАНДОТКЛО- НА, СТАНДОТКЛОНП, ДИСП, ДИСПА, СТАНДОТКЛОНПА, ДИСПР и ДИСПРА;

•      трехмерные ссылки нельзя использовать в формулах массива1;

•      трехмерные ссылки нельзя использовать вместе с оператором пересечения (пробел).

При перемещении, копировании, вставке и удалении листов ссылка изменяется. Предположим, что используется формула «= СУММ(Лист2 ∶ Лист6!A2 ∶ A5)», суммирующая содержимое ячеек с A2 по A5 с листов от Лист2 до Лист6 включительно, тогда изменение результата рассматриваемой формулы будет осуществляться по следующим правилам, в зависимости от применяемой операции.

Вставка или копирование. Если между Лист2 и Лист6 книги вставить новые листы, MS-Excel добавит в сумму содержимое ячеек с A2 по A5 на новых листах. Удаление. Если между Лист2 и Лист6 книги удалить листы, MS-Excel исклю-

чит из суммы содержимое ячеек удаленных листов.

Перемещение. Если переместить листы, находящиеся между Лист2 и Лист6 книги, и разместить их таким образом, что они будут расположены перед Лист2 или после Лист6, MS-Excel исключит из суммы содержимое ячеек перемещенных листов.

Перемещение граничного листа. Если переместить Лист2 или Лист6 в новое место книги, MS-Excel включит в сумму содержимое ячеек листов, находящихся между Лист2 и Лист6 включительно.

Удаление граничного листа. Если удалить Лист2 или Лист6, MS-Excel включит в сумму содержимое ячеек листов, находившихся между ними.

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

На основе рассмотренной в данном пункте информации решение задачи 2.4 можно представить иным способом.

. . . . . . . . . . . . . . . . . . . . . . Пример 2.5 . . . . . . . . . . . . . . . . . . . . .

Найти значения функции f (x) = xC − x + 2, где х ∈[1,5] меняется с шагом 0,5 и C — некоторая константа.

Решение:

Ячейки, содержащие значения аргумента, заполняем так же, как и раньше, с использованием автозаполнения:

•      в ячейку А1 помещаем значение 1;

•      так как переменная x изменяется с шагом 0,5 то в ячейку А2 поместим число 1,5;

•      выделяем ячейки А1 и А2 и с помощью маркера заполнения в ячейки с А3 по А9 заносим остальные значения аргумента функции.

В ячейку С1 поместим значение константы C (например, 2), ячейки с В1 по В9 заполняем следующим образом:

•      в ячейку В1 поместим выражение «= А1∧С1 − A1 + 2»;

•      помещаем курсор после ссылки С1 и нажимаем клавишу F4, при этом выражение примет вид «= А1∧$С − A1 + 2»;

•      нажимаем клавишу Enter (в ячейке В1 появляется значение функции для

x = 1);

•      выделяем ячейку В1 и с помощью маркера заполнения копируем формулу в ячейки с В2 до В9.

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Источник: Гураков А. В., Информатика. Введение в Microsoft Office : учебное пособие / А. В. Гураков, А. А. Лазичев. — Томск: Эль Контент, 2012. — 120 с.