EXCEL基础

Excel快捷键

常用快捷键

Fn

• 快速编辑功能
  • F2
  • F2能快速定位到单元格文本后面
• 重复上一步操作
  • F4
• 工作簿另存为
  • F12

ctrl+

• 插入表格
  • ctrl+T
• 显示替换功能菜单
  • Ctrl + H
• 常用黏贴
  • ctrl+v+ctrl+v
    • 黏贴数值
  • ctrl+v+ctrl+t
    • 转置
• 调出定位功能
  • ctrl+G
    • alt+s进入定位条件
    • K 空值
    • Y 可见单元格
• 显示或隐藏功能区
  • Ctrl + F1
• 快速填充
  • Ctrl + E
• 将活动单元格上一行数据复制到活动单元格
  • Ctrl + D
• 将活动单元格左侧数据复制到活动单元格
  • Ctrl +R
• 隐藏行
  • Ctrl + 9
• 取消隐藏行
  • Ctrl + Shift +9
• 隐藏列
  • Ctrl + 0
• 取消隐藏列
  • Ctrl + Shift +0
• 移动到工作簿上/下一个工作表
  • Ctrl +PageUp/PageDown

Alt+

• 创建图表
  • Alt + F1
• 分列
  • Alt+a+e
• 冻结拆分
  -Alt+w+f+f
• 选择可见单元格
  • Alt+；
• 增加/减少小数点
  • Alt+H+0/9
• 插入 SUM 求和函数
  • Alt + =
• 清除
  • Alt+E+A+A
• 将数据透视表中数字和日期字段分组
  • Alt + Shift + →
• 删除工作表
  • Alt+E+L
• 移动或复制当前工作表
  • Alt+E+M

Shift+

• 查找下一个匹配单元格
  • Shift + F4
  • 需先用ctrl+F 查找内容才能生效
• 插入或编辑批注
  • Shift + F2
• 插入新工作表
  • Shift + F11

Ctrl+shift+

• 查找上一个匹配单元格
  • Ctrl + Shift +F4
• 打开/关闭自动筛选
  • Ctrl + Shift +L
• 移除单元格所有框线
  • Ctrl + Shift + _
• 为单元格添加外边框
  • Ctrl + Shift + &
• 设置整数格式
  • Ctrl+Shift+1
• 应用不带小数的百分比格式
  • Ctrl+Shift+%

文件

• 打开工作簿
  • Ctrl + O
• 工作簿另存为
  • F12
• 选择性黏贴
• ctrl+alt+v
  • A：全部
  • F：公式
  • V：数值
  • T：格式
  • C：批注
  • N：验证
  • H：所有使用园主题的单元
  • X：边框除外
  • W：列宽
  • R：公式和数字格式
  • U：值和数字格式
  • G：所有合并条件格式
  • M：乘操作
  • D：加操作
  • I：除操作
  • S：减操作
  • B：跳过空单元格
  • E：转置
  • L：粘贴链接
• ctrl+v+ctrl
  • 黏贴
    • F:公式
    • O：公式和数字格式
    • K：保留源格式
    • B：无边框
    • W：保留原列宽
    • T：转置
  • 黏贴数值
    • V：值
    • A:值和数字格式
    • E:值和源格式
  • 其他黏贴选项
    • R：格式
    • N：黏贴链接
    • U:图片
    • I：链接的图片

定位

• F5/ctrl+G 调出定位功能
• alt+s进入定位条件
  • C 批注
  • O 常量
  • F 公式
  • K 空值
  • R 当前区域
  • A 当前数组
  • B 对象
  • W 行内容差异单元格
  • M 列内容差异单元格
  • P 引用单元格
  • D 从属单元格
  • S 最后一个单元格
  • Y 可见单元格
  • T 条件格式
  • V 数据验证

功能区

• 显示或隐藏功能区
  -Ctrl + F1
• Alt+系列快捷键
  • 筛选
    • alt+a+t
  • 分列
    • alt+a+e
  • 冻结拆分
    • alt+w+f+f

单元格拖放

• 剪切并粘贴
  • 拖放
• 复制并粘贴
  • Ctrl + 拖放
• 插入复制的单元格
  • Ctrl + Shift + 拖放
• 移动并插入
  • Shift + 拖放
• 导航
• 向上/下移动一个屏幕距离
  • PageUp、Down
• 向右/左移动一个屏幕距离
  • Alt + PageUp、Alt + Down
• 移动到数据区域边缘
  • Ctrl + ←/→/↑/↓
• 移动到行第一个单元格
  • Home
• 移动到第一个单元格
  • Ctrl + Home
• 移动到最后一个单元格
  • Ctrl + End

选择区域

• 选中一行
  • Shift + Space
• 选中一列（系统默认切换中英文，若excel使用需修改系统）
  • Ctrl + Space

按条件选择

• 选择区域中行内容不一致的部分
  • Ctrl + \
• 选择区域中列内容不一致的部分
  • Ctrl + Shift +|
• 选择公式直接引用的单元格
  • Ctrl + [
• 选择公式直接和间接引用的所有单元格
  • Ctrl + Shift +[
• 选择选定单元格直接从属的单元格
  • Ctrl + ]
• 选择选定单元格直接和间接从属的所有单元格
  • Ctrl + Shift +]
• 选择包含批注的单元格
  • Ctrl + Shift +O
• 选择可见单元格
  • Alt + ；

编辑单元格

• 单元格内向右或向左移动一个词（不止excel适用）
  • Ctrl + →/←
  • 在单元格编辑状态，使用此快捷键，向右或向左移动一个“词”。词由空格或其他标点符号分隔生成
• 单元格内向右或左选择一个字符
  • Shift + →/←
  • 在单元格编辑状态，使用此快捷键，向右或向左选择一个字符
• 单元格内向右或向左选择一个词
  • Ctrl + Shift + →/←
• 删除至当前行末尾的内容
  • Ctrl + Delete
  • 在单元格编辑状态，使用此快捷键，从光标当前位置至当前行末尾的全部内容。
• 删除光标左侧一个字符
  • Backspace
• 删除光标右侧一个字符
  • Delete
• 插入或编辑批注
  • Shift + F2
• 编辑活动单元格
  • F2
• 选择到单元格开始位置
  • Shift + Home
• 选择到单元格末尾
  • Shift + End
• 单元格内换行
  • Alt + Enter

输入数据

• 完成输入并向下移动
  • Enter
• 完成输入并向右移动
  • Tab
• 完成输入并向左移动
  • Shift + Tab
• 在选定的多个单元格输入相同的数据
  • Ctrl + Enter
• 输入当前日期
  • Ctrl + ；
• 输入当前时间
  • Ctrl + Shift +；
• 从上一行单元格复制其公式
  • Ctrl +'
• 从上一行单元格复制其值
  • Ctrl +Shift + '
• 从上一行单元格复制其值
  • Ctrl +Shift + '
• 快速填充
  • Ctrl + E
  • Ctrl+E是有自我学习能力的快捷键，只需要提供一个或多个样本，就能完成快速填充
• 完成输入并向上移动
  • Shift + Enter
• 将活动单元格上一行数据复制到活动单元格
  • Ctrl + D
• 将活动单元格左侧数据复制到活动单元格
  • Ctrl +R
• 插入超链接
  • Ctrl +K
• 显示下拉列表
  • Alt + ↓

设置单元格格式

• 将文本设置为加粗或删除加粗格式
  • Ctrl + B
• 将文本设置为斜体或删除斜体格式
  • Ctrl + I
• 为文字添加下划线或删除下划线
  • Ctrl + U
• 应用或删除文本删除线格式
  • Ctrl + 5
• 显示设置单元格格式菜单
  • Ctrl + 1
• 移除单元格所有框线
  • Ctrl + Shift + _
• 为单元格添加外边框
  • Ctrl + Shift + &

设置数字格式

• 应用常规数字格式
  • Ctrl + Shift +~
• 应用货币数字格式
  • Ctrl + Shift +$
• 应用不带小数的百分比格式
  • Ctrl + Shift +%
• 应用带有两位小数的科学计数格式
  • Ctrl + Shift +^
• 应用带有年、月和日的日期格式
  • Ctrl + Shift +#
• 应用带有小时和分钟以及 AM 或 PM 的时间格式
  • Ctrl + Shift +@
• 应用带有两位小数、千分位分隔符和减号 (-)（用于负值）的数值格式
  • Ctrl + Shift + ！
• 设置整数格式
  • Ctrl+Shift+1
• 增加/减少小数点
  • Alt+H+0/9

函数与公式

• 输入数组公式
  • Ctrl + Shift +Enter
• 计算整个工作簿
  • F9
  • Shift + F9
• 强制计算所有打开的工作簿
  • Ctrl + Shift +F9
• 对公式的一部分求值
  • F9
• 展开或折叠编辑栏
  • Ctrl + Shift +U
• 显示输入函数参数菜单
  • Ctrl + A
• 显示名称管理器菜单
  • Ctrl + F3
• 以行或列标题为名称创建定义的名称
  • Ctrl + Shift +F3
• 在公式中粘贴名称
  • F3
• 接受推荐的函数
  • Tab
• 在绝对和相对引用的所有组合之间循环切换
  • F4
• 打开插入函数菜单
  • Shift + F3
• 插入 SUM 求和函数
  • Alt + =
• 循环显示单元格包含的公式和值
  • Ctrl + `
• 插入函数参数
  • Ctrl + Shift +A

行和列操作

• 显示插入对话框（插入行/插入列)
  • Ctrl + Shift +"+"
• 显示删除对话框（删除行/删除列)
• 删除内容
  • Delete
• 组合行或列
  • Alt + Shift + →
• 取消组合行或列
  • Alt + Shift + ←
• 显示或隐藏大纲符号
  • Ctrl + 8
• 隐藏列
  • Ctrl +0
• 取消隐藏列
  • Ctrl + Shift +0
• 隐藏行
  • Ctrl + 9
• 取消隐藏行
  • Ctrl + Shift +9

数据透视表

• 选中整个数据透视表
  • Ctrl + A
• 将数字和日期字段分组
  • Alt + Shift + →
• 将以前组合的一组单元格取消组合
  • Alt + Shift + ←
• 隐藏数据透视表的一部分
  • Ctrl +"-"
  • 使用此快捷键，隐藏数据透视表中所选项目。此功能与使用筛选功能，排除一部分数据的原理是相同的。因此，使用筛选功能可恢复显示隐藏的项目。

工作簿

• 在工作表、功能区、任务窗格和缩放控件之间切换
  • F6
• 插入新工作表
  • Shift + F11
• 删除工作表
  • Alt+E+L
• 移动或复制当前工作表
  • Alt+E+M
• 移动到工作簿上/下一个工作表
  • Ctrl +PageUp/PageDown
• 选择相邻的工作表（连续选择）
  • Shift + 鼠标点击
• 选择非相邻的工作表
  • Ctrl + 鼠标点击
• 开启或关闭 Scroll Lock 状态
  • Scroll Lock

对话框

• 移动到下一个命令
  • Tab
  • 在对话框界面，使用此快捷键，移动到下一个命令。
• 移动到上一个命令
  • Shift + Tab
• 移动到下一个选项卡
  • Ctrl + Tab
• 移动到前一个选项卡
  • Ctrl + Shift +Tab
• 选中或取消选中复选框
  • Space
• 应用命令并关闭对话框
  • Enter
• 取消并关闭对话框
  • Esc

EXCEL常见功能

认识EXCEL

1. 窗口对照
   1. 视图-新建窗口-全部重排
   2. 修改文档时，两边窗口都会刷新
2. 冻结窗口
   1. 冻结拆分
   2. 点击一单元格，其上面和左面都被冻结
3. 自定义填充

文件-选项-高级-编辑用于排序和填充序列的列表：编辑自定义列表-输入序列-输入一个后enter-添加-确定

EXCEL格式设置

使用单元格格式工具美化表格

1. 合并单元格
   1. 每行合并：合并后居中-跨域合并（以行为单位，每一行都会合并）
2. 斜线单元格
   1. 右键-设置单元格-边框-加斜线-输入文字-换行输入-左对齐-空格调整

单元格数字格式

1. 利用数字格式隐藏数据

自定义输入;;;

2. 修改日期
   1. 星期：自定义aaaa
   2. 周：自定义"周"aaa
3. 文本转数值
   1. 复制1这个数字-右键所选列-选择性黏贴-乘

使用"分列"工具

1. 文本格式（如2022/04/01）转日期格式
   1. 数据-分列-完成

直接设置文本格式是不可以把文本转换成其他格式的

Excel 查找、替换及定位

查找与替换

定位工具

1. 批量填充
   1. 选定一段区域，输入内容，ctrl+enter后可全部填充
2. 定义名称
   1. 在名称框直接输入名字，下一次可以快速查找

EXCEL排列与筛选

排序

1. 利用排序插入行（实现隔行插入）
   1. 将需要隔行插入的记录加入辅助列（序号）后进行排序
2. 打印时每页加入标题行
   1. 页面布局-工作表选项-工作表-顶端标题行-选中标题行

筛选

1. BUG
   1. 问题：复制筛选后的数据，黏贴有时会出现全部的数据
   2. 解决：定位条件-可见单元格-复制
2. 高级筛选
   1. 基本步骤：数据-筛选-高级
   2. 筛选不重复数据：列表区域选取不重复数据区域-勾选“选择不重复的记录”
   3. 同行表示且，不同行表示或

分类汇总与数据有效性

1. 使用分类汇总批量合并内容相同的单元格
   1. 排序-数据-分类汇总-分类字段和选定汇总项都共同选择所需区域-汇总方式为计数-查找和选择-定位条件-空值-合并后居中-分类汇总-全部删除-格式刷（把隔壁空白列的格式复制到所需列中）
2. 数据有效性
   1. 设置整数数据有效性：数据-数据有效性（数据验证）-允许-整数
   2. 设置文本长度数据有效性：数据-数据验证-允许-文本长度
   3. 设置序列数据有效性（规定只能输入某些关键词）：数据-数据验证-允许-序列-手打关键词（用英文状态下的,连接）
   4. 数据有效性的其他设置
      1. 单元格信息保护
         1. 数据-数据验证-允许-自定义-公式-0
      2. 输入法切换
   5. 数据有效性不能用格式刷来继承

数据透视表

1. 组合

   1. 在 Excel 2016 中，使用数据透视表汇总日期字段时，Excel 会自动按年、季度和月组合源数据中的日期
      1. 若日期为文本格式，将无法组合，需要将日期改为常规格式（用分列）
   2. 手动组合
      1. 右键某一日期-组合-步长勾选
   3. 添加计算字段
      1. 分析-字段,项目和集-计算字段-公式
      2. 看新增字段公式
         1. 分析-字段,项目和集-列出公式

2. 通过数据透视表自动建立多张工作表

   1. 建立数据透视表-将数据拖拽到报表筛选字段-再拖拽到值字段-点中数据透视表-分析-选项-显示报表筛选页-确定
   2. 删除数据透视表：选中多张工作表-然后选中复制空白区域-覆盖数据透视表

函数

1. 常用函数
   1. SUMPRODUCT函数
      1. SUMPRODUCT 函数在给定的多个数组中，将数组间的对应元素相乘，返回乘积之和
   2. SUMIFS
      1. =SUMIFS(sum_range, criteria_range1, criteria1,[criteria_range2, criteria2], ...)
      2. =SUMIFS(求和区域, 条件区域1, 条件1, [条件区域2, 条件2], ...)
   3. CONCATENATE
      1. CONCATENATE 函数返回一个或多个字符串合并后的字符串
         1. =CONCATENATE( text1, [ text2, ... text_n ] )
         2. =CONCATENATE( 文本1, [文本2, ... 文本_n ] )
      2. 在 Excel 2016 版本中已由 CONCAT 函数替换
         1. =CONCAT( text1, [ text2, ... text_n ] )
         2. =CONCAT( 文本1, [ 文本2, ... 文本_n ] )

参考资料

• 懒人EXCEL：https://www.lanrenexcel.com/