Excel财务与会计应用培训教材.ppt

第二章EXCEL 的数据处理,第一节Excel 运算公式第二节Excel 函数第三节数据排序与分类汇总第四节数据筛选,第一节 EXCEL 运算公式,一、建立与编辑数据区域1.数据区域的建立。数据区域是 Excel 工作表中进行数据处理的单元区域。Excel 作为一个电子表格软件，本身具有一个完备的表结构，通常可将数据区域中的列称为字段，将行称为记录。建立数据区域的要求是最上面一行或两行必须包含字段名（数据区域的列名称），数据区域中应尽量不要出现空行或空列，并保证每列中包含的是同类的信息。建立数据区域的方法是先建立一个表格，再一条一条地录入数据，既可在工作表中录入，也可通过记录单录入。,第一节 EXCEL 运算公式,【案例 21】远华公司的人员、部门及职务工资等数据，如表 21 所示。除此之外，还有的工资表项目是：效益工资、交通补贴、住房补贴、应发合计、养老保险金、医疗保险金、代扣税、扣款合计、实发合计。要求建立数据区域。,第一节 EXCEL 运算公式,【操作提示】（1）在 Excel 工作表的最上面一行或两行输入字段名的内容，如人员编号、姓名、部门名称、养老保险金等，如图 21 第 2 行所示。（2）移动到数据区域下第一个空行（即图 21 第 3 行），选取一个单元格。（3）选择“数据记录单”菜单命令，打开如图 22 所示的记录单对话框，左边是建立的数据区域的各字段，在其编辑框中录入相应的数据。（4）一条记录录入完毕后，单击“新建”按钮后再录入第二条记录。录入完毕后单击“关闭”按钮，即可完成数据区域的建立。,第一节 EXCEL 运算公式,2.数据区域的编辑。录入的数据有误，可打开数据区域所在的工作表，选择要修改的单元格，可以在编辑栏中直接修改，也可以在“记录单”中对某条记录进行修改。要删除数据区域中的行（记录），可直接在工作表中选定要删除的行，然后右击选择“删除”命令即可。也可在“记录单”中，通过“上一条”、“下一条”或“条件”按钮，选出要删除的行（即要删除的一条记录），单击“删除”按钮，即可删除所选的行。必须注意，用记录单上的“删除”按钮删除的行是不能恢复的。要删除数据区域中的列，则应在工作表中选择所要删除的列，用“编辑”菜单或者列的“快捷菜单”中的“删除”命令进行。,第一节 EXCEL 运算公式,二、公式构成与运算类型工作表中的数据往往需要进行大量的计算，可以根据单元格之间的钩稽关系，使用一些简单的运算公式或较为复杂的函数，Excel 将自动完成这些运算。公式的构成。Excel规定，公式必须以等号开始，是由单元格引用、运算符、数值、字符、函数等组成的能够进行计算的式子。2.Excel 中运算符有四类，即算术运算、字符运算、比较运算和引用运算。（1）算术运算。算术运算是指加、减、乘、除等运算。运算的优先次序为：括号指数乘除加减。同级运算符按从左到右的顺序进行。算术运算符如表 22 所示。,第一节 EXCEL 运算公式,（2）字符运算。字符运算的运算符是“&”，它表示连接、合并运算两边的字符。加入的文本字符要使用双引号，且应是英文状态下的双引号。（3）比较运算。比较运算是用来比较两个数值的关系，并产生逻辑值 TRUL（逻辑真值）和 FALSE（逻辑假值）。比较运算符如表 23 所示。（4）引用运算。引用运算可以将单元格区域合并运算,引用运算一般都要涉及到单元格引用。“单元格引用”是指公式中输入单元格地址时，表示该单元格中的内容参加运算。当引用的单元格中的数据发生了变化时，公式则自动重新进行计算并自动更新计算结果，这种方式下，可以随时观察到数据之间的相互关系。表 24 所示。,第一节 EXCEL 运算公式,三、公式中使用引用引用的作用在于标识工作表上的单元格或单元格区域，并告知 Excel 在何处查找公式中所使用的数值或数据。单元格地址是用列标和行号来标示某一单元格的位置，所以若要引用某个单元格，则在公式中输入单元格的行号、列标，即单元格的地址即可。这样在被引用单元格的数据变化时，公式所在单元格的数据也随之变化。1.单元引用的类型。在 Excel 中有以下三种基本的单元格引用类型。,第一节 EXCEL 运算公式,（1）相对引用。是指在公式中直接对单元格的行号、列标这种相对位置进行的引用。如果公式所有的单元格位置调整，被引用单元格的地址也相应变化；如果多行或多列复制或填充公式，引用的单元格地址也随之改变。例如：E1 单元格中有公式“=A1+B1C1”；向后拖动 E1 单元格填充柄到 F1 时，则 F1 单元格中的公式为“=B1+C1D1”；向下拖动 E1 单元格填充柄到 E2 时，则 E2 单元格中的公式为“=A2+B2C2”。（2）绝对引用。是指在公式中总是对特定位置的单元格地址进行引用。如果公式所在单元格的位置改变，被引用单元格的地址不变；如果多行或多列复制或填充公式，引用的单元格地址不会改变。其方法是在单元格地址的行号、列标前均加上符号“$”，如“$A$1”表示对 A1 单元格进行绝对引用。,第一节 EXCEL 运算公式,（3）混合引用。是指在公式中对单元格的行号进行绝对引用而对列标进行相对引用，或对行号进行相对引用而对列标进行绝对引用。如果公式所在单元格的位置改变，则相对引用的随之改变，而绝对引用的不会改变。其方法是在绝对引用的行号或列标前加“$”符号。引用类型的转换。为将公式中用到的地址表示为绝对引用，需加上“$”符号。所以可以通过修改公式中“$”符号的位置、或删除“$”符号，来改变单元格的引用类型；也可按 F4 功能键，改变公式中单元格的引用类型。,第一节 EXCEL 运算公式,【案例 22】远华公司的职务工资数据见案例 21。根据该公司的分配方案，效益工资为职务工资的 10%；住房补贴为职务工资的 5%；应发合计为职务工资、效益工资、交通补贴、住房补贴之和；养老保险金为应发合计的 5%；医疗保险金为职务工资、效益工资之和的 2%；扣款合计为养老保险金、医疗保险金、代扣税之和；实发合计为应发合计减扣款合计。要求完成以上相关项目的计算（交通补贴、代扣税暂不计算）。,第一节 EXCEL 运算公式,【操作提示】（1）效益工资的计算：选中 F3 单元格，然后输入“=”；单击一次 E3 单元格，则该单元格被相对引用到公式中；再输入“*10%”；公式输入完毕后按回车键，则 F3 单元格自动显示计算结果，上部编辑框中显示其公式为“=E3*10%”。如图 23 所示。（2）应发合计计算：选中 I3 单元格，输入“=”号；单击 E3，输入“+”号；单击 F3，输入“+”号；单击“G3”，输入“+”号；单击 H3，按下回车键即可。其编辑框中显示的公式将为“=E3+F3+G3+H3”。也可在选中I3单元格后，在英文状态下直接从键盘上输入“=E3+F3+G3+H3”，然后按下键盘上的回车键 Enter。,第一节 EXCEL 运算公式,（3）医疗保险金的公式为 K3=(E3+F3)*2%，操作方法是：选定 K3，输入“=”号；输入左括号“（”，单击 E3 单元格；输入“+”号，单击 F3 单元格；输入右括号“）”，输入“*2%”；按下键盘上的回车键。（4）其他单元格的公式分别为：H3=E3*5%，J3=I3*5%，M3=J3+K3+L3，N3=I3M3。,返回,第一节 EXCEL 运算公式,（5）选定 F3:N3 单元格区域，如图 23 所示；然后将鼠标指向 N3 单元格右下角的填充柄，此时鼠标变为粗大的“+”字状，按下鼠标垂直向下拖动到第 14 行，然后释放鼠标；实现单元格公式的复制。Excel 自动计算出本表各单元格的数值。由于在公式中使用了单元格引用，若修改 E 列中某单元格的职务工资，则引用这一单元格的其他单元格的值也会随之改变。同时，由于第 3 行相关单元格的公式均是对其他单元格的相对引用，所以用填充柄自动复制的 F4:N14 区域的单元公式，也会自动调整为对本行各单元格的引用，从而保证计算结果的正确性。,第一节 EXCEL 运算公式,四、公式返回的错误值在输入公式时，有不符合要求的输入，就无法在 Excel工作表的单元格中显示正常的运算结果，该单元格中将会出现提示性的错误值信息。了解这些错误信息的含义有助于用户检查、修改公式，直到公式正确。表 25 中列出了 Excel 中的错误信息及其含义。【案例 23】某公司的产品销售额如表 26 所示，要求计算各产品的销售比重。,第一节 EXCEL 运算公式,【操作提示】A 产品25 800B 产品36 900C 产品37 300合计100 000（1）在工作表的 A1、A2:B6、C2、E2、G1、G2:K3、G4、G6 单元格中，输入相关的文字或数值；如图 24 所示。（2）选定 A1:E1 单元格区域，单击格式工具栏的合并居中F1:F6、G1:K1、G4:G5 单元格区域。（3）在 C3 单元格中输入公式“=B3/B6”，即分母为相对引用 B6 单元格；而在 D3 单元格中输入公式“=B3/$B$6”，即分母为绝对引用 B6 单元格；选定 C3:D3，鼠标指向 D3 右下角的填充柄，待变为“+”字状态时，按下鼠标垂直向下拖动到第 6 行，然后释放鼠标。,第一节 EXCEL 运算公式,（4）同样的，输入第二种分析方法的公式 H4=H3/K3、H5=H3/$K$3；选定 H4:H5，通过填充柄水平向右拖动，自动填充 I4:K5 单元区域公式。（5）分析：由于 D3 单元格公式的分母是绝对引用 B6 单元格的数值，通过自动填充方式复制的 D4:D6 单元格公式中的分母始终引用 B6 单元格的值；D3 单元格公式的分子相对引用B3单元格的值，自动填充后的分子将随单元格而改变；所以公式将变D4=B4/$B$6、D5=B5/$B$6、D6=B6/$B$6，计算结果是正确的。相反C3单元格的分子、分母均为相对引用；当自动填充复制公式时，引用的单元均会随之改变，即其公式将会变为 C4=B4/B7、C5=B5/B8、C6=B6/B9。,第一节 EXCEL 运算公式,同理，H5 单元公式的分母为绝对引用 K3，所以自动填充后的 I5:K5 的公式是正确的；而 H4 单元公式为相对引用 K3 的值，自动填充后出现“分母为 0”的错。（6）选定 A1:K6 单元格区域，单击格式工具栏的边框按钮中的下拉箭头，在其中选择“所有框线”选项，给工作表中的有效表格（即数据区域）加上边框线。（7）单击工作表左上角行列交叉处的全选按钮，然后通过格式工具栏，设置字体。（8）选定 B3:B6，按下键盘上的 CTRL 键，再选定 H3:K3 单元区域；单击格式工具栏的千位分隔按钮、单击两次减少小数位数按钮。选定 C3:D6，按下键盘上的 CTRL键，再选定H4:K5单元区域；单击格式工具栏的百分比按钮,第二节 EXCEL 函数,在 Excel中，函数实际上是一个预先定义的特定计算公式。按照这个特定的计算公式对一个或多个参数进行计算，并得出一个或多个计算结果，叫做函数值。,第二节 EXCEL 函数,一、函数的使用与构成1.函数的使用。Excel 提供了财务函数、日期与时间函数、数学与三角函数、统计函数、查找与引用函数、数据库函数、文本函数、逻辑函数、信息函数九大类两百多个函数。使用这些函数不仅可以完成许多复杂的计算，而且还可以简化公式的繁杂程度。【案例 24】某公司生产经营 46 种产品，2009 年各季度销售收入如表 27 所示。要求用函数计算各季度、各产品的合计收入（其他指标以后介绍）。,第二节 EXCEL 函数,（1）设计表格。录入 A 列和第 2 行的相关文字；输入 B3:G6 的已知金额；合并 A1:L1单元格区域；设置字体字号。如图 25 所示。（2）常用工具栏求和法。操作方法如下：a.选定 H3 单元格，单击常用工具栏的自动求和按钮，或单击该按钮边的下拉箭头，在弹出的下拉菜单中，选择“求和”命令。b.此时该单元格中将反白显示求和公式“=SUM(B3:G3)”，且 B3:G3 单元格将以闪烁的虚框显示该公式所引用的单元格区域，同时编辑框中显示其公式“=SUM(B3:G3)”。,第二节 EXCEL 函数,c.由于公式无误，所以再次单击自动求和按钮，以确认并锁定该公式；也可按下键盘上的回车键 ENTER，或编辑框前的输入按钮锁定公式。若双击自动求和按钮，可快速实现上述求和公式的输入。d.选定 H3 单元格，鼠标指向右下角的填充柄，待变为“+”字状态时，按下鼠标向下拖动至 H6 单元格，以实现自动复制公式。（3）向导求和法。操作方法如下。a.选定 B7 单元格，单击编辑框前的插入函数按钮，或单击自动求和钮边的下拉箭头选择“其他函数”命令，或选择“插入/函数”菜单命令，均会弹出“插入函数”界面，如图 26 所示。,第二节 EXCEL 函数,b.单击该界面的类别后的下拉箭头，在弹出的下拉框中选择“数学与三角函数”；再通过右部的滚动条，找到求和函数“SUM”；选定“SUM”函数后单击“确定”按钮，或双击该函数，进入“函数参数”界面，如图 27 所示。c.在函数参数界面的 NUMBER1 参数后部的输入框中输入“B3:B6”；或者单击后部的引用按钮，用鼠标选择“B3:B6”单元格区域，再单击引用按钮回到函数参数界面，实现单元格的引用。d.单击“确定”按钮回到工作表界面，此时 B7 单元格中将显示计算结果，编辑框中将显示函数公式“=SUM(B3:B6)”。,第二节 EXCEL 函数,（4）直接输入法。2.Excel函数的构成。函数是由函数名及参数构成，函数名说明函数所要进行的运算，而参数是指定函数使用的数值或单元格引用。函数公式如下：=函数（参数 1,参数 2,参数 3,）例如，公式“=SUM(C3:C6)”，式中 SUM 为函数名，是求和。C3:C6 单元格区域是参数，该公式表示为对 C3 到 C6 单元格区域内的数据求和。使用函数时，函数的参数必须满足以下规定：函数参数应用括号括起来，且括号前后不能有空格；函数中参数多于一个时，必须用逗号分隔他们；参数除了是单元格的引用外，还可以是数、文本、逻辑值、数值、公式或其他函数；给定的参数必须能产生一个有效的值。,第二节 EXCEL 函数,二、常用函数的使用1.求和函数 SUM。函数公式为：=SUM(Number1,Number2,)式中，Number1,Number2,为 130 个需要求和的参数。2.平均数函数 AVERAGE。函数公式为：=AVERAGE(Number1,Number2,)3.计数函数 COUNT。函数公式为：=COUNT(Value1,Value2,)Value1,Value2,表示包含或引用各种类型数据的参数（130 个），但只有数字类型的数据才被计算。,第二节 EXCEL 函数,4.条件函数 IF。其功能是判断一个条件是否满足，如果满足返回一个值，如果不满足则返回另一个值。函数公式为：=IF(Logical_test,Value_if_true,Value_if_false)式中，Logical_test 表示计算结果为 TRUE（逻辑真值）或 FALSE（逻辑假值）的任意值或表达式；Value_if_true 表示为 TRUE 时的返回值；Value_if_false 表示为 FALSE 时的返回值。IF 在进行多次判断中，最多可以嵌套七层。,第二节 EXCEL 函数,【案例 25】承案例 22，计算交通补贴：经理为 300 元、科员为 150 元。【操作提示】（1）选中 G3 单元格；在其中输入公式“=IF(D3=经理,300,150)”。该公式的含义为：D3 单元格中如果是文本“经理”，就返回其逻辑真值 300，否则就返回假值 150。其结果如图28 所示。（2）拖动 G3 单元格填充柄向下填充公式即可。,第二节 EXCEL 函数,5.求最大值函数 MAX。其功能是返回一组数值中的最大值。函数公式为：=MAX(Number1,Number2,)式中，Number1,Number2,，表示要从中找出最大值的 130 个数字参数。6.求最小值函数 MIN。其功能是返回一组数值中的最小值。函数公式为：=MIN(Number1,Number2,)【案例 26】承前案例 24，计算各季品种数、平均销售额、各季最大销售额、最小销售额、判断哪些产品全年销售额超过了 2 000 万元。,第二节 EXCEL 函数,【操作提示】（1）平均数。方法是：选定 I3 单元格，输入“=AVERAGE(B3:E3)”；选定 I4 单元格，输入“=AVERAGE(B4:F4)”；选定 I5 单元格，输入“=AVERAGE(B5:G5)”；选定 I6 单元格，输入“=AVERAGE(B6:G6)”。（2）各季最大销售额。输入其函数公式：J3=MAX(B3:E3)、J4=MAX(B4:F4)、J5=MAX(B5:G5)、J6=MAX(B6:G6)。（3）最小销售额。K3=MIN(B3:E3)、K4=MIN(B4:F4)、K5=MIN(B5:G5)、K6=MIN(B6:G6)。,第二节 EXCEL 函数,（4）各季经营品种。输入公式 L3=COUNT(B3:G3)；然后自动填充 L4:L7 单元区域公式。以上的计算均可使用向导法，选择“统计”类中的对应函数进行计算。（5）是否超 2 000 万元判断。输入公式 B8=IF(B72000,是,否)；然后自动填充 C8:G8单元区域公式。7.取绝对值函数 ABS。其功能是取表达式的绝对值。函数公式为：=ABS(Number)式中，Number 表示需要计算其绝对值的实数。8.条件计数 COUNTIF。其功能是计算某个区域中满足给定条件的单元格数目。函数公式为：=COUNTIF(Range,Criteria),第二节 EXCEL 函数,式中：Range 表示需要计算其中满足条件的单元格数目的单元格区域；Criteria 表示确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。9.求模函数 MOD。其功能是返回两数相除的余数。函数公式为：=MOD(Number,Divisor)式中，Number 表示被除数；Divisor 表示除数。10.条件求和函数 SUMIF。其功能是对满足条件的单元格求和。函数公式为：=SUMIF(Range,Criteria,Sum_range)式中，Range 表示用于条件判断的单元格区域；Criteria 表示确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本；Sum_range 表示需要求和的实际单元格。,第二节 EXCEL 函数,11.排位函数 RANK。其函数公式如下：=RANK(Number,Ref,Order)式中，Number 表示需要排位的数字；Ref 表示包含一组数字的数组或引用，即需排位的全部总体，非数值型参数将被忽略；Order 表示排位方式，如果 Order 为 0 或省略则按降序排列，否则按升序排列。【案例 27】承案例 22 计算个人所得税。个人所得税的起征点和税率如表 28 所示。工资、薪金所得税的计算公式是：应纳个人所得税税额=应纳税所得额适用税率速算扣除数,第二节 EXCEL 函数,【操作提示】（1）选中L3 单元格；在单元格中输入公式（这些字符均应在英文输入法下=IF(I3J3K320000,0,IF(I3J3K32000500,0.05*(I3J3K32000),IF(I3J3K320002000,0.1*(I3J3K32000)25,IF(I3J3K320005000,0.15*(I3J3K32000)125,0.2*(I3J3K32000)375),第二节 EXCEL 函数,公式含义为：按国家规定，如果应纳税税额员工的应发合计法定免征项(养老保险金+医疗保险金)起征点(现行为 2000 元)小于 0，则个人所得税为 0；否则如果应纳税税额在 0500 元，为应纳税税额5%；否则如果应纳税税额在 5002 000 元，为应纳税税额10%25元的速算扣除数，以此类推；由于该公司没有人的收入超过 20 000 元，所以，条件函数 IF只判断到 5 00020 000 即可。（2）拖动 L3 单元格的填充柄向下填充即可，结果如图 29 所示。,第三节数据排序与分类汇总,一、数据排序1.排序的概念。排序是将数据区域中的记录，按其某字段名的数据值由小到大（升序）或由大到小（降序）的顺序进行排列，用来排序的字段或条件称为排序键。排序后，将会对整顿秩序各工作表或选定区域的内容进行调整。2.排序操作。排序的操作方法有以下几种。（1）用工具按钮排序。若只按一个排序标志进行排序，用常用工具栏上的排序按钮更为方便。其操作是：在数据区域中选定要排序的单元区域。根据需要单击常用工具栏上的“降序”或“升序”，这时会弹出排序的警告对话框（如图 210 所示）。,第三节数据排序与分类汇总,在“排序警告”对话框中一定注意，选择“扩展选定区域”以某个字段进行排序时，对应的列会随排序位置的变化而变化。而“以当前选定区域排序”，其他的列将不参与排序。Excel 中进行升序排序时，数字和日期按由小到大排列；字母按 AZ、az 进行排列；汉字要选转化为汉语拼音，再按字符比较规则进行比较。（2）用菜单命令进行排序。使用“数据排序”命令，可以按多个排序键来进行排序，还可以按列、或者按行来排序。但排序键最多不能超过三个，分别称为主要关键字、次要关键字和第三关键字；当主关键字值相同时，就根据次关键字排序，如果次关键字也一致，则根据第三关键字来排序。,第三节数据排序与分类汇总,【案例 28】承案例 27，进行工资表的排序。【操作提示】a.选择数据区域中一个单元格；选择“数据排序”命令，打开如图211所示的“排序”对话框。b.单击对话框中“主要关键字”列表框的向下箭头，选取主要关键字。若需要还可以选取次要关键字和第三关键字，并在“升序”、“降序”上作出相应的选择。c.单击“确定”按钮，按职务工资的升序排序，排序后图 212 中人员编号的顺序变化。如果要按行进行排序，或者对文本要进行“按字母排序”或“按笔画排序”，或者排序时字母要区分大小写等，请在图 211 所示的“排序”对话框选择“选项”，打开“选项”对话框并在其中进行选择。,第三节数据排序与分类汇总,3.返回排序前的表格。当反复对表格进行排序后，表格的原有次序被打乱，若需返回原有表格次序，可使用常用工具栏的撤销或恢复按钮。但是排序的次数较多，或排序后还进行了其他的操作，此时恢复就不容易了。所以，在排序前最好是给表格增加一个表示原有表格次序的字段，如“序号”、“编号”、“NO.”等字段名，置于 A 列、或表格的有效数据的最后一列，对原表格的所有记录（即每行）进行编号。这样，排序后要返回原表格的次序只需对该字段进行重新排序即可。,第三节数据排序与分类汇总,二、分类汇总分类汇总就是将很多数据按同类别归类放到一起，然后再进行同类数据求和、计数、求平均等汇总运算。通过分类汇总，得到需要的统计信息，诸如商品销售按部门、按人员汇总等重要的统计信息。在分类汇总前必须对工作表指定字段进行排序；如果没有排序，汇总结果很混乱，无法达到真正的分类汇总的目的。分类汇总的形式有单字段分类汇总和多字段分类汇总。1.单字段分类汇总。Excel能够快速的以某一个字段为分类项，对数据区域中其他字段的数值进行各种汇总统计。分类汇总时将分类项称为分类字段；将其他需统计值的字段称为选定汇总项，汇总项可以多选；汇总的方式可以是求和、乘积、最大值、最小值、计数、计数值、标准差、方差等。,第三节数据排序与分类汇总,【案例 29】承案例 27，对远华公司 2008 年 8 月工资表，按“部门”分类汇总显示实发工资的部门和人数。【操作提示】（1）选取数据区域中的任一单元格,选择“数据分类汇总”命令，打开如图 213 所示的“分类汇总”对话框。（2）在“分类字段”中选择“部门名称”；在“汇总方式”中选择“求和”；在“选定汇总项”中选择“实发合计”；单击“确定”按钮，完成分类汇总操作。2.多字段分类汇总。若需要从数据区域中得到更多的信息，以便于分析数据，则多次执行汇总功能即可。即选按某一个字段进行分类汇总，然后再按另一个字段进行分类汇总，但要在图 213 的分类汇总对话框中取消“替换当前分类汇总”选项。,第三节数据排序与分类汇总,【操作提示】在上例的基础上，还需按“部门”分类汇总“部门人数”，继续其操作如下：（1）选取数据区域中的任一单元格，选择“数据分类汇总”，打开“分类汇总”对话框。（2）在“分类字段”中选择“部门名称”；在“汇总方式”中选择“计数”；在“选定汇总项”中选择“姓名”，图 214。（3）清除“替换当前分类汇总”选项，单击“确定”按钮。3.大纲功能的使用。Excel 在分类汇总的同时，也建立了大纲图标于工作表的左上角。,第三节数据排序与分类汇总,单击“1”则只显示“总计”一行，单击“3”则显示全部数据及汇总结果。4.清除分类汇总。清除分类汇总的方法很简单，只要在“分类汇总”对话框中单击“全部删除”按钮，就能将分类汇总删掉。在汇总的要求较简单时，分类汇总功能是得力的工具。但如果数据区域较庞大，并且汇总要求较为复杂时，更好的分类汇总工具是数据透视表、数据透视图，详见本书后面章节的介绍。,第四节数据筛选,使用Excel 工作表时，常常会根据需要去查找所需的某项信息，数据筛选能方便快速的找到所需的信息。执行数据筛选后只显示出包含符合某一值或一组条件的行，而其他行将会被隐藏。Excel 2003 提供了自动筛选和高级筛选功能。,第四节数据筛选,一、自动筛选使用自动筛选可以快速而方便地查找和使用单元格区域或工作表的所需信息，隐藏那些不希望显示的行。1.使用自动筛选列表进行筛选。筛选条件比较简单时，通过“自动筛选”命令产生自动筛选器，该筛选器下部列出了某一个字段下所有可供筛选的子项列表，选择列表项即可筛选。【案例 210】从远华公司 2008 年 8 月工资表中筛选显示“销售部”职工的工资情况。【操作提示】（1）选取数据区域中的任一单元格，选择“数据筛选”命令中的“自动筛选”子命令，这时可以看到各字段边的筛选器（带下拉箭头的标记），如图 215 所示。,第四节数据筛选,（2）单击要进行筛选列（字段）的筛选箭头（筛选器），下拉出一个清单框，从中选择“销售部”。从图 216 中可以看到，除了“销售部”的职工外，其他所有行（记录）的数据都在数据区域中被隐藏了（表中的行号不连续、有一部分不可见，表明未显示行仍然存在，因不符合筛选条件而被隐藏）。（3）若要取消所选子项的筛选，而按另一子项进行筛选，要先通过“筛选器”清单上的“全部”命令恢复所有数据，然后再从列表中选另一子项进行筛选。如查看了“销售部”职工的情况后，单击“部门名称”字段边的筛选器，选“全部”命令，则所有隐藏行（记录）均全部显示；然后再单击筛选器，从列表中选“采购部”，则除采购部职工外的其他记录被全部隐藏。,第四节数据筛选,（4）不同字段边的筛选器之间是“且”（即“与”）的关系。如：在部门名称筛选器中选择“采购部”，又在职务筛选器中选择“经理”，则只显示采购部经理的相关记录，除此之外的其他记录将被全部隐藏。（5）取消筛选操作。从“数据”菜单中再次选择“筛选”命令后，单击“自动筛选”子命令，则工作表中的筛选箭头消失，筛选操作被取消。2.自定义筛选。在进行自动筛选时，可对某一字段设置一些特殊的筛选条件，如大于、等于、小于、不大于、始于、止于、包含、不包含等条件；可以进行两个条件的设定，条件之间的关系，可以是“与”、也可以是“或”；这些条件的值，既可以从列表中选择、也可输入特定值，还可使用通配符“*”或“？”，其中“*”表示任意多个字符、“？”表示单个字符。,第四节数据筛选,【案例 211】从远华公司 2008 年 8 月工资表中，筛选出代扣税大于 0 而小于 200 元的职工情况。【操作提示】（1）选取数据区域中的任一单元格，选择“数据筛选/自动筛选”子命令，则工作表的所有字段边均出现筛选器；若原已进行了筛选而没有取消其筛选操作，则应保证所有已使用的筛选器均已选择了“全部”命令，即工作表的各行（记录）均没有被隐藏。（2）单击代扣税筛选器，在下拉清单框中选择“(自定义.)”项，如图 215 所示，进入图 217 所示的“自定义自动筛选方式”界面。,第四节数据筛选,（3）在该对话框中选择“大于”，右栏选择“0”（也可直接输入）；选择中部为“与”的关系；选择第二个条件为“小于”，右栏输入“200”（本例不能直接选择）。（4）单击“确定”，完成自定义自动筛选，结果将会只显示该公司代扣税大于0且小于200 元的职工记录情况。【案例 212】从远华公司 2008 年 8 月工资表中，筛选显示所有姓“陈”的职工。【操作提示】（1）对工作表使用“自动筛选”命令（或保证所有已使用的筛选器均已单击了“全部”命令）。（2）单击姓名筛选器，从下拉列表中选择“自定义”项，进入图 218 所示的“自定义自动筛选方式”界面。,第四节数据筛选,（3）在其中选择“始于”条件，在右部输入“陈*”；单击“确定”按钮后，将只显示“陈”姓职工的信息，其他记录将被隐藏。需说明的是，若需筛选姓名中最后一个字为“红”的职工，应选择“止于”条件；若要选择姓名中的任意一个字为“兵”的职工，则应选择“包含”条件。3.前 10个筛选。需统计显示某个字段中相对值靠前、或靠后的记录时，可使用自动筛选器提供的“前 10 个”功能。,第四节数据筛选,【案例 213】从远华公司 2008 年 8 月工资表中，筛选显示职务工资处于后 3 位的职工。（1）对工作表使用“自动筛选”命令（或保证所有已使用的筛选器均已单击了“全部”命令）。（2）单击职务工资筛选器，选择“前 10 个”命令进入“自动筛选前 10 个”界面，如图219 所示。（3）选择“最小”条件，输入值为“3”，选择“项”为统计数量；单击“确定”按钮即可。需说明的是，在条件中还可选择“最大”；统计数量中还可选择“百分比”为计量依据，如图 220 所示。,第四节数据筛选,二、高级筛选与自动筛选相比，高级筛选可以选用更多的条件，并且可以不使用逻辑运算符而将多个筛选条件加以逻辑运算；高级筛选还可将筛选结果从数据区域中抽取出来，复制到当前工作表的指定位置上。要使用高级筛选，需要有 3 个区域：数据区域，即需要进行筛选的数据区域或整个工作表。条件区域，即用来指定筛选数据时必须满足的条件。显示筛选结果区域，即用于存放满足筛选条件结果的区域；可以是原有区域、也可以是其他指定的区域。,第四节数据筛选,【案例 214】根据远华公司 2008 年 8 月工资表，筛选出“医疗保险大于 40，且代扣税大于 0；或养老保险大于 130 的职工”。【操作提示】（1）建立数据区域，如图 221 的 A1:N14 所示的区域。（2）建立条件区域，如图 221 的 J16:L18 所示的区域。方法如下。a.在条件区域的首行输入数据区域中需查询的“字段名”，如本例中，需要输入“医疗保险金”、“代扣税”、“养老保险金”三项。注意字段名的拼写必须同数据清单中的字段名完全一致。条件区域的字段名不一定包含数据清单中的全部字段名，并且字段的排序顺序也可以同数据清单中的不同。,第四节数据筛选,b.在条件区域的第二行及其以下各行输入筛选的具体条件，可以在条件区域的同一行中输入多重条件。但应注意：同一行中的条件其逻辑关系是“与”的关系；不同行之间的条件在逻辑上是“或”的关系。（3）实施高级筛选，方法如下：a.选择“数据筛选”命令，打开“高级筛选”对话框。如图 222 所示。b.单击“数据区域”后的按钮选定单元区域；单击“条件区域”后的按钮，指定条件区域。c.如需“在原有区域显示筛选结果”，则在“方式”选项中选中此项；如需“将筛选复制到其他位置”，则选中此项，并指定结果存放的位置。,第四节数据筛选,三、数据处理实战案例d.单击“确定”，完成高级筛选，结果如图221的【案例资料】某班学生的考试成绩如表 29 所示。【操作要求】（1）在 EXCEL 中用函数完成以下各项计算：总成绩、平均成绩、单科最高分和最低分、单科平均成绩。,第四节数据筛选,（2）评奖成绩前五科平均成绩70%+操行成绩20%+体育10%；名次按评奖成绩的降序排列；奖学金按评奖成绩大于 85 为一等，评奖成绩大于 70 为二等，评奖成绩大于 60 为三等。（3）筛选显示各科成绩都及格，以及各科成绩都在 70 分以上的学生。（4）分类汇总显示奖学金每一等级的学生。,表 21远华公司2008年8月工资表,表2-2 EXCEL公式中的算术运算符（英文输入状态）,表2-3比较运算符（英文输入状态）,表 24引用运算符（英文输入状态）,表 25EXCEL公式返回的错误值及其含义,表 26某公司产品销售收入表,表 27某公司2009年产品销售收入明细表,表 28工资、薪金所得税率及速算扣除数,表 29某班学生考试成绩表,表 29某班学生考试成绩表,图 21 数据区域示例,图 22 记录单对话框,图 23 单元格公式及运算结果,图 24 单元格绝对引用与相对引用及其错误提示,图 25 某公司产品销售分析表,图 26 插入函数界面,图 27 函数参数界面（SUM 函数）,图 28 交通补贴的计算公式及结果,图 29 个人所得税计算公式及计算结果,图 210 排序警告界面,图 211 排序对话框,图 212 职务工资按升序排列,图 213 分类汇总对话框,返回,图 214 汇总选择项,图 215 筛选箭头下拉清单,图 216 筛选示例,图 217 自定义筛选的“与”的筛选,图 218 自定义通配符的筛选,图 219 前 10 个筛选条件（相对值靠后）,图 220 前 10 个筛选可用的其他条件,图 221 高级筛选结果图（3 大区域）,图 222 高级筛选界面,