工资条的批量制作
如图，根据sheet1工资表，批量打印工资条，类似于WORD的邮件合并。
在sheet2的A1输入公式：
=CHOOSE(MOD(ROW(1:1)-1,3)+1,Sheet1!A$1,OFFSET(Sheet1!$A$1,INT((ROW(1:1)-2)/3)+1,COLUMN(A:A)-1),"")
公式向下拉、向右拉。
然后选择数据区域，设置条件格式，将标题行反色显示，效果如下：
知识点：
一、CHOOSE函数对IF的替代。
对于简单列举问题，CHOOSE显然更简洁。依次得到标题行-数据行-空行的变化。
如果用IF函数，相对复杂：
?=IF(MOD(ROW(1:1),3)=1,Sheet1!A$1,IF(MOD(ROW(1:1),3)=2,OFFSET(Sheet1!A$1,(ROW(1:1)-2)/3+1,),""))二、MOD函数取得循环序列。
一个小技巧：MOD(ROW(1:1)-1,3)+1，先减1再加1，避免了3的倍数行余数为0的情形。这样得到1,2,3的循环序列，作为CHOOSE的参数1。三、ROW、COLUMN函数。
为了使得在任何单元格输入公式都有效，这里没有使用空参数，而是使用了ROW(1:1)、COLUMN(A:A)的引用形式。四、OFFSET偏移引用。
基于Sheet1!$A$1的偏移，在2,5,8……行依次引用数据源的2,3,4……行。
原理：由OFFSET是CHOOSE的参数3，也就是列举项的第2项，这已经决定了ROW(1:1)实际得到的是2,5,8……，可以看做是首项为2，公差为3的等差数列，在这个基础上倒过来推算。
有的人可能奇怪，ROW(1:1)-2得到的不是-1吗？公式在下拉填充时，ROW(1:1)是在变的。而且CHOOSE的取值顺序已经决定了，OFFSET部分只在2,5,8行。于是ROW(1:1)-2得到0,3,6……，INT((ROW(1:1)-2)/3)+1得到1,2,3……。
实际上就这个具体应用来说，这里的INT是多余的，写成(ROW(1:1)-2)/3+1就可以。
五、混合引用。
CHOOSE的参数1用的是Sheet1!A$1的混合引用形式，这样右拉填充时，列相应变化。
OFFSET的基点Sheet1!$A$1是绝对引用。ROW(1:1)、COLUMN(A:A)是相对引用。
思考：
想了一下，如果将OFFSET的基点Sheet1!$A$1改为混合引用Sheet1!A$1，公式还可以简化，列参数可以缺省：
=CHOOSE(MOD(ROW(1:1)-1,3)+1,Sheet1!A$1,OFFSET(Sheet1!A$1,(ROW(1:1)-2)/3+1,),"")
当然这里如果用IF函数，借用OFFSET的特性，公式还可以简化：
=IF(MOD(ROW(1:1),3),OFFSET(Sheet1!A$1,(ROW(1:1)-2)/3+1,),"")
这是因为，当偏移参数为小数时，是向下取整，所以标题行和数据行的公式可以合并，只对空行另外处理就行。
不过这样，虽然公式更短，但可读性就要差一些，我个人还是喜欢用CHOOSE。 |
|
