索引/匹配时间匹配错误 [英] Index/Match Time Match Errors

问题描述

我正在使用index/match通过使用INDEX(D:D,MATCH(A2,B:B,0))匹配date/times来提取数据 date/times的A:A列已手动输入. date/times的B:B列输入为B2+time(,30,)，并向下复制.

I am using index/match to extract data by matching date/times using INDEX(D:D,MATCH(A2,B:B,0)) A:A column of date/times has been manually entered. B:B column of date/times input as B2+time(,30,) and copied down.

3行后，公式给出#N/A作为答案.

After 3 lines the formula gives #N/A as an answer.

即使A5=B5给出true结果，而行给出#N/A，也与时间不完全匹配有关 如果我将A:A复制到B:B，即所有手动输入的时间，则效果很好. 请帮忙.

It has something to do with the times not matching exactly even though A5=B5 gives a true result that row gives #N/A If I copy A:A to B:B i.e. all manually entered times it works perfectly. Please help.

A               B               C        D  E   F   G       H   I       J                   K
AET Entered     AET + 30        =    volts  lo  i1  i0      vl1 vl0     Value AET           Value Time +30
1/08/15 10:00   1/08/15 10:00   TRUE    36  36  36  36      36  36      42217.4166666667000 42217.4166666667000
1/08/15 10:30   1/08/15 10:30   TRUE    23  23  23  23      23  23      42217.4375000000000 42217.4375000000000
1/08/15 11:00   1/08/15 11:00   TRUE    44  44  44  44      44  44      42217.4583333333000 42217.4583333333000
1/08/15 11:30   1/08/15 11:30   TRUE    55  44  44  #N/A    44  #N/A    42217.4791666667000 42217.4791666667000
1/08/15 12:00   1/08/15 12:00   TRUE    13  55  55  #N/A    55  #N/A    42217.5000000000000 42217.5000000000000
1/08/15 12:30   1/08/15 12:30   TRUE    32  13  13  #N/A    13  #N/A    42217.5208333333000 42217.5208333333000
1/08/15 13:00   1/08/15 13:00   TRUE    56  32  32  #N/A    32  #N/A    42217.5416666667000 42217.5416666667000
1/08/15 13:30   1/08/15 13:30   TRUE    70  56  56  #N/A    56  #N/A    42217.5625000000000 42217.5625000000000
1/08/15 14:00   1/08/15 14:00   FALSE   43  70  70  #N/A    70  #N/A    42217.5833333333000 42217.5833333334000
1/08/15 14:30   1/08/15 14:30   TRUE    31  43  43  #N/A    43  #N/A    42217.6041666667000 42217.6041666667000
1/08/15 15:00   1/08/15 15:00   TRUE    21  31  31  #N/A    31  #N/A    42217.6250000000000 42217.6250000000000
1/08/15 15:30   1/08/15 15:30   FALSE   11  21  21  #N/A    21  #N/A    42217.6458333333000 42217.6458333334000
1/08/15 16:00   1/08/15 16:00   TRUE    66  11  11  #N/A    11  #N/A    42217.6666666667000 42217.6666666667000
1/08/15 16:30   1/08/15 16:30   TRUE    45  66  66  #N/A    66  #N/A    42217.6875000000000 42217.6875000000000
1/08/15 17:00   1/08/15 17:00   FALSE   23  45  45  #N/A    45  #N/A    42217.7083333333000 42217.7083333334000

推荐答案

这是浮点精度的问题.如您所见，即使=比较也失败，1/8/15 14:00 42012,5833333333与42012,5833333334.与MATCH(..,0)或VLOOKUP(.., FALSE)的精确匹配使用其他比较方法，因此它失败的频率更高.如果需要精确匹配的日期时间值，则需要将这些值ROUND设置为相同的小数位数.因此，我们需要将A和B的辅助列MATCH(..,0)或VLOOKUP(.., FALSE)中使用这些辅助列.

This is an issue with floating point precision. As you see even the = comparison fails with 1/8/15 14:00 42012,5833333333 vs. 42012,5833333334. The exact matching with MATCH(..,0) or VLOOKUP(.., FALSE) uses an other comparison method and so it fails more often. If we need exact matching date time values then we need those values ROUNDed the same count of decimals. So we need helper columns which ROUNDs A and B to 8 decimals and then use those helper columns in MATCH(..,0) or VLOOKUP(.., FALSE).

至少需要一个帮助程序列，因为我们可以直接在函数中四​​舍五入查找值.但是我们无法通过这种方式查找数组.

At least one helper column is needed since we could round the lookup value directly within the functions. But the lookup array we can not round that way.

示例:

公式:

B3向下:

=B2+TIME(0,30,0)

C2向下:

=A2=B2

D2向下:

=MATCH(B2,A:A,0)

E2向下:

=MATCH(ROUND(B2,8),G:G,0)

G2向下:

=ROUND(A2,8)

这篇关于索引/匹配时间匹配错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！