更改Excel中单元格的引用？ [英] Change the reference of a cell in excel?

问题描述

也许你们可以帮助我，因为我很沮丧。以下是一些有助于说明我的问题的图片：

<如您所见，D1是引用 B1。但是，由于我使用的是INDIRECT函数，因此实际的引用为C1，并且会发生以下情况：

蓝色参考框位于C1而非B1上。有什么方法可以更改蓝框，使其包围实际引用的单元格（B1），而无需实际更改INDIRECT函数（C1）引用的单元格？

此外，这仅是一个示例来说明我的问题。我认为发布我的实际工作表是不明智的，因为我正在研究实验室中。放心，INDIRECT函数是必需的。

解决方案

从技术上讲，您可以使用条件格式，但是问题是您需要添加每个偏移量公式的条件格式公式。如果您留在同一行中并且有多个偏移量，则可以这样做，但是如果从各处调用偏移量，它会变得不规则。

在这种情况下，选择您的参考区域A1：C1，并确保A1是您所选择的活动单元格。进入条件格式并制作一个新的基于公式的规则。在公式的行中输入以下内容：

  = A1 = OFFSET（INDIRECT（$ C1），0,1）
  

这是假设您停留在同一行中，否则请使用$ C $ 1。

现在的关键是，您只需要为要检查的每个偏移量函数重复该过程即可。我将其保留为$ C1的原因是，以防偏移函数逐行重复。

概念证明

Maybe you guys could help me because I'm stumped. Here are some pictures to help illustrate my problem:

As you can see, D1 is "referencing" B1. However, because I'm using the INDIRECT function, the actual reference is C1 and the following occurs:

The blue reference box is on C1 and not B1. Is there any way to change the blue box so that it surrounds the actual cell being referenced (B1) without actually changing which cell is referenced by the INDIRECT function (C1)?

Also, this is just an example to illustrate my problem. I don't think it's wise to post my actual sheet because I'm in a research lab. Rest assured, the INDIRECT function is necessary.

解决方案

You technically could use conditional formatting, but the problem is you would need to add a conditional formatting formula for each one of your offset formulas. If you are staying in the same row and there a multiple offsets this might be ok, but if your offsets are being called from all over the place and can it will become unruly.

Select your reference area in this case A1:C1 and ensure A1 is your active cell within your selection. Go into conditional formatting and make a new formula based rule. In the line for the formula enter the following:

=A1=OFFSET(INDIRECT($C1),0,1)

This assumes you are staying in the same row, if not then use $C$1.

now the kicker is you just have to repeat that process for every offset function you want to check. The reason I left it as $C1 was in case the offset function was repeated row after row.

Proof of concept

这篇关于更改Excel中单元格的引用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！