公司要临时制作一万张食堂餐券,下图所示是行政小哥设计好的餐券样式,本意是直接打印后盖章裁剪后使用;但是这个餐券是要求有编码的(黄色单元格里),编码格式是公司名缩写加数字组成,要求数字要从1到10000,也就是说要在“餐券编码”后面输入一万个连续且不重复的编码!如果手动输入起码要半天的时间,说不定还会出错,眼看行政小哥一脸生无可恋,于是我过去三两下帮他搞定(论每个公司都应该有一个EXCEL高手的重要性)

EXCEL函数之offset函数应用举例——设计餐券-趣帮office教程网

解决方案如下:

一、 准备一份辅助数据,用于存储餐券编码,我这里的编码是ABC0000001到ABC0010000,如下图所示:

EXCEL函数之offset函数应用举例——设计餐券-趣帮office教程网

二、 设计一个控件,用于控制餐券编码的显示。行政小哥的模板是每页A4纸打印10张餐券,用控件的作用是:每当单击一次控件,就会生成10张餐券,如下图所示:

EXCEL函数之offset函数应用举例——设计餐券-趣帮office教程网

三、 在B2输入函数:=OFFSET(辅助!$B$1,(ROW() 1)/3 ($H$2-1)*10,);在E2输入函数:=OFFSET(辅助!$B$1,(ROW() 1)/3 1 ($H$2-1)*10,)。

四、 选中B2到E2,复制,然后粘贴到其他的黄色单元格内,这时你会发现:前10张餐券生成了!如果你再点击下控件的右箭头,你会发现:又生成了10张餐券!

下面解释下B2单元格函数中的(ROW() 1)/3 ($H$2-1)*10。请大家注意,在生成前10张餐券时,在B2单元格内,OFFSET函数的偏移量是1,就会得到编码ABC0000001;然后单击控件,OFFSET函数的偏移量是11,…,规律如下图所示:

EXCEL函数之offset函数应用举例——设计餐券-趣帮office教程网

这是数学中的等差数列,而a的数值就是我们用控件控制的,即H2位置处,H2的初始值是1,每单击一次右箭头,数值就会增大1,这样就做到“OFFSET函数的偏移量能跟着控件的变化而变化”。

可能有人会问:既然b=1 (a-1)*10,那么(ROW() 1)/3又怎么解释?大家注意,在B2单元格,ROW函数返回的结果是2,那么(ROW() 1)/3就等于1,这样写函数,是因为我们把函数写好后要复制到B8、B14、B20…,而新的位置上(行号)对应的OFFSET偏移数值分别是3、5、7、9…,规律如下图所示:

EXCEL函数之offset函数应用举例——设计餐券-趣帮office教程网

这也是个等差数列,和上面的解释一样,E2单元格的函数也是这个道理,只是E2函数多加了1,是因为在这里OFFSET的偏移量总是比B2的偏移量大1,道理很简单,因为餐券是并排打印两张。最终效果如下图所示:

EXCEL函数之offset函数应用举例——设计餐券-趣帮office教程网