1、 Excel 数据处理与分析Excle 是微软公司 office 系列办公软件的组件之一，它是一个功能强大的电子表格程序。Excle 不仅可以将整齐而美观的表格呈现给用户，还可以用来进行数据的分析和预测，完成许多复杂的数据运算，帮助使用者做出更加有根据的决策。同时它还可以将表格中的数据通过各种各样的图形、图表的形式表现出来，增强表格的表达力和感染力。本章中通过员工工资表、公司销售统计表这两个案例，讲解了日常工作中 Excle 的常用功能，使学生能够掌握响应的 Excle 使用方法和使用技巧，提高表格的制作水平，从而提高工作效率。本章内容将通过大量的实例来学习 Excel 数据处理的基本操作。2
2、.1 数据输入工作簿是指在 Excel 环境中用来存储并处理工作数据的文件，它是由若干个工作表组成的。在 Excel 中，可以说一个文件就是一个工作簿，工作簿窗口下方有若干个标签，单击其中一个标签就会切换到该工作表。打开 Excel 时，映入眼帘的工作界面就是工作表。它由众多的行和列中的单元格排列在一起构成。工作表能存储包含字符串、数字、公式、图表和声音等丰富的信息或数据，并能够对这些信息或数据进行各种处理，同时能将工作表打印出来。当工作簿建立之后，就可以在工作簿的每一个工作表中输入数据了。在Excel 工作表的单元格中可以输入的数据有文本、数字、日期、时间和公式等。一、输入文本单元格中的文本
3、包括任何字母、数字和键盘符号的组合。每个单元格最多可包含 32000 个字符，如果单元格列宽容不下文本字符串，就要占用相邻的单元格。如果相邻单元格中已有数据，就会截断显示。二、输入数字在 Excel 中，数字可用逗号、科学计数法或某种格式表示。输入数字时，只要选中需要输入数字的单元格，按键盘上的数字键即可。在 excel 中，输入的数字数据长度在 12 为以上时，会自动转变为科学记数格式。当数据以 0 开头时则自动舍弃前面的 0。如果要让长度为 12 位以上的数字正常显示，可以通过下面两种方法来实现。1、实用“”符号辅助输入在单元格中，先输入“” （英文输入状态下的单引号） ，接着再输入身份证
4、号码。输入完成后，按 键即可正常显示输入的身份证号码。2、设置单元格格式为“文本”格式后输入三、输入日期和时间日期和时间也是数字，但它们有特定的格式。在输入日期时用斜线或短线分隔日期的年、月、日。例如，可以输入“2010/01/26”或“2010-03-26”，如果要输入当前的日期，按组合键 Ctrl + ; (分号) 即可。在输入时间时，如果按 12 小时制输入时间，需在时间数字后空一格，并键入字母 a 或 p，分别表示上午或下午。例如，输入 10:40 p，按 Enter 键后四的结果是 22:40:00，如果只输入时间数字，Excel 将按 AM(上午)处理，如果要输入当前的时间，按组合
5、键 Ctrl + Shift + ; (分号)即可。四、自动填充利用自动填充功能，可以快速地复制数据、复制公式，加快数据的输入速度。操作方法。选定单元格，拖动该单元格右下方的填充句柄，即将光标移至该单元右下角，至光标变成十字形状（+） 。按住鼠标左键不放，向下（向右）拖动。五、数据输入技巧Excel 中有许多数据输入的技巧，如在前面介绍过的自动填充功能等。本节将再介绍其他一些数据输入的技巧。1. 在同一数据列中自动填写重复录入项Excel 具有数据记忆式键入功能，在同一行或同一列中，如果前面的单元格中已有数据输入，即在某单元格输入一个与前面单元格相同的数据时，Excel 会自动显示出该单元格后
6、面的数据。但 Excel 只能自动完成包含文字的录入项，或包含文字与数字的录入项。2. 在多个单元格中输入相同的数据如果在工作表中有多处重复出现相同的数据，那么在数据输入时，可首先将这些单元格同时选中，同时选中的操作方法为在选中第一个单元格后按下 Ctrl 键，再依次单击其他单元格。然后通过编辑栏输入数据，同时按下 Ctrl+Enter 键。此时数据将同时显示在被选中的多个单元格中。例 1在 Excel 中建立公司员工工资表以及输入数据。1. 启动 Excel，新建一个工作簿。并保存为 “员工工资表 .xls”。2. 在“sheet1”工作表的 A1 单元格中输入标题“ 员工工资表” ，选取
7、A1：O1单元格区域，合并该单元格区域，实质字体为“隶书” 、 “深蓝色” 、 “加粗” 、字号为“24” 、 “水平居中” 。3. 在 A2：O2 单元格区域中依次输入“员工号” 、 “月份” 、 “姓名”等各列标题，字体设为“宋体” 、 “红色” 、 “加粗” ，结果如图所示。图 2.1 员工工资表标题行4. 在 A3 至 O12 单元格中输入相应的数据。其中，在 A3 单元格内输入数字“GD001”，选定 A3 单元格，拖动该单元格右下方的填充句柄至 A12 单元格，完成“员工号”列数据的输入。5. 选中 C3:C12，单击“ 格式”“单元格”菜单命令，在弹出的 【单元格格式】对话框中单
8、击“数字”选项卡，在“分类”列表框中选择“文本” ，单击“确定”按钮完成设置，在 C3:C12 单元格中可输入 18 位数字的身份证号。6. 输入“姓名” 、 “部门” 、 “职务” 、 “基本工资” 、 “职务工资” 、 “加班津贴” 、“奖金” 、 “缺勤”等列数据。2.2 公式和函数的使用电子表格系统除了能进行一般的表格处理外，还应该具有数据计算能力。Excel 作为优秀的电子表格处理软件，允许使用公式对数值进行计算。2.2.1 关于单元格和区域引用引用的作用在于标识工作表上的单元格或单元格区域，并指明公式中所使用的数据的位置。通过引用，可以在公式中使用工作表不同部分的数据，或者在多个公
9、式中使用同一个单元格的数值。还可以引用同一个工作簿中不同工作表上的单元格和其他工作簿中的数据。引用不同工作簿中的单元格称为链接。默认情况下，Excel 使用 A1 引用样式，此样式引用字母标识列（从 A 到 IV，共 256 列） ，引用数字标识行（从 1 到 65,536） 。这些字母和数字称为行号和列标。若要引用某个单元格，请输入列标和行号。例如，B2 引用列 B 和行 2 交叉处的单元格。一、引用其他工作表中的单元格 下面的示例中，AVERAGE 工作表函数将计算同一个工作簿中名为 Marketing 的工作表的 B1:B10 区域内的平均值。链接到同一个工作簿中的另一张工作表上请注意，
10、工作表的名称和感叹号 (!) 应位于区域引用之前。二、绝对引用与相对引用的区别一、相对引用 公式中的相对单元格引用（例如 A1）是基于包含公式和单元格引用的单元格的相对位置。如果公式所在单元格的位置改变，引用也随之改变。如果多行或多列地复制公式，引用会自动调整。默认情况下，新公式使用相对引用。例如，如果将单元格 B2 中的相对引用复制到单元格 B3，将自动从 =A1 调整到 =A2。图 2.2 相对引用二、绝对引用 单元格中的绝对单元格引用（例如 $A$1）总是在指定位置引用单元格。如果公式所在单元格的位置改变，绝对引用保持不变。如果多行或多列地复制公式，绝对引用将不作调整。例如，如果将单元格
11、 B2 中的绝对引用复制到单元格 B3，则在两个单元格中一样，都是 $A$1。图 2.3 绝对引用三、混合引用 混合引用具有绝对列和相对行，或是绝对行和相对列。绝对引用列采用 $A1、$B1 等形式。绝对引用行采用 A$1、B$1 等形式。如果公式所在单元格的位置改变，则相对引用改变，而绝对引用不变。如果多行或多列地复制公式，相对引用自动调整，而绝对引用不作调整。例如，如果将一个混合引用从 A2 复制到 B3，它将从 =A$1 调整到 =B$1。图 2.4 混合引用2.2.2 公式公式是对数据进行分析与计算的等式，使用公式可以对工作表中的数值进行加法、减法、乘法、除法等计算。所有的公式必须以符
12、号“=”或“+”开始。一个公式是由运算符和参与计算的元素(操作数 )组成的。公式的输入操作类似于输入文字数据，但输入一个公式的时候应以一个等号( = )作为开始，然后才是公式的表达式。在单元格中输入公式的步骤如下。(1) 选择要输入公式的单元格。(2) 在编辑栏的输入框中输入一个等号( = )，或者在当前选择的单元格中输入一个等号( = )，然后输入公式表达式。(3) 单击【确认】按钮或按 Enter 键就可以得到计算结果。公式中常常包含对其他单元格的相对引用 。例 2.接着上面的操作，在员工工资表中输入“应发工资” 、 “缺勤扣款” 、 “实发工资”等列的数据。操作如下：1. 单击“员工工资
13、表.xls”的 sheet1 工作表中的 L3 单元格，在单元格中输入公式“=H3+I3+J3+K3”,按回车键得出应发工资。2. 在 N3 单元格中输入公式 “=H3/30*M3”，在 O3 单元格中输入公式“=L3-N3”,结果如图 2.5 所示。图 2.5 输入公式3. 按住 CTRL 键，单击 M3、O3 单元格，选中 M3 和 O3 单元格，单击【格式】【单元格】 ，在弹出的【单元格格式】对话框中单击“数字”选项卡，在“分类”列表框中选择“数值”选项，在“小数位数”中输入“2”，单击“确定”按钮完成设置，设置的数字格式将应用于 M3:O3 单元格区域中。图 2.6 【单元格格式】对话
14、框中“数字”选项卡设置4. 选定 L3 单元格，拖动该单元格右下方的填充句柄至 L12 单元格，完成“应发工资”列数据的输入。使用同样的方法，完成“缺勤扣款”和“实发工资”列数据的输入。2.2.3 函数函数可以理解为是一种 excel 已定义好的复杂公式，也可以认为是公式的简写形式。函数可以单独使用，也可以在公式中调用函数。一、函数的语法规定函数使用一些被称为参数的数据按规定的顺序或结构进行计算，单数可以是数字、常量、逻辑值或但与个引用等。函数执行后一般给出一个结果，这个结果成为返回值。函数的结构为：函数名（参数 1，参数 2，参数 3，）二、手工输入函数对于函数的输入，可以采用手工的方法，首
15、先单击选定要输入函数的单元格，鼠标单击 Excel 编辑栏，键入一个等号 “=”，此时单元格进入公示编辑状态，在等号后按照公式的组成顺序依次输入各个部分，公式输入完毕后，单击编辑栏中的“输入”(即“”) 按钮或按回车即可。例 3. 在员工工资表中输入“月份” 、 “性别”等列的数据。操作如下：1. 在 D3 单元格内输入“=now()” ，按回车结束输入 ，D3 单元格内将自动显示系统当前时间。2. 选中 D3 单元格，单击鼠标右键，在弹出的快捷菜单中选择 “设置单元格格式”选项，在弹出的【单元格格式】对话框中单击“数字”选项卡，在“分类”列表框中选择“日期”选项，在“类型”文本框中输入“20
16、01 年 3 月” ，单击“确定”按钮完成设置，设置的日期类型将应用于单元格 D3 中，调整列宽至合适宽度。3. 可以根据身份证号求出性别的值。在 E3 单元格中输入公式“=IF(MOD(MID(C3,17,1),2)=1,“男“,“女“)” ，按“Enter”键得到计算结果。其中 MID(C3,17,1)作用是将 C3 单元格中的字符串从第 17 位起，取 1 位。MOD(MID(C3,17,1),2)=1，作用是判断身份证号的第 17 位能否被 2 整除，即判断是奇数还是偶数，如果 MOD(MID(C3,17,1),2)=1，则 C3 单元格中第 17 位为奇数，否则为偶数。IF(MOD(
17、MID(C3,17,1),2)=1,“男“,“女“) ，作用是如果 MOD(MID(C3,17,1),2)=1 成立，返回“男” ；否则，返回“女” 。图 2.7 性别的计算4. 对于“应发工资”的输入，除了可以用之前的公式以外，还可以用函数实现输入。选定 L3 单元格，单击“常用”工具栏中的 “自动求和”按钮，L3 单元格中显示求和函数，拖动鼠标选定 H3:K3 单元格区域，如图 2.8所示，按【Enter】键，系统将自动完成求和运算并显示求和结果。图 2.8 求和函数5. 选定 D3 单元格，拖动该单元格右下方的填充句柄至 D12 单元格，完成“月份”列数据的输入。使用同样的方法，完成“性
18、别”和“应发工资”列数据的输入。具体数据如图 2.9 所示。图 2.9 员工工资表数据6. 将鼠标指向工作表标签，单击右键，在弹出的快捷菜单中选择“重命名” ，将工作表标签更改为“工资表” 。三、粘帖函数对于不熟悉的函数或较复杂的函数，手工输入时容易出错，可以采用【插入函数】对话框粘贴函数的方法输入函数。【插入函数】对话框是 Excel 输入公式的重要工具，下面以 Count 和 countif函数的使用为例介绍粘贴函数的过程。Count 和 countif 函数都是统计函数，利用函数 count 可以计算单元格区域中数字项的个数，利用函数 countif 可以计算给定区域内满足特定条件的单元
19、格的数目。例 4在工作表工资明细表中，统计员工总人数以及全勤人数。操作步骤：4. 在工作表“工资表”A14 单元格中输入“总人数 ”，单击选定要输入函数的单元格 B14。5. 单击“编辑栏左侧”的“插入函数”按钮。弹出如下图所示的【插入函数】对话框。图 2.10 【插入函数】对话框输入信息选项卡6. 如果对于所用的函数不确定，可以在“搜索函数”对应的文本框中输入需求的简单描述，单击“转到”按钮，则在“选择函数”列表中会有一些列的函数。逐个单击函数，在【插入函数】对话框窗口的下部会出现关系所选函数的相关说明，可根据相关说明选择适用的函数。本例中，我们可以在“或选择类别”列表中选择“统计” ，在“选择函数”列表中选择”count”，单击“确定”按钮，弹出如下“函数参数”窗口，填写相应的参数，单击“确定”按钮，得出总人数。
