Excel函数进阶教程：从控件到动态图表的全方位实战指南
Excel函数进阶教程：从控件到动态图表的全方位实战指南
Excel是职场人必备的办公软件之一，掌握其高级功能可以大幅提升工作效率。本文将详细介绍Excel中一些实用的高级功能，包括函数与控件、定义名称、OFFSET函数等，通过多个具体案例帮助读者掌握这些功能的使用方法。
函数与控件
实现员工信息表的查询，最终效果如下：
详细实现步骤：
- 使用快捷键ALT L I S插入滚动条
- 右键设置详细参数，选定一个“值”单元格，以便后续对它的引用
- 使用
INDEX函数，对于数据进行抓取，第二个参数就是控件的变化值=INDEX(员工数据库!A:A,‘查询模板（练习）’!M2)
定义名称
使用快捷键ALT M M D定义名称，在对话框中选定区域给他命名！这样做的好处就是可以在公式引用这个命名！
当然也可以为常量创建一个名称，比如说折扣是0.8，只需在引用位置写上0.8即可！
好处：可以批量的修改公式，只需把引用位置修改一下就可以了！
注意：为公司定义名称时；在哪个单元格写公式，就在哪个单元格定义名称；因为引用位置是相对的，所以会发生变化！
制作二级下拉菜单
最终效果：
首先为这三列数据，定义名称！如何查看是否定义成功？只需选中区域并查看左上角的名称，是不是自己定义的名称！
然后，食品应该对应食材选择的类型来分配，因为“饮料”、“食材”、“调味料”我们已经为其自定义了名称，所后面的单元格只要引用前面即可，然后用INDIRECT激活就行！
OFFSET函数
参数解读 ：以什么为基准参考；下移多少 行；右移多少 列；再取回多少行、多少列？
实战：如何求变动行/列，最后五项的平均值？
公式
=OFFSET(B1,COUNTA(B:B)-5,0,5,1)
下移多少行？只要用总数减去需要保留的行数就可以！
再嵌套AVERAGE求下平均值就行
=AVERAGE(OFFSET(B1,COUNTA(B:B)-5,0,5,1))
动态抓取数据 生成折线图
需要用到OFFSET函数动态抓取数据，然后需要用到定义名称，因为折线图中不能直接引用公式！
先动态抓取数据！假设永远抓取数据源中的最后10行数据，例中的是，日期、开盘价、收盘价
先动态抓取最后10行的日期：
=OFFSET(A1,COUNTA(A:A)-10,0,10,1)
同理将最后10行开盘价、收盘价都动态抓取到！
=OFFSET(B1,COUNTA(A:A)-10,0,10,1)
=OFFSET(C1,COUNTA(A:A)-10,0,10,1)
再自定义名称，因为要对公式自定义名称，所以最好是将有需要的位置，锁定绝对引用！
=OFFSET($A1 , C O U N T A ( 1,COUNTA(1,COUNTA(A:$A)-10,0,10,1)
使用快捷键ALT N N1插入折线图，然后右键图形进行编辑；导入用定义名称指向的数据，选择数据源！
注意细节，系列值一定要选到表名来，然后接定义好的名称！
依次添加定义好的“开盘、收盘”项名称，生成图形！
最后修改时间轴，依旧“选择数据源”，“开盘、收盘”右侧对应的水平（分类）轴，指向定义的名称“日期”即可！
这样在我们新增数据的时候，图形会自动的跟随发生变化！
OFFSET函数与数据透视表
因为在生成数据透视表时，前提就是要选定数据区域。如果新增的数据，那么数据透视表并不会更新；为了解决这个固定数据区域的问题，我们可以用OFFSET函数来动态抓取数据区域，再去生成数据透视表。
公式
=OFFSET($A$1,0,0,COUNTA($A:$A),6)
定义名称：
空白处、创建数据透视表，然后动态引用范围
这样生成的数据透视表，如果数据源新增了、透视表也支持刷新更新数据！
让文本公式重新运算
对于一些宏表函数无法直接使用，需要先定义名称。在使用
如下列，计算公式，需要用到EVALUATE函数，但是它是宏表函数！
定义一下名称，然后在名称写入公式
计算一些“不规则”数据；同样的原理，只要用SUBSTITUTE的函数把符号给替换成“乘号*”就行了
公式
=EVALUATE(SUBSTITUTE(B2,“|”,“*”))
动态抓取图片
就要结合INDEX函数、定义名称、加截图来完成
先再照片区域用INDEX函数获取到数据，但还是不能直接抓取到照片！
将INDEX函数定义为新名称：“抓取照片”
然后：插入→屏幕截图（ALT N SC S）截取一个范围大小，最后将“抓取照片”定义名称，链接好！
点中截图在编辑栏为图片、链接定义名称！
INDEX加MATCH组合、定义名称，来抓取照片
公式
=INDEX(员工数据库!$H:$H,MATCH(Sheet1!$B$3,员工数据库!$A:$A,0))
定义名称为“getpic”，最后选中截图，引用定义名称！
透视表切片器 抓取照片
最终效果：
切片器中的“筛选”字段，和点击切片器中的单元格会一同变化，所以可以通过 INDEX函数和MATCH函数，对于“筛选”单元格的查找，来获取到照片；最后通过定义名称、截图来链接到照片！
公式
=INDEX(人员!$B:$B,MATCH(Sheet5!$B$1,人员!$A:$A,0))
润色一下：我没有点击筛选的时候是找不到数据项的，可以在最后一列新增一个“全部”和图片！
制作带照片的抽奖小工具
最终效果：
RANDBETWEEN随机数函数，作用是给定一个范围，随机生成该区范围内的整数。
公式：
=RANDBETWEEN(1,6)
然后依旧是用INDEX和MATCH函数，生成随机数单元格的引用（记得绝对引用），然后定义名称，分别取回姓名和照片！按住F9可以持续刷新，模拟动态选取！
条件格式创建甘特图
最终效果
逻辑分析：当前单元格上方的日期，必须>=左侧的日期，并且小于执行日过程最后一天的日期
公式：选中单元格后ALT H L N开启数据条件格式，然后输入公式即可！
=AND(D$4>=$B5,D$4<$B5+$C5)