案例：多前提查找
第26章讲的INDEX加MATCH查找，都是根据单一目的值停止查找，而当目的值和查找区域都不是独一时怎么办？图27-1所示的是“三国粹校”的进修功效笔录表，B、C、D列别离为姓名、学期、科目，那三列每一列的信息都不是独一的。那时候需要停止响应前提的查询，如图中I2:K7单位格区域的前提，要怎么处置呢？
ENTERTITLE
图27-1多前提查找数据源
察看数据，B~D列每一列的值都有反复，但是，将三列的数据粘在一路，得到的值就是独一了，查询的时候也将I~K列的数据粘在一路做为查找值就能够了。先在G列添加辅助列，G2单位格的公式为：
=B2C2D2
然后将公式向下复造到G25单位格，之后在L3单位格中输入查找公式：=INDEX(E:E,MATCH(I3J3K3,G:G,0))
留意此处需要“图钉”，公式进一步完美为：=INDEX(E:E,MATCH($I3$J3$K3,$G:$G,0))
将L3的公式复造到L3:M7单位格区域，成果如图27-2所示，此中G列是添加的具有独一值的辅助列。
ENTERTITLE
图27-2多前提查找成果
能够不消辅助列吗？当你在论坛或某些办公群里乞助时，高手们会怎么给你写公式？
{=INDEX(E:E,MATCH($I3$J3$K3,$B:$B$C:$C$D:$D,0))}
那是一个数组公式，要按三键（【Ctrl+Shift+Enter】）完毕。当你在表格中试验公式时，会发现Excel会很卡，那是为什么？
$B:$B$C:$C是将B列和C列的值粘在一路，而每一列有1048576行，将B、C列数据粘在一路后，又粘上了D列，再将公式向右向下复造，此时的计算量关于计算机来说是个灾难。
那什么时候能够用整列呢？一般来讲，“地痞三兄弟”“桃园三结义”及后续常胜将军“赵子龙”，它们间接引用的查找区域能够是整列的，但绝对不允许呈现“A:AB:B”“A:A=1”那种对整列做判断计算的形式。
多前提查找，若是返回的成果是数字的话，还能够借用另一个函数，在L3单位格中输入公式：
=SUMIFS(E:E,B:B,I3,C:C,J3,D:D,K3)
SUMIFS是多前提乞降函数，本案例有一个特点，每一组多前提都是独一的，所以多前提的乞降也就是最末关于一个值乞降，间接到达了查找的目标。
案例：造做人事工做中的动态员工信息卡
图27-3所示的是模仿HR工做中的员工信息卡造做的一个表，此中通过改换C2单位格的姓名，能够更新查找差别人员的信息，同时对应的头像也会主动改变，下面来看一下操做步调。
ENTERTITLE
图27-3员工信息卡展现
起首领会一下根底信息，如图27-4所示，根底信息工做表的A2:H14单位格区域是每个员工的根底信息，包罗姓名、照片、生日、员工号等。
ENTERTITLE
图27-4根底信息
1．做出能够选择的姓名
在员工信息卡表中，选择C2单位格，然后切换到【数据】选项卡，单击【数据验证】按钮（在2013版及之后的版本称为【数据验证】，在2010版及之前的版本称为【数据有效性】，英文版本称为【DataValidation】），在弹出的【数据验证】对话框中，将【允许】下拉列表中选择【序列】，之后在【来源】参数框中选择根底信息表的B2:B14单位格区域，会主动生成公式“=根底信息!$B$2:$B$14”，最初单击【确定】按钮完成设置，如图27-5所示。
ENTERTITLE
图27-5插入数据验证
如今选择C2单位格会呈现一个下拉按钮，单击此下拉按钮就会呈现可供选择的下拉列表，如图27-6所示。
ENTERTITLE
图27-6数据验证效果
2007版及之前版本的Excel中的数据有效性所引用的数据源不撑持跨工做表引用，必需是引用统一个工做表中的数据。从2010版才能够跨工做表引用。若是2007版及之前版本想要跨工做表引用，要把区域封拆进定义名称中，如图27-7所示，定义名称为“名单”，引用位置选择“=根底信息!$B$2:$B$14”。然后在【数据验证】对话框中的【来源】参数框中输入公式“=名单”，如图27-8所示，那时就能够处理2007版及之前版本的数据有效性跨工做表引用的问题。
ENTERTITLE
图27-7定义名称
ENTERTITLE
图27-8【数据验证】对话框
2．定义名称
接下来继续造做员工信息卡，输入公式：
=INDEX(根底信息!$C:$C,MATCH(员工信息卡$C$2,根底信息!$B:$B,0))
那个公式的意思是根据员工信息卡工做表C2单位格的姓名，查找返回根底信息工做表C列的对应照片。不外它如今返回的成果是数字0，不妨，将整个公式停止复造，并封拆进定义名称，如图27-9所示，定义名称为“照片”，在引用位置处粘贴此公式。
ENTERTITLE
图27-9定义名称返回照片单位格
3．复造粘贴单位格
从根底信息表中，肆意复造一张照片，然后贴到员工信息卡的F2单位格，如图27-10所示，选中该照片，单击公式编纂栏，输入公式：
=照片
ENTERTITLE
图27-10完成照片的主动更新
那里其实并非实正引用到了“孙权”的照片，而是引用的根底信息表C列对应的单位格，该单位格中的内容城市在那里显示出来。
提醒：以上操做在2003版及以前或2010版及以后版本中都没有问题，但是在Excel2007的某些版本中，可能无法完成输入公式“=照片”那个步调，那是Excel的Bug。
4．完美其他根底信息
在E2单位格中输入公式：
=INDEX(根底信息!$A:$H,MATCH($C$2,根底信息!$B:$B,0),MATCH(D2,根底信息!$1:$1,0))
将公式粘贴到C3、E3、C4、E4单位格，完成最末的造做。如今更新C2单位格的姓名看一下效果，如图27-11所示。
ENTERTITLE
图27-11员工信息卡效果
用那个办法选择姓名的时候，此区域是一个静态区域，若是人数增加或削减要手动调整选择区域，会很费事，那时就能够考虑定义一个动态区域来取值。例如，用将在第31章讲的OFFSET函数，公式为：
=OFFSET(根底信息!$B$1,1,0,COUNTA(根底信息!$B:$B)-1)
将此公式封拆进定义名称，如许就不怕数据源的增加和削减了。
实战操练
如操练图8-1所示，A~C列是一个成本破费统计表，利用INDEX函数连系ROW函数、COLUMN函数，将C2:C17单位格区域的一维纵向数据转化为多行多列的二维数据。在F2单位格中输入公式，并复造到F2:I5单位格区域，完效果果如F8:I11单位格区域所示。
ENTERTITLE
操练图8-1一维区域转化为二维区域2
如操练图8-2所示，B2:E6单位格区域是一个二维区域的座位表，利用INDEX函数连系ROW函数将此二维区域转化为一维纵向区域，人员挨次根据先横向后纵向摆列，在G2单位格中输入公式并向下复造到G2:G21单位格区域，完效果果如I2:I21单位格区域所示。
ENTERTITLE
操练图8-2二维区域转化为一维区域
3如操练图8-3所示，A~G列是根底人员信息，利用INDEX函数连系MATCH函数完成响应的信息查询，并在J3、J9单位格各输入一个公式，并别离复造到J3:M6、J9:L11单位格区域。
ENTERTITLE
操练图8-3查询表4
如操练图8-4所示，根据“根底信息”工做表和“照片”工做表的内容，完成员工信息卡的造做。、
ENTERTITLE
操练图8-4员工信息卡
END