实用Excel2007使用技巧―进阶篇

Excel2007使用技巧进阶篇FannyLin2013/9/2311•Excel2007图形与图表2•Excel2007公式与函数3•Excel2007自动化2目录形状图片SmartArt剪贴画艺术字3Excel2007图形与图表图形类别Excel2007图形与图表A.形状—分类4Excel2007图形与图表A.形状—建立/编辑5A.形状—组合/排序6Excel2007图形与图表A.形状—旋转/翻转7Excel2007图形与图表A.形状—对齐/分布8Excel2007图形与图表A.形状—显示/隐藏9Excel2007图形与图表B.艺术字—建立10Excel2007图形与图表C、SmartArt—建立11Excel2007图形与图表D、自定义剪贴画12Excel2007图形与图表利用Activex控件制作条形码13Excel2007图形与图表Excel2007图形与图表图表类别柱形图折线图饼图条形图面积图XY散点图曲面图圆环图气泡图雷达图股价图14Excel2007图形与图表A、筛选法动态图表15Excel2007图形与图表B、公式法动态图表16数据有效性设置Excel2007图形与图表B、公式法动态图表17复制VLOOKUP函数在B9：F9辅助单元格内，选择辅助列插入柱形图即完成公式动态图表的制作。Excel2007图形与图表18C、定义名称法动态图表设置控件格式Excel2007图形与图表C、定义名称法动态图表19定义名称Excel2007图形与图表C、定义名称法动态图表选取A1：F2插入一个二维柱形图20图表变图片21Excel2007图形与图表Excel2007公式与函数公式:是以等号引导通过运算符按照一定顺序组合进行数据运算处理的等式。主要由等号、函数、运算符、数字常量、名称和单元格引用等组成。22Excel2007公式与函数函数:是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算的功能模块。主要由等号、函数名称、参数、分隔符和左右括号等组成。23Excel2007公式与函数公式与函数的限制计算精度的限制：单元格最大可输入数值为9.99999999999999E+307•解决方案：将单元格数字先转化为文本格式，输入数据时在数据前加半角单引号函数参数限制：1、单元格函数最大参数数目受限。（内置函数为255）2、单元格最大允许嵌套层数为64层•解决方案1：将参数用逗号间隔并用括号包含形成合并区域，整体作为一个参数使用。•解决方案2：用名称代替函数24Excel2007公式与函数运算符分类引用算数文本比较25Excel2007公式与函数单元格引用分类绝对引用是指公式和函数中的位置是固定不变的且列字母和行数字前都加上”$”符例：=$A$1+$A$2相对引用指公式中的单元格位置将随着公式单元格的位置而改变。例：=A1+A2混合引用是指在一个单元格引用中,既有绝对引用,也有相对引用例：=A$1+A$2绝对相对混合26Excel2007公式与函数常用函数分类内置函数文本函数信息函数逻辑函数数学和三角函数日期和时间函数查找和引用函数统计函数财务函数工程函数多维数据集函数扩展函数宏表函数自定义函数27函数用途主要用于返回两数相除的余数，其结果的正负号与除数相同。函数语法MOD(被除数，除数)应用举例利用MOD函数生成循环序列除数不能为零，被除数与除数的商不能超过2的27次方28Excel2007公式与函数A、常用数学和三角函数—MOD函数函数用途主要用于将任意实数向下取整为最接近的整数。函数语法INT(实数)应用举例利用INT函数生成特殊要求的重复序列29Excel2007公式与函数A、常用数学和三角函数—INT函数函数用途主要用于计算两日期相差的年月日数。函数语法DATEDIF(起始日期，终止日期，函数返回值)应用举例利用DATEDIF函数计算时间差30Excel2007公式与函数B、常用日期和时间函数—DATEDIF函数函数用途主要用于计算两日期间的工作日天数。函数语法NETWORKDAYS(起始日期，终止日期，表示日期的序列号的数组常量)应用举例利用NETWORKDAYS函数计算考勤天数31Excel2007公式与函数B、常用日期和时间函数—NETWORKDAYS函数Excel2007公式与函数C、常用查找和引用函数—VLOOKUP函数函数用途主要用于搜索用户查找范围中列中满足条件的数据并根据指定列返回对应值。函数语法VLOOKUP(索引值，数据区域，列序号，查找方式)应用举例利用VLOOKUP查找员工信息32Excel2007公式与函数C、常用查找和引用函数—INDIRECT函数函数用途主要用于根据指定字符串生成具体单元格引用。函数语法INDIRECT(单元格引用值，逻辑值)应用举例利用INDIRECT函数动态统计工作量33Excel2007公式与函数D、常用统计函数—COUNTIF函数函数用途主要用于数据表中的数值进行计数。函数语法COUNTIF(条件范围，统计条件）应用举例利用COUNTIF查询重复信息条件范围只能是单元格引用，统计条件可以使用引用、字符串表达式或数组，同时支持通配符（“?”和“*”）的统计34Excel2007公式与函数D、常用统计函数—SUMIF函数函数用途主要用于主要用于单个条件的求和。函数语法SUMIF（条件范围，统计条件，求和范围）应用举例利用SUMIF汇总指定产品月销量条件范围和求和范围只能是单元格引用,统计条件可以使用引用和数组,求和范围可以简写为区域的左上角单元格引用35Excel2007公式与函数E、常用工程函数—DELTA&GESTEP函数函数用途DELTA主要用于测试两个数值是否相等进行数据筛选。GESTEP主要通过判断参数与阀值间的关系筛选数值。函数语法DELTA（参数1，参数2）GESTEP（参数，阀值）应用举例利用DELTA&GESTEP筛选数据36Excel2007公式与函数数值不同进制间的相互转换函数用途主要用于二进制、八进制、十进制、十六进制之间的数值转换。函数语法函数（待转换数值，所需字符数）函数类别函数类别二进制八进制十进制十六进制二进制NABIN2OCTBIN2DECBIN2HEX八进制OCT2BINNAOCT2DECOCT2HEX十进制DEC2BINDEC2OCTNADEC2HEX十六进制HEX2BINHEX20CTHEX2DECNA37Excel2007公式与函数数值单位间相互转换函数用途：主要用于数字度量系统的相互转换。函数语法:CONVERT(参数,转换起始单位,转换终止单位）部分单位参数:重量和质量Unit距离Unit时间Unit压强Unit力Unit克g米m年yr帕斯卡Pa牛顿N斯勒格sg法定哩mi日day大气压atm达因dyn磅（常衡制）lbm海里Nmi小时hr毫米汞柱mmHg磅力lbfU（原子质量单位）u英寸in分钟mn盎司（常衡制）ozm英尺ft秒sec码yd埃ang皮卡（1/72英寸）Pica能量Unit功率Unit磁Unit温度Unit液体度量Unit焦耳J马力HP特斯拉T摄氏度C茶匙tsp尔格e瓦特W高斯ga华氏度F汤匙tbs热力学卡c开尔文度K液量盎司ozIT卡cal杯cup电子伏eVU.S.品脱pt马力-小时HPhU.K.品脱uk_pt瓦特-小时Wh夸脱qt英尺磅flb加仑gal38Excel2007自动化VBA概念VBA全称是VisualBasicforApplication是基于Excel的一种宏语言，主要用于扩展Windows的应用程式功能。VBA的应用场景自动执行可重复操作进行复杂的数据分析对比生成报表和图表个性化用户界面Office组件协同工作Excel的二次开发初识VBA39Excel2007自动化•VBE全称为VisualBasicEditor是指Excel以及其它Offices组件中集成的VBA代码的编辑器。VBA与VBE•快捷键ALT+F11•单击【开发工具】选项卡的【VisualBasic】按钮•右键单击工作表标签，在下拉菜单中选择【查看代码】命令如何打开VBEExcel2007自动化VBA的编程环境40Excel2007自动化•【开发工具】选项卡中【代码】组的【录制宏】按钮•【视图】选项卡中【宏】下拉菜单【录制宏】按钮•Excel窗口状态栏上的【录制宏】按钮录制宏•快捷键Ctrl+任意字母（可在【录制新宏】窗口中进行任意设置，注意宏的快捷键优先于excel中其它快捷键）•宏窗口•快速工具访问栏运行宏关于宏41Excel2007自动化Excel2007自动化VBA编程基础——常量与变量变量用于存贮程序运行过程中需要临时保存的值和对象优点：在编辑代码时无须声明，可直接使用缺点：如直接使用占较大存贮空间且代码运行效率交差常量用于存贮程序运行过程中的固定信息优点：增加程序可读性，代码维护升级更加容易缺点：常量值在程序运行期间不能发生改变42•用于执行指定操作不返回运行结果•以Sub开始EndSub结束Sub过程•用于执行指定操作返回代码的运行结果•以Function开始EndFunction结束Function过程•用于设置和获取自定义对象属性的值及引用其它对象Property过程43Excel2007自动化VBA编程基础——运算运算是可以执行的语句序列单元Excel2007自动化VBA编程基础——运算符44等于=不等于≠大于小于小于且等于≥大于且等于≦指数运算^负数-求模运算Mod加减乘除+-*/字符连接&NotAndOrXorEqvImp算数比较逻辑Excel2007自动化VBA编程基础—语句结构之条件语句•If条件Then语句顺序执行语法格式•If条件Then•语句1•Else•语句2•EndIf非顺序执行语法格式45Excel2007自动化VBA编程基础—语句结构之循环语句•For变量=初值To终值•语句•NextForNext语法格式•ForEach变量In对象集合•语句•NextForEach语法格式46Excel2007自动化VBA编程基础—语句结构之With语句47•With对象•语句•EndWith语法格式•使用该结构语句仅能在一个单一对象或一个用户定义类型上执行，语句内容仅能在选定的对象上执行。注意Excel2007自动化VBA编程基础—语句结构之输入输出语句48•MsgBox(Prompt[,buttons][,title][,helpfile,context]语法格式•InputBox(Prompt[,title][,default][,xpos][,ypos][,helpfile,context]语法格式•输出代码为最终运行结果•显示对话框提醒用户•在对话框中显示提示信息，待用户选择并执行相应操作•在执行代码时显示某变量值，简化代码输出语句适用情况Q&A49