Excel中级培训 .ppt

1、Excel 2003 使用（中级）一、数据处理数据的排序1.排序原则排序关键字：指排序依照的数据字段名称。排序关键字作为排序的依据，也称为排序的键值。排序的方式分为升序和降序。n 数字按19位升序，从最小的负数到最大的正数进行排序。n 日期为数字类型按折合天数的数值排序，即从最远日期到最近日期为升序。n 文本字符，先排数字文本，再排符号文本，接着排英文字符，最后排中文字符。系统默认排序不分全角/半角字符和大小写字符。n 逻辑值按其字符串拼写排序，先“FALSE”再“TRUE”为升序。n 公式按其计算结果排序。n 空格始终排在最后。2.排序选择需要排序的区域（注意选择排序数据及对应标题名）；选择

2、“数据”主菜单下的“排序”菜单命令；弹出“排序”对话框，在其中设置各关键字、排序方向及标题行；单击“确定”按钮，完成排序设置。例：将年收入按部门、职称、年收入进行排序。其中年收入为降序。选择工作表中所有数据 单击“数据”菜单中的“排序”菜单项 在“主要关键字”中选择“所在部门”在“次要关键字”中选择“职称”在“第三关键字”中选择“年收入”选择“降序”单击“确定”按钮数据的筛选o 自动筛选利用表格数据字段名设置筛选条件，进行筛选显示记录。自动筛选只能针对一个字段进行筛选。筛选步骤：选择“数据”主菜单中“筛选”菜单下的“自动筛选”菜单命令；工作表的选中区域中的字段名称旁边显示“筛选条件选择”按钮，

3、在“自动筛选”菜单命令前出现“”标记。筛选条件选择按钮 选择筛选条件产生字段旁的“筛选条件选择”按钮，打开筛选条件框，选择需要条件，多次选择筛选条件可以查看不同的筛选结果。再次选择“数据”主菜单中“筛选”菜单下的“自动筛选”命令，取消“自动筛选”菜单命令前的“”标记，工作表中数据保留筛选后的记录顺序。例：建立年收入数据的自动筛选 选择工作表所有数据 单击“数据”菜单的“筛选”菜单项 选择“自动筛选”菜单项 在工作表的标题栏中单击某个列标题旁的按钮，查看不同的筛选结果 单击“年收入”列标题栏，选择“自动义”在对话框中输入筛选条件“小于等于 40000”分类汇总o 分类汇总的前提条件先排序后汇总。

4、即必须先按照分类字段进行排序，针对排序后数据记录进行分类汇总。选择工作表的分类汇总数据区域（已经按分类字段排序）；选择“数据”主菜单中“分类汇总”菜单命令，在弹出的“分类汇总”对话框中，选择分类字段、汇总方式、汇总项等；单击“确定”按钮确认，生成分类汇总记录，其中“+”标记表示数据记录处于折叠状态，“-”标记表示数据记录处于展开状态。例：建立年收入的分部门汇总信息。选择工作表所有数据 选择“数据”菜单中的“分类汇总”菜单项 在“分类字段”中选择“所在部门”在“汇总方式”中选择“求和”在“选定汇总项”中选择“年收入”单击“确定”按钮 在工作表中查看汇总数据改进：相同部门只有一个汇总数据。二、公式

5、与函数单元格引用 引用的作用在于表示工作表上的单元格或区域，并指明公式中所使用的数据的位置。通过引用，可以在公式中使用工作表不同部分的数据，或者在多个公式中使用同一个单元格的数值。还可以引用同一个工作簿中不同工作表上的单元格和其他工作簿中的数据。引用不同工作簿中的单元格称为链接。根据公式中单元格的引用凡是不同可分为绝对引用、相对引用、混合引用以及三维引用。1.相对引用 如果在公式中使用相对引用，当复制此公式时，目标单元格公式中的引用会根据目标单元格和原单元格的相对位移而自动产生变化。Excel 2003系统默认，所有新创建的公式均使用相对引用。如：将单元格A6中的相对引用公式“=SUM（A1：

6、A5）”复制到单元格B8中，则目标单元格中公式为“=SUM（B3：B7）”2.绝对引用 无论公式填充到任何位置，公式本身的应用指向一个绝对的单元格或区域。Excel中将绝对引用的行号或列标前加入“”。如：将单元格中的绝对引用公式“=SUM（$A$1：$A$5）”复制到单元格B8中目标单元格中的公式仍为“=SUM（$A$1：$A$5）”3.混合引用 在公式中既使用了相对引用，又使用了绝对引用，进行当前复制时，绝对引用部分保持不变，相对引用部分随单元格位置的变化而变化。如：将单元格A6中的公式“SUM（A$1：A$5）”复制到单元格B8中，目标单元格中的公式为“SUM（B$1：B$5）”3.三维引

7、用 在同一工作簿中引用不同工作表中单元格或区域中的数据。三维引用的一般格式为：工作表名称！单元格或区域3.三维引用三维引用创建方法：选择需要设置三维引用公式的单元格；键入“=”字符表示公式开始，公式中非三维引用部分可以直接输入或选择输入；输入三维引用部分，首先，单击选择切换到三维引用所需要的工作表（单击选择该工作表标签即可）；输入完成后，按“Enter”键或单击选择“输入”按钮即可。4.多维引用在其他工作簿中引用指定工作表中单元格或区域中的数据。多维引用的一般格式为:工作簿文件名.xls工作表名称！单元格或区域5.使用标志和名字使用表头和第一列的名称来引用单元格。可以在公式和函数中使用标志和名

8、字。单击“工具”菜单栏中的“选项”菜单项 单击“重新计算”选项卡 在“工具簿选项”区下，选择“接受公式标志”例：求年收入的平均值 选择指定为年收入的区域 选择“插入”菜单中的“名称”选择“定义”选项 单击“添加”在F29单元格中输入公式”=average(年收入)”例：计算“市场营销”部的年总收入少于40000的人数。选择“市场营销”部的年收入单元格 在工作表的“名称框”中输入“市场营销部年收入”在F29单元格中输入公式“=COUNTIF(市场营销部年收入,”60,合格,不合格)2.多层嵌套函数的应用=IF(F660,IF(AND(F690),“优秀”,“合格”),“不合格”)o COUNTI

9、F根据条件计算值。COUNTIF(range,criteria)。其中Range为需要计算其中满足条件的单元格数目的单元格区域。Criteria确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。例：希望计算销售人员的订单数，然后汇总每个销售人员的销售额，并且根据总发货量决定每次销售应获得的奖金。o 首先建立一个按照销售人员汇总的表单样式，如图所示。然后分别计算订单数、订单总额、销售奖金。n 订单数 用COUNTIF计算销售人员的订单数。以销售人员ANNIE的订单数公式为例。公式：=COUNTIF($C$2:$C$13,A17)n 订单总额用SUMIF汇总每个销售人员的销售额。以

10、销售人员ANNIE的订单总额公式为例。公式：=SUMIF($C$2:$C$13,A17,$B$2:$B$13)n 销售奖金用IF根据订单总额决定每次销售应获得的奖金。n 假定公司的销售奖金规则为当订单总额超过5万元时，奖励幅度为百分之十五，否则为百分之十。根据这一规则仍以销售人员ANNIE为例说明。公式为：=IF(C1750000,10%,15%)*C17文本函数大小写转换o LOWERo UPPERo PROPER文本函数字符串截取函数o LEFT函数LEFT(text,num_chars)。o MID函数MID(text,start_num,num_chars)。o RIGHT函数RIGH

11、T(text,num_chars)。文本函数去除字符串的空白o TRIM(text)。Trim函数不会清除单词之间的单个空格，如果连这部分空格都需清除的话，建议使用替换功能。日期与时间函数取出当前系统时间/日期信息o 用于取出当前系统时间/日期信息的函数主要有NOW、TODAY。日期与时间函数取得日期/时间的部分字段值o 如果需要单独的年份、月份、日数或小时的数据时，可以使用HOUR、DAY、MONTH、YEAR函数直接从日期/时间中取出需要的数据。例：在下图所示的某公司人事资料表中，除了编号、员工姓名、身份证号码以及参加工作时间为手工添入外，其余各项均为用函数计算所得。1.自动从身份证号码中

12、提取出生年月、性别信息。2.自动从参加工作时间中提取工龄信息。所用函数oIF函数：根据逻辑表达式测试的结果，返回相应的值。IF（logical_test,value_if_true,value_if_false）oCONCATENATE：将若干个文字项合并至一个文字项中。CONCATENATE(text1,text2)oMID：从文本字符串中指定的起始位置起，返回指定长度的字符。MID(text,start_num,num_chars)oTODAY：返回计算机系统内部的当前日期。TODAY（）oDATEDIF：计算两个日期之间的天数、月数或年数。DATEDIF(start_date,end_d

13、ate,unit)oVALUE：将代表数字的文字串转换成数字。VALUE(text)oRIGHT：根据所指定的字符数返回文本串中最后一个或多个字符。RIGHT(text,num_chars)oINT：返回实数舍入后的整数值。INT(number)1.根据身份证号码求性别=IF(VALUE(RIGHT(E4,3)/2=INT(VALUE(RIGHT(E4,3)/2),女,男)2.根据身份证号码求出生日期=CONCATENATE(19,MID(E4,7,2),/,MID(E4,9,2),/,MID(E4,11,2)3.根据参加工作时间求年资（即工龄）=CONCATENATE(DATEDIF(F4,

14、TODAY(),y),年,DATEDIF(F4,TODAY(),ym),个月)4.创建时间=创建日期：&TEXT(TODAY(),dd-mm-yyyy)统计函数用于求平均值的统计函数用于求平均值的统计函数 o AVERAGE AVERAGE(number1,number2,.)o TRIMMEAN TRIMMEAN(array,percent)统计函数用于求单元格个数的统计函数用于求单元格个数的统计函数 o COUNTCOUNT(value1,value2,.)统计函数求区域中数据的频率分布求区域中数据的频率分布 o FREQUENCY由于函数 FREQUENCY 返回一个数组，必须以数组公式

15、的形式输入。FREQUENCY(data_array,bins_array)统计函数用于求数据集的满足不同要求的数值的函数 o 求数据集的最大值MAX与最小值MINo 求数据集中第K个最大值LARGE与第k个最小值SMALL 语法形式为：函数(array,k)，其中Array为需要找到第 k 个最小值的数组或数字型数据区域。K为返回的数据在数组或数据区域里的位置（如果是LARGE为从大到小排，若为SMALL函数则从小到大排）。o 求数据集中的中位数MEDIAN 语法形式为MEDIAN(number1,number2,.)需要注意的是，如果参数集合中包含有偶数个数字，函数 MEDIAN 将返回位

16、于中间的两个数的平均值。o MODE函数用来返回在某一数组或数据区域中出现频率最多的数值。MODE(number1,number2,.)统计函数用来排位的函数 o 一个数值在一组数值中的排位的函数RANKRANK(number,ref,order)其中Number为需要找到排位的数字；Ref 为包含一组数字的数组或引用。Order为一数字用来指明排位的方式。如果 order 为 0 或省略，则Excel 将 ref 当作按降序排列的数据清单进行排位。如果 order 不为零，Microsoft Excel 将 ref 当作按升序排列的数据清单进行排位。三、Excel中宏的使用什么是宏o 宏是通

17、过一次单击就可以应用的命令集。它们几乎可以自动完成在程序中执行的任何操作，甚至还可以执行认为不可能的任务。o 宏可以节省时间，并可以扩展日常使用的程序的功能。使用宏可以自动执行重复的文档制作任务，简化繁冗的操作，还可以创建解决方案（例如，自动创建定期使用的文档）。o 宏宏：用于实现特定效果的代码段，拥有自己的名称 o VBA：即 Visual Basic for Applications，是宏的代码语言 o 模块模块：用于存储宏的容器，附加在工作簿中 录制宏 o 在“工具工具”菜单上单击“宏宏”，然后单击“录制新宏录制新宏”。o 在“录制宏录制宏”对话框中，命名该宏，为其指定一个工具栏或键盘快

18、捷方式以便访问，自定义保存宏的位置并添加对宏的描述以便日后参考。o重命名为宏自动分配的数字名称。宏名称可以包括字母和数字，但不能包含空格。默认情况下，录制的新宏保存在名为 Normal.dot 的全局模板中。还可以将宏保存在活动文档或模板中，或保存在其他自定义模板中。o为宏指定访问方式：通过工具栏或通过键盘快捷方式进行访问。如果跳过此部分，还可以在创建该宏后的任何时间执行这些分配。宏描述包括录制宏的日期和录制宏的用户的名称。可以根据需要编辑此描述。o 单击“确定”后，将打开一个包含以下两个按钮的小工具栏：“停止录制停止录制”和“暂停暂停录制录制”。编写宏编写宏o 在“工具工具”菜单上单击“宏宏

19、”，然后单击“宏宏”。从“宏名”列表中选择已录制的宏，然后单击“编辑编辑”。宏安全性 o 在“工具工具”菜单上单击“宏宏”，然后单击“安全性安全性”。选择您需要的安全级别，然后单击“确定确定”。注意，建议不要把安全性设置为“低”。删除宏o 打开含有要删除的宏 的工作簿。o 在“工具”菜单上，指向“宏”，再单击“宏”。o 在“位置”列表中，单击“当前工作簿”。o 在“宏名”框中，单击要删除的宏的名称。o 单击“删除”。编辑宏o 将安全级设置为“中”或“低”。操作方法o 在“工具”菜单上，单击“选项”。o 单击“安全性”选项卡。o 在“宏安全性”之下，单击“宏安全性”。o 单击“安全级”选项卡，再

20、选择所要使用的安全级。o 在“工具”菜单上，指向“宏”，再单击“宏”。o 在“宏名”框中，输入宏的名称。o 单击“编辑”。宏示例：货币样式设置o 对表格中的货币样式进行设置。1.选择一个单元格；2.录制新宏；宏示例：求个税o 给出收入计算应纳税金额；o 根据应纳税金额倒推出收入。o 自定义函数：=wtax(address,800,num)其中：o Address为单元格地址，该地址包含收入或纳税金额；o 800为纳税起始金额；o Num为0时表示从收入计算纳税金额，为1时表示从纳税金额倒推出收入；o 打开工具宏 Visul Basic编辑器；o 在“Visul Basic编辑器”中选择插入 模

21、块；o 在代码窗口中输入代码。Function Wtax(x As Variant,z,y As Integer)If y=1 Then If(x-z)0 Then Wtax=0 Else Select Case(x-z)Case Is=500 Wtax=(x-z)*0.05Case Is=2000 Wtax=(x-z)*0.1-25Case Is=5000 Wtax=(x-z)*0.15-125Case Is=20000 Wtax=(x-z)*0.2-375Case Is=40000 Wtax=(x-z)*0.25-1375Case Is=60000 Wtax=(x-z)*0.3-3375E

22、nd Select End IfElseIf y=2 Then If x=0 Then Wtax=0 Else Select Case x Case Is=25 Wtax=x/0.05+z Case Is=175 Wtax=(x+25)/0.1+z Case Is=625 Wtax=(x+125)/0.15+z Case Is=3625 Wtax=(x+375)/0.2+z Case Is=8625 Wtax=(x+1375)/0.25+z Case Is=1 Theno If Int(X)=X Or Round(X,2)=Int(X)Theno JEZH=Application.Worksh

23、eetFunction.Text(Int(X),DBNUM2)&元o ElseIf Int(X*10)=X*10 Or Int(X*10)=Round(X,2)*10 Theno JEZH=Application.WorksheetFunction.Text(Int(X),DBNUM2)&元&Application.WorksheetFunction.Text(Right(Round(X,2),1),DBNUM2)&角o Elseo JEZH=Application.WorksheetFunction.Text(Int(X),DBNUM2)&元&Application.WorksheetFun

24、ction.Text(Left(Right(Round(X,2),2),1),DBNUM2)&角&Application.WorksheetFunction.Text(Right(Round(X,2),1),DBNUM2)&分o End IfoElseIf X=0 Theno JEZH=Application.WorksheetFunction.Text(Int(X),DBNUM2)&元ElseIf X 0 Theno If Int(X*10)=X*10 Theno JEZH=Application.WorksheetFunction.Text(Right(X,1),DBNUM2)&角o El

25、seo JEZH=Application.WorksheetFunction.Text(Left(Right(Round(X,2),2),1),DBNUM2)&角&Application.WorksheetFunction.Text(Right(Round(X,2),1),DBNUM2)&分o End IfoElse oIf Int(X)=X Or Round(Abs(X),2)=Int(Abs(X)Theno JEZH=负&Application.WorksheetFunction.Text(Int(Abs(X),DBNUM2)&元o ElseIf Int(X*10)=X*10 Theno

26、JEZH=负&Application.WorksheetFunction.Text(Int(Abs(X),DBNUM2)&元&Application.WorksheetFunction.Text(Right(X,1),DBNUM2)&角o Elseo JEZH=负&Application.WorksheetFunction.Text(Int(Abs(X),DBNUM2)&元&Application.WorksheetFunction.Text(Left(Right(Round(X,2),2),1),DBNUM2)&角&Application.WorksheetFunction.Text(Rig

27、ht(Round(X,2),1),DBNUM2)&分o End IfoEnd IfoEnd Function导出宏o 代码编辑完成后，退出“Visul Basic编辑器”，回到Excel界面；o 选择“另存为”按钮，为宏命名保存，类型为“Microsoft Excel 加载宏”；o 此宏的后缀名为.xla。导入 宏o 在工具 加载宏(I)；四、Word与Excel的联合使用Word中使用Excel的方法o 复制并粘贴工作表或图表到Word文档中o 将工作表或图表作为链接对象插入Word 文档中o 将工作表或图表作为嵌入对象插入Word 文档中链接与嵌入o 链接：用链接的方法使用Excel的工作

28、表或图表，其信息仍保存于源程序Excel中，无论何时编源文件内容，在Word中的Excel项将自动更新，又称动态链接。简单地说：这种方式使Word中的对象随Excel文档的改变而改变o 嵌入：该方式使用的工作表或图表，不会自动更新在Word中创建Excel图表链接对象o 在Word文档中创建Excel图表链接对象：先在Excel中建立图表；将图表选中复制；在Word中用“选择性粘贴”；在该对话框中选中“粘贴链接”；如图，可以选中“显示为图标”复选框，效果如图二在Word中创建Excel工作表的链接对象o 在Excel中选中要复制的单元格，并使用“复制”命令o 在Word中找到插入点，使用“选择

29、性粘贴”命令o 选中“粘贴链接”单选框o 注意：可选中“显示为图标”复选框，在Word中将只显示图标创建Excel 工作表或图表的嵌入对象o 方法与创建链接对象基本一致，唯一不同处是不选“粘贴链接”单选框，而选“粘贴”单选框，结果如图o 注意：此时不能选“显示为图标”复选框新建嵌入的Excel工作表或图表o在word中，插入点移至要插入工作表或图表位置o用“插入”菜单中的“对象”命令，打开对话框，单击“新建”选项卡o在“对象类型”框中选“Microsoft Excel工作表”或图表o也可选中“显示为图标”复选框，如图一o结果如图二在Excel工作表中插入Word 文档o 在Excel中新建Word文档：选中要插入Word文档的单元格；用“插入”菜单下的“对象”命令；可选中“显示为图标”按钮，如图一o 插入已存在的Word文档：打开“插入”菜单下的“对象”命令，然后单击“从文件创建”标签，选择要插入的文件，如图二将Word 表格复制到Excel工作表o 选中Word中的表格o 用“复制”命令，如图一o 切换到Excel中，选中目标单元格，用“粘贴”命令。结果如图二五、图表练习o 按照教师复制给大家的图表示例，在计算机上练习。o 其目的在于熟悉基本的图表制作方法后，如何设置图表的其他选项以满足实际需求。o 教师讲解和学院练习相结合，以学院练习为主。