2010版本EXCEL数组公式求助

jiaodaoniu · 发表于 2024-11-24 10:54

各位老师帮忙看下，在低版本的EXCEL中，这种数组公式计算的结果如何体现在一个单元格内？

miaojohn45 · 发表于 2024-11-24 11:13

="{"&N(K6>50)&","&N(L6>50)&","&N(M6>50)&","&N(N6>50)&","&N(O6>50)&","&N(P6>50)&","&N(Q6>50)&"}"

hcm19522 · 发表于 2024-11-24 18:11

本帖最后由 hcm19522 于 2024-11-25 09:48 编辑

T6=LEFT(SUBSTITUTE(SUBSTITUTE(MID(SUMPRODUCT(((K6:Q6>50)+8)*100^-COLUMN(A:G)),3,99),"8",","),"09","1,"),13)

右拉 7格第一格是結果=IF(COLUMN(A1)=1,"",",")&N(K6>50)&U6

jiaodaoniu · 发表于 2024-11-24 22:18

hcm19522 发表于 2024-11-24 18:11
T6=LEFT(SUBSTITUTE(SUBSTITUTE(MID(SUMPRODUCT(((K6:Q6>50)+8)*100^-COLUMN(A:G)),3,99),"8",","),"09","1 ...

老师你的这个解法真是太骚了，佩服佩服。

jivy1212 · 发表于 2024-11-25 09:59

=MID(TEXT(MID(NPV(9,N(K6:Q6>50),1),3,7),REPT("!,0",7)),2,66)

复制代码

kevinchengcw · 发表于 2024-11-25 10:10

hugeinfo · 发表于 2024-11-25 10:11

=LEFT(CONCAT(TRANSPOSE(N(K6:Q6>50))&","),13)

cnmlgb9998 · 发表于 2024-11-25 10:28

="{"&TEXTJOIN(",",FALSE,TEXT(K6:Q6,"[>50]1;!0"))&"}"

wb1983 · 发表于 2024-11-25 11:01

{ ="{"&TEXTJOIN(",",1,IF($K6:$Q6>50,1,0))&"}" }

		自动登录	找回密码
密码			免费注册

[求助] 2010版本EXCEL数组公式求助