June 13th, 2012 
admin
5. Щелкните на ярлычке Листа2 в нижней части окна Книги2.
6. Щелкните на ячейке А2. Вид экрана в этот момент показан на рис. 12.1.
7. Нажмите клавишу Enter, чтобы зафиксировать ссылку.
Стиль ссылок R1C1
Данный стиль ссылок означает, что Excel ссылается на ячейки по номерам строк и столбцов. Например, ссылку вида R1C1 надо понимать как строка (Row) 1, столбец (Column) 1. То есть запись R1C1 обозначает ссылку на ту же самую ячейку, что и относительная ссылка А1. Хотя обычная нотация используется чаще, в некоторых программах обработки электронных таблиц, таких как Multiplan, стандартом является запись ссылок в виде R1C1. Excel ориентируется на буквенно-цифровую нумерацию столбцов и строк, например А1 и Z100.
Для активизации подобного стиля записи ссылок выберите в меню Сервис (Tools) команду Параметры (Options), перейдите на вкладку Общие (General) открывшегося диалогового окна и установите флажок Стиль ссылок R1C1 (R1C1 Reference Style). После этого все ссылки в формулах примут указанный вид. Например, ячейка М10 станет R10C13, а ячейка IV65536 (последняя ячейка листа) превратится в R65536C256.
Рис. 12.1. Создание внешней ссылки с помощью мыши
В синтаксисе R1C1 учитывается скорее относительное расположение ячейки, на которую ссылается формула, чем ее фактическое (абсолютное) местоположение на листе. Так, предположим, что вы,хотели бы ввести в ячейку R10C2 (В10) формулу, которая складывает значения в ячейках R1C1 (А1) и R1C2 (В1). Для этого выделите ячейку R10C2, введите знак равенства, выделите ячейку R1C1, введите знак «плюс», затем выделите ячейку R1C2 и нажмите клавишу Enter. В строке формул появится выражение
=R[-9]C[-1]+R[-9]C. Отрицательные номера строк и столбцов в квадратных скобках говорят о том, что ячейка, на которую указывает ссылка, находится выше и левее ячейки с формулой. Соответственно положительные значения, наоборот, показывают, что целевая ячейка находится ниже и правее ячейки с формулой. Квадратные скобки обозначают относительные ссылки. Таким образом, данная формула может читаться следующим образом: «Сложить ячейку, расположенную на девять строк выше и один столбец левее ячейки с формулой, с ячейкой, находящейся на девять строк выше в том же столбце».
В относительной ссылке номера должны быть заключены в квадратные скобки, в противном случае Excel будет считать, что вы используете абсолютные ссылки. Например, запись =R8C1+R9C1 подразумевает абсолютную адресацию ячеек в строках 8 и 9 столбца 1.
Поведение ссылок при копировании формул
Одно из полезнейших свойств формул — это возможность их копировать и вставлять в другие ячейки листа. Но прежде чем воспользоваться ею, необходимо четко представлять себе, как ведут себя ссылки в момент вставки, чтобы получить предсказуемый результат.
Копирование относительных ссылок
Когда вы копируете ячейку, содержащую формулу с относительной ссылкой, эта ссылка изменяется автоматически в зависимости от местоположения целевой ячейки для вставки формулы. Посмотрите на рис. 12.2 — мы ввели в ячейку F4 формулу =СРЗНАЧ(В4:Е4), которая усредняет значения в указанном диапазоне и выводит результат в той же ячейке F4.
Рис. 12.2. Ячейка F4 содержит относительную ссылку на диапазон ячеек слева
Теперь нужно закончить вычисление средних значений и для остальных ячеек этого столбца. Вместо того чтобы каждый раз вводить в ячейку столбца F новую формулу, проще просто скопировать ее. Для этого выделите ячейку F4 и выберите в меню Правка (Edit) команду Копировать (Сору). Затем выделите диапазон F5:F8 и в меню Правка воспользуйтесь командой Специальная вставка (Paste Special). Установите переключатель Вставить (Insert) в положение Формулы и форматы чисел (Formulas And Number Formats); это позволит избежать ненужного форматирования выделенных ячеек. Результат операции представлен на рис. 12.3. Теперь в каждой ячейке столбца F содержится формула, вычисляющая среднее значение в соответствующей строке этой таблицы. Например, ячейка F7 содержит формулу =СРЗНАЧ(В7:Е7).
Рис. 12.3. Мы продублировали относительную ссылку из ячейки F4 в ячейки диапазона F5:F8
Копирование абсолютных ссылок
Если вы хотите, чтобы при копировании формул ссылка на ячейку не менялась, используйте не относительные, а абсолютные ссылки. Так, на рис. 12.4 слева в ячейку В2 введено значение ставки почасовой оплаты труда, а в ячейке С5 содержится формула
с относительной ссылкой: =В2*В5. Предположим, вы хотите скопировать формулу из ячейки С5 в диапазон Сб:С8. На рис. 12.4 справа показано, что произойдет, если вы попытаетесь это сделать: Excel выдаст сообщение об ошибке. Казалось бы, формулы в диапазоне Сб:С8 должны ссылаться на ячейку В2, но на самом деле этого не происходит, и в ячейке С8 можно увидеть совершенно неправильную запись, наподобие =В5*В7.
Рис. 12.4. Формула в ячейке С5 содержит относительную ссылку. Мы скопировали эту формулу в ячейки С6:С8 и получили неверный результат
Дело в том, что ссылка на ячейку В2 в исходной формуле относительная, и при копировании формулы в другие ячейки ссылка также меняется. Чтобы вычисления выполнялись без ошибки, в данном случае нужно использовать не относительную, а абсолютную ссылку на ячейку В2.
Чтобы изменить тип ссылки, выделите еще раз ячейку С4, щелкните в строке формул на ссылке на ячейку В2 и нажмите клавишу F4. В результате формула примет вид
=$В$2*В5.
Теперь при копировании этой формулы в диапазон С6:С8 изменяться будет только относительная ссылка на ячейку В5, а абсолютная ссылка на ячейку В2 останется как есть. Таким образом, в интересующей вас ячейке, например С7 (рис. 12.5), будет получена корректная формула =$В$2*В7.
Рис. 12.5. Прежде чем копировать формулу, преобразуйте относительную ссылку В2 в абсолютную — $В$2
Копирование смешанных ссылок
В формулах допускается использовать смешанные ссылки, что позволяет фиксировать только определенную часть ячеек (как вы помните, в смешанных ссылках одна часть ссылки абсолютная, а другая — относительная). При копировании формулы
со смешанной ссылкой Excel жестко привязывает абсолютную часть этой ссылки и изменяет относительную в соответствии с местоположением конечной ячейки.
Чтобы создать ссылку смешанного типа, нажмите клавишу F4 столько раз, сколько нужно для выбора требуемого типа смешанной ссылки. Например, от ссылки В2 можно перейти к $В$2, затем — к В$2 и наконец к последнему варианту — $В2.
При расчете отчислений по кредиту в таблице, показанной на рис. 12.6, используются два типа ссылок: смешанные и абсолютные. Достаточно ввести только одну формулу в ячейку Сб, а затем просто распространить ее на весь диапазон C6:F10. Мы в эту ячейку занесли формулу =-ПЛТ($В6,$С$3,С$5), вычисляющую ежегодные отчисления по кредиту в сумме $10 000 на период 15 лет при кредитной ставке 6%, и тем самым оценили возможные выплаты при других вариантах интересующих нас процентных удержаний.
Рис. 12.6. В этой таблице используются ссылки смешанного типа
Первая ссылка в формуле ($В6) указывает, что при расчете всегда следует брать значение из столбца В, но ссылка на строку является относительной, поэтому может изменяться. Точно так же, смешанная ссылка С$5 означает, что при расчете всегда используются значения строки 5, но ссылка на столбец не фиксирована. Например, ячейка Е8 после копирования формулы в диапазон C6:F10 будет содержать формулу =ПЛТ($В8,$С$3,Е$5). Если бы не смешанные ссылки, пришлось бы для каждой ячейки этого диапазона вводить формулы по отдельности.
РЕШЕНИЕ ПРОБЛЕМ –
Если вы используете функцию СУММ (SUM) в нижней строке таблицы, то при добавлении в таблицу новых строк (между исходными значениями и строкой с формулами) новые данные не будут учитываться в расчете. В данном случае вы столкнулись с вековой проблемой, свойственной любым электронным таблицам. Но в Excel есть возможность исправления этого недочета. Ссылка на диапазон в аргументе функции СУММ не изменяется при добавлении строк. Однако, если вы начнете вводить значения в новые добавленные ячейки, ссылка на диапазон автоматически обновится. Единственное условие — все значения нужно вводить за один раз, начиная с ячейки, расположенной непосредственно ниже указанного в формуле диапазона. Если начать ввод с середины группы новых ячеек, ссылка на диапазон в формуле суммирования останется той же. Подробнее о функции СУММ см. раздел
«Функция СУММ» главы 14.
Редактирование формул
Формулы редактируются так же, как и любые текстовые значения. Для удаления ссылки или других символов из формулы выделите в ячейке или в строке формул нужные символы и нажмите клавишу Backspace или Delete. Чтобы заменить одну ссылку другой, выделите заменяемую ссылку и щелкните на ячейке, на которую хотите сослаться в этой формуле; относительные ссылки заменятся автоматически.
Вы можете добавлять в формулы дополнительные ссылки. Например, чтобы вставить ссылку на ячейку В1 в формулу =А1+А3, просто установите указатель, скажем, между А1 и знаком плюса и введите +В1 (или введите знак «плюс», а затем щелкните на ячейке В1). В результате формула примет следующий вид: =А1+В1+А3.
Источник: Эффективная работа: Microsoft Office Excel 2003 / М. Додж, К. Стинсон — СПб.: Питер, 2005. — 1088 с: ил.
Опубликовано в рубрике