谷歌电子表格的功能是按行和列名从一个范围中获取一个单元格 [英] Google Spreadsheets function to get a cell from a range by row and col name
本文介绍了谷歌电子表格的功能是按行和列名从一个范围中获取一个单元格的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有范围
idz01 idz04 ida02
foo a 1 b
bar c 3 8
baz 8 2 g
我想从这个范围中获取值,如下所示:
SOME_FUNCTION(rangeID,'foo','idz04')
得到 1
等。或者理想情况下,获得单元格:
INDIRECT(SOME_FUNCTION(rangeID,'foo','idz04'))
等等。
有没有这样的东西?
编辑: $ b $是否可以使用 GETPIVOTDATA()
b我可以以某种方式使用 LOOKUP
,如果我能够获得 result_range
:
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ L $ DataRange),0)
)
)
)
只,我没有 FIRST_ROW()
和 FIRST_COL()
...也许 FILTER ()
可以提供帮助吗?
解决方案
b $ b
= INDEX(ObjednavkyData,
//在第一列中查找vesela01的偏移量
MATCH(vesela01,
$ OFFSET((ObjednavkyData),0,0,
MAX(ARRAYFORMULA(ROW(ObjednavkyData)))//范围
,1)
,0),
//查找第一行中z03的偏移量
MATCH(z03,
OFFSET(ObjednavkyData,0,0 ,1)//第一行
,0))
I have a range
idz01 idz04 ida02
foo a 1 b
bar c 3 8
baz 8 2 g
And I want to get values from this range, like this:
SOME_FUNCTION(rangeID, 'foo', 'idz04')
to get 1
, etc. Or ideally, to get the cell:
INDIRECT(SOME_FUNCTION(rangeID, 'foo', 'idz04'))
or such.
Is there something such? Can the GETPIVOTDATA()
be used for that?
Edit:
I could somehow employ the LOOKUP
if I was able to get the result_range
:
LOOKUP("foo", DataRange,
FIRST_COL(
OFFSET(DataRange,
MATCH("idz04", FIRST_ROW(DataRange), 0)
)
)
)
Only, I don't have FIRST_ROW()
and FIRST_COL()
... Maybe FILTER()
could help?
解决方案
Here it goes:
=INDEX(ObjednavkyData,
// Find the offset of "vesela01" in the 1st column
MATCH("vesela01",
OFFSET((ObjednavkyData),0,0,
MAX(ARRAYFORMULA(ROW(ObjednavkyData))) // Num of rows in the range
, 1)
,0),
// Find the offset of "z03" in the 1st row
MATCH("z03",
OFFSET(ObjednavkyData, 0 , 0, 1) // First row
, 0))
Not really neat :P
Also see Google Spreadsheets: How to get a first (Nth) row/column from a range? (built-in functions)
这篇关于谷歌电子表格的功能是按行和列名从一个范围中获取一个单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文