搜索
前言：函数解析：123456789101112131415161718192021222324具体举例：(红色区域为函数返回结果)123456789101112131415161718192021222324123456789101112131415161718192021222324123456789101112131415161718192021222324变动方法：=OFFSET(C32,0,2,3,4)=OFFSET(C20,1,3,1,1)通过上面三个案例，我们发现Offset的返回结果可以是一个数据区域。正常情况下，我们是可以用鼠标选中一个连续区域去绘制图表的，这也就使Offset的返回结果可以成为图表的数据源。接下来,我们只要通过调整Offset参数的数值,就可以随时改变该参数引用的区域了。比如下面的例子，我们将Offset的第2、3个参数制定在D44和F44单元格上，那么我们只要随时改变单元格的数值，就可以改变选中区域（即红色区域）了。由于变动图表必须用到函数，而Offset又是比较常用的一种制作变动图表的函数，所以我想还是在开始之前把这个函数交待清楚如果对Offset有一定了解的朋友可以跳过本部分。不少朋友看到Offset有5个参数，第一反应就是太过复杂，其实这5个参数我们可以分成3组来看待。首先，Offset是个用来选择某一单元格区域的函数。在上面的公式里，我们用红、蓝、黑三种颜色把offset的五个参数分成了3组。其中红色参数（也就是第一个参数）代表执行这个函数的时候光标的起点，也就是C13单元格。蓝色的一组参数表示把起始单元格下移N行（参数2）和右移N列（参数3），本案例中就是从C13单元格开始，先下移1行，再右移2列，到达了E14单元格。最后一组的两个黑色参数，代表一个N行M列（参数4是N，参数5是M）的数据区域。这个区域是以前3个参数决定的光标的新位置(E14)为起点的，从这个单元格（E14）开始计算的N行M列（本案例参数4参数5都是1，所以是以E14为左上角的一行一列，也就是E14本身）。综合5个参数的效果，上面的函数的返回结果应该是9=OFFSET(C13,1,2,1,1)=OFFSET(C26,2,3,2,1)回首页参数2：1参数3：2公式=OFFSET(C46,D44,F44,1,1)实际结果：9123456789101112131415161718192021222324当如上面案例所示时，参数2等于1，参数3等于2，那么实际结果为9，即从C46开始下移一行右移两行以后的1行1列的区域。如果我们将D44和F44分别改称3和1，发现实际结果会改为20。这时候，通过更改函数参数来更改选择区域的效果就实现了。如果上面的变化原理你理解了，那么恭喜你，最基本的变动图表原理你已经掌握了。下一步，我们就要学习如何把可变动的Offset函数返回值变成图表的过程了。通过上面三个案例，我们发现Offset的返回结果可以是一个数据区域。正常情况下，我们是可以用鼠标选中一个连续区域去绘制图表的，这也就使Offset的返回结果可以成为图表的数据源。接下来,我们只要通过调整Offset参数的数值,就可以随时改变该参数引用的区域了。比如下面的例子，我们将Offset的第2、3个参数制定在D44和F44单元格上，那么我们只要随时改变单元格的数值，就可以改变选中区域（即红色区域）了。1月2月3月制作原理：张三216227109李四118160206王五22579310察看2月的数据姓名数据张三227李四160王五79适用范围：制作步骤：步骤一：设置辅助区域姓名数据张三李四王五步骤二：设置变动参数查看本案例是最基本的变动图表,所以先简单的说说变动图表的原理。变动图表能够变动的根本原因在于：使用函数作为数据源，在函数的某个参数发生改变的时候，函数的返回值随即发生改变，引用该函数返回值作为数据源的图表也就随即变动，这样的图表就使最基本的一种动态图表。我们通常最习惯的函数编辑方式是在单元格里编辑函数，所以最基本的动态图表，就是直接引用这些写好了函数的单元格区域制作图表。我们只要设置一个可以随时手动选择数值的函数参数，就通过改变参数的方式随时让图表变动了。蓝色表格部分为本图表的数据源，我们的目的是通过选择不同的月份察看不同月份里3位销售员销售业绩的贡献率，也就是察看他们占总销售额的百分比。确定了我们要构造什么样子的图表，就要根据这个图表的需求设计一个辅助数据源。本案例是饼状图，所以数据源结构比较简单，就是由数据系列名称和数据系列值组成。结构如下：为了让图表可以随时变动，一定要有一个函数参数是我们随时可以更改的。也就是本案例中黄色单元格部分，通过手动修改月份，可以实现红色辅助区域数据的变换，图表也随之改变。该部分只要手动设置即可，要注意的是：要让可以变动的那个参数单独占用一个单元格，以便之后引用方便。如果希望出现案例里的下拉菜单，可以使用数据有效性里的“序列”功能。（07版里在数据选项卡的“数据有效性”命令里，03版的在数据菜单的“有效该方法制作的变动图表是最基本的一类，由于采用了大家都能轻松接受的辅助区域法，并且没有使用控件，所以掌握起来非常容易。不足之处是辅助区域会影响整个报表的美观。如果隐藏，在隐藏后容易被误删或者误更改，在不进行工作表保护的情况下有一定风险。个人认为该方法作为学习变动图表的入门学习资料比较适宜，但是出于数据安全的角度，实际应用中49%34%17%张三李四王五回首页步骤三：设置引用函数姓名数据张三=VLOOKUP(A8,$A$1:$D$4,$E$6+1,0)李四=VLOOKUP(A9,$A$1:$D$4,$E$6+1,0)王五=VLOOKUP(A10,$A$1:$D$4,$E$6+1,0)步骤四：创建图表步骤五：相关善后处理截止到上一步，本案例已经基本完成。如果希望这个图表做的更加完美，可以我们之前制作的红色辅助区域进行隐藏，同时进行工作表的保护，以保证报表的美观性和安全性。只要在红色辅助区域里写一个函数，就可以把蓝色数据源里对应的数据调用过来了，当然函数里一定要包含步骤2里设置的那个黄色区域的参数，不然无法实现变动效果。其函数具体书写如下，如对Vlookup函数不理解者请先查看该函数的帮助（注：所有引用均是左侧原案例位置）选中红色辅助数据区域，插入饼状图,并根据自己的需要调整图表的各种属性。34%17%步骤一：设置辅助区域步骤二：设置变动参数2月的数据本案例是最基本的变动图表,所以先简单的说说变动图表的原理。变动图表能够变动的根本原因在于：使用函数作为数据源，在函数的某个参数发生改变的时候，函数的返回值随即发生改变，引用该函数返回值作为数据源的图表也就随即变动，这样的图表就使最基本的一种动态图表。我们通常最习惯的函数编辑方式是在单元格里编辑函数，所以最基本的动态图表，就是直接引用这些写好了函数的单元格区域制作图表。我们只要设置一个可以随时手动选择数值的函数参数，就通过改变参数的方式随时让图表变动了。蓝色表格部分为本图表的数据源，我们的目的是通过选择不同的月份察看不同月份里3位销售员销售业绩的贡献率，也就是察看他们占总销售额的百分比。确定了我们要构造什么样子的图表，就要根据这个图表的需求设计一个辅助数据源。本案例是饼状图，所以数据源结构比较简单，就是由数据系列名称和数据系列值组成。结构如下：为了让图表可以随时变动，一定要有一个函数参数是我们随时可以更改的。也就是本案例中黄色单元格部分，通过手动修改月份，可以实现红色辅助区域数据的变换，图表也随之改变。该部分只要手动设置即可，要注意的是：要让可以变动的那个参数单独占用一个单元格，以便之后引用方便。如果希望出现案例里的下拉菜单，可以使用数据有效性里的“序列”功能。（07版里在数据选项卡的“数据有效性”命令里，03版的在数据菜单的“有效该方法制作的变动图表是最基本的一类，由于采用了大家都能轻松接受的辅助区域法，并且没有使用控件，所以掌握起来非常容易。不足之处是辅助区域会影响整个报表的美观。如果隐藏，在隐藏后容易被误删或者误更改，在不进行工作表保护的情况下有一定风险。个人认为该方法作为学习变动图表的入门学习资料比较适宜，但是出于数据安全的角度，实际应用中步骤三：设置引用函数=VLOOKUP(A8,$A$1:$D$4,$E$6+1,0)=VLOOKUP(A9,$A$1:$D$4,$E$6+1,0)=VLOOKUP(A10,$A$1:$D$4,$E$6+1,0)步骤五：相关善后处理截止到上一步，本案例已经基本完成。如果希望这个图表做的更加完美，可以我们之前制作的红色辅助区域进行隐藏，同时进行工作表的保护，以保证报表的美观性和安全性。只要在红色辅助区域里写一个函数，就可以把蓝色数据源里对应的数据调用过来了，当然函数里一定要包含步骤2里设置的那个黄色区域的参数，不然无法实现变动效果。其函数具体书写如下，如对Vlookup函数不理解者请先查看该函数的帮助（注：所有引用均是左侧原案例位置）选中红色辅助数据区域，插入饼状图,并根据自己的需要调整图表的各种属性。49%张三李四王五1月2月3月制作原理:张三216227109李四118160206王五22579310察看李四的数据适用范围：制作步骤：步骤一：设置变动数据区域由于前一个案例介绍的辅助区域法存在数据安全问题，同时也显得不够专业，所以利用定义名称制作图表的方法就应运而生了。通过公式选项卡下的名称管理器功能（Office2003里是在插入菜单下的“名称”功能里），可以将上一个案例中写在辅助区域里的函数写在名称管理器里，然后使用预设好的名称作为数据源图表数据源，就可以实现与辅助区域法相同的效果了。由于名称管理的方式将函数放置在后台，所以避免了数据区域被误改的风险，使函数数据源更加稳定，看上去也更加专业。所以定义名称法是最常用的变动图表制作方法，被Excel使用者所广泛接受。也是日后制作复杂变动图表的基础。蓝色数据区域为原始数据源，本案例目的在于通过更改黄色数据区域的人名，使用柱状图表现每个任1、2、3月数据高低情况。本案例中黄色单元格部分为函数的变动参数，通过手动修改人名，可以实现名称管理器里的名称所包含的函数变换，图表也随之改变。注意：要让填写人名的单独占用一个单元格，以便之后引用方便。本案例和之前的案例都使用到了下拉菜单控制数据，简单的说一下下拉菜单的制作方式：在数据选项卡里选择“数据有效性”命令（2003版里在数据菜单的有效性命令里），在对话框中选择“允许序列”，然后在数据源选框里写上下拉列表里的内容，用英文逗号隔开（张三，李四，王五）。之后就可以点击确定了。需要说明的是：有效性下拉菜单里的数据一定要和数据源里的一模一样，否则函数不容易查找出对应的值0501001502002501月2月3月回首页步骤二：创建名称步骤三：根据名称创建图表打开公式选项卡中的名称管理器，新定义一个名称（03版的在插入菜单的名称命令里），名称为：“案例2_销售数据”，引用的公式写：=OFFSET('2、定义名称动态图表'!$A$1,MATCH('2、定义名称动态图表'!$B$6,'2、定义名称动态图表'!$A$2:$A$4,0),1,1,3)（绿色代表不同参数）。需要说明的是：之前的案例由于是引用整个辅助区域作为数据源，所以是分别用几个Vlookup分别调用不同的数值，这个案例里由于是在柱状图里引用一个连续的数据区域，所以使用了能直接引用整个数据源区域的Offset函数，建议不熟悉的朋友先查看前文Offset函数的教程，Match函数由于比较简单，请察看Match函数的说明。创建一个柱状图，右键设置数据源，添加一个新的数据系列，其系列值为：=变动图表案例教程.xls!案例2_销售数据。在数据源选择的对话框里将水平分类轴改为B1：D1单元格即可让横坐标显示月份。注意系列值里必须要写明工作簿名称。图表基本完成，根据个人需要设置图表属性即可。05010015020025010501001502002501月步骤一：设置变动数据区域由于前一个