资源简介

(共28张PPT)
第三单元第九课（一）建立表格
工作表、单元格、公式输入
工作簿与工作表
了解概念和基本设置（字体、表数设置、标签颜色等）
单元格
EXCEL2016单元格的范围：256列，65536行
默认单元格名称：A1，B5等；
R1C1单元格名称：R1C1，R5C6等；
自定义单元格名称
单元格属性，可以自定义
公式输入
单元格用等于号（=）输入公式或点击
fx
输入公式
常用公式求和、平均、计数等直接点击
即时统计显示求和、计数、平均值等（右键定制）
输入公式用点选单元格的方式
要善于使用公式的帮助菜单了解函数功能
工作表表的复制与移动
点击右键弹出菜单
选择本工作簿，则可以在本工簿中复制一个本工作表
是拷贝还是移动，如果是拷贝，打勾
选择新工作簿，则可以将本工作表生成一个新的工作簿
图表生成功能
图表功能是EXCEL的重要功能，必须掌握，教程均涉及，建议按教程自学，本处不再详述。
图表多练习设置如何设置属性
如果数据能动态更新或计算，图表也能成为动态图表
打印设置
页面设置的方法：
选取“文件”－〉页面设置，进入页面设置界面；
顶端表头设置
整页打印设置
显示页设置
$
符号
–
绝对引用和相对引用
批量输入公式，在一个单元格的公式录入完成后，拖动单元格可以快速批量输入公式，应掌握引用单元格的变化规律，观察是绝对引用还是相对引用。
一般只研究1个单元格中的公式即可。在公式中：
A3
-
行、列均可随着单元格的拖动相对变化
$A3
-
钉住列，行随着单元格的拖动相对变化
A$3
-
钉住行，列随着单元格的拖动相对变化
$A$3
-
行、列均钉住，单元格拖动不发生变化
快捷转换键：鼠标放在单元格F4
举例
常用功能键
F2
进入单元格编辑状态，ESC退出
-
减少使用鼠标的频次，提高编辑效率
F4
相对引用与绝对引用变换
-
快速设置相对引用、绝对引用、混合引用
F5
定位
-
查找功能的扩展（举例：复制分类汇总结果）
F9
公式调试
配合公式的帮助，解读公式功能和结果（学习EXCEL掌握）
3.
EXCEL常用函数与技巧
对明细表进行汇总计数、求和函数
一般（主要用于明细表）
计数：COUNT(C2:C100)
对数值计数
COUNTA
筛选后的序号
求和：SUM(C2:C100)
筛选（主要用于明细表）
计数：SUBTOTAL(9,C2:C100
)
求和：SUBTOTAL(3,
C2:C100
)
条件（主要用于统计表）
计数:
COUNTIF(C2:C100,"<60")
求和：SUMIF(A2:A100,“=条件",
C2:C100)
多条件（主要用于统计表）
计数：
SUMPRODUCT((C2:C100<24)
(C2:C100>22))
求和：
SUMPRODUCT((C2:C100<24)
(C2:C100>22),C2:C100)
举例
扩展：多条件匹配求和的应用
SUMPRODUCT((A3:A10=“条件1”)
(C2:I2=“条件2”)
(B3:B10=条件3)
C3:I9)
在一个表的特定区域中查找引用同时满足多个条件数据之和。
如果满足条件的必须为唯一值，可用于查找标准。
如果表中符合条件的数不止一个，则会返回它们的和。
实例
逻辑判断函数
IF(判断条件，条件成立的结果，条件不成立的结果)
文本连接符
“文本1”&
数字&
单元格
日期格式、含义
日期格式转换为常规数字，EXCEL默认数字是1900年1月1日后的第n天
　日期格式的改变
取系统当前日期
now()
；today()
（动态变化）
Ctrl+;
Ctrl+shift+;
（
静态日期）
取日期的年、月、日
取年：year()
取月：month()
取日：day()
组合日期：DATE(YEAR(A1),MONTH(A1),DAY(A1))
年龄计算方法
　１、用年、月、日计算
　２、用出生日期到当前日期的天数差折算
实例
日期等常用函数
高级筛选
数据筛选：点击“数据”—〉“筛选”
—〉
“自动筛选”
打勾
取消筛选：点击“数据”—〉“筛选”
—〉
“自动筛选”
去勾
全部显示：点击“数据”—〉“筛选”
—〉
“全部显示”
高级筛选：点击“数据”—〉“筛选”
—〉
“高级筛选”
高级筛选的用处：筛选条件可以多个，条件可以事先写好，筛选的条例更加清晰，避免出错。
利用“高级筛选”功能可以在一个明细表中提取不重复的值
举例
条件格式
条件格式用于数据表的辅助提示功能。
选定要操作的单元格，点击“格式”
－〉
“条件格式”，在“设置”
界面进行设定条件格式。
注意：条件格式如果设定公式，也存在绝对引用和相对引用的关系（格式刷刷格式，即会产生相对引用）
已经设定条件格式的单元格拖拽，条件格式会自动复制到拖拽的单元格中
EXCEL2016版条件格式最多可以设置3个，第一个权限最高。
实例
继续添加条件
删除已添加的条件
工作表的保护
当进行“保护工作表”时所发生的结果
选定允许编辑的单元格
录入时，指定单元格可以填入，其他不能更改
EXCEL工作表保护功能只能做一般性保护，设置了一道屏障，但仍比较容易破解，重要信息不能依靠此方式
数据有效性
数据有效性的使用主要在于约束数据规范格式，对后续的方便统计工作会很有帮助。
选定要操作的单元格，点击“数据”
－〉”有效性“，在“设置”
界面“允许”框内选取对数据的约束类型。
选择“序列”，设置填写的序列的内容，填写时可有下拉菜单。序列的内容以逗号（小写）分割，见右图。
实例
小写逗号
默认已选择
数组公式
数组公式对一组或多组值执行多重计算，并返回一个或多个结果。
可以是一个或多个数值，或是一组或多组数值，并在编辑栏可以看到以“{}”括起来的公式
按Ctrl+Shift+Enter键录入公式
选择单元格
快捷键
Ctrl+F
不仅可以选择数值、公式内容，而且可以选择格式
选择格式完成后一定要“清除查找格式”
MACH、INDEX函数的使用
多条件引用文本型数据范例
MATCH(value,array,type)
●
返回在指定方式下与指定数值匹配的数组中元素的相应位置
value
为需要在
array
中查找的数值。
array
可能包含所要查找的数值的连续单元格区域。
type
为数字
-1、0
或
1。1:查找小于或等于value
的最大数值。0:查找等于value
的第一个数值。-1:查找大于或等于value
的最小数值。如果省略type，则假设为
1。
INDEX(reference,row_num,column_num,area_num)
●
返回(多个或单个)指定数据区域中的某行某列的值
reference指的是要检索的范围；
row_num指的是指定返回的行序号
column_num指的是指定返回的列序号（多列区域时，单列可以取消）
area_num指的是返回该区域中行和列的交叉域。可省略，默认1。
OFFSET函数
OFFSET(参数1,参数2,参数3,[参数4height],[参数5width])
●
返回[参数1]单元格（左上角）往下数[参数2]行往右数[参数3]列
的单元格的值。
●
按偏移后单元格为起点选定下[参数4
height]行，右[参数5
width]列的一个区域）
如果行数和列数偏移量超出工作表边缘，函数
OFFSET
返回错误值
#REF!。
　　
如果省略
height
或
width，则假设其高度或宽度与
[参数1]相同。
　　
函数
OFFSET
实际上并不移动任何单元格或更改选定区域，它只是返回一个引用。
函数
OFFSET
可用于任何需要将引用作为参数的函数。例如，公式
SUM(OFFSET(C2,1,2,3,1))
将计算比单元格
C2
靠下
1
行并靠右
2
列的
3
行
1
列的区域的总值。
数据条件匹配引用
VLOOKUP(C8,表A1:A100,3,0)
　　两张表有相同元素的列，为将一张表的数据关联到另一张表中，常用此函数。
一般先实现一个单元格的内容引用，通过拖拽的方式实现批量引用。
拖拽时注意绝对引用和相对引用。批量匹配
实例
填写0（或者FALSE）
填写被查找表自起点列后的第N列
被查找表的区域，第一列必须包含查找的关键字
本表中的关键字单元格
0为精确匹配查找，1为模糊匹配，一般不用1
选择性粘贴
在固化数据表时，使用选择性粘贴-
[数值]
；
在整理数据时，有时会用到运算型的选择性粘贴；
在整理数据时有时候会用到选择性粘贴[转置]；
举例
EXCEL表内容图片化复制
按住Shift键后，点击菜单栏的“编辑”，下拉菜单中会有“复制图片”
，可以将选中的内容复制成图片，粘贴到其他地方。
不按Shift键显示
按Shift键显示
制作幻灯片时常用。
一般用文档编写时，保护原始文件用。
EXCEL列数过多时，图片复制粘贴更方便。
举例
数据透视表
实现快速统计
基础是内容规范的明细表
因为此功能使对明细表的统计效率较高，建议EXCEL统计工作尽量从明细表做起，利用此方法生成统计结果。
如果有相对稳定格式的明细表，可以利用公式实现统计表。
实例
其他常用功能与技巧（演示）
长数字输入：输入前面加“
’”
（比如输入身份证号）
快速调整行宽、列宽：整行整列选中调整
EXCEL文档保存加密码：保存界面[工具]-〉[安全选项]
拖拽单元格，自动添加序列：右下角[自动填充选项]
单元格内的文本强制换行：Alt+回车
单元格显示公式：Ctrl+~
排序:
筛选状态下升序、降序，排序界面选项（可以扩充排序功能）
跨栏居中：右键[设置单元格格式]-〉[对齐]-〉[水平居中]
下
大纲模式：Shift+Alt+左方向键，取消大纲：Shift+Alt+右方向键
4.
EXCEL解决问题
实例操作
EXCEL解决问题实例（正向应用）
1、现有一个EXCEL名单，多于实际人员数，初步判断存在重复记录，请快速确定哪条记录重复？
2、一个单位现两个名单人数对不上，请找出对不上的原因何在？
3、现有工时统计标准见附表1，上级要求每月上报工时统计报表见附表2，请设计一个统计方案，高效实现统计工作。
使用数据透视表
利用公式
原始文件
EXCEL应用实例分析（逆向分析）
技巧184-1
提取零件规格中的最后序号
[F9分段调试]
技巧215
动态查询数据总表应用
[注意名称定义]
技巧268
根据资源曲线分配资源数量
[公式与名称定义]
若无时间本节请自学
通过分析他人的EXCEL实例，学习EXCEL统计和函数的使用方法（可以按F9键调试）
以下实例选自所附教程5：
在所附教程中有许多实例EXCEL文档可供分析学习，另外可根据要解决的问题上网搜索类似文档进行分析学习。
THE
END
祝你成功！
Thanks

展开更多......

收起↑