Excel常用功能与函数应用及表格设计

单元格格式—数字（日期录入）
2017.5.8
2017.05.8
2017-5.8 2017-05-08 17-5-8 17-05-08
你的日期录入对了吗
单元格格式—对齐

对横向合并，一般不用“ 合并单元格”，用“水平 对齐”中的“跨列居中”

对竖向合并，可以改变文 字方向

对备注单元格，可以使用 “缩小字体填充”
单元格格式—保护
• 对单元格内容进行锁 定，不允许修改（只 有工作表被保护时才 会生效）
• 在保护工作表时，隐 藏工作表中的公式
锁定
隐藏
单元格格式—页面设置

可以在工具栏添加页 面设置快捷键

任何页面设置，首先 必须确定打印机和 纸张类型
页边距：一般应选择 水平居中，左右边距 大多数情况下相等 垂直居中视情况选择 页眉/页脚：可以自定义页眉/页脚 工作表：顶端标题行和左端标题列 选项：视打印机是否支持，如：每张纸打印页数双面打印等
数学
100 99 98 85 96
英语
100 99 100 92 94
总分
298 298 297 277 285
数据处理—筛选

选定一定范围后， 可在相应的地方加 入筛选按钮 使用自动筛选
序号 1 2 3 4 5
姓名 李明 张军 田心 陈起 王五
语文 98 99 100 100 95
数学 100 98 85 99 96
培训目录

快捷键使用—如何提高工作效率 格式设计
函数应用
数据处理

其它应用
EXCEL表格设计
数据处理—排序

仅当单一表格，表 头连续时，才能用 工具条快速排序 对于有较复杂表头 的表格，一定要选 定范围后才能排序 用选定范围后排序 ，可针对任一局部 排序，如华南区域 客户 可以按行排序 对于不想改变最终 顺序的表格，可以 加入序号列后排序
对同一表、不同表、不同工作簿均以键盘方式引用
函数应用—工作表函数
IF VLOOKUP ABS DATE OFFSET UPPER SUM LEFT MAX YEAR TEXT LOWER SUMIF RIGHT MIN MONTH RANK ISERROR COUNT MID RAND DAY LEN AND OR TODAY COUNTIF &
序号 1 2 3
生产部 一组 二组 三组
吊杆 197 200 95
绳轮 198 184 95
轴 200 191 94
sumifs函数
公式 SUMIFS($E$23:$E$27,$C$23:$C$27,C41)
序号 1 2 3
生产部 一组 二组 三组
吊杆 197 200 95
绳轮 198 184 96
数据处理—数据透视表

数据透视表一定要有全部的列名称 关于页、行、列选择


展开其中某项数据
关于表头合并：两个表头合成一个 关于时间的组合 关于数据更新：从数据源更新（如果仅调整透视表结构，不涉及数据源）
数据处理—数据透视表应用
序号 1 2 3 4 5 6 7 8 9 10 生产部 一组 一组 二组 二组 三组 一组 一组 二组 二组 三组 姓名 李明 张军 田心 陈起 王五 李明 张军 田心 陈起 王五 吊杆 98 99 100 100 95 88 92 98 89 94 绳轮 100 98 85 99 96 99 95 90 95 92 轴 100 100 92 99 94 99 98 94 98 96
此处只对“自定义数字格式”进行说明： 设定常用数字格式：Crtl+Shift+! ，如：-12,345 对于七位发票号码654可以设置自定义格式：0000000 显示：0000654 自定义格式顺序：正数格式;负数格式;零格式;文本格式 如：#,##0;[红色]-#,##0;[黄色]0;[蓝色]@ ＃和0为数字占位符，＠为文本占位符，“，”（逗号）为千分位符号
轴 200 191 94
函数应用—vlookup函数
从下表分别提取个别员工当月绩效
公式 VLOOKUP(J23,$L$9:$O$18,2,0) 序号 1 2 3 4 5 6 7 8 9 10 生产部 一组 一组 二组 二组 三组 一组 一组 二组 二组 三组 姓名 李明 张军 田心 陈起 王五 黄七 赵六 王成 周玉 李有 吊杆 绳轮 98 100 99 98 100 85 100 99 95 96 88 99 92 95 98 90 89 95 94 92 轴 100 100 92 99 94 99 98 94 98 96 姓名 李明 李有 赵六 周玉 田心 吊杆 98 94 92 89 100 绳轮 100 92 95 95 85 轴 100 96 98 98 92

格式设计


函数应用
数据处理
其它应用
EXCEL表格设计
快捷键使用—如何提高工作效率
中 + E + D(或M) + F
Alt + F4 Alt + Tab CTRL+F或SHIFT+F5 ALT+ENTER F4 或 CTRL+Y F2 (SHIFT+F2)
打开资源管理器 清屏 搜索
关闭windows任务（当前操作） 清屏 显示“查找”对话框 在单元格中折行 重复最后一次操作 编辑单元格 (编辑单元格批注)

数据处理
其它应用
EXCEL表格设计
函数应用—基本公式

原则上输入任何公式，仅使用键盘，才能提高速度 使用公式，除录入文字名，必须全部采用半角方式，包括标点符号 在单元格开始输入，按“+”、“-”、“=”时，EXCEL视同输入公式 对任一单元格，按 F2进入编辑状态，再按 F2 进入选择单元格 公式中可以使用名称，名称必须与半角引号一起使用才正确 单元格的引用：绝对引用和相对引用，使用F4切换，注意切换顺序
格式设计


函数应用
数据处理
其它应用
EXCEL表格设计
单元格格式
对任何选定的单元格区域设置格式，只需对选定区域按 鼠标右键或键盘快捷菜单：设置单元格格式，包括：
单元格格式 → 单元格格式 → 概述 数字 对齐
功 能 键
单元格格式 →
单元格格式 → 字体
单元格格式 → 单元格格式 → 单元格格式 → 单元格格式 → 单元格格式 → 边框 保护 页面设置 数据有效性 条件格式
Excel常用功能与函数应用及表格设计
2017年7月19日
培训目的

设计简洁美观的数据表格 更加准确而高效地工作
学习使用常用公式及函数
提高数据处理技能 发挥个人创造能力
培训目录

快捷键使用—如何提高工作效率 格式设计
函数应用
数据处理 EXCEL表格设计
培训目录
快捷键使用—如何提高工作效率
序号 1 2 3 4 5 6 7 8 9 10
生产部 一组 一组 二组 二组 三组 一组 一组 二组 二组 三组
姓名 李明 张军 田心 陈起 王五 黄七 赵六 王成 周玉 李有
吊杆
98 99 100 100 95 88 92 98 89 94
培训目录

快捷键使用—如何提高工作效率 格式设计
函数应用
总分 298 277 285 297 298 总分 298 297 298 285
按单元格颜色
按数字筛选 （数学>95)
1 2 4 5
数据处理—分类汇总

坚持习惯：对选定范围进行分类汇总 如何将未汇总的内容如型号复制下来


如何删除分类汇总
如何将分类汇总的结果复制到一个新表中 将汇总结果中的“汇总”样全部删除
快捷键使用—如何提高工作效率
CTRL+1 显示“单元格格式”对话框
CTRL+B
CTRL+I CTRL+U CTRL+C CTRL+X CTRL+V
应用或取消字体加粗格式
应用或取消字体倾斜格式 应用或取消下划线格式 复制选定区域 剪切选定区域 粘贴选定区域
CTRL+Z
撤消最后一次操作
培训目录

快捷键使用—如何提高工作效率

wk.baidu.com
对大单元格应在“垂直对 齐”上选“居中”
单元格格式—字体 只有一句建议
对表格正文部分，
大多数时候都不需要--改变字体大小
只需要--按缩放打印即可实现
Ctrl+鼠标滚动轮（滚动→前大后小）
单元格格式—边框

框内一般用→单细线 外框一般用→加粗线 表头与内容连接处一 般→单双线或单细线

当然也可以选择任意 线型
序号 1 2 3 4 5 6 7 8 9 10 生产部 一组 一组 二组 二组 三组 一组 一组 二组 二组 三组 姓名 李明 张军 田心 陈起 王五 黄七 赵六 王成 周玉 李有 吊杆 98 99 100 100 95 88 92 98 89 94
2014年12月生产产值
标 出 最 后 2 名 的 数 值
公式 SUMIF($C$23:$C$27,C33, ($E$23:$E$27)
序号 1 2 3 4 5
2014年12月生产产值（件） 生产部 姓名 吊杆 绳轮 一组 李明 98 100 一组 张军 99 98 二组 田心 100 85 二组 陈起 100 99 三组 王五 95 96
轴 100 100 92 99 94
序号 1 2 3 4 5
姓名 李明 张军 田心 陈起 王五
语文 98 99 100 100 95
数学 100 98 85 99 96
英语 100 100 92 99 94
总分 298 297 277 298 285



序号
1 4 2 3 5
姓名
李明 陈起 张军 田心 王五
语文
98 100 99 100 95
英语 100 100 92 99 94
总分 298 297 277 298 285


关于高级筛选：数 据区、条件区与条 件、或条件
使用高级筛选获取 不重复记录

数据处理—筛选示例数（数学）
序号 1 2 3 4 5 姓名 李明 张军 田心 陈起 王五 语文 98 99 100 100 95
序号 1 3 5 2 4 序号 姓名 李明 田心 王五 张军 陈起 姓名 李明 张军 陈起 王五
单元格格式--概述

所有设置单元格格式只 能对选定的范围起作用 ，即Excel的任何操作 都必须有指定的对象

对选定的区域按鼠标右 键或键盘菜单键→设置 单元格格式(F)→即可 设置单元格格式
记住：不要选定整个表 、整行、整列来设置格 式，否则会无谓加大文 件长度

单元格格式—数字

d→天，m→月，y→年，如：将2003-3-15显示成：今天是3月15日，用 函数：=TEXT(单元格,“今天是m月d日”)或者自定义格式：“今天是 ”m“月”d“日”
*表示重复，如：**#,##0.00，将数字前加满*号


为在不同工作表中使用自定义格式，只需把含自定义格式的单元格复制 到其他工作表中就可以了。
计算各组当月生产产值
生产部 二组 三组 一组 总计 吊杆 387 189 377 953 绳轮 369 188 392 949 轴 383 190 397 970 姓名 陈起 李明 田心 王五 张军 总计
数学 100 98 85 99 96
语文 98 100 95 99 100 语文 98 99 100 95
英语 100 100 92 99 94
数学 100 85 96 98 99 数学 100 98 99 96 英语 100 92 94 100 99 英语 100 100 99 94
总分 298 297 277 298 285
函数应用—if函数
设定每门分数≥95分的则评定为“优秀” 公式1 IF(COUNTIF(D18:F18,“<95")>0,"-","优秀") 公式2 IF(OR(D15<95,E15<95,F15<95),"-","优秀") 公式3 IF(AND(D15>=95,E15>=95,F15>=95),"优秀","-")


单元格格式—数据有效性
可以任意定义序列或范围并通过菜单选择
单元格格式—条件格式

可以输入公式或数值 最多可以设三种条件格式，加无条件时的格式，即根据一个单元格的内容 最多可自动设置四种格式
单元格格式—条件格式
标出 2015年 产值前2 的月份
单元格格式—条件格式
2014年12月生产产值
序号 1 2 3 4 5 姓名 李明 张军 田心 陈起 王五 语文 98 99 100 100 95 数学 100 98 85 99 96 英语 100 100 92 99 94 总分 298 297 277 298 285 评定 优秀 优秀 优秀 -
函数应用—sumif函数
sumif函数 按组统计当月生产产值