《excel数据管理知识及财务培训处理.ppt》由会员分享，可在线阅读，更多相关《excel数据管理知识及财务培训处理.ppt（87页珍藏版）》请在金锄头文库上搜索。
1、第二章 EXCEL 的数据处理,第一节Excel 运算公式 第二节Excel 函数 第三节数据排序与分类汇总 第四节数据筛选,第一节 EXCEL 运算公式,一、建立与编辑数据区域 1.数据区域的建立。数据区域是 Excel 工作表中进行数据处理的单元区域。Excel 作为一个电子表格软件，本身具有一个完备的表结构，通常可将数据区域中的列称为字段，将行称为记录。建立数据区域的要求是最上面一行或两行必须包含字段名（数据区域的列名称），数据区域中应尽量不要出现空行或空列，并保证每列中包含的是同类的信息。建立数据区域的方法是先建立一个表格，再一条一条地录入数据，既可在工作表中录入，也可通过记录单录入。
2、,第一节 EXCEL 运算公式,【案例 21】远华公司的人员、部门及职务工资等数据，如表 21 所示。除此之外，还有的工资表项目是：效益工资、交通补贴、住房补贴、应发合计、养老保险金、医疗保险金、代扣税、扣款合计、实发合计。要求建立数据区域。,第一节 EXCEL 运算公式,【操作提示】 （1）在 Excel 工作表的最上面一行或两行输入字段名的内容，如人员编号、姓名、部门名称、养老保险金等，如图 21 第 2 行所示。 （2）移动到数据区域下第一个空行（即图 21 第 3 行），选取一个单元格。 （3）选择“数据记录单”菜单命令，打开如图 22 所示的记录单对话框，左边是建立的数据区域的各字段
3、，在其编辑框中录入相应的数据。 （4）一条记录录入完毕后，单击“新建”按钮后再录入第二条记录。录入完毕后单击“关闭”按钮，即可完成数据区域的建立。,第一节 EXCEL 运算公式,2.数据区域的编辑。录入的数据有误，可打开数据区域所在的工作表，选择要修改的单元格，可以在编辑栏中直接修改，也可以在“记录单”中对某条记录进行修改。要删除数据区域中的行（记录），可直接在工作表中选定要删除的行，然后右击选择“删除”命令即可。也可在“记录单”中，通过“上一条”、“下一条”或“条件”按钮，选出要删除的行（即要删除的一条记录），单击“删除”按钮，即可删除所选的行。必须注意，用记录单上的“删除”按钮删除的行是不
4、能恢复的。要删除数据区域中的列，则应在工作表中选择所要删除的列，用“编辑”菜单或者列的“快捷菜单”中的“删除”命令进行。,第一节 EXCEL 运算公式,二、公式构成与运算类型 工作表中的数据往往需要进行大量的计算，可以根据单元格之间的钩稽关系，使用一些简单的运算公式或较为复杂的函数，Excel 将自动完成这些运算。 公式的构成。Excel规定，公式必须以等号开始，是由单元格引用、运算符、数值、字符、函数等组成的能够进行计算的式子。 2. Excel 中运算符有四类，即算术运算、字符运算、比较运算和引用运算。 （1）算术运算。算术运算是指加、减、乘、除等运算。运算的优先次序为：括号指数乘除加减。
5、同级运算符按从左到右的顺序进行。算术运算符如表 22 所示。,第一节 EXCEL 运算公式,（2）字符运算。字符运算的运算符是“&”，它表示连接、合并运算两边的字符。加入的文本字符要使用双引号，且应是英文状态下的双引号。 （3）比较运算。比较运算是用来比较两个数值的关系，并产生逻辑值 TRUL（逻辑真值）和 FALSE（逻辑假值）。比较运算符如表 23 所示。 （4）引用运算。引用运算可以将单元格区域合并运算,引用运算一般都要涉及到单元格引用。“单元格引用”是指公式中输入单元格地址时，表示该单元格中的内容参加运算。当引用的单元格中的数据发生了变化时，公式则自动重新进行计算并自动更新计算结果，这
6、种方式下，可以随时观察到数据之间的相互关系。表 24 所示。,第一节 EXCEL 运算公式,三、公式中使用引用 引用的作用在于标识工作表上的单元格或单元格区域，并告知 Excel 在何处查找公式中所使用的数值或数据。单元格地址是用列标和行号来标示某一单元格的位置，所以若要引用某个单元格，则在公式中输入单元格的行号、列标，即单元格的地址即可。这样在被引用单元格的数据变化时，公式所在单元格的数据也随之变化。 1. 单元引用的类型。在 Excel 中有以下三种基本的单元格引用类型。,第一节 EXCEL 运算公式,（1）相对引用。是指在公式中直接对单元格的行号、列标这种相对位置进行的引用。如果公式所有
7、的单元格位置调整，被引用单元格的地址也相应变化；如果多行或多列复制或填充公式，引用的单元格地址也随之改变。例如：E1 单元格中有公式“=A1+B1C1”；向后拖动 E1 单元格填充柄到 F1 时，则 F1 单元格中的公式为“=B1+C1D1”；向下拖动 E1 单元格填充柄到 E2 时，则 E2 单元格中的公式为“=A2+B2C2”。 （2）绝对引用。是指在公式中总是对特定位置的单元格地址进行引用。如果公式所在单元格的位置改变，被引用单元格的地址不变；如果多行或多列复制或填充公式，引用的单元格地址不会改变。其方法是在单元格地址的行号、列标前均加上符号“$”，如“$A$1”表示对 A1 单元格进行
8、绝对引用。,第一节 EXCEL 运算公式,（3）混合引用。是指在公式中对单元格的行号进行绝对引用而对列标进行相对引用，或对行号进行相对引用而对列标进行绝对引用。如果公式所在单元格的位置改变，则相对引用的随之改变，而绝对引用的不会改变。其方法是在绝对引用的行号或列标前加“$”符号。 引用类型的转换。为将公式中用到的地址表示为绝对引用，需加上“$”符号。所以可以通过修改公式中“$”符号的位置、或删除“$”符号，来改变单元格的引用类型；也可按 F4 功能键，改变公式中单元格的引用类型。,第一节 EXCEL 运算公式,【案例 22】远华公司的职务工资数据见案例 21。根据该公司的分配方案，效益工资为职
9、务工资的 10%；住房补贴为职务工资的 5%；应发合计为职务工资、效益工资、交通补贴、住房补贴之和；养老保险金为应发合计的 5%；医疗保险金为职务工资、效益工资之和的 2%；扣款合计为养老保险金、医疗保险金、代扣税之和；实发合计为应发合计减扣款合计。要求 完成以上相关项目的计算（交通补贴、代扣税暂不计算）。,第一节 EXCEL 运算公式,【操作提示】 （1）效益工资的计算：选中 F3 单元格，然后输入“=”；单击一次 E3 单元格，则该单元格被相对引用到公式中；再输入“*10%”；公式输入完毕后按回车键，则 F3 单元格自动显示计算结果，上部编辑框中显示其公式为“=E3*10%”。如图 23
10、所示。 （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
11、）其他单元格的公式分别为：H3=E3*5%，J3=I3*5%，M3=J3+K3+L3，N3=I3M3。,返回,第一节 EXCEL 运算公式,（5）选定 F3:N3 单元格区域，如图 23 所示；然后将鼠标指向 N3 单元格右下角的填充柄，此时鼠标变为粗大的“+”字状，按下鼠标垂直向下拖动到第 14 行，然后释放鼠标；实现单元格公式的复制。Excel 自动计算出本表各单元格的数值。由于在公式中使用了单元格引用，若修改 E 列中某单元格的职务工资，则引用这一单元格的其他单元格的值也会随之改变。同时，由于第 3 行相关单元格的公式均是对其他单元格的相对引用，所以用填充柄自动复制的 F4:N14 区域
12、的单元公式，也会自动调整为对本行各单元格的引用，从而保证计算结果的正确性。,第一节 EXCEL 运算公式,四、公式返回的错误值 在输入公式时，有不符合要求的输入，就无法在 Excel 工作表的单元格中显示正常的运算结果，该单元格中将会出现提示性的错误值信息。了解这些错误信息的含义有助于用户检查、修改公式，直到公式正确。表 25 中列出了 Excel 中的错误信息及其含义。 【案例 23】某公司的产品销售额如表 26 所示，要求计算各产品的销售比重。,第一节 EXCEL 运算公式,【操作提示】A 产品25 800B 产品36 900C 产品 37 300合计100 000 （1）在工作表的 A1
13、、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=H
14、3/$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。,第一节 E
15、XCEL 运算公式,同理，H5 单元公式的分母为绝对引用 K3，所以自动填充后的 I5:K5 的公式是正确的；而 H4 单元公式为相对引用 K3 的值，自动填充后出现“分母为 0”的错。 （6）选定 A1:K6 单元格区域，单击格式工具栏的边框按钮中的下拉箭头，在其中选择“所有框线”选项，给工作表中的有效表格（即数据区域）加上边框线。 （7）单击工作表左上角行列交叉处的全选按钮，然后通过格式工具栏，设置字体。（8）选定 B3:B6，按下键盘上的 CTRL 键，再选定 H3:K3 单元区域；单击格式工具栏的千位分隔按钮、单击两次减少小数位数按钮。选定 C3:D6，按下键盘上的 CTRL键，再选定
16、 H4:K5 单元区域；单击格式工具栏的百分比按钮,第二节 EXCEL 函数,在 Excel中，函数实际上是一个预先定义的特定计算公式。 按照这个特定的计算公式对一个或多个参数进行计算，并得出一个或多个计算结果，叫做函数值。,第二节 EXCEL 函数,一、函数的使用与构成 1. 函数的使用。Excel 提供了财务函数、日期与时间函数、数学与三角函数、统计函数、查找与引用函数、数据库函数、文本函数、逻辑函数、信息函数九大类两百多个函数。使用这些函数不仅可以完成许多复杂的计算，而且还可以简化公式的繁杂程度。 【案例 24】某公司生产经营 46 种产品，2009 年各季度销售收入如表 27 所示。要求用函数计算各季度、各产品的合计收入（其他指标以后介绍）。,第二节 EXCEL 函数,（1）设计表格。录入 A 列和第 2 行的相关文字；输入 B3:G6 的已知金额；合并 A1:L1单元格区域；设置字体字号。如图 25 所示。 （2）常用工具栏求和法。操作方法如下： a. 选定 H3 单元格，单击常
