excel学习库-你早该这么学Excel函数，Excel公式教程，第二课《公式与函数》

excel公式与函数摘要：①简单公式计算；②表达式与地址引用；③常用函数及其应用；④误操作提示；⑤其它函数教学重点：简单公式计算、常用函数及其应用

员工工资表一、公式在Excel中，公式是在工作表中对数据进行分析和运算的等式，它是工作表的数据计算中不可缺少的部分。由运算对象和运算符组成的一个序列。 1、创建公式输入公式的操作类似于输入文字型数据。不同的是我们在输入一个公式的时候总是以一个等号“＝”为开始的标志。输入公式后，其结果显示在单元格内，并且在选定一个含有公式的单元格后，公式也就显示在编辑栏中。公式完全输入之后，按下键盘的Enter键，或者单击“公式栏”中的“输入”按钮“P”确定公式的创建。

创建公式2、公式的编辑 1) 编辑公式：创建公式后，对于包含公式的单元格来说，可以对其重新编辑，添加或减少公式中的数据元素，改变公式的算法等。编辑公式可以直接双击含有公式的单元格。 2) 复制公式在Excel中编辑好公式之后，如果在其它的单元格中需要编辑的公式与在此中编辑的公式相同，就可以使用Excel的复制功能了。复制公式的具体操作如下： A、选定己有公式的单元格，将鼠标移动到此单元格的右下角，此时鼠标指针变成黑色的加号 B、按住鼠标左键，松开鼠标。就这样将公式复制到新的单元格中。 C、注意：复制带有公式的单元格，只是将单元格的公式进行复制和粘贴，而不是粘贴结果。

复制公式3) 移动公式创建公式后，可以将它移动到其他单元格中。移动公式后，改变公式中元素的大小，此单元格的内容也会随着元素的而改变它自己的值。在移动的过程中，单元格中的绝对引用不会改变，而相对引用则会改变。 4) 删除公式如果要将单元格中的计算结果和公式一起删除，只需选定要删除的单元格，然后按下键盘的“Delete”键就可以了。如果只删除公式，而保留计算结果的操作步骤如下： A、选定要删除的公式的单元格；单击常用工具栏中的“复制”按钮； B、选择“编辑”菜单中的“选择性粘贴”命令，弹出“选择性粘贴”对话框，选中“数值” C、单击“确定”按钮，这样就只取消公式而不删除计算结果了。二、表达式与引用功能 1、运算符算术运算符：+、-、*、/、^(乘方)、% 优先级别：由高到低 ( ) → 函数 → ％ → ^ → *,/ → +,- 2、关系运算符：=、>、>=、<、<=、<> 3、引用功能一个引用位置代表工作表上的一个或者一组单元格，引用位置告诉Excel在哪些单元格中查找公式中要用的数值。我们也可以引用同一个工作簿上其他工作表中的单元格，或者引用其他工作簿，单元格引用位置基于工作表中的行号和列标。 1) 相对引用相对引用是在单元格中引用同一个工作簿上其他工作表中的单元格，或者引用其它工作簿，单元格引用位置基于工作表中的行号和列标。 2) 绝对引用绝对引用是在单元格中引用一个或多个特定位置的单元格，绝对引用的引用样式是在相对引用的列字母前与行数字前加一个美元符号“$”。绝对引用与相对引用在于，在复制公式时，绝对引用不发生改变。 3) 相对地址、绝对地址和混合地址：“A1”访问方式相对地址：B1 绝对地址：$B$1 混合地址：B$1, $B1 按F4键输入自动转换：B1→ $B$1→ B$1→ $B1。相对地址：复制时相对位置不变；绝对地址：复制时始终为固定地址；混合地址：相对地址和绝对地址

Excel公式与函数的使用三、函数利用Excel中的函数来进行公式计算可以大大提高工作效率，函数的构成与公式相似，分为函数本身和函数参数。Excel为我们提供了大量的函数。随Excel附带的预定义或内置共识，既可以作为独立公式单独使用，也可以作为参数用于其他函数。Excel2003中为用户提供了300多个函数（如数学与三角函数、统计函数、数据库函数、逻辑函数等）。函数有函数名和参数组成，格式为：函数名（参数1、参数2、……）。引用运算符—区域运算符：冒号(两个引用在内的所有单元格进行引用) 1、求和：SUM（参数1，参数2，…）例=SUM(A1：D1) 或= SUM(A1，B1，C1，D1) 或=A1+B1+C1+D1 选择工具：（提示：求和函数自带“=”号，不用再次输入“=”号） 2、求平均值：AVERAGE（参数1，参数2，…）例=AVERAGE(A1：D1) 或=AVERAGE(A1，B1，C1，D1) 或=(A1+B1+C1+D1)/4 3、求最大值：MAX（参数1，参数2，…） 4、求最小值：MAX（参数1，参数2，…） 5、条件函数：IF（逻辑表达式，表达式1，表达式2）功能：逻辑表达式为“真值”，结果为“表达式1”的值，否则为表达式2的值。例在C2单元格输入：＝IF(A2>B2，100，200) ＝IF(A2>=60，“及格”，“不及格”) ＝IF(A2>=60，true，false)

条件函数：IF6、排名：RANK（函数返回一个数值在一组数值中的排位。）语法：RANK(数字，包含一组数字的数组或引用，排位方式) 说明：排位方式为0或省略→降序排列；不为零→升序排列；数值重复时排位相同。括号里的内容：(A1，$A$1：$A$5，0) A1→要参与排名次的数字地址引用，填充公式时可变； $A$1：$A$5→数据堆的地址。$→表示绝对地址引用，使在填充公式时不会移位；0→降序排列。

排名：RANK

排名：RANK四、错误值 #DIV/0!：被除数为0 例如＝3/0 #N/A！：引用无法使用的数值例如函数参数使用错误 #NAME?：不能识别的名字例如＝sun(a1:a4) #NULL!：交集为空例如=sum(a1:a3 b1:b3) #NUM!：数据类型不正确例如 =sqrt(-4) #REF!：引用无效单元格例如引用的单元格被删除 #VALUE!：不正确的参数或运算符例如＝1+“a” ######## ：宽度不够,加宽即可。五、其它函数 1、数学与三角函数及其应用 1) 绝对值函数：ABS(数值型) 取｜数值型｜例ABS(5.8)= ABS(-4.3)= 2) 取整函数：INT(数值型) 取小于等于该数的最大整数例INT(3.6)= INT(-3.6)= 3) 截取函数：TRUNC(数值型参数[,小数位数]) 例TRUNC(3.6)= TRUNC (-3.6)= 4) 求余函数：MOD(被除数,除数)→余数例MOD(7,4)= MOD(-7,-4)= （异号：符号与除数相同，值是余数的“互补数”） 5) 正弦函数/余弦函数：SIN(X) COS(X) 其中X用弧度表示 6) 平方根函数：SQRT(X) 7) 随机函数：RAND() 产生0～1之间的随机数例1产生(0,10)之间的随机数 8) 对数函数：LN(X)、LOG(X,a)、LOG10(X) 9) 指数函数：EXP(X) → ex 10) 幂数函数：POWER(X，Y) → XY 2、统计函数及其应用 1) 中位数函数：MEDIAN(参数1，参数2，…) 例MEDIAN(4,3,6)＝ 2) 众数函数：MODE(参数1，参数2，…) 例MODE(3,5,5,2,3,5)＝ 3) 频数函数：FREQUENCY(数据区,分段点区) 功能：分段统计指定范围内数据出现的个数。*数据接收区间（分段数据）按升序公式结束按：Ctrl+Shift+Enter 例如统计不同年龄段的人数 FREQUENCY(数据区,分段点区) 4) 标准差函数：STDEV(参数1,参数2,…) 平均值：X=(X1＋X2＋…＋XN)/N 5) 线性趋势值函数：FORECAST(x,数据系列Y,数据系列X) 功能：对给定的x推导出Y。 3、辑函数及其应用 1) 逻辑“与”：AND(参数1，参数2……) 功能：所有的参数值为真，则结果为真，否则有一个参数值为假，结果为假。例AND(C3>=20,C3<=30) AND(D2＝“男”,C3<=20) 2) 逻辑“或”：OR(参数1，参数2……) 功能：有一个参数值为真，则结果为真，所有参数的值为假，结果为假。例OR(D2＝“本科”,D2=“大专”) OR(D2＝“男”,C3<=20) 3) 逻辑“非”：NOT(参数) 例NOT(F3) 4、数据库函数及其应用 1) 求和函数：DSUM(Database, Field,条件区) 2) 求平均值：DAVERAGE(Database, Field,条件区) 3) 求最大值：DMAX(Database, Field ,条件区) 4) 求最小值：DMIN(Database, Field,条件区) 5) 统计数值型数据的个数：DCOUNT(Database, Field,条件区) 6) 统计个数：DCOUNTA(Database, Field,条件区) 5、财务函数及应用 1) 偿还函数PMT(rate,nper,pv,fv,type) 2) 可贷款函数PV(rate, nper, pmt,fv,type) 3) 未来值函数FV(rate,nper,pmt,pv,type) 4) 投资总期数函数NPER(rate,pmt,pv,fv,type) 5) 年金各期利率函数RATE(nper,nper,pv,fv,type) 6、日期函数及其应用 1) DAY(日期) 返回日例DAY(2007/5/1)=1 2) MONTH(日期)返回月份例MONTH(2007/5/1)=5 3) YEAR(日期)返回年份例YEAR(2007/5/1)=2007 4) NOW() 返回当前系统日期和时间输入：=NOW() 5) TODAY()返回当前系统日期输入： =TODAY() 6) DATE(Y,M,D) 建立日期例DATE(2007,5,1)=2007/5/1 7、查找和引用函数及应用 1) 按列查找函数：VLOOKUP(查找值,数据区,列标,匹配类型) 功能：在“数据区”“首列”查找“查找值”，找到后返回所在行的指定“列标”处的值。匹配类型： (1)0或FALSE：精确比较（没找到“查找值”,返回错误值＃N/A！） (2)省略、1或TRUE：取近似值(没找到,返回小于“查找值”的最大值)。 2) 按行查找函数：HLOOKUP(查找值,数据区,行标,匹配类型) 略，同VLOOKUP 3) 数组转置：TRANSPOSE(数据区) (1)选定结果区 (2)输入＝ TRANSPOSE(要转置的数据区) (3)按：Ctrl+Shift+Enter 8、文本函数及其应用 1) 字符串长度(计算字符和汉字的个数) (1)LEN(字符串) 一个汉字=1个字符例LEN("计算机ABC")＝6 (2)LENB(字符串) 一个汉字=2个字符例 LENB("计算机ABC")＝9 2) 截取子串：MID(S,起始位置,N) 例 MID("对外经济贸易大学",5,4)="贸易大学" MIDB("对外经济贸易大学",5,4)="经济" 3) 取左子串 LEFT(S[,N]) 例 LEFT("对外经济贸易大学",4)="对外经济" 4) 取右左子串 RIGHT(S[,N]) 例 RIGHT("对外经济贸易大学",4)="贸易大学" 5) 删除首尾空格 TRIM(S) 例 B2=TRIM(A2) 6) 数值转文本 TEXT(数值型数据,格式) 例 TEXT(12.5,"$0.00")="$12.50" 7) 四舍五入转文本 FIXED(数值型数据[,n][,逻辑值]) 例FIXED(12.47,1)的结果是12.5 8) 文本转数值：VALUE(S) 例 VALUE("12.50") =12.5 9) 大小写转换：LOWER(S),UPPER(S) 例LOWER("aBC")="abc" UPPER("aBC")="ABC" 10) 替换：REPLACE(S1,m,n,S2) 例 REPLACE("abc",2,1,"xy")="axyc" 11) 精确比较:EXACT(S1,S2) 例EXACT("ab", "AB") 结果FALSE 例 "ab"="AB" 结果TRUE 12) 查找：FIND(sub,S,n) 例 FIND("ab","aBaba",1)=3 (区分大小写,不允许用通配符) 13) 搜索：SEARCH(sub,S,n) 例 SEARCH("ab", "aBaba",1)=1 (不区分大小写,允许用通配符) 14) 重复：REPT(S,n) 例 REPT("a",3)=aaa 9、统计个数：（1）统计数值型数据的个数：COUNT（参数1，参数2，…）（2）统计非空单元格的个数：COUNTA（参数1，参数2，…）（3）统计空单元格的个数：COUNTBLANK（参数1，参数2，…） 10、四舍五入函数：ROUND（参数，N）功能：四舍五入到第n位的近似值。=ROUND（825.746，2）=825.75 11、条件计数:COUNTIF(条件区域,条件) 功能：统计“条件区域”中满足“条件”的单元格的个数。条件求和:SUMIF(条件区域，条件，求和区域)。功能：求与“条件区域”中满足“条件”的单元格相对应的“求和区域”单元格数据的累加和。如果省略“求和区域”，对“条件区域”求和。作业1：差旅费报销单