Excel合并两个列表
我有两个Excel列表：
- 一条有两万条线的大范围。其中：
两列很重要:第一:唯一ID；第二:值(数字格式化)。
它可以是多次出现或只出现一次的值。
- 我必须创建第二个列表。在这个列表中，我只想拥有一列我想要的值。
我需要一个公式，它将从列表1中的列表2中查找值，然后将唯一的ID匹配到每个值。
重要的是，当不存在直接价值时。在这种情况下，它必须搜索一个样本，其值偏差约为3-5%。
例子:没有127，但在3%，125被发现。
我试过索引和比较，但似乎不起作用。
VLOOKUP有效，但没有3-5%的偏差。
我非常感谢你的帮助。
示例：http://www.filedropper.com/excellist1and2
回答 1
Stack Overflow用户
发布于 2018-03-13 12:47:43
如果该值存在于列表中，则可以使用VLOOKUP或INDEX(MATCH查找它--这很容易。如果该值不在列表中，则需要找到最近的值。
最近的“低”值将是MAX值≤我们的输入，而最近的“高”值将是MIN值≥我们的输入。
如果您有Office 365，可以在这里使用MINIFS($D$1:$D$6,$D$1:$D$6,">="&B1,$D$1:$D$6,"<="&(B1*1.05))和MAXIFS($D$1:$D$6,$D$1:$D$6,"<="&B1,$D$1:$D$6,">="&(B1*0.95)) )`。如果不是，您将需要一个数组公式，我们可以在早期构建"±5%“，以简化公式。
从低值开始，我们希望MAX值≤我们的输入和≥95%的输入。在SUMPRODUCT中放置一个数组公式，这样我们就可以在一个正常的公式中使用它，我们得到了=SUMPRODUCT(MAX($D$1:$D$6*--($D$1:$D$6<=B1)*--($D$1:$D$6>=(B1*0.95))))
高值稍微困难一些，因为我们不能仅仅乘以be 0来抵消太低的值，或者超过目标的105%。我们需要添加一个很大的数字，比如1E+99 (一个在它之后有99-90个0的1)，这样MIN就会忽略它们：SUMPRODUCT(MIN($D$1:$D$6+1E+99*(--($D$1:$D$6<B1)+--($D$1:$D$6>(B1*1.05)))))
最后一步是决定哪些数字更接近目标，然后找到要匹配的唯一ID。百分比贴近度计算是(TARGET - LOW)/TARGET和(HIGH - TARGET)/TARGET)，从另一个中减去一个就可以得到2-(HIGH + LOW)/TARGET --一个正数表示“高”更近，一个负数表示“低”更近，而0表示它们都是相同的距离(我将这个值默认为低数)。然后我们使用SIGN将其更改为±1，添加2以得到1,2或3，然后用CHOOSE完成输出我们的数字。在伪代码、CHOOSE(2+SIGN(2-(HIGH+LOW)/TARGET),LOW,LOW,HIGH)和全部内容中：
CHOOSE(2+SIGN(2-(SUMPRODUCT(MAX($D$1:$D$6*--($D$1:$D$6<=B1)*--($D$6>B1*0.95)))+SUMPRODUCT(MIN($D$1:$D$6+1E+99*(--($D$1:$D$6<B1)+--($D$1:$D$6>(B1*1.05))))))/B1),SUMPRODUCT(MAX($D$1:$D$6*--($D$1:$D$6<=B1)*--($D$6>B1*0.95))),SUMPRODUCT(MAX($D$1:$D$6*--($D$1:$D$6<=B1)*--($D$6>B1*0.95))),SUMPRODUCT(MIN($D$1:$D$6+1E+99*(--($D$1:$D$6<B1)+--($D$1:$D$6>(B1*1.05))))))现在，我们有了一个号码。我们所需要做的就是要么使用VLOOKUP，要么使用MATCH获取它所在的行，并使用INDEX来提取该行的数据：
办公室365：
=IFERROR(VLOOKUP(B1,$D$1:$E$6,2,FALSE),VLOOKUP(CHOOSE(2+SIGN(2-(MAXIFS($D$1:$D$6,$D$1:$D$6,"<="&B1,$D$1:$D$6,">="&(B1*0.95))+MINIFS($D$1:$D$6,$D$1:$D$6,">="&B1,$D$1:$D$6,"<="&(B1*1.05)))/B1),MAXIFS($D$1:$D$6,$D$1:$D$6,"<="&B1,$D$1:$D$6,">="&(B1*0.95)),MAXIFS($D$1:$D$6,$D$1:$D$6,"<="&B1,$D$1:$D$6,">="&(B1*0.95)),MINIFS($D$1:$D$6,$D$1:$D$6,">="&B1,$D$1:$D$6,"<="&(B1*1.05))),$D$1:E$7,2,FALSE))否则：
=IFERROR(VLOOKUP(B1,$D$1:$E$6,2,FALSE),VLOOKUP(CHOOSE(2+SIGN(2-(SUMPRODUCT(MAX($D$1:$D$6*--($D$1:$D$6<=B1)*--($D$6>B1*0.95)))+SUMPRODUCT(MIN($D$1:$D$6+1E+99*(--($D$1:$D$6<B1)+--($D$1:$D$6>(B1*1.05))))))/B1),SUMPRODUCT(MAX($D$1:$D$6*--($D$1:$D$6<=B1)*--($D$6>B1*0.95))),SUMPRODUCT(MAX($D$1:$D$6*--($D$1:$D$6<=B1)*--($D$6>B1*0.95))),SUMPRODUCT(MIN($D$1:$D$6+1E+99*(--($D$1:$D$6<B1)+--($D$1:$D$6>(B1*1.05)))))),$D$1:E$7,2,FALSE))(显然，将$D$1:$D$6和$D$1:$E$6更改为实际数据表范围，将B1更改为输入值范围)
https://stackoverflow.com/questions/49251244
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号