Абсолютная относительная и смешанная адресация ячеек и блоков

Создание формул путем указания ячеек рабочего листа

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

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

1. Выделите ячейку, в которую надо ввести формулу (в нашем примере – В14);
2. Введите в В14 знак =.;
3. Щелкните левой кнопкой по ячейке, ссылку на которую надо ввести в формулу (в нашем примере – В5;
4. Ввести знак операции (в нашем примере знак минус -);
5. Щелкните левой кнопкой мыши по ячейке, ссылку на которую надо ввести в формулу (в нашем примере – В11;
6. Нажмите клавишу <Enter> или кнопку с галочкой в строке формул, чтобы завершить ввод.

Ссылки на ячейки в формулах

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

При обращении к ячейке можно использовать обычный способ, при котором адрес ячейки определяется как пересечение определенного столбца и определенной строки, например, В5, G9. Такая адресация называется относительной. При ее использовании в формулах Excel запоминает расположение относительно текущей ячейки. Так, например при вводе формулы =В1+В2 в ячейку В4 Excel интерпретирует формулу как “прибавить содержимое ячейки, расположенной тремя рядами выше, к содержимому ячейки, расположенной двумя рядами выше». Если скопировать формулу из ячейки В4 в С4 Excel также интерпретирует формулу как“ прибавить содержимое ячейки, расположенной тремя рядами выше, к содержимому ячейки, расположенной двумя рядами выше». Таким образом, формула в ячейке С4 примет вид =С1+С2

Абсолютная адресация. Если при копировании формул необходимо сохранить ссылку на КОНКРЕТНУЮ ячейку или область, то необходимо воспользоваться абсолютной адресацией. Для ее задания необходимо перед именем столбца и перед номером строки ввести символ $. Например: $B$4 или $C$2:$F$48.

В качестве иллюстрации рассмотрим следующий пример.

В приведенной ниже таблице данных необходимо вычислить объем выпускаемой продукции в процентах.

	A	B	C
	Название изделия	Количество произведенной продукции, шт.	Объем продукции, %
	Карандаши		=В2/$B$5*100
	Ручки		= В3/$B$5*100
	Фломастеры		= В4/$B$5*100
	Всего	=В2+В3+В4

В столбце А находятся названия изделий, в столбце В – количества выпущенных изделий за некоторый промежуток времени, в столбце же С нужно ввести формулу, вычисляющую долю процента, занимаемую данной продукцией среди всей выпускаемой продукции (если выпускается только три указанных вида продукции). Для того чтобы рассчитать последний показатель, необходимо сначала рассчитать общий объем продукции, что делается в ячейке В5. После этого в ячейку С2 ввести формулу = В2/В5*100. копируя эту формулу в ячейку С3, получим формулу =В3/В6*100, поскольку используется относительная адресация. Но в таком случае будет получен неверный результат, так как общее количество выпускаемой продукции находится в ячейке В5 и при копировании формулы этот адрес должен оставаться неизменяемым. Таким образом, в данном случае необходимо использовать абсолютную адресацию, и формула должна выглядеть так: =B2/$B$5*100.

Смешанная адресация. Символ $ ставится только там, где он необходим. Например: В$4 или $C2. Тогда при копировании один параметр адреса изменяется, а другой – нет. Совершенно очевидно, Что в последнем примере абсолютная адресация должна применяться по отношению к строкам, но никак не к столбцам, так как последний остается неизменным. Таким образом, окончательный вид формулы в ячейке С2 будет выглядеть так: =B2/B$5*100.

Одним из видов абсолютной адресации является адресация с помощью задания имени ячейки. Например, можно присвоить (с помощью меню Вставка, Имя, Присвоить) ячейке В5 имя «Всего». В таком случае формула в ячейке С2 будет выглядеть следующим образом: = В2/Всего*100.

Ввод ссылок на другие рабочие листы. Для этого нужно поместить название листа и восклицательный знак (!) перед ссылкой на ячейку. Например, =[Книга1.xls]Лист2!$C$5

Использование операций в формулах

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

Дата добавления: 2015-06-04