[第125期]火车票余票查询[开贴待总结]

xiaoh99

我也凑个热闹。
我已想好了算法：
1.将每个运行区间分为2个站点
2.计算每个站点的空座数
3.考察每个区间的经过站点
4.找出区间中最小的站点空位数
具体的公式等我验证通过后再传上。抱歉先占个位!
算法虽然有，但要实现也不容易。再让我考虑考虑。


补充内容 (2019-1-13 22:21):
要做这么多的事，只用一个公式来完成，真不容易

补充内容 (2019-2-3 22:32):
算出来了，但组合在一起有错，长度也超了，不好意思呀。时间可能不够了

补充内容 (2019-2-3 23:37):
看来我对内存数组还是不够了解，前面过于自信了

265094

=MIN(IF(MMULT({-1,1},-(COLUMN(B:Z)<LARGE(ISNUMBER(FIND(F$1:F$7,A2))*ROW($1:$7),{2;1}))),1028-MMULT(COLUMN(A:Y)^0,(SUMIF(A:A,F$2:F$26&"*",B:B)-SUMIF(A:A,"*"&F$2:F$26,B:B))*(ROW($1:$25)<COLUMN(B:Z)))))
=MIN(IF(MMULT({-1,1},N(COLUMN(A:H)<MATCH({1;9},FIND(F$2:F$7,A2)))),1028-MMULT(S1:Z1+1,MMULT(SUMIF(A:A,"*"&F$2:F$9&{"-*",""},B:B),{1;-1})*(ROW($1:$8)<COLUMN(B:I)))))

补充内容 (2019-1-13 13:26):
=1028-MAX(MMULT((ROW($1:$8)<MATCH({1,9},FIND(F$2:F$7,A2)))*MMULT(-(ROW($2:$9)>COLUMN(A:H)),MMULT(SUMIF(A:A,"*"&F$2:F$9&{"-*",""},B:B),{1;-1})),{1;-1}))

安然祥和

=MIN(IF(MMULT((ROW($1:$6)>=MATCH(TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",9)),{1,9},9)),F$2:F$7,))*1,{1;1})=1,1028-MMULT(n(ROW($1:$6)>=COLUMN(A:F)),SUMIF(A$2:A$16,F$2:F$7&"*",B$2:B$16)-SUMIF(A$2:A$16,"*"&F$2:F$7,B$2:B$16))))

PQ小学生

没有搞清 到广州的余票与武汉的空座有何关系

hno3861

烧脑，超过七层括号可以吗？174，希望没啥别的错误。
=1028-MOD(MAX(MMULT(2001^(COLUMN(B:P)=ROW()),(RIGHT(INT(MMULT(MATCH(MID(SUBSTITUTE(A$2:A$16,"-","*"),{4,1},3),F$2:F$7,),{100;1})-COLUMN(A:F)*101.01),2)-80>0)*B$2:B$16)),2000)
=1028-RIGHT(MAX(MMULT(10001^(COLUMN(B:P)=ROW()),(RIGHT(INT(MMULT(MATCH(MID(SUBSTITUTE(A$2:A$16,"-","*"),{4,1},3),F$2:F$7,),{100;1})-COLUMN(A:F)*101.01),2)-80>0)*B$2:B$16)),4)

1月15号10:16，思路稍微变了下，好像更合理了。160字符。

1. =1028-RIGHT(MAX(MMULT(10001^(COLUMN(B:P)=ROW()),(MID(MMULT(1-ISERR(FIND(TRANSPOSE(F$2:F$7),A$2:A$16)),REPT(1,ROW($1:$6))%%%),COLUMN(C:H),1)="1")*B$2:B$16)),4)

复制代码

补充内容 (2019-2-20 14:00):
看了下，和10楼的公式思路一样，矩阵用法差距好大，厉害。把尛ぃ謉公式抄一遍：
=3028-MAX(MMULT(N(MMULT(N(ROW($2:$7)>COLUMN(A:F)),1-ISERR(FIND(F$2:F$7,TRANSPOSE(A$2:A$16))))=1),B$2:B$16+(A$2:A$16=A2)/5%%))

梦幻小丑

1. =MIN(1028-(MMULT(N(LARGE(ISNUMBER(FIND(F$2:F$7,A2))*ROW($2:$7),{2,1})>ROW($1:$6)),{1;1})=1)*MMULT(N(ROW($2:$7)>COLUMN(A:F)),MMULT(SUMIF(A:A,"*"&F$1:F$6&{"-*",""},B:B),{1;-1})))

复制代码

jinlong506

1. =MIN(1028-MMULT((TRANSPOSE(MATCH(LEFT(A$2:A$16,FIND("-",A$2:A$16)-1),F$2:F$7,))<=ROW(INDIRECT(MATCH(1,FIND(F$2:F$6,A2))&":"&MATCH(0,-FIND(F$3:F$7,A2)))))-(TRANSPOSE(MATCH(MID(A$2:A$16,FIND("-",A$2:A$16)+1,9),F$2:F$7,))<=ROW(INDIRECT(MATCH(1,FIND(F$2:F$7,A2))&":"&MATCH(0,-FIND(F$3:F$7,A2))))),B$2:B$16))

复制代码

头疼  303个 超太多了

41126238

有点长，再想想。

1. =MIN(1028-MMULT(U1:Z1+1,(ROW($1:$6)<=TRANSPOSE(ROW(INDIRECT(MATCH(LEFT(A2,FIND("-",A2)-1),F:F,)-1&":"&MATCH(MID(A2,FIND("-",A2)+1,9),F:F,)-2))))*(SUMIF(A:A,$F$2:$F$7&"-*",B:B)-SUMIF(A:A,"*-"&$F$2:$F$7,B:B))))

复制代码

micch

1. =MIN(IF(MMULT(N(ROW($1:$6)<COLUMN(B:G)),1-ISERR(FIND(F$2:F$7,A2)))=1,1028+MMULT(N(ROW($1:$6)<COLUMN(B:G)),MMULT(SUMIF(A:A,{"","*"}&F$2:F$7&{"*",""},B:B),{1;-1}))))

复制代码

C2
不会验证，不知道对错，先写一个，自己再琢磨琢磨吧

想不出别的办法，只能原公式简化

1. =MIN(ABS(MMULT(N(ROW($1:$6)<COLUMN(B:G)),ISNUMBER(FIND(F$2:F$7,A2))*9^9+MMULT(SUMIF(A:A,{"","*-"}&F$2:F$7&"*",B:B),{1;-1}))-9^9+1028))

复制代码

再减就不知道对错了，好像得以车上人比空座多前提

1. =MIN(ABS(MMULT(N(ROW($1:$6)<COLUMN(B:G)),ISNUMBER(FIND(F$2:F$7,A2))*1028+MMULT(SUMIF(A:A,{"","*-"}&F$2:F$7&"*",B:B),{1;-1}))))

复制代码

会飞的鱼kk

公式1：长度300字符。
=MIN(INDEX(1028-MMULT(1*(ROW($1:$5)>=COLUMN(A:E)),SUMIF(A$2:A$16,G$2:G$6&"-*",B$2:B$16))+MMULT(1*(ROW($1:$5)>=COLUMN(A:E)),SUMIF(A$2:A$16,"*-"&G$2:G$6,B$2:B$16)),N(IF(1,(ROW($1:$5)>=MATCH(LEFT(A2,FIND("-",A2)-1),$G$2:$G$7,))*(ROW($1:$5)<=MATCH(MID(A2,FIND("-",A2)+1,9),$G$2:$G$7,)-1)*ROW($1:$5)))))

公式2：长度220字符。
=MIN(1028-MMULT((ROW($1:$5)>=COLUMN(A:E))*(ROW($2:$6)>MATCH(LEFT(A2,FIND("-",A2)-1),G$2:G7,))*(ROW($1:$5)<MATCH(MID(A2,FIND("-",A2)+1,9),G$2:G7,)),SUMIF(A$2:A16,G$2:G$6&"-*",B$2:B16)-SUMIF(A$2:A16,"*-"&G$2:G$6,B$2:B16)))