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

1、在人事表单中使用函数edateemonthdatedifmonthroundup转换中文大小写金额迅速查找和跨表引用indexmatchindirect工资的管理vlookup批量制作工资条排序邮件合并创建工资查询系统int人事信息到期提醒表序号 员工编号 员工姓名参工时间身份证号出生日期01AY0001胡 军2003年4月15日19年月日02AY0003陈 波2004年4月16日19年月日03AY0002闫晓洪 2005年4月20日19年月日04AY0004敖雪东 2006年3月12日19年月日05AY0005陈小萍 2004年5月18日19年月日06AY0006郑安平 2003年5月10日

2、19年月日07AY0007满 雄2005年4月21日19年月日08AY0008廖 彬2004年7月19日19年月日09AY0009邓 勇2006年10月8日19年月日10AY0010陈 霞2005年3月26日19年月日11AY0011郑青松 2004年5月15日19年月日12AY0012廖挺鸿 2003年4月16日19年月日13AY0013王燕玲 2006年2月17日19年月日14AY0014邓 群2005年6月27日19年月日15AY0015闫 波2003年4月19日19年月日16AY0016王 霞1993年4月15日19年月日17AY0017张伯蓉 1984年3月18日19年月日18AY0

3、018王 艳1997年8月19日19年月日19AY0019张 菁2002年6月29日19年月日20AY0020陈 好2005年6月27日19年月日21AY0021黄大秀 1971年6月26日19年月日22AY0022陈道华 1982年9月12日19年月日23AY0023陈红林 1969年10月9日19年月日24AY0024王志刚 1970年11月8日19年月日25AY0025陈进军 1992年11月9日19年月日26AY0026邓欣雨 1994年5月26日19年月日27AY0027张林强 1980年10月8日19年月日28AY0028郑克强 1980年6月25日19年月日29AY0029张荣恩

4、 2003年6月26日19年月日30AY0030谢雨欣 2002年5月16日19年月日进入公司时间试用期限签订合同日期合同期限性别年龄工龄生日到期#3个月2006年9月30日3年14#3个月2007年8月12日3年13#3个月2008年9月12日3年12#3个月2007年4月26日3年12#2006年2月6日3个月2006年5月6日3年13#3个月2008年4月12日3年14#3个月2008年8月24日3年12#3个月2006年8月23日3年13#2009年2月6日3个月无无11#3个月2008年6月26日3年12#3个月2008年9月30日3年13#3个月2007年8月30日3年14#3个月

5、2009年3月1日3年12#3个月2007年1月8日3年12#3个月2008年9月30日3年14#3个月2004年7月26日无固定24#3个月1996年9月30日无固定33#3个月2006年7月15日无固定20#3个月2006年7月25日3年15#3个月2006年5月26日3年12#3个月1986年7月12日无固定46#3个月2004年9月24日无固定35#3个月2005年5月16日无固定48#3个月2007年7月25日无固定47#3个月2007年6月16日3年25#3个月2006年7月15日无固定23#3个月2006年7月25日3年37#2006年2月6日3个月2006年5月6日3年37#3

6、个月2007年1月8日3年14#3个月2008年9月30日3年15#人事信息到期提醒表试用到期合同到期退休到期退休日期退休日期工龄第几季度签订合同#VALUE!#VALUE!1673#3#3#3#2#2#目标值：5188.1499999999996各部分转换值整数部分角位分位角分位判断条件是否为零是否只有整数部分是否没有整数部分是否只有角是否只有分转换值：=IF(D11,数值为零,IF(D12,D5&整,IF(D13,IF(D14,D6&整,IF(D15,D7,D8),IF(D14,D5&D6&整,IF(D15,D5&零&D7,D5&D8)分三次转换元位角分位=IF(TRUNC(C2,2)=0

7、,0,IF(TRUNC(C2,2)=INT(C2),TEXT(TRUNC(C2),DBNUM2)&元整,IF(TRUNC(C2)=0,TEXT(TRUNC(C2),DBNUM2)&元)=IF(OR(B21=0,TRUNC(C2,2)=INT(C2),0,IF(RIGHT(TEXT(TRUNC(C2,2),0.00),1)=0,TEXT(MID(C2,LEN(TEXT(TRUNC(C2,2),0.00)-1,1),DBNUM2)&角整,IF(MID(C2,LEN(TEXT(TRUNC(C2,2),0.00)-1,1)=0,TEXT(RIGHT(TEXT(TRUNC(C2,2),0.00),1),

8、DBNUM2)&分,TEXT(MID(C2,LEN(TEXT(TRUNC(C2,2),0.00)-1,1),DBNUM2)&角&TEXT(RIGHT(TEXT(TRUNC(C2,2),0.00),1),DBNUM2)&分)=IF(D11,数值为零,IF(D12,D5&整,IF(D13,D5)=IF(D12,IF(D14,D6,IF(D15,IF(D13,D7,零&D7),D8)=TEXT(TRUNC(C2),DBNUM2)&元=TEXT(MID(C2,LEN(TEXT(TRUNC(C2,2),0.00)-1,1),DBNUM2)&角=TEXT(RIGHT(TEXT(TRUNC(C2,2),0.

9、00),1),DBNUM2)&分=D6&D7=IF(TRUNC(C2,2)=0,TRUE,FALSE)=IF(TRUNC(C2,2)=TRUNC(C2),TRUE,FALSE)=IF(TRUNC(C2)=0,TRUE,FALSE)=IF(RIGHT(TEXT(TRUNC(C2,2),0.00),1)=0,TRUE,FALSE)=IF(MID(C2,LEN(TEXT(TRUNC(C2,2),0.00)-1,1)=0,TRUE,FALSE)转换值=IF(AND(OR(B21=0,B21=),C21=),数值为零,IF(C21=0,B21,IF(MID(C2,LEN(TEXT(TRUNC(C2,2)

10、,0.00)-1,1)=0,B21&零&C21,B21&C21)=IF(D11,数值为零,B22&C22)=IF(D11,数值为零,IF(D12,D5&整,IF(D13,IF(D14,D6&整,IF(D15,D7,D8),IF(D14,D5&D6&整,IF(D15,D5&零&D7,D5&D8)目标值：200.11各部分转换值整数部分贰佰元角位壹角分位壹分角分位壹角壹分判断条件是否为零FALSE是否只有整数部分FALSE是否没有整数部分FALSE是否只有角FALSE是否只有分FALSE转换值：贰佰元壹角壹分分三次转换元位角分位转换值贰佰元壹角壹分 贰佰元壹角壹分贰佰元壹角壹分 贰佰元壹角壹分部门

11、生产部费用项目水电费金额1464四月份各部门费用明细表费费用用项项目目办办公公室室财财务务部部信信息息部部销销售售部部生生产产部部人人力力资资源源部部合合计计薪金234324334532576876659854532455318018广告费355512655876524975水电费23554367612414643453387保险费248502224422816159717166681899865通讯费53457632448766455234415919培训费878670760111787910215325杂费1234235465487924342437798费用总额5471869732607

12、2612717711094351991475287姓名照片唐僧猪八戒孙悟空沙和尚姓名照片沙和尚沙和尚广东省浙江省辽宁省四川省河北省湖南省安徽省广州市杭州市沈阳市成都市石家庄市 长沙市合肥市深圳市宁波市大连市自贡市唐山市株洲市芜湖市珠海市温州市鞍山市攀枝花市 秦皇岛市 湘潭市蚌埠市省份汕头市嘉兴市抚顺市泸州市邯郸市衡阳市淮南市广东省韶关市湖州市本溪市德阳市邢台市邵阳市马鞍山市佛山市绍兴市丹东市绵阳市保定市岳阳市淮北市江门市金华市锦州市广元市张家口市 常德市铜陵市湛江市衢州市营口市遂宁市承德市张家界市 安庆市茂名市舟山市阜新市内江市沧州市益阳市黄山市肇庆市台州市辽阳市乐山市廊坊市郴州市滁州市惠州市

13、丽水市盘锦市南充市衡水市永州市阜阳市梅州市铁岭市宜宾市怀化市宿州市汕尾市朝阳市广安市娄底市巢湖市河源市葫芦岛市 达州市湘西州六安市阳江市眉山市亳州市清远市雅安市池州市东莞市巴中市宣城市中山市资阳市潮州市阿坝州揭阳市甘孜州云浮市凉山州城市广州市Quarter1Office63Windows66Xbox70Quarter2Office93Windows90Xbox99Quarter3Office77Windows58Xbox60OfficeWindows XboxQuarter1636670Quarter4Quarter2939099Office97quarter3775860Windows56q

14、uarter4975695Xbox95ABCDEFGHIJ123456789101112131415161718192021员工销售奖金的计算就可以查找出来税率，税款表2查找业务员不同商场的销售额表六批量制作工资条名称为工资表工资查询对月度工资按部门汇总工资发放零钞备用表加班记录表-如何计算上班小时数今天前后星期几员工年假表员工销售产品工作业绩冰箱洗衣机空调 彩电李辰1210520￥117,045.40刘欣259315￥135,566.11杨建国6211019￥136,243.79王心语2116931￥195,134.44田宏涛15141225￥174,465.16张小丽138616￥112,

15、987.82刘保国30201410￥206,783.40单价￥3,050.50￥1,560.99￥4,489.90￥2,119.00总库存量200100100150现库存量7824114奖金基本工资总工资#￥600.00￥12,305.00公公式式#￥600.00￥14,157.004#￥600.00￥14,225.0077#￥600.00￥20,114.003.142#￥600.00￥18,047.00-3.2#￥600.00￥11,899.0031500#￥600.00￥21,279.00说说明明（结结果果）将 3.2 向上舍入，小数位为 0(4)将 76.9 向上舍入，小数位为 0(77

16、)将 3.14159 向上舍入，保留三位小数(3.142)将-3.14159 向上舍入，保留一位小数(-3.2)-3.1 不同于round将 31415.92654向上舍入到小数点左侧两位(31500)VLOOKUPVLOOKUP(lookup_valuelookup_value,table_arraytable_array,col_index_numcol_index_num,range_lookup)Lookup_valueLookup_value 为需要在Table_array第一列中查找的数值。可以为数值、引用或文本字符串。需要注意的是类型必须与table_array第一列的类型一致。

17、查找文本时，文本不区分大小写；可以使用通配符“*”、“?”。Table_arrayTable_array 为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用、常数数组、计算后的内存数组。对区域引用时，可以引用整列,excel会自动判断使用区域。该参数的第一列必须包含查找的内容，其它列包含需返回的内容；返回内容的列序号由下个参数指定。Col_index_numCol_index_num 为table_array中待返回的匹配值的列序号。如为1时，返回table_array第一列中的数值；为2，返回table_array第二列中的数值，以此类推。如果col_index_num小于1，函数

18、 VLOOKUP 返回错误值值#VALUE!；如果col_index_num大于table_array的列数，函数 VLOOKUP 返回错误值#REF!。Range_lookup 为一逻辑值，指明函数VLOOKUP返回时是精确匹配还是近似匹配。如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；近似匹配查询一般用于数值的查询，table_arraytable_array的的第第一一列列必必须须按按升升序序排排列列；否则不能返回正确的结果。如果range_value为FALSE（或0），函数VLOOKUP将返回精确匹配值。此时，t

19、able_array不必进行排序。如果找不到，则返回错误值#N/A；可isna检测错误后使用if判断去除错误信息。处理查找错误例1精确匹配查询之基本运用例8在同一单元格按不同字段查询例2数值近似匹配查询之基本运用例9返回多个符合条件的查询结果例3文本近似匹配查询之基本运用例10有两个并列条件（不同字段）的查询例4区别查询结果的空白与0值例11使用数组常量的查询例5依次序返回同一查询结果的多列内容例12从右向左查询例6返回同一查询结果的多列（不按原次序）的内容例7按部分内容模糊查询以上举例是vlookup的基本运用，再结合其它函数，可以发挥其更多作用。可以为数值、引用或文本字符串。需要注意的是类

20、型必须与table_array第一列的类型一致。查找文本时，文本不区分大小写；可以使用通配符“*”、“?”。可以使用对区域或区域名称的引用、常数数组、计算后的内存数组。该参数的第一列必须包含查找的内容，其它列包含需返回的内容；返回内容的列序号由下个参数指定。如为1时，返回table_array第一列中的数值；为2，返回table_array第二列中的数值，以此类推。如果col_index_num小于1，函数 VLOOKUP 返回错误值值#VALUE!；如果col_index_num大于table_array的列数，函数 VLOOKUP 返回错误值#REF!。如果为TRUE或省略，则返回近似匹配

21、值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；近似匹配查询一般用于数值的查询，table_arraytable_array的的第第一一列列必必须须按按升升序序排排列列；否则不能返回正确的结果。如果range_value为FALSE（或0），函数VLOOKUP将返回精确匹配值。此时，table_array不必进行排序。如果找不到，则返回错误值#N/A；可isna检测错误后使用if判断去除错误信息。在同一单元格按不同字段查询返回多个符合条件的查询结果有两个并列条件（不同字段）的查询vlookupvlookup应应用用实实例例返返回回精精确确匹匹配配查查询询之之基

22、基本本运运用用精确查找是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转

23、换），便于复制。查找的姓名必须位于$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中查找出生年月，但此时返回的是

24、时间序列值（即格式没有带过来）1970年8月需要重新设定单元格格式1970年8月或在公式中使用text设定格式数数值值近近似似匹匹配配查查询询之之基基本本运运用用近似匹配查找通常情况下用于累进数值的查找。此时第四个参数省略，或为true，或为非0数值。（EXCEL中0等同FALSE，非零值均视为TRUE）这是一张个所税税率表，对于不确定的收入，可以利用近似匹配查找税率及速扣数表表二二级级数数 应应税税所所得得超超过过 且且不不超超过过税税率率(%)速速算算扣扣除除数数 150050250020001025320005000151254500020000203755200004000025137

25、56400006000030337576000080000356375880000100000401037591000004515375应应税税所所得得税税率率速速算算扣扣除除数数 税税款款5360203756975360在表中未列出，因此EXCEL查找小于5360的最大值即5000，并返回对应的税率等。注意公式中第四个参数省略，或为true，或为非0数值。并且查找的第一列必须升序排列，否则不会返回期望的结果（需要注意的是此时不一定返回错误）。试着输入5000，你会发现税率为20%，应是15%（由条件指定），虽然对最后计算结果没有影响。以下公式可以解决这个问题：应应税税所所得得税税率率速速算算

26、扣扣除除数数 税税款款5000151256251520文文本本近近似似匹匹配配查查询询之之基基本本运运用用表表三三对于文本，一般不使用近似匹配查找（非模糊查找）。吖A并非文本不能用于近似查找，主要是没有太多用途。除了下面这个例子：八B嚓C这个例子利用文本近似匹配查找汉字的第一个拼音字母咑D由于EXCEL中汉字字符是按拼音排序的，因此汉字字符比较时的大小与拼音同序（一般情况下）鵽E左边的表是根据汉字拼音首字母排列，并取各字母的临界点汉字字符（该字母的“最小汉字”）发F猤G汉汉字字字字符符拼拼音音首首字字母母铪H家J夻J咔K通过将“家”与表中字符比较后，找到比“家”小的最大值“夻”，返回J垃L这个

27、例子一般用于姓名的缩写，而上面的公式只找第一个字符，因此需要修改嘸M旀N汉汉字字字字符符拼拼音音首首字字母母噢O普利卡PLK妑P七Q这个公式取得前三个汉字字符的拼音字母。使用MID依次取出字符后由Vlookup查询到字母，最后“&”连接文本囕R仨S使用该例关键是【表三】的建立；同理，如果要查找汉字的完整拼音，可以建立一个由各种拼音组成的类似列表。他T但这个方法并不一直正确，由于多音字及文字大小排序与其拼音不完全一致的原因，有时返回的字母可能不是你要的屲W夕X丫Y帀Z区区别别查查询询结结果果的的空空白白与与0 0值值表表四四姓姓名名内内容容张三丰0vlookup对于查找到的0值会返回0；张三丰0

28、李四光0对于空白单元格（非空字符串）也会返回0；李四光王麻子对空字符串仍返回空字符串。王麻子赵六儿赵六儿张三丰0如要对0值和空白单元格区别，可以直接判断是否，或使用len函数测试返回值的长度；李四光如不需要0值，可直接判断0，或在【工具】【选项】中将【零值】去掉。依依次次序序返返回回同同一一查查询询结结果果的的多多列列内内容容按姓名在【表一】中查询全部内容姓姓名名工工号号性性别别籍籍贯贯出出生生年年月月李四光KT002女天津1980年9月王麻子KT003男河北1975年3月当查询某个结果的多列内容，并要依次取出，可使用column()函数作为index_num参数column()函数返回公式所

29、在列的列号，当公式复制时，会随单元格的移动相应改变lookup_value参数使用混合引用，如$B96。即对列标绝对引用（列标前加$）、行号相对引用(行号前没有$)；复制时引用单元格会改变。上面公式只要输入C96中的公式，然后向右、向下复制就可以。返返回回同同一一查查询询结结果果的的多多列列（不不按按原原次次序序）的的内内容容按姓名及不同项目（不依次序）在【表一】中查询姓姓名名性性别别籍籍贯贯出出生生年年月月工工号号先由match函数确定项目内容在B8:F8区域的位置，将返回值作为vlookup的第三个参数。赵六儿女河南#KT004注意公式中Match函数的第一个参数使用行号绝对引用、列标相对

30、引用；王麻子男河北1975年3月KT003按按部部分分内内容容模模糊糊查查询询按姓在【表一】中查询姓姓姓姓名名工工号号性性别别籍籍贯贯出出生生年年月月王王麻子KT003男河北1975年3月vlookup支持通配符【*】和【?】，因此当查找内容不全时，可以使用通配符；如例公式中使用【&】将查找文本与通配符连接后查找，注意通配符连接位置注意查找中，有多个符合条件的结果时，只会返回第一个在在同同一一单单元元格格按按不不同同字字段段查查询询在同一单元格按姓名或工号在【表一】中查询姓姓名名或或工工号号性性别别籍籍贯贯出出生生年年月月KT002女天津1980年9月 使用countif判断，如果在姓名区中有

31、C131单元格的内容，按姓名查找；否则按工号查找李四光女天津1980年9月 或者使用isna判断，如果按姓名查找出错，就按工号查找；王麻子男河北1975年3月 或者使用一个vlookup，第二、三个参数由判断结果决定（查找范围、列号）。返返回回多多个个符符合合条条件件的的查查询询结结果果表表五五查找的结果不止一个，并要全部返回姓姓名名工工号号vlookup对于包含多个结果的查找只会返回第一个结果（数据区由上向下第一个）1张三丰KT001但有时需要返回所有的结果。如例，需要按姓名查找同名的各个工号1李四光KT002这种情况一般使用数组公式解决。但数据量大时，使用数组公式会由于计算量大，速度会很慢

32、1李四光KT003vlookup没有直接的解决方法，需要添加辅助列（注意辅助列需添加在左侧，因为要根据辅助列的内容查找）2张三丰KT0043张三丰KT005表五左侧白色区域为辅助列，B141公式为=($C$148=C141)+B140；等同=IF($C$148=C141,1,0)+B140姓姓名名工工号号该公式结果为：如姓名列内容与查找姓名相同，则按顺序显示1、2、3，从而将相同姓名区分张三丰KT001然后，在查询中查找数值1、2、3即可查找到对应查找内容的多个结果(注意查找区域由辅助列开始）KT004为便于复制，vlookup第一个参数数值1、2、3使用函数row()返回的行号产生KT005

33、当然，辅助列中的公式可以改为其它，只要达到区别相同内容的目的有有两两个个并并列列条条件件（不不同同字字段段）的的查查询询表表六六：查找某业务员对某客户的销售额业业务务员员客客户户销销售售额额对于此类根据两个或多个并列条件查找某个结果的问题，使用vlookup也需要添加辅助列张三丰商场A张三丰商场A100B158公式=C158&D158，即将被查找的内容合并作为一列，并以此作为查找列。张三丰超市B张三丰超市B200李四光店铺C李四光店铺C300有些数据需要在两个数据间插入一特殊符号(查询数据中没有的任意符号)以示区别李四光超市B李四光超市B400例如两个条件分别是：张三丰店铺C张三丰店铺C500

34、李四光商场A李四光商场A600此时就需要用其他符号将其区别：业业务务员员客客户户销销售售额额李四光超市B400使用【&】将要查询的两个条件合并后作为查询值，在区域$B$158:$E$163中查询第4列内容使使用用数数组组常常量量的的查查询询一般，使用vlookup前需要有一个对应的表格供查找，即作为vlookup的Table_array参数的表格区域；但有时这个表格可能会显得累赘；如果数据不是太多而且比较固定，可以考虑使用数组常量代替单元格区域的方法；所谓数组常量就是直接输入的一组数组，以“”开始结束、同一行的数值用逗号“，”分隔、同一列的数值用分号“；”分隔如10,20,30，是一行三列（横

35、向）、包含三个元素的一维数组常量10;20;30;40，是一列四行（纵向）、包含四个元素的一维数组常量10,20,30;40,50,60，是两行三列、包含六个元素的二维数组常量；二维数组常量需要行列等长如果你觉得输数组常量麻烦（要区分【,】、【;】，文本要加【】），可以先将数据输入单元格区域，然后在其它单元格输入【=】，用鼠标选定该区域后按F9，显示的就是数组常量，然后复制到公式中其实将以前使用的vlookup公式中第三个参数（区域不要太大）选定按F9，再回车，可以看到公式正常运作；此时已使用了数组常量以下举例中的公式并不是数组公式，输入时不用Ctrl+Shift+Enter一齐按搬一个由ch

36、enjun版主提供的计算个人所得税的例子：应应税税所所得得税税率率速速算算扣扣除除数数 税税款款650020375925再搬一个前面的例子汉汉字字字字符符拼拼音音首首字字母母海H是否被公式中的数组常量吓一跳？不是一个个输的，按F9得到的；汉汉字字字字符符拼拼音音首首字字母母螺丝扣LQK对于此类固定的数组常量可以将其定义一个名称，公式就简洁多了，还不占用单元格公式中的【拼音】就是一个定义的名称，可以自【插入】【名称】【定义】中查看从从右右向向左左查查询询我们知道，vlookup查找的列必须位于查找区域的最左列，有时会需要按右面的查找左边的值以【表一】为例，要按【姓名】查询【工号】很容易；但反过来

37、按【工号】查找【姓名】直接使用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

38、是一行二列（横向）常量数组，后面的两个区域是多行一列（纵向），即两个数组的方向不同，这样才会生成两列多行数组张三丰李四光王麻子赵六儿KT001KT002KT003KT004三个结果是不一样的精确查找是vlookup最基本也是最常用的功能，对于数据量大的查找，其速度比菜单中的查找还快。设置vlookup第四个参数为false或0，即为精确查找。根据姓名在$B$9:$F$12中查找籍贯，$B$9:$F$12区域最好使用绝对引用（可在公式中选定区域按F4转换），便于复制。查找的姓名必须位于$B$6:$F$9的第一列；籍贯在$B$6:$F$9位于第四列，因此第三个参数为4；精确查找，第四个参数FALS

39、E【表一】是定义的单元格区域$B$9:$F$12的名称，在名称框（编辑栏左边）可以选定；也可在插入名称定义中查看修改注意范围已改为$C$8:$E$12，确保工号在第一列；由于范围的改变，籍贯位于该范围的第3列，因此第三个参数为3根据工号在$C$8:$F$12中查找出生年月，但此时返回的是时间序列值（即格式没有带过来）近似匹配查找通常情况下用于累进数值的查找。此时第四个参数省略，或为true，或为非0数值。（EXCEL中0等同FALSE，非零值均视为TRUE）并且查找的第一列必须升序排列，否则不会返回期望的结果（需要注意的是此时不一定返回错误）。试着输入5000，你会发现税率为20%，应是15%

40、（由条件指定），虽然对最后计算结果没有影响。以下公式可以解决这个问题：对于文本，一般不使用近似匹配查找（非模糊查找）。并非文本不能用于近似查找，主要是没有太多用途。除了下面这个例子：这个例子利用文本近似匹配查找汉字的第一个拼音字母由于EXCEL中汉字字符是按拼音排序的，因此汉字字符比较时的大小与拼音同序（一般情况下）左边的表是根据汉字拼音首字母排列，并取各字母的临界点汉字字符（该字母的“最小汉字”）通过将“家”与表中字符比较后，找到比“家”小的最大值“夻”，返回J这个例子一般用于姓名的缩写，而上面的公式只找第一个字符，因此需要修改这个公式取得前三个汉字字符的拼音字母。使用MID依次取出字符后由

41、Vlookup查询到字母，最后“&”连接文本使用该例关键是【表三】的建立；同理，如果要查找汉字的完整拼音，可以建立一个由各种拼音组成的类似列表。但这个方法并不一直正确，由于多音字及文字大小排序与其拼音不完全一致的原因，有时返回的字母可能不是你要的vlookup对于查找到的0值会返回0；对于空白单元格（非空字符串）也会返回0；对空字符串仍返回空字符串。如要对0值和空白单元格区别，可以直接判断是否，或使用len函数测试返回值的长度；如不需要0值，可直接判断0，或在【工具】【选项】中将【零值】去掉。当查询某个结果的多列内容，并要依次取出，可使用column()函数作为index_num参数colum

42、n()函数返回公式所在列的列号，当公式复制时，会随单元格的移动相应改变lookup_value参数使用混合引用，如$B96。即对列标绝对引用（列标前加$）、行号相对引用(行号前没有$)；复制时引用单元格会改变。先由match函数确定项目内容在B8:F8区域的位置，将返回值作为vlookup的第三个参数。注意公式中Match函数的第一个参数使用行号绝对引用、列标相对引用；Vlookup函数的第一个参数使用行号相对引用、列标绝对引用；中国人民直接合并后相同中国人民中国人民中国人民vlookup支持通配符【*】和【?】，因此当查找内容不全时，可以使用通配符；如例使用countif判断，如果在姓名区中

43、有C131单元格的内容，按姓名查找；否则按工号查找或者使用isna判断，如果按姓名查找出错，就按工号查找；或者使用一个vlookup，第二、三个参数由判断结果决定（查找范围、列号）。vlookup对于包含多个结果的查找只会返回第一个结果（数据区由上向下第一个）但有时需要返回所有的结果。如例，需要按姓名查找同名的各个工号这种情况一般使用数组公式解决。但数据量大时，使用数组公式会由于计算量大，速度会很慢vlookup没有直接的解决方法，需要添加辅助列（注意辅助列需添加在左侧，因为要根据辅助列的内容查找）表五左侧白色区域为辅助列，B141公式为=($C$148=C141)+B140；等同=IF($C

44、$148=C141,1,0)+B140该公式结果为：如姓名列内容与查找姓名相同，则按顺序显示1、2、3，从而将相同姓名区分然后，在查询中查找数值1、2、3即可查找到对应查找内容的多个结果(注意查找区域由辅助列开始）为便于复制，vlookup第一个参数数值1、2、3使用函数row()返回的行号产生当然，辅助列中的公式可以改为其它，只要达到区别相同内容的目的对于此类根据两个或多个并列条件查找某个结果的问题，使用vlookup也需要添加辅助列B158公式=C158&D158，即将被查找的内容合并作为一列，并以此作为查找列。有些数据需要在两个数据间插入一特殊符号(查询数据中没有的任意符号)以示区别例如

45、两个条件分别是：此时就需要用其他符号将其区别：使用【&】将要查询的两个条件合并后作为查询值，在区域$B$158:$E$163中查询第4列内容一般，使用vlookup前需要有一个对应的表格供查找，即作为vlookup的Table_array参数的表格区域；但有时这个表格可能会显得累赘；如果数据不是太多而且比较固定，可以考虑使用数组常量代替单元格区域的方法；所谓数组常量就是直接输入的一组数组，以“”开始结束、同一行的数值用逗号“，”分隔、同一列的数值用分号“；”分隔如10,20,30，是一行三列（横向）、包含三个元素的一维数组常量10;20;30;40，是一列四行（纵向）、包含四个元素的一维数组常

46、量10,20,30;40,50,60，是两行三列、包含六个元素的二维数组常量；二维数组常量需要行列等长可以先将数据输入单元格区域，然后在其它单元格输入【=】，用鼠标选定该区域后按F9，显示的就是数组常量，然后复制到公式中其实将以前使用的vlookup公式中第三个参数（区域不要太大）选定按F9，再回车，可以看到公式正常运作；此时已使用了数组常量是否被公式中的数组常量吓一跳？不是一个个输的，按F9得到的；对于此类固定的数组常量可以将其定义一个名称，公式就简洁多了，还不占用单元格公式中的【拼音】就是一个定义的名称，可以自【插入】【名称】【定义】中查看我们知道，vlookup查找的列必须位于查找区域的

47、最左列，有时会需要按右面的查找左边的值以【表一】为例，要按【姓名】查询【工号】很容易；但反过来按【工号】查找【姓名】直接使用vlookup就不行了此时可以使用的公式很多，如lookup、index+match、offset+match、indirect+match等但由chenjun版主提供的使用vlookup的解法我觉得很有参考价值，介绍这个用法的目的主要是学会将两个一维数组合并为一个二维数组这个公式先由【IF(1,0,$C$9:$C$12,$B$9:$B$12)】计算出内存数组：KT001,张三丰;KT002,李四光;KT003,王麻子;KT004,赵六儿可以看到，在该内存数组中，【工号】

48、位于【姓名】的左侧，Vlookup在该内存数组中查找注意1,0是一行二列（横向）常量数组，后面的两个区域是多行一列（纵向），即两个数组的方向不同，这样才会生成两列多行数组处处理理查查找找错错误误返返回回vlookup使用中一般会产生以下错误：#VALUE!和#REF!是由于col_index_num设置不当造成的，虽然可以使用ISERROR剔除，但建议不要这么做，因为这类错误需要纠正。有时col_index_num参数使用column()函数生成，当设置不当造成的错误用ISERROR剔除时，会与#N/A错误混淆，不利于公式查错。#N/A是常见的错误信息，需要针对不同情况处理：精确匹配查找时出现

49、，是由于未找到完全匹配的值。近似匹配查找时出现，是由于查找值小于数据区的最小值。如果vlookup的参数产生错误，vlooukp也返回相同错误，那不在本讨论范围内。精确匹配查找时出现#N/A错误首先检查引用范围是否正确，查询值是否在数据区的第一列。当确定引用正确、不应出现#N/A（即应该查找到值）时，检查是否存在空格或其它字符、格式是否一致表表一一张三丰100对于空格或其他不可见字符，可以使用len函数测试文本长度来确定，或使用code测试其ASCII码李四军 200B18单元格长度4数据含空格等张三丰#N/A该错误由于查询字符“张三丰”后含空格，可能看不出，但在精确匹配查找时造成不完全匹配1

50、00对于lookup_value中的空格，将其中的空格替换掉，或使用TRIM去除空格后查找李四军#N/A该错误是由于查询表表四中“李四军”后含空格，在精确匹配查找时不完全匹配200对于Table_array中的空格，也可以使用trim。这个公式中的Table_array已不是直接引用单元格区域，而是引用对$B$87:$C$88运算trim后的内存数组因此，这是一个数组公式，需要按Ctrl+Shift+Enter输入如果数据区比较大，数组公式会很慢；不如直接使用替换将数据区的空格替换后使用普通公式。李四军#N/A该错误由于查询字符“李四军”后含不可见字符（非空格），使用trim、clean、替换

51、空格都不能去除。由网页复制、其他程序转出的表格，经常会出现这类字符，CODE可以测试其并非空格对于这类不可见字符，可以先复制该字符，然后替换该字符为空白表表二二100A查找内容格式不符，也会造成N/A错误200B可以使用鼠标选定几个单元格，看状态栏的合计。有，则为数值，无，则为文本。数据格式不一致100#N/A这两个错误都是由于格式不一致造成，一个是按数值查文本，一个是按文本查数值200#N/A对于此类错误，可以修改查找值、数据区的格式，使之统一；也可以在公式中处理A这个公式将数值100用&连接空字符串，强制转为文本B这个公式将文本200用*1运算，强制转为数值要注意的是采用修改格式的方法，仅

52、仅将单元格格式更改还不行：例如对于包含数值的常规单元格将格式改为文本后，单元格中的值仍是数值形式,需要激活（双击）才会真正转为文本。单元格很多时，采用逐个激活的方法肯定会累死。可以采用分列或选择性粘贴的方法：当某列数据需要全部转换时，采用分列是个好办法。它可以将文本转为数值，也可将数值转为文本。分列位于菜单栏数据分列由文本转数值也可以复制一空白单元格，选定需转换的数据区，选择性粘贴加当确定应该出现#N/A（即查找值不存在）时，如果不要显示错误，可以使用条件格式或直接在公式中处理表表三三A100注意使用条件格式处理后单元格内的值仍是#N/A，其他单元格引用该单元格也会返回#N/A错误。C200当

53、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。对于此类错误，只要数据区设计合理就可避免（建立可能的最小值）,当然也可以使用IS

54、NA去除。处理错误B18第四个字符码32#VALUE!和#REF!是由于col_index_num设置不当造成的，虽然可以使用ISERROR剔除，但建议不要这么做，因为这类错误需要纠正。有时col_index_num参数使用column()函数生成，当设置不当造成的错误用ISERROR剔除时，会与#N/A错误混淆，不利于公式查错。当确定引用正确、不应出现#N/A（即应该查找到值）时，检查是否存在空格或其它字符、格式是否一致对于空格或其他不可见字符，可以使用len函数测试文本长度来确定，或使用code测试其ASCII码该错误由于查询字符“张三丰”后含空格，可能看不出，但在精确匹配查找时造成不完全

55、匹配对于lookup_value中的空格，将其中的空格替换掉，或使用TRIM去除空格后查找该错误是由于查询表表四中“李四军”后含空格，在精确匹配查找时不完全匹配这个公式中的Table_array已不是直接引用单元格区域，而是引用对$B$87:$C$88运算trim后的内存数组因此，这是一个数组公式，需要按Ctrl+Shift+Enter输入如果数据区比较大，数组公式会很慢；不如直接使用替换将数据区的空格替换后使用普通公式。该错误由于查询字符“李四军”后含不可见字符（非空格），使用trim、clean、替换空格都不能去除。由网页复制、其他程序转出的表格，经常会出现这类字符，CODE可以测试其并非

56、空格对于这类不可见字符，可以先复制该字符，然后替换该字符为空白可以使用鼠标选定几个单元格，看状态栏的合计。有，则为数值，无，则为文本。这两个错误都是由于格式不一致造成，一个是按数值查文本，一个是按文本查数值对于此类错误，可以修改查找值、数据区的格式，使之统一；也可以在公式中处理要注意的是采用修改格式的方法，仅仅将单元格格式更改还不行：例如对于包含数值的常规单元格将格式改为文本后，单元格中的值仍是数值形式,需要激活（双击）才会真正转为文本。单元格很多时，采用逐个激活的方法肯定会累死。可以采用分列或选择性粘贴的方法：当某列数据需要全部转换时，采用分列是个好办法。它可以将文本转为数值，也可将数值转为

57、文本。复制一空白单元格，选定需转换的数据区，选择性粘贴加当确定应该出现#N/A（即查找值不存在）时，如果不要显示错误，可以使用条件格式或直接在公式中处理注意使用条件格式处理后单元格内的值仍是#N/A，其他单元格引用该单元格也会返回#N/A错误。这个单元格使用条件格式处理，条件格式公式isna(D55)，并设定条件字体颜色与底色相同。使用ISNA测试vlookup函数是否返回#N/A，如vlookup函数返回#N/A，则ISNA函数返回TRUE。再使用IF函数判断，即可去除错误。精确查找时，也可以直接判断查找值是否存在来去除#N/A错误，如此例使用COUNTIF测试数据区是否有查找值。近似匹配查

58、找时，除了前述的格式等原因，查找值小于数据区的最小值将返回#N/A。对于此类错误，只要数据区设计合理就可避免（建立可能的最小值）,当然也可以使用ISNA去除。本月员工工资表员工编号 员工姓名 性别 年龄所属部门职位固定工资 福利津贴 全勤奖金NO.0001胡林男26人力资源部助理￥2,500￥800￥0NO.0002郑择意男39行政部经理￥4,500￥1,200￥200NO.0003熊雨寒女35销售部经理￥4,700￥1,200￥200NO.0004王安娜女28人力资源部专员￥2,500￥800￥0NO.0005范国毅男27技术部主管￥2,500￥1,200￥0NO.0006姜志刚男34技术部

59、经理￥5,100￥1,200￥0NO.0007王肖男30销售部主管￥2,500￥1,200￥200NO.0008邓小欣女31人力资源部主管￥2,500￥1,200￥200NO.0009蹇枫霞女25销售部业务员￥2,000￥800￥0NO.0010李良慧女29销售部主管￥2,500￥1,200￥200NO.0011戴文兰女31行政部助理￥2,500￥800￥200NO.0012王珊女28行政部文员￥1,700￥800￥200NO.0013苏美珍女30技术部主管￥2,500￥1,200￥200NO.0014徐双男29技术部技术员￥3,000￥800￥0NO.0015张坤男28销售部业务员￥2,00

60、0￥800￥0NO.0016邓小强男34人力资源部经理￥4,700￥1,200￥200NO.0017郑青松男26技术部技术员￥3,000￥800￥0NO.0018王晓东男29销售部业务员￥2,000￥800￥200NO.0019王强男30销售部业务员￥2,000￥800￥0NO.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￥27

61、5￥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,40

62、0业务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工资发放零钞备用表员工编号 员工姓名本月应发工资100元张数50元张数20元张数10元张数5元张数NO.0001胡 林2,955.63291001N

63、O.0002郑择意5,405.83540001NO.0003熊雨寒5,583.53551110NO.0004王安娜3,015.63300011NO.0005范国毅3,405.85340001NO.0006姜志刚5,637.83560111NO.0007王 肖3,625.53360101NO.0008邓小欣3,625.52360101NO.0009蹇枫霞2,500.53250000NO.0010李良慧3,625.53360101NO.0011戴文兰3,225.63320101NO.0012王 珊2,513.63250010NO.0013苏美珍3,625.38360101NO.0014徐 双3,4

64、40.83340200NO.0015张 坤2,570.63251100NO.0016邓小强5,583.83551110NO.0017郑青松3,350.63331000NO.0018王晓东2,780.83271110NO.0019王 强2,560.83251010NO.0020张玲琳2,249.83220201本月工资发放所需各面值钞票张数7057147112元张数1元张数5毛张数2毛张数1毛张数0010100111返回商的整数部分，该函数可用于舍掉商的小数部分11100001010011110111001000010000100001000010111101000110011100101111

65、11001010011100111201116319915返回商的整数部分，该函数可用于舍掉商的小数部分起始日终止日特殊间隔具体数值2001/1/12003/1/1相差年数22001/6/12002/8/15计算实际相差天数4402001/6/12002/8/15忽略年份计算天数752001/6/12002/8/15忽略年份和月份计算天数14起起始始日日期期计计算算要要求求计计算算结结果果2006/10/2510个工作前日期2006/10/112006/10/2510个工作后日期2006/11/8起起始始日日期期终终止止日日期期节节假假日日结结果果计计算算2005/5/12005/5/3120

66、05/5/1202005/5/22005/5/3加加班班记记录录表表日期姓名所属部门 起始时间结束时间累计小时数加班费1/Jun/09刘琴玉生产部7:30:00 PM10:00:00 PM 2:30:00￥375.003/Jun/09黄平峰行政部6:20:00 PM9:45:30 PM 3:25:30￥513.755/Jun/09杨玉楷行政部6:10:12 PM11:10:11 PM 4:59:59￥749.967/Jun/09何醉雨人事部6:21:22 PM7:11:20 PM 0:49:58￥124.928/Jun/09刘琴玉生产部7:15:20 PM11:21:11 PM 4:05:51

67、￥614.639/Jun/09黄平峰行政部7:55:30 PM11:20:22 PM 3:24:52￥512.1710/Jun/09杨玟玟研发部7:20:33 PM10:30:25 PM 3:09:52￥474.6712/Jun/09何醉雨人事部6:23:20 PM11:25:20 PM 5:02:00￥755.0013/Jun/09罗峰研发部6:20:30 PM10:24:25 PM 4:03:55￥609.7913/Jun/09何溴颜生产部7:30:20 PM12:20:00 AM 4:49:40￥724.1713/Jun/09陈哲宇行政部7:55:00 PM10:44:22 PM 2:4

68、9:22￥423.4218/Jun/09何醉雨人事部6:22:23 PM7:20:25 PM 0:58:02￥145.0818/Jun/09罗平平研发部6:26:25 PM11:21:00 PM 4:54:35￥736.4619/Jun/09谢玉漺研发部7:44:20 PM11:22:11 PM 3:37:51￥544.6320/Jun/09罗峰研发部8:30:22 PM10:24:00 PM 1:53:38￥284.0820/Jun/09刘琴玉生产部7:20:11 PM11:20:22 PM 4:00:11￥600.4621/Jun/09杨珏人事部8:10:10 PM10:30:00 PM

69、2:19:50￥349.5823/Jun/09邓容光生产部7:20:20 PM11:20:00 PM 3:59:40￥599.1723/Jun/09刘淸湨行政部8:25:20 PM10:20:20 PM 1:55:00￥287.5024/Jun/09黄平峰行政部7:22:23 PM11:10:10 PM 3:47:47￥569.4625/Jun/09何峰生产部7:22:22 PM11:21:22 PM 3:59:00￥597.5028/Jun/09何勇生产部8:10:11 PM11:10:14 PM 3:00:03￥450.1328/Jun/09陈哲宇行政部8:21:00 PM10:20:10

70、 PM 1:59:10￥297.9229/Jun/09陈好人事部8:21:23 PM10:10:20 PM 1:48:57￥272.3729/Jun/09刘碟研发部8:41:10 PM10:23:20 PM 1:42:10￥255.42核准人事由刘康玉新项目2:30:00 AM 2:30刘康玉新项目3:25:30 AM 3:25刘康玉新项目4:59:59 AM 4:59刘康玉新项目12:49:58 AM 0:49刘康玉新项目4:05:51 AM 4:05刘康玉新项目3:24:52 AM 3:24刘康玉新项目3:09:52 AM 3:09刘康玉返回项目5:02:00 AM 5:02何玉嵧返回项目

71、4:03:55 AM 4:03何玉嵧返回项目4:49:40 AM 4:49何玉嵧返回项目2:49:22 AM 2:49何玉嵧新项目何玉嵧新项目刘康玉新项目比较2个的区别刘康玉新项目刘康玉新项目刘康玉新项目刘康玉新项目何玉嵧返回项目何玉嵧返回项目何玉嵧返回项目何玉嵧返回项目何玉嵧新项目刘康玉新项目刘康玉新项目4#2017/3/12#2017/3/19weekday返回某日期为星期几。默认情况下，其值为 1（星期天）到 7（星期六）之间的整数。今天星期几今天减掉几天，即为前一个周日员员 工工 年年 假假 表表日日期期2017/3/162017/3/16 13:4113:41编编号号姓姓名名性性别别

72、部部门门入入司司时时间间工工龄龄年年假假天天数数0001张烨男市场部2002/7/114230002吴畏男行政部2002/7/1514230003李佳女办公室2004/3/113220004王凯男策划部2001/6/1015240005刘恺男市场部2000/2/2517260006蔡卓女办公室2004/3/113220007曾蒙男市场部2000/5/1016250008郑文女服务部2003/6/913220009黄采男行政部1999/8/2317260010王柳女人资部2002/7/1214230011章冬男人资部 2003/10/1513220012孙蕊女服务部2003/4/213220013柳风男市场部2001/6/315240014李东男行政部2003/5/11322