春节快乐! 答案: 1.定义名称Data0=魔格!$B$16:$D$18 2.定义名称Data1=魔格!$B$5:$J$13 3.W5中输入公式=IF(INDEX(Data1,CEILING(ROW(A1)/3,1),CEILING(COLUMN(A1)/3,1))=INDEX(Data0,IF(MOD(ROW(A1),3)=0,3,MOD(ROW(A1),3)),IF(MOD(COLUMN(A1),3)=0,3,MOD(COLUMN(A1),3))),INDEX(Data0,IF(MOD(ROW(A1),3)=0,3,MOD(ROW(A1),3)),IF(MOD(COLUMN(A1),3)=0,3,MOD(COLUMN(A1),3))),"") 4.将W5的公式复制粘贴到整个“变幻区”W5:AW31 数组公式也可以,如下表: {=IF(INDEX(Data1,CEILING(ROW(A1:AA27)/3,1),CEILING(COLUMN(A1:AA27)/3,1))=INDEX(Data0,IF(MOD(ROW(A1:AA27),3)=0,3,MOD(ROW(A1:AA27),3)),IF(MOD(COLUMN(A1:AA27),3)=0,3,MOD(COLUMN(A1:AA27),3))),INDEX(Data0,IF(MOD(ROW(A1:AA27),3)=0,3,MOD(ROW(A1:AA27),3)),IF(MOD(COLUMN(A1:AA27),3)=0,3,MOD(COLUMN(A1:AA27),3))),"")}
|