Excel在财务中的应用案例
小写 大写第一种方法¥213.56 (人民币)213元 5角 6分¥213.50 (人民币)213元 5角 0分¥213.00 (人民币)213元 0角 0分¥213.06 (人民币)213元 0角 6分-¥216.56 金额为负无效¥0.50 (人民币)0元 5角 0分¥0.56 (人民币)0元 5角 6分¥0.00 (人民币)0元 0角 0分(人民币)0元 0角 0分¥1,234,567.89可以先将数据输入单元格区域,然后在其它单元格输入【=】,用鼠标选定该区域后按F9,显示的就是数组常量,然后复制到公式中第二种方法(人民币)贰佰壹拾叁元伍角陆分(人民币)贰佰壹拾叁元伍角整(人民币)贰佰壹拾叁元整(人民币)贰佰壹拾叁元零陆分金额为负无效(人民币)伍角整(人民币)伍角陆分(人民币)零元整(人民币)零元整(人民币)壹百贰拾叁万肆千伍百陆拾柒元捌角玖分大写可以先将数据输入单元格区域,然后在其它单元格输入【=】,用鼠标选定该区域后按F9,显示的就是数组常量,然后复制到公式中数值 填位 公式123 123# =(A2&REPT(#,16-LEN(A2)123 #123 =REPT(#,16-LEN(A3)&A3123 #123# =REPT(#,8-LEN(A4)/2)&A4&REPT(#,8-LEN(A4)/2)123 123.00# =(TEXT(A5,¥#,#0.00)&REPT(#,16-LEN(TEXT(A5,$#,#0.00)数据 整齐123 00123 0000012.3 012.30 000.0012.3 12.3 #.#12.3456 12.351234.1234 1234.1212.3 12.3 ?.?12.3456 12.351234.123 1234.1212.3 12.3 ?.?12.3456 12.3461234.123 1234.123数据自定义人民币 10 人民币 010 人民币 000人民币 500 人民币 500人民币 315 人民币 3150 825=REPT(#,8-LEN(A4)/2)&A4&REPT(#,8-LEN(A4)/2)=(TEXT(A5,¥#,#0.00)&REPT(#,16-LEN(TEXT(A5,$#,#0.00)职工编号 姓名 性别 年龄 职称 部门 基础工资 车帖2003001 王1 男 45 高级工程师 一车间 5400 2002003002 王2 男 38 高级工程师 一车间 4800 1202003003 王3 女 44 高级工程师 二车间 4900 1202003004 王4 男 28 工程师 四车间 4000 1202003005 王5 男 35 技师 二车间 5000 2002003006 王6 男 50 高级工程师 三车间 5000 1202003007 王7 男 49 高级工程师 二车间 5200 2002003008 王8 男 52 高级工程师 三车间 5200 1202003009 王9 男 55 高级工程师 一车间 5000 1202003010 王10 男 29 工程师 四车间 3500 1202003011 王11 男 20 助理工程师 四车间 2400 02003012 王12 男 42 高级工程师 四车间 4900 2002003013 王13 男 26 助理工程师 二车间 2500 02003014 王14 男 38 工程师 三车间 3100 1202003015 王15 男 48 工程师 四车间 4800 1202003016 王16 女 36 工程师 三车间 3200 1202003017 王17 女 23 助理工程师 一车间 2000 02003018 王18 女 27 助理工程师 二车间 2200 02003019 王19 女 30 工程师 四车间 3000 1202003020 王20 男 38 工程师 三车间 3500 120简单求和统计所有人的基础工资总和 79600 =SUM(G2:G21)单条件求和统计男职工人数 15 =COUNTIF(C2:C21,男)统计一车间工资之和 17200 =SUMIF(F2:F21,一车间,G2:G21)求30岁以下职工工资 16600 =SUMIF(D2:D21,5000)多条件求和求男性高级工程师人数考 勤 及 奖 金 表月 份: 8月职工编号 姓名 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 262003001 王1 Q B B B Q J J Q Q Q Q Q J J Q Q Q Q Q J J Q Q Q Q Q2003002 王2 Q Q Q Q Q J J S S S S S J J S S S S S J J Q S Q Q Q2003003 王3 Q Q Q Q Q J J Q Q Q Q Q J J Q Q Q Q Q J J Q Q Q G G2003004 王4 Q Q Q Q Q J J B B B B B J J Q Q Q Q Q J J Q Q Q Q Q2003005 王5 Q Q Q Q Q J J Q Q Q Q Q J J Q Q Q Q Q J J Q Q Q Q Q2003006 王6 Q Q Q Q Q J J Q Q Q Q Q J J Q Q Q Q Q J J Q Q Q Q Q2003007 王7 Q Q Q B Q J J Q Q Q Q Q J J Q Q Q Q Q J J Q Q Q Q Q2003008 王8 Q Q Q Q Q J J Q Q Q Q Q J J Q Q Q Q Q J J Q Q Q Q Q2003009 王9 Q Q Q Q Q J J Q Q G G Q J J Q Q B Q Q J J Q Q Q Q Q2003010 王10 Q Q Q Q Q J J Q Q G Q Q J J Q Q Q Q Q J J Q Q Q Q Q2003011 王11 Q Q Q S Q J J Q Q Q Q Q J J Q Q Q Q Q J J Q Q Q Q Q2003012 王12 Q Q Q Q Q J J Q Q Q Q Q J J Q Q Q B Q J J Q Q S S Q2003013 王13 Q Q Q Q Q J J Q Q Q Q Q J J Q Q Q Q Q J J Q Q Q Q Q2003014 王14 Q Q Q Q Q J J Q Q Q Q Q J J Q Q Q Q Q J J Q Q Q Q Q2003015 王15 Q Q Q Q Q J J Q Q Q Q Q J J Q Q Q Q Q J J Q Q Q Q Q2003016 王16 Q Q G Q Q J J Q Q Q Q Q J J Q Q S Q Q J J Q Q Q Q Q2003017 王17 Q Q Q Q Q J J Q Q Q Q Q J J Q Q Q Q Q J J Q Q Q Q Q2003018 王18 Q Q Q Q Q J J Q Q B Q Q J J Q Q Q Q Q J J Q Q Q Q Q2003019 王19 Q Q Q Q Q J J Q Q Q Q Q J J Q Q Q Q Q J J Q Q Q Q Q2003020 王20 Q Q Q Q Q J J Q Q Q Q Q J J Q Q Q Q Q J J Q Q Q Q Q注:图中符号的含义是:B表示病假,S表示事假,G表示旷工,Q表示出勤,J表示法定休息日。IF(300-COUNTIF(C6:AG6,B)*15-COUNTIF(C6:AG6,S)*30-COUNTIF(C6:AG6,G)*600,300-COUNTIF(C6:AG6,B)*15-COUNTIF(C6:AG6,S)*30-COUNTIF(C6:AG6,G)*60,0)27 28 29 30 31 奖金额J J B B B 210.0J J Q Q Q 0.0J J G G G 0.0J J Q Q Q 225.0J J Q Q Q 300.0J J Q Q Q 300.0J J Q Q Q 285.0J J Q Q Q 300.0J J Q Q Q 165.0J J Q Q Q 240.0J J Q Q Q 270.0J J G Q Q 165.0J J Q Q Q 300.0J J Q Q Q 300.0J J Q Q Q 300.0J J Q Q Q 210.0J J Q Q Q 300.0J J Q Q Q 285.0J J Q Q Q 300.0J J Q Q Q 300.0考 勤 及 奖 金 表IF(300-COUNTIF(C6:AG6,B)*15-COUNTIF(C6:AG6,S)*30-COUNTIF(C6:AG6,G)*600,300-COUNTIF(C6:AG6,B)*15-COUNTIF(C6:AG6,S)*30-COUNTIF(C6:AG6,G)*60,0)员 工 工 资 表 2010/10员工编号 员工姓名 所在部门 基本工资 考勤奖 住房补助 车费补助保险金应发金额扣税所得额个人所得税2003001 王1 一车间 5400 210 100 200 200 5710 4710 581.52003002 王2 一车间 4800 0 100 120 200 4820 3820 4482003003 王3 二车间 4900 0 100 120 200 4920 3920 4632003004 王4 四车间 4000 225 100 120 200 4245 3245361.752003005 王5 二车间 5000 300 100 200 200 5400 4400 5352003006 王6 三车间 5000 300 100 120 200 5320 4320 5232003007 王7 二车间 5200 285 100 200 200 5585 4585562.752003008 王8 三车间 5200 300 100 120 200 5520 4520 5532003009 王9 一车间 5000 165 100 120 200 5185 4185502.752003010 王10 四车间 3500 240 100 120 200 3760 2760 2892003011 王11 四车间 2400 270 100 0 200 2570 1570 1322003012 王12 四车间 4900 165 100 200 200 5165 4165499.752003013 王13 二车间 2500 300 100 0 200 2700 1700 1452003014 王14 三车间 3100 300 100 120 200 3420 2420 2382003015 王15 四车间 4800 300 100 120 200 5120 4120 4932003016 王16 三车间 3200 210 100 120 200 3430 2430 239.52003017 王17 一车间 2000 300 100 0 200 2200 1200 952003018 王18 二车间 2200 285 100 0 200 2385 1385 113.52003019 王19 四车间 3000 300 100 120 200 3320 2320 2232003020 王20 三车间 3500 300 100 120 200 3820 2820 298实发金额5128.5437244573883.25486547975022.2549674682.25347124384665.252555318246273190.521052271.5309735222010/10员工编号 员工姓名 所在部门 基本工资 考勤奖 住房补助 车费补助 保险金 应发金额2003001 王1 一车间 5400 210 100 200 200 5710员工编号 员工姓名 所在部门 基本工资 考勤奖 住房补助 车费补助 保险金 应发金额2003002 王2 一车间 4800 0 100 120 200 4820员工编号 员工姓名 所在部门 基本工资 考勤奖 住房补助 车费补助 保险金 应发金额2003003 王3 二车间 4900 0 100 120 200 4920员工编号 员工姓名 所在部门 基本工资 考勤奖 住房补助 车费补助 保险金 应发金额2003004 王4 四车间 4000 225 100 120 200 4245员工编号 员工姓名 所在部门 基本工资 考勤奖 住房补助 车费补助 保险金 应发金额2003005 王5 二车间 5000 300 100 200 200 5400员工编号 员工姓名 所在部门 基本工资 考勤奖 住房补助 车费补助 保险金 应发金额2003006 王6 三车间 5000 300 100 120 200 5320员工编号 员工姓名 所在部门 基本工资 考勤奖 住房补助 车费补助 保险金 应发金额2003007 王7 二车间 5200 285 100 200 200 5585员工编号 员工姓名 所在部门 基本工资 考勤奖 住房补助 车费补助 保险金 应发金额2003008 王8 三车间 5200 300 100 120 200 5520员工编号 员工姓名 所在部门 基本工资 考勤奖 住房补助 车费补助 保险金 应发金额2003009 王9 一车间 5000 165 100 120 200 5185员工编号 员工姓名 所在部门 基本工资 考勤奖 住房补助 车费补助 保险金 应发金额2003010 王10 四车间 3500 240 100 120 200 3760员工编号 员工姓名 所在部门 基本工资 考勤奖 住房补助 车费补助 保险金 应发金额2003011 王11 四车间 2400 270 100 0 200 2570员工编号 员工姓名 所在部门 基本工资 考勤奖 住房补助 车费补助 保险金 应发金额2003012 王12 四车间 4900 165 100 200 200 5165员工编号 员工姓名 所在部门 基本工资 考勤奖 住房补助 车费补助 保险金 应发金额2003013 王13 二车间 2500 300 100 0 200 2700员工编号 员工姓名 所在部门 基本工资 考勤奖 住房补助 车费补助 保险金 应发金额2003014 王14 三车间 3100 300 100 120 200 3420员工编号 员工姓名 所在部门 基本工资 考勤奖 住房补助 车费补助 保险金 应发金额2003015 王15 四车间 4800 300 100 120 200 5120员工编号 员工姓名 所在部门 基本工资 考勤奖 住房补助 车费补助 保险金 应发金额2003016 王16 三车间 3200 210 100 120 200 3430员工编号 员工姓名 所在部门 基本工资 考勤奖 住房补助 车费补助 保险金 应发金额2003017 王17 一车间 2000 300 100 0 200 2200员工编号 员工姓名 所在部门 基本工资 考勤奖 住房补助 车费补助 保险金 应发金额2003018 王18 二车间 2200 285 100 0 200 2385员工编号 员工姓名 所在部门 基本工资 考勤奖 住房补助 车费补助 保险金 应发金额2003019 王19 四车间 3000 300 100 120 200 3320员工编号 员工姓名 所在部门 基本工资 考勤奖 住房补助 车费补助 保险金 应发金额2003020 王20 三车间 3500 300 100 120 200 3820扣税所得额 个人所得税 实发金额4710 581.5 5128.5扣税所得额 个人所得税 实发金额3820 448 4372扣税所得额 个人所得税 实发金额3920 463 4457扣税所得额 个人所得税 实发金额3245 361.75 3883.25扣税所得额 个人所得税 实发金额4400 535 4865扣税所得额 个人所得税 实发金额4320 523 4797扣税所得额 个人所得税 实发金额4585 562.75 5022.25扣税所得额 个人所得税 实发金额4520 553 4967扣税所得额 个人所得税 实发金额4185 502.75 4682.25扣税所得额 个人所得税 实发金额2760 289 3471扣税所得额 个人所得税 实发金额1570 132 2438扣税所得额 个人所得税 实发金额4165 499.75 4665.25扣税所得额 个人所得税 实发金额1700 145 2555扣税所得额 个人所得税 实发金额2420 238 3182扣税所得额 个人所得税 实发金额4120 493 4627扣税所得额 个人所得税 实发金额2430 239.5 3190.5扣税所得额 个人所得税 实发金额1200 95 2105扣税所得额 个人所得税 实发金额1385 113.5 2271.5扣税所得额 个人所得税 实发金额2320 223 3097扣税所得额 个人所得税 实发金额2820 298 3522vlookup应用实例精确匹配查询之基本运用精确查找是vlookup最基本也是最常用的功能,对于数据量大的查找,其速度比菜单中的查找还快。设置vlookup第四个参数为false或0,即为精确查找。精确查找适用于文本,也适用于数值;但对数值查找时须注意格式一致,否则会出错$B$9:$F$12 姓名 工号 性别 籍贯 出生年月张三丰 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】中查找籍贯【$B$9:$F$12】是定义的单元格区域$B$9:$F$12的名称,在名称框(编辑栏左边)可以选定;也可在插入名称定义中查看修改工号 籍贯KT002 天津 根据工号在$C$8:$E$12中查找籍贯注意范围已改为$C$8:$E$12,确保工号在第一列;由于范围的改变,籍贯位于该范围的第3列,因此第三个参数为3工号 出生年月KT001 25781 根据工号在$C$8:$F$12中查找出生年月,但此时返回的是时间序列值(即格式没有带过来)1970年8月 需要重新设定单元格格式1970年8月 或在公式中使用text设定格式数值近似匹配查询之基本运用近似匹配查找通常情况下用于累进数值的查找。此时第四个参数省略,或为true,或为非0数值。(EXCEL中0等同FALSE,非零值均视为TRUE)这是一张个所税税率表,对于不确定的收入,可以利用近似匹配查找税率及速扣数表二 级数 应税所得超过 且不超过 税率(%) 速算扣除数 1 500 5 02 500 2000 10 253 2000 5000 15 1254 5000 20000 20 3755 20000 40000 25 13756 40000 60000 30 33757 60000 80000 35 63758 80000 100000 40 103759 100000 45 15375应税所得 税率 速算扣除数 税款5360 20 375 6975360在表中未列出,因此EXCEL查找小于5360的最大值即5000,并返回对应的税率等。注意公式中第四个参数省略,或为true,或为非0数值。并且查找的第一列必须升序排列,否则不会返回期望的结果(需要注意的是此时不一定返回错误)。试着输入5000,你会发现税率为20%,应是15%(由条件指定),虽然对最后计算结果没有影响。以下公式可以解决这个问题:应税所得 税率 速算扣除数 税款5000 15 125 625文本近似匹配查询之基本运用表三 对于文本,一般不使用近似匹配查找(非模糊查找)。吖 A 并非文本不能用于近似查找,主要是没有太多用途。除了下面这个例子:八 B嚓 C 这个例子利用文本近似匹配查找汉字的第一个拼音字母咑 D 由于EXCEL中汉字字符是按拼音排序的,因此汉字字符比较时的大小与拼音同序(一般情况下)鵽 E 左边的表是根据汉字拼音首字母排列,并取各字母的临界点汉字字符(该字母的“最小汉字”)发 F猤 G 汉字字符 拼音首字母铪 H 家 J夻 J咔 K 通过将“家”与表中字符比较后,找到比“家”小的最大值“夻”,返回J垃 L 这个例子一般用于姓名的缩写,而上面的公式只找第一个字符,因此需要修改嘸 M旀 N 汉字字符 拼音首字母噢 O 普利卡 PLK妑 P七 Q 这个公式取得前三个汉字字符的拼音字母。使用MID依次取出字符后由Vlookup查询到字母,最后“&”连接文本囕 R仨 S 使用该例关键是【表三】的建立;同理,如果要查找汉字的完整拼音,可以建立一个由各种拼音组成的类似列表。他 T 但这个方法并不一直正确,由于多音字及文字大小排序与其拼音不完全一致的原因,有时返回的字母可能不是你要的屲 W夕 X丫 Y帀 Z区别查询结果的空白与0值表四 姓名 内容 张三丰 0 vlookup对于查找到的0值会返回0;张三丰 0 李四光 0 对于空白单元格(非空字符串)也会返回0;李四光 王麻子 对空字符串仍返回空字符串。王麻子 赵六儿赵六儿张三丰 0 如要对0值和空白单元格区别,可以直接判断是否,或使用len函数测试返回值的长度;李四光 如不需要0值,可直接判断0,或在【工具】【选项】中将【零值】去掉。依次序返回同一查询结果的多列内容按姓名在【$B$9:$F$12】中查询全部内容姓名 工号 性别 籍贯 出生年月李四光 KT002 女 天津 1980年9月王麻子 KT003 男 河北 1975年3月当查询某个结果的多列内容,并要依次取出,可使用column()函数作为index_num参数column()函数返回公式所在列的列号,当公式复制时,会随单元格的移动相应改变lookup_value参数使用混合引用,如$B96。即对列标绝对引用(列标前加$)、行号相对引用(行号前没有$);复制时引用单元格会改变。上面公式只要输入C96中的公式,然后向右、向下复制就可以。返回同一查询结果的多列(不按原次序)的内容按姓名及不同项目(不依次序)在【$B$9:$F$12】中查询姓名 性别 籍贯 出生年月 工号 先由match函数确定项目内容在B8:F8区域的位置,将返回值作为vlookup的第三个参数。赵六儿 女 河南 1985年12月 KT004 注意公式中Match函数的第一个参数使用行号绝对引用、列标相对引用;王麻子 男 河北 1975年3月 KT003 Vlookup函数的第一个参数使用行号相对引用、列标绝对引用;按部分内容模糊查询按姓在【$B$9:$F$12】中查询姓 姓名 工号 性别 籍贯 出生年月王 王麻子 KT003 男 河北 1975年3月vlookup支持通配符【*】和【?】,因此当查找内容不全时,可以使用通配符;如例公式中使用【&】将查找文本与通配符连接后查找,注意通配符连接位置注意查找中,有多个符合条件的结果时,只会返回第一个在同一单元格按不同字段查询在同一单元格按姓名或工号在【$B$9:$F$12】中查询姓名或工号 性别 籍贯 出生年月KT002 女 天津 1980年9月 使用countif判断,如果在姓名区中有C131单元格的内容,按姓名查找;否则按工号查找李四光 女 天津 1980年9月 或者使用isna判断,如果按姓名查找出错,就按工号查找;王麻子 男 河北 1975年3月 或者使用一个vlookup,第二、三个参数由判断结果决定(查找范围、列号)。返回多个符合条件的查询结果表五 查找的结果不止一个,并要全部返回姓名 工号 vlookup对于包含多个结果的查找只会返回第一个结果(数据区由上向下第一个)1 张三丰 KT001 但有时需要返回所有的结果。如例,需要按姓名查找同名的各个工号1 李四光 KT002 这种情况一般使用数组公式解决。但数据量大时,使用数组公式会由于计算量大,速度会很慢1 李四光 KT003 vlookup没有直接的解决方法,需要添加辅助列(注意辅助列需添加在左侧,因为要根据辅助列的内容查找)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当然,辅助列中的公式可以改为其它,只要达到区别相同内容的目的有两个并列条件(不同字段)的查询表六: 查找某业务员对某客户的销售额业务员 客户 销售额 对于此类根据两个或多个并列条件查找某个结果的问题,使用vlookup也需要添加辅助列张三丰商场A 张三丰 商场A 100 B158公式=C158&D158,即将被查找的内容合并作为一列,并以此作为查找列。张三丰超市B 张三丰 超市B 200李四光店铺C 李四光 店铺C 300 有些数据需要在两个数据间插入一特殊符号(查询数据中没有的任意符号)以示区别李四光超市B 李四光 超市B 400 例如两个条件分别是:张三丰店铺C 张三丰 店铺C 500李四光商场A 李四光 商场A 600 此时就需要用其他符号将其区别:业务员 客户 销售额李四光 超市B 400 使用【&】将要查询的两个条件合并后作为查询值,在区域$B$158:$E$163中查询第4列内容使用数组常量的查询一般,使用vlookup前需要有一个对应的表格供查找,即作为vlookup的Table_array参数的表格区域;但有时这个表格可能会显得累赘;如果数据不是太多而且比较固定,可以考虑使用数组常量代替单元格区域的方法;所谓数组常量就是直接输入的一组数组,以“”开始结束、同一行的数值用逗号“,”分隔、同一列的数值用分号“;”分隔如10,20,30,是一行三列(横向)、包含三个元素的一维数组常量10;20;30;40,是一列四行(纵向)、包含四个元素的一维数组常量10,20,30;40,50,60,是两行三列、包含六个元素的二维数组常量;二维数组常量需要行列等长如果你觉得输数组常量麻烦(要区分【,】、【;】,文本要加【】),可以先将数据输入单元格区域,然后在其它单元格输入【=】,用鼠标选定该区域后按F9,显示的就是数组常量,然后复制到公式中其实将以前使用的vlookup公式中第三个参数(区域不要太大)选定按F9,再回车,可以看到公式正常运作;此时已使用了数组常量以下举例中的公式并不是数组公式,输入时不用Ctrl+Shift+Enter一齐按搬一个由chenjun版主提供的计算个人所得税的例子:应税所得 税率 速算扣除数 税款6500 20 375 925再搬一个前面的例子汉字字符 拼音首字母海 H 是否被公式中的数组常量吓一跳?不是一个个输的,按F9得到的;汉字字符 拼音首字母螺丝扣 LSK 对于此类固定的数组常量可以将其定义一个名称,公式就简洁多了,还不占用单元格公式中的【拼音】就是一个定义的名称,可以自【插入】【名称】【定义】中查看从右向左查询我们知道,vlookup查找的列必须位于查找区域的最左列,有时会需要按右面的查找左边的值以【$B$9:$F$12】为例,要按【姓名】查询【工号】很容易;但反过来按【工号】查找【姓名】直接使用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是一行二列(横向)常量数组,后面的两个区域是多行一列(纵向),即两个数组的方向不同,这样才会生成两列多行数组张三丰李四光王麻子赵六儿KT001KT002KT003KT004精确查找是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】是定义的单元格区域$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查询到字母,最后“&”连接文本使用该例关键是【表三】的建立;同理,如果要查找汉字的完整拼音,可以建立一个由各种拼音组成的类似列表。但这个方法并不一直正确,由于多音字及文字大小排序与其拼音不完全一致的原因,有时返回的字母可能不是你要的对于空白单元格(非空字符串)也会返回0;如要对0值和空白单元格区别,可以直接判断是否,或使用len函数测试返回值的长度;如不需要0值,可直接判断0,或在【工具】【选项】中将【零值】去掉。column()函数返回公式所在列的列号,当公式复制时,会随单元格的移动相应改变lookup_value参数使用混合引用,如$B96。即对列标绝对引用(列标前加$)、行号相对引用(行号前没有$);复制时引用单元格会改变。先由match函数确定项目内容在B8:F8区域的位置,将返回值作为vlookup的第三个参数。注意公式中Match函数的第一个参数使用行号绝对引用、列标相对引用;Vlookup函数的第一个参数使用行号相对引用、列标绝对引用;中国 人民 直接合并后相同中国人 民中国人民中国人民使用countif判断,如果在姓名区中有C131单元格的内容,按姓名查找;否则按工号查找或者使用isna判断,如果按姓名查找出错,就按工号查找;或者使用一个vlookup,第二、三个参数由判断结果决定(查找范围、列号)。vlookup对于包含多个结果的查找只会返回第一个结果(数据区由上向下第一个)但有时需要返回所有的结果。如例,需要按姓名查找同名的各个工号这种情况一般使用数组公式解决。但数据量大时,使用数组公式会由于计算量大,速度会很慢vlookup没有直接的解决方法,需要添加辅助列(注意辅助列需添加在左侧,因为要根据辅助列的内容查找)表五左侧白色区域为辅助列,B141公式为=($C$148=C141)+B140;等同=IF($C$148=C141,1,0)+B140该公式结果为:如姓名列内容与查找姓名相同,则按顺序显示1、2、3,从而将相同姓名区分然后,在查询中查找数值1、2、3即可查找到对应查找内容的多个结果(注意查找区域由辅助列开始)为便于复制,vlookup第一个参数数值1、2、3使用函数row()返回的行号产生当然,辅助列中的公式可以改为其它,只要达到区别相同内容的目的对于此类根据两个或多个并列条件查找某个结果的问题,使用vlookup也需要添加辅助列B158公式=C158&D158,即将被查找的内容合并作为一列,并以此作为查找列。有些数据需要在两个数据间插入一特殊符号(查询数据中没有的任意符号)以示区别此时就需要用其他符号将其区别:使用【&】将要查询的两个条件合并后作为查询值,在区域$B$158:$E$163中查询第4列内容一般,使用vlookup前需要有一个对应的表格供查找,即作为vlookup的Table_array参数的表格区域;但有时这个表格可能会显得累赘;如果数据不是太多而且比较固定,可以考虑使用数组常量代替单元格区域的方法;所谓数组常量就是直接输入的一组数组,以“”开始结束、同一行的数值用逗号“,”分隔、同一列的数值用分号“;”分隔如10,20,30,是一行三列(横向)、包含三个元素的一维数组常量10;20;30;40,是一列四行(纵向)、包含四个元素的一维数组常量10,20,30;40,50,60,是两行三列、包含六个元素的二维数组常量;二维数组常量需要行列等长可以先将数据输入单元格区域,然后在其它单元格输入【=】,用鼠标选定该区域后按F9,显示的就是数组常量,然后复制到公式中其实将以前使用的vlookup公式中第三个参数(区域不要太大)选定按F9,再回车,可以看到公式正常运作;此时已使用了数组常量对于此类固定的数组常量可以将其定义一个名称,公式就简洁多了,还不占用单元格公式中的【拼音】就是一个定义的名称,可以自【插入】【名称】【定义】中查看我们知道,vlookup查找的列必须位于查找区域的最左列,有时会需要按右面的查找左边的值以【$B$9:$F$12】为例,要按【姓名】查询【工号】很容易;但反过来按【工号】查找【姓名】直接使用vlookup就不行了此时可以使用的公式很多,如lookup、index+match、offset+match、indirect+match等但由chenjun版主提供的使用vlookup的解法我觉得很有参考价值,介绍这个用法的目的主要是学会将两个一维数组合并为一个二维数组K