Question

请高手帮忙简化EXCEL函数公式？

我有两个EXCEL函数公式，目前正常使用，但是太麻烦了，请帮忙简化一下，谢谢
第一个：
=TRIM('101'!E4&" "&'101'!G4&" "&'101'!I4&" "&'101'!K4&" "&'101'!M4&" "&'101'!O4&" "&'101'!Q4&" "&'101'!S4&" "&'101'!U4&" "&'101'!W4&'102'!E4&" "&'102'!G4&" "&'102'!I4&" "&'102'!K4&" "&'102'!M4&" "&'102'!O4&" "&'102'!Q4&" "&'102'!S4&" "&'102'!U4&" "&'102'!W4&'103'!E4&" "&'103'!G4&" "&'103'!I4&" "&'103'!K4&" "&'103'!M4&" "&'103'!O4&" "&'103'!Q4&" "&'103'!S4&" "&'103'!U4&" "&'103'!W4&'104'!E4&" "&'104'!G4&" "&'104'!I4&" "&'104'!K4&" "&'104'!M4&" "&'104'!O4&" "&'104'!Q4&" "&'104'!S4&" "&'104'!U4&" "&'104'!W4&'105'!E4&" "&'105'!G4&" "&'105'!I4&" "&'105'!K4&" "&'105'!M4&" "&'105'!O4&" "&'105'!Q4&" "&'105'!S4&" "&'105'!U4&" "&'105'!W4&'106'!E4&" "&'106'!G4&" "&'106'!I4&" "&'106'!K4&" "&'106'!M4&" "&'106'!O4&" "&'106'!Q4&" "&'106'!S4&" "&'106'!U4&" "&'106'!W4&'107'!E4&" "&'107'!G4&" "&'107'!I4&" "&'107'!K4&" "&'107'!M4&" "&'107'!O4&" "&'107'!Q4&" "&'107'!S4&" "&'107'!U4&" "&'107'!W4&'108'!E4&" "&'108'!G4&" "&'108'!I4&" "&'108'!K4&" "&'108'!M4&" "&'108'!O4&" "&'108'!Q4&" "&'108'!S4&" "&'108'!U4&" "&'108'!W4)
//说明：把101至108sheet内的制定单元格内的值合并在一起，用空格隔开，消除多余的空格

第二个：='101'!F3+'101'!H3+'101'!J3+'101'!L3+'101'!N3+'101'!P3+'101'!R3+'101'!T3+'101'!V3+'101'!X3+'102'!F3+'102'!H3+'102'!J3+'102'!L3+'102'!N3+'102'!P3+'102'!R3+'102'!T3+'102'!V3+'102'!X3+'103'!F3+'103'!H3+'103'!J3+'103'!L3+'103'!N3+'103'!P3+'103'!R3+'103'!T3+'103'!V3+'103'!X3+'104'!F3+'104'!H3+'104'!J3+'104'!L3+'104'!N3+'104'!P3+'104'!R3+'104'!T3+'104'!V3+'104'!X3+'105'!F3+'105'!H3+'105'!J3+'105'!L3+'105'!N3+'105'!P3+'105'!R3+'105'!T3+'105'!V3+'105'!X3+'106'!F3+'106'!H3+'106'!J3+'106'!L3+'106'!N3+'106'!P3+'106'!R3+'106'!T3+'106'!V3+'106'!X3+'107'!F3+'107'!H3+'107'!J3+'107'!L3+'107'!N3+'107'!P3+'107'!R3+'107'!T3+'107'!V3+'107'!X3+'108'!F3+'108'!H3+'108'!J3+'108'!L3+'108'!N3+'108'!P3+'108'!R3+'108'!T3+'108'!V3+'108'!X3
//说明：把sheet101至108的指定单元格的数字相加，每个sheet有10个单元格。

Answer 1

方法是：

1、在101-108表中先设置某个辅助单元，输入相同的函数=CONCATENATE(E4," ",G4," ",I4," ",K4," ",M4," ",O4," ",Q4," ",S4," ",U4," ",W4)，假如，A4存放本表汇总

2、在汇总表中=CONCATENATE('101'!A4,'102'!A4,'103'!A4,'104'!A4,'105'!A4,'106'!A4,'107'!A4,'108'!A4)

Answer 2

由于你这些单元格没有看出有什么规律，这是没有办法的优化的，只有这么列出来。特别是对于第一个公式，目前EXCEL所有版本没有提供条件连接这类公式，是无法优化的。对于公式二，如果是表头有标记，可以考虑SUMIF优化。

Answer 3

第一个公式，如果你的excel支持textjoin函数，那么可以想法简化一下，不支持的话没什么太好办法了。
第二个可以简化成：
=SUM('101:108'!F3)+SUM('101:108'!H3)+SUM('101:108'!J3)+SUM('101:108'!L3)+SUM('101:108'!N3)+SUM('101:108'!P3)+SUM('101:108'!R3)+SUM('101:108'!T3)+SUM('101:108'!V3)+SUM('101:108'!X3)
或者
=SUMPRODUCT(('101'!F3:X3)*(MOD(COLUMN(F3:X3),2)=0))+SUMPRODUCT(('102'!F3:X3)*(MOD(COLUMN(F3:X3),2)=0))+SUMPRODUCT(('103'!F3:X3)*(MOD(COLUMN(F3:X3),2)=0))+SUMPRODUCT(('104'!F3:X3)*(MOD(COLUMN(F3:X3),2)=0))+SUMPRODUCT(('105'!F3:X3)*(MOD(COLUMN(F3:X3),2)=0))+SUMPRODUCT(('106'!F3:X3)*(MOD(COLUMN(F3:X3),2)=0))+SUMPRODUCT(('107'!F3:X3)*(MOD(COLUMN(F3:X3),2)=0))+SUMPRODUCT(('108'!F3:X3)*(MOD(COLUMN(F3:X3),2)=0))

Answer 4

你这是做交集公式吗？
如果是我，我不会这么做的。行列数都可以计算出来。sheet名称 是有规律的，目测每个sheet引用10次，那么可以用row函数相乘做出来，然后用textjoin合并，然后再用indirect返回引用。
如果是我，我不会这么麻烦，直接VBA代码 ，写完一键完成不好吗？

Answer 5

操作上有点麻烦，若是想简单一点，你应该是在每一个工作表中求得相应的数据，就可在最后把8个数据操作处理即可。
或者是通过8个辅助列，分别求得各表的数据，最后汇总，就简单很多了……
否则，就按你这样的操作吧，也可以解决你的问题，哈
比如，101表的数据求和公式为：
=SUMPRODUCT(TEXT('101'!$F3:$X3,"0;-0;0;!0")*(MOD(COLUMN($F3:$X3),2)=0))