搜索列A以匹配列C中的数字，如果True返回列B，如果返回错误“0” [英] Search Column A to match a number in column C if True return column B if false return "0"

问题描述

我正在尝试以15分钟的增量收集数据，看起来像以5分钟为增量收集的数据，空格为零。我创建了一个具有儒略日期/时间（15分钟增量）的列（A）和一个具有儒略日期/时间（5分钟增量）（C）的列，并且15分钟增量数据在列B中。我创建了一个简单的公式检查列A中的所有列与C列完全匹配，如果为true，则需要返回与列A中的匹配对应的列B，如果为false则返回0。

这是我到目前为止是真正的回报公式。在列B中返回相应数据的公式是什么？

  = IF（ISNUMBER（MATCH（C2，D：D， 0）），???，0）
  

解决方案

您的示例公式看起来像使用您叙述描述的不同列。我将使用我从您的描述中理解的列作为列。查看下面的示例图片。

时间查找是。这是有符号整数的最大正值或32,767（约22¾天的分钟）。

I am trying to make data collected in 15 minute increments look like data collected in 5 minute increments with the blank spaces being zero. I have created a column with Julian date/time in 15 minute increments (A) and a column with Julian date/time in 5 minute increments (C) and the 15 minute increment data is in column B. I have created a simple formula to check all of column A for an exact match to column C if true I need it to return Column B corresponding to the match in column A, If false it can return 0.

Here is what I have so far with the ?????? being the missing True return formula. What is the formula to return the corresponding data in column B?

=IF(ISNUMBER(MATCH(C2,D:D,0)),"?????????","0")

解决方案

Your sample formula looks like it uses different columns that your narrative describes. I will use what I understand from your description as the columns. See my sample image below for clarity.

Time lookups are prime candidates for 15 digit precision floating point errors. This is due to the base nature of time being a decimal portion of a day and the repeating decimals inherent with displaying 60 minutes and 24 hours. This problem is often magnified by using incremental progression vs datum growth.

If you have a base time in A2 and wish to create a series of 15 minute increments from that to fill down the column, do not use something like the following:

=TIME(0,15,0)+A2     ◄ generates incremental error growth

When you fill that formula down, any error is multiplied as you fill down. Your base display of minutes may not show any obvious errors but if it was displayed as seconds you would likely find that an extra second was added or subtracted every several hundred rows. This is known as Incremental Error Expansion where each successive row adds to the error generated by the previous formula. In short, if you have a hundred of these formulas in a column, any error in the first is multiplied by 100 by the last one.

By always basing the sequential time formula on A2, the error is minimized to a single calculation. Change the formula to something like,

=TIME(0,ROW(1:1)*15,0)+A$2    ◄ datum growth based on minutes added to A2

Even with a datum growth formula you may have some errors on exact matches. Best to wrap the formula in an MROUND function that allows you to round off the returned value to a specified multiple; in this case to the nearest second.

=MROUND(TIME(0, ROW(1:1)*15,0)+A$2, TIME(0, 0, 1))

Fill down to get a progressive column of values with a 15 minute increment. You will likely not see any difference in the displayed values but the raw underlying value that is used for the lookup will be quite different. This principle is know as Datum Incrementing; e.g. all progressive numbers are based on a single calculation against the original.

So with a time value in A2 these are the formulas that should be used.

8:30:00 AM                                           ◄ starting time value in A2
=MROUND(TIME(0, ROW(1:1)*15,0)+A$2, TIME(0, 0, 1))   ◄ in A3 add 15 minutes to A2 (fill down as necessary)
=A2                                                  ◄ starting time value in C2
=MROUND(TIME(0, ROW(1:1)*5,0)+C$2, TIME(0, 0, 1))    ◄ in C3 add 5 minutes to C2 (fill down as necessary)

These will produce the most accurate time increments to be used for an exact match lookup.

Now you can apply a VLOOKUP function or an INDEX function paired with a MATCH function to retrieve the values from column B. An IFERROR function will catch non-matches and output a 0 instead of the #N/A error.

     

My formula in column D uses an INDEX/MATCH pair. D2 is,

=IFERROR(INDEX($B:$B, MATCH(C2,$A:$A, 0)), 0)

There is an alternate formula in E2 using VLOOKUP,

=IFERROR(VLOOKUP(C2,$A:$B, 2, FALSE), 0)

Caveat: There is a maximum number of minutes that can be applied with the TIME function. This is the maximum positive value of a signed integer or 32,767 (about 22¾ days worth of minutes).

这篇关于搜索列A以匹配列C中的数字，如果True返回列B，如果返回错误“0”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！