Использование ссылок в Excel.

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

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

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

Копирование и перемещение ячеек

В редакторе Excel  копирование и перемещение данных осуществляется стандартным для Windows способом, который состоит из следующих этапов:

1.   Выделить одну или несколько ячеек либо часть содержимого ячейки, т.е. ту информацию, которую нужно копировать.

2.   Скопировать выделенный блок в буфер обмена ( например, посредством кнопки Копировать или Вырезать), расположенной в группе команд Буфер обмена на вкладке Главная.

3.   Установить курсор в то место документа, куда будет вставлена переносимая информация.

4.   Вставить находящуюся в буфере информацию в то место расположения курсора нажатием кнопки Вставить, расположенной в группе команд Буфер обмена.

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

Кроме того, копирование и перемещение можно осуществлять путем перетаскивания с помощью мыши. Для этого следует выполнить следующие действия:

1.   Выделить ячейку или блок ячеек.

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

3.   Для копирования: нажать клавишу Ctrl и левую кнопку мыши и, удерживая их в нажатом положении, переместить ячейки в нужное место таблицы. Для перемещения клавишу Ctrl нажимать не надо.

4.   Отпустить кнопку мыши, а затем клавишу Ctrl.

При этом все имеющиеся в области вставки данные будут заменены новыми.

Заполнение ячеек копированием

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

1.   Ввести значение в некоторую ячейку.

2.   Нажать и удерживать клавишу Ctrl.

3.   Захватить маркер заполнения левой кнопкой мыши и протащить по заполняемым ячейкам.

Вернемся к ссылкам.

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

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

Например, если в ячейке А3 была записана формула =А1*А2, то при копировании содержимого А3 в ячейки В3 и С3 новые формулы с обновленными ссылками примут следующий вид: =В1*В2, =С1*С2.

Кроме относительных ссылок, в редакторе Excel часто используются абсолютные ссылки, где кроме названия столбца и номера строки используется специальный символ «$», который фиксирует часть ссылки (столбец, строку) и оставляет её неизменной при копировании формулы с такой ссылкой в другую ячейку. Обычно абсолютные ссылки указывают на ячейки, в которых содержатся константы, используемые при вычислениях.

Например, если необходимо зафиксировать в формуле =А1*В1 значение ячейки А1, которое не должно изменяться в случае копирования данной формулы, то абсолютная ссылка на эту ячейку будет иметь следующий вид: $А$1. Таким образом при копировании формулы из ячейки В2 в ячейку С2 формула примет вид: =$A$1*C1.

Если требуется зафиксировать в ссылке только строку или только столбец, в котором находится используемая ячейка, в частности столбец А или строку 1, то ссылка примет вид $A1 или A$1 соответственно.

$A$1 – абсолютная ссылка (фиксированная ячейка);

A$1 – изменяемый столбец и неизменяемая строка;

$A1 – неизменяемый столбец и изменяемая строка;

A1 – относительная ссылка.

 

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

 

Создадим следующую таблицу и потренируемся в применении ссылок:

 

Все ячейки со знаком вопроса заполним нужными формулами.

1.   Чтобы название таблицы выглядело, как показано на рисунке, необходимо объединить семь ячеек первой строки. Для этого нужно их выделить и нажать на значок объединения на панели Главная, раздел Выравнивание.  

2.   Вносим название и форматируем его по образцу, применяя 16 размер шрифта, полужирный шрифт и наклон.

3.   Создаем таблицу констант, применяя необходимое форматирование и % - ный формат ячеек, где это необходимо.

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

Формулы для расчетов

Подоходный налог = (Оклад – Необлагаемый налогом доход) х % подоходного налога, в ячейку D10 введите формулу = (С10 - $C$3)*$C$4;

С помощью маркера заполнения, как описано выше, распространите формулу на весь столбец.

Отчисления в благотворительный фонд = Оклад  х % отчисления в благотворительный фонд, в ячейку E10 введите формулу = C10*$C$5;

С помощью маркера заполнения распространить формулу на весь столбец.

Всего удержано = Подоходный налог + Отчисления в благотворительный фонд, в ячейку F10  введите формулу = D10 + E10;

С помощью маркера заполнения распространить формулу на весь столбец.

К выдаче = Оклад – Всего удержано, в ячейку G10 введите формулу =C10 – F10;

С помощью маркера заполнения распространить формулу на весь столбец.

Таким образом, таблица заполнена.