问题描述
我有这个文件
I have this file
Date | Time | Table | Checkin Time | Bill Number ..| .. |
7/1/2018 **18:03:48** **6** **18:03:48** **4332**
7/1/2018 18:11:23 **6** **18:03:48** **4332**
7/1/2018 18:23:10 **6** **18:03:48** **4332**
7/1/2018 19:24:11 8 19:24:11 4333
7/1/2018 19:56:17 8 19:24:11 4333
7/1/2018 20:31:11 *6 20:31:11 4332*
我需要计算签到时间"，这是表号和账单号的第一时间.
例如，使用table number 6
在18:03:48处查看一个客户签到他的Bill number是4332.
因此对于table number 6和Bill number 4332 chekin time将是第一个time
这是18:03:48.
所以我应用了公式:
I need to calculate Checkin Time which is 1st time of table number and bill number.
For example See one customer checkin at 18:03:48 with table number 6
His Bill number is 4332 .
So for table number 6 and Bill number 4332 chekin time will be 1st time
which is 18:03:48.
So I applied the formula:
=IF(AND(E3=E2,C3=C2,A3=A2),D2,MIN(OFFSET(E3,0,-3,MATCH(0,(E3:E$35470=E3)*(C3:C$35470=C3)*(A3:A$35470=A3),0)-1,1)))
但是值在6th row处更改.
它给20:31:11作为checkin time.
它应该是18:03:48，因为6th row table number and bill number与第一个相同.
那么，无论哪一行，我如何才能根据具有相同bill and table number的1st time来计算checkin time.
这是我已上传的excel 文件到Google云端硬盘.
这是屏幕截图.
But the value changes at 6th row .
It's giving 20:31:11 as checkin time .
It should be 18:03:48 because the 6th row table number and bill number is same as 1st one.
So How do I make it possible, to calculate the checkin time as per 1st time who have same bill and table number , no matter which row .
Here is the excel file I have uploaded to Google drive.
Here is the screenshot.
-由@jeeped工作公式，但是由于日志为30天
数据也不断重复前一天.
可以通过提供一些unique日期值来做到这一点吗?
- Formula by @jeeped work, But as the log is of 30 days
The data keeps repeating the other day too.
Can it be done by giving some unique date value ?
推荐答案
@Jeeped的答案很明确，这是对公式的一种调整，该公式可在具有相同表号和账单号的多天内工作:
@Jeeped's answer is spot on, here is an adjustment for the formula to work over multiple days that have the same table numbers and bill numbers:
=AGGREGATE(15, 7, ((A$2:A$13)+(B$2:B$13))/((C$2:C$13=C2)*(E$2:E$13=E2)*(A$2:A$13=A2)), 1)
我所做的只是添加了这个额外的条件参数，该参数检查日期"是否相同:
All I did was add this extra conditional argument, that checks if the "Date" is the same:
*(A$2:A$13=A2)
这篇关于获取第一对的价值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！