使用动态数组查找最接近的值

完美Excel · 公众号 · Excel · 2024-12-21 06:39

正文

学习Excel技术，关注微信公众号：

excelperfect

标签：Excel公式，SORTBY函数

有不少公式可以用来查找一组值中与给定值最接近的值，但大多非常复杂，本文给出了一个运用动态数组解决这个问题的公式，很简洁。

示例数据如下图1所示。列B中是一组值，单元格F1中是指定的值，可以看出，最接近的值是15。

图1

因为这个列表没有排序，所以不能使用查找函数。可以使用Excel动态数组函数来解决。

使用SORTBY函数创建一个与指定值的差值进行排序的列表。因为不关心这个差值的正负，即差值为-2与2是相同的，可以使用ABS函数将负值转换为正值。

SORTBY函数允许对要排序的列执行计算。在单元格E4中输入公式：

=SORTBY(A1:B5,ABS(B1:B5-F1))

结果如下图2所示，按与指定值的差值从小到大进行显示。

图2

然而，我们不需要列出完整的数据列表，只需要列出最接近的值的数据，即上面结果中的第一行。此时，可以使用INDEX函数来提取，在单元格E4中输入公式：

=INDEX(SORTBY(A1:B5,ABS(B1:B5-F1)),1)

结果如下图3所示。

图3

注意，如果存在重复项或其他接近值相同的数字，本文展示的技术不会将其全部列出。

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。

欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料，并通过社群加入专门的微信讨论群，更方便交流。

推荐文章

完美Excel · 使用动态数组查找最接近的值

3 天前

Excel之家ExcelHome · 快速拆分工作表，一个函数公式轻松搞定

4 天前

Excel之家ExcelHome · 这些公式用不好，天天加班做报表

5 天前

完美Excel · 在Word中控制PowerPint

4 天前

完美Excel · 判断单元格区域中的数值是否为连续数值

6 天前

生活一点通 · 这些食物一吃就胖，想瘦的人千万别碰！

7 年前

人像摄影 · 别致影像 |记录摄影的视角和厚度

7 年前

全球流行风尚 · 腿粗，来不及锻炼了，那就穿这个!

7 年前

芭莎娱乐 · 每日星范 | 周冬雨机场造型能美过金像奖红毯，靠的是这双轻便时髦的穆勒鞋！

7 年前

EHS俱乐部 · 【PPT】还记得砂轮片爆裂刺死工人的视频吗？现在就告诉你如何安全使用砂轮

7 年前