当前位置: 首页 > Excel > Excel常用函数 > ExcelExcel技巧 > 用EXCEL在二维表查找数据-Excel教程-Excel学习网

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

发布时间:2020年09月27日 07:40:14 来源: 点击量:580

【摘要】此图说明了一个朋友今天提出的一个关于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),但是您可以输入所需的任何查找值。

分享到: 编辑:wangmin

就业培训申请领取
您的姓名
您的电话
意向课程
点击领取

环球青藤

官方QQ

扫描上方二维码或点击一键加群,免费领取大礼包,加群暗号:青藤。 一键加群

绑定手机号

应《中华人民共和国网络安全法》加强实名认证机制要求,同时为更加全面的体验产品服务,烦请您绑定手机号.

预约成功

本直播为付费学员的直播课节

请您购买课程后再预约

环球青藤移动课堂APP 直播、听课。职达未来!

安卓版

下载

iPhone版

下载
环球青藤官方微信服务平台

刷题看课 APP下载

免费直播 一键购课

代报名等人工服务

课程咨询 学员服务 公众号

扫描关注微信公众号

APP

扫描下载APP

返回顶部