拖动INDIRECT和 [英] Dragging INDIRECT and

问题描述

我想我会带您思考，因为可能有比使用INDIRECT达到预期结果更好的方法，但是如果没有，那么使用间接解决方案将非常方便.

I thought I would take you through my thinking as there may be a better way than using INDIRECT to achieve the desired result but if not then a solution with indirect would be very handy.

我有一个简单的公式

=SUMPRODUCT($E$10:$E20,J10:J20)拖到大量以CC结尾的列中

=SUMPRODUCT($E$10:$E20,J10:J20) Dragged across a great number of columns ending somewhere in the CC's

但是我发现当我插入一行时，它将把我的10美元和10美元更改为11美元和11美元，所以我在其中放置了一个间接地址.

However I found when I inserted a row it would change my 10 and $10 to 11 and $11, so i placed an indirect in there.

=SUMPRODUCT(INDIRECT("$E$10:$E20"),INDIRECT("J10:J20"))

我现在意识到我要引用一个字符串，如果将其拖到右侧，则单元格引用将不会更新.

I now realize that I'm referring to a string and if I drag this across to the right, the cell references won't update.

所以最初我的第一个问题是如何更新这些内容，以便可以将INDIRECT跨列拖动到右侧并更新单元格引用，以便我的公式类似于或至少以相同的方式工作将其拖入K列等.

So initially my first question was how could I go about updating these so that I can drag the INDIRECT across the columns to the right and update the cell references so that my formula would resemble or at least work in the same way when dragged into column K and so on.

=SUMPRODUCT(INDIRECT("$E$10:$E20"),INDIRECT("K10:K20"))

但是

如果有比间接方法更好的解决方案，在插入或删除行时保留原始公式，这也将非常有帮助.

If There is a better solution than the indirect to keep my orignial formula when rows are inserted or deleted that would be very helpful too.

推荐答案

当您只想防止行名称在行插入时发生更改时，您已将行和列都转换为静态字符串表示形式.将您的INDIRECT函数 索引功能将允许您用常规的 $ 将E列设为绝对，并保持J列相对，并对引用的行进行硬编码，以使它们在行插入时不会改变.

You have turned both rows and columns into static string representations when you really want to only guard the row designations from changing on a row insert. Replacing your INDIRECT functions with INDEX functions will allow you to make column E absolute with a conventional $ and leave column J relative as well as hard-code the referenced rows so they will not change on a row insert.

=SUMPRODUCT((INDEX($E:$E, 10):INDEX($E:$E, 20)), (INDEX(J:J, 10):INDEX(J:J, 20)))

只需不插入任何列，一切都应按计划进行.

Just don't insert any columns and everything should work as planned.

在相关说明中，将INDIRECT中的字符串"$ E $ 10:$ E20"引用为字符串可能有助于视觉识别其目的，但是正如您所注意到的，尽管将其拖动到新位置或插入行.如果使用INDIRECT("E10:E20")，则没有区别.

On a related note, referring to "$E$10:$E20" as a string in INDIRECT may help visually identify its purpose but as you've noted, the text is not going to change despite dragging to a new location or inserting rows. There is no difference if you use INDIRECT("E10:E20").

这篇关于拖动INDIRECT和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！