EXCEL(6)
中文Excel2007电子表格制作实训教程(电子教程)第六章
6.1 数据清单 6.2 排序数据
6.3 筛选数据
6.4 分类汇总数据
6.5 典型实例——制作销售报告
小结 过关练习六
6.1 数 据 清 单
所谓数据清单，就是包含有关数据的一系列工作表数据行。
它具备数据库的多种管理功能，是Excel中常用的工具。数据清单 中的行相当于数据库中的记录，行标题相当于记录名。数据清单 中的列相当于数据库中的字段，列标题相当于数据库中的字段 名称。
图6.3.3 筛选结果
2．筛选数字 如果用户要对工作表中的数字进行筛选，可按照以下操作步 骤进行： （1）选择包含数值数据的单元格区域。
（2）在“开始”选项卡中的“编辑”选项区中单击“排序和
筛选”按钮，在弹出的下拉菜单中选择“筛选”命令，此时，被 选定数据区域标题栏下方将显示箭头。 （3）单击箭头，弹出其下拉菜单，如图6.3.4所示。 （4）取消选中“（全选）”复选框，然后选择要作为筛选依
（3）如果要将标志和其他数据分开，应使用单元格边框
（而不是空格或短画线），在标志行下插入一条直线。 3．行和列内容 （1）在设计数据清单时，应使用同一列中的各行有近似的 数据项。
（2）在单元格的开始处不要插入多余的空格，因为多余的 空格影响排序和查找。 （3）不要使用空白行将列标题和第一行数据分开。
被选定数据区域标题栏下方将显示箭头，如图6.3.1所示。
（3）单击箭头，弹出其下拉菜单，如图6.3.2所示。
图6.3.1 显示下拉箭头
图6.3.2 下拉菜单
（4）取消选中“(全选)”复选框，然后选择要作为筛选依据 的特定文本值。 （5）设置好后，单击“确定”按钮，效果如图6.3.3所示。
6.1.2 建立数据清单
Excel商务数据处理与分析 第6章 产品营销数据分析
26
6.3.1 计算各店铺的成交率和客单价
5.查看计算结果 6.复制公式
27
6.3.1 计算各店铺的成交率和客单价
7.设置单元格的数字格式 8.单击“展开”按钮
28
6.3.1 计算各店铺的成交率和客单价
29
9.设置数字格式
10.应用百分比样式后的效果
6.3.2 使用迷你图显示各店铺的销量 利用Excel进行数据分析时，除了使用最常见的图表外，还可以使用迷你图来展示。迷你图 是 Excel 单元格中的一种微型图表，通过它可以非常方便的对数据进行直观的展示。下面将在 “多店铺销售数据分析.xlsx”工作簿中进行迷你图的创建与编辑，其具体操作如下。
1.筛选数据 2.设置筛选条件
18
6.2.3 创建饼图查看库存金额
3.确定筛选条件 4.选择图表类型
19
6.2.3 创建饼图查看库存金额
5.设置数据标签 6.设置标签位置和包括内容
20
6.2.3 创建饼图查看库存金额
7.删除图例元素 8.调整图表大小和位置
21
6.2.3 创建饼图查看库存金额
35
11.创建其它迷你图
12.调整其它迷你图的效果
7.设置趋势线形状样式 8.添加趋势线
8
6.1.2 判断单款产品销售生命周期
9.设置趋势线 2 10.设置趋势线选项
9
6.1.2 判断单款产品销售生命周期
11.设置趋势线线型 12.选择图表区的填充颜色
10
6.1.2 判断单款产品销售生命周期
13.自定义填充颜色 14.填充颜色后的最终效果
11
5
结合自身的库存量，进行适当的补货，以减少缺货损失。下面将利用折线图来判断产品的销售
做网优必备-EXCEL使用说明(有图)
Excel使用说明2009-6-16目录一、Excel简介 (3)二、Excel 常用操作 (3)1、Shift+Ctrl+方向键 (3)2、快速选择每列或每行的首位和末尾单元格。
(4)3、CTRL+鼠标左键拖动（复制单元格内容到指定范围） (4)4、灵活使用状态栏 (6)5、筛选功能 (6)6、条件格式功能 (7)8、选择性粘贴 (8)9、数据分列功能 (9)10、冻结窗口功能 (9)11、自动换行功能 (10)12、剪切，粘贴命令的鼠标操作 (10)13、强制换行功能 (10)14、自动设定合适行高列宽 (11)15、查找及替换功能 (11)16、设置数据有效性 (11)17、排序功能 (12)18、合并单元格中的数据 (13)19、数据透视表 (14)三、Excel常用函数 (15)1、数学函数 (15)1) Sum函数 (15)2) Subtotal函数 (16)3) Sumif函数 (17)4) Average 函数 (17)5) Max函数 (18)6) Min函数 (18)7) ABS函数 (18)8) Hex2dec函数 (18)9) Dec2hex函数 (19)2、文本函数 (19)1) Lift函数 (19)2) Right函数 (19)3) Mid函数 (19)4) Substitute函数 (19)3、逻辑函数 (20)1) If函数 (20)2) And函数 (21)3) Or函数 (21)4、查找函数 (21)1) Vlookup函数 (21)四、Excel中插入图表 (22)五、Excel中录制宏 (26)六、Excel常用快捷键 (27)七、总结 (28)一、Excel简介Excel 在网络优化工作中起的作用，是不言而喻的。
比如利用Excel制作工参、分析话统指标、统计数据等等。
灵活的使用Excel可以极大的提高我们的工作效率，以下是我工作之余总结的Excel常用功能，希望对大家工作有所益处。
Excel 的6种逆向查询方法
Excel 的6种逆向查询方法
VLOOKUP ：（正常用法）
VLOOKUP (参数1，参数2，参数3，参数4)
语法解析代表
参数1代表找什么（所需要查找的值）
参数2代表哪里找（目标所在的查找区域）
参数3代表第几列（返回值在查找区域的第几列）
参数4代表精确还是模糊（0或1，默认为1）
逆向1：VLOOKUP + IF
代码：=VLOOKUP(E4,IF({1,0},B2:B11,A2:A11),2,0)
利用IF 函数，值为1 即TRUE 的时候返回B 列单元格，值为0 即FALSE 的时候返回A 列单元格，重新构造查找区域，也就是B 列“姓名”在前、A 列“班级”在后的新单元格区域。
逆向2：VLOOKUP + CHOOSE
代码：=VLOOKUP(E4,CHOOSE({1,2},B2:B10,A2:A10),2,0)
利用SHOOSE 函数，值为1 即TRUE 的时候返回B 列单元格，值为2 即FALSE 的时候返回A 列单元格，重新构造查找区域，也就是B 列“姓名”在前、A 列“班级”在后的新单元格区域。
逆向3： LOOKUP
代码：=LOOKUP(1,0/(E4=B2:B11),A2:A11)
逆向4：INDEX + MATCH
代码：=INDEX(A2:A11,MATCH(E4,B2:B11,0))逆向5：OFFSET + MATCH
代码：=OFFSET(A1,MATCH(E4,B2:B11,0),)逆向6：INDIRECT + MATCH
代码：=INDIRECT("A"&MATCH(E4,$B$2:$B$11,0)+1)。
Excel2010_6 图形化数据分析
点选要使用 的色阶项目
实例6：用"图标集"规则标示学生成绩
图标集规则有17种图标类型，可在各数据数据旁边附注旗帜、灯号或箭头等 图示。假设以三旗帜图示 为例：绿色旗帜表示较高值，黄色旗帜 表示中间值，红色旗帜表示较低值。 下面以学生成绩工作表进行示例：选取数据范围，按下设定格式化的条件 钮，执行『图标集』命令
选择要标示的格式
再执行『项目选取规则／低于平均值』命令，使用不同的格式来标示低于 平均值的项目，就可以清楚分辨出每一季大家的业绩好坏了：
分别列出每一季业绩高 于平均（标示为浅红色 填满与深红色文字）与 低于平均（标示为黄色 填满与深黄色文字）， 马上就一目了然
实例4：用“数据横条”规则标示业绩数据高低 资料横条会使用不同长度的色条，来显示数据数据，数字愈大色条愈长，反 之，数字愈小则色条愈短。继续以销售业绩工作表作为示例。请选取要查询 的范围，按下设定格式化的条件钮，执行『数据条』命令：
切换到插入页次
选择折线图
这里显示的是 我们之前选取 的单元格范围
按下此钮，设 定折线圈要放 在哪个单元格
这里即会显示出F3 单元格的绝对位置
按下此钮完整显示 建立走势图对话框
在工作表中点选F3 单元格
按下确定
F3单元格中便出现了此业务员一 年来的销售业绩变化.
调整走势图的格式 现在的折线图不太方便辨识，所以需要再加入一些标记，点选折线图所在 的储存格，便会自动切换到迷你图工具的设计页次，在功能区中看到折线 图相关的格式设定：
选择要标示的格式
列出第1季 的倒数3名
按确定
用"前100%“及"最后10%"规则标示前30%或最后30%的业绩
EXCEL操作 2-6章
6-43
（3）分析结果如下。
6-44
二、方差分析
• 例6-12 用Excel实现例6-7的计算过程。 • （1）作表。第一行中的数字表示小鸡序号。
6-45
（2）调出[方差分析：单因素方差分析]对话框， 如下填写。
6-46
（3）单击确定，可得方差分析结果。
6-47
例6-13 用Excel实现例6-9的计算过程。 （1）输入数据。
5
（3）计算频率。 在E2中输入公式“=D2/D$10*100”，然后将 该公式复制到E3：E9即可。鼠标放在单元格 右下角呈现实心十字时，向下拉动即可。
6
（4）计算向上累计频数。 在 F2 单元格中输入“ =D2 ”，在 E3 单元 格中输入公式“ =D3+F2 ”，再将公式复 制到F4：F9.
6-48
• （2）调出[方差分析：可重复双因素分析]对话框， 其填写如下：
6-49
• （3）单击确定。计算结果如下：
6-50
第一题
1、输入数据 按照书第30页输入原始数据，B、C、D列 为原始数据。年财政收入放在B2：B32单 元格区域。
51
C列为分组上限，需要在C7单元格计算原始数 据最大值，选中该单元格输入公式 “=MAX(B2:B32)” （2）选定D2：D9，输入公式 “=FREQUENCY(A2:A51,B2:B9)” 然后同时按crtl+shift+enter组合键，即可 计算出各组的频数。
6-41
• （5）根据计算结果，使用临界值规则或P-值 规则进行判断，检验统计量的观测值落在拒 绝域，因而拒绝u=150克的原假设。
6-42
例6-11 利用Excel求解例6-5的问题。 （1）输入数据，A、B列为原始输入数据。 （2）使用分析工具库中的[t检验：双样本等 方差假设]分析工具，调出该对话框。
ExcelVBA入门（6）-Worksheet对象常用方法事件
ExcelVBA⼊门（6）-Worksheet对象常⽤⽅法事件1. 激活⼯作表 ActiveDim ws As WorksheetSet ws = Application.WorkBooks(1).Worksheets(2)ws.Activate激活了第⼀个⼯作簿的Sheet22. 复制⼯作表 Copy([before], [after])将当前⼯作表复制⼀份, 名字为"当前⼯作表名字(2)"Dim ws As WorksheetSet ws = Application.ActiveWorkbook.Worksheets(1)ws.Copy after:=Worksheets(1)该代码将Sheet1的内容拷贝到新表Sheet1(2)如果不指定before或after, 同样会⽣成⼀个新表, 注意, before和after不能同时使⽤另外, ⼯作表的复制可以跨⼯作簿之间进⾏Dim wbSrc As WorkbookDim wbDes As WorkbookDim ws As WorksheetSet wbSrc = Application.WorkBooks(1)Set wbDes = Application.WorkBooks.AddSet ws = wbSrc.Worksheets(1)ws.Copy after:=wbDes.Worksheets(1)将当前⼯作表的内容复制到新⼯作簿的第2个⼯作表3. 将剪贴板的内容粘贴到⼯作表Paste([destination], [link])Dim ws As WorksheetSet ws = Application.ActiveWorkbook.ActiveSheetws.range("A1:A3").Copyws.Paste destination:=ws.range("F1:F3")先将A1:A3的内容复制到剪贴板, 然后利⽤Paste⽅法, 粘贴到F1:F3区域, 亦或者直接:ws.range("A1:A3").Copyws.Paste destination:=ws.range("F1")Worksheet事件:和Workbook的事件类似, 在"⼯程资源管理器"中, 双击⼀个⼯作表, 在右边代码区上⾯选择Worksheet, 然后再选择相应的事件选择⼀个事件会⾃动列出事件代码常⽤的操作⼯作表的⽅法1. 访问⼯作表两种⽅式: a. 根据索引号(从1开始) b.根据⼯作表名称Dim wb As WorkbookDim ws As WorksheetDim wsCount As IntegerDim i As IntegerDim sheetnames() As StringSet wb = Application.WorkBooks(1)wb.ActivatewsCount = wb.Worksheets.CountReDim sheetnames(1 To wsCount)PrintInfo "当前⼯作簿共包含" & CStr(wsCount) & "个⼯作表"For i = 1 To wsCountSet ws = wb.Worksheets(i)Debug.Print Space(5) & sheetnames(i) = NextDebug.PrintDebug.Print "使⽤Sheets集合按名称访问⼯作表"For i = 1 To wsCountSet ws = wb.Worksheets(sheetnames(i))Debug.Print Space(5) & NextSet ws = NothingSet wb = Nothing例⼦⽐较简单, 说明⼀下Space(5)的意思是五个空格, CStr()是把参数转换为字符串核⼼就是 Worksheets(1) 和Worksheets("Sheet1") 是等效的 (默认没有改⼯作表名字⽽且没有移动⼯作表顺序的情况下)另外在遍历⼯作表的时候使⽤的是Worksheets属性, 如果使⽤Sheets属性则需要判断⼯作表的类型是普通⼯作表还是图表⼯作表根据Type属性判断: If ws.Type = xlWorksheet Then2. 判断⼯作表是否存在判断⼯作表是否存在就是⽤指定的名称遍历所有⼯作表, 没什么难点Dim wb As WorkbookDim ws As WorksheetDim i As IntegerDim count As IntegerDim flag As BooleanDim findName As StringfindName = "Sheet7"Set wb = Application.ActiveWorkbookcount = wb.Worksheets.countflag = FalseFor i = 1 To countIf wb.Worksheets(i).name = findName Thenflag = TrueExit ForEnd IfNextIf flag ThenMsgBox "存在" & findNameElseMsgBox "不存在" & findNameEnd IfSet ws = NothingSet wb = Nothing这段代码不⽤解释了3.新建⼯作表 Application.ActiveWorkbook.Worksheets.Add([Before], [After], [Count], [Type]) As Object新建⼯作表和之前的新建⼯作簿类似Dim ws As WorksheetSet ws = Worksheets.AddDebug.Print 完整写法Set ws = Application.ActiveWorkbook.Worksheets.Add(before:=Worksheets(8), count:=2, Type:=xlWorksheet)意思是在第8个表前加⼊两个⼯作表同样的Before和After不能同时使⽤4. 重命名⼯作表直接设置⼯作表的name属性即可, 但是要先判断该名称是否已经存在, 否则会报错为了简单说明, 这⾥就不作判断了Dim ws As WorksheetSet ws = Application.WorkBooks(1).Worksheets(1) = "SheeT1"将"Sheet1"重命名为了"SheeT1"5. 移动⼯作表Dim wb As WorkbookDim ws As WorksheetSet wb = Application.WorkBooks(1)wb.ActivateSet ws = wb.Worksheets(1)ws.Move after:=ws.NextSet wb = NothingSet ws = Nothing道理和复制⼀样, ws.Move after:=ws.Next 将第⼀个⼯作表向后移动⼀次同理, 移动也可以跨⼯作簿进⾏Dim wbSrc As WorkbookDim wbDes As WorkbookDim ws As WorksheetSet wbSrc = Application.WorkBooks(1)Set wbDes = Application.WorkBooks.AddSet ws = wbSrc.Worksheets(1)ws.Move after:=wbDes.Worksheets(1)将当前⼯作簿的Sheet1 移动到新的⼯作簿的Sheet1后6. 删除⼯作表注意: 删除前请保存重要数据Dim wb As WorkbookDim ws As WorksheetDim sheetName As StringDim count As IntegerSet wb = Application.WorkBooks(1)Set ws = wb.Worksheets(1)sheetName = count = wb.Worksheets.countIf count > 1 ThenApplication.DisplayAlerts = Falsews.DeleteMsgBox "成功删除" & sheetName, vbOKOnly, "删除⼯作表"Application.DisplayAlerts = TrueElseMsgBox "⼯作表" & sheetName & "是⼯作簿的最后⼀张表, ⽆法删除", vbCritical, "删除⼯作表" End IfSet wb = NothingSet ws = Nothing。
中级职称考试计算机EXCEL实验(6)
一.在工作表“课程成绩表”中，在G列插入空列，字段名为“优良率”，按“优良率=80'以上人数/实考人数”填充该列；数据格式为“%”，保留小数点后二位参考答案：A.在“课程成绩表”中，选中G列中的任意一个单元格B.在“插入”菜单中，选择“列”菜单项C.在G1单元格输入“优良率”D.在G2单元格输入公式“（H2+I2）/C2”E.选中G2单元格，用填充柄复制满（G3：G125）F.选中（G2：G125）区域，用工具按钮设置格式为“%”，两位小数二.在“课程成绩表”中以“及格率”降序排序，及格率相同的以“平均分”降序排列，再相同者以“实考人数”多的排在前。
参考答案：A.在“课程成绩表”中，全部选中B.在菜单“数据”中选择“排序...”C.在“排序”对话框中，主要关键字选“及格率”，并选中其右面的“递减”单选钮D.第二关键字选“平均分”，并选中其右面的“递减”单选钮E.第三关键字选“实考人数”，并选中其右面的“递减”单选钮F.按“确定”按钮三.对排序后的“课程成绩表”筛选出“理工类”、实考人数>=150人的课程。
将筛选结果的课程名称复制到sheet1表的A列中参考答案：A.在“课程成绩表”中，选中任意一个有内容的单元格B.在二级菜单“数据/筛选”中选择“自动筛选”C.单击“专业类”右面的下拉按钮，选择“理工类”D.单击“实考人数”右面的下拉按钮，选择“自定义...”E.在“自定义”对话框中，在第一行左面的框中，选择“大于等于”在第一行右面的框中，输入150，按“确定”按钮F.将“课程成绩表”中筛选出来的课程名称都选中，按“复制”按钮E.选中SHEET1表，选中A2单元格，按“粘贴”按钮四.在工作表sheet2中，将表格标题“成绩表”按表格宽度合并及居中，文字设置成粗黑体14号，大红色，加下划会计用双线。
将工作表“sheet2”重命名为“成绩表”参考答案：A.在工作表sheet2中，选中区域（A1:I1),按“合并及居中”按钮B.在菜单“格式”中，选择“单元格”，选择“字体"标签,设置粗黑体14号，大红色，选择下划线为“会计用双线”C.在二级菜单“格式/工作表”中，选择“重命名...”D.将表名SHEET2改为“成绩表”五.在工作表“成绩表”中，按学号顺序插入一行，单元格内容依次为“980004、钱丁、81、82、83、84、85”六.在“成绩表”中总分和平均分两列用函数计算各人的五门课总分和平均分， 居中对齐，平均分保留一位小数参考答案：A.在“成绩表”中，选中（H4:H8）B.输入函数“SUM（C4:G4)”，按CTRL+回车C.在“成绩表”中，选中（I4:I8）D.输入函数“AVERAGE（C4:G4)”，按CTRL+回车E.在“成绩表”中，选中（H4:I8），按“居中”按钮F.在“成绩表”中，选中（I4:I8），设置一位小数选钮号，大红色，。
中文Excel2007教程第6章
（2）在“开始”选项卡中的“编辑”选项区中单击“排序 和筛选”按钮，弹出其下拉菜单，如图6.2.1所示。
（3）当用户选择“升序”或“降序”命令，将会弹出如图 6.2.2所示的提示框，提示用户是否调整选中字母列对应的数据。
图6.2.1 排序和筛选下拉菜单
图6.2.2 提示框
（4）单击“排序”按钮，则可按照所选排序方式进行排序。 如图6.2.3所示为按字母Z～A的顺序降序排序后的效果。
图6.4.4 设置后的“分类汇总”对话框
图6.4.5 分类汇总后的表格
为数据清单添加自动分类汇总时，Excel自动将数据清单分级
显示（在工作表左侧出现分级显示符号
），以便用户根据
需要查看其结构。例如，只显示分类汇总和总计的汇总，可单击
行数值旁的分级显示符号
中的符号 ，如图6.4.6所示。或
者单击 和 符号来显示或隐藏单个分类汇总的明细数据行。
6.2 数据排序
数据排序是按照一定的规则对数据进行整理和重新排列，从 而为数据的进一步处理做好准备。Excel 2007为用户提供多种数 据清单的排序方法，允许按一列、多列和行进行排序，也允许用 用户自己定义的规则进行排序。
6.2.1 常规排序 按常规排序可以将数据清单中的列标记作为关键字进行排序， 其具体操作步骤如下： （1）选中要进行排序的列中的任意一个单元格。
技巧：在使用Excel进行排序时，无论是按升序还是按降序 排列，空白单元格总是被排在最后，其他单元格中的数据在两种 排序方式中显示的顺序相反。
图6.2.5 “排序”对话框
图6.2.6 按出生年月先后排序
6.3 筛选数据
筛选数据可显示满足指定条件的行，并隐藏不希望显示的行。 筛选数据之后，对于筛选过的数据子集，不用重新排列或移动就 可以复制、查找、编辑、设置格式、制作图表或打印。Excel 2007提供了两种筛选数据的方法，即自动筛选和高级筛选。使用 自动筛选可以创建按列表值、按格式和按条件3种筛选类型。
excel图表操作题6(分值20)
表
员工编号 部门
K12
开发部
C24
测试部
W24
文档部
S21
市场部
S20
市场部
K01
开发部
W08
文档部
C04
测试部
S23
市场部
S14
市场部
S22
市场部
C16
测试部
W04
文档部
K02
开发部
C29
测试部
K11
开发部
S17
市场部
W18
开发部
性别 男 男 女 男 女 女 男 男 男
女 女 男 男 男 女 女 男 女
4 1800 2 1200 4 2100 3 1500 6 2500 5 2000 3 1700 5 1600 2 1400
要求: （请严格按照要求在原表上进行操作，否则答案视为无效！！！） 1、将“部门”一列移到“员工编号”一列之前。（1） 2、清除表格中员工编号为S23的一行的内容。(1) 3、标题文字格式：隶书、20号字、合并及居中。(1) 4、第二行黄底红字、宋体、字形：加粗。(1) 5、为部门一列中所有的“市场部”填加浅黄色底纹(A6:A7,A12:A13,A19)。(1) 67、、使按用样“文工为资A2”：(GG22:0G区20域)数设据置创边建框一。个(1三) 维饼图，图表标题为“工资比例图”。 （3）
样文：
年龄
籍贯 30 陕西 32 江西 24 河北 26 山东 25 江西 26 湖南 24 广东 22 上海 26 河南
24 山东 25 北京 28 湖北 32 山西 36 陕西 25 江西 25 辽宁 26 四川 24 江苏
工龄
工资 5 2000 4 1600 2 1200 4 1800 2 1900 2 1400 1 1200 5 1800 4 1500