Одна из функций, которая делает Excel настолько гибким, заключается в том, что вы можете заполнять строку или столбец, а формулы автоматически корректируются в зависимости от количества заполненных строк или столбцов. Однако, если вы хотите запретить автоматическую настройку формул, вам нужно знать, как использовать абсолютную ссылку на ячейку в Excel.
Инструкции в этой статье относятся к Excel 2019, 2016, 2013, 2010; Excel для Office 365, Excel для Mac и Excel Online.
Что такое абсолютная ссылка на ячейку в Excel?
Существует два типа ссылок на ячейку внутри формулы в Excel. Вы можете использовать относительную ссылку на ячейку или абсолютную ссылку на ячейку.
- Относительная ссылка на ячейку : адрес ячейки, который не содержит символ $ перед координатами строки или столбца. Эта ссылка автоматически обновит столбец или ячейку относительно исходной ячейки, когда вы заполните или поперек .
- Абсолютная ссылка на ячейку : адрес ячейки, который содержит символ $ перед координатами строки или столбца. Это «держит» ссылочную строку или константу столбца даже при заполнении столбца или строки с той же формулой.
Ссылка на ячейку может показаться абстрактным понятием для новичков.
Как использовать Absolute Cell Reference в Excel
Есть несколько разных способов использовать абсолютную ссылку в Excel. Метод, который вы используете, зависит от того, какую часть ссылки вы хотите сохранить постоянной: столбец или строку.
-
Например, возьмите электронную таблицу, в которой вы хотите рассчитать фактический налог с продаж для нескольких товаров на основе ссылочных ячеек в других частях листа.
-
В первой ячейке вы должны ввести формулу, используя стандартные относительные ссылки на ячейки для цены товара. Но чтобы включить в расчет правильный налог с продаж, вы должны использовать абсолютную ссылку для строки, а не для ячейки.
-
Эта формула ссылается на две ячейки по-разному. B2 — это ячейка слева от первой ячейки налога с продаж относительно положения этой ячейки. В $ 12 ссылка указывает на фактический налог с продаж номер , указанный в А12. Символ $ будет сохранять постоянную ссылку на строку 12 независимо от того, в каком направлении вы заполняете соседние ячейки. Заполните все ячейки под этой первой, чтобы увидеть это в действии.
-
Можно видеть, что при заполнении столбца ячейка во втором столбце использует относительную ссылку на ячейку для значений затрат в столбце B, увеличивая столбец в соответствии со столбцом налога с продаж. Однако ссылка на A12 остается постоянной из-за символа $, который сохраняет ссылку на строку одинаковой. Теперь заполните столбец D, начиная с ячейки C2.
-
Теперь вы можете видеть, что при заполнении строки справа используется правильный налог с продаж штата (штат Индиана), поскольку ссылка на столбец является относительной (она сдвигается вправо так же, как ячейка, которую вы заполняете). Однако относительная ссылка на стоимость теперь также сместилась вправо, что неверно. Это можно исправить, сделав исходную формулу в C2 ссылкой на столбец B вместо абсолютной ссылки.
-
Поместив символ $ перед «B», вы создали абсолютную ссылку на столбец. Теперь, когда вы заполните справа, вы увидите, что ссылка на столбец B для стоимости остается прежней.
-
Теперь, когда вы заполните столбец D вниз , вы увидите, что все относительные и абсолютные ссылки на ячейки работают точно так, как вы планировали.
-
Absolute cell references in Excel are critical for controlling which cells are referenced when you fill columns or rows. Use the $ symbol however you need within the cell reference to keep either the row or the column reference constant, in order to reference the correct data in the correct cell. When you combine relative and absolute cell references like this, it’s called a mixed cell reference.
If you use a mixed reference, it’s important to align the column or row of the source data with the column or row where you’re typing the formula. If you’ve made the row reference relative, then keep in mind that when you fill to the side, the column number of the source data will increment along with the column of the formula cell. This is one of the things that makes relative and absolute addressing complicated for new users, but once you master it you will have much more control over pulling source data cells into your formula cell references.
Using Absolute Cell References to Pin One Cell Reference
Another approach to using absolute cell referencing is by applying it to both the column and the row to essentially «pin» the formula to use only one single cell no matter where it is.
Using this approach, you can fill to the side or down and the cell reference will always stay the same.
Using an absolute cell reference on both column and row is only effective if you’re only referencing a single cell across all the cells you’re filling.
-
Using the same example spreadsheet from above, you can reference only the single state tax rate by adding the $ symbol to both the column and the row reference.
-
This makes both the «A» column and the «12» row stay constant, no matter what direction you fill the cells. To see this in action, fill the entire column for ME sales tax after you’ve updated the formula with the absolute column and row reference. You can see that each filled cell always uses the $A$12 absolute reference. Neither the column or the row changes.
-
Use the same formula for the Indiana sales tax column, but this time use the absolute reference for both column and row. In this case that’s $B$12.
-
Fill this column, and again you’ll see that the reference to B12 doesn’t change in ever cell, thanks to the absolute reference for both column and row.
-
As you can see, there are multiple ways you can use absolute cell references in Excel to accomplish similar tasks. What absolute references provide you is the flexibility to maintain constant cell references even when you’re filling a large number of columns or rows.
You can cycle through relative or absolute cell references by highlighting the reference and then pressing F4. Every time you press F4, the absolute reference will be applied to either the column, the row, both the column and cell, or neither of them. This is a simple way to modify your formula without having to type the $ symbol.
When You Should Use Absolute Cell References
Throughout nearly every industry and field, there are a lot of reasons you may want to use absolute cell references in Excel.
- Using fixed multipliers (like price per unit) in a large list of items.
- Apply a single percentage for each year when you’re projecting annual profit targets.
- When creating invoices, use absolute references to refer to the same tax rate across all items.
- Use absolute cell references in project management to refer to fixed availability rates for individual resources.
- Use relative column references and absolute row references to match column calculations in your referenced cells to column values in another table.
If you do use a combination of relative and absolute references for columns or rows, you just need to make sure that the position of the source data columns or rows match the column or row of the destination cells (where you’re typing the formula).