Excel技能树
2022-09-27 14:11:55 0 举报
AI智能生成
EXCEL知识大全
作者其他创作
大纲/内容
软件操作
排版基础
单元格格式
数据分列验证
数据有效性
允许”序列“
来源”是,否“
查找与替换
定位
定位到”空值“
输入某值后,【Ctrl+回车】全部填充
行列翻转
复制-选择性粘贴-“转置”
某列乘某值
某列×n — 复制n —选中某列 —选择性粘贴 —选“乘”——完成
建立副本
右键某个小工作簿-“移动或复制工作表”—工作簿“新工作簿”—“建立副本”
文档保护与打印
文档保护
另存为或者保存时有个“加密”提示,输入打开密码和编辑密码
打印
设置打印区域:选中数据—”页面布局“—“打印区域”—“设置打印区域”
打印每页都有标题:“页面布局”—”打印标题或表头“—打印标题“顶端标题行”
快捷键
Ctrl+C 复制
Ctrl+V 粘贴
Ctrl+X 剪切
Ctrl+S 保存
Ctrl+A 全选
Ctrl+Z 撤销
Ctrl+Y 恢复
Ctrl+B 加粗
Ctrl+I 斜体
Ctrl+F 查找
Ctrl+H 替换
Ctrl+G 定位
Ctrl+N 新建工作簿
Ctrl+C 复制
Ctrl+C 复制
条件格式
条件格式样式:突出显示/数据条/色阶/图标集
条件格式应用:数值大小/文本信息/最前最后规则
自定义条件格式
突出显示
【选中要突出显示的某列数据】-突出单元格-其他规则-使用公式确定单元格形式-【$D7>$Z7】-设置字体颜色和底纹
📢注意:【$D7>$Z7】绝对引用行列标,具体行列数不绝对引用
项目选取规则
高于或低于n/前后n名
函数进阶
数学函数
逻辑函数
IF/True/False
=IF(你>50,“达标”,"不达标")
如果你大于50,就达标,否则不达标
=OR(P18>90,Q18<100)
OR(条件1,条件2,条件3……)
"或"的关系,【满足其一】就可以输出“True”,否则输出“False”
"或"的关系,【满足其一】就可以输出“True”,否则输出“False”
=and(P18>90,Q18<100)
and(条件1,条件2,条件3……)
"且"的关系,【全部满足】就可以输出“True”,否则输出“False”
"且"的关系,【全部满足】就可以输出“True”,否则输出“False”
=IF(AND(SUM(P6:Z6)>10,SUM(P6:Z6)>20),"好",“不好")
如果这些条件全满足,就输出”好“,否则输出 ”不好“
=IF(OR(SUM(P6:Z6)>10,SUM(P6:Z6)>20),"好",“不好")
如果这些条件满足之一,就输出”好“,否则输出 ”不好“
时间与日期函数
=Year(F5):提取年份数据
2022/1/20=2022
=Month(F5):提取月份数据
2022/1/20=1
=Day(F5):提取天数据
2022/1/20=20
=Date(F5,F6,F7)整合日期
2022 1 20=2022/1/20
=TODAY():今天日期
=datedif(F5,F6,"y"):起始到结束经历年数y/月数m/天数d
2022/01/22 ,2023/01/22=1
查找函数
vlookup/match/index/lookup
vlookup:参考的数据是列向
hlookup:参考的数据是横向
引用函数
offset/Indirect
文本函数
数据公式
Find/Left/Right/text/round/mid/len
整容函数:TEXT(把谁整容,怎么整)
数值位数:TEXT(P5,"0000") : 55=0055
日期格式:TEXT(P5,"yyyy.mm.dd") : 2022-5-31 = 2022.05.31
查找字符:Find(要查找的字符串,在哪里查)
=Find(6,F4): 123684=4 :代表在数字123684中截止6共有多少字符,共有1236(共4个字符)
四舍五入:Round(要四舍五入的数,小数点后保留几位)
=round(F5,2) :1.234=1.23,1.235=1.24
取整:Int(数值)
=INT(F5): F5单元格的数字是5.66→5(取整)
Left(文本,从左取几个)
Left(F5,2): 567=56
Right(文本,从右取几个)
Right(F5,2): 567=67
Mid(文本,从第几个截取,取几个)
Mid(F5,3,4): 56386877=3868
len(文本)有几个字符
len(F5):220-我=5
lenb(文本)有几个字符
len(F5):220-我=6
特殊:文字算2个字符
统计函数
SUM/count/sumif/countif/rank
Count:数值个数
Counta:非空值个数(空值就是空格,不包括0)
Countblank:空值个数(0值不算空值)
Countif:在范围内满足条件的个数
COUNTIF(范围,"条件")=COUNTIF(F5:F6,">60")
Countifs:多条件统计满足条件个数
COUNTIFS(范围1,"条件1",范围2,"条件2"……)=COUNTIF(F5:F6,"销售部",Q5:Q6,"男")
Sumif(条件区域,求和条件,求和区域)
=Sumif(地区代码列,F5,出勤小时数):F5代表010Y北京区,在多个地区中,想查找北京区的出勤时间总数
Sumifs(求和区域,第1个条件区域,条件1,第2个条件区域,条件2……)
=SUMIFS(S18:S21,Q18:Q21,Q18,P18:P21,P18)
SUMPRODUCT 累积求和
SUMPRODUCT(数组或区域1,[数组或区域2]……[数组或区域3])
=SUMPRODUCT(R19:$R$21,$S19:$S$21)
第一列+第二列的和、第1+2+3列的和
Averageif:条件下求均值
=Averageif(条件区域,求均值条件,求均值区域)
Averageifs:条件下求均值
=Averageifs(求均值区域,限定条件区域1(比如销售部),条件1(比如销售1部),第2个条件区域,条件2……)
=Averageifs(S18:S21,Q18:Q21,Q18,P18:P21,P18)
Rank:排名
RANK(要排位的数字,比较的范围,[升序1/降序0])
图表基础
6种常用图表
图表类型
柱状图/条形图/折线图/饼图/散点图
图表元素
标题/刻度/坐标轴/图例/网格线/自定义元素
图表美化
边框/背景/样式/主题
动态图表
数据透视表
基础
创建
图/表
数据源要求
每列数据的第一行包含【该列的标题】
数据源中不能包含【空行或空列】
数据源中不能包含【空单元格】
Ctrl+G 定位到”空值“
输入某值后,【Ctrl+回车】全部填充
数据源中不能包含【合并单元格】
数据源中不能包含【同类字段】
🌰:销售1部
操作
数据透视表的命名
数据透视表分析-左上角-命名数据透视表
右键数据-数据透视表选项-重命名
数据透视表的移动
数据透视表分析-移动数据透视表
数据透视表的删除
数据透视表分析-清除 或者 直接选中删除
字段
字段:代表列标题,每个字段代表一类数据
项:单个单元格内容
分类:筛选字段、行字段(表左侧)、列字段(表顶部)、值字段
筛选字段水平/垂直排列
数据透视表选项-显示字段-垂直/水平+每行显示几项
展开/折叠
数据透视表分析-最右边+-号
字段标题
数据透视表分析-最右边-字段标题
元素
字段/记录/项
选择某连续数据区域:先选中某首数据,按住shift键,再选中末数据
选择某不连续数据区域:Ctrl键
分析
排序/筛选/切片器
选中整个数据透视表
在某个单元格中鼠标变成黑色小箭头按住可以选中整行整列整个表
数据透视表-分析-选择-选择整个
刷新
手动刷新
数据源区域未更改
只是区域内的金额变动
右键-刷新
数据透视表分析-刷新数据
数据-全部刷新
数据源区域更改
数据透视表分析-更改数据源
自动刷新
打开工作簿自动刷新
右键数据透视表选项-数据-打开文件时自动刷新数据(其他的工作簿的数据也会自动更新,不仅仅是当下的这个表)
使用VBA实现自动刷新
插入-图形-右键制定宏-输入【ThisWorkbook.RefreshAll】-运行(当数据源变动时,点击图形实现更新)
另存为文件-Excel启用宏的工作簿
刷新后保持原列宽
布局和格式-取消勾选”更新时自动调整列宽“
清理垃圾条目
右键数据透视表选项-数据-保留删除的数据源项目-选择”无“
🌰12月份的数据源已删除,在数据透视表中也看不到12月份的数据
但!筛选时候有”12月“的勾选依然存在
🌰12月份的数据源已删除,在数据透视表中也看不到12月份的数据
但!筛选时候有”12月“的勾选依然存在
美化
样式/主题/布局
布局
压缩布局形势
行字段里有两项数据,在表里会在左侧【同一列】显示
大纲布局形式
行字段里有两项数据,在表里会在左侧以【两列】显示
表格布局形式
会对每一个行字段多加一行【汇总】数据
样式
a.设计-表格样式 b.亦可自定义保存
c.页面布局-主题
设置默认样式
设计-选择表格样式-右键-设为默认值
清除样式
设计-选择表格样式-清除样式
空白行
数据透视表-设计-空行-在每个项目后面插入空白行
分类汇总
数据透视表-设计-分类汇总-不显示/在底部显示/在顶部显示
合并字段项
右键数据透视表选项-布局-合并并使带标签的单元格居中
同时显示求和、均值等求值
右键行标签-字段设置-自定义-选中要求的值
行列总计显示
数据透视表-设计-总计-行列显示与否
右键数据表数据-数据透视表选项-汇总与筛选-勾选行列总计
数字格式
右键数据-数字格式
插入对数据的计算行/列
右键数据-数据透视表-分析-“字段、项目”-”计算字段“-命名计算行/列,输入公式
错值/空值
右键数据-数据透视表选项-对于空白值/空值替换为xx
切片器
插入切片器
使用切片器
共享切片器
前提要共享同一数据源
右键切片器-报表链接
更改切片器显示顺序
右键切片器-置于顶层
其他的垒加:点击上面”选项“-”上移一层“/”下移一层“
分组
数值按数据段分组
对行标签进行分组:右键-分组-划数据段 比如10~20、20~30
文本分组
对行标签的文本进行”分组“,单独会多出这部分的数据单成一组 比如:数据组1:aa、bb、cc
日期分组
日期形式但是”常规“格式——数据靠单元格【左边】——没有办法直接分组
日期形式是”日期“格式——数据靠单元格【右边】——可以直接分组
按年月日季度等分组-🌰原:5月13日、5月24日、6月11日→ 5月、6月...
百分比
子主题
动态
数据处理
排序
升序/降序/关键词排序/自定义排序
数据透视表排序
自动
右侧字段列表-选择升降序
数据透视表设计-报表布局-以表格形势-单成一列-筛选
数据选项卡-排序-升降序
右键数据-排序
手动
右键移动
手动拖动
按字母/笔画
右键数据-排序-其他选项
按求和项排序
点击筛选项标题下拉箭头-其他排序选项-按求和数值排序
按值排序(如求和项没有筛选选项):数据-排序
自定义排序
文件-表格偏好设置-自定义序列
筛选
自动筛选/高级筛选/自定义筛选
保留所选项目
连续选中要保留的项-右键-筛选-仅保留所选项目
定位
Ctrl+G-
合并
合并单元格内容/合并工作表
分列
单元格内容分列
保护工作表
保护/加密/隐藏
数据验证
验证条件
整数/小数/序列/日期/时间/文本长度/自定义
验证提示
提示/警告
自定义数据验证
实战
邮件合并
财务数据
人事数据
销售数据
0 条评论
下一页