第2章 Excel公式与函数

第2章Excel公式与函数2.1Excel公式 2.2Excel函数2.3Excel公式与函数运用中的常见问题1

2.1Excel公式2.1.1Excel公式中的运算符2.1.2Excel公式中单元格的引用2.1.3Excel公式中地址的引用2单元格引用就是标识工作表上的单元格或单元格区域，指明公式中所用的数据的位置。在Excel中，可以引用同一工作表不同部分的数据，一工作簿不同工作表的数据，甚至不同工作簿的单元格数据。单元格或单元格区域引用一般式为：工作表名！单元格引用或[工作簿名]工作表名！单元格引用。2.1.2Excel公式中单元格的引用3

在“Exce项”对话框中，单击左侧列表中的“高级”选项，在“此工作表的显示选项”栏中，选中“在单元格中显示公式而非其计算结果”复选框，可以使单元格显示公式，而不是计算的结果。41．名称的语法规则（1）有效字符：名称的第一字符必须是字母、下划线或反斜杠（\）。名称中的其余字符可以是字母、数字、句点和下划线。（2）名称不能与单元格引用地址相同，如Z$100或R1C1。（3）不能使用空格：在名称中不允许使用空格，请使用下划线（_）和句点（.）作为单词分隔符。2.1.3名称的定义与运用5（4）名称长度限制：名称最多可以包含255个字符。（5）不区分大小写：名称可以包含大写字母和小写字母，但Excel在名称中不区分大写字母和小写字母。（6）唯一性：名称在其适用范围之内必须具备唯一性，不可重复。62．名称的适用范围

如果定义了一个名称（如Budget_FY08）且其适应范围为Sheet1，则该名称只能在Sheet1中被识别，如要在其他同一工作簿的工作表中使用该名称，必须加上工作表名称，如Sheet1!Budget_FY08。

如果定义了一个名称（如Sales_01）且适用范围是工作簿（即该Excel文件），则该名称对于该工作秒表中的所有工作表都是可识别的，但对于其他工作簿是不可识别的。73．为单元格或单元格区域定义名称（1）快速定义名称①选择要命名的单元格或单元格区域②单击“编辑栏”最左边的“名称”框。③在“名称”框中键入引用选定内容时要使用的名称。④按Enter键确认。8（2）将现有行和列标签转换为名称①选择要命名的区域，包括行或列标签。②在“公式”选项卡的“定义的名称”组中，单击“根据所选内容创建”按钮。③在弹出的“以选定区域创建名称”对话框中，通过选中“首行”、“左列”、“末行”、“右列”复选框来指定包含标签的位置，如图所示④单击“确定”按钮，完成名称的创建。通过该方式创建的名称仅引用相应标题下包含值的单元格，并且不包含现有行和列标题。9（3）使用“新名称”对话框定义名称①在“公式”选项卡的“定义的名称”组中，单击“定义名称”。②在“新建名称”对话框的“名称”框中，输入要用于引用的名称。③指定名称的适用范围：在“适用范围”下拉列表框中选择“工作簿”或工作簿中工作表的名称④根据需要，在备注栏中输入对该名称的说明性批注，最多255个字符。1011⑤在“引用位置”框中，执行下列操作之一：（a）要引用一个单元格，则鼠标单击“引用位置”框，然后在工作表中重新选择需要引用的单元格或单元格区域。（b）要引用一个常量，则输入＝（等号），然后输入常量值。（c）要引用公式，则输入＝（等号），然后输入公式。⑥单击“确定”按钮，完成命名并返回工作表。4．使用“名称管理器”管理名称使用“名称管理器”可以处理工作簿中所有已定义的名称和表名称。鼠标单击“公式”选项卡的“定义的名称”组中的“名称管理器”按钮，打开“名称管理器”对话框，如图所示。12（1）更改名称

如果更改某个已定义名称或表名称，则工作簿中该名称的所有实例也会随之更改。

打开“名称管理器”，在该对话框中单击要更改的名称，然后单击“编辑”按钮，打开“编辑名称”对话框，在该对话框中按照需要修改名称、引用位置、备注说明等，但适用范围不能更改，更改完成后单击“确定”按钮即可。13（2）删除名称

在“名称管理器”对话框中，选择要删除的名称，也可按住Shift并单击鼠标选择连续的几个名称或按住Ctrl并单击鼠标选择不连续的多个名称，单击“删除”按钮或按Delete键，再单击“确定”，确认删除。145．引用名称

名称可直接用来快速选定已命名的区域，也可在公式中引用名称以实现精确引用。（1）通过“名称框”引用

单击“名称框”右侧的黑色箭头，打开“名称”下拉列表，在其中显示所有已被命名的单元格名称，但不包括常量和公式的名称。

单击选择某一名称，该名称所引用的单元格或单元区域将公被选中，如果是在输入公式过程中，则该名称会出现在公式中。15（2）在公式中引用

单击要输入公式的单元格。

在“公式”选项卡的“定义的名称”组中，单击“用于公式”按钮，打开名称下拉列表。16

2.2Excel函数2.2.1统计函数2.2.2数学计算函数2.2.3文本处理函数2.2.4逻辑运算函数2.2.5关联匹配函数2.2.6日期与时间函数171．平均值函数平均值是表示一组数据集中趋势的量数，是反映数据集中趋势的一项指标。（1）算术平均值：功能是返回参数的平均值，语法格式为AVERAGE(number1,number2,...)。2.2.1统计函数18（2）几何平均值：功能是返回正数数组或数值区域的几何平均数，语法格式为GEOMEAN(number1,[number2],...)。（3）调和平均值：功能是返回一组正数的调和平均数，语法格式为HARMEAN(number1,[number2],...)。192．计数函数

在数据分析过程中，经常需要统计选定区域内数值型单元格的数目、空白单元格的数目、非空单元格的数目以及满足某条件的单元格的数目。（1）COUNT函数：功能是返回包含数字以及包含参数列表中的数字的单元格的个数；语法格式为COUNT(value1,value2,...)。（2）COUNTA函数：功能是计算范围中不为空的单元格的个数；语法格式为COUNTA(value1,[value2],...)。20（3）COUNTBLANK函数：功能是用于计算单元格区域中的空单元格的个数；语法格式为COUNTBLANK（range）。（4）COUNTIF函数：功能是计算区域中满足给定条件的单元格的个数；语法格式为COUNTIF(range,criteria)。213．其他统计函数（1）最大值函数：功能是返回一组值中的最大值；语法格式为MAX(number1,number2,...)。（2）最小值函数：功能是返回一组值中的最小值；语法格式为MIN(number1,number2,...)。（3）MEDIAN函数：功能是返回给定数值集合的中值.中值是在一组数据中居于中间的数,即在这组数据中，有一半的数据比它大,有一半的数据比它小；语法格式为MEDIAN(number1,number2,...)。22（4）Rank函数：功能是返回一个数字在数字列表中的排位。数字的排位是其大小与列表中其他值的比值（如果列表已排过序，则数字的排位就是它当前的位置）；语法格式为RANK(number,ref,order)。（5）Frequency函数：功能是以一列垂直数组返回一组数据的频率分布；语法格式为Frequency(data_array,bins_array)。231．求和函数

在数据分析过程中，有时需要对数值型数据进行求和或根据指定条件求和等。（1）SUM函数：功能是返回某一单元格区域中所有数字之和；语法格式为SUM(number1,number2,...)，其中，Number1,number2,...为1到30个需要求和的参数，可以是一个具体的数值、一个单元格或单元格区域。2.2.2数学计算函数24（2）SUMIF函数：功能是对范围中符合指定条件的数值求和；语法格式为SUMIF(range,criteria,sum_range)。（3）SUMIFS函数：功能是用于计算其满足多个条件的全部参数的总量；语法格式为SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)。（4）SUMPRODUCT函数：功能是在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和；SUMPRODUCT(array1,[array2],[array3],...)。252．其他数学函数（1）INT()函数：功能是将number数值向下舍入为最接近的整数；语法格式为INT(number)。（2）MOD函数：功能是返回两数相除的余数，结果的正负号与除数相同；语法格式为MOD(number,divisor)。261．字符串截取类函数（1）LEN()函数：功能是返回文本字符串中的字符数；语法格式为LEN(text)。（2）LENB()函数：功能是返回文本字符串中用于代表字符的字节数；语法格式为LENB(text)。（3）LEFT()函数：功能是LEFT基于所指定的字符数返回文本字符串中的第一个或前几个字符；语法格式为LEFT(text,num_chars)2.2.3文本处理函数27（4）RIGHT()函数：功能是根据所指定的字符数返回文本字符串中最后一个或多个字符）；语法格式为RIGHT(text,num_chars)。（5）MID()函数：功能是返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定；语法格式为MID(text,start_num,num_chars)。282．字符串查找替换函数（1）FIND()函数：功能是FIND用于查找其他文本字符串(within_text)内的文本字符串(find_text)，并从within_text的首字符开始返回find_text的起始位置编号；语法格式为FIND(find_text,within_text,start_num)。（2）REPLACE()函数：功能是使用其他文本字符串并根据所指定的字符数替换某文本字符串中的部分文本；语法格式为REPLACE(old_text,start_num,num_chars,new_text)。（3）SUBSTITUTE()函数：功能是在文本字符串中用new_text替代old_text；如果需要在某一文本字符串中替换指定的文本语法格式为SUBSTITUTE(text,old_text,new_text,instance_num)。293．文本转换类函数

TEXT函数的功能是根据指定的数值格式将数字转换为文本。其语法格式为TEXT(value,format_text)。301．IF类函数（1）IF()函数：功能是执行真假值判断，根据逻辑计算的真假值，返回不同结果；语法格式为IF(logical_test,value_if_true,value_if_false)。（2）IFNA()函数：功能是如果表达式解析为#N/A，则返回value_if_na指定的值，否则返回表达式的结果；语法格式为IFNA(value,value_if_na)。2.2.4逻辑运算函数31（3）IFERROR()函数：功能是如果表达式是一个错误，则返回value_if_error的值，否则返回表达式自身的值；语法格式为IFERROR(value,value_if_error)。322．IS类函数（1）ISBLANK(value)：检查是否引用了空单元格。（2）ISERR(value)：检查一个值是否为#N/A以外的错误。（3）ISERROR(value)：检查一个值是否为错误。（4）ISLOGICAL(value)：检查一个值是否是逻辑值。（5）ISNA(value)：检查一个值是否为#N/A。（6）ISNONTEXT(value)：检查一个值是否不是文本。33（7）ISNUMBER(value)：检查一个值是否是数值。（8）ISREF(value)：检查一个值是否为引用。（9）ISTEXT(value)：检查一个值是否为文本。（10）ISEVEN(number)：检查number值是偶数。（11）ISODD(number)：检查number值是奇数。343．逻辑判断类函数（1）AND()函数：功能是检查是否所有参数均为TRUE；语法格式为AND(logical1,logical2,……)。（2）OR()函数：功能是如果任一参数值为TRUE，即返回TRUE，只有当所有参数值均为FALSE时，才返回FALSE；语法格式为OR(logical1,logical2,……)。（3）NOT()函数：功能是对参数的逻辑值求反，参数为TRUE时返回FALSE，参数为FALSE时返回TRUE；语法格式为NOT(logical)。351．关联类函数（1）VLOOKUP()函数：功能是在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值；语法格式为VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)。2.2.5关联匹配函数36（2）LOOKUP()函数：；LOOKUP()函数有向量形式和数组形式两种使用方式，向量形式的语法格式为LOOKUP(Lookup_value,Lookup_vector,Result_vector)，功能是从单行或单列中查找一个值；数组形式的语法格式为LOOKUP(Lookup_value,Array)，功能是从数组中查找一个值。372．查询类函数（1）行列函数：包括COLUMN()函数和ROW()函数；COLUMN()函数的功能是返回指定单元格引用的列号，语法格式为COLUMN([Reference])；ROW()函数的功能是返回一个引用的行号，语法格式为ROW([Reference])。38（2）INDEX()函数：INDEX函数有数组形式和引用形式两种使用方式，数组形式语法格式为INDEX(Array,Row_num,Column_num)，功能是返回表格或数组中的元素值，此元素由行号和列号的索引值给定；引用形式语法格式为INDEX(Reference,Row_num,Column_num,Area_num)，功能是返回指定的行与列交叉处的单元格引用。如果引用由不连续的选定区域组成，可以选择某一选定区域。（3）MATCH()函数：功能是返回符合特定值特定顺序的项在数组中的相对位置，语法格式为MATCH(Lookup_value,Lookup_array,[Match_type])。391．计算天数函数

NETWORKDAYS()函数：功能是返回两个日期之间的完整工作日数，语法格式为NETWORKDAYS(Start_date,End_date,[Holidays])。2.2.6日期与时间函数402．年月日判断函数（1）TODAY()函数：功能是返回当前日期的序列号，TODAY无参数。（2）YEAR()函数：功能是返回对应于某个日期的年份，Year作为1900-9999之间的整数返回。（3）MONTH()函数：功能是返回日期（以序列数表示）中的月份。月份是介于1（一月）到12（十二月）之间的整数，语法格式为MONTH(Serial_number)。（4）DAY()函数：功能是返回以序列数表示的某日期的天数。天数是介于1到31之间的整数，语法格式为DAY(Serial_number)。41

2.3Excel公式与函数运用中的常见问题2.3.1常见的错误信息与处理方法2.3.2单元格的追踪2.1.3Excel公式中地址的引用422.3.1常见的错误信息与处理方法43错误常见原因处理方法#DIV/0!在公式中有除数为零，或者有除数为空白的单元格(Excel把空白单元格也当作0)。把除数改为非零的数值，或者用IF函数进行控制。#N/A在公式使用查找功能的函数(VLOOKUP、HLOOKUP、LOOKUP等)时，找不到匹配的值。检查被查找的值，使之的确存在于查找的数据表中的第一列。#NAME?在公式中使用了Excel无法识别的文本，例如函数的名称拼写错误，使用了没有被定义的区域或单元格名称，引用文本时没有加引号等。根据具体的公式，逐步分析出现该错误的可能，并加以改正。#NUM!当公式需要数字型参数时，却给了它一个非数字型参数；给了公式一个无效的参数；公式返回的值太大或者太小。根据公式的具体情况，逐一分析可能的原因并修正。44#VALUE