Excel动态图表应用.pdf

《Excel动态图表应用.pdf》由会员分享，可在线阅读，更多相关《Excel动态图表应用.pdf（37页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、http:/ Excel 动态图表应用1 首先看一下数据源：根据分析需求，要制作可以 A列、B列、月份选择的动态数据表。例如某月中某个城市各个组别的销售图表包含柱形图及饼图，柱形图用以突出各个项目之间的对比情况，饼图以用对各个项目在整个数据中的占比情况，完成后效果如下：某个城市某个组别在各个月的销售图表：本实例的图表重点在于两个选择项的同时对下拉列表的控制互动。为了便于理解及添加新的应用要求，因此在【名称管理器】中新建名称来解决这类问题。此次实例中的名称管理器中的新建名称公式如下：如果觉得有些生涩难懂，可以参考我之前的博文：Excel 动态图表应用，与本文类似但应用的名称公式较为简单。这些名称

2、的主要功能是快速地、动态地赋值给图表的【系列】及【数据】、【标签】等。名称管理器中的部分名称解释：（1）按钮 _分公司：=图表!$M$3 将三个分公司按钮分为一组，设置其属性【控制】单元格链接“图表!$M$3”。按钮 _下拉列表分类链接至M1。（2）标签：=CHOOSE(图表!$M$1,CHOOSE(图表!$M$3,组别 _北京,组别 _上海,组别 _广州),月)为图表【水平（分类）轴标签】做标识。CHOOSE 选择函数是本动态图表的核心部分，“北京、上海、广州”这三个选项会链接至M3单元格，因此可以用M3单元格来控制标签标识。（3）系列：=CHOOSE(图表!$M$1,系列 _月,系列 _组

3、别)服务于图表的【图例项（系列）】。它与其他几个名称中的函数类似嵌套了CHOOSE 函数。标签和系列在图表中的位置【插入】选择图表类型创建图表，然后点击右键【选择数据】。如下图所示：当选择编辑时需要输入完整的工作簿路径：（4）系列 _月：=OFFSET(CHOOSE(图表!$M$3,区域 _北京,区域 _上海,区域 _广州),0,图表!$M$2,COUNTA(CHOOSE(图表!$M$3,组别 _北京,组别 _上海,组别 _广州),1)这个函数比较长，这里稍微解释一下，如果对函数构成不了解，可以按F1 帮助一下。Offset函数可以利用偏移量得到新的引用。范围根据选择按钮的链接单元格M3为基础

4、，用 CHOOSE 函数对其进行选择置顶区域范围。后面的COUNTA 函数同样通过按钮链接选择组别，并对其计数。通过对名称的设置，就能很好的为图表服务，动态的体现数据的意义。当然也可以通过其他函数来实现同样的效果，本文下面有此图表应用附件，如有兴趣欢迎交流。多坐标轴图表让数据更生动在 Excel 中，我们常常用到图表。因为它可以直观的让数据更生动，更容易让人记住。直接生成的图表往往生硬，不合我们想表达的内容。有时候就需要做点小改变了，本博文简单的介绍下多坐标轴的妙用。让数据更生动。数据源如下：直接根据数据源创建出来的图表如下。可见1 处显示的内容根本无法表达AB组的对比情况，因此点击2处的“切

5、换行/列”。得到图表如下，但可以看到由于只有一个纵坐标轴，发货次数 明细超越 发货延时 很多，无法显示延时的真实情况。故此选中图表中发货延时AB组数据，然后按键盘“Ctrl+1”弹出“设置数据系列格式”窗口。选中“系列选项”，分别对主次坐标 进行“分隔”、“无间距”进行设置。设置完成后得到下图，AB小组的发货次数及发货延时对比图表就这样完成了。如果觉得图表有点单调的话，那你可以选择你喜欢的“图表样式”、“设置图表区格式”进行美化，最终得到下面的图表。统计时间出现次数某表内有电话拨打时间，需要统计8:00 至 11:59、12:00 至 16:59、17:00 至 7:59 这三个时间段的拨打次

6、数填入 D2至 D4单元格内。此时我们B2单元格，输入公式：=HOUR(A2)。然后下拉填充至单元格A15，建立辅助列。然后在 D2中输入公式：=COUNTIF($B$2:$B$15,<12)-COUNTIF($B$2:$B$15,<7)8:00 至 11:59 12 点前的拨打次数减去小于7 点的拨打次数，得到6 次。D3中输入公式：=COUNTIF($B$2:$B$15,<17)-COUNTIF($B$2:$B$15,<12)12:00 至 16:59 17 点前拨打的次数减去小于12 点的次数，得到5 次。D4中输入公式：=COUNTIF($B$2:$B$15,&

7、lt;24)-COUNTIF($B$2:$B$15,<17)+COUNTIF($B$2:$B$15,<8)17:00 至 7:59 24 点前拨打的次数减去小于17 点的次数，加上0 点至 7 点拨打的次数，得到3 次。vlookup 函数简单应用“合并计费”2011-01-24 00:23:29标签：vlookupexcel用户原创作品，允许转载，转载时请务必以超链接形式标明文章原始出处、作者信息和本声明。否则将追究法律责任。http:/ excel 报表，含每月出账用户。如图，每个月用户不同。需要将用户合计入“合计”表内按每月排列。这里先将用户名全部复制在“总计”表“用户名”下

8、，然后选择“数据”选项卡中“数据工具”，“删除重复项”。得到所有出账用户名。然后使用vlookup 函数在 B2单元格中输入“=VLOOKUP($A2,1月!A1:B4,2,FALSE)”，在 C2单元格中输入“=VLOOKUP($A2,2月!A1:B4,2,FALSE)”，在 D2单元格中输入“=VLOOKUP($A2,3月!A1:B4,2,FALSE)”，向下拖动得到每月出账金额。这里出现的#N/A，表示该函数中每月可用数值。因为有些用户在某个月无金额产生。那么为了便于计算及美观。我们稍稍修改一下：=IF(ISERROR(VLOOKUP($A2,1 月!A:B,2,FALSE),VLOOK

9、UP($A2,1月!A:B,2,FALSE)还有很多方法可用实现上述结果，甚至更简单。此处为了演示vlookup 函数而用，欢迎大家一起交流。Excel 中 index 和 match 函数的应用实例2010-10-04 20:41:27标签：函数 实例Excel indexmatch原创作品，允许转载，转载时请务必以超链接形式标明文章原始出处、作者信息和本声明。否则将追究法律责任。http:/ 中常被用到的一种函数，本篇来介绍一下index 与 match 在实际工作中的应用实例。先看一下这个Excel 工作簿。要求：将“用户分析”工作表中机房名称 列中输入函数，向下拖动使其自动选择对应“号

10、段检索”工作表中备注 的机房名称。其中故障号码为“号段检索”表中起始、结束号段中的码号。因此这里需要利用index 与 match 函数来完成检索号段归属机房查询工作。想到了 index 与 match 函数了吧，可以先回顾一下。-INDEX-index 函数的意义：返回指定行列交叉处引用的单元格。公式：=index(reference,row_num,column_num,area_num)reference指的是要检索的范围；row_num指的是指定返回的行序号，如超出指定检索范围，返回错误值#REF!;column_num 指的是指定返回的列序号，如超出指定检索范围，返回错误值#REF!

11、;area_num 指的是返回该区域中行和列的交叉域。可省略，默认1。如小于1时返回错误值#VALUE!-MATCH-match 函数的意义：返回指定方式下查找指定查找值（可以是数字、文本或逻辑值）在查找范围1 行或 1列的位置。公式：=match(lookup_value,lookup_array,match_type)lookup_value指指定查找值；lookup_array指的是 1 行或 1 列的被查找连续单元格区域。match_type 指的是查找方式，1 或省略指查找小于或等于lookup_value的最大值，lookup_array必须为升序排列，否则无法得到正确结果。0 指

12、查找等于lookup_value的第一个数值，如果不是第一个数值则返回#N/A-1 指查找大于或等于lookup_value的最小值，lookup_array必须为降序，否则无法得到正确结果。-那么在这里是用match 函数来定位“用户分析”表中故障号码 在“号段检索”起始号段 或结束号段 的所在行序号。如下图：=MATCH(用户分析!K2,号段检索!B:B,1)。但是为什么检索出来的行号会是错误值呢？别忘了，match_type 为-1 时，lookup_array必须为升序排列，也就是“号段检索”表中起始号段 应按升序排序。OK，这次查询正确，也可以抽一个验证一下无误即可。最后 index

13、 函数利用match 定位的行序号，来引用“号段检索”表中该行序号所在备注 的机房名称。输入公式如下：INDEX(号段检索!A:G,MATCH(用户分析!K2,号段检索!B:B,1),6)。最后向下拖动公式，完成机房名称 检索。在实际应用中可能常用的是精确查找，但是如果match 函数需要应用-1 和 1 时，特别要注意lookup_array的排序问题。Excel 中多条件查询函数公式2010-07-25 12:34:12标签：函数 查询Excel 公式条件收集一下，以备不时只需。=VLOOKUP(G2&H2,IF(1,0,A2:A7&C2:C7,B2:B7),2,)=VLOOKUP($G$

14、2&$H$2,CHOOSE(1,2,3,$A$2:$A$7&$C$2:$C$7,$B$2:$B$7,$D$2:$D$7),COLUMN(B1),)=VLOOKUP(H2,IF(A2:A7=G2,C2:D7,0),2,)=HLOOKUP(G2&H2,IF(1;0,TRANSPOSE(A2:A7&C2:C7),TRANSPOSE(B2:B7),2,)=HLOOKUP($G2&$H2,CHOOSE(1;2;3,TRANSPOSE($A2:$A7&$C2:$C7),TRANSPOSE($B2:$B7),TRANSPOSE($D2:$D7),COLUMN(B1),)=HLOOKUP(H2,TRANSPO

15、SE(IF(A2:A7=G2,C2:D7,0),2,)=LOOKUP(MATCH(G2&H2,A2:A7&C2:C7,),ROW(1:6),B2:B7)=LOOKUP(1,0/(A2:A7&C2:C7=G2&H2),B2:B7)=LOOKUP(2,1/(A2:A7&C2:C7=G2&H2),B2:B7)=LOOKUP(2,1/(A2:A7=G2)*(C2:C7=H2),B2:B7)=INDEX(B2:B7,MATCH(G2&H2,A2:A7&C2:C7,)=INDEX(B2:B7,MATCH(1,(A2:A7=G2)*(C2:C7=H2),)=INDEX(B2:B7,MATCH(TRUE,A2

16、:A7&C2:C7=G2&H2,)=INDEX(B2:B7,MAX(IF(A2:A7&C2:C7=G2&H2,ROW(1:6)=CHOOSE(MATCH(G2&H2,A2:A7&C2:C7,),B2,B3,B4,B5,B6,B7)=CHOOSE(MATCH(1,(A2:A7=G2)*(C2:C7=H2),),B2,B3,B4,B5,B6,B7)=CHOOSE(MATCH(TRUE,A2:A7&C2:C7=G2&H2,),B2,B3,B4,B5,B6,B7)=CHOOSE(MAX(IF(A2:A7&C2:C7=G2&H2,ROW(1:6),B2,B3,B4,B5,B6,B7)利用 Excel 函

17、数偷懒2010-04-21 23:19:35标签：函数 ExcelOffice2010偷懒最近真的很忙，那些远程工单平台的系统非常的老，可能有 8 年了吧。但神奇的是它们的生命力异常强大，至今依然在服役。虽然它们老了，许多事情已经跟不上时代也力不从心了，但是工作还是要做的。要不它们怎么来养活这个公司大家庭呢。所以很多时候许多数据都需要人工来统计，这个是非常可怕的事情。为了不让大家陷入人工手动统计工作的汪洋大海中，我不得不找了个函数使其中一个需要手动查询地址并按地址进行分类的Excel 表格变为自动。这个表格我隐藏和摘取了部分数据。需要填写的就是根据“联系地址”然后再填写每个地址所在的公司划分的

18、“区域”，黄色部分。这些表格数据原本应该由系统完成，可惜系统玩不转，没有数据库维护人员还是什么其他原因。我们只能完全采用手工输入的方式，费时又费力，实在是个头痛的工作。如果我们只是抱怨，那痛苦的依然还是自己。怎么办呢？哎，人是要学会偷懒的啦。函数是死的但人是活的。光会一个个死记硬背的函数是不行的，要学会联想。找到每个所需数据的特点性格，然后让其对号入座。区域的划分主要是根据具体的路名，这个路名属于哪个区域就是哪个区域。在这个表格中“博兴路、沪东新村、杨高北路、加太路、浙桥路、金新路、川桥路、新金桥”这些是我们需要的重点内容。因此需要采用函数MID来找出这些路名。如下图，S列的内容就是根据MID

19、（L2，5，3）得出的。最后摘取的是3 个字符，所以“杨高北路”得出的是“杨高北”，不过不影响我们最终需要的结果。有了 S列做辅助我们就成功了一小半。如下图所示，还需要一个单元格做所有路名的集中存放点。并且将路名有意识的按照区域顺序进行排放，另外还对于不同区域的路名进行了字体设置。这样我们就能够更容易的区分他们了。我们将绿色为“A区域”，红色为“B区域”，黑色为“C区域”。为什么这样做呢？呵呵，相信有些朋友已经猜到了。首先在T 列中用 SEARCH（S2，$U$1)函数让刚才S列摘取的路名对U1单元格进行搜索，将得出S列中路名在U1中的字符位置。最后根据所在字符的位置来判断属于哪个区域。下图中

20、T列得到的就是该结果。然后我们将辅助的S列、T 列中的内容带入IF 函数中 IF(T28,T216,C区域,未知区域)。测试一下，果然需要的区域名称就可以自动得出了。为了是页面更加整洁，最后我们可以将辅助列中的内容带入最终的IF 函数中，IF(SEARCH(MID(L2,5,3),$U$1)8,SEARCH(MID(L2,5,3),$U$1)16,C区域,未知区域)。辅助列也完成了它们的使命，可以删除了。如下图最终“区域”内容就会自动根据“联系地址”进行更改。免去了我们手动输入的苦恼。Excel 横向查找函数2010-03-15 16:04:53标签：函数 Excel原创作品，允许转载，转载时

21、请务必以超链接形式标明文章原始出处、作者信息和本声明。否则将追究法律责任。http:/ 中应用较多，收集了一部分做个记录。欢迎补充。呵呵。Excel 动态图表应用2010-02-02 00:23:22标签：ExcelOffice2010原创作品，允许转载，转载时请务必以超链接形式标明文章原始出处、作者信息和本声明。否则将追究法律责任。http:/ 来展现动态图表。本例依然采用 Excel2010beta。数据内容如下：最终我们利用窗口控件达到的效果如下：首先要将横向的“第一周”,“第四周”转换成纵向的数据，便于之后数据引用。这里用到的函数是=TRANSPOSE(B1:E1)，输入按Ctrl+S

22、hift+Enter完成转制。第二步，“插入”图表，并适当调整位置，为该图表创建标题。“开发工具”选项卡“插入”“选项按钮”和“组合框”窗体控件。将这两个窗体控件放置图表适当位置，并对选项按钮重新命名。然后在“公式”选项卡中选择“名称管理器”来新建之后将要使用的各个自定义名称。创建以下名称，并在引用位置内输入各自定义名称的定义。设定名称管理器完成后，在图表中选择“选项按钮”右键“设置控件格式”。选择“控制”，在单元格链接内输入$H$1，两个“选项按钮”都做如此操作。右键“组合框”控件后，选择“控制”在数据源区域内输入“下拉列表”名称，并在单元格链接内输入$H$2。现在你就可以根据控件选择具体“

23、区域”或是“时间”了。此时数据依然没有变化，因为我们没有针对图表的标签和系列进行定义。接下来要使图表动起来，那么我们就用在图表中使用前面设定的自定义名称了。选中图表，右键，“选择数据”，弹出“选择数据源”菜单。对其中“系列”和“标签”进行编辑。在其中系列中输入“=动态图表 _2.xlsx!系列”，在轴标签中输入“=动态图表 _2.xlsx!标签”，确定。然后修改一下前面“选择数据源”菜单中的其他系列选项，将不需要的删除。点击确定后至此真正的动态图表就完成了。Excel 伪动态图表应用2010-01-28 15:30:17标签：ExcelOffice2010原创作品，允许转载，转载时请务必以超链

24、接形式标明文章原始出处、作者信息和本声明。否则将追究法律责任。http:/ 版本是 Excel2010beta。以下是需要制作图表的源数据：选择 A9 单元格，然后选择“数据”选项卡，点击其中“数据有效性”。在弹出“数据有效性”窗口中“设置”，在“允许”下拉菜单中选择“序列”。然后在“来源”中输入需要选择的数据单元格。确定后效果如下：在 A11 中输入函数“=MATCH(A9,A2:A4,0)”，按顺序，选择张三，显示“1”；李四，“2”,将数据标题复制到A12：D12，便于后面制作图表的时候输入标题。当然也可以不输，在完成图表后直接引用 B1：E1也可。在 A13 单元格输入函数“=INDEX(B2:E4,$A$11,1)”,拖动复制A13至 D13 拖动后显示数据如下，与源数据相同。然后选择“插入”选项卡，选择图表类型“柱形图”。最终显示效果如下，将图表拖动覆盖在数据表上。图表也会根据A9 单元格中的名称变化，这样简易的动态图表就完成了。本文出自“Kirin的茶水间”博客，请务必保留此出处http:/