5.2. Относительная и абсолютная адресация клеток

В записи формул обычно используются адреса ячеек. Соответственно при вычислениях в формулу подставляется содержимое этих ячеек. Если оно меняется, то меняется и значение формулы. Мы рассматривали адрес ячейки, как имя столбца и номер строки. Такой адрес называется относительным. Фактически он указывает расстояние от ячейки, содержащей формулу, до ячейки, на которую есть ссылка в этой формуле. Особенность заключается в том, что при копировании формулы в другие ячейки относительные адреса настраиваются в соответствии с заданным расстоянием. Или другими словами: при копировании формулы, содержащей относительный адрес в любое место листаExcel(либо на другой лист, или даже в другую книгуExcel), данный адрес будет меняться относительно нового местоположения формулы.

Рассмотрим пример. На рисунке показан фрагмент таблицы, в которой в ячейку А4 занесена формула суммирования ячеек А1:А3. Используемые относительные адреса показывают, что суммируется содержимое трех ячеек, расположенных сверху над текущей, содержащей формулу (рис. 5.4.).

После выполнения операции копирования содержимого ячейки А4 в ячейку В4 произойдет настройка относительных адресов, т.е. будут суммироваться три ячейки вверх, но уже над ячейкой В4. (рис. 5.5)

Таким образом, относительные адреса удобны тем, что при вводе одинаковых формул использующих лишь различные диапазоны ячеек, мы их можем не вводить с клавиатуры, быстро вставить с помощью операции копирования.

Но бывают случаи, когда адрес ячейки, на которую ссылается формула, должен оставаться неизменным при выполнении копирования формулы. Тогда используют, так называемый, абсолютныйадрес ячейки. Он вводится со знаком $. Например, $A$5; $F$6; $D$4. Могут использоваться и смешанные адреса: $A7;D$6. Если знак доллара стоит только возле номера строки, то при копировании номер строки не изменяется, а имя столбца изменяется. Если только имя столбца записано со знаком доллара, то не настраивается только столбец. Рассмотрим фрагмент таблице, в которой используется формула с абсолютным адресом ячейки, и что произошло с формулой после операции копирования в соседнюю ячейку. Относительные адреса изменились относительно нового местоположения формулы, а абсолютный не изменился (рис. 5.6).

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

5.3. Имя ячейки. Связывание листов посредством формул

Для упрощения просмотра и запоминания формул одной или нескольким ячейкам можно присвоить имя. Если в листе присутствуют заголовки строк и столбцов, то на них можно сослаться в формуле или использовать как имена, связанные с ячейками. Кроме того, можно создать имя, которое представляет одинаковые ячейки или группу ячеек на нескольких листах. Выделите ячейку, группу ячеек или несмежный диапазон, которому необходимо присвоить имя. Установите курсор в поле имени, которое расположено слева в строке формул. Введите имя ячеек. Нажмите клавишу ENTER.

При редактировании содержимого ячейки ей нельзя присвоить имя.

Использование имен может упростить понимание формулы. Например, формула «=СУММ(Продано_в_первом_квартале)» проще для понимания, чем формула «=СУММ(Продажа!C20:C30)». В этом примере имя «Продано_в_первом_квартале» представляет группу ячеек «C20:C30» на листе «Продажа».

Имена можно использовать на любом листе книги. Например, если имя «Контракты» ссылается на группу ячеек «A20:A30» на первом листе рабочей книги, то это имя можно применить в любом другом листе той же рабочей книги для ссылки на эту группу. Имя можно присвоить формуле или постоянному значению (константе). Например, имя «ПроцентнаяСтавка», которому присвоено значение 6.2 процента, можно использовать в любом месте для вычисления процентов. По умолчанию имена являются абсолютными ссылками.

Использование в формулах данных других листов и книг. Используя связывание или внешние ссылки, можно обмениваться данными, расположенными в разных листах и книгах. Связывание особенно полезно в тех случаях, когда отсутствует практика хранения больших моделей и систем расчетов в одной книге. Связывание не ограничено связыванием двух книг. Можно создавать иерархии связанных книг. В следующем примере (рис. 5.7) функция СРЗНАЧиспользуется для вычисления среднего значения диапазона B1:B10 на листе «Маркетинг».

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

1. слияния данных нескольких книг. С помощью связывания книг отдельных пользователей или коллективов распределенные данные можно интегрировать в одну итоговую книгу. Исходные книги по-прежнему могут изменяться независимо от итоговой книги;

2. создания различных представлений одних и тех же данных. Все данные и формулы можно ввести в одну книгу или несколько книг и затем, создать книгу отчетов по данным исходных книг;

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

Создание формулы, использующей значения другого листа или книги. Укажите ячейку, которая будет содержать внешнюю ссылку. Если создается новая формула, введите знак равенства (=). Если внешняя ссылка является частью формулу, то перед ней можно ввести оператор или функцию. Если связываются листы рабочей книги, укажите лист, содержащий связываемые ячейки. Если листы связываются с другой книгой, перейдите в эту книгу и укажите лист, содержащий связываемые ячейки. Выделите связываемые ячейки. Для завершения ввода формулы нажмите клавишу ENTER.