办公三件套之EXCEL

EXCEL

荣誉、自由、愉悦 -- 尘风风尘

一、EXCEL 介绍

1.1 EXCEL 简述

• EXCEL 概念：Microsoft Excel 是微软公司的办公软件 Microsoft office 的组件之一
• EXCEL 发展：1985 年第一款 Excel 诞生但只应用于 Mac 系统，其中文的译名为"超越"
• EXCEL 版本：Excel 97、Excel 2000、Excel 2003、Excel 2007、Excel 2016、Excel 2021
• EXCEL 作用：Excel 用于数据处理、统计分析、辅助决策操作，广泛应用于管理、统计财经、金融

1987 年第一款 Windows 系统 的 Excel 诞生

Excel 版本查看步骤：文件 -> 账户 -> 关于 EXCEL

WPS 是国产免费的但兼容性差，office 是国外收费的但兼容性好

微软的各版本操作系统及其 office 各版本正版软件都可在 MSDN 上下载

office 可通过在淘宝购买激活码或下载破解版或购买电脑赠送的正版随附，进行正版激活认证

1.2 EXCEL 格式

• xls：Excel 早期表格格式（Excel 2003 及以前 EXCEL 版本）
• xlsx：Excel 目前表格格式（Excel 2007 及之后 EXCEL 版本）
• xlsm：Excel 启用宏的工作簿（xlsm 与 xlsx 基本无异，用 Excel2007 及以上版本就可打开）
• xlsb：Excel 二进制工作表（表格体积更小、打开速度更快，可保存宏代码但兼容性比 xlsx 差）

相同表内容的不同格式占用大小：xls > csv > xlsm > xlsx > xlsb

CSV 是逗号分隔值文件，其以纯文本形式存储表格数据（数字和文本），可用 Excel 软件打开

EXCEL 的不同格式可互相转换，但需安装 Excel 2007 及其以上版本（"另存为" -> "保存类型"）

xls 格式的表格仅能储存 65536 行（row）、256 列（column），csv 格式的原表公式、格式设置会丢失

1.3 EXCEL 表格

• EXCEL 工作簿：各种格式的 EXCEL 文件
• EXCEL 工作表：工作簿中具体的分组文件
• EXCEL 表格：工作表中的具体键入数据的区域

EXCEL 工作表也叫做 WorkSheet，简称为 Sheet

EXCEL 工作簿包含 EXCEL 工作表，EXCEL 工作表包含 EXCEL 表格

1.4 EXCEL 数据

• 文本格式存储的数据：系统默认靠左对齐
• 数值方式存储的数字：系统默认靠右对齐

EXCEL 的数据分为文本、数值、货币、日期等，可通过"设置单元格格式 -> 数字"实现其数据类型的设置

文本型的数字（如：身份证号）的输入，可通过"设置单元格格式 -> 文本"或"在内容前加英文单引号"实现

1.5 EXCEL 提示

• 右上角红色三角：表示此处有注释，且会自动显示
• 右上角紫色三角：表示此处有批注，且会自动显示
• 左上角绿色三角：表示此处有函数键入，若正确显示为效果
• 左上角绿色三角：表示此处值的引用有误，若错误且显示为 #N/A
• 左上角绿色三角：表示此处函数名称有误，若错误显示为 #NAME!
• 左上角绿色三角：表示此处函数参数有误，若错误且显示为 #VALUE!
• 内容全部变成＃：表示此处的内容超出单元格长度，调整之后便可正常显示内容

#NAME! 代表的意思是函数名键入错误

#VALUE! 代表的意思是函数键入的参数错误

#N/A 代表的意思是函数中存在值不可用（如：VLOOKUP 函数没有设置绝对参照，导致数据引用错误）

1.6 EXCEL 常识

• 储存格：组成工作表的单元格
• 参照位置：以列行的形式在左上角显示
• 时间格式：Excel 默认识别的时间格式以"/"分隔
• 公式格式："=参照位置1 计算符号 参照位置2"，如：= A1 * A2

Excel 允许像数学一样建立计算表达式（公式）

1.7 EXCEL 图表

• 圆形图：反应数据间的比例关系
• 散点图：比较两组数据间的关系
• 折线图：反应数据持续变化关系

一表胜千言，请尽量使用图表去展示数据间的关系

二、EXCEL 函数

2.1 EXCEL 函数简述

• 函数格式：=函数名(引数)
• 函数概念：用于数学计算、数据分析的特殊公式

引数指的是引用的单元格数据

引数也叫参数，若它为字符串内容需用双引号括起来

辅助栏可帮助简化函数的参数键入，因为可用单元格来代替有些参数内容的键入

2.2 EXCEL 函数键入

• EXCEL 函数键入方式一：点击"fx"图标
• EXCEL 函数键入方式二：单击"自动求和"图标
• EXCEL 函数键入方式三：在单元格内键入"=函数名(引数)"

在点击 fx 图标后，还可搜索查看函数的官方说明

"自动求和"图标的函数较少，但基本满足常用的需求

最常用的最强大的也是第三种函数键入方式但稍嫌麻烦

2.3 EXCEL 函数符号

• ""：字符串键入
• ","：函数间的多参数键入
• "$"：绝对参照即锁定单元格范围
• ":"：多单元格内容（单元格范围）键入
• "&"：多函数键入或连接字符串内容和引用的单元格内容

2.1 EXCEL 数学函数

• MOD：取模
• SUM：求和
• SUMIF：特定求和
• COUNTIF：特定计数
• ROUND：四舍五入到指定位数
• COUNT：计数（仅限数字计数）
• COUNTA：计数（所有非空计数）
• COUNTIF：计数（单条件筛选计数）
• COUNTIFS：计数（多条件筛选计数）

函数名和函数参数的书写不区分大小写

单元格内容的引用分为相对参照（引用位置自动变化）、绝对参照（引用位置固定不变）

函数参数的位置引用可用"鼠标拖动"或"[$]列标行号:[$]列标行号"（绝对参照）实现

2.2 EXCEL 查找函数

• ROW：行号
• COLUMN：列标
• ADDRESS：列标行号
• VLOOKUP：纵向查找

EXCEL 二维表格表示形式为：先列后场景行，即列标行号

VLOOKUP 查找函数的参数的"TRUE"/"FALSE"取值，可用数字 0/1 替代

若只显示键入的完整函数而不显示其执行效果，需将其数据类型从"文本"改成"常规"

VLOOKUP 原理为先列再行，其格式：VLOOKUP(查询关键字,查询范围,查询列数,查询模式)

VLOOKUP 查找函数的精确查找相当于直接给了精确的定位，而模糊查找相当于还需再给更精确的定位

2.3 EXCEL 逻辑函数

• if：单条件判断或多条件判断
• OR：若函数的任一参数均为 TRUE，则返回 TRUE（或操作，有真为真）
• AND：若函数的所有参数均为 TRUE，则返回 TRUE（与操作，同真为真）

if 函数最常用，其余逻辑函数基本没啥实际使用的场景了解即可

逻辑函数需通过"逻辑函数(函数名(参数))"的形式使用，如：AND(SUM(A1))

if 多条件判断在 Excel2016 中用 ifs（127个条件） 替代，否则用 if 嵌套（巢状if）【64个条件】替代

AND 和 OR 的 True 及False 会随着不同函数的参数而不同，如：SUM 的 True 是有数据、False 是无数据

2.4 EXCEL 信息函数

• CELL：单元格信息
• COUNTBLANK：空白单元格计数
• ISBLANK：若值为空返回 TRUE，否则 False
• ISERROR：若值为任何错误值包含 #N/A，返回 TRUE
• ISERR：若值为除 #N/A 以外的任何错误值，返回 TRUE
• IFERROR：判断函数执行是否正确，且可显示具体的错误提示信息

IFERROR 常和 IF、VLOOKUP 组合使用以验证数据

函数的参数说明会自动提示，如：cell 函数的 info_type 参数

2.5 EXCEL 时间函数

• DATE：生成日期格式（年/月/日）
• TODAY：返回今天日期（无需参数键入）
• TIME：生成时间格式（小时：分钟：AM/PM）
• DAY：生成具体某月份中的某日（从一月开始数）

时间函数主要用于自动生成的一些时间中

2.6 EXCEL 统计函数

• MAX：最大值
• MIN：最小值
• LARGE：指定大值
• SMALL：指定小值
• AVERAGE：平均值

LARGE 和 SMALL 函数中的参数 array 指选中的单元格区域

WPS 和 高版本的 EXCEL 中默认有计数、求和、平均值的提示

三、EXCEL 技巧

3.1 EXCEL 整体操作

• 筛选的条件：开始 -> 条件格式
• 删除重复值：选中区域 -> 数据 -> 删除重复值
• 建立工作组：CTRL + 鼠标左键点选要合并的工作表
• 移动工作表：鼠标右键要复制的工作表 -> 移动或复制
• 保护工作簿：选择文件 -> 另存为 -> 点击工具 -> 常规选项

工作组同时可设置相同操作

密码设置分为打开密码和修改密码

在设置密码时用鼠标左键点击而不是用 Enter ，因为它会被认为是密码的一部分，进而导致失败

3.2 EXCEL 打印操作

• 插入水印：插入 -> 艺术字
• 插入背景：页面布局 -> 背景
• 打印分页：视图 -> 分页预览
• 页眉页脚：视图 -> 页面布局 -> 页眉和页脚

分页中的分页线是蓝色的，可用它进行具体的分页打印

3.3 EXCEL 数据操作

• 定义名称：公式 -> 定义名称

• 验证数据：选中单元格区域 -> 数据 -> 数据验证

• 分割视窗：视图 -> 拆分

• 自动排序：开始 -> 排序和筛选

• 绘制边框：开始 -> 绘制边框网格

• 键入函数："=" -> "EXCEL 函数名"

• 单斜表头：设置单元格格式 -> 边框

• 数据筛选：开始 -> 排序和筛选 -> 筛选

• 双斜表头：插入 -> 形状 -> 直线（重复两次）

• 自定排序：开始 -> 排序和筛选 -> 自定义排序

• 跨栏置中：选中跨栏的区域 -> 开始 -> 合并后居中

• 冻结窗格：反选冻结的单元格区域 -> 视图 -> 冻结窗格

• 自动求和：选中求和的单元格区域 -> 开始 -> 自动求和

• 验证数据：数据 -> 数据验证 -> 设置 -> 圈释无效数据

• 创建图表：选中生成图表的数据区域 -> 插入 -> 推荐的图表

• 生成数列：将鼠标放置单元格右小角直至变成黑十字 -> 鼠标拖动

• 下拉列表：选中下拉的单元格区域 -> 鼠标右键 -> 从下拉列表中选择

• 自动适应：将鼠标放在表格中要自适应的列之间（间隔线） -> 鼠标左键双击

生成的数列会自动判断数列的公差

绘制边框中的橡皮擦可用 Shift 或鼠标选择

筛选的结果只是暂时隐藏了不符合筛选条件的结果，而不是删除掉了

为避免排序出现问题，请选中其中一个单元格而不是选整个单元格，来替代排序

图表的生成最好将标题也选上而不仅仅是选数据，因为这样可自动生成图表的轴说明

定义名称类似于定义变量，来替代一长串的单元格范围，可通过名称管理器进行增删改

隐藏的筛选结果可通过"开始 -> 排序和筛选 -> 清除"或"开始 -> 排序和筛选 -> 筛选"复原

四、EXCEL 快捷键

4.1 EXCEL Alt 组合键

• 表格换行：Alt + Enter
• 键入立方：Alt + 数字 179
• 键入平方：Alt + 数字 178
• 输人民币：Alt + 数字 165
• 键正负号：Alt + 数字 41408

方向键可实现单元格的移动

人民币符号也可通过输入法的输入实现

4.2 EXCEL Ctrl 组合键

• 表格打印：Ctrl + P
• 内容替换：Ctrl + H
• 内容查找：Ctrl + F
• 内容复制：Ctrl + C
• 内容粘贴：Ctrl + V
• 今日日期：Ctrl + ;
• 内容放缩：Ctrl + 滚轮
• 点选内容：Ctrl + 鼠标左键
• 复制工作表：Ctrl + 鼠标左键

EXCEL 的复制分为单元格复制、单元格内容复制

EXCEL 的粘贴默认会将单元格复制的格式一起粘贴，若要修改选择"选择性粘贴"

查找内容时可用"?"和"*"作为通配符，问号代表一个字符，星号代表一个或多个字符

若要查找问号和星号，需在问号和星号前加上"~"，若要查找破浪号，需在波浪号前加上"~~"

4.3 EXCEL Shift 组合键

• 插入批注：Shift + F2
• 向左移动：Shift + Tab
• 数据筛选：Ctrl + Shift + L

若不设置数据筛选的范围，默认是所有列进行数据的筛选

4.4 EXCEL 单独的功能键

• Esc：退出操作
• Tab：向右移动
• F4：添加绝对参照
• F2：选中单元格以便在其中输入内容
• delete：可删除选中的所有单元格内容
• Backspace：只能删掉选中单元格中的第一个单元格内容

F2 键的作用类似于鼠标左键双击单元格，用于选中单元格以便在其中输入内容

五、EXCEL 数据透视表

5.1 EXCEL 数据透视表简述

• EXCEL 数据透视表概念：枢纽分析表
• EXCEL 数据透视表概念：一种更加清楚清晰的数据表
• EXCEL 数据透视表作用：更加透彻地数据可视化，相当于积木的搭建，可随意分析数据

数据透视表的列增加可通过鼠标点选或鼠标拖动

5.2 EXCEL 数据透视表操作

• EXCEL 数据透视表创建步骤：插入 -> 数据透视表

数据透视表的创建需要注意其范围，否则会报错

六、EXCEL 宏编程

6.1 EXCEL VBA 简述

• VBA 概念：Visual Basic for Application，用 VB（Visual Basic） 对应用程序进行控制
• VBA 作用：用来扩展 Windows 的应用程序功能，特别是 Microsoft Office，也是视觉化 Basic 脚本
• VBA 产生：1993 年由微软公司开发，VBA 寄生于 VB，1994 年发行的 Excel5.0 才具备 VBA 的功能

因 VBA 基于 VB，所以 VBA 和 VB 语法特别相似

要运行 VBA 开发的应用程序，必须依赖它的父应用程序

VB 具有自己的开发环境，VBA 必须寄生于已用的应用程序上

VB 用于创建标准的应用程序，VBA 用于扩展已有应用程序功能如：自动化 EXCEL

Excel 宏是一个记录和回放工具即 VBA 编程，它可简单地记录 Excel 步骤，且宏会根据需要多次回放

VBA 宏可自动执行重复性任务，实质是一段在 Excel 环境中运行的编程代码，可用或不用编辑器就能编写宏

6.2 EXCEL VBA 操作

• VBA 打开操作：文件 -> 选项 -> 自定义功能区 -> 开发工具
• VBA 设置操作：开发工具 -> 宏安全性 -> 宏设置
• VBA 编写操作：开发工具 -> Visual Basic

VBA 宏默认是关闭的，需要自行手动打开

只有成功设置了"VBA 打开操作"，才能进行后续的"VBA 设置操作"、"VBA 编写操作"

6.3 EXCEL VBA 代码

Sub 第一个程序()
MsgBox "Hello World!"
End Sub

七、EXCEL 扩展资源

7.1 EXCEL 官方资源

• EXCEL 官方教程
• EXCEL 官方函数

7.2 EXCEL 三方资源

• MSDN
• 知乎 图文教程
• B站 EXCEL 视频教程
• VLOOKUP