Excel表格学习

每次苦于Excel的公式计算，总是因为公式使用错误迟迟完成不了数据统计。别人家针对数据统计的分析很快就能出图交差，而自己苦苦加班才能完成。不要再一个个百度函数公式去玩Excel了，这张思维导图已梳理好Excel快捷键及函数公式，让你不再烦恼。

EXCEL办公

• 1. 常规
  • 1.1. Insert
    • 插入模式
  • 1.2. Delete
    • 清除
  • 1.3. Home
    • 返回起点
  • 1.4. End
    • 移至终点
  • 1.5. PgUp
    • 上一页
  • 1.6. PgDn
    • 下一页
  • 1.7. Esc
    • 取消、暂停、中断、返回某项操作
  • 1.8. Tab
    • 移至下一单元格或焦点
  • 1.9. Caps Lock
    • 大写字母锁定开关
  • 1.10. Ctrl
    • 单独使用无意义
  • 1.11. Alt
    • 配合字母激活各菜单项，配合数字输入ASCII字符
  • 1.12. Backspace
    • 返回上一级，删除前一字符
  • 1.13. Shift
    • 输入时，键位上档，操作时，方向操作
  • 1.14. Enter
    • 确认、继续、完成某项操作
  • 1.15. Win
    • 激活开始菜单或Win操作
• 2. 快捷键
  • 2.1. Ctrl+组合
    • 1. Ctrl+数字
      • Ctrl+1
        • 单元格格式
      • Ctrl+2
        • 加粗
      • Ctrl+3
        • 加斜
      • Ctrl+4
        • 下划线
      • Ctrl+5
        • 删除线
      • Ctrl+6
        • 对象切换
      • Ctrl+8
        • 分级
      • Ctrl+9
        • 隐藏单元格或者区域所在的行
      • Ctrl+0
        • 隐藏单元格或者区域所在的列
    • 2. Ctrl+字母
      • 1. Ctrl+A
        • 全选
      • 2. Ctrl+B
        • 加粗
      • 3. Ctrl+C
        • 复制
      • 4. Ctrl+D
        • 向下填充
      • 5. Ctrl+F
        • 查找
      • 6. Ctrl+G
        • 定位
      • 7. Ctrl+H
        • 替换
      • 8. Ctrl+I
        • 加斜
      • 9. Ctrl+K
        • 超链接
      • 10. Ctrl+L/T
        • 创建表
      • 11. Ctrl+N
        • 新建
      • 12. Ctrl+O
        • 打开
      • 13. Ctrl+P
        • 打印
      • 14. Ctrl+R
        • 向右填充
      • 15. Ctrl+S
        • 保存
      • 16. Ctrl+U
        • 下划线
      • 17. Ctrl+V
        • 粘贴
      • 18. Ctrl+W
        • 关闭当前浏览器页面
        • 关闭当前窗口
      • 19. Ctrl+Q
        • 格式
        • 图表
        • 汇总
        • 表
        • 迷你图
        • 表格中选取数据
      • 20. Ctrl+X
        • 剪切
      • 21. Ctrl+Y
        • 恢复
      • 22. Ctrl+Z
        • 撤销
    • 3. Ctrl+F
      • Ctrl+F1
        • 隐藏工具栏
        • 打开工具栏
      • Ctrl+F2
        • 打印预览
      • Ctrl+F3
        • 打开名称管理器操作新建
      • Ctrl+F4
        • 在允许同时打开多个文档的程序中关闭当前文档
        • 关闭工作簿
      • Ctrl+F5
        • 还原窗口
      • Ctrl+F7
        • 移动窗口
      • Ctrl+F8
        • 窗口大小
      • Ctrl+F9
        • 最小化窗口
      • Ctrl+F10
        • 最大化窗口
      • Ctrl+F11
        • 插入宏表
      • Ctrl+F12
        • 打开
    • 4. Ctrl+Shift+
      • Ctrl+Shift+（
        • 取消隐藏的行
      • Ctrl+Shift+&
        • 应用边框
      • Ctrl+Shift+-
        • 删除边框
      • Ctrl+Shift+~
        • 常规数字格式
      • Ctrl+Shift+$
        • 货币格式
      • Ctrl+Shift+%
        • 百分比格式
      • Ctrl+Shift+^
        • 指数格式
      • Ctrl+Shift+#
        • 日期格式
      • Ctrl+Shift+@
        • 时间格式
      • Ctrl+Shift+!
        • 千位值格式
      • Ctrl+Shift+“
        • 选择环绕区域
      • Ctrl+Shift+：
        • 插入时间
      • Ctrl+Shift+“
        • 快速选中单元格
      • Ctrl+Shift+=
        • 快速插入单元格
      • Ctrl+Shift+0
        • 复制
      • Ctrl+Shift+L
        • 筛选
      • Ctrl+Shift+T
        • 恢复浏览器关闭页面
    • 5. Ctrl+其他
      • Ctrl+Alt+A
        • 快速截图
      • Ctrl+Alt+Delete
        • 快速锁屏
        • 解除锁屏
      • Ctrl+
        • 中英文标点符号切换
      • Ctrl+Esc
        • 显示“开始”菜单
      • Ctrl+Shift
        • 不同输入法的快捷切换
      • Ctrl+空格
        • 中英文快捷输入法的切换
      • Ctrl+鼠标滚轮
        • 更改桌面上的图表大小
  • 2.2. Shift+组合
    • Shift+Enter
      • 转换到上面单元格
    • Shift+F10
      • 显示所选项的快捷菜单
    • Shift+Tab
      • 转换到左边的单元格
    • Shift+空格
      • 全角半角状态切换快捷键
  • 2.3. Win+组合
    • Win+E
      • 打开文件资源管理器
    • Win+M/D
      • 收起所有窗口
    • Win+L
      • 快速锁屏
    • Win+U
      • 轻松访问
    • Win+F
      • 查找
    • Win+R
      • 运行
    • Win+U
      • 控制面板
  • 2.4. F4
    • F4用途，重复前一次的操作
    • F4一键添加绝对引用“$”
    • F4比格式刷更快一步，同样F4键可以重复上一步对单元格格式的字体、对齐方式、数字等的操作。
    • F4快速合并单元格
    • F4快速插入或删除行/列
    • F4键快速删除工作表
    • F4一键切换单元格引用方式，选中单元格或单元格区域，F4一键切换引用方式，避免一个一个修改“$”
    • F4组合键使用,CTRL+F4，快速关闭工作簿,ALT+F4，快速关闭当前打开的程序
  • 2.5. F1-12
    • F1
      • 帮助
    • F2
      • 编辑
      • 重命名
    • F3
      • 粘贴名称
    • F4
      • 恢复
    • F5
      • 定位
    • F6
      • 切换工作区域
    • F7
      • 拼写检查
    • F8
      • 扩展模式
    • F9
      • 重新计算
    • F10
      • 激活各菜单项
    • F11
      • 创建图表
    • F12
      • 另存为
  • 2.6. 其他
    • Tab
      • 转换到右面的单元格
    • Alt+F11
      • 启动VBA
    • Alt+F4
      • 关闭
    • Alt+Enter
      • 自动换行
    • Alt+T+O
      • 快速打开Excel选项
    • Enter
      • 转换到下面单元格
• 3. 函数
  • 3.1. 条件函数
    • if
      • 条件函数（参数为：条件表达式，满足条件结果，不满足条件结果）
        • 查找重复内容
          • =IF(COUNTIF(A:A,A2)>1,"重复","")
        • 重复内容首次出现时不提示
          • =IF(COUNTIF(A$2:A2,A2)>1,"重复","")
        • 重复内容首次出现时提示重复
          • =IF(COUNTIF(A2:A99,A2)>1,"重复","")
        • 根据身份证号码提取性别
          • =IF(MOD(MID(A2,15,3),2),"男","女")IF(MOD(MID(A2,15,3),2),"男","女")
        • 自动录入时间
          • =IF(A2="","",IF(B2="",NOW(),B2))
          • 提示：此功能需要启用迭代计算，启用方法如下
          • 文件-选项-公式-在计算选项下，勾选启用迭代计算
    • ifs
      • 多个条件
  • 3.2. 统计函数
    • 求平均
      • average
        • 求平均，不能排除空格
      • averagea
        • 对文字进行求平均
      • averageif
        • 按条件统计平均值
        • =averageif(B2:B7,"男",C2:C7)
      • averageifs
        • 多条件统计平均值
        • =averageifs(D2:D7,C2:C7,"男",B2:B7,"销售")
    • 求大小/排名
      • max
        • 求最大值
        • =MAX (A:A)
      • large
        • 比较大小，可以求第几大
      • mix
        • 求最小值
        • =MIN(A:A)
      • small
        • 比较大小，可以求第几小
      • sumproduct
        • 中国式排名（相同成绩不占用名次）
          • =SUMPRODUCT((B$2:B$7>B2)/COUNTIF(B$2:B$7,B$2:B$7))+1
        • 统计不重复的个数
          • =SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9))
      • rank
        • 成绩排名，排名函数，第二个参数的引用值
        • =RANK.EQ(A2,A$2:A$7)
        • 数据排序:返回某一数值在一列数值中的相对于其他数值的排位
    • 求和
      • sum
        • 求和
        • SUM(A:A)
      • sumif
        • 条件求和
      • sumifs
        • 多个条件求和
      • 条件数据:计算符合指定条件的单元格区域内的数值和。
    • 计数
      • count
        • 对数字计数
        • =count(A:A)
      • counta
        • 对文字、数字计数
        • =counta(A:A)
      • countblank
        • 对空白单元格计数
        • countblank(A1,B1)
      • countif
        • 条件计数
        • =countif(B1:B7,">90")
      • countifs
        • 条件计数，多个条件计算值
  • 3.3. 文本函数
    • leftb
      • 拆分，利用searchb函数，文字与数字一起，拆分留下文字
      • =LEFTB(D12,SEARCHB("?",D12)-1)
    • midb
      • 拆分，利用searchb函数，文字与数字一起，拆分留下数字
      • =MIDB(D13,SEARCHB("?",D13),11)/MIDB(D13,SEARCHB("?",D13),11)
    • right
      • 从右边截取
        • 从文本最后一个字符开始，截取指定数目的字符。
    • mid
      • 从中间截取
        • 从文本的指定位置开始，截取指定数目的字符。
    • left
      • 从左边截取
        • 从文本第一个字符开始，截取指定数目的字符。
    • concatenate
      • 将多个区域或字符串中的文本引用组合起来
    • replace
      • 原参数
      • 第二个参数为从原参数从第几个开始
      • 从第二个参数开始需要替换的字节数
      • 被替换的新的文本/数字/符号等
      • 字符替换函数
    • text
      • 插入mid函数，也可以求出生年月日；也可用于计算间隔小时数（结束时间-开始时间，"h"）
        • =TEXT(MID(D2,7,8),"0000-00-00")
      • 根据身份证号码提取出生年月
        • =TEXT(MID(A2,7,8),"0-00-00")
      • 数字转换文本
    • value
      • 文本转换数值
  • 3.4. 数据函数
    • dcount
    • daverage
    • dsum
    • dget
    • dmax
    • dmin
    • 数据库函数需要提前写好条件
      • 在哪里找
      • 找谁
      • 条件
  • 3.5. 日期时间函数
    • edate
      • 时间函数，求转正时间（+1多少月之后的函数）/（-1多少月之前）
        • =edate(D11,+1)-1
        • =edate(D11,-1)-1
    • datedif
      • 计算天数:计算返回两个日期参数的差值。
      • 身份证提取年龄
        • =datedif(TEXT(MID(A2,7,8),"0000-00-00"),TODAY(),"y")
      • 根据出生年月计算年龄
        • =datedif(A2,TODAY(),"y")
      • 外来函数，计算年龄/工龄（用于求年月日，还可用于求工龄）表周岁（三个参数分别为：从什么时候开始，到什么时候结束，年/月/日）
        • =datedif(A2,TODAY(),"y")
    • date
      • 日期函数，插入mid函数可以计算出生年月日
        • =date(MID(D3,7,4),MID(D3,11,2),MID(D3,13,2))
      • 显示日期:给出指定数值的日期。
    • year
      • 用于求虚岁
        • =year(TODAY())-year(D5)+1
      • 提取年份
        • =year(D5)
    • month
      • 提取月份
        • =month(D5)
      • 求出指定日期或引用单元格中的日期的月份。
    • day
      • 求出指定日期或引用单元格中的日期天数。
      • 提取日期
        • =day(D5)
    • now
      • 显示日期:时间给出当前系统日期和时间。
    • today
      • 显示日期:给出系统日期
    • weekday
      • 星期计算:给出指定日期的对应的星期数。
  • 3.6. 查找引用函数
    • vlookup
      • 找谁
      • 在哪里找
      • 返回第几列
      • 模糊1/精确0
      • =VLOOKUP(A2,表1[#全部],2,0)
        • 条件定位:在数据表的首列查找指定的数值，并由此返回数据表当前行中指定列处的数值
    • search
      • 要查找的数据
      • 原始数据
      • 要查找的数据出现的位置
    • indirect
      • 二级菜单建立
      • =Indirect($A2)
  • 3.7. 其他函数
    • replace
      • 隐藏身份证号码
      • =REPLACE(A1,7,4,"****")
    • isodd
      • 判断奇偶函数
      • 根据身份证判断性别
        • =IF(ISODD(MID(A2,17,1)),"男","女")
    • index
      • 提取不重复的内容
      • =INDEX(A:A,1+MATCH(,COUNTIF(C$1:C1,A$2:A$10),))&""
    • freauency
      • 各分数段的人数
      • 同时选中E2:E5，输入以下公式，按Shift+Ctrl+Enter FREQUENCY(B2:B7,{70;80;90})
    • mod
      • 数据计算:求出两数相除的余数。
    • lower
      • 将英文单词所有字符转换为小写形式
    • upper
      • 将英文单词所有字符转换为大写形式
    • proper
      • 将各英文单词的第一个字符转换为大写形式，其他小写
• 4. 函数运用
  • 4.1. 身份证提取相关信息
    • 性别
      • =IF(ISODD(MID(A2,17,1)),"男","女")
    • 出生
      • =DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2))
      • =TEXT(MID(A2,7,8),"0000-00-00")
    • 年龄
      • =DATEDIF(A2,TODAY(),"y")
    • 籍贯
      • =VLOOKUP(LEFT(A2,2),参数表!$A$1:$B$35,2,0)
    • 地址
      • =VLOOKUP(LEFT(A2,6),参数表!$A$1:$B$8232,2,0)
  • 4.2. 入职日期
    • 转正
      • =EDATE(A2,1)-1
    • 工龄
      • =DATEDIF(A2,TODAY(),"y")
    • 在职状态
      • =IF(离职时间,"离职","在职")
    • 在职月数
      • =IF(R2="在职",DATEDIF(入职时间,TODAY(),"m"),DATEDIF(入职时间,离职时间,"m"))
  • 4.3. 合并单元格计数
    • 选中需要计算的单元格
    • 输入公式=COUNTA(B2:B14)-SUM(C3:C14)
    • 然后按组合件Ctrl+Enter
  • 4.4. 合并单元格排序
    • 选中所有需要合并排序单元格，如选中A2:A11,在编辑栏中输入公式max(A$1:A1)+1,再按Ctrl+Enter
    • 选中所有需要合并排序单元格，如选中A2:A11,在编辑栏中输入公式counta(A$1:A1),再按Ctrl+Enter
  • 4.5. 单个单元格内容计数
    • =LEN(B2)-LEN(SUBSTITUTE(B2,"，",""))+1
    • =LEN(B2)-LEN(SUBSTITUTE(B2,"，",""))
  • 4.6. 判断相同值
    • delta
      • =DELTA(B2,C2)
    • exact
      • =EXACT(B2,C2)
    • countif
      • =COUNTIF(B2,C2)
    • if
      • =IF(B2=C2,"相同","不相同")
    • countif(范围,任意值)>0
      • =countif(E:E,J1)>0
  • 4.7. 判断闰年平年
    • 能被400整除，为闰年
    • 能被4整除，但是不能被100整除，为闰年
    • 条件
    • =IF(OR(MOD(A2,400)=0,AND(MOD(A2,4)=0,MOD(A2,100)<>0)),"闰年","平年")
• 5. 超链接
  • 5.1. 公式-定义名称-名称
    • sht
    • 引用位置公式
      • =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),)&T(NOW())
  • 5.2. A2单元格公式
    • =IFERROR(HYPERLINK("#"&INDEX(sht,ROW(A3))&"!a1",INDEX(sht,ROW(A3))),"")
  • 5.3. 返回各表单
    • =HYPERLINK("#目录!a1","返回")
  • 备注：名称不能特殊符号，否则引用无效
• 6. 数据透视表
• 7. VBA