Excel公式和函数 典型案例—员工工资表
Excel数据管理与分析 项目二 职员工资表
图4-46 员工工资表效果
任务一：制作简单的职员工资表
1．输入数据 启动Excel 2010软件，并在Sheet1工作表中输入 如图4-47所示的表格内容。
图4-47 员工工资表基本数据
任务一：制作简单的职员工资表
2．使用公式计算“应发合计”、“公积金”、“扣款合 计”和“实发合计” 3．使用自动求和功能插入SUM()函数并计算工资“各项 合计”
图4-73所示。
② 按【Enter】键或单击编辑栏左边的 按钮即可完成公
式的输入。
图4-73 计算工龄的公式
任务四：IF() 、YEAR() 和NOW()函数的综合应用
2．IF()函数的应用 使用IF()函数计算“杨林”的工龄工资。
① 将光标定位于M2单元格中，并在编辑栏中输入如图
4-74所示的公式。
表4-3 三金缴纳比例
以员工各项工资总额为基数
保险类型
养老保险
企 8%
2%
业
个
8%
人 2%
1%
20%
医疗保险
失业保险
任务五：用公式计算“三金”
1．计算“杨林”的养老保险，并将结果放在相应的单元格中 将光标定位于N2单元格中，并在编辑栏中输入公式，如 图4-75所示，然后按【Enter】键。
图4-75 计算养老保险的公式
任务三：VLOOKUP()函数的使用
2．定义名为“基本工资对照表”的单元格区域 ① 选择“职员工资表”工作簿中的“各项工资对照表”中的
A3:B8单元格。
② 选择“公式”选项卡“定义的名称”组中的“定义名称”命
令，打开“新建名称”对话框，
然后将“在当前工作簿中的名称” 文本框中输入“基本工资对照表”，如图 4-66所示。 ③ 单击“确定”按钮。
excel数据处理—员工工资表数据处理(办公软件培训课件)
2 逻辑函数介绍
（1）函数and
用于判断我们给出的所有条件都为真的时候， 也就是都符合我们指定的要求。正常情况下 我们一般用于检测数据是否一致。
当AND的参数全部满足某一条件时，返回结 果为TRUE，否则为FALSE。
2 逻辑函数介绍
（2）函数or
用于判断我们给出的所有条件有一个为真的 时候，也就是符合我们指定的要求。
——excel逻辑函数
教学目标
知识目标
了解并掌握excel中逻辑函数的结构
技能目标
能运用excel的逻辑函数完成相应的数据运算
1 逻辑函数的概念
C
O
T
S
1 逻辑函数的概念
用来判断真假值，或者进行复合检验的 Excel函数，我们称为逻辑函数。
在Excel中提供了六种逻辑函数。即AND、 OR、NOT、FALSE、IF、TRUE函数。
2 逻辑函数介绍
（4）函数not
用于对参数值求反。 当要确保一个值不等于某一特定值时，可以
使用 NOT 函数。简言之，就是当参数值为 TRUE时，NOT函数返回的结果恰与之相反， 结果为FALSE.
任何一个参数逻辑值为 TRUE，即返回 TRUE
2 逻辑函数介绍
（3）函数if
根据我们指定的条件进行判断，是否符合，如果不符合则返 回我们指定的答案
它的语法为IF(logical_test,value_if_true,value_if_false)。 其中Logical_test表示计算结果为 TRUE或 FALSE 的 任意值或表达式。
Excel制作常用工资表，加班工时、工资一目了然，特别实用的表格
Excel制作常用工资表，加班工时、工资一目了然，特别实用的表格工资表无论是小单位还是大企业都是必备的，每个月工资统计是一件很重要的事情，那么如何一目了然的分清加班工时和正常工时，以及各种补贴、扣减费用，最后才会得出应发工资。
对了还有全勤奖项等等。
今天和大家分享一款特别实用的工资表表格，准备好你的Excel一起来制作吧！第一步、打开Excel新建空白表格•粗略算了一下，列数需要17列，行数无视它，多少都可以去加的。
•以上是合并好的单元格，1-2行A列合并，选择好2个单元格找到开始-合并单元格，那么其他多个单元格的合并方法都是一样的，这里只举例一个合并单元格方法。
如下图。
第二步、添加斜线•合并后的第一个单元格需要添加斜线，来区别一下，姓名和工资。
选择好单元格，鼠标右键单击-选择设置单元格格式。
•弹出的设置单元格格式-边框-选择斜线，单击确定即可。
第三步、添加文字•在第一个单元格输入：工资、姓名，光标放在中间，操作键盘Alt+enter键，即可换行，然后用空格调整左右距离合适位置。
•依次在后面输入上班工时、应发工资、全勤奖、补贴、应发工资、扣除费用、实发工资、签字处、基本工时、正常加班工时、双休加班、节假日加班、总工时、基本工资、正常上班工资、正常加班工资、双休加班工资、节假日加班工资。
第四步、添加边框，调整表格左右上下边距，字体字号•文字添加完成后，表格就基本完成了，然后选择我们需要的行数，添加边框线即可。
•选择文字，设置字体和字号大小，居中方式等等。
如下图。
最后输入员工姓名、工资和工时就搞定了。
这样一个简单明了的工资表就做好了，加班工时一目了然，非常的实用，下面是最终样式。
今天的课程就分享到这里，同学们拿起手中的电脑，一起来从头制作一下吧，在家里呆着不如学习点知识。
来充实我们自己。
活到老，学到老，勤快点吧！喜欢的朋友收藏起来吧，分享给更多的爱学习的朋友们！本文由，图文设计师东东，原创，特此声明！。
做工资表常用的函数
做工资表常用的函数一、函数介绍工资表是企业日常管理中必不可少的一项工作，而制作工资表需要大量的时间和精力。
为了提高效率、减少错误率，我们可以利用Excel 中的函数来自动计算员工的工资。
本文将介绍几个常用的函数，包括SUM、IF、VLOOKUP等。
二、SUM函数SUM函数是Excel中最基本的函数之一，它可以对一列或多列数字进行求和。
在制作工资表时，我们通常会用到SUM函数来计算员工的基本工资、津贴等。
语法：=SUM(number1,number2,...)参数说明：number1：必选参数，要相加的第一个数值或单元格区域。
number2：可选参数，要相加的第二个数值或单元格区域。
...：可选参数，要相加的其他数值或单元格区域。
示例：=SUM(A2:A10) 表示计算A2到A10单元格区域内所有数字的和。
=SUM(A2,B3,C4) 表示计算A2、B3和C4三个单元格内数字之和。
三、IF函数IF函数是Excel中常用的逻辑判断函数之一。
在制作工资表时，我们通常会用到IF函数来根据员工是否出勤来计算其实际薪水。
语法：=IF(logical_test,value_if_true,value_if_false)参数说明：logical_test：必选参数，要进行逻辑判断的条件。
value_if_true：必选参数，如果逻辑判断为真，则返回的值。
value_if_false：必选参数，如果逻辑判断为假，则返回的值。
示例：=IF(B2>=20,1000,500) 表示如果B2单元格内数字大于等于20，则返回1000，否则返回500。
四、VLOOKUP函数VLOOKUP函数是Excel中常用的查找函数之一。
在制作工资表时，我们通常会用到VLOOKUP函数来根据员工编号或姓名来查找其基本工资、津贴等信息。
语法：=VLOOKUP(lookup_value,table_array,col_index_num,[range_look up])参数说明：lookup_value：必选参数，要查找的值。
工资表的常用公式
工资表的常用公式————————————————————————————————作者: ————————————————————————————————日期：excel中的一些常用计算公式一、年龄计算公式：=IＦ((YＥAR(E2）=2008)*（MＯNTH（E2)>８),0,200８-YEAR(E2)IF （MONTH(E2)<=8,0,-1))其中e2为单元格,2０08为当前年份二、身份证号中提取出生年月=MID（Ｊ11，7，4) & "年" ＆ＭＩD（J1１, 11，2）&"月" & ＭID（J１1, 13, 2)&＂日＂三、班级平均分公式＝IF(ＣOUNTIF($C$2:$Ｃ$２4,Ｊ２6）＝0,""，ＳＵＭIF($C$2：$C$24,Ｊ2６,$D$2)/ＣOUＮTIF（$C$2:＄C$２4,J２6)）四、Excel表中身份证号码提取出生年月、性别、年龄的使用技巧如何从Ｅxcel表中的身份证号码中提取出生年月、性别、年龄呢？看了本文就知道了。
方法一:1．Excel表中用身份证号码中取其中的号码用：MIＤ(文本，开始字符，所取字符数)；2.1５位身份证号从第7位到第12位是出生年月日，年份用的是2位数。
18位身份证号从第7位到第14位是出生的年月日,年份用的是４位数。
从身份证号码中提取出表示出生年、月、日的数字,用文本函数ＭID（)可以达到目的。
M ＩＤ()——从指定位置开始提取指定个数的字符（从左向右)。
对一个身份证号码是1５位或是18位进行判断,用逻辑判断函数IF(）和字符个数计算函数LEＮ()辅助使用可以完成。
综合上述分析，可以通过下述操作,完成形如1978-12-２4样式的出生年月日自动提取：假如身份证号数据在A１单元格，在B１单元格中编辑公式=IF(LＥN(A1)=15,MIＤ(Ａ1,7,２）＆＂-"&MＩD(A1，9,2)&"－"＆MID(Ａ1,11,２),ＭID(A1,７,４)＆"-＂&ＭＩD(A1，11,2)&"-"＆MID（A1,1３,2))回车确认即可。
Excel数据管理与分析项目二职员工资表
任务六：使用公式计算“应发工资”、“实发工资”
3. 将工资表中不需要出现的“参加工作年月”、“职务”、 “工龄”列隐藏，如图4-78所示。
图4-78 完成后的结果
任务七：巧用“辅助序列”和“定位”制作工资条
任务要求：应用“辅助序列”和“定位”巧妙、快速 地制作工资条，以确保能快速、简便地打印工资条。
② 使用相同的方法将“工作部门”、“学历”、“身份证 号”、“联系电话”、“Email地址”列隐藏。 ③ 在表头右边添加“基本工资”、“工龄”、“工龄工资”、 “养老保险”、“医疗保险”、“失业保险”、“应发工 资”、“实发工资”列标题。 ④ 将“工龄”列的数字类型设置为“数值”，小数点后保留 0位，其他列的数字格式设置为“数值”且小数点后保留2位， 其余格式设置如图4-64所示。
组最左边的“插入函数” 命令
图标，打开“插入函数”对话
框，在“或选择类别”下拉列
表中选择“查找与引用”选项，
如图4-69所示。
图4-69 选择函数类别
任务三：VLOOKUP()函数的使用
② 在对话框中选择VLOOKUP()函数，如图4-70所示。 ③ 单击“确定”按扭，打开“函数参数”对话框。在 此对话框中设置各个参数，如图4-71所示。 ④单击“确定”按扭。
应用 任务五：用公式计算“三金” 任务六：使用公式计算“应发工资”、“实发工资” 任务七：巧用“辅助序列”和“定位”制作工资条 相关知识
任务一：制作简单的职员工资表
任务要求：使用公式和SUM()、AVERAGE()、MAX()、 MIN()等函数统计工资表中的各项数据。本任务完成 后的效果如图4-46所示。
图4-58 “工作簿连接”对话框
图4-59 “现有连接”对话框
Excel制作工资表及函数的用法超赞超经典
也许你已经在Excel中完成过上百张财务报表，也许你已利用Excel 函数实现过上千次的复杂运算，也许你认为Excel也不过如此，甚至了无新意。
但我们平日里无数次重复的得心应手的使用方法只不过是Excel全部技巧的百分之一。
本专题从Excel中的一些鲜为人知的技巧入手，领略一下关于Excel的别样风情。
一、让不同类型数据用不同颜色显示在工资表中，如果想让大于等于2000元的工资总额以“红色”显示，大于等于1500元的工资总额以“蓝色”显示，低于1000元的工资总额以“棕色”显示，其它以“黑色”显示，我们可以这样设臵。
1.打开“工资表”工作簿，选中“工资总额”所在列，执行“格式→条件格式”命令，打开“条件格式”对话框。
单击第二个方框右侧的下拉按钮，选中“大于或等于”选项，在后面的方框中输入数值“2000”。
单击“格式”按钮，打开“单元格格式”对话框，将“字体”的“颜色”设臵为“红色”。
2.按“添加”按钮，并仿照上面的操作设臵好其它条件(大于等于1500，字体设臵为“蓝色”；小于1000，字体设臵为“棕色”)。
3.设臵完成后，按下“确定”按钮。
看看工资表吧，工资总额的数据是不是按你的要求以不同颜色显示出来了。
二、建立分类下拉列表填充项我们常常要将企业的名称输入到表格中，为了保持名称的一致性，利用“数据有效性”功能建了一个分类下拉列表填充项。
1.在Sheet2中，将企业名称按类别(如“工业企业”、“商业企业”、“个体企业”等)分别输入不同列中，建立一个企业名称数据库。
2.选中A列(“工业企业”名称所在列)，在“名称”栏内，输入“工业企业”字符后，按“回车”键进行确认。
仿照上面的操作，将B、C……列分别命名为“商业企业”、“个体企业”……3.切换到Sheet1中，选中需要输入“企业类别”的列(如C列)，执行“数据→有效性”命令，打开“数据有效性”对话框。
在“设臵”标签中，单击“允许”右侧的下拉按钮，选中“序列”选项，在下面的“来源”方框中，输入“工业企业”，“商业企业”，“个体企业”……序列(各元素之间用英文逗号隔开)，确定退出。
Excel高级函数进阶教你如何使用SUMIFS和COUNTIFS函数进行复杂计算
Excel高级函数进阶教你如何使用SUMIFS 和COUNTIFS函数进行复杂计算Excel是一种功能强大的电子表格软件，广泛应用于商务和数据处理领域。
在Excel中，SUMIFS和COUNTIFS是两个非常实用的高级函数，能够帮助用户进行复杂的计算和数据分析。
本文将详细介绍如何使用SUMIFS和COUNTIFS函数，并通过案例演示它们的具体应用。
一、SUMIFS函数的使用SUMIFS函数是Excel中的一个高级求和函数，可以根据多个条件进行筛选和求和。
其函数语法如下：SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2,criteria2, ...)其中，sum_range为需要求和的数值范围；criteria_range为需要筛选的条件范围；criteria为满足条件的值。
下面通过一个实例来说明SUMIFS函数的使用。
假设某公司有一个销售数据表格，包含了销售额、产品类型和销售地区等信息。
现在需要计算某一地区某一类型产品的总销售额。
首先，在Excel表格中，需要有一个销售数据表格，并确保销售额、产品类型和销售地区等信息分别位于不同的列中。
然后，选中一个单元格，输入SUMIFS函数的公式。
例如，假设销售数据表格中销售额位于B列，产品类型位于C列，销售地区位于D列。
要计算某一地区某一类型产品的总销售额，可以使用以下公式：=SUMIFS(B2:B100, C2:C100, "产品类型", D2:D100, "销售地区")这个公式的意思是，求B2到B100范围内，同时满足C2到C100范围内的条件为“产品类型”以及D2到D100范围内的条件为“销售地区”的销售额总和。
二、COUNTIFS函数的使用COUNTIFS函数是Excel中的另一个高级函数，用于统计满足多个条件的单元格个数。
其函数语法如下：COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, ...)其中，criteria_range为需要筛选的条件范围；criteria为满足条件的值。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
Excel公式和函数典型案例—员工工资表
每个公司对员工工资进行核算的方法各不相同，下面的“员工工资表”是通过“学历”的不同，来计算员工的基本工资，再通过工龄的不同来计算员工的奖金。
通过本例的学习，可以使用户掌握数据有效性的应用，以及学习逻辑函数IF和OR 函数的用法。
1．练习要点：
●单元格样式
●设置单元格格式
●数据有效性
●IF函数
2．操作步骤：
（1）新建一张空白工作表，在A1
单元格中，输入“员工工资表”文字，
并在A2至J2单元格区域中，分别输入
各字段名。
然后合并A1至J1单元格区
域，如图5-17所示。
图5-17 合并单元格
提示选择A1至J1单元格区域，并单击
【对齐方式】组中的【合并后居中】
按钮，即可合并单元格。
（2）在A3单元格中，输入数字1。
然后选择A3至A13单元格区域，并单
击【编辑】组中的【填充】下拉按钮，
执行【系列】命令，如图5-18所示。
图5-18 执行【系列】命令
（3）在弹出的【序列】对话框中，
在【序列产生在】栏中，选择【列】单
选按钮，并设置【步长值】为1，【终止
值】为11，如图5-19所示。
单击
输入
输入并
选择执行
图5-19 设置序列填充
（4）单击【数字】组中的【对话框启动器】按钮，然后，在弹出的【设置单元格格式】对话框中，设置数字的格式，如图5-20所示。
图5-20 设置单元格格式
提 示 在【分类】栏中，选择【特殊】项，并在【区域设置（国家/地区）】下拉列表中，选择【俄语】项，并设置【类型】项。
（5）选择B3单元格，输入“=NOW()”公式，并选择B3单元格，在【设置单元格格式】对话框中，选择【分类】栏中的【自定义】选项，并设置【类型】为yyyy.mm ，如图5-21所示。
图5-21 设置单元格格式
提 示 选择B3单元格，将鼠标置于该单元格的填充柄上，向下拖动至B13单元格，即可复制公式。
（6）在C3至C13单元格区域中，输入员工的“姓名”。
然后选择D3至D13单元格区域，并选择【数据】选项卡，单击【数据工具】组中的【数据有效性】下拉按钮，执行【数据有效性】对话框，如图5-22所示。
图5-22 执行【数据有效性】命令
（7）在弹出的【数据有效性】对话框中，设置【允许】为“序列”；并在【来源】列表中，输入“KTV 客服部,VOD 客服部,KTV 开发部,KTV 工程部”文字，如图5-23所示。
单击
设置
执行
选择
选择
设置
效果
选择
图5-23 设置数据有效性
（8）在工作表中，分别单击D列单元格中的下拉按钮，在其下拉列表中选择合适的选项，如图5-24所示。
图5-24 选择合适的部门
（9）在E3至E13单元格区域中，设置数据的有效性，并分别进行选择，效果如图5-25所示。
图5-25 设置数据的有效性
（10）在F3至F13单元格区域中，输入“参加工作时间”的具体数据。
然后选择该时间区域，并在【设置单元格格式】对话框中，设置单元格的格式，如图5-26所示。
图5-26 设置单元格格式
（11）在G3单元格中，输入“=INT((B3-F3)/365+1)”公式，并复制该公式至此列的其他单元格中，如图5-27所示。
图5-27 计算工龄
（12）在B3单元格中，输入“=IF(E 3="博士",1500+G3*200,IF(E3="硕士",12 00+G3*150,IF(E3="本科",800+100*G3,IF (E3="大专",600+G3*80))))”公式。
然后复制此公式至该列的其他单元格中，如图5-28所示。
图5-28 计算基本工资
（13）选择I3单元格，输入“=IF(OR(G3>5,OR(E3="硕士",E3="博士")),500,"无奖金")”公式。
然后复制此公式至该列的其他单元格中，如图5-29所示。
执行设置
选择
计算
计算
输入
图5-29 计算奖金
（14）选择J3单元格，输入“=SU M(H3:I3)”公式，然后复制此公式至该列的其他单元格中，如图5-30所示。
图5-30 计算实发工资
（15）选择A1单元格，并单击【样式】组中的【单元格样式】下拉按钮，在【标题】栏中选择【标题】项，如图5-31所示。
图5-31 应用单元格样式
（16）将鼠标置于B列和C列的边界线上，向左拖动至显示“宽度：8.38(72像素)”，如图5-32所示。
然后运用相同的方法分别调整D列和F列的单元格列宽。
图5-32 调整列宽
（17）选择A2至J13单元格区域，并单击【单元格】组中的【格式】下拉按钮，执行【行高】命令，在弹出的【行高】对话框中，设置【行高】为18.75，如图5-33所示。
图5-33 设置行高
（18）选择A2至J13单元格区域，并单击【对齐方式】组中的【居中】按钮，如图5-34所示。
图5-34 设置对齐方式
（19）选择A2至J13单元格区域，单击【字体】组中的【边框】下拉按钮，执行【所有框线】命令，如图5-35所示。
计算
计算
输入
输入
单击
拖动
图5-35 执行【所有框线】命令
（20）选择A2至J2单元格区域，并单击【样式】组中的【单元格样式】下拉按钮，选择【差】项，如图5-36所示。
图5-36 应用单元格样式
（21）选择A4至J5单元格区域，单击【字体】组中的【填充颜色】下拉按钮，选择“水绿色，强调文字颜色5，淡色80%”色块，如图5-37所示。
图5-37 设置填充颜色
（22）选择A8至J9单元格区域，设置【填充颜色】为“橄榄色，强调文字颜色3，淡色80%”，效果如图5-38所示。
图5-38 设置填充颜色
（23）选择A12至J13单元格区域，设置【填充颜色】为“水绿色，强调文字颜色5，淡色80%”。
（24）单击Office按钮，执行【打印】|【打印预览】命令，即可预览工作表效果。
技巧
按Ctrl+F2组合键，即可预览工作表
效果。
选择
单击
选择效果。