EXCEL函数培训课件

《Excel函数的根底及应用》第一讲2010-04-10EXCEL高手操作函数图表分析VBAEXCEL根底知识讲解实例操作Excel根底及函数的应用函数根底及应用1Excel根底及函数的应用EXCEL的根本操作EXCEL工作簿及工作环境一、操作2EXCEL中表格的美化及数据的处理31、工作簿与工作表的关系2、工具的用途3、工作环境的设定1EXCEL工作簿及工作环境1、数据录入技巧

2、数据、表格格式化

3、自动求和自动填充EXCEL的根本操作24、单元格的编辑和置换

5、复制、选择性粘贴

6、查找、替换的强大功能

7、页面设置、打印的方式

8、学会盲打和使用快捷键1、合理的使用格式工具2、常规运算符：“+”“-”“*”“/”3、比较运算符：“<”“>”“=”“<>”4、使用公式对数值进行计算5、绝对、相对引用EXCEL中表格的美化及数据的处理3《Excel函数的根底及应用》第二讲2010-051、函数的定义

2、函数的应用

3、案例的分析Excel根底及函数的应用函数二、函数§运用根本函数实现对数据、单元格的操作

§运用函数Count、Countif等进行复杂数量统计

§运用函数Sumif、Sumproduct、数组公式等进行有条件的求和

§运用函数Vlookup、Hlookup进行高级数据查询

§Datedif、Mid等主要日期和文本函数的应用

§函数嵌套的计算、引用函数与查询函数结合使用学习目标Excel中所提的函数其实是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。用户可以直接用它们对某个区域内的数值进行一系列运算，如分析和处理日期值和时间值、确定贷款的支付额、确定单元格中的数据类型、计算平均值、排序显示和运算文本数据等等。例如，SUM函数对单元格或单元格区域进行加法运算。什么是函数？Excel根底及函数的应用函数的定义逻辑函数:AND、OR、NOT、TRUE、FALSE、IF函数Excel根底及函数的应用认识逻辑函数这三个函数都用来返回参数逻辑值AND、OR、NOT表达式：AND(logical1,logical2,...)OR(logical1,logical2,...)NOT(logical1,logical2,...)EXCEL逻辑函数AND函数：所有参数的逻辑值为真时返回TRUE；只要一个参数的逻辑值为假即返回FALSE。简言之，就是当AND的参数全部满足某一条件时，返回结果为TRUE，否那么为FALSE。EXCEL逻辑函数定义OR函数：OR函数指在其参数组中，任何一个参数逻辑值为TRUE，即返回TRUE,它与AND函数的区别在于，AND函数要求所有函数逻辑值均为真，结果方为真。而OR函数仅需其中任何一个为真即可为真EXCEL逻辑函数定义NOT函数：NOT函数用于对参数值求反。当要确保一个值不等于某一特定值时，可以使用NOT函数。简言之，就是当参数值为TRUE时，NOT函数返回的结果恰与之相反，结果为FALSEEXCEL逻辑函数定义TRUE、FALSE这两个函数用来返回参数的逻辑值，可以直接在单元格或公式中键入值TRUE或者FALSE。EXCEL逻辑函数定义IF函数IF函数用于执行真假值判断后，根据逻辑测试的真假值返回不同的结果，因此If函数也称之为条件函数。它的应用很广泛，可以使用函数IF对数值和公式进行条件检测语法：IF(logical_test,value_if_true,value_if_false)。说明：Logical_test表示计算结果为TRUE或FALSE的任意值或表达式。本参数可使用任何比较运算符EXCEL逻辑函数例：Excel根底及函数的应用假设单元格A1、B1、C1中是各科成绩，如果有一科成绩不到60分，那么不录取。公式为？=IF〔OR〔A1<60，B1<60，C1<60)”不录取”,”录取“〕♂常用函数

♂重要函数Excel根底及函数的应用If

SUM--SUMIF

Count--counta

COUNTIF

Average〔求平均值〕

Left-Right-Mid

Max-Min

RANK(排位)

FREQUENCY(频率分布〕Excel根底及函数的应用常用函数表达式

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函数Excel根底及函数的应用IF函数公式F1=IF(SUM(A1:A5),SUM(A1:A5),“”)

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

几点说明：

〔1〕SUM(C5:F5)不等于零的正规写法是SUM(C5:F5)<>0，在EXCEL中可以省略<>0；

〔2〕""表示字符串的内容为空，因此执行的结果是在单元格中不显示任何字符。ABCDEF11223344245例解一EXCEL函数例如IF函数公式E61=IF(D61<=300,"预算内","超出预算")例解二EXCEL函数例如IF函数方法一：G26=IF(F26>=180,IF(F26>=240,“优秀”，“合格”〕,“不合格”〕例解三EXCEL函数例如IF函数方法二：G26=IF(F26>=240,“优秀”，IF(F26>=180,“合格”，“不合格”))嵌套使用：

If函数可以嵌套使用七层,利用

True、False构造复杂的关系！IF函数EXCEL函数实例操作If函数Excel根底及函数的应用sum函数语法:

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

number1，number2，...为1到30个需要求和的参数。定义：返回某一单元格区域中数字、逻辑值及数字的文本表达式之和Excel根底及函数的应用说明：

直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算。

如果参数为数组或引用，只有其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。

如果参数为错误值或为不能转换成数字的文本，将会导致错误。

Excel根底及函数的应用例如：

SUM(5,3)等于8。

SUM(“6",3,TRUE)等于10，因为文本值被转换成数字，而逻辑值"TRUE"被转换成数字1。

不同于前例，如果A1包含"3"，而B1包含TRUE，那么：SUM(A1,B1,2)等于2，因为对非数值型的值的引用不能被转换成数值。

如果单元格A2：E2包含5，15，30，40和50，那么：SUM(A2:C2)等于50；SUM(B2:E2,15)等于150。Excel根底及函数的应用定义：根据指定条件对假设干单元格求和sumif函数语法

SUMIF(range,criteria,sum_range)

1〕range

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

2〕criteria

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

3〕sum_range

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

结果=488Excel根底及函数的应用求出上图中大于10的数值总和？例解二sumif(C3:C7,">=10")

结果=33Excel根底及函数的应用实例操作sumif函数Excel根底及函数的应用Count表达式：

Count(value1,value2,……)

说明:计算连续或不连续区域的数字个数。参数value代表1-30个各种不同类型的数据，或单元格引用，但只对数字型数据进行计算。Excel根底及函数的应用COUNT：A1=0A2=2A3=“”A4=“#REEF”

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

结果

A6=3Excel根底及函数的应用CountA表达式：

CountA(value1,value2,……)说明:计算参数表中任意类型数据的个数。或单元格区域中非空的单元格个数。参数value代表1-30个各种不同类型的数据，或单元格引用。Excel根底及函数的应用COUNTA：A1=0A2=2A3=“”A4=“#REEF”

A5=“总量”

A6=Count(A1,A2,A3,A4,A5,2)结果A6=6Excel根底及函数的应用COUNT与COUNTA的区别

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

Excel根底及函数的应用实例操作Count函数Counta函数Excel根底及函数的应用countif函数语法：

COUNTIF(range,criteria)

说明：

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

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

结果=3Excel根底及函数的应用Left函数

表达式：

Left(Text,num_chars)

说明

Text:文本字符串

num_chars:指定要提取的字符数

注意:

num_chars必须大于等于0如果n_cs大于文本长度,返回所有文本如果省略num_chars,默认为1

Excel根底及函数的应用功能:从字符串左端取指定个数字符:A1=012141002A2=Left(A1,2)结果:A2=01

例解一Left函数Excel根底及函数的应用right函数功能:从字符串右端取指定个数字符表达式right(Text,num_chars)

说明

Text:文本字符串

num_chars:指定要提取的字符数Excel根底及函数的应用:A1=012141002A2=right(A1,2)结果:A2=02

例解一right函数Excel根底及函数的应用Right函数与Left函数用法相同,只是意义不同.Excel根底及函数的应用mid函数表达式：Mid(Text，start_num，num_chars)Text:文本字符串start_num:提取的第一个字符的位置num_chars:返回的字符的个数,不能是负数〔注：start_num大于文本总长度,返回空，start_num+num_chars大于文本总长度,返回到文本末的字符串，start_num小于1,返回错误值#value!〕Excel根底及函数的应用：A1=002141001A2=mid(A1,3,3)结果A2=214

mid函数功能：

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

字符截取Excel根底及函数的应用Left-Right-Mid实例操作Excel根底及函数的应用Max-Min函数

表达式：

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

求出一组数中的最大值。

数据计算MIN

求出一组数中的最小值。

数据计算Excel根底及函数的应用：A1=6,A2=4,A3=8如果：A4=Max(A1,A2,A3,2)结果：A4=8Max-Min函数

如果：A4=MIN(A1,A2,A3,2)结果：A4=2Excel根底及函数的应用学会使用嵌套的关系联结符&对有价值数据的提取和组合实例操作Excel根底及函数的应用Max-Min函数

Rank函数Excel根底及函数的应用表达式：rank(Number,ref,order)使用功能：求指定数字在一列数字中的排位〔排名次〕说明：Number代表需要求排位的数据〔通常使用单元格的相对引用〕，ref代表Number所在的一组数据〔通常使用单元格区域的绝对引用〕Order为一数字，指明排位的方式〔0或省略为降序，大于0那么为升序〕。求A2在$A$2:$A$5单元格区域降序排列的名次？

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

=2Excel根底及函数的应用例如：EXCELABCD1分数排名2502326430552实例操作Excel根底及函数的应用Rank函数FREQUENCY函数Excel根底及函数的应用表达式：

FREQUENCY〔Data_array,Bins_array)说明：参数Data_array用于计算频率的数组，可以是一个单元格区域。参数Bins_array代表分组区间数组，表示频率计算的分段点。

功能：求参数序列的频率分布。举例：Excel根底及函数的应用=FREQUENCY〔B2：B7,C2:C5〕,可以求各个分数段的人数。EXCELABCDE1姓名分数分数段说明结果2李先春7570<=70个数13郭俊808071-80个数34马虎909081-90个数15文军67>=90个数16高伟917番有才78分数小于等于70的个数(1)

成绩介于71-80之间的个数(3)

成绩介于81-90之间的个数(1)

成绩大于等于91的个数(1)

注意:公式必须以数组公式的形式输入。选中从公式单元格开始的单元格区域C2:C5。按F2，再按Ctrl+Shift+Enter。如果公式未以数组公式的形式输入，那么返回的结果为1FREQUENCY函数实例操作重点函数Vlookup

hlookup

Sumif

Sumproduct

Datedif

Weeknum

monthExcel根底及函数的应用vlookup表达式:VLOOKUP〔lookup_value，table_array，col_index_num，range_lookup〕

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

Excel根底及函数的应用VLOOKUP函数的功能:在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。

Vlookup例如:

Excel根底及函数的应用在B50中得出王勇的总分数?B50=Vlookup(A50,A1:E5,5,0)

=223excelAB50王勇51郭俊实例操作vlookupExcel根底及函数的应用Excel根底及函数的应用HLOOKUP函数表达式:HLOOKUP(lookup_value,table_array,row_index_um,range_lookup)

参数lookup_value用于设定要查找的值，可以是数值，也可以是文本字符串或引用，参数table_array用于设置要在其中查找数据的数据表，这里可以使用区域或区域名称的引用，参数row_index_um为在查找之后要返回的匹配值的行序号，参数range_lookup是一个逻辑值，用于指明函数在查找时是精确匹配，还是近似匹配hlookup函数就是查找函数，不过它与lookup和vlookup函数有区别

hlookup是按行查找的vlookup是按列查找的

=hlookup("要查找的值","待查找的数据区域",行数)Excel根底及函数的应用Hlookup例如:ExcelABCDE1日期姓名123汇总2李鹏