Excel - 使用单元格输入引用公式的列 [英] Excel - Reference a column for a formula, using input from a cell

问题描述

我正在努力提高我在excel方面的知识，我一整天都在搜索我现在的问题的答案，但是我无法找到任何有关我的需求的信息。

基本上，我想知道是否可以通过将公式存储在单元格中来引用公式应用哪一列。

所以如果我有一个公式这是使用列 - Test！C：C，我能够将该字符串存储在单元格中，并在公式中引用该单元格？我有很多公式使用相同的引用，所以如果我决定更改我想要使用的列，需要一些时间来删除它们。

我知道我可以使用替换全部，但如果有一种通过单元格引用列的方法将是非常棒的。

解决方案

使用间接功能：

如果您将列地址Test！C：C的单元格为A1，则可以使用：

  = INDIRECT（A1）
  

改变了，你只想参考列。所以在A1你只想把C：

  = INDIRECT（Test！& A1&： & A1）
  

间接允许您输入一个字符串，然后用作参考。 p>

有一个主要的回调到INDIRECT（）函数，它是volatile。这意味着它将在每次excel计算时计算，不仅在参考单元发生变化时。所以取决于公式的数量，它会减慢计算时间。

I am trying to further my knowledge in excel, I have searched around all day for an answer to my current question but I was unable to find anything relating to my needs.

I basically, want to know if it is possible to reference which column a formula should use, by storing it in a cell.

So if I have a formula which is using column - Test!C:C, am I able to store that string in a cell and reference that cell in the formula? I have many formulas which are using the same reference, so if i decide to change what column i want to utilize, it takes some time to remove them all.

I know i can use replace all, but it would be fantastic if there was a method to reference a column via a cell.

解决方案

Use the Indirect Function:

If the cell in which you put the column Address "Test!C:C" was A1, you would use:

=INDIRECT(A1)

If the sheet never changed and you only wanted to reference the column. So in A1 you only want to put "C":

=INDIRECT("Test!" & A1 & ":" & A1)

Indirect lets you enter a string that is then used as a reference.

There is one major draw back to the INDIRECT() function, it is volatile. This means that it will calculate every time excel calculates, not only when the reference cells change. So depending on the number of formulas, it will slow the calculation times.

这篇关于Excel - 使用单元格输入引用公式的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！