Excel里面数组函数应用技巧 -pc6资讯

您的位置：首页 → 办公软件 → Excel → Excel里面数组函数应用技巧

Excel里面数组函数应用技巧 时间：2011/1/31 15:48:49来源：百度整理作者：清晨我要评论(0)

我们这里以一个例子来和大家讲解Excel里面数组函数应用技巧

工作簿内一共有两个工作表Sheet1和Sheet2，先说Sheei1，如下
　Ａ　　　　Ｂ　　　　　Ｃ
货号序号　　　名称
101　　　1　　　车背带
101　　　2　　　合前片
101　　　3　　　合后片
101　　　4　　　车手带
101　　　5　　　车边片
101　　　6　　　合前袋
101　　　7　　　车后手带
202　　　1　　　车前片链
202　　　2　　　车前袋
202　　　3　　　合包
202　　　4　　　车后片
202　　　5　　　车手垫
202　　　6　　　合前袋

表Sheet2如下：
　　Ａ　　　　　　Ｂ　　　　　　Ｃ
货号　序号　　名称
101　　　5　　　[此单元格空]
101　　　2　　　[此单元格空]
101　　　3　　　[此单元格空]
101　　　7　　　[此单元格空]
202　　　3　　　[此单元格空]
202　　　1　　　[此单元格空]

　　要求根据Sheet2内容，查找Sheet1，在Sheet2的名称列填写入正确的内容。

　　题目分析：
　　根据要求，如果要在Sheet2某一行n填入正确的名称，首先需要在Sheet1找到这样的行：该行第一列内容等于Sheet2某行n第一列，该行第二列内容等于Sheet2某行n第二列。
　　因为涉及到数据的查找，拟采用数组公式实现。

　　1、首先定义名称：
　　（定义名称使用菜单：插入-名称）
　　dataA=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
　　dataB=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B:$B),1)

　　这样做的好处是在于无论Sheet1添加了多少行，dataA始终包括A列有内容的行，这样比起直接使用A:A整列，要节约很多的计算时间。dataB同理。

　　2、匹配条件
　　先匹配A列，选中Sheet2！D2:D13（因为原始数据从2~13行），在编辑栏输入
　　=IF(dataA=A2,1,0)
　　用Ctrl+Shift+回车，这样可以看到凡是与Sheet2！A2匹配的Sheet1的行，在这里都变成了1，而不匹配的则是0。
　　同理，在Sheet2！E2:E13输入
　　=IF(dataB=B2,1,0)
　　这样我们在D、E两列就得到了分别与Sheet2!A和Sheet2!B列匹配的数组。

　　3、合并匹配条件
　　我们需要的是两个条件的与运算，因此使用乘法是最好的，只有两个条件同时成立，1*1=1，否则两个乘数里面至少有1个为0，结果为0。
　　well，在Sheet2!F2:F13中输入数组公式：
　　=IF((dataA=A2)*(dataB=B2),1,0)
　　如此一来，就得到了一个新的由1和0组成的数组，某元素对应Sheet1的行如果能够匹配，该元素为1，否则为0。

　　4、检索行号
　　有了这个数组，我们需要得到匹配出来的行号，因为数组是由若干0和一个1组成的，所以我们只需要使用MATCH函数，就可以得到与Sheet2!An匹配的Sheet1的行号。
　　在Sheet2!G1输入
　　=MATCH（1，IF((dataA=A2)*(dataB=B2),1,0),1）
　　注意此处虽然仅仅是在一个单元格里面输入公式，但仍然是一个数组公式，需要使用Ctrl+Shift+Enter。
　　结果就是Sheet1中匹配的行号。

　　5、检索结果
　　有了行号，检索结果就很容易了，我们使用INDIRECT函数。
　　在Sheet2!C2输入数组公式：
　　=INDIRECT("Sheet1!$C$" & MATCH（1，IF((dataA=A2)*(dataB=B2),1,0),1）)
　　注意依然使用Ctrl+Shift+Enter，正确的结果就出现了。

　　对于下面的行，只需要拖动填充句柄复制公式就可以了。

　　另外别忘了删除掉用来演示的D、E、F、G列的已经不再需要的公式。

文章评论

查看所有0条评论>>