如何巧用电子表格做工资及员工档案.xlsx

在在人人事事表表单单中中使使用用函函数数edateemonthdatedifmonthroundup 转转换换中中文文大大小小写写金金额额 迅迅速速查查找找和和跨跨表表引引用用indexmatchindirect 工工资资的的管管理理vlookup 批批量量制制作作工工资资条条排序邮件合并 创创建建工工资资查查询询系系统统 int 人事信息到期提醒表 序序号号员员工工编编号号 员员工工姓姓名名参参工工时时间间身身份份证证号号出出生生日日期期 01ay0001胡 军2003年4月15日 5111291981030220111981年03月02日 02ay0003陈 波2004年4月16日 5111291982101020921982年10月10日 03ay0002闫晓洪2005年4月20日 5111291981041220711981年04月12日 04ay0004敖雪东2006年3月12日 5111298111052031981年11月05日 05ay0005陈小萍2004年5月18日 5111291984081520611984年08月15日 06ay0006郑安平2003年5月10日 5111291981032720411981年03月27日 07ay0007满 雄2005年4月21日 5111291981061220511981年06月12日 08ay0008廖 彬2004年7月19日 5111291981011720311981年01月17日 09ay0009邓 勇2006年10月8日 5111291980121320111980年12月13日 10ay0010陈 霞2005年3月26日 5111298103212021981年03月21日 11ay0011郑青松2004年5月15日 5111291982111520121982年11月15日 12ay0012廖挺鸿2003年4月16日 5111298104132041981年04月13日 13ay0013王燕玲2006年2月17日 5111291984101512211984年10月15日 14ay0014邓 群2005年6月27日 5111298308152061983年08月15日 15ay0015闫 波2003年4月19日 5111298109120011981年09月12日 16ay0016王 霞1993年4月15日 5111291971041520211971年04月15日 17ay0017张伯蓉1984年3月18日 5111291962101020821962年10月10日 18ay0018王 艳1997年8月19日 5111291975041220411975年04月12日 19ay0019张 菁2002年6月29日 5111298011052041980年11月05日 20ay0020陈 好2005年6月27日 5111291982081520611982年08月15日 21ay0021黄大秀1971年6月26日 5111291959050620411959年05月06日 22ay0022陈道华1982年9月12日 5111291961050620311961年05月06日 23ay0023陈红林1969年10月9日 5111291949050620111949年05月06日 24ay0024王志刚1970年11月8日 5111291950121320111950年12月13日 25ay0025陈进军1992年11月9日 5111297103212011971年03月21日 26ay0026邓欣雨1994年5月26日 5111291972101520421972年10月15日 27ay0027张林强1980年10月8日 5111296105062031961年05月06日 28ay0028郑克强1980年6月25日 5111291974101512311974年10月15日 29ay0029张荣恩2003年6月26日 5111298108152051981年08月15日 30ay0030谢雨欣2002年5月16日 5111298006150041980年06月15日 进进入入公公司司时时间间试试用用期期限限签签订订合合同同日日期期合合同同期期限限性性别别年年龄龄工工龄龄生生日日到到期期 2006年6月30日3个月2006年9月30日3年男3211 2007年5月12日3个月2007年8月12日3年男3010 2008年6月12日3个月2008年9月12日3年男329 2007年1月26日3个月2007年4月26日3年男318 2006年2月6日3个月2006年5月6日3年女289 2008年1月12日3个月2008年4月12日3年女3210 2008年5月24日3个月2008年8月24日3年男319 2006年5月23日3个月2006年8月23日3年男329 2009年2月6日3个月无无男327 2008年3月26日3个月2008年6月26日3年女329 2005年6月30日3个月2008年9月30日3年男309 2004年5月30日3个月2007年8月30日3年女3211 2008年12月1日3个月2009年3月1日3年女288 2006年10月8日3个月2007年1月8日3年女298 2005年6月30日3个月2008年9月30日3年男3111 1998年4月26日3个月2004年7月26日无固定女4221 1990年6月30日3个月1996年9月30日无固定女5030 2000年4月15日3个月2006年7月15日无固定女3816 2006年4月25日3个月2006年7月25日3年女3211 2006年2月26日3个月2006年5月26日3年女308 1980年4月12日3个月1986年7月12日无固定女5342 1998年6月24日3个月2004年9月24日无固定男5131 1999年2月16日3个月2005年5月16日无固定男6344 2001年4月25日3个月2007年7月25日无固定男6243 2004年3月16日3个月2007年6月16日3年男4221 2000年4月15日3个月2006年7月15日无固定女4019 2006年4月25日3个月2006年7月25日3年男5133 2006年2月6日3个月2006年5月6日3年男3833 2006年10月8日3个月2007年1月8日3年男3110 2005年6月30日3个月2008年9月30日3年女3211 人事信息到期提醒表 试试用用到到期期 合合同同到到期期 退退休休到到期期退退休休日日期期退退休休日日期期工工龄龄第第几几季季度度签签订订合合同同 2041年3月31日2036/3/21203 3 3 3 2 2 人事信息到期提醒表 目标值：5188.15 各部分转换值 整数部分伍仟壹佰捌拾捌元 角位壹角 分位伍分 角分位壹角伍分 判断条件 是否为零false 是否只有整数部分false 是否没有整数部分false 是否只有角false 是否只有分false 转换值：伍仟壹佰捌拾捌元壹角伍分 分三次转换 元位角分位转换值 伍仟壹佰捌拾捌元壹角伍分伍仟壹佰捌拾捌元壹角伍分 伍仟壹佰捌拾捌元壹角伍分伍仟壹佰捌拾捌元壹角伍分 目标值：200.11 各部分转换值 整数部分贰佰元 角位壹角 分位壹分 角分位壹角壹分 判断条件 是否为零false 是否只有整数部分false 是否没有整数部分false 是否只有角false 是否只有分false 转换值：贰佰元壹角壹分 分三次转换 元位角分位转换值 贰佰元壹角壹分贰佰元壹角壹分 贰佰元壹角壹分贰佰元壹角壹分 部门生产部 费用项目水电费 金额1464 四月份各部门费用明细表 费费用用项项目目办办公公室室财财务务部部信信息息部部销销售售部部生生产产部部人人力力资资源源部部合合计计 薪金234324334532576876659854532455318018 广告费355512655876524975 水电费23554367612414643453387 保险费248502224422816159717166681899865 通讯费53457632448766455234415919 培训费878670760111787910215325 杂费1234235465487924342437798 费用总额54718697326072612717711094351991475287 姓名照片 唐僧 猪八戒 孙悟空 沙和尚 姓名照片 沙和尚沙和尚 广东省浙江省辽宁省四川省河北省湖南省安徽省 广州市杭州市沈阳市成都市石家庄市长沙市合肥市 深圳市宁波市大连市自贡市唐山市株洲市芜湖市 珠海市温州市鞍山市攀枝花市秦皇岛市湘潭市蚌埠市省份 汕头市嘉兴市抚顺市泸州市邯郸市衡阳市淮南市广东省 韶关市湖州市本溪市德阳市邢台市邵阳市马鞍山市 佛山市绍兴市丹东市绵阳市保定市岳阳市淮北市 江门市金华市锦州市广元市张家口市常德市铜陵市 湛江市衢州市营口市遂宁市承德市张家界市安庆市 茂名市舟山市阜新市内江市沧州市益阳市黄山市 肇庆市台州市辽阳市乐山市廊坊市郴州市滁州市 惠州市丽水市盘锦市南充市衡水市永州市阜阳市 梅州市铁岭市宜宾市怀化市宿州市 汕尾市朝阳市广安市娄底市巢湖市 河源市葫芦岛市达州市湘西州六安市 阳江市眉山市亳州市 清远市雅安市池州市 东莞市巴中市宣城市 中山市资阳市 潮州市阿坝州 揭阳市甘孜州 云浮市凉山州 城市 广州市 quarter1 office63 windows66 xbox70 quarter2 office93 windows90 xbox99 quarter3 office77 windows58 xbox60officewindowsxbox quarter1636670 quarter4quarter2939099 office97quarter3775860 windows56quarter4975695 xbox95 abcdefghij 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 员工销售奖金的计算 就可以查找出来税率，税款表2 查查找找业业务务员员不不同同商商场场的的销销售售额额表六 批量制作工资条名称为工资表 工资查询 对月度工资按部门汇总 工资发放零钞备用表 加班记录表-如何计算上班小时数 今天前后星期几 员工年假表 员工 销售产品 工作业绩奖金 冰箱洗衣机空调 彩电 李辰1210520 ￥117,045.40￥11,705.00 刘欣259315 ￥135,566.11￥13,557.00 杨建国6211019 ￥136,243.79￥13,625.00 王心语2116931 ￥195,134.44￥19,514.00 田宏涛15141225 ￥174,465.16￥17,447.00 张小丽138616 ￥112,987.82￥11,299.00 刘保国30201410 ￥206,783.40￥20,679.00 单价￥3,050.50￥1,560.99￥4,489.90￥2,119.00 总库存量200100100150 现库存量7824114 基本工资总工资 ￥600.00￥12,305.00公公式式说说明明（结结果果） ￥600.00￥14,157.00 4将 3.2 向上舍入， 小数位为 0 (4) ￥600.00￥14,225.0077 将 76.9 向上舍入 ，小数位为 0 (77) ￥600.00￥20,114.003.142 将 3.14159 向上舍 入，保留三位小数 (3.142) ￥600.00￥18,047.00-3.2 将 -3.14159 向上 舍入，保留一位小 数 (-3.2) ￥600.00￥11,899.0031500 将 31415.92654 向上舍入到小数点 左侧两位 (31500) ￥600.00￥21,279.00 -3.1 不同于round vlookupvlookup(lookup_valuelookup_value,table_arraytable_array,col_index_numcol_index_num,range_lookup) lookup_valuelookup_value 为需要在table_array第一列中查找的数值。 可以为数值、引用或文本字符串。需要注意的是类型必须与table_array第一列的类型一致。 查找文本时，文本不区分大小写；可以使用通配符“*”、“?”。 table_arraytable_array 为需要在其中查找数据的数据表。 可以使用对区域或区域名称的引用、常数数组、计算后的内存数组。 对区域引用时，可以引用整列,excel会自动判断使用区域。 该参数的第一列必须包含查找的内容，其它列包含需返回的内容；返回内容的列序号由下个参数指定。 col_index_numcol_index_num 为table_array中待返回的匹配值的列序号。 如为1时，返回table_array第一列中的数值；为2，返回table_array第二列中的数值，以此类推。 如果col_index_num小于1，函数 vlookup 返回错误值值 #value!； 如果col_index_num大于table_array的列数，函数 vlookup 返回错误值 #ref!。 range_lookup 为一逻辑值，指明函数vlookup返回时是精确匹配还是近似匹配。 如果为true或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值； 近似匹配查询一般用于数值的查询，table_arraytable_array的的第第一一列列必必须须按按升升序序排排列列；否则不能返回正确的结果。 如果range_value为false（或0），函数vlookup将返回精确匹配值。 此时，table_array不必进行排序。如果找不到，则返回错误值#n/a；可isna检测错误后使用if判断去除错误信息。 处理查找错误 例1精确匹配查询之基本运用例8在同一单元格按不同字段查询 例2数值近似匹配查询之基本运用例9返回多个符合条件的查询结果 例3文本近似匹配查询之基本运用例10有两个并列条件（不同字段）的查询 例4区别查询结果的空白与0值例11使用数组常量的查询 例5依次序返回同一查询结果的多列内容例12从右向左查询 例6返回同一查询结果的多列（不按原次序）的内容 例7按部分内容模糊查询 以上举例是vlookup的基本运用，再结合其它函数，可以发挥其更多作用。 可以为数值、引用或文本字符串。需要注意的是类型必须与table_array第一列的类型一致。 该参数的第一列必须包含查找的内容，其它列包含需返回的内容；返回内容的列序号由下个参数指定。 如为1时，返回table_array第一列中的数值；为2，返回table_array第二列中的数值，以此类推。 如果col_index_num小于1，函数 vlookup 返回错误值值 #value!； 如果col_index_num大于table_array的列数，函数 vlookup 返回错误值 #ref!。 如果为true或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值； 近似匹配查询一般用于数值的查询，table_arraytable_array的的第第一一列列必必须须按按升升序序排排列列；否则不能返回正确的结果。 如果range_value为false（或0），函数vlookup将返回精确匹配值。 此时，table_array不必进行排序。如果找不到，则返回错误值#n/a；可isna检测错误后使用if判断去除错误信息。 在同一单元格按不同字段查询 返回多个符合条件的查询结果 有两个并列条件（不同字段）的查询 vlookupvlookup应应用用实实例例 返返回回 精精确确匹匹配配查查询询之之基基本本运运用用 精确查找是vlookup最基本也是最常用的功能，对于数据量大的查找，其速度比菜单中的查找还快。设置vlookup第四个参数为false或0，即为精确查找。 精确查找适用于文本，也适用于数值；但对数值查查找找时时须须注注意意格格式式一一致致，否则会出错 表表一一姓姓名名工工号号性性别别籍籍贯贯出出生生年年月月 张三丰kt001男北京1970年8月 李四光kt002女天津1980年9月 王麻子kt003男河北1975年3月 赵六儿kt004女河南1985年12月 姓姓名名籍籍贯贯 赵六儿河南根据姓名在$b$9:$f$12中查找籍贯，$b$9:$f$12区域最好使用绝对引用（可在公式中选定区域按f4转换），便于复制。 查找的姓名必须位于$b$6:$f$9的第一列；籍贯在$b$6:$f$9位于第四列，因此第三个参数为4；精确查找，第四个参数false 姓姓名名性性别别 李四光女根据姓名在【表一】中查找籍贯 【表一】是定义的单元格区域$b$9:$f$12的名称，在名称框（编辑栏左边）可以选定；也可在插入名称定义中查看修改 工工号号籍籍贯贯 kt002天津根据工号在$c$8:$e$12中查找籍贯 注意范围已改为$c$8:$e$12，确保工号在第一列；由于范围的改变，籍贯位于该范围的第3列，因此第三个参数为3 工工号号出出生生年年月月 kt00125781根据工号在$c$8:$f$12中查找出生年月，但此时返回的是时间序列值（即格式没有带过来） 1970年8月需要重新设定单元格格式 1970年8月或在公式中使用text设定格式 数数值值近近似似匹匹配配查查询询之之基基本本运运用用 近似匹配查找通常情况下用于累进数值的查找。此时第四个参数省略，或为true，或为非0数值。（excel中0等同false，非零值均视为true） 这是一张个所税税率表，对于不确定的收入，可以利用近似匹配查找税率及速扣数 表表二二级级数数 应应税税所所得得超超过过且且不不超超过过税税率率(%) 速速算算扣扣除除数数 150050 250020001025 32000500015125 450002000020375 52000040000251375 64000060000303375 76000080000356375 8800001000004010375 91000004515375 应应税税所所得得税税率率速速算算扣扣除除数数 税税款款 536020375697 5360在表中未列出，因此excel查找小于5360的最大值即5000，并返回对应的税率等。 注意公式中第四个参数省略，或为true，或为非0数值。 并且查找的第一列必须升序排列，否则不会返回期望的结果（需要注意的是此时不一定返回错误）。 试着输入5000，你会发现税率为20%，应是15%（由条件指定），虽然对最后计算结果没有影响。以下公式可以解决这个问题： 应应税税所所得得税税率率速速算算扣扣除除数数 税税款款 5000151256251520三个结果是不一样的 文文本本近近似似匹匹配配查查询询之之基基本本运运用用 表表三三对于文本，一般不使用近似匹配查找（非模糊查找）。 吖a并非文本不能用于近似查找，主要是没有太多用途。除了下面这个例子： 八b 嚓c这个例子利用文本近似匹配查找汉字的第一个拼音字母 咑d由于excel中汉字字符是按拼音排序的，因此汉字字符比较时的大小与拼音同序（一般情况下） 鵽e左边的表是根据汉字拼音首字母排列，并取各字母的临界点汉字字符（该字母的“最小汉字”） 发f 猤g汉汉字字字字符符拼拼音音首首字字母母 铪h家j 夻j 咔k通过将“家”与表中字符比较后，找到比“家”小的最大值“夻”，返回j 垃l这个例子一般用于姓名的缩写，而上面的公式只找第一个字符，因此需要修改 嘸m 旀n汉汉字字字字符符拼拼音音首首字字母母 噢o普利卡plk 妑p 七q这个公式取得前三个汉字字符的拼音字母。使用mid依次取出字符后由vlookup查询到字母，最后“20;30;40，是一列四行（纵向）、包含四个元素的一维数组常量 10,20,30;40,50,60，是两行三列、包含六个元素的二维数组常量；二维数组常量需要行列等长 如果你觉得输数组常量麻烦（要区分【,】、【;】，文本要加【“】）， 可以先将数据输入单元格区域，然后在其它单元格输入【=】，用鼠标选定该区域后按f9，显示的就是数组常量，然后复制到公式中 其实将以前使用的vlookup公式中第三个参数（区域不要太大）选定按f9，再回车，可以看到公式正常运作；此时已使用了数组常量 以下举例中的公式并不是数组公式，输入时不用ctrl+shift+enter一齐按 搬一个由chenjun版主提供的计算个人所得税的例子： 应应税税所所得得税税率率速速算算扣扣除除数数 税税款款 650020375925 再搬一个前面的例子 汉汉字字字字符符拼拼音音首首字字母母 海h是否被公式中的数组常量吓一跳？不是一个个输的，按f9得到的； 汉汉字字字字符符拼拼音音首首字字母母 螺丝扣lqk对于此类固定的数组常量可以将其定义一个名称，公式就简洁多了，还不占用单元格 公式中的【拼音】就是一个定义的名称，可以自【插入】【名称】【定义】中查看 从从右右向向左左查查询询 我们知道，vlookup查找的列必须位于查找区域的最左列，有时会需要按右面的查找左边的值 以【表一】为例，要按【姓名】查询【工号】很容易；但反过来按【工号】查找【姓名】直接使用vlookup就不行了 此时可以使用的公式很多，如lookup、index+match、offset+match、indirect+match等 但由chenjun版主提供的使用vlookup的解法我觉得很有参考价值，介绍这个用法的目的主要是学会将两个一维数组合并为一个二维数组 工工号号姓姓名名 kt002李四光这个公式先由【if(1,0,$c$9:$c$12,$b$9:$b$12)】计算出内存数组： “kt001“,“张三丰“;“kt002“,“李四光“;“kt003“,“王麻子“;“kt004“,“赵六儿“ 可以看到，在该内存数组中，【工号】位于【姓名】的左侧，vlookup在该内存数组中查找 注意1,0是一行二列（横向）常量数组，后面的两个区域是多行一列（纵向），即两个数组的方向不同，这样才会生成两列多行数组 张三丰 李四光 王麻子 赵六儿 kt001 kt002 kt003 kt004 精确查找是vlookup最基本也是最常用的功能，对于数据量大的查找，其速度比菜单中的查找还快。设置vlookup第四个参数为false或0，即为精确查找。 根据姓名在$b$9:$f$12中查找籍贯，$b$9:$f$12区域最好使用绝对引用（可在公式中选定区域按f4转换），便于复制。 查找的姓名必须位于$b$6:$f$9的第一列；籍贯在$b$6:$f$9位于第四列，因此第三个参数为4；精确查找，第四个参数false 【表一】是定义的单元格区域$b$9:$f$12的名称，在名称框（编辑栏左边）可以选定；也可在插入名称定义中查看修改 注意范围已改为$c$8:$e$12，确保工号在第一列；由于范围的改变，籍贯位于该范围的第3列，因此第三个参数为3 根据工号在$c$8:$f$12中查找出生年月，但此时返回的是时间序列值（即格式没有带过来） 近似匹配查找通常情况下用于累进数值的查找。此时第四个参数省略，或为true，或为非0数值。（excel中0等同false，非零值均视为true） 试着输入5000，你会发现税率为20%，应是15%（由条件指定），虽然对最后计算结果没有影响。以下公式可以解决这个问题： 三个结果是不一样的 并非文本不能用于近似查找，主要是没有太多用途。除了下面这个例子： 由于excel中汉字字符是按拼音排序的，因此汉字字符比较时的大小与拼音同序（一般情况下） 左边的表是根据汉字拼音首字母排列，并取各字母的临界点汉字字符（该字母的“最小汉字”） 通过将“家”与表中字符比较后，找到比“家”小的最大值“夻”，返回j 这个例子一般用于姓名的缩写，而上面的公式只找第一个字符，因此需要修改 这个公式取得前三个汉字字符的拼音字母。使用mid依次取出字符后由vlookup查询到字母，最后“40,50,60，是两行三列、包含六个元素的二维数组常量；二维数组常量需要行列等长 可以先将数据输入单元格区域，然后在其它单元格输入【=】，用鼠标选定该区域后按f9，显示的就是数组常量，然后复制到公式中 其实将以前使用的vlookup公式中第三个参数（区域不要太大）选定按f9，再回车，可以看到公式正常运作；此时已使用了数组常量 对于此类固定的数组常量可以将其定义一个名称，公式就简洁多了，还不占用单元格 公式中的【拼音】就是一个定义的名称，可以自【插入】【名称】【定义】中查看 以【表一】为例，要按【姓名】查询【工号】很容易；但反过来按【工号】查找【姓名】直接使用vlookup就不行了 此时可以使用的公式很多，如lookup、index+match、offset+match、indirect+match等 但由chenjun版主提供的使用vlookup的解法我觉得很有参考价值，介绍这个用法的目的主要是学会将两个一维数组合并为一个二维数组 “kt001“,“张三丰“;“kt002“,“李四光“;“kt003“,“王麻子“;“kt004“,“赵六儿“ 可以看到，在该内存数组中，【工号】位于【姓名】的左侧，vlookup在该内存数组中查找 注意1,0是一行二列（横向）常量数组，后面的两个区域是多行一列（纵向），即两个数组的方向不同，这样才会生成两列多行数组 处处理理查查找找错错误误 返返回回 vlookup使用中一般会产生以下错误： #value!和 #ref!是由于col_index_num设置不当造成的，虽然可以使用iserror剔除，但建议不要这么做，因为这类错误需要纠正。 有时col_index_num参数使用column()函数生成，当设置不当造成的错误用iserror剔除时，会与#n/a错误混淆，不利于公式查错。 #n/a是常见的错误信息，需要针对不同情况处理： 精确匹配查找时出现，是由于未找到完全匹配的值。 近似匹配查找时出现，是由于查找值小于数据区的最小值。 如果vlookup的参数产生错误，vlooukp也返回相同错误，那不在本讨论范围内。 精确匹配查找时出现#n/a错误 首先检查引用范围是否正确，查询值是否在数据区的第一列。 当确定引用正确、不应出现#n/a（即应该查找到值）时，检查是否存在空格或其它字符、格式是否一致 表表一一 张三丰100 对于空格或其他不可见字符，可以使用len函数测试文本长度来确定，或使用code测试其ascii码 李四军 200 b18单元格长度 4 数 据 含 空 格 等 张三丰 #n/a 该错误由于查询字符“张三丰 ”后含空格，可能看不出，但在精确匹配查找时造成不完全匹配 100 对于lookup_value中的空格，将其中的空格替换掉，或使用trim去除空格后查找 李四军#n/a 该错误是由于查询表表四中“李四军”后含空格，在精确匹配查找时不完全匹配 200 对于table_array中的空格，也可以使用trim。 这个公式中的table_array已不是直接引用单元格区域，而是引用对$b$87:$c$88运算trim后的内存数组 因此，这是一个数组公式，需要按ctrl+shift+enter输入 如果数据区比较大，数组公式会很慢；不如直接使用替换将数据区的空格替换后使用普通公式。 李四军 #n/a 该错误由于查询字符“李四军”后含不可见字符（非空格），使用trim、clean、替换空格都不能去除。 由网页复制、其他程序转出的表格，经常会出现这类字符，code可以测试其并非空格 对于这类不可见字符，可以先复制该字符，然后替换该字符为空白 表表二二 100a 查找内容格式不符，也会造成n/a错误 200b 可以使用鼠标选定几个单元格，看状态栏的合计。有，则为数值，无，则为文本。 数 据 格 式 不 一 致 100#n/a 这两个错误都是由于格式不一致造成，一个是按数值查文本，一个是按文本查数值 200#n/a 对于此类错误，可以修改查找值、数据区的格式，使之统一；也可以在公式中处理 a 这个公式将数值100用&连接空字符串“，强制转为文本 b 这个公式将文本200用*1运算，强制转为数值 要注意的是采用修改格式的方法，仅仅将单元格格式更改还不行： 例如对于包含数值的常规单元格将格式改为文本后，单元格中的值仍是数值形式,需要激活（双击）才会真正转为文本。 单元格很多时，采用逐个激活的方法肯定会累死。可以采用分列或选择性粘贴的方法： 当某列数据需要全部转换时，采用分列是个好办法。它可以将文本转为数值，也可将数值转为文本。 分列位于菜单栏数据分列 由文本转数值也可以 复制一空白单元格，选定需转换的数据区，选择性粘贴加 当确定应该出现#n/a（即查找值不存在）时，如果不要显示错误，可以使用条件格式或直接在公式中处理 表表三三 a100 注意使用条件格式处理后单元格内的值仍是#n/a，其他单元格引用该单元格也会返回#n/a错误。 c200 当c55选b时，显然应该返回错误（数据区没有b）。 处理错误 b#n/a 这个单元格使用条件格式处理，条件格式公式isna(d55)，并设定条件字体颜色与底色相同。 使用isna测试vlookup函数是否返回#n/a，如vlookup函数返回#n/a，则isna函数返回true。再使用if函数判断，即可去除错误。 精确查找时，也可以直接判断查找值是否存在来去除#n/a错误，如此例使用countif测试数据区是否有查找值。 近似匹配查找时出现#n/a错误 近似匹配查找时，除了前述的格式等原因，查找值小于数据区的最小值将返回#n/a。 对于此类错误，只要数据区设计合理就可避免（建立可能的最小值）,当然也可以使用isna去除。 b18第四个字符码 32 #value!和 #ref!是由于col_index_num设置不当造成的，虽然可以使用iserror剔除，但建议不要这么做，因为这类错误需要纠正。 有时col_index_num参数使用column()函数生成，当设置不当造成的错误用iserror剔除时，会与#n/a错误混淆，不利于公式查错。 当确定引用正确、不应出现#n/a（即应该查找到值）时，检查是否存在空格或其它字符、格式是否一致 对于空格或其他不可见字符，可以使用len函数测试文本长度来确定，或使用code测试其ascii码 该错误由于查询字符“张三丰 ”后含空格，可能看不出，但在精确匹配查找时造成不完全匹配 对于lookup_value中的空格，将其中的空格替换掉，或使用trim去除空格后查找 该错误是由于查询表表四中“李四军”后含空格，在精确匹配查找时不完全匹配 这个公式中的table_array已不是直接引用单元格区域，而是引用对$b$87:$c$88运算trim后的内存数组 如果数据区比较大，数组公式会很慢；不如直接使用替换将数据区的空格替换后使用普通公式。 该错误由于查询字符“李四军”后含不可见字符（非空格），使用trim、clean、替换空格都不能去除。 由网页复制、其他程序转出的表格，经常会出现这类字符，code可以测试其并非空格 对于这类不可见字符，可以先复制该字符，然后替换该字符为空白 可以使用鼠标选定几个单元格，看状态栏的合计。有，则为数值，无，则为文本。 这两个错误都是由于格式不一致造成，一个是按数值查文本，一个是按文本查数值 对于此类错误，可以修改查找值、数据区的格式，使之统一；也可以在公式中处理 要注意的是采用修改格式的方法，仅仅将单元格格式更改还不行： 例如对于包含数值的常规单元格将格式改为文本后，单元格中的值仍是数值形式,需要激活（双击）才会真正转为文本。 单元格很多时，采用逐个激活的方法肯定会累死。可以采用分列或选择性粘贴的方法： 当某列数据需要全部转换时，采用分列是个好办法。它可以将文本转为数值，也可将数值转为文本。 复制一空白单元格，选定需转换的数据区，选择性粘贴加 当确定应该出现#n/a（即查找值不存在）时，如果不要显示错误，可以使用条件格式或直接在公式中处理 注意使用条件格式处理后单元格内的值仍是#n/a，其他单元格引用该单元格也会返回#n/a错误。 这个单元格使用条件格式处理，条件格式公式isna(d55)，并设定条件字体颜色与底色相同。 使用isna测试vlookup函数是否返回#n/a，如vlookup函数返回#n/a，则isna函数返回true。再使用if函数判断，即可去除错误。 精确查找时，也可以直接判断查找值是否存在来去除#n/a错误，如此例使用countif测试数据区是否有查找值。 对于此类错误，只要数据区设计合理就可避免（建立可能的最小值）,当然也可以使用isna去除。 本月员工工资表 员工编号员工姓名性别年龄所属部门职位固定工资福利津贴全勤奖金 no.0001胡林男26人力资源部助理￥2,500￥800￥0 no.0002郑择意男39行政部经理￥4,500￥1,200￥200 no.0003熊雨寒女35销售部经理￥4,700￥1,200￥200 no.0004王安娜女28人力资源部专员￥2,500￥800￥0 no.0005范国毅男27技术部主管￥2,500￥1,200￥0 no.0006姜志刚男34技术部经理￥5,100￥1,200￥0 no.0007王肖男30销售部主管￥2,500￥1,200￥200 no.0008邓小欣女31人力资源部主管￥2,500￥1,200￥200 no.0009蹇枫霞女25销售部业务员￥2,000￥800￥0 no.0010李良慧女29销售部主管￥2,500￥1,200￥200 no.0011戴文兰女31行政部助理￥2,500￥800￥200 no.0012王珊女28行政部文员￥1,700￥800￥200 no.0013苏美珍女30技术部主管￥2,500￥1,200￥200 no.0014徐双男29技术部技术员￥3,000￥800￥0 no.0015张坤男28销售部业务员￥2,000￥800￥0 no.0016邓小强男34人力资源部经理￥4,700￥1,200￥200 no.0017郑青松男26技术部技术员￥3,000￥800￥0 no.0018王晓东男29销售部业务员￥2,000￥800￥200 no.0019王强男30销售部业务员￥2,000￥800￥0 no.0020张玲琳女27行政部文员￥1,700￥800￥0 应扣工资应缴社保实发工资 ￥70￥275￥2,955 ￥0￥495￥5,405 ￥0￥517￥5,583 ￥10￥275￥3,015 ￥20￥275￥3,405 ￥102￥561￥5,637 ￥0￥275￥3,625 ￥0￥275￥3,625 ￥80￥220￥2,500 ￥0￥275￥3,625 ￥0￥275￥3,225 ￥0￥187￥2,513 ￥0￥275￥3,625 ￥30￥330￥3,440 ￥10￥220￥2,570 ￥0￥517￥5,583 ￥120￥330￥3,350 ￥0￥220￥2,780 ￥20￥220￥2,560 ￥64￥187￥2,249 本月员工工资表 员工工资查询表 请选择员工姓名:王珊 性别女年龄28 所属部门行政部职位文员 固定工资1700福利津贴800 全勤奖金200应扣工资0 应缴社保187实发工资2513 部部门门金金额额 业务1部76,400 业务1部109,800 业务1部88,400 业务1部137,000 业务1部42,600 业务1部56,200 业业务务1 1部部 汇汇总总510,400 业务2部109,000 业务2部97,200 业务2部111,400 业务2部35,000 业务2部14,400 业务2部4,800 业务2部9,000 业务2部22,600 业业务务2 2部部 汇汇总总403,400 业务3部37,600 业务3部33,200 业务3部37,000 业务3部155,000 业务3部14,200 业务3部64,200 业务3部9,800 业务3部9,600 业务3部114,400 业业务务3 3部部 汇汇总总475,000 总总计计1,388,800 工资发放零钞备用表 员员工工编编号号员员工工姓姓名名 本本月月应应发发工工资资 100100元元张张数数 5050元元张张数数 2020元元张张数数 1010元元张张数数 5 5元元张张数数 2 2元元张张数数 no.0001胡 林2,955.632910010 no.0002郑择意5,405.835400010 no.0003熊雨寒5,583.535511101 no.0004王安娜3,015.633000110 no.0005范国毅3,405.853400010 no.0006姜志刚5,637.835601111 no.0007王 肖3,625.533601010 no.0008邓小欣3,625.523601010 no.0009蹇枫霞2,500.532500000 no.0010李良慧3,625.533601010 no.0011戴文兰3,225.633201010 no.0012王 珊2,513.632500101 no.0013苏美珍3,625.383601010 no.0014徐 双3,440.833402000 no.0015张 坤2,570.632511000 no.0016邓小强5,583.835511101 no.0017郑青松3,350.633310000 no.0018王晓东2,780.832711100 no.0019王 强2,560.832510100 no.0020张玲琳2,249.832202012 本月工资发放所需各面值钞票张数7057147116 1 1元元张张数数 5 5毛毛张张数数 2 2毛毛张张数数 1 1毛毛张张数数 0101 0111返回商的整数部分，该函数可用于舍掉商的小数部分 1100 0101 0111 0111 0100 0100 0100 0100 0101 1101 0011 0111 0101 1111 0101 0111 0111 0111 319915 工资发放零钞备用表 返回商的整数部分，该函数可用于舍掉商的小数部分 起起始始日日终终止止日日特特殊殊间间隔隔具具体体数数值值 2001/1/12003/1/1相差年数2 2001/6/12002/8/15计算实际相差天数440 2001/6/12002/8/15忽略年份计算天数75 2001/6/12002/8/15忽略年份和月份计算天数14 起起始始日日期期计计算算要要求求计计算算结结果果 2006/10/2510个工作前日期2006/10/11 2006/10/2510个工作后日期2006/11/8 起起始始日日期期终终止止日日期期节节假假日日结结果果计计算算 2005/5/12005/5/312005/5/120 2005/5/2 2005/5/3 加加班班记记录录表表 日期姓名所属部门起始时间结束时间累计小时数加班费核准人 1/jun/09刘琴玉生产部7:30:00 pm10:00:00 pm 2:30:00￥ 375.00 刘康玉 3/jun/09黄平峰行政部6:20:00 pm9:45:30 pm 3:25:30￥ 513.75 刘康玉 5/jun/09杨玉楷行政部6:10:12 pm11:10:11 pm 4:59:59￥ 749.96 刘康玉 7/jun/09何醉雨人事部6:21:22 pm7:11:20 pm 0:49:58￥ 124.92 刘康玉 8/jun/09刘琴玉生产部7:15:20 pm11:21:11 pm 4:05:51￥ 614.63 刘康玉 9/jun/09黄平峰行政部7:55:30 pm11:20:22 pm 3:24:52￥ 512.17 刘康玉 10/jun/09杨玟玟研发部7:20:33 pm10:30:25 pm 3:09