ССЫЛКИ в MS Excel
По умолчанию MS Excel создает в формулах относительные ссылки. На рисунке 1 показана рабочая таблица с формулой, введенной в ячейку D2. Эта формула, в которой по умолчанию используются относительные ссылки, выглядит следующим образом: = В2 * С2.
Рис. 1. Пример рабочей таблицы с формулой, введенной в ячейку D2
Если скопировать эту формулу в две ячейки, расположенные под ячейкой D2, то MS Excel вставит копии приведенной формулы, при этом видоизменит их следующим образом:
§ В ячейку D3 будет скопирована формула = В3 * СЗ;
§ В ячейку D4 будет скопирована формула = В4 * С4.
Другими словами, MS Excelизменяет ссылки на ячейки в соответствии с новым положением формулы.
Если вырезать и вставить (переместить) формулу на другое место, то содержащиеся в ней адреса ячеек не будут изменены.
Однако бываю случаи, когда необходимо, чтобы адреса ячеек копировались без изменений. Для этого используют абсолютные ссылки. Пример формулы, содержащей абсолютную ссылку, показан на рисунке 2.
Рис. 2. Формула, в которой используется абсолютная ссылка на ячейку B6
В данном примере в ячейке В6 содержится значение ставки налога на продажу. Формула в ячейке D2 выглядит следующим образом: = В2 * С2 * $В$6
В ссылке на ячейку В6 перед буквой столбца и перед номером строки стоят знаки доллара. Это означает, что данная ссылка является абсолютной. При копировании этой формулы в две ячейки снизу будут получены следующие формулы.
§ В ячейку D3 будет скопирована формула = B3 * C3 * $B$6;
§ В ячейку D4 будет скопирована формула = B4 * C4 * $B$6.
Как видно из примера, относительные ссылки изменились, тогда, как ссылка на ячейку B6 осталась прежней, поскольку она является абсолютной.
В абсолютной ссылке используется два знака доллара: один — перед именем столбца, а другой — перед номером строки. Однако в MS Excelсуществуют также смешанные ссылки, в которых только одна часть адреса (относящаяся к строке или к столбцу) является абсолютной. Приведем все возможные типы ссылок на ячейки:
§ A1 - относительная ссылка;
§ $A$1 - абсолютная ссылка;
§ $A1 - смешанная ссылка (абсолютной является координата столбца);
§ A$1 - смешанная ссылка (абсолютной является координата строки).
 |
На рисунке 3 показана таблица, в каждой ячейке которой должно находиться значение, взятое из столбца А, умноженное на значение из строки 1. Формула в ячейке В2 выглядит следующим образом: = B$1 * $A2.
Рис. 3. Пример использования смешанных ссылок на ячейки
Приведенная формула содержит две смешанные ссылки на ячейки. В ссылке B$1 координата строки является абсолютной, тогда как координата столбца — относительной. А в ссылке $А2, наоборот, координата строки является относительной, тогда как координата столбца — абсолютной. Данную формулу можно скопировать в диапазон В2: Е5, и в каждой ячейке будут содержаться правильные формулы. Например, формула в ячейке Е5 будет выглядеть следующим образом: = Е$1 * $А5.
Неотносительные (т.е. абсолютные или смешанные) ссылки можно ввести вручную, вставив в нужных местах знаки доллара. Можно также воспользоваться удобным клавиатурным эквивалентом — клавишей F4. При вводе ссылки на ячейку — либо вручную, либо путем, указания — нужно нажать клавишу F4 несколько раз, чтобы программа "прокрутила" по циклу все четыре типа ссылок.
Например, если в начале формулы ввести = A1, то первое нажатие клавиши F4 преобразует ссылку на ячейку в $А$1, второе — в А$1, третье - в $А1, а четвертое вернет ей первоначальный вид — А1. Нажимать клавишу F4 необходимо до тех пор, пока не появится нужный тип ссылки.
Пример 1. Составить таблицу, вычисляющую n-й член и сумму арифметической прогрессии.
Формула n-го члена арифметической прогрессии: an = a1 + d * (n - 1);
формула суммы n первых членов арифметической прогрессии: Sn = (a1 + an) * n / 2, где a1 - первый член прогрессии, d - разность арифметической прогрессии.
На рисунке 4 приведен пример таблицы для вычисления n-го члена и суммы арифметической прогрессии.
 |
Рис. 4. Пример таблицы для расчета n-го члена и суммы арифметической прогрессии
Методика выполнения примера
§ Введите в ячейку A1 заголовок таблицы. Нажмите Enter. Активизируйте ячейку A1 и выполните команду Формат | Ячейки, на вкладке Выравнивание в разделе Отображение выбрать Переносить по словам.
§ В ячейку A2 введите d; в ячейку В2 - n; в ячейку C2 - an (для набора нижних индексов воспользуйтесь командой Формат | Ячейки, на вкладке Шрифт активизируйте переключатель Нижний индекс в группе переключателей Видоизменение); в ячейку D2 введите Sn.
§ В ячейку A3 введите значение разности арифметической прогрессии (d).
§ В ячейку B3 введите 1, выделите эту ячейку и, удерживая нажатой левую клавишу мыши на маркере заполнения и клавишу Ctrl, заполните диапазон значениями от 1 до 10.
§ В ячейку C3 введите произвольное значение первого члена арифметической прогрессии.
§ В ячейку C4 введите формулу: = $C$3 + $A$3 * (B4 - 1). Скопируйте формулу до ячейки C12.
§ В ячейку D4 введите формулу: = ($C$3 + C3) * B3 / 2. Скопируйте формулу до ячейки D12.
ОШИБКИ В ФОРМУЛАХ
Иногда при вводе формулы MS Excelвыдает значение, которое начинается с символа #. Это говорит о том, что данная формула возвращает ошибочное значение. В подобном случае необходимо исправить формулу (или ссылку на ячейку, содержащуюся в этой формуле), чтобы избавиться от сообщения об ошибке. MS Excelчасто предлагает свой вариант исправления ошибочной формулы.
Если ячейка полностью заполнена знаками решетки (#), это означает, что столбец недостаточно широк, чтобы отобразить некоторую величину. В этом случае нужно или расширить столбец, или изменить числовой формат ячейки.
Формулы могут возвращать ошибки и в том случае, если ячейки, на которые в них есть ссылки, содержат ошибки. Этот эффект носит название цепной реакции — когда единственное ошибочное значение может породить ошибки во многих других ячейках, содержащих формулы со ссылкой на эту ячейку.
| Значения ошибок | |
| Ошибка | Описание |
| #ДЕЛ/0! | 1) Формула пытается выполнить деление на 0. 2) Формула пытается выполнить деление на содержимое пустой ячейки. |
| #ИМЯ? | В формуле используется имя, которое Excel не может распознать. Например, удалено имя, используемое в формуле, или при вводе текста где-то пропущена кавычка. |
| #Н/Д | Неопределенные данные. Формула ссылается (прямо или косвенно) на ячейку, содержащую функцию, ссылающуюся на недопустимый тип данных. |
| #ПУСТО! | В формуле используется пересечение двух диапазонов, которые на самом деле не пересекаются. |
| #ЧИСЛО! | Проблема связана со значением. Например, Вы задали отрицательное число там, где должно быть положительное. |
| #ССЫЛКА! | Недопустимая ссылка. Например, формула ссылается на ячейку, удаленную из рабочего листа. |
| #ЗНАЧ! | В формулу включен аргумент или операнд недопустимого типа. Операнд — это значение (или ссылка на ячейку), которое используются в формуле для вычисления результата. |
| ########## | Столбец недостаточно широк |