1.4.Excel--函数

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 公式和函数
  1. 相同点:公式与函数的相同点就是:都是以“=”开头的,并且都会得到一个返回值。

    公式:公式是一个等式,以“=”开头,后面紧跟数据和运算符,并得到返回值。

    例如下面C1单元格中的 =A1+B1 就是一个公式。

在这里插入图片描述

函数:函数是excel内部预定义的功能,以“=”开头,按照特定的规则进行计算,并得到返回值。

例如下面C1单元格中的 =SUM(A1:B1)就使用的SUM函数。
在这里插入图片描述

  1. 不同点

    • 函数可以是公式里面的一部分,但公式不一定总需要包含函数。所以公式的范畴是更大的,公式包含函数。

      例如下面C1单元格中的 =SUM(A1:B1)+10就是一个公式,在这个公式中包含了sum函数。

在这里插入图片描述

  • 函数有唯一的函数名称,而公式没有。
 比如说我们刚刚举例用到的这个sum函数,它的名称就叫sum。每个函数都有特定的功能和用途,比如sum函数是用来求和的。
  • 函数和公式的录入方法不同,录入函数可以通过“插入函数”,如下所示:(公式—》文本—》插入函数)

在这里插入图片描述

​ 也可以通过在单元格中输入 = 号,然后从下拉列表中选择函数。如下所示:
在这里插入图片描述

​ 而公式可以直接在单元格中输入等于号,并用运算符连接单元格即可进行计算。

1.2 函数的使用–Excel常用函数4

1.2_1 IF+AND_OR例子

前提:

  1. IF 语句可能有两个结果。 第一个结果是比较结果为 True,第二个结果是比较结果为 False。

    例如,=IF(C2=”Yes”,1,2) 表示 IF(C2 = Yes, 则返回 1, 否则返回 2)。

  2. AND函数,它是一个逻辑函数,用于确定测试中的所有条件是否均为 TRUE,参数最多256个。

在这里插入图片描述

  1. OR函数,它是一个逻辑函数,用于确定测试中的所有条件是否均为 TRUE。

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9keseMty-1608973908251)(.\media_函数\OR.png)]

  1. 案例1:所有学科分数都要大于60,才算通过

    方案:=IF(AND(E2>60,F2>60,G2>60),“通过”,“不通过”)

  2. 案例2:只要有1门通过就算通过

    方案:=IF(OR(E2>60,F2>60,G2>60),“通过”,“不通过”)

1.2_2 COUNTIF统计
  1. 前提1:COUNT() 计算数值的个数

    案例:统计有多少个整数

    方案:=COUNT(D47:D51)

    在这里插入图片描述

  2. 前提2:COUNTA()计算非空单元格个数

    案例:统计有多少个非空单元格

    方案:=COUNTA(D47:D51)
    在这里插入图片描述

  3. 前提3:COUNTIF 是一个统计函数,用于统计满足某个条件的单元格的数量;

    COUNTIF 的最简形式为:=COUNTIF(要检查哪些区域? 要查找哪些内容?)

    例如,统计特定城市在客户列表中出现的次数。

    ​ eg:=COUNTIF(A2:A5,“London”)

    案例:统计迟到的次数,旷课的次数

    方案:=COUNTIF(B2:K2,“b”)

  4. 前提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水电费
  1. 案例:计算电费(if嵌套)

    方案:=IF(D3<=240,D3*B$11,IF(D3<=400,D3*C$11,D3*D$11))

  2. 作业:水费,气费 注意:水费每吨都会有污水处理费

1.2_4 借贷-sumif
  1. 前提1:SUMIF 函数对 范围 中符合指定条件的值求和

    例如,如果某列中含有数字,你只需对大于 10000 的数值求和。 可使用以下公式:=SUMIF(B2:B25,">10000")

    案例:金额大于10000的钱进行求和(条件求和)

    方案:=SUMIF(H3:H14,">10000")

  2. 前提2:SUMIF(range, criteria, [sum_range])如果第一个参数是数字,那么就可以省略第三个参数

    案例:借款金额总和

    方案:=SUMIF(D3:D14,“借”,H3:H14)

  3. 前提3:SUMIFS是office2007新增函数,使用该函数可快速对多条件单元格求和,sumifs函数功能十分强大,可以通过不同范围的条件求规定范围的和。

    案例:计算贷款方,建设银行的金融综合

    方案:=SUMIFS(H3:H14,D3:D14,“贷”,E3:E14,“建行”)

1.2_5 日期函数
  1. 前提:YEAR(serial_number)

    案例:查看某日期的年份

    方案:=YEAR(A2)
    在这里插入图片描述

  2. 前提:MONTH(serial_number)

    案例:查看某日期的月份

    方案:=MONTH(A3)在这里插入图片描述

  3. 前提:DAY(serial_number)

    案例:查看某日期的日

    方案:=DAY(A4)

在这里插入图片描述

  1. 前提:TODAY()

    案例:返回当前日期

    方案:=TODAY()

  2. 前提:DATE(year,month,day)

    案例:返回指定日期

    方案:=DATE(2020,7,20)

  3. 前提:NOW()

    案例:返回当前日期和时间。

    方案:=NOW() 注意在常规的前提下

  4. 前提:EDATE(start_date,months)

    案例1:获取当前时间的后一个月时间

    方案:=EDATE(A2,1)

    案例2:获取当前时间的前一个月时间

    方案:=EDATE(A2,-1)

  5. 前提:EOMONTH(start_date,months)

    案例1:该月最后一天日期

    方案:EOMONTH(A1,0)

    案例2:返回某一个月的下一个月的最后一天的日期

    方案:EOMONTH(A1,1)

  6. 案例:该月有多少天

    方案:=DAY(EOMONTH(A2,0))

  7. 案例:第几季度

方案:=IF((MONTH(A2)/3)<=1,“第一季度”,

​ IF((MONTH(A2)/3)<=2,“第二季度”,

​ IF((MONTH(A2)/3)<=3,“第三季度”,

​ IF((MONTH(A2)/3)<=4,“第四季度”))))

  1. 提示: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判断闰年
  1. 提示: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 隐藏几位手机号
  1. 提示: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. 方案:计算三个单元格总和—》选中三个单元格—》下拉
1.2_9 生成间断序号
  1. 案例:是空得单元格不做统计,并且排序
  2. 方案:=IF(B8="","",COUNTA(B$2:B8))
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值