Excel 中的 vlookup函数可结合 if 多条件查找,并且可用数组作为 if 的条件,这样可以同时查找多个字段,例如查找服装销量表中分类为衬衫且价格为85元的服装。文章先列举了四个vlookup函数的多条件操作实例,然后对它们逐一剖析,主要剖析if{1,0}数组条件;四个操作实例分别为:实例1:条件用 IF{1,0}、实例2:条件用 IF{0,1}、实例3:两列连接查找,条件用 IF{1,0}与用 & 连接查找区域和实例4:两列连接查找,条件用 IF{0,1}与用 & 连接查找区域。实例中操作所用版本均为 Excel 2016。
一、Excel vlookup函数的多条件操作实例
(一)实例1:条件用 IF{1,0}
1、假如要从服装销量表中找出价格为85元的服装名称。在 A12 单元格中输入要查找的价格,例如 85,把公式 =VLOOKUP(A12,IF({1,0},C2:C9,B2:B9),2,0) 复制到 B12 单元格,按回车,则返回“粉红短袖衬衫”,这件衬衫的价格恰好是 85 元,说明结果正确;操作过程步骤,如图1所示:
图1
提示:若表格中有多件85元的服装,则vlookup函数只返回第一件符合条件的服装。
2、公式简析(详细剖析见下文)
公式中查找区域为 IF 数组条件,数组由 1 和 0 组成,1 表示 True(真),0 表示 False(假);执行公式时,先从数组中取 1,由于 1 为真,所以从 C2:C9 中返回一个值;然后再从数组中取 0,由于 0 为假,所以从 B2:B9 中返回一个值;如此反复,直到遍历完 C2 到 C9 与 B2 到 B9。
(二)实例2:条件用 IF{0,1}
1、同样要从服装销量表中找出价格为85元的服装名称。在 A13 单元格输入 85,把公式 =VLOOKUP(A13,IF({0,1},B2:B9,C2:C9),2,0) 复制到 B13 单元格,按回车,则同样返回“粉红短袖衬衫”,与实例1的结果一样,操作过程步骤,如图2所示:
图2
2、公式简析
公式与实例1相比,IF 的数组条件与只是把 1 和 0 交换了次序,由 {1,0} 变为 {0,1};公式执行时,先从数组中取出 0,由于 0 为假,所以从 C2 到 C9 中返回一个值;然后再从数组中取 1,由于 1 为真,所以从 B2 到 B9 中返回一个值;如此反复,直到遍历完 B2 到 B9 与 C2 到 C9。从执行过程来看,取出值的顺序与实例1完全一样,因此返回同一个结果。
(三)实例3:两列连接查找,条件用 IF{1,0}与用 & 连接查找区域
1、假如要从服装销量表中找出“小类”为“衬衫”、“价格”为85元的服装名称。在 A12 输入“衬衫”,B12 输入 85,把公式 =VLOOKUP(A12&B12,IF({1,0},D2:D9&E2:E9,B2:B9),2,0) 复制到 C12,按 Ctrl + Shift + 回车,则返回服装名称同样为“粉红短袖衬衫”,操作过程步骤,如图3所示:
图3
2、公式说明:
A12&B12 是要查找的值,A12 与 B12 用连接符号 & 连接起来,结果为“衬衫85”;查找区域的条件同样用 {1,0} 数组条件;公式执行时,先取 1,由于 1 为真,所以从 D2:D9&E2:E9 中返回一个连结值(例如 D2&E2);然后取 0,由于 0 是假,所以从 B2:B9 中取一个值取来;如此反复,直到遍历完 D2&E2 到 D9&E9 与 B2 到 B9。
(四)实例4:两列连接查找,条件用 IF{0,1}与用 & 连接查找区域
1、同样要从服装销量表中找出“小类”为“衬衫”、“价格”为85元的服装名称。在 A13 输入“衬衫”,B13 输入 85,如图4所示:
图4
2、把公式 =VLOOKUP(A13&B13,IF({0,1},B2:B9,D2:D9&E2:E9),2,0) 复制到 C13 单元格,如图5所示:
图5
3、按 Ctrl + Shift + 回车,同样返回与实例3一样的服装“粉红短袖衬衫”,如图6所示:
图6
4、公式说明:
与实例3相比,实例4只是 IF 的数组条件由 {1,0} 变为 {0,1},IF 条件的真假返回值相互调换,即 D2:D9&E2:E9 与 B2:B9 的位置相互调换。经此调换,公式执行所返回的值与实例3一样,所以能查到与实例3一样的结果。
二、Excel vlookup函数与if{1,0}数组组合剖析
(一)实例1剖析
公式为:=VLOOKUP(A12,IF({1,0},C2:C9,B2:B9),2,0)
1、Excel 横向数组与纵向数组的区别。横向数组是占一行两列,元素之间用“,”分隔,例如 {1,0} 为横向数组;纵向数组占一列两行,元素之间用“;”分隔,例如 {1;0} 为纵向数组。
2、if 条件分解
由于 {1,0} 为横向数组,C2:C9 与 B2:B9 之间共有八行,因此 IF 的三个参数要分为八组,即:
参数1分解为:{1,0; 1,0; 1,0; 1,0; 1,0; 1,0; 1,0; 1,0;}
参数2分解为:{C2,C2; C3,C3; C4,C4; C5,C5; C6,C6; C7,C7; C8,C8; C9,C9;}
参数3分解为:{B2,B2; B3,B3; B4,B4; B5,B5; B6,B6; B7,B7; B8,B8; B9,B9; }
3、公式执行时,if 条件组合
A、第一次执行,分别从三个参数中取第一个元素(即从参数1中取 1,从参数2中取 C2,从参数3中取 B2),组成 IF(1,C2,B2),由于 1 为真,所以取 C2。
B、第二次执行,分别从三个参数中取第二个元素,组成 IF(0,C2,B2),由于 0 为假,所以取 B2。
C、第三次执行,分别从三个参数中取第三个元素,组成 IF(1,C3,B3),由于 1 为真,所以取 C3。
D、以此类推,直到遍历完 C2 到 C9 和 B2 到 B9。
(二)实例2剖析
公式为:=VLOOKUP(A13,IF({0,1},B2:B9,C2:C9),2,0)
实例2 与实例1 只是数组元素 1 和 0及真假条件调换了位置,剖析方法与实例1一样。
1、if 条件分解
由于 {0,1} 同样为横向数组,B2:B9 与 C2:C9 之间共有八行,因此 IF 的三个参数要分为八组,即:
参数1分解为:{0,1; 0,1; 0,1; 0,1; 0,1; 0,1; 0,1; 0,1;}
参数2分解为:{B2,B2; B3,B3; B4,B4; B5,B5; B6,B6; B7,B7; B8,B8; B9,B9; }
参数3分解为:{C2,C2; C3,C3; C4,C4; C5,C5; C6,C6; C7,C7; C8,C8; C9,C9;}
2、公式执行时,if 条件组合
A、第一次执行,分别从三个参数中取第一个元素,组成 IF(0,B2,C2),由于 0 为假,所以取 C2。
B、第二次执行,分别从三个参数中取第二个元素,组成 IF(1,B2,C2),由于 1 为真,所以取 B2。
C、第三次执行,分别从三个参数中取第三个元素,组成 IF(0,B3,C3),由于 0 为假,所以取 C3。
D、以此类推,直到遍历完 B2 到 B9 和 C2 到 C9。从执行的结果来看,实例2与实例1每次执行返回的结果一样。
(三)实例3剖析
公式为:=VLOOKUP(A12&B12,IF({1,0},D2:D9&E2:E9,B2:B9),2,0)
实例3 与实例1 的原理是一样的,只是实例3的查找值和查找范围用连接符号 & 把两列连接成一列,剖析方法与实例1也一样。
1、if 条件分解
由于 {1,0} 为横向数组,D2&E2 到 D9&E9 与 B2 到 B9 之间共有八行,因此 IF 的三个参数要分为八组,即:
参数1分解为:{1,0; 1,0; 1,0; 1,0; 1,0; 1,0; 1,0; 1,0;}
参数2分解为:{D2&E2,D2&E2; D3&E3,D3&E3; D4&E4,D4&E4; D5&E5,D5&E5; D6&E6,D6&E6; D7&E7,D7&E7; D8&E8,D8&E8; D9&E9,D9&E9;}
参数3分解为:{B2,B2; B3,B3; B4,B4; B5,B5; B6,B6; B7,B7; B8,B8; B9,B9; }
2、公式执行时,if 条件组合
A、第一次执行,分别从三个参数中取第一个元素,组成 IF(1,D2&E2,B2),由于 1 为真,所以取 D2&E2。
B、第二次执行,分别从三个参数中取第二个元素,组成 IF(0,D2&E2,B2),由于 0 为假,所以取 B2。
C、第三次执行,分别从三个参数中取第三个元素,组成 IF(1,D3&E3,B3),由于 1 为真,所以取 D3&E3。
D、以此类推,直到遍历完 D2&E2 到 D9&E9 与 B2 到 B9。
实例4与实例2和实例3类似,剖析方法也相同,可以自己尝试剖析以加深理解。
联系客服