excel函数公开课一等奖省优质课大赛获奖课件

EXCEL函数培训课件

吕治军-11-16

泼水节，大家相互泼水表示祝福，突然有个人骂道：哪个混蛋泼我了？大家劝道：泼水表示祝福。那人吼到：少扯淡，哪个混蛋用开水泼我了…

早晨特冷和朋友去ATM取钱，巧遇运钞车加钞，只好站一旁等。朋友问我冻手不？我冷冷说：冻手！瞬间四杆枪指向我俩。被送入派出所。路上一直缄默我问朋友你怎么不开腔呢？瞬间八杆枪指向我俩。警察：叫什么名字？我：蒋英羽。警察：什么名字？我：蒋英羽！警察：what

is

your

name？

历史考试选择题：黄花岗起义第一枪谁开？A宋教仁B孙中山C黄兴D徐锡麟，考生选C。又看第二题：黄花岗起义第二枪谁开？考生傻了，就选了个B。接着看第三题：黄花岗起义中，第三枪谁开？考生疯了，胡乱选了A。考试出来就去找出卷老师，老师拿出书本说：黄兴连开三枪，揭开了黄花岗起义序幕……

Excel函数培训1、什么是函数？函数是预先定义，执行计算、分析等处理数据任务特殊公式-------实际上就是已经定义好公式。2、什么是公式？ 公式就是用户自行设计对工作表数据计算和处理表示式。公式中能够包含函数。一、函数基础1、函数结构 函数是由函数名称和一个或多个参数组成，其形式为：函数名（参数1,参数2,…），参数可是常量（数字与文本），代数表示式，单元格及单元格区域，逻辑值，数组和其它函数。 函数语法:由函数名+括号+参数组成例求和函数:SUM(A1,B2,…)参数与参数之间用逗号“

,

”表示2、函数种类

(1)逻辑函数(2)时间与日期函数

(3)数学与三角函数(4)文本函数

(5)财务函数(6)统计函数

(7)查找与引用函数(8)其它函数3、相关概念

A、常规运算符：“+”“-”“*”“/”B、比较运算符：“=”“>”“<”“>=”“<=”“<>”

C、引用运算符:,:(用于区域)D、逻辑值：True(真-1,1)False(假0)E.单元格相对引用与绝对引用 样式:A1

$A1锁定第A例

A$1锁定第1横

$A$1锁定第A例与第1横

F4键是快速锁定功效键二、函数介绍与应用 (1)AND函数 格式：AND(logical1,logical2,...)

全部参数逻辑值为真时返回TRUE；只要一个参数逻辑值为假即返回FALSE。简言之，就是当AND参数全部满足某一条件时，返回结果为TRUE，不然为FALSE。

(2)OR函数 格式：OR(logical1,logical2,...) OR函数指在其参数组中，任何一个参数逻辑值为TRUE，即返回TRUE,它与AND函数区分在于，AND函数要求全部函数逻辑值均为真，结果方为真。而OR函数仅需其中任何一个为真即可为真

(3)NOT函数格式：NOT(logical) NOT函数用于对参数值求反。当要确保一个值不等于某一特定值时，能够使用NOT函数。简言之，就是当参数值为TRUE时，NOT函数返回结果恰与之相反，结果为FALSEAND(条件)

括号中条件值均为TRUE，则为TRUE；假如任一个为FALSE，则为FALSE=AND（5>3,2>1）=TRUE=AND（1>3,2>1）=FALSEOR(条件)

括号中条件值均为FALSE，则为FALSE；假如任一个为TRUE，则为TRUE=OR（5>3,2>1）=TRUE=OR（1>3,2>1）=TRUE=OR（1>3,2>5）=FALSENOT(条件)

括号中条件值为FALSE，则为TRUE；假如为TRUE，则为FALSE

=NOT（5>3）=FALSE=NOT（1>3）=TRUE表示式

IF(logical_test,value_if_true,value_if_false)

IF(逻辑条件,真值,假值)

说明

逻辑条件:

运算结果为True或者False表示式或值

真值

:Logical_test为True时返回值

假值:Logical_test为False时返回值

简言之，假如第一个参数logical_test返回结果为真话，则执行第二个参数Value_if_true结果，不然执行第三个参数Value_if_false结果IF函数用产品型号判断公式F1=IF(SUM(A1:A5)<>0,SUM(A1:A5),“”)

解析：假如SUM(A1:E1)不等于零，则在F1单元格中显示SUM(A1:E1)结果，不然显示字符串。

几点说明：

（1）SUM(C5:F5)不等于零正规写法是SUM(C5:F5)<>0，在EXCEL中能够省略<>0；

（2）“”表示字符串内容为空，所以执行结果是在单元格中不显示任何字符。

例：假如单元格A1、B1、C1中是各科成绩，假如有一科成绩不到60分，则不录用。公式为？=IF（OR（A1<60，B1<60，C1<60)”不录用”,”录用“）ABCDEF11223344245例解一嵌套使用：

If函数能够嵌套使用七层,利用

True、False结构复杂关系！IF函数方法一：G26=IF(F26>=180,IF(F26>=240,“优异”，“合格”）,“不合格”）例解二方法二：G26=IF(F26>=240,“优异”，IF(F26>=180,“合格”，“不合格”))思索：各工序目标是否达标。sum函数语法:

SUM(number1,number2,...)参数:

number1，number2，...为1到30个需要求和参数。定义：返回某一单元格区域中数字、逻辑值及数字文本表示式之和算术平均数:AVERAGE

格式：AVERAGE(数值1,数值2,…)ABC11002010002200403300603000

=AVERAGE（A1：A3）=200=AVERAGE（A1：A3，B1）=105四舍五入函数:ROUNDROUND(数值,小数位)ABC11002010002200403300603000

=ROUND(A1/A3,2)=0.33=ROUNDUP(A1/A3,2)=0.34=ROUNDDOWN(A1/A3,2)=0.33Int取整函数（取向较小方向数,向下取整）Int(数值)ABC110.820.3-10.32-10.840 int(A1)=10int(A2)=-11 int(B1)=20 int(C1)=-11定义：依据指定条件对若干单元格求和

sumif函数语法

SUMIF(range,criteria,sum_range)

1）range

为用于条件判断单元格区域。

2）criteria

为确定哪些单元格将被相加求和条件，其形式可认为数字、表达式或文本。例如，条件可以表示为32、“32”、“>32”或“李先春"。

3）sum_range

是需要求和实际单元格。求出上图中李先春销量总和？例解一=sumif(B2:E7,”李先春“，C2:E7)

结果=488求出上图中大于10数值总和？例解二sumif(C3:C7,">=10")

结果=33Count表示式：

Count(value1,value2,……)

说明:

计算连续或不连续区域数字个数。参数value代表1-30个各种不一样类型数据，或单元格引用，但只对数字型数据进行计算。COUNT

已知：A1=0A2=2A3=“”A4=“#REEF”

A5=Count(A1,A2,A3,A4,2)

结果

A6=3CountA表示式：

CountA(value1,value2,……)说明:

计算参数表中任意类型数据个数。或单元格区域中非空单元格个数。参数value代表1-30个各种不一样类型数据，或单元格引用。COUNTA

已知：

A1=0A2=2A3=“”A4=“#REEF”

A5=“总量”

A6=Count(A1,A2,A3,A4,A5,2)

结果

A6=6COUNT与COUNTA区分

count只统计数字型单元格个数,对于文字,错误值,逻辑值<True>不能统计,而counta能够,但对于空白单元格都不能统计.

countif函数语法：

COUNTIF(range,criteria)

说明：

Range为需要计算其中满足条件单元格数目标单元格区域，

Criteria为确定哪些单元格将被计算在内条件，其形式可认为数字、表达式、单元格引用或文本（条件可以表示为79、“79”、“>79”、“李先春”或B4）上图中求出数据1中A开头个数？例解一=countif(B2:B6,”A*”)

结果=3Left函数

表示式：Left(Text,num_chars)

说明

Text:文本字符串

num_chars:指定要提取字符数

注意:num_chars必须大于等于0

假如n_cs大于文本长度,返回全部文本假如省略num_chars,默认为1

功效:从字符串左端取指定个数字符

已知:A1=012141002A2=Left(A1,2)

结果:A2=01

例解一Left函数

已知:A1=012141002A2=right(A1,2)

结果:A2=02

例解一right函数

已知:A1=012141002A2=right(A1,2)

结果:A2=02

例解一right函数mid函数

表示式：

Mid(Text，start_num，num_chars)Text:文本字符串

start_num:提取第一个字符位置

num_chars:返回字符个数,不能是负数

（注：start_num大于文本总长度,返回空，start_num+num_chars大于文本总长度,返回到文本末字符串，start_num小于1,返回错误值#value!）

已知：

A1=002141001A2=mid(A1,3,3)结果

A2=214

mid函数功效：

从一个文本字符串指定位置开始，截取指定数目标字符。

字符截取计算字符长度LEN

LEN(文本)

计算字符串长度

=LEN（“abcdef”）=6SDWL1005C3N6JSTFR01SDWL1005C3N6JSTF=Left(,12)&Right(,len()-13)将数值转化为文本TEXTTEXT(数值,参数)参数为转换后文本格式.

=TEXT（1234,0）=1234将文本符转化为数值VALUEVALUE(数值文本),分列=VALUE（“1234”）=1234合并字符函数:CONCATENATECONCATENATE(文本1，…)

合并字符串&=CONCATENATE（“a”,“ef”）=aef比较两个字符是否完全相符EXACTEXACT(文本1,文本2)比较两个字符是否完全相符,是则为TRUE,不然为FALSE.=EXACT（“a”,“a”）=TRUE=EXACT（“a”,“ab”）=FALSE=EXACT（“a”,“A”）=FALSE大写函数：UPPER(text)把文本中小写字母改变成大写字母，是大写不变。

UPPER(“acbDE”)=ACBDE小写函数：LOWER(text)把文本中大写字母改变成小写字母，是小写不变。

LOWER(“acbDE”)=acbde去除文本中全部空格：TRIM(text)

除了单词之间单个空格外，去除文本中全部空格。TRIM(“SWPA5040S100MT”)=SWPA5040S100MTTRIM(“Howdoyoudo?”)=Howdoyoudo?Max-Min函数表示式：

Max(number1,number2,......)MAX

求出一组数中最大值。

数据计算MIN

求出一组数中最小值。

数据计算已知：

A1=6,A2=4,A3=8假如：A4=Max(A1,A2,A3,2)结果：A4=8Max-Min函数

假如：A4=MIN(A1,A2,A3,2)结果：A4=2Rank函数表示式：rank(Number,ref,order)使用功效：求指定数字在一列数字中排位（排名次）说明：Number代表需要求排位数据（通常使用单元格相对引用），ref代表Number所在一组数据（通常使用单元格区域绝对引用）Order为一数字，指明排位方式（0或省略为降序，大于0则为升序）。求A2在$A$2:$A$5单元格区域降序排列名次？

=rank(A2,$A$2:$A$5)

=2示例：EXCELABCD1分数排名2502326430552例：1-升序,0-降序

ABC11002010002200403300603000=RANK(A1,A1:A3,1)=1=RANK(A1,A1:A3,0)=3vlookup表示式:VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）

说明:1:要查找值;2:查找内容数组;3:数据后开始列数;4:TrueFalse

VLOOKUP函数功效:在表格或数值数组首列查找指定数值，并由此返回表格或数组当前行中指定列处数值。

Vlookup示例:

在B50中得出王勇总分数?B50=Vlookup(A50,A1:E5,5,0)

=223excelAB50王勇51郭俊在字符串中查找特定字符FIND查找一个字符在另一个字符串中位置FIND(文本,范围,数值)数值表示查找第几个.=FIND（“a”,“abcaef”,1）=1=FIND（“a”,“abcaef”,2）=4能够用来取小批号1.返回日期序列年份YEAR

YEAR(日期)

A1=-07-29星期二