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

Содержание
  1. Введение Есть два типа ссылок на ячейки: относительные и абсолютные. Относительные и абсолютные ссылки ведут себя по-разному при копировании и заполнении в другие ячейки. Относительные ссылки меняются при копировании формулы в другую ячейку. С другой стороны, абсолютные ссылки остаются неизменными независимо от того, где они копируются. Относительные ссылки на ячейки Ссылки на ячейки по умолчанию являются относительными. См. Картинку ниже. При копировании в несколько ячеек они меняются в зависимости от относительного положения строк. и столбцы. Например, если вы скопируете формулу = C2 * D2 из строки 2 в строку 3, формула станет = C3 * D3. Относительные ссылки особенно удобны, когда вам нужно повторить один и тот же расчет для нескольких строк или столбцов. Нажмите клавишу Enter на клавиатуре. Формула будет рассчитана, и результат отобразится в ячейке. Чтобы создать и скопировать формулу с использованием относительных ссылок: В следующем примере мы хотим создать формулу, которая будет рассчитайте брутто, умножив единицы на ставку/единицу. Лучше создать формулу и скопировать формулу для каждой строки, чем создавать формулу для каждой строки. В приведенном ниже примере мы записали формулу в ячейку E2 и перетащили ее ниже, либо дважды щелкните параметр автозаполнения, либо скопируйте ее в другие строки. Ячейки будут относительно изменены. Вот изображение ниже после копирования формулу для каждой из строк. Здесь на рисунке ниже показана формула в ячейке E7 ссылка на строку 7, то есть C7 * D7. Абсолютная ссылка на ячейку Иногда нам нужно чтобы скопировать формулу, необходимо зафиксировать содержимое какой-либо ячейки, связанной с этой формулой. В этом состоянии можно использовать относительные ссылки на ячейки. В этом типе ссылок на ячейки мы можем сохранить постоянную строку и/или столбец. Абсолютная ссылка обозначается в формуле добавлением знака доллара ($). Он может предшествовать ссылке на столбец или ссылке на строку или и тем, и другим. В таблице ниже показано, что используется абсолютная ссылка на ячейку. Абсолютная ссылка Особые Клавиши на клавиатуре $ A $ 1 Столбец и строка не меняются при копировании. Нажмите F4. A $ 1 Строка не изменяется при копировании. Дважды нажмите F4. $ A1 Столбец не изменяется при копировании. Трижды нажмите F4. Обычно вы будете использовать формат $ A $ 1 при создании формул, содержащих абсолютные ссылки.. Два других формата используются гораздо реже. При написании формулы вы можете нажать клавишу F4 на клавиатуре, чтобы переключаться между относительными и абсолютными ссылками на ячейки. Это простой способ быстро вставить абсолютную ссылку. Создайте и скопируйте формулу, используя абсолютные ссылки: Если мы хотим рассчитать комиссию для каждой строки на 5% от брутто, мы должны использовать абсолютную ссылку на ячейку. По умолчанию ссылка на ячейку является относительной и меняет адрес ячейки во время копирования формулы. Здесь, в примере ниже, мы записали формулу в ячейку F5. Здесь мы видим, что E5 умножается на $ D $ 1, это означает, что каждое значение столбца E будет умножено на значение столбца D и строки 1. Знак $ (доллар) ограничен изменением адреса ячейки. Нажмите клавишу ввода в ячейку F5, чтобы увидеть результат, или, чтобы остаться на нем, нажмите Ctrl + ввод Теперь найдите маркер заполнения в ячейке, в которую была записана формула, нажмите и удерживайте кнопку мыши на маркере заполнения, затем перетащите вверх к ячейке, которую вы хотите скопировать, и отпустите кнопку мыши. Вы также можете дважды щелкнуть маркер заполнения, чтобы автоматически скопировать формулу в ячейку Вот изображение ниже, после копирования формулы для количества строк. Теперь вы видите, как работает абсолютная ссылка на ячейку. Смешанная ссылка Иногда нам нужна такая комбинация формул которые содержат такие ссылки на ячейки, могут быть статическими для строк или столбцов, то есть комбинацией относительных и абсолютных ссылок (смешанная ссылка). На листе ниже показано, что компания установила цель для 1-й. Qtr. для двух продуктов: TV (LCD) и TV (LED), а также указал достижимую цель на месяцы Qtr. и рассчитайте единицы, которые должны быть достигнуты за 3 месяца. Предположим, что целью является 75000 и 12000 См. Следующий пример, мы хотим получить количество единиц, которое будет произведено в январе, чтобы получить установленную цель. Здесь в листе согласно условию мы умножили В2 на В7. А теперь нам нужно скопировать формулу для февраля месяца, и здесь мы видим, что ссылки на ячейки являются относительными, а результат неверен. См. Рисунок ниже. Чтобы предотвратить эту ситуацию, мы должны использовать смешанный ссылки на ячейки. Мы использовали $ B2, это означает, что если мы копируем формулу по горизонтали или вертикали, столбец будет абсолютным, а строка будет относительной.. Таким же образом мы использовали C $ 7, это означает, что если мы копируем формулу по горизонтали или вертикали, столбец будет относительным, а строка будет статической. Вот изображение ниже. См. Изображение ниже для телевизора (ЖКД) за месяц февраль. См. рисунок ниже для телевизора (LED) в январе месяце. Как использовать ссылки на ячейки с несколькими листами? Excel позволяет ссылаться на ячейки не только в пределах одного листа книги, но также может обновлять множество листов одновременно с изменением значения одной ячейки листа. Для работы с большим количеством листов адрес ячейки обозначается, как на рисунке ниже. На рисунке ниже показаны активный мастер листа и другая неактивная транзакция листа. Мы хотим записать формулу в лист транзакции с использованием ссылки на ячейку мастер-листа. Вот картинка ниже. Здесь в формуле [master! $ B2] указывается, что лист является «основным», а адрес ячейки — $ B2, то есть на листе «мастер» столбец B является абсолютным, а строка — относительным. В формуле [master! B $ 7], указывающей, что лист является «основным», а адрес ячейки — B $ 7, то есть в листе «master» столбец является относительным, а строка 7 — абсолютным. В формуле [транзакция! $ B2], указывающая, что лист является «транзакцией», а адрес ячейки — $ B2, то есть в листе «транзакция» столбец B является абсолютным, а строка — относительной. Обратите внимание, что если имя рабочего листа содержит пробел, вам нужно будет включить одинарные кавычки (») вокруг имени. Например, «Ссылка на ячейку»! | $ F $ 2.
  2. Относительные ссылки на ячейки
  3. Абсолютная ссылка на ячейку
  4. Смешанная ссылка

Введение

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

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

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

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

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

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

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

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

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

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

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

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

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

Абсолютная ссылка Особые Клавиши на клавиатуре
$ A $ 1 Столбец и строка не меняются при копировании. Нажмите F4.
A $ 1 Строка не изменяется при копировании. Дважды нажмите F4.
$ A1 Столбец не изменяется при копировании. Трижды нажмите F4.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

См. Изображение ниже для телевизора (ЖКД) за месяц февраль.

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

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

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

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

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

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

Оцените статью
nanomode.ru
Добавить комментарий