大家好 ，我是虎课网的讲师 ，我叫郭辉 ，主讲财务会计 玩转excel类的课程 ，目前呢是现任的某公司财务总监 ，拥有20多年的财务工作经验和十几年的excel学习经历 ，下面我们要讲解的是一个营业收入分类数据分析的这样一个案例 ，那么这个案例呢它主要是想实现在不同类别下 ，指定月份销售任务完成数据的一个对比 ，以图表的方式呢展现完成情况以及呢与平均值的一个对比 ，那么本案例呢主要是应用了OFFSET这个函数 ，以及呢它在制作图表数据中一些应用的特点 ，应用的范围呢包括很多岗位 ，就是说咱们这些案例呢不单纯是用在财务上 ，其他方面呢也都会用到 ，那么接下来呢我们先来看一看OFFSET这个函数它的应用的原理 ，什么是OFFSET函数呢 ，OFFSET函数呢是一个以指定的引用为参照系 ，通过给定偏移量 返回新的引用的这样一个函数 ，那么单纯用这一句话来解释这个函数呢会觉得有点苍白无力 ，那么下面呢我们先简单的介绍一下这个函数的语法结构 ，OFFSET呢一共有五个参数 ，这五个参数呢我们用中文做一下翻译的话 ，那就是第一个是定位的起点 ，或者说就是我们上面这一句话当中的第一句 ，以指定的引用为参照系 ，那么这个定位起点什么作用呢 我们一会来看 ，

然后后面这四个偏移量呢是两对 ，第一对 下偏移量 右偏移量 ，第二对是高度和宽度 ，那么这五个参数分别代表什么样的含义呢 ，下面呢我们先看一下第一个 ，第一个表呢我们制作了一个这样的数据区 ，然后同时呢我们又给这个区域呢分别设定了一个行号和一个列号 ，这样的话呢我们做一个这个对照 ，我们现在呢以A1单元格作为起始的这个参照点 ，然后呢分别给定一个下偏移量和右偏移量 ，也就是说我们在使用这个函数的过程中 ，可以呢仅仅使用这五个参数当中的其中的两个三个甚至四个或者是五个 ，这五个参数不一定都出现 ，那么我们这张表当中使用的呢是前三个参数 ，也就是定位起点 下偏移量和右偏移量 ，那么这公式呢我们把它写在这儿了 ，定位起点就是A1单元格 ，就是以A1单元格为定位的起点 ，向下偏移O2也就是它 向右偏移P2也就是这个数字 ，那我们通过改变这两个数字来观察这个公式的结果是什么 ，由于这个公式呢在编辑栏里呢太小 看不太清 ，所以呢我们把它做了一个这个动态的一个截图 ，通过我们改变O2和P2单元格内的值 ，然后来观察呢我们这个公式 ，它的这个结果引用的是哪个单元格 ，那现在呢我们分别把这个数字呢都改成3 ，

那么解读这个公式呢就是以A1单元格呢 ，为起始点 为定位的起点 或者说为参照点 ，向下偏移3 向右偏移3 ，向下偏移3 123 再向右偏移3 123 获得的这个结果呢 53 ，那我们再来一个 大家体会一下这两个参数的作用 ，向下偏移5 向右偏移4 ，从A1单元格向下偏移12345 向右偏移4 1234结果是84 ，那我们再来体会这个函数的作用 ，它就是在指定的参照点的基础上 ，通过给定的向下和向右的一个偏移量 ，那么可以获得偏移之后的那个结果 ，那么可能在这儿呢细心的人也许会问 ，你这个下偏移量和右偏移量可以是负数吗 ，可以 当然了 那你的这个起始点就不能是A1了 ，你A1因为它的负数代表什么呢 就是反向 ，你比方说负5 那就是向上 要是负4呢就是向左 ，而你这个A1呢是在这个表格的左上角 ，所以说向上也好 向左也好 它没法移动了 ，除非呢你把定位点比方说放在这 ，放在这个数据表的右下角 ，那你就可以让它呢向上偏移和向左偏移了 ，代表呢就是负数就可以了 ，好 ，我们再来看刚才呢讲了是123 ，这三个参数的情况下它是怎么运行的 ，那如果说是四个参数一起参与进来会是什么结果呢 我们来看一看 ，我们仍然呢在这个表格里面输入了一个这样的公式 ，而且把这个公式呢已经写在这儿了 ，

它呢是五个参数非常的齐全 ，还是以A1单元格为起始点 ，然后呢向下偏移 向右偏移 高度和宽度 ，那么它的这个作用是什么 ，我们先来看看当前的这个参数 ，以A1单元格为起始点 向下偏移3 123 向右偏移4 1234 ，那么这个高度和宽度代表什么 ，就是当我们定位到这个位置之后 ，向下偏移 向右偏移之后 ，我们获得的高是多少呢 2 宽是多少呢 3 ，也就是说我们这个OFFSET的这个函数 ，它呢最终获得的这个结果呢可以是一个单元格 ，也可以是多个单元格 ，是一个2×3的这样的一个区域 ，我们也可以试着把这个改一下 比方说3×3 3×3 ，那我们获得的这个结果呢 就是从这两个偏移量之后获得的这个点 ，然后以这个点为起始点 ，获得一个几乘几啊 3×3的这样的一个区域 ，那这个结果能得到验证嘛 就是刚才的这个能够验证 ，我们一看 噢 原来它跟它是相等的 ，那么在这里面能验证吗 能 ，因为我们加一个SUM求和 ，就是我们单纯地用OFFSET获得这个结果 ，是没办法把这些数据呢全部的都显示在单元格里 ，但是呢我们用SUM对这个结果呢求个和就可以了 ，那现在呢我们来看 我们随意呢找一个单元格 ，输入一个SUM 然后呢我们对这个区域选中 然后呢求和 结果是315 ，

那就说明呢我们这个OFFSET通过这四个参数作用之后获得的这个结果 ，求和确实就是一个3×3这样一个区域 ，这就是OFFSET的作用 ，它的作用用一个简单的话来描述呢是什么呢 ，就是通过一个定位点和两个偏移量之后获得的 ，或许是一个单元格的值 ，也可以呢是多个单元格的一个区域 这样一个函数 ，好 ，关于OFFSET呢我们就给大家介绍到这里 ，好 ，下面呢我们就来看一下数据源 ，所谓数据源呢就是我们是根据什么样的一组数据做的一些分析 ，这组数据呢它的纵向字段呢是商品的大类 ，包括五金 包括家用日用百货 ，包括这个数码产品等等各种各样类别 ，然后它的横向的字段呢是一个月份 ，也就相当于是一个时间轴 是个时间轴 ，然后同时呢还有两组比较特殊的数据 ，第一个呢是一个月度的目标 ，就比方说家用电器这一项 ，我们给它这一年制定了一个每个月的目标啊 50万 ，同时呢还有一组数据呢就是它的平均值 ，就是这个实际完成的一个平均值 ，那这个月度目标也好 还有这个平均值也好 ，我们都会在后面的这个数据分析当中图表当中呢 有分别的一个体现 ，所以呢我们在这儿呢特意呢给大家介绍这两组比较特殊的数据 ，那对于这样的一张表 其实在我们这个工作当中呢比比皆是 ，我们不一定非要把它理解成这就是一种 ，

超市或者说是商场的一个统计表 ，你可以把它想象成很多 比方说我们把这月份固定下来 ，你可以往这里面添加 ，比方说人 比方说产品 比方说部门 ，比方说销售一部销售二部销售三部等等的 都可以 ，那对于这样的一个非常普通的 非常常见这么一个数据表 ，我们能从这里面做出哪些分析的 ，如果说单纯看这些数据的话 ，我不用说大家也会有感觉 ，就是说这数据好像从里边也看不出什么来 ，谁大谁小 这怎么个看法 是纵着看呢还是横着看 ，那接下来呢我们给大家提供一个思路 看看它的效果 ，我们通过调整这个控件来实现一个数据的切换 ，那为什么不把这个数据整体的绘制到一张图里 ，那这就是我们做图表的一个套路 ，就是尽可能的简化反映在图表当中的数据 ，如果我们要是把这一组数据一股脑的放在一张图上 ，那么导致的结果是什么呢我们来看一下 什么也看不清了 ，那如果说要是插入一个折线图 那也可以来看一下 ，这几乎呢就是什么也看不见了 什么也看不到了 ，最好的办法是什么呢 就是把它提取出来 ，我们一项一项的看 先看家用电器 ，家用电器这一项 我们从这张图里来看 ，就是1月2月3月在逐渐的下降 ，45678 这几个月连续在高位徘徊 ，然后到了9月10月呢又有所回落 ，11月又达到了一个比较高的一个水平等等的 ，

我们就不再一一的给大家说了 ，那从这里面我们就可以很清晰的看到 ，哪些呢是在这个平均值之上 哪些呢与我们的这个目标 也就是这个目标值相比 ，它呢大约呢是比目标值高 还是低 非常的清晰 ，这个家用电器一目了然 ，然后我们再来切换一个 我们再随便切换一个 ，比方切换一个家具家纺啊这一类的 那从这来看的话 ，显然呢这个7月份之前这个数据呢相对还是比较平稳 ，基本上是在这个平均线的左右上下这么一个变化 ，那只有3月和6月呢略微的要高出一些平均值 ，但是呢从8月份往后就变化得比较起伏比较大 ，8月份比较低 9月10月呢又比较高 11 12月又下来了 ，显然呢这个上半年相对表现的还是比较平稳的 ，