实践操作 3 - excel2010电子表格(1)
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
23
操作准备——函数
语 法
SUM（num1，num2，…） AVERAGE（num1，num2，…） COUNT（num1，num2，…） MAX/ MIN（num1，num2，…） AND/OR（logical1，logical2，….） NOT（logical） SUMIF （ range ， criteria ， sum_range） COUNTIF（range,criteria） 计算一组数值的总和 计算一组数值的平均值 计算出某区域内数值的个数 计算一组数值的最大值或最小值 多个逻辑式是与（AND）或者是或（OR）的运算关系 逻辑非运算 计算符合指定条件的单元格和值 计算某区域内满足条件的数值个数。 Range ：为需要计算数 目的单元格区域。Criteria：为确定条件，其形式可以为数字、 表达式、单元格引用或文本等。 在指定区域中针对某一个数的最佳排名。Number 为需要排 名的数字，Ref 为包含一组数字的数组或引用，Ref 中的非数值 型参数将被忽略，Order 为一数字，指明排名的方式（升序1或 降序0）
返回指定位置截取指定个数长度的指定文本子串 返回指定文本包含字符的个数
REPLACE （ old_text ， start_num ， 返回用新文本串替换指定位置和指定个数的旧文本串 num_chars，new_text） 之后的文本 VLOOKUP（lookup_value, talbe_array, 可以在一个表格或指定的区域的最左列中查找某一指 col_index_num, range_lookup） 定的值，幵由此返回该值对应当前行中指定列的数值。 参数 lookup_value 是要在表格第一列中要搜索的值， 参数 table_array 是要搜索的数据表格， col_index_num 代 表 所 要 返 回 的 值 位 于 table_array 列 中 的 第 几 列 ， range_lookup是个逻辑值，如果其值为“TRUE”或被省 略 ， 则 返 回 部 分 符 合 的 数 值 ， 如 果 为 “FALSE”,VLOOKUP函数只会查找完全符合的数值。
17
操作准备
概述
数据输入
单元格编辑 格式设置 创建名称 公式概述
单元格的引用 函数 排序
筛选
汇总
图表分析
18
操作准备——单元格引用
• 单元格的引用
– 相对引用
– 绝对引用
– 混合引用
– 三维引用
– 循环引用
19
操作准备——单元格引用
• 相对引用
– 总是以当前单元格位置为基准，在复制公式时，当前单元 格改变了，单元格引用的地址也随之发生变化。 相对引用 的表示方法：直接写列字母和行号（如A1，D8等）。
DGET（database，field，criteria） DMAX（database，field，criteria） DSUM（database，field，criteria） DAVERAGE（database，field，criteria）
从列表或数据库的列中提取符合指定条件的单个值。 返回列表或数据库的列中满足条件的最大值。
DATE（year，month，day）
YEAR（date）、MONTH（date）、DAY（date）
返回由给定的年、月、日数值组成的日期
返回对应日期的数值型年份、月份和日数
HOUR （ time ） 、 MINUTE （ time ） 、 SECOND （time）
ABS（number）
返回对应时间的数值型小时、分钟和秒数
• 每个运算符都有一个优先级。按照从高到低的优先级顺
序迚行计算。对于同一优先级的运算，按照从左到右的 书讯迚行计算。
16
操作准备——公式概述
– 算术运算符： • 加（+），减（-），乘（*），除（/），百分号（%）， 乘方（^）； – 比较运算符： • =，>，<， >=，<=，<>（不等于）； – 文字运算符： • &（连接字符）； – 引用运算符： • 冒号“：”（连续区域引用）、逗号“，”（离散区域 引用）、空格“ ”（交叉区域引用）、感叹号“！” （三维区域引用）。
概述
数据输入
单元格编辑 格式设置 创建名称 公式概述
单元格的引用 函数 排序
筛选
汇总
图表分析
15
操作准备——公式概述
• 公式的定义：对工作表中的数值迚行计算的式子。 • 公式的组成：由操作符和运算符组成。
– 操作符可以是常量、名称、数组、单元格引用和函数等。 – 运算符用于连接公式中的操作符，是处理数据的指令。 • 算术运算符 、逻辑运算符 、文本运算符 、引用运算符
Excel大作业要求
6. 使用条件格式功能标示出“单项分值均超过单项平均分”的学生。
7. 挑选部分学生的成绩进行图表分析比较，并将图表存放在单独的工作表中。 8. 生成排名前二十的各个专业的人数分布情况饼图，并将图表存放在单独的工 作表中。 9. 输出排名在前十名的学生成绩信息和不及格学生的名单，要求使用自动套用 格式功能。
• 三维引用
– 引用工作簿中其它工作表的单元格 。 – 三维引用的一般格式为：工作表标签！单元格引用，如： 进货清单！$B$2 : $B$。
21
操作准备
概述
数据输入
单元格编辑 格式设置 创建名称 公式概述
单元格的引用 函数 排序
筛选
汇总
图表分析
22
操作准备——函数
6
操作准备——数据输入
• 自定义序列与填充柄
– 自定义序列：一组数据，可按重复方式填充列。 – 创建自定义序列的方法
• 文件选项高级常规编辑自定义列表
• 利用工作表中现有的数据项 • 采用临时输入的方式
– 创建好自定义序列之后，采用填充柄进行填充能 达到快速、有效的结果。
7
操作准备——数据输入
• 如果公式中要使用函数，可以直接输入函数名和参数， 也可以通过以下方式编辑函数：
– “公式”选项卡“函数库”组，单击组中的某类函数选 项，在其下拉列表中选择所需的函数，通过“函数参数” 对话框，编辑参数。 – “公式”选项卡“函数库”组，单击“自动求和” 选项， 可以选择求和、平均值、计数、最大值、最小值以及其他 函数的计算。 – “公式”选项卡“函数库”组，单击“插入函数” 选项， 可以打开“插入函数”对话框，同样可以实现插入和编辑 函数。
26
操作准备——函数
DCOUNT（database，field，criteria）
返回列表或数据库中满足指定条件的记录字段（列） 中包含数值的单元格的个数。 database：构成数据清单或数据库的单元格区域 ； field：指定函数所使用的数据列； criteria：一组包含给定条件的单元格区域。
操作准备
概述
数据输入
单元格编辑 格式设置 创建名称 公式概述
单元格的引用 函数 排序
筛选
汇总
图表分析
5
操作准备——数据输入
• 通过自定义填充序列和数据有效性下拉列表， 可以提高数据输入的速度和准确性。
• 自定义下拉列表输入
– 建立方法
数据数据有效性“数据有效性”允许“序列” 普通方法设置：直接键入列表选项 使用名称设置：使用名称建立列表选项
1. 建立包含学生相关信息的数据表格信息，输入符合统计汇总要求的相应基础 数据（学生人数不得少于20人，课程不得少于4门，专业不得少于5个）。 2. 确保信息输入的有效性和界面友好型（专业由下拉菜单选择输入）。 3. 学生的各门课程的成绩来源：平时成绩、期中成绩和期末成绩，总评成绩= 平时*20%+期中*30%+期末*50%。 4. 将学生各门课程的最后得分汇总到一个工作表（要求用vlookup函数实现）， 并计算各门课程的单项平均分，每个学生的多项平均分以及排名情况。 5. 利用函数统计学生的各门课程平均分超过85分的人数。
– 在Excel中，可以通过一个名称来代表工作表、单元格、常 量、图表或公式等。
– 创建方法：
公式定义名称定义名称 – 定义了一个名称之后，就可以在公式中直接使用它。
13
操作准备——创建名称
• 创建名称及其使用
– 定义了一个名称之后，就可以在公式中直接使用它。
14
操作准备
百度文库
返回圆周率π的值
返回e的幂次值 返回指定整数的阶乘值 返回指定底数的整数对数值/自然对数值 返回数值的平方根值
25 返回指定小数位数的四舍五入数值
操作准备——函数
LEFT（text，num_chars） RIGHT（text，num_chars） 返回从左边/右边截取指定个数长度的指定文本子串
MID（text，start_num，num_chars） LEN（text）
• 套用样式
– 可以套用系统内置样式，快速地装饰选中区域的单元格的 边框、底纹和字体等格式。
10
操作准备——格式设置
• 条件格式
11
操作准备
概述
数据输入
单元格编辑 格式设置 创建名称 公式概述
单元格的引用 函数 排序
筛选
汇总
图表分析
12
操作准备——创建名称
• 创建名称及其使用
大学计算机基础
实践操作环节 excel2010电子表格
Excel大作业要求
• 以小组为单位，自选题材完成“***管理”项目。
模仿实践指导书的操作案例，实现一个“学生成绩管理”
或“职工工资管理”项目。项目只提出必须实现的功能和效果，
但具体实现的方法和过程可以自由选择和发挥。
• “学生成绩管理”项目
作 用
IF （ logical_test ， value1 ， value2 ） 逻辑表达式为真，返回value1的值，否则返回value2的值。
RANK (number, ref, order) RANK.EQ (number, ref, order)
24
操作准备——函数
TODAY（）/NOW（） 返回系统当前日期或日期时间
Excel大作业要求
• “职工工资管理”项目：
1. 建立包含职工相关信息的数据表格信息，输入符合统计汇总要求的相应基础
数据（职工人数不得少于20人，部门不得少于5个）。 2. 确保信息输入的有效性和界面友好型（部门由下拉菜单选择输入）。 3. 职工的工资来源：基本工资、职务津贴、绩效津贴、工龄津贴和纳税扣除等， 纳税扣除=（基本工资+职务津贴+绩效津贴+工龄津贴）*20%。 4. 计算职工的实发工资，并统计各项的平均值。 5. 按实发工资升序排列，并将实发工资最低的前十人信息的字体用蓝色表示。 6. 输出低工资（2000元以下的）职工名单，要求使用自动套用格式功能。 7. 统计按部门分类的工资汇总情况，生成各个部分的工资分布情况饼图，并将 图表存放在单独的工作表中。
• 自定义序列与填充柄
操作准备
概述
数据输入
单元格编辑 格式设置 创建名称 公式概述
单元格的引用 函数 排序
筛选
汇总
图表分析
9
操作准备——格式设置
• 条件格式
– 作用：通过设置数据条件格式，可以让单元格中的数据满 足指定条件时就以特殊的标记（如：以红色、数据条、图 标等）显示出来。 – 设置方法： • 开始样式条件格式新建（管理）规则
• 绝对引用
– 在复制公式时，不想改变公式中的某些数据，即所引用的 单元格地址在工作表中的位置固定不变，它的位置与包含 公式的单元格无关 。绝对引用的表示方法：在相应的单元 格地址的列字母和行号前加“$”符号 （如$A$1）。
20
操作准备——单元格引用
• 混合引用
– 公式中参数的行采用相对引用、列采用绝对引用，或者列 采用相对引用、行采用绝对引用，如：$A1、A$1 。
返回指定数值的绝对值
MOD（num1，num2）
INT（number） SIN（number）/COS（number）
计算两个整数相除的余数
截尾取不超过这个数的最大整数 返回指定弧度角度的正弦值/余弦值
PI（）
EXP（number） FACT（number） LOG（number，base）/LN（number） SQRT（number） ROUND（number，num_digits）
返回列表或数据库中满足指定条件的记录字段（列） 中的数字之和。 计算列表或数据库的列中满足指定条件的数值的平 均值。
27
操作准备——函数
操作准备——函数
语 法
SUM（num1，num2，…） AVERAGE（num1，num2，…） COUNT（num1，num2，…） MAX/ MIN（num1，num2，…） AND/OR（logical1，logical2，….） NOT（logical） SUMIF （ range ， criteria ， sum_range） COUNTIF（range,criteria） 计算一组数值的总和 计算一组数值的平均值 计算出某区域内数值的个数 计算一组数值的最大值或最小值 多个逻辑式是与（AND）或者是或（OR）的运算关系 逻辑非运算 计算符合指定条件的单元格和值 计算某区域内满足条件的数值个数。 Range ：为需要计算数 目的单元格区域。Criteria：为确定条件，其形式可以为数字、 表达式、单元格引用或文本等。 在指定区域中针对某一个数的最佳排名。Number 为需要排 名的数字，Ref 为包含一组数字的数组或引用，Ref 中的非数值 型参数将被忽略，Order 为一数字，指明排名的方式（升序1或 降序0）
返回指定位置截取指定个数长度的指定文本子串 返回指定文本包含字符的个数
REPLACE （ old_text ， start_num ， 返回用新文本串替换指定位置和指定个数的旧文本串 num_chars，new_text） 之后的文本 VLOOKUP（lookup_value, talbe_array, 可以在一个表格或指定的区域的最左列中查找某一指 col_index_num, range_lookup） 定的值，幵由此返回该值对应当前行中指定列的数值。 参数 lookup_value 是要在表格第一列中要搜索的值， 参数 table_array 是要搜索的数据表格， col_index_num 代 表 所 要 返 回 的 值 位 于 table_array 列 中 的 第 几 列 ， range_lookup是个逻辑值，如果其值为“TRUE”或被省 略 ， 则 返 回 部 分 符 合 的 数 值 ， 如 果 为 “FALSE”,VLOOKUP函数只会查找完全符合的数值。
17
操作准备
概述
数据输入
单元格编辑 格式设置 创建名称 公式概述
单元格的引用 函数 排序
筛选
汇总
图表分析
18
操作准备——单元格引用
• 单元格的引用
– 相对引用
– 绝对引用
– 混合引用
– 三维引用
– 循环引用
19
操作准备——单元格引用
• 相对引用
– 总是以当前单元格位置为基准，在复制公式时，当前单元 格改变了，单元格引用的地址也随之发生变化。 相对引用 的表示方法：直接写列字母和行号（如A1，D8等）。
DGET（database，field，criteria） DMAX（database，field，criteria） DSUM（database，field，criteria） DAVERAGE（database，field，criteria）
从列表或数据库的列中提取符合指定条件的单个值。 返回列表或数据库的列中满足条件的最大值。
DATE（year，month，day）
YEAR（date）、MONTH（date）、DAY（date）
返回由给定的年、月、日数值组成的日期
返回对应日期的数值型年份、月份和日数
HOUR （ time ） 、 MINUTE （ time ） 、 SECOND （time）
ABS（number）
返回对应时间的数值型小时、分钟和秒数
• 每个运算符都有一个优先级。按照从高到低的优先级顺
序迚行计算。对于同一优先级的运算，按照从左到右的 书讯迚行计算。
16
操作准备——公式概述
– 算术运算符： • 加（+），减（-），乘（*），除（/），百分号（%）， 乘方（^）； – 比较运算符： • =，>，<， >=，<=，<>（不等于）； – 文字运算符： • &（连接字符）； – 引用运算符： • 冒号“：”（连续区域引用）、逗号“，”（离散区域 引用）、空格“ ”（交叉区域引用）、感叹号“！” （三维区域引用）。
概述
数据输入
单元格编辑 格式设置 创建名称 公式概述
单元格的引用 函数 排序
筛选
汇总
图表分析
15
操作准备——公式概述
• 公式的定义：对工作表中的数值迚行计算的式子。 • 公式的组成：由操作符和运算符组成。
– 操作符可以是常量、名称、数组、单元格引用和函数等。 – 运算符用于连接公式中的操作符，是处理数据的指令。 • 算术运算符 、逻辑运算符 、文本运算符 、引用运算符
Excel大作业要求
6. 使用条件格式功能标示出“单项分值均超过单项平均分”的学生。
7. 挑选部分学生的成绩进行图表分析比较，并将图表存放在单独的工作表中。 8. 生成排名前二十的各个专业的人数分布情况饼图，并将图表存放在单独的工 作表中。 9. 输出排名在前十名的学生成绩信息和不及格学生的名单，要求使用自动套用 格式功能。
• 三维引用
– 引用工作簿中其它工作表的单元格 。 – 三维引用的一般格式为：工作表标签！单元格引用，如： 进货清单！$B$2 : $B$。
21
操作准备
概述
数据输入
单元格编辑 格式设置 创建名称 公式概述
单元格的引用 函数 排序
筛选
汇总
图表分析
22
操作准备——函数
6
操作准备——数据输入
• 自定义序列与填充柄
– 自定义序列：一组数据，可按重复方式填充列。 – 创建自定义序列的方法
• 文件选项高级常规编辑自定义列表
• 利用工作表中现有的数据项 • 采用临时输入的方式
– 创建好自定义序列之后，采用填充柄进行填充能 达到快速、有效的结果。
7
操作准备——数据输入
• 如果公式中要使用函数，可以直接输入函数名和参数， 也可以通过以下方式编辑函数：
– “公式”选项卡“函数库”组，单击组中的某类函数选 项，在其下拉列表中选择所需的函数，通过“函数参数” 对话框，编辑参数。 – “公式”选项卡“函数库”组，单击“自动求和” 选项， 可以选择求和、平均值、计数、最大值、最小值以及其他 函数的计算。 – “公式”选项卡“函数库”组，单击“插入函数” 选项， 可以打开“插入函数”对话框，同样可以实现插入和编辑 函数。
26
操作准备——函数
DCOUNT（database，field，criteria）
返回列表或数据库中满足指定条件的记录字段（列） 中包含数值的单元格的个数。 database：构成数据清单或数据库的单元格区域 ； field：指定函数所使用的数据列； criteria：一组包含给定条件的单元格区域。
操作准备
概述
数据输入
单元格编辑 格式设置 创建名称 公式概述
单元格的引用 函数 排序
筛选
汇总
图表分析
5
操作准备——数据输入
• 通过自定义填充序列和数据有效性下拉列表， 可以提高数据输入的速度和准确性。
• 自定义下拉列表输入
– 建立方法
数据数据有效性“数据有效性”允许“序列” 普通方法设置：直接键入列表选项 使用名称设置：使用名称建立列表选项
1. 建立包含学生相关信息的数据表格信息，输入符合统计汇总要求的相应基础 数据（学生人数不得少于20人，课程不得少于4门，专业不得少于5个）。 2. 确保信息输入的有效性和界面友好型（专业由下拉菜单选择输入）。 3. 学生的各门课程的成绩来源：平时成绩、期中成绩和期末成绩，总评成绩= 平时*20%+期中*30%+期末*50%。 4. 将学生各门课程的最后得分汇总到一个工作表（要求用vlookup函数实现）， 并计算各门课程的单项平均分，每个学生的多项平均分以及排名情况。 5. 利用函数统计学生的各门课程平均分超过85分的人数。
– 在Excel中，可以通过一个名称来代表工作表、单元格、常 量、图表或公式等。
– 创建方法：
公式定义名称定义名称 – 定义了一个名称之后，就可以在公式中直接使用它。
13
操作准备——创建名称
• 创建名称及其使用
– 定义了一个名称之后，就可以在公式中直接使用它。
14
操作准备
百度文库
返回圆周率π的值
返回e的幂次值 返回指定整数的阶乘值 返回指定底数的整数对数值/自然对数值 返回数值的平方根值
25 返回指定小数位数的四舍五入数值
操作准备——函数
LEFT（text，num_chars） RIGHT（text，num_chars） 返回从左边/右边截取指定个数长度的指定文本子串
MID（text，start_num，num_chars） LEN（text）
• 套用样式
– 可以套用系统内置样式，快速地装饰选中区域的单元格的 边框、底纹和字体等格式。
10
操作准备——格式设置
• 条件格式
11
操作准备
概述
数据输入
单元格编辑 格式设置 创建名称 公式概述
单元格的引用 函数 排序
筛选
汇总
图表分析
12
操作准备——创建名称
• 创建名称及其使用
大学计算机基础
实践操作环节 excel2010电子表格
Excel大作业要求
• 以小组为单位，自选题材完成“***管理”项目。
模仿实践指导书的操作案例，实现一个“学生成绩管理”
或“职工工资管理”项目。项目只提出必须实现的功能和效果，
但具体实现的方法和过程可以自由选择和发挥。
• “学生成绩管理”项目
作 用
IF （ logical_test ， value1 ， value2 ） 逻辑表达式为真，返回value1的值，否则返回value2的值。
RANK (number, ref, order) RANK.EQ (number, ref, order)
24
操作准备——函数
TODAY（）/NOW（） 返回系统当前日期或日期时间
Excel大作业要求
• “职工工资管理”项目：
1. 建立包含职工相关信息的数据表格信息，输入符合统计汇总要求的相应基础
数据（职工人数不得少于20人，部门不得少于5个）。 2. 确保信息输入的有效性和界面友好型（部门由下拉菜单选择输入）。 3. 职工的工资来源：基本工资、职务津贴、绩效津贴、工龄津贴和纳税扣除等， 纳税扣除=（基本工资+职务津贴+绩效津贴+工龄津贴）*20%。 4. 计算职工的实发工资，并统计各项的平均值。 5. 按实发工资升序排列，并将实发工资最低的前十人信息的字体用蓝色表示。 6. 输出低工资（2000元以下的）职工名单，要求使用自动套用格式功能。 7. 统计按部门分类的工资汇总情况，生成各个部分的工资分布情况饼图，并将 图表存放在单独的工作表中。
• 自定义序列与填充柄
操作准备
概述
数据输入
单元格编辑 格式设置 创建名称 公式概述
单元格的引用 函数 排序
筛选
汇总
图表分析
9
操作准备——格式设置
• 条件格式
– 作用：通过设置数据条件格式，可以让单元格中的数据满 足指定条件时就以特殊的标记（如：以红色、数据条、图 标等）显示出来。 – 设置方法： • 开始样式条件格式新建（管理）规则
• 绝对引用
– 在复制公式时，不想改变公式中的某些数据，即所引用的 单元格地址在工作表中的位置固定不变，它的位置与包含 公式的单元格无关 。绝对引用的表示方法：在相应的单元 格地址的列字母和行号前加“$”符号 （如$A$1）。
20
操作准备——单元格引用
• 混合引用
– 公式中参数的行采用相对引用、列采用绝对引用，或者列 采用相对引用、行采用绝对引用，如：$A1、A$1 。
返回指定数值的绝对值
MOD（num1，num2）
INT（number） SIN（number）/COS（number）
计算两个整数相除的余数
截尾取不超过这个数的最大整数 返回指定弧度角度的正弦值/余弦值
PI（）
EXP（number） FACT（number） LOG（number，base）/LN（number） SQRT（number） ROUND（number，num_digits）
返回列表或数据库中满足指定条件的记录字段（列） 中的数字之和。 计算列表或数据库的列中满足指定条件的数值的平 均值。
27
操作准备——函数