Excel学习网

用EXCEL在二维表查找数据-Excel教程-Excel学习网

39
此图说明了一个朋友今天提出的一个关于Excel的大问题。   他最初的问题是:;在此表中,如何返回出现最小值的日期?;   但是,更笼统地说,他在问如何在两个维度上查找值。与大多数Excel查找(仅限于单个行或列)相比,这是一个不同的挑战。   为了使解释尽可能简单,我开始 定义四个范围名称:  

用EXCEL在二维表查找数据-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),但是您可以输入所需的任何查找值。
本文链接:http://168361.com/excelyiyongjiqiao/2033.html (转载请保留)