涨停公式源码EXCEL最有用的函数公式用法大全

2020年9月20日发(作者：仇远)
常用函数:
1、VLOOKUP（查找目标，查找范围，返回值的列数，精确OR模
糊查找)
2、LEFT就是提取该数值的前几位，LEFT(H2，2)就是H2的前2
位。
3、IF
IF
4、四舍五入:
=ROUND(B5,2) =ROUND(SUM(G12:G14),3)
’2,3为保留小数的位数’
5、直接舍去:
=ROUNDDOWN(B5,2) =ROUNDDOWN(SUM(G12:G14),3)
’2,3为保留小数的位数’
6、COLUMN(B1) 返回值1
7、COUNTIF：求得分大于等于80分小于90分的学生数
=COUNTIF(B2:B20,
8、sumif
=sumif(range，criteria，sum_range)
Sumif（条件区 域，求和条件，实际求和区域），第二个求和条件参
数在第一个条件区域里。

6、access。
1、excel单元格之中同时按键盘上的Alt和Enter组合键， 这时光标就在同一个单元格内
切换到下一行


5、单元格的“绝对引用”、“混合引用”和“相对引用”：
绝对引用：绝对地址 =$$A$$1 复制后永远引用相同单元格地址
混合引用相对引用: 绝对列=$$A1 绝对行=A$$1


数学和三角函数
1.=IF(A1>0,”增加”,”减少”)

2.交集运算=SUM(B7:D9 C7:E10)’中间用空格隔开’

3.乘方运算=5^5

4.整行、列 7:7 7:10 A:A A:C
eg:=SUM(7:7) =SUM(A:C)

5.1.四舍五入:
=ROUND(B5,2) =ROUND(SUM(G12:G14),3)
’2,3为保留小数的位数’

5.2.直接舍去:
=ROUNDDOWN(B5,2) =ROUNDDOWN(SUM(G12:G14),3)
’2,3为保留小数的位数’

6.三维引用:多张工作表同一单元格区域
=SUM(SHEET1:SHEET3!B7)
=AVERAGE(SHEET1:SHEET3!B7)

7.绝对地址 =$$A$$1 ’复制后永远引用相同单元格地址’
混合引用绝对地址:绝对列=$$A1 绝对行=A$$1

8.保护单元格区域内容：菜单栏→工具→保护→允许用户编辑区域

9.单元格编辑栏修改单元格内容，或按F2键修改

10.阿拉伯数字转成罗马数字，不能为负数，且必须小于3999
=ROMAN(number,form)’数字,格式’
=ROMAN(499,0) =ROMAN(499,1) =ROMAN(499,2)
=ROMAN(499,3) =ROMAN(499,4)
0’经典的’1’较简洁的’2’较简洁的’3’较简洁的’4’简化的’
TURE为经典的 FALSE为简化的

11.所有参数相乘=PRODUCT(4,5) =PRODUCT(A1:A3,C1C3)

12.产生随机数字=RANDBETWEEN(BOTTOM,TOP)
=RANDBETWEEN(1,100)
13.=SUBTOTAL(function_num,2,…ref29)
Function_Num的设置值
1
2
3
4
5
6
7
8
9
10
11



VLOOKUP（查找目标，查找范围，返回值的列数，精确OR模糊查
找)

所应用的函数
AVERAGE
COUNT
COUNTA
MAX
MIN
PRODUCT
STDEV
STDEVP
SUM
VAR
VARP
14.对满足条件的单元格求和
=SUMIF(range,criteria,sum_range)
=SUMIF(区域,条件,要求和区域)
range 要进行筛选的单元格区域
criteria 设置筛选的”条件”(条件可以是文字)
sum_range 实际求和的相对应单元格区域

1
2
3
4
5
6
7
8

A
123
456
789






B



SUMIF函数
1
2
3
4

C
100
200
300
公式
=SUMIF(A1:A3,”>600”, C1:C3)
=SUMIF(A1:A3,”>400”, C1:C3)
=SUMIF(A1:A3,”>100”, C1:C3)
=SUMIF(A1:A3,”<400”,C1:C3)

D



结果
300
500
600
100

日期与时间函数
1.=DATE(year,month,day)
year 1900～9999
month 若值大于12，则会将12的余数设置为月份，15=3月
day 若日数大于所指定月份的日数，则余数为该月份日数
eg：=DATE(2002,1,35)即为2002年2月4日
右键可在【设置单元格格式】选择日期类型

2.计算2个日期之间的相差天数
=DATE(2003,3,8)-DATE(2002,6,5)

3.系统时间=NOW() 系统日期=TODAY()

4.日期所对应的星期数值
=WEEKDAY(serial_number,return_type)
= WEEKDAY(序列号,返回值类型)
serial_number 可用DATE()函数输入
return_type 若为1或默认，则1代表周日，7代表周六
若为2，则1代表周一，7代表周日
若为3，则0代表周一，6代表周日
eg:=WEEKDAY(A1) =WEEKDAY(A1,2) =WEEKDAY(A1,3)

5.日期所对应的该星期为一年中的第几周
=WEEKNUM(serial_number,return_type)
=WEEKNUM(序列号,返回值类型)
serial_number 可用DATE()函数输入
return_type 1,一周是从周日开始计算
2,一周是从周一开始计算
eg:=WEEKNUM(A1,1) =WEEKNUM(A1,2)





信息函数

1. 计算指定区域空白单元格的数目=COUNTBLANK(range)

2. 测试值是否为数字=ISNUMBER(value)

3. 测试值是否为偶数=ISEVEN(value)

4.测试值是否为奇数=ISODD(value)

5.当前目录或文件夹的路径名称=INFO(“directory”)
逻辑函数

()所有的参数都为TURE时，结果才是TURE
=AND(logicall1, logicall2,…,logicall30)’ logicall1～30’
eg:
=AND(TRUE,FALSE)结果为FALSE
=AND(TRUE,TRUE)结果为TRUE
=AND(A1,A2)结果为FALSE
=AND(A1,A1)结果为TRUE
=IF(AND(A1>100,A4>90),”测试成功”,”测试失败”)

2.对参数求反=NOT(logicall)
Logicall为TRUE时结果为FALSE;
Logicall为FALSE时结果为TRUE
eg:
=NOT(FALSE)结果为TRUE
=NOT(TRUE)结果为FALSE
=NOT(1+1=2)结果为FALSE

()其中一个参数为TURE时，结果就为TURE
=OR(TRUE,FALSE)结果为TRUE
=OR(FALSE,FALSE)结果为FALSE
=OR(1+1=1,2+2=5)结果为FALSE =IF(OR(),””,””)
文本函数

1.将数字转换为泰文=BAHTTEXT(NUMBER) =BAHTTEXT(A1)

2.将数字四舍五入到指定的小数位数
=FIXED(number,decimals,no_commas)
=FIXED(数字,小数点位数,逻辑值)
number 要被进行四舍五入的数字
decimals 指定小数点位数，默认值为2
no_comma 默认值为FALSE或省略，则返回的文本中包含有逗点
9，876.10，若为TRUE则无逗点9876.10
=FIXED(-1234.567,-1,TRUE)结果为-1230
decimals 正数表示小数点后的位数，负数表示小数点左边位数。

3.将文本字符串中英文单词的第1个字母改为大写
=PROPER(text) text只能为单元格引用，英文须写在单元格。
***=LOWER(text)大写字母全部改小写
VLOOKUP函数

P(lookup_value,table_array,col_index_num,range_lo okup)
lookup_value 目前新文档查找目标
table_array 查找范围(绝对地址$$$$)要包含查找目标及目标参数
col_index_num 返回值的列数，是查找范围的第几列，非原表列数
range_lookup 精确OR模糊查找。0或FALSE表示精确查找；
1或TRUE表示模糊查找
切勿遗漏第四参数，若遗漏则默认值为模糊查找。
eg：
B13 =VLOOKUP(A13,$$B$$2:$$D$$8,3,0) 或
B13 =VLOOKUP(A13,$$B:$$D,3,0)
★目标表变动大可扩大预留空白区域或整列引用

选定公式内查找范围按F4插入绝对引用$$符号
★“跨表引用”查找范围目标表可以更改名称和所在工作簿位置
★“跨簿引用”目标表被删除后，数据→编辑链接→断开链接
★★★快速修改第三参数（返回值列数）：
方法1：另一空白行对应处写对应序数，然后第三参数引用单元格。
方法2：第三参数用“column”函数（函数意义“列”转换为“数字”）
A=1，B=2，C=3……
Eg：=VLOOKUP(A13,$$B$$2:$$D$$8,COLUMN(C1),0)
Eg：=IF(A13=””,””,VLOOKUP(A13,$$B$$2:$$D$$8,3,0)) 避免出现
#NA
★★如果返回值为#NA报错，而不想显示#NA而显示空白或其他
Eg：=IF(ISNA(VLOOKUP(A13,$$B$$2:$$D$$8,3,0),””,
VLOOKUP(A13,$$B$$2:$$D$$8,3,0))

数据有效性→序列→选定区域创建单元格下拉菜单
数据有效性跨表引用 备案表选定引用区域→公式→定义名称；操
作表数据有效性→序列→来源输入“=公式名称”

★★★复合查找
=IF（VLOOKUP=0,VLOOKUP返回值其他列，VLOOKUP）
=IF（VLOOKUP(A13,$$B$$2:$$D$$8,3,0)=0, VLOOKUP
(A13,$$B$$2:$$D$$8,4,0), VLOOKUP(A13,$$B$$2:$$D$$8,3,0))

★★★重复数据“双列多列结合查找”， “&”合并两列多列成为
“辅助列”，以“辅助列”为查找标的。







VLOOKUP的基础概念和一些日常碰到的问题。
例一
src=
g
zh-lightbox- thumb
data-original=
8_
这里就是一个vlookup函数的 最简单的雏形。通过李四名字进行查找到相应的成
绩。这里我先解释一下这个函数
=VLOOKUP(E2,A2:B4,2,FALSE)
E2代表了李四，就是用户查找内 容的条件，A2:B2代表的是需要查找的区域，2
代表的是该区域的第二列，就是成绩列，FALSE 是代表了精确查找，和TRUE（模
糊查找）相对，但是大多数情况下都是用FALSE，TRUE要使 用的时候情况比较特
殊。
vlookup的四个参数，第一：单元格。第二：区域(条件所在 列必须是所属区域
第一列）。第三：数字，必须是正数（不能超出你区域的总列数）。第四，一般
用false或0。
这里如果明白了，先恭喜你，你已经踏入了这个函数的门了。
例二：
例一只是引入门，我会在例二部分，充分的讲解该函数的使用和注意事项。
src=
g
zh-lightbox-thumb
data- original=
0_

表格有了变化，大家注意一下，现在问一个问题，如果我要 查找李四的年龄怎么
办，1，2，3，考虑完了，就是把函数中的列数选中为“3”，这里我要说一些注
意事项。
1、这里的“3”不是指该表格的第三列，而是选取范围的第三列，该表格的选取< br>范围是B2：E6，年龄就是为第三列。
2、“李四”是作为函数的检索条件，“李四”所在的 列，必须是在该函数的第
一列，如果B2：E6我改成A2：E6会是什么结果呢？如图

src=
g
zh-lightbox- thumb
data-original=
0_
是的，它出错了。
3、因为 所在列必须是第一列，所以也就只能提取右边的数值，不能提取左边的，
提取可以用index+mat ch或是if（1,0），不过这个不在这个教程内阐述。
4、如果一列里有出现多个“李四”的情况下，默认只提取第一个。
5、vlookup查找时注意查找值与查找区域的第一列的格式设置必须一致。 如果
不一致也会出错，如图：

src=
g
zh-lightbox-thumb
data- original=
e_

我这里使用学号进行检索姓名，但是A列和H2的格式不同，也无法得出结果。
6、如果要下 拉vlookup公式，在第二参数里面加上“$$”符号。没加“$$”，下
拉后，第二参数的区域就变动 了。所以需要加“$$”把你要查找的区域固定住。
如图：一个是没有加“$$”的截图，一个是加了“$$” 的截图。
src=
g
zh-lightbox- thumb
data-original=
f_
src=
g
zh-lightbox-thumb
data- original=
4_

注意看函数的位置，如果你是横拉就在字母前加“$$”，如果是竖拉，就在数字
前加“$$”。
例三：拓展用法，暂时还没想出太多的拓展情况，我先举出一个例子，就是函数
的复合使用。
src=
g
zh-lightbox- thumb
data-original=
4_

注意看我圈起来的部分，如 果是直接使用H2进行提取，肯定是无法识别“李四”
的成绩，因为有时候各地个人写表格的时候，要求 不同，但是碰到不一样的情况
怎么办呢，在函数上进行修改，LEFT就是提取该数值的前几位，LEF T(H2，2)就
是H2的前2位。所以
LEFT（“李四102”，2）＝李四