excel函数应用.docx
《excel函数应用.docx》由会员分享，可在线阅读，更多相关《excel函数应用.docx（10页珍藏版）》请在冰豆网上搜索。
excel函数应用
excel函数应用
查找公式
VLOOKUP（C2,$D$45:
$H$197,5,FALSE）
根据C2单元格的值,查找$D$45:
$H$197范围内以之相同的值,再取相同的值的同行第5列单元格数值。
判断公式
IF（A4*B4/100>800,800,A4*B4/100）
数组工式
{=SUM（（$D$2:
$D$24=D2）*（$F$2:
$F$24）}，如果我想同时满足双重条件，也就是如果$D$2:
$D$24=D2，而且$E$2:
$E$24=E2，那这个公式应该怎么写呢？
如果$D$2:
$D$24=D2，而且$E$2:
$E$24=E2则汇总对应的$F$2:
$F$24:
=SUMPRODUCT（（$D$2:
$D$24=D2）*（$E$2:
$E$24=E2）,（$F$2:
$F$24）
ROUND函数（4舍5入）
“=ROUND（（RAND（）＊42＋258）,0）”回车即可，式中42是258和300的差。
ROUND函数的功能是设置给定数的小数位数，将从C的方法进行取舍。
比如ROUND（3.1415926,2）=3.14，ROUND（3.1415926,3）=3.142。
假设您的计算机里未安装RANDBETWEEN函数，要产生258到300之间的随机整数，可以在单元格中输入“
AND函数（条件）
使用格式：
AND（logical1,logical2,...）参数说明：
Logical1,Logical2,Logical3……：
表示待测试的条件值或表达式，最多这30个。
在C5单元格输入公式：
=AND（A5>=60,B5>=60），确认。
如果C5中返回TRUE，说明A5和B5中的数值均大于等于60，如果返回FALSE，说明A5和B5中的数值至少有一个小于60
AVERAGE函数、算术平均值
使用格式：
AVERAGE（number1,number2,……）参数说明：
number1,number2,……：
需要求平均值的数值或引用单元格（区域），参数不超过30个。
应用举例：
在B8单元格中输入公式：
=AVERAGE（B7:
D7,F7:
H7,7,8），确认后，即可求出B7至D7区域、F7至H7区域中的数值和7、8的平均值。
COLUMN函数显示所引用单元格的列标号值
使用格式：
COLUMN（reference）
参数说明：
reference为引用的单元格。
应用举例：
在C11单元格中输入公式：
=COLUMN（B11），确认后显示为2（即B列）。
特别提醒：
如果在B11单元格中输入公式：
=COLUMN（），也显示出2；与之相对应的还有一个返回行标号值的函数——ROW（reference）。
CONCATENATE（合并字符）
使用格式：
CONCATENATE（Text1，Text……）参数说明：
Text1、Text2……为需要连接的字符文本或引用的单元格。
应用举例：
在C14单元格中输入公式：
=CONCATENATE（A14,"@",B14,".com"），确认后，即可将A14单元格中字符、@、B14单元格中的字符和.com连接成一个整体，显示在C14单元格中。
特别提醒：
如果参数不是引用的单元格，且为文本格式的，请给参数加上英文状态下的双引号，如果将上述公式改为：
=A14&"@"&B14&".com"，也能达到相同的目的。
COUNTIF函数（条件表达数值大于等于的单元格数目）
使用格式：
COUNTIF（Range,Criteria）
参数说明：
Range代表要统计的单元格区域；Criteria表示指定的条件表达式。
应用举例：
在C17单元格中输入公式=COUNTIF（B1:
B13,">=80"），确认后，即可统计出B1至B13单元格区域中，数值大于等于80的单元格数目。
特别提醒：
允许引用的单元格区域中有空白单元格出现。
DATE函数给出指定数值的日期
使用格式：
DATE（year,month,day）
参数说明：
year为指定的年份数值（小于9999）；month为指定的月份数值（可以大于12）；day为指定的天数。
应用举例：
在C20单元格中输入公式：
=DATE（2003,13,35），确认后，显示出2004-2-4。
特别提醒：
由于上述公式中，月份为13，多了一个月，顺延至2004年1月；天数为35，比2004年1月的实际天数又多了4天，故又顺延至2004年2月4
ABS函数（绝对值）
使用格式：
ABS（number）
参数说明：
number代表需要求绝对值的数值或引用的单元格。
应用举例：
如果在B2单元格中输入公式：
=ABS（A2），则在A2单元格中无论输入正数（如100）还是负数（如-100），B2中均显示出正数（如100）。
特别提醒：
如果number参数不是数值，而是一些字符（如A等），则B2中返回错误值“#VALUE！
DATEDIF函数（日期相差）
使用格式：
=DATEDIF（date1,date2,"y"）、=DATEDIF（date1,date2,"m"）、=DATEDIF（date1,date2,"d"）
参数说明：
date1代表前面一个日期，date2代表后面一个日期；y（m、d）要求返回两个日期相差的年（月、天）数
应用举例：
在C23单元格中输入公式：
=DATEDIF（A23,TODAY（）,"y"），确认后返回系统当前日期[用TODAY（）表示）与A23单元格中日期的差值，并返回相差的年数。
特别提醒：
这是Excel中的一个隐藏函数，在函数向导中是找不到的，可以直接输入使用，对于计算年龄、工龄等非常有效
日期-DAY函（日）MONTH函数（月）年YEAR（A1）
使用格式：
DAY（serial_number）
参数说明：
serial_number代表指定的日期或引用的单元格。
应用举例：
输入公式：
=DAY（"2003-12-18"），确认后，显示出18。
特别提醒：
如果是给定的日期，请包含在英文双引号中
DCOUNT函数（列中满足指定条件）
主要功能：
返回数据库或列表的列中满足指定条件并且包含数字的单元格数目。
使用格DCOUNT（database,field,criteria）参数说明：
Database表示需要统计的单元格区域；Field表示函数所使用的数据列（在第一行必须要有标志项）；Criteria包含条件的单元格区域
应用举例：
如图1所示，在F4单元格中输入公式：
=DCOUNT（A1:
D11,"语文",F1:
G2），确认后即可求出“语文”列中，成绩大于等于70，而小于80的数值单元格数目（相当于分数段人数）。
FREQUENCY函数（统计各分数段人数）
主要功能：
以一列垂直数组返回某个区域中数据的频率分布。
使用格式FREQUENCY（data_array,bins_array）
参数说明：
Data_array表示用来计算频率的一组数据或单元格区域；Bins_array表示为前面数组进行分隔一列数值。
应用举例：
如图2所示，同时选中B32至B36单元格区域，输入公式：
=FREQUENCY（B2:
B31,D2:
D36），输入完成后按下“Ctrl+Shift+Enter”组合键进行确认，即可求出B2至B31区域中，按D2至D36区域进行分隔的各段数值的出现频率数目（相当于统计各分数段人数）。
INT函数（取整为最接近的整数）
主要功能：
将数值向下取整为最接近的整数。
使用格式：
INT（number）参数说明：
number表示需要或包含数值的引用单元格。
应用举例：
输入公式：
=INT（18.89），确认后显示出18。
特别提醒：
在取整时，不进行四舍五入；如果输入的公式为=INT（-18.89），则返回结果为-19。
LEFT函数（截取的数目）
使用格式：
LEFT（text,num_chars）
参数说明：
text代表要截字符的字符串；num_chars代表给定的截取数目。
应用举例：
假定A38单元格中保存了“我喜欢天极网”的字符串，我们在C38单元格中输入公式：
=LEFT（A38,3），确认后即显示出“我喜欢”的字符。
特别提醒：
此函数名的英文意思为“左”，即从左边截取，Excel很多函数都取其英文的意思。
MID函数（截取的数目）
使用格式：
MID（text,start_num,num_chars）
参数说明：
text代表一个文本字符串；start_num表示指定的起始位置；num_chars表示要截取的数目
应用举例：
假定A47单元格中保存了“我喜欢天极网”的字符串，我们在C47单元格中输入公式：
=MID（A47,4,3），确认后即显示出“天极网”的字符。
特别提醒：
公式中各参数间，要用英文状态下的逗号“,”隔开
MAX函数（求最大值）
使用格式：
MAX（number1,number2……）
参数说明：
number1,number2……代表需要求最大值的数值或引用单元格（区域），参数不超过30个。
应用举例：
输入公式：
=MAX（E44:
J44,7,8,9,10），确认后即可显示出E44至J44单元和区域和数值7，8，9，10中的最大值。
特别提醒：
如如果参数中有文本或逻辑值，则忽略
MIN函数（求最小值）
使用格式：
MIN（number1,number2……）
参数说明：
number1,number2……代表需要求最小值的数值或引用单元格（区域），参数不超过30个。
应用举例：
输入公式：
=MIN（E44:
J44,7,8,9,10），确认后即可显示出E44至J44单元和区域和数值7，8，9，10中的最小值。
特别提醒：
如果参数中有文本或逻辑值，则忽略。
NOW函数（显示系统日期和时间）
使用格式：
NOW（）
参数说明：
该函数不需要参数
应用举例：
输入公式：
=NOW（），确认后即刻显示出当前系统日期和时间。
如果系统日期和时间发生了改变，只要按一下F9功能键，即可让其随之改变。
特别提醒：
显示出来的日期和时间格式，可以通过单元格格式进行重新设置。
OR函数（逻辑条件）
使用格式：
OR（logical1,logical2,...）
参数说明：
Logical1,Logical2,Logical3……：
表示待测试的条件值或表达式，最多这30个。
应用举例：
在C62单元格输入公式：
=OR（A62>=60,B62>=60），确认。
如果C62中返回TRUE，说明A62和B62中的数值至少有一个大于或等于60，如果返回FALSE，说明A62和B62中的数值都小于60。
特别提醒：
如果指定的逻辑条件参数中包含非逻辑值时，则函数返回错误值“#VALUE!
”或“#NAME”。
RANK函数（排名）
使用格式：
RANK（Number,ref,order）
参数说明：
Number代表需要排序的数值；ref代表排序数值所处的单元格区域；order代表排序方式参数（如果为“0”或者忽略，则按降序排名，即数值越大，排名结果数值越小；如果为非“0”值，则按升序排名，即数值越大，排名结果数值越大；）
应用举例：
如在C2单元格中输入公式：
=RANK（B2,$B$2:
$B$31,0），确认后即可得出丁1同学的语文成绩在全班成绩中的排名结果。
特别提醒：
在上述公式中，我们让Number参数采取了相对引用形式，而让ref参数采取了绝对引用形式（增加了一个“$”符号），这样设置后，选中C2单元格，将鼠标移至该单元格右下角，成细十字线状时（通常称之为“填充柄”），按住左键向下拖拉，即可将上述公式快速复制到C列下面的单元格中，完成其他同学语文成绩的排名统计。
RIGHT函数（截取指定数目的字符）
使用格式：
RIGHT（text,num_chars）
参数说明：
text代表要截字符的字符串；num_chars代表给定的截取数目。
应用举例：
假定A65单元格中保存了“我喜欢天极网”的字符串，我们在C65单元格中输入公式：
=RIGHT（A65,3），确认后即显示出“天极网”的字符。
特别提醒：
Num_chars参数必须大于或等于0，如果忽略，则默认其为1；如果num_chars参数大于文本长度，则函数返回整个文本。
SUBTOTAL函数（分类汇总显示单元格非空值的和）
主要功能：
返回列表或数据库中的分类汇总。
使用格式：
SUBTOTAL（function_num,ref1,ref2,...）参数说明：
Function_num为1到11（包含隐藏值）或101到111（忽略隐藏值）之间的数字，用来指定使用什么函数在列表中进行分类汇总计算（如图6）；ref1,ref2,……代表要进行分类汇总区域或引用，不超过29个。
应用举例：
如图7所示，在B64和C64单元格中分别输入公式：
=SUBTOTAL（3,C2:
C63）和=SUBTOTAL103,C2:
C63），并且将61行隐藏起来，确认后，前者显示为62（包括隐藏的行），后者显示为61，不包括隐藏的行。
特别提醒：
如果采取自动筛选，无论function_num参数选用什么类型，SUBTOTAL函数忽略任何不包括在筛选结果中的行；SUBTOTAL函数适用于数据列或垂直区域，不适用于数据行或水平区域。
SUMIF函数（条件表达式）
使用格式：
SUMIF（Range,Criteria,Sum_Range）
参数说明：
Range代表条件判断的单元格区域；Criteria为指定条件表达式；Sum_Range代表需要计算的数值所在的单元格区域。
应用举例：
如图7所示，在D64单元格中输入公式：
=SUMIF（C2:
C63,"男",D2:
D63），确认后即可求出“男”生的语文成绩和。
特别提醒：
如果把上述公式修改为：
=SUMIF（C2:
C63,"女",D2:
D63），即可求出“女”生的语文成绩和；其中“男”和“女”由于是文本型的，需要放在英文状态下的双引号（"男"、"女"）中。
TEXT函数（数字转换为文本）
使用格式：
TEXT（value,format_text）
参数说明：
value代表需要转换的数值或引用的单元格；format_text为指定文字形式的数字格式。
应用举例：
如果B68单元格中保存有数值1280.45，我们在C68单元格中输入公式：
=TEXT（B68,"$0.00"），确认后显示为“$1280.45”。
特别提醒：
format_text参数可以根据“单元格格式”对话框“数字”标签中的类型进行确定。
OFFSET（基点单元格，移动的行数，移动的列数，所要引用的高度，所要引用的宽度）
例
OFFSET（A3,2,3,1,1）
A3是基点单元格，
2是正数，为向下移动2行，负为向上移动2行
3是正数，为向右移动3列，负为向左移动3列
1是引用1个单元格的高度
1是引用1个单元格的宽度
它的结果是引用了D5中数值。
函数OFFSET实际上并不移动任何单元格或更改选定区域，它只是返回一个引用。
函数OFFSET可用于任
何需要将引用作为参数的函数。
例如，公式SUM（OFFSET（C2,1,2,3,1））将计算比单元格C2靠下1行并
靠右2列的3行1列的区域的总值。
执行真假值判断，根据逻辑计算的真假值，返回不同结果。
可以使用函数IF对数值和公式进行条件检测。
语法
IF（logical_test,value_if_true,value_if_false）
Logical_test表示计算结果为TRUE或FALSE的任意值或表达式。
例如，A10=100就是一个逻辑表达式，如果单元格A10中的值等于100，表达式即为TRUE，否则为FALSE。
本参数可使用任何比较运算符。
Value_if_truelogical_test为TRUE时返回的值。
例如，如果本参数为文本字符串“预算内”而且logical_test参数值为TRUE，则IF函数将显示文本“预算内”。
如果logical_test为TRUE而value_if_true为空，则本参数返回0（零）。
如果要显示TRUE，则请为本参数使用逻辑值TRUE。
Value_if_true也可以是其他公式。
Value_if_falselogical_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工作表函数。
请了解关于根据条件计算值。
示例
=IF（A2<=100,"Withinbudget","Overbudget"）如果上面的数字小于等于100，则公式将显示“Withinbudget”。
否则，公式显示“Overbudget”。
（Withinbudget）
=IF（A2=100,SUM（B5:
B15）,""）如果上面数字为100，则计算单元格区域B5:
B15，否则返回空文本（""）
升级会员 