Формулы делятся по типу адресации на:
а) формулы с абсолютной адресацией,
б) формулы с относительной адресацией,
в) формулы со смешанной адресацией.
Признаком абсолютной адресации служит добавление для соответствующей ячейки в формуле знака «$» перед буквой столбца и перед цифрой строки в адресе соответствующей ячейки (см. рис. 107).
Адресация применяется как к одиночным ячейкам, так и к диапазонам ячеек, строкам и столбцам. Как видно на рис. 107 ячейка $A$1 имеет абсолютную адресацию (т.е. в ее адресе и перед буквой столбца и перед цифрой строки стоит знак «$»), а ячейка B1 имеет относительную адресацию (т.к. в ее адресе и перед буквой столбца и перед цифрой строки знак «$» не стоит). Ячейки со смешанной адресацией имеют вид $A1 или A$1 – в первом случае «зафиксирован» столбец, во втором – строка.
Установить знак «$» в адресе ячейки можно двумя способами:
а) установить курсор в строке формул слева от адреса столбца (или строки) нужной ячейки и нажать SHIFT+4 в английской раскладке клавиатуры,
б) установить курсор в строке формул слева, справа или в середине адреса нужной ячейки и нажать на клавиатуре кнопку F4. При нажатии F4 первый раз – «фиксируются» и столбец и строка ячейки (например, $A$1), при нажатии второй раз — «фиксируется» только строка (например, A$1), при нажатии третий раз — «фиксируется» только столбец (например, $A1), при нажатии четвертый раз — «фиксация» убирается полностью (например, A1),
|
Рис. 107 |
Разница при использовании различных типов адресации видна при «протягивании» формулы. Разберем на примерах:
|
Рис. 108 |
Относительныя адресация (Рис. 108). Имеем числа от 1 до 5 в столбце A. В ячейку B1 поставлена формула «=A1*5». Т.к. в формуле ячейка A1 имеет относительную адресацию, то при «протягивании» формулы вправо, столбец ячейки в формуле будет изменяться, т.е. в ячейке C1 формула будет иметь вид «=B1*5», в ячейке D1 «=C1*5» и.т.д. Аналогичная ситуация будет и при «протягивании» формулы вниз, только в этом случае в формуле будет изменяться строка ячейки, т.е. в ячейке B2 формула будет иметь вид «=A2*5, ячейке B3 формула будет иметь вид «=A3*5» и т.д.
|
Рис. 109 |
Смешанная адресация при «фиксации» столбца (Рис. 109). Начальные данные как в предыдущем пункте, но в формуле «зафиксирован» столбец ячейки. При «протягивании» формулы вправо, столбец ячейки в формуле не будет изменяться, т.е. в ячейке C1 формула будет иметь вид «=$A1*5», в ячейке D1 «=$A1*5» и.т.д. При «протягивании» формулы вниз в формуле будет изменяться строка ячейки, т.е. в ячейке B2 формула будет иметь вид «=$A2*5, ячейке B3 формула будет иметь вид «=$A3*5» и т.д.
|
Рис. 110 |
Смешанная адресация при «фиксации» строки (Рис. 110). Начальные данные те же, но в формуле «зафиксирована» строка ячейки. Ситуация, обратная описанной в предыдущем абзаце. При «протягивании» формулы вправо, столбец ячейки в формуле будет изменяться, т.е. в ячейке C1 формула будет иметь вид «=B$1*5», в ячейке D1 «=C$1*5» и.т.д., а при при «протягивании» формулы вниз в формуле строка ячейки не будет изменяться, т.е. в ячейке B2 формула будет иметь вид «=A$1*5, ячейке B3 формула будет иметь вид «=A$1*5» и т.д.
|
Рис. 111 |
Абсолютная адресация (Рис. 111). Начальные данные те же, но в формуле «зафиксирована» вся ячейка. В этом случае как при «протягивании» формулы вправо, так и при «протягивании» вниз ни столбец ни строка в формуле изменяться не будут. Т.е. во всех ячейках формула будет иметь вид «=$A$1*5».
При использовании в формулах ссылок на ячейки из других книг, адресация автоматически проставляется абсолютной. Это обстоятельство нужно учитывать при «протягивании» таких формул, при необходимости убирая абсолютную адресацию.
При копировании формул в другие книги следует учитывать, что ссылки на ячейки из листа, на котором расположена формула, копируются в другую книгу так же как они стоят в исходной формуле, а ссылки на ячейки из других листов или книг копируются с добавлением названия книги, к которой они относятся.
Рассмотрим пример. Есть книга1, в ней имеется Лист1 и Лист2. На Листе2 в ячейке B33 стоит число 2 (рис. 112), а на Листе1 ячейке B33 стоит число 1, а в ячейке A33 стоит формула » =B33+Лист2!B33″ (рис. 113а, б).
|
Рис. 112 |
Рис. 113а |
|
Рис. 113б |
Если создать новую книгу Excel с названием Книга2 и листами Лист 1 и Лист2, а затем скопировать формулу из Книги1 в аналогичную ячейку A33 на Лист1 Книги2, то скопированная формула будет иметь вид » =B33+[Книга1]Лист2!B33″ (рис. 114а, б). Как можно видеть, ссылка на ячейку с того же листа, что и формула, скопировалась без изменений, а к ссылке на другой лист добавилось название книги, содержащей этот лист. Таким образом, даже если книга, в которую производится копирование, имеет такие же листы, как и книга-источник, формулы, ссылающиеся на другие листы книги-источника, будет скопированы с добавлением названия этой книги к соответствующим ссылкам.
|
Рис. 114а |
|
Рис. 114б |
