EXCEL在人力资源管理中的应用案例与技巧.docx

Excel案例Ol第1部分EXCWal概念I.名称名称可以代表个单元格或者个单元格区域，或者是常量，公式。名称的定义：选中须要命名的单元格或区域，在界面左上角名称框中输入名称后回车;名称的删除：插入一名称一定义，选中须要删除的名称点击删除按钮，点确定。名称的引用：须要引用某单元格时输入该单元格的名称：=名称第1步：选中须要命名的某个单元格或单元格区域。第2步：在左匕角名称根输入命名后回车。StandardTAB1金艳2Gl-I10003Gl-22000-5Gl-330005G2-140006G2-250007G2-360008删除已有命名：选择“插入”-“名称”-“定义”；选中须要删除的名称，点击“删除”按钮。我入9格式(Q)工H辽)故IgQ)窗口Qt)工作表回)Q_男%，.).粘贴电).:淀。Sffl).标至3函数(E).名称通三y)雕接g).CtrHK名称命名的优点：D避开确定引用的错误2)对公式进行文字化表述，让公式更加简洁理解3)可以在整个工作簿中通用，引用便利2,常用引用函数row:返回指定单元格的行号column:返回指定单元格的列标match:返回杳找值在杳找范围中的序号=match(杳找值，杳找范围，0)其笫三个参数为0,表示查找精确值address:返同单元格名称，其参数为行号列标或计算行号列标的表达式=address(行号,列标)=address(l,l)此公式返回Alindirect:返回单元格的值.其参数为单元格名称=indirect("Al"),假设Al=IO,则indirect返回10index:在某区域内查找某个位置的值=index(杳找区域，杳找值所在的行号，杳找值所在的列号)offset：指定基点，指定位移量，得到单元格引用单个单元格引用：=offset（基点单元格，向下移动的行，向右移动的列）区域的引用：=OffSeH基点单元格，向下移动的行，向右移动的列，区域包括的行数，区域包括的列数）向上和向左移动时，位移量为负值。=Offset（Aiaj）将得到B2单元格的值&动态引用动态引用是通过引用函数实现对于单元格或区域的相对引用。它和相对引用的效果很相像，但比简洁的相对引用用途广泛。常用的动态引用的实现方法有：引用函数嵌套。比如offset和row,column嵌套；index和match,indirect,address嵌套等；引用函数与控件结合运用，此类控件包括组合框，滚动条。动态引用的作用：实现对于单元格的动态引用；进行动态分析；制作动态报表。1.运算类型数值运算：1+1=2逻辑运算：1>O=TRUE在逻辑运算中，TRUE=I,FA1.SE=O我们利用逻辑运算进行条件推断在EXCel中常用的一些逻辑函数：if,and,or,not,iserror等and:只有当全部条件全部满意，才会返PItrUe的逻辑值语法：and（条件1,条件2,）or:满意其中任何一个条件，都会返IqtrUe的逻辑值第3部分BXCel基硝操作1 .快速选中数据表的蟋行或者整列Ctrl+shift+下箭头/右箭头2 .快速选中区域Ctrl+shift+83,行列互换豆制须要进行行列互换的区域后，将光标放置在数据表外面位置，选择性粘贴一选中“转置”选项4 .一个单元格内容输入为多行alt+回车键5 .显示公式Ctrl+6-冻结窗口选择须耍进行冻结的单元格位置，选择窗口一冻结窗格7 .自定义格式语法：I大于条件值格式；小于条件值格式；I等于条件值格式；文本格式8 .缩放数值o.oo,按百万缩放00,按万缩放0.00,按千缩放9不复制隐藏的行或列a首先选中须要复制的被隐藏了一些行或列的表格区域；b然后点击“编辑”-“定位”-“定位条件”，在其中选择“可见单元格”；c豆制表格区域，粘贴即可。案例。2一文本的处理1 .等长文本的分割从字符申的左边取字符：=1.eft（字符串，文本长度）从字符串的右边取字符：=Right（字符串，文本长度）从字符串的中间取字符：=Mid（字符申，文本起始位置，文本长度）2 .不等长文本的分割第1步：选中要进行分割的字符申区域。英文姓名英文曼称、stcven.IiJohn.Iieorgc.Iiu第2步：点击“数据”一“分列”，在文本分列向导中选择适合的分隔符。所在的单元格。6.罔示无效数据。选择“工具”一“公式审核”一“显示公式审核工具栏”，点击“圈示无效数据”按钮。公式审核该工具可以将全部不符合有效性设定的内容圈示出来。二日期的处理1.Datedif函数：用于计算两个日期之间的年数，月数，天数。=Datedif(起先口期，结束日期，"y”)第三个参数：V：表示年数"m”:表示月数“d”：表示天数2 .生日提示=DATEDlF(ToDAY(),DATE(YEAR(ToDAYO),MoNTH(G2),DAY(G2),"d")3 .某日期的星期=weekday(A2,2)返回的值是3,则表示该日期是星期三。4 .两个日期间的工作日天数=networkdays(起先日期，结束日期，节,假日列表)须要忏先加载“分析工具库”才能运用此工具案例03聂据查询1. V1.OOKUP()V1.OOKUP在表格或数值数组的首列杳找指定的数值，并由此返回表格或数组当前行中指定列处的数值。V1.OOKUP中的V代表垂直。V1.oC)KUP。OOkUP_VaIUe,table_array,col_index_num,rangejookup)1.OokUP_value为须要在数组第一列中杳找的数值。1.ookup_value可以为数值、引用或文本字符串。也可以理解为：两表共有的索引字段。Table,array为须要在其中杳找数据的数据表。可以运用对区域或区域名称的引用，例如数据库或列表。必需使得共有字段位于该范困的第列。CO1.indcxjium为须要调转的数据位于其次个参数中定义的范围的第几列。必需为单纯数值。rangjlookup定义大致匹配或精确匹配CFalse或0:精确匹配；true或忽视或1:假如无法找到精确匹配的值，那么就查找并匹配比查找值小的最近似的值。2. hlookup()H100kUP中的H是水平的意思，在横向排列的表格中查询数据用H100kUp,垂直排列的表格杳询用Vlookup函数。其参数含义与V1.OOKUP近似。案例04查询表信息杳询表利用有效性和V1.oOKUP函数实现。利用有效性实现索引字段的切换。利用V1.OoKUP函数查询与索引字段相关的侪息。案例05”箫选1 .自动筛选（蛤）2 .高级筛选由于自动箭选只能箭选出简洁条件的数据，因此如要在困难条件下进行筛选就须要采纳高级筛选的方式。在须要进行筛选的数据表外设定筛选条件.在对话框中设定：列表区域为数据表区域；条件区域为刚才在数据表外部设定的条件区域,要包括字段名称和条件所在的单元格区域。3 .条件格式条件格式可以使得符合特定条件的记录依据某种设定的格式显示。1）选中要进行条件格式设定的单元格范围2）执行【格式】【条件格式】三(fi)Iia(I)三u单元格圾）一ClriH行。）列IE）工作表QPI自动套用格式（）1.Z条件格式Qj）.3）在条件格式设置界面进行条件的设定:4 .统计FreqUenCy（须要进行统计的数据区域，分段点）对数据进行区间统计。此函数须要和数组公式起运用.此函数是依据数值的大小划分区间进行统计的C列出须要分析的工资区间，选中须要计算统计结果的单元格区域，输入函数:=frequency(P2:P22,R2:R7)_IniEWnrY(dMjur>y,bi”arruJiQjp-始烟性别血小年龄公司工龄T密系数工资金额婚婚婚婚婚婚婚婚未未己己未未未已女男女男女男女男BABAABOC28-1-3-2TT21121GGGGGOOO0O0O0O0O0O0O01060204030204030Q_1._frcucncv1500s27P22,R2zR7!2500OooQSOOQ5553456-按卜组合键：Ctrl+shift+enter5-排名次=rank（需排名数值，排名数据范围，排序方式）排序方式：O或忽视降序，否则为升序。案例06窗体控件的运用I.下拉菜单的制作。选择“视图”一“工具栏”一“窗体”，出现“窗体”工具条，在工具条上选择“组合框按下鼠标左键，拖放出合适的大小后释放鼠标，出现下拉菜单形态。选中该下拉菜单，右键选择“设置控件格式”在“限制”菜单上，去选择相应的数据源。“三维阴影”选项是卜拉菜单显示效果设定。单元格链接：该处指定的单元格中将存储在控件中被选中的项目在数据源中的序列号。我们常常利用该序列号引用控件选中的值，还可以利用该值制作动态图表。依据报销单上填报的日期自动生成该报销单的起止H期。Period:FromI200&9/3To200616运用最大值函数MAX和最小假函数MIN0运用身体工具条上的“选项按钮”制作单选按钮。Paymentpaty:COflPAKYPAYOC1.IimPAT2.界面定制行号列标，滚动条，工作表标签的隐藏,选择“工具”一“选项“一“视图”，将窗11选I工具(DI数学6)曲口保护©食称tM三dKIT1nIABMhI8U1.X>剪切9洁贴(?)方案(D公式审核处报戈除解g.>bmc)geesQt*(£。无S位Qtmref(DORtiEW©上里早aOt*5ffiw<±三m选项Q).滨传文字1.向导<!)>IHe分析它口HWWl9tiMP公大30WJBuQ>0RtUti)FIOBBIWMfR*ltfWlC)Oti*组合，叠故次再和,指定念设置拄件格式9z三勺项中的行号列标，水平滚动条，垂直滚动条，工作表标签选项去掉。案例07汇总神I.对于分布在同一文件中的表格汇总:忏先创建格式完全样的汇总表的表结构在汇总表须要汇总的项目上输入：=SUm()将光标放入括号，用鼠标选择第一张工作表，按下Shift键，再选取最终一张工作表，然后选择须要汇总的单元格，回车。将该公式复制到其他须要汇总的单元格即可实现整张表的汇总。2.对于分布在不同文件中的表格汇总：首先创建格式完全一样的汇总表的表结构将光标置于起始报表项目上选择【数据】一【合并计弟】数据。)I笆口M)帮助虬Ag妁旦)1排序¢)»有效性0,).I植项运苴表一分列©"""合并计井加).“引用位置”