excel2013高级教程-数据处理及分析_图文
合集下载
电子表格的数据处理PPT课件
注意：输入的双引号必须是英. 文字符。
COUNTIF函数
➢COUNTIF 函数的语法格式为 COUNTIF(Range,Criteria)。
➢计算单元格区域中满足指定条件的单元格个数。 ➢Range指定要进行数据计算的单元格区域，Criteria指 定计算的条件，其形式可以为数字、表达式或文本。
例如，COUNTIF(B2:B10,">60")是计算B2到B10单元 格区域中大于60的单元格的数目。
.
分类汇总
➢分类汇总就是对数据清单按指定字段进行分类， 将此字段值相同的连续的记录作为一类，进行求和、 平均值、计数等汇总运算。 ➢在分类汇总前，必须按分类的字段排序，使此字 段值相同的记录排列在一起。
.
分类汇总
将班级所在区域（A2到A22）的任一单元格 作为活动单元格， 单击“常用”工具栏的 “升序”按钮
.
单元格引用 ➢单元格引用是在公式中通过单元格的名 称来引用此单元格的数据。 ➢当公式中所引用单元格的数据发生变化 时，公式会自动更新计算结果。
.
单元格引用
相对引用 ➢默认的引用方式，直接由单元格的列 号和行号组成。 ➢当公式被复制到其他单元格，引用单 元格的地址会根据位置的变化自动调节。 ➢例如，在G3单元格输入公式=D3+E3, 将其复制到H4单元格时，变为=E4+F4。
.
8 计算百分比
通过公式统计各个等级人数占总人数的百分比
.
计算百分比
选中C2单元格，输入公式=B2/B$6，统计等级 为差的学生占总人数的比例。 通过“填充”或“选择性粘贴”将公式复制到 C3到C5单元格区域。
.
计算百分比
选中C2到C5单元格区域
COUNTIF函数
➢COUNTIF 函数的语法格式为 COUNTIF(Range,Criteria)。
➢计算单元格区域中满足指定条件的单元格个数。 ➢Range指定要进行数据计算的单元格区域，Criteria指 定计算的条件，其形式可以为数字、表达式或文本。
例如，COUNTIF(B2:B10,">60")是计算B2到B10单元 格区域中大于60的单元格的数目。
.
分类汇总
➢分类汇总就是对数据清单按指定字段进行分类， 将此字段值相同的连续的记录作为一类，进行求和、 平均值、计数等汇总运算。 ➢在分类汇总前，必须按分类的字段排序，使此字 段值相同的记录排列在一起。
.
分类汇总
将班级所在区域（A2到A22）的任一单元格 作为活动单元格， 单击“常用”工具栏的 “升序”按钮
.
单元格引用 ➢单元格引用是在公式中通过单元格的名 称来引用此单元格的数据。 ➢当公式中所引用单元格的数据发生变化 时，公式会自动更新计算结果。
.
单元格引用
相对引用 ➢默认的引用方式，直接由单元格的列 号和行号组成。 ➢当公式被复制到其他单元格，引用单 元格的地址会根据位置的变化自动调节。 ➢例如，在G3单元格输入公式=D3+E3, 将其复制到H4单元格时，变为=E4+F4。
.
8 计算百分比
通过公式统计各个等级人数占总人数的百分比
.
计算百分比
选中C2单元格，输入公式=B2/B$6，统计等级 为差的学生占总人数的比例。 通过“填充”或“选择性粘贴”将公式复制到 C3到C5单元格区域。
.
计算百分比
选中C2到C5单元格区域
Excel2013高级教程
·······（有书也称主菜单） 组：每个组都与特定任务相关；（有书也称工具栏） 快速访问工具栏：独立显示，默认有“保存”、“撤销”和“恢复“三按钮，可以自定义；
标题栏：显示工作薄名称。 状态栏：宏录制按钮、查看方式、缩放工具。
1.2 数据的输入、编辑与运算
目标：建立某企业产品销售数据统计报表（两个工作表） 1.2.1建立产品基本信息表
Excel2013高级教程 数据统计与处理分析
Excel是Office软件中的核心成员，是最优秀的电 子表格软件之一，具有强大的数据处理和数据分 析能力，是个人及办公事务中进行表格处理和数 据分析的理想工具之一。
如何利用Excel的函数、图表、高级分析工具、 VBA程序等功能进行数据分析是本次学习的重点。
1.2 数据的输入、编辑与运算
1.2.2 建立“销售记录汇总”表
思考： 公式可分为几大类？
可分三大类： 普通公式：如=A1+B1 , =sum(a1:b5) 数组公式：如 {=average(B1:B10-A1:A10)} （使用数组公式可减少存储空间，提高工作效率，详见“ 综合实例”工作薄中相关内容） 命名公式：如：
任务2： 建立表框架（见实例）； 区分数据来源（原始的、引用的、计算的）； 输入公式（从信息表引用的、本表计算的）； 数据复制（客户名称、发货日期、订单号、数量）。
思考： 什么是公式？其价值何在？
公式是由“=”号或“+”号开头，由常数、函数、单元格引用 以及运算符组成的式子。其价值不仅是计算，重要的是 构建计算模型。
1600万种颜色） （3）更强大的表功能，提供了全新的数据引用方式，称为结构化引用，可以方
便地构造动态数据报表。
（4）其他方面：
提供了大量预定义主题和样式 丰富的条件格式 自动调整编辑栏 函数记忆式输入
标题栏：显示工作薄名称。 状态栏：宏录制按钮、查看方式、缩放工具。
1.2 数据的输入、编辑与运算
目标：建立某企业产品销售数据统计报表（两个工作表） 1.2.1建立产品基本信息表
Excel2013高级教程 数据统计与处理分析
Excel是Office软件中的核心成员，是最优秀的电 子表格软件之一，具有强大的数据处理和数据分 析能力，是个人及办公事务中进行表格处理和数 据分析的理想工具之一。
如何利用Excel的函数、图表、高级分析工具、 VBA程序等功能进行数据分析是本次学习的重点。
1.2 数据的输入、编辑与运算
1.2.2 建立“销售记录汇总”表
思考： 公式可分为几大类？
可分三大类： 普通公式：如=A1+B1 , =sum(a1:b5) 数组公式：如 {=average(B1:B10-A1:A10)} （使用数组公式可减少存储空间，提高工作效率，详见“ 综合实例”工作薄中相关内容） 命名公式：如：
任务2： 建立表框架（见实例）； 区分数据来源（原始的、引用的、计算的）； 输入公式（从信息表引用的、本表计算的）； 数据复制（客户名称、发货日期、订单号、数量）。
思考： 什么是公式？其价值何在？
公式是由“=”号或“+”号开头，由常数、函数、单元格引用 以及运算符组成的式子。其价值不仅是计算，重要的是 构建计算模型。
1600万种颜色） （3）更强大的表功能，提供了全新的数据引用方式，称为结构化引用，可以方
便地构造动态数据报表。
（4）其他方面：
提供了大量预定义主题和样式 丰富的条件格式 自动调整编辑栏 函数记忆式输入
Chap08 使用Excel 2013处理数据
8.2.6 选择单元格
在对单元格进行操作之前，首先需要选择单元格，在 Excel 2013中选择单元格主要包括以下几种情况。
8.2.6 选择单元格
8.2.6 选择单元格
8.2.7 插入单元格
在用Excel 2013制作工作表的过程中，有时需要在 已有数据的工作表的之间某处添加数据，此时就 需要插入单元格（如果有不需要的单元格，则应 该将其删除，删除单元格的操作与插入单元格的 操作相似，这里就不再赘述）。
2.依据多列数据排序
2.依据多列数据排序
3.自定义排序
如果用户感觉使用的排序方式不能够反映表格数据， 可自定义序列排序。 其方法是：选择任意数据单元格，打开“排序”对 话框，在“次序”下拉列表框中选择“自定义序列” 命令，打开“自定义序列”对话框。
8.4.2 数据的筛选
在Excel中，利用筛选功能可在数据量大的表格中查看 某些具有特定条件的数据，根据筛选方式不同，可以 分为自动筛选、自定义筛选和高级筛选3种，下面分 别进行介绍。
8.2.9 设置单元格的行高和列宽
很多时候，我们会因为所要输入的数据较多或者字号 较大而使之在默认的有限的单元格中不能完全显示。 此时就需要为单元格调整合适的行高和列宽。 在Excel 2013中，用户可用以下几种方法设置行高或 列宽。
1.快速调整行高和列宽 如果对单元格的行高和列宽的具体高度值和宽度值没 有特别要求，可以通过拖动鼠标或自动调整功能快速 调整行高和列宽，其具体操作方法如下。
2.自定义筛选
在该菜单的子菜单中选择任意菜单命令都可以打开“自 定义自动筛选方式”对话框，如图8-53所示。
自定义筛选方式可以在任何条件下进行，它可以是 一个条件，也可以是两个条件，其具体的应用范围 如下所示。
第9章Excel2013表格的基本操作-PPT课件
1-11
• • • • •
1-8
对表格使用样式
• 在工作表中使用样式，能够保证工作表中的数据具有一致 的外观，同时能够节省设置格式的时间，提高工作效率。 样式包括数字格式、对齐方式、字体和字号的大小、边框 以及填充的颜色和底纹等。为了能够快速实现表格样式的 设置，Excel 2019提供了大量自动套用表格格式供用户使 用。
• • • • •
1-3
单元格的基本操作
• 单元格是Excel工作表中数据存储的基本单元，任何数据都只 能在单元格中输入。掌握对工作表中单元格的操作，是使用 Excel进行数据处理的基础。 选择单元格 移动和复制单元格 插入和删除单元格 合并单元格 重命名单元格
• • • • •
1-4
数据的输入
第9章 Excel 2019表格的基本操作
杨章伟
课程内容安排
• • • • • • • •
工作表的基本操作 单元格的基本操作 数据的输入 格式化数据 设置单元格外观 对表格使用样式 数据的查找和替换 小结
1-2
工作表的基本操作
• 在Excel中进行的所有工作都是在一个打开的工作簿中进行的， 工作簿实际上是工作表的容器，其以文件的方式进行存储。工 作表是Excel的工作平台，用于处理和存储数据。对工作簿的操 作实际上就是对文件的操作，其类似于Word中的文档操作 。 添加和删除工作表 重命名和着色工作表 复制和移动工作表 拆分工作表 保护工作表
• 要在Excel工作表中对数据进行处理和分析，首先要输入数 据。Excel工作表中可以输入多种类型的数据 。 输入数据 ：在Excel中，选择单元格和单元格区域后就可 以直接向其中输入数据，包括文本、数字、图表和声音等 。 自动填充相同的数据 ：当需要在工作表的连续单元格中输 入相同的数据时，可以使用自动填充功能来加快数据的输 入。 自动填充有序数据 ：利用自动填充功能能够在工作表中快 速输入一组有序数据，如等差数据、等比数据和年份等。
Excel2013课件
格式复制 双击格式刷，嘻唰唰
4.1 工作表格式化
常用的格式命令
字体、字号、字号增加、字号减小 粗体、斜体、文字下划线，单元格边框，单元格 底色，文字颜色，拼音
垂直对齐，文字方向 水平对齐，减少缩进和增加缩进
数字格式 会计格式，百分号，千位符，增加和减少小数位
小贴士：右键格式命令，右击单元格，EXCEL会 弹出常用的格式命令，大大提供工作效率
3.4 工作表窗口的拆分与冻结
窗口的拆分
水平拆分 垂直拆分 水平垂直拆分
窗口的冻结
行冻结 列冻结 行列冻结
第四章 美化工作表
4.1 工作表格式化 4.2 套用格式 4.3设置工作表背景图案
4.1 工作表格式化
单元格格式
数字 对齐 字体 边框 填充 保护
列宽和行高 格式刷的应用
4.2 套用格式
自动套用单元格格式
自动套用表格格式
表格格式的扩大和缩减
4.2 套用格式
条件格式
套用条件格式 自定义条件格式
4.3 设置工作表背景图案
添加背景
添加水印
EXCEL没有标准水印功能 利用页眉页脚创造水印
第五章 打印表格
6.1 设置打印区域和分页 6.2 页面的设置 6.3 打印预览和打印
####### 列宽不足以显示数字
第8章 图表的应用
8.1 图表的基本概念 8.2 创建图表 8.3 编辑图表 8.4 常用图表类型及范例练习
8.1 图表的基本概念
图表是数据的另一种展现形式 选择正确的图表让数据展示更清楚 嵌入式图表与图表工作表 图表区和绘图区
图表区包含图表的所有元素，作为图表的主背景和容器 绘图区是图表区的一部分，包括实际图表
柱形图：把一项与另一项进行比较 折线图：某项数据在时间上的趋势，如销售额、生产量 饼图：相对值比较，如不同品牌的市场份额 XY散点图：比较两种数据之间的关系，如销售费用与销售额 雷达图：比较两组数据之间的关系，常用于多款产品性能比较。 环形图：两组相对值比较，如不同年度的不同品牌市场份额
4.1 工作表格式化
常用的格式命令
字体、字号、字号增加、字号减小 粗体、斜体、文字下划线，单元格边框，单元格 底色，文字颜色，拼音
垂直对齐，文字方向 水平对齐，减少缩进和增加缩进
数字格式 会计格式，百分号，千位符，增加和减少小数位
小贴士：右键格式命令，右击单元格，EXCEL会 弹出常用的格式命令，大大提供工作效率
3.4 工作表窗口的拆分与冻结
窗口的拆分
水平拆分 垂直拆分 水平垂直拆分
窗口的冻结
行冻结 列冻结 行列冻结
第四章 美化工作表
4.1 工作表格式化 4.2 套用格式 4.3设置工作表背景图案
4.1 工作表格式化
单元格格式
数字 对齐 字体 边框 填充 保护
列宽和行高 格式刷的应用
4.2 套用格式
自动套用单元格格式
自动套用表格格式
表格格式的扩大和缩减
4.2 套用格式
条件格式
套用条件格式 自定义条件格式
4.3 设置工作表背景图案
添加背景
添加水印
EXCEL没有标准水印功能 利用页眉页脚创造水印
第五章 打印表格
6.1 设置打印区域和分页 6.2 页面的设置 6.3 打印预览和打印
####### 列宽不足以显示数字
第8章 图表的应用
8.1 图表的基本概念 8.2 创建图表 8.3 编辑图表 8.4 常用图表类型及范例练习
8.1 图表的基本概念
图表是数据的另一种展现形式 选择正确的图表让数据展示更清楚 嵌入式图表与图表工作表 图表区和绘图区
图表区包含图表的所有元素，作为图表的主背景和容器 绘图区是图表区的一部分，包括实际图表
柱形图：把一项与另一项进行比较 折线图：某项数据在时间上的趋势，如销售额、生产量 饼图：相对值比较，如不同品牌的市场份额 XY散点图：比较两种数据之间的关系，如销售费用与销售额 雷达图：比较两组数据之间的关系，常用于多款产品性能比较。 环形图：两组相对值比较，如不同年度的不同品牌市场份额
Excel电子表格高级数据处理与分析
后面的4个0表示4位可变数字。（注意这种
编号不是真实的，需要数据统计分析的表
不用，最好采用分列输入相同部分，再用&
连接）
精品课件
22
3.6利用下拉列表进行数据选择
数据有效性 数据有效性——允许——序列
3.7利用公式与函数从已有数据表查找输入（后 面介绍VLOOUP函数
3.8行列转置输入
3.9数据有效性限制性输入
28
2、编辑工作表
（4）行、列的删除和插入 行、列的删除
①单击所要删除的行、列的标头 ②选择“编辑”/“删除”项即可
行、列的插入 ①单击所要插入行、列所在任一单元格 ②选择“插入”/“行”或“列”项即可
精品课件
29
2、编辑工作表
（5）编辑批注 • 插入批注
插入——批注——输入批注——确定 • 删除批注
3.10大量仿真数据的产生
用随机函数RAND()结合INT()，输入大量 的数字数据。如INT(RAND()*90)
精品课件
23
3.7Excel2007的汉子简转繁
• Excel2007的审阅选项——汉繁转换
精品课件
24
2、编辑工作表
（1）单元格内容的移动、复制和删除 A. 用鼠标拖曳的方法移动和复制单元格的
1、数字的填写 n 在该数字的填写行（列）的前两个单
元格内输入开始的2个数字； n 选择这两个单元格； ① 用鼠标按住第二个单元格的填充柄，
将其拖曳到结束单元格，松开鼠标
精品课件
19
3.3自定义序列的自动填写
① 在“工具”菜单中， 选择“选项”，单击“自 定义序列”标签； ② 确定输入序列； ③ 在单元格中输入所选序 列的任一项，并选择该单元格； ④ 用鼠标左键拖曳所选单元
06-2013_EXCEL_讲课
工作簿窗口的切换
单击需激活窗口的任意位置。
§3.3 工作表的编辑和格式化
案例2
例：①打开”课堂演示例子.xlsx”工作簿，将“输入与编辑.xlsx”工作 簿Sheet1表中的学生记录（本表没有的记录）复制到当前工作簿 Sheet1表的第4和第5条记录之间； ②在Sheet1和Sheet2之间插入 “输入与编辑.xlsx”工作簿中的“九九乘法表” 1张工作表； ③编辑
公式中引用单元格地址、单元区域地址时，若引用非连续区域， 则在各单元格地址间用“，”分隔。 公式中运算符
算术运算符：＋、－、*、/、%（百分比）、^（指数）；
比较运算符：＝、>、>=、<、<=、<>；
文字运算符：&（连接），用于文字合并。
2.使用函数
(1)函数：Excel为用户提供的内置算法程序 (2)EXCEL函数的一般表达式：
1．修改单元格中的数据
– 双击单元格进行修改；或选中单元格，在编辑 栏内修改
2．复制、移动和清除单元格数据
– 复制单元格数据（注意多重选区复制的限制）， 粘贴到目标单元格 – 选择性粘贴数据
选定要复制单元格复制 选定接受区的左上角单元格 选择性粘贴
– 移动单元格数据（ቤተ መጻሕፍቲ ባይዱWORD相同）
– 选定要移动内容→“剪切”→ 单击接受移动内容区 域的左上角单元格→“粘贴”。 选定要移动的单元格，将鼠标指向选定区域的边框， 鼠标变为一个指向左上角的箭头拖动鼠标，移动 虚框到新的位置上，松开鼠标，完成移动。
任意步长的序列（用命令菜单填充方法 ）
– ①在填充区的起始单元格中输入初值； – ② 单击“编辑/填充/ 序列”命令； – ③ 在“序列”对话框中选择填充方向、填充 类型、步长、终值等； – ④ 单击“确定”，数据自动填入。
3.2EXCEL数据处理-PPT文档
任务3 公式的应用3
制作如样文所示实例，相关 要求如下： （1）打开图例所示表格。 （2）按照要求输入公式获 得语文、英语、计算机 基础、体育四门总和。 （3）其余数据格式自行分 析。
任务4 函数的应用1
制作如样文所示实例，相关要 求如下： （1）打开图例所示表格。 （2）按照要求插入函数获得每 个同学9门学科总和。 （3）其余数据格式自行分析。
任务15 创建数据透视表
制作如样文所示实例，相关要求如下： （1）打开指定文件。 （2）对表格中数据创建数据透视表，要求 以班级为行字段，统计各班级各学科的平 均值以及年级各学科的平均值。 （3）其余数据格式自行分析。 （4）课堂练习：试着利用数据透视表计算 各班级各学科成绩总和。
任务9 数据排序1
制作如样文所示实例，相关要求 如下： （1）打开指定文件。 （2）选中表格，对表格中语文成 绩进行由高到低降序排序。 （3）其余数据格式自行分析。 （4）拓展练习：做完上面练习后， 依次分别对表格中其他列成绩 进行降序排序。按学号排名次
任务10 数据排序2
制作如样文所示实例，相关要求如 下： （1）打开指定文件。 （2）选中表格，将表格中所有学 科成绩作为主要关键字、语数 外三门成绩作为次要关键字、 汽车基础与运用作为第三关键 字进行降序排序。 （3）在学号不变的情况下，对成 绩排名。 （4）对表格标题行进行冻结窗格 处理。 （5）对班级人数进行统计。 （6）对表格进行打印要求每一页 要显示标题行。 （7）其余数据格式自行分析。
任务13 数据分类汇总
制作如样文所示实例，相关要求如下： （1）打开指定文件。 （2）对表格中所有成绩数据按班级进行平 均值分类汇总。 （3）其余数据格式自行分析。
任务14 图表的应用