大多数人都熟悉在 Excel 中(in Excel)使用相对引用。这是因为Excel电子表格中的单元格引用默认为相对引用方法。
但是，有时相对引用会变得烦人。每当您复制单元格或填充列和行时，它都会更改单元格引用。如果您不想更改引用，则需要使用绝对引用或混合引用（包括相对引用和绝对引用）。
在绝对引用中，列和行引用都是“锁定的”，因此当您从该单元格复制或填充时，它们都不会改变。
本文将介绍如何使用绝对引用使Excel以您希望的方式处理数据。
相对引用在(References)Excel中的工作原理
当您在Excel(Excel)电子表格中输入值时，每个单元格都有一个特定的字母和数字分配给它。这表示该单元格的列和行。
例如，下面电子表格中“1”的值在 A 列和第 2 行中。所以对这个单元格的“引用”是A2。
如果要基于此单元格在下一个单元格中执行计算，则将其加1，您将编写以下公式：
=A2+1
这个公式会将A2中的值插入到公式中，进行计算，然后将结果输出到这个公式所在的单元格中。
当你按下Enter时，你会看到结果。
使用相对引用，您不必在每个其他单元格中输入此公式。您所要做的就是将带有原始公式的单元格的一角向下拖动到您想要的位置。
在下一个单元格中，对 A2 的引用将变为 A3。在其下方的单元格中，A3 将变为 A4。换句话说，Excel知道您要向前一个单元格添加 1，因此Excel会在您向下拖动时相应地更新数字（行引用）。
如果您跨列拖动公式，这将以相同的方式工作。Excel不会更新数字，而是更新引用的后半部分（列）以始终引用其上方的单元格。
右侧的列包含 B2，右侧的列包含 C2，依此类推。
这是一个简单的例子，说明相对寻址如何对列和单元格引用起作用。
绝对引用在(References)Excel中的工作原理
(Absolute)Excel中的(Excel)绝对引用允许您引用相同的单元格，而不是让Excel 自动(Excel automatically update)为您更新行或列引用。“混合”引用是仅锁定行或列，而“绝对引用”是同时锁定两者。
让我们看一些例子。
假设您的电子表格在顶行有一个“10”，并且您希望它下面的每一行都将该数字乘以左侧单元格中的数字。
为此，您需要输入如下所示的公式：
=B$2*A3
这将锁定“2”引用，因此如果您将使用此公式的单元格向下拖动到其下方的单元格，它不会更改行引用。由于 A3 保持“解锁”状态，行和列引用仍将自动更改并始终引用左侧的单元格。
您会注意到这仅在您向下拖动到同一列中的单元格时才有效。因此，您不必通过在列 (B) 前面放置美元符号 ($) 来锁定列 (B)。
这样做的问题是，如果您想在原始公式的右侧使用相同的公式，“B”引用将发生变化，公式将不再按预期引用 B2。
让我们看看如何使用绝对引用而不是混合引用来使两个方向的填充都正常工作。
在Excel中正确使用绝对(Absolute) 引用(References)
要在此公式中使用正确的引用，您必须准确考虑您要执行的操作。
在这种情况下，我们希望在向右填充时具有以下行为。
- 始终引用单元格 B2 中的值
- 始终引用 A 列中的值
- 将 A 列的行的引用移动到公式的当前行
查看这些行为，您现在知道需要“锁定”什么，不需要“锁定”什么。“B”和“2”都需要锁定（不变）。此外，A 列需要锁定。
因此，您在 B3 中的公式需要如下所示：=$B$2*$A3
现在，当您向下或向上拖动同一单元格时，公式将按预期工作。
正确使用绝对引用可能会变得很棘手，因此当您在任一方向填充列或行时(columns or rows)，必须花时间仔细考虑您希望 Excel 如何更新公式。
在Excel中循环引用类型(Reference Types)
当您通过按F4键(F4)输入具有绝对引用的公式(typing formulas)时，您可以加快该过程，这将使单元格引用成为绝对引用。
当您按 F4 时，光标可以在单元格引用的任一侧（甚至在它的中间），它仍然会将该单个引用转换为绝对引用。
如果您不想要绝对（例如，改为混合），请继续点击 F4，直到参考看起来像您想要的那样。
如果您想在公式中添加对其他单元格的任何类型的引用，只需将光标放在那里并再次开始循环 F4。
配置好公式后，只需按Enter 键(Enter)，然后按您喜欢的任何方向开始填写电子表格。如果您正确设置了引用，那么一切都应该按预期工作。
How to Use Absolute References in Excel
Most people are familiar with using relative references in Excel. This is because cell references in Excel spreadsheets default to the relative reference method.
However, there are times when relative references get annoying. It will change cell references any time you copy cells or fill columns and rows. If you don’t want references to change, you’ll need to go with absolute references or mixed references (including relative and absolute references).
In an absolute reference, both the column and row references are “locked,” so neither of those change when you copy or fill from that cell.
This article will look at how you can use absolute references to make Excel behave the way you want with data.
How Relative References Work in Excel
When you enter values into an Excel spreadsheet, each cell has a specific letter and number assigned to it. This represents the column and row of that cell.
For example, the value of “1” in the spreadsheet below is in column A and row 2. So the “reference” to this cell is A2.
If you want to perform a calculation in the next cell based on this cell, by adding 1 to it, you will write the following formula:
=A2+1
This formula will insert the value from A2 into the formula, calculate it, and then output the result into the cell where this formula is.
When you press Enter, you’ll see the result.
With relative referencing, you don’t have to type this formula into every other cell. All you have to do is drag the corner of the cell with the original formula down as far as you’d like.
In the next cell, the reference to A2 will become A3. In the cell below that, A3 will become A4. In other words, Excel knows that you want to add 1 to the previous cell, so Excel updates the number (the row reference) accordingly as you drag down.
This works in the same way if you drag the formula across columns. Instead of updating the number, Excel will update the latter part of the reference (the column) to always reference the cell above it.
The column to the right contains B2, to the right of that contains C2, and so on.
This is a simple example of how relative addressing works for both column and cell references.
How Absolute References Work in Excel
Absolute references in Excel let you reference the same cell, rather than letting Excel automatically update the row or column references for you. “Mixed” referencing is if you lock only the row or the column, and “absolute referencing” is when you lock both.
Let’s look at some examples.
Let’s say your spreadsheet has a “10” in a top row, and you want every row beneath it to multiply that number by the number in the cell to the left.
To do this, you’d enter a formula that looks like this:
=B$2*A3
This locks the “2” reference so it won’t change the row reference if you drag the cell with this formula down to the cells below it. Since A3 remains “unlocked” both the row and column reference will still change automatically and always reference the cell to the left.
You will notice that this only works because you’re dragging down into cells in the same column. So you don’t have to lock column (B) by placing a dollar sign ($) in front of it.
The problem with this is that if you want to use the same formula to the right of the original formula, the “B” reference will change, and the formula will no longer reference B2 as intended.
Let’s take a look at how to use absolute references instead of mixed references to make filling in both directions work properly.
Correctly Using Absolute References in Excel
To use the correct referencing in this formula, you must consider precisely what you’re trying to do.
In this case, we want the following behaviors when filling to the right.
- Always reference the value in cell B2
- Always reference the value in column A
- Shift the reference of the row for column A to the current row of the formula
Looking at these behaviors, you now know what you need to “lock” and what you don’t. Both “B” and “2” need to be locked (unchanged). Also, column A needs to be locked.
So your formula in B3 needs to look like this: =$B$2*$A3
Now when you drag this same cell either down or up, the formula works as intended.
Correctly using absolute referencing can get tricky, so it’s essential to take the time to carefully consider how you want Excel to update the formula as you fill columns or rows in either direction.
Cycling Through Reference Types in Excel
You can speed along the process when you’re typing formulas with absolute references by pressing the F4 key, which will make the cell reference absolute.
The cursor can be on either side of the cell reference (or even in the middle of it) when you press F4, and it’ll still convert that single reference to absolute.
If you don’t want absolute (for example, mixed instead), keep tapping F4 until the reference looks the way you want.
If you want to add any kind of referencing to other cells in the formula, just place your cursor there and start cycling through F4 again.
Once you have configured your formula, just press Enter and begin filling your spreadsheet in any direction you like. If you set up your references right, everything should work just as expected.
