Excel函数速查表—天极网
一、信息函数（9条） ............................................. 1
1.CELL .............................................................. 1 2.ERROR.TYPE ............................................... 1 3.INFO ............................................................... 1 4.IS类函数 ........................................................ 1 5.ISEVEN .......................................................... 2 6.ISODD ............................................................ 2 7.N ...................................................................... 2 8.NA ................................................................... 2 1.ADDRESS ..................................................... 8 2.AREAS ........................................................... 8 3.CHOOSE ....................................................... 9 4.COLUMN ....................................................... 9 5.COLUMNS .................................................... 9 6.HLOOKUP ..................................................... 9 7.HYPERLINK ................................................. 9 8.INDEX ............................................................ 9 9.INDIRECT .................................................... 10 9.TYPE .............................................................. 2 二、文本和数据函数（28条） ............................... 2
1.ASC ................................................................ 3 2.CHAR ............................................................. 3 3.CLEAN ........................................................... 3 4.CODE ............................................................. 3 5.CONCATENATE ........................................... 3 6.DOLLAR或RMB ......................................... 3 7.EXACT ........................................................... 3 8.FIND................................................................ 4 9.FINDB ............................................................. 4 10.FIXED ........................................................... 4 11.JIS ................................................................. 4 12.LEFT或LEFTB .......................................... 4 13.LEN或LENB .............................................. 4 14.LOWER ........................................................ 4
15.MID或MIDB ............................................... 5 16.PHONETIC .................................................. 5 17.PROPER ...................................................... 5 18.REPLACE或REPLACEB ........................ 5 19.REPT ............................................................ 5 20.RIGHT或RIGHTB ..................................... 5 21.SEARCH或SEARCHB ............................ 6 22.SUBSTITUTE .............................................. 6 23.T .................................................................... 6 24.TEXT ............................................................ 6 25.TRIM ............................................................. 6 26.UPPER ......................................................... 7 27.value ............................................................ 7 28.WIDECHAR ................................................. 7 三、逻辑运算符（6条） ......................................... 7
1.AND ................................................................ 7 2.FALSE ............................................................ 7 3.IF ..................................................................... 7 4.NOT ................................................................ 8 5.OR ................................................................... 8 6.TRUE .............................................................. 8 四、查找和引用函数（17条） ............................... 8
10.LOOKUP ................................................... 10 11.MATCH ...................................................... 10 12.OFFSET ..................................................... 10 13.ROW ........................................................... 11 14.ROWS ........................................................ 11 15.RTD ............................................................ 11 16.TRANSPOSE............................................ 11 17.VLOOKUP ................................................. 11 五、日期与时间函数（20条） ............................. 12
1.DATE ............................................................ 12 2.DATEVaLUE ............................................... 12 3.DAY .............................................................. 12 4.DAYS360 ..................................................... 12 5.EDATE ......................................................... 12 6.EOMONTH .................................................. 13 7.HOUR ........................................................... 13 8.MINUTE ....................................................... 13 9.MONTH ........................................................ 13 10.NETWORKDAYS ..................................... 13 11.NOW ........................................................... 14 12.SECOND ................................................... 14 13.TIME ........................................................... 14 14.TIMEVaLUE .............................................. 14 15.TODAY ....................................................... 14 16.WEEKDAY ................................................ 14 17.WEEKNUM ............................................... 14 18.WORKDAY ................................................ 15 19.YEAR ......................................................... 15 20.YEARFRAC .............................................. 15 六、数学和三角函数（60条） ............................. 15
1.ABS .............................................................. 15 2.ACOS ........................................................... 15 3.ACOSH ........................................................ 16 4.ASIN ............................................................. 16 5.ASINH .......................................................... 16 6.ATAN ............................................................ 16 7.ATAN2 .......................................................... 16 8.ATANH ......................................................... 16
9.CEILING ....................................................... 16 10.COMBIN .................................................... 17 11.COS ............................................................ 17 12.COSH ......................................................... 17 13.COUNTIF ................................................... 17 14.DEGREES ................................................. 17 15.EVEN .......................................................... 17 16.EXP ............................................................. 17 17.FACT .......................................................... 18 18.FACTDOUBLE ......................................... 18 19.FLOOR ....................................................... 18 20.GCD ............................................................ 18 21.INT .............................................................. 18 22.LCM ............................................................ 18 23.LN ............................................................... 18 24.LOG ............................................................ 19 25.LOG10........................................................ 19 26.MDETERM ................................................ 19 27.MINVERSE ................................................ 19 28.MMULT....................................................... 19 29.MOD ........................................................... 19 30.MROUND ................................................... 19 31.MULTINOMIAL ......................................... 20 32.ODD ............................................................ 20 33.PI ................................................................. 20 34.POWER ...................................................... 20 35.PRODUCT ................................................. 20 36.QUOTIENT ................................................ 20 37.RADIANS .................................................. 20 38.RAND ......................................................... 20 39.RANDBETWEEN ..................................... 21 40.ROMAN ...................................................... 21 41.ROUND ...................................................... 21 42.ROUNDDOWN ......................................... 21 43.ROUNDUP ................................................ 21 44.SERIESSUM ............................................. 21 45.SIGN ........................................................... 22 46.SIN .............................................................. 22 47.SINH ........................................................... 22 48.SQRT.......................................................... 22 49.SQRTPI ...................................................... 22 50.SUBTOTAL ............................................... 22 51.SUM ............................................................ 22 52.SUMIF ........................................................ 23 53.SUMPRODUCT ........................................ 23 54.SUMSQ ...................................................... 23 55.SUMX2MY2 ............................................... 23 56.SUMX2PY2 ............................................... 23 57.SUMXMY2 ................................................. 23 58.TAN ............................................................. 23 59.TANH .......................................................... 24 60.TRUNC ...................................................... 24 七、统计函数（80条）......................................... 24
1.AVEDEV ...................................................... 24 2.AVERAGE ................................................... 24 3.AVERAGEA ................................................ 25 4.BETADIST ................................................... 25 5.BETAINV ..................................................... 25 6.BINOMDIST ................................................ 25 7.CHIDIST ....................................................... 25 8.CHIINV ......................................................... 25 9.CHITEST ..................................................... 25 10.CONFIDENCE .......................................... 26 11.CORREL .................................................... 26 12.COUNT ...................................................... 26 13.COUNTA .................................................... 26 14.COUNTBLANK ........................................ 26 15.COUNTIF ................................................... 26 16.COVAR ...................................................... 27 17.CRITBINOM .............................................. 27 18.DEVSQ ...................................................... 27 19.EXPONDIST ............................................. 27 20.FDIST ......................................................... 27 21.FINV ........................................................... 27 22.FISHER ...................................................... 27 23.FISHERINV ............................................... 27 24.FORECAST .............................................. 28 25.FREQUENCY ........................................... 28 26.FTEST ........................................................ 28 27.GAMMADIST ............................................ 28 28.GAMMAINV .............................................. 28 29.GAMMALN ............................................... 28 30.GEOMEAN ................................................ 29 31.GROWTH .................................................. 29 32.HARMEAN ................................................ 29 33.HYPGEOMDIST ....................................... 29 34.INTERCEPT .............................................. 29 35.KURT ......................................................... 29 36.LARGE ...................................................... 30 37.LINEST ...................................................... 30 38.LOGEST .................................................... 30 39.LOGINV ..................................................... 30 40.LOGNORMDIST ...................................... 30 41.MAX ........................................................... 30 42.MAXA ......................................................... 31 43.MEDIAN ..................................................... 31 44.MIN ............................................................. 31 45.MINA .......................................................... 31 46.MODE ........................................................ 31 47.NEGBINOMDIST ..................................... 31 48.NORMDIST ............................................... 31 49.NORMSINV ............................................... 32
50.NORMSDIST ............................................. 32 51.NORMSINV ............................................... 32 52.PEARSON ................................................. 32 53.PERCENTILE ........................................... 32 54.PERCENTRANK ...................................... 32 55.PERMUT .................................................... 32 56.POISSON .................................................. 33 57.PROB ......................................................... 33 58.QUARTILE ................................................ 33 59.RANK ......................................................... 33 60.RSQ ............................................................ 33 61.SKEW ......................................................... 33 62.SLOPE ....................................................... 34 63.SMALL ....................................................... 34 64.STANDARDIZE ........................................ 34 65.STDEV ....................................................... 34 66.STDEVA ..................................................... 34 67.STDEVP ..................................................... 34 68.STDEVPA .................................................. 35 69.STEYX........................................................ 35 70.TDIST ......................................................... 35 71.TINV............................................................ 35 72.TREND ....................................................... 35 73.TRIMMEAN ............................................... 35 74.TTEST ........................................................ 36 75.VAR ............................................................ 36 76.VARA .......................................................... 36 77.VARP .......................................................... 36 78.VARPA ....................................................... 36 79.WEIBULL .................................................. 36 80.ZTEST ........................................................ 36 八、数据库函数（13条） ..................................... 37
1.DAVERAGE ................................................. 37 2.DCOUNT ...................................................... 37 3.DCOUNTA ................................................... 37 4.DGET ............................................................ 37 5.DMAX ........................................................... 37 6.DMIN ............................................................. 37 7.DPRODUCT ................................................ 37 8.DSTDEV ....................................................... 37 9.DSTDEVP .................................................... 38 10.DSUM ......................................................... 38 11.DVAR .......................................................... 38 12.DVARP ....................................................... 38 13.GETPIVOTDATA ...................................... 38 九、外部函数（2条） ........................................... 38
1.EUROCONVERT ........................................ 38 2.SQL.REQUEST .......................................... 39 十、财务函数（52条） ......................................... 39
1.ACCRINT ..................................................... 39 2.ACCRINTM .................................................. 39
3.AMORDEGRC ............................................ 39
4.AMORLINC ................................................. 40 5.COUPDAYBS ............................................. 40 6.COUPDAYS ................................................ 40 7.COUPDAYSNC ........................................... 40 8.COUPNUM .................................................. 40 9.COUPPCD ................................................... 40 10.CUMIPMT .................................................. 40 11.CUMPRINC ............................................... 40 12.DB............................................................... 41 13.DDB ............................................................ 41 14.DISC ........................................................... 41 15.DOLLARDE .............................................. 41 16.DOLLARFR .............................................. 41 17.DURATION ................................................ 41 18.EFFECT ..................................................... 41 19.FV ............................................................... 42 20.FVSCHEDULE ......................................... 42 21.INTRATE ................................................... 42 22.IPMT ........................................................... 42 23.IRR ............................................................. 42 24.ISPMT ........................................................ 42 25.MDURATION ............................................ 42 26.MIRR .......................................................... 42 27.NOMINAL .................................................. 43 28.NPER ......................................................... 43 29.NPV ............................................................ 43 30.ODDFPRICE ............................................. 43 31.ODDFYIELD ............................................. 43 32.ODDLPRICE ............................................. 43 33.ODDLYIELD .............................................. 43 34.PMT ............................................................ 44 35.PPMT ......................................................... 44 36.PRICE ........................................................ 44 37.PRICEDISC ............................................... 44 38.PRICEMAT ................................................ 44 39.PV ............................................................... 44 40.RATE .......................................................... 44 41.RECEIVED ................................................ 45 42.SLN ............................................................ 45 43.SYD ............................................................ 45 44.TBILLEQ ................................................... 45 45.TBILLPRICE ............................................. 45 46.TBILLYIELD .............................................. 45 47.VDB ............................................................ 45 48.XIRR ........................................................... 45 49.XNPV ......................................................... 46 50.YIELD ......................................................... 46 51.YIELDDISC ............................................... 46 52.YIELDMAT ................................................ 46
一、信息函数（9条） ·CELL ·N·ERROR.TYPE ·NA ·INFO ·TYPE ·IS类 ·ISEVEN ·ISODD 1.CELL
用途：返回某一引用区域的左上角单元格的格式、位置或内容等信息，该函数主要用于保持与其它电子表格程序的兼容性。 语法：CELL(info_type，reference)
参数：Info_type为一个文本值，指明所需要的单元格信息的类型。Reference表示要获取其有关信息的单元格。如果忽略，则
在info_type中所指定的信息将返回给最后更改的单元格。
实例：公式―=CELL(\，A20)‖返回20，如果A3单元格包含TOTAL，则CELL(\，A3)返回\。 2.ERROR.TYPE
用途：返回对应于某一错误类型的数字，如果没有错误则返回#N/A。在IF函数中可以使用ERROR.TYPE检测错误值，并返
回文字串(如―发生错误‖)来取代错误值。
语法：ERROR.TYPE(error_val)
参数：Error_val为需要得到其数字代码的一个错误类型。尽管error_val可以是实际的错误值，但它通常为一个单元格引用，
而此单元格中包含需要检测的公式。
注意：ERROR.TYPE函数返回的错误代码是:#NULL!返回1，#DIV/0!返回2，#value!返回3，#REF!返回4，#NAME?返回5，#NUM!返回6，#N/A返回7，其他错误则返回#N/A。 实例：如果A5=36/0，则公式―=ERROR.TYPE(A5)‖返回2。 3.INFO
用途：返回有关当前操作环境的的信息。 语法：INFO(type_text)
参数：Type_text是一文本，指明所要返回的信息类型。其中\返回当前目录或文件夹的路径，\返回可用的
内存空间(以字节为单位)，\返回数据占用的内存空间，\返回当前所有打开的工作表的数目，\返回当前操作系统的版本号(文本)，\返回当前的重计算方式(―自动‖或―人工‖)，\返回Excel的版本号(文本)，\返回操作系统名称(Macintosh=\，Windows=\，\返回全部内存空间，包括已经占用的内存空间(以字节为单位)。
注意：INFO函数中的文本参数必须用西文引号引起来。
实例：公式―=INFO(\返回―Windows(32-bit)4.90‖，=INFO(\返回1048576。 4.IS类函数
用途：其中包括用来检验数值或引用类型的九个工作表函数。它们可以检验数值的类型并根据参数的值返回TRUE或FALSE。
例如，数值为空白单元格引用时，ISBLANK函数返回逻辑值TRUE，否则返回FALSE。
语法：ISBLANK(value)、ISERR(value)、ISERROR(value)、ISLOGICAL(value)、ISNA(value)、ISNONTEXT(value)、
ISNUMBER(value)、ISREF(value)、ISTEXT(value)。
参数：value是需要进行检验的参数。分别为空白(空白单元格)、错误值、逻辑值、文本、数字、引用值或对于以上任意参数
的名称引用。
如果函数中的参数为下面的内容，则返回TRUE:ISBLANK的参数是空白单元格，ISERR的参数是任意错误值(除去#N/A)，ISERROR的参数是任意错误值(#N/A、#value!、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!)，ISLOGICAL的参数是逻辑值，ISNA的参数是错误值#N/A，ISNONTEXT的参数是任意不是文本的内容(此函数在值为空白单元格时返回TRUE)，ISNUMBER的参数是数字，ISREF的参数是引用，ISTEXT的参数是文本。
注意：IS类函数的参数value是不可转换的。在其他大多数需要数字的函数中，文本\会被转换成数字19。然而在公式ISNUMBER(\中，\并由文本值转换成其他类型的值，而是返回FALSE。
IS类函数用于检验公式计算结果十分有用，它与函数IF结合在一起可以提供一种在公式中查出错误值的方法。
实例：公式―=ISBLANK(\返回FALSE，=ISREF(A5)返回TRUE(其中a5为空白单元格)。如果需要计算A1:A4区域的平均值，
但不能确定单元格内是否包含数字，则公式AVERAGE(A1:A4)返回错误值#DIV/0!。为了应付这种情况，可以使用公式―=IF(ISERROR(AVERAGE(A1:A4))，\引用包含空白单元格\，AVERAGE(A1:A4))‖查出可能存在的错误。
5.ISEVEN
用途：测试参数的奇偶性，如果参数为偶数返回TRUE，否则返回FALSE。 语法：ISEVEN(number)
Number待测试的数值。如果参数值不是整数，则自动截去小数部分取整。
注意：该函数必须加载―分析工具库‖方能使用。如果参数number不是数值，ISEVEN函数返回错误值#value!。 实例：公式―=ISEVEN(11)返回FALSE‖，=ISEVEN(6)返回TRUE。 6.ISODD
用途：测试参数的奇偶性，如果参数为奇数返回TRUE，否则返回FALSE。 语法：ISODD(number)
参数：Number待测试的数值。如果参数不是整数，则自动截去小数部分取整。 注意：该函数必须加载―分析工具库‖方能使用。
实例：公式―=ISODD(19)‖返回TRUE，=ISODD(14.5)返回FALSE。 7.N
用途：返回转化为数值后的值。 语法：N(value)
参数：value为要转化的值。函数N可以转化下表列出的值:数字返回该数字，日期返回该日期的序列号，TRUE返回1，FALSE
返回0，错误值(如#DIV/0!)返回该错误值，其他值返回0。
实例：如果A1包含\，A2包含\文件\，A3包含\，则公式―=N(A1)‖返回7，=N(A2)返回0(因为A2含有文字)，=N(A3)
返回1(因为A3含有TRUE)。
8.NA
用途：返回错误值#N/A。#N/A表示―无法得到有效值‖。如果使用#N/A标志空白单元格，可以避免不小心将空白单元格计算在
内而产生的问题，因为公式引用含有#N/A的单元格时返回错误值#N/A。
语法：NA() 参数：空白单元格
实例：公式―=NA(\返回#N/A。 9.TYPE
用途：返回数值的类型。当某一个函数的计算结果取决于特定单元格中数值的类型时，可使用函数TYPE。 语法：TYPE(value)
参数：value可以是Excel中的数据，如数字、文本、逻辑值等等。
如果value为数字返回1，是文本返回2，逻辑值返回4，错误值返回16，数组返回64。 实例：如果A1包含文本\金额\，则公式―=TYPE(A1)‖返回2。=TYPE(2+A1)返回16。
二、文本和数据函数（28条） ·ASC·CHAR·CLEAN·FINDB·CODE·CONCATENATE·DOLLAR 或 RMB·EXACTLENB·FIND·FIXED·JIS·LEFT 或 LEFTB·REPLACE 或 ·LEN 或 ·LOWER·MID 或 MIDB·SEARCH 或 SEARC·PHONETIC·PROPERREPL·REPT·RIGHT 或 RIGHTB·SUBSTITUTE·T·TEXT