Что нужно знать
- Чтобы вычислить несколько элементов на основе ячеек в другом месте и сохранить константу строки или столбца, используйте абсолютную ссылку на ячейку.
- В этом уравнении абсолютная ссылка на ячейку имеет вид A$12:=+B2+B2+A$12.
- Символ $ «удерживает» строку или столбец постоянными даже при копировании или заполнении столбца или строки одной и той же формулой.
В этой статье объясняется, как использовать абсолютную ссылку на ячейку в Excel 2010 и более поздних версиях, чтобы предотвратить автоматическую корректировку формул в зависимости от того, сколько строк или столбцов вы заполнили.
Что такое абсолютная ссылка на ячейку в Excel?
Существует два способа ссылки на ячейку внутри формулы в Excel. Вы можете использовать относительную ссылку на ячейку или абсолютную ссылку на ячейку.
- Относительная ссылка на ячейку: адрес ячейки, который не содержит символ $ перед координатами строки или столбца. Эта ссылка будет автоматически обновлять столбец или ячейку относительно исходной ячейки, когда вы заполняете либо вниз, либо поперек.
- Абсолютная ссылка на ячейку: адрес ячейки, который содержит символ $ перед координатами строки или столбца. Это «удерживает» ссылочную строку или столбец постоянными даже при заполнении столбца или строки одной и той же формулой.
Ссылка на ячейку может показаться абстрактной концепцией для тех, кто плохо знаком с ней.
Как использовать абсолютную ссылку на ячейку в Excel
Есть несколько различных способов использования абсолютной ссылки в Excel. Используемый метод зависит от того, какую часть ссылки вы хотите оставить неизменной: столбец или строку.
-
Например, возьмите электронную таблицу, в которой вы хотите рассчитать фактический налог с продаж для нескольких товаров на основе ссылочных ячеек в других местах таблицы.
Image -
В первой ячейке вы должны ввести формулу, используя стандартные относительные ссылки на ячейки для цен на товары. Но чтобы включить в расчет правильный налог с продаж, вы должны использовать абсолютную ссылку для строки, но не для ячейки.
Image -
Эта формула по-разному ссылается на две ячейки. B2 - это ячейка слева от первой ячейки налога с продаж относительно позиции этой ячейки. Ссылка A$12 указывает на фактический налоговый номер, указанный в A12. Символ $ сохранит ссылку на строку 12 постоянной, независимо от того, в каком направлении вы заполняете соседние ячейки. Заполните все ячейки под этой первой, чтобы увидеть это в действии.
Image -
Вы можете видеть, что при заполнении столбца ячейка во втором столбце использует относительную ссылку на ячейки для значений затрат в столбце B, увеличивая столбец, чтобы он соответствовал столбцу налога с продаж. Однако ссылка на A12 остается постоянной из-за символа $, который сохраняет ссылку на строку неизменной. Теперь заполните столбец D, начиная с ячейки C2.
Image -
Теперь вы можете видеть, что при заполнении строки справа используется правильный налог с продаж штата (Индиана), потому что ссылка на столбец является относительной (она сдвигается на единицу вправо, как и ячейка, которую вы заполняете). Однако относительная ссылка на стоимость теперь также смещена на единицу вправо, что неверно. Вы можете исправить это, заставив исходную формулу в C2 ссылаться на столбец B вместо абсолютной ссылки.
Image -
Поместив символ $ перед «B», вы создали абсолютную ссылку на столбец. Теперь, когда вы заполните справа, вы увидите, что ссылка на столбец B для стоимости остается прежней.
Image -
Теперь, когда вы заполните столбец D, вы увидите, что все относительные и абсолютные ссылки на ячейки работают именно так, как вы предполагали.
Image -
Абсолютные ссылки на ячейки в Excel имеют решающее значение для управления тем, на какие ячейки ссылаются при заполнении столбцов или строк. Однако используйте символ $ в ссылке на ячейку, чтобы сохранить постоянную ссылку на строку или столбец, чтобы ссылаться на правильные данные в правильной ячейке. Когда вы комбинируете относительные и абсолютные ссылки на ячейки таким образом, это называется смешанной ссылкой на ячейку.
Если вы используете смешанную ссылку, важно выровнять столбец или строку исходных данных со столбцом или строкой, где вы вводите формулу. Если вы сделали ссылку на строку относительной, то имейте в виду, что при заполнении сбоку номер столбца исходных данных будет увеличиваться вместе со столбцом ячейки формулы. Это одна из вещей, которая усложняет относительную и абсолютную адресацию для новых пользователей, но как только вы ее изучите, у вас будет гораздо больше контроля над извлечением ячеек исходных данных в ссылки на ячейки формул.
Использование абсолютных ссылок на ячейки для закрепления ссылки на одну ячейку
Другой подход к использованию абсолютной ссылки на ячейку заключается в применении ее как к столбцу, так и к строке, чтобы по существу «закрепить» формулу, чтобы использовать только одну ячейку, независимо от того, где она находится.
Используя этот подход, вы можете заполнить вбок или вниз, и ссылка на ячейку всегда останется неизменной.
Использование абсолютной ссылки на ячейку как для столбца, так и для строки эффективно, только если вы ссылаетесь только на одну ячейку во всех заполняемых вами ячейках.
-
Используя тот же пример электронной таблицы, что и выше, вы можете указать только ставку единого налога штата, добавив символ $ как к столбцу, так и к строке.
Image -
Это делает столбец «A» и строку «12» постоянными, независимо от того, в каком направлении вы заполняете ячейки. Чтобы увидеть это в действии, заполните весь столбец для налога с продаж ME после того, как вы обновите формулу с помощью абсолютной ссылки на столбец и строку. Вы можете видеть, что каждая заполненная ячейка всегда использует абсолютную ссылку $A$12. Ни столбец, ни строка не меняются.
Image -
Используйте ту же формулу для столбца налога с продаж в штате Индиана, но на этот раз используйте абсолютную ссылку как для столбца, так и для строки. В данном случае это $B$12.
Image -
Заполните этот столбец, и снова вы увидите, что ссылка на B12 не меняется ни в одной ячейке благодаря абсолютной ссылке как на столбец, так и на строку.
Image -
Как видите, существует несколько способов использования абсолютных ссылок на ячейки в Excel для выполнения аналогичных задач. Абсолютные ссылки дают вам возможность поддерживать постоянные ссылки на ячейки, даже если вы заполняете большое количество столбцов или строк.
Вы можете переключаться между относительными или абсолютными ссылками на ячейки, выделяя ссылку и нажимая F4 Каждый раз, когда вы нажимаете F4, абсолютная ссылка будет применяться либо к столбцу, строку, столбец и ячейку или ни то, ни другое. Это простой способ изменить формулу, не вводя символ $.
Когда следует использовать абсолютные ссылки на ячейки
Практически в каждой отрасли и области существует множество причин, по которым вы можете захотеть использовать абсолютные ссылки на ячейки в Excel.
- Использование фиксированных множителей (например, цена за единицу) в большом списке товаров.
- Применяйте единый процент для каждого года, когда вы прогнозируете годовые цели по прибыли.
- При создании счетов-фактур используйте абсолютные ссылки, чтобы ссылаться на одну и ту же налоговую ставку для всех товаров.
- Используйте абсолютные ссылки на ячейки в управлении проектами, чтобы ссылаться на фиксированные уровни доступности для отдельных ресурсов.
- Используйте относительные ссылки на столбцы и абсолютные ссылки на строки, чтобы сопоставить вычисления столбцов в ячейках, на которые ссылаются, со значениями столбцов в другой таблице.
Если вы используете комбинацию относительных и абсолютных ссылок для столбцов или строк, вам просто нужно убедиться, что положение столбцов или строк исходных данных соответствует столбцу или строке ячеек назначения (где вы повторно введите формулу).
