MS EXCEL. ВЫЧИСЛЕНИЯ С ИСПОЛЬЗОВАНИЕМ ОТНОСИТЕЛЬНЫХ, АБСОЛЮТНЫХ, ВНЕШНИХ (ОБЪЕМНЫХ) ССЫЛОК
1. Типы ссылок. Запись формул с использованием относительных, абсолютных и внешних ссылок.
2. Вставка примечаний.
Ссылкаявляется основным элементом при выполнении вычислений с использованием нескольких ячеек. Ссылки позволяют использовать в одной формуле данные из различных областей, а также значения из одной ячейки – в нескольких формулах.
Например, B2 – ссылка на ячейку,
B2:B8 – ссылка на часть столбца В (интервал ячеек),
B2:D2 – ссылка на часть 2-й строки (интервал ячеек),
B2:D8 – ссылка на прямоугольный смежный фрагмент.
Со ссылками могут использоваться следующие операторы:
Ø оператор интервалов «:» – определяет ссылку на смежный интервал ячеек, например (А1:В12);
Ø оператор объединения «;» – определяет одну ссылку, объединяя ячейки, на которые указывают две ссылки и более, например (А1;В12);
Ø оператор пересечения «пробел» – определяет одну ссылку на ячейки, общие для двух ссылок, например (В5:D5 C4:C6) (результатом пересечения будет ячейка С5).
В MS Excel используются несколько типов ссылок:
– относительные,
– абсолютные,
– смешанные,
– внешние,
– циклические.
Относительная ссылка используется в формулах для указания адреса ячейки относительно позиции ячейки, в которой содержится формула. Относительная ссылка записывается как последовательность заголовка столбца и строки (A8, F12, GD123).
При переносе формулы на некоторое число позиций каждая содержащаяся в ней относительная ссылка на ячейку заменяется ссылкой на другую ячейку, смещенную относительно исходной на такое же число позиций и в том же направлении, что и формула. Другими словами, относительная ссылка указывает MS Excel, как найти другую ячейку, начиная поиск с ячейки, в которой расположена формула (рис. 5).
Рис. 5. Изменение относительных ссылок при копировании формул
Абсолютная ссылкана ячейку указывает, как найти ячейку на основании ее точного местоположения на Рабочем листе книги. Если в формуле используется абсолютная ссылка на ячейку, то при ее копировании и переносе в другую ячейку или другую таблицу ссылка будет указывать на ту же самую ячейку.
Абсолютная ссылка на ячейку содержит символ доллара ($) перед буквой столбца и номером строки, например $A$10. Ввести абсолютный адрес в формулу можно, набрав его с клавиатуры или заменив нажатием клавиши <F4> введенную относительную ссылку на абсолютную. Абсолютная ссылка используется, если требуется произвести умножение или деление на постоянное число, сложение и вычитание с постоянным числом и в ряде других случаев.
Различие между относительной и абсолютной формами адресации ячеек проявляется только при копировании или перемещении ячейки с формулой в другое место рабочей книги. Входящий в формулу абсолютный адрес ячейки при копировании и перемещении не изменяется, а относительный адрес всегда изменяется в соответствии с новым местом в таблице (рис. 6).
Смешанная ссылка содержит относительную и абсолютную ссылки. В смешанной ссылке символ «$» может находиться либо перед буквой столбца, либо перед номером строки, например $A1, A$1. Если символ доллара стоит перед буквой, то координата столбца абсолютная, а строки относительная; если символ доллара стоит перед номером строки, то координата строки является абсолютной, а столбца относительной.
При переносе формул, содержащих смешанные ссылки, относительная часть ссылок изменяется (настраивается), а абсолютная часть остается неизменной.
Рис. 6. Изменение относительных и абсолютных ссылок при копировании формулы
Внешние (объемные) ссылкипозволяют обратиться к ячейкам, расположенным на других листах текущей Рабочей книги или на листах другой Рабочей книги.
Для записи ссылки на другие листы текущей книги необходимо указать перед ссылкой на ячейку ссылку на лист, например, Лист2!А2 или Лист2!$А$2 – ссылается на ячейку А2, расположенную на втором листе книги. Ссылка на ячейку может быть как относительной, так и абсолютной.
Для записи ссылки на листы других книгнеобходимо указать имя книги, ссылку на лист и ссылку на ячейку, например, [Книга2]Лист2!А1 или [Книга2]Лист2!$А$1.
Следует помнить, что после имени листа ставится восклицательный знак, а ссылка на книгу заключается в квадратные скобки.
Примеры использования внешних ссылок
1. Лист1!$А$1:$Н$1 – ссылка на диапазон ячеек с А1 по Н1 на Листе1 текущей рабочей книги.
2. Лист1:Лист6!$А$1:$Н$1 – ссылка на диапазон ячеек с А1 по Н1 на листах с первого по шестой текущей рабочей книги.
3. Январь:Декабрь!$B1 – ссылка на ячейку В1 на листах с Январь по Декабрь (в этом примере листы поименованные).
4. [Книга5]Лист1!$А$1:$Н$1 – ссылка на диапазон ячеек с А1 по Н1 на Листе1 рабочей Книги5.
5. [Книга2:Книга5]Лист1:Лист6!;$А$1:$Н$1 – ссылка на диапазон ячеек с А1 по Н1 на листах с первого по шестой рабочих книг со второй по пятую.
6. [2004:2007]Январь:Декабрь!$B1 – ссылка на ячейку В1 на листах с Январь по Декабрь в книгах с именами 2004, 2005, 2006, 2007 (в этом примере и книги, и листы поименованные).
Циклическая ссылка– это формула, которая зависит от своего собственного значения.
