内存数组练习题

我走我流

solextrade 发表于 2019-10-20 15:30
不好意思了各位，发习题贴还是缺乏经验呀。
就以这个附件为准吧。

=SUM(IFNA(VLOOKUP(T(IF({1},"洲*"&OFFSET(E$13,MAX(ROW(A1)-1)*4,,3,2)&"市*")),IF({1,0},SUBSTITUTE(MID(A$2:A$13,SEARCH("洲"&T(INDIRECT(TEXT(SMALL(IF($E$2:$H$4<>"",COLUMN($E:$H)+ROW($2:$4)/1%),ROW($1:$12)),"R0C00"),))&"?市*",A$2:A$13),99),"市","市市",1),B$2:B$13),2,),))

有点长了。。。也是钻了漏洞，，，，

sbdk007

火车接着开，用“洲”来代替省不少字符
=SUM(ISNUMBER(FIND(INDEX("洲"&T(OFFSET(E$1,ROW($4:$15)/4,MOD(ROW($1:$12),4)))&T(OFFSET(D$13,ROW(A1)*4-{2,2,3,3,4,4},{1,2,1,2,1,2})),N(IF({1},COLUMN(F:BY)/6)),N(IF({1},MOD(COLUMN(A:BT)-1,6)+1))),A$2:A$13))*B$2:B$13)

dipperrrr

参考下楼上各位老师，修正下：

1. =SUM(SUMIF(A:A,"*洲"&T(OFFSET(E$1,ROW($4:$15)/4,MOD(ROW($1:$12),4)))&T(OFFSET(D$12,ROW(A1)*4-{1,1,2,2,3,3},{1,2,1,2,1,2}))&"*",B:B))

复制代码

sbdk007

dipperrrr 发表于 2019-10-20 22:22
参考下楼上各位老师，修正下：

老师精简的好

1055751654

solextrade 发表于 2019-10-20 19:23
这样都能找出规律？佩服果仁老师。
我自己瞎填的那些小红字，自己都不知道什么规律。目的其实是为了 ...

再改改，短了一点=SUM(IFERROR(N(INDIRECT("b"&MATCH(TRANSPOSE("*"&TRIM(MID(SUBSTITUTE(SUBSTITUTE(0&"/"&F$6,"/","/"&E$6)&"/0"&SUBSTITUTE("/"&F$7,"/","/"&E$7)&"/0"&SUBSTITUTE("/"&F$8,"/","/"&E$8)&"/0"&SUBSTITUTE("/"&F$9,"/","/"&E$9)&"/","/",REPT(" ",99)),ROW(INDIRECT("1:"&LEN(PHONETIC(F$6:H$9))-LEN(SUBSTITUTE(PHONETIC(F$6:H$9),"/",))+8))*99-98,99))&TRANSPOSE(CHAR(SMALL(CODE(T(IF({1},OFFSET(E$12:F$12,ROW(A1)*4-3,,3)))),ROW($1:$6)))))&"市*",A:A,))),))谁来跟我比长，秒杀你们

micch

1. =SUM(SUMIF(A:A,"*"&TRIM(MID(E$6:E$9&SUBSTITUTE(F$6:F$9&"/1/1/1","/",REPT(" ",33)&E$6:E$9),INT(COLUMN(F:AI)/6)*33-32,33))&T(OFFSET(E$13,ROW(A1)*4-4+MID(REPT(0&12,10),COLUMN(A:AD),1),MID(REPT(0&1,15),COLUMN(A:AD),1)))&"*",B:B))

复制代码

这个意思是建立内存数组，笨办法，直接地区国家城市连接吧，30个可能组合

micch

1. =SUM(SUMIF(A:A,"*洲"&T(OFFSET(E$1,ROW($4:$15)/4,MOD(ROW($4:$15),4)))&T(OFFSET(E$13,ROW(A1)*4-{4,4,3,3,2,2},{0,1,0,1,0,1}))&"*",B:B))

复制代码

直接offset生成？

sssnb

别人都在做题了，我还在审题，最后还是没看懂

1055751654

大神们脑袋瓜子真是聪明，我也要学，看上去顺眼多了=SUM(IFERROR(N(INDIRECT("b"&MATCH("*洲"&T(INDIRECT(TEXT(SMALL(ROW($2:$4)/1%+COLUMN($E:$H),ROW($1:$12)),"r0c00"),))&TRANSPOSE(CHAR(SMALL(CODE(T(IF({1},OFFSET(E$12:F$12,ROW(A1)*4-3,,3)))),ROW($1:$6))))&"市*",A:A,))),))

solextrade

首先感谢众位老师参与此帖！

        此帖本意是让大家用内存数组来解题，对应帖子名称。
        我最初的思路就是SUMIF(判断区域，条件，求和区域），在SUMIF的二参上面做文章，也就是构建内存数组：大洲+国家+城市，形成一个12行12列的内存数组，来作为SUMIF的二参。
        但由于经验不足，致使数据内容有多处漏洞。所以帖子刚放上来没一会，那些漏洞就被我走我流、轩檐和jisijie630三位老师的敏锐目光发现，秒杀！于是修改1次，重新上传，但依然还有漏洞，又被jisijie630和杨老师发现，秒杀！

       憋屈呀！就好比两军对垒，两将PK，一方还没来得及通报姓名，就被斩于马下。于是重整旗鼓，再修改一次上传。终于把漏洞堵死了（自以为是），一切似乎步入正轨。

        以下按回帖顺序：

果仁老师目光如电，竟然从杂乱无章的数据中再次找到规律，抽丝剥茧，写出公式，厉害！388字符
=SUM(IFERROR(N(INDIRECT("b"&FIND(TRANSPOSE(CHAR(SMALL(CODE(T(IF({1},OFFSET(E$12:F$12,ROW(A1)*4-3,,3)))),ROW($1:$6)))),MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A$2:A$13,"洲C",),"洲K",),"洲A",),FIND("洲",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A$2:A$13,"洲C",),"洲K",),"洲A",)),ABS(MMULT(IFERROR(FIND({"洲","市"},SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A$2:A$13,"洲C",),"洲K",),"洲A",)),),{-1;1}))+3))^0*ROW($2:$13))),))

dipperrrr老师最初没用SUMIF，而是用VLOOKUP，至今我的电脑还是显示不正确（跟轩檐老师同样情况）170字符
=SUM(IFNA(VLOOKUP("*洲"&INDEX(E$2:H$4,INT((MOD(ROW($1:$12)-1,12)+4)/4),MOD(ROW($4:$15),4)+1)&INDEX(OFFSET(E$9:F$11,ROW(A1)*4,,),{1,1,2,2,3,3},{1,2,1,2,1,2})&"*",A:B,2,),))  （其中最关键的是"*洲"，这个字节在后面起到重要作用）

满版思路奇特，这个SUBSTITUTE用法巧妙，至今未能消化。公式正确！（稍有投机之嫌:）。196字符
=SUM(SUMIF(A:A,"*"&TRIM(MID(SUBSTITUTE(CONCAT(SUBSTITUTE("/"&F$6:F$9,"/",0&E$6:E$9)),0,REPT(" ",99)),COLUMN(A:P)*99,99))&CHAR(SMALL(CODE(OFFSET(E$13:F$15,SUM(ROW(A1)*4-4),)),ROW($1:$6)))&"*",B:B))

我走我流老师的VLOOKUP用的神乎其神！公式正确，259字符（我改了2次数据，还是没把陈哥逼到SUMIF上  :）
=SUM(IFNA(VLOOKUP(T(IF({1},"洲*"&OFFSET(E$13,MAX(ROW(A1)-1)*4,,3,2)&"市*")),IF({1,0},SUBSTITUTE(MID(A$2:A$13,SEARCH("洲"&T(INDIRECT(TEXT(SMALL(IF($E$2:$H$4<>"",COLUMN($E:$H)+ROW($2:$4)/1%),ROW($1:$12)),"R0C00"),))&"?市*",A$2:A$13),99),"市","市市",1),B$2:B$13),2,),))

杨老师用了这个规律"洲"，不用再去[大洲区]调取数据，使得公式大大缩短，正确，213字符。
=SUM(ISNUMBER(FIND(INDEX("洲"&T(OFFSET(E$1,ROW($4:$15)/4,MOD(ROW($1:$12),4)))&T(OFFSET(D$13,ROW(A1)*4-{2,2,3,3,4,4},{1,2,1,2,1,2})),N(IF({1},COLUMN(F:BY)/6)),N(IF({1},MOD(COLUMN(A:BT)-1,6)+1))),A$2:A$13))*B$2:B$13)

dipperrrr老师终于把公式改成我这里通用的了，且换用SUMIF求值，公式最短，结果正确！131字符
=SUM(SUMIF(A:A,"*洲"&T(OFFSET(E$1,ROW($4:$15)/4,MOD(ROW($1:$12),4)))&T(OFFSET(D$12,ROW(A1)*4-{1,1,2,2,3,3},{1,2,1,2,1,2}))&"*",B:B))

M老师第一个公式正确，225字符。第二个公式并列最短！131字符（1楼把M老师划进不能先贴答案的队伍是对的）
公式1 =SUM(SUMIF(A:A,"*"&TRIM(MID(E$6:E$9&SUBSTITUTE(F$6:F$9&"/1/1/1","/",REPT(" ",33)&E$6:E$9),INT(COLUMN(F:AI)/6)*33-32,33))&T(OFFSET(E$13,ROW(A1)*4-4+MID(REPT(0&12,10),COLUMN(A:AD),1),MID(REPT(0&1,15),COLUMN(A:AD),1)))&"*",B:B))

公式2 =SUM(SUMIF(A:A,"*洲"&T(OFFSET(E$1,ROW($4:$15)/4,MOD(ROW($4:$15),4)))&T(OFFSET(E$13,ROW(A1)*4-{4,4,3,3,2,2},{0,1,0,1,0,1}))&"*",B:B))

果仁老师卷土重来，INDIRECT用的好，公式正确！（也有投机之嫌  :）214字符
=SUM(IFERROR(N(INDIRECT("b"&MATCH("*洲"&T(INDIRECT(TEXT(SMALL(ROW($2:$4)/1%+COLUMN($E:$H),ROW($1:$12)),"r0c00"),))&TRANSPOSE(CHAR(SMALL(CODE(T(IF({1},OFFSET(E$12:F$12,ROW(A1)*4-3,,3)))),ROW($1:$6))))&"市*",A:A,))),))

惭愧的很，我的原公式234字符，不过是按部就班来的，没有取巧。如果也用"洲"字定位，可以缩到147字符，不改了吧。
=SUM(SUMIF(A:A,"*"&T(OFFSET(E$5,MATCH("*"&T(OFFSET(E$1,ROW($4:$15)/4,MOD(ROW($4:$15),4)))&"*",F$6:F$9,),))&T(OFFSET(E$1,ROW($4:$15)/4,MOD(ROW($4:$15),4)))&T(OFFSET(INDEX(D:D,MATCH(J14,D:D)),MOD(COLUMN(C:H),3),COLUMN(C:H)/3))&"*",B:B))

结论：
各位老师都比我厉害，我发帖竟然敢用“讨论”二字，下次坚决改为“求助”；
        如果下次，编好帖子后，要封存几天酝酿一下，先想想有没有什么漏洞再发；
        如果下次，我就这样写数据：中东伊拉克巴格达市；北欧丹麦哥本哈根广场......