计算Google表格范围内子范围的PRODUCT [英] Calculate PRODUCT of a sub-range in a Google Sheets range

问题描述

我有一个范围，列的类型为行，行的级别为. 我需要找到一个从1级到给定类型的给定级别的子范围的产品.

I have a range with Type for columns and Level for rows. I need to find the PRODUCT of a sub-range which is from Level 1 to the given Level of given Type.

到目前为止，我尝试使用以下方法找到子范围的开始和结束单元格的位置:

So far I tried to find the position of the starting and ending cells of sub-range by using:

=CELL("address",INDEX(A1:N21,2,MATCH(R4,A1:N1,0)))

=CELL("address",index(A1:N21,R3+1,match(R4,A1:N1,0)))

然后我考虑使用CONCATENATE给出范围地址，但它返回0.

Then I think about using CONCATENATE to give the range address but it returns 0.

示例表: https://docs.google.com/spreadsheets/d/1byIjDzHC1Z edit#gid = 0

推荐答案

您是对的，不需要那么长的公式. Index返回一个单元格引用，因此您可以执行index(... startrow，startcol):index(... endrow，endcol)形式的操作，如下所示:

You are right, you don't need such a long formula. Index returns a cell reference, so you can do something in the form of index(...startrow,startcol):index(...endrow,endcol) like this:

=PRODUCT(INDEX(A1:N21,2,MATCH(R4,A1:N1,0)):INDEX(A1:N21,R3+1,MATCH(R4,A1:N1,0)))

更短的时间是偏移量:

=PRODUCT(offset(A1,1,MATCH(R4,A1:N1,0)-1,R3,1))

这篇关于计算Google表格范围内子范围的PRODUCT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！