excel函数offset制作动态下拉菜单（EXCEL函数之offset函数应用举例）

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

解决方案如下：

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

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

三、 在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，…，规律如下图所示：

这是数学中的等差数列，而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…，规律如下图所示：

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

“数据分析小哥哥”公众号，以后将不定时更新我在数据分析领域的见解，可能会有数据思维训练、数据分析过程解读、数据报告撰写、分析工具使用等方面的文章不断出现，如果你想学点数据分析方面的知识，想提升自己的数据分析能力，那么请跟着“数据分析小哥哥”一起前行吧，喜欢“数据分析小哥哥”的话，别忘了分享给你的同事、同学和朋友哦~