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

时间:2022-04-18 04:15:14 

此图说明了一个朋友今天提出的一个关于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教程
0
投稿

猜你喜欢

  • TREND函数预测双十二的交易额

    2022-07-28 13:15:46
  • excel中的Edate怎么使用

    2023-06-05 14:03:57
  • word如何插入分隔符和删除分隔符

    2023-12-13 05:32:03
  • Word中格式刷之后公式靠上怎么解决

    2023-11-12 16:48:26
  • Excel如何制作动态图表?Excel动态图表制作方法

    2023-10-19 01:57:20
  • win10控制面板卸载不了软件怎么办?win10控制面板卸载不了软件解决方法

    2023-08-22 14:06:37
  • 在WORD文档里填加目录怎么设置?

    2023-11-29 12:32:02
  • Excel合并单元格后换行的两种方式

    2023-10-15 00:31:20
  • Word页码怎么设置第几页共几页

    2022-02-09 11:19:33
  • office软件单击超链接出现“由于本机的限制,该操作已被取消,请与系统管理员联系”的解决方法

    2023-10-10 00:13:33
  • windows10版本1903更新卡在96%怎么解决

    2023-11-09 11:03:26
  • 在Word2007文档中如何批量替换呢?

    2022-03-07 05:48:59
  • excel中怎么使用VALUE函数

    2023-11-26 02:50:44
  • 在Word2010中创建包含上下标的公式

    2023-04-30 06:38:36
  • Word2003是如何运行宏的?

    2023-12-13 14:42:55
  • keynote可以插入word文档吗?

    2023-12-01 19:42:58
  • word文档中的文字如何直接变成繁体字?

    2023-08-29 02:15:44
  • word中怎样调整脚注的格式

    2023-11-30 04:07:51
  • win10开机慢而且黑屏很久_win10开机慢黑屏久怎么解决?

    2023-11-09 00:08:46
  • word 输入的文字不在线上,下划线一直变成长,后面空格添加不了下划线

    2023-04-20 15:45:12
  • asp之家 电脑教程 m.aspxhome.com