计算机典型案例 6页

行政管理典型案例一、制作员工基本情况明细表1.知识要点：l设置数据有效性l几个常用函数MID（）函数取“子函数”比如：MID（C1，3，2）当C1单元格的值为43567时,MID（C1，3，2）返回值为56IF（）函数条件函数比如：IF（C1<60,“不及格”,“及格”）TRUNC（）函数取整函数比如：TRUNC（5.6）=5DATEDIF（）函数计算两个日期相差的年份比如：DATEIF（B2，C2，“Y”）TODAY（）函数返回今天的日期，比如：TODAY（）=现在的日期2.基本框架。并输入“编号”、“姓名”的具体内容。文本与数值3.为“部门”、“职位”设置有效数据序列（数据|数据工具|数据有效性|设置|序列|来源……英文逗号隔开！）部门：软件开发部,图书开发部,基础部,财务部,人事部职位：部门经理,开发工程师,程序员,职员,文员4.为“身份证号码”、“联系电话”设置有效位数注意：身份证号码设为“文本”格式！（数据|数据工具|数据有效性|设置|文本长度|等于|18……提示信息）（数据|数据工具|数据有效性|设置|文本长度|等于|11……提示信息）5.为“入职时间”设置日期格式（右击|设置单元格格式……）6.根据“身份证号码”提取“性别”、“出生日期”、“年龄”性别:（18位身份证号的倒数第2位是偶数（女）、奇数（男））E3=IF（MID（H3,17,1）/2=TRUNC(MID（H3,17,1）/2),“女”，“男”）注意：设置出生日期：（18位身份证号的第7-10位表示年、11-12月、13-14日）G3=MID（H3，7，4）&＂年＂&MID(H3,11,2)&＂月＂&MID(H3,13,2)&＂日＂注意：出生日期设为“日期”格式！XXXX年XX月XX日年龄：F3=DATEDIF（$G3，TODAY（），＂Y＂）7.格式化工作表A1—L1：合并居中、华文新魏、字号18、行高302—32行：行高15、居中选中表格（不含表头）—样式—套中表格样式—中等深浅2（表包含标题）表工具—工具—转换为区域6\n二、利用COUNTIF()函数统计员工人数1.知识要点：l数据的筛选和排序l常用函数COUNTIF()函数2.女员工人数3.男员工人数4.各部门人数三、制作员工生日提醒表1.知识要点：l条件格式l常用函数MONTH()函数返回日期的月份，比如MONTH（G6）DAY()函数返回日期的天数，比如DAY(G6)2.将当月过生日的员工突出显示选择A3：F32开始|条件格式|新建规则|使用公式确定要设置格式的单元格|……=MONTH(TODAY())=MONTH($G3)（当前月份与G列中相同的月份）3.将当日过生日的员工突出显示=AND(MONTH(TODAY())=MONTH($G3),DAY(TODAY())=DAY($G3))（月、日相同）工资管理典型案例一、制作员工考勤情况表操作目录:2.1.2使用VLOOKUP函数自动输入员工姓名、部门及职位VLOOKUP（lookup_Value,table_array,col_index_num,range_lookup）搜索函数参数说明如下:lookup_Value:表示要在table_array表格第1列中查找的数值。lookup_Value可以是数值，也可以是对单元格地址的引用。table_array：表示由多列数据组成的一个表格区域。可使用对区域或区域名称的引用。这个表格区域第1列的值是由lookup_Value搜索的值。col_index_num：表示table_array表格区域中待返回的列序号。col_index_num为1时，返回table_array表格区域第1列中的数值，col_index_num为2时，返回table_array表格区域第2列中的数值，以此类推。range_lookup：值为0和10表示只寻找精确匹配值，1表示寻找精确匹配值或近似匹配值2.1.3录入请假数据6\n2.1.4使用DATEDIF函数计算员工的入职年限DATEDIF（）函数计算两个日期相差的年份比如：DATEIF（B2，C2，"Y"）入职年限：从入职时间到当前日期前一年的年数。参考公式：在G3单元格输入公式：=DATEDIF(F3,today(),"Y")-12.1.5使用IF函数计算员工的本年年假如果入职年限<1则年假为0如果入职年限>=1且<=5则年假为3天如果入职年限>5年，则每增加一年，就增加一天的年假。参考公式：在H3单元格输入公式：=if(G3>=1,if(G3<=5,3,3+(G3-5)),0)或:=if(G3<1,0,if(G3<=5,3,3+(G3-5)))2.1.6使用SUMIF函数计算员工本月使用的年假Sumif()条件求和函数,用于按给定的条件对指定单元格求和参考公式：在J3单元格输入公式：=sumif(员工请假明细表!$B$3:$B$41,$A3,员工请假明细表!$G$3:$G$41)公式的含义:在[员工请假明细表]中[员工编号]列查找[员工编号]等于$A3的单元格,然后将这些单元格对应的[年假]所在的$G$3:$G$41区域中的数据相加2.17计算并汇总员工本月剩余年假1．导入上月剩余年假假定今年到本月前没有人请过年假，则[上月剩余年]假可用Vlookup()函数取本工作表中[本年年假]的值在“员工年假表”中J3单元格输入公式：=Vlookup($A3,$A$3:$I$32,8,0)2.计算[本月剩余年假]在“员工年假表”中K3单元格输入公式：=I3-J32.1.9使用DATEIF函数计算员工的工龄工龄：入职年份到当前年份在“员工考勤明细表”中E3单元格输入公式=DATEIF(员工年假表!$F3,today(),"y")2.1.10使用SUMIF函数计算员工的请假天数1．计算[事假]，[年假]，[病假]，[其它]天数在“员工考勤明细表”中事假列F3单元格输入公式：=SUMIF(员工考勤明细表!$B$3:$B$41,$A3,员工考勤明细表!$F$3:$F$41)在“员工考勤明细表”中将事假列F3单元格公式横向拖动填充到[其它],依次更改最后一项的列号即可6\n2．计算迟到次数在“员工考勤明细表”中迟到（次）列J3单元格输入公式：=SUMIF(员工迟到明细表!$B$3:$B$41,$A3,员工迟到明细表!$F$3:$F$41)2.1.11通过分类汇总统计各部门的总体考勤情况一、制作员工工资明细表操作目录:2.4.2制作基本工资标准表1.知识要点：IF（）函数比如：IF（C1<60,“不及格”,“及格”）2.各部门工资标准如下软件开发部：2000元图书开发部：1800元基础部：1200元财务部：1000元人事部：1000元3.在部门工资单元格E3中输入如下公式：=IF(C3="软件开发部",2000,IF(C3="图书开发部",1800,IF(C3="基础部",1200,IF(C3="财务部",1000,1000))))4.各职位工资标准如下部门经理：600元其他职位：200元5.在职位工资单元格F3中输入如下公式：=IF(D3="部门经理",600,200)6.基本工资的计算方法:部门工资+职位工资=基本工资2.4.3通过选择性粘贴导入所有员工的工龄2.4.4使用IF函数计算工龄工资1.知识要点：IF（）函数比如：IF（C1<60,“不及格”,“及格”）DATEDIF(日期1,日期2,"y")计算两个日期相差的年份2.工龄的E3单元格输入:=DATEDIF(基本情况表!L3,TODAY(),"y")3.工龄工资的计算方法:工龄×50=工龄工资，最多不超过500元!F3=IF(E3>=10,500,E3*50)2.4.5制作加班费计算表1.知识要点：VLOOKUP（）函数搜索函数ROUND（）函数4舍5入函数，按指定的位数4舍5入。如:ROUND(4.6,0)=5；ROUND(4.521,1)=4.5；ROUND(6.321,0)=62.加班费的计算方法:正常情况下一个月有22个工作日，一天工作7个小时,平均每小时的基本工资为：基本工资/22/7每小时的加班费规定为：每小时的基本工资的1.5倍6\n则：加班费=基本工资/22/7×1.5×加班时间难点：通过VLOOKUP()函数把“基本工资表”中的“基本工资”引用到“加班费表”中。E3=VLOOKUP（A3,基本工资表!$A$3:$G$32,7,0）2.4.6使用VLOOKUP函数和ROUND函数计算各项应扣款1.知识要点：VLOOKUP（）函数搜索函数ROUND（）函数4舍5入函数2.事假扣款的计算方法:基本工资/22×事假天数=事假扣款3.病假扣款的计算方法:基本工资/22×0.5×病假天数=病假扣款4.迟到扣款的计算方法:迟到次数×50=迟到扣款5.扣款合计：=SUM（G3，I3，K3）2.4.7向员工工资明细表中导入相关数据1.基本工资2.工龄工资3.加班费4.合计金额2.4.8计算应扣工资中的各项数值1.养老保险=基本工资×8%2.医疗保险=基本工资×2%3.失业保险=基本工资×1%4.住房公积金=基本工资×5%2.4.9使用IF函数计算个人所得税2.4.10制作工资条1.确定月份、员工编号月份：=TODAY（）设置日期格式为2001年3月（只有年月格式）员工编号：直接复制MR0012.从工资表中引用姓名、部门、职位、基本工资、工龄工资、加班费、……姓名=VLOOKUP(B4,工资表!$A$4:$Q$33,2,0)部门=VLOOKUP(B4,工资表!$A$4:$Q$33,3,0)职位=VLOOKUP(B4,工资表!$A$4:$Q$33,4,0)…………3.填充、粘贴格式。填充：选择A1：R5，拖动填充至150行（不会自动复制行高！）粘贴：选择第1至第5行，复制,选择第6至第150行，选择性粘贴、格式学生成绩管理典型案例一、几个常用函数1.求和函数SUM2.求平均值函数AVERAGE3.最大值和最小值函数MAX、MIN6\n4.条件函数IF条件判断：满足条件返回一个值；不满足条件返回另一个值5.VLOOKUP（）函数搜索函数格式：=VLOOKUP（lookup_Value,table_array,col_index_num,range_lookup）参数说明如下:lookup_Value:表示要在table_array表格第1列中查找的数值。lookup_Value可以是数值，也可以是对单元格地址的引用。table_array：表示由多列数据组成的一个表格区域。可使用对区域或区域名称的引用。这个表格区域第1列的值是由lookup_Value搜索的值。col_index_num：表示table_array表格区域中待返回的列序号。col_index_num为1时，返回table_array表格区域第1列中的数值，col_index_num为2时，返回table_array表格区域第2列中的数值，以此类推。range_lookup：值为0和10表示只寻找精确匹配值，1表示寻找精确匹配值或近似匹配值6.排名函数RANK返回某数在一列数中的排名7.统计函数COUNTA计算非空单元格的个数8.条件计数函数COUNTIF计算满足条件的单元格个数9.分段计数函数FREQUENCY返回各段的频率颁布（单元格个数）++确定（频率）fri:kwensi6