通信设计中的Excel 函数应用 信通院: 蔡嘉兴

课程大纲 使用公式 公式中的运算符 公式中的运算顺序 引用单元格 创建公式 创建公式的方法 创建简单公式 创建包含引用或名称的公式 创建包含函数的公式 使用函数 日期与时间函数 文本函数 逻辑函数 数学函数 查找与引用函数 统计函数

公式中的运算符 算术运算符 使用公式 算术运算符号 运算符含义 示 例 +(加号) 加 2+3=5 -(减号) 减 3-1=2 *(星号 乘 示 例 +(加号) 加 2+3=5 -(减号) 减 3-1=2 *(星号 乘 3*2=6 /(斜杠) 除 6/2=3 %(百分号) 百分号 50% ^(脱字号) 乘方 4^3=43=64

使用公式 公式中的运算符 2. 文本运算符 “&”号,可以将文本连接起来. 3. 比较运算符 比较运算符 运算符含义 示 例 =(等号) 3. 比较运算符 比较运算符 运算符含义 示 例 =(等号) 相等 B1=C1,若B1中单元格内的值确实与C1中的值相等,则产生逻辑真值TRUE,若不相等,则产生逻辑假值FALSE <(小于号) 小于 B1<C1 >(大于号) 大于 B1>C1,若B1中数值为6,C1中数值为4,则条件成立产生逻辑真值TRUE,否则产生逻辑假值FALSE >=(大于等于号) 大于等于 B1>=C1 <>(不等号) 不等于 B1<>C1 <=(小于等于号) 小于等于 B1<=C1

例:A4=B4+C4+D4+E4+F4,可写为: A4=SUM(B4:F4) 使用公式 公式中的运算符 4. 引用运算符 引用运算符 运算符含义 示 例 : 区域运算符,产生对包括在丙相引用之间的所有单元格的引用 (A5:A15) , 联合运算符,将多个引用合并为一个引用 SUM(A5:A15,C5:C15) (空格) 交叉运算符,产生对两个引用共有的单元格的引用 (B7:D7 C6:C8) 例:A4=B4+C4+D4+E4+F4,可写为: A4=SUM(B4:F4)

公式中出现不同类型的运算符混用时,运算次序是：引用运算符——算术运算符——文本运算符。如果需要改变次序,可将先要计算的部分括上圆括号。 使用公式 公式中的运算顺序 如果在公式中要同时使用多个运算符,则应该了解运算符的优先级.算术运算符的优先级是先乘幂运算,再乘、除运算,最后为加、减运算。相同优先级的运算符按从左到右的次序进行运算。 公式中出现不同类型的运算符混用时,运算次序是：引用运算符——算术运算符——文本运算符。如果需要改变次序,可将先要计算的部分括上圆括号。

相对引用 A1 绝对引用 $A$1 混合引用 $A1 或 A$1 三维引用 工作表名!单元格地址 引用单元格： 使用公式 引用单元格： 相对引用 A1 绝对引用 $A$1 混合引用 $A1 或 A$1 三维引用 工作表名!单元格地址 相对引用：行与列不固定，随着函数公式的拖动，会自动改变引用位置； 绝对引用：行与列固定，随着函数公式的拖动，不会改变引用的位置 混合引用：行固定或列固定，随着函数公式拖动，相对改变不固定的位置 $:给谁美金$，就能把谁冻住。

相对引用 行与列不固定 目标单元格与原单元格呈相对位置。

绝对引用 行与列固定 复制公式时，不会改变引用的位置，通过添加$

混合引用 行固定或列固定， 复制公式时，相对改变不固定的位置 相对列、绝对行 相对行、绝对列

三维引用 引用工作薄中其他工作表的数据 格式：”工作表名!单元格地址” 例如：Sheet1!A1 表一甲!B6:D8 三维引用直接在word中演示

公式是指对工作表中的数据进行分析和处理的一个等式,不仅用于算术计算,也用于逻辑运算、日期和时间的计算等。 一个公式必须具备3个条件： 创建公式 公式是指对工作表中的数据进行分析和处理的一个等式,不仅用于算术计算,也用于逻辑运算、日期和时间的计算等。 一个公式必须具备3个条件： 以等式开头; 2. 含有运算符和运算对象; 3. 能产生结果 公式的3个基本组成部分: 数值:包括数字和文本 单元格引用 操作符

创建公式的方法 创建3种类型的公式 创建公式 使用编辑栏 直接在单元格中输入 创建简单公式; 2. 创建包含引用或名称的公式 3. 创建包含函数的公式

使用函数 函数是一种特殊的公式,因此所有的函数要以“=”开始，可以直接输入函数。或单击fx（插入函数）按钮进入向导界面。 函数说明、实例可以在Excel帮助中查询。

函数是指预定义的内置公式,可以进行数学、文本和逻辑的运算或者查找工作表的信息。 函数是一个已经提供给用户的公式,并且有一个描述性的名称。 使用函数 函数是指预定义的内置公式,可以进行数学、文本和逻辑的运算或者查找工作表的信息。 函数是一个已经提供给用户的公式,并且有一个描述性的名称。 函数结构 函数名称：如果要查看可用函数的列表,可以单击一个单元格并按Shift+F3键(插入函数快捷键)； 参数：参数可以是数字、文本、逻辑值（TRUE或FALSE）、数值、错误值（如#N/A）或单元格引用。 函数的参数 函数的参数是一个函数体中括号内的部分 =SIN(4*A1+B1+30)

常用函数分类 财务函数 工程函数 多维数据集函数 信息函数 兼容性函数 日期与时间函数 文本函数 逻辑函数 数学函数 查找与引用函数 统计函数 财务函数 工程函数 多维数据集函数 信息函数 兼容性函数 一共有11种函数，此时可以打开Excel表，把公式栏中有的函数给大家做一个简单说明。excel函数共500多个，但常见的不过是30多个。

日期与时间函数 函数 名称 说明 输入 结果 例：A1=2003-7-29 year 年 拆分日期， 提取年份 =YEAR(A1) 2003 month 月 提取月份 =MONTH(A1) 7 day 日 提取日 =DAY(A1) 29 date 日期 用于合并日期 =DATE(2003,7,29) 2003-7-29 today 今天 提取系统日期 =TODAY() 2013-8-16 在有些情况下，我们需要把一个日期拆分成年、月、日3个数字，此时就要用到year、month、day这3个函数了。 Date的功能是将3个数字组成一个日期序列号。如果在设计表格时，把年、月、日3个数字分别保存在3个单元格中，若要把这3个数字组合成一个真正的日期，就需要使用date函数了。 案例： =today（） =year（today（）） =date（年，月，日） 例：A1=2003-7-29

小贴士 在单元格中插入当前日期和时间 插入静态的日期和时间 当前日期 选取一个单元格,并按“ Ctrl+; ” 当前时间      选取一个单元格,并按 “Ctrl+Shift+；” 当前日期和时间      选取一个单元格,并按 Ctrl+;,然后按空格键,最后按 Ctrl+Shift+; 插入会更新的日期和时间 使用 TODAY 和 NOW 函数完成这一工作。

文本函数 223 = abc 你我他 字符：字母、数字、文字和符号 中文字符（文字及标点）为2个字节， 其他字符皆为1个字节 字节：信息的最小计量单位 中文字符（文字及标点）为2个字节， 其他字符皆为1个字节 字符：字母、数字、字和符号

文本函数 ① 文本度量函数： 计算字符个数 =LEN（text）空格作为字符计数 计算字节个数 =LENB（text）

文本函数 ② 文本截取函数 =LEFT(维达纸业,2) 维达 =LEFTB(维达纸业,2) 维 =MID(维达纸业,3,2) 纸业 可省略，如省略则默认为1 如大于文本长度，则返回全部文本 ② 文本截取函数 =LEFT(维达纸业,2) 维达 =LEFTB(维达纸业,2) 维 =MID(维达纸业,3,2) 纸业 函数 说明 格式 LEFT 左取字符 LEFTB 左取字节 函数名（文本字符串，提取个数） RIGHT 右取字符 RIGHTB 右取字节 MID 中取字符 MIDB 中取字节 函数名（文本字符串，第一个字符位置，提取个数） 示例中： 取A公司：=LEFT(C2,3) =LEFTB(C2,5) 取财务部：=RIGHT(C2,3) =RIGHTB(C2,6)

逻辑函数 执行条件：IF 将条件按且方式结合：AND 将条件按或方式结合：OR

IF函数 =IF(B4>5,"优秀","") =IF(B2>5, "优秀","" )

IF函数 IF函数的嵌套：多级判断（可利用决策树） Q:根据员工工资，写出他们对应的工资级别

= IF ( 表达式,ture,IF(表达式,ture,IF(表达式,ture,false) ) ) 条件 工资级别 >=5000 1级 >=3000 2级 >=2000 3级 <2000 4级 = IF ( 表达式 , TRUE , FALSE ) = IF ( 表达式,ture,IF(表达式,ture,IF(表达式,ture,false) ) )

AND、OR函数 AND(条件1,条件2,…) =AND（ 5>3,2>1 ） = TRUE =AND（ 1>3,2>1 ） = FALSE OR(条件1,条件2,…) =OR（ 1>3,2>1 ） = TRUE =OR（ 1>3,2>5 ） = FALSE 使用and时，所有条件为true，则返回true，否则返回false。 使用or时， 其中之一条件为true，则返回true，否则返回false。 在and和or的日常使用中，我们一般不会去说判断这样的条件是 ture 还是 false，一般会把它们和其他函数组合来进行运用。

数学函数 求和：SUM 条件求和：SUMIF

求和：SUM SUM(数值1,数值2, …) =SUM(A2,B2,C2) =SUM(A2:C2)

条件求和：SUMIF SUMIF(条件区域,条件,求和区域) Q:求职务为文员的员工工资总和？ 条件区域 求和区域

查找与引用函数 VLOOKUP是一个查找函数，给定一个查找的目标，它就能从指定的查找区域中查找返回想要查找到的值。 找到目标的依据 包含目标值所在的区域 区域中目标值所在的是第几列 VLOOKUP（查询条件，查询区域，指定列位置，匹配模式） 作用：根据首列数据为依据进行查询返回相应列的结果 查询条件：在两个表格中相连接、也就是都有的数据。 查询区域：选中包含目标值所在的区域，查询条件必须在第一列。 匹配模式：默认精确查找 注意：1、查询区域为列结构；2、单条件查询；3、关键字唯一；4、查询区域不区分大小写；5、向右查询 精确(false/0)模糊(ture/1)

统计函数 条件计数： COUNTIF(计数区域，条件) Q：工资大于等于2000的人数？ 计数区域

平均值，最大、最小值 AVERAGE(数值1,数值2…) MAX(数值1,数值2,…) MIN(数值1,数值2,…) 注意： 只针对数值型，零值单元格会计算。 如单元格中有文字、逻辑值和空单元格时，忽略不计。

感谢聆听！