Word财务知识分析及人员管理进阶教程

1、PAGE PAGE 49霸只记得函数名称胺，记不清函数的拔参数时：艾可以在编辑栏中背输入一个等号其埃后接函数名，再凹按Ctrl+A白键，则自动进入耙“俺函数参数拔”鞍。拔返回到函数对话般框：哀光标定位于编辑吧栏，再点击拌钮。碍多个工作表的单叭元格合并计算：敖=Sheet1哀!D4+She扮et2!D4+傲Sheet3!般D4 皑或=SUM(S啊heet1：S扒heet3!D碍4)按E阿xcel中自动坝筛选后计算个数啊时：般=SUBTOT柏AL(3,C3氨:C11)扮E鞍xcel中开方伴运算：八如8开3次方，扮输入半“拌8吧靶 (1/3)半”凹E阿xcel中查找癌工作表中的链接按：肮按Ctrl+

2、蔼霸或扮“扮编辑菜单链接板”斑E半xcel中让空凹单元格自动填为鞍0：捌选中需更改的区哀域后点查找命令唉，直接输替换为扳0即可。背E芭xcel中设置半加权平均：哎设置公式解决（俺其实就是一个除矮法算式），分母岸是各个量值之和版，分子是相应的啊各个数量之和，胺它的结果就是这佰些量值的加权平澳均值。扮为多个工作表设靶置相同的页眉和袄页脚或一次打印哀：澳在某工作表名称蔼处右击点靶“案选择全部工作表暗”案，这时你的所有澳操作都是针对全疤部工作表了。颁E把xcel中无法芭输入小数点，显芭示的是逗号：佰“办控制面板区域跋和语言选项中柏文（中国）自挨定义笆”板中将小数点改为奥“爸.耙”俺快速选取特定区靶域：

3、爱例如选取A2:搬A1000，按稗F5键出现熬“班定位阿”霸窗口中，在肮“暗引用版”吧栏内输入要选取把的区域A2:A啊1000。袄快速返回选中的矮区域：哎Ctrl+Ba百cksPae（扒退格）懊E霸xcel中隐藏般列背：Ctrl+搬，若取消隐藏可挨选中该隐藏列的版前后两列Ctr俺l+ Shif啊t+，右击选柏取消或双击选中岸的任一列宽或改颁变任一列宽；或氨当鼠标变为双竖翱线时拖动。把Word里的数字转换到Excel中：矮鞍选中，复制，设半置输入单元格为叭文本，选择性粘靶贴，值挨版选中，表格转换靶为文本，粘贴，按分列，对分列选案项设置文本；皑版另存为文本文件百，在Excel皑中打开文本文件邦直接

4、打开一个电绊子表格文件时打拜不开：唉“暗文件夹选项文爱件类型按”拜中找到.xls斑文件，并在捌“氨高级昂”傲中确认是否有参埃数1%，如果没芭有，就手工加上敖。疤E佰xcel中快速啊复制上一单元格摆的内容：爱选中下面的单元瓣格，按Ctrl翱+西文单引号班E盎xcel中给单稗元格数据的尾部艾快速添加信息：般选中该单元格按伴2键输入数据吧即可。阿将某个长行转成柏段落并在指定区摆域内换行：班例如A10内容绊很长，欲将其显懊示在A列至C列吧之内：选定A1拔0:C12点击斑“跋编辑菜单填充吧内容重排矮”蔼，此法很适合用背于表格内的注释隘。耙E皑xcel中快速艾定位到活动单元八格所在列或行中碍的最后一个非空

5、霸单元格，或者下扮一个单元格为空坝，则扩展到下一捌个非空单元格：俺Ctrl+Sh版ift+箭头败让佰E奥xcel自动填搬充固定倍数的小鞍数点或固定个数翱的零：按“瓣工具菜单选项矮编辑自动设隘置小数点哎”办，若需要自动填澳充小数点，应在白“唉位数扒”叭框中输入小数点吧右面的位数，如伴。叭信封尺寸：颁DL220啊鞍110挨E邦xcel中将一芭列组合型字符分阿开到若干列保存碍：澳可用艾“爸数据分列把”埃命令坝E拜xcel中合并凹各列(胺A熬列、埃B哀列)中的数据，岸中间加减号：爸=A3&-扮&B3疤用汉字名称代替翱单元格地址：版芭选定单元格区域啊在哎“把名称框拌”翱中直接输入名字班；摆般选定要命名的

6、区白域，选按“安插入菜单名称案定义败”般键入名字即可。罢在公式中快速输安入不连续的单元按格地址：般按Ctrl键选捌取这些区域后敖“袄定义摆”伴名称，将此区域百命名，如Gro敖up1，然后在扮公式中使用这个碍区域名，如摆“挨=SUM(Gr班oup1)啊”傲命名常数：靶在某个工作表中蔼经常用到利率3跋.5%来计算利办息，可以在凹“佰插入菜单名称邦定义当前工艾作薄的名字把”岸框内输入把“胺利率矮”跋，在霸“胺引用肮”败框中输入扮“芭=0.035肮”胺确定。叭制作财务报表时按，自动转换大写霸金额：瓣在要转换大写金巴额的区域上右击隘，凹“般自定义熬”哎输入伴“懊DBNum2盎 0万0蔼千0百拜0拾0元八

7、0角整板白E疤xcel中把一耙个编辑了宏或自胺定义函数的工作扒簿移植到其它电佰脑上使用：熬最科学的方法是按保存为加载宏。癌保存好的宏可以霸通过皑“唉工具败”拔菜单加载宏命唉令，载入进来使败用。氨将一些常见文件皑夹快捷方式添加拔到爱E哎xcel皑“啊打开蔼”懊和斑“罢另存为埃”挨对话框左侧区域般中：芭点击皑“哀打开哎”拔或懊“瓣另存为稗”伴对话框，选定自哀己喜欢的文件夹罢，再点击氨“办工具(鞍L背)添加到我的敖位置跋”扮命令。 碍将一个表格中的爸某些列分别组合拌打印出来：板选将不需要打印班的行事列隐藏起肮来，再点击暗“胺视图盎”爸菜单视图管理叭器，如上图所示班。袄输入一些唯一数癌据，如身份证号坝

8、码时，可用数据背有效性功能，防矮止重复输入：隘选定要输入数据挨的区域，如列碍，再点击般“班数据有效性百允许(拌A瓣):自定义公蔼式(巴F扳)吧“叭=counti艾f ( B:B颁,隘B1)=1板”瓣，如上图所示。斑接着点击俺“霸出错警告叭”稗选项卡，标题:搬“翱数据重复霸”昂 错误信息:罢“爸请核查后，重新摆输入！昂”半如果希望一次打蔼开多个文档，可翱以将它们保存为凹一个工作区文件八：懊打开多个文档后般点击案“懊文件岸”隘菜单下的吧“佰保存工作区懊”昂命令昂利用Excel扳锁定、隐藏和保摆护工作表的功能芭，把公式隐藏和皑锁定起来不让使挨用者查看和修改澳：芭先点击隘“芭编辑菜单定位皑定位条件扳”

9、矮按钮，选中疤“昂公式邦”傲项，会看到单元奥格中含公式的区板域。若想隐藏可捌右击该区域后点扳“艾设置单元格格式俺保护笆”昂，选中摆“霸锁定疤”颁与靶“败隐藏佰”靶功能，点击败“昂工具菜单保护碍保护工作表袄”百，输入密码。笆为数字自动添加翱单位：氨选中单元格区域肮后右击点绊“柏设置单元格格式把”耙对话框中的数字罢选项卡中的矮“爸自定义跋”斑命令，输入#.凹00元翱利用F4键快速奥切换案“霸相对引用拜”耙和稗“般绝对引用奥”肮格式：笆在编辑栏中选中按公式后按下F4伴键半对于一些复杂公柏式，可以利用肮“岸公式求值败”岸分段查检公式的爸返回结果，以查捌出错误所在：扳定位在含公式的傲单元格上点击班“板工

10、具坝”把菜单公式审核皑公式求值。板第1章 办公懊室管理工作表岸办公室的工作并百不难，只是多而翱杂。处理得井井瓣有条是完成了工埃作，处理得一塌摆糊涂，也是工作八了，但却做了一背堆糊涂账。下面肮这些表格是办公碍室工作中常遇到拔的，跟财务管理斑，或多或少，有八一些直接或间接瓣的关系，谨作为斑一些例子，希望暗能给你的日常工稗作一点启示。矮办公室用品分为癌消耗性物品和非埃消耗性物品，领稗用需登记在册。绊一来可以掌控耗俺材的使用情况，哎控制成本，二来巴对于物品的领用斑做到心中有数，吧特别是非消耗性皑办公室用品，原唉则不能重复申领啊，登记可做到有笆账可查。计算乘积的函数=PRODUCT(D3:E3) 其中D

11、3数量；E3是单价。氨知识点把PRODUCT伴函数：函数将所暗有以参数形式给巴出的数字相乘，败并返回乘积值。靶函数语法摆PRODUCT艾 (numbe叭r l，num绊ber2，翱伴)扒函数说明搬1当参数为数癌字、逻辑值或数吧字的文字型表达熬式时可以被计算胺；当参数为错误唉值或是不能转换佰为数字的文字时颁，将导致错误。叭2如果参数为俺数组或引用，只罢有其中的数字将隘被计算。数组或颁引用中的空白单癌元格、逻辑值、搬文本或错误值将搬被忽略。鞍报销费公式的编般制捌当车辆使用时为岸了办公事，车辆拜消耗费可以报销敖，如果车辆使用瓣为私事，那么车爱辆产生的消耗费埃则不予报销。本巴着这个原则，来敖编制报销费

12、的公凹式。选中I3单霸元格，在编辑栏败中输入公式：胺“奥=IF(D3=叭百公事哎搬,巴H3芭,坝0)埃”板，按回车键确定拌。版编制驾驶员补助俺费啊选中J3单元格暗，输入公式：拔“傲=IF(G3巴-跋F3)*24矮8扳,拌INT(G3绊-唉F3)*24捌-傲8)*30耙,拔0)案”岸，按回车键确定熬。其中啊“翱(G3啊-隘F3)*24绊”搬是将时间格式转唉换为小时制，凹“唉8矮”笆为法定工作时间柏八小时，颁“鞍30蔼”班为超出法定八小搬时工作制时，每肮小时补助金额。案插入部门合计行跋并编制计数、汇按总公式矮为了方便观察和坝统计各部门用车袄情况，需要按部隘门进行分类统计摆。在不同的部门拔后插入两个

13、空行绊。然后在C列按皑部门的不同，分岸别输入熬“敖业务部 计数败”氨和氨“扒业务务部门 汇啊总岸”笆，下同。同时，安调整列宽保证单拔元格中内容完整巴显示。捌选中H6单元格艾，输入公式：拜“颁=背SU袄BTOTAL(敖3挨,哀H3:H5)白”班，在右击鼠标设百置为斑“拜数值隘”柏格式。其中按“罢3斑”捌对应COUNT背A函数，表示返柏回H3:H5区班域中非空值的单扮元格个数。拔选中H7单元格跋，输入公式：拔“叭=绊SU搬BTOTAL(安9班,八H3:H5)笆”稗，其中爸“碍9办”颁对应SUM函数搬，表示对H3:笆H5区域求和并背返回值。 懊 最后跨行复制芭以上两个公式C昂trl+C，C败trl+

14、V。肮知识点暗SUBTOTA拔L函数：返回列昂表或数据库中的隘分类汇总。熬函数语法邦SUBTOTA岸L爱(阿F邦unction百_num，re芭fl，ref2扒，瓣半)案 佰F半unction翱_num：为l唉到11(包含隐办藏值)或l01盎到111(忽略氨隐藏值)之间的傲数字，指定使用奥何种函数在列表隘中进行分类汇总叭计算。爸ref1、re半f2：为要进行懊分类汇总计算的唉1到254个区碍域或引用。版函数说明敖如果在ref1蔼、ref2澳捌中有其他的分类安汇总(嵌套分类艾汇总)，将忽略扳这些嵌套分类汇昂总，以避免重复罢计算。颁当胺F邦unction柏_num为从l岸到11的常数时般，该函数将

15、包括懊通过般“敖隐藏行皑”埃命令所隐藏的行笆中的值，当你要哎对列表中的隐藏板和非隐藏数字进斑行分类汇总时，疤请使用这些常数奥。当版F叭unction敖_num为从1般01到111的皑常数时，皑SUBTOTA搬L捌函数将忽略通过爸“板隐藏行扳”坝命令所隐藏的行斑中的值。只对列般表中的非隐藏数翱字进行分类汇总哎时，就使用这些扮常数。案总计数与总计公疤式的编制爱对本月车辆使用碍情况进行汇总统稗计，选中C23爱单元格输入版“拔总计数暗”扮，在C24单元爱格输入隘“疤总计吧”疤。选中H23单伴元格，输入公式矮：疤“般=拌SUBTO熬T隘AL暗(3俺,盎H3:H20)艾”俺。选中H24单安元格输入公式：罢

16、“矮=岸SUBTOTA霸L背(9扳,白H3:H20)叭”岸。般 第2章 固扒定资产的核算办财务统计表中以敖固定资产核算爱、啊收费统计佰、白账龄统计氨和肮损益表百尤为重要。通过跋这些表,企业决版策者才可以了解霸销售业绩、营业案规模，便于了解肮企业的销售情况癌及市场前景。还疤可以了解企业的扮费用和支出情况案，便于控制和降板低成本费用开支邦水平。肮固定资产的核算扮对于财务人员来安说，一直是个比按较头疼的工作。班如果用手工制作拔，不仅工作量非罢常大，技术含量疤非常低，而且非挨常耗时耗力。本翱章以固定资产核盎算为例，根据录拜入的设备原值、哎使用年限、残值爱率等信息，自动熬生成年折旧额、唉月折旧额等数据盎

17、，形成手工输入哀和电脑自动计算板相结合的固定资翱产台账表。把我扒们的财务人员从班繁重和枯燥的手胺工计算中解放出爱来。绊固定资产核算在半应用Excel笆之前有两套账，柏一套是固定资产胺卡片或台账，作俺用是登记购入固绊定资产的名称、埃原值、折旧年限白、年折旧额、净叭残值等，同时还搬要记录因报废或哎售出后固定资产柏的减少。另一套罢账负责记录每个八会计期间各项资班产应计提的折旧背额、累计折旧额拜和净值。使用E懊xcel创建固鞍定资产台账和折熬旧提算表则可大办大减轻工作量，爱并增加核算的准跋确度。跋下面是依据国家盎规定制定的分类隘折旧表，可用来霸查询设备的折旧搬年限。 扮固定资产台账中叭编制翱“办编号百

18、”巴公式搬依次录入昂“稗使用部门胺伴资产类别巴肮名称百搬计量单位按傲始用日期懊按原值八疤耐用年限邦俺残值率哎”靶等数据。再选中把B3单元格，在背编辑栏中输入公岸式：拜“扮=SUMPRO爱DUCT($摆C$3：C3=邦 C3)*1)白”罢，回车确认。该俺公式是指计算C暗3单元格到C3隘单元格中内容相靶同的单元格个数耙，返回值为艾“百1拌”氨。傲我们来看B10阿单元格的公式：稗“爱=SUMPRO熬DUCT($靶C$3：C10碍=C10)*1瓣)邦”般计算拌C拜3单元格到C1办0单元格中内容懊相同的单元格个斑数，返回值为柏“爱6芭”罢，从C3单元格蔼数到C10单元案格，百“岸管理部分笆”爸出现正好是

19、6次靶。澳知识点鞍SUMPROD昂UCT函数的功癌能是在给定的几艾组数组中，将数疤组间对应的元素白相乘，并返回乘拌积之和。败函数语法坝SUMPROD芭UCT(arr扒ayl，arr巴ay2，arr斑ay3，板芭)凹arrayl，八array2，般array3叭盎：为2到255扮个数组，函数将挨对相应元素进行凹相乘并求和。佰函数说明翱数组参数必须具俺有相同的维数，班否则SUMPR般ODUCT函数稗将返回错误值#袄VALUE!。埃函数SUMPR翱ODUCT将非斑数值型的数组元奥素作为0处理。蔼固定资产台账中版编制搬“奥残值熬”办公式奥选中K3单元格爸输入公式：吧“斑=唉R半OUND(H3翱*J3，

20、2)澳”澳，回车键确认。翱固定资产台账中澳编制柏“暗年折旧额斑”版公式盎选中L3单元格跋输入公式：案“坝=按 R败OUND(SL凹N(H3肮,按K3败,吧I3)俺,安2)碍”般，按回车键确认案。其中用SLN哀函数对资产进行霸线性折旧，折旧疤额用四合五入法败保留两位小数。芭知识点澳SLN函数的功胺能是返回某项资昂产在一个期间中搬的线性折旧值。芭函数语法爱SLN(cos哎t，salva案ge，life罢)斑cost：为资肮产原值。霸salvage瓣：为资产在折旧鞍期末的价值(有邦时也称为资产残敖值)。把life：为折敖旧期限(有时也办称作资产的使用皑寿命)。挨编制扳“扳月折旧额拔”邦公式埃选中M3

21、单元格巴输入公式：唉“拌=ROUND(拔L312，2摆)摆”暗，按回车键确认捌。氨在公室管理工作八中，要制作固定肮资产的月折旧表奥，这里以200碍9年1月的折旧俺表为例。当制作胺下一个月的折旧岸表时，上期原值拔、上期折旧、上佰期累积折旧等数拜据就可以直接从扳上月的折旧表中鞍复制了。 百本月折旧隘=上期折旧+折袄旧变化 （蔼=G3+J3蔼） 绊本月原值碍=上期原值+原耙值变化 （爸=F3+I3昂）白本月累计折旧靶=上期折旧+上霸期累计折旧+折扒旧变化懊-跋累折变化 （扳=G3+H3+埃J3-K3扳）百本月净值拌=本月原值捌-伴本月累计折旧 碍 （岸=M3-N3白）皑到期提示挨=本月原值瓣-暗设备

22、残值吧-吧本月累计折旧 袄 （奥=M3-败固定资产台账挨!K3-N3肮）碍选中P3：P1拜5区域，点击爱“瓣格式/条件格式颁”耙设置扳“吧小于佰”颁、白“佰L3柏”半再点击蔼“阿格式耙”澳按钮，选红色填半充，表示满足该半条件时，单元格奥用红色填充。唉第3章 两个班重要的统计（收暗费统计表、账龄按统计表）袄新建霸“拔收费登记表笆”肮工作表，在A1肮:E41区域录拜入标题及日常收霸费记录。霸新建俺“佰收费统计表霸”安工作表，制作标袄题和月份数。耙编制绊“鞍单位1 的2八008年班”矮收费公式胺选中B3格，输把入公式：版“爱=SUMPRO案DUCT(收凹费登记表!$C芭$2:$C$1埃00=$A3)

23、爱*(收费登记表隘!$D$2:$耙D$100=L班OOKUP(2暗,1/($B$邦1:B$1翱),$B$矮1:B$1)白*(收费登记表隘!$B$2:$半B$100=B胺$2)*收费登拜记表!$E$2昂:$E$100霸)扮”拔，按回车键确认拌。使用拖拽的方啊法完成该列公式鞍的复制。版在横向复制（智按能填充）200板9年收费公式，奥再编制佰“颁单位2坝”板和隘“胺单位3哎”把的收费公式或者办做（智能填充）埃即可。傲选中D3格，输凹入公式：笆“办=SUMPRO袄DUCT(收矮费登记表!$C昂$2:$C$1昂00=$A3)佰*(收费登记表挨!$D$2:$埃D$100=L啊OOKUP(2袄,1/($B$

24、把1:B$1罢佰 跋),$B$1斑:D$1)*啊(收费登记表!拌$B$2:$B背$100=D$版2)*收费登记罢表!$E$2:颁$E$100)把”绊，按回车键确认埃。用拖拽法完成澳该列公式的复制癌。拌选中F3格，输蔼入公式：稗“艾=SUMPRO氨DUCT(收般费登记表!$C傲$2:$C$1盎00=$A3)俺*(收费登记表绊!$D$2:$蔼D$100=L跋OOKUP(2扒,1/($B$挨1:F$1柏绊 艾),$B$1耙:F$1)*拜(收费登记表!岸$B$2:$B氨$100=F$暗2)*收费登记扮表!$E$2:凹$E$100)碍”瓣，按回车键确认昂。使用拖拽的方氨法完成该列公式唉的复制。账龄统计表

25、鞍无论是对内还是敖对外，企业都需稗要进行账龄分析翱。特别是法律健懊全的今天，各个板企业对应收账款白的账龄数则更为柏关心。因为账龄伴一旦超过诉讼时捌效，就不再受法肮律保护，财务人盎员必须及时创建霸账龄分析表，提拔醒相关决策者。斑财务人员常用的佰账龄统计表，经爱过Excel处熬理后，能直观地袄反映出每个往来敖单位的账龄。这拌对于及时观察往哀来账的诉讼时效耙，避免给企业造敖成损失由很大的跋作用。下面，我傲们就来对这两种暗统计表的制作进佰行讲解。凹本节将示范如何拜通过Excel巴直观反映每个账啊户的账龄，并计百算出每个账龄区捌间总额。罢新建氨“案往来账龄分析佰”耙工作表，在A2昂格中输入伴“癌截止时间

26、：搬”蔼，在B2中输入巴“阿2009-1-啊1搬”奥，其它格制作标碍题，设要输的氨“伴上限值天数隘”案和半“瓣下限值天数案”罢的区域为：扳“叭自定义奥疤/ 0天 斑”拔这样输数字时会爸自动带艾“板天鞍”摆，输其它字符时碍不会自动带把“凹天背”挨并且在编制爸“肮金额懊”把公式时才能计算坝正确。拌编制癌“跋金额肮”隘公式颁选中D4格，输熬入公式：熬“吧=IF(AND昂($B$2-$绊C4=D$2笆,$B$2-$岸C4霸160碍,澳达标疤,叭不达标)唉”哎在选中这个区域岸设败“懊格式菜单条件阿格式板”碍凡等于稗“扳不达标坝”霸项都红色显示。疤自动生成排名：拔在G2格中输入绊公式颁“稗=RANK($笆

27、E2斑,笆$E$2:$E埃$6)隘”凹总 分:昂=SUM(C3埃:C45) 肮 哀 办 邦平 均 分:版=AVERAG罢E(C3:C4昂5)靶及格人数:跋=COUNTI拌F(C3:C4肮5,=60瓣) 癌 矮优秀人数碍:般=COUNTI八F(C3:C4坝5,=85蔼)爱成绩排名:捌=RANK(D碍3,$D$3:哀$D$45,0耙) 邦 案参考人数: 把=芭COUNTA(按A3:A45)拌:盎自动平均分: 哀=AVERAG扒EA(C3:C拔45) 把 袄最高分: 佰=MAX(C3摆:C45) 霸优秀人数:瓣 爸=COUNTI翱F(C3:C4坝5,=85白) 叭 疤 跋最低分:隘 哎=MIN(C3

28、隘:C45)凹及 格 率:笆=COUNTI耙F(C3:C4拜5,=60百)/COUN办T(C3:C4爸5)*100捌优 秀 率:坝=COUNTI隘F(C3:C4俺5,=85袄)/COUN盎T(C3:C4稗5)*100罢男生人数:摆 疤=CONCAT袄ENATE(C巴OUNTIF(俺$B$3:$B懊$45,男八),人)或笆=COUNTI败F(B$3:B皑$45,男搬)坝女生人数:埃 拜=CONCAT扒ENATE(C搬OUNTIF(安$B$3:$B傲$45,女按),人)或凹=COUNTI把F(B$3:B癌$45,女拌)板100分:扮 跋=COUNTI爸F(C3:C4唉5,=100半) 背 搬40以

29、下（不含斑40）啊: 案=COUNTI艾F(C3:C4挨5,=90蔼)-COUN奥TIF(C3:叭C45,=艾100)蔼8090（不唉含90）:哎 邦=COUNTI扳F(C3:C4绊5,=80扳)-COUN艾TIF(C3:背C45,=碍90)般7080（不百含80）:摆 摆=COUNTI靶F(C3:C4疤5,=70瓣)-COUN案TIF(C3:扒C45,=八80)板6070（不鞍含70）:敖 翱=COUNTI摆F(C3:C4岸5,=60翱)-COUN芭TIF(C3:阿C45,=懊70)蔼5060（不埃含60）:背 坝=COUNTI阿F(C3:C4矮5,=50拌)-COUN懊TIF(C3:啊C4

30、5,=靶60)绊4050（不巴含50）:叭 氨=COUNTI霸F(C3:C4盎5,=40拜)-COUN笆TIF(C3:耙C45,=哀50)挨说明：C列为学笆生成绩，D列为笆学生成绩排名，鞍60分及60分颁以上为及格，8把5分及85分以懊上为优秀，学生俺人数为43人，巴“拜成绩排名:=R八ANK(D5,艾$D$5:$D班$45,0)稗”暗的0为降序排列肮，若是1则为升懊序排列。八本部分适合人员斑：HR薪酬管理翱专员半薪酬福利管理是百人力资源管理中般最重要的部分之霸一，它直接对应啊了员工在绩效考阿核中的成绩，全爸面反映薪酬福利靶管理成果的工具柏就是工资表。一啊般来说，工资表埃由员工的基本工百资、岗

31、位工资、搬绩效工资、代扣把个税、社会保险班、考勤扣款等部按分组成，其中，盎基本工资、岗位稗工资、绩效工资芭是员工工资的应白得部分(是做斑“懊加法版”扒的部分)，而代背扣个税、社会保皑险、考勤扣款则罢是扣款部分(是败做胺“奥减法俺”班的部分)。因此扮我们将工资表大笆致分为三个大的把部分，即个税及哎保险代扣表，考白勤扣款表，实得扒工资表。下面我班们就分别从这三败个部分讲解如何跋利用柏Excel摆 2003进行办制作。懊 薪酬管理工作碍是一个用数字说阿话的工作，而表胺格是最直观的表扮现形式。随着无拌纸化办公的进一案步推广，写写画哀画的传统管理方哎式正逐渐被电子斑表格所取代，从案事薪酬管理的工熬作人员

32、除了掌握稗基本的财务管理俺软件，还需要熟拜练掌握把Excel搬工具软件，让工八作变得更轻松愉柏快。皑NOW函数：坝调用计算机系统唉内部时钟的当前白日期和时间，可百以给制作人返回俺一个打印时间。拜在某格中输入隘“百NOW( )啊”按按确定。八年功工资：哎=DATEDI翱F(哎G4敖,背H4半,捌y)*50傲 其中在扒“邦年功工资佰”稗中，公式起始日白期为傲“伴G4格的进厂时邦间伴”笆，结束日期为俺“埃H4格的辞工时阿间巴”碍。这个公式中只坝取了两个时间段艾之间的整年数，哀员工每工作一整扒年就多50元钱碍，整年数*50坝就得到了该员工凹的年功工资。傲知识点癌DATEDIF碍函数，在财务工叭作应用中

33、很广泛靶，用于计算两个懊日期之间的天数稗、月数或年数。懊函数语法芭DATED版I绊F(start澳_盎date扒，霸end澳_捌date哀，败unit)俺start_d斑ate：为一个班日期，代表时间爱段内的第一个日扳期或起始日期。肮end_dat埃e：为一个日期跋，代表时间段内八的最后一个日期扳或结束日期。按Unit：为所肮需返回的类型，翱所包括的类型有百：“Y”时间段中的整年数“M”时间段中的整月数“MD”start_date与end_date日期中的天数的差，忽略日期中的月和年。“YM”start_date与end_date日期中的月数的差，忽略日期中的日和年。“YD”start_dat

34、e与end_date日期中的月数的差，忽略日期中的年。 佰员工的当月相关柏资料信息表斑员工的当月相关暗资料信息是工资鞍表的一个重要项拜目，包含了出勤氨、加班、养老保傲险和补贴等重要靶信息。因为存在板一定变数，可单坝独成表，然后按佰照当月实际情况扳进行修改，然后班供其他工作表调瓣用其中的数据，傲这样才是一个系澳统而全面的工资鞍表体系。癌实际出勤天数扮=应出勤天数胺-背缺勤天数（F4肮=D4-E4肮）数据的调用皑做工资明细表时挨，在第一行输入办表格标题后我们佰发现按“俺员工代码、部门隘、姓名跋”皑等数据与般“傲员工基础资料表八”佰中的内容是相同唉的，因此就无需岸反输入这些数据皑，可以采用调用班数据

35、的方法。同爱时，当员工资料暗发生变化时，不背需要核对改动每扮一张表格，只需蔼要修改第一张（癌基础资料表）表埃格的资料，其他板工作表就能自动把变更。以姓名为背例选中C4格，巴输入公式捌“昂=VLOOKU叭P(A4盎,扒基础资料表!A吧:C稗,跋3斑,皑0)疤”白，回车。用同样安的方法调用部门颁中的数据。斑若用到制表时间按(如矮2009年4月白30日昂)时可输入公式俺“斑=基础资料表！板A2岸”版进行数据调用，瓣其中A2格是制皑表时间；伴调用半“拜员工代码柏”昂输入公式柏“胺=基础资料表！熬A4懊”俺 其中A4格是绊第一位员工的代背码号。按若需调用澳“版部门、姓名班”办等数据，输入公把式皑“哎=V

36、LOOKU碍P(A4傲,哎基础资料表!A败:I埃,案2瓣,扮0)熬”哎其中A4格是需斑要在数据表首列阿搜索的值如员工哎代码号，员工的哀基础资料表!A矮:I指需要在其敖中搜索数据的信败息表区域，2指熬满足条件的数组版区域的列号，0俺指搜索时数据精霸度大致匹配。 办编制背“盎基础工资袄”摆公式耙选中D4单元格澳，设为懊“颁数值癌”袄格式，并保留佰“斑2懊”蔼位小数。输入公昂式背“袄=ROUND(百VLOOKUP隘(A4隘,翱基础资料表!A案:I岸,傲5唉,芭0)耙/盎VLOOKUP扳(A4爸,扒相关资料!A:办G傲,岸4矮,靶0)*VLOO拜KUP(A4八,百相关资料!A:半G胺,袄6凹,扒0)哎

37、,爱0)爱”绊，回车。其中5摆是基础资料表中爱的【基础工资标啊准】的列号；4盎是相关资料表中巴的【应出勤天数颁】的列号；6是伴相关资料表中的坝【实出勤天数】耙的列号。澳基础工资=霸基础工资标准吧/阿应出勤天数鞍澳实出勤天数翱编制盎“昂绩效工资颁”把公式叭选中E4单元格败，设为奥“昂数值耙”白格式，并保留昂“般2哎”隘位小数。输入公皑式白“摆=ROUND(斑VLOOKUP瓣(A4，基础资霸料表!A:I唉,颁6拔,佰0)*VLOO佰KUP(A4稗,按相关资料!A:氨G把,般7隘,拌0)邦,巴0)皑”氨，回车。其中A耙4格是【员工代扳码】，员工的基啊础资料表!A:岸I指需要在其中拜搜索数据的信息矮表

38、区域，6是【背绩效工资标准】败的列号，0指搜癌索时数据精度大阿致匹配；员工的哎相关资料表!A氨:G指需要在其霸中搜索数据的信皑息表区域，7是百【绩效考核系数盎】的列号。拜绩效工资袄=绩效工资标准搬搬绩效考核系数碍调用扒“凹年功工资拜”稗选中F4单元格案，输入公式安“扒=VLOOKU拔P(A4艾,吧基础资料表!A拌:I蔼,半9皑,安0)盎”癌回车。其中A4哎格是【员工代码艾】，员工的基础爱资料表!A:I熬指需要在其中搜岸索数据的信息表岸区域，9是【年绊功工资】日期的懊列号，0指搜索扒时数据精度大致板匹配。伴调用伴“岸通讯补助叭”按选中G4单元格颁，输入公式岸“版=VLOOKU百P(A4胺,傲相关

39、资料!A:肮J暗,扒10吧,办0)巴”斑回车。其中A4安格是【员工代码霸】，员工的基础氨资料表!A:J颁指需要在其中搜耙索数据的信息表澳区域，10是【肮通讯补助】的列肮号，0指搜索时皑数据精度大致匹隘配。芭应发合计=敖基础工资+绩效背工资+年功工资芭+通讯补助（H霸4隘=霸 罢=SUM(D4伴:G4)氨）扮编制班“败日工资挨”背公式氨选中I4格，输办入公式笆“皑=ROUND(哎H4/VLOO俺KUP(A4,伴相关资料!A:挨D,4,0),鞍0)背”皑，按回车键确认芭。其中A4格是挨【员工代码】，佰员工的相关资料邦表!A:D指需把要在其中搜索数跋据的信息表区域百，4是【应出勤白天数】的列号，捌0

40、指搜索时数据摆精度大致匹配，按最后的0指四合跋五入为整数。氨编制班“败正常加班工资啊”阿公式熬选中J4格，输隘入公式艾“伴=VLOOKU白P(A4,相关案资料!A:L,耙8,0)*I4笆*2爱”班，回车。表示正艾常加班给予双倍捌工资补偿。8是白【日常加班天数吧】的列号，0指吧搜索时数据精度叭大致匹配，I4懊是工资明细表中笆的【日工资】单叭元格。搬编制唉“傲节日加班工资碍”背公式拌选中K4格，输摆入公式案“拔=VLOOKU办P(A4,相关八资料!A:L,案9,0)*I4澳*3蔼”翱，回车。表示按伴规定，节日加班瓣给予三倍工资补捌偿。9是【节日百加班天数】的列挨号，0指搜索时耙数据精度大致匹熬配，

41、I4是工资百明细表中的【日白工资】单元格。胺工资合计岸应发合计+正常俺加班工资+节日捌加班工资（L4笆=H4+J4+哎K4蔼）调用“住宿费”懊选中N4格，输懊入公式哎“艾=VLOOKU班P(A4搬,把相关资料!A:芭L哀,挨11班,翱0)搬”版，回车。瓣知识点哀ROUND函数澳用来返回某个数败字按指定数取整懊后的数字。傲函数语法懊ROUND(n靶umber颁，哀num柏_安digits)安Number：拌需要进行四合五扒入的数字懊n安um袄_digits哎：指定的位数，碍按此位数进行四奥合五入。扮函数说明拔如果n绊um稗_digits氨大于0，则四合背五入到指定的小靶数位。鞍如果n爱um白_d

42、igits埃等于0，则四舍矮五入到最接近的唉整数。捌如果n版um背_digits拔小于0，则在小哎数点的左侧进行摆四舍五入。按调用把“按代扣养老保险金盎”吧选中O4格，输懊入公式柏“斑=VLOOKU俺P(A4,相关靶资料!A:L,把12,0)俺”隘，回车。败1 053办计算个人所得税霸在前面我们制作扳了一张个人所得败税税率表，现在敖就要用到这张税艾率表，计算每个俺员工该缴纳的个碍人所得税了。应纳税所得额跋在R3格中输入拔“胺应纳税所得额罢”埃。选中R4，输哎入公式俺“伴=IF(L4搬税率表!$F$斑2捌,胺L4俺-懊税率表!$F$碍2摆,爸0)背”埃，回车。其中【盎税率表!$F$蔼2】是税率表

43、中疤的【起征额】。税率啊在按S3半格中输入碍“挨税率艾”芭。选中S4格，敖输入公式：唉“版=IF(R4=半0,0,LOO按KUP(R4,艾税率表!$C$拌2:$C$11叭,税率表!$D白$2:$D$1爸1)靶”爸，回车。其中【柏税率表!$C$啊2:$C$11巴】是税率表中的霸【上限范围】；颁【税率表!$D吧$2:$D$1拔1】是税率表中把的【扣税百分率癌】。俺知识点隘LOOKUP函办数是用来返回向哀量或数组中的数蔼值。班函数语法霸LOOKUP函艾数的语法有两种把形式，向量和数熬组，在我们涉及耙的例予中就是向盎量。背向量形式的语法肮LOOKUP(按1ookup_拜value，l扳ookup_v埃

44、ector，r搬esult_v哀ector)岸1ookup_伴value：为扳需要查找的数值半，数值可以是数耙字、文本、逻辑霸值或者包含数值懊的名称或引用。班lookup_肮vector：邦为之包含一行或伴一列的区域，数爱值可以是数字、爱文本或逻辑值。般如果是树枝则必碍须按升序排列，隘否则函数不能返胺回正确的结果。白result_翱vector：哀为只包含一行或白一列的区域，且熬如果looku背p_vecto芭r为行(列)，鞍result_盎vector也懊只能为行(列)傲，包含的数值的班个数也必须相同霸。拜上例中的公式所氨表达的意思是：澳如果R4=0则挨返回0值，否则八要在岸“矮基础资料表艾

45、”叭工作表中的C6跋：C15中查找埃等于R4的值或拔是小于R4又最败接近R4的值，搬并返回同行中D安（E）列的值。速算扣除数傲在T3格中输入摆“斑速算扣除数拌”昂。选中T4格，奥输入公式：蔼“癌=IF(R4=板0,0,LOO把KUP(R4,八税率表!$C$懊2:$C$11靶,税率表!$E唉$2:$E$1搬1)扒”芭，回车。其中【摆税率表!$C$啊2:$C$11绊】是税率表中的唉【上限范围】；八【税率表!$E哀$2:$E$1昂1】是税率表中皑的【扣除数】。跋个人所得税熬=伴应纳税所得额巴傲税率伴-凹速算扣除数（M笆4捌=R4*S4坝-安T4吧）爸实发合计扳=搬工资合计挨-隘个人所得税笆-瓣住宿费

46、瓣-捌代扣养老保险（班P4=蔼L4败-爸M4隘-扒N4摆-靶O4哎）编制工资条公式般插入一张新佰“版工资条表跋”癌并选中A1格输案入公式凹“半=IF(MOD百(ROW案(埃 靶)把,3)=0,埃,IF(MO俺D(ROW艾(瓣 袄)吧,3)=1,工班资明细表!A$阿3,INDEX碍(工资明细表!吧$A:$Q,I挨NT(ROW罢(拌 艾)瓣-暗1)/3)+4熬,COLUMN佰(巴 矮)敖)八”隘。叭将光标放到A1稗格右下角，变为傲黑十字形状时，懊向右拖动智能填矮充到P列松开，氨再选中A1：P把1向下智能填充佰，完成公式的复瓣制。再给一溜工蔼资条加边框线后背双击格式刷选中埃一个未加边框的瓣工资条添加

47、边框俺线。本例公式说明巴首先分析IND瓣EX(工资明细唉表!$A:$Q盎，INT(R皑OW澳(盎 唉)阿-哎1)/3)+4叭，其中行参数为百INT(RO芭W耙(岸 哎)傲-稗1)/3)+4碍，如果在第一行把输入该参数，结澳果是4，向下拖疤拽公式至20行吧，可以看到结果翱是4；4；4；扒5；5；5；5盎；6；6；6斑盎如果用白“爸INT(RO拜W邦(懊 瓣)版-靶1)/3)+4芭”袄做INDEx的傲行参数，公式将叭连续3行重复返氨回指定区域内的搬第4、5、6行傲的内容，而指定捌区域是版“斑工资明细表肮”稗工作表，第四行碍以下是人员记录哀的第一行，这样班就可以每隔3行般得到下一条记录跋。用COLU

48、M肮N罢(懊 笆)八做INDEX的佰列参数，当公式叭向右侧拖拽时，半列参数COLU八MN扮(奥 八)熬也随之增加。啊如果公式到此为耙止，返回的结果罢是每隔连续3行柏显示下一条记录捌，与期望的结果摆还有一定的差距捌。希望得到的结暗果是第一行显示昂字段、第二行显傲示记录、第三行背为空，这就需要坝做判断取值。如盎果当前行是第一跋行或是3的整数敖倍加1行，结果颁返叵I绊“扒工资明细表肮”邦工作表的字段行背。如果当前行是搬第二行或是3的翱整数倍加两行，蔼公式返回IND哀EX的结果；如把果当前行是3的版整数倍行，公式摆返回空。板公式中的第一个翱IF判断IF(碍MOD(ROW碍(佰 霸)班,3)=0,拜,澳

49、熬)用来判断3的矮整数倍行的情况艾，如果判断结果暗为肮“靶真霸”凹则返回空，第二澳个判断IF(M癌OD(RO佰W柏(碍 哀)啊,3)=1，工傲资明细表!A$扒3，坝芭)用来判断3的罢整数倍加l时的案情况，判断结果白为疤“佰真摆”背则返回工资明细跋表!A$3即字昂段行的内容；余艾下的情况则返N按INDEX函数盎段的结果。吧输入日期如7月把1日、7月2日捌、7月3日等想昂让他显示为1日邦、2日、3日：把可选中该区域后板设为日期格式，败再设为自定义下柏的半“斑d日颁”坝。板(2)选【正常颁出勤】W5格输坝公式翱“暗=COUNTI爱F(B5:V5隘,袄扒)可显示有几敖人正常出勤；在半【迟到次数】X奥1

50、0格输公式八“颁=COUNTI半F(拜B10罢:扳V10岸,8:30白)摆”扮(这里假设上班霸时间为8：30吧)回车。该格中肮便会出现选中员版工所有迟于8：唉30上班的工作胺日天数。同理输柏入公式般“按=COUNTI败F(邦B霸3扮:V般3肮，傲17跋:捌00)白”邦(假设下班时间爸为17：00)斑回车。该格中便斑会出现选中员工俺所有早于17：岸00下班的工作伴日天数。败(3)在【事假败次数】AA10鞍格输入公式背“暗=C稗OUNTIF(艾B胺2伴:V2，翱般事假安伴)耙”唉回车。AB2单俺元格中便出现了坝选中员工本月的安事假次数。巴(4)其他人的癌统计方法可以利挨用Excel的拌公式和相对引

51、用疤功能来完成。叭(5)单击埃“斑工具/选项/重板新计算瓣”耙选项卡，并单击拌“癌重算活动工作表肮”敖按钮。这样所有扮员工的考勤就全班部统计出来了。啊COUNTIF百函数只能有一个般条件，如大于9挨0，为=COU拜NTIF(A1埃：A10，挨=90)叭介于80与90绊之间需用减，为隘 =COUNT矮IF(A1:A柏10，80氨)-COUN罢TIF(A1:吧A10，9艾0)邦知识点碍INDEX数用澳来返回表或区域皑中的值或值的引板用。函数有两种扳形式：数组和引绊用。数组形式通隘常用来返回数值俺或数组数值，引叭用形式通常返回皑引用，这里我们唉学习到得是数组扒形式。办函数语法扒INDEX（a拌rra

52、y，ro隘w_num，c安olumn_n澳um）背array：为把单元格区域或数扮组常量。如果数啊组值包含一行或白一列，则只要选背择相对应的一个扮参数row_n袄um或colu版mn_num。八如果数组有多行吧或多列，但是只罢使用row_n搬um或colu袄mn_num，扮INDEX函数靶则返回数组中的傲整行或整列，且埃返回值也为数组半。盎row_num矮：为数组中的某瓣行的行序号，函柏数从该行返回数伴值。如果省略r颁ow_num，埃则必须有col澳umn_num扮。办column_艾num：为数组凹中某列的序列号挨，函数从该列返爸回数值。如果省隘略版column_巴num，则必须佰有row_

53、nu暗m。隘函数说明捌如果同时使用r芭ow_num和氨column_捌num，IND办EX函数则返同懊row_num罢和column拔_num交叉处爸的单元格的数值柏。半知识点鞍ROW函数用来把返回引用的行号巴。啊函数语法扮ROW叭（隘referen伴ce挨）般Referen捌ce：为需要得捌到其行号的单元暗格或单元格区域办。扒函数说明摆如果省略ref俺erence，绊则指奥ROW敖函数对所在单元板格的引用。如果氨referen胺ce为一个单元奥格区域，并且啊ROW澳函数作为垂直数半组输入，ROW摆函数则将rcf瓣erence的按行号以垂直数组笆的形式返回。爸知识点蔼COLUMN函按数用来返回

54、给定板引用的列标。懊函数语法伴COLUMN(矮referen碍ce)俺Referen背ce：为需要得唉到其列标的单元巴格或单元格区域凹。懊函数说明疤如果省略ref爱erence，班则假定为是对C拌OLUMN函数隘所在的单元格的疤引用。如果re叭ference笆为一个单元格区佰域，并且COL岸UMN函数作为安水平数组输入，奥COLUMN函半数则将refe扳rence的列癌标以水平数组形氨式返回。背1.爱 E办xcel中根据败身份证号提取出败生日期：凹=TEXT(D傲2,yyyy邦.mm.dd拜)半其中D2格是【癌19靶58暗-隘09奥-哀03】形式的出蔼生日期，套用此扳公式后变为【1办958.0

55、9.哀03】形式的出耙生日期。爱 【法二】霸：爸=MID(B3矮,7,4)&哎年&MID(绊B3,11,2埃)&月&M隘ID(B3,1柏3,2)&日百内容。绊对于15位身份翱证而言，7-1氨2位即个人的出扳生年月日，而最稗后一位奇数或偶肮数则分别表示男蔼性或女性。奇数半表示为男性，偶瓣数为女性；笆对于新式的18熬位身份证而言，哎7-14位代表把个人的出身年月按日，而倒数第二稗位的奇数或偶数拔则分别表示男性靶或女性）。翱MID函数与另伴一个名为MID叭B的函数，其作班用完全一样，不俺过MID仅适用翱于单字节文字，办而MIDB函数板则可用于汉字等暗双字节字符）。版自动计算性别的笆函数跋=IF(MO

56、D绊(IF(LEN拔(G3)=15芭,瓣MID(G3傲,巴15邦,敖1)疤,吧MID(G3耙,爸17霸,啊1)隘,懊2)=1靶,吧男案,埃女)其中艾G3斑处是输入的身份稗证信息；隘【法二】唉：肮=IF(MID半(B3,15,按1)/2=TR傲UNC(MID哎(B3,15,袄1)/2),翱女,男)把。这就表示取身矮份证号码的第1白5位数，若能被斑2整除，这表明柏该员工为女性，搬否则为男性。霸自动计算年龄的败函数凹=DATEDI按F(G3挨,蔼TODAY()矮,芭Y)安其中胺G3埃处是【自动计算板出的出生年月】扒的位置；TOD霸AY()函数获扮取的是系统当前白日期；傲“叭Y巴”扳是指时间段中的氨整

57、年数，因此对伴于当前系统日期背以前的出生年月哀都可精确计算，按对于当前系统日按期以后的出生年阿月将会少一岁，袄因此，计算年龄凹时因将系统日期耙调整为本年年末颁日期数。当单位摆代码为Y时哎，计算结果是两奥个日期间隔的年搬数。捌计算1973-袄4-1和当前日笆期的间隔月份数扮=DATEDI澳F(1973拜-4-1,T案ODAY(),捌M) 结果暗为403，当单罢位代码为M颁时，计算结果是伴两个日期间隔的艾月份数。袄计算1973-拔4-1和当前日白期的间隔天数隘=DATEDI瓣F(1973爱-4-1,T胺ODAY(),熬D) 结果拌为12273，懊当单位代码为爸D时，计算结跋果是两个日期间颁隔的天数

58、。跋计算1973-安4-1和当前日碍期的不计年数的般间隔天数百 =DATED伴IF(197哎3-4-1,岸TODAY()按,YD) 佰 结果为220背，当单位代码为氨YD时，计拌算结果是两个日暗期间隔的天数，版忽略年数差。氨计算1973-捌4-1和当前日芭期的不计月份和绊年份的间隔天数盎=DATEDI靶F(1973懊-4-1,T疤ODAY(),把MD) 佰结果为6，当单扮位代码为MD吧时，计算结果邦是两个日期间隔挨的天数，忽略年版数和月份之差。绊计算1973-罢4-1和当前日斑期的不计年份的氨间隔月份数碍=DATEDI稗F(1973扮-4-1,T按ODAY(),颁YM) 结挨果为7，当单位跋代

59、码为YM颁时,计算结果是皑两个日期间隔的碍月份数，不计相摆差年数。哀自动计算出生年班月的函数埃=DATE疤(MID耙(肮E佰3疤,隘7般,斑4)稗,稗MID(E3霸,靶11芭,柏2)岸,罢MID(E3爸,隘13氨,扮2)其中E胺3白格是【身份证号碍码】的位置；需败设为【日期】格靶式懊试用期到期时间颁函数奥=DATE(Y扒EAR(P3)半,MONTH(傲P3)+3,D熬AY(P3)-啊1)俺 其中P3是【坝入司时间】的位袄置，假设盎试用期昂为3个月，则在鞍Q3格中输入上傲述公式，其中M邦ONTH(P3吧)+3表示在此昂人入职时间月的鞍基础上增加三个稗月。而DAY(班P3)-1)是跋根据劳动合同签

60、啊订为整年正月而捌设置的。比如2扮009年3月3搬1日到2009八年6月30日为佰一个劳动合同签摆订期。安劳动合同到期时肮间函数蔼=DATE盎(YEAR(P拔3)+1罢,笆MONTH(P靶3)背,绊DAY(P3)哎-1)其中P3靶是【入司时间】邦的位置，假设劳搬动合同为1年，澳则需要设成YE蔼AR(P3)+半1，另外这个数芭值依然以入职日矮期为计算机根据皑，所以天数上还暗要设置成DAY矮(P3)-1的笆格式。吧续签合同到期时版间函数唉=DATE芭(YEAR(S笆3)+1翱,俺MONTH(S半3)霸,胺DAY(S3)碍) 其中P3是艾【入司时间】的哎位置，注意续签袄合同计算是以前版份合同签订到期