魏无牙 发表于 2021-10-6 22:02:00

以下公式有没有简单的表达方式？

单元格内敲入下面的公式，太繁琐。从黄色的Z3到Z26，其余的都一样，有没有简单的表达方式？
类似于从Z3到Z26循环之类的？
=IF(COUNTIF(INDIRECT({"h9:j9","e10:g10","i10:n10","s10:w10"}),Z3),"",Z3&",")&IF(COUNTIF(INDIRECT({"h9:j9","e10:g10","i10:n10","s10:w10"}),Z4),"",Z4&",")&IF(COUNTIF(INDIRECT({"h9:j9","e10:g10","i10:n10","s10:w10"}),Z5),"",Z5&",")&IF(COUNTIF(INDIRECT({"h9:j9","e10:g10","i10:n10","s10:w10"}),Z6),"",Z6&",")&IF(COUNTIF(INDIRECT({"h9:j9","e10:g10","i10:n10","s10:w10"}),Z7),"",Z7&",")&IF(COUNTIF(INDIRECT({"h9:j9","e10:g10","i10:n10","s10:w10"}),Z8),"",Z8&",")&IF(COUNTIF(INDIRECT({"h9:j9","e10:g10","i10:n10","s10:w10"}),Z9),"",Z9&",")&IF(COUNTIF(INDIRECT({"h9:j9","e10:g10","i10:n10","s10:w10"}),Z10),"",Z10&",")&IF(COUNTIF(INDIRECT({"h9:j9","e10:g10","i10:n10","s10:w10"}),Z11),"",Z11&",")&IF(COUNTIF(INDIRECT({"h9:j9","e10:g10","i10:n10","s10:w10"}),Z12),"",Z12&",")&IF(COUNTIF(INDIRECT({"h9:j9","e10:g10","i10:n10","s10:w10"}),Z13),"",Z13&",")&IF(COUNTIF(INDIRECT({"h9:j9","e10:g10","i10:n10","s10:w10"}),Z14),"",Z14&",")&IF(COUNTIF(INDIRECT({"h9:j9","e10:g10","i10:n10","s10:w10"}),Z15),"",Z15&",")&IF(COUNTIF(INDIRECT({"h9:j9","e10:g10","i10:n10","s10:w10"}),Z17),"",Z17&",")&IF(COUNTIF(INDIRECT({"h9:j9","e10:g10","i10:n10","s10:w10"}),Z18),"",Z18&",")&IF(COUNTIF(INDIRECT({"h9:j9","e10:g10","i10:n10","s10:w10"}),Z19),"",Z19&",")&IF(COUNTIF(INDIRECT({"h9:j9","e10:g10","i10:n10","s10:w10"}),Z20),"",Z20&",")&IF(COUNTIF(INDIRECT({"h9:j9","e10:g10","i10:n10","s10:w10"}),Z16),"",Z16&",")&IF(COUNTIF(INDIRECT({"h9:j9","e10:g10","i10:n10","s10:w10"}),Z21),"",Z21&",")&IF(COUNTIF(INDIRECT({"h9:j9","e10:g10","i10:n10","s10:w10"}),Z22),"",Z22&",")&IF(COUNTIF(INDIRECT({"h9:j9","e10:g10","i10:n10","s10:w10"}),Z23),"",Z23&",")&IF(COUNTIF(INDIRECT({"h9:j9","e10:g10","i10:n10","s10:w10"}),Z24),"",Z24&",")&IF(COUNTIF(INDIRECT({"h9:j9","e10:g10","i10:n10","s10:w10"}),Z25),"",Z25&",")&IF(COUNTIF(INDIRECT({"h9:j9","e10:g10","i10:n10","s10:w10"}),Z26),"",Z26&",")

建行建信辉 发表于 2021-10-10 00:05:39

能上传附件吗？

用户长空孤雁 发表于 2021-10-19 16:25:22

这个不需要附件吧，只是觉着公式这么写太复杂。直接拷贝，粘贴到EXCEL即可。
或者，再简化一下，以下的单元格公式有没有简化的表达方式。
其实就是看看Z3到Z26单元格的数值有没有在A1到A10出现过，如果出现，就显示Z单元格的值，如果没有出现，就不显示。
=IF(COUNTIF(A1:A10,Z3),"",Z3&",")
&IF(COUNTIF(A1:A10,Z4),"",Z4&",")
&IF(COUNTIF(A1:A10,Z5),"",Z5&",")
&IF(COUNTIF(A1:A10,Z6),"",Z6&",")
&IF(COUNTIF(A1:A10,Z7),"",Z7&",")
&IF(COUNTIF(A1:A10,Z8),"",Z8&",")
&IF(COUNTIF(A1:A10,Z9),"",Z9&",")
&IF(COUNTIF(A1:A10,Z10),"",Z10&",")
&IF(COUNTIF(A1:A10,Z11),"",Z11&",")
&IF(COUNTIF(A1:A10,Z12),"",Z12&",")
&IF(COUNTIF(A1:A10,Z13),"",Z13&",")
&IF(COUNTIF(A1:A10,Z14),"",Z14&",")
&IF(COUNTIF(A1:A10,Z15),"",Z15&",")
&IF(COUNTIF(A1:A10,Z17),"",Z17&",")
&IF(COUNTIF(A1:A10,Z18),"",Z18&",")
&IF(COUNTIF(A1:A10,Z19),"",Z19&",")
&IF(COUNTIF(A1:A10,Z20),"",Z20&",")
&IF(COUNTIF(A1:A10,Z16),"",Z16&",")
&IF(COUNTIF(A1:A10,Z21),"",Z21&",")
&IF(COUNTIF(A1:A10,Z22),"",Z22&",")
&IF(COUNTIF(A1:A10,Z23),"",Z23&",")
&IF(COUNTIF(A1:A10,Z24),"",Z24&",")
&IF(COUNTIF(A1:A10,Z25),"",Z25&",")
&IF(COUNTIF(A1:A10,Z26),"",Z26&",")

伯虎哥 发表于 2021-11-23 06:04:15

如果是 office2019及更高版本或WPS，可以用 CONCAT/TEXTJOIN，否则没有。

用户海阔天空 发表于 2021-12-5 23:00:45

写一下公式吧，我还不会用，谢谢

熟悉的脸 发表于 2021-12-6 07:21:29

=TEXTJOIN(",",,IF(COUNTIF(A1:A10,Z3:Z26),"",Z3:Z26))
没附件，你自己验证吧。
页: [1]
查看完整版本: 以下公式有没有简单的表达方式？