1.1 函数
1.1.1 概念
Excel 里的函数是预先写好的一个运算公式,接受零或多个参数返回计算结果,以常用的求和函数SUM为例,它的语法是sum(number1,number2)
1.1.2 常用函数
函数类型 | 函数名字 |
---|---|
数学函数 | int(),mod(),round(),abs(),sqrt(),rand(),randbetween() |
统计函数 | min(),sum(),count(),counta(),average(),countif(),sumif(),averageif(),countifs(),sumifs() |
averageifs(),frequency(),rank() | |
日期函数 | year(),month(),day(),today(),date(),now(),edate(),eomonth(),datedif() |
文本函数 | mid(),left(),right(),len(),text(),rept(),replace(),substitute() |
逻辑函数 | if(),and(),or(),not() |
查找与引用函数 | vlookup(),offset(),match(),index(),indirect(),row(),column(),hlookup |
1.1.3 公式和函数
-
相同点:公式与函数的相同点就是:都是以“=”开头的,并且都会得到一个返回值。
公式:公式是一个等式,以“=”开头,后面紧跟数据和运算符,并得到返回值。
例如下面C1单元格中的 =A1+B1 就是一个公式。
函数:函数是excel内部预定义的功能,以“=”开头,按照特定的规则进行计算,并得到返回值。
例如下面C1单元格中的 =SUM(A1:B1)就使用的SUM函数。
-
不同点
-
函数可以是公式里面的一部分,但公式不一定总需要包含函数。所以公式的范畴是更大的,公式包含函数。
例如下面C1单元格中的 =SUM(A1:B1)+10就是一个公式,在这个公式中包含了sum函数。
-
- 函数有唯一的函数名称,而公式没有。
比如说我们刚刚举例用到的这个sum函数,它的名称就叫sum。每个函数都有特定的功能和用途,比如sum函数是用来求和的。
- 函数和公式的录入方法不同,录入函数可以通过“插入函数”,如下所示:(公式—》文本—》插入函数)
也可以通过在单元格中输入 = 号,然后从下拉列表中选择函数。如下所示:
而公式可以直接在单元格中输入等于号,并用运算符连接单元格即可进行计算。
1.2 函数的使用–Excel常用函数4
1.2_1 IF+AND_OR例子
前提:
IF 语句可能有两个结果。 第一个结果是比较结果为 True,第二个结果是比较结果为 False。
例如,=IF(C2=”Yes”,1,2) 表示 IF(C2 = Yes, 则返回 1, 否则返回 2)。
AND函数,它是一个逻辑函数,用于确定测试中的所有条件是否均为 TRUE,参数最多256个。
OR函数,它是一个逻辑函数,用于确定测试中的所有条件是否均为 TRUE。
-
案例1:所有学科分数都要大于60,才算通过
方案:=IF(AND(E2>60,F2>60,G2>60),“通过”,“不通过”)
-
案例2:只要有1门通过就算通过
方案:=IF(OR(E2>60,F2>60,G2>60),“通过”,“不通过”)
1.2_2 COUNTIF统计
-
前提1:COUNT() 计算数值的个数
案例:统计有多少个整数
方案:=COUNT(D47:D51)
-
前提2:COUNTA()计算非空单元格个数
案例:统计有多少个非空单元格
方案:=COUNTA(D47:D51)
-
前提3:COUNTIF 是一个统计函数,用于统计满足某个条件的单元格的数量;
COUNTIF 的最简形式为:=COUNTIF(要检查哪些区域? 要查找哪些内容?)
例如,统计特定城市在客户列表中出现的次数。
eg:=COUNTIF(A2:A5,“London”)
案例:统计迟到的次数,旷课的次数
方案:=COUNTIF(B2:K2,“b”)
-
前提4:COUNTIFS函数将条件应用于跨多个区域的单元格,然后统计满足所有条件的次数。
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)
COUNTIFS 函数语法具有以下参数:
- criteria_range1 必需。 在其中计算关联条件的第一个区域。
- criteria1 必需。 条件的形式为数字、表达式、单元格引用或文本,它定义了要计数的单元格范围。 例如,条件可以表示为 32、">32"、B4、"apples"或 “32”。
- criteria_range2, criteria2, … 可选。 附加的区域及其关联条件。 最多允许 127 个区域/条件对。
案例:统计1月1号和1月2号有多少人全勤
方案:=COUNTIFS(B2:B11,“a”,C2:C11,“a”)
1.2_3 IF水电费
-
案例:计算电费(if嵌套)
方案:=IF(D3<=240,D3*B$11,IF(D3<=400,D3*C$11,D3*D$11))
-
作业:水费,气费 注意:水费每吨都会有污水处理费
1.2_4 借贷-sumif
-
前提1:SUMIF 函数对 范围 中符合指定条件的值求和
例如,如果某列中含有数字,你只需对大于 10000 的数值求和。 可使用以下公式:=SUMIF(B2:B25,">10000")
案例:金额大于10000的钱进行求和(条件求和)
方案:=SUMIF(H3:H14,">10000")
-
前提2:SUMIF(range, criteria, [sum_range])如果第一个参数是数字,那么就可以省略第三个参数
案例:借款金额总和
方案:=SUMIF(D3:D14,“借”,H3:H14)
-
前提3:SUMIFS是office2007新增函数,使用该函数可快速对多条件单元格求和,sumifs函数功能十分强大,可以通过不同范围的条件求规定范围的和。
案例:计算贷款方,建设银行的金融综合
方案:=SUMIFS(H3:H14,D3:D14,“贷”,E3:E14,“建行”)
1.2_5 日期函数
-
前提:YEAR(serial_number)
案例:查看某日期的年份
方案:=YEAR(A2)
-
前提:MONTH(serial_number)
案例:查看某日期的月份
方案:=MONTH(A3)
-
前提:DAY(serial_number)
案例:查看某日期的日
方案:=DAY(A4)
-
前提:TODAY()
案例:返回当前日期
方案:=TODAY()
-
前提:DATE(year,month,day)
案例:返回指定日期
方案:=DATE(2020,7,20)
-
前提:NOW()
案例:返回当前日期和时间。
方案:=NOW() 注意在常规的前提下
-
前提:EDATE(start_date,months)
案例1:获取当前时间的后一个月时间
方案:=EDATE(A2,1)
案例2:获取当前时间的前一个月时间
方案:=EDATE(A2,-1)
-
前提:EOMONTH(start_date,months)
案例1:该月最后一天日期
方案:EOMONTH(A1,0)
案例2:返回某一个月的下一个月的最后一天的日期
方案:EOMONTH(A1,1)
-
案例:该月有多少天
方案:=DAY(EOMONTH(A2,0))
-
案例:第几季度
方案:=IF((MONTH(A2)/3)<=1,“第一季度”,
IF((MONTH(A2)/3)<=2,“第二季度”,
IF((MONTH(A2)/3)<=3,“第三季度”,
IF((MONTH(A2)/3)<=4,“第四季度”))))
-
提示:round函数语法是ROUND(number, num_digits),number 必需,要四舍五入的数字;num_digits 必需,位数,按此位数对 number 参数进行四舍五入;
案例1:ROUND
方案1:=ROUND(G13,1)
rounddown函数语法是ROUNDDOWN(number,num_digits),Number 为需要向下舍入的任意实数;Num_digits 舍入后的数字的位数;
案例2:ROUNDDOWN(G13,2)
方案2:=ROUNDDOWN(G13,2)
ROUNDUP(number, num_digits)
ROUNDUP 函数语法具有下列参数:number 必需。 需要向上舍入的任意实数。 num_digits 必需。 要将数字舍入到的位数。
案例3:ROUNDUP
方案3:=ROUNDUP(G13,0)
1.2_6 MOD判断闰年
-
提示:MOD(number, divisor)
MOD 函数语法具有下列参数:
number 必需。 要计算余数的被除数。
divisor 必需。 除数。
公式 说明 值 =MOD(3, 2) 3/2 的余数 1 =MOD(-3, 2) -3/2 的余数。 符号与除数相同 1 =MOD(3, -2) 3/-2 的余数。 符号与除数相同 -1 =MOD(-3, -2) -3/-2 的余数。 符号与除数相同 -1 案例:如果能被100整除,且能被400整除,则为闰年
如果能被4整除,且不能被100整除,则为闰年
方案:=IF(OR(AND(MOD(A2,100)=0,MOD(A2,400)=0),AND(MOD(A2,4)=0,MOD(A2,100)<>0)),“闰年”,“平年”)
1.2_7 隐藏几位手机号
-
提示:replace函数的语法格式
=Replace(old_text,start_num,num_chars,new_text)
=Replace(要替换的字符串,开始位置,替换个数,新的文本)
注意:第四个参数是文本,要加上引号。
案例:隐藏8到10位电话号码
方案:=REPLACE(A2,8,3,"***")
方案2:=REPLACE(A2,8,3,REPT("*",3))
1.2_8 合并单元格并且计算
- 案例:将三个单元格计算总和
- 方案:计算三个单元格总和—》选中三个单元格—》下拉
1.2_9 生成间断序号
- 案例:是空得单元格不做统计,并且排序
- 方案:=IF(B8="","",COUNTA(B$2:B8))