Excel单元格内公式计算交易完成潜力的技术求助

需求概述 #

需在Excel中通过单元格公式计算60秒内的潜在交易完成率，结果为完全完成交易数量加上所有未完成交易的已执行时长占单笔交易时长的比例之和。

输入参数 #

• x：完成单笔交易所需的秒数（示例中对应单元格A1）
• y：两次交易启动的时间间隔秒数（示例中对应单元格B1）

计算目标与约束 #

• 结果恒为正数，且随x、y增大趋近于0
• 约束1：若y≥60，仅能启动1笔交易
• 约束2：若x>60，所有启动的交易均为部分完成（无完全完成的交易）

示例验证 #

示例1 #

x=30秒，y=10秒
t1：0秒启动，30秒完成 → 1笔完全交易
t2：10秒启动，40秒完成 → 1笔完全交易
t3：20秒启动，50秒完成 → 1笔完全交易
t4：30秒启动，60秒完成 → 1笔完全交易
t5：40秒启动，60秒结束 → (60-40)/30 = 2/3
t6：50秒启动，60秒结束 → (60-50)/30 = 1/3
总完成率：1+1+1+1+2/3+1/3 = 5

示例2 #

x=40秒，y=10秒
t1：0秒启动，40秒完成 → 1笔完全交易
t2：10秒启动，50秒完成 → 1笔完全交易
t3：20秒启动，60秒完成 → 1笔完全交易
t4：30秒启动，60秒结束 → (60-30)/40 = 3/4
t5：40秒启动，60秒结束 → (60-40)/40 = 1/2
t6：50秒启动，60秒结束 → (60-50)/40 = 1/4
总完成率：1+1+1+3/4+1/2+1/4 = 4.5

示例3 #

x=70秒，y=60秒
t1：0秒启动，60秒结束 → (60-0)/70 = 6/7 ≈0.857
无后续交易（y≥60）
总完成率：≈0.857

示例4 #

x=70秒，y=30秒
t1：0秒启动，60秒结束 → 6/7
t2：30秒启动，60秒结束 → 3/7
总完成率：9/7≈1.286

示例5 #

x=10秒，y=60秒
t1：0秒启动，10秒完成 → 1笔完全交易
无后续交易（y≥60）
总完成率：1

解决方案 #

方案1：单单元格整合公式（x=A1，y=B1） #

=IF(B1>=60,MIN(1,60/A1),MAX(0,INT((60-A1)/B1)+1)+SUM(SEQUENCE(INT(60/B1)+1-INT((60-A1)/B1)-1,1,MAX(0,60-INT((60-A1)/B1)*B1),-B1))/A1)

方案2：辅助列分步计算（更易维护） #

步骤1：计算可启动的交易总数（C1）

=IF(B1>=60,1,INT(60/B1)+1)

步骤2：计算完全完成的交易数（D1）

=IF(B1>=60,IF(A1<=60,1,0),MAX(0,INT((60-A1)/B1)+1))

步骤3：计算部分完成交易的总比例（E1）

=IF(B1>=60,IF(A1>60,60/A1,0),SUM(SEQUENCE(C1-D1,1,60-(D1*B1),-B1))/A1)

步骤4：总交易完成率（F1）

=D1+E1

思路解析 #

1. 可启动交易数：当y≥60时仅启动1笔；否则按INT(60/y)+1计算（从0秒开始，每y秒启动一次，最后一次启动时间≤60秒）
2. 完全完成交易数：交易结束时间≤60秒，即启动时间+x≤60 → 启动时间≤60-x。当y≥60时，仅x≤60时完成1笔；否则计算满足k*y ≤60-x的最大k值，加1得到完全交易数
3. 部分完成交易总比例：对启动时间>60-x的交易，每笔已执行时长为60-启动时间，求和后除以x得到总比例

内容的提问来源于stack exchange，提问作者AWaddington