体验零代码搭建

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

网友投稿  ·  2023-12-14 12:12  ·  CRM管理系统  ·  阅读 485


此图说明了一个朋友今天提出的一个关于Excel的大问题。他最初的问题是:“在此表中,如何返回出现最小值的日期?”但是,更笼统地说,他在问如何在两个维度上查找值。与大多数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),但是您可以输入所需的任何查找值。


Excel中使用美联储FRED数据库-自动下载美联储数据库数据- << 上一篇
2023-12-14 12:12
excel 如何画锯齿状折线?-excel如何画曲线图-
2023-12-14 12:12
下一篇 >>

相关推荐