Профессиональные приемы работы в  Microsoft Excel

Top.Mail.Ru

Яндекс.Метрика

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

Стили ссылок 

Ссылки в 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,0

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,0

100,0

Как рассчитать долю каждого продукта в выручке?

В ячейку 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 находятся в одной строке.)