一项很容易被忽视但对于精确计算非常重要的功能是“锚定”。如果您曾经复制过 Excel 公式(copied an Excel formula)，将其粘贴到不同的单元格中，并看到意外的结果，那么锚定可能是拼图中缺失的部分。
让我们了解相对和绝对单元格引用(absolute cell references)之间的区别，以及如何使用它们在Microsoft Excel中锚定单元格。
Excel 中的锚定单元格是什么？
在Excel(Excel)中抛锚就像从船上抛锚一样。当您抛锚时，无论水流如何移动，您的船都会留在原地。同样，当您在Excel(Excel)中锚定单元格时，即使您将公式移动和复制到电子表格的不同部分，也可以确保单元格引用保持“固定”。
锚定细胞在许多情况下都可以派上用场。例如，您正在制定预算，并且有一个包含税率的单元格。当您计算电子表格中各个项目的税费时，您会希望税率保持不变，而不是在将公式向下或跨行拖动时发生变化(down a column or across a row)。
在Excel(Excel)中锚定单元格可以避免潜在的错误，并简化在包含大量数据的电子表格中使用重复值的过程。
了解相对(Relative)和绝对单元格(Absolute Cell)引用
了解锚细胞如何工作需要了解相对参考(relative reference)和绝对参考。
将相对引用视为Excel的适应性方式。当您将公式中的相对引用复制到另一个单元格时，引用会根据其新位置发生变化。您告诉Excel根据您移动公式的位置来调整公式。例如，假设在 A1 中使用 =A2+10 公式，然后将其复制到 B1，则它将变为 =B2+10。
绝对引用则相反。无论您将公式复制到哪里，绝对引用都可以确保公式的部分（或全部）保持固定在该特定单元格上。使用我们之前的税务参考，如果您在 A1 中有税率并且想要在各种计算中使用该税率，则可以在公式中使用 $A$1。这告诉Excel，无论您将公式拖动或复制到何处，始终使用 A1 中的值进行这部分计算。
因此，正如您可能已经猜到的那样，当您想要使用绝对引用时，应该锚定单元格。
如何在 Excel 中锚定单元格
现在您已经了解了锚定单元的复杂性，下面是具体操作方法。
- 选择包含您要锚定的公式的单元格。无论您接下来在何处使用公式，此单元格都应该具有您不想更改的特殊值或引用。
- 在顶部的编辑栏中，在列字母、行号或两者之前添加 $（美元符号），具体取决于您想要保持不变的内容。或者，您可以突出显示要锚定的部分，然后按 F4(and press F4)（键盘快捷键）。这将自动添加美元符号。
- 按 Enter 键。
您的单元格现已锚定。将此公式复制或移动到其他地方时，锚定参考将保持不变。
使用正确的(Right) 锚点(Anchors)组织您的数据
在Excel(Excel)中锚定单元格是保持电子表格准确和一致所需的基本技能。通过了解相对引用和绝对引用之间的差异，您可以控制公式在移动或复制到工作表的不同部分时的行为方式。
How to Anchor Cells in Microsoft Excel
One featυre that’s easy to overlook but is incredibly important for accurate сalculatіons is “anchoring.” If you’ve ever copied an Excel formula, pasted it in a different cell, and saw unexpected results, anchoring might be the missing piece of the puzzle.
Let’s learn the difference between relative and absolute cell references and how to use them for anchoring cells in Microsoft Excel.
What Is Anchoring Cells in Excel?
Anchoring in Excel is like dropping an anchor from a ship. When you drop an anchor, your boat stays in place regardless of how the water moves. Similarly, when you anchor a cell in Excel, you ensure that the cell reference stays “fixed” even as you move and copy your formulas to different parts of your spreadsheet.
Anchoring cells can come in handy in many scenarios. For example, you’re working on a budget and have a cell containing the tax rate. As you calculate taxes for various items across your spreadsheet, you’ll want that tax rate to remain constant—not change as you drag your formula down a column or across a row.
Anchoring cells in Excel can save you from potential errors and simplify the process of using repetitive values across spreadsheets with lots of data.
Understanding Relative and Absolute Cell References
Understanding how anchor cells work requires knowledge about relative reference and absolute reference.
Think of relative references as Excel’s way of being adaptable. When you copy a relative reference in a formula to another cell, the reference changes based on its new location. You’re telling Excel to adjust the formula based on where you move it. For example, let’s say in A1 you use the =A2+10 formula, and then you copy this to B1, it will then become =B2+10 instead.
An absolute reference is the opposite. No matter where you copy your formula, an absolute reference ensures that part (or all) of the formula remains fixed on that specific cell. Using our tax reference from earlier, if you have the tax rate in A1 and you want to use this in various calculations, you can use $A$1 in your formulas. This tells Excel that no matter where you drag or copy the formula, always use the value in A1 for this part of the calculation.
So, as you may have guessed, you should anchor cells when you want to use an absolute reference.
How to Anchor Cells in Excel
Now that you understand the intricacies of anchoring a cell, here’s how to do it.
- Select the cell that contains the formula you’d like to anchor. This cell should have the special value or reference you don’t want to change, no matter where you use the formula next.
- In the formula bar at the top, add a $ (dollar sign) right before the column letter, the row number, or both, depending on what you want to keep constant. Alternatively, you can highlight the parts you want to anchor and press F4 (the keyboard shortcut). This will automatically add the dollar signs.
- Press Enter.
Your cell is now anchored. The anchored reference will remain the same when copying or moving this formula elsewhere.
Organize Your Data With the Right Anchors
Anchoring cells in Excel is a fundamental skill required for keeping spreadsheets accurate and consistent. By understanding the difference between relative and absolute references, you can control how formulas behave when moved or copied to different parts of the worksheet.
