excel使用函数技巧

1、DAVERAGE返回选定数据库项的平均值DCOUNT计算数据库中包含数字的单元格个数DCOUNTA计算数据库中非空单元格的个数DGET从数据库中提取满足指定条件的单个记录DPRODUCT将数据库中满足条件的记录的特定字段中的数值相乘DSUM对数据库中满足条件的记录的字段列中的数字求和GETPIVOTDATA返回存储于数据透视表中的数据 日期和时间函数 DATE返回特定日期的序列号DATEVALUE将文本格式的日期转换为序列号DAY将序列号转换为月份中的日EDATE返回在开始日期之前或之后指定月数的日期的序列号MONTH将序列号转换为月NOW返回当前日期和时间的序列号TIMEVALUE将文本格式

2、的时间转换为序列号TODAY返回今天日期的序列号WEEKDAY将序列号转换为星期几WEEKNUM 将序列号转换为一年中相应的周数逻辑函数 IF指定要执行的逻辑检测数学和三角函数 CEILING将数字舍入为最接近的整数，或最接近的有效数字的倍数ROUND将数字舍入到指定位数SUBTOTAL返回数据库列表或数据库中的分类汇总SUMIF按给定条件将指定单元格求和SUMPRODUCT返回相对应的数组部分的乘积和TRUNC将数字截尾取整 信息函数 CELL返回有关单元格格式、位置或内容的信息ISBLANK如果值为空，则返回 TRUEISNA如果值为 #N/A 错误值，则返回 TRUE查找和引用函数 AD

3、DRESS以文本形式返回对工作表中某个单元格的引用CHOOSE从值的列表中选择一个值HLOOKUP在数组的首行查找并返回指定单元格的值LOOKUP在向量或数组中查找值TRANSPOSE返回数组的转置VLOOKUP在数组第一列中查找，然后在行之间移动以返回单元格的值 统计函数 CORREL返回两个数据集之间的相关系数COUNT计算参数列表中数字的个数COUNTA计算参数列表中值的个数COUNTBLANK 计算区间内的空白单元格个数COUNTIF 计算满足给定标准的区间内的非空单元格的个数MODE返回数据集中出现最多的值PERCENTRANK返回数据集中值的百分比排位文本和数据函数 ASC将字符串

4、内的全角（双字节）英文字母或片假名更改为半角（单字节）字符CONCATENATE将若干文本项合并到一个文本项中EXACT检查两个文本值是否完全相同FIND在一文本值内查找另一文本值（区分大小写）FIXED将数字设置为具有固定小数位的文本格式LEFT返回文本值最左边的字符LEN返回文本字符串中的字符个数LOWER将文本转换为小写形式MID从文本字符串中的指定位置起返回特定个数的字符REPLACE替换文本内的字符RIGHT返回文本值最右边的字符SEARCH在一文本值中查找另一文本值（不区分大小写）SUBSTITUTE在文本字符串中以新文本替换旧文本TEXT设置数字的格式并将数字转换为文本TRIM删

5、除文本中的空格UPPER将文本转换为大写形式 A 1 数据2 Ceramic Insulators #124-TD45-873 Copper Coils #12-671-67724 Variable Resistors #116010公式 说明（结果）=MID(A2,1,FIND( #,A2,1)-1) 在上面第一个字符串中，抽取从第一个字符到“#”的文本 (Ceramic Insulators)=MID(A3,1,FIND( #,A3,1)-1) 在上面第二个字符串中，抽取从第一个字符到“#”的文本 (Copper Coils)=MID(A4,1,FIND( #,A4,1)-1) 在上面第三

6、个字符串中，抽取从第一个字符到“#”的文本 (Variable Resistors) A1 数据2 Statements3 Profit Margin4 margin公式说明（结果）=SEARCH(e,A2,6)上面第一个字符串中的第一个 e 出现的位置，开始于第六个位置 (7)=SEARCH(A4,A3)margin 在 Profit Margin 中的位置 (8)=REPLACE(A3,SEARCH(A4,A3),6,Amount)用 Amount 替换 Margin常用函数及技巧详解 1.求平均值.函数名称：AVERAGE主要功能：求出所有参数的算术平均值。使用格式：AVERAGE(nu

7、mber1,number2,)参数说明：number1,number2,：需要求平均值的数值或引用单元格（区域），参数不能超过30个。应用举例：如在B8单元格中输入公式：=AVERAGE(B7:D7)，即可求出B7至D7区域值的平均值.也可求出指定区域的平均值，如：在B8单元格中输入公式：=AVERAGE(B7:D7,F7:H7,7,8)，确认后，即可求出B7至D7区域、F7至H7区域中的数值和7、8的平均值。 特别提醒：如果引用区域中包含“0”值单元格，则计算在内；如果引用区域中包含空白或字符单元格，则不计算在内。2.求一组数据的和函数名称：SUM主要功能：计算所有参数数值的和。使用格式：S

8、UM（Number1,Number2）参数说明：Number1、Number2代表需要计算的值，可以是具体的数值、引用的单元格（区域）、逻辑值等。应用举例：如在D64单元格中输入公式：=SUM(D2:D63)，确认后即可求出D列中2-63格中的和。特别提醒：如果参数为数组或引用，只有其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略；如果将上述公式修改为：=SUM(LARGE(D2:D63,1,2,3,4,5)，则可以求出前5名成绩的和。3、求出部分指定数据的和函数名称：SUMIF主要功能：计算符合指定条件的单元格区域内的数值和。使用格式：SUMIF（Range,Cri

9、teria,Sum_Range）参数说明：Range代表条件判断的单元格区域；Criteria为指定条件表达式；Sum_Range代表需要计算的数值所在的单元格区域。应用举例：如，在D64单元格中输入公式：=SUMIF(B3:B500,七1,E3:E500) .即：可求出在B3B500中（为班名）E3E500(某科目)的和，另外输入： =SUMIF(B3:B500,七1,E3:E500)/R17，（R17代表人数栏），确认后即可求出“七1”班的某科成绩的平均值（班平均分）。补充：如果把上述公式修改为：=SUMIF(C2:C63,女,D2:D63)，即可求出“女”生的语文成绩和。特别提醒：其中的

10、“男”和“女”由于是文本型的，需要放在英文状态下的双引号（男、女）中。4. 对一组数据进行自动排名函数名称：RANK主要功能：返回某一数值在一列数值中的相对于其他数值的排位。使用格式：RANK（Number,ref,order）参数说明：Number代表需要排序的数值；ref代表排序数值所处的单元格区域；order代表排序方式参数（如果为“0”或者忽略，则按降序排名，即数值越大，排名结果数值越小；如果为非“0”值，则按升序排名，即数值越大，排名结果数值越大；）。应用举例：如在C2单元格中输入公式：=RANK(B2,$B$2:$B$31,0)，确认后即可得出某位同学的成绩在B2-B31中的排名结

11、果。特别提醒：在上述公式中，我们让Number参数采取了相对引用形式，而让ref参数采取了绝对引用形式（增加了一个绝对引用“$”符号），这样设置后，选中C2单元格，将鼠标移至该单元格右下角，成细十字线状时（通常称之为“填充柄”），按住左键向下拖拉，即可将上述公式快速复制到C列下面的单元格中，完成其他同学的排名统计。 5. 求出一组数中的最大值和最小值函数名称：MAX （求最大值）主要功能：求出一组数中的最大值。使用格式：MAX(number1,number2)参数说明：number1,number2代表需要求最大值的数值或引用单元格（区域），参数不超过30个。应用举例：输入公式：=MAX(E4

12、:E100)，确认后即可显示出E4至E100单元中的最大值。特别提醒：如如果参数中有文本或逻辑值，则忽略MIN函数 （求最小值）函数名称：MIN主要功能：求出一组数中的最小值。使用格式：MIN(number1,number2)参数说明：number1,number2代表需要求最小值的数值或引用单元格（区域），参数不超过30个。应用举例：输入公式：=MAX(E4:E100)，确认后即可显示出E4至E100单元中的最小值特别提醒：如果参数中有文本或逻辑值，则忽略 6、根据给定的条件，返回相对应的指定内容函数名称：IF主要功能：根据对指定条件的逻辑判断的真假结果，返回相对应的内容。使用格式：=IF(

13、Logical,Value_if_true,Value_if_false)参数说明：Logical代表逻辑判断表达式；Value_if_true表示当判断条件为逻辑“真（TRUE）”时的显示内容，如果忽略返回“TRUE”；Value_if_false表示当判断条件为逻辑“假（FALSE）”时的显示内容，如果忽略返回“FALSE”。应用举例：在D26单元格中输入公式：=IF(C26=60,及格,不及格)，确认以后，如果C26单元格中的数值大于或等于60，则D26单元格显示及格字样，反之显示“不及格”字样。也可根据实际可多设几档条件和内容，如：=IF（A360,不合格,(IF(A380,合格,(I

14、F(A3=80)，确认后，即可统计出B1至B13单元格区域中，数值大于等于80的单元格数目。特别提醒：允许引用的单元格区域中有空白单元格出现 8、巧用函数组合进行多条件的计数统计（原创）举例说明：第一行为表头，A列是“姓名”，B列是“班级”，C列是“语文成绩”，现在要统计“班级”为“2”，“语文成绩”大于等于100的人数。统计结果存放在本工作表的其他列。公式如下：=SUM(IF(B2:B500=2)*(C2:C500=100),1,0)输入完公式后按Ctrl+Shift+Enter键,让它自动加上数组公式符号 。另：如果只求某一分数段的人数，假如B列是学生的语文成绩，则求从B2:B56这一范围

15、内分数在80-90之间的人数，则可以用这样一个公式:=COUNTIF (B2:B56,=80)-COUNTIF (B2:B56,=90) 输入公式回车即可。 注意：函数COUNTIF是条件统计函数，只能对一个条件进行限制，如果判断条件有两个以上，则不能用。另外，公式中的各种符号都是在英文状态下输入的。 还有一个办法：求出一个数目，然后采用递减的办法，=COUNTIF (B2:B56,=80)-A2 - .)9、核对两列数据或文本是否一样举例说明：比较A列和B列是否相同方法一：在c列输入：=IF(a1=b1,一样,不一样)即：相同的显示“一样”，不相同的显示“不一样”.方法二：（把B列与A列不同

16、之处标识出来）（1）、如果是要求A、B两列的同一行数据相比较：假定第一行为表头，单击A2单元格，点“格式”-“条件格式”，将条件设为:“单元格数值” “不等于” =B2点“格式”-“字体”-“颜色”，选中红色，点两次“确定”。用格式刷将A2单元格的条件格式向下复制。B列可参照此方法设置。不同的自动显示为红色.（2）、如果是A列与B列整体比较（即相同数据不在同一行）：假定第一行为表头，单击A2单元格，点“格式”-“条件格式”，将条件设为:“公式”=COUNTIF(B:B,A2)=0点“格式”-“字体”-“颜色”，选中红色，点两次“确定”。用格式刷将A2单元格的条件格式向下复制。B列可参照此方法设

17、置。注：实际用时要将被用来比较的复制到相邻两列来，整体设条件，按第一格设.按以上方法设置后，AB列均有的数据不着色，A列有B列无或者B列有A列无的数据标记为红色字体。 提示：如果AB两列整体区别时，先要用鼠标点击成为整体选中再设置. 10、跨表调取数据方法 (原创)用VLOOKUP函数主要功能：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组中该数值所在行中指定列处的数值。这里所说的“数组”，可以理解为表格中的一个区域。数组的列序号：数组的“首列”，就是这个区域的第一纵列，此列右边依次为第2列、3列。假定某数组区域为B2:E10，那么，B2:B10为第1列、C2:C10为第2列。语法

18、： VLOOKUP（查找值，区域，列序号，逻辑值）“查找值”：为需要在数组第一列中查找的数值，它可以是数值、引用或文字符串。“区域”：数组所在的区域，如“B2:E10”，也可以使用对区域或区域名称的引用，例如数据库或数据清单。“列序号”：即希望区域（数组）中待返回的匹配值的列序号，为1时，返回第一列中的数值，为2时，返回第二列中的数值，以此类推；若列序号小于1，函数VLOOKUP 返回错误值 #VALUE!；如果大于区域的列数，函数VLOOKUP返回错误值 #REF!。“逻辑值”：为TRUE或FALSE。它指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省略，则返回

19、近似匹配值，也就是说，如果找不到精确匹配值，则返回小于“查找值”的最大数值；如果“逻辑值”为FALSE，函数 VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值 #N/A。如果“查找值”为文本时，“逻辑值”一般应为 FALSE 。另外：如果“查找值”小于“区域”第一列中的最小数值，函数 VLOOKUP 返回错误值 #N/A。如果函数 VLOOKUP 找不到“查找值” 且“逻辑值”为 FALSE，函数 VLOOKUP 返回错误值 #N/A。下面用示意图来说明VLOOKUP函数的使用方法。如：“=VLOOKUP(A10,模拟1!$C$3:$K$500,3,0)”“A10”表示要查找的对象（

20、在表2中）.“模拟1!”表示被引用表的名称 “$C$3:$K$500”表示被引用表的要引用的区域，“C3”表示要引用的第一格，“K500”表示要引用的最后一格.“$”表示绝对引用，是锁定的意思，没有为不锁定，复制时要变化。“3”表示要引用区域的第3列数据，(如例中的从C开始为1).“0”表示准确查找，如果是“1”表示近似查找. 11.自动给出当时日期函数名称：TODAY主要功能：给出系统日期。使用格式：TODAY()参数说明：该函数不需要参数。应用举例：输入公式：=TODAY()，确认后即刻显示出系统日期和时间。如果系统日期和时间发生了改变，只要按一下F9功能键，即可让其随之改变。特别提醒：显

21、示出来的日期格式，可以通过单元格格式进行重新设置12.其它一些常用的但又特殊的方法（一）如何在EXCEL中设置单元格编辑权限(保护部分单元格)1、先选定所有单元格,点格式-单元格-保护,取消锁定前面的。2、再选定你要保护的单元格,点格式-单元格-保护,在锁定前面打上。3、点工具-保护-保护工作表,输入两次密码,点两次确定即可。 （二）在EXCEL中建立下拉列表按钮选定你要设置下拉列表的单元格，点“数据”-“有效性”-“设置”，在“允许”下面选择“序列”，在“来源”框中输入你的下拉列表内容，各项之间用半角逗号隔开，如：A,B,C,D选中“提供下拉前头”，点“确定”。 （三）自动从学生的身份证号“

22、提取”出生日期和性别。第一步：转换身份证号码格式选中该列中的所有身份证号后，右击鼠标，选择“设置单元格格式”。在弹出对话框中“数字”标签内的“分类”设为“文本”，然后点击确定。第二步：“提取出”出生日期 将光标指针放到“出生日期”列的单元格内，这里以C2单元格为例。然后输入“=MID(B2,7,4)&年&MID(B2,11,2)&月&MID(B2,13,2)&日”（注意：外侧的双引号不用输入，函数式中的引号和逗号等符号应在英文状态下输入）。回车后，你会发现在C2单元格内已经出现了该学生的出生日期。然后，选中该单元格后拖动填充柄，其它单元格内就会出现相应的出生日期。还可以提取出“性别”：判断性别

23、“男女” 选中“性别”列的单元格，如D2。输入“=IF(MID(B2,17,1)/2=TRUNC(MID(B2,17,1)/2),女,男)”（注意如上）后回车，该生“是男还是女”已经乖乖地判断出来了。拖动填充柄让其他学生的性别也自动输入 （四）解释：单元格的相对引用、绝对引用和混合引用相对引用、绝对引用和混合引用是指在公式中使用单元格或单元格区域的地址时，当将公式向旁边复制时，地址是如何变化的。具体情况举例说明：1、相对引用，复制公式时地址跟着发生变化，如C1单元格有公式：=A1+B1当将公式复制到C2单元格时变为：=A2+B2当将公式复制到D1单元格时变为：=B1+C1 2、绝对引用，复制公

24、式时地址不会跟着发生变化，如C1单元格有公式：=$A$1+$B$1当将公式复制到C2单元格时仍为：=$A$1+$B$1当将公式复制到D1单元格时仍为：=$A$1+$B$1 3、混合引用，复制公式时地址的部分内容跟着发生变化，如C1单元格有公式：=$A1+B$1当将公式复制到C2单元格时变为：=$A2+B$1当将公式复制到D1单元格时变为：=$A1+C$1 规律：加上了绝对地址符“$”的列标和行号为绝对地址，在公式向旁边复制时不会发生变化，没有加上绝对地址符号的列标和行号为相对地址，在公式向旁边复制时会跟着发生变化。混合引用时部分地址发生变化。注意：工作薄和工作表都是绝对引用，没有相对引用。技巧：在输入单元格地址后可以按F4键切换“绝对引用”、“混合引用”和“相对引用”状态（五）出错自动提示设置方法1使用“数据有效性”