

首页  思维导图  详情



Excel技能树

2022-09-27 14:11:55   0  举报





AI智能生成

EXCEL知识大全

教程

数组

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 复制

条件格式

条件格式样式：突出显示/数据条/色阶/图标集

条件格式应用：数值大小/文本信息/最前最后规则

自定义条件格式

突出显示

【选中要突出显示的某列数据】-突出单元格-其他规则-使用公式确定单元格形式-【$D7＞$Z7】-设置字体颜色和底纹

📢注意：【$D7＞$Z7】绝对引用行列标，具体行列数不绝对引用

项目选取规则

高于或低于n/前后n名 

函数进阶

数学函数

逻辑函数

IF/True/False

=IF（你＞50，“达标”，"不达标"）

如果你大于50，就达标，否则不达标

=OR(P18>90,Q18<100)

OR(条件1，条件2，条件3……) "或"的关系，【满足其一】就可以输出“True”，否则输出“False”

=and(P18>90,Q18<100)

and(条件1，条件2，条件3……) "且"的关系，【全部满足】就可以输出“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种常用图表

图表类型

柱状图/条形图/折线图/饼图/散点图

图表元素

标题/刻度/坐标轴/图例/网格线/自定义元素

图表美化

边框/背景/样式/主题

动态图表

数据透视表

基础

创建

图/表

数据源要求

每列数据的第一行包含【该列的标题】

数据源中不能包含【<strike>空行或空列】</strike>

数据源中不能包含【<strike>空单元格】</strike>

Ctrl+G 定位到”空值“

输入某值后，【Ctrl+回车】全部填充

数据源中不能包含【<strike>合并单元格】</strike>

数据源中不能包含【<strike>同类字段】</strike>

🌰：销售1部

操作

数据透视表的命名

数据透视表分析-左上角-命名数据透视表

右键数据-数据透视表选项-重命名

数据透视表的移动

数据透视表分析-移动数据透视表

数据透视表的删除

数据透视表分析-清除或者直接选中删除

字段

字段：代表列标题，每个字段代表一类数据

项：单个单元格内容

分类：筛选字段、行字段（表左侧）、列字段（表顶部）、值字段

筛选字段水平/垂直排列

数据透视表选项-显示字段-垂直/水平+每行显示几项

展开/折叠

数据透视表分析-最右边+-号

字段标题

数据透视表分析-最右边-字段标题

元素

字段/记录/项

选择某连续数据区域：先选中某首数据，按住shift键，再选中末数据

选择某不连续数据区域：Ctrl键

分析

排序/筛选/切片器

选中整个数据透视表

在某个单元格中鼠标变成黑色小箭头按住可以选中整行整列整个表  

数据透视表-分析-选择-选择整个

刷新

手动刷新

数据源区域未更改

只是区域内的金额变动

右键-刷新

数据透视表分析-刷新数据

数据-全部刷新

数据源区域更改

数据透视表分析-更改数据源

自动刷新

打开工作簿自动刷新

右键数据透视表选项-数据-打开文件时自动刷新数据（其他的工作簿的数据也会自动更新，不仅仅是当下的这个表）

使用VBA实现自动刷新

插入-图形-右键制定宏-输入【ThisWorkbook.RefreshAll】-运行（当数据源变动时，点击图形实现更新）

另存为文件-Excel启用宏的工作簿

刷新后保持原列宽

布局和格式-取消勾选”更新时自动调整列宽“

清理垃圾条目

右键数据透视表选项-数据-保留删除的数据源项目-选择”无“ 🌰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-

合并

合并单元格内容/合并工作表

分列

单元格内容分列

保护工作表

保护/加密/隐藏

数据验证

验证条件

整数/小数/序列/日期/时间/文本长度/自定义

验证提示

提示/警告

自定义数据验证

实战

邮件合并

财务数据

人事数据

销售数据

 Collect

Get Started

钥匙导入excel文件流程图

 Collect

Get Started

Excel图表

 Collect

Get Started

精通EXCEL

 Collect

Get Started

主页-客户





0 条评论

下一页