这是一位网友提给我的问题,说给大家看看;
工作簿内一共有两个工作表Sheet1和Sheet2,先说Sheei1,如下
A B C
货号 序号 名称
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如下:
A B C
货号 序号 名称
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列的已经不再需要的公式。
当前位置:主页 > 电脑教程 > 工具软件 > 办公软件 > Excel教程 > excel函数 > >
闁告帗顨夐妴鍐礃閸涱収鍟囧銈呭悁缁楀倿鐛崹顔藉暈濞达綇鎷�-600*90
Excel里面数组函数应用技巧
来源::未知 | 作者:bet皇冠体育_澳门皇冠体育-在线官网 | 本文已影响 人
闁告劕鎳庨鎰亜閸偆鍨煎Λ鐗埫肩粭鍛寸嵁閸喗鍟炲ù锝忔嫹-500*200
- 上一篇:Excel用Round函数让四舍五入更加精准
- 下一篇:没有了
闁告帗顨夐妴鍐礃閸涱収鍟囧銈夋涧瑜板憡绗熻缁犲秹宕ㄦ繝浣虹Т1-300*250
随机阅读
闁告帗顨夐妴鍐礃閸涱収鍟囧銈夋涧瑜板憡绗熻缁犲秹宕ㄦ繝浣虹Т3-300*250
- [excel函数] 怎样用Excel中的RAND函数
- [excel函数] Excel帮你选函数
- [excel函数] Excel用Round函数让四舍五
- [excel函数] Excel数据查找与引用相关
- [excel函数] Excel表格向上,中,向下
- [excel函数] 教你如何使用Excel函数 统
- [excel函数] Excel中的GET.CELL函数的
- [excel函数] Excel中组合函数分别得到
- [excel函数] Excel工作表中“IF”函数
- [excel函数] Excel表格中求差函数公式
- [excel函数] Excel三个条件函数解决实
- [excel函数] Excel函数筛选与排序使用
- [excel函数] 18个Excel最常用的公式运
- [excel函数] 快速掌握如何在Excel中自
- [excel函数] 灵活运用Excel函数快速录
- [excel函数] Excel自动求平均值的函数