Excel-公式

Excel的函数有上千种，常用的只有几十种，包含两个类别f(x)，f(x,y)，参数的类型又对应数字，文本，日期，整个的函数集合构成是2X3，掌握这个不等式，就可以轻松应对各种数据处理场景。

f(x)型

数字
=ROUND(4.56789,1)
=MOD(21,4)
=SUMIF(D37:D38,">1",E37:E38)

文本
=FIND("a","abc")
=LEFT("abc",2)
=RIGHT("abc",2)
=MID("abc",2,2)
=LEN("abc")
=LENB("字节")
="a"&"b"
=CONCAT("a","b")
=REPLACE("abc",1,1,"d")
=CLEAN(D11)
=TRIM(C11)
=SUBSTITUTE

时间
=DATE(2018,4,1)
=MONTH(NOW())
=DAY(NOW())
=YEAR(NOW())

cell型
=COLUMN(B4)
=ROW(B4)
=COUNTIF(A1:A3,A1)
=INDIRECT("A"&ROW())

类型互转
=VALUE("1")
=CHAR(64+1)
=IFERROR(TEXT(AB861-AC861,"hh时mm分ss秒"),"")
=ISNUMBER(1)

f(x,y)型

=vlookup
=find
=LOOKUP
=IF(2>1,2,1)
=IFERROR(2/0,1)
=ifs
=maxif
=minif

数组

=SMALL({1,7,2,4},1)
=LARGE({1,7,2,4},1)
=SUM({1,2,3})
=vlookup
=find