第4章 4 3电子表格

电子表格,2,电子表格,数据管理,3,电子表格,创建表格的工具,创造有效准确的工作表,利用公式实现简单计算,电子表格,4,电子表格是利用整行整列的数字创建真实情况的模型或表示。如消费额的流水账单就是一种电子表格，它是消费资金流出情况的数字表示。,电子表格软件,5,电子表格软件是创建表格的工具。它可以完成对数据的输入、检查、处理、存储等基本操作，可以将数据转换成各种形式的图表，有特定的数据处理功能。电子表格更容易使用并且正确率高于手工计算。电子表格软件能创建在屏幕上显示的工作表。例,电子表格软件,6,电子表格软件工作方式,某个单元格中的数值可由其它单元格中的公式操作获得。公式在后台工作，它能够指挥计算机如何将单元格中的内容用于计算。在单元格中输入简单的公式可以对数字进行加、减、乘、除运算。更复杂的公式几乎可以完成用户所能想象到的任何计算。下图举例说明了如何在电子表格中利用公式进行计算。工作表中任何一个单元格的内容被改变后，所有引用该单元格数值的公式都会被重新计算。,7,电子表格软件工作方式,8,这种自动重算的功能保证了在工作表中输入当前信息后，每个单元格中的结果仍是准确的。,模板应用,要创建有效准确的工作表，就必须理解涉及的计算和公式。大多数电子表格软件为预先设计的工作表提供了一些模板或向导，一些模板还可以通过在Web上得到。这些模板一般由专业人员设计，里面包含所有必要的文字和公式。使用模板时，只需要填入数值就可进行计算了。用户也可以在具体应用中创建适合自己需要的模板。,9,10,编辑电子表格,单元格的选定,数据的输入,自动填充数据,设置单元格条件格式,单元格的选定,11,将鼠标移动到窗口左侧的行标题，当鼠标指针变成黑色箭头时单击某一行号，即可选定该行，结合Shift键上下拖动鼠标或结合Ctrl键，可选定连续或不连续的多行。将鼠标移动到窗口上方的列标题，当鼠标指针变成黑色箭头时单击某一列标，即可选定该列，结合Shift键左右拖动鼠标或结合Ctrl键，可选定连续或不连续的多列。需要选定工作表中的全部单元格时，将鼠标移动到行标题与列标题交叉的空白区域，即可。,数据的输入,在单元格中输入数据，首先需要选定单元格使之成为当前单元格，然后再向其输入数据，所输入的数据将会显示在编辑栏和单元格中，最后确认输入。输入单元格的数据主要包括文本、数字、时间日期、逻辑值和公式，不同类型的数据有不同的输入方法。通常情况下数据录入方法比较简单，选中单元格后可以直接录入。但是特殊情况下录入数据需要进行特别处理。,12,自动填充数据,电子表格软件的自动填充功能为数值输入提供了便捷的方法，可以加快数据的输入，提高工作表的编辑效率。使用填充功能可以填充相同的数据，也可以填充数据序列。所谓数据序列，是指行或者列的数据呈有规律的变化趋势。,13,自动填充数据,实现自动填充时要使用填充柄，它是位于当前活动单元格或活动单元格区域右下角的黑色小方块，当鼠标指向填充柄时，鼠标指针将更改为黑十字形状，按住鼠标横向或纵向拖动填充柄，即可自动填充数据。填充相同的数据、填充有规律的数据、自定义填充序列,14,自定义填充序列,15,单元格的选择性粘贴,选择性粘贴是在复制单元格或单元格区域时，只需要复制其中特定的内容而不是全部内容所使用的命令。执行此操作时，先选定需要复制的单元格或单元格区域，在“开始”|“剪贴板”组中，单击“粘贴”下拉三角，选择“选择性粘贴”，打开“选择性粘贴”对话框，选择所需的选项，单击“确定”即可。,16,“选择性粘贴”对话框中的部分选项含义,公式：当复制公式时，单元格地址引用的结果将根据引用类型而变化，若要单元格地址引用不变，则使用绝对地址引用。数值：仅粘贴源数据的数值，不粘贴源数据的格式等。若单元格中是公式，则只粘贴公式计算后的结果。格式：复制格式到目标单元格，但不能粘贴单元格的有效性。,17,“选择性粘贴”对话框中的部分选项含义,运算区选项：粘贴时运算可对粘贴对象进行加减乘除运算。例如，A1单元格中的数值为5，B1单元格中的数值为6，复制A1单元格，选定B1单元格，执行“选择性粘贴”对话框中的“乘”运算，则B1单元格中的数值变成30。其余运算类推。转置：将复制区域的行转为列，列转为行，即将复制区域的最上一行数据显示在粘贴区域的最左列，而将复制区域的最左列显示在粘贴区域的最上一行。,18,设置单元格条件格式,条件格式主要包括五种默认的规则：突出显示单元格规则、项目选取规则、数据条、色阶和图标集。设置条件格式时，先选定要添加条件格式的单元格区域，单击“开始”|“样式”组中的“条件格式”按钮，在弹出的下拉列表中选择合适的条件格式选项，设置相应规则即可。,19,20,数据计算,公式,函数,运算符引用,函数的结构函数的参数,公式,在Excel中使用公式是计算数据以符号“=”为引导，后面接用于计算的公式。公式是可以实现执行计算、返回信息、操作其它单元格的内容或测试条件等功能的表达式。公式中可以包含函数、引用、运算符以及常数等组成元素。,运算符：公式中可使用的运算符包括算术运算符、关系运算符和文本运算符。引用：电子表格有相当灵活的引用方式，除了一般电子表格软件有的相对引用和绝对引用外，还有混合引用和三维引用。,21,函数,函数是电子表格处理软件预先定义好的公式，由函数名和一对括号括起来的参数表组成，它使用参数并按照特定的顺序进行计算。函数只在输入的公式中调用，它可以出现在公式的任何位置，有时一个公式就是一个函数调用。因此函数可以看成公式的特例。,22,函数,函数的结构函数都是由函数名称、参数和圆括号组成。其基本结构为：函数名称（参数1，参数2，参数n）。其中，函数名称指出了函数具有的功能，可提高函数的易读性。每个函数名称唯一标识一个函数；函数名称的后面是圆括号括起来的参数，如若一个函数有多个参数，参数之间需要用半角的逗号分隔开；参数的多少随函数定义来确定。,23,函数,函数的参数函数的参数是函数进行计算所必须的初始值。参数是函数中最复杂的组成部分，它规定了函数的运算对象、顺序或结构等。函数的参数可以是常量、逻辑值、单元格引用、错误值、数组，甚至可以是一个或多个函数。参数的类型和位置必须满足函数的语法要求，否则将返回错误的计算结果。,24,25,数据图表,图表类型,总结,图表的组成,为了形象直观的表现工作表中的数据，简洁揭示工作表的数据的变化规律。,柱形图、拆线图、饼图、条形图、面积图、XY散点图、股价图、曲面图、圆环图、气泡图、雷达图。,图表主要由图表区、绘图区、图表标题、数值轴、分类轴、网格线、数据系列和图例组成。,数据图表,图表的创建：可以根据工作表中的数据直接创建各种类型的图表。用户自由选取需要创建图表的数据区域，根据不同的需要选择所要插入的图表类型。下图为选择插入了柱状图类型。,26,数据图表,27,28,数据管理,又称数据清单管理对数据进行分析、管理具有规定的格式,29,数据管理,一般电子表格的另一个主要功能是对数据进行分析、管理，而数据清单恰恰可以帮助用户完成这些功能。因此，电子表格的数据管理也称为数据清单管理。对工作表中的数据进行排序、筛选、分类和汇总等操作一般都可以通过数据清单管理实现，一个学生信息的数据清单如下图所示。,30,数据管理,数据清单作为一种具有规定格式的表格，其第一行为标题行，设置列标题，第二行起为数据区，每一行数据称作一条记录。数据清单中的每一列应具有相同的数据类型，并且不允许有空白行和空白列，数据区中也不能有重复的记录。,31,32,数据管理,数据筛选,数据排序,数据分类汇总,数据排序,33,为了方便查看数据清单中的数据，对其进一步分析和管理，可按照某些列数据的升序或降序重新排列数据清单中记录的顺序。按升序排序时，电子表格一般默认使用如下规则：数值：按数字从最小的负数到最大的正数排序。,数据排序,34,文本：包括英文字母和汉字。英文字母按其对应的ASCII码值由小到大排序，即A，B，Z顺序排列，系统不区分大写和小写。汉字有两种排序方式，可以根据汉语拼音的字母顺序排列，例如“李四”与“王五”按拼音升序排序时，“李四”排在“王五”前面；也可以根据笔画排列，“王五”则排在“李四”前面逻辑值：False排在True前面。错误值和空格：所有错误值相等不分大小，空格排在最后。,35,数据排序,简单排序,组合排序,自定义排序,数据排序,36,（1）简单排序当只需要按数据清单中的某一列数据重新排列记录顺序时，可以只选定需要排序列中的任意一个单元格（或需要排序的整个单元格区域），使用“数据”-“排序”命令打开“排序”对话框设置排序依据及排序的次序即可。如图所示，对数据清单按总分降序排列。,数据排序,37,数据排序,38,数据排序,39,（2）组合排序按照一列数据进行排序，有时会遇到列中某些数据相同的情况，排序后它们会保持原始次序，为进一步对这些数据进行比较可采用多列数据的组合排序，即对数据清单中的数据按两个或两个以上的列数据进行排序。多列排序可使数据在第一关键字相同的情况下，按第二关键字排序；在第一关键字、第二关键字都相同的情况下，按第三关键字排序。,数据排序,40,组合排序与简单排序的方法类似排序时需要几个排序条件，就进行几次“添加条件”，依次设置排序顺序。例如，对数据清单按“总分”降序排序，分数相同的“折算后的平时成绩”降序排序，排序依据设置和排序结果如图所示。,数据排序,41,数据排序,42,数据排序,（3）自定义序列排序有时用户不希望按照电子表格按照已有规则对数据进行排序，而是按照用户指定的特殊次序进行排列，即可通过自定义序列排序实现。自定义序列排序需先添加排序规则方可按指定序列排序，如图所示。,43,数据排序,44,数据排序,45,数据筛选,46,需要实现快速查找数据清单中的数据，可使用电子表格的数据筛选功能，以便快速查找出符合条件的数据。当筛选一个数据清单时，电子表格只显示符合指定条件的数据，把不符合条件的数据隐藏起来。一旦筛选条件被撤销，隐藏的数据就重新被显示。筛选方式有三种，自动筛选，自定义筛选和高级筛选。,47,数据筛选,自动筛选,自定义筛选,高级筛选,数据筛选,48,（1）自动筛选自动筛选适用于简单条件筛选，也是筛选方法中最常用的一种。使用筛选功能后，根据筛选条件设置相应字段后下拉菜单中需要筛选的选项，即可快速的在数据清单中看到筛选出的数据结果。如果同时对多列数据设置筛选条件，逐一设置相应字段后需要筛选的选项，这些筛选条件之间是“逻辑与”的关系。,数据筛选,49,再次使用筛选功能时，即可取消自动筛选。如果取消对某一列进行的筛选，可以通过设置相应字段后下拉菜单中需要筛选的选项为“全选”来实现。例如，查看电力学院成绩等于或高于80分全部学生信息。,数据筛选,50,图4.49,数据筛选,51,（2）自定义筛选在自动筛选的基础上，对筛选条件设置筛选范围，进行自定义筛选。使用相应字段名后下拉菜单中的文本筛选或数字筛选功能，在“自定义自动筛选方式”对话框中进一步设置筛选条件，在条件范围内的数据都会被筛选出来。不同类型的数据，自定义筛选可以设置的条件也不同。对文本进行筛选时，可以筛选出与设置文本相同、不同或者是否包含该文本的数据。对数值进行筛选时，可以筛选出大于、小于和介于某个范围的数据。,数据筛选,52,自定义筛选数据时，可以结合通配符“*”和“？”进行筛选，其中“*”表示任意多个字符，而“？”表示任意单个字符。,数据筛选,53,（3）高级筛选针对筛选条件复杂的情况，可以使用高级筛选功能处理。使用高级筛选功能进行筛选时，在工作表中使用原数据外的其他区域输入要筛选的一个或多个条件，将筛选的结果显示在原数据区域或当前工作表的其他位置，甚至显示在另一个工作表中。,数据筛选,54,在“高级筛选”对话框中，显示筛选数据的方式有两种，一种是在工作表的原有区域显示筛选结果，选择“在原有区域显示筛选结果”单选按钮；另一种是将筛选结果复制到其他位置，选择“将筛选结果复制到其他位置”单选按钮，在“复制到”文本框中输入显示区域左上角单元格的地址。“列表区域”文本框用于指定要筛选的数据区域，一般为包含列标题行的整个数据表，可以直接输入数据区域地址，也可以单击“列表区域”文本框右侧的“折叠”按钮，拖动鼠标选择数据区域地址。,数据分类汇总,55,“条件区域”文本框用于指定设置的筛选条件区域，可以直接输入条件区域地址，也可以单击“条件区域”文本框右侧的“折叠”按钮，拖动鼠标选择数据区域地址。使用分类汇总对数据清单数据进行分析，不仅能使数据按指定数据列进行排序分类还可以按分类进行汇总，提供清晰、有价值的分析显示结果。,数据分类汇总,56,57,数据筛选,分类汇总,删除分类汇总,数据分类汇总,58,（1）分类汇总分类汇总，既可以统计按指定数据列对数据清单数据分类后同类记录的记录条数，也可以对为数值型的同类记录进行求和、求平均值、最大值、最小值等运算。进行分类汇总前，必须先对需要分类的数据列排序，以便把同一类记录汇合在一起，然后再对数值型数据列进行汇总计算。排序后，使用“数据”-“分类汇总”命令打开“分类汇总”对话框，依次设置对数据进行分类的数据列字段、需要汇总的字段及汇总的具体方式即可。,数据分类汇总,59,分类汇总后的数据是分级显示的，通过数据清单左侧分级显示区顶端的“1”、“2”、“3”按钮可以对分类后的数据显示进行控制，默认数据按3级显示。“1”显示分级显示列标题和总计结果；“2”显示分级显示列标题、各分类汇总结果和总计结果；“3”显示分级显示所有数据。分级显示区的“+”、“-”按钮则用于设置隐藏和显示分级数据。,数据分类汇总,60,（2）删除分类汇总查看分类汇总的结果后，当不再需要把数据清单中数据以这种方式显示时，可以删除分类汇总，把数据清单恢复到原状。,案例分析（一）：现金日记账,内容及数据来源如下：某公司从2010年1月1日开始发生了如下的业务：2010年1月1日从上年结转过来的现金余额，其中销售部门是2000元，一车间是4000元。2010年1月1日销售部门出售由销售部门使用的废旧包装物200元。2010年1月2日暂付一车间差旅费300元。2010年1月3日销售部门销售产品2000元。,61,案例分析（一）：现金日记账,通过直接输入的方法将上述内容输入到工作表中。要求为工作表中各个字段设置数据有效性，具体要求是:所有的日期按照2001-3-14”的方法来表示。借方、贷方和余额数据必须保留两位小数，并定义为“会计专用”格式。,62,案例分析（一）：现金日记账,完成上述内容的操作过程如下：（1）设计现金日记账界面现金日记账的数据构成就是一张简单的二维表格。它由行和列构成。第一行是字段名，其余各行是记录，它是工作表中包含相关数据的一系列数据行。打开Excel2010，将Sheet1重命名为“现金日记账”。选中A1单元格，在A1到G1单元格区域内依次输入：日期、编号、部门、摘要、借方、贷方、余额字段名。,63,案例分析（一）：现金日记账,按住键盘上的Ctrl键的同时，选中E、F、G列，调整E、F、G列中任意一列的宽度，使得E,F,G列的宽度相等。选中A1:G1区域，选择“开始”选项卡，执行“对齐方式”组中的“居中”命令，使选中区域的文字以居中对齐方式排列。,64,案例分析（一）：现金日记账,65,设计完成后的表格如图,案例分析（一）：现金日记账,定义各个字段的数据类型选中A列，右击鼠标，在快捷菜单中选择“设置单元格格式”。,66,案例分析（一）：现金日记账,打开“设置单元格格式”对话框，如图所示。单击“数字”标签。在分类中选择“日期”，选择类型为“*2001/3/14，单击“确定”按钮，完成A列数据类型的确定。选中B列，按上面的方法设置单元格格式为“数值”，小数位数设置为0。选中C列和D列，设置单元格格式为“文本”。选中E列、F列和G列，设置单元格格式为“会计专用”，小数位数设置为2，货币符号设置为无。,67,案例分析（一）：现金日记账,设置数据有效性在第I列中输入如图所示的内容，完成序列内容的设置。选中C列，单击“数据”选项卡，执行“数据工具”组内的“数据有效性”命令，打开“数据有效性”对活框。在如图所示的数据有效性对话框中，选择“设置”标签。在“允许”下拉列表中选择“序列”。单击“来源”后面的折叠按钮，选中I2:I5单元格区域，完成对C列数据来源的设定。,68,案例分析（一）：现金日记账,单击“数据有效性”对话框中的“出错警告”标签，如图所示。在“样式”下拉列表中选择“停止”，在“标题”文本框中输入“部门信息输入错误”，在“错误信息”下的文本框中输入出错后的提示性语句，单击“确定”按钮，完成对“出错警告”选项卡的设置。说明：警告分为3种。分别是停止、警告和信息。停止表示一旦发生了错误输入，必须修改为正确的值才可以完成输入，在该种情况下是无法输入序列以外的值的。如果为警告样式或者是信息样式，使用者可以强制输入序列以外的值。,69,案例分析（一）：现金日记账,选中A列，设置其“数据有效性”允许为“日期”，输入开始日期2010/1/1和结束日期2010/12/31。单击“确定”按钮，完成日期字段的设置。选中B列，设置其“数据有效性”允许为“整数”，最小值为0，最大值为100。单击“确定”按钮，完成编号字段的设置。选中E、F两列，设置其“数据有效性”允许为“小数”，在“数据”下拉列表中选择“介于”。在“最小值”中输入0，在“最大值”中输入1000000。单击“确定”按钮，完成借贷方发生额的数据有效性设定。,70,案例分析（一）：现金日记账,公式设置选中G2单元格，在编辑栏内输入公式“=E2”，完成第一行记录余额的计算。选中G3单元格，在编辑栏内输入公式“=G2+E3-F3”，完成第二行记录余额的计算。,71,案例分析（一）：利用函数和公式建立现金日记账,内容及数据来源将日常使用的现金日记账账簿格式绘制到工作表中，并且该工作表要能够实现以下的操作：能够在其中输入数据，这些数据可以完整地反映现金日记账的信息。能够反映现金余额的借贷方向。能够按月计算出本月借方累计发生额、本月贷方累计发生额、本年借方累计发生额和本年贷方累计发生额，并计算出最终的现金余额。,72,案例分析（一）：利用函数和公式建立现金日记账,部分完成上述内容的操作如下：（1）绘制表格界面本案例所采用的现金日记账的外观就是常见的现金日记账账页的样式。在前一个现金日记账例子文件中，将Sheet2重命名为“现金日记账2”。,73,案例分析（一）：利用函数和公式建立现金日记账,在工作表内输入如图所示的现金日记账信息，完成界面的设置。（提示：单击“视图”选项卡的“显示”窗格中“网格线”可以去掉Excel表中的那些灰线表格线）,74,案例分析（一）：利用函数和公式建立现金日记账,选中F、G和I列，右击鼠标，从打开的快捷菜单中执行“设置单元格格式”选项，将其类型设置为会计专用，小数位数设置为2，货币符号设置为无，单击“确定”按钮，完成对指定列的数字格式的设置。在E10中输入“本月合计”。在E11中输入“本年累计”讨论：对“现金日记账2”表中的数据通过表间单元格引用方法，引用“现金日记账”表中的摘要、借方、贷方数据。,75,案例分析（一）：利用函数和公式建立现金日记账,公式设置选中B10单元格，在编辑栏内输入=COUNTIF($E$5:E10,本月合计)选中B11单元格，输入=COUNTIF($E$5:E11,本月合计)选中F10单元格，在编辑栏内输入=SUBTOTAL(9,F6:F9)，完成本月借方发生额的计算。=SUBTOTAL(9,G6:G9)，完成本月贷方发生额的计算。,76,案例分析（一）：利用函数和公式建立现金日记账,选中G10单元格，在编辑栏内输入“=SUBTOTAL(9,G6:G9)”,完成本月贷方发生额的计算。选中F11单元格，在编辑栏内输入=SUBTOTAL(9,$F$5:$F10)，完成本年借方累计的计算。选中G11单元格，在编辑栏内输入=SUBTOTAL(9,$G$5:G10)，完成本年贷方累计的计算。,77,案例分析（一）：利用函数和公式建立现金日记账,选中H11,输入=IF(IF($H$5=贷,-1*$I$5,$I$5)+SUBTOTAL(9,$F$5:F11)-SUBTOTAL(9,$G$5:G11)0,借,IF(IF($H$5=贷,-1*$I$5,$I$5)+SUBTOTAL(9,$F$5:F11)-SUBTOTAL(9,$G$5:G11)，完成余额方向的确定。选中I11单元格输入=ABS($I$5+F11+G11)，完成余额的计算。,78,案例分析（一）：利用函数和公式建立现金日记账,持续记账上述定义的公式只是针对1月份的情况进行的，而且1月份所预留输入的记录行数是非常有限的，因此用户面临的一个问题就是如何添加记录行数，并要为1月份以后的各月准备添加记录的区域。要添加新的记账，可以通过复制前面的记录，再粘贴的方式实现，切忌不要断开本月合计和本年累计这两行（这两行之间不能插入新行）。,79,案例分析（一）：利用函数和公式建立现金日记账,通过上述方法获得1、2月份的现金日记账如图所示。,80,公式设置,案例分析（二）：工资管理,工资管理系统与企业的管理方式和工资构成状况是相关的。一般来说员工的工资包含基本工资、工龄工资、考勤工资以及绩效工资等。计算工资时还要考虑到企业所代扣的企业员工的个人所得税。由于工资是要发放给个人的，因此还需要为个人发放工资条。,81,案例分析（二）：工资管理,1.建立简易工作表本案例完成的任务是：建立工资管理的基本数据表，最终通过这些基本数据表格形成工资表。内容及数据来源建立简易工资管理系统的首要任务就是建立员工基本信息表、考勤表和业绩工作表。通过这些基本信息最终进行数据的分析和汇总。,82,案例分析（二）：工资管理,本案例所建立的表格具体要求如下:“员工基本信息表”包括年份、月份、姓名、员工编号、id、性别、部门、学历、工作时间、基本工资和工龄工资等字段。“考勤表”包括年份、月份、姓名、员工编号、id、部门、迟到早退、旷工、加班、扣除工资、加班费和总计等字段。,83,案例分析（二）：工资管理,“业绩表”包括年份、月份、姓名、员工编号、id、部门、数量和业绩工资等字段。“工资表”包括姓名、id、部门、基本工资、工龄工资、业绩工资、加班费、应发数、应纳税所得、应缴所得税、考勤扣费和实发工资等字段。,84,案例分析（二）：工资管理,工资的计算方式是：员工的基本工资是和员工学历挂钩，员工的工龄工资是和工龄相挂钩。假设该企业员工基本工资原则是:专科以下学历的基本工资为2000元，专科学历基本工资为2600元，本科学历为3200元，硕士研究生学历为3800元，博士研究生学历为4400元。工龄工资的政策是每工作一个整年为100元。工龄设定方法是从加入企业开始，到本月1日为止的整年数。,85,案例分析（二）：工资管理,公司的考勤奖惩办法规定迟到或者早退每次扣除50元，旷工每次扣除200元，加班每次奖励100元公司的业绩奖惩办法是对按照销售或者生产的数量和价格乘积的5%提成，每单位数量的计算标准是300元。非生产和销售部门的业绩工资按照200单位固定值计算。,86,案例分析（二）：工资管理,完成上述内容的操作过程如下：（1）建立员工基本信息表结构员工基本信息包括了员工的基本情况，例如员工的姓名、编号和学历等各方面的内容。工资管理系统中的员工基本信息数据可以从人事管理部门获得，也可以由用户手工录入数据。,87,案例分析（二）：工资管理,员工基本工资信息主要反映了员工的基本情况和基本工资。员工基本信息中的基本工资等信息也是工资表中计算总工资的一部分。员工基本信息表的创建过程如下所示：打开Excel2010，将“Sheet1”工作表重命名“员工基本信息”，完成建立“员工基本信息”工作表。,88,案例分析（二）：工资管理,89,在“员工基本信息”工作表中从A1单元格位置开始输入年份、月份、姓名、员工编号、id、性别、部门、学历、工作时间、基本工资和工龄工资等字段，完成除E、J、K列的其它字段内容的输入。结果如图,案例分析（二）：工资管理,90,选中I列，右击执行“设置单元格格式”命令，打开“设置单元格格式”对话框。单击“数字”标签，选择数字格式为“日期”，将“类型”设置为“2001/3/14”。完成对I列的数据格式设定。选中J列到K列区域，右击执行“设置单元格格式”命令，打开“设置单元格格式”对话框，单击“数字”标签，选择数字格式为“会计专用”，小数位数设置为2，无货币符合类型，完成对基本工资和工龄工资数据的设定。,案例分析（二）：工资管理,（2）设置员工基本信息表公式为了方便数据在各个工作表中相互引用和查询，需要各个表中可以通用的公共字段。本案例使用了唯一的编号字段“id，该字段是通过公式自动得出的。id字段中包含的信息是员工编号和年份及月份信息的综合以确保该id值的唯一性。设置“员工基本信息”工作表公式的具体操作步骤如下：,91,案例分析（二）：工资管理,单击E2单元格，在编辑栏内输入“=D2&-&A2&-&B2”，完成员工id的设置。单击J2单元格，在编辑栏内输入“=2000+(MATCH(H2,专科以下,专科,本科,硕士,博士,0)-1)*600”，完成基本工资的计算。单击K2单元格，在编辑栏内输入公式“=DATEDIF(I2,DATE(A2,B2,1),y)*100”，完成工龄工资的计算。（注：工龄每年加100）,92,案例分析（二）：工资管理,输入如图所示的数据，但是不包括设置有公式的列（E、J、K列）。然后选中第2行中有公式的单元格，向下拖动，完成公式在各行内的复制。选中第2行到第8行，右击鼠标，复制这些行的数据，选中A9单元格，右击鼠标，执行“粘贴”命令，完成将1月份的数据复制到2月份中。,93,案例分析（二）：工资管理,94,更改上述复制的数据，将月份信息改为2，如果员工信息存在变动，就更改这些员工信息，添加新员工信息，删除已离职的员工信息。结果如图,案例分析（二）：工资管理,（3）设置考勤表结构及公式考勤信息登记了员工的缺勤数据和加班数据，缺勤会扣除工资，加班会产生加班费。记录这部分内容的表格就构成了考勤表。在“考勤记录”工作表中，id字段联系到了员工基本信息表中的id字段，通过该字段可以提取到部门信息。考勤记录涉及到的考勤情况表等原始数据会在工资计算前送达财务部门，财务部门根据这些原始记录汇总到“考勤记录”工作表中，财务部门针对考勤情况计算的工资过程如下所示。,95,案例分析（二）：工资管理,将Sheet2”工作表重命名“考勤记录”，建立“考勤记录”工作表。在“考勤记录”工作表中从A1单元格位置年份、月份、姓名、员工编号、id、部门、迟到早退、旷工、加班、扣除工资、加班费和总计等字段，完成除E、F、J、K、L字段内容的输入，结果如图,96,案例分析（二）：工资管理,单击E2单元格，在编辑栏内输入公式“=D2&-&A2&-&B2”，完成id字段的设置。单击F2单元格，在编辑栏内输入公式“=VLOOKUP(E2,员工基本信息!E:H,2,FALSE)”，完成从员工信息表中提取部门信息的工作。单击J2单元格，在编辑栏内输入公式“=G2*50+H2*200”，完成对扣除工资的计算。,97,案例分析（二）：工资管理,单击K2单元格，在编辑栏内输入公式“=I2*100”，完成对加班工资的计算。单击L2单元格，在编辑栏内输入公式“=K2-J2”，完成对考勤工资的计算。输入图3所示的数据，但是不包括设置有公式的列。然后选中第2行中有公式的单元格，向下拖动，完成公式在各行内的复制。结果如图,98,案例分析（二）：工资管理,（4）设置业绩表结构及公式企业会对业绩优秀的人员给子奖励，业绩工资的计算通常包括业绩的数量和金额。最常见的是在计件工资条件下，企业设计一个较低的保底工资作为基本工资，然后根据生产情况来计算和计件提成相结合的业绩工资，用这两部分作为企业员工的总工资。当企业存在着业绩激励制度的时候，就可以通过设计业绩表来反映这部分工资支出。,99,案例分析（二）：工资管理,将“Sheet3”工作表重命名“业绩”，建立“业绩”工作表。在“业绩”工作表中从A1单元格位置开始输入年份、月份、姓名、员工编号、id、部门、数量和业绩工资等字段，完成字段输入，结果如图,100,案例分析（二）：工资管理,选中G列到H列，右击执行设置单元格格式命令，选择数字格式为“会计专用”，小数位数设置为2，无货币符合类型，完成对数量和业绩的单元格设置。,101,案例分析（二）：工资管理,单击E2单元格，在编辑栏内输入公式“=D2&-&A2&-&B2”，完成id字段的设置。说明：id字段的作用是为了便于提取“员工基本信息”工作表中的信息，也便于“工资表”工作表提取业绩工资。单击F2单元格，在编辑栏内输入公式“=VLOOKUP(E2,员工基本信息!E:H,2,FALSE)”，完成从员工信息表中提取部门信息。,102,案例分析（二）：工资管理,单击H2单元格，在编辑栏内输入公式“=ROUND(G2*300*0.05,2)”，完成业绩工资的计算。说明：ROUND函数将数字四舍五入到指定的位数。例如，如果单元格A1包含23.7825，而且您想要将此数值舍入到两个小数位数，可以使用以下公式：=ROUND(A1,2)，此函数的结果为23.78。,103,案例分析（二）：工资管理,104,在工作表内输入图5所示的数据，但是不包括设置有公式的列。然后选中第2行中有公式的单元格，向下拖动，完成公式在各行内的复制。结果如图,案例分析（二）：建立员工工资表、生成工资条,105