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

太阳之子

火车票余票查询

题目说明：

  1.北京至广州的一列高铁，全车共1028座。列车不出售站票。

  2.假设列车共6个停靠站点（含起始站和终点站，如F2:F7)

  3.B列是现在各区间已出售的车票数量，求各区间的剩余可出售车票数

  4.举例说明：

    根据A、B列数据，可知列车在北京站出发时有477名乘客，还有551个空座，

    到达石家庄后，有120名乘客下车，但有373名乘客上车，车上乘客477-120+373=730，有空座298个

    到达郑州后有77+45人下车，有78+100+130人上车，730-77-45+78+100+130=916，剩余空座112个   

站点	北京	石家庄	郑州	武汉	长沙
空座	551	298	112	61	284

    所以:北京-石家庄，可以出售的票为551张，

    北京-郑州区间的余票数为298张，而不是551张，因为石家庄站的空座只有298个。

    同样，北京-长沙的余票只有61张，因为武汉站的空座只有61张。

答题要求：

  1.纯函数题，除在C列输入公式外，不能有其他任何操作。

  2.D列不可引用，公式中不可使用不合理的全列、全行引用如ROW(A:A)、COLUMN(1:1)

  3.公式需适用其他车次更多站点的余票数计算。

评分规则：

  1.公式长度（含等号）小于220字符的正确答案评技术1分；

  2.公式长度（含等号）小于180字符的正确答案评技术2分；

竞赛日期：2019-1-4 至 2019-2-3

delete_007

C2单元格数组公式下拉，178字符

1. =MIN(1028+(ROW($2:$6)<MATCH("*"&RIGHT(A2,2),F:F,))*(ROW($3:$7)>MATCH(LEFT(A2,2)&"*",F:F,))*MMULT(N(ROW($1:$5)>{0,1,2,3,4}),SUMIF(A:A,"*"&F$2:F$6,B:B)-SUMIF(A:A,F$2:F$6&"*",B:B)))

复制代码

象山海鲜

1. =3076-MAX(MOD(MMULT(N(ROW($2:7)>COLUMN(A:E)),MMULT(SUMIF(A:A,{"","*"}&F$2:F$6&"*",B:B),{2;-1})+ISNUMBER(FIND(F$2:F$6,A2))*2^11),4^6))

复制代码

mmlzh

跟模拟结果一致，不知道公式是否正确，最好能有个类似124期的VBA来判断
207=MIN(IF(MMULT({-1,1},N(COLUMN(B:Z)<MATCH(TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",99)),{1;99},98)),F:F,))),1028-MMULT(COLUMN(A:Y)^0,MMULT(SUMIF(A:A,"*"&F$2:F$26&{"-*",""},B:B),{1;-1})*(ROW($1:$25)<COLUMN(B:Z)))))

=1028-MAX(MMULT(MMULT(N(ROW($1:8)<MATCH({1,9},FIND("-"&F$2:F$7&"-","-"&A2&"-"))),{-1;1})*MMULT(-(ROW($2:9)>COLUMN(A:F)),SUMIF(A:A,{"","*-"}&F$2:F$7&{"-*",""},B:B)),{-1;1}))

=1028-MAX(MMULT(MMULT(N(ROW($1:8)<MATCH({1,9},FIND(F$2:F$7,A2))),{-1;1})*MMULT(-(ROW($2:9)>COLUMN(A:F)),SUMIF(A:A,"*"&F$2:F$7&{"*",""},B:B)),{-1;2}))

丢丢表格

抢一张 134 次的火车票来

1. =MIN(ABS(11028+MMULT(N(ROW($2:$7)>COLUMN(A:F)),SUMIF(A:A,"*"&F$2:F$7,B:B)-SUMIF(A:A,F$2:F$7&"*",B:B)-ISNUMBER(FIND(F$2:F$7,A2))/1%%)))

复制代码

LSL1128xgw

=MIN(IF((ROW($3:$8)>MATCH(,0/FIND(F:F,A2),))*(ROW($2:$7)<MATCH(,-FIND(F$1:F$7,A2))),1028-MMULT(N(COLUMN(A:F)<ROW($2:$7)),SUMIF(A:A,F$2:F$7&"*",B:B)-SUMIF(A:A,"*"&F$2:F$7,B:B))))  先交个长的=MIN(1028-MMULT(MMULT(N(COLUMN(A:F)<ROW(INDIRECT(MATCH(,0/FIND(F:F,A2),)&":"&MATCH(,-FIND(F$2:F$7,A2))))),SUMIF(A:A,"*"&F$2:F$7&{"*",""},B:B)),{1;-2}))

江苏永能朱兴华

=1028-MAX(MMULT(N(ROW($2:$7)>COLUMN(A:F)),SUMIF(A:A,F$2:F$7&"*",B:B)-SUMIF(A:A,"*"&F$2:F$7,B:B))*(ROW($2:$7)>MATCH(,0/FIND(F$2:F$7,A2),))*(ROW($1:$6)<MATCH(1,0/FIND(F$2:F$7,A2))))

=1028-MAX(MMULT(N(ROW($2:$7)>COLUMN(A:F)),SUMIF(A:A,F$2:F$7&"*",B:B)-SUMIF(A:A,"*"&F$2:F$7,B:B))*(ROW($2:$7)>MATCH(,0/FIND(F$2:F$7,A2),))*(ROW($1:$6)<MATCH(9,FIND(F$2:F$7,A2))))

=1028-MAX(MMULT(N(ROW($2:$7)>COLUMN(A:F)),SUMIF(A:A,F$2:F$7&"*",B:B)-SUMIF(A:A,"*"&F$2:F$7,B:B))*ISNUMBER(-(LOOKUP(ROW($1:$6),MATCH({1;9},FIND(F$2:F$7,A2))-{0;0.1})&0.1)))

=1028-MAX(MMULT((ROW($2:$7)>COLUMN(A:F))*ISNUMBER(-(LOOKUP(ROW($1:$6),MATCH({1;9},FIND(F$2:F$7,A2))-{0;1}%)&1%)),SUMIF(A:A,F$2:F$7&"*",B:B)-SUMIF(A:A,"*"&F$2:F$7,B:B)))

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

sayhi95

例行开火车，哈哈~

1. =MIN(INDEX(MMULT(TRANSPOSE(MMULT(SUMIF(A$2:A$16,MID("*"&F$2:F$7&"*",{1,2},IF({1,0},LEN("*"&F$2:F$7&"*")-1,9)),B$2:B$16),{1;-1}))*(ROW($2:$7)>COLUMN(A:F)),Z1:Z6+1)+1028,N(IF(1,ROW(INDIRECT(MATCH(LEFT(A2,FIND("-",A2)-1),F$2:F$7,)&":"&MATCH(MID(A2,FIND("-",A2)+1,9),F$2:F$7,)-1))))))

复制代码

快接近了

1. =MIN(INDEX(MMULT(TRANSPOSE(SUMIF(A$2:A$16,F$2:F$7&"*",B$2)-SUMIF(A$2:A$16,"*"&F$2:F$7,B$2))*(ROW($2:$7)>COLUMN(A:F)),Z1:Z6-1)+1028,N(IF(1,ROW(INDIRECT(MATCH(LEFT(A2,FIND("-",A2)-1),F$2:F$7,)&":"&MATCH(MID(A2,FIND("-",A2)+1,9),F$2:F$7,)-1))))))

复制代码

整列引用了都还差点，汗

1. =MIN(INDEX(MMULT(TRANSPOSE(SUMIF(A:A,F$2:F$7&"*",B:B)-SUMIF(A:A,"*"&F$2:F$7,B:B))*(ROW($2:$7)>COLUMN(A:F)),Z1:Z6-1)+1028,N(IF(1,ROW(INDIRECT(MATCH(LEFT(A2,FIND("-",A2)-1),F:F,)-1&":"&MATCH(MID(A2,FIND("-",A2)+1,9),F:F,)-2))))))

复制代码

太艰难了

1. =1028-MAX(MMULT(N(ROW($2:$7)>COLUMN(A:F)),SUMIF(A:A,F$2:F$7&"*",B:B)-SUMIF(A:A,"*"&F$2:F$7,B:B))*(MMULT(N(ROW($2:$7)>COLUMN(A:F)),COUNTIF(A2,"*"&F$2:F$7&"*"))=1))

复制代码

我走我流

=MIN(IFNA(LOOKUP(IF((ROW($1:$6)>=LOOKUP(1,-FIND(LEFT(A2,2),F$2:F$7),ROW($1:$6)))*((ROW($1:$6)<LOOKUP(1,-FIND(RIGHT(A2,2),F$2:F$7),ROW($1:$6)))),ROW($1:$6)),COLUMN($A:$F),1028-MMULT(SUMIFS(B:B,A:A,T(OFFSET(F$2:F$6,ROW($1:$6)-1,,-COLUMN($A:$F)))&"*"),ROW($1:$6)^0)+MMULT(SUMIFS(B:B,A:A,"*"&T(OFFSET(F$2:F$6,ROW($1:$6)-1,,-COLUMN($A:$F)))),ROW($1:$6)^0)),9^9))
357字太长了，                 精简下，=MIN(LOOKUP(ROW(INDIRECT(LOOKUP(1,-FIND(LEFT(A2,2),F$2:F7),ROW($1:6))&":"&LOOKUP(1,-FIND(RIGHT(A2,2),F$2:F7),ROW($1:6))-1)),COLUMN(A:F),1028-MMULT(SUMIFS(B:B,A:A,T(OFFSET(F$1,ROW($1:$6),,-COLUMN(A:F)))&"*")-SUMIFS(B:B,A:A,"*"&T(OFFSET(F$1,ROW($1:$6),,-COLUMN(A:F)))),ROW(1:6)^0)))   280个字，还差的多=MIN(LOOKUP(ROW(INDIRECT(TEXTJOIN(":",,LARGE(ISNUMBER(FIND(F$2:F$7,A2))*ROW($1:$6),{2,1})-{0,1}))),COLUMN(A:F),1028+MMULT(ISERR(-MID(BASE(2^ROW($1:$6)-1,2),COLUMN(A:F),1))-1,SUMIF(A:A,F$2:F$7&"*",B:B)-SUMIF(A:A,"*"&F$2:F$7,B:B))))   228

=MIN(LOOKUP(ROW(INDIRECT(TEXTJOIN(":",,LARGE(ISNUMBER(FIND(F$2:F$7,A2))*ROW($1:$6),{2,1})-{0,1}))),COLUMN(A:F),1028+MMULT(-MID(LEFT(1/9,ROW($3:$8))*10^6,COLUMN(A:F),1),SUMIF(A:A,F$2:F$7&"*",B:B)-SUMIF(A:A,"*"&F$2:F$7,B:B)))) 224字。。。。

尛ぃ謉

``

1. =1028-MAX(MMULT(N(MMULT(N(ROW($2:$7)>COLUMN(A:F)),N(FIND(F$2:F$7,TRANSPOSE(A$2:A$16)&F$2:F$7)<6))=1),B$2:B$16)*(MMULT(N(ROW($2:$7)>COLUMN(A:F)),N(FIND(F$2:F$7,A2&F$2:F$7)<6))=1))

复制代码

合并``

1. =1028-MAX(MMULT(N(MMULT(N(ROW($2:$7)>COLUMN(A:F)),(FIND(F$2:F$7,TRANSPOSE(A$2:A$16)&F$2:F$7)<6)+(FIND(F$2:F$7,A2&F$2:F$7)<6)*2)=3),B$2:B$16))

复制代码

再合并，字符并没少``

1. =1028-MAX(MMULT(N(MMULT(N(ROW($2:$7)>COLUMN(A:F)),MMULT(1-ISERR(FIND(F$2:F$7,TRANSPOSE(A$2:A$16))),(A$2:A$16=A2)*2+MUNIT(15)))=3),B$2:B$16))

复制代码

再减``

1. =1028-MAX(MMULT(N(MMULT(N(ROW($2:$7)>COLUMN(A:F)),3-ISERR(FIND(F$2:F$7,TRANSPOSE(A$2:A$16)))-ISERR(FIND(F$2:F$7,A2))*2)=3),B$2:B$16))

复制代码

````

1. =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%%))

复制代码