Ссылки на ячейки и диапазоны ячеек Microsoft Excel

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

По умолчанию Microsoft Excel использует стиль ссылок A1, определяющий столбцы буквами, а строки цифрами. Эти буквы и цифры называются заголовками строк и столбцов. Для ссылки на ячейку следует вводить букву столбца, а следом номер строки. Например, ссылка B2 указывает на ячейку, расположенную на пересечении столбца B и строки 2.

 

Ячейка или диапазон	Использование
Ячейку в столбце A и строке 10	A10
Диапазон ячеек: столбец А, строки 10-20.	A10:A20
Диапазон ячеек: строка 15, столбцы B-E.	B15:E15
Все ячейки в строке 5.	5:5
Все ячейки в строках с 5 по 10.	5:10
Все ячейки в столбце H.	H:H
Все ячейки в столбцах с H по J.	H:J
Диапазон ячеек: столбцы А-E, строки 10-20.	A10:E20

Пример ссылки на другой лист в той же книге =СУММ(Лист2!A1:B19), следует обратить внимание на то, что имя листа и восклицательный знак (!) предшествуют ссылке на диапазон ячеек. Пример ссылки на другую книгу =СРЗНАЧ([Книга2.xls]Лист1!$A$1:$A$6), следует обратить внимание на то, что имя другой книги заключается в прямоугольные скобки, а после указывается имя листа с указанием ссылок на диапазон ячеек с данными.

Ссылки на ячейки бывают двух типов:

· Относительные – ячейки обозначаются относительным смещением от ячейки с формулой (например: F7).

· Абсолютные – ячейки обозначаются координатами ячеек в сочетании со знаком $ (например: $F$7).

· И их комбинация (смешанные ссылки) (например: F$7).

· Трехмерные ссылки (=СУММ(Лист2:Лист13!B5)).

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

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

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

Трехмерная ссылка используется при необходимости анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов. Microsoft Excel использует все листы, хранящиеся между начальным и конечным именами, указанными в ссылке. Например, формула =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5 на всех листах в диапазоне от Лист2 до Лист13 включительно.

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

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

Трехмерные ссылки нельзя использовать вместе с оператором, а также в формулах, использующих неявное пересечение (Неявное пересечение. Ссылка на диапазон ячеек вместо одной ячейки, из которого в расчете выбирается одна соответствующая ячейка. Например, если в ячейке C10 содержится формула =B5:B15*5, на 5 будет умножено значение из ячейки B10, потому что ячейки B10 и C10 находятся в одной строке.).

Некоторые примеры поясняют, какие изменения происходят в трехмерных ссылках при перемещении, копировании, вставке и удалении листов, на которые такие ссылки указывают. Предположим, что используется формула " =СУММ(Лист2:Лист6!A2:A5)", суммирующая содержимое ячеек с A2 по A5 с лист2 по лист6 включительно.

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

Если между листом 2 и листом 6 книги удалить листы, Microsoft Excel исключит из суммы содержимое ячеек удаленных листов.

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

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

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

Также можно использовать стиль ссылок, в котором нумеруются как строки, так и столбцы. Стиль ссылок R1C1 полезен при вычислении положения столбцов и строк в макросах. В стиле ссылок R1C1 Microsoft Excel указывает положение ячейки буквой «R», за которой идет номер строки, и буквой «C», за которой идет номер столбца.

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

При записи макроса Microsoft Excel записывает некоторые команды с использованием стиля ссылок R1C1. Например, если записывается такая команда как нажатие кнопки Автосумма для вставки формулы, суммирующей диапазон ячеек, Microsoft Excel использует при записи формулы стиль ссылок R1C1, а не A1.

Чтобы включить или выключить стиль ссылок R1C1 следует вызвать меню кнопки Office, далее нажать кнопку Параметры Excel, в окне диалога Параметры Excel перейти на вкладку Формулы, там установить или снять флажок Стиль ссылок R1C1.

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

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

· выделить ячейку (ячейки);

· выбрать на ленте вкладку Формулы;

· в группе Определеные имена выбрать инструмент Присвоить имя и команду Присвоить имя;

· в диалоговом окне Создание имени, в поле Имя ввести имя выделенной ячейки (ячеек).

Имя диапазона не может начинаться с цифры, быть схожим с адресом ячейки (например, FQ1999) и содержать пробелы или знаки препинания. Вместо пробелов можно использовать символ подчеркивания _. Первый_Квартал_2013. Несмотря на то, что Excel позволяет присваивать ячейкам имена, состоящие из сотен символов, для более легкого запоминания лучше использовать короткие имена.

 

Контрольные вопросы

1. Дайте определение функции.

2. Дайте определение формулы.

3. Дайте определение оператора, назовите основные операторы и последовательность их выполнение.

4. С какого оператора начинается в ячейке формула?

5. Какие функции поддерживает автовычисление?

6. Какие существуют способы редактирования формул?

7. Что такое ссылка?

8. Какие ссылки существуют в MS Excel?

9. Приведите пример применения относительных ссылок.

10. Приведите пример применения абсолютных ссылок.

11. Что такое смешанная ссылка, привести пример.

12. Как присвоить имя ячейке и для чего это нужно?

13. Как задать ссылку на диапазон ячеек?

14. Каким образом осуществляется связывание данных на разных рабочих листах?

15. Как можно вставить в формулу ссылку на именованную область?