查找End＆amp;开始日期Double vlookup excel
我有成千上万的数据单元格由
uid，日期，质量，开始 /结束，浮点天，从开始< / strong>的天数，
我有一个简单的版本的表格，以下内容：
每个新数据条目都以行输入。 每个UID的数据（唯一ID）在其浮子寿命的长度上在非一致的日子中获取。 （例如：木材漂浮物和我在不同的日子里占用质量，直到下沉为止）。
我想通过摄入下沉日期并通过ID的第一个引用来识别“浮动日”。
#simple:
When the row has "1" in the D column, Find the ID on that row, then find that ID when that row has "0".
Take the date from that row and subtract by sink date to give me float days
#complex:
=IF(D16="1", (vlookup(A16 & "0",$A$1:$F$999,2,0)-B16, "")
如果可能的话，我想使用公式的框架来计算每个数据输入的日期，从开始日期开始，如下所示：
#simple: in row E
Take the date from current row (row 10 for example), find that ID's start date,
subtract from one another to give me days from time=0
#complex:
=B10-vlookup(A10 & "0",$A$1:$F$999,B,0)
我是否对我是否错误地键入公式感到困惑，或者是否有另一个更容易的做到这一点的方法。 谢谢你！
I have thousands of cells of data formated with the headers of
UID, Date, Mass, Start / End, Float Days, Days from Start
I have a simple version of what the sheet looks like below:
Each new data entry is entered as a row.
The data for each UID ( unique ID) is taken on non-consistent days over the length of its float life. (ex: wood floats and I take its mass on different days until it sinks).
I want to Identify a "Float Days" by taking the Sink date and subtracting it by the first reference of the ID.
#simple:
When the row has "1" in the D column, Find the ID on that row, then find that ID when that row has "0".
Take the date from that row and subtract by sink date to give me float days
#complex:
=IF(D16="1", (vlookup(A16 & "0",$A$1:$F$999,2,0)-B16, "")
If possible, I would like to use the framework of the formula to also calculate each data-inputs date in days from its Start Date as shown below:
#simple: in row E
Take the date from current row (row 10 for example), find that ID's start date,
subtract from one another to give me days from time=0
#complex:
=B10-vlookup(A10 & "0",$A$1:$F$999,B,0)
I am confused as to whether I am typing the formula incorrectly or if there is another and easier way to do this.
Thank you!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
Sumfis是这里更好的选择。在单元格E2中复制：
如果您希望同一天/结束对等于1而不是0，则只需在SUMIF的末端添加+1即可。
SUMFIS is the better choice here. In cell E2 and copied down:
If you want a same day start/end pair to be equal to 1 instead of 0, just add a +1 to the end of the SUMIFS.