Excel的相用及函用教程.ppt

Excel的相关运用及函数 应用教程 首先我们来认识一下Excel2007的操作界面 Excel 常用快捷键 认 识 公 式 类别运算符及含义含义示例 算术 +（加号）加1+2 （减号）减21 （负号）负数1 *（星号）乘2*3 /（斜杠）除4/2 %（百分比）百分比10% ?（乘方）乘幂3?2 比较 =（等号）等于A1=A2 （大于号）大于A1A2 =（大于等于号）大于等于A1=A2 （不等号）不等于A1、=、 区域运算符 交集运算符 联合运算符 负数 百分比 乘幂 乘和除 加和减 连接两个文本字符串（串 连） 比较运算符 各种运算符的优先级 使用公式 操作方法：控制柄CTRL+ENTER 5.3 单元格的引用 单元格的数据又叫作单元格的引用，分为相对引用和绝对引用 单元格的引用通常是为了使用某个单元格的公式，而对单元格进行标识的方式。 引用单元格能够通过所标识的单元格来快速获得公式对数据的运算。 2010年10月份销销售完成情况统计统计 产产品名称单单位实际销实际销 售金额额计计划销销售金额额完成百分比奖奖金 服务务器台1050010100103.96%525 交换换器台2454834550 集线线器个20002100 UPS电电源台920900 网卡个30003050 调调制解调调器个2340023000 中继继器台14001500 网络摄络摄像机台250002000 防火墙墙台20003000 以上公式中的完成百分比公式可以往下拉 即为相对引用； 而奖金这一栏所提的比例都是5%，所以这 一列是不变的，那么我们要在列前加个美 金符号，此即为绝对引用。因绝对引用时 较好麻烦，故用快捷键F4即可 跨工作表引用 跨工作簿引用 现在我们开始认识一下所谓的Excel函数。 什么是Excel函数？Excel函数就是预先定义，执行计 算、分析等处理数据任务的特殊公式。一个函数只有唯一 的一个名称，它决定了函数的功能和用途。在Excel中使 用函数，肯定是要以公式来徦接的，但是函数与公式是有 区别的，可是又有不可分割的联系。如果说前者是Excel 预先定义好的特殊公式，那么后者就是由用户自行设计对 工作表进行计算和处理的计算式。 函数格式函数名（参数1，参数2，） 函数一般分为10大类，那么这10大类函数呢并不是说我们都要掌握，我们只要掌握简单的、经常用到的就可以了，像财 务函数、逻辑函数、统计函数和查找引用函数。 序号分类功能简介 1数据库工作表函数分析数据清单中的数值是否符合特定条件 2日期与时间 函数在公式中分析和处理日期值和时间值 3工程函数用于工作分析 4信息函数确定存储在单元格中数据的类型 5财务 函数进行一般的财务计 算 6逻辑 函数进行逻辑 判断或者进行复合检验 7统计 函数对数据区域进行统计 分析 8查找和引用函数在数据清单中查找特定数据或者查找一个单元格的引用 9文本函数在公式中处理字符串 10数学和三角函数进行数学计算 使用函数 常用函数的应用 1、SUM函数 “sum”在英语中表示“总数、总和、求和”的意思，SUM函数是用来计算某一个或多个单 元格区域中所有数字的总和的求和函数。（最大参数量为250个） 2、 AVERAGE函数 “average”在英语中表示“平均、平均数”的意思，AVERAGE函数是用来计算指定数据集 合中所有数值平均值的函数。 其语法结构为： AVERAGE（number1,number2,） （最大参数量为255个） 3、MAX和MIN函数 又称最大量、最小量函数 “max”/“min”是“maximum”和“minimum”的缩写。在英语中分别表示“最大量”和“最小量”的 意思，分别用来返回指定区域内所有数值的最大值和最小值。 其语法结构为： MAX（number1,number2,） MIN（number1,number2,） 4、类似于MAX和MIN函数的還有LARGE和SMALL函数 “large”/“small”是分别表示“大的”和“小的”的意思，分别用来返回指定区域 内所有数值的第k个最大值和最小值。 其语法结构为： LARGE（array，k） （kn） SMALL（array，k） （kn） 注意：LARGE和MAX的区别 LARGE函数的统计范围比MAX函数广，可以返回第k个最大值，而MAX 函数只能返回指定数据集合中的最大值。 同理，SMALL和MIN函数也是如此。 5、 RANK函数 “rank”在英语中表示“等级、排列”的意思，用来返回指定区域内所有数值 的大小排序的情况。 其语法结构为： RANK（number，ref，order） number 需要进行排位的数值 ref 数字列表数组 order 表示对ref进行排序的方式 为0或省略时表示降序，为1表示升序 6、IF函数 “if”在英语中表示“如果，假设”的意思，用来判断真假值，在EXCEL中呢它是一个逻辑函数，根 据逻辑判断的真假值返回不同结果的函数。 其语法结构为： IF（logical_test,value_if_true,value_if_false） logical_test 判断的方式，返回值或表达式 value_if_true 表示判断结果为真时返回的值 value_if_false 表示判断结果为假时返回的值 类似于IF函数的还有COUNTIF函数 COUNTIF函数是计算数据区域中满足给定条件的单元格数量。 其语法结构为： COUNTIF（range，criteria） range 需要计算满足条件的单元格数目的单元格区域； criteria 确定哪些单元格将被计算在内的条件。 嵌套函数 7、VLOOKUP函数 VLOOKUP函数是在表格数据的首列查找指定的值，并由此返回表格数据当前行其他列的值。 其语法结构为： VLOOKUP（lookup_value,table_array,col_index_num,range_lookup） lookup_value 需要在表格数组第一列查找的数值； table_array 需要进行查询转置的数组或单元格区域； ol_index_num 待返回的匹配值的列序号； range_lookup 逻辑值，希望查找匹配的是精确值还是近似值，如果为true或省略，则为模糊查 询；false为精确匹配。 8、INDEX函数 返回数组中指定单元格或单元格数组的数值。 INDEX（array,row_num,column_num） Array：单元格区域或数组常数； Row_num为数组中某行的行序号，函数从该行返回数值； Column_num为数组中某列的列序号，函数从该列返回数值。 9、OFFSET函数 以指定引用为参考，通过给定偏移量返回新的引用 10、用Round函数让四舍五入变的更精确 在前面，我们介绍了，利用INT函数来构造四舍五入函数的方法，但 是，有时候我们会遇到更多小数位的四舍五入，用INT函数构造就会 有些吃力了，Excel的Round函数可以帮助我们。Round函数的作用 是返回某个数字按指定位数取整后的数字。语法为 “ROUND(number,num_digits)”，其中Number是需要进行四舍五入的 数字；Num_digits为指定的位数，按此位数进行四舍五入，如果 num_digits 大于 0，则四舍五入到指定的小数位，如果 num_digits 等于 0，则四舍五入到最接近的整数，如果 num_digits 小于 0，则在 小数点左侧进行四舍五入。 举例来说， =ROUND(2.15, 1) 将 2.15 四舍五入到一个小数位，结果为2.2 =ROUND(2.149, 1) 将 2.149 四舍五入到一个小数位结果为2.1 =ROUND(-1.475, 2) 将 -1.475 四舍五入到两小数位结果为-1.48) =ROUND(21.5, -1) 将 21.5 四舍五入到小数点左侧一位结果为20 11、SUMIF函数 函数名称：SUMIF 主要功能：计算符合指定条件的单元格区域内的数值和。 使用格式：SUMIF（Range,Criteria,Sum_Range） 参数说明：Range代表条件判断的单元格区域；Criteria为指定条件表达式； Sum_Range代表需要计算的数值所在的单元格区域。 应用举例：如图7所示，在D64单元格中输入公式：=SUMIF(C2:C63,“男“,D2:D63)，确 认后即可求出“男”生的语文成绩和。 特别提醒：如果把上述公式修改为：=SUMIF(C2:C63,“女“,D2:D63)，即可求出“女” 生的语文成绩和；其中“男”和“女”由于是文本型的，需要放在英文状态下的双引号 （“男“、“女“）中。 12、WEEKDAY函数 函数名称：WEEKDAY 主要功能：给出指定日期的对应的星期数。 使用格式：WEEKDAY(serial_number,return_type) 参数说明：serial_number代表指定的日期或引用含有日期的单元格；return_type代 表星期的表示方式当Sunday（星期日）为1、Saturday（星期六）为7时，该参数为1 ；当Monday（星期一）为1、Sunday（星期日）为7时，该参数为2（这种情况符合中国 人的习惯）；当Monday（星期一）为0、Sunday（星期日）为6时，该参数为3。 应用举例：输入公式：=WEEKDAY(TODAY(),2)，确认后即给出系统日期的星期数。 特别提醒：如果是指定的日期，请放在英文状态下的双引号中，如=WEEKDAY(“2003-12 -18“,2)。 13、CONCATENATE函数 函数名称：CONCATENATE 主要功能：将多个字符文本或单元格中的数据连接在一起，显示在一个单 元格中。 使用格式：CONCATENATE(Text1，Text) 参数说明：