excel与数据处理电子教案

Excel与数据处理Excel高级应用篇本章 学习目标 认识 EXCEL的工作界面，窗口组成元素； 了解工作薄、工作表、单元格等基本概念； 掌握不同类型的数据输入方法； 理解相对引用和绝对引用； 掌握公式输入方法； 掌握工作表的删除、插入、移动、改名等操作 掌握单元格、行、列常用操作； 掌握 sumcountif等简单函数的应用方法1.1 EXCEL操作基础1启动 Excel的方法 通过 Windows任务栏选择 “开始 ”按钮 通过 Excel 快捷方式 双击 xcel文件的名字或图标、 退出 xcel的方法 单击 Excel 窗口右上角的关闭按钮 选择 “文件 ”菜单中的 “退出 ”菜单项。 按快捷键 Alt+F4。 1.1 EXCEL操作基础3、 Excel界面 功能区工作表区1.1 EXCEL操作基础 功能区的主要控件 Excel 2007用功能区取代了早期版本中的菜单、工具栏和大部分任务窗格，包括按钮、库和对话框内容。 功能区的 主要控件 Office 按钮 取代了早期版本中的 “文件 ”菜单，单击时，将弹出与 Excel早期版本 “文件 ”菜单相似的菜单项，它是 Excel 2007中唯一的菜单，提供了常用的 Excel文件操作功能，包括的菜单命令有 “新建、打开、保存、另存为、打印、准备、发送、发布、关闭 ”。 选项卡 功能区由 “开始、插入、页面布局、公式、数据 ”等选项卡组成。各选项卡是面向任务的，每个选项卡以特定任务或方案为主题组织其中的功能控件。 能区中的选项卡是动态的，为了减少屏幕混乱，某些选项卡平时是隐藏的，在执行相应的操作时，它们会自动显示出来。 组 Excel 2007将以前版本中那些隐藏在菜单和工具栏中的命令和功能，以面向任务的方式放置在不同的逻辑组中，每个逻辑组能够完成某种类型的子任务。 快速访问工具栏 其中包含一组独立于当前所显示的选项卡的命令，无论用户选择哪个选项卡，它将一直显示，为用户提供操作的便利。用户可以根据需要设置其 中的工具按钮 .1.1 EXCEL操作基础 工作表区的主要对象1.1 EXCEL操作基础 工作表 就是人们常说的电子表格，是 Excel 中用于存储和处理数据的主要文档。由一些横向和纵向的网格组成，横向的称为行，纵向的称为列。 Excel 2007的一个工作表最多可有 1,048,576 行、 16,384 列数据（而 Excel 2003的工作表最多有 65 536行、 256列）。当前正在使用的工作表称为 活动工作表 。 工作表标签 每个工作表有一个名称，体现在工作表标签上。单击工作表标签按钮可使对应的工作表成为活动工作表，双击工作表标签按钮可改变它们的名称。 插入工作表标签按钮 在默认情况下， Excel 只打开 3个工作表： Sheet1， Sheet2，Sheet.单击插入新工作表按钮就会后面插入一个新工作表 sheet4，再单击一次就会插入新工作表 sheet51.1 EXCEL操作基础 行号、列标题 工作表由 1048576行组成，每行的编号称为 行号 工作表的每列用英文字母标志，称为列标题 单元格 工作表中的网格，它用列标题和行号定位。如 A1就表示列第行对应的单元格 单元格区域 是指多个连续单元格的组合。表示形式如下：左上角单元格 :右下角单元格 例如 A2:B3代表一个单元格区域，包括 A2、 B2、 A3、 B3 只包括行号或列号的单元格区域代表整行或整列。如， 1:1表示第一行的全部单元格组成的区域， 1:5则表示由第 1到 5行全部单元格组成的区域； A:A表示第一列全部单元格组成的区域， A:D则表由A、 B、 C、 D四列的全部单元格组成的区域。1.1 EXCEL操作基础 编辑栏 用于显示、修改活动单元格的内容 全选按钮 用于选定整个工作表所有的单元格。 工作表查看方式 工作表下边框的右侧提供的三个按钮用于切换工作表的查看方式 工作表缩放1.2 构造第一 个工作表【 例 1.1】 某单位的工资数据如下表所示，计算每个职工的总工资。假设个人所得税的税率为：收入 1600以下，所得税率为0%；收入 16002500，所得税率为 5%；收入 25003500，所得税率为 8%；收入 35004500，所得税率为 12%；收入4500以上，所得税率为 15%。计算各位职工应缴纳的所得税，以及缴税后的实际收入。姓名 职 称 日期 基本工 资 水 费 电费 气 费 奖 金张贴 民 高工 2009-3-1 2389 244.32 91.2 210.9 871李虎 高工 2009-3-1 2389 155.48 59.88 218.9 900王景 工程 师 2009-3-1 1579 244.89 201 171.3 5291.2 构造第一 个工作表在 EXCEL中可以轻松制作出例 1-1的报表，如图所示输入 A1： H9中的原始档案数据1、在 I2中输入工资计算公式： =D2-E2-F2-G2+H22、向下复制此公式到 I91、在 H2中输入计算公式： =I2-K2、向下复制此公式到H91、在 K2中输入工资计算公式： =I2*J22、向下复制此公式到 K9输入 J列的税率1.3 工作薄和 EXCEL文件管理1.3.1 工作薄与工作表的关系 工作簿 在 Excel中创建的文件叫做工作簿，扩展名是 .xlsx 工作薄由工作表组成，新建工作薄只有默认的个工作表。但可以根据需要增加，个数不限。 新建第一个工作薄的默认名： book1.xlsx。 工作表 由一些横向和纵向的网格组成的表格。一个工作表最多有1,048,576行、 16,384列。 工作表由工作表标签区别，如 sheet1、 sheet21.3.1 工作薄与 工作表的关系 工作薄与 工作表的关系1.3.2 创建 新工作薄1、 工作薄 与模板模板模板即模型、样板，是一种已经建立好的特殊工作薄，已在其中的工作表中设计好了许多功能，如单元格中的字体、字型，工作表的样式和功能（如财务计算，学生成绩统计，会议安排等。工作薄创建 Excel总是根据模板创建工作薄，创建的工作薄具有与模板结构、内容和功能。在没有指定建立工作薄模板的情况下， Excel会据默认模板建立工作薄，这种工作薄在默认情况下只有 Sheet1、 Sheet2和 Sheet3三个工作表。1.3.2 创建 新工作薄2、新建 默认的空工作薄，三种方法 启动 Excel， Excel就会自动建立一个名为 Boolk1.xlsx; 在已启动 Excel的情况下，按 Ctrl+N； 单击功能区左上角的 Office 按钮 ，然后从弹出的文件菜单中选择 “新建 ” ；1.3.2 创建 新工作薄3、根据旧工作薄 建立 工作薄（ 1）单击 Office 按钮 ，从弹出的文件菜单中选择 “新建 ”。Excel 将弹出 “新建工作薄 ”对话框。（ 2）单击 “根据已有内容新建 ” ，然后在弹出的 “根据现有工作薄新建 ”对话框找到并选择已有工作薄的名称，单击该对话框中的 “新建 ”按钮。1.3.2 创建 新工作薄4、根据模板建立工作薄Excel 2007模板类型 已安装的模板 ：安装 Excel时安装在本地计算机中的模板； 我的模板 ：用户自已创建的模板； 在线模板 ：微软公司网站上的在线工作薄模板，在用这类模板创建新工作薄时， Excel会自动从微软的网站中下载相关模板到用户的本地计算机中，并用它创建新工作薄。当然，前提是用户计算机与 Internet网络相连接（即 Online，在线 ）。 1.3.2 创建 新工作薄5、在线 模板示例下面的图表是用 Microsoft Office Online”中的 “预算 ”模板类中的 “商务旅行预算 ”模板建立的工作薄。在该工作薄中，只需要输入 D列的费用和 F列的数字， Excel就会自动计算出 H列的费用，这一功能是由 “商务旅行模板 ”提供的。 1.3.2 创建 新工作薄5、在线 模板示例下图是用 “Microsoft Office Online”中的 “库存控制 ”模板类中的 “设备资产清单 ”模板建立的工作薄。在该工作薄中预设了许多功能，在 L、 N、 P、 Q、 R列中都预设了计算公式，只要填定了其余列的数据， Excel就会自动计算出这些列的数据。1.3.2 创建 新工作薄5、在线模板 的建立方法(1) 确定计算机已连接上了 Internet网络(2) 单击 “Office按钮 ” 新建 Microsoft Office Online.将连接到微软的在线模板网站。(3)从微软网站的在线模板网站中选择需要的模板，就会从网站中将选择的模板下载到本机中，同时将应用。1.3.3 保存和 打开工作薄文件1、保存文件 方法一单击 “快速访问工具栏 ”中的 按钮保存文件。 方法二单击 Office 按钮，从弹出的文件菜单， 选择 “另存为 ”菜单项，然后在 “另存为 ”对话框中输入文件名。 2、打开文件 单击 Office 按钮，从弹出的文件菜单， 选择 “打开 ”菜单项，然后在 “打开 ”对话框中选择要打开文件的名称。 1.3.4 文件格式 与兼容性1、 Excel 2007的文件格式 Microsoft Office 2007引入了一种基于 XML的新文件格式： Microsoft Office Open XML Formats，适用于2007版的 Word、 Excel和 PowerPoint。 Office XML Formats有许多优点 ： 其一，文件自动压缩。某些情况下文档最多可缩小 75% 其二， Office XML Formats格式以模块形式组织文件结构，是如果文件中的某个组件（如图表）受到损坏，文件仍然能够打开 其三，更强的安全性。 Office XML Formats格式支持以保密方式共享文档，可避免用文档检查器工具获取信息。 其四，更好的业务数据集成性和互操作性。在 Office 中创建的信息容易被其他业务应用程序所采用，只需一个 ZIP 工具和 XML 编辑器就可打开和编辑 Office 文件。 1.3.4 文件格式 与兼容性2、 Excel 2007的 文件扩展名和文件兼容性格式 扩 展名 说 明Excel 工作簿 .xlsx Excel 2007默 认 的基于 XML的文件格式。不能存 储 VBA 宏代 码 。Excel 工作簿（代 码 ） .xlsm Excel 2007 基于 XML 且启用宏的文件格式。存 储 VBA 宏代 码 或 Excel 4.0 宏工作表 (.xlm)。Excel 二 进 制工作簿 .xlsb Excel 2007 二 进 制文件格式 (BIFF12)。模板 .xltx Excel 2007模板的默 认 文件格式。不能存 储 VBA 宏代码 或 Excel 4.0 宏工作表 (.xlm)。模板（代 码 ） .xltxm Excel 2007模板的启用了宏的文件格式。存 储 VBA 宏代 码 或 Excel 4.0 宏工作表 (.xlm)。Excel 97- 2003 工作簿 .xls Excel 97 - Excel 2003 二 进 制文件格式 (BIFF8)。Excel 97- 2003 模板 .xlt Excel 97 - Excel 2003 模板的二 进 制文件格式 。XML 电 子表格 2003 .xml XML 电 子表格 2003 文件格式 (XMLSS)。XML 数据 .xml XML 数据格式。Excel 加 载 宏 .xlam Excel 2007 基于 XML 且启用宏的加 载项 ，支持 VBA 项 目和 Excel 4.0 宏工作表 (.xlm) 的使用。1.4 公式和 单元格引用 、公式的概念 Excel的公式由运算符、数值、字符串、变量和函数组成。 公式必须以等号 “=”开头 ，即在 Excel的单元格中， 凡是以等号开头的输入数据都被认为是公式 。在等号的后面可以跟数值、运算符、变量或函数，在公式中还可以使用括号。 例如：=10+4*6/2+(2+1)*50 就是公式，可以在任何单元格中输入此公式 Excel会把公式的计算结果显示在相应的单元格中1.4 公 式2、运算符 在 xcel公式中可以使用运算符。运算符 运算功能 优 先级( ) 括号 1- 负 号 2% 百分号 3 乘方 4*与 / 乘、除法 5+与 - 加、减法 6& 文本 连 接 7=、 、 =、 等于、小于、大于、小于等于、大于等于、不等于81.4 公 式3、说明 括号的运算级别最高，在 Excel的公式中只能使用小括号，无中括号和大括号。小括号可以嵌套使用，当有多重小括号时， 最内层的表达式优先运算 。同等级别的运算符从左到