第10周上课内容（3节）no.08 2010-11-15
excel高级应用（ⅰ）
一、教学目标。
1．正确使用“数学公式”；正确使用“数组运算”；搞清“单元格引用”概念。
2．掌握“数据透视图”和“数据透视表”的生成方法
3．掌握“文本函数”、“日期与时间函数”、“统计函数”、“逻辑函数”
4．掌握“查找与引用函数”、部分“财务函数”
二、基本理论。
1．公式概念对工作表的数值进行计算的式子称为公式。由操作符和运算符两个部分组成。操作符可以是常量、名称、数组、单元格引用和函数等。
运算符用于连接公式中的操作符，是工作表处理数据的指令。运算符有很多等等。
2．数组公式。
数组——就是单元格的集合或者是一组处理值的集合。
数组公式——能够执行多个输入操作、产生多个结果，并可将结果显示在某一个单元格区域的一个公式称为“数组公式”。
3．单元格引用。
定义：一个单元格对其它单元格的名称或单元格区域的名称进行的应用，叫单元格引用。单元格引用的目的是为了获得某种结果。引用分为以下类型：
相对引用——例如在e1单元格中输入：a1＋b1。总是以当前单元格的位置为基准。
绝对引用——例如在f1单元格中输入：$c$1: $c$28－$d$1: $d$28。
混合引用——例如在l1单元格中输入：（j$1＋j$2）（$l1:$l2）。
注意：按f4键可以使“相对引用”、“绝对引用”、“混合引用”三者进行转换。
4．数据筛选。
筛选是一种查找数据的快速方法，可将数据列表中不满足条件的记录暂时隐藏起来，只显示有用的数据行，以供用户浏览和分析。有两种筛选方式：
1）自动筛选
2）高级筛选。
5．数据透视表。
定义：数据透视表是通过对源数据表的行、列进行重新排列，提供多角度的数据汇总信息。用户可以旋转行和列以查看源数据的不同汇总，还可以根据需要显示感兴趣区域的明细数据。
要学好它，应掌握数据透视表的有关术语，如表1所示。
表1 数据透视表术语含义。
6．函数概念事先存放在函数库中用来执行计算、分析、判断等处理数据任务的特殊公式，就称为函数。例如“sum（）”if（）”rank（）”等等。一般情况下，函数都有“参数”，参数放在括号内。
7．函数类型 excel 2003的函数共有11类：
1）常用函数10个（包含在其它函数之中，不在总数中统计）
2）财务函数16个。
3）日期与时间函数………14个。
4）数学和三角函数………50个。
5）统计函数80个。
6）查找和引用函数………18个。
7）数据库函数12个。
8）文本函数34个。
9）逻辑函数6个。
10）信息函数16个。
11）户自定义函数………
总数 ＝ 246个（不含常用函数）＋ 用户自定义。
三、有关函数。
1. 逻辑函数。
用来判断事物的“真”与“假”、“有”与“无”、“是”与“非”之结果或者进行复合检验的excel函数，称为逻辑函数。在excel中共有六个逻辑函数，即and、or、not、if、true、false函数。
and（logical1，logical2…logical30） 结果为“ture”或“false”——与”函数。
or（logical1，logical2…logical30）结果为“ture”或“false” —或”函数。
not（logical）可对数字或表达式进行“ture”或“false”判断——“非”函数。
if（logical_test，value_if_ture，value_if_false）——条件”逻辑函数。
true、false函数——用来返回参数的逻辑值。可以直接在单元格或公式中键入true或者false。通常可以不用。但可以配合其它函数，作为其它函数的参数。
2. 统计函数（置于常用函数内）
rank（number，ref，order）——统计排名函数；有降序、升序排列之分。
rank— n. 排，等级；vt. 把…分等，排列）
其中：number为需要进行排名数组（区域）的第一个单元格名称。
ref（reference引用，参考；referee裁判）为需要进行排名数组的绝对引用区域；ref中的非数值型参数将被忽略。
order（顺序，命令）为一数字，0或非0（例如）。
若order＝0 或省略，rank将对ref按降序进行排名，即排名结果就是“顺数第几名”。
若order≠0，例如1，rank将对ref按升序进行排名。即排名结果就是“倒数第几名”。
注意：rank中，如“＝rank（f2，$f$2:$f$39）”，数据区域要用绝对引用符号“$”
countif（range，criteria）——条件统计函数。
range 范围，区域；criteria 条件，标准）
**erage（number1，number2，… number30）——求平均值统计函数。
注意：numberx表示一个数据“区域”或一个单元格“引用（名称）”
3．文本函数。
replace（old_text，start_num，num_chars，new_text）——文本替换、插入函数。
其中：old_text 要被替换字符的单元格引用（名称）；
start_num 要被替换的字符串的起始位置（字符串中第一个字符的起始位置为1）；
num_chars 要被替换的字符个数；【chars是′characters（字符）的缩写】
new_text 要替换的新字符。
注意：第三个参数num_chars＝0（或者为“空”）时，此时该函数就是“插入函数”。
可书写为“＝replace(b3,3, ,0)”
也可写为“＝replace(b3,3,0,0)”
表示要在第3个字符后不替换，而要插入一个“0”
mid（text，start_num，num_chars) —字符串提取函数。
midb（text，start_num，num_bytes) —字节提取函数。
其中：text 要从中提取字符串的字符串地址。
start_num 要提取字符串的开始位置（字符串的首位为1，其余类推）
num_chars 要提取的字符串的长度。
num_bytes 要提取的字节长度。
concatenate（text1，text2，..text30）——字符串连接函数。
其中：textxx 是要连接的字符串。
说明：concatenate的功能可用“&”实现。方法：“＝text1&text2& .text30”。
4．查找与引用函数。
vlookup（lookup_value，table_array，col_index_num，range_lookup）——列查找函数。v＝vertical垂直的，所以vlookup表示“垂直查找”。
其中：lookup_value 垂直方向要查找的值。
table_array 垂直方向要查找的矩阵（数组）。
col_index_num 要查找的内容在阵列中的列序号，例如“3”表示“第3列”。
col＝column（列），而不是指“山口”、“山坳”之类的意思。
range_lookup 是一个逻辑值（“0”或“1”，或者“false”或“true”）。
a）如果range_lookup＝true，表示要查找的数据事前必须“升序排列”，否则不能返回正确的结果；
b）如果range_lookup＝false，表示要查找的数据事前不必“升序排列”，而且可以返回精确的结果；
c）根据a）点，把“range_lookup”中的“rang”理解为“级别”、“等级”较妥。
hlookup（lookup_value，table_array，row_index_num，range_lookup)——行查找函数。h＝horizontal水平的。
其中：lookup_value 水平方向要查找的值。
table_array 水平方向要查找的矩阵（数组）。
row_index_num 要查找的内容在阵列中的行序号，例如“3”表示“第3行”。
range_lookup 是一个逻辑值（“0”或“1”，或者“false”或“true”）。
hlookup与vlookup完全相仿。只要把“行”和“列”加以区别即可。
5．财务函数。
sln（cost，salvage，life）——财务折旧函数。
其中：cost （费用）为资产原值。
salvage（残值）为资产在折旧期末的价值（也称为资产残值）。
life （寿命）为折旧期限（有时也称作资产的使用寿命）。
四、对命题的操作步骤。
例1】 给定excel文档“19 员工信息表。xls”，按要求完成各题。
1．在sheet4的a1单元格中设置为只能录入5位数字或文本。当录入位数错误时，提示错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本 xx张三”（xx是你学号末二位，张三指你姓名）。
操作：选中sheet4的a1单元格 → 数据 → 有效性 →“出错警告”选卡 →“样式”下选择“警告”→“出错信息”文本框中输入“只能录入5位数字或文本 xx张三”→【确定】。
2．使用replace函数，对sheet1中“员工信息表”的员工**进行升级。 要求：
升级方法：在pa后面加上0，例如pa125，修改后为pa0125；
将升级后的员工**结果填入表中的“升级员工**”列中。
操作：在sheet1的c3单元格中输入“＝replace(b3,3, ,0)”，或者。
Excel的综合练习
清远市电脑学校11级电脑班11 12学年度上学期期中考试实操题四。powerpoint部分，总分100分，时间45分钟，2011.11.28 电脑编号班级姓名座号得分 用powerpoint按下列要求和参考效果做一个趣味选择题，保存在桌面上，文件名为 座号 姓名 ppt期中考。ppt 完成后提交到主...
Excel练习
excel 电子 练习题 1 5页。1楼。1 完成歌手大奖赛成绩单。打开素材文件 素材并进行如下操作 1 在第一列进行001到010的序列填充。2 在b2单元填入日期型数据2005年5月4日。3 将工作表 sheet1 改名为 第一届 4 对工作表进行页面设置纸张大小为a4横向，缩放调整为1个页宽。...
EXCEL练习
我们将要对 e原始的 数据输入 表进行如下操作 把单元格b5中格式化成文本，输入数据 设备a 把单元格中b6格式化成百分数 保留二位小数 输入数据0.12 把单元格b7输入日期数据2002 2 25，单元格格式数字为 自定义，yyyy mm dd 答案 单击b5单元格 选中b5 单击菜单栏格式按钮 ...