首页
学习
活动
专区
工具
TVP
发布
精选内容/技术社群/优惠产品,尽在小程序
立即前往

查找 Excel 表格行列交叉处的值,三个公式套路拿走不谢

公众号平台最新的推送规则对技术类文章不太友善,如果不想错过干货,请务必 “设为星标”哦!!!

点击上方蓝字 --> 点击“...”--> 选择“设为星标

今天的查找案例其实不复杂,根据行列线索,查找出交叉点的值,类似的案例教过很多次了,但是既然有人提问,还是再讲解一下,一共 3 个公式,从小众到常见。

案例:

根据下图 1 中右边的数据表的行列标题,从左侧数据表中查找出对应的值。

效果如下图 2 所示。

解决方案 1:

如果是 365 版本,那么完全可以尝试这个函数。

1. 在 J2 单元格中输入以下公式 --> 向右向下拖动,复制公式:

=FILTER(FILTER($B$2:$G$13,$A$2:$A$13=$I2),$B$1:$G$1=J$1)

公式释义:

这里用了 2 次 FILTER 函数,我们照例从内向外解析;

FILTER($B$2:$G$13,$A$2:$A$13=$I2):

filter 是个 365 函数,作用是基于定义的条件筛选一系列数据;

语法为 FILTER(要筛选的数组或区域,其高度或宽度与数组相同的布尔值数组,[为空时返回的值]);

$B$2:$G$13:要从中取值的区域

$A$2:$A$13=$I2:取值条件为 A 列的姓名等于 I2 单元格的姓名;

FILTER(...,$B$1:$G$1=J$1):

上述公式会提取出姓名为“詹姆斯下士”的一整行数值,该数组为此处的第一个参数;

$B$1:$G$1=J$1:从上述区域中提取出月份等于 J1 单元格的数值,即找出纵坐标也符合条件的单元格

* 请注意参数的绝对和相对引用。

解决方案 2:

如果还没升级到 365,那也没关系,这个神仙函数也很好使。

1. 将公式修改如下:

=SUMPRODUCT($B$2:$G$13*($A$2:$A$13=$I2)*($B$1:$G$1=J$1))

公式释义:

SUMPRODUCT 函数的作用是返回相应范围或数组的个数之和;

参数为 SUMPRODUCT (array1,[array2],[array3], ...)

array1:相乘并求和的第一个数组参数;

[array2],[array3],...:要要进行相乘并求和的其他参数;

参数中相乘的三个区域分别为:

$B$2:$G$13:要查找的所有数值区域;

$A$2:$A$13=$I2:符合查找条件的行;

$B$1:$G$1=J$1:符合查找条件的列

上述三个区域相乘后,仅满足所有条件的,乘积才不会为 0,求和后就是纵横交叉处的单元格值

解决方案 3:

1. 还可以使用以下传统公式:

=INDEX($B$2:$G$13,MATCH($I2,$A$2:$A$13,0),MATCH(J$1,$B$1:$G$1,0))

公式释义:

index 函数的语法为 INDEX(要查找的区域, 区域内的行号, [区域内的列号]);

$B$2:$G$13:要查找的区域;

MATCH($I2,$A$2:$A$13,0):

在区域 $A$2:$A$13 中查找 $I2,并返回代表其所在位置的编号;

0 表示精确查找;

返回的值为行号;

MATCH(J$1,$B$1:$G$1,0):用同样的公式匹配出列号

  • 发表于:
  • 原文链接https://page.om.qq.com/page/Odgri-Mv-9nC3GoblbOoVW5w0
  • 腾讯「腾讯云开发者社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 cloudcommunity@tencent.com 删除。

扫码

添加站长 进交流群

领取专属 10元无门槛券

私享最新 技术干货

扫码加入开发者社群
领券