函数进阶应用3
认识OFFSET函数
| 函数格式 | 参数说明 | 作用 |
|---|---|---|
| OFFSET（参数1，参数2，参数3，参数4，参数5） | 参数1：以谁为标准 参数2：下一多少行 参数3：右移多少列 参数4：取几行 参数5：取几列 |
动态获取数据 |
应用：使用offset函数获取表格最后五行数据，并计算平均值
在空白单元格输入“=offset()”,然后复制函数再在外面包上AVERAGE函数
OFFSET函数与动态图表
本节涉及offset函数、图标以及定义名称的相关内容
应用：以最后十行的数据为基础绘制折线图
-
具体操作
-
定义名称-最后十行日期，最后是行开盘价，最后十行收盘价
在空白单元格内输入=offset()函数，如下所示：
将对应得offset函数剪切——公式选项卡——定义名称——输入名称（最后十日，最后十日开盘价，最后十日收盘价）以及在引用位置输入框中输入对应得offset函数
-
自定义二维折线图
点击空白单元格—— 插入选项卡——选择二维折线图（此时显示的图表是空白的）——在空白图表上右击——选择数据，—— 添加，弹出界面填入”开盘价“，“=历史数据!最后十日开盘价”——依次填入开盘价，收盘价——在选择数据右侧，点击编辑，弹出界面填入”=历史数据!最后十日”——点击确定*2
-
-
操作演示
Offset函数与透视表
-
场景描述
传统的数据透视表，当数据源增加n行数据后，在数据透视表中是刷新不出来的，为了解决这一问题我们可以将数据源区域转为表格，第二种方式则是本期介绍的Offset函数
-
具体操作
-
利用Offset函数选择原始数据的全部区域
在原始数据表格某一空白单元格内输入如下：
剪切该函数——公式选项卡——定义名称，输入“数据区域”，在引用位置输入框中输入剪切的函数——点击确定
-
数据透视表的操作
插入选项卡——数据透视表——在弹出界面第一个输入框中输入“数据区域”——统计每个部门的发生额——将“部门”拖拽到行字段，“发生额”拖拽到值字段
当添加n行数据时，我们来到数据透视表，鼠标右击，刷新即可
-
-
操作演示
文本公式重新运算
宏表函数之一：evaluate函数
替换函数：SUBSTITUTE(替换目标所在单元格位置,"被替换字符","替换字符")
-
操作演示
说明：这里被evaluate函数包起来的是相对引用，在定义名称时，应选中对应输出单元格所在位置
控件与函数-员工信息查询表
案例前半部分请参考:https://www.cnblogs.com/DLChen/p/18292656
所涉及的函数有INDIRECT函数和MATCH函数
这里我们抓取对应员工的照片，设计到了定义名称、INDIRECT函数和MATCH函数
-
操作演示
流程说明1：在插入照片的区域所在单元格输入INDIRECT函数（绝对引用）—— 公式选项卡——定义名称——输入名称（照片），引用位置（剪切自INDIRECT函数）内容——插入选项卡——屏幕截图，截一块图片用来放照片——选中截图，在函数输入框中输入“=照片”（其中，“照片”是定义的公式名称）
流程说明2：根据姓名取照片——将姓名做成序列（数据选项卡——数据验证——序列——来源自姓名所在列）—— 输入INDIRECT函数和MATCH函数——公式选项卡——输入名称（PIC），引用位置（剪切自INDIRECT函数和MATCH函数）内容——插入选项卡——屏幕截图，截一块图片用来放照片——选中截图，在函数输入框中输入“=PIC”（其中，“PIC”是定义的公式名称）
