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

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

Ошибки в формулах

Ошибки при составлении формул можно разделить на две категории. Во-первых, это логические ошибки в формулах, не дающие сбоев при вычислении, но приводящие к неверным результатам. Эти ошибки не выявляются Excel. Их надо искать самим, проверяя на контрольных примерах. Во-вторых, это ошибки, которые распознает Excel и выдает стандартные сообщения об ошибках. Сообщения об ошибках начинаются со знака #.

#ДЕЛ/0! – попытка деления на ноль. Например, в формуле делается попытка в качестве делителя использовать ячейку, содержащую нулевое значение.

#ЗНАЧ! – недопустимый тип аргумента. Например, вместо числового аргумента или логического используется текстовый или для оператора или функции, требующей одного значения, задается диапазон значений.

#ИМЯ? – в формуле используется ссылка на отсутствующее имя области данных или неверно задано имя функции. Часто причиной может быть ввод адресов ячеек русскими, а не латинскими буквами.

#Н/Д (сокращение термина «Неопределенные Данные») – использование ссылки на пустую ячейку.

#ПУСТО! – в формуле задано пересечение двух интервалов, которые на самом деле не имеют общих ячеек.

#ЧИСЛО! – в числовых формулах используется недопустимый аргумент. Например, отрицательное подкоренное выражение или текстовый аргумент.

#ССЫЛКА! – используется недопустимая ссылка на ячейку. Например, ячейки, на которые ссылаются в формуле, были удалены или в эти ячейки было помещено содержимое других ячеек.

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

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

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

Пусть требуется в ячейку А 3 ввести формулу А 1+ А 2. Пусть также в ячейке А 1 записано число 3, а в ячейке А 2 – число 7. Чтобы ввести формулу в ячейку А 3 ее надо выделить (активизировать), набрать

=А1+А2

и нажать Enter. В ячейке А 3 появится число 10. Если выделить ячейку A 3, в строке формул появится только что введенная формула (содержимое ячейки) А 3 рисунок 200. В режиме показа формул будем иметь рисунок 201:

Рисунок 200

Рисунок 201

Задания.

1. Перейдите в режим решения.

2. Введите в ячейку А 1 число 7. Обратите внимание на то, что в ячейке А 3 появился новый результат: 14.

3. Отмените предыдущее действие, нажав кнопку . В ячейке А 3 появится прежний результат: 7.

4. Оставьте в ячейке А 1 число 3, в ячейке А 2 – число 7, в ячейку В 1 введите число 2; в ячейку В 3 введите формулу =(А1+А2)/В1*6. Формулу можно ввести заново, либо отредактировать уже имеющуюся.

4. Нажмите Enter, в ячейке В 3 появится число 30. Выделите ячейку В 3 – в строке формул будет содержимое ячейки.

При вводе в формулу ссылок вручную необходимо убедиться, что клавиатура находится в режиме ввода латинских символов, иначе вместо результата будет выведено #ИМЯ?, что указывает на ошибочный ввод рисунок 202.

Рисунок 202

Для уменьшения количества ошибок, допускаемых при непосредственном вводе адресов ячеек в формулу, целесообразно использовать мышь. Например, если требуется ввести в ячейку С 5 формулу =А4+В5, можно:

- выделить ячейку С5 и набрать знак равенства,

- щелкнуть по ячейке А4

- набрать знак плюс,

- щелкнуть по ячейке В5 (в формулу будет введена ссылка на ячейку А),

- нажать Enter.

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

В Excel существует два основных типа адресации данных (ссылок): относительные и абсолютные.

Относительная ссылка определяет адрес ячейки по отношению к активной ячейке. Пусть, например, в ячейке В 2 имеется ссылка на ячейку А 3. В относительном представлении ссылка А 3 указывает на ячейку, которая расположена на один столбец левее и на одну строку ниже данной ячейки рисунок 203.

Рисунок 203

Если формула будет скопирована в другую ячейку, то такое относительное указание ссылки сохранится. Например, если формула будет скопирована в ячейку С 3, ссылка будет продолжать указывать на ячейку, расположенную на один столбец левее и на одну строку ниже данной ячейки, то есть на ячейку В 4 рисунок 204.

Рисунок 204

Если выполнить копирование содержимого ячейки В 2 в разные стороны, можно увидеть, что копии будут отличаться от содержимого копируемой ячейки рисунок 205 – режим показа формул). При копировании по горизонтали изменяется имя столбца, при копировании по вертикали – номер строки, при копировании по диагонали – и то и другое. Часто такие изменения адресов нужны при копировании ячеек, чтобы ссылки на ячейки корректировались автоматически.

Рисунок 205

По умолчанию ссылки на ячейки в формулах рассматриваются как относительные.

Абсолютная ссылка задает адрес ячейки в соответствии с его положением на рабочем листе. Перед элементами номера ячейки, использующей абсолютную адресацию, ставятся символы доллара $. Например, для ячейки, расположенной на пересечении столбца В и строки 2, абсолютный адрес буде записан следующим образом: $В$ 2. При копировании формулы абсолютные адреса в них не меняются, например, при копировании формулы из ячейки В 3 в ячейку С 4 абсолютный адрес $В$ 2 не изменился рисунок 206 – режим показа формул.

Рисунок 206

В формулах можно создавать смешанные ссылки. Смешанная ссылка содержит и относительную, и абсолютную компоненты адреса ячейки. Например ссылка В$ 3 по столбцу относительная, а по строке абсолютная, поэтому она сохраняет адрес при копировании формулы, в которую она входит, по вертикали, а при копировании по горизонтали в третьей строке меняется адрес столбца рисунок 207 – режим показа формул).

Рисунок 207

Таким образом, ссылки на ячейку В 3 записываются:

- относительная ссылка: =В3

- абсолютная ссылка: =$В$3

- смешанные ссылки: =$В3 =В$3.

Если перед символом стоит знак $, то соответствующие компоненты адреса являются абсолютными (при копировании не меняются), если знака $ нет – относительными (при копировании могут меняться).

Для быстрого изменения типа ссылки используется клавиша F4. Для изменения способа адресации (относительного в абсолютный) надо выделить адрес в формуле и нажать кнопку F4. При последовательном нажатии клавиши F4 номер ячейки В 3 будет записываться как $В$ 3, В$ 3, $В 3.

Для ускорения ввода абсолютных ссылок и избежания ошибок следует использовать клавишу F4.

Например.

1. Выделите ячейку С 2 и наберите =А2+В2.

2. Нажмите F4. Формула примет вид =А2+$В$2.

3. Еще раз нажмите F4 – ссылка станет смешанной и формула примет вид =А2+В$2 (относительный адрес столбца и абсолютный адрес строки).

4. Еще раз нажмите F4 – ссылка преобразуется и формула примет вид =А2+$В2 (абсолютный адрес столбца и относительный адрес строки).

5. Еще раз нажмите F4, и вы вернетесь к исходному относительному типу адресации.

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

Дата добавления: 2014-10-17