第一章EXCEL高级应用

1.2公式与函数的高级应用17:0311.2.1公式的组成公式：以等号开始，由常量、单元格引用、函数和运算符组成。例如，假设A1单元格的内容为圆半径，则计算圆的周长的公式为：=2*PI()*A1利用公式完成例fl1-2-1公式组成.xlsx中的圆周和面积的计算常量单元格的引用17:032常量数值型，123文本型，“ABC”日期与时间型数据，2015-03-27逻辑型数据，TRUE,FALSE数组常量（请通过帮助了解）输入：(100)，1/2,0½,1.2E+12观察结果17:033单元格的引用目的：标识单元格或区域中的数据引用方法“A1”引用样式：列标行号“R1C1”引用样式：R行号C列标单元格名称引用相对引用、绝对引用和混合引用<F4>键切换17:034“A1”引用样式三维引用样式引用同一工作簿中多张工作表上的同一单元格或单元格区域中的数据三维引用的格式为：“工作表名称的范围！单元格或区域引用17:036“R1C1”引用样式单元格的名称引用17:038单元格或区域的命名名称的使用例：在fl1-1-1中，先对考试区域命名，再计算运算符17:039算术运算符+、-、*、/、%、^比较运算符=、>、<、>=、<=、<>文本连接运算符（&）引用运算符:,联合运算符，将多个引用合并为一个引用例如：SUM(B5:B15,D5:D15)空格交集运算符，生成一个对两个引用中共有单元格的引用例如：SUM(B7:D7C6:C8)17:0310运算符的优先级引用运算符（:、,、（空格））负数（-）百分比（%）乘方（^）乘和除（*、/）加和减（+、-）文本连接运算符（&）比较运算符（=、>、<、<=、>=、<>）17:0311函数预定义公式Excel内置函数用户创建的自定义函数17:0312数组公式17:0313同时执行多项计算并返回一个或多个结果对两组或多组数组参数运算数组参数的行列数应相同使用步骤选择结果区域输入数组公式Ctrl+Shift+Enter自动用{}将数组公式括起来数组公式举例在fl1-2-2职工工资表.xlsx的Sheet1中放着职工的基本工资和补贴，Sheet2中放着职工的奖金和调整幅度，请分别计算工资总计（含奖金和不含奖金），以及调整后的基本工资。17:0314数组公式使用注意事项不能直接输入{}不能单独更改数组公式中的某个单元格内容，否则系统报错若要删除，应整体选中数组公式所在单元格17:0315“R1C1”引用、名称引用举例在fl1-2-3商品信息.xlsx的Sheet1-Sheet4表格中，包含了几家商场商品的销售单价、数量、新增折扣店数量等信息，采用R1C1引用，利用数组公式计算销售总金额、特价商品平均价格，以及新增折扣店总数。17:0316使用数组公式的好处简洁性用传统公式多步，用数组公式一步一致性数组公式中每个单元格的计算方法都一致保证更高的准确性安全性不能更改数组公式的局部17:0317公式审核找出有问题的公式追踪引用单元格、追踪从属单元格例1-2-417:0318公式与函数中的常见问题17:0319公式求值单步执行公式，调试公式例fl1-1-4年龄.xlsx中公式的求值：=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,“男”,“女”),IF(MOD(MID(C2,17,1),2)=1,“男”,“女”))的计算公式审核-公式求值17:0320内置工作表函数数学和三角函数逻辑函数文本函数日期和时间函数统计函数财务函数查找和引用函数17:0321工程函数数据库函数信息函数用户自定义函数数学函数Rand()：产生0-1之间的随机数Round(number,num_digits）对number保留num_digits位小数Randbetween(bottom,top)产生bottom到top之间的随机数SQRT(number):number的平方根Subtotal(function_num,ref)Sumif(range,critiera,[sum_range])Sumifs(sum_range,range1,critiera1,range2,critiera2,…)17:0322分类汇总函数17:0323数学函数举例利用数学函数计算fl1-2-6学生信息表.xlsx中的数据身高（150.0---240.0，保留1位小数）成绩（0---100的整数）月消费（0.0---1000.0，保留1位小数）调整成绩（开根号乘以10，四舍五入到整数）用SUBTOTAL计算最长身高月消费<50的学生的总消费运动员的总月消费（身高>=200为运动员）调整后成绩及格的运动员的总月消费17:0324注意事项产生a-b的随机数函数公式Rand()*(b-a)+a将随机数固定下来编辑栏中输入=rand()后按<F9>键Sumif()与sumifs()的区别与用法17:0325三角函数PI()Sin()：返回给定角度的正弦值Cos()：返回给定角度的余弦值17:0326三角函数举例打开fl1-2-8正弦函数和余弦函数.xlsxA列中输入不同角度（0-360）B列中显示对应的正弦函数结果C列中显示对应的余弦函数结果绘制带直线和数据标记的散点图显示17:0327三角函数举例结果17:0328逻辑函数用于逻辑控制IFERROR(value,value_if_error)捕获和处理表达式中的错误计算正确，结果为value计算错误，结果为value_if_error错误类型17:0329逻辑函数举例为fl1-2-9学生成绩.xlsx的语文成绩添加等级初步调整千分考成绩调整为“开根号*3并取整”通过IFERROR()判断负分情况17:0330逻辑函数结果17:0331逻辑函数与数学、三角函数综合逻辑函数IF(),AND(),OR()数学和三角函数ROUND(),RAND(),SIN(),SQRT(),PI()EXP(number)：enumberLN(number)：ln(number)ABS(number)：number的绝对值17:0332例1-2-10计算fl1-2-10分段函数.xlsx中的y的值其中x取值为-10----10的随机实数结果保留2位小数请分别使用AND（）和OR（）函数计算17:0333文本函数——处理字符串LEFT(text,num):从左向右从文本中取num个字符RIGHT(text,num):从右向左取num个字符LEN(text)：文本中的字符数LENB(text)：文本中字符总共多少字节FIND(find_text,within_text)在within_text中找find_text，返回找到的字符在从左向右数第几个UPPER(text):全部转换成大写REPLACE(old_text,start_num,num,new_text)替换文本17:0334文本函数举例在Fl1-2-11供应商信息.xlsx中根据供应商联系方式，抽取姓名和电话根据Email，抽取用户名，并转换为大写抽取身份证号码中的后6位，作为登录密码将身份证号码的前3位510（四川）变更为320（江苏）17:0335日期与时间函数用于处理日期和时间Weekday(Serial_num,serial_type)Datedif(start_date,end_date,unit)17:0336例1-2-12职工加班出差信息为fl1-2-12中的职工技术加班工资加班工资按1小时计算，不足1小时的按1小时算，不足半小时的则忽略不计统计每位员工的加班时长时间相减×24得到判断加班的是星期几，并确定是否双休日Weekday(),IF()统计员工总加班时长Sumif(),数组公式17:0337例1-2-12职工加班出差信息为fl1-2-12中的职工技术加班工资统计员工双休日总加班时长Sumifs(),数组公式计算每位员工的加班工资一般加班工资+双休日加班工资统计每位员工出差总天数DATEDIF()计算出差补助总支出SUM(),数组公式17:0338例1-2-12结果17:0339公式审核检验带有单元格引用的公式是否有误17:0340公式审核公式-公式审核-追踪从属单元格17:03415.数据输入及表格规划合理布局表格数据，提高数据计算与分析效率17:0342常量区域计算和变化区域布局方式之二17:0343计算和变化区域常量区域1.1.3数据的输出与显示套用格式常规单元格格式设置自定义格式条件格式17:03441.自定义格式自定义格式的编码组成17:0345常用格式码及含义格式符功能及用法通用格式对未格式化的单元格使用默认格式，在列宽许可的情况下，尽可能地显示较高的精度，在列宽不许可的情况下，使用科学记数法显示数值#数字的占位符，不显示无效的0。如单元格的格式码为：#,###.##，则1233456.6789将被显示为1,233,456.68。0234.30将被显示为234.30作数字的占位符，如果数字的位数少于格式中的零，则显示无效的零。如单元格的格式码为：0,000.00，则1233456.6789将被显示为1,233,456.68。0234.30将被显示为0,234.30%把输入的数字乘以100，然后以百分数的形式显示出来"text"显示双引号内的文本。如设置自定义格式为："33981"0#，输入1将被显示为：3398101，输入22，将被显示为：3398122[颜色]用指定的颜色格式化单元格内容，如在定义了[红色]的格式的单元格中输入的内容将以红色显示[条件]对单元格进行条件格式化，允许的条件有：<，>，<=，>=，<>，=17:0346自定义格式的设置与应用请将fl1-1-10自定义格式.xlsx的表格最后一列“价格汇总”的数据格式化为“人民币#,##0.00元”的格式。17:03472.条件格式当条件满足时，格式出现区分不同数据或公式结果，当数据变化时，结果格式会自动变化17:0348fl1-1-11条件格式（1）将一季度中大于10的单元格设置为黄色填充效果；（2）将二季度中低于平均值的单元格图案颜色设置为红色，图案样式设置为25%灰色；（3）将三季度中重复值单元格设置为蓝色填充效果；（4）将合计列利用自动求和功能求得各个产品的年度销售合计，格式设置为蓝色数据条和三色交通灯图标集，并添加人民币符号、保留两位小数；（5）将G列数据设置为F列数字的中文大写。17:0349实验要求（1）掌握数值型、文本型、日期与时间型、以及逻辑型数据的输入和运算方法。（2