Использование ссылок в формулах

Ссылка является идентификатором ячейки или группы ячеек в книге. Создавая формулу, содержащую ссылки на ячейки, вы связываете формулу с ячейками книги. Значение формулы зависит от содержимого ячеек, на которые указы­вают ссылки, и оно изменяется при изменении содержимого этих ячеек.

Например, введём формулу, которая содержит ссылку на ячейку.

1. Выделите ячейку А1 и введите следующее: =10*2

2. Теперь выделите ячейку А2 и введите формулу:=А1

Значения в обеих ячейках равны 20. Если вы измените значение в ячейке А1, значение в ячейке А2 также изменит­ся.

3. Теперь выделите ячейку A3 и введите:=А1+А2 Excel выведет значение 40.

 

Ввод ссылок с использованием мыши

Вы сэкономите время и избежите многих ошибок, если будете вводить в формулах ссылки на ячейки, выделяя ячей­ки с помощью мыши, а не набирая ссылки на клавиатуре. Например, чтобы ввести в ячейку В10 формулу со ссылками на ячейки А9 и А10, выполните описанные ниже действия.

1. Выделите ячейку В10 и введите знак равенства.

2. Щелкните на ячейке А9 и введите знак плюс.

3. Щелкните на А10 и нажмите клавишу Enter.

Когда вы щелкаете на ячейке, вокруг нее появляется под­вижная рамка и ссылка на эту ячейку вставляется в формулу. По окончании ввода формулы нажмите клавишу Enter. Если этого не сделать и выделить другую ячейку, Excel предполо­жит, что вы хотите вставить в формулу ссылку на выделенную ячейку.

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

Если после прокрутки активная ячейка не видна, вы мо­жете снова вывести ее на экран, нажав клавиши Ctrl+BackSpace.

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

Относительная ссылка указывает на ячейку, основыва­ясь на ее положении относительно ячейки, в которой находится формула, например, «на две строки выше».

Абсолютная ссылка используется для указания на ячей­ку ее фиксированное положение на листе, например, «ячейка находится в столбце А строки 2».

Смешанная ссылка содержит относительную и абсо­лютную ссылку, например, «ячейка находится в столбце А и выше на две строки». Абсолютные и смешанные ссылки осо­бенно полезны при копировании формулы из одного места листа в другое.

Относительная ссылка на ячейку А1 записывается так:=А1

Абсолютная ссылка на ячейку А1 имеет следующий вид:=$А$1

Комбинируя абсолютные и относительные ссылки на ячейку А1, можно создать следующие смешанные ссылки:

=$А1

=А$1

Если символ доллара стоит перед буквой, то координата столбца абсолютная, а строки - относительная.

Если символ доллара стоит перед числом, то, напротив, координата столбца относительная, а строки — абсолютная.

С помощью клавиши F4 можно быстро изменить тип ссылки.

1. Выделите ячейку А1 и введите: =В1+В2

2. Нажмите клавишу F4, чтобы ссылку, находящуюся ря­дом с точкой вставки в строке формул, изменить на абсолютную. Формула примет вид:=В1+$В$2

3. Снова нажмите F4, чтобы сделать эту ссылку смешан­ной (относительная координата столбца и абсолютная координата строки). Формула примет вид:=В1+В$2

4. Еще раз нажмите F4, чтобы реверсировать эту сме­шанную ссылку (абсолютная координата столбца и относительная координата строки). Формула примет вид:=В1+$В2

5. Снова нажмите F4, чтобы вернуться к исходной отно­сительной ссылке.

 

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

Формулы редактируются таким же образом, как и тексто­вые значения. Для удаления ссылки или других символов из формулы выделите в ячейке или в строке формул нужные символы и нажмите клавишу ВаскSрасе или Delete. Чтобы заменить одну ссылку на другую, выделите заменяемую ссылку и щелкните на ячейке, на которую хотите сослаться, и нажмите клавишу Enter.

Вы также можете вставить в формулу дополнительные ссылки. Например, чтобы вставить ссылку на ячейку В1 в формулу

=А1+А3

просто установите точку вставки в нужном месте, скажем между А1 и знаком плюс, и введите

+В1

или введите знак плюс и щелкните на ячейке В1. Формула примет вид:

=А1+В1+А3

Если ввод еще не зафиксирован, можно отказаться от из­менений, нажав кнопку отмены или клавишу Esc. Если вы зафиксировали ввод, выберите команду Отменить, нажмите клавиши Ctrl+Z или кнопку Отменить на панели быстрого доступа.

Команда Отменить помнит 16 последних изменений. Нажимая кнопку Отменить несколько раз, можно отменить эти изменения одно за одним. Аналогично, команда Вернуть позволяют отказаться от последних 16 отмен.

Рис.4.1.

 

Использование числового текста в формулах

С текстом можно выполнять математические операции, если текстовые значения содержат только следующие симво­лы:

0123456789,+-Ее

Кроме того, можно использовать символ «/» для записи дробей и пять символов числового форматирования:

1. $

2. пробел

3. %

4. (

5.)

Но при этом числовой текст должен быть заключен в двойные кавычки. Например, при вводе формулы =$1234+$123

Excel выведет сообщение Ошибка в формуле и подска­жет способ исправления ошибки. В то же время, результатом применения формулы

="$1234"+"$123" является число 1357 (без знака доллара). При выполнении сложения Excel автоматически преобразует числовой текст в числовые значения.

 

Текстовые значения.

В большинстве случаев работа с текстовыми значениями происходит так же, как с числами. Например, если ячейка А1 содержит текст ABCDE и в ячейку А10 введена формула

=А1

то в ячейке А10 также будет отображено ABCDE. Поскольку этот тип формулы обращается с текстовой строкой как со значением, она называется текстовым значением.

Для объединения текстовых значений используется тек­стовый оператор & (амперсанд). Например, если ячейка А2 содержит текст FGHTJ и в ячейку A3 введена формула

=А1&А2,

то в ячейке A3 будет выведено ABCDEFGHIJ. Чтобы вста­вить пробел между двумя строками, измените формулу на следующую: =А1&" "&А2

Последняя формула использует два текстовых оператора и текстовую константу - в данном случае пробел, заклю­ченный в двойные кавычки.

С помощью оператора & можно объединять и числовые значения. Например, если ячейка A3 содержит числовое зна­чение 123 и в ячейке А4 записано число 456, значением формулы

=АЗ&А4

будет строка символов 123456. Эта строка выравнивается в ячейке по левому краю, так как она является текстовым зна­чением.

Кроме того, можно использовать оператор & для объеди­нения текстовых и числовых значений. Например, если ячейка А1 содержит текст АВСОЕ и к ячейке A3 записано число 123, то значением формулы

=А1&АЗ будет строка АВСОЕ123.