Excel函数运用

Excel是目前使用比较广泛的数据管理分析软件，其强大的内置函数几乎无所不能，但由于许多用户不知道函数的作用，不熟悉函数使用的格式，即使用错了，也不知错在哪里。Excel2002/2003帮助我们解决了许多这方面的问题，与Excel2000及以前的版本相比，其提供的智能公式功能将引领我们进入它的函数世界，轻松让你成为Excel的使用高手，从而可以更加方便快捷地满足我们日常办公的需要。1．常见公式用按钮在Excel中，我们经常遇到求和、求平均值、求最大/小值的问题。早期的Excel版本在工具栏中提供了“自动求和（∑）”按钮，通过它可以很方便地对单元格区域求和，但求平均值和最大/小值一般只能通过手工输入公式实现。在Excel2002/2003中，单击“自动求和”按钮右侧的下拉按钮，我们会发现多出了“平均值”、“计数”、“最大值”、“最小值”等命令，通过这些命令，我们可以很方便地求出所选单元格区域的平均值（average）、单元格个数（count）、最大值（max）和最小值(min)。将原本要用函数来计算的数据，现在通过工具栏按钮就轻松实现了（如图1）。图1“自动求和”下拉菜单2．插入函数帮你搜如果我们在数据分析时要用到某个功能，但不知用什么函数才能实现这一操作，我们可以借助Excel2002/2003中提供的“搜索函数”功能来实现。假设我们想了解与“贷款”有关的函数，只需单击“插入”菜单中的“函数”命令，弹出“插入函数”对话框，在“搜索函数”框中输入要搜索函数功能的关键字，单击“转到”按钮就可以看到Excel推荐你使用的函数了。3．应用格式看清楚对一些不常用的Excel函数，用户在使用时往往容易出现格式输入错误，这时Excel2002/2003会人性化地提醒你，从而确保公式输入的正确性。在图2中我们也可以看到，输入公式时，系统会将当前引用的单元格（或单元格区域）用蓝框选中，以便于用户检查输入时有没有引用错误。另外，在套用函数格式时（如“参数”），系统也会在函数的下方用粗体将当前参数标记出来，这样就一目了然了。图2检查格式输入错误4．提示信息查错误即使有了人性化的提醒，在输入公式时也难免会出现一些如“#NAME？”、“#VALUE！”等错误的提示，用户往往不知道错在哪儿，Excel2002/2003中我们会发现，在出错单元格的左侧（或右侧）多出一个带有错误警告提示的“！”号（如图3），单击其右侧的下拉按钮，我们就可通过系统帮助知道错在哪儿了。偶然˙˙﹒碰到一个问题，要求求出一组数中去掉5个最大值和5个最小值之后的平均值，于是仔细查找了一下excel函数，发现trimmean（）函数可以解决这个问题语法1.TRIMMEAN(array,percent)Array为需要进行整理并求平均值的数组或数值区域。Percent为计算时所要除去的数据点的比例，例如，如果percent=0.2，在20个数据点的集合中，就要除去4个数据点(20x0.2)：头部除去2，尾部除去2个。说明2-9如果percent0或percent1，函数TRIMMEAN返回错误值#NUM!。函数TRIMMEAN将除去的数据点数目向下舍入为最接近的2的倍数。如果percent=0.1，30个数据点的10%等于3个数据点。函数TRIMMEAN将对称地在数据集的头部和尾部各除去一个数据。另外percent值可以用分数表示，比如有30个数据，想去掉最大5个和最小5个，就可以用1/3表示。由此我想到如果前后要去掉的是不一样多的数该怎么办呢？比如在30个数中想去掉2个最高分，3个最低分，又摸索了一阵，发现可以这样解决：首先介绍一下两个函数：LARGE(array,k)Array为需要从中选择第k个最大值的数组或数据区域。K为返回值在数组或数据单元格区域中的位置（从大到小排）。SMALL(array,k)Array为需要找到第k个最小值的数组或数字型数据区域。K为返回的数据在数组或数据区域里的位置（从小到大）。说明：k也可以是一个数组，如large（array，{1，2，3}）表示取最大的三个数，注意一定要用大括号括起来。上面的问题就可以这样解决：=(SUM(A1:A30)-SUM(LARGE(A1:A30,{1,2}))-SUM(SMALL(A1:A30,{1,2，3})))/25（假设数据在A1:A30单元格内），如果有需要数据个数可以用count（）函数来表示。excel中的几个函数运用=countif(B1:B10,=80)-countif(B1:B10,90)假设B1:B10为你的数据区域.语法COUNTIF(range,criteria)Range为需要计算其中满足条件的单元格数目的单元格区域。Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式、单元格引用或文本。例如，条件可以表示为32、32、32、apples或B4。说明MicrosoftExcel还提供了其他一些函数，它们可根据条件来分析数据。若要计算基于一个文本字符串或某范围内的一个数值的总和，可使用SUMIF工作表函数。若要使公式返回两个基于条件的值之一，例如某指定销售金额的销售红利，可使用IF工作表函数。若要计算空或非空单元格的个数，可使用COUNTA和COUNTBLANK函数。示例1：通用COUNTIF公式如果您将示例复制到空白工作表中，可能会更易于理解该示例。AB1数据数据2apples323oranges544peaches755apples86公式说明（结果）=COUNTIF(A2:A5,apples)计算第一列中苹果所在单元格的个数(2)=COUNTIF(A2:A5,A4)计算第一列中桃所在单元格的个数(2)=COUNTIF(A2:A5,A3)+COUNTIF(A2:A5,A2)计算第一列中柑桔和苹果所在单元格的个数(3)=COUNTIF(B2:B5,55)计算第二列中值大于55的单元格个数(2)=COUNTIF(B2:B5,&B4)计算第二列中值不等于75的单元格个数(2)=COUNTIF(B2:B5,=32)-COUNTIF(B2:B5,85)计算第二列中值大于或等于32且小于或等于85的单元格个数(3)SUMIF函数函数名称：SUMIF主要功能：计算符合指定条件的单元格区域内的数值和。使用格式：SUMIF（Range，Criteria，Sum_Range）参数说明：Range代表条件判断的单元格区域；Criteria为指定条件表达式；Sum_Range代表需要计算的数值所在的单元格区域。应用举例：如图7所示，在D64单元格中输入公式：=SUMIF(C2:C63，男，D2:D63)，确认后即可求出“男”生的语文成绩和。特别提醒：如果把上述公式修改为：=SUMIF(C2:C63，女，D2:D63)，即可求出“女”生的语文成绩和；其中“男”和“女”由于是文本型的，需要放在英文状态下的双引号（男、女）中在Excel中，通过将两个日期值进行减运算，可以得到这两个日期相隔的天数；而将一个日期值与一个表示天数的整数相加减，则可以得到一个相隔该整数代表的天数的日期，这是Excel提供的虽然简单却相当实用的日期计算功能。然而，该功能在实际应用中的局限性，使其无法满足更高层次的需要，特别是“工作日”这一概念逐渐在各领域广泛应用，许多工作都需要进行与之相关的计算。最常见的譬如员工的出勤天数、交货期的计算、付款日的计算等等。本文介绍了Excel自带的用于计算工作日的NETWORKDAYS和WORKDAY函数，并指出了这两个函数在实际应用中所存在的问题，同时给出了用Excel自定义函数进行相关计算的实现。Excel自带的工作日计算函数Excel中自带的工作日计算函数在“分析工具库”中，要使用该类函数必须选择“加载宏”命令安装“分析工具库”。▲NETWORKDAYS函数NETWORKDAYS函数的用途是返回两个日期值之间完整的工作日数值，此工作日数值不包括周末（周六和周日）和用户专门指定的假期。1.语法形式NETWORKDAYS(start_date,end_date,holidays)2.参数说明start_date：开始日期。end_date：终止日期。holidays：表示不在工作日历中的一个或多个日期所构成的可选数据清单，如国家规定的法定假日以及其他非法定假日。此参数可以是包含日期的单元格区域，也可以是由代表日期的系列数所构成的数组常量。▲WORKDAY函数WORKDAY函数是用来计算某日期（起始日期）之前或之后相隔指定工作日数的某一日期的日期值。1.语法形式WORKDAY(start_date,days,holidays)2.参数说明start_date：开始日期。days：为start_date之前或之后不含周末及节假日的天数。days为正值将产生未来日期；为负值产生过去日期。holidays：为可选的数据清单，表示需要从工作日历中排除的日期值，如国家规定的法定假日或非法定假日。此参数可以是包含日期的单元格区域，也可以是由代表日期的系列数所构成的数组常量。