Excel公式、图表_思维导图模板

Excel

函数公式

日期函数

获取当前时间 NOW

NOW()

详情网址

获取星期 WEEKDAY

WEEKDAY(A1，2)

在A1单元格中输入一个日期:2011年10月15日

TEXT(WEEKDAY(A1),"aaaa")

输出："星期六"

数字2 则1 至7 代表星期一到星期天

详情网址

获取天数 DAYS

DAYS(结束日期，开始日期)

详情网址

创建日期 DATE

DATE(年，月，日)

直接引用单元格来表示日期

详情网址

创建时间 TIME

TIME(时，分，秒)

直接引用单元格来表示时间

详情网址

获取周数 DATEDIF

DATEDIF（开始日期，结束日期，单位）

单位："y"返回整年数，"m”返回整月数，"d"返回整天数，"md”返回参数1和参数2的天数之差，忽略年和月，"ym“返回参数1和参数2的月数之差，忽略年和日，"yd”返回参数1和参数2的天数之差，忽略年。按照月、日计算天数

详情网址

获取当前日期 TODAY

TODAY()

详情网址

文本函数

文本提取函数

LEFT

left(A2,从左开始截取的字符数)

将A2单元格12345，的前3个数字保留

输出：123

详情网址

RIGHT

RIGHT(A2,从右开始截取的字符数)

将A2单元格12345，的后3个数字保留

输出：345

详情网址

MID

MID(A2,从第几位开始，截几位数)

将A2单元格12345，第2位开始，截2位

输出：23

详情网址

文本替换函数 REPLACE

REPLACE(A4,1,3,"@")

将 123456 的前三个字符替换为单个字符 @

输出：@456

详情网址

文本重复函数 REPT

REPT(A2，重复次数)

将A2单元格的1，重复4次

输出：1111

详情网址

文本合并函数

连接符&

CONCATENATE

CONCATENATE(A1,B1,C1,)

把多个字符文本或数值连接在一起，实现合并的功能

详情网址

CONCAT

CONCAT(A2:D2)

将多个区域和/或字符串中的文本组合起来

详情网址

TEXTJOIN

TEXTJOIN(",",TRUE,A2:A6)

","为分隔符号
TRUE为忽略空行
A2:A6代表选择的单元格

将单元格区域内的文本合并为由符号分割的一个内容

详情网址

PHONETIC

PHONETIC(A2:B6)

只对中文字符串单元格进行合并，不对单独数字进行合并

详情网址

格式化函数 TEXT

TEXT(A2，单元格格式)

单元格格式包含：“数字”、“日期”、“时间”、“货币”或“自定义”

详情网址

查询和引用函数

行列查找 LOOKUP

LOOKUP(查找值，查找范围，要获得的值)

LOOKUP要查询一个明确的值或者范围的时候(也就是知道在查找的数据列是肯定包含被查找的值)，查询列必须按升序排列

详情网址

按列查找 VLOOKUP

VLOOKUP(查找值，表格数组，列索引，匹配条件)

列索引：查找值在表格数组的第二列
匹配条件：精确匹配0

详情网址

按行查找 HLOOKUP

HLOOKUP(查找值，表格数组，行索引，匹配条件)

行索引：查找值在表格数组的第二列
匹配条件：精确匹配0

详情网址

选择查找 CHOOSE

CHOOSE(编号，区域1，区域2，...)

详情网址

按位置查找 INDEX

INDEX(数组，行编号，列编号，区域编号)

搜索位置 MATCH

MATCH(查找值，查找数组，匹配类型)

获取行列位置 XMATCH

XMATCH(查找值，查找数组，匹配模式，搜索模式)

字符串查找

SEARCH(待查找字符串，字符串，开始位置)

SEARCH("d",A1)

详情网址

FIND

FIND(要查找的字符串，包含字符串，开始位置)

详情网址

统计函数

条件统计函数

多条件计数 COUNTIFS

COUNTIFS（条件区域1，条件1，条件区域2，条件2...）

COUNTIFS(C:C,"小学")，其中第一个参数”C:C“数据范围，第二个”小学“为条件，意为在C列中一共有多少小学

详情网址

条件计数 COUNTIF

COUNTIF（区域，条件）

COUNTIF(A1:A10,">6")

大于6的单元格数

详情网址

统计非空个数 COUNTA

COUNTA（区域1，区域2...）

COUNTA(A1:C3)

详情网址

条件求和 SUMIF

SUMIF(条件区域，条件，求和区域）

SUMIF(B2:B10,E2,C2:C10)

详情网址

SUMIFS

条件最大值 MAXIFS

MAXIFS(最大范围，条件范围1，条件1)

最大范围：你要得出结果的位置
条件范围1：条件1所在的全部范围
条件1：你根据什么确定结果

详情网址

求和

数字求和 SUM

SUM（数值1，数值2）

详情网址

相乘后求和 SUMPRODUCT

SUMPRODUCT（数组1，数组2...）

实现功能：相乘后求和，单条件计数，多条件计数，多条件统计-1，多条件统计-2，跨列求和，生成二维汇总数据表，生成自动排名

详情网址

求平均值 AVERAGE

AVERAGE（数值1，数值2...）

AVERAGE（A2:C10）

详情网址

含数字格个数 COUNT

COUNT（区域1，区域2...）

COUNT(B2:B13)

详情网址

求余数 MOD

mod(A2,B2)

详情网址

逻辑函数

条件判断 IF

IF(B2>=80,"优秀",IF(B2>=60,"及格","不及格"))

B2为80的时候，输出优秀

详情网址

多条件判断函数 IFS+AND

IFS(B2<5000,200,AND(B2>=5000,B2<8000),400,B2>=8000,500)

详情网址

目的是：根据职工的产量多少，给职工颁发产量奖。产量<5000，奖励200元；5000<=产量<8000，奖励400元；产量>=8000，奖励500元

错误美化函数 IFERROR

VLOOKUP函数是查询函数，在查询的过程当中，如果没有查询的，将返回错误值，比如“#N/A”，IFERROR就是把错误变固定值或空值

IFERROR(IF(A2,'男','女'),'')

详情网址

匹配过滤 FILTER

FILTER(返回列，查找条件)

=FILTER(A2:A6,B2:B6="在职")

详情网址

wps需要先框选单元格，并在输入函数后按Ctrl+Shift+Enter

排名 RANK

RANK(A1，A1:A10,0)

A1单元格里的数字在A1到A10单元格数字中的排序，0代表降序

详情网址

技巧

找不同

两个表格数据的不同

先全选第一个表格的内容，点击条件格式，新建规则，选择使用公式确定要设置格式的单元格

在编辑规则里面，再选择第一个表格，按三下F4，取消锁定，输入小于大于号，再全选第二个表格，按三下F4,取消锁定

选择格式里面的图案填充，点击确定，文本有数字时格式需要转换

两列数据找不同

全选两列数据，Ctrl+/

或在第三个单元格里写入 =A1=B2

对比两个表格的数据差异

先复制一个表格，在第二个表格的第一格，选择性粘贴，运算位置选择减就可以了

手机号加密显示

=REPLACE(B4，4，"****")

B4是单元格
4是第四位开始加密
*是代替的字符

手动输入第一个，选择要填充的单元格，然后按Ctrl+E

隔行插入空行

在最边上写入序列号，复制一份放到空行底下，再点升序

隔两行同理

高级筛选

列表区域:选择查找的区域，带上表头
条件区域:在其他区域写上你想查找的数据，要带上原来的表头

详情网址

查看表格聚光灯效果

=OR(CELL("row")=ROW(), CELL("col")=COLUMN())

首先全选表格，点击条件格式，选择新建规则，选择最后一项规则，输入公式，点击格式，选择一个边框，点击图案/填充，选择一个颜色，点击确定，最后点击任意地方，按下F9,效果就出来了

输入的快捷方式（搜狗输入法就可以）

生僻字

U模式

例:Unvge

中文状态的数字

V模式

V2345

日期格式

计算公式

V23+34

网页文字不能复制

Ctrl+P,在打印预览里就可以复制，再粘贴到文本里

数字显示为E+怎么恢复

选中数据，右键选择设置单元格对话框，点击自定义，把类型改为0

强制换行

Alt+回车键

下拉列表

WPS

选择表格，点菜单栏数据，有效性，设置，允许里选择序列

Offic

选择表格，点菜单栏，数据验证，设置，允许里选择序列

微调项

图表类

甘特图

可以实时修改时间

首先选择工作项目和开始时间两列，然后插入图表。
在所有图表中选择条形图，选择第二种堆积条形图，
这里的日期对应的数值就被显示在图表中了。

1、接着再选择图表，右击选择数据。添加数据源。
2、系列名称选择耗时。系列值选择耗时底下的数据。
3、然后确定。现在这里红色的就是耗时对应的条形图。

然后选择蓝色的条形图。选择数据点格式，设置为无填充。

日期范围，起始日期和结束日期的格式改为数值我们先调出来。（右键设置单元格格式）

然后选择下方坐标轴，右击设置坐标轴格式，最小值设置为44932，最大值设置为45279。

最后再把开始和结束日期设置为日期类型。

选择数据系列，右击选择添加数据标签，再调整字号。

最后再选择左侧坐标轴，右击设置坐标轴格式。选择逆序类别

左键选择图表，图表元素，图表标题

组合图（折线柱状）

框选数据，点插入，全部图表，选择折线图，点插入预设图表，选中图表，右键，点选择数据

选每列数据作为一列，
或者选每行数据作为一列，根据你的图表选择

这是每列

两个折线图更改为一个柱状图一个折线图

雷达图

框选数据，点插入，全部图表，选择雷达图，点插入预设图表

添加坐标轴
点击图表，右击更改图表类型，我们先把它更改成柱形图，点击纵坐标，点击填充。线条变成直线，再更改图表类型，把它再改成雷达图

电池百分比图

首先准备好数据以及辅助列值为一，点击插入柱形图，去掉网格线。

插入一个长方形，将填充颜色改为绿色。边框粗细改为1.5磅，颜色改为白色，
再复制一份，将填充颜色改为灰色，选中红色长方形复制并粘贴到百分比数据中，同样将灰色长方形复制到辅助列中

在右键设置数据系列格式。将两组数据填充分别改为层叠，再把数据重叠改为100%。最后再插入一个长方形，将填充颜色改为五，边框颜色改为黑色，调整到合适的大小，复制一份。

漏斗图

我们选择前三列数据，然后点击插入找到柱形图，在二维条形图中选择堆积条形图。这样的话就会插入一个条形图，

但是呢，它的系列名称与图表是完全相反的。这个时候呢，我们需要点击一下Y轴，然后呢按CTRL加一调出格窗口，勾选一下逆序类别，它们的顺序就会一模一样了，最后呢，我们将X轴删掉，将网格线呢也删掉，把图例也删掉。点击这个蓝色柱子，在上方找到形状格式，把它形状填充设置为无填充。

紧接着我为图表添加数据标签，先点击中间这个标签，然后呢按CTRL加一调出格窗口，找到标签选项勾选项单元格中的值，最后呢我们选择相较于总数的这一列转化率。点击回车

然后再把值和引导线都去掉，这样的话呢，就显示为总数的转化率，我们将字体颜色设置为白色。感觉条形图比较细，我们点击一下条形图，按CTRL加一调出格窗口，找到解析宽度设置为100%，来增大一下它的宽度，紧接着我们点击图表设计，找到添加图表元素，选择为线条，我们添加一下系列线，这样的话呢，就可以把它们连在一起了。