公式与函数
来自CloudWiki
公式
- 公式是Excel 2010 最重要的内容之一，充分灵活地运用公式，可以实现数据处理的自动化。公式对于那些需要填写计算结果的表格非常有用
- 公式以“ =”开始。例如，公式“ =A1+B2”中，“A1”和“B2”是单元格地址，“ + ”是运算符。
公式中的运算符类型
- Excel 2010 包含四类运算符：算术运算符、比较运算符、文本运算符和引用运算符。
算术运算符
- 算术运算符有 +（ 加号）、－（ 减号或负号）、 *（ 星号或乘号）、 /（ 除号）、%（百分号）、 ^（ 乘方）。完成基本的数学运算，返回值为数值。例如，
在单元格中输入“ =2+5 ^ 2”后回车，结果为27。
比较运算符
- 比较运算符有 =（ 等号）、 >（ 大于）、<（ 小于）、>=（ 大于等于）、 <=（ 小于等于）、<>（不等于）。用以实现两个值的比较，结果是一个逻辑值True 或False。例如：
在单元格中输入“ =3 < 8”，结果为True。
文本运算符
- 文本运算符为“&”，用来连接一个或多个文本数据以产生组合的文本。例如，
在单元格中输入“ ="职业"＆"学院"”（ 注意文本输入时须加英文引号）后回车，将产生“职业学院”的结果。
引用运算符
- 单元格引用运算符为“:”（ 冒号），用于合并多个单元格区域。例如，
B2:E2 表示引用 B2 到E2 之间的所有单元格。
- 联合运算符为“，”（ 逗号），将多个引用合并为一个引用。例如，
SUM(B5:C7，C5:D8) 求的是这两个区域所覆盖的数值分别求和后累加起来的总和，即重复的单元格需重复计算。
- 交叉运算符为空格，产生同时属于两个引用的单元格区域的引用。例如，
SUM(B5:C7,C5:D8) 求的是这两个区域的公共部分的数值总和。
公式中的运算顺序
- 公式中的运算符运算优先级为：
- （冒号）、空格、，（逗号） → ％（百分比） → ^（ 乘幂）→ *（ 乘）、 /（ 除）→ +（ 加）、 －（ 减）
→ &（ 连接符）→ = 、< 、 >、 <= 、 >= 、 <>（ 比较运算符）
输入和编辑公式
- 选择要在其中输入公式的单元格，先输入等号“ =”，然后再输入运算数和运算符。在输入公式时，一般都需要引用单元格数据。引用单元格数据有两种方法，第一种是直接输入单元格地址，第二种是利用鼠标选择单元格来填充单元格地址，最后按回车键确认。
如在成绩表的I2 单元格中，先输入“ = ”，然后输入“D2”或者单击D2，输入“+ ”号，再输入“E2”或单击E2，用同样的方法引用F2、G2、H2 单元格，直到公式“ =D2+E2+F2+G2+H2”输入完毕
相对引用和绝对引用
- Excel 单元格的引用方式有相对引用、绝对引用和混合引用。默认方式为相对引用。
相对引用
- 相对引用是指单元格地址会随公式所在位置的变化而改变，公式的值将会依据更改后的单元格地址的值重新计算。例如，在图4-29 中，在单元格I2 中输入“ =D2+E2+F2+G2+H2”
绝对引用
- 绝对引用是指公式中的单元格或单元格区域地址不随公式位置的改变而发生改变。不论公式的单元格处在什么位置，公式中所引用的单元格位置都是其在工作表中的确切位置。绝对引用的形式是在每一个列标及行号前加一个“$”符号，例如，输入公式“ =$D$2+$E$2+$F$2+$G$2+$H$2”。
混合引用
- 混合引用是指单元格或单元格区域的地址部分是相对引用，部分是绝对引用，如$B2、B$2。
三维地址引用
- 在Excel 中，不但可以引用同一工作表中的单元格，还能引用不同工作表中的单元格，引用格式为：[ 工作簿名 ]+ 工作表名!+ 单元格引用。例如，在工作簿Book1 中引用工作簿Book2 的Sheet1 工作表中的第3 行第5 列单元格，可表示为：[Book2]Sheet1!E3。
函数
函数的组成与分类
- 函数一般由函数名和参数组成。
- 函数名一般代表了函数的用途，如SUM 代表求和、AVERAGE 代表求平均、MAX 代表求最大值等。
- 参数根据函数计算功能的不同可以是数字、文本、逻辑值、数组、错误值或单元格引用。
- 函数可以有一个或多个参数，一般结构是：函数名（参数1，参数2，……）。
- Excel 2010 中的函数可分为数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数和用户自定义函数等十几大类函数。
函数的输入与使用
- 1）手工输入函数
若用户能够准确记住函数的名称及各参数的意义和使用方法，可直接在相应的单元格或编辑栏中输入函数。如统计C3:C17 区域内的男教师人数，可在C19 单元格内直接输入“5COUNTIF(C3:C17," 男 ")”，回车即可
- 2） 使用“插入函数”对话框
- 如果对插入到单元格中的函数不熟悉，则可通过“插入函数”对话框来插入函数。
- 3）出错信息
常用函数介绍
求和函数SUM
- 求和函数SUM 的语法格式为SUM ( number1,number2， … )，其中number1、number2、……为需要求和的参数。
求平均值函数AVERAGE
- 求平均值函数AVERAGE 的语法格式为AVERAGE( number1,number2,... )，其中number1、number2、 ……为要计算平均值的参数。
COUNT 函数
- COUNT 函数计算包含数字的单元格以及参数列表中数字的个数。使用函数COUNT 可以获取区域或数字数组中数字输入项的个数。
- 例如，若“ =COUNT(A1:A20)”的返回值为5，说明该区域中有5个单元格包含数字。
单条件求和函数SUMIF
- SUMIF 函数语法格式为SUMIF(range,criteria,[sum_range])，用于对区域中符合指定条件的值求和。其中range 参数必选，用于条件计算的单元格区域。criteria为筛选条件，sum_range为实际求和的范围。
- 以下表为例，求价格大于（包含等于）35的高端产品销售额之和
COUNTIF 函数
- COUNTIF 为统计区域中满足给定条件单元格个数的函数，其语法格式为COUNTIF （range，criteria），range 代表要统计的单元格区域，criteria 表示指定的条件表达式
- 例如，“ =COUNTIF ( B2:B11，" 男 " ) ”用于计算男生人数；公式“ =COUNTIF ( H2:H11，".80" ) ”用于计算平均分在80 分以上的人数。
排位函数RANK
- 排位函数RANK 的语法格式为RANK(number，ref，order)，其中number 为需要找到排位的数字，ref 为包含一组数字的数组或引用，order 为一数字，用来指明排位的方式。
- 如果order 为0或省略，则Excel 将ref 当作按降序排列的数据清单进行排位；如果order 不为零，Excel 将ref 当作按升序排列的数据清单进行排位。
- 例如，公式“ =RANK(G2，$G$2:$G$11)”用于计算G2 单元格中的数据在G2:G11 区域内的排名。
- 注意，这里排序的范围一定要写成绝对引用的方式。
MAX、MIN 函数
- MAX、MIN 分别用来求解数据集的极值（即最大值、最小值）。函数的用法非常简单，语法格式为函数（number1,number2,……）
AND、OR 函数
- AND、OR 函数语法格式为函数（logical1,logical2,……）。
对于AND 函数，所有参数的逻辑值为真时，返回True；只要一个参数的逻辑值为假，即返回False。对于OR 函数，所有参数的逻辑值为假时，返回False；只要一个参数的逻辑值为真，即返回True。
IF 函数
- 如果指定条件的计算结果为True，则IF 函数将返回某个值；如果该条件的计算结果为False，则返回另一个值。
- IF 函数语法格式为IF(logical_test,[value_if_true],[value_if_false])
取字符串子串函数LEFT、RIGHT、MID
- LEFT、RIGHT、MID 都是字符串提取函数。前两个格式是一样的，只是提取的方向相反。
- LEFT 是从左向右取，RIGHT 是从右向左取。
- LEFT 和RIGHT 函数语法格式为LEFT(text，num_chars)、RIGHT(text，num_chars)。
- 比如，“ =LEFT(A1，2)”是从A1 单元格的文本里，从左边第一位开始，向右提取两位。如果A1 是“山东高校”，则得到的结果是“山东”。
VLOOKUP 函数
- 使用VLOOKUP 函数搜索某个单元格区域的第一列，然后返回该区域相同行上任何单元格中的值。
- 其语法格式为VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
- 其中lookup_value 参数必选，表示要在表格或区域的第一列中搜索的值,
- table_array 参数必选，表示包含数据的单元格区域。
- col_index_num 参数必选，是table_array 参数中要返回的匹配值对应的列号。
- range_lookup 参数可选，是一个逻辑值，指定希望VLOOKUP 查找精确匹配值还是近似匹配值：如果range_lookup 为True 或被省略，则返回精确匹配值或近似匹配值。
