根据多个条件查找EXCEL中最近的匹配 [英] Find the Closest match in EXCEL based on multiple criteria
问题描述
请在下面找到我的问题陈述：
Please find below my problem statement:
要求：对于下面显示的每一行记录，我想在10分钟范围内和+ 15个单位的数量。然后找到与当前记录最接近的记录，并获取Price值。
Requirement : For every row in the below displayed records, I want to find the records within 10 minutes range and within +-15 units of the quantity. And then find the record which is closest in time with the current record and fetch the Price value.
示例：对于Sno = 1的第一条记录，记录在10分钟的时间范围内，在+ -15个数量单位内，Sno = 2,4,6。在这三个记录中，时间范围内最接近的记录是Sno = 2.我想要获得Sno = 2的价格值，为12。
Example : Say for the first record with Sno=1,the records which fall within 10 min time range and within +-15 units of quantity are records with Sno = 2,4,6. Of these three records the one closest in time range is record Sno=2.So I want to get the Price value for record with Sno=2 which is 12.
- Sno价格数量时间
- 1 10 100 05/08/2013 10:12:13 AM
- 2 12 111 05/08/2013 10:10:11 AM
- 3 13 123 05/08/2013 10:22:13 AM
- 4 2 111 05/08/2013 10:22:13 AM
- 5 13 112 05/08/2013 10:42:13 AM
- 6 14 100 05/08/2013 10:15:13 AM
- Sno Price Quantity Time
- 1 10 100 05/08/2013 10:12:13 AM
- 2 12 111 05/08/2013 10:10:11 AM
- 3 13 123 05/08/2013 10:22:13 AM
- 4 2 111 05/08/2013 10:22:13 AM
- 5 13 112 05/08/2013 10:42:13 AM
- 6 14 100 05/08/2013 10:15:13 AM
欣赏您的帮助：）
感谢
RVK
Thanks RVK
推荐答案
假设 A2：D7 中的数据输入 E2 并填写：
Assuming data in A2:D7, enter in E2 and fill down:
= LOOKUP（2,1 /（ABS（D $ 2：D $ 7-D2）< TIME（，10,1））/ FREQUENCY（0， ABS（D $ 2：D $ 7 D2 ）*（2 *（ABS（C $ 2：C $ 7 C2）< = 15）-1） - （A $ 2：A $ J = A2）），B $ 2：B $ 7）
说明
公式可以分为几个组件（通过突出显示在公式栏中选择部分并按F9）：
The formula can be broken down into several components (by highlighting the chosen section in the formula bar and pressing F9):
A： =（ABS（D $ 2：D $ 7-D2）< TIME 0,10,1））返回 {TRUE; TRUE; TRUE; TRUE; FALSE; TRUE} 这是一个数组，表示它是否在10分钟（或 10:01 计算四舍五入）
A: =(ABS(D$2:D$7-D2)<TIME(0,10,1)) returns {TRUE;TRUE;TRUE;TRUE;FALSE;TRUE} which is an array indicating if it is within 10 minutes (or 10:01 to account for rounding)
B： = FREQUENCY （0，{...}）返回 {0; 1; 0; 0; 0; 0; 0} 在15个单位内的数量。 ABS（D $ 2：D $ 7-D2）*（2 *（ABS（C $ 2：C $ 7-C2）<= 15）-1）返回一个如果产品在15个单位内为负，则为时间差，否则为 - （A $ 2：A $ 7 = A2），则返回当前的负数行。在第一个参数中为零，频率函数将忽略这些负数。
B: =FREQUENCY(0,{...}) returns {0;1;0;0;0;0;0} which is the closest match in time within 15 units of quantity. ABS(D$2:D$7-D2)*(2*(ABS(C$2:C$7-C2)<=15)-1) returns an array of time differences which are positive if the product is within 15 units and negative otherwise, -(A$2:A$7=A2) is added so a negative number is returned for the current row. With zero in the first argument, the frequency function will ignore these negative numbers.
C：= LOOKUP（2,1 / A / B， B $ 2：B $ 7）返回相应的匹配，其中A和B都为1或TRUE。如果没有匹配，LOOKUP将返回＃N / A。
C: =LOOKUP(2,1/A/B,B$2:B$7) returns the corresponding match where A and B are both 1 or TRUE. The reciprocal is used as LOOKUP ignores errors, if no match is found LOOKUP will return #N/A.
这篇关于根据多个条件查找EXCEL中最近的匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
