Стили ссылок
Ссылки в Excel бывают двух стилей:
- стиль А1
- и стиль R1C1
Ссылка стиля А1 использует буквенную нумерацию столбцов и числовую нумерацию строк. Ниже приведены примеры ссылок стиля А1 из справочной системы Excel:
|
Ячейка или диапазон |
Ссылка |
|
Ячейку в столбце 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 |
В стиле R1C1 и строки (rows), и столбцы (columns) обозначаются номерами. Например, R2C2 - абсолютная ссылка на ячейку, расположенную во второй строке и во втором столбце.
Включение режима ссылок стиля R1C1 происходит в меню Сервис - Параметры - Общие (галочка Стиль ссылок R1C1).
Смысл использования этого непривычного способа записи ссылок станет понятен, когда мы разберемся с относительными и абсолютными ссылками.
Абсолютные и относительные ссылки
В зависимости от поведения при копировании ячеек различают абсолютные, относительные и смешанные ссылки.
Относительные ссылки при копировании ячейки будут автоматически корректироваться, "сдвигаться" относительно ячейки так, что смещение влияющей ячейки (той, на которую мы ссылаемся) относительно зависимой (той, в которую введена формула сос ссылкой) останется прежним.
Например, в ячейку А1 введена формула =В1*2.
При копировании формулы в ячейку А2 она будет автоматически откорректирована на формулу =В2*2.
Как в первом, так и во втором случае, ссылка смещена относительно ячейки с формулой на одну ячейку вправо и находится в той же строке.
Если мы скопируем формулу в ячейку С1, формула превратится в =D1*2. Теперь корректировка коснулась столбца, а смещение осталось прежним.
Абсолютные ссылки при копировании не корректируются.
Формула из нашего примера в абсолютной нотации будет выглядеть так: =$B$1*2.
Теперь, куда бы мы ни копировали формулу, она все равно будет ссылаться на ячейку В1.
Вводить абсолютные ссылки просто - при вводе формулы после ввода ссылки (обычно это делается мышью - Вы открываете формулу знаком "=" и щелкаете мышью нужную ячейку. В формуле появляется относительная ссылка) нужно нажать клавишу F4. Ссылка преобразуется в абсолютную.
F4 - "закрепить" ссылку, преобразовать ее в абсолютную нотацию
Повторное нажатие F4 преобразует ссылку в смешанную. Преобразования происходят в такой последовательности:
А1 → $A$1 → $A1 → A$1 → A1
и дальше по кругу.
Вы уже наверняка догадались, что у смешанных ссылок "закреплены" либо столбец, либо строка, в зависимости от того, перед чем стоит знак доллара.
Изменить тип ссылки можно в любой момент - просто выделите в строке формул нужную ссылку и нажмите F4.
При использовании стиля ссылок R1C1 относительные ссылки записываются как R[1]C[1], где в квадратных скобках записано относительное смещение влияющей ячейки соответственно по вертикали и по горизонтали. R[1]C означает ссылку на ячейку на одну строку ниже в том же столбце. RC[-1] - ячейка на один столбец левее в той же строке. При копировании формула в другой ячейке останется неизменной, но ссылаться будет на другую, "смещенную", ячейку.
R1C1 - пример абсолютной ссылки. Если числа даны без квадратных скобок, они обозначают абсолютный номер строки.
Стиль R1C1 записи гораздо удобнее, например, если вы работаете с макросами и используете циклы обхода ячеек.
Зачем нужны абсолютные и смешанные ссылки?
Разберем пример.
Допустим, нам нужно проанализировать структуру продаж за два года.
Данные по продажам представлены в таблице следующего вида
|
|
A |
B |
C |
D |
E |
|
1 |
Наименование |
Объем |
Доля, % | ||
|
2 |
2005 г. |
2006 г. |
2005 г. |
2006 г. | |
|
3 |
Продукт 1 |
150 |
120 |
13,6 |
15,1 |
|
4 |
Продукт 2 |
155 |
160 |
14 |
20,1 |
|
5 |
Продукт 3 |
120 |
110 |
10,9 |
13,8 |
|
6 |
Продукт 4 |
10 |
50 |
0,9 |
6,3 |
|
... |
... |
... |
... |
... |
... |
|
100 |
Продукт 98 |
655 |
200 |
59,3 |
25,2 |
|
101 |
Продукт 99 |
15 |
155 |
1,4 |
19,5 |
|
102 |
ИТОГО |
1105 |
795 |
100 |
100 |
Как рассчитать долю каждого продукта в выручке?
В ячейку D3 вводим формулу =B3/B$102%
Теперь достаточно скопировать формулу в столбец E и все строки таблицы.
Трехмерные ссылки
Трехмерные ссылки используются при необходимости анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов. Microsoft Excel использует все листы, хранящиеся между начальным и конечным именами, указанными в ссылке. Например, формула =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5 на всех листах в диапазоне от Лист2 до Лист13 включительно.
Трехмерные ссылки могут быть использованы для создания ссылок на ячейки на других листах, определения имен и создания формул с использованием следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЁТ, СЧЁТЗ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СТАНДОТКЛОН, СТАНДОТКЛОНА, СТАНДОТКЛОНП, СТАНДОТКЛОНПА, ДИСП, ДИСПА, ДИСПР и ДИСПРА.
Операторы ссылки
|
Оператор ссылки |
Значение (пример) |
|
: (двоеточие) |
Ставится между ссылками на первую и последнюю ячейки диапазона. Такое сочетание является ссылкой на диапазон (B5:B15) |
|
; (точка с запятой) |
Оператор объединения. Объединяет несколько ссылок в одну ссылку (СУММ(B5:B15;D5:D15)) |
|
(пробел) |
Оператор пересечения множеств, служит для ссылки на общие ячейки двух диапазонов (B7:D7 C6:C8). (Также возможно неявное пересечение - ссылка на диапазон ячеек вместо одной ячейки, из которого в расчете выбирается одна соответствующая ячейка. Например, если в ячейке C10 содержится формула =B5:B15*5, на 5 будет умножено значение из ячейки B10, потому что ячейки B10 и C10 находятся в одной строке.) |