Как использовать Абсолютную ссылку на ячейку в Excel

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

Инструкции в этой статье относятся к Excel 2019, 2016, 2013, 2010; Excel для Office 365, Excel для Mac и Excel Online.

Что такое абсолютная ссылка на ячейку в Excel?

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

  • Относительная ссылка на ячейку: Адрес ячейки, который не содержит символ $ перед координатами строки или столбца. Эта ссылка автоматически обновит столбец или ячейку относительно исходной ячейки, когда вы заполните или поперек.
  • Абсолютная ссылка на ячейку: Адрес ячейки, который содержит символ $ перед координатами строки или столбца. Это «держит» ссылочную строку или константу столбца даже при заполнении столбца или строки с той же формулой.

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

Как использовать Абсолютную ссылку на ячейку в Excel

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


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

    Снимок экрана таблицы налога с продаж


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

    Скриншот использования абсолютной строки


  3. Эта формула ссылается на две ячейки по-разному. Би 2 является ячейкой слева от первой ячейки налога с продаж относительно положения этой ячейки. A $ 12 ссылки на фактический номер налога с продаж, указанный в A12. Символ $ будет сохранять постоянную ссылку на строку 12 независимо от того, в каком направлении вы заполняете соседние ячейки. Заполните все ячейки под этой первой, чтобы увидеть это в действии.

    Снимок экрана: абсолютная ссылка на ячейку после заполнения столбца


  4. Вы можете видеть, что при заполнении столбца ячейка во втором столбце использует относительную ссылку на ячейку для значений затрат в столбце B, увеличивая столбец в соответствии со столбцом налога с продаж. Однако ссылка на A12 остается постоянной из-за символа $, который сохраняет ссылку на строку одинаковой. Теперь заполните столбец D, начиная с ячейки C2.

    Скриншот заполнения строки с абсолютной ссылкой в ​​Excel


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

    Снимок экрана с использованием абсолютной ссылки на столбец в Excel


  6. Поместив символ $ перед «B», вы создали абсолютную ссылку на столбец. Теперь, когда вы заполните справа, вы увидите, что ссылка на столбец B для стоимости остается прежней.

    Снимок экрана заполнения Excel справа с абсолютным столбцом


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

    Скриншот абсолютных ссылок на ячейки в Excel


  8. Абсолютные ссылки на ячейки в Excel имеют решающее значение для управления тем, на какие ячейки ссылаются при заполнении столбцов или строк. Используйте символ $, однако вам нужно в ссылке на ячейку сохранить константу ссылки на строку или столбец, чтобы ссылаться на правильные данные в правильной ячейке. Когда вы объединяете относительные и абсолютные ссылки на ячейки, как это, это называется смешанной ссылкой на ячейку.


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

Использование абсолютных ссылок на ячейки для закрепления ссылки на одну ячейку

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

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

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


  1. Используя приведенную выше примерную электронную таблицу, вы можете ссылаться только на единую ставку налога штата, добавив символ $ как к столбцу, так и к ссылке на строку.

    Снимок экрана: использование абсолютной ссылки на столбец и строку


  2. Это делает столбец «A» и строку «12» постоянными, независимо от направления заполнения ячеек. Чтобы увидеть это в действии, заполните весь столбец налога с продаж ME после того, как вы обновите формулу абсолютной ссылкой на столбец и строку. Вы можете видеть, что каждая заполненная ячейка всегда использует абсолютную ссылку $ A $ 12. Ни столбец, ни строка не изменятся.

    Снимок экрана с использованием абсолютной ссылки для столбца и строки


  3. Используйте ту же формулу для столбца налога с продаж штата Индиана, но на этот раз используйте абсолютную ссылку для столбца и строки. В этом случае это $ B $ 12.

    Снимок экрана с использованием абсолютной ссылки для столбца и строки


  4. Заполните этот столбец, и вы снова увидите, что ссылка на B12 не изменяется ни в одной ячейке благодаря абсолютной ссылке для столбца и строки.

    Снимок экрана абсолютной ссылки на столбец и строку после заполнения столбца


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


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

Когда вы должны использовать абсолютные ссылки на ячейки

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

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

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

Ссылка на основную публикацию