亿图脑图MindMasterExcel表格学习
导图社区 Excel表格学习
- 4.7k
- 1.2k
- 231
- 举报
-
Excel表格学习
每次苦于Excel的公式计算，总是因为公式使用错误迟迟完成不了数据统计。别人家针对数据统计的分析很快就能出图交差，而自己苦苦加班才能完成。不要再一个个百度函数公式去玩Excel了，这张思维导图已梳理好Excel快捷键及函数公式，让你不再烦恼。
编辑于2019-11-25 07:27:49- 相似推荐
- 大纲
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
畅然
怎么使用？