Excel应用大全.docx
- 文档编号:29022920
- 上传时间:2023-07-20
- 格式:DOCX
- 页数:21
- 大小:41.03KB
Excel应用大全.docx
《Excel应用大全.docx》由会员分享,可在线阅读,更多相关《Excel应用大全.docx(21页珍藏版)》请在冰豆网上搜索。
Excel应用大全
一、用于求平均值的统计函数AVERAGE、TRIMMEAN
1、求参数的算术平均值函数AVERAGE
语法形式为AVERAGE(number1,number2,...)
其中Number1,number2,...为要计算平均值的1~30个参数。
这些参数可以是数字,或者是涉及数字的名称、数组或引用。
如果数组或单元格引用参数中有文字、逻辑值或空单元格,则忽略其值。
但是,如果单元格包含零值则计算在内。
2、求数据集的内部平均值TRIMMEAN
函数TRIMMEAN先从数据集的头部和尾部除去一定百分比的数据点,然后再求平均值。
当希望在分析中剔除一部分数据的计算时,可以使用此函数。
比如,我们在计算选手平均分数中常用去掉一个最高分,去掉一个最低分,XX号选手的最后得分,就可以使用该函数来计算。
语法形式为TRIMMEAN(array,percent)
其中Array为需要进行筛选并求平均值的数组或数据区域。
Percent为计算时所要除去的数据点的比例,例如,如果percent=0.2,在20个数据点的集合中,就要除去4个数据点(20x0.2),头部除去2个,尾部除去2个。
函数TRIMMEAN将除去的数据点数目向下舍为最接近的2的倍数。
3、举例说明:
示例中也列举了带A的函数AVERAGEA的求解方法。
求选手Annie的参赛分数。
在这里,我们先假定已经将该选手的分数进行了从高到底的排序,在后面的介绍中我们将详细了解排序的方法。
二、用于求单元格个数的统计函数COUNT
语法形式为COUNT(value1,value2,...)
其中Value1,value2,...为包含或引用各种类型数据的参数(1~30个),但只有数字类型的数据才被计数。
函数COUNT在计数时,将把数字、空值、逻辑值、日期或以文字代表的数计算进去;但是错误值或其他无法转化成数字的文字则被忽略。
如果参数是一个数组或引用,那么只统计数组或引用中的数字;数组中或引用的空单元格、逻辑值、文字或错误值都将忽略。
如果要统计逻辑值、文字或错误值,应当使用函数COUNTA。
举例说明COUNT函数的用途,示例中也列举了带A的函数COUNTA的用途。
仍以上例为例,要计算一共有多少评委参与评分(用函数COUNTA),以及有几个评委给出了有效分数(用函数COUNT)。
三、求区域中数据的频率分布FREQUENCY
由于函数FREQUENCY返回一个数组,必须以数组公式的形式输入。
语法形式为FREQUENCY(data_array,bins_array)
其中Data_array为一数组或对一组数值的引用,用来计算频率。
如果data_array中不包含任何数值,函数FREQUENCY返回零数组。
Bins_array为一数组或对数组区域的引用,设定对data_array进行频率计算的分段点。
如果bins_array中不包含任何数值,函数FREQUENCY返回data_array元素的数目。
看起来FREQUENCY的用法蛮复杂的,但其用处很大。
比如可以计算不同工资段的人员分布,公司员工的年龄分布,学生成绩的分布情况等。
这里以具体示例说明其基本的用法。
以计算某公司的员工年龄分布情况为例说明。
在工作表里列出了员工的年龄。
这些年龄为28、25、31、21、44、33、22和35,并分别输入到单元格C4:
C11。
这一列年龄就是data_array。
Bins_array是另一列用来对年龄分组的区间值。
在本例中,bins_array是指C13:
C16单元格,分别含有值25、30、35、和40。
以数组形式输入函数FREQUENCY,就可以计算出年龄在25岁以下、26~30岁、31~35岁、36~40岁和40岁以上各区间中的数目。
本例中选择了5个垂直相邻的单元格后,即以数组公式输入下面的公式。
返回的数组中的元素个数比bins_array(数组)中的元素个数多1。
第五个数字1表示大于最高间隔(40)的数值(44)的个数。
函数FREQUENCY忽略空白单元格和文本值。
{=FREQUENCY(C4:
C11,C13:
C16)}等于{2;2;2;1;1}
四、一组用于求数据集的满足不同要求的数值的函数
1、求数据集的最大值MAX与最小值MIN
这两个函数MAX、MIN就是用来求解数据集的极值(即最大值、最小值)。
函数的用法非常简单。
语法形式为函数(number1,number2,...),其中Number1,number2,...为需要找出最大数值的1到30个数值。
如果要计算数组或引用中的空白单元格、逻辑值或文本将被忽略。
因此如果逻辑值和文本不能忽略,请使用带A的函数MAXA或者MINA来代替。
2、求数据集中第K个最大值LARGE与第k个最小值SMALL
这两个函数LARGE、SMALL与MAX、MIN非常想像,区别在于它们返回的不是极值,而是第K个值。
语法形式为:
函数(array,k),其中Array为需要找到第k个最小值的数组或数字型数据区域。
K为返回的数据在数组或数据区域里的位置(如果是LARGE为从大到小排,若为SMALL函数则从小到大排)。
说到这,大家可以想得到吧。
如果K=1或者K=n(假定数据集中有n个数据)的时候,是不是就可以返回数据集的最大值或者最小值了呢。
3、求数据集中的中位数MEDIAN
MEDIAN函数返回给定数值集合的中位数。
所谓中位数是指在一组数据中居于中间的数,换句话说,在这组数据中,有一半的数据比它大,有一半的数据比它小。
语法形式为MEDIAN(number1,number2,...)其中Number1,number2,...是需要找出中位数的1到30个数字参数。
如果数组或引用参数中包含有文字、逻辑值或空白单元格,则忽略这些值,但是其值为零的单元格会计算在内。
需要注意的是,如果参数集合中包含有偶数个数字,函数MEDIAN将返回位于中间的两个数的平均值。
4、求数据集中出现频率最多的数MODE
MODE函数用来返回在某一数组或数据区域中出现频率最多的数值。
跟MEDIAN一样,MODE也是一个位置测量函数。
语法形式为MODE(number1,number2,...)其中Number1,number2,...是用于众数(众数指在一组数值中出现频率最高的数值)计算的1到30个参数,也可以使用单一数组(即对数组区域的引用)来代替由逗号分隔的参数。
5、以上函数的示例
以某单位年终奖金分配表为例说明。
在示例中,我们将利用这些函数求解该单位年终奖金分配中的最高金额、最低金额、平均金额、中间金额、众数金额以及第二高金额等。
详细的公式写法可从图中清楚的看出,在此不再赘述。
五、用来排位的函数RANK、PERCENTRANK
1、一个数值在一组数值中的排位的函数RANK
数值的排位是与数据清单中其他数值的相对大小,当然如果数据清单已经排过序了,则数值的排位就是它当前的位置。
数据清单的排序可以使用Excel提供的排序功能完成。
语法形式为RANK(number,ref,order)其中Number为需要找到排位的数字;Ref为包含一组数字的数组或引用。
Order为一数字用来指明排位的方式。
如果order为0或省略,则Excel将ref当作按降序排列的数据清单进行排位。
如果order不为零,MicrosoftExcel将ref当作按升序排列的数据清单进行排位。
需要说明的是,函数RANK对重复数的排位相同。
但重复数的存在将影响后续数值的排位。
嗯,这就好像并列第几的概念啊。
例如,在一列整数里,如果整数10出现两次,其排位为5,则11的排位为7(没有排位为6的数值)。
2、求特定数值在一个数据集中的百分比排位的函数PERCENTRANK
此PERCENTRANK函数可用于查看特定数据在数据集中所处的位置。
例如,可以使用函数PERCENTRANK计算某个特定的能力测试得分在所有的能力测试得分中的位置。
语法形式为PERCENTRANK(array,x,significance)其中Array为彼此间相对位置确定的数字数组或数字区域。
X为数组中需要得到其排位的值。
Significance为可选项,表示返回的百分数值的有效位数。
如果省略,函数PERCENTRANK保留3位小数。
3、与排名有关的示例
仍以某单位的年终奖金分配为例说明,这里以员工Annie的排名为例说明公式的写法。
奖金排名的公式写法为:
=RANK(C3,$C:
$C)
百分比排名的公式写法为:
=PERCENTRANK($C:
$C,C3)
日期时间函数
DATE
返回代表特定日期的序列号。
如果在输入函数前,单元格格式为“常规”,则结果将设为日期格式。
语法
DATE(year,month,day)
Year 参数year可以为一到四位数字。
MicrosoftExcel将根据所使用的日期系统来解释year参数。
默认情况下,MicrosoftExcelforWindows将使用1900日期系统,而MicrosoftExcelforMacintosh将使用1904日期系统。
对于1900年日期系统
如果year位于0(零)到1899(包含)之间,则Excel会将该值加上1900,再计算年份。
例如:
DATE(108,1,2)将返回2008年1月2日(1900+108)。
如果year位于1900到9999(包含)之间,则Excel将使用该数值作为年份。
例如:
DATE(2008,1,2)将返回2008年1月2日。
如果year小于0或大于等于10000,则Excel将返回错误值#NUM!
。
对于1904年日期系统
如果year位于4到1899(含)之间,则Excel会将该值加上1900,再计算年份。
例如:
DATE(108,1,2)将返回2008年1月2日(1900+108)。
如果year位于1904到9999(含)之间,则Excel将使用该数值作为年份。
例如:
DATE(2008,1,2)将返回2008年1月2日。
如果year小于4或大于等于10000,或者位于1900到1903(含)之间,则Excel将返回错误值#NUM!
。
Month 代表每年中月份的数字。
如果所输入的月份大于12,将从指定年份的一月份开始往上加算。
例如:
DATE(2008,14,2)返回代表2009年2月2日的序列号。
Day 代表在该月份中第几天的数字。
如果day大于该月份的最大天数,则将从指定月份的第一天开始往上累加。
例如,DATE(2008,1,35)返回代表2008年2月4日的序列号。
说明
MicrosoftExcel可将日期存储为可用于计算的序列号。
默认情况下,1900年1月1日的序列号是1而2008年1月1日的序列号是39448,这是因为它距1900年1月1日有39448天。
MicrosoftExcelfortheMacintosh使用另外一个默认日期系统。
函数DATE在年、月、日为变量的公式中非常有用。
DATEVALUE
返回date_text所表示的日期的序列号。
函数DATEVALUE的主要功能是将以文本表示的日期转换成一个序列号。
语法
DATEVALUE(date_text)
Date_text 代表以MicrosoftExcel日期格式表示的日期的文本。
例如,"2008-1-30"或"30-Jan-08"就是带引号的文本,它用于代表日期。
在使用ExcelforWindows中的默认日期系统时,date_text必须表示1900年1月1日到9999年12月31日之间的一个日期;而在使用ExcelforMacintosh中的默认日期系统时,date_text必须表示1904年1月1日到9999年12月31日之间的一个日期。
如果date_text超出上述范围,则函数DATEVALUE返回错误值#VALUE!
。
如果省略date_text中的年份部分,则函数DATEVALUE使用计算机系统内部时钟的当前年份。
date_text中的时间信息将被忽略。
说明
MicrosoftExcel可将日期存储为可用于计算的序列号。
默认情况下,1900年1月1日的序列号是1而2008年1月1日的序列号是39448,这是因为它距1900年1月1日有39448天。
MicrosoftExcelfortheMacintosh使用另外一个默认日期系统。
大部分函数都会自动将日期值转换成序列号
DAY
返回以序列号表示的某日期的天数,用整数1到31表示。
语法
DAY(serial_number)
Serial_number 为要查找的那一天的日期。
应使用DATE函数来输入日期,或者将日期作为其他公式或函数的结果输入。
例如,可使用函数DATE(2008,5,23)输入日期2008年5月23日。
如果日期以文本的形式输入,则会出现问题。
说明
MicrosoftExcel可将日期存储为可用于计算的序列号。
默认情况下,1900年1月1日的序列号是1而2008年1月1日的序列号是39448,这是因为它距1900年1月1日有39448天。
MicrosoftExcelfortheMacintosh使用另外一个默认日期系统。
不论日期值以何种格式显示,YEAR,MONTH和DAY函数返回的值都是Gregorian值。
例如,如果日期的显示格式是回历,则YEAR、MONTH和DAY函数返回的值将是与相同的Gregorian日期相关联的值。
DAYS360
按照一年360天的算法(每个月以30天计,一年共计12个月),返回两日期间相差的天数,这在一些会计计算中将会用到。
如果财务系统是基于一年12个月,每月30天,可用此函数帮助计算支付款项。
语法
DAYS360(start_date,end_date,method)
Start_date 和end_date是用于计算期间天数的起止日期。
如果start_date在end_date之后,则DAYS360将返回一个负数。
应使用DATE函数来输入日期,或者将日期作为其他公式或函数的结果输入。
例如,使用函数DATE(2008,5,23)输入日期2008年5月23日。
如果日期以文本的形式输入,则会出现问题。
Method 为一个逻辑值,它指定了在计算中是采用欧洲方法还是美国方法。
Method
定义
FALSE或省略
美国方法(NASD)。
如果起始日期是一个月的31号,则等于同月的30号。
如果终止日期是一个月的31号,并且起始日期早于30号,则终止日期等于下一个月的1号,否则,终止日期等于本月的30号。
TRUE
欧洲方法。
起始日期和终止日期为一个月的31号,都将等于本月的30号。
说明
MicrosoftExcel可将日期存储为可用于计算的序列号。
默认情况下,1900年1月1日的序列号是1,而2008年1月1日的序列号是39448,这是因为它距1900年1月1日有39448天。
MicrosoftExcelfortheMacintosh使用另外一个默认日期系统。
HOUR
返回时间值的小时数。
即一个介于0(12:
00A.M.)到23(11:
00P.M.)之间的整数。
语法
HOUR(serial_number)
Serial_number 表示一个时间值,其中包含要查找的小时。
时间有多种输入方式:
带引号的文本字符串(例如:
"6:
45PM")、十进制数(例如0.78125表示6:
45PM)或其他公式或函数的结果(例如:
TIMEVALUE("6:
45PM"))。
说明
MicrosoftExcelforWindows和ExcelforMacintosh使用不同的默认日期系统。
时间值为日期值的一部分,并用十进制数来表示(例如12:
00PM可表示为0.5,因为此时是一天的一半)。
MINUTE
返回时间值中的分钟,为一个介于0到59之间的整数。
语法
MINUTE(serial_number)
Serial_number 表示一个时间值,其中包含要查找的分钟。
时间有多种输入方式:
带引号的文本字符串(例如"6:
45PM")、十进制数(例如0.78125表示6:
45PM)或其他公式或函数的结果(例如TIMEVALUE("6:
45PM"))。
说明
MicrosoftExcelforWindows和MicrosoftExcelfortheMacintosh使用不同的默认日期系统。
时间值为日期值的一部分,并用十进制数表示(例如12:
00PM可表示为0.5,因为此时是一天的一半)。
MONTH
返回以序列号表示的日期中的月份。
月份是介于1(一月)到12(十二月)之间的整数。
语法
MONTH(serial_number)
Serial_number 表示一个日期值,其中包含要查找的月份。
应使用DATE函数来输入日期,或者将日期作为其他公式或函数的结果输入。
例如,使用DATE(2008,5,23)输入2008年5月23日。
如果日期以文本的形式输入,则会出现问题。
说明
MicrosoftExcel可将日期存储为可用于计算的序列号。
默认情况下,1900年1月1日的序列号是1而2008年1月1日的序列号是39448,这是因为它距1900年1月1日有39448天。
MicrosoftExcelfortheMacintosh使用另外一个默认日期系统。
由YEAR、MONTH和DAY函数返回的值为公历值,而不用考虑所给日期值的显示格式。
例如,如果所给日期值的显示格式为Hijri,则YEAR、MONTH和DAY函数的返回值将是与等价的公历日期相关的值。
NOW
返回当前日期和时间所对应的序列号。
如果在输入函数前,单元格的格式为“常规”,则结果将设为日期格式。
语法
NOW()
说明
MicrosoftExcel可将日期存储为可用于计算的序列号。
默认情况下,1900年1月1日的序列号是1而2008年1月1日的序列号是39448,这是因为它距1900年1月1日有39448天。
MicrosoftExcelfortheMacintosh使用另外一个默认日期系统。
序列号中小数点右边的数字表示时间,左边的数字表示日期。
例如,序列号.5表示时间为中午12:
00。
函数NOW只有在重新计算工作表,或执行含有此函数的宏时改变。
它并不会随时更新。
SECOND
返回时间值的秒数。
返回的秒数为0到59之间的整数。
语法
SECOND(serial_number)
Serial_number 表示一个时间值,其中包含要查找的秒数。
时间有多种输入方式:
带引号的文本字符串(例如"6:
45PM")、十进制数(例如,0.78125表示6:
45PM)或其他公式或函数的结果(例如TIMEVALUE("6:
45PM"))。
说明
MicrosoftExcelforWindows和MicrosoftExcelforMacintosh使用不同的默认日期系统。
时间值为日期值的一部分,并用十进制数来表示(例如12:
00PM可表示为0.5,因为此时是一天的一半)。
TODAY
返回当前日期的序列号。
序列号是MicrosoftExcel日期和时间计算使用的日期-时间代码。
如果在输入函数前,单元格的格式为“常规”,则结果将设为日期格式。
语法
TODAY()
说明
MicrosoftExcel可将日期存储为可用于计算的序列号。
默认情况下,1900年1月1日的序列号是1而2008年1月1日的序列号是39448,这是因为它距1900年1月1日有39448天。
MicrosoftExcelfortheMacintosh使用另外一个默认日期系统。
WEEKDAY
返回某日期为星期几。
默认情况下,其值为1(星期天)到7(星期六)之间的整数。
语法:
WEEKDAY(serial_number,return_type)
Serial_number 表示一个顺序的序列号,代表要查找的那一天的日期。
应使用DATE函数输入日期,或者将函数作为其他公式或函数的结果输入。
例如,使用DATE(2008,5,23)输入2008年5月23日。
如果日期以文本的形式输入,则会出现问题。
Return_type 为确定返回值类型的数字。
Return_type
返回的数字
1或省略
数字1(星期日)到数字7(星期六),同MicrosoftExcel早期版本。
2
数字1(星期一)到数字7(星期日)。
3
数字0(星期一)到数字6(星期日)。
说明
MicrosoftExcel可将日期存储为可用于计算的序列号。
默认情况下,1900年1月1日的序列号是1,而2008年1月1日的序列号是39448,这是因为它距1900年1月1日有39448天。
MicrosoftExcelfortheMacintosh使用另外一个默认日期系统。
YEAR
返回某日期对应的年份。
返回值为1900到9999之间的整数。
语法
YEAR(serial_number)
Serial_number 为一个日期值,其中包含要查找年份的日期。
应使用DATE函数来输入日期,或者将日期作为其他公式或函数的结果输入。
例如,使用DATE(2008,5,23)输入2008年5月23日。
如果日期以文本的形式输入,则会出现问题。
说明
MicrosoftExcel可将日期存储为可用于计算的序列号。
默认情况下,1900年1月1日的序列号是1而2008年1月1日的序列号是39448,这是因为它距1900年1月1日有39448天。
MicrosoftExcelfortheMacintosh使用另外一个默认日期系统。
由YEAR、MONTH和DAY函数返回的值是Grego
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 应用 大全