Excel教学案例.docx
《Excel教学案例.docx》由会员分享,可在线阅读,更多相关《Excel教学案例.docx(24页珍藏版)》请在冰豆网上搜索。
Excel教学案例
第一讲:
IF函数
执行真假值判断,根据逻辑计算的真假值,返回不同结果。
可以使用函数IF对数值和公式进行条件检测。
语法
IF(logical_test,value_if_true,value_if_false)
Logical_test 表示计算结果为TRUE或FALSE的任意值或表达式。
例如,A10=100就是一个逻辑表达式,如果单元格A10中的值等于100,表达式即为TRUE,否则为FALSE。
本参数可使用任何比较运算符。
Value_if_true logical_test为TRUE时返回的值。
例如,如果本参数为文本字符串“预算内”而且logical_test参数值为TRUE,则IF函数将显示文本“预算内”。
如果logical_test为TRUE而value_if_true为空,则本参数返回0(零)。
如果要显示TRUE,则请为本参数使用逻辑值TRUE。
Value_if_true也可以是其他公式。
Value_if_false logical_test为FALSE时返回的值。
例如,如果本参数为文本字符串“超出预算”而且logical_test参数值为FALSE,则IF函数将显示文本“超出预算”。
如果logical_test为FALSE且忽略了Value_if_false(即value_if_true后没有逗号),则会返回逻辑值FALSE。
如果logical_test为FALSE且Value_if_false为空(即value_if_true后有逗号,并紧跟着右括号),则本参数返回0(零)。
Value_if_false也可以是其他公式。
说明
∙函数IF可以嵌套七层,用value_if_false及value_if_true参数可以构造复杂的检测条件。
请参阅下面最后一个示例。
∙在计算参数value_if_true和value_if_false后,函数IF返回相应语句执行后的返回值。
∙如果函数IF的参数包含数组,则在执行IF语句时,数组中的每一个元素都将计算。
∙MicrosoftExcel还提供了其他一些函数,可依据条件来分析数据。
例如,如果要计算单元格区域中某个文本字符串或数字出现的次数,则可使用COUNTIF工作表函数。
如果要根据单元格区域中的某一文本字符串或数字求和,则可使用SUMIF工作表函数。
请了解关于根据条件计算值。
示例1
示例2
第二讲:
IF函数嵌套
示例
在上例中,第二个IF语句同时也是第一个IF语句的参数value_if_false。
同样,第三个IF语句是第二个IF语句的参数value_if_false。
例如,如果第一个logical_test(Average>89)为TRUE,则返回“A”;如果第一个logical_test为FALSE,则计算第二个IF语句,以此类推。
用下列关键字指定数字的字母等级。
如果成绩是
则返回
大于89
A
80到89
B
70到79
C
60到69
D
小于60
F
示例:
逻辑函数在实际工作中的应用050
第三讲:
OR函数
在其参数组中,任何一个参数逻辑值为TRUE,即返回TRUE;任何一个参数的逻辑值为FALSE,即返回FALSE。
语法
OR(logical1,logical2,...)
Logical1,logical2,... 为需要进行检验的1到30个条件,分别为TRUE或FALSE。
说明
∙参数必须能计算为逻辑值,如TRUE或FALSE,或者为包含逻辑值的数组或引用。
∙如果数组或引用参数中包含文本或空白单元格,则这些值将被忽略。
∙如果指定的区域中不包含逻辑值,函数OR返回错误值#VALUE!
。
∙可以使用OR数组公式来检验数组中是否包含特定的数值。
若要输入数组公式,请按Ctrl+Shift+Enter。
示例
1
2
3
4
A
B
公式
说明(结果)
=OR(TRUE)
参数为TRUE(TRUE)
=OR(1+1=1,2+2=5)
所有参数的逻辑值为FALSE(FALSE)
=OR(TRUE,FALSE,TRUE)
至少一个参数为TRUE(TRUE)
示例:
逻辑函数在实际工作中的应用050
第四讲:
AND函数
所有参数的逻辑值为真时,返回TRUE;只要一个参数的逻辑值为假,即返回FALSE。
语法
AND(logical1,logical2,...)
Logical1,logical2,... 表示待检测的1到30个条件值,各条件值可为TRUE或FALSE。
说明
∙参数必须是逻辑值TRUE或FALSE,或者包含逻辑值的数组或引用。
∙如果数组或引用参数中包含文本或空白单元格,则这些值将被忽略。
∙如果指定的单元格区域内包括非逻辑值,则AND将返回错误值#VALUE!
。
示例
1
2
3
4
A
B
公式
说明(结果)
=AND(TRUE,TRUE)
所有参数的逻辑值为真(TRUE)
=AND(TRUE,FALSE)
一个参数的逻辑值为假(FALSE)
=AND(2+2=4,2+3=5)
所有参数的计算结果为真(TRUE)
第五讲:
IF函数和AND函数
示例1
示例2
示例:
逻辑函数在实际工作中的应用050
第六讲:
IF函数和OR函数
示例
第七讲:
IF、OR、AND函数综合应用
综合应用:
1、IF(OR(AND(B2>45,C2="男"),AND(B2>40,C2="女")),"否","是")
示例1
2、IF(OR(AND(B7<=45,C7="男"),AND(B7<=40,C7="女")),"是","否")
示例2
示例3
示例:
逻辑函数在实际工作中的应用050
第八讲:
FIND、FINDB函数
也应用于:
FINDB
FIND用于查找其他文本字符串(within_text)内的文本字符串(find_text),并从within_text的首字符开始返回find_text的起始位置编号。
也可使用SEARCH查找其他文本字符串中的某个文本字符串,但是,FIND和SEARCH不同,FIND区分大小写并且不允许使用通配符。
FINDB用于查找其他文本字符串(within_text)内的文本字符串(find_text),并基于每个字符所使用的字节数从within_text的首字符开始返回find_text的起始位置编号。
此函数用于双字节字符。
也可使用SEARCHB查找其他文本字符串中的某个文本字符串。
语法
FIND(find_text,within_text,start_num)
FINDB(find_text,within_text,start_num)
Find_text 是要查找的文本。
Within_text 是包含要查找文本的文本。
Start_num 指定开始进行查找的字符。
within_text中的首字符是编号为1的字符。
如果忽略start_num,则假设其为1。
提示
使用start_num可跳过指定数目的字符。
例如,假定使用文本字符串“AYF0093.YoungMensApparel”,如果要查找文本字符串中说明部分的第一个“Y”的编号,则可将start_num设置为8,这样就不会查找文本的序列号部分。
FIND将从第8个字符开始查找,而在下一个字符处即可找到find_text,于是返回编号9。
FIND总是从within_text的起始处返回字符编号,如果start_num大于1,也会对跳过的字符进行计数。
说明
∙如果find_text是空文本(""),则FIND会匹配搜索串中的首字符(即:
编号为start_num或1的字符)。
∙Find_text中不能包含通配符。
∙如果within_text中没有find_text,则FIND和FINDB返回错误值#VALUE!
。
∙如果start_num不大于0,则FIND和FINDB返回错误值#VALUE!
。
∙如果start_num大于within_text的长度,则FIND和FINDB返回错误值#VALUE!
。
示例(FIND)
技巧056库存表中各种品牌电视机的数量求和-FIND
示例(FINDB)
在以下示例中,FIND返回2,因为空文本("")位于字符串中的第二个位置,而FINDB返回3,因为每个字符均按字节进行计数,而第一个字符包含2个字节,所以第二个字符从第三个字节开始。
=FIND("学","好好学习")等于3
=FINDB("学","好好学习")等于5
第九讲:
ISNUMBER函数
语法
ISNUMBER(value)
示例1
示例2
第十讲:
SUMPRODUCT函数
在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。
语法
SUMPRODUCT(array1,array2,array3,...)
Array1,array2,array3,...为2到30个数组,其相应元素需要进行相乘并求和。
说明
∙数组参数必须具有相同的维数,否则,函数SUMPRODUCT将返回错误值#VALUE!
。
∙函数SUMPRODUCT将非数值型的数组元素作为0处理。
示例1
说明
上例所返回的乘积之和,与以数组形式输入的公式SUM(A2:
B4*C2:
D4)的计算结果相同。
使用数组公式可以为类似于SUMPRODUCT函数的计算提供更通用的解法。
例如,使用公式=SUM(A2:
B4^2)并按Ctrl+Shift+Enter可以计算A2:
B4中所有元素的平方和。
示例2
第十一讲:
FLOOR函数
将参数Number沿绝对值减小的方向向下舍入,使其等于最接近的significance的倍数。
语法
FLOOR(number,significance)
Number 所要四舍五入的数值。
Significance 基数。
说明
∙如果任一参数为非数值参数,则FLOOR将返回错误值#VALUE!
。
∙如果number和significance符号相反,则函数FLOOR将返回错误值#NUM!
。
∙不论number的正负号如何,舍入时参数的绝对值都将减小。
如果number恰好是significance的倍数,则无需进行任何舍入处理。
示例
原文件:
Excel-教程.xls
第十二讲:
CEILING函数
将参数Number向上舍入(沿绝对值增大的方向)为最接近的significance的倍数。
例如,如果您不愿意使用像“分”这样的零钱,而所要购买的商品价格为$4.42,可以用公式=CEILING(4.42,0.1)将价格向上舍入为以“角”表示。
语法
CEILING(number,significance)
Number要四舍五入的数值。
Significance 是需要四舍五入的乘数。
说明
∙如果参数为非数值型,CEILING返回错误值#VALUE!
。
∙无论数字符号如何,都按远离0的方向向上舍入。
如果数字已经为Significance的倍数,则不进行舍入。
∙如果Number和Significance符号不同,CEILING返回错误值#NUM!
。
示例
第十三讲:
隐秘函数
1)NUMBERSTRING,EXCEL中隐藏了一个数字转换为中文小写或大写数字的函数NUMBERSTRING,但它仅支持正整数,用中文数字的朋友们可以试试这个函数。
语法:
=NUMBERSTRING(VALUE,TYPE)
参数:
value:
要转化的数字
type:
返回结果的类型,有三种:
1,2,3
示例
2)DATESTRING函数是用来将指定日期转换为文本日期之间,DATESTRING函数语法是DATESTRING(serial_number)
示例
3)DATEDIF函数,计算年龄
示例
第1章技巧015-1隐秘函数示例
第十四讲:
LOOKUP函数
函数LOOKUP有两种语法形式:
向量和数组。
向量为只包含一行或一列的区域。
函数LOOKUP的向量形式是在单行区域或单列区域(向量)中查找数值,然后返回第二个单行区域或单列区域中相同位置的数值。
如果需要指定包含待查找数值的区域,则可以使用函数LOOKUP的这种形式。
函数LOOKUP的另一种形式为自动在第一列或第一行中查找数值。
语法1
向量形式
LOOKUP(lookup_value,lookup_vector,result_vector)
Lookup_value 为函数LOOKUP在第一个向量中所要查找的数值。
Lookup_value可以为数字、文本、逻辑值或包含数值的名称或引用。
Lookup_vector 为只包含一行或一列的区域。
Lookup_vector的数值可以为文本、数字或逻辑值。
要点 Lookup_vector的数值必须按升序排序:
...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE;否则,函数LOOKUP不能返回正确的结果。
文本不区分大小写。
Result_vector 只包含一行或一列的区域,其大小必须与lookup_vector相同。
说明
∙如果函数LOOKUP找不到lookup_value,则查找lookup_vector中小于或等于lookup_value的最大数值。
∙如果lookup_value小于lookup_vector中的最小值,函数LOOKUP返回错误值#N/A。
示例
示例
第十五讲:
COUNT函数
返回包含数字以及包含参数列表中的数字的单元格的个数。
利用函数COUNT可以计算单元格区域或数字数组中数字字段的输入项个数。
语法
COUNT(value1,value2,...)
Value1,value2,... 为包含或引用各种类型数据的参数(1到30个),但只有数字类型的数据才被计算。
说明
∙函数COUNT在计数时,将把数字、日期、或以文本代表的数字计算在内;但是错误值或其他无法转换成数字的文字将被忽略。
∙如果参数是一个数组或引用,那么只统计数组或引用中的数字;数组或引用中的空白单元格、逻辑值、文字或错误值都将被忽略。
如果要统计逻辑值、文字或错误值,请使用函数COUNTA。
示例1
示例2
第十六讲:
COUNTA函数
返回参数列表中非空值的单元格个数。
利用函数COUNTA可以计算单元格区域或数组中包含数据的单元格个数。
语法
COUNTA(value1,value2,...)
Value1,value2,... 为所要计算的值,参数个数为1到30个。
在这种情况下,参数值可以是任何类型,它们可以包括空字符(""),但不包括空白单元格。
如果参数是数组或单元格引用,则数组或引用中的空白单元格将被忽略。
如果不需要统计逻辑值、文字或错误值,请使用函数COUNT。
示例1
示例2
第十七讲:
COUNTBLANK函数
计算指定单元格区域中空白单元格的个数。
语法
COUNTBLANK(range)
Range 为需要计算其中空白单元格个数的区域。
说明
即使单元格中含有返回值为空文本("")的公式,该单元格也会计算在内,但包含零值的单元格不计算在内。
示例1
示例2
第十八讲:
COUNTIF函数
计算区域中满足给定条件的单元格的个数。
语法
COUNTIF(range,criteria)
Range 为需要计算其中满足条件的单元格数目的单元格区域。
Criteria 为确定哪些单元格将被计算在内的条件,其形式可以为数字、表达式或文本。
例如,条件可以表示为32、"32"、">32"或"apples"。
说明
MicrosoftExcel提供其他函数,可用来基于条件分析数据。
例如,若要计算基于一个文本字符串或某范围内的一个数值的总和,可使用SUMIF工作表函数。
若要使公式返回两个基于条件的值之一,例如某指定销售量的销售红利,可使用IF工作表函数。
示例1
示例2
示例3
第十九讲:
MOD函数
返回两数相除的余数。
结果的正负号与除数相同。
语法
MOD(number,divisor)
Number 为被除数。
Divisor 为除数。
说明
∙如果divisor为零,函数MOD返回错误值#DIV/0!
。
∙函数MOD可以借用函数INT来表示:
∙MOD(n,d)=n-d*INT(n/d)
示例1
第21章条件格式中使用函数-技巧232-1按不同地区数据设置间隔底纹.xls
第二十讲:
SUMIF函数
根据指定条件对若干单元格求和。
语法
SUMIF(range,criteria,sum_range)
Range 为用于条件判断的单元格区域。
Criteria 为确定哪些单元格将被相加求和的条件,其形式可以为数字、表达式或文本。
例如,条件可以表示为32、"32"、">32"或"apples"。
Sum_range 是需要求和的实际单元格。
说明
∙只有在区域中相应的单元格符合条件的情况下,sum_range中的单元格才求和。
∙如果忽略了sum_range,则对区域中的单元格求和。
∙MicrosoftExcel还提供了其他一些函数,它们可根据条件来分析数据。
例如,如果要计算单元格区域内某个文本字符串或数字出现的次数,则可使用COUNTIF函数。
如果要让公式根据某一条件返回两个数值中的某一值(例如,根据指定销售额返回销售红利),则可使用IF函数。
示例