此图说明了一个朋友今天提出的一个关于Excel的大问题。
他最初的问题是:“在此表中,如何返回出现最小值的日期?”
但是,更笼统地说,他在问如何在两个维度上查找值。与大多数Excel查找(仅限于单个行或列)相比,这是一个不同的挑战。
为了使解释尽可能简单,我开始 定义四个范围名称:
FindVal= Sheet1!$ A $ 10
月数= Sheet1!$ B $ 2:$ I $ 2
年份= Sheet1!$ A $ 3:$ A $ 8
数据= Sheet1!$ B $ 3:$ I $ 8
然后我开始建立公式。
SUMPRODUCT函数是关键,因为它是唯一可以像数组公式一样工作而无需输入数组的函数。例如,此公式返回数据范围内的任何值的值等于FindVal的次数:
= SUMPRODUCT((Data = FindVal)* 1)
(Data = FindVal)件返回TRUE和FALSE值的数组。我们需要将该数组乘以1才能将数组转换为1和0值的值,这些值可以计数。
假设现在只有一个值与FindVal匹配,我们可以使用以下公式找到它所在的行:
= SUMPRODUCT(ROW(数据)*(数据= FindVal))
之所以可行,是因为(Data = FindVal)返回的数组只有一个TRUE值。当我们将其行号乘以TRUE值,然后对结果求和时,公式将返回该单个行号。
但是,就像Excel的MATCH函数一样,我们不需要实际的行号,我们希望数据范围内的索引号……这也是Years范围的索引号。因此,我们减去数据范围顶行的行号,然后添加1:
= SUMPRODUCT(ROW(数据)*(Data = FindVal))-ROW(数据)+1
该公式在某种程度上等效于MATCH函数。但是,如果数据范围具有两个与FindVal匹配的值,则此公式将返回错误的结果。因此,我们需要添加一个测试以确保只有一个这样的值存在:
= IF(SUMPRODUCT((Data = FindVal)* 1)<> 1,NA(),SUMPRODUCT(ROW(Data)*(Data = FindVal))-ROW(Data)+1)
最后,我们可以将此值传递给INDEX函数以返回所需的年份值:
A11:= INDEX(年份,IF(SUMPRODUCT((Data = FindVal)* 1)<> 1,NA(),SUMPRODUCT(ROW(Data)*(Data = FindVal))-ROW(Data)+1))
返回月份值的版本非常相似:
A12:= INDEX(月,IF(SUMPRODUCT((Data = FindVal)* 1)<> 1,NA(),SUMPRODUCT(COLUMN(Data)*(Data = FindVal))-COLUMN(Data)+1))
最后,要回答我朋友的原始问题,以下是返回日期序列号的公式,可以在其中找到单元格A10中的值:
A13:= DATEVALUE(A12&“-”&A11)
我的朋友将在单元格A10中输入= MIN(Data),但是您可以输入所需的任何查找值。