EXCEL中比较常用的的函数Word格式.docx

1、时,计算结果是两个日期间隔的天数即公式为=DATEDIF（）、YD时,计算结果是忽略年数差两个日期间隔的天数，即公式为=DATEDIF（）MD时,计算结果是两个日期间隔的天数.忽略年数和月份之差。YM时, 计算结果是不计年份的间隔月份数. 将小写金额转换为大写金额=IF（INT（D11）=0,TEXT（INT（D11）,DBNum2）&IF（OR（INT（D11*10）=0,INT（D11）=D11）,IF（INT（D11*10）=INT（D11）*10,零,RIGHT（TEXT（INT（D11*10）,）,1）&）&IF（INT（D11*100）=INT（D11*10）*10,整,RIGHT

2、（TEXT（ROUND（D11,2）,分函数SUBTOTAL返回列表或数据库中的分类汇总。通常，使用“数据”菜单中的“分类汇总”命令可以容易地创建带有分类汇总的列表。一旦创建了分类汇总，就可以通过编辑 SUBTOTAL 函数对该列表进行修改。语法SUBTOTAL（function_num, ref1, ref2, .） =SUBTOTAL（分类汇总中使用的函数代码，数据区域）Function_num 为 1 到 11（包含隐藏值）或 101 到 111（忽略隐藏值）之间的数字，指定使用何种函数在列表中进行分类汇总计算。Function_num（包含隐藏值）（忽略隐藏值）函数例子1101AVER

3、AGE平均数2102COUNT包含数据单元格的个数3103COUNTA-非空格的个数序号=SUBTOTAL（103,$B$3:B3）4104MAX最大值5105MIN最小值6106PRODUCT乘积7107STDEV8108STDEVP9109SUM合计数10110VAR11111VARPRef1, ref2, 为要进行分类汇总计算的 1 到 29 个区域或引用。说明 如果在 ref1, ref2, 中有其他的分类汇总（嵌套分类汇总），将忽略这些嵌套分类汇总，以避免重复计算。 当 function_num 为从 1 到 11 的常数时，SUBTOTAL 函数将包括通过“格式”菜单的“行”子菜单

4、下面的“隐藏”命令所隐藏的行中的值。当您要分类汇总列表中的隐藏和非隐藏值时，请使用这些常数。当 function_num 为从 101 到 111 的常数时，SUBTOTAL 函数将忽略通过“格式”菜单的“行”子菜单下面的“隐藏”命令所隐藏的行中的值。当您只分类汇总列表中的非隐藏数字时，使用这些常数。 SUBTOTAL 函数忽略任何不包括在筛选结果中的行，不论使用什么 function_num 值。 SUBTOTAL 函数适用于数据列或垂直区域。不适用于数据行或水平区域。例如，当 function_num 大于或等于 101 时需要分类汇总某个水平区域时，例如 SUBTOTAL（109,B2:

5、G2），则隐藏某一列不影响分类汇总。但是隐藏分类汇总的垂直区域中的某一行就会对其产生影响。 如果所指定的某一引用为三维引用，函数 SUBTOTAL 将返回错误值 #VALUE!。SUBTOTAL（分类汇总中使用的函数代码，数据区域） 举例 数据表 A列 B列 C列 D列 姓名 语文 数学 英语 张三 95 94 88 共56条记录， 求语文的平均分，则公式 =SUBTOTAL（1，B2：B57） 注意：参数“1”表示分类汇总使用的平均值函数AVERAGE（参见函数帮助）。公式等价于公式=AVERAGE（B2：B57）。求语文的最高分，则公式 =SUBTOTAL（4，B2：参数“4”表示分类汇总

6、使用的最大值函数MAX（参见函数帮助） 公式等价于公式=MAX（B2：B57）设置有效性条件验证假设G列为员工“身份证号”字段， G2单元格为第一个员工的身份证号码所在的单元格。在未输入之前，我们可先设置该列的有效性条件来确保该列数据的惟一性。选中G2单元格，单击“数据”菜单中的“有效性”命令，弹出“数据有效性”对话框，选择“设置”选项卡，在“允许”下拉列表中选择“自定义”，在“公式”框内输入“=COUNTIF（G:G,G2）=1”（公式内所有的字符使用半角英文，不包括双引号，如图1所示）。设置出错警告提示信息设置出错警告提示信息的目的在于提醒用户正确输入数据。具体步骤是：单击“数据有效性”对

7、话框中的“出错警告”选项卡，在“标题”框内输入“数据输入错误”，在“错误信息”框内输入“你刚才输入的数据已经存在，请检查数据的惟一性！”。设置完之后，单击“确定”按钮（如图2所示）。至此，已经设置了G2单元格的有效性条件验证和出错提示信息。为了将这个设置应用到整个G列（除了字段名称所在的单元格即G1单元格），可用填充柄工具向下拖动将公式复制到G列其他的单元格。输入身份证信息以上设置完成之后我们就可以向G列中输入员工的身份证号了。每输入一个员工的身份证号，Excel会自动对该数据进行有效性验证，如果该数据已经存在，系统将弹出出错警告提示框，如图3所示。上述功能只能验证数据的惟一性，若数据位数输入

8、错误，系统则检测不出这一错误。若在输入时需要同时验证数据的位数，还是以身份证号为例，可将图一中的公式改为“=AND（COUNTIF（G:G,G2）=1,OR（LEN（G2）=15,LEN（G2）=18）”，图二中的错误信息改为“请检查数据的惟一性或输入数据位数错！设置完后重新复制G2单元格的公式至G列其他的单元格。该公式的含义是：在G列输入的数据必须是惟一的且数据位数必须是15位或18位。最后还需要提醒大家，由于G列输入的是身份证号，位数超过了11位数据，所以最好在输入数据之间，选将G列全部选定，设置“单元格格式”中的“数字分类” 格式为“文本”格式，这样才能保证身份证号以正确形式输入。CON

9、CATENATE 主要功能：将多个字符文本或单元格中的数据连接在一起，显示在一个单元格中。使用格式：CONCATENATE（Text1，Text） 参数说明：Text1、Text2为需要连接的字符文本或引用的单元格。应用举例：在C14单元格中输入公式：=CONCATENATE（A14,B14,.com），确认后，即可将A14单元格中字符、B14单元格中的字符和.com连接成一个整体，显示在C14单元格中。特别提醒：如果参数不是引用的单元格，且为文本格式的，请给参数加上英文状态下的双引号，如果将上述公式改为：=A14&B14&，也能达到相同的目的。将多个字符文本或单元格中的数据连接在一起，显示在

10、一个单元格中也可用以下方式实现：Text1& Text1& Text，Text表示单元格或文本或数值COUNTIF 统计某个单元格区域中符合指定条件的单元格数目。COUNTIF（Range,Criteria） Range代表要统计的单元格区域；Criteria表示指定的条件表达式。 在C17单元格中输入公式：=COUNTIF（B1:B13,=80），确认后，即可统计出B1至B13单元格区域中，数值大于等于80的单元格数目。允许引用的单元格区域中有空白单元格出现。VLOOKUPVLOOKUP函数在表格或数值数组的首列查找指定的数值，并由此返回表格或数组中该数值所在行中指定列处的数值。这里所说的“

11、数组”，可以理解为表格中的一个区域。数组的列序号：数组的“首列”，就是这个区域的第一纵列，此列右边依次为第2列、3列。假定某数组区域为B2:E10，那么，B2:B10为第1列、C2:C10为第2列。语法：VLOOKUP（查找值，区域，列序号，逻辑值）“查找值”：为需要在数组第一列中查找的数值，它可以是数值、引用或文字符串。“区域”：数组所在的区域，如“B2:E10”，也可以使用对区域或区域名称的引用，例如数据库或数据清单。“列序号”：即希望区域（数组）中待返回的匹配值的列序号，为1时，返回第一列中的数值，为2时，返回第二列中的数值，以此类推；若列序号小于1，函数VLOOKUP 返回错误值 #V

12、ALUE!；如果大于区域的列数，函数VLOOKUP返回错误值 #REF!“逻辑值”：为TRUE或FALSE。它指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于“查找值”的最大数值；如果“逻辑值”为FALSE，函数 VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值 #N/A。如果“查找值”为文本时，“逻辑值”一般应为 FALSE 。另外：如果“查找值”小于“区域”第一列中的最小数值，函数 VLOOKUP 返回错误值 #N/A。如果函数 VLOOKUP 找不到“查找值” 且“逻辑值”为 FALSE，函数 VLOOKUP 返回错误值 #N/A。下面举例说明VLOOKUP函数的使用方法。假设在Sheet1中存放小麦、水稻、玉米、花生等若干农产品的销售单价： A B1 农产品名称 单价2 小麦 0.563 水稻 0.484 玉米 0.395 花生 0.51100 大豆 0.45Sheet2为销售清单，每次填写的清单内容不尽相同：要求在Sheet2中输入农产品名称、数量后，根据Sheet1的数据，自动生成单价和销售额。设下表为Sheet2： B C D 数量 单价 金额 1000 0.48 480