搜索
VLOOKUP|PIVOTTABLE|DATAVALIDATION|FILL|SORT|SLICER|CONDITIONALFORMATTING|MATCH|COLLABORATION|PROTECTION|MERGE|SUBSTITUTION|COUNTIF|REFERENCE|SPARKLINES|HYPERLINK|Excel在人事信息管理中的妙用广东方胜人力资源服务有限公司广东方胜内容提要创建“静态”的人事信息表并分析静态•用表格管理数据•从身份证号中提取信息•用数据透视表进行分析•用条件格式监控数据•用数据有效性管理数据创建“动态”的人事信息汇总报告动态•对表格进行动态计数•对表格进行动态求和•动态排序的制作•动态图表的制作广东方胜内容提要创建“静态”的人事信息表并分析静态•用表格管理数据•从身份证号中提取信息•用数据透视表进行分析•用条件格式监控数据•用数据有效性管理数据创建“动态”的人事信息汇总报告动态•对表格进行动态计数•对表格进行动态求和•动态排序的制作•动态图表的制作广东方胜日期函数函数说明函数说明DATE根据年月日返回特定日期NOW返回当前日期和时间的序列号DATEVALUE将文本格式的日期转换为日期格式的日期SECOND将序列号转换为秒DAY将序列号转换为月份日期TIME返回特定时间的序列号DAYS360以一年360天为基准计算两个日期间的天数TIMEVALUE将文本格式的时间转换为序列号EDATE返回表示开始日期之前或之后月数的日期TODAY返回今天日期的序列号EOMONTH返回指定月数之前或之后的月份的最后一天WEEKDAY将序列号转换为星期日期HOUR将序列号转换为小时WEEKNUM将序列号转换为代表该一年中第几周的数字MINUTE将序列号转换为分钟WORKDAY返回指定的若干个工作日之前或之后的日期MONTH将序列号转换为月YEAR将序列号转换为年NETWORKDAYS返回两个日期间的全部工作日数YEARFRAC返回代表起始日和结束日之间整天天数的年分数、FINDB在一个文本值中查找（区分大小写）TRIM删除文本中的空格SEARCH、SEARCHB在一个文本值中查找（不区分大小写）CONCATENATE将几个文本项合并为一个文本项REPLACE、REPLACEB替换文本中的字符DOLLAR使用货币格式将数字转换为文本SUBSTITUTE在文本字符串中用新文本替换旧文本FIXED将数字设为具有固定小数位数的文本REPT按给定次数重复文本T将参数转换为文本LEN、LENB返回文本字符串中的字符个数TEXT设置数字格式并将其转换为文本LEFT、LEFTB返回文本值中最左边的字符VALUE将文本参数转换为数字MID、MIDB从指定位置起返回特定个数的字符UPPER将文本转换为大写形式RIGHT、RIGHTB返回文本值中最右边的字符函数•DATEDIF(start_date,end_date,unit)–Start_date为一个日期，它代表时间段内的第一个日期或起始日期–End_date为一个日期，它代表时间段内的最后一个日期或结束日期–Unit为所需信息的返回类型，六种类型可选：•“Y”返回时间段中的整年数•“YM”返回时间段中扣掉整年之后整月数•“M”返回时间段中的整月数•“MD”返回时间段中扣掉整月之后天数•“D”返回时间段中的天数•“YD”返回时间段中扣掉整年之后天数广东方胜身份证号中提取生日、性别信息•生日：=DATE(MID(C2,7,4),MID(C2,11,2),MID(C2,13,2))•性别：=IF(ISEVEN(MID(C2,17,1)),女,男)•年龄：=DATEDIF(J2,TODAY(),Y)•工龄：=DATEDIF(G2,TODAY(),Y)广东方胜条件格式•数据条•色阶•图标集•突出显示•项目选取•重复值•唯一值广东方胜用条件格式制作自动提醒自动提醒要求：高亮出未来三十天到期的合同自动提醒公式：=AND($H2TODAY(),$H2TODAY()+30)格式适用范围：=$A$2:$M$65536公式编写要求：针对适用范围左上角的单元格编写，需要注意相对应用和绝对应用广东方胜数据有效性•数据有效性–限制为整数–限制为小数–限制为时间–显示为日期–限制为列表–限制文本长度–根据公式限制广东方胜值汇总方式设置汇总方式对应函数求和Sum计数Count平均值Average最大值Max最小值Min乘积Product标准偏差StDev总体标准偏差StDevp方差Var总体方差Varp广东方胜值显示方式设置值显示方式要显示或计算无计算在该字段中输入的值。总计的百分比值为报表中所有值或数据点的总计的百分比。列汇总百分比每个列或系列中的所有值为列或系列的汇总的百分比。行汇总百分比每个行或类别中的值为行或类别中的汇总的百分比。百分比值为基本字段中选定的基本项的值的百分比。父行汇总百分比值为：（该项的值）/（行上父项的值）父列汇总百分比值为：（该项的值）/（列上父项的值）父级汇总的百分比值为：（该项的值）/（所选基本字段中父项的值）差异值为与基本字段中所选基本项的值的差异。差异百分比值为与基本字段中所选基本项的值的差异的百分比。按某一字段汇总所选基本字段中连续项的汇总。按某一字段汇总的百分比值为所选基本字段中连续项的汇总百分比。按升序排名某一特定字段中所选值的升序排位。按降序排名某一特定字段中所选值的降序排位。索引值为：((单元格中的值)x(整体总计))/((行总计)x(列总计))广东方胜内容提要创建“静态”的人事信息表并分析静态•用表格管理数据•从身份证号中提取信息•用数据透视表进行分析•用条件格式监控数据•用数据有效性管理数据创建“动态”的人事信息汇总报告动态•对表格进行动态计数•对表格进行动态求和•动态排序的制作•动态图表的制作广东方胜条件求和与条件计数•COUNTIF(range,criteria)•COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2]…)•SUMIF(range,criteria,[sum_range])•SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)–sum_range，求和区域。忽略空白和文本值。–criteria_range，条件区域。–criteria。条件。条件的形式为数字、表达式、单元格引用或文本，可用来定义将对条件区域中的哪些单元格求和。例如，条件可以表示为32、“32”、B4、“苹果”或“32”。条件也可以使用通配符。函数•RANK(number,ref,[order])–Number必需。需要找到排位的数字。–Ref必需。数字列表数组或对数字列表的引用。Ref中的非数值型值将被忽略。–Order可选。一数字，指明数字排位的方式。•如果order为0（零）或省略，MicrosoftExcel对数字的排位是基于ref为按照降序排列的列表。•如果order不为零，MicrosoftExcel对数字的排位是基于ref为按照升序排列的列表。广东方胜动态图表：窗体控件复选框组合框分组框数值调节按钮选项按钮滚动条广东方胜窗体控件属性组合框控件格式：•数据源区域：下拉列表内容•单元格链接：存储用户选择结果复选框控件格式：•值：用户选择状况•单元格链接：存储用户选择结果广东方胜用组合框制作动态图表主要知识点：•组合框控件：交由用户选择，得到选项的序列号•SERIERS函数：图表中的数据系列，并用名称管理数据源•OFFSET函数：根据用户选择的序列号移动图表数据源函数•OFFSET(reference,rows,cols,[height],[width])–Reference必需。作为偏移量参照系的引用区域。Reference必须为对单元格或相连单元格区域的引用；否则，OFFSET返回错误值#VALUE!。–Rows必需。相对于偏移量参照系的左上角单元格，上（下）偏移的行数。如果使用5作为参数Rows，则说明目标引用区域的左上角单元格比reference低5行。行数可为正数（代表在起始引用的下方）或负数（代表在起始引用的上方）。–Cols必需。相对于偏移量参照系的左上角单元格，左（右）偏移的列数。如果使用5作为参数Cols，则说明目标引用区域的左上角的单元格比reference靠右5列。列数可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）。–Height可选。高度，即所要返回的引用区域的行数。Height必须为正数。–Width可选。宽度，即所要返回的引用区域的列数。Width必须为正数。广东方胜用复选框制作动态图表主要知识点：•复选框控件：交由用户选择是否选中，得到每个选项的TRUE/FALSE值•IF函数：将TURE/FALSE值转化成1/0数值，便于后续计算•SUMPRODUCT函数：根据用户选中与否的结果，得到柱形图应当展示的数据