2. 3. Копирование формул. Абсолютные и относительные ссылки

С формулами связано весьма удобное свойство: занеся формулу в одну из ячеек ЭТ, ее можно скопировать в другие ячейки (например, в приведенном на рис. 3 электронном документе можно занести формулу в ячейку D2 и скопировать в ячейки D3, D4 и далее). Простейший способ копирования – сделать текущей нужную ячейку (или даже выделить блок ячеек), выполнить операцию Копировать, затем выделить ячейку или блок ячеек, в которые копируется формула, и выполнить операцию Вставить или Специальная вставка.

При копировании формулы автоматически настраиваются на свое новое местоположение. Так, если формулу из ячейки D2 скопировать в ячейки D3 и D4, то в них появятся формулы, настроенные на новое местоположение. Например, содержимым ячейки D3 будет формула:

=B3*(1+C3/100),

а ячейки D4 – формула:

=B4*(1+C4/100)

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

Дело в том, что при копировании формулы выполняется изменение имеющихся в формуле ссылок на другие ячейки ЭТ таким образом, чтобы ссылки в копиях формулы указывали на ячейки, расположенные относительно ячейки, содержащей копию формулы так же, как это имеет место для ячейки, содержащей формулу-оригинал. Иллюстрирует сказанное рис. 4.

Примечание: помимо операции копирования возможно перемещение формулы из одной ячейки в другую, при котором в ячейке-источнике формула исчезает и ее точная копия появляется в ячейке-приемнике. Изменения ссылок в перемещаемой ячейке при выполнении этой операции не происходит. В других формулах, которые ссылались на ячейку-источник, ссылки перестраиваются на новую ячейку-приемник.

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

В модифицированном документе необходимо изменить формулы в ячейках колонки D, по которым ведутся расчеты зарплаты. Так, в ячейке D2 расчет зарплаты должен быть проведен по формуле:

=F3*B2*(1+C2/100)

Однако если скопировать эту формулу в ячейку D3, то получившаяся формула

=F4*B3*(1+C3/100)

даст неправильный результат вследствие того, что ссылка на ячейку F3 была заменена при копировании ссылкой на ячейку F4, в которой нет никаких данных.

	A	B	C	D	E
1
2
3			=A1+B2
4

Исходная формула

	A	B	C	D	E
1
2
3				=B1+C2
4
	A	B	C	D	E
1
2
3
4			=A2+B3
	A	B	C	D	E
1
2
3
4					=C2+D3

Формула после копирования в разные ячейки

Рис. 4. Результат копирования формулы из ячейки C3 в ячейки D3, C4 и E4

	A	B	C	D	E	F
1	Ф. И. О.	Тариф	Надбавки (%)	Зарплата		Минималь-
2	Иванов И. П.	3,3	35	445,50		ный оклад
3	Петров П. А.	3,3	45	478,50		100
4	Пашин Ю.Я.	1	20	120,00
5 6	Мольков В. А.	2	20	240,00
	Яшин В. С	2	10	220,00
7			Всего:	1504,00

Рис. 5. Пример модифицированной ведомости на зарплату

Для запрета изменения ссылок на имена колонок или номера строк при копировании формул служит аппарат абсолютных ссылок. Признаком абсолютной ссылки является символ "$", проставляемый перед именем колонки и/или номером строки. При копировании формул, в которых содержатся абсолютные ссылки, не происходит подстройки имен колонок и/или номеров строк в этих ссылках на новое местоположение формулы.

В том случае, если необходимо запретить изменение ссылки на колонку, признак абсолютной ссылки ставится перед именем колонки, например $B7. Номер строки при копировании в этом случае будет изменяться (в том случае, если формула копируется в строки с другими номерами).

При запрете изменения номера строки абсолютная ссылка ставится перед ним, например B$7. Имя колонки при копировании может изменяться (если формула копируется в другие колонки).

Если необходимо запретить любое изменение ссылки, то символ "$" ставится и перед именем колонки, и перед номером строки – $B$7.

Результаты использования абсолютных ссылок демонстрируются на рис. 6.

	A	B	C	D	E
1
2
3			=$A1+B$2
4

Исходная формула

	A	B	C	D	E
1
2
3				=$A1+C$2
4

	A	B	C	D	E
1
2
3
4			=$A2+B$2

	A	B	C	D	E
1
2
3
4					=$A2+D$2

Формула после копирования в разные ячейки

Рис. 6. Результат копирования формулы, содержащей абсолютные

ссылки, из ячейки C3 в ячейки D3, C4 и E4

В рассматриваемой задаче в ячейку D2 следует занести формулу

=F$3*B2*(1+C2/100) или =$F$3*B2*(1+C2/100)

Наиболее сложные проблемы при использовании аппарата абсолютных ссылок возникают, когда необходимо скопировать формулу не исключительно в ячейки той же колонки или той же строки, а в прямоугольный блок ячеек ЭТ и при этом требуется фиксировать некоторые ссылки на строки, некоторые – на колонки.

Примечание: блоком называется прямоугольный фрагмент ЭТ, идентифицируемый левой верхней и правой нижней ячейками, например C4:E20. В частном случае блок может состоять из нескольких подряд расположенных ячеек одной колонки или одной строки, в этом случае его называют также диапазоном или интервалом.

Рассмотрим в качестве примера, иллюстрирующего случай копирования формулы в блок, состоящий из ячеек более чем одной строки и одной колонки, документ, определяющий размер зарплаты работника в зависимости от размера минимального оклада, тарифного коэффициента (тарифного разряда) и стажа работы (рис. 7).

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

	A	B	C	D	E	F	G
1		Тарифный	Надбавка (%) за стаж работы				Минималь-
2	Раз-	коэффи-	до 1 года	от 1 до 3 года	более 3 лет		ный оклад
3	ряд	циент	0	10	30		100
4	1	1	100	110	130
5	2	1,2	120	132	160
6	3	2	200	220	260
7	4	3	300	330	390

Рис. 7. Документ для расчета размера зарплаты работника в зависимости от размера минимального оклада, тарифного коэффициента и стажа работы

Требуется определить, какую формулу необходимо внести в одну из ячеек блока C4:E20, например в ячейку C4, чтобы после копирования ее в остальные ячейки этого блока в каждой из них получался правильный результат. Возможный вариант формулы:

=$G$3*$B4*(1+C$3/100)

Иногда в формулах приходится делать ссылки не на отдельную ячейку, а на целый блок, например в случае, если необходимо подсчитать сумму содержимого ячеек колонки/строки и т. п. При копировании таких формул ссылки на ячейки, идентифицирующие блок, изменяются по тем же правилам, что и ссылки на отдельные ячейки. Если такие изменения нежелательны, то необходимо зафиксировать ссылки на эти ячейки, например, таким образом: $C$4:$E$20. Возможны ситуации, когда потребуется более гибкое использование ссылок, к примеру: $C4:$E20 и т. п.

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

=Лист3!B$3:D$9

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

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

Использовать страничную организацию удобно в тех случаях, когда документ состоит из нескольких частей. Размещение их на одном листе создаст сложность при просмотре (придется "ползать" по всему листу, что неудобно). Если же разместить каждую часть на своем листе, то можно просто переходить с листа на лист (аналогично тому, как листают книгу; переход выполняется путем щелчка правой кнопкой мыши на имени нужного листа, либо с помощью комбинаций клавиш [Ctrl]+[PgUp/PgDown]).

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

ЭТ позволяют легко организовать такой способ обмена данными. Достаточно поместить электронный документ со справочными данными общего назначения в отдельный файл и во всех других документах, где требуются эти данные, перед ссылкой на соответствующую ячейку документа-справочника указать не только адрес ячейки и имя страницы, но и имя файла (в квадратных скобках), хранящего этот документ, а если файл находится не в текущем каталоге, то путь к нему. Вся эта информация заключается в кавычки.

Проиллюстрируем сказанное. Пусть в файле sprav.xls, находящемся на диске C: в папке ALL, располагаются нормативно-справочные данные и в некотором электронном документе требуется получить сведения из ячейки A3, находящейся на странице Нормативы документа-справочника. Ссылка, позволяющая получить необходимые сведения, выглядит так:

'[sprav.xls]Нормативы'!A3

или, если указанная папка не используется по умолчанию:

'C:\ALL\[sprav.xls]Нормативы'!A3

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

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

Чтобы присвоить имя формуле, необходимо:

1) в меню Вставка выбрать пункт Имя, а затем пункт Вставить;

2) в поле Имя ввести имя для формулы;

3) в поле Формула ввести формулу (она должна начинаться со знака “=”);

4) щелкнуть кнопку Добавить, после чего имя формулы появится в списке;

5) для удаления имени из списка следует выделить его и щелкнуть кнопку Удалить;

6) после ввода всех имен щелкнуть ОК.

Для присвоения имени ячейке или диапазону, следует:

1) выделить ячейку, диапазон ячеек или несколько диапазонов, которым следует присвоить имя;

2) щелкнуть мышью поле имени в левой части строки формул;

3) набрать имя для ячейки или диапазона;

4) нажать клавишу Enter.

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

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

1) в меню Вставка выбрать пункт Имя, а затем – Присвоить;

2) в поле Имя набрать сначала имя рабочего листа, за ним восклицательный знак, а затем имя ячейки или диапазона ячеек, например: Лист5!Баланс;

3) в поле Формула ввести формулу или ссылку (она должна начинаться со знака “=”) и щелкнуть кнопку ОК.

Можно использовать одно и то же имя на уровне листа и на уровне всей книги. Тогда имя на уровне рабочего листа будет перекрывать общее имя на том листе, где оно определено.