Excel函数公式背诵文.docVIP

Excel函数公式一、 预备知识l 通配符：问号匹配任意单个字符；星号匹配任意多个字符。通配符可以通过在其前插入波形符 ()转义。l 插入数组值的方法是：选择需要插入数组的单元格区域，然后输入可返回数组的函数，按Ctrl+shift+enter键返回。l 在公式编辑框里选择引用项按F4，则相对引用与绝对引用之间进行转换。l 停留在公式编辑框按F9，则把公式值转为直接值。停留在工作表按F9，则重新计算工作簿。停留在工作表按shift+F9，则重新计算工作表。l 若要选定一个包含超链接的单元格并且不跳往超链接的目标文件，请单击单元格区域并按住鼠标按钮直到光标变成一个十字 ，然后释放鼠标按钮。l 在数值型函数中往往直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算。但通过数组或引用参数提供时，只有其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本或错误值往往被忽略l 引用不相邻的多个单元格区域用“，”逗号分隔，引用相邻的多个单元格区域用“：”冒号分隔，引用选定的多个单元格的交叉区域用空格分隔。l 如果要对引用输入一个非连续区域，请使用括号将该引用括起来。 l Excel中有个优先级最高的特殊运算符%，他表示百分比。l 输入公式可以通过选择单元格直接输入“”开始，不必到编辑栏去输入，也可以通过双击单元格或按F2调出以前的公式进入编辑。l “选择区域”闪亮可通过按ESC键消除。l Excel提供插入函数、查找函数及自动求和等快捷功能。l 数组常量就是用“，”表示列区间，用“；”表示行区间的一组数据，数组常量要加大括号表示。l 选定数组公式对应的全部单元格可通过选中它们当中的一个单元格，按ctrl+/键实现。l 进行3D求和时，作为计算对象的数据必须在各个工作表的同一位置。通过选择起始sheet、选择单元格、按住shift、选择终止sheet和enter键完成。l 利用菜单方式进行分类汇总时需要先排序再在菜单中选择“数据-分类汇总”。l 按ctrl+；可以快速输入当前日期到单元格。按ctrl+：可以快速输入当前时间到单元格。l 在返回值的单元格内输入”=”，只需单击数据透视表单元格，就可以输入GETPIVOTDATA函数。二、 函数与公式 INDEX(array,行,列)返回列表或数组中由行、列指定的元素值。 INDEX(reference,行,列,area_num)返回特定行和列交叉处单元格的引用。如果该引用是由非连续选定区域组成的，则可以选择要用作查找范围的选定区域。 LOOKUP(lookup_value,lookup_vector,result_vector)在单行区域或单列区域中查找值，然后返回第二个单行区域或单列区域中相同位置的值。说明：lookup_vector 中的值必须以升序顺序放置，否则，LOOKUP 可能无法提供正确的值。文本比较不区分大小写。Result_vector 必须与 lookup_vector 大小相同。 LOOKUP(lookup_value,array)在数组的第一行或第一列中查找指定的值，然后返回数组的最后一行或最后一列中相同位置的值。说明：数组中被搜索的行或列值必须以升序顺序放置，否则，LOOKUP 无法提供正确的值。文本比较不区分大小写。 HLOOKUP(lookup_value,array,row_num,order)在表格或数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。如果 order为 TRUE，表示使用近似匹配值，此时array 的第一行的数值必须按升序排列。如果order为 FALSE，表示使用精确匹配值，此时array 不必进行排序并在lookup_value 中可使用通配符。文本不区分大小写。 VLOOKUP(lookup_value,array,col_num,order)在表格数组的首列查找值，并由此返回表格数组当前行中指定列处的值。 MATCH(lookup_value,lookup_vector,match_type)返回在指定方式下与指定数值匹配的数组中元素的相应位置。match_type 为 1或省略，查找小于或等于 lookup_value 的最大数值。lookup_vector 必须按升序排列。 match_type 为 0，查找等于 lookup_value 的第一个数值。lookup_vector 可以按任何顺序排列，此时可以含通配符。 match_type 为 -1，查找大于或等于 lookup_value 的最小数值。lookup_vector 必须按降序排列。 CHOOSE(index_num,value1,value2,.)可以使用 index_num 返回数值参数列表中的数值。 数值参数不仅可以为单个数值，也可以为区域引用。 ADDRESS(行, 列,abs_num, a1_mode,sheet_text)按照给定的行号和列标，建立文本类型的单元格地址。 OFFSET(reference,rows,cols,height,width)以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以指定返回的行数或列数。 如果省略 height 或 width，则其高度或宽度与 reference 相同。 INDIRECT(ref_text,a1_mode)返回由文本字符串指定的引用。此函数立即对引用进行计算，并显示其内容。 AREAS(reference)返回引用中包含的连续单元格区域的个数。 ROW(reference)返回引用的行号。 如果省略 reference，则返回所在单元格的行号。 Reference可为一个单元格区域但不能引用多个区域。 COLUMN(reference)返回给定引用的列号。 ROWS(array)返回引用或数组的行数。 COLUMNS(array)返回数组或引用的列数。 TRANSPOSE(array)返回转置单元格区域。 HYPERLINK(link_location, friendly_name)创建一个超连接。 EOMONTH(start_date,months)返回 start-date 之前或之后某月份的最后一天的序列号。 EDATE(start_date,months)返回 start-date 之前或之后某月份的相同日期的序列号。 SUM(number1,number2, .)返回某一单元格区域中所有数字之和。SUM可以穿越sheet，实现三维求和。 SUMIF(range,criteria,sum_range)根据指定条件对若干单元格求和。 Criteria 为条件，其形式可以为数字、表达式、单元格引用或文本。例如， 32、32、32 或 *ples。 如果忽略了 sum_range，则对区域中的单元格求和，相当于Sum_range为Range。如：=SUMIF(B:B,女,C:C) ;=SUMIF(A1:C6,E1,B2:D7) PRODUCT(number1,number2,.)将所有以参数形式给出的数字相乘，并返回乘积值。 SUMPRODUCT(array1,array2,array3, .)在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。 数组参数必须具有相同的维数，非数值型的数组元素作为 0 处理。 SUMSQ(number1,number2, .)返回参数的平方和。 SQRT(number)返回正平方根。 SUMX2PY2(array_x,array_y)返回两数组中对应数值的平方和之和。 SUMX2MY2(array_x,array_y)返回两数组中对应数值的平方差之和。 POWER(number,power)返回给定数字的乘幂。 SUMXMY2(array_x,array_y)返回两数组中对应数值之差的平方和。 SUBTOTAL(function_num, ref1, ref2, .)返回列表或数据库中的分类汇总。Function_num 为 1 到 11（包含隐藏值）或 101 到 111（忽略隐藏值）之间的数字，AVERAGE、COUNT、COUNTA、MAX、MIN、PRODUCT及SUM等。 SUBTOTAL 函数忽略任何不包括在筛选结果中的行，不论使用什么 function_num 值。 SUBTOTAL不支持三维引用。 SUBTOTAL适用于数据列或垂直区域。不适用于数据行或水平区域。 ABS(number)返回数字的绝对值。 SIGN(number)返回数字的符号。当数字为正数时返回 1，为零时返回 0，为负数时返回 -1。 IF(logical_test,value_if_true,value_if_false) COUNTIF(range,criteria)计算区域中满足给定条件的单元格的个数。 MOD(number,divisor)返回两数相除的余数。结果的正负号与除数相同。 QUOTIENT(numerator,denominator)返回商的整数部分，该函数可用于舍掉商的小数部分。 INT(number)将数字向下取整数。 TRUNC(number,num_digits)将数字的小数部分截去，返回整数。Num_digits用于指定取整精度。Num_digits 的默认值为 0。1是小数点后第一位，0是个位，-1是十位. FIXED(number,decimals,no_commas)将数字按指定的精度进行四舍五入取整，利用句号和逗号进行格式化并以文本形式返回结果。 No_commas 为一逻辑值。如果其值为 FALSE 或被省略，则返回的文本中和往常一样包含逗号。 如果省略Decimals，则假设其值为 2。 DOLLAR(number,decimals) 或 RMB(number,decimals)该函数依照货币格式将小数四舍五入到指定的位数并转换成文本。 Decimals 为十进制数的小数位数。如果 Decimals 为负数，则参数 number 从小数点往左按相应位数取整。如果省略 Decimals，则假设其值为 2。 ROUND(number,num_digits)返回某个数字按指定位数进行四舍五入的数字。 ROUNDUP(number,num_digits)远离零值，向上（绝对值增大的方向）舍入数字。 ROUNDDOWN(number,num_digits)靠近零值，向下（绝对值减小的方向）舍入数字。 CEILING(number,significance)远离零值，向上（绝对值增大的方向）舍入为最接近的 significance 的倍数。 FLOOR(number,significance)靠近零值，向下（绝对值减小的方向）舍入为最接近的 significance 的倍数。 EVEN(number)返回沿绝对值增大方向取整后最接近的偶数。 ODD(number)返回沿绝对值增大方向取整后最接近的奇数。 RAND( )返回大于等于 0 及小于 1 的均匀分布随机数。 RANDBETWEEN(bottom,top)返回位于两个指定数之间的一个随机数。 COUNTA(value1,value2,.)返回参数列表中非空值的单元格个数。 COUNT(value1,value2,.)返回包含数字以及参数列表中的数字的单元格的个数。 COUNTBLANK(range)计算指定单元格区域中空白单元格的个数，空白指没有任何内容，空格不是空白。 AVERAGE(number1,number2,.)返回参数的平均值（算术平均值）。 AVERAGEA(value1,value2,.)计算参数列表中数值的平均值（算数平均值）。不仅数字，而且文本和逻辑值（如 TRUE 和 FALSE）也将计算在内。 MAX(number1,number2,.)返回一组值中的最大值。 MAXA(value1,value2,.)返回参数列表中的最大值。文本值和逻辑值（如 TRUE 和 FALSE）也作为数字来计算。 MIN(number1,number2,.)返回一组值中的最小值。 MINA(value1,value2,.)返回参数列表中的最小值。文本值和逻辑值（如 TRUE 和 FALSE）也作为数字来计算。 RANK(number,ref,order)返回一个数字在数字列表中的排位。order 为 0或省略，则按降序考虑排位。order 不为零，则按升序考虑排位。Ref无须预先排序。 LARGE(array,k)返回数据集中第 k 个最大值。 SMALL(array,k)返回数据集中第 k 个最小值。 DATE(year,month,day)返回代表特定日期的序列号。 TIME(hour,minute,second)返回某一特定时间的小数值。 DATEVALUE(date_text)返回由文本字符串所代表的日期的序列号。 TIMEVALUE(time_text)返回由文本字符串所代表的时间的小数值。 TODAY( )返回当前日期的序列号。 NOW( )返回当前日期和时间所对应的序列号。 YEAR(serial_number)返回某日期对应的年份。返回值为 1900 到 9999 之间的整数。 MONTH(serial_number)返回某日期对应的月份。月份是介于 1到 12之间的整数。 DAY(serial_number)返回某日期对应的天数，用整数 1 到 31 表示。 WEEKDAY(serial_number,return_type)返回某日期为星期几。默认情况下，其值为 1（星期天）到 7（星期六）之间的整数。 WEEKNUM(serial_num,return_type)返回一个数字，该数字代表一年中的第几周。return_type默认情况下，假定星期天为一周的第一天。 HOUR(serial_number)返回时间值的小时数。即一个介于 0 到 23之间的整数。 MINUTE(serial_number)返回时间值中的分钟，为一个介于 0 到 59 之间的整数。 SECOND(serial_number)返回时间值的秒数。返回的秒数为 0 到 59 之间的整数。 WORKDAY(start_date,days,holidays)返回起始日期之前或之后相隔指定工作日的日期值。工作日不包括周末和专门指定的假日。 Holidays 为可选的假日列表。 NETWORKDAYS(start_date,end_date,holidays)返回参数 start-data 和 end-data 之间完整的工作日数值。工作日不包括周末和专门指定的假期。 LEN(text)LEN 返回文本字符串中的字符数。 LENB(text)LENB 返回文本字符串中的字节数。 AND(logical1,logical2, .)所有参数的逻辑值为真时，返回 TRUE；否则返回 FALSE。 OR(logical1,logical2,.)所有参数的逻辑值为假时，返回 FALSE；否则返回 TRUE。 NOT(logical)如果逻辑值为 FALSE，函数返回 TRUE；如果逻辑值为 TRUE，函数返回 FALSE。 FALSE( )返回逻辑值 FALSE。 TRUE( )返回逻辑值 TRUE。 ISEVEN(number)如果参数 number 为偶数，返回 TRUE，否则返回 FALSE。Number如果不是整数，则截尾取整。 ISODD(number)如果参数 number 为奇数，返回 TRUE，否则返回 FALSE。Number如果不是整数，则截尾取整。 ISBLANK(value)ISERR(value)ISERROR(value)ISLOGICAL(value)ISNA(value)ISNONTEXT(value)ISNUMBER(value)ISREF(value)ISTEXT(value) CONCATENATE (text1,text2,.)将几个文本字符串合并为一个文本字符串。 FIND(find_text,within_text,start_num)用于查找某字符串 (find_text)在目标字符串 (within_text) 内的位置。也可使用 SEARCH 查找某字符串在目标字符串内的位置，但是，FIND 和 SEARCH 不同，FIND 区分大小写但不允许使用通配符。 Start_num 指定开始进行查找的位置。within_text 中的首字符是编号为 1 的字符。如果忽略 start_num，则假设其为 1。 FINDB(find_text,within_text,start_num)用于查找某字符串 (find_text)在目标字符串 (within_text) 内的位置。但计数单位基于字节。 SEARCH(find_text,within_text,start_num)用于查找某字符串 (find_text)在目标字符串 (within_text) 内的位置。SEARCH不区分大小写但允许使用通配符。 SEARCHB(find_text,within_text,start_num)用于查找某字符串 (find_text)在目标字符串 (within_text) 内的位置。但计数单位基于字节。 LEFT(text,num_chars)返回文本字符串中的前num_chars个字符的字符串。 如果省略 num_chars，则假定其为 1。 LEFTB(text,num_bytes)返回文本字符串中的第num_bytes个字节的字符串。 RIGHT(text,num_chars)返回文本字符串中最后num_chars个字符。 如果忽略 num_chars，则假定其为 1。 RIGHTB(text,num_bytes)返回文本字符串中最后num_bytes个字节的字符串。 MID(text,start_num,num_chars)返回文本字符串中从指定位置开始的特定数目的字符。 MIDB(text,start_num,num_bytes)返回文本字符串中从指定位置开始的特定数目字节的字符串。 REPLACE(old_text,start_num,num_chars,new_text)用某字符串替换文本字符串中从指定位置开始的特定数目的字符。 REPLACEB(old_text,start_num,num_bytes,new_text)用某字符串替换文本字符串中从指定位置开始的特定数目的字节。 SUBSTITUTE(text,old_text,new_text,instance_num)在文本字符串中用 new_text 替代 old_text。 Instance_num 为一数值，用来指定以 new_text 替换第几次出现的 old_text。如果没有指定 instance_num，则将用 new_text 替换 Text 中出现的所有 old_text。 ASC(text)将全角（双字节）字符更改为半角（单字节）字符。 WIDECHAR(text)将半角（单字节）字符转换为全角（双字节）字符。 UPPER(text)将文本转换成大写形式。 LOWER(text)将文本转换成小写形式。 PROPER(text)将文本字符串的首字母及任何非字母字符之后的首字母转换成大写，将其余的字母转换成小写。 TEXT(value,format)将数值转换为按指定格式表示的文本。format：”$#,#.00”，”#.#%”，”DBnum1”，”DBnum1#.#0”，”DBnum2”，” DBnum3”，”yyyy年mm月dd日hh时mm分ss秒 aaaa” VALUE(text)将代表数字的文本转换成数字，不管是全角文本还是半角文本。 CODE(text)返回文本字符串中第一个字符的数字代码。返回的代码对应于计算机当前使用的字符集。 CHAR(number)返回对应于数字代码的字符。函数 CHAR 可将其他类型计算机文件中的代码转换为字符。 TRIM(text)除了单词之间的单个空格外，清除文本中所有的空格。 REPT(text,number_times)按照给定的次数重复显示文本。 DSUM(database,field,criteria)返回列表或数据库中满足指定条件的记录字段（列）中的数字之和。同行条件的是and，不同行的条件是or，并且可使用通配符。相关函数还有如下所示：DAVERAGE 返回选择的数据库条目的平均值DCOUNT 计算数据库中包含数字的单元格个数DCOUNTA 计算数据库中的非空单元格个数DGET 从数据库提取符合指定条件的单个值。DMAX 返回选择的数据库条目的最大值DMIN 返回选择的数据库条目的最小值DPRODUCT 将数据库中符合条件的记录的特定字段中的值相乘DSTDEV 基于选择的数据库条目的样本估算标准偏差DSTDEVP 基于选择的数据库条目的总体计算标准偏差DVAR 基于选择的数据库条目的样本估算方差DVARP 基于选择的数据库条目的样本总体计算方差GETPIVOTDATA 返回存储在数据透视表中的数据 DEC2BIN(number,places)将十进制数转换为二进制数。如果省略 places，函数用能表示此数的最少字符来表示。否则在数值前置零补齐长度。 BIN2DEC(number)将二进制数转换为十进制数。另外还有一系列进制转换的函数如下，但是所有转换成十进制数的函数都没有places的参数。BIN2DEC将二进制数转换为十进制数BIN2HEX将二进制数转换为十六进制数BIN2OCT将二进制数转换为八进制数DEC2BIN将十进制数转换为二进制数DEC2HEX将十进制数转