Ссылки на ячейки в Excel

Вступление

Существует два типа ссылок на ячейки: относительная и абсолютная. Относительные и абсолютные ссылки ведут себя по-разному, когда копируются и заполняются в другие ячейки. Относительные ссылки изменяются, когда формула копируется в другую ячейку. Абсолютные ссылки, с другой стороны, остаются постоянными, независимо от того, где они копируются.

Относительные ссылки на ячейки

Ссылки на ячейки по умолчанию являются относительными. Смотрите картинку ниже.

При копировании в несколько ячеек они изменяются в зависимости от взаимного расположения строк и столбцов. Например, если скопировать формулу = C2 * D2 из строки 2 в строку 3, формула станет = C3 * D3. Относительные ссылки особенно удобны, когда вам нужно повторить один и тот же расчет для нескольких строк или столбцов.

Нажмите клавишу ввода на клавиатуре. Формула будет рассчитана, а результат будет отображаться в ячейке.

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

В следующем примере мы хотим создать формулу, которая будет вычислять брутто путем умножения единиц на ставку / единицу. Лучше создать формулу и скопировать формулу для каждой строки, а не создавать формулу для каждой строки. Здесь, в приведенном ниже примере, мы записали формулу в ячейку E2 и перетащите ее ниже или дважды щелкните параметр автозаполнения или скопируйте его в другие строки. Клетки будут относительно изменены.

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

Здесь на рисунке ниже показана формула в ячейке E7, ссылающаяся на строку 7, т.е. C7 * D7.

Абсолютная ссылка на ячейку

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

Абсолютная ссылка обозначается в формуле добавлением знака доллара ($). Он может предшествовать ссылке на столбец или на строку, или на оба.

Таблица ниже показывает, что используется абсолютная ссылка на ячейку.

Обычно вы будете использовать формат $ A $ 1 при создании формул, содержащих абсолютные ссылки. Два других формата используются гораздо реже.

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

Создайте и скопируйте формулу, используя абсолютные ссылки:

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

Здесь, в приведенном ниже примере, мы записали формулу в ячейку F5. Здесь мы видим, что E5 умножается на $ D $ 1, это означает, что каждое значение столбца E будет умножено на значение столбца D и строки 1. Знак $ (доллар) ограничен изменением адреса ячейки. Нажмите клавишу ввода в ячейку F5, чтобы увидеть результат или, чтобы остаться на нем, нажмите Ctrl + ввод

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

Вот картинка внизу показывает, после копирования формула для ряда строк.

Теперь вы видите, как работает абсолютная ссылка на ячейку.

Смешанная ссылка

Иногда нам нужна такая комбинация формул, которые содержат такие ссылки на ячейки, которые могут быть статическими для строк или столбцов, то есть комбинацию относительных и абсолютных ссылок (смешанная ссылка).

Приведенный ниже лист показывает, что компания поставила цель для 1-го. Qtr. для двух продуктов: телевизор (LCD) и телевизор (LED), а также указана достижимая цель для месяцев Qtr. и рассчитать единицы, которые должны быть достигнуты за 3 месяца. Предположим, что цель составляет 75000 и 12000

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

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

Чтобы предотвратить эту ситуацию, мы должны использовать смешанные ссылки на ячейки. Мы использовали $ B2, это означает, что если мы скопируем формулу по горизонтали или по вертикали, столбец будет абсолютным, а строка - относительной. Таким же образом мы использовали C $ 7, это означает, что если мы скопируем формулу по горизонтали или по вертикали, столбец будет относительным, а строка будет статической. Вот картинка ниже.

Смотрите картинку ниже для ТВ (LCD) за февраль месяц.

Смотрите рисунок ниже для телевизора (LED) в январе месяце.

Как использовать ссылки на ячейки с несколькими листами ?

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

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

Вот картинка ниже. Здесь в формуле [master! $ B2] указано, что лист является «основным», а адрес ячейки - $ B2, то есть в листе «master» столбец B является абсолютным, а строка - относительной. В формуле [master! B $ 7], указывающей, что лист является «master», а адрес ячейки - B $ 7, то есть в листе «master» столбец является относительным, а row7 - абсолютным. В формуле [транзакция! $ B2], указывающей, что лист - это «транзакция», а адрес ячейки - $ B2, то есть в листе «транзакция» столбец B является абсолютным, а строка - относительной.

Обратите внимание, что если имя листа содержит пробел, вам нужно будет заключить в него одинарные кавычки (''). Например, «Ссылка на ячейку»! | $ F $ 2.

Предыдущая: Основы ячейки - Excel 2013
Далее: Основные функции - Excel 2013