获取给定行号和列号的单元格内容

我想得到一个单元格的内容，给出它的行号和列号。行号和列号存储在单元格中(这里是 B1、 B2)。我知道下面的解决方案是有效的，但是它们感觉有点粗糙。

第一个火星日

=CELL("contents",INDIRECT(ADDRESS(B1,B2)))

第二个火星日

=CELL("contents",OFFSET($A$1, B1-1,B2-1))

有没有更详细的方法? (比如 = CellValue (row，coll)或者其他什么) ？

编辑/澄清: 我只想使用 Excel 工作表公式。没有 VBA。简而言之，我几乎在寻找与 VBA Cell ()方法等价的 Excel 公式。

556770 次浏览
小开
最佳答案

You don't need the CELL() part of your formulas:

=INDIRECT(ADDRESS(B1,B2))

or 

=OFFSET($A$1, B1-1,B2-1)

will both work. Note that both INDIRECT and OFFSET are volatile functions. Volatile functions can slow down calculation because they are calculated at every single recalculation.

小开

Try =index(ARRAY, ROW, COLUMN)

where: Array: select the whole sheet Row, Column: Your row and column references

That should be easier to understand to those looking at the formula.

小开

It took me a while, but here's how I made it dynamic. It doesn't depend on a sorted table.

First I started with a column of state names (Column A) and a column of aircraft in each state (Column B). (Row 1 is a header row).

Finding the cell that contains the number of aircraft was:

=MATCH(MAX($B$2:$B$54),$B$2:$B$54,0)+MIN(ROW($B$2:$B$54))-1

I put that into a cell and then gave that cell a name, "StateRow" Then using the tips from above, I wound up with this:

=INDIRECT(ADDRESS(StateRow,1))

This returns the name of the state from the dynamic value in row "StateRow", column 1

Now, as the values in the count column change over time as more data is entered, I always know which state has the most aircraft.


提交答案