excel2013高级教程-数据处理及分析_图文

注意：输入的双引号必须是英. 文字符。
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单元格区域

·······（有书也称主菜单） 组：每个组都与特定任务相关；（有书也称工具栏） 快速访问工具栏：独立显示，默认有“保存”、“撤销”和“恢复“三按钮，可以自定义；
标题栏：显示工作薄名称。 状态栏：宏录制按钮、查看方式、缩放工具。
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）其他方面：
提供了大量预定义主题和样式 丰富的条件格式 自动调整编辑栏 函数记忆式输入

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所示。
自定义筛选方式可以在任何条件下进行，它可以是 一个条件，也可以是两个条件，其具体的应用范围 如下所示。

1-11
• • • • •
1-8
对表格使用样式
• 在工作表中使用样式，能够保证工作表中的数据具有一致 的外观，同时能够节省设置格式的时间，提高工作效率。 样式包括数字格式、对齐方式、字体和字号的大小、边框 以及填充的颜色和底纹等。为了能够快速实现表格样式的 设置，Excel 2019提供了大量自动套用表格格式供用户使 用。
• • • • •
1-3
单元格的基本操作
• 单元格是Excel工作表中数据存储的基本单元，任何数据都只 能在单元格中输入。掌握对工作表中单元格的操作，是使用 Excel进行数据处理的基础。 选择单元格 移动和复制单元格 插入和删除单元格 合并单元格 重命名单元格
• • • • •
1-4
数据的输入
第9章 Excel 2019表格的基本操作
杨章伟
课程内容安排
• • • • • • • •
工作表的基本操作 单元格的基本操作 数据的输入 格式化数据 设置单元格外观 对表格使用样式 数据的查找和替换 小结
1-2
工作表的基本操作
• 在Excel中进行的所有工作都是在一个打开的工作簿中进行的， 工作簿实际上是工作表的容器，其以文件的方式进行存储。工 作表是Excel的工作平台，用于处理和存储数据。对工作簿的操 作实际上就是对文件的操作，其类似于Word中的文档操作 。 添加和删除工作表 重命名和着色工作表 复制和移动工作表 拆分工作表 保护工作表
• 要在Excel工作表中对数据进行处理和分析，首先要输入数 据。Excel工作表中可以输入多种类型的数据 。 输入数据 ：在Excel中，选择单元格和单元格区域后就可 以直接向其中输入数据，包括文本、数字、图表和声音等 。 自动填充相同的数据 ：当需要在工作表的连续单元格中输 入相同的数据时，可以使用自动填充功能来加快数据的输 入。 自动填充有序数据 ：利用自动填充功能能够在工作表中快 速输入一组有序数据，如等差数据、等比数据和年份等。

格式复制 双击格式刷，嘻唰唰
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个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自定义序列的自动填写
① 在“工具”菜单中， 选择“选项”，单击“自 定义序列”标签； ② 确定输入序列； ③ 在单元格中输入所选序 列的任一项，并选择该单元格； ④ 用鼠标左键拖曳所选单元

工作簿窗口的切换
单击需激活窗口的任意位置。
§3.3 工作表的编辑和格式化
案例2
例：①打开”课堂演示例子.xlsx”工作簿，将“输入与编辑.xlsx”工作 簿Sheet1表中的学生记录（本表没有的记录）复制到当前工作簿 Sheet1表的第4和第5条记录之间； ②在Sheet1和Sheet2之间插入 “输入与编辑.xlsx”工作簿中的“九九乘法表” 1张工作表； ③编辑
公式中引用单元格地址、单元区域地址时，若引用非连续区域， 则在各单元格地址间用“，”分隔。 公式中运算符
算术运算符：＋、－、*、/、%（百分比）、^（指数）；
比较运算符：＝、>、>=、<、<=、<>；
文字运算符：&（连接），用于文字合并。
2.使用函数
(1)函数：Excel为用户提供的内置算法程序 (2)EXCEL函数的一般表达式：
1．修改单元格中的数据
– 双击单元格进行修改；或选中单元格，在编辑 栏内修改
2．复制、移动和清除单元格数据
– 复制单元格数据（注意多重选区复制的限制）， 粘贴到目标单元格 – 选择性粘贴数据
选定要复制单元格复制 选定接受区的左上角单元格 选择性粘贴
– 移动单元格数据（ቤተ መጻሕፍቲ ባይዱWORD相同）
– 选定要移动内容→“剪切”→ 单击接受移动内容区 域的左上角单元格→“粘贴”。 选定要移动的单元格，将鼠标指向选定区域的边框， 鼠标变为一个指向左上角的箭头拖动鼠标，移动 虚框到新的位置上，松开鼠标，完成移动。
任意步长的序列（用命令菜单填充方法 ）
– ①在填充区的起始单元格中输入初值； – ② 单击“编辑/填充/ 序列”命令； – ③ 在“序列”对话框中选择填充方向、填充 类型、步长、终值等； – ④ 单击“确定”，数据自动填入。

任务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 图表的应用

选中单元格区域，输入内容，同时按住Ctrl+Enter组合键，则该区 域单元格内容均为输入内容
2021/3/12
7
4、其他输入技巧
连接符&
默认的Excel数值精度为15位，若超过15位，则用&连接，如 =”12345678901111”&”001” 或者加入双引号
自定义格式输入
选中区域单击数字(或者右击选择设置单元格格式)数字选项内的 自定义
数据的预测分析
✓移动平均法 ✓回归法 ✓指数平滑
3
1、数值的输入
正数 负数 小数 分数
2、文本的输入
一般文本 特殊文本
2021/3/12
3、相同数据的输入
一般复制法 自动填充法 Ctrl+Enter组合键
4、其他的输入
连接符 固定格式输入 等差序列 等比序列 使用数据有效性列表选择数据
4
1、数值的输入
版本：Excel 2003、 Excel 2007、 Excel 2010、 Excel
2013
2021/3/12
2
数据的输入
数据的运算
数据整理
✓排序 ✓统计汇总 ✓查找与替换 ✓筛选
2021/3/12
基础数据统计与分析
✓数据透视表 ✓数据统计分析
数据的相关性与回归分析
✓数据的相关性分析 ✓回归分析
24
(3)高级查找与替换
开始编辑查找与选择选择选项（可查找字体、颜色、大小等）
2021/3/12
25
4、筛选
(1) 自动筛选
数据筛选选择条件
2021/3/12
26
(2) 自定义筛选
数据筛选数字筛选选择筛选条件填写筛选条件内容(数字或文本)

第5章 Excel 2013的基本操作
Office 2013办公软件高级应用立体化教程
内容导航
5.1 Excel 2013的基础操作 5.1.1 熟悉Excel 2013工作界面 5.1.2 认识工作簿、工作表、单元格 5.1.3 切换工作簿视图 5.2 课堂案例：制作“客户登记表” 5.3 项目实训 5.4 课后练习 5.5 技巧提升
2. 选择工作表
选择一张工作表：单击需选择的工作表标签，如果看不到所需标签，可单击工作表标签滚动显示按钮或
将其显示出来，然后再单击相应的标签即可。选择并切换到的工作表标签呈白色显示。 选择工作簿中的所有工作表：在任意一张工作表标签上单击鼠标右键，在弹出的快捷菜单中选择“选定
全部工作表”命令即可选择同一工作簿中的所有工作表。
选择相邻的多张工作表：选择第一张工作表标签，按住【Shift】键的同时，单击要选择的最后一张工作 表标签即可选择相邻的多张工作表。 选择不相邻的多张工作表：选择第一张工作表标签，按住【Ctrl】键的同时，单击要选择的其他工作表的 标签即可依次选择单击的多张工作表。
5.2.1
工作簿的基本操作
12
3. 选择单元格
5.2.1
工作簿的基本操作
10
1. 新建工作簿 （2）新建基于模板的工作簿 （2） 在查询的结果中选择“学生课程安排”选项，在弹出的界面中单击“创建”按钮，创建模板文档
（1） 选择【文件】/【新建】菜单命令，在“搜索联机模板”文本框中输入“课程表”，查询简历模板样式。
5.2.1
工作簿的基本操作
11
5.3.1
新建“员工信息表”工作簿
20
实训目标：
本实训的目标是制作员工信息表，需要先将新建的空白工作簿以“员工信息表”为名进 行保存，然后在其中选择相应的单元格输入并填充数据，重命名工作表名称并设置密码， 完成后再次保存工作簿并退出Excel。

6、如何输出001
• 常规输入001 都会被自动转换为1
6.1操作：
操作方式1： 设置单元格格式---自定义---000
6.2操作：
操作方式2： 输入 ‘ 001 直接转换为文本格式
7、如何输出超长数字
• 数字长度超过11位就会出现 • 例如：身份证信息等
7.1操作
操作方法1： 设置单元格格式---自定义---@
目录
• 1、使数据根据单元格宽度换行
• 1.2操作方法 • 1.3如何手动快捷键换行
• 2、复制数据时不改变列宽
• 2.1操作 • 2.2如何原原本本的复制数据
数据和数据格式
• 3、快速将整列数据增加/减少数值
及常用单元格运算
• 3.1操作 • 3.2常用单元格运算
• 4、数据转换日期类型 • 5、 Ctrl +Enter 键 和 Enter 键的区别 • 6、如何输出001 • 7、如何输出超长数字 • 8、输出 中文大写数字 • 9、自动输入小数点 • 10、日期单元格的填充 • 11、自定义填充序列 • 12、控制数据输入（数据验证） • 13、控制数据输入（数据验证） • 14、为单元格添加下拉选项
20.2操作：色阶
20.3操作： 图标集
21、表格样式
• 操作：开始—套用表格样式—新建表格样式
21.2操作
22、单元格样式ቤተ መጻሕፍቲ ባይዱ
保存单元格样式，方便复用操作
22.1操作 • 开始---单元格样式---新建单元格样式
补充：23、只排序选中区域
• 23.1 默认的排序方式：按列排序
23.2操作：
19、找出大于80的值
• 操作：开始---条件格式---突出显示单元格规则---大于

图4-1-10 粘贴选项
（4）“行”、“列”的插入和删除 单击左侧的行地址，选中一行，点击“开始”选项卡“单元格”功能 区中“插入”命令或右键点击行地址，选择“插入”命令，默认情况 下可以在选中行上面插入一行。或者选择一行中的任意单元格，右键 点击选择“插入”命令，通过“插入”对话空中的“整行”选项，可 以在选中行的上面插入一行。通过类似的操作，可以插入对应列。根 据题目要求在“打印数据”工作表的上方插入两行，选中A1:H2单元 格，使用“开始”选项卡“对齐方式”功能区中的“合并后居中”命 令进行合并居中操作，输入“新进员工基本信息统计表（2016 年）”，如图4-1-11所示。
图4-1-12 快捷修改数字类型
但是“数字”功能区中的数据类型较为单一，如果目标数据类型或显 示格式不存在时，可以通过“设置单元格格式”中的数字选项卡进行 个性化的设置。例如，将“出生年月”列设置为“年/月”的形式。选 中“出生年月”列，点击“数字”功能区右下角的扩展按钮打开“数 字”选项卡，或者打开“设置单元格格式”中的“数字”选项卡，选 择“自定义”类型，在“类型”中输入“yyyy/mm”（其中yyyy表示用 四位数字表示年，mm表示用两位数字表示月），点击“确定”，如图 4-1-13所示。
学习目标
掌握制作Excel工作簿的基本步骤。 掌握在Excel工作表中输入数据、修改格式等基本操作。 掌握利用Excel工作表对数据进行排序、筛选、汇总等处理。 掌握利用图表分析数据的方法。 掌握Excel工作表之间的交叉引用及处理。 掌握Excel工作表中函数、数据库函数的使用。 掌握Excel工作表的页面设置及打印方法。
图4-1-7连续单元格的选取
（2）单元格的插入和删除 在“打印数据”工作表中，单击选中单元格F6，点击“开始”选项卡 “单元格”功能区中“删除”命令，或单击右键，选择下拉式菜单 中的“删除”命令，弹出“删除”对话框，选择“下方单元格上移”选 项，删除单元格，如图4-1-8所示。