分享：在 EXCEL试算表中找出最接近目标的查找值

alias · 发表于 2019-1-8 19:41:39

本帖最后由 alias 于 2019-1-8 19:48 编辑

要在EXCEL中找到最接近目标的查找值，这就是查表呀，一个什么的 Vlookup()不就能轻易搅定？我也是这样想。

当我计算得到的电阻值6.82K，竟然给匹配成8K2而非6K8，所以要看看如何克服这点不足。只要你的目标值大於现有值，
那怕是大了0.001，EXCEL 都会寻找下一个比目标值大的结果。

要在EXCEL中找到最接近目标的查找值，我们需要了解以下五个方面，分工合作，缺一不可。希望对大家有帮助。

1. ABS功能
2. MIN功能
3. MATCH功能
4. INDEX功能
5. 使用ARRAY公式，必须用 "Control + Shift + Enter"键来确定。

以下是一个从要求工龄去匹配员工的例子。

假设单元格内容
目标值: E3          // 要求工龄
结果  : F3          // 匹配员工姓名

查找值列： C3：C16    // 员工工龄
姓名称列： B3：B16    // 员工姓名

用家在E3输入要求工龄数值，希望在F3单元格内得到最适当的人选姓名

步骤：

a. 计算目标值和多个查找值之间误差的绝对值

ABS（E3 - $C$3：$C$16）  //这会在内存中创建一个数组“a”

b. 使用MIN（）找出数组“a”内的最低误差值，就称它为“b”吧

MIN（ABS（E3 - $C$3：$C$16））//找出最小误差值

c。使用MATCH（）找出“b”在数组“a”中的位置

MATCH（MIN（ABS（E3 - $C$3：$C$16）），ABS（E3 - $C$3：$C$16），0）

d. 现在我们知道“b”的行位置，我们就可以使用INDEX（）找出需要的查找值或相关值

INDEX（$B3：$B16，MATCH（MIN（ABS（E3 - $C$3：$C$16）），ABS（E3 - $C$3：$C$16），0））

e。将以上 d 的数组公式输入F3单元格，然后必须使用 "Control + Shift + Enter"键来确定。

kevinchen026 · 发表于 2019-1-8 20:05:20

这个实用。

wye11083 · 发表于 2019-1-8 21:58:35

太麻烦了。。

rei1984 · 发表于 2019-1-9 09:57:08

成功了！！！

hyghyg1234 · 发表于 2019-1-9 11:08:05

我还是习惯用VBA来做这些。

wangjiati · 发表于 2019-1-9 12:14:49

cocalli 发表于 2019-1-9 11:20
去下载我的那个库存表，里面有些查找公式，不过现在有一点一直没好，
=VLOOKUP("*"&F2&"*",bom!C:D,2,) ...

=VLOOKUP("*"&F2&"*",bom!C:D,2,0)

wangjiati · 发表于 2019-1-9 19:05:46

cocalli 发表于 2019-1-9 12:28
哦，那我试试，谢谢
修正，是能用的，再认证下，谢谢

=VLOOKUP(F2,bom!C:D,2,0)

qqrenzhi1 · 发表于 2019-1-9 19:23:57

EXCEL试算表中找出最接近目标的查找值厉害，学习了!

逐鹿老猫 · 发表于 2021-8-2 15:57:57

这个是针对某行或某列数据，若是多行多列该如何改进此公式？求教列位大佬！

分享：在 EXCEL试算表中找出最接近目标的查找值

本帖子中包含更多资源

阿莫论坛20周年了！感谢大家的支持与爱护！！