EXCEL常用函数使用整理归类

EXCEL常用函数使用整理归类EXCEL中常用函数的使用1、求和函数:=SUM(区域或单元格,„„)2、条件式求和函数:=SUMIF(条件区域，条件，求和区域)3、多重条件求和函数:=SUMIFS(求和区域，条件区域1，条件1，条件区域2，条件2，„„)4、求最大值函数:=MAX(区域或单元格,„„)5、求最小值函数:=MIX(区域或单元格,„„)应用举例求选手的最后得分:=(SUM(D2:D8)-MAX(D2:D8)-MIN(D2:D8))/66、四舍五入函数:=ROUND(单元格或表达式或函数，保留小数位数)如:=ROUND($E3/30/8,0)*1.5*$F37、取整函数:(不是四舍五入而是直接去掉小数)=TRUNC(单元格或表达式或函数)8、排名函数:=RANK(单元格，单元格所在区域，0)9、还贷款额函数:=PMT(月利率，偿还期限，贷款总额)可求出每月的还款额10、开平方函数:=SQRT(单元格数字)11、数组公式:(1)计算单个结果:=SUM(F2:F17*G2:G17)+CTRL+SHIST+ENTER(一一对应分别乘起来后求和)(2)频率分布函数:=FREQUENCY(数据区域，频率点区段)+CTRL+SHIST+ENTER。注:输入函数前先需选定要生成频率的区域。12、求平均数函数:=AVERAGE(区域或单元格,„„)13、条件式求平均函数:=AVERAGEIF(条件区域，条件，平均区域)14、多重条件求平均函数:=AVERAGEIFS(求平均区域，条件区域1，条件1，条件区域2，条件2，„„)15、统计个数函数:=COUNT(区域或单元格,„„)16、条件式统计个数函数:=COUNTIF(统计区域，条件)17、多重条件统计个数函数:=COUNTIFS(条件区域1，条件1，条件区域2，条件2，„„)实际应用举例:及格率公式:=(COUNTIF(C2:C59,=60)/COUNT(C2:C59));优秀率公式:=(COUNTIF(C2:C59,=80)/COUNT(C2:C59));语文及格率公式:=COUNTIFS(语文,=90,班级,A8)/COUNTIFS(语文,0,班级,A8)90分以上人数公式:=COUNTIF(C2:C59,=90);80分~85分人数公式:=COUNTIF(C2:C59,=80)-COUNTIF(C2:C59,=85);60分以下人数公式:=COUNTIF(C2:C59,60)。440~450的人数:=COUNTIFS(前四,=440,班级,$A8)-COUNTIFS(前四,=450,班级,$A8)某班语文120~129分数段人数:=COUNTIFS(语文,=120,班级,A8)-COUNTIFS(语文,=130,班级,A8)18、垂直查询函数:=VLOOKUP(查询依据,查询区域，返回查询区域第几列的值，0)实际应用举例:=VLOOKUP($B4,员工考勤明细表!$A$3:$D$32,2,0)=VLOOKUP($A4,基本工资标准表!$A$3:$G$32,7,0)19、水平查询函数:=HLOOKUP(查询依据,查询区域，返回查询区域第几行的值，0)20、条件判断函数:=IF(条件，满足条件返回的值，不满足条件返回的值)如个税计算:=IF(h32000,IF((h3-2000)=500,(h3-2000)*5%,IF(AND((h3-2000)500,(h3-2000)=2000),(h3-2500)*10%+(2500-2000)*5%，IF(AND((h3-2000)2000,(h3-4000)=2000),(h3-4000)*15%+(4000-2500)*10%+(2500-2000)*5%))),0)如判断学生等级:=IF(F4=85,”优秀”，IF(F4=75,”良好”,IF(F4=60,”及格”,”不及格”)))如基本工资标准计算:=IF($C3=软件开发部,2000,IF($C3=图书开发部,1800,IF($C3=基础部,1200,1000)))21、逻辑运算函数:=AND(参数(条件)1，参数(条件)2，„„)同时成立(逻辑与)=OR(参数(条件)1，参数(条件)2，„„)其中一个成立(逻辑或)=NOT(参数(条件)1，参数(条件)2，„„)都不成立(逻辑非)如IF(OR(C3=70,d3=70),”入围”，”×”)如IF(AND(C3=70,d3=80),”入围”，”落选”)22、文本处理函数:=LEFT(文本字符串，位数)从左起取得相应位数的字符串=RIGHT(文本字符串，位数)从右起取得相应位数的字符串=LEN(文本字符串)可传回字符串的总长度=MID(文本字符串，起始位数，位数)应用举例:(1)身份证中提取出身年月日:=MID(H3，7，8)返回19950512(2)由身份证生成出身年月日=MID(H3，7，4)&”年”&MID(H3，11,2)&”月”&MID(H3，13，2)&”日”返回1995年05月12日(3)由身份证生成年龄:=DATEDIF(MID(H3，7，4)&”年”&MID(H3，11,2)&”月”&MID(H3，13，2)&”日”,TODAY(),”Y”)23、日期时间函数:(1)DATE函数主要功能:给出指定数值的日期。使用格式:DATE(year,month,day)参数说明:year为指定的年份数值(小于9999);month为指定的月份数值(可以大于12);day为指定的天数。应用举例:在C20单元格中输入公式:=DATE(2003,13,35)，确认后，显示出2004-2-4。特别提醒:由于上述公式中，月份为13，多了一个月，顺延至2004年1月;天数为35，比2004年1月的实际天数又多了4天，故又顺延至2004年2月4日。如生成当天日期函数:=TODAY()(返回今天的日期)如人的计算年龄:A、由身份证生成年龄:=DATEDIF(MID(H3，7，4)&”年”&MID(H3，11,2)&”月”&MID(H3，13，2)&”日”,TODAY(),”Y”)B、由出身年月日生成年龄:=DATEDIF(出生年月日所在单元格，TODAY()，”Y”)综合应用由身份证号直接生成性别:(身份证倒数第二位奇数为男，偶数为女)=IF(MID(H3，17，1)/2=TRUNC(MID(H3，17，1)/2，”女”,”男”)C、由入职时间计算工龄:=DATEDIF(员工年假表!$F3,TODAY(),Y)24、求数据集中出现频率最多的数MODE,MODE(区域或单元格,„„)MODE函数用来返回在某一数组或数据区域中出现频率最多的数值。跟MEDIAN一样，MODE也是一个位置测量函数。25、求数据集中的中位数MEDIAN,MEDIAN(区域或单元格,„„)返回给定数值区域的中位数26、INDEX函数主要功能:返回列表或数组中的元素值，此元素由行序号和列序号的索引值进行确定。使用格式:INDEX(array,row_num,column_num)参数说明:Array代表单元格区域或数组常量;Row_num表示指定的行序号(如果省略row_num，则必须有column_num);Column_num表示指定的列序号(如果省略column_num，则必须有row_num)。应用举例:如图3所示，在F8单元格中输入公式:=INDEX(A1:D11,4,3)，确认后则显示出A1至D11单元格区域中，第4行和第3列交叉处的单元格(即C4)中的内容。特别提醒:此处的行序号参数(row_num)和列序号参数(column_num)是相对于所引用的单元格区域而言的，不是Excel工作表中的行或列序号。27、INT函数=INT(需要取整的数值或包含数值的引用单元格)将数值向下取整为最接近的整数。应用举例:=INT(18.89)，确认后显示出18。=INT(-18.89)，则返回结果为-19。特别提醒:在取整时，不进行四舍五入.28、MOD函数:求出两数相除的余数。使用格式:MOD(被除数,除数)应用举例:=MOD(13,4)，确认后显示出结果“1”。MOD函数可以借用函数INT来表示:上述公式可以修改为:=13-4*INT(13/4)。29、DAY函数求出指定年月日或引用单元格中的年月日中的天数。应用举例:=DAY(2003-12-18)，确认后，显示出18。特别提醒:如果是给定的日期，请包含在英文双引号中MONTH函数求出指定年月日或引用单元格中的年月日的月份。应用举例:=MONTH(2003-11-18)，确认后，显示出11。特别提醒:如果是给定的日期，请包含在英文双引号中.30、YEAR函数求出指定年月日或引用单元格中的年月日中的年数应用举例:=YEAR(2003-12-18)，则返回年份对应的值“2003”。31、NOW函数给出当前系统日期和时间。与TODAY()的区别在于NOW()显示的是“,年,月,日,时”，而TODAY()只显示,年,月,日。应用举例:=NOW()，确认后即刻显示出当前系统日期和时间。如果系统日期和时间发生了改变，只要按一下F9功能键，即可让其随之改变。特别提醒:显示出来的日期和时间格式，可以通过单元格格式进行重新设置。32、TEXT函数根据指定的数值格式将相应的数字转换为文本形式。=TEXT(转换的数值或引用的单元格,指定文字形式的数字格式)应用举例:如果B68单元格中保存有数值1280.45，我们在C68单元格中输入公式:=TEXT(B68,$0.00)，确认后显示为“$1280.45”。特别提醒:“指定文字形式的数字格式”可以根据“单元格格式”对话框“数字”标签中的类型进行确定。33、VALUE函数将一个代表数值的文本型字符串转换为数值型。=VALUE(需要转换成数值型的文本型字符串或单元格引用)EXCEL如何求满足某个条件或多个条件的最大值1、例如:学生质量分析表中求某一个班的数学最高分或前四最高分或英语最高分:=MAX((班级=A8)*数学)同时按下ctrl+shift+enter输入数组公式,再选中N8,用自动填充柄下拉.=MAX((班级=A8)*前四)同时按下ctrl+shift+enter输入数组公式,再选中N8,用自动填充柄下拉.=MAX(IF(班级=A8,英语))同时按下ctrl+shift+enter输入数组公式,再选中N8,用自动填充柄下拉.2、例如:CELL值311312313314315316317318329321032113212321332143215分别求出CELL列中等于31和32对应的值列中的最大值。=MAX(IF(OR(A1:A100=31,A1:A100=32),B1:B100))按ctrl+shift+enter三键结束.或者用下面的公式，非数组公式，不用按三键:=SUMPRODUCT(OR(A1:A100=31,A1:A100=32)*MAX(B1:B100))如果你的数据在A2:B10000中,在C2中输入31,C3中输入32,选中C2:C3,用自动填充柄下拉到出现1000,在D2中输入=MAX(($A$2:$A$10000=C2)*$B$2:$B$10000)同时按下ctrl+shift+enter输入数组公式,再选中D2,用自动填充柄下拉.查找重复数据:如何快速找到重复项呢,利用函数公式吧。在B2单元格中输入=IF(COUNTIF(A:B,B1)1,重复,)，再向下拖拽即可。公式什么意思呢,在A列中寻找和A2单元格名字相同的姓名，如果有相同姓名，countif函数就会进行计数，当没有出现重复姓名时，if的判断条件不成立，此时不输入任何字符。当出现重复姓名时，if判断成立，输出“重复”两字。