Относительные и абсолютные ссылки

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

Относительная ссылка характеризуется постоянством относительного положения ячейки со ссылкой и ячейки, на которую ссылаются. При изменении позиции ячейки, содержащей ссылку, изменяется и текст ссылки, но их взаимное расположение сохраняется. При копировании формулы вдоль строк или вдоль столбцов ссылка автоматически корректируется. По умолчанию в новых формулах используются относительные ссылки. Например, при копировании относительной ссылки из ячейки B2 в ячейку B3, она автоматически изменяется с =A2 на =A3. Рассмотренные в табл. 11.3 примеры ссылок также были относительными.

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

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

Вместо того, чтобы вставлять знаки доллара вручную, можно выделить ссылку (или установить в нее курсор ввода текста) и воспользоваться клавишей F4. Каждое нажатие на нее циклически меняет ссылку: относительная, абсолютная, первая и вторая смешанные.

Ссылки в стиле R1C1

Альтернативой стилю ссылок A1 является стиль, в котором нумеруются как строки, так и столбцы. Этот стиль называется R1C1. Это стиль более удобен при решении сложных задач со множеством относительных ссылок и при макропрограммировании.

Особенность стиля R1C1 заключается в написании абсолютных и относительных ссылок. В абсолютных ссылках номера строки и столбца пишутся непосредственно после букв R и C, в относительных ссылках после буквы в квадратных скобках пишется смещение ячейки. При этом смещение влево и вверх пишется со знаком «минус». Если смещение равно 0, цифру «0» в квадратных скобках опускают. Способ написания ссылок показан в табл. 11.4.

Табл. 11.4

Ссылка	Значение
R[-2]C	Относительная ссылка на ячейку, расположенную на две строки выше и в том же столбце
R[2]C[2]	Относительная ссылка на ячейку, расположенную на две строки ниже и на два столбца правее
R2C2	Абсолютная ссылка на ячейку, расположенную во второй строке и во втором столбце
R[-1]	Относительная ссылка на строку, расположенную выше текущей ячейки
R	Абсолютная ссылка на текущую строку

Формулы массива

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

Формула массива создается так же, как и обычные формулы, с той разницей, что после написания такой формулы вместо клавиши Enter используется комбинация клавиш Ctrl+Shift+Enter. При нажатии этих клавиш формула заключается в фигурные скобки. Следует отметить, что ручной ввод фигурных скобок не воспринимается программой как формула массива.

Формула массива может упростить создание документа, заменив собой несколько отдельных формул. В примере на рис. 11.1 показано, как при помощи формулы массива можно перемножить все числа из столбца A на столбец B и просуммировать результат.

                               а)                              б)                                       в)

Рис. 11.1 Пример использования формулы массива для обработки простым оператором (умножения) диапазонов ячеек:
а) формула вида «=СУММ(A1:A4*B1:B4)» – попытка умножить диапазон на диапазон внутри функции суммирования выдает ошибку; б) то же самое при помощи формулы массива вида «{=СУММ(A1:A4*B1:B4)}»; в) отказ от формулы массива заставляет использовать дополнительный столбец C с размноженной формулой «= A 1* B 1»,и считать сумму как «=СУММ(C1:C4)»

Формула массива также может оказаться полезной тем, что если перед вводом формулы выделить сразу несколько ячеек, а саму формулу оформить как формулу массива, она автоматически возвратит столько разных значений, сколько ячеек было выделено при ее вставке. При этом необходимо, чтобы в качестве аргументов формулы выступали диапазоны того же размера, что и выделенный набор ячеек. В примере на рис. 11.2 показано, как при помощи формулы массива можно в одну формулу перемножить все числа из столбца A на столбец B.

                      а)                                            б)                                            в)

Рис. 11.2 11.3 Пример использования формулы массива для обработки диапазона значений:
а) простая формула вида «=A1:A4*B1:B4» выдает один результат, даже когда мы попытались ввести ее, выделив несколько ячеек; б) формула массива вида «{=A1:A4*B1:B4}» при вводе ее в диапазон ячеек сделала то, что нам нужно; в) вместо этого можно было создать формулу «=A1*B1» и размножить ее автозаполнением

Редактирование формул

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

Для редактирования непосредственно в ячейке следует дважды щелкнуть на ней или нажать клавишу F2 – ячейка переключится в режим редактирования, и в ней появится введенная формула и курсор ввода.

Для облегчения редактирования сложных формул Microsoft Excel выделяет пары скобок различными цветами. Это происходит при подведении курсора ввода к любой из скобок. Также, если в формуле имеются ссылки на ячейки, во время редактирования эти ссылки будут выделены различными цветами. При этом ячейка, на которую ссылаются, будет обведена рамкой того же цвета.