Office信息管理方案

《Office信息管理方案》由会员分享，可在线阅读，更多相关《Office信息管理方案（20页珍藏版）》请在金锄头文库上搜索。

1、当前文章所在位置：附录 - 高级办公应用 Excel 2007信息管理方案大全信息管理方案大全 微软Office产品专家 陈秀峰 (电脑报2006年合订本附录) 众所周知，Excel是一款功能非常强大的电子表格软件，充分地利用它，可以帮助 我们编辑处理工作中很多复杂的表格，快速统计相关的数据，大大提高我们的工作效 率。下面，我们用五个工作中经常用到的表格实例，来全面介绍一下Excel2007的数据 和信息的管理技巧。 一、工资管理方案 工资表是我们工作中最为常见的一种表格，利用Excel来处理工资表，在提高处理 效率的前提下，可以确保工资数据的准确性。 1.工资表的建立工资表的建立 (1)输入

2、列标题和序号输入列标题和序号 启动Excel 2007，在第2行，自A2单元格开始，依次输入列标题的字符， “序号、 姓名、基本工资如图1所示。 图1 为了能让“工龄工资”自动调整，我们特意增加了“参加工作时间”一列。 在A3、A4单元格中分别输入序号“1、2”，然后同时选中A3、A4单元格，将鼠标移 至A4单元格右下角成“细黑十字”状时，接住左键向下拖拉至最后一位员工所对应的单 元格，即可快速输入“序号”。 页码，1/20中国计算机年鉴 20062010-7-27file:/C:Documents and SettingsAdministratorLocal SettingsTemp.注意：

3、这种“细黑十字”状，我们通常称之为“填充柄”状。利用“填充柄”可以 快速输入大量的内置序列（如日期序列、时间序列等），快速输入“自定义序列”，快 速复制公式 (2)输入工资表标题输入工资表标题 同时选中工资表第一行标题单元格区域（此处为A1：M1），点击“开始”菜单中的 “合并及居中”按钮，将其合并成一个单元格，并输入公式：=“晓风公司“&YEAR(TODAY ()&“年“&MONTH(TODAY()&“月工资表“。 注意：输入这样的工资表标题，其中的时间会随系统时间的变化而自动调整（参 见图1）。函数“YEAR”返回日期参数对应“年份”；函数“MONTH”返回日期参数对 应“月份”；函数“T

4、ODAY（）”返回系统当前日期（不需要指定参数）。 (3)格式化表格元素格式化表格元素 设置标题：选中A1单元格，在“开始”菜单中将“字体”设置为“华文新魏”（或 其他字体），将“字号”设置为“24” 添加边框：选中表格所有数据区域（标题行除外），在“开始”菜单中，点击“边 框”按钮旁的下拉按钮，在随后弹出的下拉列表中选择“所有框线”选项，为表格添加 上边框。 (4)固定表头的行和列固定表头的行和列 为了保证输入数据的准确性，我们通常将“表标题”行、“列标题”行、“序号” 列及“姓名”列固定起来：选中C3单元格，切换到“视图”菜单下，单击其中的“冻结 窗格”按钮，在随后弹出的下拉菜单中，选择“

5、冻结窗格”选项。 经过这样的设置后，无论我们如何拉动“垂直滚动条”或者“水平滚动条”，第 1、2行和第1、2列，始终显示在当前窗口中（参见图1）。 至此，一份规范的工资表就制作完成了，取名保存（工资.xlsx），并向其中输入 工资数据即可。 2. 计算工龄工资计算工龄工资 选中F3单元格，输入公式：=DATEDIF(C3,TODAY(),“Y“)*3，并按下“Enter”键确 认，第一位员工的“工龄工资”就计算出来了。 注意：表格中工龄工资的标准为每年3元，请根据各自的实际情况进行调整。 “DATEDIF”是一个隐藏函数，通过“函数向导”是不能输入的，只能通过手动直接输 入。如果输入公式：=D

6、ATEDIF(开始日期参数,结束日期参数,“M“)，则返回两个日期 间隔的“月数”；如果输入公式：=DATEDIF(开始日期参数,结束日期参数,“D“)，则返 回两个日期间隔的“天数”。 用“填充柄”将F3单元格中的公式拖拉复制到其他员工所对应的F列单元格中，即 可完成“工龄工资”的计算，并且“工龄工资”会随系统时间的变化而自动调整（系统 时间一定要设置准确）。 3. 计算应发工资计算应发工资 同时选中D3至H32单元格区域（此处，我们假定员工为30人），单击“开始”菜单 中的“求和”按钮，即可计算出“应发工资”。 4.计算公积金计算公积金 页码，2/20中国计算机年鉴 20062010-7-

7、27file:/C:Documents and SettingsAdministratorLocal SettingsTemp.“公积金”通常按“应发工资”的一定比例计算的，我们这里假定为12%，我们先 在I3单元格中输入公式：=INT(H3*12%)，然后用“填充柄”将其复制到下面对应的单元 格区域中就可以了。 注意：这里为了防止出现“角”、“分”数值，我们用“INT”函数对“公积金” 的计算结果进行了“取整”处理。 5.计算所得税计算所得税 由于所得税的计算比较复杂，直接用Excel内置的函数来计算非常麻烦，我们通过 一个自定义函数来进行计算。 (1)自定义函数自定义函数 按下“Alt+F

8、11”组合键，进入VBA编辑窗口，在左侧的“工资资源管理器”中选中 “VBAProject(工资.xlsx)”选项，执行“插入模块”命令，插入一个新模块（“模 块1”），将下面的代码输入到右边的代码编辑窗口中： Function sds(gze, qze) nse = gze qze Select Case nse / 100 Case 0 To 5 sds = nse * 0.05 Case 5 To 20 sds = nse * 0.1 - 25 Case 20 To 50 sds = nse * 0.15 - 125 Case 50 To 200 sds = nse * 0.2 - 37

9、5 Case 200 To 400 sds = nse * 0.25 - 1375 Case 400 To 600 sds = nse * 0.3 - 3375 Case 600 To 800 sds = nse * 0.35 - 6375 Case 800 To 1000 sds = nse * 0.4 - 10375 Case Is 1000 sds = nse * 0.45 - 15375 End Select End Function 输入完成后，关闭窗口返回。 注意：自定义函数的VBA结构为“FunctionEnd Function”。上述代码中 “sds”、“gze”、“nse”

10、、“qze”分别代表为“自定义函数名称”、“全月总收 入”、“全月应纳税所得额”和“起征金额”（目前为1600）。这些名称代码，大家可 以自行修改。使用了VBA后，需要将工作簿文档“另存为”“Excel启用宏的工作簿页码，3/20中国计算机年鉴 20062010-7-27file:/C:Documents and SettingsAdministratorLocal SettingsTemp.（*.xlsm）”格式的文档（在“另存为”对话框的“文件类型”中调整），如果继续保 存为“*.xlsx”格式，则会让VBA代码丢失。 (2)计算所得税计算所得税 先在K3单元格中输入公式：=INT(SDS

11、(H3,1600)（用INT函数取整），然后用“填 充柄”将其复制到下面对应的单元格区域中就可以了。 注意：调用自定义函数同调用内置函数的方法完全一样，可以用“函数向导”来输 入函数式，也可以手动直接输入函数式。 6.计算实发工资计算实发工资 先在L3单元格中输入公式：=H3-SUM(I3:K3)，然后用“填充柄”将其复制到下面对 应的单元格区域中就可以了。 7.制作工资条制作工资条 切换到Sheet2工作表中，在A1单元格中输入公式：=IF(MOD(ROW(),3)=1,OFFSET (Sheet1!A$2:A$32,0,0,1,1),IF(MOD(ROW(),3)=2,OFFSET(She

12、et1!A$2:A$32,(ROW() +1)/3,0,1,1),“)，然后用“填充柄”将此公式复制到A1至L90单元格区域中，调整好 行高和列宽，工资条制作完成，如图2所示。 图2 注意：函数“MOD”返回两个数值相除的余数；函数“ROW（）”返回当前行序数； 函数“OFFSET”返回指定区域偏移若干行、列后对应单元格内的数据。“IF”函数是一 个逻辑函数，判断结果为“真”时，返回指定的数据，判断结果为假时，返回另一个指 定的数据。 8.打印工资表（条）打印工资表（条） 在打印工资表（Sheet1）、工资条（Sheet2）表格时，我们都不希望将“参加工作 时间”列打印出来，先将其隐藏起来：选

13、中“参加工作时间”列，右击鼠标，在随后弹页码，4/20中国计算机年鉴 20062010-7-27file:/C:Documents and SettingsAdministratorLocal SettingsTemp.出的快捷菜单中，选择“隐藏”选项。然后将相应的工作表直接打印出来就OK了（工资 条表格打印出来后，需要裁剪后分发给职工）。 二、员工档案管理方案 我们这里所说的档案是指员工基本情况档案，通过Excel来实现员工基本情况的统 计，包括年龄、性别、学历和职称人数等要素的统计及员工简历表的打印等内容。 1.员工基本情况数据表的建立员工基本情况数据表的建立 (1)输入列标题和序号输入列

14、标题和序号 启动Excel2007，按照上面工资表的建立方法，输入序号和列标题。 注意：有时候，我们为了方便对数据的统计，通过增加过渡列（行），先对基本数 据进行预处理后，再进一步完成复杂的统计。此处的“年龄段”列就是一个过渡列。 (2)年龄和年龄段数据的输入年龄和年龄段数据的输入 在输入了“出生时间”的数据后，我们就可以通过函数来计算出员工的年龄和所在 的年龄段： 在G2单元格中输入公式：=DATEDIF(F2,TODAY(),“Y“)，用于计算第一位员工的动态 年龄；在H2单元格中输入公式：H2=IF(G2=60,6,IF(G2=50,5,IF(G2=40,4,IF (G2=30,3,IF

15、(G2=20,2,1)，用于确定第一位员工的“年龄段”；然后用“填充 柄”将上述公式复制到下面的单元格区域中，完成其他员工的年龄和年龄数据的填充。 注意：逻辑函数“IF”的语法结构：=IF (logical_test,value_if_true,value_if_false)，其中“logical_test”参数通常是 一个判断表达式，“value_if_true”为逻辑“真”时的返回数据， “value_if_false” 为逻辑“假”时的返回数据。在本例中的我们进一步用后面的 “IF”函数作为前面“IF”函数的参数，这种表达方法通常称之为“函数的嵌套”，用 以达到多重判断的目的。函数的嵌套

16、最多只能有七层。我们这里按10年确定一个年龄段，具体的划分依据请根据实际情况确定，并修改公式中相应的数值。 (3)称职和学历的输入称职和学历的输入 当手动直接输入“学历”和“职称”等数据时，同一类型输入的字符可能不一样 （如“大学”和“本科”等），这给后面的数据统计带来错误。为了解决这一问题，我 们可以利用“数据有效性”建立一个输入元素的下拉列表，供操作者选择输入。 选中需要输入员工“学历”的单元格区域（此处假定员有为100人，如I2： I115），切换到“数据”菜单中，单击“数据有效性”按钮，在随后出现的下拉列表 中，选择“数据有效性”选项， 单击“允许”右侧的下拉按钮，在随后出现的下拉列表中，选择“序列”选项，然 后将“学历”元素“博士,硕士,大学,大专,中专,高中,初中,其他”（注意：每个元素 间要用英文状态下的逗号分隔开）输入到“来源”下面的方框中，输入完成后，按下 “确定”按钮返回。 按照上面的操作，建立起“职称”数据