Создание формул и анализ данных – ЧАСТЬ 2

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 с: ил.

Похожие посты:

Вы можете оставить комментарий, или ссылку на Ваш сайт.

Оставить комментарий